MySQL knowledge summary III (MySQL query data)
MySQL knowledge summary I (MySQL common terms)
MySQL knowledge summary II (basic MySQL operation)
MySQL knowledge summary III (MySQL query data)
MySQL knowledge summary IV (SQL injection)
MySQL knowledge summary 5 (MySQL functions and operators)
- Keyword execution order
- 1,from
- 2,join on
- 3,where
- 4,group by
- 5,having
- 6,order by
- 7. limit and offset
- 8,distinct
- 9,union
- 10. LIKE fuzzy matching
- 11. Regular expression matching
- 12. Multiple nested query
sql keyword execution order
<SELECT clause> [<FROM clause>] [<WHERE clause>] [<GROUP BY clause>] [<HAVING clause>] [<ORDER BY clause>] [<LIMIT clause>] [ OFFSET M] start->FROM clause->WHERE clause->GROUP BY clause->HAVING clause->ORDER BY clause->SELECT clause->LIMIT clause->final result One SELECT In a statement, the order of clauses is fixed. # In the query statement, you can use one or more tables, which are separated by commas (,), and use the WHERE statement to set the query conditions. # The SELECT command can read one or more records. # Use an asterisk (*) instead of other fields, and the SELECT statement will return all field data of the table # Use the WHERE statement to include any conditions. It is a constraint declaration that is used to constrain data and takes effect before returning the result set. # Use the LIMIT property to set the number of records returned. # Specify the data OFFSET to start the query with the SELECT statement through OFFSET. By default, the OFFSET is 0. # group by: group the result set of the select query according to a certain field or expression to obtain a set of groups, and then take the value of a specified field or expression from each group. # having: used to filter the groups found by where and group by to find the qualified grouping results. It is a filter declaration, which is used to filter the query results after the query returns the result set. from -> on -> join -> where -> group by(Start using select The alias in can be used in subsequent statements) -> avg,sum.... -> having -> select -> distinct -> order by -> limit and offset
Table creation
>create table student(stuNum int(9) unsigned zerofill not null primary key,
teaNum int(9) unsigned not null,
stuName varchar(11) not null,
stuAge int(3) unsigned,
stuIn date not null,
stuOut date)ENGINE=InnoDB DEFAULT CHARSET=utf8;
# The above is to create a table named "student",
# The student number ("stuNum") is an integer, the number is 9 digits, is not empty, and is the primary key. Fill the missing number in the front with 0 (Note: the position described by zerofill);
# The teacher number ("teaNum") is an integer with 9 digits and cannot be empty;
# The name ("stuName") is a character type, with a maximum of 10 characters and cannot be empty;
# The age ("stuAge") is an integer, and the longest number is 3 digits;
# The enrollment time ("stuIn") is the date and cannot be blank;
# The departure time ("stuOut") is the date;
# ENGINE sets the storage ENGINE and CHARSET sets the encoding. (Note: if Chinese is saved, the data coding format can be set to utf-8 in order to prevent garbled code)
>create table if not exists teacher(teaNum int(9) unsigned not null,
teaName varchar(11) not null,
teaIn date not null,
teaOut date,
primary key(teaNum)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
# Partially ibid
# if not exists table name: create this table if it does not exist;
>create table grade(rank int unsigned,
stuNum int(9) unsigned not null,
stuName varchar(11) not null,
teaNum int(9) unsigned not null,
Chinese int(3),
English int(3),
primary key(rank, stuName),
constraint g_stuNum foreign key(stuNum) references student(stuNum),
foreign key(teaNum) references teacher(teaNum))ENGINE=InnoDB DEFAULT CHARSET=utf8;
# Partially ibid
# Set the joint primary key ranking and student name ('rank ',' stuname ');
# Set the foreign key student number ("stunum"), reference the "stunum" field in the "student" table, and name it "g"_ stuNum“；
# Set the foreign key student number ("teaNum"), reference the "teaNum" field in the "teacher" table, and use the name generated by the system by default;
After adding data
> select * from student; +-----------+-----------+---------+--------+------------+------------+ | stuNum | teaNum | stuName | stuAge | stuIn | stuOut | +-----------+-----------+---------+--------+------------+------------+ | 020181001 | 120181001 | Zhang 1 | 16 | 2018-09-01 | NULL | | 020181002 | 120181001 | Li 2 | 16 | 2018-09-01 | 2022-03-04 | | 020181003 | 120181002 | Li 3 | 16 | 2018-09-01 | 2022-03-01 | | 020181004 | 120181002 | Wang 4 | 16 | 2018-09-01 | 2022-03-01 | | 020181005 | 120181003 | Wang 4 | 17 | 2018-09-01 | 2022-03-01 | | 020181006 | 120181003 | Zhang 1 | 17 | 2018-09-01 | 2021-07-01 | +-----------+-----------+---------+--------+------------+------------+ > select * from teacher; +-----------+---------+------------+--------+ | teaNum | teaName | teaIn | teaOut | +-----------+---------+------------+--------+ | 120181001 | A li | 2016-03-01 | NULL | | 120181002 | Ajiang | 2017-03-01 | NULL | | 120181003 | A Xia | 2016-03-01 | NULL | | 120181004 | A Liang | 2017-03-01 | NULL | +-----------+---------+------------+--------+ > select * from grade; +------+----------+---------+-----------+---------+---------+ | rank | stuNum | stuName | teaNum | Chinese | English | +------+----------+---------+-----------+---------+---------+ | 1 | 20181001 | Zhang 1 | 120181001 | 100 | 100 | | 1 | 20181002 | Li 2 | 120181001 | 100 | 100 | | 2 | 20181003 | Li 3 | 120181002 | 99 | 98 | | 3 | 20181004 | Wang 4 | 120181002 | 95 | 98 | | 4 | 20181005 | Wang 4 | 120181003 | 95 | 90 | | 5 | 20181006 | Zhang 1 | 120181003 | 80 | 80 | +------+----------+---------+-----------+---------+---------+
1,from
# Select the table and Cartesian product the table. >select * from student, teacher; >+-----------+-----------+---------+--------+------------+------------+-----------+---------+------------+--------+ | stuNum | teaNum | stuName | stuAge | stuIn | stuOut | teaNum | teaName | teaIn | teaOut | +-----------+-----------+---------+--------+------------+------------+-----------+---------+------------+--------+ | 020181001 | 120181001 | Zhang 1 | 16 | 2018-09-01 | NULL | 120181001 | A li | 2016-03-01 | NULL | | 020181001 | 120181001 | Zhang 1 | 16 | 2018-09-01 | NULL | 120181002 | Ajiang | 2017-03-01 | NULL | | 020181001 | 120181001 | Zhang 1 | 16 | 2018-09-01 | NULL | 120181003 | A Xia | 2016-03-01 | NULL | | 020181001 | 120181001 | Zhang 1 | 16 | 2018-09-01 | NULL | 120181004 | A Liang | 2017-03-01 | NULL | | 020181002 | 120181001 | Li 2 | 16 | 2018-09-01 | 2022-03-04 | 120181001 | A li | 2016-03-01 | NULL | | 020181002 | 120181001 | Li 2 | 16 | 2018-09-01 | 2022-03-04 | 120181002 | Ajiang | 2017-03-01 | NULL | | 020181002 | 120181001 | Li 2 | 16 | 2018-09-01 | 2022-03-04 | 120181003 | A Xia | 2016-03-01 | NULL | | 020181002 | 120181001 | Li 2 | 16 | 2018-09-01 | 2022-03-04 | 120181004 | A Liang | 2017-03-01 | NULL | | 020181003 | 120181002 | Li 3 | 16 | 2018-09-01 | 2022-03-01 | 120181001 | A li | 2016-03-01 | NULL | | 020181003 | 120181002 | Li 3 | 16 | 2018-09-01 | 2022-03-01 | 120181002 | Ajiang | 2017-03-01 | NULL | | 020181003 | 120181002 | Li 3 | 16 | 2018-09-01 | 2022-03-01 | 120181003 | A Xia | 2016-03-01 | NULL | | 020181003 | 120181002 | Li 3 | 16 | 2018-09-01 | 2022-03-01 | 120181004 | A Liang | 2017-03-01 | NULL | | 020181004 | 120181002 | Wang 4 | 16 | 2018-09-01 | 2022-03-01 | 120181001 | A li | 2016-03-01 | NULL | | 020181004 | 120181002 | Wang 4 | 16 | 2018-09-01 | 2022-03-01 | 120181002 | Ajiang | 2017-03-01 | NULL | | 020181004 | 120181002 | Wang 4 | 16 | 2018-09-01 | 2022-03-01 | 120181003 | A Xia | 2016-03-01 | NULL | | 020181004 | 120181002 | Wang 4 | 16 | 2018-09-01 | 2022-03-01 | 120181004 | A Liang | 2017-03-01 | NULL | | 020181005 | 120181003 | Wang 4 | 17 | 2018-09-01 | 2022-03-01 | 120181001 | A li | 2016-03-01 | NULL | | 020181005 | 120181003 | Wang 4 | 17 | 2018-09-01 | 2022-03-01 | 120181002 | Ajiang | 2017-03-01 | NULL | | 020181005 | 120181003 | Wang 4 | 17 | 2018-09-01 | 2022-03-01 | 120181003 | A Xia | 2016-03-01 | NULL | | 020181005 | 120181003 | Wang 4 | 17 | 2018-09-01 | 2022-03-01 | 120181004 | A Liang | 2017-03-01 | NULL | | 020181006 | 120181003 | Zhang 1 | 17 | 2018-09-01 | 2021-07-01 | 120181001 | A li | 2016-03-01 | NULL | | 020181006 | 120181003 | Zhang 1 | 17 | 2018-09-01 | 2021-07-01 | 120181002 | Ajiang | 2017-03-01 | NULL | | 020181006 | 120181003 | Zhang 1 | 17 | 2018-09-01 | 2021-07-01 | 120181003 | A Xia | 2016-03-01 | NULL | | 020181006 | 120181003 | Zhang 1 | 17 | 2018-09-01 | 2021-07-01 | 120181004 | A Liang | 2017-03-01 | NULL | +-----------+-----------+---------+--------+------------+------------+-----------+---------+------------+--------+
2,join on
on Yes, I often follow join Later, it plays a restrictive role; But in the actual operation, it is carried out first on Filter and execute join Connect tables. So put the two together. See left connection for details( left join)Analysis of (1)Inner connection( INNER JOIN)(Equivalent connection): find the intersection of two tables (meet on Constraints, No on Is a simple Cartesian product. >select * from student inner join teacher on student.teaNum = teacher.teaNum; +-----------+-----------+---------+--------+------------+------------+-----------+---------+------------+--------+ | stuNum | teaNum | stuName | stuAge | stuIn | stuOut | teaNum | teaName | teaIn | teaOut | +-----------+-----------+---------+--------+------------+------------+-----------+---------+------------+--------+ | 020181001 | 120181001 | Zhang 1 | 16 | 2018-09-01 | NULL | 120181001 | A li | 2016-03-01 | NULL | | 020181002 | 120181001 | Li 2 | 16 | 2018-09-01 | 2022-03-04 | 120181001 | A li | 2016-03-01 | NULL | | 020181003 | 120181002 | Li 3 | 16 | 2018-09-01 | 2022-03-01 | 120181002 | Ajiang | 2017-03-01 | NULL | | 020181004 | 120181002 | Wang 4 | 16 | 2018-09-01 | 2022-03-01 | 120181002 | Ajiang | 2017-03-01 | NULL | | 020181005 | 120181003 | Wang 4 | 17 | 2018-09-01 | 2022-03-01 | 120181003 | A Xia | 2016-03-01 | NULL | | 020181006 | 120181003 | Zhang 1 | 17 | 2018-09-01 | 2021-07-01 | 120181003 | A Xia | 2016-03-01 | NULL | +-----------+-----------+---------+--------+------------+------------+-----------+---------+------------+--------+ (2)Left connection( LEFT JOIN): The intersection of the two tables plus the remaining data in the left table. Get all records in the left table, even if there is no corresponding matching record in the right table. >select * from student left join teacher on student.teaNum = teacher.teaNum; +-----------+-----------+---------+--------+------------+------------+-----------+---------+------------+--------+ | stuNum | teaNum | stuName | stuAge | stuIn | stuOut | teaNum | teaName | teaIn | teaOut | +-----------+-----------+---------+--------+------------+------------+-----------+---------+------------+--------+ | 020181001 | 120181001 | Zhang 1 | 16 | 2018-09-01 | NULL | 120181001 | A li | 2016-03-01 | NULL | | 020181002 | 120181001 | Li 2 | 16 | 2018-09-01 | 2022-03-04 | 120181001 | A li | 2016-03-01 | NULL | | 020181003 | 120181002 | Li 3 | 16 | 2018-09-01 | 2022-03-01 | 120181002 | Ajiang | 2017-03-01 | NULL | | 020181004 | 120181002 | Wang 4 | 16 | 2018-09-01 | 2022-03-01 | 120181002 | Ajiang | 2017-03-01 | NULL | | 020181005 | 120181003 | Wang 4 | 17 | 2018-09-01 | 2022-03-01 | 120181003 | A Xia | 2016-03-01 | NULL | | 020181006 | 120181003 | Zhang 1 | 17 | 2018-09-01 | 2021-07-01 | 120181003 | A Xia | 2016-03-01 | NULL | +-----------+-----------+---------+--------+------------+------------+-----------+---------+------------+--------+ > select * from teacher left join student on student.teaNum = teacher.teaNum; +-----------+---------+------------+--------+-----------+-----------+---------+--------+------------+------------+ | teaNum | teaName | teaIn | teaOut | stuNum | teaNum | stuName | stuAge | stuIn | stuOut | +-----------+---------+------------+--------+-----------+-----------+---------+--------+------------+------------+ | 120181001 | A li | 2016-03-01 | NULL | 020181001 | 120181001 | Zhang 1 | 16 | 2018-09-01 | NULL | | 120181001 | A li | 2016-03-01 | NULL | 020181002 | 120181001 | Li 2 | 16 | 2018-09-01 | 2022-03-04 | | 120181002 | Ajiang | 2017-03-01 | NULL | 020181003 | 120181002 | Li 3 | 16 | 2018-09-01 | 2022-03-01 | | 120181002 | Ajiang | 2017-03-01 | NULL | 020181004 | 120181002 | Wang 4 | 16 | 2018-09-01 | 2022-03-01 | | 120181003 | A Xia | 2016-03-01 | NULL | 020181005 | 120181003 | Wang 4 | 17 | 2018-09-01 | 2022-03-01 | | 120181003 | A Xia | 2016-03-01 | NULL | 020181006 | 120181003 | Zhang 1 | 17 | 2018-09-01 | 2021-07-01 | | 120181004 | A Liang | 2017-03-01 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | +-----------+---------+------------+--------+-----------+-----------+---------+--------+------------+------------+ # The above is to take student as the left table and teacher as the left table respectively. It can be seen that when the teacher is used as the left table for left connection, the "120181004" row data that is not in the student table is also connected. # And (select * from student, teacher;) Compared with the generated Cartesian product, (120181004, a Liang) in the Cartesian product, there is data for the fields in the student table, while there is no data for the fields in the left connected student table, so the join on is to perform on (filtering) first and then join. (3)Right connection( RIGHT JOIN): Find the intersection of two tables plus the remaining data in the right table. Get all records in the right table, even if there is no corresponding matching record in the left table. > select * from student right join teacher on student.teaNum = teacher.teaNum; +-----------+-----------+---------+--------+------------+------------+-----------+---------+------------+--------+ | stuNum | teaNum | stuName | stuAge | stuIn | stuOut | teaNum | teaName | teaIn | teaOut | +-----------+-----------+---------+--------+------------+------------+-----------+---------+------------+--------+ | 020181001 | 120181001 | Zhang 1 | 16 | 2018-09-01 | NULL | 120181001 | A li | 2016-03-01 | NULL | | 020181002 | 120181001 | Li 2 | 16 | 2018-09-01 | 2022-03-04 | 120181001 | A li | 2016-03-01 | NULL | | 020181003 | 120181002 | Li 3 | 16 | 2018-09-01 | 2022-03-01 | 120181002 | Ajiang | 2017-03-01 | NULL | | 020181004 | 120181002 | Wang 4 | 16 | 2018-09-01 | 2022-03-01 | 120181002 | Ajiang | 2017-03-01 | NULL | | 020181005 | 120181003 | Wang 4 | 17 | 2018-09-01 | 2022-03-01 | 120181003 | A Xia | 2016-03-01 | NULL | | 020181006 | 120181003 | Zhang 1 | 17 | 2018-09-01 | 2021-07-01 | 120181003 | A Xia | 2016-03-01 | NULL | | NULL | NULL | NULL | NULL | NULL | NULL | 120181004 | A Liang | 2017-03-01 | NULL | +-----------+-----------+---------+--------+------------+------------+-----------+---------+------------+--------+ > select * from teacher right join student on student.teaNum = teacher.teaNum; +-----------+---------+------------+--------+-----------+-----------+---------+--------+------------+------------+ | teaNum | teaName | teaIn | teaOut | stuNum | teaNum | stuName | stuAge | stuIn | stuOut | +-----------+---------+------------+--------+-----------+-----------+---------+--------+------------+------------+ | 120181001 | A li | 2016-03-01 | NULL | 020181001 | 120181001 | Zhang 1 | 16 | 2018-09-01 | NULL | | 120181001 | A li | 2016-03-01 | NULL | 020181002 | 120181001 | Li 2 | 16 | 2018-09-01 | 2022-03-04 | | 120181002 | Ajiang | 2017-03-01 | NULL | 020181003 | 120181002 | Li 3 | 16 | 2018-09-01 | 2022-03-01 | | 120181002 | Ajiang | 2017-03-01 | NULL | 020181004 | 120181002 | Wang 4 | 16 | 2018-09-01 | 2022-03-01 | | 120181003 | A Xia | 2016-03-01 | NULL | 020181005 | 120181003 | Wang 4 | 17 | 2018-09-01 | 2022-03-01 | | 120181003 | A Xia | 2016-03-01 | NULL | 020181006 | 120181003 | Zhang 1 | 17 | 2018-09-01 | 2021-07-01 | +-----------+---------+------------+--------+-----------+-----------+---------+--------+------------+------------+ (4)External connection( OUTER JOIN): Find the union of two sets. From the perspective of Cartesian product, it is to pick out from Cartesian product ON Clause condition holds, then add the remaining records in the left table, and finally add the remaining records in the right table. MySQL I won't support it OUTER JOIN，But we can compare the results of left connection and right connection UNION Operation. > select * from student right join teacher on student.teaNum = teacher.teaNum union select * from student left join teacher on student.teaNum = teacher.teaNum; +----------+-----------+---------+--------+------------+------------+-----------+---------+------------+--------+ | stuNum | teaNum | stuName | stuAge | stuIn | stuOut | teaNum | teaName | teaIn | teaOut | +----------+-----------+---------+--------+------------+------------+-----------+---------+------------+--------+ | 20181001 | 120181001 | Zhang 1 | 16 | 2018-09-01 | NULL | 120181001 | A li | 2016-03-01 | NULL | | 20181002 | 120181001 | Li 2 | 16 | 2018-09-01 | 2022-03-04 | 120181001 | A li | 2016-03-01 | NULL | | 20181003 | 120181002 | Li 3 | 16 | 2018-09-01 | 2022-03-01 | 120181002 | Ajiang | 2017-03-01 | NULL | | 20181004 | 120181002 | Wang 4 | 16 | 2018-09-01 | 2022-03-01 | 120181002 | Ajiang | 2017-03-01 | NULL | | 20181005 | 120181003 | Wang 4 | 17 | 2018-09-01 | 2022-03-01 | 120181003 | A Xia | 2016-03-01 | NULL | | 20181006 | 120181003 | Zhang 1 | 17 | 2018-09-01 | 2021-07-01 | 120181003 | A Xia | 2016-03-01 | NULL | | NULL | NULL | NULL | NULL | NULL | NULL | 120181004 | A Liang | 2017-03-01 | NULL | +----------+-----------+---------+--------+------------+------------+-----------+---------+------------+--------+ # There are fields with the same name in the student table and the teacher table, and their meanings are the same. Therefore, the above statements can use using instead of on. as >select * from teacher right join student using(teaNum); (5)Natural connection: mysql Complete the connection process by your own judgment,There is no need to specify connection conditions, mysql The same fields in multiple tables are used as connection conditions. There are two kinds of natural connection: internal natural connection(natural join)And outer natural connection, in which outer natural connection is divided into left outer natural connection(natural left join)Natural connection with right outer wall(rnatural right join). Note: there is no judgment statement for natural connection. Equivalent to the above connection on All the same field names have been filtered. >select * from teacher natural join student; >+-----------+---------+------------+--------+-----------+---------+--------+------------+------------+ | teaNum | teaName | teaIn | teaOut | stuNum | stuName | stuAge | stuIn | stuOut | +-----------+---------+------------+--------+-----------+---------+--------+------------+------------+ | 120181001 | A li | 2016-03-01 | NULL | 020181001 | Zhang 1 | 16 | 2018-09-01 | NULL | | 120181001 | A li | 2016-03-01 | NULL | 020181002 | Li 2 | 16 | 2018-09-01 | 2022-03-04 | | 120181002 | Ajiang | 2017-03-01 | NULL | 020181003 | Li 3 | 16 | 2018-09-01 | 2022-03-01 | | 120181002 | Ajiang | 2017-03-01 | NULL | 020181004 | Wang 4 | 16 | 2018-09-01 | 2022-03-01 | | 120181003 | A Xia | 2016-03-01 | NULL | 020181005 | Wang 4 | 17 | 2018-09-01 | 2022-03-01 | | 120181003 | A Xia | 2016-03-01 | NULL | 020181006 | Zhang 1 | 17 | 2018-09-01 | 2021-07-01 | +-----------+---------+------------+--------+-----------+---------+--------+------------+------------+ #Equivalent to > select * from teacher inner join student using (teanum); (select * from teacher inner join student using)
3,where
SELECT field1, field2,...fieldN FROM table_name1, table_name2... [WHERE condition1 [AND [OR]] condition2..... (1),You can use one or more tables in a query statement, with commas between the tables, Split and use WHERE Statement to set query conditions. (2),Can be in WHERE Clause. (3),have access to AND perhaps OR Specify one or more conditions. (4),WHERE Clauses can also be applied to SQL of DELETE perhaps UPDATE Command. (5),WHERE Clauses are similar to those in programming languages if Conditions, according to MySQL The field value in the table to read the specified data. (6),have access to BINARY Keyword to set WHERE Clause is case sensitive. > select * from student where stuOut < '2022-03-02' and stuName like 'Lee%'; +-----------+-----------+---------+--------+------------+------------+ | stuNum | teaNum | stuName | stuAge | stuIn | stuOut | +-----------+-----------+---------+--------+------------+------------+ | 020181003 | 120181002 | Li 3 | 16 | 2018-09-01 | 2022-03-01 | +-----------+-----------+---------+--------+------------+------------+ # where can be used to filter the size of dates, and like fuzzy matching can realize most conditional filtering.
on and where
where can follow on to constrain the connected tables. where can basically complete the constraint function of on, but the efficiency of the two will be different under different circumstances. Please analyze the specific circumstances.
4,group by
SELECT Field 1, function(column_name) FROM Table name [WHERE conditions] GROUP BY field; # GROUP BY statement groups the result set according to one or more columns. On the grouped columns, we can use aggregate functions such as count, sum and AVG to finally get a grouping summary table. >select stuAge, count(*) from student where stuOut is not null group by stuAge; +--------+----------+ | stuAge | count(*) | +--------+----------+ | 16 | 3 | | 17 | 2 | +--------+----------+ # In the statistics student table, if stuOut is not empty, it will be grouped and counted according to stuAge. > select leftName,group_concat(stuName) from (select left(stuName, 1) as leftName, stuName from student) tmp group by leftName; +----------+-----------------------+ | leftName | group_concat(stuName) | +----------+-----------------------+ | Zhang | Zhang 1,Zhang 1 | | Lee | Li 2,Li 3 | | king | Wang 4,Wang 4 | +----------+-----------------------+ # Count the person's name corresponding to each last name, group_ Concat (field) can display the detailed information of this field after grouping. # Using WITH ROLLUP, the same statistics (SUM,AVG,COUNT...) can be made on the basis of grouped statistics. >select stuOut, count(*), group_concat(stuName) from student group by stuOut with rollup; +------------+----------+-------------------------------+ | stuOut | count(*) | group_concat(stuName) | +------------+----------+-------------------------------+ | NULL | 1 | Zhang 1 | | 2021-07-01 | 1 | Zhang 1 | | 2022-03-01 | 3 | Li 3,Wang 4,Wang 4 | | 2022-03-04 | 1 | Li 2 | | NULL | 6 | Zhang 1,Zhang 1,Li 3,Wang 4,Wang 4,Li 2 | +------------+----------+-------------------------------+
5,having
# The "having" sentence filters various data after grouping, and the "where" sentence filters records before aggregation, That is to say, it acts on group by and having Before the sentence. and having Clause filters group records after aggregation. SELECT Field 1, function(column_name) FROM Table name GROUP BY field HAVING Conditional judgment; >select Chinese,count(*) as number from grade group by Chinese having Chinese>90; +---------+--------+ | Chinese | number | +---------+--------+ | 95 | 2 | | 99 | 1 | | 100 | 2 | +---------+--------+ # Query the number of people with each score when Chinese is greater than 90.
6,order by
SELECT Field 1, Field 2,...field N FROM Table name 1, Table name 2... ORDER BY Field 1 [ASC [DESC][default ASC]], [Field 2...] [ASC [DESC][default ASC]] # ORDER BY clause to set which field and how you want to sort, and then return the search results. # You can use any field as the sorting condition to return the sorted query results. # You can set multiple fields to sort. # You can use ASC or DESC keywords to set whether query results are arranged in ascending or descending order. By default, it is arranged in ascending order. > select English, Chinese, stuName from grade order by English ASC, Chinese DESC; +---------+---------+---------+ | English | Chinese | stuName | +---------+---------+---------+ | 80 | 80 | Zhang 1 | | 90 | 95 | Wang 4 | | 98 | 99 | Li 3 | | 98 | 95 | Wang 4 | | 100 | 100 | Zhang 1 | | 100 | 100 | Li 2 | +---------+---------+---------+ # Sort according to the ascending order of the first keyword English and the descending order of the second keyword Chinese.
7. limit and offset
SELECT Column name 1 FROM _Table name [WHERE Clause] [LIMIT N][OFFSET M] # limit limits the number of N records returned, and offset offsets m records, that is, starting from the M record. > select * from grade where rank != 2 limit 2 offset 1; +------+----------+---------+-----------+---------+---------+ | rank | stuNum | stuName | teaNum | Chinese | English | +------+----------+---------+-----------+---------+---------+ | 1 | 20181002 | Li 2 | 120181001 | 100 | 100 | | 3 | 20181004 | Wang 4 | 120181002 | 95 | 98 | +------+----------+---------+-----------+---------+---------+
8,distinct
SELECT DISTINCT <Field name> FROM <Table name>; # Remove duplicate data > select distinct Chinese,English from grade; +---------+---------+ | Chinese | English | +---------+---------+ | 100 | 100 | | 99 | 98 | | 95 | 98 | | 95 | 90 | | 80 | 80 | +---------+---------+ # View the combination of scores in the student's test scores
9,union
SELECT Field 1, Field 2, ... field n FROM Table name 1 [WHERE conditions] UNION [ALL | DISTINCT] SELECT field_1, field_2, ... field_n FROM Table 2 [WHERE conditions]; # It is used to connect the results of more than two SELECT statements and combine them into a result set. # DISTINCT: optional. Delete duplicate data in the result set. By default, the UNION operator is decorated with DISTINCT, # ALL: optional, return ALL result sets, including duplicate data. # Note: the field requirements before and after UNION are the same. > select stuNum,teaNum from student where stuAge=16 union distinct select stuNum,teaNum from grade where Chinese<100; +----------+-----------+ | stuNum | teaNum | +----------+-----------+ | 20181001 | 120181001 | | 20181002 | 120181001 | | 20181003 | 120181002 | | 20181004 | 120181002 | | 20181005 | 120181003 | | 20181006 | 120181003 | +----------+-----------+ > select stuNum,teaNum from student where stuAge=16 union all select stuNum,teaNum from grade where Chinese<100; +----------+-----------+ | stuNum | teaNum | +----------+-----------+ | 20181001 | 120181001 | | 20181002 | 120181001 | | 20181003 | 120181002 | | 20181004 | 120181002 | | 20181003 | 120181002 | | 20181004 | 120181002 | | 20181005 | 120181003 | | 20181006 | 120181003 | +----------+-----------+
10. LIKE fuzzy matching
SELECT Field 1, Field 2,...field N FROM Table name WHERE Field 1 [NOT] LIKE condition1 [AND [OR]] filed2 = 'somevalue'; # You can use the LIKE clause in the WHERE clause. # You can use the LIKE clause instead of the equal sign =. # LIKE is usually used together with%. It is similar to a meta character search, representing 0~N characters. # With "_" Wildcard query, a "" Represents 1 character. # You can use where in the DELETE or UPDATE command Like clause to specify the condition. > select * from student where stuAge=16 and stuOut like "%03-01"; # Find the record with stuAge=16 and stuOut containing "03-01". +-----------+-----------+---------+--------+------------+------------+ | stuNum | teaNum | stuName | stuAge | stuIn | stuOut | +-----------+-----------+---------+--------+------------+------------+ | 020181003 | 120181002 | Li 3 | 16 | 2018-09-01 | 2022-03-01 | | 020181004 | 120181002 | Wang 4 | 16 | 2018-09-01 | 2022-03-01 | +-----------+-----------+---------+--------+------------+------------+ > select * from student where stuName not like '_4' and teaNum not like '_2018%2'; # Select that the second character in stuName is not "4" and teaNum does not contain '_ Records for 2018% 2 '. +-----------+-----------+---------+--------+------------+------------+ | stuNum | teaNum | stuName | stuAge | stuIn | stuOut | +-----------+-----------+---------+--------+------------+------------+ | 020181001 | 120181001 | Zhang 1 | 16 | 2018-09-01 | NULL | | 020181002 | 120181001 | Li 2 | 16 | 2018-09-01 | 2022-03-04 | | 020181006 | 120181003 | Zhang 1 | 17 | 2018-09-01 | 2021-07-01 | +-----------+-----------+---------+--------+------------+------------+
11. Regular expression matching
and LIKE%Fuzzy matching is similar. The usage is the same. > select * from grade where Chinese regexp '^9'; +------+----------+---------+-----------+---------+---------+ | rank | stuNum | stuName | teaNum | Chinese | English | +------+----------+---------+-----------+---------+---------+ | 2 | 20181003 | Li 3 | 120181002 | 99 | 98 | | 3 | 20181004 | Wang 4 | 120181002 | 95 | 98 | | 4 | 20181005 | Wang 4 | 120181003 | 95 | 90 | +------+----------+---------+-----------+---------+---------+ # Find records with Chinese beginning with 9. > select * from grade where Chinese regexp '0$'; +------+----------+---------+-----------+---------+---------+ | rank | stuNum | stuName | teaNum | Chinese | English | +------+----------+---------+-----------+---------+---------+ | 1 | 20181001 | Zhang 1 | 120181001 | 100 | 100 | | 1 | 20181002 | Li 2 | 120181001 | 100 | 100 | | 5 | 20181006 | Zhang 1 | 120181003 | 80 | 80 | +------+----------+---------+-----------+---------+---------+ # Find records with Chinese ending in "0". > select * from grade where English regexp '8'; +------+----------+---------+-----------+---------+---------+ | rank | stuNum | stuName | teaNum | Chinese | English | +------+----------+---------+-----------+---------+---------+ | 2 | 20181003 | Li 3 | 120181002 | 99 | 98 | | 3 | 20181004 | Wang 4 | 120181002 | 95 | 98 | | 5 | 20181006 | Zhang 1 | 120181003 | 80 | 80 | +------+----------+---------+-----------+---------+---------+ # Find records containing "8" in English. > select * from grade where Chinese regexp '^[1]|5$'; +------+----------+---------+-----------+---------+---------+ | rank | stuNum | stuName | teaNum | Chinese | English | +------+----------+---------+-----------+---------+---------+ | 1 | 20181001 | Zhang 1 | 120181001 | 100 | 100 | | 1 | 20181002 | Li 2 | 120181001 | 100 | 100 | | 3 | 20181004 | Wang 4 | 120181002 | 95 | 98 | | 4 | 20181005 | Wang 4 | 120181003 | 95 | 90 | +------+----------+---------+-----------+---------+---------+ # Find records that start with 1 or end with 5 in Chinese.
| pattern | describe |
|---|---|
| ^ | Matches the start of the input string. If the Multiline property of RegExp object is set, ^ also matches the position after '\ n' or '\ r'. |
| $ | Matches the end of the input string. If the Multiline property of RegExp object is set, $also matches the position before '\ n' or '\ r'. |
| . | Matches any single character except '\ n'. To match any character including '\ n', use a pattern like '[. \ n]'. |
| [...] | Character set. Match any character contained. For example, '[abc]' can match 'a' in 'plain'. |
| [^...] | Negative character set. Matches any characters that are not included. For example, '[^ abc]' can match 'p' in 'plain'. |
| p1|p2|p3 | Match p1 or p2 or p3. For example, 'z|food' can match 'Z' or 'food' (z|f)ood 'matches "zoo" or "food". |
| * | Matches the previous subexpression zero or more times. For example, zo * can match "z" and "zoo"* Equivalent to {0,}. |
| + | Matches the previous subexpression one or more times. For example, 'zo +' can match "zo" and "zoo", but not "z"+ Equivalent to {1,}. |
| {n} | N is a nonnegative integer. Match the determined n times. For example, 'o{2}' cannot match 'o' in "Bob", but it can match two o's in "food". |
| {n,m} | Both M and N are nonnegative integers, where n < = M. Match at least N times and at most m times. |
12. Multiple nested query
1,from You need to give a name to the temporary table > select leftName,group_concat(stuName) from (select left(stuName, 1) as leftName, stuName from student)tmp group by leftName; +----------+-----------------------+ | leftName | group_concat(stuName) | +----------+-----------------------+ | Zhang | Zhang 1,Zhang 1 | | Lee | Li 2,Li 3 | | king | Wang 4,Wang 4 | +----------+-----------------------+ # Count the person's name corresponding to each last name, group_ Concat (field) can display the detailed information of this field after grouping. 2,where Restricted use in >select teaNum, teaName, teaIn from teacher where teaNum not in (select teaNum from student group by teaNum);
Full range of references:
runoob MySQL tutorial
If there is any mistake, please correct it.