数据库进阶_完整性、多表增删改查和数据库优化（MySQL)

1. 数据完整性

1.1 什么是数据的完整性？

​ 在创建表时给表中添加约束，用来保证存放到数据库中的数据是有效的，即数据的有效性和准确性。

1.2 完整性的分类

• 实体完整性（行）
• 域完整性（列）
• 参照完整性（也叫引用完整性，关联表，创建表时设置）

1.2.1 实体完整性

​ 表中的一行记录代表一个实体（entity），实体完整性的作用是标识每一行数据不重复。

1. 主键约束（primary key）

   每个表中要有一个主键，其作用为使数据唯一，且不能为null。

   1. 使用DDL，创建表中字段的时候直接在后面添加主键约束；

      CREATE TABLE student(
          id int primary key,
          name varchar(50)
      );
      

   2. 使用DDL，创建表时，定义完字段后最后加上主键约束，优势在于可以创建联合主键；

      CREATE TABLE student(
          id int,
          name varchar(50),
          primary key(id)
      );
      
      CREATE TABLE student(
          classid int,
          stuid int,
          name varchar(50),
          primary key(classid,stuid)
      );
      

   3. 使用DDL，给已创建的指定表添加主键。

      ALTER TABLE student ADD PRIMARY KEY (id);
      

2. 唯一约束(unique)

   其作用为使数据不能重复，允许一次为null。

   CREATE TABLE student(
       id int primary key,
       name varchar(50) unique
   );
   

3. 自动增长列(auto_increment)

   给主键添加自动增长的数值，列只能是整数类型。（SQL Server数据库里叫identity-标识列、Oracle数据库里叫sequence-序列）

   CREATE TABLE student( 
       id int primary key auto_increment, 
       name varchar(50) 
   )auto_increment = 2;##可以定义初始值，默认从1开始
   
   INSERT INTO student(name) values(‘tom’);
   

1.2.2 域完整性

​ 限制此单元格的数据正确，不与此列的其它单元格比较。

1. 非空约束(not null)

   CREATE TABLE student(
       id int primary key,
       name varchar(50) not null,
       sex varchar(10)
   );
   

2. 默认值约束(default)

   CREATE TABLE student(
       id int primary key,
       name varchar(50) not null,
       sex varchar(10) default '男'
   );
   
   insert intostudent1 values(1,'tom','女');
   insert intostudent1 values(2,'jerry',default);
   insert intostudent1 values(3,'jerry');
   

3. check约束（mysql不支持，其他的数据库提供）

1.2.3 参照完整性

​ 实体完整性和域完整性主要关注的是单表存储数据的有效性和准确性，而参照完整性考虑的是表与表之间的完整性。

1. 外键约束(FOREIGN KEY)

   在保存了大量数据的数据库中，外键用多了会导致性能降低。

   1. 创建表时添加表中的外键约束；

      格式:constraint 自定义外键名称 foreign key(外键列名) references 关联表名(主键列名)。

      (注意：外键列的数据类型一定要与指定关联表中的主键的类型一致)

      CREATE TABLE student(
          id int primary key,
          name varchar(50) not null,
          sex varchar(10) default '男'
      );
      
      create table score(
          id int primary key,
          score int,
          sid int,
          constraint fk_score_sid foreign key(sid) references student(id)
      );
      

   2. 使用DDL，创建表之后，增加表中的外键约束。

      格式：ALTER 被修改的表名 ADD CONSTRAINT 自定义外键约束名称 FOREIGN KEY(外键列名) REFERENCES 关联表名(主键列名);

      ALTER TABLEscore1 ADD CONSTRAINT fk_stu_score FOREIGN KEY(sid) REFERENCES stu(id);
      

---

2. 多表查询

2.1 多表关系

2.1.1 一对多/多对一关系

​ 例如：客户和订单、分类和商品、部门和员工。

​ 一对多建表原则：在多的一方创建一个字段，字段作为外键指向一的一方的主键。

2.1.2 多对多关系

​ 例如：学生和课程。

​ 多对多关系建表原则：需要创建第三张表，中间表中至少两个字段，这两个字段分别作为外键指向各自一方的主键。

2.1.3 一对一关系

​ 在实际的开发中应用不多，因为一对一可以创建成一张表。

​ 一对一的两种建表原则：

1. 唯一外键对应

   ​ 假设一对一是一个一对多的关系，在多的一方创建一个外键指向一的一方的主键，将外键设置为unique。

2. 主键对应

   ​ 让一对一的双方的主键进行建立关系（一个表的主键，同时也是外键指向另一个表的主键）。

2.2 多表查询

2.2.1 合并结果集

​ 合并结果集就是把两个select语句的查询结果合并到一起，被合并的两个结果的列数、列类型必须相同。

2.2.1.1 UNION

​ 去除重复记录，例如：SELECT* FROM t1 UNION SELECT * FROM t2；

2.2.1.2 UNION ALL

