#mysql进阶
文章目录
多表联合查询
创建表格
mysql> create database tnt;
Query OK, 1 row affected (0.02 sec)
mysql> use tnt;
Database changed
mysql> create table tb_students_info(id int not null primary key auto_increment,name varchar(50),age tinyint,sex varchar(4),height float,course_id int);
Query OK, 0 rows affected (0.04 sec)
查看表结构
mysql> desc tb_students_info;
+-----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(50) | YES | | NULL | |
| age | tinyint(4) | YES | | NULL | |
| sex | varchar(4) | YES | | NULL | |
| height | float | YES | | NULL | |
| course_id | int(11) | YES | | NULL | |
+-----------+-------------+------+-----+---------+----------------+
6 rows in set (0.01 sec)
mysql> insert tb_students_info (name,age,sex,height,course_id) values('Dany',25,'W',160,1),('Green',23,'W',158,2),('Henry',23,'M',185,1),('Jane',22,'W',62,3),('Jim',24,'M',175,2),('John',21,'M',172,4),('Lily',22,'W',165,4),('Susan',23,'W',170,5),('Thomas',22,'M',178,5),('Tom',23,'M',165,5);
Query OK, 10 rows affected (0.01 sec)
Records: 10 Duplicates: 0 Warnings: 0
mysql> select * from tb_students_info;
+----+--------+------+------+--------+-----------+
| id | name | age | sex | height | course_id |
+----+--------+------+------+--------+-----------+
| 1 | Dany | 25 | W | 160 | 1 |
| 2 | Green | 23 | W | 158 | 2 |
| 3 | Henry | 23 | M | 185 | 1 |
| 4 | Jane | 22 | W | 62 | 3 |
| 5 | Jim | 24 | M | 175 | 2 |
| 6 | John | 21 | M | 172 | 4 |
| 7 | Lily | 22 | W | 165 | 4 |
| 8 | Susan | 23 | W | 170 | 5 |
| 9 | Thomas | 22 | M | 178 | 5 |
| 10 | Tom | 23 | M | 165 | 5 |
+----+--------+------+------+--------+-----------+
10 rows in set (0.01 sec)
创建第二张表格
mysql> create table tb_course(id int not null primary key auto_increment,course_name varchar(50));
Query OK, 0 rows affected (0.02 sec)
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)
###什么是多表联合查询
前面所讲的查询语句都是针对一个表的,但是在关系型数据库中,表与表之间是有联系的,所以在实际应用中,经常使用多表查询。多表查询就是同时查询两个或两个以上的表。
在 MySQL 中,多表查询主要有交叉连接、内连接、外连接、分组查询与子查询等5种
###交叉连接
####笛卡尔积
交叉连接(CROSS JOIN):有两种,显式的和隐式的2种,一般用来返回连接表的笛卡尔积。
笛卡尔积(笛卡尔积)是指两个集合 X 和 Y 的乘积。
例如,有 A 和 B 两个集合,它们的值如下:
A={1,2}
B={3,4,5}
集合 A×B 和 B×A 的结果集分别表示为:
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) };
以上 A×B 和 B×A 的结果就叫做两个集合的笛卡尔积。
mysql> select * from tb_course,tb_students_info;
+----+-------------+----+--------+------+------+--------+-----------+
| id | course_name | id | name | age | sex | height | course_id |
+----+-------------+----+--------+------+------+--------+-----------+
| 1 | Java | 1 | Dany | 25 | W | 160 | 1 |
| 2 | Mysql | 1 | Dany | 25 | W | 160 | 1 |
| 3 | Python | 1 | Dany | 25 | W | 160 | 1 |
| 4 | Go | 1 | Dany | 25 | W | 160 | 1 |
| 5 | C++ | 1 | Dany | 25 | W | 160 | 1 |
| 1 | Java | 2 | Green | 23 | W | 158 | 2 |
| 2 | Mysql | 2 | Green | 23 | W | 158 | 2 |
| 3 | Python | 2 | Green | 23 | W | 158 | 2 |
| 4 | Go | 2 | Green | 23 | W | 158 | 2 |
| 5 | C++ | 2 | Green | 23 | W | 158 | 2 |
| 1 | Java | 3 | Henry | 23 | M | 185 | 1 |
| 2 | Mysql | 3 | Henry | 23 | M | 185 | 1 |
| 3 | Python | 3 | Henry | 23 | M | 185 | 1 |
| 4 | Go | 3 | Henry | 23 | M | 185 | 1 |
| 5 | C++ | 3 | Henry | 23 | M | 185 | 1 |
| 1 | Java | 4 | Jane | 22 | W | 62 | 3 |
| 2 | Mysql | 4 | Jane | 22 | W | 62 | 3 |
| 3 | Python | 4 | Jane | 22 | W | 62 | 3 |
| 4 | Go | 4 | Jane | 22 | W | 62 | 3 |
| 5 | C++ | 4 | Jane | 22 | W | 62 | 3 |
| 1 | Java | 5 | Jim | 24 | M | 175 | 2 |
| 2 | Mysql | 5 | Jim | 24 | M | 175 | 2 |
| 3 | Python | 5 | Jim | 24 | M | 175 | 2 |
| 4 | Go | 5 | Jim | 24 | M | 175 | 2 |
| 5 | C++ | 5 | Jim | 24 | M | 175 | 2 |
| 1 | Java | 6 | John | 21 | M | 172 | 4 |
| 2 | Mysql | 6 | John | 21 | M | 172 | 4 |
| 3 | Python | 6 | John | 21 | M | 172 | 4 |
| 4 | Go | 6 | John | 21 | M | 172 | 4 |
| 5 | C++ | 6 | John | 21 | M | 172 | 4 |
| 1 | Java | 7 | Lily | 22 | W | 165 | 4 |
| 2 | Mysql | 7 | Lily | 22 | W | 165 | 4 |
| 3 | Python | 7 | Lily | 22 | W | 165 | 4 |
| 4 | Go | 7 | Lily | 22 | W | 165 | 4 |
| 5 | C++ | 7 | Lily | 22 | W | 165 | 4 |
| 1 | Java | 8 | Susan | 23 | W | 170 | 5 |
| 2 | Mysql | 8 | Susan | 23 | W | 170 | 5 |
| 3 | Python | 8 | Susan | 23 | W | 170 | 5 |
| 4 | Go | 8 | Susan | 23 | W | 170 | 5 |
| 5 | C++ | 8 | Susan | 23 | W | 170 | 5 |
| 1 | Java | 9 | Thomas | 22 | M | 178 | 5 |
| 2 | Mysql | 9 | Thomas | 22 | M | 178 | 5 |
| 3 | Python | 9 | Thomas | 22 | M | 178 | 5 |
| 4 | Go | 9 | Thomas | 22 | M | 178 | 5 |
| 5 | C++ | 9 | Thomas | 22 | M | 178 | 5 |
| 1 | Java | 10 | Tom | 23 | M | 165 | 5 |
| 2 | Mysql | 10 | Tom | 23 | M | 165 | 5 |
| 3 | Python | 10 | Tom | 23 | M | 165 | 5 |
| 4 | Go | 10 | Tom | 23 | M | 165 | 5 |
| 5 | C++ | 10 | Tom | 23 | M | 165 | 5 |
+----+-------------+----+--------+------+------+--------+-----------+
50 rows in set (0.00 sec)
并且,从以上结果我们可以看出:
两个集合相乘,不满足交换率,即 A×B≠B×A。
A 集合和 B 集合的笛卡尔积是 A 集合的元素个数 × B 集合的元素个数。
多表查询遵循的算法就是以上提到的笛卡尔积,表与表之间的连接可以看成是在做乘法运算。在实际应用中,应避免使用笛卡尔积,因为笛卡尔积中容易存在大量的不合理数据,简单来说就是容易导致查询结果重复、混乱。
####交叉连接
交叉连接的语法格式如下:
SELECT <字段名> FROM <表1> CROSS JOIN <表2> [WHERE子句];
或
SELECT <字段名> FROM <表1>, <表2> [WHERE子句];
语法说明如下:
字段名:需要查询的字段名称。
<表1><表2>:需要交叉连接的表名。
WHERE 子句:用来设置交叉连接的查询条件。
查询 tb_course 表中的 id 字段和 tb_students_info 表中的 course_id 字段相等的内容, SQL 语句和运行结果如下:
mysql> select * from tb_course cross join tb_students_info where tb_course.id = tb_students_info.course_id;
+----+-------------+----+--------+------+------+--------+-----------+
| id | course_name | id | name | age | sex | height | course_id |
+----+-------------+----+--------+------+------+--------+-----------+
| 1 | Java | 1 | Dany | 25 | W | 160 | 1 |
| 2 | Mysql | 2 | Green | 23 | W | 158 | 2 |
| 1 | Java | 3 | Henry | 23 | M | 185 | 1 |
| 3 | Python | 4 | Jane | 22 | W | 62 | 3 |
| 2 | Mysql | 5 | Jim | 24 | M | 175 | 2 |
| 4 | Go | 6 | John | 21 | M | 172 | 4 |
| 4 | Go | 7 | Lily | 22 | W | 165 | 4 |
| 5 | C++ | 8 | Susan | 23 | W | 170 | 5 |
| 5 | C++ | 9 | Thomas | 22 | M | 178 | 5 |
| 5 | C++ | 10 | Tom | 23 | M | 165 | 5 |
+----+-------------+----+--------+------+------+--------+-----------+
10 rows in set (0.03 sec)
如果在交叉连接时使用 WHERE 子句,MySQL 会先生成两个表的笛卡尔积,然后再选择满足 WHERE 条件的记录。因此,表的数量较多时,交叉连接会非常非常慢。一般情况下不建议使用交叉连接。
在 MySQL 中,多表查询一般使用内连接和外连接,它们的效率要高于交叉连接。
###内连接
内连接(INNER JOIN)主要通过设置连接条件的方式,来移除查询结果中某些数据行的交叉连接。简单来说,就是利用条件表达式来消除交叉连接的某些数据行。
内连接使用 INNER JOIN 关键字连接两张表,并使用 ON 子句来设置连接条件。如果没有连接条件,INNER JOIN 和 CROSS JOIN 在语法上是等同的,两者可以互换。
内连接的语法格式如下:
SELECT <字段名> FROM <表1> INNER JOIN <表2> [ON子句];
语法说明如下。
字段名:需要查询的字段名称。
<表1><表2>:需要内连接的表名。
INNER JOIN :内连接中可以省略 INNER 关键字,只用关键字 JOIN。
ON 子句:用来设置内连接的连接条件。
在 tb_students_info 表和 tb_course 表之间,使用内连接查询学生姓名和相对应的课程名称,SQL 语句和运行结果如下
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 | Mysql |
| John | Go |
| Lily | Go |
| Susan | C++ |
| Thomas | C++ |
| Tom | C++ |
+--------+-------------+
10 rows in set (0.00 sec)
###外连接
内连接的查询结果都是符合连接条件的记录,而外连接会先将连接的表分为基表和参考表,再以基表为依据返回满足和不满足条件的记录。
外连接可以分为左外连接和右外连接2种,下面根据实例分别介绍左外连接和右外连接。
####左连接
左外连接又称为左连接,使用 LEFT OUTER JOIN 关键字连接两个表,并使用 ON 子句来设置连接条件。
左连接的语法格式如下:
SELECT <字段名> FROM <表1> LEFT OUTER JOIN <表2> <ON子句>;
语法说明如下:
字段名:需要查询的字段名称。
<表1><表2>:需要左连接的表名。
LEFT OUTER JOIN:左连接中可以省略 OUTER 关键字,只使用关键字 LEFT JOIN。
ON 子句:用来设置左连接的连接条件,不能省略。
在 tb_students_info 表和 tb_course 表中查询所有学生姓名和相对应的课程名称,包括没有课程的学生,SQL 语句和运行结果如下:
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 |
| Henry | Java |
| Green | Mysql |
| Jim | Mysql |
| Jane | Python |
| John | Go |
| Lily | Go |
| Susan | C++ |
| Thomas | C++ |
| Tom | C++ |
+--------+-------------+
10 rows in set (0.01 sec)
####右连接
右外连接又称为右连接,右连接是左连接的反向连接。使用 RIGHT OUTER JOIN 关键字连接两个表,并使用 ON 子句来设置连接条件。
右连接的语法格式如下:
SELECT <字段名> FROM <表1> RIGHT OUTER JOIN <表2> <ON子句>;
语法说明如下:
字段名:需要查询的字段名称。
<表1><表2>:需要右连接的表名。
RIGHT OUTER JOIN:右连接中可以省略 OUTER 关键字,只使用关键字 RIGHT JOIN。
ON 子句:用来设置右连接的连接条件,不能省略。
在 tb_students_info 表和 tb_course 表中查询所有课程,包括没有学生的课程,SQL 语句和运行结果如下:
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 | Mysql |
| John | Go |
| Lily | Go |
| Susan | C++ |
| Thomas | C++ |
| Tom | C++ |
| NULL | HTML |
+--------+-------------+
11 rows in set (0.00 sec)
###分组查询
在 MySQL 中,GROUP BY 关键字可以根据一个或多个字段对查询结果进行分组。
使用 GROUP BY 关键字的语法格式如下:
GROUP BY <字段名>
其中,"字段名"表示需要分组的字段名称,多个字段时用逗号隔开。
####按单独使用
单独使用 GROUP BY 关键字时,查询结果会只显示每个分组的第一条记录。
下面根据 tb_students_info 表中的 字段进行分组查询,
####GROUP BY 与 GROUP_CONCAT()
GROUP BY 关键字可以和 GROUP_CONCAT() 函数一起使用。GROUP_CONCAT() 函数会把每个分组的字段值都显示出来。
下面根据 tb_students_info 表中的 字段进行分组查询,使用 GROUP_CONCAT() 函数将每个分组的 name 字段的值都显示出来。SQL 语句和运行结果如下:
mysql> SELECT `sex`, GROUP_CONCAT(name) FROM tb_students_info GROUP BY sex;
+------+----------------------------+
| sex | GROUP_CONCAT(name) |
+------+----------------------------+
| M | Henry,Jim,John,Thomas,Tom |
| W | Dany,Green,Jane,Lily,Susan |
+------+----------------------------+
2 rows in set (0.00 sec)
下面根据 tb_students_info 表中的年龄 和 字段进行分组查询
mysql> SELECT age,sex,GROUP_CONCAT(name) FROM tb_students_info GROUP BY age,sex;
+------+------+--------------------+
| age | sex | GROUP_CONCAT(name) |
+------+------+--------------------+
| 21 | M | John |
| 22 | M | Thomas |
| 22 | W | Jane,Lily |
| 23 | M | Henry,Tom |
| 23 | W | Green,Susan |
| 24 | M | Jim |
| 25 | W | Dany |
+------+------+--------------------+
7 rows in set (0.00 sec)
上面实例在分组过程中,先按照 age 字段进行分组,当 age 字段值相等时,再把 age 字段值相等的记录按照 字段进行分组。
多个字段分组查询时,会先按照第一个字段进行分组。如果第一个字段中有相同的值,MySQL 才会按照第二个字段进行分组。如果第一个字段中的数据都是唯一的,那么 MySQL 将不再对第二个字段进行分组。
####GROUP BY 与聚合函数
在数据统计时,GROUP BY 关键字经常和聚合函数一起使用。
mysql> SELECT sex,COUNT(sex) FROM tb_students_info GROUP BY sex;
+------+------------+
| sex | COUNT(sex) |
+------+------------+
| M | 5 |
| W | 5 |
+------+------------+
2 rows in set (0.00 sec)
结果显示,字段值为“女”的记录是一组,有 5 条记录;字段值为“男”的记录是一组,有 5 条记录。
####分组依据与汇总
WITH POLLUP 关键字用来在所有记录的最后加上一条记录,这条记录是上面所有记录的总和,即统计记录数量。
下面根据 tb_students_info 表中的 字段进行分组查询,并使用 WITH ROLLUP 显示记录的总和:
mysql> SELECT sex,GROUP_CONCAT(name) FROM tb_students_info GROUP BY sex WITH ROLLUP;
+------+------------------------------------------------------+
| sex | GROUP_CONCAT(name) |
+------+------------------------------------------------------+
| M | Henry,Jim,John,Thomas,Tom |
| W | Dany,Green,Jane,Lily,Susan |
| NULL | Henry,Jim,John,Thomas,Tom,Dany,Green,Jane,Lily,Susan |
+------+------------------------------------------------------+
3 rows in set (0.00 sec)
查询结果显示,GROUP_CONCAT(name) 显示了每个分组的 name 字段值。同时,最后一条记录的 GROUP_CONCAT(name) 字段的值刚好是上面分组 name 字段值的总和。
###子查询
子查询是 MySQL 中比较常用的查询方法,通过子查询可以实现多表查询。子查询指将一个查询语句嵌套在另一个查询语句中。子查询可以在 SELECT、UPDATE 和 DELETE 语句中使用,而且可以进行多层嵌套。在实际开发时,子查询经常出现在 WHERE 子句中。
子查询在 WHERE 中的语法格式如下:
WHERE <表达式> <操作符> (子查询)
其中,操作符可以是比较运算符和 IN、NOT IN、EXISTS、NOT EXISTS 等关键字。
1)在|NOT IN
当表达式与子查询返回的结果集中的某个值相等时,返回 TRUE,否则返回 FALSE;若使用关键字 NOT,则返回值正好相反。
2)存在|NOT EXISTS
用于判断子查询的结果集是否为空,若子查询的结果集不为空,返回 TRUE,否则返回 FALSE;若使用关键字 NOT,则返回的值正好相反。
使用子查询在 tb_students_info 表和 tb_course 表中查询学习 Java 课程的学生姓名,SQL 语句和运行结果如下:
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)
首先单独执行内查询,查询出 tb_course 表中课程为 Java 的 id,SQL 语句和运行结果如下:
mysql> SELECT id FROM tb_course WHERE course_name = 'Java';
+----+
| id |
+----+
| 1 |
+----+
1 row in set (0.00 sec)
然后执行外层查询,在 tb_students_info 表中查询 course_id 等于 1 的学生姓名。SQL 语句和运行结果如下:
mysql> SELECT name FROM tb_students_info WHERE course_id IN (1);
+-------+
| name |
+-------+
| Dany |
| Henry |
+-------+
2 rows in set (0.00 sec)
使用=运算符,在 tb_course 表和 tb_students_info 表中查询出所有学习 Python 课程的学生姓名
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)
使用<>运算符,在 tb_course 表和 tb_students_info 表中查询出没有学习 Python 课程的学生姓名
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 |
+--------+
9 rows in set (0.00 sec)
查询 tb_course 表中是否存在 id=1 的课程,如果存在,就查询出 tb_students_info 表中的记录
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 | W | 160 | 1 |
| 2 | Green | 23 | W | 158 | 2 |
| 3 | Henry | 23 | M | 185 | 1 |
| 4 | Jane | 22 | W | 62 | 3 |
| 5 | Jim | 24 | M | 175 | 2 |
| 6 | John | 21 | M | 172 | 4 |
| 7 | Lily | 22 | W | 165 | 4 |
| 8 | Susan | 23 | W | 170 | 5 |
| 9 | Thomas | 22 | M | 178 | 5 |
| 10 | Tom | 23 | M | 165 | 5 |
+----+--------+------+------+--------+-----------+
10 rows in set (0.00 sec)
子查询的功能也可以通过表连接完成,但是子查询会使 SQL 语句更容易阅读和编写。
一般来说,表连接(内连接和外连接等)都可以用子查询替换,但反过来却不一定,有的子查询不能用表连接来替换。子查询比较灵活、方便、形式多样,适合作为查询的筛选条件,而表连接更适合于查看连接表的数据。