MySQL的复杂查询-索引-事务

复杂查询

基本查询

SELECT 字句
FROM 字句
WHERE 字句
ORDER BY 字句
LIMIT 字句

聚合查询

GROUP BY 字句（可以独立出现）
HAVING 字句（必须跟在 GROUP BY 后面）

1.聚合函数（独立于 GROUP BY)
1）COUNT
2) SUM / AVG / MAX / MIN

2.GROUP BY
1) 分组凭证
2）支持多个分组
3) SELECT 字句有限制 （MySQL 有例外）：聚合函数 OR 分组凭证

3.HAVING
1）和 WHERE 对比： WHERE 对聚合前的数据进行过滤；HAVING 对聚合后的数据进 行过滤

联表查询

多张表进行联系的查询，不加筛选条件，出现的结果就是一个笛卡尔积
一般都要使用联系字段进行过滤，例如：文章中的作者 id ，评论中的作者 id ，评论中的文章 id

测试准用数据

create table classes(	
	id int primary key auto_increment,
	name varchar(255),
	`desc` varchar(255)
	); 
create table student (	
	id int primary key auto_increment,	
	sn int,	name varchar(255),
	qq_mail varchar(255),
	classes_id int
		);
create table course (
		id int primary key auto_increment,
		name varchar(255)
		); 
create table score (
	student_id int,
		course_id int,
			score float
			);
insert into classes(name, `desc`) values ('计算机系2019级1班', '学习了计算机原理、C和Java语言、数据结构和算法'),('中文系2019级3班','学习了中国传统文学'),('自动化2019级5班','学习了机械自动化'); 
insert into student(sn, name, qq_mail, classes_id) values('09982','黑旋风李逵','xuanfeng@qq.com',1),('00835','菩提老祖',null,1),('00391','白素贞',null,1),('00031','许仙','xuxian@qq.com',1),('00054','不想毕业',null,1),('51234','好好说话','say@qq.com',2),('83223','tellme',null,2),('3527','幽灵人',null,8),('09527','老外学中文','foreigner@qq.com',2);
insert into course(name) values('Java'),('中国传统文化'),('计算机原理'),('语文'),('高阶数学'),('英文');
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);

内连接（inner join) vs 外连接（outer join）

内连接：

左外联

右外联

MySQL 不支持 全外联（Full Join）

自连接

因为是同一张表联表，所以必须起不同别名以作区分

示例：筛选出 score 中的 java分数 < 计算机原理分数的学生

先找到两门课程的id ：

给两个课程分别起名字作为两个联表

查询 Java 分数 < 计算机分数的同学

子查询

先通过一个 SELECT 查询，得到一个结果集，利用这个结果集做第二次查询
1、把第一次的结果集看做一张表做新的查询
2、把第一次的结果集作为过滤条件查询

练习：
1.名字是“不想毕业”的同学的同班同学

方法一：
1） 查询“不想毕业”同学所属的班级id

2）根据查询到的 classes_id 查询 ‘不想毕业’ 的同班同学

方法二（子查询）：

返回多行的记录的子查询
IN关键字

示例2.查询“语文”，“英文”成绩
方法一（分步查询）：
1）查询 “语文”，“英文”的 course_id

2)根据查询到的 course_id 查询成绩

方法二（子查询）：

EXISTS 关键字

可以多层嵌套
但必须要起一个别名

合并查询

SELECT 字段1，字段2 FROM 表1；
1，张三
2，李四 1，张三
2，李四
UNION >>>
1，小王
SELECT 字段1，字段2 FROM 表2； 3，小刘
1，小王
3，小刘

UNION vs UNION ALL
UNION 会进行合并重复项
UNION 不会合并重复项

索引（Index）

加速查询的一种数据结构

优点：
提升了查询效率
缺点：
降低了插入/修改/删除的效率
增加了磁盘的使用空间

使用场景
索引不是无限制加的，根据需要来加
1）数据量够大
2）索引应该加在经常查找的字段上

索引的分类
1、主键 Primary Key
2、唯一键 Unique
3、普通索引（一般提到索引，没有特别说明，都是这个） key|lndex

调优查询的工具：
EXPLAIN 命令

正常搜索数据

创建索引

速度变快

事务（Transaction）

CREATE TABLE accounts (name VARCHAR(255),balance INT); INSERT INTO accounts (name, balance) VALUES ('小高', 300), ('小陈', 500);

退出后重新进入，查询发现数据并没有发生更改
进行下图操作后再查询，数据发生了更改