preface

As a test engineer, when comparing the test results, you have to deal with the database more or less. To deal with the database, you must master some common SQL query syntax. Recently, some test partners said that they were not good at SQL query. They asked me if I had a document on SQL query syntax to learn, so Xiaobian sorted out this super detailed SQL query syntax tutorial for your reference!

1. Data preparation

Create database and data table

--Create database
create database test1 charset=utf8;

--Use database
use test1;

--Create students table
create table students(
    id int unsigned primary key auto_increment not null,
    name varchar(20) default '',
    age tinyint unsigned default 0,
    height decimal(5,2),
    Gender enum ('male ',' female ',' neutral ',' confidential ') default' confidential ',
    cls_id int unsigned default 0,
    is_delete bit default 0
);

Student table field description

--Create classes table
create table classes (
    id int unsigned auto_increment primary key not null,
    name varchar(30) not null
);

Class table field description

Prepare data

--Insert data into the classes table
insert into classes values (6, "python01"), (0, "python02");
--Insert data into the students table
insert into students values
(0, 'Xiao Ming', 18180.00,2,1,0),
(0, 'Xiaoyue', 18180.00,2,2,1),
(0, 'Xiang Yu', 29185.00,1,1,0),
(0, 'Liu Liu', 59175.00,1,2,1),
(0, 'Wang laomazi', 38160.00,2,1,0),
(0, 'Lao Li', 28150.00,4,2,1),
(0, 'Lao Zhang', 18172.00,2,1,1),
(0, 'old cow', 36, null, 1,1,0),
(0, 'Zhang Fei', 58181.00,1,2,0),
(0, 'Guan Yu', 74166.00,2,2,0),
(0, 'Liu Bei', 66162.00,3,3,1),
(0, 'Sun Quan', 77180.00,2,4,0),
(0, 'Cao Cao', 66170.00,1,4,0),
(0, 'Zhou Yu', 55176.00,2,5,0);

2. Basic query

• Query all fields

Select * from table name;
Example:
select * from students;

• Query specified fields

Select column 1, column 2 From table name;
Example:
select name from students;

• Alias fields using as

Select id as sequence number, name as name, gender as gender from students;

• You can alias the table through as

--If it is a single table query, the indication can be omitted
select id, name, gender from students;

--Table name Field name
select students.id,students.name,students.gender from students;

--You can alias the table through as 
select s.id,s.name,s.gender from students as s;

• Eliminate duplicate rows
• Use distinct before the column after select to eliminate duplicate rows

Select distinct column 1 From table name;
Example:
select distinct gender from students;

3. Condition query

Use the where clause to filter the data in the table, and the rows with the result of true will appear in the result set

• The syntax is as follows:

Select * from table name where condition;
Example:
select * from students where id=1;

• After where, multiple operators are supported for conditional processing
• Comparison operator
• Logical operator
• Fuzzy query
• Range query
• Null judgment

1. Comparison operator

• Equal to:=
• Greater than: >
• Greater than or equal to: >=
• Less than:<
• Up to:<=
• Not equal to:= Or < >

Example 1: query students with number greater than 3

select * from students where id > 3;

Example 2: query students with no more than 4

select * from students where id <= 4;

Example 3: query students whose names are not “Guan Yu”

select * from students where name != ' Guan Yu ';

Example 4: query students not deleted

select * from students where is_delete=0;

2. Logical operator

• and
• or
• not

Example 5: query female students with number greater than 3

select * from students where id > 3 and gender=0;

Example 6: students whose query number is less than 4 or not deleted

select * from students where id < 4 or is_delete=0;

3. Fuzzy query

• like
• %Represents any number of arbitrary characters
• _ Represents an arbitrary character

Example 7: query students surnamed Huang

Select * from students where name like 'yellow%';

Example 8: query students whose surname is Huang and whose “first name” is one word

Select * from students where name like 'yellow';

Example 9: query students surnamed Liu or Fei

Select * from students where name like 'Liu%' or name like '% Fei';

