MySQLi Articles - Page 326 of 422

We can use ‘cross join’ without on condition. Cross join gives the result in cartesian product form. For instance, if in one table there are 3 records and another table has 2 records, then the first record will match with all the second table records. Then, the same process will be repeated for second record and so on. Example of cross join Creating the first table mysql> CREATE table ForeignTableDemo - > ( - > Id int, - > Name varchar(100), - > FK int ... Read More

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 More

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 More

‘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 More

Similarity between two strings can be checked with the help of ‘strcmp()’ function. Here are the conditions.If both strings are equal, then it returns 0.If first string is less than the second string, it returns -1.If first string is greater than the second string, it returns 1.Here is an example.Case 1 − If both strings are equal.The following is the query.mysql > SELECT STRCMP("demo", "demo");The following is the output of the above query.+------------------------+ | STRCMP("demo", "demo") | +------------------------+ | 0 ... Read More

Yes, it is possible to use UPDATE query with LIMIT in MySQL. Let us see how.For our example, we will first create a table. The CREATE command is used to create a table.mysql>CREATE table tblUpdateLimit -> ( -> id int, -> name varchar(100) -> ); Query OK, 0 rows affected (0.53 sec)Records are inserted with the help of INSERT command.mysql>INSERT into tblUpdateLimit values(1, 'John'); Query OK, 1 row affected (0.54 sec) mysql>INSERT into tblUpdateLimit values(2, 'Carol'); Query OK, 1 row affected (0.12 sec) mysql>INSERT into tblUpdateLimit values(3, 'Smith'); Query OK, 1 row affected (0.10 sec) mysql>INSERT into ... Read More

To change the timezone in MySQL, we can use the SET command. The following is the syntax. SET time_zone=’someValue’; Let us now use the above syntax and apply it in the below query. mysql > SET time_zone = '+8:00'; Query OK, 0 rows affected (0.00 sec) Above, the time_zone is changed by a value equivalent to 8. To change the timezone globally, use the GLOBAL command. Here is the syntax. SET GLOBAL time_zone = 'someValue'; Here is an example. mysql> SET GLOBAL time_zone = '+8:00'; Query OK, 0 rows affected (0.00 sec) The above query will change the timezone globally.

Firstly, check the MySQL database with DESC command. mysql>DESC mysql.db; The following is the output. +-----------------------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------------+---------------+------+-----+---------+-------+ | Host | char(60) | NO | PRI | | ... Read More

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 More

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.