This article includes the following contents

• Simple query
• Conditional query:
• Group query: group by
• Sort query: order by
• Reduplication of query results: distinct

• Limit query volume: limit

  0 preparation

  First, create two tables employee and department:

mysql> select * from employee;
+----+----------+--------+--------+------+------+---------+
| id | name     | gender | salary | age  | gmr  | dept_id |
+----+----------+--------+--------+------+------+---------+
|  1 | lisan    | f      | 100.00 |   20 |    4 |    1001 |
|  2 | Will     | f      | 130.00 |   28 |    3 |    1001 |
|  3 | lucy     | m      | 500.70 |   18 |    2 |    1002 |
|  4 | Li Lei     | male     | 670.00 |   20 |    4 |    1003 |
|  5 | WangYong | m      | 700.90 |   25 |    5 |    NULL |
|  6 | Sam      | m      | 600.90 |   25 |    5 |    1004 |
+----+----------+--------+--------+------+------+---------+

mysql> select * from department;
+------+-----------+--------------+
| id   | name      | description  |
+------+-----------+--------------+
| 1001 | Ministry of Personnel    | personnel management     |
| 1002 | Administration Department    | Administrative management     |
| 1003 | R & D department    | research and development         |
| 1004 | Quality department    | Quality assurance     |
+------+-----------+--------------+

1 Simple Query

select * from employee;
//
select id，name,salary from employee;

2 Conditional Query

where conditional expression
// There are several query conditions

1. Comparisons: =,<,>>=,<=,!=,<>
2. Specified scope: between [] and [],NOT between [] and []
3. Specified set: IN [],NOT IN []
4. Matching character: like,NOT LIKE
5. Is it empty: IS NULL, IS NOT NULL
6. Multiple query conditions: AND, OR

//compare
select * from employ where age>20;
select * from employee where age<>20;

//Specified scope
select * from employee where age between 18 and 25;

//Specified set
select * from employee where name in ("Will","lucy");

//Match characters, where "%" is a wildcard, and "" matches a single character
select * from employee where name like "w%";
+----+----------+--------+--------+------+------+---------+
| id | name     | gender | salary | age  | gmr  | dept_id |
+----+----------+--------+--------+------+------+---------+
|  2 | Will     | f      | 130.00 |   28 |    3 |    1001 |
|  5 | WangYong | m      | 700.90 |   25 |    5 |    NULL |
+----+----------+--------+--------+------+------+---------+

select * from employee where name like "w_l";//The query result is empty
//Become
select * from employee where name like "w_ll";You can find this data.

//AND
select * from employ where age>20 and salary<500; 

select * from employee where name like "w%" AND age>20 AND salary>500;

//OR 
select * from employee where name like "w%" AND age>25 OR salary>500;

3 Reduplication of query results

Use the DISTINCT keyword to eliminate duplicate records

select distinct [value Name]

//eg:
select distinct age from employee;

4 ranking

Sorting is divided into descending DESC and ascending ASC, using ORDER BY keyword.

ORDER BY [value] [ASC | DESC]
Install ASC sort by default

Example:

//Ascending order
select * from employee order by age ASC;
//Descending order
select * from employee order by age DESC;
//Multiple Sorting Conditions: First, the fields of the same age are sorted in descending order according to id, and the fields of the same age are sorted in ascending order according to ID.
select * from employee order by age DESC,id ASC;

5 grouping query

GROUP BY [value] [HAVAING conditional expression] [WITH ROLLUP]

Value is grouped according to the field value. HAVING is used to restrict the display after grouping. WITH ROLLUP will add a record at the end of the record, which is the sum of all the records above.

5.1 Use group by query alone

//Using group by grouping alone, the query results show only one record of the grouping
select * from employee group by age;

5.2 is used with GRIOUP_CONCAT() function

Each group specified field is displayed

// Use with "GROUP_CONCAT()" function
select age,group_concat(name) from employee group by age;

+------+--------------------+
| age  | group_concat(name) |
+------+--------------------+
|   18 | lucy               |
| 20 | lisan, Li Lei|
|   25 | WangYong,Sam       |
|   28 | Will               |
+------+--------------------+
The results showed that they were divided into four groups, and the name s of all the people in each group were listed.

5.3 Used with Collection Functions

The set function is used to calculate the maximum and minimum values of the total records in the grouping.

//According to age grouping query, age is the same as a group, and then count() is used to calculate the number of records of each group for each group.
select age,count(age) from employee group by age;

5.4 is used with HAVING conditional expressions

Add HAVCING... It can limit the output of the results, only meet the conditions will be displayed.

//Use with HAVING
//Query according to age field, and then show the grouping whose record is greater than or equal to 2.
select age,count(age) from employee group by age HAVING count(age)>=2;

+------+------------+
| age  | count(age) |
+------+------------+
|   20 |          2 |
|   25 |          2 |
+------+------------+

More than 5.5 field groupings

//Grouped by age, the same records in age are grouped by gender.
select * from employee group by age,gender;

+----+----------+--------+--------+------+------+---------+
| id | name     | gender | salary | age  | gmr  | dept_id |
+----+----------+--------+--------+------+------+---------+
|  3 | lucy     | m      | 500.70 |   18 |    2 |    1002 |
|  1 | lisan    | f      | 100.00 |   20 |    4 |    1001 |
|  4 | Li Lei     | male     | 670.00 |   20 |    4 |    1003 |
|  5 | WangYong | m      | 700.90 |   25 |    5 |    NULL |
|  2 | Will     | f      | 130.00 |   28 |    3 |    1001 |
+----+----------+--------+--------+------+------+---------+

5.6 with WITH ROLLUP

WITH ROLLUP will add a record at the end of the sitting record, which is the sum of all the records above.

//The last one is the sum.
select age,count(*) from employee group by age WITH ROLLUP;

+------+----------+
| age  | count(*) |
+------+----------+
|   18 |        1 |
|   20 |        2 |
|   25 |        2 |
|   28 |        1 |
| NULL |        6 |
+------+----------+

6. Restrict the amount of queries

6.1 No initial location is specified

The number of entries recorded starts with the first entry.

limit [num]

//Show only the first two

select * from employee limit 2；

6.2 Specify the initial location

Starting from the specified location, the first record is 0.

limit [start],[limitNUm]

//Specify the initial, displaying Articles 2 to 4
select * from employee limit 1,3;