4. Range query

• In means in a discontinuous range

Example 10: query students whose number is 1 or 3 or 8

select * from students where id in(1,3,8);

• between … and … Indicates in a continuous range

Example 11: query students with numbers 3 to 8

select * from students where id between 3 and 8;

Example 12: query boys with numbers 3 to 8

select * from students where (id between 3 and 8) and gender=1;

5. Null judgment

• Note: null is different from ”
• Null is null

Example 13: query students who do not fill in their height

select * from students where height is null;

• Non null is not null

Example 14: query the students whose height is filled in

select * from students where height is not null;

Example 15: query the boy whose height is filled in

select * from students where height is not null and gender=1;

6. Priority description

• The order of priority from high to low is: parentheses, not, comparison operators and logical operators
• And operates before or. If it occurs at the same time and you want to calculate or first, it needs to be used in combination with ()

4. Sort

To facilitate viewing data, you can sort the data

Syntax:

Select * from table name order by column 1 asc|desc

explain

• Sort row data by column 1. If the values of some rows and columns are the same, sort by column 2, and so on
• By default, the column values are sorted from small to large (ASC)
• ASC is in ascending order
• Desc is sorted from large to small, i.e. descending

Example 1: query the information of undeleted boys, in descending order by student number

select * from students where gender=1 and is_delete=0 order by id desc;

Example 2: query the undeleted student information in ascending order by name

select * from students where is_delete=0 order by name;

Example 3: display all student information, first sort according to the age from big — > small, and when the age is the same, sort according to the height from tall — > short

select * from students  order by age desc,height desc;

5. Aggregate function

In order to get statistics quickly, the following five aggregate functions are often used

1. Count

• Count (*) means to calculate the total row number. Write the star and column name in parentheses, and the result is the same

Example 1: query the total number of students

select count(*) from students;

2. Maximum

• Max (column) indicates the maximum value of this column

Example 2: query the maximum number of girls

select max(id) from students where gender=2;

3. Minimum value

• Min (column) means to find the minimum value of this column

Example 3: query the minimum number of undeleted students

select min(id) from students where is_delete=0;

4. Sum

• Sum (column) means to sum this column

Example 4: query the total age of boys

select sum(age) from students where gender=1;

--Average age
select sum(age)/count(*) from students where gender=1;

5. Average value

• AVG (column) means averaging this column

Example 5: query the average number of undeleted girls

select avg(id) from students where is_delete=0 and gender=2;

6. Grouping query

1、group by

1. Group by means of grouping query results according to one or more fields. Those with the same field values are a group
2. Group by can be used for single field grouping or multiple field grouping

select * from students;

#Grouping by gender
select gender from students group by gender;

Group according to the gender field. All the values of the gender field have four ‘male’, ‘female’, ‘neutral’ and ‘confidential’, so they are divided into four groups. When group by is used alone, only the first record of each group is displayed, so it has little practical significance when group by is used alone

2、group by + group_concat()

1. group_ Concat (field name) can be used as an output field,
2. Indicates that after grouping, group is used according to the grouping results_ Concat () to place a collection of values for a field in each group

select gender from students group by gender;

#Displays the name of each group after grouping
select gender,group_concat(name) from students group by gender;


#Displays the ID of each group after grouping
select gender,group_concat(id) from students group by gender;

3. Group by + aggregate function

1. Via group_ Inspired by concat (), since we can count the set of values of a field in each group, we can also do some operations on the set of values through the set function

#Show age after grouping
select gender,group_concat(age) from students group by gender;


#The average age of men / women was counted respectively
select gender,avg(age) from students group by gender;
+--------+----------+
| gender | avg(age) |
+--------+----------+
|Male | 32.6000|
|Female 23.2857|
|Neutral | 33.0000|
|Confidentiality | 28.0000|
+--------+----------+

#Count the number of men / women respectively
select gender,count(*) from students group by gender;
+--------+----------+
| gender | count(*) |
+--------+----------+
|Male | 5|
|Female 7|
|Neutral | 1|
|Confidentiality | 1|
+--------+----------+

