MySQL学习记录【基本操作 → 数据操作 → 高级查询 → 联合查询】总结

文档中心

MySQL学习记录【基本操作 → 数据操作 → 高级查询 → 联合查询】总结

数据库和表的基本操作—1
- 1. SQL命令分类
- 2. 数据库操作
- - 2.1 MySQL 数据类型
  - 2.2 表结构操作
- 3. MySQL增删改查
- - 3.1 CRUD
  - - 新增（Create）
    - 查询（Retrieve）
    - 修改（Update）
    - 删除（Delete）
表约束和表设计—2
- 1. 什么是表约束
- 2. 为什么需要表约束？
- 3. 常见约束
- - 非空约束 not null
  - 唯一约束 unique
  - 主键约束 primary key
  - 外键约束 foreign key
  - 检查约束 check
  - 自增 auto_increment
  - 默认约束 default
  - insert...select
- 4. 表的设计规则-数据库设计的三范式
- - 第一范式(确保每列保持原子性)
  - 第二范式(确保表中的每列都和主键相关)
  - 第三范式(确保每列都和主键列直接相关,而不是间接相关)
  - - 范式优点：实现列和非主键的解耦！
- 5. 表结构
高级SQL查询—3
- 1.聚合查询
- 2. SQL 查询关键字执行顺序
- 3. 联合查询（多表查询）
- - 3.1 前置知识—笛卡尔积
  - 3.2 内连接
  - 3.3 外连接
  - 3.4 自连接
  - 3.5 子查询
  - 3.6 合并查询
内容重点总结

数据库和表的基本操作—1

1. SQL命令分类

DDL【Data Definition Language】数据定义语言，用来维护存储数据的结构代表指令: create, drop,alter；它的作用是做了一个数据库表的结构的创建和操作数据库结构的语言。数据库表为怎样的一个储存状态，由DDL语言来决定。
DML【Data Manipulation Language】数据操纵语言，用来对数据进行操作代表指令：insert，delete，update，DML中⼜单独分了⼀个DQL，数据查询语⾔，代表指令：select；表结构有了，对表内数据的填充和操作，就是DML数据操纵语言了。
DCL【Data Control Language】数据控制语⾔，主要负责权限管理和事务代表指令：grant， revoke，commit。一般由运维工程师分配权限，如果是研发的话，需要了解DDL和DML就好了。

DDL 可以看着是操作库或表的结构的，而 DML 是⽤来操作数据的，而 DCL 是操作其他的，如权限和事务等。

2. 数据库操作

查询服务器上所有的数据库

show databases;

创建数据库

create database [if not exists] db_name;-- [if not exists] 表示非必要命令-- db_name 数据库名

MySQL命名规则：所有的数据库名称和表名以及字段名，必须要求全部小写。多个字母之间使用下划线"_"分隔开。

使用数据库 / 查询当前使用的数据库

--use db_name;--select database();

删除数据库

drop database db_name;

2.1 MySQL 数据类型

数值类型

分为整型和浮点型：
在这里插入图片描述

decimal(m,n) m→最大长度(包括小数点后的最大长度)，n→保留小数点后几位。

字符串类型
日期类型

2.2 表结构操作

创建表

-- 示例create table table_name( id int, name varchar(20) comment '姓名',password varchar(50) comment '密码', age int, sex varchar(1), birthday timestamp, amout decimal(13,2), resume text );-- table_name 表名 -- id 字段名-- int 类型-- 可以使⽤comment增加字段说明

查询所有表名

show tables;

查询表结构

desc table_name;

在这里插入图片描述

desc 查看不到说明信息，可以使⽤另⼀种查询表结构的⽅法：

-- 1show create table table_name;-- 2show full columns from table_name;

效果：
在这里插入图片描述

删除表

drop table table_name;

修改表结构

-- 添加列alter table table_name add column id int;-- 删除列alter table table_name drop column 字段名;-- 修改列alter table test_user change 原字段名 新字段名 类型 [约束];-- 修改表名alter table table_name 旧表名 rename 新表名;-- 扩展：修改某个表编码格式（⽀持中⽂）alter table table_name convert to character set utf8mb4;

