Common query statements for mysql databases (Single Table query)

1. Use the select statement to query the select * from data table name of a data table. 2. query the select Field 1 and 2from data table of one or more fields in the table; 3. query the specified data in the table. select * from the data table name orderbyiddesclimit2, 1. query the data in reverse order by id 4. query the specified path Select * f.

1. Use the select statement to query the select * from data table name of a data table. 2. query the select Field 1 and Field 2 from data table for one or more fields in the table; 3. query the specified data information in the table select * from the data table name order by id desc limit; // The queried data is sorted by id in reverse order 4. query the specified path Select * f

1. query a data table using the select statement

Select * from data table name;

2. query one or more fields in the table.

Select Field 1, Field 2 from data table;

3. query the specified data in the table.

Select * from data table name order by id desc limit 2, 1; // The queried data is sorted by id in reverse order.

4. query the specified path

Select * from data table where attribute = '... ';

5. query with the IN keyword

Select * from data table where field [NOT] IN (field value 1, field value 2... Field Value n );

<注：最适于数组作为查询条件，即：in (数组数据)>

For example:

Mysql> select * from library where price in (99,50 );

+ ---- + -------------- + -------- + ------- +

| Id | name | author | price |

+ ---- + -------------- + -------- + ------- +

| 1 | java example Daquan | Zhang Fan | 99 |

| 2 | mySQL | Pan Kaihua | 50 |

+ ---- + -------------- + -------- + ------- +

Mysql> select * from library where price not in (99,50 );

+ ---- + --------------- + -------- + ------- +

| Id | name | author | price |

+ ---- + --------------- + -------- + ------- +

| 3 | SQLserver2005 | Liu Zhiyong | 80 |

+ ---- + --------------- + -------- + ------- +

6. query the range with BETWEEN and

Select * from data table where field [NOT] BETWEEN value 1 and value 2;

For example:

Mysql> select * from country where idBETWEEN 2 and 4;

+ ---- + ---------- + ------------ + ------ + ---------- +

| Id | name | population | area | language |

+ ---- + ---------- + ------------ + ------ + ---------- +

| 2 | us | 489 | 60 | English |

| 3 | Japan | 89 | 30 | Jpanese |

| 4 | England | 2 | 300 | English |

+ ---- + ---------- + ------------ + ------ + ---------- +

3 rows in set (0.09 sec)

Mysql> select * from country where id not between 2 and 4;

+ ---- + ------- + ------------ + ------ + ---------- +

| Id | name | population | area | language |

+ ---- + ------- + ------------ + ------ + ---------- +

| 1 | china | 13 | 960 | chinese |

+ ---- + ------- + ------------ + ------ + ---------- +

7. Search for like character matching

(1) select * from data table where attribute like '% SQL %'; // query data whose attribute contains SQL characters

(2) select * from data table where attribute like 'a % B '; // you can call this operation to query the data of a string starting with a and ending with B.

(3) select * from data table where attribute like'm _ n'; // query data with three characters starting with m and ending with n in the attribute, the '_' in the middle can only represent one character

For example:

Mysql> select * from library where name like '% SQL % ';

+ ---- + --------------- + -------- + ------- +

| Id | name | author | price |

+ ---- + --------------- + -------- + ------- +

| 2 | mySQL | Pan Kaihua | 50 |

| 3 | SQLserver2005 | Liu Zhiyong | 80 |

+ ---- + --------------- + -------- + ------- +

Mysql> select * from library where name like 'mys _ L ';

+ ---- + ------- + -------- + ------- +

| Id | name | author | price |

+ ---- + ------- + -------- + ------- +

| 2 | mySQL | Pan Kaihua | 50 |

+ ---- + ------- + -------- + ------- +

8. Multi-condition query with AND

Mysql> select * from library whereprice = 50 and name like 'mys _ L ';

+ ---- + ------- + -------- + ------- +

| Id | name | author | price |

+ ---- + ------- + -------- + ------- +

| 2 | mySQL | Pan Kaihua | 50 |

+ ---- + ------- + -------- + ------- +

9 multi-condition query with OR

Mysql> select * from library where price = 50or price = 99;

+ ---- + -------------- + -------- + ------- +

| Id | name | author | price |

+ ---- + -------------- + -------- + ------- +

