MySQL表的增删改查（进阶）

数据库约束类型

约束类型

• not null： 指示某列不能存储 null 值
• unique： 保证某列的每行必须有唯一的值
• default： 规定没有给列赋值时的默认值
• primary key ： not null 和 unique 的结合。确保某列（或者两个列多个列的结合）有唯一的标识，有助于更容易更快速的找到表中的一个特定的记录
• foreign key：保证一个表的数据匹配到另一个表中的值参照完整性
• check：保证列中的值符合指定的条件。

null 约束

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

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

unique: 唯一约束

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

default:默认值约束

default 指定的默认值，会在按列插入时生效（按列插入但未被指定的列，就是按默认值来插入）
如果手动给某一列设定为 null，不会触发 默认值 的效果

create table student(
	id int not null,
	name varchar(20) unique default 'aaa'
);

primary key: 主键约束

设计表的时候，一般都需要指定一个主键
主键就是一条记录的唯一身份标识

对于一张表来说，主键只能有一个
特性：

1. 不能为空
2. 不能重复

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

自增主键

create table student(
	id int primary key auto_increment,
	name varchar(20)
);

foreign key: 外键约束

外键用于关联其他表的主键或唯一键

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

create table class (
	id int primary key,
	name varchar(20)
);

create table student (
	id int primary key auto_increment,
	name varchar(20),
	classId int,
	
	foreign key(classId) references class(id)
);

外键约束就是要求当前表里面 classId 字段的值，必须在 class 表的 id 中出现过才可以（此时要求 id 得是 class 表的主键）

外键约束会影响到数据的修改和删除
如果 class 表中的 id 已经被 student 中的 classId 用到了，那么就不能删除 class 表中的对应记录，更不能删除整个 class 表

表的设计

所谓的“数据库设计”其实就是设计表，根据区当前问题的场景，这些表中应该有哪些字段，这些表中的约束等

设计数据库的基本思路：
从具体的问题场景中，先提取出“实体（对象、关键性的名词）”，让后再找出多个“实体”之间的关联关系

三种关系范式：

1. 一对一
2. 一对多
3. 多对多

查询

聚合查询

“聚合”值的事行之间的聚合，和“列之间”无关

聚合函数

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

• count ：数一数有多少行
  相当于先执行 select * from 表名
  在数一数结果中有多少行
  如果当前列里的值为 null ，不会被计数到其中

group by 子句

group by 是针对某一列，按照列里面的内容，把这些记录进行分组（值相同的在一组）

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

group by 往往要搭配 聚合函数 来使用，如果不是用聚合函数，此时尝试查询一些不重复的类，可能结果就不太科学

select 需要查询的字段 from 表名 group by 指定列;

having

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

select 需要查询的字段 from 表名 group by 指定列 having 筛选条件;

联合查询

注：
关联查询可以对关联表使用别名

实际开发中往往数据来自不同的表，所以需要多表联合查询。多表查询是对多张表的数据取笛卡尔积：

内连接

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

外连接

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

select 字段名 from 表名1 left join 表名2 on 连接条件;

右外连接

select 字段 from 表名1 right join 表名2 on 连接条件;

自连接

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

子查询

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

1. 多行子查询：但会多行记录的子查询
   查询“语文”或“英文”课程的成绩信息：
   • [not] in 关键字

-- 使用IN
select * from score where course_id in (select id from course where
name='语文' or name='英文');
-- 使用 NOT IN
select * from score where course_id not in (select id from course where
name!='语文' and name!='英文');

- [not] exists 关键字

-- 使用 EXISTS
select * from score sco where exists (select sco.id from course cou
where (name='语文' or name='英文') and cou.id = sco.course_id);
-- 使用 NOT EXISTS
select * from score sco where not exists (select sco.id from course cou
where (name!='语文' and name!='英文') and cou.id = sco.course_id);

2. 在from子句中使用子查询：子查询语句出现在from子句中。这里要用到数据查询的技巧，把一个
   子查询当做一个临时表使用。

合并查询

在实际应用中，为了合并多个select的执行结果，可以使用集合操作符 union，union all。使用union 和 union all时，前后查询的结果集中，字段需要一致。

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 ：
该操作符用于取得两个结果集的并集。当使用该操作符时，不会去掉结果集中的重复行。
查询id小于3，或者名字为“Java”的课程：

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