3. MySQL增删改查

3.1 CRUD

CRUD 即增加(Create)、查询(Retrieve)、更新(Update)、删除(Delete)四个单词的首字母缩写。

新增（Create）

全列添加

-- ⼀次添加⼀条 insert into table_name values('字段1','字段2'...);-- ⼀次添加多条 insert into table_name values('字段1','字段2'...),('字段1','字段2'...)...;

指定列添加

-- ⼀次添加⼀条 insert into table_name('指定列1','指定列2'...) values('字段1','字段2'...);-- ⼀次添加多条 insert into table_name('指定列1','指定列2'...) values('字段1','字段2'...),('字段1','字段2'...)...;

扩展：表和表数据的存储⽬录：show variables like '%dir%';，也可以使用：select @@datadir;。
在这里插入图片描述

查询（Retrieve）

全列查询

select * from table_name;

开发环境使用较多(因为开发环境的数据一般不大)，但在生产环境一定要慎用！
全列查询的缺点：

 1.如果数据量大，会消耗很大的带宽信息，并且传输的速度会很慢 2.可能会影响索引的使用！

指定列查询

select 列名1,列名2 from table_name;

表达式查询

-- eg:计算每个⼈的总成绩： select name,chinese+math+english from score;-- eg:将所有数学成绩 + 10 分： select name,math+10 from score;

使⽤ select 进⾏的表达式计算不会修改原表数据。

使⽤别名查询

-- eg:计算成绩总和，列名命名为 totalselect chinese+math+english as total from score;

去重 distinct

select distinct columns from table_name;-- 数学成绩去重： select distinct math from score; -- 多⾏去重，语⽂+数据重复的才会合并： select distinct chinese,math from score;

distinct 只能放在查询的最前⾯，distinct 错误⽤法：
select name,distinct math from score;

排序 order by → 升序(从小到大) asc / 降序(从大到小) asc/desc

sleect * from 表名 order by 列名,列名 asc/desc;-- 如果不指定asc/desc排序规则，那么默认情况下使用的就是升序的排序规则-- eg：总分排名： selsct name,(math+chinese+english) as '总分' from score orger by (math+chinese+english) desc;-- NULL 列为最⼩值-- 多列排序：order by 列1,列2 asc/desc；

条件查询

比较运算符
在这里插入图片描述
逻辑运算符
MySQL学习记录【基本操作 → 数据操作 → 高级查询 → 联合查询】总结
注：
· where条件可以使用表达式，但不能使用别名
· and的优先级高于or，在同时使用时，需要使用小括号()包裹优先执行的部分。

面试问题：为什么别名不能使用在where中，而可以使用在order by中？
MySQL学习记录【基本操作 → 数据操作 → 高级查询 → 联合查询】总结
别名是在第五步select 才产生的，在第二步where中不能使用。

基本查询

-- 查询英语不及格的同学及英语成绩 ( < 60 )select name, english from exam_result where english < 60;-- 查询语文成绩好于英语成绩的同学select name, chinese, english from exam_result where chinese > english;-- 查询总分在 200 分以下的同学select name, chinese + math + english 总分 from exam_result  where chinese + math + english < 200;

AND与OR：

-- 查询语文成绩大于80分，且英语成绩大于80分的同学select * from exam_result where chinese > 80 and english > 80;-- 查询语文成绩大于80分，或英语成绩大于80分的同学select * from exam_result where chinese > 80 or english > 80;-- 观察AND 和 OR 的优先级：select * from exam_result where chinese > 80 or math>70 and english > 70;select * from exam_result where (chinese > 80 or math>70) and english > 70;-- 查询语文成绩在 [80, 90] 分的同学及语文成绩select name, chinese from exam_result where chinese between 80 and 90;-- 使用 AND 也可以实现select name, chinese from exam_result where chinese >= 80 and chinese <= 90;

范围查询
① between x and y
包含x和y

-- 查询语文成绩在 [80, 90] 分的同学及语文成绩select name, chinese from exam_result where chinese between 80 and 90;-- 使用 AND 也可以实现select name, chinese from exam_result where chinese >= 80 and chinese <= 90;

