学校网站的常规化建设,营销型网站是什么,seo点击排名软件营销工具,网站建设与管理作业✨个人主页#xff1a;bit me#x1f447; ✨当前专栏#xff1a;MySQL数据库#x1f447; ✨算法专栏#xff1a;算法基础#x1f447; ✨每日一语#xff1a;悟已往之不谏#xff0c;知来者之可追。实迷途其未远#xff0c;觉今是而昨非。 目 录#x1f384;一. 数… ✨个人主页bit me ✨当前专栏MySQL数据库 ✨算法专栏算法基础 ✨每日一语悟已往之不谏知来者之可追。实迷途其未远觉今是而昨非。 目 录一. 数据库约束二. 表的设计三. 新增四. 查询️4.1 聚合函数以下都是聚合查询4.2 分组查询4.3 内连接以下都是联合查询4.4 外连接️4.5 自连接☠️4.6 子查询️⚧️4.7 合并查询一. 数据库约束
约束就是数据库可以让程序猿定义一些对数据的限制规则数据库会在插入 / 修改数据的时候按照这些规则对数据进行校验如果校验不通过就直接报错。
约束的本质就是让我们及时发现数据中的错误更好的保证数据的正确性
约束类型
NOT NULL - 指示某列不能存储 NULL 值。必填项UNIQUE - 保证某列的每行必须有唯一的值。DEFAULT - 规定没有给列赋值时的默认值。可以通过default修改这个默认值PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列或两个列多个列的结合有唯一标识有助于更容易更快速地找到表中的一个特定的记录。FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。CHECK - 保证列中的值符合指定的条件。对于MySQL数据库对CHECK子句进行分析但是忽略CHECK子句
①NOT NULL
初始情况下一个表允许为NULL。
mysql desc student;
-----------------------------------------------
| Field | Type | Null | Key | Default | Extra |
-----------------------------------------------
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
-----------------------------------------------
2 rows in set (0.00 sec)mysql insert into student values(NULL, NULL);
Query OK, 1 row affected (0.00 sec)加上 NOT NULL 约束之后就不再允许插入空值 mysql drop table student;
Query OK, 0 rows affected (0.01 sec)mysql create table student (id int not null, name varchar(20));
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) | YES | | NULL | |
-----------------------------------------------
2 rows in set (0.00 sec)再尝试插入空值就会报错
mysql insert into student values (NULL, NULL);
ERROR 1048 (23000): Column id cannot be null②UNIQUE 唯一性
默认情况下表里的数据都是可以重复的
mysql drop table student;
Query OK, 0 rows affected (0.01 sec)mysql create table student (id int, name varchar(20));
Query OK, 0 rows affected (0.01 sec)mysql insert into student values(1, 张三);
Query OK, 1 row affected (0.00 sec)mysql insert into student values(1, 张三);
Query OK, 1 row affected (0.00 sec)mysql insert into student values(1, 张三);
Query OK, 1 row affected (0.00 sec)mysql select * from student;
--------------
| id | name |
--------------
| 1 | 张三 |
| 1 | 张三 |
| 1 | 张三 |
--------------
3 rows in set (0.00 sec)没有约束的表和有约束的表对比
mysql desc student;
-----------------------------------------------
| Field | Type | Null | Key | Default | Extra |
-----------------------------------------------
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
-----------------------------------------------
2 rows in set (0.00 sec)mysql drop table student;
Query OK, 0 rows affected (0.01 sec)mysql create table student (id int unique, name varchar(20));
Query OK, 0 rows affected (0.01 sec)mysql desc student;
-----------------------------------------------
| Field | Type | Null | Key | Default | Extra |
-----------------------------------------------
| id | int(11) | YES | UNI | NULL | |
| name | varchar(20) | 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(1, 张三);
ERROR 1062 (23000): Duplicate entry 1 for key idDuplicate -- 重复 entry -- 条目不是入口 触发了 UNIQUE 约束 ③DEFAULT
设定默认值 默认的默认值是NULL可以通过 default 约束来修改这里的默认值的取值 mysql drop table student;
Query OK, 0 rows affected (0.01 sec)mysql create table student (id int, name varchar(20));
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 | | NULL | |
-----------------------------------------------
2 rows in set (0.00 sec)对表进行指定列插入的时候会涉及到默认值的情况的 mysql insert into student (id) values (1);
Query OK, 1 row affected (0.00 sec)指定了 id 这一列插入此时的 name 就是按照默认值来走的 mysql select * from student;
------------
| id | name |
------------
| 1 | NULL |
------------
1 row in set (0.00 sec)通过 default 约束来修改默认值 mysql drop table student;
Query OK, 0 rows affected (0.00 sec)mysql create table student (id int default 0, 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 | | 0 | |
| 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)mysql insert into student (name) values (张三);
Query OK, 1 row affected (0.00 sec)mysql select * from student;
-----------------
| id | name |
-----------------
| 1 | 未命名 |
| 0 | 张三 |
-----------------
2 rows in set (0.00 sec)④PRIMARY KEY -- 主键
在设计一个表的时候非常重要的一个列表示一条记录的身份标识用来区分这条记录和别的记录的
注 不能为空相当于 NOT NULL不能重复相当于UNIQUE一个表里只能有一个主键 mysql drop table student;
Query OK, 0 rows affected (0.00 sec)mysql create table student (id int primary key,name varchar(20));
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(20) | YES | | NULL | |
-----------------------------------------------
2 rows in set (0.00 sec)添加元素
mysql insert into student values(null, null);
ERROR 1048 (23000): Column id cannot be null
mysql insert into student values(1,张三);
Query OK, 1 row affected (0.00 sec)mysql insert into student values(1,张三);
ERROR 1062 (23000): Duplicate entry 1 for key PRIMARY主键不能为空主键的值也不能重复 由于主键必须要填还不能重复MySQL为了方便大家填写主键内置了一个功能 ”自增主键“ 帮助我们自动生成主键的值就不用程序猿自己来保证了
mysql drop table 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.01 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)当前 id 是自增主键不同于主键可以使用 null
mysql insert into student values (null,张三);
Query OK, 1 row affected (0.00 sec)如果写的是具体数值就是手动指定 id如果写的是 null 就是让 MySQL 按照自增主键自动生成 mysql select * from student;
------------
| id | name |
------------
| 1 | 张三 |
------------
1 row in set (0.00 sec)mysql insert into student values (null,张三);
Query OK, 1 row affected (0.00 sec)mysql insert into student values (null,张三);
Query OK, 1 row affected (0.00 sec)mysql insert into student values (null,张三);
Query OK, 1 row affected (0.00 sec)mysql select * from student;
------------
| id | name |
------------
| 1 | 张三 |
| 2 | 张三 |
| 3 | 张三 |
| 4 | 张三 |
------------
4 rows in set (0.00 sec)中间插入手动增加之后自增从当前的数字自增
mysql insert into student values (100,张三);
Query OK, 1 row affected (0.00 sec)mysql select * from student;
-------------
| id | name |
-------------
| 1 | 张三 |
| 2 | 张三 |
| 3 | 张三 |
| 4 | 张三 |
| 100 | 张三 |
-------------
5 rows in set (0.00 sec)mysql insert into student values (null,张三);
Query OK, 1 row affected (0.00 sec)mysql select * from student;
-------------
| id | name |
-------------
| 1 | 张三 |
| 2 | 张三 |
| 3 | 张三 |
| 4 | 张三 |
| 100 | 张三 |
| 101 | 张三 |
-------------
6 rows in set (0.00 sec)MySQL 想要自增必须要能够记录下来当前 id 已经到哪了还要保证自增后得是不能重复的。MySQL里简单粗暴的做法直接就是记录当前 自增主键 里的最大值这就能保证自增速度很快并且一定是不重复的可能会觉得有数据库浪费了但是数据库一般而言是很大的一般不考虑这个浪费 自增主键主要就是用来生成一个唯一的 id 来保证不重复如果数据库是分布式部署这个时候自增主键就要带来问题。
在一个MySQL集群里有许多的MySQL节点多个节点上的数据放在一起才是一个完整的数据集合MySQL生成自增主键的时候可以保证在自己这个节点上生成的 id 是唯一的但是无法保证这个 id 在其他节点上也是唯一的。
生成一个 ID 这样的需求是客观存在的但是 MySQL 自增主键已经难以满足要求了
一个典型的解决方法在生成 id 的时候让这些节点相互协商一下彼此了解了对方的情况之后就能生成唯一的 id 了但是代价有点大。为了轻量高效又出了另外一个方法唯一 id 时间戳(ms) 机房编号/主机编号 随机因子 这里的 不是算术运算 而是字符串拼接进入数据库的数据有先有后形成时间戳并且会分摊到不同的主机上就算是一个主机又会生成随机数的也有极端情况三种一模一样理论上存在但是工程上是忽略不计的
⑤FOREIGN KEY
描述了两张表之间的关联关系
举例 班级表是负责约束的一方称为父表学生表是被约束的一方称为子表 mysql drop table student;
Query OK, 0 rows affected (0.01 sec)mysql create table class (classId int primary key,className varchar(20));
Query OK, 0 rows affected (0.01 sec)mysql create table student (studentId int primary key, name varchar(20), classId int, foreign key (classId) references class (classId));
Query OK, 0 rows affected (0.01 sec)mysql
mysql desc student;
---------------------------------------------------
| Field | Type | Null | Key | Default | Extra |
---------------------------------------------------
| studentId | int(11) | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
| classId | int(11) | YES | MUL | NULL | |
---------------------------------------------------
3 rows in set (0.00 sec)第一句为父表第二句为子表先把所有的列都定义完逗号然后再写外键约束 在父表为空的情况下直接尝试往子表插入就会报错
mysql insert into student values(1,张三,1);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (java.student, CONSTRAINT student_ibfk_1 FOREIGN KEY (classId) REFERENCES class (classId))在父表里面添加一些数据
mysql insert into class values (1,java101);
Query OK, 1 row affected (0.00 sec)mysql insert into class values (2,java102);
Query OK, 1 row affected (0.00 sec)mysql insert into class values (3,java103);
Query OK, 1 row affected (0.00 sec)mysql select * from class;
--------------------
| classId | className |
--------------------
| 1 | java101 |
| 2 | java102 |
| 3 | java103 |
--------------------
3 rows in set (0.00 sec)往子表插入数据
mysql insert into student values(1,张三,4);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (java.student, CONSTRAINT student_ibfk_1 FOREIGN KEY (classId) REFERENCES class (classId))依旧报错插入子表的数据没有在父表的 classId 中同样也是报的刚才的错误 在外键的约束下每次你插入 / 修改操作都会先触发在父表中的查询父表中存在才能插入 / 修改成功否则就会失败。 mysql insert into student values(1,张三,1);
Query OK, 1 row affected (0.00 sec)每次插入都要先查询会拖慢执行效率但是也没有特别拖慢 如果查询操作触发了遍历表低效的如果触发了索引相对于快不少
建立外键约束的时候MySQL 就要求引用的父表的列必须是主键或者UNIQUE自带索引查询速度就会快一些
父表对子表产生了限制但是反过来子表也会对父表产生限制
父表对子表的限制是不能随意插入 / 修改 子表对于父表的限制就是不能随意修改 / 删除
mysql select * from student;
----------------------------
| studentId | name | classId |
----------------------------
| 1 | 张三 | 1 |
----------------------------
1 row in set (0.00 sec)mysql delete from class where classId 1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (java.student, CONSTRAINT student_ibfk_1 FOREIGN KEY (classId) REFERENCES class (classId))由于在子表中引入了 classId 1 的记录尝试删除父表中的对应记录发现这个就难以删除 ⑥ CHECK了解
直接对表中的值做出限制
check (sex 男 or sex女)例如限制性别这一列只能是 男 或 女 二. 表的设计
根据一些实际的业务场景来设计表主要就是确定有几个表每个表干啥每个表有多少个字段。 面试官你的项目里的数据库是如何设计的 其实是让你回答你的数据库里有几个表每个表是干啥的以及表里有哪些字段 那我们该如何设计它呢
我们需要明确需求场景提取出需求中的 实体 实体可以认为是关键性名词一般来说每个实体都会分配一个表来进行表示除了实体之外还需要理清楚实体和实体之间的关系。
1. 一对一 2. 一对多 3. 多对多 4. 没关系 例如一个学校的教务系统 有学生账号课程班级 一个学生只能有一个账号一个账号只能分配给一个学生一对一一个同学只属于一个班级一个班级包含多个学生一对多一个学生可以选择多门课程一个课程可以包含多个学生多对多 不同的关系在设计表的时候有不同的套路对应的设计表的套路 一对一
1.把学生和账号直接放在一个表里
student (id, name, account, password…) 这种设计方式是下册尤其是一个系统中包含不同身份角色的时候 2.把学生和账号各自放到一个表里使用一个额外的 id 来关联
student (studentId, name, accountId) account (accountId, password) 或 student (studentId, name) account (accountId, password,studentId)
一对多
方法1 student (id, name) 1, 张三 2, 李四 3, 王五 class (classId, className, studentList) 1 Java101 12 2 Java102 3
方法2:
class (classId, className 1, java101 2, java102
student (id, name, classId) 1 张三 1 2 李四 1 3 王五 2 因为 MySQL 中没有 数组 这样的类型第一种方案是无法实现的可以用字符串拼接的方式凑合着来实现但是实际上是不好设计的比较低效也失去了数据库对于数据校验的一些能力 多对多 一般就是采用个中间表俩表示多对多的关系 student (studentId, name) 1 张三 2 李四 3 王五
course (courseId, courseName) 1 语文 2 数学 3 英语
student_course (studentId, courseId) 1 1 (张三选了语文课) 1 2 (张三选了数学课) 2 1 (李四选了语文课)
数据库设计要点 找出实体描述清楚实体之间的关系代入固定套路即可 如果需求场景比较简单很容易理清其中的实体关系 如果需求场景比较复杂可能涉及到很多实体会很乱因此程序猿发明了一个工具 – ER 图实体 – 关系图
关联之后字段名相同是不会有影响的
mysql select * from class;
--------------------
| classId | className |
--------------------
| 1 | java101 |
| 2 | java102 |
| 3 | java103 |
--------------------
3 rows in set (0.00 sec)mysql select * from student;
----------------------------
| studentId | name | classId |
----------------------------
| 1 | 张三 | 1 |
----------------------------
1 row in set (0.00 sec)例如这俩张表里都有 classId 但是详细讲就是 class.classId 和 student.classId 三. 新增
把查询结果作为新增的数据 insert into 表名1 select 列名 from 表名2; 先执行查询操作查询出来的结果插入到另外一个表里保证查询结果的临时表的列要和插入的表的列匹配 要求从表名 2 中查询出来的结果的列数和类型 和表名 1 匹配不要求列名匹配 mysql create table student (id int, name varchar(20));
Query OK, 0 rows affected (0.01 sec)mysql insert into student values (1,张三),(2,李四),(3,王五);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0mysql create table student2 (id int, name varchar(20));
Query OK, 0 rows affected (0.01 sec)mysql select * from student;
--------------
| id | name |
--------------
| 1 | 张三 |
| 2 | 李四 |
| 3 | 王五 |
--------------
3 rows in set (0.00 sec)mysql select * from student2;
Empty set (0.00 sec)mysql insert into student2 select * from student;
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0mysql select * from student2;
--------------
| id | name |
--------------
| 1 | 张三 |
| 2 | 李四 |
| 3 | 王五 |
--------------
3 rows in set (0.00 sec)四. 查询 聚合查询 通过行和行之间进行的运算处理 表达式查询是列和列之间 ️4.1 聚合函数以下都是聚合查询
函数说明COUNT([DISTINCT] expr)返回查询到的数据的 数量SUM([DISTINCT] expr)返回查询到的数据的 总和不是数字没有意义AVG([DISTINCT] expr)返回查询到的数据的 平均值不是数字没有意义MAX([DISTINCT] expr)返回查询到的数据的 最大值不是数字没有意义MIN([DISTINCT] expr)返回查询到的数据的 最小值不是数字没有意义
这些都属于 SQL 内置函数SQL 作为一个编程语言也是内置了一些库函数供我们来使用的
COUNT函数的使用
mysql select * from exam_result;
-----------------------------------------
| id | name | chinese | math | english |
-----------------------------------------
| 1 | 唐三藏 | 67.0 | 98.0 | 56.0 |
| 2 | 孙悟空 | 87.5 | 78.0 | 77.0 |
| 3 | 猪悟能 | 88.0 | 98.5 | 90.0 |
| 4 | 曹孟德 | 82.0 | 84.0 | 67.0 |
| 5 | 刘玄德 | 55.5 | 85.0 | 45.0 |
| 6 | 孙权 | 70.0 | 73.0 | 78.5 |
| 7 | 宋公明 | 75.0 | 65.0 | 30.0 |
-----------------------------------------
7 rows in set (0.00 sec)mysql select count(*) from exam_result;
----------
| count(*) |
----------
| 7 |
----------
1 row in set (0.00 sec)mysql select count(name) from exam_result;
-------------
| count(name) |
-------------
| 7 |
-------------
1 row in set (0.00 sec)那么其中使用 * 和 name(列名) 有什么区别呢
mysql insert into exam_result values(null, null, null, null, null);
Query OK, 1 row affected (0.00 sec)mysql select * from exam_result;
-----------------------------------------
| id | name | chinese | math | english |
-----------------------------------------
| 1 | 唐三藏 | 67.0 | 98.0 | 56.0 |
| 2 | 孙悟空 | 87.5 | 78.0 | 77.0 |
| 3 | 猪悟能 | 88.0 | 98.5 | 90.0 |
| 4 | 曹孟德 | 82.0 | 84.0 | 67.0 |
| 5 | 刘玄德 | 55.5 | 85.0 | 45.0 |
| 6 | 孙权 | 70.0 | 73.0 | 78.5 |
| 7 | 宋公明 | 75.0 | 65.0 | 30.0 |
| NULL | NULL | NULL | NULL | NULL |
-----------------------------------------
8 rows in set (0.00 sec)mysql select count(*) from exam_result;
----------
| count(*) |
----------
| 8 |
----------
1 row in set (0.00 sec)mysql select count(name) from exam_result;
-------------
| count(name) |
-------------
| 7 |
-------------
1 row in set (0.00 sec)可以看到我们再添加一个全为空的信息* 把 NULL 值也记录到行数中了name(列名) 对于 NULL 不会计数。
注 count(name) 中间不能带有空格否则就会出错SQL 不能被正常解析 SUM函数的使用
mysql select * from exam_result;
-----------------------------------------
| id | name | chinese | math | english |
-----------------------------------------
| 1 | 唐三藏 | 67.0 | 98.0 | 56.0 |
| 2 | 孙悟空 | 87.5 | 78.0 | 77.0 |
| 3 | 猪悟能 | 88.0 | 98.5 | 90.0 |
| 4 | 曹孟德 | 82.0 | 84.0 | 67.0 |
| 5 | 刘玄德 | 55.5 | 85.0 | 45.0 |
| 6 | 孙权 | 70.0 | 73.0 | 78.5 |
| 7 | 宋公明 | 75.0 | 65.0 | 30.0 |
| NULL | NULL | NULL | NULL | NULL |
-----------------------------------------
8 rows in set (0.01 sec)mysql select sum(math) from exam_result;
-----------
| sum(math) |
-----------
| 581.5 |
-----------
1 row in set (0.00 sec)在我们之前学过NULL 和其他数字进行算数运算结果还是 NULL在这里可以得出结论SUM函数中 NULL 没有参加运算
此处聚合查询也是完全可以指定筛选条件的
mysql select sum(english) from exam_result where english 60;
--------------
| sum(english) |
--------------
| 131.0 |
--------------
1 row in set (0.00 sec)mysql select sum(english) from exam_result;
--------------
| sum(english) |
--------------
| 443.5 |
--------------
1 row in set (0.00 sec)对于带有条件的聚合查询先会按照条件进行筛选筛选后得到的结果进行聚合
对名字进行求和
mysql select sum(name) from exam_result;
-----------
| sum(name) |
-----------
| 0 |
-----------
1 row in set, 7 warnings (0.00 sec)名字是字符串无法求和所以 sum 函数只能对数字进行求和 AVG函数的使用
mysql select avg(math) from exam_result;
-----------
| avg(math) |
-----------
| 83.07143 |
-----------
1 row in set (0.00 sec)此处通过计算也可以得出平均值的计算是不会计入 NULL 值的个数的 MAX 和 MIN 函数的使用
mysql select max(math) from exam_result;
-----------
| max(math) |
-----------
| 98.5 |
-----------
1 row in set (0.00 sec)mysql select min(math) from exam_result;
-----------
| min(math) |
-----------
| 65.0 |
-----------
1 row in set (0.00 sec)4.2 分组查询
把表中的若干行分成好几组指定某一列作为分组的依据分组依据的列值相同则被归为一组分成多个组之后还可以针对每个组分别使用聚合函数。 group by 列 建一张表
mysql create table student (id int, name varchar(20), gender varchar(20), score int);
Query OK, 0 rows affected (0.01 sec)mysql insert into student values (1, 张三, 男, 95);
Query OK, 1 row affected (0.00 sec)mysql insert into student values (2, 李四, 女, 75);
Query OK, 1 row affected (0.00 sec)mysql insert into student values (3, 王五, 男, 85);
Query OK, 1 row affected (0.00 sec)mysql insert into student values (4, 赵六, 女, 65);
Query OK, 1 row affected (0.00 sec)mysql select * from student;
-----------------------------
| id | name | gender | score |
-----------------------------
| 1 | 张三 | 男 | 95 |
| 2 | 李四 | 女 | 75 |
| 3 | 王五 | 男 | 85 |
| 4 | 赵六 | 女 | 65 |
-----------------------------
4 rows in set (0.00 sec)需要统计男生和女生分别各自的最高分平均分最低分
mysql select gender, max(score), min(score), avg(score) from student group by gender;
--------------------------------------------
| gender | max(score) | min(score) | avg(score) |
--------------------------------------------
| 女 | 75 | 65 | 70.0000 |
| 男 | 95 | 85 | 90.0000 |
--------------------------------------------
2 rows in set (0.00 sec)分组规则是把记录的值相同的行作为一组 在进行聚合查询的时候也能指定条件筛选 1. 在聚合之前进行筛选针对筛选后的结果再聚合。where 子句2. 在聚合之后进行筛选。having 子句 聚合之前查询查询每个性别平均分但是出去赵六同学
mysql select gender, avg(score) from student where name ! 赵六 group by gender;
--------------------
| gender | avg(score) |
--------------------
| 女 | 75.0000 |
| 男 | 90.0000 |
--------------------
2 rows in set (0.00 sec)聚合之后筛选查询平均分大于 80 的性别情况需要先把平均分算出来算平均分需要先聚合
mysql select gender, avg(score) from student group by gender having avg(score) 80;
--------------------
| gender | avg(score) |
--------------------
| 男 | 90.0000 |
--------------------
1 row in set (0.00 sec)同时在前面和后面都筛选查询平均分大于 80 的性别情况除去赵六
mysql select gender, avg(score) from student where name ! 赵六 group by gender having avg(score) 80;
--------------------
| gender | avg(score) |
--------------------
| 男 | 90.0000 |
--------------------
1 row in set (0.00 sec)聚合查询的执行过程先按照 where 条件进行筛选记录把筛选出来的结果按照 group by 来分组分组之后按照 having 再进行筛选最后再按照制定列中调用的聚合函数来显示计算结果。 联合查询
也叫多表查询是把多个表的记录合并到一起综合进行查询 select * from 表1, 表2; 联合查询中的核心概念笛卡尔积。
笛卡尔积就是把这两个表中的所有记录进行排列组合穷举出所有的可能情况
排列组合之后的结果就是笛卡尔积。笛卡尔积的列数就是原来俩张表的列数之和笛卡尔积的行数就是原来俩张表的行数之和。
针对多个表进行联合查询本质上就是先针对多个表进行笛卡尔积运算。万一原来的表就很大再进行多个表的笛卡尔积就会得到一个更复杂的表这个过程就会很低效因此在实际开发中进行多表查询一定要克制。
笛卡尔积中包含了大量无效数据指定了合理的过滤条件把有效的数据挑出来这个时候就得到了一个非常有用的数据表这个过程就是 联合查询 的过程。
多表查询的一般步骤 1. 先根据需求理清楚说想要的数据都在哪些表中2. 【核心操作】先针对多个表进行笛卡尔积3. 根据连接条件筛选出合法数据过滤掉非法数据4. 进一步增加条件根据需求做更精细的筛选5. 去掉不必要的列保留最关注的信息 此处创建四个表
mysql create table classes (id int primary key auto_increment, name varchar(20), desc varchar(100));
Query OK, 0 rows affected (0.01 sec)mysql create table student (id int primary key auto_increment, sn varchar(20), name varchar(20), qq_mail varchar(20), classes_id int);
Query OK, 0 rows affected (0.01 sec)mysql create table course (id int primary key auto_increment, name varchar(20));
Query OK, 0 rows affected (0.01 sec)mysql create table score (score decimal(3,1),student_id int, course_id int);
Query OK, 0 rows affected (0.01 sec)填入数据
mysql insert into classes(name, desc) values- (计算机系2019级1班, 学习了计算机原理、C和Java语言、数据结构和算法),- (中文系2019级3班,学习了中国传统文学),- (自动化2019级5班,学习了机械自动化);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0mysql insert into student(sn, name, qq_mail, classes_id) values- (09982,黑旋风李逵,xuanfengqq.com,1),- (00835,菩提老祖,null,1),- (00391,白素贞,null,1),- (00031,许仙,xuxianqq.com,1),- (00054,不想毕业,null,1),- (51234,好好说话,sayqq.com,2),- (83223,tellme,null,2),- (09527,老外学中文,foreignerqq.com,2);
Query OK, 8 rows affected (0.00 sec)
Records: 8 Duplicates: 0 Warnings: 0mysql insert into course(name) values- (Java),(中国传统文化),(计算机原理),(语文),(高阶数学),(英文);
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0mysql insert into score(score, student_id, course_id) values- -- 黑旋风李逵- (70.5, 1, 1),(98.5, 1, 3),(33, 1, 5),(98, 1, 6),- -- 菩提老祖- (60, 2, 1),(59.5, 2, 5),- -- 白素贞- (33, 3, 1),(68, 3, 3),(99, 3, 5),- -- 许仙- (67, 4, 1),(23, 4, 3),(56, 4, 5),(72, 4, 6),- -- 不想毕业- (81, 5, 1),(37, 5, 5),- -- 好好说话- (56, 6, 2),(43, 6, 4),(79, 6, 6),- -- tellme- (80, 7, 2),(92, 7, 6);
Query OK, 20 rows affected (0.00 sec)
Records: 20 Duplicates: 0 Warnings: 0打印四个表
mysql select * from student;
----------------------------------------------------------
| id | sn | name | qq_mail | classes_id |
----------------------------------------------------------
| 1 | 09982 | 黑旋风李逵 | xuanfengqq.com | 1 |
| 2 | 00835 | 菩提老祖 | NULL | 1 |
| 3 | 00391 | 白素贞 | NULL | 1 |
| 4 | 00031 | 许仙 | xuxianqq.com | 1 |
| 5 | 00054 | 不想毕业 | NULL | 1 |
| 6 | 51234 | 好好说话 | sayqq.com | 2 |
| 7 | 83223 | tellme | NULL | 2 |
| 8 | 09527 | 老外学中文 | foreignerqq.com | 2 |
----------------------------------------------------------
8 rows in set (0.00 sec)mysql select * from classes;
------------------------------------------------------------------------------------------------
| id | name | desc |
------------------------------------------------------------------------------------------------
| 1 | 计算机系2019级1班 | 学习了计算机原理、C和Java语言、数据结构和算法 |
| 2 | 中文系2019级3班 | 学习了中国传统文学 |
| 3 | 自动化2019级5班 | 学习了机械自动化 |
------------------------------------------------------------------------------------------------
3 rows in set (0.00 sec)mysql select * from score;
------------------------------
| score | student_id | course_id |
------------------------------
| 70.5 | 1 | 1 |
| 98.5 | 1 | 3 |
| 33.0 | 1 | 5 |
| 98.0 | 1 | 6 |
| 60.0 | 2 | 1 |
| 59.5 | 2 | 5 |
| 33.0 | 3 | 1 |
| 68.0 | 3 | 3 |
| 99.0 | 3 | 5 |
| 67.0 | 4 | 1 |
| 23.0 | 4 | 3 |
| 56.0 | 4 | 5 |
| 72.0 | 4 | 6 |
| 81.0 | 5 | 1 |
| 37.0 | 5 | 5 |
| 56.0 | 6 | 2 |
| 43.0 | 6 | 4 |
| 79.0 | 6 | 6 |
| 80.0 | 7 | 2 |
| 92.0 | 7 | 6 |
------------------------------
20 rows in set (0.00 sec)mysql select * from course;
------------------------
| id | name |
------------------------
| 1 | Java |
| 2 | 中国传统文化 |
| 3 | 计算机原理 |
| 4 | 语文 |
| 5 | 高阶数学 |
| 6 | 英文 |
------------------------
6 rows in set (0.00 sec)4.3 内连接以下都是联合查询 select 列名 from 表1表2…;select 列名 from 表1 inner join 表2…; ①目标查询 “ 许仙 ” 同学的 成绩 在这里提及一下为什么不用代码的形式根据笛卡尔积行数是由两个表的行数之积得来的成绩表和学生表行数相乘 160 行代码太长因此截图省略位置。 其中 160 行代码大部分是无用数据清理掉即可
mysql select * from student, score where student.id score.student_id;
---------------------------------------------------------------------------------------
| id | sn | name | qq_mail | classes_id | score | student_id | course_id |
---------------------------------------------------------------------------------------
| 1 | 09982 | 黑旋风李逵 | xuanfengqq.com | 1 | 70.5 | 1 | 1 |
| 1 | 09982 | 黑旋风李逵 | xuanfengqq.com | 1 | 98.5 | 1 | 3 |
| 1 | 09982 | 黑旋风李逵 | xuanfengqq.com | 1 | 33.0 | 1 | 5 |
| 1 | 09982 | 黑旋风李逵 | xuanfengqq.com | 1 | 98.0 | 1 | 6 |
| 2 | 00835 | 菩提老祖 | NULL | 1 | 60.0 | 2 | 1 |
| 2 | 00835 | 菩提老祖 | NULL | 1 | 59.5 | 2 | 5 |
| 3 | 00391 | 白素贞 | NULL | 1 | 33.0 | 3 | 1 |
| 3 | 00391 | 白素贞 | NULL | 1 | 68.0 | 3 | 3 |
| 3 | 00391 | 白素贞 | NULL | 1 | 99.0 | 3 | 5 |
| 4 | 00031 | 许仙 | xuxianqq.com | 1 | 67.0 | 4 | 1 |
| 4 | 00031 | 许仙 | xuxianqq.com | 1 | 23.0 | 4 | 3 |
| 4 | 00031 | 许仙 | xuxianqq.com | 1 | 56.0 | 4 | 5 |
| 4 | 00031 | 许仙 | xuxianqq.com | 1 | 72.0 | 4 | 6 |
| 5 | 00054 | 不想毕业 | NULL | 1 | 81.0 | 5 | 1 |
| 5 | 00054 | 不想毕业 | NULL | 1 | 37.0 | 5 | 5 |
| 6 | 51234 | 好好说话 | sayqq.com | 2 | 56.0 | 6 | 2 |
| 6 | 51234 | 好好说话 | sayqq.com | 2 | 43.0 | 6 | 4 |
| 6 | 51234 | 好好说话 | sayqq.com | 2 | 79.0 | 6 | 6 |
| 7 | 83223 | tellme | NULL | 2 | 80.0 | 7 | 2 |
| 7 | 83223 | tellme | NULL | 2 | 92.0 | 7 | 6 |
---------------------------------------------------------------------------------------
20 rows in set (0.00 sec)就只剩下同学的各科成绩再对 “ 许仙 ” 进行筛选
mysql select * from student, score where student.id score.student_id and student.name 许仙;
----------------------------------------------------------------------------
| id | sn | name | qq_mail | classes_id | score | student_id | course_id |
----------------------------------------------------------------------------
| 4 | 00031 | 许仙 | xuxianqq.com | 1 | 67.0 | 4 | 1 |
| 4 | 00031 | 许仙 | xuxianqq.com | 1 | 23.0 | 4 | 3 |
| 4 | 00031 | 许仙 | xuxianqq.com | 1 | 56.0 | 4 | 5 |
| 4 | 00031 | 许仙 | xuxianqq.com | 1 | 72.0 | 4 | 6 |
----------------------------------------------------------------------------
4 rows in set (0.00 sec)最后只需要过滤掉不必要的列
mysql select name, course_id, score from student, score where student.id score.student_id and student.name 许仙;
--------------------------
| name | course_id | score |
--------------------------
| 许仙 | 1 | 67.0 |
| 许仙 | 3 | 23.0 |
| 许仙 | 5 | 56.0 |
| 许仙 | 6 | 72.0 |
--------------------------
4 rows in set (0.00 sec)一张清晰完整的结果就呈现在我们眼前。
通过 select 列名 from 表1, 表2 这种方式可以进行多表查询除此之外还有另外一种写法 select 列名 from 表1 join 表2 on 连接条件; (当使用 join 计算笛卡尔积的时候后面的条件使用 on 来表示而不是 where 这个相当于 固定搭配 )
mysql select * from student join score on student.id score.student_id and student.name 许仙;
----------------------------------------------------------------------------
| id | sn | name | qq_mail | classes_id | score | student_id | course_id |
----------------------------------------------------------------------------
| 4 | 00031 | 许仙 | xuxianqq.com | 1 | 67.0 | 4 | 1 |
| 4 | 00031 | 许仙 | xuxianqq.com | 1 | 23.0 | 4 | 3 |
| 4 | 00031 | 许仙 | xuxianqq.com | 1 | 56.0 | 4 | 5 |
| 4 | 00031 | 许仙 | xuxianqq.com | 1 | 72.0 | 4 | 6 |
----------------------------------------------------------------------------
4 rows in set (0.00 sec)mysql select student.name, score.course_id, score.score from student join score on student.id score.student_id and student.name 许仙;
--------------------------
| name | course_id | score |
--------------------------
| 许仙 | 1 | 67.0 |
| 许仙 | 3 | 23.0 |
| 许仙 | 5 | 56.0 |
| 许仙 | 6 | 72.0 |
--------------------------
4 rows in set (0.00 sec)既然存在了 from 多个表那为什么还要引入 join on 出来呢join on 默认情况下的行为和 from 多个表是一致的但是 join on 还能延伸出一些其他的用法功能比 from 多个表更广泛了 多张表的情况下
select student.name score.course_id, score.score from student join score join classes;join 多个表之后最后来个 on 同意筛选条件就可以了 ②查询所有同学的总成绩及同学的个人信息 在我们上文已经写出了 160 行代码过滤后的每个人总成绩在这里针对行和行之间的计算就需要使用聚合查询不能说一提到总成绩就想到表达式查询要根据实际的表结构见招拆招。 mysql select * from student, score where student.id score.student_id group by id;
---------------------------------------------------------------------------------------
| id | sn | name | qq_mail | classes_id | score | student_id | course_id |
---------------------------------------------------------------------------------------
| 1 | 09982 | 黑旋风李逵 | xuanfengqq.com | 1 | 70.5 | 1 | 1 |
| 2 | 00835 | 菩提老祖 | NULL | 1 | 60.0 | 2 | 1 |
| 3 | 00391 | 白素贞 | NULL | 1 | 33.0 | 3 | 1 |
| 4 | 00031 | 许仙 | xuxianqq.com | 1 | 67.0 | 4 | 1 |
| 5 | 00054 | 不想毕业 | NULL | 1 | 81.0 | 5 | 1 |
| 6 | 51234 | 好好说话 | sayqq.com | 2 | 56.0 | 6 | 2 |
| 7 | 83223 | tellme | NULL | 2 | 80.0 | 7 | 2 |
---------------------------------------------------------------------------------------
7 rows in set (0.00 sec)在不使用聚合函数的情况下后面这部分显示出来的结果相当于每个分组中的第一条记录
总成绩
mysql select student.name, sum(score.score) from student, score where student.id score.student_id group by id;
-----------------------------------
| name | sum(score.score) |
-----------------------------------
| 黑旋风李逵 | 300.0 |
| 菩提老祖 | 119.5 |
| 白素贞 | 200.0 |
| 许仙 | 218.0 |
| 不想毕业 | 118.0 |
| 好好说话 | 178.0 |
| tellme | 172.0 |
-----------------------------------
7 rows in set (0.00 sec)还可以指定别名
mysql select student.name, sum(score.score) as total from student, score where student.id score.student_id group by id;
------------------------
| name | total |
------------------------
| 黑旋风李逵 | 300.0 |
| 菩提老祖 | 119.5 |
| 白素贞 | 200.0 |
| 许仙 | 218.0 |
| 不想毕业 | 118.0 |
| 好好说话 | 178.0 |
| tellme | 172.0 |
------------------------
7 rows in set (0.00 sec)③查询每个同学姓名课程名分数
mysql select * from student, course, score where student.id score.student_id and course.id score.course_id;
---------------------------------------------------------------------------------------------------------------
| id | sn | name | qq_mail | classes_id | id | name | score | student_id | course_id |
---------------------------------------------------------------------------------------------------------------
| 1 | 09982 | 黑旋风李逵 | xuanfengqq.com | 1 | 1 | Java | 70.5 | 1 | 1 |
| 1 | 09982 | 黑旋风李逵 | xuanfengqq.com | 1 | 3 | 计算机原理 | 98.5 | 1 | 3 |
| 1 | 09982 | 黑旋风李逵 | xuanfengqq.com | 1 | 5 | 高阶数学 | 33.0 | 1 | 5 |
| 1 | 09982 | 黑旋风李逵 | xuanfengqq.com | 1 | 6 | 英文 | 98.0 | 1 | 6 |
| 2 | 00835 | 菩提老祖 | NULL | 1 | 1 | Java | 60.0 | 2 | 1 |
| 2 | 00835 | 菩提老祖 | NULL | 1 | 5 | 高阶数学 | 59.5 | 2 | 5 |
| 3 | 00391 | 白素贞 | NULL | 1 | 1 | Java | 33.0 | 3 | 1 |
| 3 | 00391 | 白素贞 | NULL | 1 | 3 | 计算机原理 | 68.0 | 3 | 3 |
| 3 | 00391 | 白素贞 | NULL | 1 | 5 | 高阶数学 | 99.0 | 3 | 5 |
| 4 | 00031 | 许仙 | xuxianqq.com | 1 | 1 | Java | 67.0 | 4 | 1 |
| 4 | 00031 | 许仙 | xuxianqq.com | 1 | 3 | 计算机原理 | 23.0 | 4 | 3 |
| 4 | 00031 | 许仙 | xuxianqq.com | 1 | 5 | 高阶数学 | 56.0 | 4 | 5 |
| 4 | 00031 | 许仙 | xuxianqq.com | 1 | 6 | 英文 | 72.0 | 4 | 6 |
| 5 | 00054 | 不想毕业 | NULL | 1 | 1 | Java | 81.0 | 5 | 1 |
| 5 | 00054 | 不想毕业 | NULL | 1 | 5 | 高阶数学 | 37.0 | 5 | 5 |
| 6 | 51234 | 好好说话 | sayqq.com | 2 | 2 | 中国传统文化 | 56.0 | 6 | 2 |
| 6 | 51234 | 好好说话 | sayqq.com | 2 | 4 | 语文 | 43.0 | 6 | 4 |
| 6 | 51234 | 好好说话 | sayqq.com | 2 | 6 | 英文 | 79.0 | 6 | 6 |
| 7 | 83223 | tellme | NULL | 2 | 2 | 中国传统文化 | 80.0 | 7 | 2 |
| 7 | 83223 | tellme | NULL | 2 | 6 | 英文 | 92.0 | 7 | 6 |
---------------------------------------------------------------------------------------------------------------
20 rows in set (0.00 sec)mysql select student.name, course.name, score.score from student, course, score where student.id score.student_id and course.id score.course_id;
--------------------------------------------
| name | name | score |
--------------------------------------------
| 黑旋风李逵 | Java | 70.5 |
| 黑旋风李逵 | 计算机原理 | 98.5 |
| 黑旋风李逵 | 高阶数学 | 33.0 |
| 黑旋风李逵 | 英文 | 98.0 |
| 菩提老祖 | Java | 60.0 |
| 菩提老祖 | 高阶数学 | 59.5 |
| 白素贞 | Java | 33.0 |
| 白素贞 | 计算机原理 | 68.0 |
| 白素贞 | 高阶数学 | 99.0 |
| 许仙 | Java | 67.0 |
| 许仙 | 计算机原理 | 23.0 |
| 许仙 | 高阶数学 | 56.0 |
| 许仙 | 英文 | 72.0 |
| 不想毕业 | Java | 81.0 |
| 不想毕业 | 高阶数学 | 37.0 |
| 好好说话 | 中国传统文化 | 56.0 |
| 好好说话 | 语文 | 43.0 |
| 好好说话 | 英文 | 79.0 |
| tellme | 中国传统文化 | 80.0 |
| tellme | 英文 | 92.0 |
--------------------------------------------
20 rows in set (0.00 sec)4.4 外连接 select 列名 from 表1 left join 表2; 左外连接select 列名 from 表1 right join 表2; 右外连接 如果两张表里的数据是对应的此时外连接和内连接看起来没区别如果数据不对应外连接和内连接的区别就明显了
制作一个表格
mysql create table student2(id int, name varchar(20));
Query OK, 0 rows affected (0.01 sec)mysql create table score2(id int, score int);
Query OK, 0 rows affected (0.01 sec)mysql insert into student2 values(1, 张三), (2, 李四), (3, 王五);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0mysql insert into score2 values(1, 90), (2, 80), (3, 70);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0mysql select * from student2;
--------------
| id | name |
--------------
| 1 | 张三 |
| 2 | 李四 |
| 3 | 王五 |
--------------
3 rows in set (0.00 sec)mysql select * from score2;
-------------
| id | score |
-------------
| 1 | 90 |
| 2 | 80 |
| 3 | 70 |
-------------
3 rows in set (0.00 sec)mysql select name, score from student2 join score2 on student2.id score2.id;
---------------
| name | score |
---------------
| 张三 | 90 |
| 李四 | 80 |
| 王五 | 70 |
---------------
3 rows in set (0.00 sec)左右对比如果两个表数据记录都是一一对应的此时内连接和外连接没有区别
mysql select name, score from student2 left join score2 on student2.id score2.id;
---------------
| name | score |
---------------
| 张三 | 90 |
| 李四 | 80 |
| 王五 | 70 |
---------------
3 rows in set (0.00 sec)mysql select name, score from student2 right join score2 on student2.id score2.id;
---------------
| name | score |
---------------
| 张三 | 90 |
| 李四 | 80 |
| 王五 | 70 |
---------------
3 rows in set (0.00 sec)对成绩序号进行修改出现 id 4 无人对应王五没有对应的成绩
mysql update score2 set id 4 where score 70;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0mysql select * from score2;
-------------
| id | score |
-------------
| 1 | 90 |
| 2 | 80 |
| 4 | 70 |
-------------
3 rows in set (0.00 sec)mysql select * from student2;
--------------
| id | name |
--------------
| 1 | 张三 |
| 2 | 李四 |
| 3 | 王五 |
--------------
3 rows in set (0.00 sec)从学生表的角度看王五同学没有分数 从分数表的角度看4 号同学没有身份信息
内连接产生的结果是两张表都包含的数据
mysql select name, score from student2 join score2 on student2.id score2.id;
---------------
| name | score |
---------------
| 张三 | 90 |
| 李四 | 80 |
---------------
2 rows in set (0.00 sec)左外连接就是以 join 左侧的表为主保证左侧的表的每个记录都能体现在结果中如果左侧的记录在右侧表中不存在则填充 NULL。
mysql select name, score from student2 left join score2 on student2.id score2.id;
---------------
| name | score |
---------------
| 张三 | 90 |
| 李四 | 80 |
| 王五 | NULL |
---------------
3 rows in set (0.00 sec)右外连接就是以 join 右侧的表为主保证右侧的表每个记录都能体现在结果中如果右侧的记录在左侧不存在则填充 NULL。
mysql select name, score from student2 right join score2 on student2.id score2.id;
---------------
| name | score |
---------------
| 张三 | 90 |
| 李四 | 80 |
| NULL | 70 |
---------------
3 rows in set (0.00 sec)综上来看是否存在一种外连接可以把整个全集都获取到呢
答案是有的全外连接但是 MySQL 不支持全外连接 ️4.5 自连接
自己和自己进行连接同一张表和自己进行笛卡尔积 SQL 里面指定条件筛选都是按照列和列之间进行筛选难以进行行和行之间筛选自连接操作能把行转化为列 显示所有 计算机原理 成绩比 Java 成绩高的成绩信息
mysql select * from course;
------------------------
| id | name |
------------------------
| 1 | Java |
| 2 | 中国传统文化 |
| 3 | 计算机原理 |
| 4 | 语文 |
| 5 | 高阶数学 |
| 6 | 英文 |
------------------------
6 rows in set (0.00 sec)course_id 1 和 course_id 3的比较 mysql select * from score;
------------------------------
| score | student_id | course_id |
------------------------------
| 70.5 | 1 | 1 |
| 98.5 | 1 | 3 |
| 33.0 | 1 | 5 |
| 98.0 | 1 | 6 |
| 60.0 | 2 | 1 |
| 59.5 | 2 | 5 |
| 33.0 | 3 | 1 |
| 68.0 | 3 | 3 |
| 99.0 | 3 | 5 |
| 67.0 | 4 | 1 |
| 23.0 | 4 | 3 |
| 56.0 | 4 | 5 |
| 72.0 | 4 | 6 |
| 81.0 | 5 | 1 |
| 37.0 | 5 | 5 |
| 56.0 | 6 | 2 |
| 43.0 | 6 | 4 |
| 79.0 | 6 | 6 |
| 80.0 | 7 | 2 |
| 92.0 | 7 | 6 |
------------------------------
20 rows in set (0.00 sec)SQL 中无法实现行和行之间的比较行和行不能直接比较转换成列和列自连接就可以做到这一点 mysql select * from score,score;
ERROR 1066 (42000): Not unique table/alias: score可以看到出现了错误因为自连接的情况下必须给表起别名了
但是此处笛卡尔积数据太多了需要过滤掉不合理的数据保留关注的合法数据
我们的需求是找到哪个同学的 3 号课程比 1 号课程高 当学生 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;
------------------------------------------------------------
| score | student_id | course_id | score | student_id | course_id |
------------------------------------------------------------
| 98.5 | 1 | 3 | 70.5 | 1 | 1 |
| 68.0 | 3 | 3 | 33.0 | 3 | 1 |
| 23.0 | 4 | 3 | 67.0 | 4 | 1 |
------------------------------------------------------------
3 rows in set (0.00 sec)此时表里剩下的数据就是左侧都为 3 的课程分数右侧就都是为 1 的课程分数 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 | 70.5 | 1 | 1 |
| 68.0 | 3 | 3 | 33.0 | 3 | 1 |
------------------------------------------------------------
2 rows in set (0.00 sec)加个课程 3 大于课程 1 的条件 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)更加精准把 id 列单独列出来结果就是学生 1 和 学生 3 的成绩数据符合 ☠️4.6 子查询
子查询是指嵌入在其他sql语句中的select语句也叫嵌套查询
单行子查询返回一行记录的子查询
查询与“不想毕业” 同学的同班同学 先根据名字获取到班级 id根据班级 id 查询 id 匹配同学 mysql select * from student;
----------------------------------------------------------
| id | sn | name | qq_mail | classes_id |
----------------------------------------------------------
| 1 | 09982 | 黑旋风李逵 | xuanfengqq.com | 1 |
| 2 | 00835 | 菩提老祖 | NULL | 1 |
| 3 | 00391 | 白素贞 | NULL | 1 |
| 4 | 00031 | 许仙 | xuxianqq.com | 1 |
| 5 | 00054 | 不想毕业 | NULL | 1 |
| 6 | 51234 | 好好说话 | sayqq.com | 2 |
| 7 | 83223 | tellme | NULL | 2 |
| 8 | 09527 | 老外学中文 | foreignerqq.com | 2 |
----------------------------------------------------------
8 rows in set (0.00 sec)mysql select classes_id from student where name 不想毕业;
------------
| classes_id |
------------
| 1 |
------------
1 row in set (0.00 sec)mysql select name from student where classes_id 1;
-----------------
| name |
-----------------
| 黑旋风李逵 |
| 菩提老祖 |
| 白素贞 |
| 许仙 |
| 不想毕业 |
-----------------
5 rows in set (0.00 sec)把这俩 SQL 一合并就成了一个子查询了
mysql select name from student where classes_id (select classes_id from student where name 不想毕业);
-----------------
| name |
-----------------
| 黑旋风李逵 |
| 菩提老祖 |
| 白素贞 |
| 许仙 |
| 不想毕业 |
-----------------
5 rows in set (0.00 sec)此处的子查询可以任意级别的嵌套N 个 SQL 组合成了一个巨无霸 SQL 多行子查询返回多行记录的子查询
查询“语文”或“英文”课程的成绩信息: 先根据课程名直到课程的 id拿着课程的 id 去分数表里进行查询 mysql select * from course;
------------------------
| id | name |
------------------------
| 1 | Java |
| 2 | 中国传统文化 |
| 3 | 计算机原理 |
| 4 | 语文 |
| 5 | 高阶数学 |
| 6 | 英文 |
------------------------
6 rows in set (0.00 sec)mysql select id from course where name 语文 or name 英文;
----
| id |
----
| 4 |
| 6 |
----
2 rows in set (0.00 sec)mysql select * from score where course_id in (select id from course where name 语文 or name 英文);
------------------------------
| score | student_id | course_id |
------------------------------
| 98.0 | 1 | 6 |
| 72.0 | 4 | 6 |
| 43.0 | 6 | 4 |
| 79.0 | 6 | 6 |
| 92.0 | 7 | 6 |
------------------------------
5 rows in set (0.00 sec)️⚧️4.7 合并查询
把两个查询结果结果集合合并到一起
可以使用集合操作符 unionunion all。使用 UNION 和 UNION ALL 时前后查询的结果集中字段需要一致。
union
该操作符用于取得两个结果集的并集。当使用该操作符时会自动去掉结果集中的重复行
查询id小于3或者名字为“英文”的课程
mysql select * from course where id 3 or name 英文;
------------------------
| id | name |
------------------------
| 1 | Java |
| 2 | 中国传统文化 |
| 6 | 英文 |
------------------------
3 rows in set (0.00 sec)mysql select * from course where id 3 union select * from course where name 英文;
------------------------
| id | name |
------------------------
| 1 | Java |
| 2 | 中国传统文化 |
| 6 | 英文 |
------------------------
3 rows in set (0.00 sec)union all 和 union 的唯一区别就是 union 操作如果有重复记录会去重union all 不会去重 SQL查询中各个关键字的执行先后顺序 from on join where group by with having select distinct order by limit 文章转载自： http://www.morning.brwwr.cn.gov.cn.brwwr.cn http://www.morning.bpmnq.cn.gov.cn.bpmnq.cn http://www.morning.hcsqznn.cn.gov.cn.hcsqznn.cn http://www.morning.pskjm.cn.gov.cn.pskjm.cn http://www.morning.rgrys.cn.gov.cn.rgrys.cn http://www.morning.fdmfn.cn.gov.cn.fdmfn.cn http://www.morning.wbfg.cn.gov.cn.wbfg.cn http://www.morning.whothehellami.com.gov.cn.whothehellami.com http://www.morning.bwqr.cn.gov.cn.bwqr.cn http://www.morning.hrtwt.cn.gov.cn.hrtwt.cn http://www.morning.mzwqt.cn.gov.cn.mzwqt.cn http://www.morning.jrqcj.cn.gov.cn.jrqcj.cn http://www.morning.wrdpj.cn.gov.cn.wrdpj.cn http://www.morning.mhsmj.cn.gov.cn.mhsmj.cn http://www.morning.mdwlg.cn.gov.cn.mdwlg.cn http://www.morning.hryhq.cn.gov.cn.hryhq.cn http://www.morning.zsfooo.com.gov.cn.zsfooo.com http://www.morning.sgcdr.com.gov.cn.sgcdr.com http://www.morning.tyjnr.cn.gov.cn.tyjnr.cn http://www.morning.dsgdt.cn.gov.cn.dsgdt.cn http://www.morning.wqcz.cn.gov.cn.wqcz.cn http://www.morning.nzmhk.cn.gov.cn.nzmhk.cn http://www.morning.rgpy.cn.gov.cn.rgpy.cn http://www.morning.jqjnl.cn.gov.cn.jqjnl.cn http://www.morning.snzgg.cn.gov.cn.snzgg.cn http://www.morning.cnkrd.cn.gov.cn.cnkrd.cn http://www.morning.qkpzq.cn.gov.cn.qkpzq.cn http://www.morning.cykqg.cn.gov.cn.cykqg.cn http://www.morning.kpgft.cn.gov.cn.kpgft.cn http://www.morning.zdzgf.cn.gov.cn.zdzgf.cn http://www.morning.mzmqg.cn.gov.cn.mzmqg.cn http://www.morning.ryywf.cn.gov.cn.ryywf.cn http://www.morning.dtrcl.cn.gov.cn.dtrcl.cn http://www.morning.xylxm.cn.gov.cn.xylxm.cn http://www.morning.psxcr.cn.gov.cn.psxcr.cn http://www.morning.incmt.com.gov.cn.incmt.com http://www.morning.qtkfp.cn.gov.cn.qtkfp.cn http://www.morning.hhxpl.cn.gov.cn.hhxpl.cn http://www.morning.dlrsjc.com.gov.cn.dlrsjc.com http://www.morning.cnyqj.cn.gov.cn.cnyqj.cn http://www.morning.mkydt.cn.gov.cn.mkydt.cn http://www.morning.mtyhk.cn.gov.cn.mtyhk.cn http://www.morning.jqsyp.cn.gov.cn.jqsyp.cn http://www.morning.kcnjz.cn.gov.cn.kcnjz.cn http://www.morning.xinyishufa.cn.gov.cn.xinyishufa.cn http://www.morning.mlfgx.cn.gov.cn.mlfgx.cn http://www.morning.pjtw.cn.gov.cn.pjtw.cn http://www.morning.jzxqj.cn.gov.cn.jzxqj.cn http://www.morning.ldqzz.cn.gov.cn.ldqzz.cn http://www.morning.zxqyd.cn.gov.cn.zxqyd.cn http://www.morning.tnthd.cn.gov.cn.tnthd.cn http://www.morning.fpyll.cn.gov.cn.fpyll.cn http://www.morning.rwrn.cn.gov.cn.rwrn.cn http://www.morning.hpggl.cn.gov.cn.hpggl.cn http://www.morning.ptxwg.cn.gov.cn.ptxwg.cn http://www.morning.yrbq.cn.gov.cn.yrbq.cn http://www.morning.gccdr.cn.gov.cn.gccdr.cn http://www.morning.rnzbr.cn.gov.cn.rnzbr.cn http://www.morning.qgzmz.cn.gov.cn.qgzmz.cn http://www.morning.yydeq.cn.gov.cn.yydeq.cn http://www.morning.zzgtdz.cn.gov.cn.zzgtdz.cn http://www.morning.htbsk.cn.gov.cn.htbsk.cn http://www.morning.rfjmy.cn.gov.cn.rfjmy.cn http://www.morning.xnhnl.cn.gov.cn.xnhnl.cn http://www.morning.woyoua.com.gov.cn.woyoua.com http://www.morning.lnyds.cn.gov.cn.lnyds.cn http://www.morning.jcyrs.cn.gov.cn.jcyrs.cn http://www.morning.xczyj.cn.gov.cn.xczyj.cn http://www.morning.rwmq.cn.gov.cn.rwmq.cn http://www.morning.dhwyl.cn.gov.cn.dhwyl.cn http://www.morning.krdb.cn.gov.cn.krdb.cn http://www.morning.liyixun.com.gov.cn.liyixun.com http://www.morning.qhrlb.cn.gov.cn.qhrlb.cn http://www.morning.zpxwg.cn.gov.cn.zpxwg.cn http://www.morning.benqc.com.gov.cn.benqc.com http://www.morning.yrjhr.cn.gov.cn.yrjhr.cn http://www.morning.mqwdh.cn.gov.cn.mqwdh.cn http://www.morning.sqqpb.cn.gov.cn.sqqpb.cn http://www.morning.ppllj.cn.gov.cn.ppllj.cn http://www.morning.rtlrz.cn.gov.cn.rtlrz.cn
