MySQL表的增删改查

1.数据库约束

对于某一列的值能添加哪些内容做了一定限制，这种限制手段称为约束。

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

1.1非空约束 NOT NULL

若在某一属性定义时规定该属性not null，则不能将空值存放在该属性中。

create table test1(

   id int,
   name varchar(10) not null comment 'name属性不能存放空值'
);

只给name属性插入值：

若只给id属性插入值，约定了非空属性的name属性必须有默认值或显示插入：

Null的这一列表示是否允许字段为空：

如何在创建表之后添加浮空约束？

表约束属于表结构上的修改使用alter+ change

必须将当前表中要添加非空约束的属性所有为null的行先删除或者修改为非空

1.2唯一约束 UNIQUE

唯一约束指的是对应的字段是唯一的，不能重复。

当插入重复的id时，就会报错：

null值不受唯一键约束

查看唯一约束：

show keys from tb_name;

如何在创建表之后修改某个字段为唯一约束？

表约束属于表结构上的修改使用alter+ change

alter table tb_name add unique(属性名)；

给name属性加上唯一约束

1.3默认值约束 DEFAULAT

规定了默认值的表，在插入时若没有指定该列，则使用默认值插入数据。

I.插入时没有显示指定的sex值，则读取sex默认值；

II.若显示对默认值插入null值，不会触发默认值。

1.4主键约束 PRIMARY KEY

1）主键索引

将学生学号设置为主键：

1. 一张表只能有一个主键，primary = unique + not null

   （若某个属性使用unique + not null ，默认会将第一个unique + not null 共同约束的属性属性作为主键）

2. 主键约束的列值不能重复且不能为null

   

3. 主键约束可以由多个列共同组成（联合主键）

如何在创建表之后修改某个字段为主键约束？

只有当表中没有主键时才能添加主键

alter table tb_name add primary key(作为主键的属性)；

如何删除表中的主键？

alter table tb_name drop primary key；

2）复合主键

将id - name作为主键：

复合主键必须id - name完全相同才认为相同，部分属性不同就是不同的两条记录。

3）自增主键

因为主键不重复且不为空，一般作为主键的列都是int或者定长的char类型，因此我们可以将主键的增长交给数据库自动执行。

自增auto_increment，插入数据不给值时，使用最大值+1。

1. 自增主键可以显示的插入null或者不写都会触发自增主键

2. 关于自增主键删除后的自增情况

   仍然以当前最大值+1

   truncate之后的表会还原自增主键的值

   

3. 显示的给自增主键插入一个值之后，下一次复发自增操作时，仍以当前最大值为基准+1

1.5外键约束 FOREIGN KEY

涉及多个表之间的关联约束，用于关联其他表的主键或唯一键：

foreign key (当前表中的某个属性) references 主表（列）

比如：当前有一个学生表，还有一个班级表，学生表中有一列属性为该学生的班级信息

drop table if exists class;

--MySQL中的关键字作为属性名称，加上``括起来
create table class(
    id int primary key auto_increment,
    name varchar(10),
    `desc` varchar(100) comment '班级信息描述'
);

drop table if exists student;

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

学生表中的class id关联class表的id属性，学生表插入数据时，class id要能正确输入，必须在class表中id值时存在的

在class表中不存在id为1的值，因此插入失败。

在student从表中若有记录关联到了主表class的主键id，若要删除主表class的id行，必须确保所有表中关联的数据先删除

插入时先看主表，主表中有这个属性值才能在从表中插入；

删除时先看从表，从表中该属性关联的所有记录都删除后才能在主表中删除。

2.表的设计

数据库三范式：

• 一对一：一对一的两个属性一般都可以放在一个表中，例如学生和学号的关系都放在学生表中，姓名和身份证号的关系都放在公民信息表中；

• 一对多：例如一个同学只能在一个班级，一个班级可以包含多个同学，学生和班级就属于一（班级）对多（学生）的关系

  班级（id，name）

  学生（id，name，class_id）

  一对多的关系都可以使用这个套路来创建两张表，使用一对多的关系来创建表；