​ 不去除重复记录，例如：SELECT * FROM t1 UNION ALL SELECT * FROM t2。

2.2.2 连接查询

​ 连接查询就是求出多个表的笛卡尔积，假设集合A={a,b}，集合B={0,1,2}，则两个集合的笛卡尔积为{(a,0),(a,1), (a,2),(b,0),(b,1),(b,2)}。可以扩展到多个集合的情况。

​

​ 但是得到的笛卡儿积结果中，很多结果不是我们想要的，这时候我们需要对结果进行过滤，即使用主外键关系做为条件来去除无用信息。

2.2.2.1 内连接

​ 多表的内连接查询中表的顺序可以互换。

​ 两表内连接的标准语法格式：

select 列名
from 表1
inner join 表2
on 表1.列名=表2.列名 //外键列的关系
where.....

​ 等价于（不标准）：

select 列名
from 表1,表2
where 表1.列名=表2.列名 and ...(其他条件)

​ 三表内连接查询的标准语法格式：

 select 列名 from 表1
 inner join 表2 on 表1.列名=表2.列名
 inner join 表3 on 表1或表2.列名=表3.列名 where 条件...

​ 等价于（不标准）：

select 列名 from 表1,表2,表3
where 表1.列名=表2.列名 and 表1/表2.列名=表3.列名

2.2.2.2 外连接

​ 包括左外连接和右外连接，主表和次表不能随意调换位置。主表数据全部显示，次表数据匹配显示，能匹配到的显示数据，匹配不成功的显示null。

​ 外连接的特点是查询出的结果存在不满足条件的可能，一般作为子查询使用。

2.2.2.2.1 左外连接

​ 左连接是先查询出左表（即以左表为主），然后查询右表，右表中满足条件的显示出来，不满足条件的显示NULL。

SELECT * FROM emp e
LEFT OUTER JOIN dept d
ON e.deptno=d.deptno;

2.2.2.2.2 右外连接

​ 右连接就是先把右表中所有记录都查询出来，然后左表满足条件的显示，不满足显示NULL。

SELECT * FROM emp e
RIGHT OUTER JOIN dept d
ON e.deptno=d.deptno;

2.2.2.3 自然连接

​ 自然连接是一种特殊的等值连接，他要求两个关系表中进行连接的必须是相同的属性列（名字相同），无须添加连接条件，并且在结果中消除重复的属性列。

select * from emp e natural join dept d;

2.2.3 子查询

​ 即嵌套查询，SELECT中包含SELECT，如果一条语句中存在两个或两个以上SELECT，那么 就是子查询语句了。

• 子查询出现的位置

  • where后，作为被查询的一条件的一部分，(当子查询结果集形式为多行单列时)可以使用如下关键字：

    • any；

    • all。

      SELECT * FROM emp WHERE sal > ALL (SELECT sal FROM emp WHERE deptno=30)
      

  • from后，作表。

2.3 多表新增

insert into 表名(列名) values (列值),(列值),(列值);

2.4 多表更新

update 表1,表2 set 列名=列值 where 表1.列名=表2.列名 and 其他限定条件

update 表1
 inner join 表2 on 表1.列名=表2.列名
 set 列名=列值
 where 限定条件

2.5 多表删除

​ 语法：

delete 被删除数据的表 from 删除操作中使用的表 where 限定条件

​ 示例：

##删除人事部信息
delete d,e,s from department d,employee e,salary s
where d.depid=e.depid and s.empid=e.empid and depname='人事部'

2.6 日期运算函数

• now() 获得当前系统时间

• year(日期值) 获得日期值中的年份

• date_add(日期,interval 计算值 计算的字段);

​

例如：date_add(now(),interval -40 year)；//40年前的今天

​ 计算值大于0表示往后推日期，小于0表示往前推日期。

---

3. SQL优化

1. 对查询进行优化，要尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引；

2. 应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描（备注、描述、评论等可以设置成null，其他的字段最好设置成not null，添加的时候如果不知道添加什么也可以在这个字段上设置defult默认值）；

3. 应尽量避免在 where 子句中使用 != 或 <> 操作符，否则引擎将放弃使用索引而进行全表扫描；

4. 应尽量避免在 where 子句中使用 or 来连接条件，如果一个字段有索引，一个字段没有索引，将导致 引擎放弃使用索引而进行全表扫描；

   select id from t where num = 10 or name = 'admin';
   ##改为
   select id from t where num = 10
   union all
   select id from t where name = 'admin'
   

5. in 和 not in 也要慎用，否则会导致全表扫描，对于连续的数值，能用 between 就不要用 in 了，很多时候用 exists 代替 in 是一个好的选择；

6. 尽量使用连接代替子查询，因为使用 join 时，MySQL 不会在内存中创建临时表。

注：我这里的sql优化还不是特别全，看到一位大佬写的sql性能优化，说得比较详细，这里给个跳转链接。