MySQL数据库约束与数据表查询的进阶操作

最新推荐文章于 2024-09-15 11:40:06 发布
最新推荐文章于 2024-09-15 11:40:06 发布
阅读量67 收藏
点赞数
分类专栏： 面试 学习路线 阿里巴巴 文章标签： java-ee c++ 后端
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/jiey0407/article/details/125157271
版权
面试 同时被 3 个专栏收录
139 篇文章 10 订阅
订阅专栏
阿里巴巴
125 篇文章 2 订阅
订阅专栏
学习路线
119 篇文章 1 订阅
订阅专栏
本文深入讲解了数据库约束（NOTNULL、UNIQUE、DEFAULT、PRIMARYKEY、FOREIGNKEY）的应用，数据表设计（一对一、一对多、多对多），以及数据查询技巧（聚合函数、分组、联合查询、子查询和合并查询）。适合初学者提升数据库操作能力。
摘要由CSDN通过智能技术生成

前面的话

本篇文章主要介绍数据库约束，数据表的聚合查询，多表查询，合并查询，难点基本上都集中在多表查询部分，大家要耐心地去看去练习哦。

??博客主页：的博客主页
??欢迎关注??点赞??收藏留言??
??本文由原创，CSDN首发！
??首发时间：??2022年3月19日??
坚持和努力一定能换来诗与远方！
??参考书籍：??《MySQL必知必会》，??《高性能MySQL》
??参考在线编程网站：??牛客网??力扣
博主的码云gitee，平常博主写的程序代码都在里面。
博主的github，平常博主写的程序代码都在里面。
??作者水平很有限，如果发现错误，一定要及时告知作者哦！感谢感谢！

??导航小助手??

温馨提示： 全文共2.9万字，阅读可能需要较长时间。
封面

??1.数据库的约束

??1.1数据库常用约束

  • NOT NULL - 指示某列不能存储 NULL 值。
  • UNIQUE - 保证某列的每行必须有唯一的值。
  • DEFAULT - 规定没有给列赋值时的默认值。
  • PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。
  • FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。
  • CHECK - 保证列中的值符合指定的条件。对于MySQL数据库，对CHECK子句进行分析，但是忽略CHECK子句。

??1.2NOT NULL约束

该约束使用对象为列，创建数据表的时候使用，可以约束列的值不能为null。
语法：

create table 表名 (变量 数据类型 not null, ...);

栗子：

-- 对id使用not null约束
mysql> create table student (id int not null, name varchar(20));
Query OK, 0 rows affected (0.01 sec)
--id值不能为null ，id默认值为null，所以插入数据时必须对id赋值
mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
-- 不为null的id，没问题
mysql> insert into student values (1, "张三");
Query OK, 1 row affected (0.00 sec)
-- 为null的id插入失败
mysql> insert into student values (nul1, "李四");
ERROR 1054 (42S22): Unknown column 'nul1' in 'field list'
-- 插入时不给id列赋值，插入失败
mysql> insert into student (name) values ("李四");
ERROR 1364 (HY000): Field 'id' doesn't have a default value

该约束可以同时对多个使用。

-- 如果存在一个表则删除
mysql> drop table if exists student;
Query OK, 0 rows affected (0.01 sec)
-- 对id和name都进行 not null 约束
mysql> create table student (id int not null, name varchar(20) not null);
Query OK, 0 rows affected (0.01 sec)
-- 查看表结构
mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |
| name  | varchar(20) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

??1.3UNIQUE约束

该约束使用的对象为列，创建数据表的时候使用，可以约束该列的值不能重复。
语法：

create table 表名 (变量 数据类型 unique, ...);

栗子:

-- 删除原有的student表
mysql> drop table if exists student;
Query OK, 0 rows affected (0.01 sec)
-- 创建student表，并对id和name使用unique约束
mysql> create table student (id int unique, name varchar(20) unique);
Query OK, 0 rows affected (0.02 sec)
-- 查看表结构
mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  | UNI | NULL    |       |
| name  | varchar(20) | YES  | UNI | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
-- 插入不重复数据
mysql> insert into student values (1, "张三");
Query OK, 1 row affected (0.00 sec)
-- 插入id或name重复的数据，均失败
mysql> insert into student values (1, "张三");
ERROR 1062 (23000): Duplicate entry '1' for key 'id'
mysql> insert into student values (2, "张三");
ERROR 1062 (23000): Duplicate entry '张三' for key 'name'
mysql> insert into student values (1, "李四");
ERROR 1062 (23000): Duplicate entry '1' for key 'id'

??1.4DEFAULT约定默认值

与上面约束使用方法一样，创建表时可使default 默认值来给某列设置默认值。
语法：

create table 表名 (变量 数据类型 default 默认值, ...);

栗子：

mysql> drop table if exists student;
Query OK, 0 rows affected (0.01 sec)

mysql> create table student (id int, name varchar(20) default "匿名");
Query OK, 0 rows affected (0.01 sec)

mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | 匿名    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> insert into student (id) values (1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
+------+--------+
| id   | name   |
+------+--------+
|    1 | 匿名   |
+------+--------+
1 row in set (0.00 sec)

??1.5 PRIMARY KEY约束

该约束叫做主键约束，相当于not null和unique同时使用的效果，它能约束该列的值不能与其他列相同且不能为null，相当于一个身份证标识。
语法：

create table 表名 (变量 数据类型 primary key, ...);

栗子：

mysql> drop table if exists student;
Query OK, 0 rows affected (0.01 sec)

mysql> create table student (id int primary key, name varchar(50));
Query OK, 0 rows affected (0.01 sec)

mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(50) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> insert into student values (1, "张三");
Query OK, 1 row affected (0.00 sec)

mysql> insert into student values (2, "李四");
Query OK, 1 row affected (0.00 sec)

mysql> insert into student values (2, "王五");
ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'
mysql> select * from student;
+----+--------+
| id | name   |
+----+--------+
|  1 | 张三   |
|  2 | 李四   |
+----+--------+
2 rows in set (0.00 sec)

MySQL还支持自增主键，就是使用自增主键约束后，可以不用给这个列赋值，在插入记录时，它会自动从1开始自增（未初始值时），如果前一次插入时给定了一个值，则后续插入会基于该值自增。
语法：

create table 表名 (变量 数据类型 primary key auto_increment, ...);

栗子：

mysql> drop table if exists student;
Query OK, 0 rows affected (0.01 sec)
mysql> create table student (id int primary key auto_increment, name varchar(20));
Query OK, 0 rows affected (0.02 sec)

mysql> desc student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql> insert into student values (null, "张三"), (null, "李四"), (null, "王五");
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from student;
+----+--------+
| id | name   |
+----+--------+
|  1 | 张三   |
|  2 | 李四   |
|  3 | 王五   |
+----+--------+
3 rows in set (0.00 sec)

mysql> insert into student values (10, "黑大帅"), (null, "灰太狼"), (null, "小灰灰");
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from student;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | 张三      |
|  2 | 李四      |
|  3 | 王五      |
| 10 | 黑大帅    |
| 11 | 灰太狼    |
| 12 | 小灰灰    |
+----+-----------+
6 rows in set (0.00 sec)

??1.6 FOREIGN KEY约束

该约束称为外键约束，是针对与两张表的情况下使用的，比如有一张学生表里面有学号，姓名，班级号，还有一张表是班级表，里面有班级号和班级名，该学生表里面的学生所在班级都能在班级表里面找到，这个时候就需要对学生表做一个外键约束，就能使学生表与班级表联系起来。我们发现学生表是依赖与班级表的，称学生表为子表，班级表为父表。
语法：

--父表已经创建
create table 表名 (变量 数据类型. ..., foreign key (子表中的一个变量) references 父表名(父表中的一个变量));

栗子：

-- 创建class表作为student的父表
mysql> create table class (classId int primary key auto_increment, className varchar(50));
Query OK, 0 rows affected (0.02 sec)

mysql> desc class;
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| classId   | int(11)     | NO   | PRI | NULL    | auto_increment |
| className | varchar(50) | YES  |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> insert into class values (null, "计算机1班"), (null, "软工1班"), (null, "英语1班"), (null, "中文1班"), (null, "机械1班"), (null, "食工1班"), (null, "化学1班"), (null, "电子1班"), (null, "材料1班");
Query OK, 9 rows affected (0.00 sec)
Records: 9  Duplicates: 0  Warnings: 0

mysql> select * from class;
+---------+---------------+
| classId | className     |
+---------+---------------+
|       1 | 计算机1班     |
|       2 | 软工1班       |
|       3 | 英语1班       |
|       4 | 中文1班       |
|       5 | 机械1班       |
|       6 | 食工1班       |
|       7 | 化学1班       |
|       8 | 电子1班       |
|       9 | 材料1班       |
+---------+---------------+
9 rows in set (0.00 sec)
-- 添加外键约束创建子表student
mysql> drop table if exists student;
Query OK, 0 rows affected (0.01 sec)

mysql> create table student (id int primary key auto_increment, name varchar(20), classId int, foreign key (classId) references class(classId));
Query OK, 0 rows affected (0.02 sec)

mysql> desc student;
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| id      | int(11)     | NO   | PRI | NULL    | auto_increment |
| name    | varchar(20) | YES  |     | NULL    |                |
| classId | int(11)     | YES  | MUL | NULL    |                |
+---------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

-- 添加学生
mysql> insert into student (name, classId) values ("喜羊羊", 1), ("美羊羊", 4), ("沸羊羊", 5), ("暖羊羊", 3), ("懒羊羊", 6), ("慢羊羊", 9), ("灰太狼", 8), ("小灰灰", 7), ("黑大帅", 8);
Query OK, 9 rows affected (0.01 sec)
Records: 9  Duplicates: 0  Warnings: 0
mysql> select * from student;
+----+-----------+---------+
| id | name      | classId |
+----+-----------+---------+
|  1 | 喜羊羊    |       1 |
|  2 | 美羊羊    |       4 |
|  3 | 沸羊羊    |       5 |
|  4 | 暖羊羊    |       3 |
|  5 | 懒羊羊    |       6 |
|  6 | 慢羊羊    |       9 |
|  7 | 灰太狼    |       8 |
|  8 | 小灰灰    |       7 |
|  9 | 黑大帅    |       8 |
+----+-----------+---------+
9 rows in set (0.00 sec)
-- 添加班级表中不存在的班级会插入失败
mysql> insert into student (name, classId) values ("光头强", 20);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`classId`) REFERENCES `class` (`classId`))

