MySQL表的增删改查（进阶）

一、数据库约束

概念：
mysql提供的机制，辅助我们自动的依赖程序，对数据进行检查。一旦把约束确定好了，后续在进行增删改查时，mysql就会自动的对修改的数据做出检查，看这个数据是否满足约束，如果不满足，就会报错

1. 约束类型

NOT NULL - 指示某列不能存储 NULL 值。
UNIQUE - 保证某列的每行必须有唯一的值。
DEFAULT - 规定没有给列赋值时的默认值。
PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。
FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。
CHECK - 保证列中的值符合指定的条件。对于MySQL数据库，对CHECK子句进行分析，但是忽略CHECK子句（java4 不考虑）

1. NULL约束

-- 创建表时，可以指定某列不为空
DROP TABLE IF EXISTS student;
CREATE TABLE student (
   id INT NOT NULL,
   sn INT,
   name VARCHAR(20),
   qq_mail VARCHAR(20)
);

2. UNIQUE：唯一约束

• unique 唯一，不允许存在两行数据，在这个指定列上重复。即，指定列不同行之间不能重复，同一行不同列之间可以重复，不同行不同列之间也可以重复
• 针对带有unique约束的数据，在插入或插入记录的时候，就会先进行查询，看看结果是否已经存在，存在才操作，不存在，则不会操作了。此处是通过额外的查询操作，来确保此处的操作不会造成重复数据，会消耗额外的时间

-- 重新设置学生表结构
DROP TABLE IF EXISTS student;
CREATE TABLE student (
   id INT NOT NULL,
   sn INT UNIQUE,
   name VARCHAR(20),
   qq_mail VARCHAR(20)
);

3. DEFAULT：默认值约束

• 使用指定列插入的时候，未被指定的列就会按照默认值来填充，其中如果不修改默认值，默认情况下的默认值就是NULL。
• 但插入数据的时候，大多数情况下都还是需要手动指定插入的值的，使用默认值，会让代码看起来没那么直观（代码和自设的默认值没太大关系）

CREATE TABLE student (
   id INT NOT NULL,
   name VARCHAR(20) DEFAULT '未命名',
);

4. PRIMARY KEY：主键约束

主键就是一条数据的身份标识，根据这个主键可以唯一确定某个数据。

• 主键要求：非空 + 不能重复
• 一个表只能有一个主键，但是一个主键可以对应一个列或者多个列（联合主键）

CREATE TABLE student (
   id INT NOT NULL PRIMARY KEY,
   name VARCHAR(20)
);

自增主键

• 对于整数类型的主键，常配搭自增长auto_increment来使用。插入数据对应字段不给值时（可以自己决定是否要手动给值），使用最大值+1
• id分配方式：mysql会记录当前id中的最大值，下一个分配的id，就会在之前最大值的基础上，继续自增。所有会存在浪费的情况，但是mysql一共可以表示42亿个数据，浪费几个数据没关系

-- 主键是 NOT NULL 和 UNIQUE 的结合，可以不用 NOT NULL
id INT PRIMARY KEY auto_increment

关于自增主键的最大值

• 如果mysql是一个单个节点的系统，基于上述策略，没有问题
• 如果mysql是一个分布式系统，要想分配这种表示唯一性的id，就不能依赖mysql的自增主键了（每个主机上的mysql只知道自己这里存储的最大值）
  • 分布式系统
    • 概念：有好几个主机，每个主机上都有mysql，要求它们之间相互配合
    • 作用：联合硬件资源（一台主机的硬件资源是有限的）
  • 解决方法：
    • 根据分布式id的生成算法（把id作为一个字符串），保证系统中每个节点生成的id都是唯一的
    • 核心公式：由主机编号/机房编号、时间戳、随机因子三部分组成的字符串格式的id，可以确保唯一性

5. FOREIGN KEY：外键约束

• 使用场景：涉及两个表之间的约束，如学生表中的classId 必须在班级表中存在才合理，即学生表被班级表约束。术语上，学生表是子表，班级表是父表，子表要听父表的。注意，约束是双向的，班级表也会被学生表所约束。具体看下面的两种情景
• 外键用于关联其他表的主键或唯一键
• 创建外键约束的时候，是修改子表的代码，父表代码是不受影响的
• 两种情景：
  • 插入或修改子表中受约束的这一列的数据，就需要保证插入/修改后的数据，在父表中是存在的
  • 约束是双向的，删除/修改父表中的记录，需要看这个记录是否在子表中被使用了，如果被使用了，则不能进行删除或者修改