② in

-- 查询数学成绩是 58 或者 59 或者 98 或者 99 分的同学及数学成绩select name, math from exam_result where math in(58, 59, 98, 99);-- 使用 OR 也可以实现select name, math from exam_result where math = 58 or math = 59 or math = 98 or math = 99;

③ 模糊查询：like

-- % 匹配任意多个（包括 0 个）字符select name from exam_result where name like '孙%';-- 匹配到孙悟空、孙权-- _ 匹配严格的一个任意字符select name from exam_result where name like '孙_';-- 匹配到孙权

④ null 查询

-- 查询 qq_mail 已知的同学姓名select name, qq_mail from student where qq_mail is not null;-- 查询 qq_mail 未知的同学姓名select name, qq_mail from student where qq_mail is null;

分页查询：limit
查询某一个页面的部分信息（截取了部分行信息）。

-- 起始下标为 0-- 从 0 开始，筛选 n 条结果select... from table_name [where...] [order by ...] limit n;-- 从 s 开始，筛选 n 条结果select... from table_name [where...] [order by ...] limit s, n;-- 从 s 开始，筛选 n 条结果，比第二种用法更明确，建议使用select... from table_name [where...] [order by ...] limit n offset s;

-- eg:-- 总分数排名前三select name,chinese+math+english as total from score order by total desc limit 3;-- 总分数第⼆⻚（4-6条数据）select name,chinese+math+english as total from score order by total desc limit 3 offset 3;-- limit x offset y（x,y 值超出不报错）select name,chinese+math+english as total from score order by total desc limit 1000 offset 100;

修改（Update）

update table_name set column = expr [, column = expr ...][where...] [order by ...] [limit...]

-- 1.全部修改update score set math=85-- 2.部分修改update score set math=85 where name='刘备';-- 3.修改多列update score set math=86,chinese=86 where name='刘备';-- 4.表达式修改-- 全班倒数后 3 名成绩（按总成绩排名） +10 分update score set math=math+10 order by math+chinese+english asc;

删除（Delete）

delete from table_name [where...] [order by ...] [limit...]

-- 1.删除单条 delete from score where name='刘备'; -- 2.全部删除 delete from score -- 3.清空表 truncate [table] 名称

扩充：
delete vs truncate
● truncate 删除快，但是数据不能被恢复；delete 删除慢，但数据能被恢复。
● delete 删除可以加条件，⽽ truncate 不能加条件。
delete 和 drop 的区别
● delete：只删除表中的数据。
● drop：删除表数据+表结构。
drop属于DDL，delete属于DML，而trucate属于DDL。
truvate执行原理:
① 拷贝一份原表结构进行创建
② 将原表进性删除和数据清除
delete执行原理
● 在InnoDB数据库引擎下，delete并不是真正的把数据删除掉了，而是给数据打了一个删除标签，标时当前的数据已经被删除了。数据存储大小不会改变。
● delete的数据是可以被恢复的，但是truncate、drop删除的数据是不能被恢复的。
● 执行效率：drop > truncate > delete
数据量越大，上面的执行效率差距也越大。
因为drop直接就删除掉表这个整体的结构就好了，truncate需要先把原表的结构拷贝一份，再删除原表结构，而delete是要去给数据打上删除标签的，他不能够快速直截了当的清除。

表约束和表设计—2

1. 什么是表约束

表约束是在创建表的时候，设计⼀些表的约束条件，⽤来保证数据的合法性和数据的正确性。

例如⼀个账号只能绑定⼀个⼿机号，那么⼿机号就不能重复，不能被多个账号绑定使⽤，所以它应该设置唯⼀约束。⽽主键是⽤来标识数据的，因此它不能为 NULL，也不能为空，所以他需要设置⾮空约束或（主键约束，主键约束也不能为 NULL）。

2. 为什么需要表约束？

就像法律是⽤来规范⼈们的正确行为的⼀样，表约束也是为了规范程序员正确使用表的，但表约束是预先设置的，设置之后就对所有插⼊和修改立即生效，比如非空约束设置好之后，如果添加和修改为 NULL 值就会报错，这点是和法律有细微的不同。