使用外键约束的数据表，依赖于其他一个表，这个被依赖的表称为父表，被约束的表被称为子表，当在子表插入记录时，必须在父表中存在某一对应关系才能插入。

除了插入记录，修改子表中的记录也有可能失败，就如上面的学生表和班级表，如果将黑大帅的班级id修改为10,但是这个id在班级表是不存在的，所以会发生修改失败的情况。

mysql> update student set classId = 10 where name = "黑大帅";
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`classId`) REFERENCES `class` (`classId`))

同样，外键约束不仅约束子表，父表也是受约束的，当你试图将班级表中的id修改或删除，且在学生表中存在班级为此id的学生，那么你会操作失败。

mysql> update class set classId = 10 where classId = 1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`classId`) REFERENCES `class` (`classId`))

当然如果你试图删除或修改父表中的一个id所对应的记录，只要学生表中不存在班级为此id的学生，就可以删除。比如学生表中没有软工1班（对应id为2）的学生，那么这个记录在班级表可以修改或者删除。

mysql> delete from class where classId=2;
Query OK, 1 row affected (0.00 sec)

外键约束的工作原理：
在子表插入新的记录时，会根据对应的值先在父表查询，查询到了目标值之后才能完成子表的插入操作。
对于父表的查询操作，被依赖的这一列必须要要有索引（索引能加快查询效率），如果使用primary key或者unique约束该列，则该列会自动创建索引。所以上面的学生表与班级表的例子，虽然我们没有主动对父表中的classId列创建索引，但是该列被主键约束，自动创建了索引。

??2.数据表的设计

??2.1一对一

以教务系统为例，学生表有学号姓名班级等属性，教务系统上的用户表有账号密码等属性，每一个学生有且仅有一个教务系统的账号，并且教务系统上的一个账号仅对应一个学生，像这种关系就是一对一的关系。

那么如何在数据库表示这种一对一的关系呢？
方案1：把学生表与用户表放在一个表中一一对应。
方案2：学生表中增加一列，存放学生在教务系统上的账号，在教务系统用户表增加一列，存放学生的学号。

??2.2一对多

还是以教务系统为例，如学生与班级之间的关系就是一个一对多的关系，一个学生原则上只能在一个班级中，一个班级考研英语容纳多名学生。

那么如何在数据库表示这种一对多的关系呢？
方案1：在班级表中增加一列，存放一个班里面所有学生的学号。但是MySQL不支持这种方案，因为SQL中没有类似于数组的类型。
方案2：在一个学生表中增加一列，存放学生所在的班级。

??2.3多对多

还是以教务系统为例，如学生与课程之间的关系就是一个典型的多对多的关系，一个学生可以学习多门课程，一门课程中有多名学生学习。

那么如何在数据库表示这种多对多的关系呢？
只有一种方案：建立一个关联表，来关联学生表和课程表，这个关联表中有两列，一列用来存放学生学号，另一列存放课程编号，这样两表可以通过这一个关联表来实现多对多的一个关系。

??3.数据表的查询操作（进阶）

??3.1将一个表中的数据插入到另一个表

SQL支持将表A的数据插入到表B，但是前提的满足插入的时候需满足列与列之间的数据类型以及顺序要相同。
语法：

insert into B select 对应与表B的列集合 from A;

栗子：

mysql> create table A (id int, name varchar(20));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into A values (1, "张三疯"), (2, "夏洛特"), (3, "熊大"), (4, "刻晴"), (5, "急先锋");
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from A;
+------+-----------+
| id   | name      |
+------+-----------+
|    1 | 张三疯    |
|    2 | 夏洛特    |
|    3 | 熊大      |
|    4 | 刻晴      |
|    5 | 急先锋    |
+------+-----------+
5 rows in set (0.00 sec)

mysql> create table B (id int, name varchar(20));
Query OK, 0 rows affected (0.02 sec)
-- A与B表数据类型顺序对应，不需调整
mysql> insert into B select * from A;
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from B;
+------+-----------+
| id   | name      |
+------+-----------+
|    1 | 张三疯    |
|    2 | 夏洛特    |
|    3 | 熊大      |
|    4 | 刻晴      |
|    5 | 急先锋    |
+------+-----------+
5 rows in set (0.00 sec)
-- A与C表数据类型顺序不对应，需要调整
mysql> create table C (name varchar(20), id int);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into C select name, id from A;
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from C;
+-----------+------+
| name      | id   |
+-----------+------+
| 张三疯    |    1 |
| 夏洛特    |    2 |
| 熊大      |    3 |
| 刻晴      |    4 |
| 急先锋    |    5 |
+-----------+------+
5 rows in set (0.00 sec)

将一个表A数据拷贝到另外一个表B本质是先查询表A的数据，查询出来的数据需要与表B对应，表A中查询结果为一临时表，然后再将这个临时表插入到表B。

??3.2聚合查询

??3.2.1聚合查询函数

函数

说明

count(列名或表达式)

返回查询到的数据的个数

sum(列名或表达式)

返回查询到的数据的和

avg(列名或表达式)

返回查询到的数据的平均值

max(列名或表达式)

返回查询到的数据的最大值

min（列名或表达式)

返回查询到的数据的最小值

对于上面的聚合函数可以在列名（表达式）前加入一个关键字distinct可以实现对查询到的数据去重后，再进行计算。

我们来对什么的聚合函数做一些演示，首先我们来创建一个表：

create table exam_score(id int, name varchar(50), chinese double(4,1), math double(4,1),english double(4,1), computer double(4,1));
insert into exam_score values (1,'美羊羊',99.5,90.5,98,82),
    -> (2, '懒羊羊', 58.5,32.5,44,66.5),
    -> (3, '喜羊羊', 92,98,88,100),
    -> (4, '沸羊羊', 78,72,74.5,81),
    -> (5, '暖羊羊', 90,91,98,76),
    -> (6, '灰太狼', 33,91,32,98.5),
    -> (7, '小灰灰', 81, 82,78,88),
    -> (8, '神秘人', null, null, null, null);

