MySQL表的增删改查（进阶）

1. 数据库约束

约束，就是让数据库帮助程序员更好地检查数据是否准确！

1.1 约束类型

not null - 指示某列不能存储 NULL 值。        (允许为null→选填项，不允许为null→必填项)

unique  - 保证某列的每行必须有唯一的值。        (表里存的数据不能有重复的值)

default - 规定没有给列赋值时的默认值。

primary key - not null  和  unique  的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。        (主键，每条记录的身份标识)

foreign key - 保证一个表中的数据匹配另一个表中的值的参照完整性。        (外键，多个表的关联关系，要求某个记录必须在另外一个表里存在)

check - 保证列中的值符合指定的条件。对于 MySQL 数据库，对 check子句进行分析，但是忽略check子句。        (显示通过条件来描述字段的取值)

1.2 NULL约束

创建表时，可以指定某列不为空：

1.3 UNIQUE：唯一约束

指定列是唯一的、不重复的：

1.4 DEFAULT：默认值约束

指定插入数据时，如果 name 列为空，那么就会填充默认值：

1.5 PRIMARY KEY：主键约束

主键约束，就是not null + unique

         因为 主键 和 unique 都是先查询再插入的过程，因此MySQL就会默认给primary key和unique这样的列自动添加索引，从而提高查询速度

在MySQL中，一个表里只能有一个主键，因此在实际开发中，主键往往是一个整数表示的id

但是MySQL允许把多个列放到一起共同作为一个主键(联合主键)

主键有个很常用的方法：" 自增主键" → 自动生成不重复的主键值        (加上 auto_increment)

        插入id的时候可以手动指定，也可以MySQL自动生成，这里的null并不会生成控制，而是会交给数据库使用 自增主键。自动生成的数据是根据上一条记录的基础增加的

1.6 FOREIGN KEY：外键约束

针对两个表之间产生的约束

         因为每次给子表插入数据时，都会查询夫表中对应的数值是否存在，默认情况下需要遍历整个表，因此需要使用索引(即想要创建外键，就要求父表对应的列得有 primary key 或者 unique 来约束，因为会自动添加索引)

此外键约束的含义：要求student里的classID要在class表的id列中存在

class就是父表，student就是子表

在class表中的id列存在的话就可以正常插入

否则不论是插入(insert)还是修改(update)都会报错

不仅仅是父表对子表有约束，子表也同样约束这父表

想要顺利的删除，得先删除子表再删除父表

2. 表的设计

表的设计/数据库的设计就是明确一个程序里，需要使用几个数据库，几个表，表里有哪些列

设计表/数据库 基本思路

以教务系统为例

2.1 先明确实体

和Java中的"对象"是类似的，需求中"关键的名词/概念"

比如教务系统的学生信息、老师信息、课程表......        学生、老师、课程表就可以称为"实体"

2.2 再明确实体之间的关系

2.2.1一对一关系

一个学生只能有一个账号(一个学生不能有多个账号)

一个账号只能被一个同学使用(一个账号不能给多个同学共享)

针对这种关系可以按以下方式创建：

1.把学生和账号放同一个表里

student_account(id, name, username, password);

2.学生和账号在不同表里，相互关联

2.2.2一对多关系

一个学生只能存在一个班级中

一个班级可以有多个多个学生

针对这种关系可以按以下方式创建：

1.student(id, name, classId);

   class(classId, name);

2.2.3一对多关系

一个学生可以选修多门课程

一个课程可以被多个学生选择

针对这种关系可以按以下方式创建：

总结

1.找实体，给每个实体都安排个表(需求中的关键名词)

2.明确实体的关系(往固定造句格式中套)

3.根据这些关系使用固定的方法建表即可

3. 新增

插入查询结果：把查询的结果插入到另一个表中

要求 查询结果 的临时表的列数和列的类型，要和student2匹配

4. 查询

4.1 聚合查询

本质上是针对 行和行 之间进行运算（带表达式查询是 列和列）

4.1.1 聚合函数

常见的统计总数、计算平局值等操作，可以使用聚合函数来实现，常见的聚合函数有：

count

这个操作相当于先进行select *，然后针对这个返回结果再进行count运算

不一定非要写 *，还可以写任意的 列名/ 表达式

* 的计算会把NULL的行也计数进去

name 计算的时候会去掉

sum