foreign key (字段名) references 主表(列) 
-- 字段名表示【当前这个表的那一类要收到约束】

案例

失败，因为设置外键的时候，会导致子表操作时频繁地查询父表，这个操作十分耗时，为了加快查询速度，如果父表中的某一列带有索引的话，就很方便找。后来干脆，规定，没有索引，就不能建立外键约束
primary key 和 unique 这两个约束，自带索引。

6. CHECK约束（了解）

写一个具体的条件表达式，当前的记录符合条件，就是可以插入/修改的，不符合条件，就失败。但对于mysql5来说，check并不支持（写上不会报错，但也没什么用），故忽略该约束

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

二、表的设计

设计数据库的表，就需要先把实体和关系梳理清楚。实体/对象是从需求场景中，提炼出的一些关键性质的名词，能够描述出学生和班级的相关信息

2.1 一对一

组句：一个学生可以有一个教务系统账号，但是一个教务系统账号只能归一个学生所有

三种创建方法：

2.2 一对多

组句：一个同学只能在一个班级里，但是一个班级可以有很多个学生

两种创建方法

2.3 多对多

组句：一个同学可以选择多门课程来学习，一门课程，也可以被多个同学来选择

创建方法需要引入一个关联表

2.4 没关系

单个独自美丽也很好

三、新增 + 查询

• 语法

-- 把select查询出来的结果数据，插入到另一个表中
-- 此时select查询的结果，需要和插入的表，能一一对应
INSERT INTO table_name [(column [, column ...])] SELECT ...

CREATETABLE test_user (
    id INT primary key auto_increment,
    name VARCHAR(20) comment '姓名',
    age INT comment '年龄',
    email VARCHAR(20) comment '邮箱',
    sex varchar(1) comment '性别',
    mobile varchar(20) comment '手机号'
);
-- 将学生表中的所有数据复制到用户表
insertinto test_user(name, email) select name, qq_mail from student;

四、聚合查询

查询的时候带表达式，本质是列和列之间进行运算，而行与行之间的运算需要使用聚合查询，聚合查询依托于聚合函数

sql中，聚合函数必须和（）紧挨在一起

1.聚合函数

函数	说明
COUNT([DISTINCT] expr)	返回查询到的数据的数量
SUM([DISTINCT] expr)	返回查询到的数据的总和，不是数字没有意义
AVG([DISTINCT] expr)	返回查询到的数据的平均值，不是数字没有意义
MAX([DISTINCT] expr)	返回查询到的数据的最大值，不是数字没有意义
MIN([DISTINCT] expr)	返回查询到的数据的最小值，不是数字没有意义

1.COUNT
空值不会被算入数字个数之中

-- 统计班级共有多少同学
SELECT COUNT(*) FROM student;      -- 一共有多少行
SELECT COUNT(0) FROM student;

-- 统计班级收集的 qq_mail 有多少个，qq_mail 为 NULL 的数据不会计入结果SELECT 
COUNT(qq_mail) FROM student;

2.SUM

• sum 进行求和，会把这一列的若干行，按照double的方式进行累加（会尝试先把这一列的每一行的数据线转成double）
• 如果是String类型，sum尝试把每一行数据都转为double，但是转化失败，注意并没有中断求和操作，而是跳过继续下一行，同时记录一个warning。用show warnings 可以查看当前所有的警告

-- 统计数学成绩总分
SELECTSUM(math) FROM exam_result;
-- 不及格 < 60 的总分，没有结果，返回 NULL
SELECTSUM(math) FROM exam_result WHERE math < 60;

3.AVG

-- 统计平均总分
SELECT AVG(chinese + math + english) 平均总分 FROM exam_result;

4.MAX

-- 返回英语最高分
SELECT MAX(english) FROM exam_result;

5.MIN

-- 返回 > 70 分以上的数学最低分
SELECT MIN(math) FROM exam_result WHERE math > 70;

2.分组查询

1. 概述