比如下表，如果对⽼师名称没有约束的话，那么数据 5 的这条⼯资就不能正常发放了，因为压根就不知道要发给谁： MySQL学习记录【基本操作 → 数据操作 → 高级查询 → 联合查询】总结
总之如果没有表约束，会导致数据不正确，从而导致程序或现实中的业务⽆法推进和执行。

3. 常见约束

在这里插入图片描述
表约束是需要在创建表的时候就给他设计的

非空约束 not null

创建非空约束

crete table table_name (   id int not null,   sn int,   name varchar(20),);

在这里插入图片描述
添加了⾮空约束之后，设置的字段就不能为空了。

查看非空约束
验证非空约束

唯一约束 unique

创建唯一约束

create table student (   id int not null,   sn int unique,   name varchar(20),);

MySQL学习记录【基本操作 → 数据操作 → 高级查询 → 联合查询】总结

查看唯一约束
使⽤ show keys from table_name 可以查看索引详情
验证唯一约束

问题： 给某个字段创建了唯一约束，没有指定非空约束
① 这个字段是否可以插入null值？
② 这个字段是否可以插入空值？
可以插入null，也是可以重复插入null的。对于null来说，并不是一个“值”，它相当于“为空”，就是“没有”的意思。
可以插入空值，但是不可以重复插入空值，空和null*在查询上的区别就是“空”是可以被查询出来的，用它作为条件去查询到结果，而null是“没有”的意思，是查询不到的。*

主键约束 primary key

主键是可以⽤来表示⼀张表中某条数据的代表凭证，例如对于“⼈”这张表来说，唯⼀的身份证就可以作为主键来代表这个⼈，“姓名”不⾏，因为姓名有可能会重复。

主键的特征：

主键可以由多个字段或单个字段组成。
主键不能为空且且唯一。
一个表中只能有⼀个主键。

指定id列为主键：

独立主键

create table table_name(   id int not null PRIMARY KEY,   sn int unique,   name varchar(20) default'unkown',   qq_mail varchar(20));

在这里插入图片描述

联合主键

create table table_name( id int, name varchar(250), primary key(id,name) );

在这里插入图片描述
注意事项，设置主键字段不能为 null，否则会报错。

面试题：主键约束 VS 唯⼀约束
① 一个表中主键约束只能有一个，而唯一约束可以有多个。
② 唯一约束可以插入一个或多个null，而主键约束不允许插入null。

外键约束 foreign key

foreign key (字段名) references 主表(列)

案例：
创建班级表classes，id为主键：

-- 创建班级表，有使⽤MySQL关键字作为字段时，需要使⽤``来标识create table classes ( id int primary key, name varchar(250), `desc` text);

创建学⽣表student，⼀个学⽣对应⼀个班级，⼀个班级对应多个学⽣。使⽤id为主键，classes_id 为外键，关联班级表id

-- 重新设置学⽣表结构drop table if exists student;create table student (   id int primary key auto_increment,   sn int unique,   name varchar(250) default'unkown',   classes_id int,   foreign key(classes_id) references classes(id) );

检查约束 check

检查约束是保证列中的值符合指定的条件，⽐如性别只能插⼊“男”或者“⼥”。
检查约束 check 使⽤语法：

check(<检查约束>)

create table test_user (   id int,   name varchar(20),   sex varchar(1),   check (sex ='男' or sex='⼥') );

自增 auto_increment

create table test_user(   id int primary key auto_increment,   username varchar(250) );

在这里插入图片描述

注意事项：
① ⼀个表中只能有⼀个⾃增列。
② 2.⾃增列的字段类型只能为整数类型（TINYINT、SMALLINT、INT、BIGINT 等）。
③ auto_increment 必须配合 key ⼀起使⽤，这个 key 可以是 primary key，foreign key，如果没有 key 就会报错
④ 唯⼀列可以为自增列
⑤ 外键也可以为自增列
查看自增值

show create table table_name;show create table table_name\G;

