MySQLi Articles - Page 110 of 422

Let us first create a table −mysql> create table DemoTable (    StudentName varchar(40),    StudentMarks int ); Query OK, 0 rows affected (0.64 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('John', 78); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values('Chris', 48); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable values('John', 67); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable values('Chris', 89); Query OK, 1 row affected (0.15 sec)Display all records from the table using select statement −mysql> select *from DemoTable;This will produce the ... Read More

For this, you can use UNION along with the ORDER BY clause. Let us first create a table −mysql> create table DemoTable1 (    Amount int ); Query OK, 0 rows affected (0.63 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1 values(234); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable1 values(567); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable1 values(134); Query OK, 1 row affected (0.43 sec)Display all records from the table using select statement −mysql> select *from DemoTable1;This will produce the following output −+--------+ | Amount | +--------+ ... Read More

If there are multiple MySQL keywords in a query, use backticks symbol rather than single quotes. Let us first create a table. Here, we have used two reserved keywords i.e. ‘key’ and ‘Limit’ −mysql> create table DemoTable (    `key` int NOT NULL AUTO_INCREMENT PRIMARY KEY ,    `Limit` int ); Query OK, 0 rows affected (0.72 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(`key`, `Limit`) values(null, 80); Query OK, 1 row affected (0.49 sec) mysql> insert into DemoTable(`key`, `Limit`) values(null, 90); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable(`key`, `Limit`) values(null, ... Read More

To skip records in MySQL SELECT, use OFFSET. Let us first create a table−mysql> create table DemoTable (    Name varchar(40) ); Query OK, 0 rows affected (0.63 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('Chris'); Query OK, 1 row affected (0.22 sec) mysql> insert into DemoTable values('Robert'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values('Mike'); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable values('Bob'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable values('David'); Query OK, 1 row affected (0.11 sec)Display all records from ... Read More

To speed up SELECT DISTINCT, you can create an index on the column or set of columns. Let us first create a table −mysql> create table DemoTable (    Name varchar(40) ); Query OK, 0 rows affected (1.13 sec)Following is the query to create an index −mysql> create index Name_Index on DemoTable(Name); Query OK, 0 rows affected (1.56 sec) Records: 0 Duplicates: 0 Warnings: 0Insert some records in the table using insert command −mysql> insert into DemoTable values('Chris'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values('Mike'); Query OK, 1 row affected (0.15 sec) mysql> insert into ... Read More

Let us first create a table −mysql> create table DemoTable (    Cost int,    Quantity int ); Query OK, 0 rows affected (0.80 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(65, 2); Query OK, 1 row affected (0.08 sec) mysql> insert into DemoTable values(290, 4); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable values(40, 3); Query OK, 1 row affected (0.10 sec)Display all records from the table using select statement −mysql> select *from DemoTable;This will produce the following output −+------+----------+ | Cost | Quantity | +------+----------+ | 65 ... Read More

For this, you can use the LIKE operator along with CONCAT() function. Let us first create a table −mysql> create table DemoTable (    Value text ); Query OK, 0 rows affected (0.63 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('MySQL'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable values('Is'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable values('Relational'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable values('Database'); Query OK, 1 row affected (0.13 sec)Display all records from the table using select statement −mysql> select ... Read More

To avoid displaying a certain id from a table, you need to use the operator, which is the NOT EQUAL operator. Let us first create a table −mysql> create table DemoTable7 (    StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    StudentName varchar(40) ); Query OK, 0 rows affected (0.53 sec)Insert some records in the table using insert command −mysql> insert into DemoTable7(StudentName) values('Chris'); Query OK, 1 row affected (0.25 sec) mysql> insert into DemoTable7(StudentName) values('Robert'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable7(StudentName) values('Mike'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable7(StudentName) ... Read More

For this, use LENGTH(), since if the length is 0 that would mean the string is empty. After finding, you can set it to NULL using the SET clause in the UPDATE command. Let us first create a table −mysql> create table DemoTable (    Name varchar(50) ); Query OK, 0 rows affected (0.68 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('Chris'); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable values(''); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values('David'); Query OK, 1 row affected (0.12 sec) mysql> ... Read More

Let us first create a table −mysql> create table DemoTable (    Id int ); Query OK, 0 rows affected (0.71 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(100); Query OK, 1 row affected (0.31 sec) mysql> insert into DemoTable values(101); Query OK, 1 row affected (0.26 sec) mysql> insert into DemoTable values(102); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable values(103); Query OK, 1 row affected (0.64 sec) mysql> insert into DemoTable values(104); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable values(105); Query OK, 1 row affected ... Read More