把这一列的所有行进行相加，要求都是数字（不能是字符串/日期）

sum会在运算的时候会对NULL进行特殊处理

这个表是临时表，不会受有效数字的约束

avg

求平均值

可以对某一列也可以针对表达式进行运算

max，min

求最大最小值

4.1.2 GROUP BY子句

不用group by分组的时候相当于只有一组，引入group by就可以针对不同的组进行聚合

准备测试表及数据：职员表，有 id （主键）、 name （姓名）、 role （角色）、 salary （薪水）

这样求得的平均薪水其实是没啥价值的，把同种职位的薪水取平均值才是我们更希望看到的

把role这一列值相同的行分成一组，然后针对每个分组计算平均值

4.1.3 HAVING

指定条件有两组情况

1.分组之前，先筛选再分组        where

除去“老板”这个职位的数据

2.分组之后，先分组再筛选        having

平均薪水小于10000的数据

也可以一个SQL中同时使用where和having

除去“周瑜”和“老板”再查询每个岗位的平均薪资

SQL语句执行的先后顺序表：

4.2 联合查询(多表查询)

多表查询的基本执行过程：笛卡尔积

通过观察可以发现很多数据是无意义的，例如张三并不在Java102这个班级中

因此我们可以通过SQL代码来去除这些没意义的数据

通过表名 . 列名 → 把这个用来筛选有效数据的条件成为“连接条件”

4.2.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), 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 ）查询 “ 许仙 ” 同学的成绩

如何具体进行联合查询？

1.先计算笛卡尔积         select * from student, score;

2.引入连接条件        select * from student, score where student.id = score.student_id;

3.再根据要求加入必要条件        select * from student, score where student.id = score.student_id and name = '许仙';

4.把不必要的列去掉        select student.name, score.score from student, score where student.id = score.student_id and name = '许仙';

（ 2 ）查询所有同学的总成绩，及同学的个人名字

1. select * from student, score;

2. select * from student, score where student.id = score.student_id;

3. select name,sum(score.score) from student, score where student.id = score.student_id group by student.name; 

（ 3 ）查询所有同学的成绩，及同学的全部个人信息

1. select * from student, course,score;

2. select * from student, course,score where student.id = score.student_id and course.id = score.course_id;

3.select student.name, course.name, score.score from student, course,score where student.id = score.student_id and course.id = score.course_id;

4.2.2 外连接

如果要连接的两个表之间数据 不是一一对应的，外连接和内连接才有区别

外连接分为左外连接和右外连接。

左外连接：左侧的表完全显示

右外连接：右侧的表完全显示

4.2.3 自连接（特殊情况下才用到）

自连接是指在同一张表连接自身进行查询。可以把行转成列，因为SQL无法对行和行之间进行条件比较，因此就可以使用自连接把行转成列

显示所有 “ 计算机原理 ” 成绩比 “Java” 成绩高的成绩信息

1.      select * from score as s1, score as s2;

2.      select * from score as s1, score as s2 where s1.student_id = s2.student_id;

3.      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;

4.      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;

4.2.4 子查询（嵌套查询）

把多个SQL组合成一个（不易读。效率也会低）

单行子查询：返回一条记录

查询与“不想毕业”同学一个班的同学

1.先查询“不想毕业”这个同学的班级id

2.再按照班级id来查询哪个同学和他一个班

子查询就是将1，2合并：select name from student where classes_id = (select classes_id from student where name = '不想毕业') and name != '不想毕业';         此时的 “=” 要求后面子查询只返回一条记录，否则就要多行子查询

多行子查询：返回多行记录

查询 “ 语文 ” 或 “ 英文 ” 课程的成绩信息

1.先根据名字查询出课程id

2.根据课程id查询出课程分数

[NOT] IN：

select * from score where course_id in (select id from course where name = '语文' or name = '英文');

[NOT] EXISTS：

        这个可读性比较差而且执行效率也远低于in写法，只有当in查询的结果太大内存放不下才用exists，那还不如多步查询，不仅通俗易懂还好操作。

4.2.5 合并查询

把两个查询的结果集合并成一个（要求这两个集的列要相同，）

查询 id 小于 3 ，或者名字为 “ 英文”的课程：

union（可以去重）

问题：为啥不用 or ？？

因为 or 的查询只能来源于一个表，而 union 可以是来自不同的表，只要查询的结果的列相同即可

union all（不去重，全部显示）