在这里插入图片描述

手动设置自增值
修改自增值
注意事项：auto_increment 的值只能设置⽐⽬前存储的最⼤值⼤，否则设置不会⽣效。

alter table table_name auto_increment=n;

delete 和 truncate 重置自增验证
delete 不重置⾃增：
truncate 重置⾃增：

默认约束 default

默认约束是给没有给列赋值时的默认值，⽤法如下：

create table test_user(   id int primary key auto_increment,   sex varchar(1) default '男' );

在这里插入图片描述

insert…select

将一张表的某一字段的数据完全的插入令一张新表中,按照字段的顺序进行匹配。
在这里插入图片描述

MySQL学习记录【基本操作 → 数据操作 → 高级查询 → 联合查询】总结

4. 表的设计规则-数据库设计的三范式

第一范式(确保每列保持原子性)

第⼀范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原⼦值，就说明该数据库表满足了第⼀范式。第⼀范式的合理遵循需要根据系统的实际需求来定。

错误示例：
在这里插入图片描述

“地址”这个属性，本来直接将“地址”属性设计成⼀个数据库表的字段就行。但是如果系统经常会访问“地址”属性中的“城市”部分，那么就非要将“地址”这个属性重新拆分为省份、城市、详细地址等多个部分进行存储，这样在对地址中某⼀部分操作的时候将非常方便

正确示例：
在这里插入图片描述
上表所示的用户信息遵循了第⼀范式的要求，这样在对⽤户使⽤城市进⾏分类的时候就⾮常⽅便，也提高了数据库的性能。

第二范式(确保表中的每列都和主键相关)

第⼆范式在第⼀范式的基础之上更进⼀层。第⼆范式需要确保数据库表中的每⼀列都和主键相关，而不能只与主键的某⼀部分相关（主要针对联合主键⽽⾔）。也就是说在⼀个数据库表中，⼀个表中只能保存⼀种数据，不可以把多种数据保存在同⼀张数据库表中。

不符合示例：
在这里插入图片描述
这个表中是以订单编号和商品编号作为联合主键。这样在该表中商品名称、单位、商品价格等信息不与该表的主键相关，⽽仅仅是与商品编号相关。所以在这⾥违反了第⼆范式的设计原则。

正确示例：
在这里插入图片描述

这样设计，在很⼤程度上减⼩了数据库的冗余。如果要获取订单的商品信息，使⽤商品编号到商品信息表中查询即可。

第三范式(确保每列都和主键列直接相关,而不是间接相关)

第三范式需要确保数据表中的每⼀列数据都和主键直接相关，而不能间接相关，并且每一个字段都不能和非主键字段有依赖。

比如在设计⼀个订单数据表的时候，可以将客户编号作为⼀个外键和订单表建⽴相应的关系。⽽不可以在订单表中添加关于客户其它信息（比如姓名、所属公司等）的字段。如下面这两个表所示的设计就是⼀个满⾜第三范式的数据库表。不符合第三范式：
MySQL学习记录【基本操作 → 数据操作 → 高级查询 → 联合查询】总结
正确表结构：
在这里插入图片描述
这样在查询订单信息的时候，就可以使用客户编号来引用客户信息表中的记录，也不必在订单信息表中多次输⼊客户信息的内容，减小了数据冗余。

范式优点：实现列和非主键的解耦！

修改了一个字段，不会影响其他字段，优秀的设计理念。“高内聚低耦合”

5. 表结构

表和表之间的关系有三种：

⼀对⼀：⼀个学⽣对应⼀个学号。
⼀对多：⼀个班级包含多个学⽣（⽤两张表表示）。
多对多：⼀张表中的⼀条数据可以对应到另⼀张表的多条数据，反之也是这样的，这样的关系叫做多对多，⼀般⽤三个表表示。

高级SQL查询—3

1.聚合查询

常见的统计总数、计算平局值等操作，可以使⽤聚合函数来实现，常⻅的聚合函数有：
在这里插入图片描述

count 查询条数