表格数据如下：

+------+-----------+---------+------+---------+----------+
| id   | name      | chinese | math | english | computer |
+------+-----------+---------+------+---------+----------+
|    1 | 美羊羊    |    99.5 | 90.5 |    98.0 |     82.0 |
|    2 | 懒羊羊    |    58.5 | 32.5 |    44.0 |     66.5 |
|    3 | 喜羊羊    |    92.0 | 98.0 |    92.0 |    100.0 |
|    4 | 沸羊羊    |    78.0 | 72.0 |    74.5 |     81.0 |
|    5 | 暖羊羊    |    90.0 | 91.0 |    98.0 |     76.0 |
|    6 | 灰太狼    |    33.0 | 91.0 |    32.0 |     98.5 |
|    7 | 小灰灰    |    81.0 | 82.0 |    78.0 |     88.0 |
|    8 | 神秘人    |    NULL | NULL |    NULL |     NULL |
+------+-----------+---------+------+---------+----------+

首先我们来使用count函数，计算这个数据表有多少行，统计数据表行数可以对*的查询结果计数。

-- 一共8行
mysql> select count(*) from exam_score;
+----------+
| count(*) |
+----------+
|        8 |
+----------+
1 row in set (0.00 sec)
-- 当然也可以对输出的表名改名
mysql> select count(*) as "全表行数" from exam_score;
+--------------+
| 全表行数     |
+--------------+
|            8 |
+--------------+
1 row in set (0.00 sec)

还有一个疑问，count函数能不能对null计数？我们来计算一下语文成绩的个数。

mysql> select count(chinese) as "语文成绩个数" from exam_score;
+--------------------+
| 语文成绩个数       |
+--------------------+
|                  7 |
+--------------------+
1 row in set (0.00 sec)

所以说null值count函数不会计算，其实包括其他的聚合函数也不会计算null。

例如sum函数，对语文成绩进行求和。

-- 包含null结果
mysql> select sum(chinese) as "语文成绩总分" from exam_score;
+--------------------+
| 语文成绩总分       |
+--------------------+
|              532.0 |
+--------------------+
1 row in set (0.00 sec)
-- 删除null的记录
mysql> delete from exam_score where name="神秘人";
Query OK, 1 row affected (0.01 sec)

mysql> select sum(chinese) as "语文成绩总分" from exam_score;
+--------------------+
| 语文成绩总分       |
+--------------------+
|              532.0 |
+--------------------+

最后，聚合函数不能直接嵌套使用。

mysql> select count(count(math)) from exam_score;
ERROR 1111 (HY000): Invalid use of group function

聚合函数针对的是行与行之间的计算，之前的表达式求值是列与列之间的计算。

??3.2.2分组查询

我们可以通过group by 列名关键字对行进行分组，按照列的值，将值相同的分成一组。
分组查询语法：

select 列,... from 表名 (条件表达式) group by 列名;

其中条件表达式是类似where，limit的语句，可以省略。

栗子：
我们来建一个不同职位的薪水表。

-- 建表
mysql> create table emp (
	id int primary key auto_increment, 
	name varchar(20) not null, 
	role varchar(20) not null, 
	salary numeric(11, 2)
);
Query OK, 0 rows affected (0.03 sec)
-- 插入数据
mysql> insert into emp (name, role, salary) values
     ("幕后老板", "董事长", 666666.66),
     ("诸葛亮", "策划", 12000),
     ("柯南", "策划", 12800),
     ("梅毛冰", "策划", 11000),
     ("可莉", "游戏角色", 5000),
     ("刻晴", "游戏角色", 5600),
     ("胡桃", "游戏角色", 6666),
     ("八重神子", "游戏角色", 4888),
     ("枫原万叶", "游戏角色", 5200),
     ("心海", "游戏角色", 6000),
     ("打工人", "开发工程师", 20000),
     ("苦逼加班人", "开发工程师", 20000),
     ("996专业户", "开发工程师", 21000),
     ("搬砖大师", "开发工程师", 20888);
-- 查看数据
mysql> select * from emp;
+----+-----------------+-----------------+-----------+
| id | name            | role            | salary    |
+----+-----------------+-----------------+-----------+
|  1 | 幕后老板        | 董事长          | 666666.66 |
|  2 | 诸葛亮          | 策划            |  12000.00 |
|  3 | 柯南            | 策划            |  12800.00 |
|  4 | 梅毛冰          | 策划            |  11000.00 |
|  5 | 可莉            | 游戏角色        |   5000.00 |
|  6 | 刻晴            | 游戏角色        |   5600.00 |
|  7 | 胡桃            | 游戏角色        |   6666.00 |
|  8 | 八重神子        | 游戏角色        |   4888.00 |
|  9 | 枫原万叶        | 游戏角色        |   5200.00 |
| 10 | 心海            | 游戏角色        |   6000.00 |
| 11 | 打工人          | 开发工程师      |  20000.00 |
| 12 | 苦逼加班人      | 开发工程师      |  20000.00 |
| 13 | 996专业户       | 开发工程师      |  21000.00 |
| 14 | 搬砖大师        | 开发工程师      |  20888.00 |
+----+-----------------+-----------------+-----------+
14 rows in set (0.00 sec)

我们现在要查询每种岗位员工的薪水的平均值，最高值，最低值。

mysql> select role, avg(salary), max(salary), min(salary) from emp group by role;
+-----------------+---------------+-------------+-------------+
| role            | avg(salary)   | max(salary) | min(salary) |
+-----------------+---------------+-------------+-------------+
| 开发工程师      |  20472.000000 |    21000.00 |    20000.00 |
| 游戏角色        |   5559.000000 |     6666.00 |     4888.00 |
| 策划            |  11933.333333 |    12800.00 |    11000.00 |
| 董事长          | 666666.660000 |   666666.66 |   666666.66 |
+-----------------+---------------+-------------+-------------+
4 rows in set (0.00 sec)
-- 可以为查询的临时表更名
mysql> select role, avg(salary) as "平均值", max(salary) as "最高薪资", min(salary) as "最低薪资" from emp group by role;
+-----------------+---------------+--------------+--------------+
| role            | 平均值        | 最高薪资     | 最低薪资     |
+-----------------+---------------+--------------+--------------+
| 开发工程师      |  20472.000000 |     21000.00 |     20000.00 |
| 游戏角色        |   5559.000000 |      6666.00 |      4888.00 |
| 策划            |  11933.333333 |     12800.00 |     11000.00 |
| 董事长          | 666666.660000 |    666666.66 |    666666.66 |
+-----------------+---------------+--------------+--------------+
4 rows in set (0.00 sec)

如果数据中存在null，那么聚合函数avg, max, min不会纳入计算。

??3.2.3having

针对group by分组后的数据，需要对分组结果再进行条件过滤时，不能使用where条件语句进行过滤，只能使用having进行数据过滤。
语法格式与where差不多，只是where语句紧跟在表名后，而having跟在group by后。
语法：

-- having 用法
select 聚合函数(或者列), ... from 表名 group by 列 having 条件;
-- where 用法
select 聚合函数(或者列), ... from 表名 where 条件 group by 列;

栗子：董事长的薪水太高了，算平均工资时不要把董事长的记录加进去（不是从服务器表中删除）

-- where筛选
mysql> select role, avg(salary) as "平均薪水" from emp where role != "董事长" group by role;
+-----------------+--------------+
| role            | 平均薪水     |
+-----------------+--------------+
| 开发工程师      | 20472.000000 |
| 游戏角色        |  5559.000000 |
| 策划            | 11933.333333 |
+-----------------+--------------+
3 rows in set (0.00 sec)
-- having 筛选
mysql> select role, avg(salary) as "平均薪水" from emp group by role having role != "董事长";
+-----------------+--------------+
| role            | 平均薪水     |
+-----------------+--------------+
| 开发工程师      | 20472.000000 |
| 游戏角色        |  5559.000000 |
| 策划            | 11933.333333 |
+-----------------+--------------+
3 rows in set (0.00 sec)