4、group by + having

1. Having condition expression: used to specify some conditions after grouping queries to output query results
2. Having is the same as where, but having can only be used for group by

select gender,count(*) from students group by gender having count(*)>2;
+--------+----------+
| gender | count(*) |
+--------+----------+
|Male | 5|
|Female 7|
+--------+----------+

5、group by + with rollup

1. The function of with rollup is to add a new row at the end to record the sum of all records in the current column

select gender,count(*) from students group by gender with rollup;
+--------+----------+
| gender | count(*) |
+--------+----------+
|Male | 5|
|Female 7|
|Neutral | 1|
|Confidentiality | 1|
| NULL   |       14 |
+--------+----------+


select gender,group_concat(age) from students group by gender with rollup;
+--------+-------------------------------------------+
| gender | group_concat(age)                         |
+--------+-------------------------------------------+
|Men 29,59,36,27,12|
|Female 18,18,38,18,25,12,34|
|Neutral | 33|
|Confidentiality | 28|
| NULL   | 29,59,36,27,12,18,18,38,18,25,12,34,33,28 |
+--------+-------------------------------------------+

5. Pagination

When the amount of data is too large, it is very troublesome to view data in one page. At this time, multiple data paging is required. Let’s take a look at SQL paging query

grammar

Select * from table name limit start, count

explain

• Starting from start, get count pieces of data

Example 1: query the information of the first three lines

select * from students where gender=1 limit 0,3;

Example: paging

• Known: M pieces of data are displayed on each page, and page n is currently displayed
• Total pages: this logic will be implemented later in Python
• Total number of queries P1
• Use P1 divided by m to get P2
• If divided by whole, P2 is the total number of pages
• If not divided, P2 + 1 is the total number of pages
• Find the data on page n

select * from students where is_delete=0 limit (n-1)*m,m

7. Subquery

Subquery

If another select statement is embedded in a select statement, the embedded select statement is called a subquery statement

Sub query classification

• Scalar subquery: the result returned by the subquery is one data (one row and one column)
• Column subquery: the returned result is one column (one column with multiple rows)
• Row subquery: the returned result is one row (one row with multiple columns)

scalar subquery

1. Query the average age of students in class
2. Query students older than average age

Query the average age of students in the class

select * from students where age > (select avg(age) from students);

Column level subquery

• Query all class names of students in the class
• Find all class IDS in the student table
• Find the corresponding name in the class table

select name from classes where id in (select cls_id from students);

Row level subquery

• Demand: find the youngest and shortest students in the class
• Row element: synthesize multiple fields into one row element, which will be used in row level subqueries

select * from students where height=(select min(height) from students where age=(select min(age) from students));

Use of specific keywords in subquery

• In range
• Format: main query where condition in (column subquery)

8. Connection query

When the columns of query results come from multiple tables, you need to connect multiple tables into a large data set, and then select the appropriate columns to return. In this case, you need to use the connection query. The following describes the common three connection query syntax:

• 1. Inner join: the query result is the data syntax matched by the two tables. Select * from table 1 inner join table 2 on table 1 Column = Table 2 Example: use left join to query class table and student table select s.name, c.name from students as s inner join classes as C on s.cls_ id = c.id;
• 2. Right join: the query result is the data matched by the two tables. The data is unique to the right table. For the data that does not exist in the left table, use the null filling syntax select * from table 1 right join table 2 on table 1 Column = Table 2 Example: use right join to query class table and student table select * from students as s right join classes as C on s.cls_ id = c.id;
• 3. Left join: the result of the query is the data matched by two tables, which is unique to the left table. For the data that does not exist in the right table, use the null filling syntax select * from table 1, left join table 2 on table 1 Column = Table 2 Example: query student name and class name select * from students as s left join classes as C on s.cls_ id = c.id;

The commonly used connection query syntax is introduced here. You can expand your study of more connection query syntax