-- 统计班级共有多少同学select count(*) from student;select count(0) from student;-- 统计班级收集的 qq_mail 有多少个，qq_mail 为 NULL 的数据不会计⼊结果select count(qq_mail) from student;

count用法1：
在这里插入图片描述
用法2

用法3

在不同的 count 统计场景下，要使用不同的 count 查询。

sum 函数

-- 统计数学成绩总分select sum(math) from exam_result;-- 不及格 < 60 的总分，没有结果，返回 NULLselect sum(math) from exam_result where math < 60;

MySQL学习记录【基本操作 → 数据操作 → 高级查询 → 联合查询】总结

avg函数

-- 统计平均总分select avg(chinese + math + english) 平均总分 from exam_result;

MySQL学习记录【基本操作 → 数据操作 → 高级查询 → 联合查询】总结

max / min 函数

-- 返回英语最⾼分select max(english) from exam_result;-- 返回 > 70 分以上的数学最低分select min(math) from exam_result where math > 70;

ifnull 函数
ifnull 函数是 MySQL 控制流函数之一，它接受两个参数，如果不是 null，则返回第⼀个参数，否则 ifnull 函数返回第⼆个参数。

select ifnull(expression_1,expression_2);-- 示例1select ifnull(1,0); -- returns 1-- 示例2select ifnull('',1); -- returns ''-- 示例3select ifnull(unll,'Hello,Null'); -- returns Hello,Null

解决总成绩为null的问题
在这里插入图片描述
使⽤ ifnull 函数来解决：
MySQL学习记录【基本操作 → 数据操作 → 高级查询 → 联合查询】总结
但是我们尽量是在开发中直接设置字段的约束为“not null”，尽量不调用数据库自己的函数。

分组查询 group by
select 中使⽤ group by 子句可以对指定列进⾏分组查询。需要满足：使用 group by 进⾏分组查询时，select 指定的字段必须是“分组依据字段”，其他字段若想出现在 select 中则必须包含在聚合函数中。

select column1, sum(column2), .. from table group by column1;

案例：
准备测试表及数据：职员表，有id（主键）、name（姓名）、role（⻆⾊）、salary（薪⽔）

-- 查询每个⻆⾊的最⾼⼯资、最低⼯资和平均⼯资select role,max(salary),min(salary),avg(salary) from emp group by role;

分组条件查询 having
group by ⼦句进⾏分组以后，需要对分组结果再进⾏条件过滤时，不能使⽤ where 语句，而需要用 having

-- 显示平均⼯资低于 1500 的⻆⾊和它的平均⼯资select role,max(salary),min(salary),avg(salary) from emp group by rolehaving avg(salary)>1500;

2. SQL 查询关键字执行顺序

SQL 查询执行先后顺序： group by > having > order by > limit

3. 联合查询（多表查询）

3.1 前置知识—笛卡尔积

笛卡尔积是联合查询也就是多表查询的基础，笛卡尔积⼜称直积，表示为 XY，比如 A 表中的数据为 m ⾏，B 表中的数据有 n ⾏，那么 A 和 B 做笛卡尔积，结果为 mn 行。
比如以下表，它们的笛卡尔积就有 9 个：
在这里插入图片描述

我们可以发现，在联表查询时首先要使用笛卡尔积，但是单纯的笛卡尔积查询出来的是由很多的无效数据的。因此我们要进行过滤。

3.2 内连接

内连接侧重于两个表之间的共性，它的作⽤是使⽤联接，⽐较两个（或多个）表之间的共有数据，然后进行返回。
比如我要查询学生的成绩，涉及到两张表：学生表和成绩表，使⽤内连接查询的数据是下图的红色部分：
MySQL学习记录【基本操作 → 数据操作 → 高级查询 → 联合查询】总结

内连接语法

上一个问题所述，如果不加过滤条件，我们联表查询出来的就是笛卡尔积，会有很多的无意义数据，所以需要加过滤条件保留有效数据
在这里插入图片描述

select * from t1 [inner|cross] join t2 [on 过滤条件] [where 过滤条件]

在这里插入图片描述
内连接的写法有以下 4 种：