??3.3联合（多表）查询

??3.3.1笛卡尔积

在数学中，两个集合 X X X和 Y Y Y的笛卡儿积(Cartesian product)，又称直积，表示为 X × Y X × Y X×Y，是其第一个对象是X的成员而第二个对象是Y的一个成员的所有可能的有序对。

例如，设集合 A = a , b A={a,b} A=a,b，集合 B = 0 , 1 , 2 B={0,1,2} B=0,1,2，则两个集合的笛卡尔积为 ( a , 0 ) , ( a , 1 ) , ( a , 2 ) , ( b , 0 ) , ( b , 1 ) , ( b , 2 ) {(a,0),(a,1),(a,2),(b,0),(b,1), (b,2)} (a,0),(a,1),(a,2),(b,0),(b,1),(b,2)。

联合查询也称为多表查询，该查询基于笛卡尔积将多个表合并，再对这个合并表进行有效数据筛选。
笛卡尔积查询语法：

select 列,... from 表名1, 表名2;

栗子：假设我们有下面两张表，请把这两张表进行笛卡尔积。

-- 表1 stu 一共9个学生
+----+-----------+---------+
| id | name      | classId |
+----+-----------+---------+
|  1 | 喜羊羊    |       1 |
|  2 | 美羊羊    |       4 |
|  3 | 沸羊羊    |       5 |
|  4 | 暖羊羊    |       3 |
|  5 | 懒羊羊    |       6 |
|  6 | 慢羊羊    |       9 |
|  7 | 灰太狼    |       8 |
|  8 | 小灰灰    |       7 |
|  9 | 黑大帅    |       8 |
+----+-----------+---------+
-- 表2 class 没有id为2的班级 一共8个班级
+---------+---------------+
| classId | className     |
+---------+---------------+
|       1 | 计算机1班     |
|       3 | 英语1班       |
|       4 | 中文1班       |
|       5 | 机械1班       |
|       6 | 食工1班       |
|       7 | 化学1班       |
|       8 | 电子1班       |
|       9 | 材料1班       |
+---------+---------------+
-- 笛卡尔积结果 一共 9x8=72 条记录
mysql> select * from stu, class;
+----+-----------+---------+---------+---------------+
| id | name      | classId | classId | className     |
+----+-----------+---------+---------+---------------+
|  1 | 喜羊羊    |       1 |       1 | 计算机1班     |
|  1 | 喜羊羊    |       1 |       3 | 英语1班       |
|  1 | 喜羊羊    |       1 |       4 | 中文1班       |
|  1 | 喜羊羊    |       1 |       5 | 机械1班       |
|  1 | 喜羊羊    |       1 |       6 | 食工1班       |
|  1 | 喜羊羊    |       1 |       7 | 化学1班       |
|  1 | 喜羊羊    |       1 |       8 | 电子1班       |
|  1 | 喜羊羊    |       1 |       9 | 材料1班       |
|  2 | 美羊羊    |       4 |       1 | 计算机1班     |
|  2 | 美羊羊    |       4 |       3 | 英语1班       |
|  2 | 美羊羊    |       4 |       4 | 中文1班       |
|  2 | 美羊羊    |       4 |       5 | 机械1班       |
|  2 | 美羊羊    |       4 |       6 | 食工1班       |
|  2 | 美羊羊    |       4 |       7 | 化学1班       |
|  2 | 美羊羊    |       4 |       8 | 电子1班       |
|  2 | 美羊羊    |       4 |       9 | 材料1班       |
|  3 | 沸羊羊    |       5 |       1 | 计算机1班     |
|  3 | 沸羊羊    |       5 |       3 | 英语1班       |
|  3 | 沸羊羊    |       5 |       4 | 中文1班       |
|  3 | 沸羊羊    |       5 |       5 | 机械1班       |
|  3 | 沸羊羊    |       5 |       6 | 食工1班       |
|  3 | 沸羊羊    |       5 |       7 | 化学1班       |
|  3 | 沸羊羊    |       5 |       8 | 电子1班       |
|  3 | 沸羊羊    |       5 |       9 | 材料1班       |
|  4 | 暖羊羊    |       3 |       1 | 计算机1班     |
|  4 | 暖羊羊    |       3 |       3 | 英语1班       |
|  4 | 暖羊羊    |       3 |       4 | 中文1班       |
|  4 | 暖羊羊    |       3 |       5 | 机械1班       |
|  4 | 暖羊羊    |       3 |       6 | 食工1班       |
|  4 | 暖羊羊    |       3 |       7 | 化学1班       |
|  4 | 暖羊羊    |       3 |       8 | 电子1班       |
|  4 | 暖羊羊    |       3 |       9 | 材料1班       |
|  5 | 懒羊羊    |       6 |       1 | 计算机1班     |
|  5 | 懒羊羊    |       6 |       3 | 英语1班       |
|  5 | 懒羊羊    |       6 |       4 | 中文1班       |
|  5 | 懒羊羊    |       6 |       5 | 机械1班       |
|  5 | 懒羊羊    |       6 |       6 | 食工1班       |
|  5 | 懒羊羊    |       6 |       7 | 化学1班       |
|  5 | 懒羊羊    |       6 |       8 | 电子1班       |
|  5 | 懒羊羊    |       6 |       9 | 材料1班       |
|  6 | 慢羊羊    |       9 |       1 | 计算机1班     |
|  6 | 慢羊羊    |       9 |       3 | 英语1班       |
|  6 | 慢羊羊    |       9 |       4 | 中文1班       |
|  6 | 慢羊羊    |       9 |       5 | 机械1班       |
|  6 | 慢羊羊    |       9 |       6 | 食工1班       |
|  6 | 慢羊羊    |       9 |       7 | 化学1班       |
|  6 | 慢羊羊    |       9 |       8 | 电子1班       |
|  6 | 慢羊羊    |       9 |       9 | 材料1班       |
|  7 | 灰太狼    |       8 |       1 | 计算机1班     |
|  7 | 灰太狼    |       8 |       3 | 英语1班       |
|  7 | 灰太狼    |       8 |       4 | 中文1班       |
|  7 | 灰太狼    |       8 |       5 | 机械1班       |
|  7 | 灰太狼    |       8 |       6 | 食工1班       |
|  7 | 灰太狼    |       8 |       7 | 化学1班       |
|  7 | 灰太狼    |       8 |       8 | 电子1班       |
|  7 | 灰太狼    |       8 |       9 | 材料1班       |
|  8 | 小灰灰    |       7 |       1 | 计算机1班     |
|  8 | 小灰灰    |       7 |       3 | 英语1班       |
|  8 | 小灰灰    |       7 |       4 | 中文1班       |
|  8 | 小灰灰    |       7 |       5 | 机械1班       |
|  8 | 小灰灰    |       7 |       6 | 食工1班       |
|  8 | 小灰灰    |       7 |       7 | 化学1班       |
|  8 | 小灰灰    |       7 |       8 | 电子1班       |
|  8 | 小灰灰    |       7 |       9 | 材料1班       |
|  9 | 黑大帅    |       8 |       1 | 计算机1班     |
|  9 | 黑大帅    |       8 |       3 | 英语1班       |
|  9 | 黑大帅    |       8 |       4 | 中文1班       |
|  9 | 黑大帅    |       8 |       5 | 机械1班       |
|  9 | 黑大帅    |       8 |       6 | 食工1班       |
|  9 | 黑大帅    |       8 |       7 | 化学1班       |
|  9 | 黑大帅    |       8 |       8 | 电子1班       |
|  9 | 黑大帅    |       8 |       9 | 材料1班       |
+----+-----------+---------+---------+---------------+
72 rows in set (0.00 sec)

对n条记录的表A和m条记录的表B进行笛卡尔积，一共会产生n*m条记录，所以这个操作很危险，因为实际工作时的表数据量非常大，进行笛卡尔积产生的记录就会更大，因此工作中很少使用，由于多表查询是基于笛卡尔积，因此多表查询操作也是危险的。