• 多对多：例如学生和课程的关系，一个学生可以选择多个课程，一个课程也可以被多个学生选择

  学生：

  id	name
  1	小马
  2	小林
  3	小胡

  课程：

  id	name
  101	语文
  102	数学
  103	英语

  多对多的关系——>创建一个学生-课程中间表来记录多对多的关系：stu_course(stu_id,course_id)

• 第一范式：确保每列的原子性（设计表时，每一列都不能再次分解）
• 第二范式：当前表中所有属性都和主键相关
• 第三范式：表中所有属性都和主键直接相关而不是间接相关

3.新增

根据查询结果插入：

insert into table_name (属性1，属性2，...) select 查询的属性...

案例：创建一张用户表，设计有name姓名、email邮箱、sex性别、mobile手机号字段。需要把已有的学生数据复制进来，可以复制的字段为name、qq_mail

-- 创建用户表 

DROP TABLE IF EXISTS test_user; 

CREATE TABLE 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 '手机号' 

);

-- 将学生表中的所有数据复制到用户表 

insert into test_user(name, email) select name, qq_mail from student; 

4.查询

4.1聚合查询

4.1.1聚合函数

常见的聚合函数：

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

聚合查询指的是把行之间数据进行聚合，和列无关。

案例：

• count

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

• sum

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

• avg

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

• max

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

• min

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

4.1.2 group by语句

一般聚合函数搭配group by语句分组查询使用

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

案例：

统计每个岗位的平均工资、最低工资和最高工资——>需要根据查询出来的结果按照role进行分组

select role,max(salary),min(salary),avg(salary) from emp group by role;

group by 可以使用别名

4.1.3 HAVING

分组之后进行条件查询必须使用having（和group by搭配使用，按照聚合后的条件过滤使用）

显示平均工资高于2000的角色和他的平均工资：

select role,avg(salary) from emp group by role having avg(salary) > 2000;

聚合之前的条件使用where

聚合之后的条件使用having

4.2 联合查询

笛卡尔积：实际上就是两个表之间的排列组合，多表查询得到的结果是两个表的组合，列数相加，行数相乘。然后根据两张表的关联关系，过滤掉不正确的数据，最后再对结果进行过滤。

4.2.1 内连接

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

select 字段 from 表1 别名1,表2 别名2 where 连接条件 and 其他条件; 

就是多表的笛卡尔积根据关联关系严格筛选结果，内连接的结果两张表的数据都不为空。内连接一定都是多个表同时都存在关联数据的情况，不会出现假设表1存在数据，表2没有数据，此时这条记录关联后就不会显示。

4.2.2 外连接

外连接：两张表查询时，若某个表存在空数据，仍然显示出来。外连接分为左外连接和右外连接。

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

-- 左外连接，表1完全显示 

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

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

-- 右外连接，表2完全显示 

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

4.2.3 自连接

一般 MySQL 筛选数据时都是不同行的列之间进行数据的比较，涉及到同一张表中行数据的筛选会用到自连接。

4.2.4 子查询

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

• 单行子查询：内部的查询sql返回单条记录的查询

  eg：查询与“小马”同学的同班同学：

  1. 查询出“小马”同学的班级id
  2. 根据查找出来的班级id去查询同班的其他同学
  3. 组装a和b两步

  select * from student 
  where classes_id = (select classes_id from student where name = '小马')
  and name != '小马';
  

• 多行子查询：内部嵌套的查询sql返回的多条记录的查询，内部的返回结果有多条的嵌套语句

  eg：查询“语文”或“英文”的成绩信息：

  1. in关键字

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

  2. exists关键字

     select * from score sco where 
     exists (select sco.id from course cou where (name='语文' or name='英文') 
     and cou.id = sco.course_id);
     

  多行子查询使用in或exists的区别：

  1. in执行过程中只有两次查询，先执行内部查询，根据内部查询的结果筛选外部条件，子查询结果会缓存到内存中，适用于子查询结果集比较小的情况，效率比较高；
  2. exists比较耗时，每次都是从外部查询中取出记录和内部查询匹配，内部子查询不会产生临时表，不耗费内存空间，适用于组查询结果集比较大，且内存放不下的情况。

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