开开心心带你学习MySQL数据库之第七篇

MySQL提供的约束
1.not null
2.unique
3.default
4.primary key
5.foreign key

表的设计

1. 找到实体
2. 确定实体间的关系
   • 一对一
   • 一对多
   • 多对多

聚合查询
~~行之间的运算
~~聚合函数
~~分组group by

联合查询

~~多表查询
~~笛卡尔积: 把两个表放到一起进行排列组合

班级表

学生表

学生表和班级表的笛卡尔积结果

笛卡尔积是得到了一张更大的表
笛卡尔积的列数是两个表列数之和
行数是两个表行数之积

由于笛卡尔积是排列组合出来的结果.这里的有些数据是无效/无意义的数据.

当去掉无效数据之后,笛卡尔积里剩余的数据就是当前每个同学在哪个班级里这样的信息了

像"两个classld对得上"在SQL中就可以使用一个 where子句的条件来描述
基于笛卡尔积＋条件进行查询,此时就是联合查询/多表查询

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),
    qq_mail 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  
	('计算机系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),
    ('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); 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58

(1)查询‘’许仙‘’同学的成绩
“许仙” => student表
成绩 => score表
~~要找的两个关键信息,存在于不同的表中,就需要进行联合查询
1)先把student表和score表笛卡尔积 => select * from student, score;
2)去掉无效数据 => select * from student, score where id = student_id;
3)按照“许仙‘’名字来筛选 => select * from student, score where id = student_id and student.name = '许仙';

当前结果的行数已经是所求了.还需要针对列数也精简一下~~
select student.name, score.score from student, score where id = student_id and student.name = '许仙';

如果列名重名怎么办?
通过表名.列名的方式来指定列
⭐️⭐️⭐️建议大家,多表查询的时候,显式的把表名给写出来
select * from student, score where student.id = score.student_id;

多表查询一般实现步骤:
1.分析清楚需求中,涉及到的信息都在哪些表里
2.针对这多个表进行笛卡尔积.
3.筛选出其中的有效数据(此处使用学生 id作为关联条件)
4.结合需求中的条件,进一步加强条件
5.针对列进行精简

多表查询查询‘’许仙‘’同学成绩的另一种写法
1)完成笛卡尔积 => select * from student join score;
2)此时,后续条件不是使用where关键字了,而是使用on
~~select * from student join score on student.id = score.student_id;
3)按照名字来筛选select * from student join score on student.id = score.student_id and student.name = '许仙';
4)精简列 select student.name, score.score from student join score on student.id = score.student_id and student.name = '许仙';

使用join on的方式和第一种方式,效果是相同的

(2)查询所有同学的总成绩，及同学的个人信息
总成绩 => 分数表/score表
个人信息 => 学生表/student表
此时需要按照学生姓名/学号进行分组(group by)再针对每个组,分别求和

select 
	student.name, 
	sum(score.score) 
from 
	student, 
	score where student.id = score.student_id 
group by 
	id;
1
2
3
4
5
6
7
8

联合查询,在学校上数据库课考试的时候,一定是最后的一个大题
实际开发中,慎重使用联合查询~~联合查询,本质上是"笛卡尔积"笛卡尔积,就把多个表排列组合…
如果表里的数据多,此时笛卡尔积开销是非常大的!!

(3)查询所有同学的成绩，课程名字,及同学名字
学生名字 => student表
课程名字 => course表
分数 => score表 => student 和 course表的关联表
三张表,就得指定两个连接条件

select 
	student.name, 
	course.name, 
	score.score 
from 
	student, 
	course, score 
where 
	student.id = score.student_id 
and 
	course.id = score.course_id;
1
2
3
4
5
6
7
8
9
10
11

---

绝大多数的面试题,其实都是清楚明确的,并不是死记硬背的
二叉搜索树查询的时间复杂度 => O(N)
哈希表查询的时间复杂度 => O(1)
默认是最坏,不是平均!!!单叉树,相当于链表了,
得是一个平衡的二叉搜索树,才能达到 logN~~TreeMap里面是红黑树

面试中一个最重要的原则,实事求是!!!
遇到不会千万不要瞎编,面试官阅人无数~~人家很容易能识别你是不是在编～被戳穿了，妥妥的小丑!!!

---

外连接

上述联合查询,其实都是"内连接"(inner join)
mysql还有一种联合查询,叫做外连接,
内连接和外连接都是进行笛卡尔积.但是细节上有所差别

create table student(
	id int,
	name varchar(20)
);
create table score(
    student_id int, 
    score int
);
insert into student values
	(1, '张三'),
	(2, '李四'),
	(3, '王五');
insert into score values
	(1, 90),
	(2, 80),
	(3, 70);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

当前情况下,这两个表的数据是一 一对应的(第一个表的每个记录,在第二个表里都有体现.第二个表的每个记录,在第一个表里也都有体现)此时内连接和外连接,查询结果是相同的
内连接

select 
	name, 
	score 
from 
	student join score 
on 
	student.id = score.student_id;