得到两表的笛卡尔积后，有大量的记录都是无效的，需要进行筛选，对于上面的这一个栗子，两表的classId相等的数据是有效的，我们可以通过条件查询来筛选出有效的数据，但是这两张表中都有classId这一列，当进行表合并时，存在同名列的情况，我们可以使用表名.列名的形式访问对应表中的列。

-- 筛选有效数据
mysql> select id, name, className from stu, class where stu.classId=class.classId;
+----+-----------+---------------+
| id | name      | className     |
+----+-----------+---------------+
|  1 | 喜羊羊    | 计算机1班     |
|  4 | 暖羊羊    | 英语1班       |
|  2 | 美羊羊    | 中文1班       |
|  3 | 沸羊羊    | 机械1班       |
|  5 | 懒羊羊    | 食工1班       |
|  8 | 小灰灰    | 化学1班       |
|  7 | 灰太狼    | 电子1班       |
|  9 | 黑大帅    | 电子1班       |
|  6 | 慢羊羊    | 材料1班       |
+----+-----------+---------------+

我们来创建几个表来模拟不同班级不同学生不同课程的学生成绩信息：

drop table if exists classes;
drop table if exists student;
drop table if exists course;
drop table if exists score;

create table classes (id int primary key auto_increment, name varchar(20), `desc` varchar(100));

create table student (id int primary key auto_increment, sn varchar(20),  name varchar(20), email varchar(20) ,
                      classes_id int);

create table course(id int primary key auto_increment, name varchar(20));

create table score(score decimal(3, 1), student_id int, course_id int);

insert into classes(name, `desc`) values
                                      ('计算机系2020级1班', '学习了计算机原理、C和Java语言、数据结构和算法'),
                                      ('中文系2020级3班','学习了中国传统文学'),
                                      ('自动化2020级5班','学习了机械自动化'),
                                      ('数学系2020级2班' , '学习了数学分析'),
                                      ('化学系2020级4班' , '学习了有机化学，无机化学，物理化学'),
                                      ('生物系2020级6班', '学习了生物学');

insert into student(sn, name, email, classes_id) values
                                                       ('2020001','喜羊羊','xyy@163.com',1),
                                                       ('2020002','美羊羊','myy@163.com',1),
                                                       ('2020003','沸羊羊','fyy@163.com',1),
                                                       ('2020004','懒羊羊','lyy@163.com',2),
                                                       ('2020005','花羊羊',null,2),
                                                       ('2020006','慢羊羊','yjyj@163.com',6),
                                                       ('2020007','小灰灰',null,2),
                                                       ('2020008','希儿','nzdxierl@163.com',2),
                                                       ('2020009','布洛尼亚','zzxt@163.com',3),
                                                       ('2020010','光头强','wyfc@163.com',3),
                                                       ('2020011','黑大帅','wwzz@163.com',3),
                                                       ('2020012','开心超人','kxcr@163.com',4),
                                                       ('2020013','刻晴','zjnz@163.com',4),
                                                       ('2020014','可莉','nslzknwdm@163.com',5),
                                                       ('2020015','万叶','lypl@163.com',5),
                                                       ('2020016','胡桃','ylzh@163.com',6);

insert into course(name) values
                             ('Java'),('中国传统文化'),('计算机原理'),('汉语言文学'),('高等数学'),('英语'),
                             ('大学物理'),('有机化学'),('生物学'),('数学分析'),('无机化学'),('机械制图');

insert into score(score, student_id, course_id) values
-- 喜羊羊
(90.5, 1, 1),(98.5, 1, 3),(93, 1, 5),(88, 1, 6),
-- 美羊羊
(88, 2, 1), (78.5, 2, 3), (94.5, 2, 5), (98, 2, 6),
-- 沸羊羊
(63, 3, 1),(68, 3, 3),(79, 3, 5),
-- 懒羊羊
(67, 4, 2),(23, 4, 4), (72, 4, 6),
-- 花羊羊
(81, 5, 2),(90, 5, 6),
-- 慢羊羊
(87, 6, 7),(90, 6, 9),(89, 6, 5),
-- 小灰灰
(80, 7, 2),(92, 7, 6),
-- 希儿
(89, 8, 2),(78, 8, 6),
-- 布洛尼亚
(82, 9, 7), (98, 9, 12),
-- 光头强
(70, 10, 7), (81, 10, 12),
-- 黑大帅
(88, 11, 1), (90, 11, 5), (72, 11, 12),
-- 开心超人
(61, 12, 6), (87, 12, 10),
-- 刻晴
(90, 13, 2), (88, 13, 10),
-- 可莉
(58, 14, 5), (72, 14, 8), (78, 14, 11),
-- 万叶
(90, 15, 5), (82, 15, 8), (91, 15, 11),
-- 胡桃
(99, 16, 2), (73, 16, 5), (89, 16, 9);

一共有四张表，classes为班级表，student为学生表，course表为课程表，score为成绩表，其中学生与班级的关系是一对多，学生与课程之间的关系是多对多。

mysql> select * from classes;
+----+-------------------------+-------------------------------------------------------------------+
| id | name                    | desc                                                              |
+----+-------------------------+-------------------------------------------------------------------+
|  1 | 计算机系2020级1班       | 学习了计算机原理、C和Java语言、数据结构和算法                     |
|  2 | 中文系2020级3班         | 学习了中国传统文学                                                |
|  3 | 自动化2020级5班         | 学习了机械自动化                                                  |
|  4 | 数学系2020级2班         | 学习了数学分析                                                    |
|  5 | 化学系2020级4班         | 学习了有机化学，无机化学，物理化学                                |
|  6 | 生物系2020级6班         | 学习了生物学                                                      |
+----+-------------------------+-------------------------------------------------------------------+
6 rows in set (0.00 sec)

mysql> select * from student;
+----+---------+--------------+-------------------+------------+
| id | sn      | name         | email             | classes_id |
+----+---------+--------------+-------------------+------------+
|  1 | 2020001 | 喜羊羊       | xyy@163.com       |          1 |
|  2 | 2020002 | 美羊羊       | myy@163.com       |          1 |
|  3 | 2020003 | 沸羊羊       | fyy@163.com       |          1 |
|  4 | 2020004 | 懒羊羊       | lyy@163.com       |          2 |
|  5 | 2020005 | 花羊羊       | NULL              |          2 |
|  6 | 2020006 | 慢羊羊       | yjyj@163.com      |          6 |
|  7 | 2020007 | 小灰灰       | NULL              |          2 |
|  8 | 2020008 | 希儿         | nzdxierl@163.com  |          2 |
|  9 | 2020009 | 布洛尼亚     | zzxt@163.com      |          3 |
| 10 | 2020010 | 光头强       | wyfc@163.com      |          3 |
| 11 | 2020011 | 黑大帅       | wwzz@163.com      |          3 |
| 12 | 2020012 | 开心超人     | kxcr@163.com      |          4 |
| 13 | 2020013 | 刻晴         | zjnz@163.com      |          4 |
| 14 | 2020014 | 可莉         | nslzknwdm@163.com |          5 |
| 15 | 2020015 | 万叶         | lypl@163.com      |          5 |
| 16 | 2020016 | 胡桃         | ylzh@163.com      |          6 |
+----+---------+--------------+-------------------+------------+
16 rows in set (0.00 sec)

mysql> select * from course;
+----+--------------------+
| id | name               |
+----+--------------------+
|  1 | Java               |
|  2 | 中国传统文化       |
|  3 | 计算机原理         |
|  4 | 汉语言文学         |
|  5 | 高等数学           |
|  6 | 英语               |
|  7 | 大学物理           |
|  8 | 有机化学           |
|  9 | 生物学             |
| 10 | 数学分析           |
| 11 | 无机化学           |
| 12 | 机械制图           |
+----+--------------------+
12 rows in set (0.00 sec)