使用场景：希望把数据分成多组来进行计算，这时候可以通过GROUP BY来实现
使用方法：group by 通过指定一个列，可以按照这一列进行分组，而这一列中，数值相同的行，会被分到一组。而每个分组，都可以使用聚合函数进行计算。
注意：select中指定的列，必须是当前group by 指定的列，如果select 中想用到其他的列，其他的列必须放到聚合函数中，不然查询的结果，会无意义

-- 语法
select column1, sum(column2), .. from table group by column1,column3

2.分组之前的条件 where

-- 求每个岗位的平均薪资，除了张三
select role, avg(salary) from emp where name != '张三' group by role;

2.分组之后的条件 having

分组之后的条件，也是要搭配聚合函数来使用的

select role, avg(salary) from emp group by role having avg(salary) < 20000;

3.分组前，分组后都包含

先执行where，在执行group by，然后select查询，最后是having

select role, avg(salary) from emp where name != '张三' group by role having avg(salary) < 20000;

五、联合查询（多表查询）

使用场景：需要结合多个表的数据，进行一些综合性更强的查询
概念：多表查询是对多张表的数据取笛卡尔积，不管是内连接还是外连接都需要用到笛卡尔积
缺点：笛卡尔积是一个非常低效的操作，尤其是当每个表的数据都比较多的时候，也因此，多表联合查询也是一个非常低效的操作（数据少的时候，执行笛卡尔积的过程确实也快，但是控制打印台会一条一条打印，要花费时间）

• 笛卡尔积

  • 笛卡尔积描述了多表查询的基本的执行逻辑，就是把两个表里的数据，按照排列组合的方式，构造成一个更大的表
    • 笛卡尔积的列数，就是原来这多个表的列数之和
    • 笛卡尔积的行数，即使原来这多个表的行数值积
  • 因为是排列组合，所以有的数据是不靠谱，即和客观情况不一致的，我们只要加上一个链接条件，这样就能筛选出合理的数据了

• 联合查询操作步骤（示例是内连接的一种）

  • 计算笛卡尔积：select XXX from 表1，表2，……           此时出现的就是排列组合式的结果
  • 添加连接条件：where 表名1.列名1 = 表名1.列名2。
    • 进行联合查询的多个表中，有些列名可能是一样的，如果没有重复可以直接写列名，但是为了方便，都加上表名
    • 此时出现的就是合理的数据

1. 内连接

-- 语法
select 字段 from 表1 别名1 [inner] join 表2 别名2 on 连接条件 and 其他条件;
select 字段 from 表1 别名1,表2 别名2 where 连接条件 and 其他条件

1. 方法2对比方法1，多个表，方法2使用逗号类分割，方法1用join来分割
2. 连接条件，方法2通过where来指定，方法1通过on来指定
3. 主要使用方法2

2. 外连接

• 内连接产生的结果，一定是两个表中都存在的数据（公共的），外连接相反
• 外连接在mysql中分为左外连接（left join）和右外连接（right join）
  • 左外连接就是以左侧的表为主，左侧表的每个记录都会存在于最终结果里，如果有左表存在，但右表中不存在的数据，就会把对应的列填为null。右连接同理。

ps.全外连接（mysql不支持，但是Oracle支持）

3. 自连接

本质上是自己和自己笛卡尔积，将“行之间的关系”转换成“列之间的关系”，因为sql无法进行“行与行之间的比较”

操作步骤

• 求笛卡尔积（select的时候，多个表名不能相同，所以需要取别名）

select * from score as s1, score as s2; --正确
select * from score, score;     -- 错误

• 添加连接条件

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

4. 子连接

本质上是把多个SQL合并为一个SQL，但这会让代码变得复杂，所以尽量不要使用

简单版本（分两步，化繁为简）

select name from student where classes_id = 1 and name != '不想毕业';

子查询版本（化简为繁，不支持）

select name from student where classes_id = (select classes_id from student where name = '不想毕业') and name != '不想毕业';

5. 合并连接

把多个select所查询得到的结果集合成一个，前后查询的结果集中，结果集的列需要一致

• union
  • 该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中的重复行

-- 查询id小于3，或者名字为“英文”的课程：
select * from course where id<3
union
select * from course where name='英文';

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

• union all
  • 相比于union不会去重复行

-- 查询id小于3，或者名字为“Java”的课程
select * from course where id<3
union all
select * from course where name='英文';