select * from t1 join t2;
select * from t1 inner join t2;
select * from t1 cross join t2;
select * from t1,t2;
内连接查询的“问题”
内连接的问题是，查询到的是两张表相关联的部分，没有主表和次表之分，但是如果分了主要字段和其他查询字段，需要把否一张表的主要字段都查出来，但是如果另外一张表没有相关这个字段某个数据的外键信息，那么内连接就不能满足查询条件了。

3.3 外连接

外连接包括内连接和其他至少一张表的所有满足条件的信息，外连接包括：

左（外）连接
右（外）连接

在这里插入图片描述

语法连接：

-- 左连接语法select * from t1 left join t2 [on 连接条件];-- 右连接语法select * from t1 right join t2 [on 连接条件];

eg：查询所有人的成绩

-- 左连接查询所有人成绩select s.sn,s.username,s.mail,st.score from student s left join score_table st on s.id=st.student_id;-- 右连接查询所有人成绩 select s.sn,s.username,s.mail,st.score from score_table st right join student s on s.id=st.student_id;

注意：
外连接必须加 on 过滤条件。

3.4 自连接

自连接是指在同一张表连接自身进行查询。

eg:
① 查询英语成绩>计算机成绩的数据

select st1.score 英语,st2.score 计算机 from score_table st1,score_table st2wherest1.student_id=st2.student_id and st1.score>st2.score;

从上面的语法“select * from t1,t2”我们可以看出，自连接就是查询同⼀张表的内查询，因为这个语句就是内连接的查询语句，因此我们还可以使⽤以下 join 语句查询：

select st1.score 英语,st2.score 计算机 from score_table st1join score_table st2 on st1.student_id=st2.student_id and st1.score>st2.score;

② 查询学⽣信息+英语成绩>计算机成绩的数据

select st1.score 英语,st2.score 计算机,s.username from score_table st1join score_table st2 on st1.student_id=st2.student_id and st1.score>st2.scorejoin student s on s.id=st1.student_id;

3.5 子查询

子查询是指嵌入在其他 sql 语句中的 select 语句，也叫嵌套查询。

eg:
① 查询张三的同班同学
查询的实现步骤：

先查询张三的班级 id。
查询⽤户表 where 条件是班级 id。

-- 步骤1select class_id from student where username='张三';-- 步骤2 select * from student where class_id=1;--最终合并的⼦查询语句如下：select * from student where class_id=(select class_id from student whereusername='张三');

② 查询计算机或英语的成绩

select * from score_table where course_id in(select id from course where name='计算机' or name='英语');

③ 查询比Java班平均分高的学⽣
查询思路：

查询 Java 班的 id。
根据班级 id 查询出所有的学⽣。
根据学生计算出 Java 班的平均分。
把上⼀步作为临时表，查询分数大于平均分的分数。

--前三步sqlselect avg(score) from score_table where student_id in (select id from student where class_id=  (select id from class where classname='Java班'));--查询分数表中⼤于此平均分的数据：select * from score_table st, (select avg(score) as avg from score_table where student_id in   (select id from student where class_id=(      select id from class where classname='Java班'))) tmp where st.score>tmp.avg

3.6 合并查询

合并查询用于合并结果集相同的两张（多张）表，它有两个关键字：

union
union all

union 使用
查询 id ⼩于 3 和名字为“英语”的课程：

select * from course where id<3unionselect * from course where name='英语';-- 或者使⽤ or 来实现select * from course where id<3 or name='英语';

MySQL学习记录【基本操作 → 数据操作 → 高级查询 → 联合查询】总结

union all 使用
该操作符用于取得两个结果集的并集。当使用该操作符时，不会去掉结果集中的重复行案例：查询 id 小于 3，或者名字为“英语”的课程。

-- 可以看到结果集中出现重复数据Javaselect * from course where id<3union allselect * from course where name='英语';

在这里插入图片描述

内容重点总结

MySQL索引，知识点总结
链接:link.
MySQL事务，知识点总结
链接:link.
Java通过 JDBC 连接数据库操作
链接: link.

MySQL学习记录 【基本操作 → 数据操作 → 高级查询 → 联合查询】 总结