mysql> select * from score;
+-------+------------+-----------+
| score | student_id | course_id |
+-------+------------+-----------+
|  90.5 |          1 |         1 |
|  98.5 |          1 |         3 |
|  93.0 |          1 |         5 |
|  88.0 |          1 |         6 |
|  88.0 |          2 |         1 |
|  78.5 |          2 |         3 |
|  94.5 |          2 |         5 |
|  98.0 |          2 |         6 |
|  63.0 |          3 |         1 |
|  68.0 |          3 |         3 |
|  79.0 |          3 |         5 |
|  67.0 |          4 |         2 |
|  23.0 |          4 |         4 |
|  72.0 |          4 |         6 |
|  81.0 |          5 |         2 |
|  90.0 |          5 |         6 |
|  87.0 |          6 |         7 |
|  90.0 |          6 |         9 |
|  89.0 |          6 |         5 |
|  80.0 |          7 |         2 |
|  92.0 |          7 |         6 |
|  89.0 |          8 |         2 |
|  78.0 |          8 |         6 |
|  82.0 |          9 |         7 |
|  98.0 |          9 |        12 |
|  70.0 |         10 |         7 |
|  81.0 |         10 |        12 |
|  88.0 |         11 |         1 |
|  90.0 |         11 |         5 |
|  72.0 |         11 |        12 |
|  61.0 |         12 |         6 |
|  87.0 |         12 |        10 |
|  90.0 |         13 |         2 |
|  88.0 |         13 |        10 |
|  58.0 |         14 |         5 |
|  72.0 |         14 |         8 |
|  78.0 |         14 |        11 |
|  90.0 |         15 |         5 |
|  82.0 |         15 |         8 |
|  91.0 |         15 |        11 |
|  99.0 |         16 |         2 |
|  73.0 |         16 |         5 |
|  89.0 |         16 |         9 |
+-------+------------+-----------+
43 rows in set (0.00 sec)

我们根据这几张表来说明如何进行多表查询，其中多表查询需要对多个表进行连接，连接的常用方式有内连接，左外连接，右外连接，全外连接（MySQL不支持），如果多表之间的数据均有对应，内外连接没有区别，如果多表之间的记录没有全部对应，内连接只会显示多表对应的数据集合，左外连接以左表为主，右外连接以右表连接为主。

多表查询的主要步骤为：

  1. 将多个表笛卡尔积。
  2. 筛选有效信息。
  3. 根据需求慢慢使查询结果达到预期。

??3.3.2内连接

格式语法：

select 字段 from 表1, 表2, ... where 条件;
select 字段 from 表1 inner join 表2 on 条件 ...;

其中inner可以省略。
内连接查询的数据记录如下图涂色部分：
3-3-2
栗子：查询“喜羊羊”同学的成绩列表，无需显示科目。
首先要知道喜羊羊的成绩，那么必须得查询到喜羊羊的个人信息，除此之外，还得从分数表获取成绩，所以需要对学生表和分数表进行笛卡尔积。对上面所举例的学生表和成绩表进行笛卡尔积，一共会产生16*43条数据。
然后我们需要对这些数据进行筛选，首先学生姓名必须是喜羊羊，学生表中的学生id要与成绩表中学生id相同。

mysql> select * from student, score where student.name="喜羊羊" and student.id=score.student_id;
+----+---------+-----------+-------------+------------+-------+------------+-----------+
| id | sn      | name      | email       | classes_id | score | student_id | course_id |
+----+---------+-----------+-------------+------------+-------+------------+-----------+
|  1 | 2020001 | 喜羊羊    | xyy@163.com |          1 |  90.5 |          1 |         1 |
|  1 | 2020001 | 喜羊羊    | xyy@163.com |          1 |  98.5 |          1 |         3 |
|  1 | 2020001 | 喜羊羊    | xyy@163.com |          1 |  93.0 |          1 |         5 |
|  1 | 2020001 | 喜羊羊    | xyy@163.com |          1 |  88.0 |          1 |         6 |
+----+---------+-----------+-------------+------------+-------+------------+-----------+
4 rows in set (0.00 sec)
-- 只保留姓名，成绩，课程编号即可
mysql> select name, score, course_id from student, score where name="喜羊羊" and student.id=score.student_id;
+-----------+-------+-----------+
| name      | score | course_id |
+-----------+-------+-----------+
| 喜羊羊    |  90.5 |         1 |
| 喜羊羊    |  98.5 |         3 |
| 喜羊羊    |  93.0 |         5 |
| 喜羊羊    |  88.0 |         6 |
+-----------+-------+-----------+
4 rows in set (0.00 sec)

再来一个栗子：查询所有同学的总成绩和个人信息。首先肯定是需要对学生表和成绩表进行笛卡尔积并筛选出有效数据，得到这些数据后，我们需要查询每位同学的总分，每位同学的分数是分布在多行的，所以需要使用分组聚合查询，整体思路就是先得到所有同学的有效成绩，再利用聚合查询将每个同学的成绩汇总。至于分组，我们可以按照id分组也可以按照name分组进行聚合查询。

mysql> select name, sum(score) from student, score where id=student_id group by id;
+--------------+------------+
| name         | sum(score) |
+--------------+------------+
| 喜羊羊       |      370.0 |
| 美羊羊       |      359.0 |
| 沸羊羊       |      210.0 |
| 懒羊羊       |      162.0 |
| 花羊羊       |      171.0 |
| 慢羊羊       |      266.0 |
| 小灰灰       |      172.0 |
| 希儿         |      167.0 |
| 布洛尼亚     |      180.0 |
| 光头强       |      151.0 |
| 黑大帅       |      250.0 |
| 开心超人     |      148.0 |
| 刻晴         |      178.0 |
| 可莉         |      208.0 |
| 万叶         |      263.0 |
| 胡桃         |      261.0 |
+--------------+------------+
16 rows in set (0.00 sec)

趁热打铁，继续加大难度，现在需要查询每位同学的成绩，个人信息以及需要将成绩具体对应到哪一门课程。这个问题，我们需要将学生表，分数表，课程表依次进行笛卡尔积运算并筛选出有效的信息。

mysql> select student.name as "姓名", course.name as "课程科目", score as "成绩" from student, score, course where student.id=score.student_id and score.course_id=course.id;
+--------------+--------------------+--------+
| 姓名         | 课程科目           | 成绩   |
+--------------+--------------------+--------+
| 喜羊羊       | Java               |   90.5 |
| 喜羊羊       | 计算机原理         |   98.5 |
| 喜羊羊       | 高等数学           |   93.0 |
| 喜羊羊       | 英语               |   88.0 |
| 美羊羊       | Java               |   88.0 |
| 美羊羊       | 计算机原理         |   78.5 |
| 美羊羊       | 高等数学           |   94.5 |
| 美羊羊       | 英语               |   98.0 |
| 沸羊羊       | Java               |   63.0 |
| 沸羊羊       | 计算机原理         |   68.0 |
| 沸羊羊       | 高等数学           |   79.0 |
| 懒羊羊       | 中国传统文化       |   67.0 |
| 懒羊羊       | 汉语言文学         |   23.0 |
| 懒羊羊       | 英语               |   72.0 |
| 花羊羊       | 中国传统文化       |   81.0 |
| 花羊羊       | 英语               |   90.0 |
| 慢羊羊       | 大学物理           |   87.0 |
| 慢羊羊       | 生物学             |   90.0 |
| 慢羊羊       | 高等数学           |   89.0 |
| 小灰灰       | 中国传统文化       |   80.0 |
| 小灰灰       | 英语               |   92.0 |
| 希儿         | 中国传统文化       |   89.0 |
| 希儿         | 英语               |   78.0 |
| 布洛尼亚     | 大学物理           |   82.0 |
| 布洛尼亚     | 机械制图           |   98.0 |
| 光头强       | 大学物理           |   70.0 |
| 光头强       | 机械制图           |   81.0 |
| 黑大帅       | Java               |   88.0 |
| 黑大帅       | 高等数学           |   90.0 |
| 黑大帅       | 机械制图           |   72.0 |
| 开心超人     | 英语               |   61.0 |
| 开心超人     | 数学分析           |   87.0 |
| 刻晴         | 中国传统文化       |   90.0 |
| 刻晴         | 数学分析           |   88.0 |
| 可莉         | 高等数学           |   58.0 |
| 可莉         | 有机化学           |   72.0 |
| 可莉         | 无机化学           |   78.0 |
| 万叶         | 高等数学           |   90.0 |
| 万叶         | 有机化学           |   82.0 |
| 万叶         | 无机化学           |   91.0 |
| 胡桃         | 中国传统文化       |   99.0 |
| 胡桃         | 高等数学           |   73.0 |
| 胡桃         | 生物学             |   89.0 |
+--------------+--------------------+--------+
43 rows in set (0.00 sec)