| 1 | java example Daquan | Zhang Fan | 99 |

| 2 | mySQL | Pan Kaihua | 50 |

+ ---- + -------------- + -------- + ------- 10 use the DISTINCT keyword to remove duplicate rows in the result

For example:

Original table: mysql> select * from library;

+ ---- + --------------- + -------- + ------- +

| Id | name | author | price |

+ ---- + --------------- + -------- + ------- +

| 1 | java example Daquan | Zhang Fan | 99 |

| 2 | mySQL | Pan Kaihua | 50 |

| 3 | SQLserver2005 | Liu Zhiyong | 80 |

| 4 | mySQL | Li Hui | 50 |

+ ---- + --------------- + -------- + ------- +

Mysql> select distinct name fromlibrary;

+ --------------- +

| Name |

+ --------------- +

| Java example overview |

| MySQL |

| SQLserver2005 |

+ --------------- +

11 sort query results with the order by keyword

Mysql> select * from library orderby id desc; // sort in reverse order

+ ---- + --------------- + -------- + ------- +

| Id | name | author | price |

+ ---- + --------------- + -------- + ------- +

| 4 | mySQL | Li Hui | 50 |

| 3 | SQLserver2005 | Liu Zhiyong | 80 |

| 2 | mySQL | Pan Kaihua | 50 |

| 1 | java example Daquan | Zhang Fan | 99 |

+ ---- + --------------- + -------- + ------- +

Mysql> select * from library orderby id asc; // forward order

+ ---- + --------------- + -------- + ------- +

| Id | name | author | price |

+ ---- + --------------- + -------- + ------- +

| 1 | java example Daquan | Zhang Fan | 99 |

| 2 | mySQL | Pan Kaihua | 50 |

| 3 | SQLserver2005 | Liu Zhiyong | 80 |

| 4 | mySQL | Li Hui | 50 |

+ ---- + --------------- + -------- + ------- +

12 query by group by keyword

(1) query by group by keyword

For example, mysql> select name, price from library group by price;

+ --------------- + ------- +

| Name | price |

+ --------------- + ------- +

| MySQL | 50 |

| SQLserver2005 | 80 |

| Java examples | 99 |

+ --------------- + ------- +

3 rows in set (0.00 sec)

Mysql> select name, price from library group by name;

+ --------------- + ------- +

| Name | price |

+ --------------- + ------- +

| Java examples | 99 |

| MySQL | 50 |

| SQLserver2005 | 80 |

+ --------------- + ------- +

(2) Use the group by keyword with the GROU_CONCAT Function

Mysql> selectname, GROUP_CONCAT (price) from library group by name;

+ --------------- + --------------------- +

| Name | GROUP_CONCAT (price) |

+ --------------- + --------------------- +

| Java examples | 99 |

| MySQL | 50, 50 |

| SQLserver2005 | 80 |

+ --------------- + --------------------- +

(3) grouping by multiple fields

Mysql> selectid, name, price from library group by name, price; // when the value of the price field is equal, group by name field

+ ---- + --------------- + ------- +

| Id | name | price |

+ ---- + --------------- + ------- +

| 1 | java example description | 99 |

| 2 | mySQL | 50 |

| 3 | SQLserver2005 | 80 |

+ ---- + --------------- + ------- +

13 use LIMIT to LIMIT the number of query results

Mysql> select * from library orderby id asc limit 2, 3; // obtain two pieces of data in the forward direction, starting from the third

+ ---- + --------------- + -------- + ------- +

| Id | name | author | price |

+ ---- + --------------- + -------- + ------- +

| 3 | SQLserver2005 | Liu Zhiyong | 80 |

| 4 | mySQL | Li Hui | 50 |

+ ---- + --------------- + -------- + ------- +

2 rows in set (0.00 sec)

Mysql> select * from libraryorder by id desc limit 2, 3; // obtain two pieces of data, in reverse order, ending from the last three, only display the first two

+ ---- + -------------- + -------- + ------- +

| Id | name | author | price |

+ ---- + -------------- + -------- + ------- +

| 2 | mySQL | Pan Kaihua | 50 |

| 1 | java example Daquan | Zhang Fan | 99 |

+ ---- + -------------- + -------- + ------- +