1
2
3
4
5
6
7

外连接分为左外连接和右外连接,如果联合查询,左侧的表完全显示我们就说是左外连接;右侧的表完
全显示我们就说是右外连接
左外连接

-- 左外连接，表1完全显示
select 字段名 from 表名1 left join 表名2 on 连接条件;

select 
	name, 
	score 
from 
	student left join score 
on 
	student.id = score.student_id;
1
2
3
4
5
6
7
8
9
10

右外连接

-- 右外连接，表2完全显示
select 字段 from 表名1 right join 表名2 on 连接条件;

select 
	name, score 
from 
	student right join score 
on 
	student.id = score.student_id;
1
2
3
4
5
6
7
8
9

如果两个表中数据不再一一对应了，此时进行内连接,结果就只是两个表中都有体现的数据
如果进行左外连接,就是以左侧的表为准.左侧表中的所有数据都能体现出来
如果进行右外连接,就是以右侧表为准.右侧表中的所有数据都体现出来

相比之下,内连接的使用是更多一些.外连接主要是一些特定场景
三张表联合查询怎么外连接查询的?
join on也是可以针对多个表来进行的~~select * from 表1 join 表2 on 条件1 join 表3 on 条件2

三张表 => 左表, 中表, 右表
先考虑左表和中表进行外连接. 得到一个"临时"表
再拿这个"临时"表和右表进行外连接
select * from 表1 [left/right] join 表2 on 条件1 [left/right] join 表3 on 条件2

自连接

~~ 奇淫巧技,特殊情况下的特殊操作,并非是一般用法,了解即可
自己和自己连接 => 自己和自己做笛卡尔积 => 这个操作本质上是把"行"转成"列"
~~ SQL中进行条件查询,都是指定某一列/多个列之间进行关系运算,
无法行和行之间关系运算,有的时候为了实现这种行之间的比较,就需要把行关系转成列关系
案例:显示所有“计算机原理”成绩比“Java”成绩高的成绩信息
“计算机原理” ,“Java” => course表里
成绩信息 => score表里
自连接需要指定表的别名

select 
	* 
from 
	score as s1, 
	score as s2 
where 
	s1.student_id = s2.student_id 
	and s1.course_id = 1 
	and s2.course_id = 3 
	and s1.score < s2.score;
1
2
3
4
5
6
7
8
9
10

SQL的逻辑表达能力是有限的.很多时候不是做不了,而是代价大.相比之下,使用Java这样的语言来描述复杂逻辑是更好的选择!!

子查询

两个字 ~~套娃 => 把多个查询语句合并成一个
~~子查询一旦嵌套的层次多了,对于代码的可读性是毁灭性的打击!!
子查询,在实际开发中使用的时候也要慎重!!!

单行子查询：返回一行记录的子查询
查询与“不想毕业” 同学的同班同学

-- 第一步
select 
	classes_id 
from 
	student 
where = '不想毕业';
-- 第二步
select 
	name 
from 
	student 
where class_id = 1;

-- 上面两步查询等效于下面的一步查询
select 
	name 
from 
	student 
where 
	classes_id = (select classes_id from student where name = '不想毕业');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

多行子查询：返回多行记录的子查询
查询“语文”或“英文”课程的成绩信息

-- 第一步
select 
	id 
from 
	course 
where name = '语文' or name = '英文';
-- 第二步
select 
	* 
from 
	score 
where 
	course_id in (4, 6);
-- 上面两步查询等效于下面的一步查询
select 
	* 
from 
	score 
where 
	course_id in 
	(select id from course where name = '语文' or name = '英文');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

合并查询

把两个查询语句的结果合并到一起
union关键字完成
~~ C 进阶里面,自定义类型详解.
struct => 结构体
union => 联合体 => 本质上就是让一个内存上的数据,可以有多种理解方式~好比一个人,有好多种身份是一样的
java里有没有union??为啥java就没有了呢??~~
~~ 时代变了!!!C 当年所面对的编程环境是比较的落后的~~ 计算机提供的硬件资源非常有限!!!内存只有几 kb,那就得把空间利用到极致~~ 后来计算机突飞猛进,此时,内存已经不值钱了 => union就没有用武之地了
小时候的游戏小霸王游戏机/红白机上的FC游戏魂斗罗,超级玛丽,一个游戏空间,大概64kb,大的是128kb.在64kb的空间里,有很多关卡,很多角色,很多npc,很多地图,很多道具,还有动画效果,音频效果,各种剧情…更可怕的是,还有3D游戏~~ => 对内存的空间利用达到了令人震惊的地步,那些年的开发FC游戏的程序猿,都是大神!!!
案例:查询id小于3，或者名字为“英文”的课程

select * from course where id < 3 or name = '英文';
-- 与下面的SQL语句等效
select * from course where id < 3 
union 
select * from course where name = '英文';
1
2
3
4
5

注:or只能针对一个表,而union可以把多个表的查询结果合(要求多个结果列得对应),适用范围更广一点,比or更强一些;
union会自动去重,union all 不会去重