使用join语句也是可以的：

select 
	student.name as "姓名", course.name as "课程科目", score as "成绩" 
from 
	student join score on student.id=score.student_id join course on score.course_id=course.id;

??3.3.3外连接

外连接分为左外连接，右外连接，全外连接（SQL不支持），其中左外连接是以左表为主，右外连接以右表为主。
3-3-3
3-3-4

3-3-5
外连接是通过join on关键字来实现的，具体语法如下：

-- 左外连接
select 字段 from 表A left join 表B on 条件 ...;
-- 右外连接
select 字段 from 表A right join 表B on 条件 ...;

我们来看下面的一个例子：

-- 建表A和B
insert into A values (1, "张三"), (2, "李四"), (4, "王五");
insert into B values (1, 98.5), (2, 89.5), (3, 82.0);
-- A， B表数据
+------+--------+
| id   | name   |
+------+--------+
|    1 | 张三   |
|    2 | 李四   |
|    4 | 王五   |
+------+--------+
+------+-------+
| A_id | score |
+------+-------+
|    1 | 98.50 |
|    2 | 89.50 |
|    3 | 82.00 |
+------+-------+

使用左外连接多表查询：

mysql> select * from A left join B on A.id=B.A_id;
+------+--------+------+-------+
| id   | name   | A_id | score |
+------+--------+------+-------+
|    1 | 张三   |    1 | 98.50 |
|    2 | 李四   |    2 | 89.50 |
|    4 | 王五   | NULL |  NULL |
+------+--------+------+-------+
3 rows in set (0.00 sec)

使用右外连接多表查询：

mysql> select * from A right join B on A.id=B.A_id;
+------+--------+------+-------+
| id   | name   | A_id | score |
+------+--------+------+-------+
|    1 | 张三   |    1 | 98.50 |
|    2 | 李四   |    2 | 89.50 |
| NULL | NULL   |    3 | 82.00 |
+------+--------+------+-------+
3 rows in set (0.00 sec)

使用内连接多表查询：

mysql> select * from A join B on A.id=B.A_id;
+------+--------+------+-------+
| id   | name   | A_id | score |
+------+--------+------+-------+
|    1 | 张三   |    1 | 98.50 |
|    2 | 李四   |    2 | 89.50 |
+------+--------+------+-------+
2 rows in set (0.00 sec)

??3.3.4自连接

自连接其实也是多表查询的一种，只不过原来是多张不同的表进行笛卡尔积，自连接是多张相同的表进行笛卡尔积，主要用于将行与行转换为列与列。
语法：

select 字段 from 表A, 表A,... where 条件;

栗子：根据3.3.1-3.3.2的成绩表，查找计算机组成原理成绩高于Java成绩的同学id，其中计算机组成原理课程id为3，Java课程id为1。
首先需要将两个成绩表笛卡尔积，由于两张相同的表存在列同名情况，需要使用表名·.列名来指定是哪一个表的列，对结果保留满足以下条件的数据：

  1. 两表学生id相同。
  2. 使左边的表保留课程id为3的数据，右边的表保留课程id为1的数据。
  3. 上一步保证了左边的成绩是计算机原理，右边的成绩是Java，筛选计算机组成原理成绩大于Java成绩的同学id。

最后将学生id输出即可。

mysql> select * from score as s1, score as s2 where s1.student_id=s2.student_id and s1.course_id=3 and s2.course_id=1 and s1.score>s2.score;
+-------+------------+-----------+-------+------------+-----------+
| score | student_id | course_id | score | student_id | course_id |
+-------+------------+-----------+-------+------------+-----------+
|  98.5 |          1 |         3 |  90.5 |          1 |         1 |
|  68.0 |          3 |         3 |  63.0 |          3 |         1 |
+-------+------------+-----------+-------+------------+-----------+
2 rows in set (0.00 sec)
-- 仅保留学生id
mysql> select s1.student_id from score as s1, score as s2 where s1.student_id=s2.student_id and s1.course_id=3 and s2.course_id=1 and s1.score>s2.score;
+------------+
| student_id |
+------------+
|          1 |
|          3 |
+------------+
2 rows in set (0.00 sec)

??3.3.5子查询

所谓子查询就是将查询语句嵌套，原来需要多部查询的操作转换为一步操作，但是嵌套层数多了，非常容易出错，因为人类的大脑并不擅长同时维护多个变量。

比如，查找花羊羊的同班同学，正常操作是先查询花羊羊所处班级，在根据班级查询花羊羊的同班同学。

-- 分步查询
mysql> select classes_id from student where name="花羊羊";
+------------+
| classes_id |
+------------+
|          2 |
+------------+
mysql> select name from student where classes_id=2;
+-----------+
| name      |
+-----------+
| 懒羊羊    |
| 花羊羊    |
| 小灰灰    |
| 希儿      |
+-----------+
-- 子查询
mysql> select name from student where classes_id=(select classes_id from student where name="花羊羊");
+-----------+
| name      |
+-----------+
| 懒羊羊    |
| 花羊羊    |
| 小灰灰    |
| 希儿      |
+-----------+

当嵌套的查询语句出现多条记录，可以搭配[not]in，[not]exists来使用·，就不多说了，因为子查询的方式比较反人类，工作中用的也不多。

??3.3.6合并查询

使用关键字union和union all能够取得两个结果集的并集，这两个合并查询关键字不同点就是带all的不会对结果去重，而不带all的会对结果去重，与or不相同的一点的是合并查询能够在多表进行查询得到并集，而or只能在单表中查询得到并集。
总的来说，合并查询能够查询不仅能够查询单表中两个结果的并集，也能查询多表中两个结果的并集，而or只能用于单表查询。
语法格式：

-- 去重合并查询
select 字段 from 表A where 条件 union select 字段 from 表B where 条件;
-- 不去重合并查询
select 字段 from 表A where 条件 union all select 字段 from 表B where 条件;

表A与表B可以相同。

例如查询课程名为高等数学或者课程id大于等于10的课程名称。

-- 合并查询
mysql> select * from course where name="高等数学" union select * from course where id>=10;
+----+--------------+
| id | name         |
+----+--------------+
|  5 | 高等数学     |
| 10 | 数学分析     |
| 11 | 无机化学     |
| 12 | 机械制图     |
+----+--------------+
4 rows in set (0.00 sec)
-- or
mysql> select * from course where name="高等数学" or id>=10;
+----+--------------+
| id | name         |
+----+--------------+
|  5 | 高等数学     |
| 10 | 数学分析     |
| 11 | 无机化学     |
| 12 | 机械制图     |
+----+--------------+
4 rows in set (0.00 sec)

到这里数据库的约束和数据表的一些稍微难一点的操作就全部介绍完毕了，最重要的还是实践，多写一些实例，多敲一些代码，这些知识完全是可以熟练掌握的。好了，内容就到这里了，下次见！

修订补充时间：2022年3月21日

补充1： SQL查询中各个关键字的执行先后顺序： from > on> join > where > group by > with > having >select > distinct > order by > limit
补充2： 外键约束语法补充

-- 建表时创建外键
constraint 外键名 foreign key (子表字段) references 父表名(父表字段);
-- 建表是创建多个外键约束
foreign key (子表字段) references 父表名(父表字段), foreign key (子表字段) references 父表名(父表字段), ...;
-- 建表后创建外键
alter table 子表名 add constraint 外键名 foreign key (子表字段) references 父表名(父表字段);
-- 删除外键
alter table 子表名 drop foreign key 外键约束名;

CSDN社区 《原创标兵》活动，只要参与其中并发布原创就有机会获得官方奖品：精品日历、新程序员杂志，快来参与吧！链接直达 https://bbs.csdn.net/topics/605387592

