Day 23
MySQL
3. Multi table joint query
3.1 what is multi table joint query
The query statements mentioned above are all for one table, but in relational databases, there are connections between tables, so in practical applications, multi table queries are often used. Multi table query is to query two or more tables at the same time.
In MySQL, there are five kinds of multi table queries: cross connection, inner connection, outer connection, grouping query and sub query.
3.2 cross join
3.2.1 Cartesian product
Cross join: there are two types, explicit and implicit, which are generally used to return the Cartesian product of the join table.
Cartesian product refers to the product of two sets X and Y.
For example, there are two sets, A and B, whose values are as follows:
A = {1,2}
B = {3,4,5}
Set A × B and B × The result sets of A are respectively expressed as:
A×B={(1,3), (1,4), (1,5), (2,3), (2,4), (2,5) };
B×A={(3,1), (3,2), (4,1), (4,2), (5,1), (5,2) };
Above A × B and B × The result of A is called the Cartesian product of two sets.
Moreover, we can see from the above results:
- Multiplication of two sets does not satisfy the exchange rate, that is, a × B≠B × A.
- The Cartesian product of A set and B set is the number of elements of A set × Number of elements in B set.
The algorithm of multi table query is the Cartesian product mentioned above. The connection between tables can be regarded as multiplication. In practical application, we should avoid using Cartesian product, because Cartesian product is prone to a large number of unreasonable data, which is easy to lead to repeated and chaotic query results.
3.2.2 cross connection
The syntax format of cross connection is as follows:
SELECT <Field name> FROM <Table 1> CROSS JOIN <Table 2> [WHERE clause];
or
SELECT <Field name> FROM <Table 1>, <Table 2> [WHERE clause];
The syntax is as follows:
- Field name: the name of the field to be queried.
- < Table 1 > < table 2>: table names that need to be cross connected.
- WHERE clause: used to set query conditions for cross joins.
Note: when multiple tables are cross connected, you can use CROSS JOIN or after FROM. The return results of the above two grammars are the same, but the first grammar is the official recommended standard writing.
When there is no relationship between the connected tables, we will omit the WHERE clause. At this time, the returned result is the Cartesian product of the two tables, and the number of returned results is the multiplication of the data rows of the two tables. It should be noted that if each table has 1000 rows, the number of returned results will be 1000 × 1000 = 1000000 rows, and the amount of data is very huge.
Cross connect can query two or more tables. For better understanding, we will explain the cross connect query of two tables.
Example 1
Query the student information table and subject information table, and get a Cartesian product.
In order to facilitate the observation of the running results of the cross connection between the student information table and the chart of subjects, we first query the data of the two tables respectively, and then conduct the cross connection query.
1) Query TB_ students_ The data, SQL statements and running results in the info table are as follows:
mysql> create table tb_students_info (id int not null primary key auto_increment,name varchar(50) not null,age tinyint,sex varchar(4),height float,course_id int) default character set utf8 collate utf8_general_ci;
Query OK, 0 rows affected (0.00 sec)
mysql> insert tb_students_info(name,age,sex,height,course_id) values ('dany',25,'male',160,1),('green',23,'male',158,2),('henry',23,'female',185,1),('jane',22,'male',162,3),('jim',24,'female',175,4),('john',21,'female',172,4),('lily',22,'male',165,4),('susan',23,'male',170,5),('thomas',22,'female',178,5),('tom',23,'female',165,5);
Query OK, 10 rows affected (0.00 sec)
Records: 10 Duplicates: 0 Warnings: 0
mysql> select * from tb_students_info;
+----+--------+------+------+--------+-----------+
| id | name | age | sex | height | course_id |
+----+--------+------+------+--------+-----------+
| 1 | dany | 25 | male | 160 | 1 |
| 2 | green | 23 | male | 158 | 2 |
| 3 | henry | 23 | female | 185 | 1 |
| 4 | jane | 22 | male | 162 | 3 |
| 5 | jim | 24 | female | 175 | 4 |
| 6 | john | 21 | female | 172 | 4 |
| 7 | lily | 22 | male | 165 | 4 |
| 8 | susan | 23 | male | 170 | 5 |
| 9 | thomas | 22 | female | 178 | 5 |
| 10 | tom | 23 | female | 165 | 5 |
+----+--------+------+------+--------+-----------+
10 rows in set (0.00 sec)
2) Query TB_ The data, SQL statements and running results in the course table are as follows:
mysql> create table tb_course (id int not null primary key auto_increment,course_name varchar(100));
mysql> insert tb_course (course_name) values ('java'),('mysql'),('python'),('go'),('c++');
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select * from tb_course;
+----+-------------+
| id | course_name |
+----+-------------+
| 1 | java |
| 2 | mysql |
| 3 | python |
| 4 | go |
| 5 | c++ |
+----+-------------+
5 rows in set (0.00 sec)
3) Use CROSS JOIN to query the Cartesian product in the two tables. The SQL statement and running results are as follows:
mysql> SELECT * FROM tb_course CROSS JOIN tb_students_info; +----+-------------+----+--------+------+------+--------+-----------+ | id | course_name | id | name | age | sex | height | course_id | +----+-------------+----+--------+------+------+--------+-----------+ | 1 | java | 1 | dany | 25 | male | 160 | 1 | | 2 | mysql | 1 | dany | 25 | male | 160 | 1 | | 3 | python | 1 | dany | 25 | male | 160 | 1 | | 4 | go | 1 | dany | 25 | male | 160 | 1 | | 5 | c++ | 1 | dany | 25 | male | 160 | 1 | | 1 | java | 2 | green | 23 | male | 158 | 2 | | 2 | mysql | 2 | green | 23 | male | 158 | 2 | | 3 | python | 2 | green | 23 | male | 158 | 2 | | 4 | go | 2 | green | 23 | male | 158 | 2 | | 5 | c++ | 2 | green | 23 | male | 158 | 2 | | 1 | java | 3 | henry | 23 | female | 185 | 1 | | 2 | mysql | 3 | henry | 23 | female | 185 | 1 | | 3 | python | 3 | henry | 23 | female | 185 | 1 | | 4 | go | 3 | henry | 23 | female | 185 | 1 | | 5 | c++ | 3 | henry | 23 | female | 185 | 1 | | 1 | java | 4 | jane | 22 | male | 162 | 3 | | 2 | mysql | 4 | jane | 22 | male | 162 | 3 | | 3 | python | 4 | jane | 22 | male | 162 | 3 | | 4 | go | 4 | jane | 22 | male | 162 | 3 | | 5 | c++ | 4 | jane | 22 | male | 162 | 3 | | 1 | java | 5 | jim | 24 | female | 175 | 4 | | 2 | mysql | 5 | jim | 24 | female | 175 | 4 | | 3 | python | 5 | jim | 24 | female | 175 | 4 | | 4 | go | 5 | jim | 24 | female | 175 | 4 | | 5 | c++ | 5 | jim | 24 | female | 175 | 4 | | 1 | java | 6 | john | 21 | female | 172 | 4 | | 2 | mysql | 6 | john | 21 | female | 172 | 4 | | 3 | python | 6 | john | 21 | female | 172 | 4 | | 4 | go | 6 | john | 21 | female | 172 | 4 | | 5 | c++ | 6 | john | 21 | female | 172 | 4 | | 1 | java | 7 | lily | 22 | male | 165 | 4 | | 2 | mysql | 7 | lily | 22 | male | 165 | 4 | | 3 | python | 7 | lily | 22 | male | 165 | 4 | | 4 | go | 7 | lily | 22 | male | 165 | 4 | | 5 | c++ | 7 | lily | 22 | male | 165 | 4 | | 1 | java | 8 | susan | 23 | male | 170 | 5 | | 2 | mysql | 8 | susan | 23 | male | 170 | 5 | | 3 | python | 8 | susan | 23 | male | 170 | 5 | | 4 | go | 8 | susan | 23 | male | 170 | 5 | | 5 | c++ | 8 | susan | 23 | male | 170 | 5 | | 1 | java | 9 | thomas | 22 | female | 178 | 5 | | 2 | mysql | 9 | thomas | 22 | female | 178 | 5 | | 3 | python | 9 | thomas | 22 | female | 178 | 5 | | 4 | go | 9 | thomas | 22 | female | 178 | 5 | | 5 | c++ | 9 | thomas | 22 | female | 178 | 5 | | 1 | java | 10 | tom | 23 | female | 165 | 5 | | 2 | mysql | 10 | tom | 23 | female | 165 | 5 | | 3 | python | 10 | tom | 23 | female | 165 | 5 | | 4 | go | 10 | tom | 23 | female | 165 | 5 | | 5 | c++ | 10 | tom | 23 | female | 165 | 5 | +----+-------------+----+--------+------+------+--------+-----------+ 50 rows in set (0.00 sec)
4. Use cross join to query the names of the two tables and the course names
mysql> select s.name,c.course_name from tb_students_info s cross join tb_cou rse c where s.course_id = c.id; +--------+-------------+ | name | course_name | +--------+-------------+ | dany | java | | green | mysql | | henry | java | | jane | python | | jim | go | | john | go | | lily | go | | susan | c++ | | thomas | c++ | | tom | c++ | +--------+-------------+ 10 rows in set (0.00 sec)
It can be seen from the operation results that tb_course and TB_ students_ After the cross connection query of info table, 50 records were returned. It is conceivable that when there are many data in the table, the running results will be very long, and the running results will not be of much significance. Therefore, this method of multi table query through cross connection is not commonly used, and we should try to avoid this kind of query.
Example 2
Query TB_ id field and TB in course table_ students_ Course in info table_ The contents of the id field are the same, and the SQL statement and running results are as follows:
mysql> select * from tb_course c cross join tb_students_info s where s.course_id = c.id; +----+-------------+----+--------+------+------+--------+-----------+ | id | course_name | id | name | age | sex | height | course_id | +----+-------------+----+--------+------+------+--------+-----------+ | 1 | java | 1 | dany | 25 | male | 160 | 1 | | 2 | mysql | 2 | green | 23 | male | 158 | 2 | | 1 | java | 3 | henry | 23 | female | 185 | 1 | | 3 | python | 4 | jane | 22 | male | 162 | 3 | | 4 | go | 5 | jim | 24 | female | 175 | 4 | | 4 | go | 6 | john | 21 | female | 172 | 4 | | 4 | go | 7 | lily | 22 | male | 165 | 4 | | 5 | c++ | 8 | susan | 23 | male | 170 | 5 | | 5 | c++ | 9 | thomas | 22 | female | 178 | 5 | | 5 | c++ | 10 | tom | 23 | female | 165 | 5 | +----+-------------+----+--------+------+------+--------+-----------+ 10 rows in set (0.01 sec)
Note: when querying multiple tables, you should specify which table the field comes from after the SELECT statement. Therefore, in multi table queries, the writing method after the SELECT statement is the table name Column name. In addition, if the table name is very long, you can also set an alias for the table, so that you can write the alias and column name of the table directly after the SELECT statement.
If the WHERE clause is used in cross join, MySQL will generate the Cartesian product of two tables, and then select the records that meet the WHERE condition. Therefore, when the number of tables is large, the cross connection will be very, very slow. Cross connect is generally not recommended.
In MySQL, multi table queries generally use inner joins and outer joins, which are more efficient than cross joins.
3.3 internal connection
**INNER JOIN * * mainly removes the cross connection of some data rows in the query result by setting the connection conditions. Simply put, it is to use conditional expressions to eliminate some data rows of cross connections.
INNER JOIN uses the INNER JOIN keyword to connect two tables, and uses the ON clause to set the connection condition. If there is no connection condition, INNER JOIN and CROSS JOIN are syntactically equivalent and can be interchanged.
The syntax format of inner connection is as follows:
SELECT <Field name> FROM <Table 1> INNER JOIN <Table 2> [ON clause];
The syntax is described below.
- Field name: the name of the field to be queried.
- < Table 1 > < table 2>: table names that require internal connections.
- INNER JOIN: the INNER keyword can be omitted from the INNER JOIN, and only the keyword JOIN is used.
- ON clause: used to set the connection condition of inner connection.
INNER JOIN can also use the WHERE clause to specify connection conditions, but the INNER JOIN... ON syntax is the official standard, and the WHERE clause will affect the performance of queries at some times.
For multiple intra table connections, you can use INNER JOIN or JOIN continuously after FROM.
Inner join can query two or more tables. For a better understanding, only the join query of two tables will be explained for the time being.
Example 1
In tb_students_info table and TB_ Between the course tables, use the inner connection to query the student name and the corresponding course name. The SQL statement and running results are as follows.
mysql> select s.name,c.course_name from tb_students_info s inner join tb_course c on s.course_id = c.id; +--------+-------------+ | name | course_name | +--------+-------------+ | dany | java | | green | mysql | | henry | java | | jane | python | | jim | go | | john | go | | lily | go | | susan | c++ | | thomas | c++ | | tom | c++ | +--------+-------------+ 10 rows in set (0.00 sec)
Example 2
In tb_students_info table and TB_ Between the course tables, use the inner connection to query the student's name, height and learning course. The SQL statement and running results are as follows.
mysql> select s.name,s.height,c.course_name from tb_students_info s inner join tb_course c on s.course_id = c.id; +--------+--------+-------------+ | name | height | course_name | +--------+--------+-------------+ | dany | 160 | java | | green | 158 | mysql | | henry | 185 | java | | jane | 162 | python | | jim | 175 | go | | john | 172 | go | | lily | 165 | go | | susan | 170 | c++ | | thomas | 178 | c++ | | tom | 165 | c++ | +--------+--------+-------------+ 10 rows in set (0.00 sec)
In the query statement here, the relationship between the two tables is specified by INNER JOIN, and the connection conditions are given by using the ON clause.
Note: when querying multiple tables, you should specify which table the field comes from after the SELECT statement. Therefore, in multi table queries, the writing method after the SELECT statement is the table name Column name. In addition, if the table name is very long, you can also set an alias for the table, so that you can write the alias and column name of the table directly after the SELECT statement.
3.4 external connection
The query results of the inner connection are all records that meet the connection conditions, while the outer connection will first divide the connected table into the base table and the reference table, and then return the records that meet and do not meet the conditions based on the base table.
External connection can be divided into two types: left external connection and right external connection. The left external connection and right external connection are introduced respectively according to examples.
3.4.1 left connection
LEFT OUTER JOIN, also known as left join, uses the LEFT OUTER JOIN keyword to connect two tables, and uses the ON clause to set the connection conditions.
The syntax format of left join is as follows:
SELECT <Field name> FROM <Table 1> LEFT OUTER JOIN <Table 2> <ON clause>;
The syntax is as follows:
- Field name: the name of the field to be queried.
- < Table 1 > < table 2>: table names that require left connections.
- LEFT OUTER JOIN: the OUTER keyword can be omitted from the left connection, and only the keyword LEFT JOIN is used.
- ON clause: used to set the connection condition of left connection, which cannot be omitted.
In the above syntax, "Table 1" is the base table and "Table 2" is the reference table. When querying the left connection, you can query all the records in "Table 1" and the records matching the connection conditions in "Table 2". If a row of "Table 1" does not match in "Table 2", the field values of "Table 2" are all NULL in the return result.
Example 1
Before left connection query, let's check TB first_ Course and tb_students_info data in two tables. The SQL statement and running results are as follows:
mysql> select * from tb_course; +----+-------------+ | id | course_name | +----+-------------+ | 1 | java | | 2 | mysql | | 3 | python | | 4 | go | | 5 | c++ | | 6 | web | +----+-------------+ 6 rows in set (0.00 sec) mysql> select * from tb_students_info; +----+----------+------+------+--------+-----------+ | id | name | age | sex | height | course_id | +----+----------+------+------+--------+-----------+ | 1 | dany | 25 | male | 160 | 1 | | 2 | green | 23 | male | 158 | 2 | | 3 | henry | 23 | female | 185 | 1 | | 4 | jane | 22 | male | 162 | 3 | | 5 | jim | 24 | female | 175 | 4 | | 6 | john | 21 | female | 172 | 4 | | 7 | lily | 22 | male | 165 | 4 | | 8 | susan | 23 | male | 170 | 5 | | 9 | thomas | 22 | female | 178 | 5 | | 10 | tom | 23 | female | 165 | 5 | | 11 | zhangsan | 20 | male | 180 | 8 | +----+----------+------+------+--------+-----------+ 11 rows in set (0.00 sec)
In tb_students_info table and TB_ Query the names of all students and the corresponding course names in the course table, including students without courses. The SQL statements and running results are as follows:
mysql> select s.name,c.course_name from tb_students_info s left outer join tb_course c on s.course_id = c.id; +----------+-------------+ | name | course_name | +----------+-------------+ | dany | java | | green | mysql | | henry | java | | jane | python | | jim | go | | john | go | | lily | go | | susan | c++ | | thomas | c++ | | tom | c++ | | zhangsan | NULL | +----------+-------------+ 11 rows in set (0.00 sec)
It can be seen that the running result shows 11 records. The students whose name is LiMing have no courses at present, because the corresponding TB_ There is no course information of the student in the course table, so only TB is taken out of this record_ students_ The corresponding value in the info table, and from TB_ The value retrieved from the course table is NULL.
3.4.2 right connection
Right outer connection is also called right connection, which is the reverse connection of left connection. Use the RIGHT OUTER JOIN keyword to join two tables, and use the ON clause to set the join condition.
The syntax format of right connection is as follows:
SELECT <Field name> FROM <Table 1> RIGHT OUTER JOIN <Table 2> <ON clause>;
The syntax is as follows:
- Field name: the name of the field to be queried.
- < Table 1 > < table 2>: table names that require right connections.
- RIGHT OUTER JOIN: you can omit the OUTER keyword in the right connection and only use the keyword RIGHT JOIN.
- ON clause: used to set the connection condition of right connection, which cannot be omitted.
Contrary to the left connection, the right connection takes "Table 2" as the base table and "Table 1" as the reference table. When querying the right connection, you can query all the records in "Table 2" and the records matching the connection conditions in "Table 1". If a row of "Table 2" does not match in "Table 1", the field values of "Table 1" are all NULL in the return result.
Example 2
In tb_students_info table and TB_ Query all courses in the course table, including those without students. The SQL statements and running results are as follows:
mysql> select s.name,c.course_name from tb_students_info s right outer join tb_course c on s.course_id = c.id; +--------+-------------+ | name | course_name | +--------+-------------+ | dany | java | | green | mysql | | henry | java | | jane | python | | jim | go | | john | go | | lily | go | | susan | c++ | | thomas | c++ | | tom | c++ | | NULL | web | +--------+-------------+ 11 rows in set (0.01 sec)
You can see that the result shows 11 records. There are no students in the course named HTML at present, because the corresponding TB_ students_ There is no information about the student in the info table, so only TB is taken out of this record_ The corresponding value in the course table, and from TB_ students_ The value retrieved from the info table is NULL.
When multiple tables are connected left / right, you can use LEFT/RIGHT OUTER JOIN or LEFT/RIGHT JOIN after the ON clause.
When using external connection query, you must distinguish the results to be queried, whether you need to display all records of the left table or all records of the right table, and then select the corresponding left connection and right connection.
3.5 group query
In MySQL, the GROUP BY keyword can group query results according to one or more fields.
The syntax format of using the GROUP BY keyword is as follows:
GROUP BY <Field name>
Where, "field name" refers to the name of the field to be grouped, and multiple fields are separated by commas.
3.5.1 GROUP BY used alone
When using the GROUP BY keyword alone, the query result will only display the first record of each group.
According to TB_ students_ The sex field in the info table is queried in groups. The SQL statement and running results are as follows:
mysql> select name,sex from tb_students_info group by sex; +-------+------+ | name | sex | +-------+------+ | henry | female | | dany | male | +-------+------+ 2 rows in set (0.00 sec)
Only two records are displayed in the result, and the values of the sex field of these two records are "female" and "male" respectively.
According to TB_ students_ The age field in the info table is used for grouping query. The SQL statement and running results are as follows:
mysql> select name,age from tb_students_info group by age; +----------+------+ | name | age | +----------+------+ | zhangsan | 20 | | john | 21 | | jane | 22 | | green | 23 | | jim | 24 | | dany | 25 | +----------+------+ 6 rows in set (0.00 sec)
3.5.2 GROUP BY and GROUP_CONCAT()
The GROUP BY keyword can be used with group_ Use with the concat() function. GROUP_ The concat() function will display the field values of each group.
According to TB_ students_ The sex field in the info table is used for group query, and group is used_ The concat() function displays the value of the name field of each group. The SQL statement and running results are as follows:
mysql> select sex, group_concat(name) from tb_students_info group by sex; +------+-------------------------------------+ | sex | group_concat(name) | +------+-------------------------------------+ | female | henry,jim,john,thomas,tom | | male | dany,green,jane,lily,susan,zhangsan | +------+-------------------------------------+ 2 rows in set (0.00 sec)
It can be seen from the results that the query results are divided into two groups, one group with the value of "female" in the sex field and one group with the value of "male", and the names of students in each group are displayed.
According to TB_ students_ The age and sex fields in the info table are used for grouping queries. The SQL statement and running results are as follows:
mysql> select age,sex,group_concat(name) from tb_students_info group by age,sex; +------+------+--------------------+ | age | sex | group_concat(name) | +------+------+--------------------+ | 20 | male | zhangsan | | 21 | female | john | | 22 | female | thomas | | 22 | male | jane,lily | | 23 | female | henry,tom | | 23 | male | green,susan | | 24 | female | jim | | 25 | male | dany | +------+------+--------------------+ 8 rows in set (0.01 sec)
In the above example, in the process of grouping, we first group the records according to the age field. When the age field values are equal, we then group the records with equal age field values according to the sex field.
When multiple fields are grouped for query, the first field will be grouped first. If the first field has the same value, MySQL will group according to the second field. If the data in the first field is unique, MySQL will no longer group the second field.
give an example:
mysql> select group_concat(s.age),group_concat(c.course_name) from tb_students_info s right outer join tb_course c on s.course_id = c.id; +-------------------------------+-------------------------------------------------+ | group_concat(s.age) | group_concat(c.course_name) | +-------------------------------+-------------------------------------------------+ | 25,23,23,22,24,21,22,23,22,23 | java,mysql,java,python,go,go,go,c++,c++,c++,web | +-------------------------------+-------------------------------------------------+ 1 row in set (0.00 sec)
3.5.3 GROUP BY and aggregate function
In data statistics, the GROUP BY keyword is often used together with aggregate functions.
Aggregate functions include COUNT(), SUM(), AVG(), MAX() and MIN(). Among them, COUNT() is used to count the number of records; SUM() is used to calculate the sum of field values; AVG() is used to calculate the average value of the field value; MAX() is used to query the maximum value of the field; MIN() is used to query the minimum value of the field.
According to TB_ students_ The sex field of the info table is queried in groups, and the COUNT() function is used to calculate the number of records in each group. The SQL statement and running results are as follows:
mysql> select sex,count(sex) from tb_students_info group by sex; +------+------------+ | sex | count(sex) | +------+------------+ | female | 5 | | male | 6 | +------+------------+ 2 rows in set (0.00 sec)
The results show that the records with sex field value of "female" are a group, with 5 records; The records with sex field value of "male" are a group with 5 records.
avg
mysql> select sex,avg(height),age from tb_students_info group by age; +------+--------------------+------+ | sex | avg(height) | age | +------+--------------------+------+ | male | 180 | 20 | | female | 172 | 21 | | male | 168.33333333333334 | 22 | | male | 169.5 | 23 | | female | 175 | 24 | | male | 160 | 25 | +------+--------------------+------+ 6 rows in set (0.00 sec)
sum
mysql> select sum(age),group_concat(name),id from tb_students_info; +----------+---------------------------------------------------------------+----+ | sum(age) | group_concat(name) | id | +----------+---------------------------------------------------------------+----+ | 248 | dany,green,henry,jane,jim,john,lily,susan,thomas,tom,zhangsan | 1 | +----------+---------------------------------------------------------------+----+ 1 row in set (0.00 sec)
max
mysql> select max(age),group_concat(name),id from tb_students_info; +----------+---------------------------------------------------------------+----+ | max(age) | group_concat(name) | id | +----------+---------------------------------------------------------------+----+ | 25 | dany,green,henry,jane,jim,john,lily,susan,thomas,tom,zhangsan | 1 | +----------+---------------------------------------------------------------+----+ 1 row in set (0.01 sec)
min
mysql> select s.name,min(s.age),c.course_name from tb_students_info s inner join tb_course c on s.course_id = c.id; +------+------------+-------------+ | name | min(s.age) | course_name | +------+------------+-------------+ | dany | 21 | java | +------+------------+-------------+ 1 row in set (0.00 sec)
3.5.4 GROUP BY and WITH ROLLUP
With poll keyword is used to add a record at the end of all records. This record is the sum of all the above records, that is, to count the number of records.
According to TB_ students_ The sex field in the info table is queried in groups, and the total of records is displayed WITH ROLLUP:
mysql> select sex,group_concat(name) from tb_students_info group by sex with rollup; +------+---------------------------------------------------------------+ | sex | group_concat(name) | +------+---------------------------------------------------------------+ | female | henry,jim,john,thomas,tom | | male | dany,green,jane,lily,susan,zhangsan | | NULL | henry,jim,john,thomas,tom,dany,green,jane,lily,susan,zhangsan | +------+---------------------------------------------------------------+ 3 rows in set (0.00 sec)
The query result shows that GROUP_CONCAT(name) shows the value of the name field for each group. Meanwhile, the group of the last record_ The value of the concat (name) field is just the sum of the above grouped name field values.
3.6 sub query
Subquery is a commonly used query method in MySQL. Multi table query can be realized through subquery. Subquery refers to nesting one query statement in another query statement. Subqueries can be used in SELECT, UPDATE, and DELETE statements, and can be nested at multiple levels. In actual development, subqueries often appear in the WHERE clause.
The syntax format of subquery in WHERE is as follows:
WHERE <expression> <Operator> (Subquery)
Among them, operators can be comparison operators and keywords such as IN, NOT IN, EXISTS, NOT EXISTS, etc.
1)IN | NOT IN
When the expression is equal to a value in the result set returned by the subquery, it returns TRUE; otherwise, it returns FALSE; If the keyword NOT is used, the return value is exactly the opposite.
2)EXISTS | NOT EXISTS
It is used to judge whether the result set of the sub query is empty. If the result set of the sub query is NOT empty, it returns TRUE, otherwise it returns FALSE; If the keyword NOT is used, the returned value is exactly the opposite.
Example 1
Use subquery in tb_students_info table and TB_ Query the names of students studying Java courses in the course table, and the SQL statements and running results are as follows:
mysql> select name from tb_students_info where course_id in (select id from tb_course where course_name = 'java'); +-------+ | name | +-------+ | dany | | henry | +-------+ 2 rows in set (0.00 sec)
The results show that only Danny and Henry are learning Java courses. The above query process can also be divided into the following two steps, and the implementation effect is the same.
First, execute internal query separately to find TB_ The course in the course table is the id of Java, and the SQL statement and running results are as follows:
mysql> SELECT id FROM tb_course
-> WHERE course_name = 'Java';
+----+
| id |
+----+
| 1 |
+----+
1 row in set (0.00 sec)
You can see that the value of the qualified id field is 1.
Then execute the outer layer query in TB_ students_ Query course in info table_ Student name with ID equal to 1. The SQL statement and running results are as follows:
mysql> SELECT name FROM tb_students_info
-> WHERE course_id IN (1);
+-------+
| name |
+-------+
| Dany |
| Henry |
+-------+
2 rows in set (0.00 sec)
Traditionally, the outer SELECT query is called the parent query, and the query embedded in parentheses is called the sub query (the sub query must be placed in parentheses). When MySQL processes the SELECT statement in the above example, the execution process is: execute the sub query first, and then the parent query.
Example 2
Similar to example 1, use the NOT IN keyword in the SELECT statement to query the names of students who have not studied Java courses. The SQL statement and running results are as follows:
mysql> select name from tb_students_info where course_id not in (select id from tb_course where course_name = 'java'); +----------+ | name | +----------+ | green | | jane | | jim | | john | | lily | | susan | | thomas | | tom | | zhangsan | +----------+ 9 rows in set (0.00 sec)
It can be seen that the running result is just opposite to the above example. Students other than Danny and Henry did not learn Java courses.
Example 3
Use the = operator in tb_course table and TB_ students_ The names of all students studying Python courses are queried in the info table. The SQL statements and running results are as follows:
mysql> select name from tb_students_info where course_id = (select id from tb_course where course_name = 'python'); +------+ | name | +------+ | jane | +------+ 1 row in set (0.00 sec)
give an example:
Use the = operator in tb_course table and TB_ students_ The names, ages, SQL statements and running results of all students studying c++ courses are queried in the info table as follows:
mysql> select name,age from tb_students_info where course_id = (select id from tb_course where course_name = 'c++'); +--------+------+ | name | age | +--------+------+ | susan | 23 | | thomas | 22 | | tom | 23 | +--------+------+ 3 rows in set (0.00 sec)
Example 4
Using the < > operator, in tb_course table and TB_ students_ The names of students who did not study Python courses were found in the info table. The SQL statements and running results are as follows:
mysql> select name from tb_students_info where course_id <> (select id from tb_course where course_name = 'python'); +----------+ | name | +----------+ | dany | | green | | henry | | jim | | john | | lily | | susan | | thomas | | tom | | zhangsan | +----------+ 10 rows in set (0.00 sec)
It can be seen that the running result is just opposite to that of example 3. Students other than Jane did not learn Python courses.
Example 5
Query TB_ Whether there is a course with id=1 in the course table. If so, query TB_ students_ The records, SQL statements and running results in the info table are as follows:
mysql> select * from tb_students_info where exists(select course_name from tb_course where id=1); +----+----------+------+------+--------+-----------+ | id | name | age | sex | height | course_id | +----+----------+------+------+--------+-----------+ | 1 | dany | 25 | male | 160 | 1 | | 2 | green | 23 | male | 158 | 2 | | 3 | henry | 23 | female | 185 | 1 | | 4 | jane | 22 | male | 162 | 3 | | 5 | jim | 24 | female | 175 | 4 | | 6 | john | 21 | female | 172 | 4 | | 7 | lily | 22 | male | 165 | 4 | | 8 | susan | 23 | male | 170 | 5 | | 9 | thomas | 22 | female | 178 | 5 | | 10 | tom | 23 | female | 165 | 5 | | 11 | zhangsan | 20 | male | 180 | 8 | +----+----------+------+------+--------+-----------+ 11 rows in set (0.00 sec)
As can be seen from the results, TB_ There is a record with id=1 in the course table, so the EXISTS expression returns TRUE. After receiving TRUE, the outer query statement evaluates the table tb_students_info to query and return all records.
The EXISTS keyword can be used with other query conditions. Conditional expressions are connected with the EXISTS keyword by AND and OR.
not exists
mysql> select * from tb_students_info where not exists(select course_name from tb_course where id=1); Empty set (0.00 sec)
Example 6
Query TB_ Whether there is a course with id=1 in the course table. If so, query TB_ students_ For records with an age field greater than 24 in the info table, the SQL statement and running results are as follows:
mysql> select * from tb_students_info where age>24 and exists(select course_name fr om tb_course where id=1); +----+------+------+------+--------+-----------+ | id | name | age | sex | height | course_id | +----+------+------+------+--------+-----------+ | 1 | dany | 25 | male | 160 | 1 | +----+------+------+------+--------+-----------+ 1 row in set (0.00 sec)
The results show that from TB_ students_ A record is found in the info table. The age field of this record is 25. Inner query statement from TB_ When a record is queried in the course table, it returns TRUE. The outer query statement starts the query. According to the query criteria, from TB_ students_ Query records with an age greater than 24 in the info table.
The function of sub query can also be completed through table connection, but sub query will make SQL statements easier to read and write.
exists**
mysql> select * from tb_students_info where not exists(select course_name from tb_course where id=1); Empty set (0.00 sec)
Example 6
Query TB_ Whether there is a course with id=1 in the course table. If so, query TB_ students_ For records with an age field greater than 24 in the info table, the SQL statement and running results are as follows:
mysql> select * from tb_students_info where age>24 and exists(select course_name fr om tb_course where id=1); +----+------+------+------+--------+-----------+ | id | name | age | sex | height | course_id | +----+------+------+------+--------+-----------+ | 1 | dany | 25 | male | 160 | 1 | +----+------+------+------+--------+-----------+ 1 row in set (0.00 sec)
The results show that from TB_ students_ A record is found in the info table. The age field of this record is 25. Inner query statement from TB_ When a record is queried in the course table, it returns TRUE. The outer query statement starts the query. According to the query criteria, from TB_ students_ Query records with an age greater than 24 in the info table.
The function of sub query can also be completed through table connection, but sub query will make SQL statements easier to read and write.
Generally speaking, table joins (inner joins and outer joins, etc.) can be replaced by subqueries, but the reverse is not necessarily true. Some subqueries cannot be replaced by table joins. Subqueries are flexible, convenient and diverse, which are suitable for filtering queries, while table joins are more suitable for viewing the data of connected tables.