Article Categories
- All Categories
-
Data Structure
-
Networking
-
RDBMS
-
Operating System
-
Java
-
MS Excel
-
iOS
-
HTML
-
CSS
-
Android
-
Python
-
C Programming
-
C++
-
C#
-
MongoDB
-
MySQL
-
Javascript
-
PHP
-
Economics & Finance
MySQLi Articles
Page 331 of 341
Open MySQL root access from all hosts?
To open root access from all hosts, we need to change the database to “mysql” with the help of USE command. The syntax of USE command is as follows − USE anyDatabasename; Now, I will use predefined database ‘mysql’, which is as follows − mysql> use mysql; Database changed I have changed the database above. Here is the query to get root access from the entire host − mysql> UPDATE user set host='%' where host='localhost'; Query OK, 6 rows affected (0.19 sec) Rows matched: 6 Changed: 6 Warnings: 0
Read MoreMyISAM versus InnoDB in MySQL?
Both are engine types. Here is the query by which we can get to know which engine type and tables are being used. Firstly, we will choose the database with the help of USE command − mysql> USE business; Database changed Here is the query through which we can know which table or engine is being used − mysql> SHOW table status; The following is the output +------------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+ |Name | Engine | Version | Row_format | ...
Read MoreError 1046 No database Selected, how to resolve?
The 1046 error occurs if you forget to select any database before creating a table. Let us see how and why this error occurs. We will try to create a table without selecting a database − mysql> CREATE table MyTable1 -> ( -> id int -> ); ERROR 1046 (3D000): No database selected Or mysql> INSERT into sample values(1); ERROR 1046 (3D000): No database selected Look at the output above, we are getting the same 1046 error: “No database selected” Now, we can resolve this error after selecting any ...
Read MoreHow to raise an error within MySQL?
MySQL has introduced signals similar to an exception in other languages. Let us first see the syntax of signal. SIGNAL SQLSTATE ' PredefinedValueforSignalError' SET MESSAGE_TEXT = 'AnyMessageInformation'; Above, we have set our own error message text as well. We will apply the above query to get an error message with the help of signals. mysql > SIGNAL SQLSTATE '42927' SET MESSAGE_TEXT = 'Error Generated'; The following is the output of the above query. ERROR 1644 (42927): Error Generated In the above output, ‘42927’ is a SQLSTATE and ‘Error Generated’ is an error message, which we added.
Read MoreMySQL pagination without double-querying?
To work with MySQL pagination, firstly let us see how to use CREATE command and use it to create a table. mysql>CREATE table RowCountDemo -> ( -> ID int, -> Name varchar(100) -> ); Query OK, 0 rows affected (0.95 sec) Records are inserted with the help of INSERT command. mysql>INSERT into RowCountDemo values(1, 'Larry'); Query OK, 1 row affected (0.15 sec) mysql>INSERT into RowCountDemo values(2, 'John'); Query OK, 1 row affected (0.13 sec) mysql>INSERT into RowCountDemo values(3, 'Bela'); Query OK, 1 row ...
Read MoreSorting varchar field numerically in MySQL?
‘LPAD(lower(column_name))’ is used to sort the varchar field numerically in MySQL. Let us see an example. Firstly, we will create a table. The CREATE command is used to create a table. mysql> create table SortingvarcharDemo -> ( -> List varchar(10) -> ); Query OK, 0 rows affected (0.82 sec) Records are inserted with the help of INSERT command. mysql> insert into SortingvarcharDemo values("99"); Query OK, 1 row affected (0.12 sec) mysql> insert into SortingvarcharDemo values("9"); Query OK, 1 row affected (0.17 sec) mysql> insert into SortingvarcharDemo ...
Read MoreUsage of backtick in SQL statements?
The backtick can be used in MySQL. To create a table, we can put table_name in backticks. Example of Backtick in MySQL. The CREATE command is used to create a table. Here, we have added the table name using the backtick symbol. mysql> create table `backtickSymbol` -> ( -> uniId int -> ); Query OK, 0 rows affected (1.65 sec) Records are inserted with the help of INSERT command. mysql> insert into `backtickSymbol` values(1); Query OK, 1 row affected (0.20 sec) mysql> insert into `backtickSymbol` values(2); Query ...
Read MoreGenerating a unique random 10 character string using MySQL?
In order to generate a 10 character string, we can use inbuilt functions ‘rand()’ and ‘char()’. The following is the query to generate random 10 character string. mysql> SELECT concat( - > char(round(rand()*25)+97), - > char(round(rand()*25)+97), - > char(round(rand()*25)+97), - > char(round(rand()*25)+97), - > char(round(rand()*25)+97), - > char(round(rand()*25)+97), - > char(round(rand()*25)+97), - > char(round(rand()*25)+97), - > char(round(rand()*25)+97), - > char(round(rand()*25)+97) - > )AS Random10CharacterString; ...
Read MoreMySQL ON vs USING?
In general, we use ON in MySQL. In Joins, we use ON in a set of columns. USING is useful when both the tables share a column of the exact same name on which they join. Example of On. Creating our first table. mysql> CREATE table ForeignTableDemo -> ( -> Id int, -> Name varchar(100), - > FK int - > ); Query OK, 0 rows affected (0.47 sec) Creating our second table. mysql> CREATE table PrimaryTableDemo - > ...
Read MoreHow to use a function for default value in MySQL?
We cannot use a function for default value in MySQL, but we can use triggers. Let us see an example. First, we will create a table. The CREATE command is used to create a table. mysql> CREATE table TbLFunctionTrigger - > ( - > id int, - > username varchar(100) - > ); Query OK, 0 rows affected (0.55 sec) The following is the syntax to create a trigger and include a default value. CREATE TRIGGER anyName BEFORE INSERT ON yourTableName FOR EACH ROW ...
Read More