觉得文章写得不错的老铁们，点赞评论关注走一波！谢谢啦！

1-99

技术布道师
关注
专栏目录
MySQL数据库数据操作实战与进阶
m0_50542140的博客
06-23 751
在这篇文章中，我们将介绍如何使用MySQL数据库进行更加完善的数据操作。MySQL是一个广泛使用的开源关系型数据库管理系统，它具有高性能、高可靠性和易用性等特点。通过学习本文，你将掌握如何创建表、插入数据、查询数据、更新数据和删除数据等基本操作，并了解一些高级功能，如索引、事务和存储过程等。还有继续深入探讨MySQL数据库的数据操作，包括关联查询、联合查询、子查询等高级查询技巧，以及如何优化查询性能、管理数据库事务、使用视图和触发器等。通过学习这些内容，你将能够更加高效地处理复杂的数据场景。
MYSQL数据库进阶篇
weixin_45044818的博客
07-09 774
体系结构连接层、服务层、引擎层、存储层存储引擎特点：InnoDB和MyISAM： 事务、外键、行锁存储引擎应用InnoDB: 存储业务系统对于事务、数据完整性要求较高的核心数据。MyISAM： 存储业务系统的非核心业务。
MySQL数据库入门到精通——进阶篇（1）
这里有一只小马吖
09-30 1533
黑马程序员 MySQL数据库入门到精通——进阶篇（1）
【MySQL进阶】数据库与表的创建和管理
zhangxia_的博客
08-03 2062
存储数据是处理数据的第一步 ，只有正确地把数据存储起来，我们才能进行有效的处理和分析。否则，只能是一团乱麻，无从下手。那么，怎样才能把用户各种经营相关的、纷繁复杂的数据，有序、高效地存储起来呢？在 MySQL 中，一个完整的数据存储过程总共有 4 步，分别是创建数据库、确认字段、创建数据表、插入数据。本文详细介绍了数据库以及数据库表的创建和管理。 ...
MySQL数据查询--进阶操作（超详细）！！
2301_80133875的博客
09-10 784
聚合查询，联合查询，内外连接，子查询，合并查询爽歪歪
【MySQL】MySQL数据库的进阶使用
The porters of knowledge
07-21 555
对表的CURD操作，聚合函数，group by、having、order by，limit等关键字的使用，MySQL内置函数，MySQL多表查询，子查询，内连接，左外连接，右外连接，
【MySQL】数据库约束与聚合查询和联合查询等进阶操作知识汇总
qq_68993495的博客
12-20 1048
本章知识点: 数据库约束, 主键, 外键, 数据库如何设计? 聚合查询, 多表查询, 笛卡尔积的使用等基础知识汇总, 很想对你有帮助!
MySQL~数据库约束、表的设计以及进阶版插入
qq_58284486的博客
04-18 660
目录 数据库约束 NULL约束 UNIQUE 唯一约束 DEFAULT 默认约束值 PRIMARY KEY 主键约束 FOREIGN KEY 外键约束 表的设计 一对一 一对多 多对多 新增(插入)进阶 数据库约束 约束即按照一定条件进行规范的做事，表定义的时候某些字段保存的数据，需要按照一定的约束条件去定义. NULL约束 · NOT NULL：指示某列不能存储NULL值 创建表时，可以指定某列不为空 -- NULL约束 drop table if ex...
MySQL数据库学习教程从入门到进阶全套笔记
08-06
MySQL数据库是世界上最流行的开源关系型数据库管理系统之一，广泛应用于各种规模的应用中，从个人项目到大型企业级系统。本教程涵盖了MySQL从基础到高级的全方位学习，旨在帮助初学者快速入门并逐步提升技能。 首先...
【MySQL进阶之路丨第八篇】MySQL数据的插入及查询
热门推荐
等风来
10-08 1万+
在MySQL数据库中，使用 INSERT INTO 语句来插入数据。而INSERT INTO语法有两种常见的格式，是需要我们掌握的：1.插入单行数据：INSERT INTO 表名 (列1, 列2, 列3, ...) VALUES (值1, 值2, 值3, ...);例如，要向名为 “users” 的表中插入一条记录，包含id、name和email字段：INSERT INTO users (id, name, email) VALUES (1, 'John', 'john@example.
MySQL数据库进阶操作
weixin_51725822的博客
02-27 309
MySQL数据库进阶操作一、克隆表方法一方法二二、清空表，删除表内的所有数据方法一方法二三、创建临时表四、创建外键约束 在学习进阶操作前，我们需要首先安装MySQL数据库，创建库和表 在前面的《数据库基本概念》一文中，我们讲了MySQL的一键部署，在这里我们就不再赘述了。 我们在安装完MySQL后，需要创建数据库和表。 create database school; use school; create table student(id int not null,name char(20) not nul
MySQL数据库进阶操作一（克隆表、清空表、创建临时表及外键约束）
weixin_51613313的博客
01-27 560
MySQL准备工作一、克隆表,将数据表的数据记录生成到新的表中二、清空表,删除除表内的所有数据三、创建临时表四、创建外键约束，保证数据的完整性和一致性。 准备工作 一、克隆表,将数据表的数据记录生成到新的表中 方法一: create table TEST02 like TEST01; #通过 LIKE 方法，复制KY08表结构生成 test01表 insert into test02 select * from TET01; #修改表 TEST02 ,使其从表 TEST01 中获取字
《JavaEE进阶》----15.＜Spring Boot 日志＞
m0_73456341的博客
09-11 1476
本篇文章将记录我学习SpringBoot日志 1.日志文件的用途 2.SpringBoot日志文件的配置 3.用lombook依赖引入@Slf4j注解，从而引入log对象。方便我们打印日志。
C++ STL中sort函数
最新发布
weixin_43349440的博客
09-15 353
刷题中经常遇到，用于各种元素排序，可以自定义排序方式，vector、deque适用。详细讲解：https://zhuanlan.zhihu.com/p/36274119。，为避免QuickSort快排的递归调用带来过大的额外负荷，就改用。一旦分段后的数据量小于某个门槛。STL的sort算法，
C语言 12 函数
天河有尽后为涯，星海无边前作岸。
09-13 1132
程序的入口点就是main函数，只需要将程序代码编写到主函数中就可以运行了，不过这个函数只是由我们来定义，而不是我们来调用。当然，除了主函数之外，一直在使用的printf那么，函数的具体定义是什么呢？函数是完成特定任务的独立程序代码单元。每次要做这个任务时，都要完完整整地将任务的每一行代码都写下来，如果程序中多处都需要执行这个任务，每个地方都完整地写一遍，实在是太臃肿了，有没有一种更好的办法能优化代码呢？
C++与QML的数据交互
qq_50679120的博客
09-11 545
这里没什么好说的，在C++中组合好json后，通过信号发送到qml中，qml中是可以解析json的。通过Q_PROPERTY属性，可以轻松地在QML中读取和修改数据，并将其更新到C++中，先创建一个类。在QML接收，直接通过data.can_id这样的形式去访问结构体中的元素。这两个也没什么好说的，在C++中直接发送信号就好了，在C++中声明并注册一个结构体。然后发送信号到QML中。
CF C. Nikita and LCM
qq_74190237的博客
09-13 291
思维题.首先先对数组排序,考虑一种特殊情况,那就是如果数组最大值不能被其他数整除,这代表什么?代表其他数里面有某个质因子,最大值里面没有或者数量不管,所有最小公倍数都会大于最大值.那么其他情况的话,就可以确定所有的数都是最大数的因子,那么形成的最小公倍数也一定是最大数的因子,那么就可以枚举一下最大数的因子,就可以了.给长度为n的数组,求出最长的特殊子数组,如果子数组的最小公倍数不在原数组中,那么就是特殊的.
MySQL数据库操作指南：创建、选择与数据表管理
本资源是一份关于MySQL数据库操作的PPT，主要讲解了在MySQL环境中进行数据库和数据表管理的基本步骤。内容详尽且实用，适合初学者和进阶用户参考。 首先，文档详细介绍了数据库服务的管理，包括如何开启（使用...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值