数据库高级

一、数据完整性

1、数据库的完整性

用来保证存放到数据库中的数据是有效的，即数据的有效性和准确性
确保数据的完整性=在创建表时给表中添加约束
完整性分类：

• 实体完整性（行完整性）
• 域完整性（列完整性）
• 引用完整性（关联表完整性）：
  主键约束：primary key
  唯一约束：unique [key]
  非空约束：not null
  默认约束：default
  自动增长：auto_increment
  外键约束：foreign key

建议这些约束应该在创建表的时候设置
多个约束条件之间使用空格间隔

示例：

create table student(
	studentno int primary key auto_icrement,//主键，自动增长
	loginpwd varchar(20) not null default '123456',//非空，有默认
	studentname varchar(50) not null,//非空
	sex char(2) not null,//非空
	gradeid int not null,//非空
	phone varchar(255) not null,//非空
	address varchar(255) default '学生宿舍'//有默认
	borndate datetime,
	email varchar(50)
);

2、实体完整性

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

约束类型：
主键约束（primary key）
唯一约束（unique）
自动增长列（auto_increment）

主键约束

注：每个表中要有一个主键
特点：数据唯一，且不能为null

示例：
第一种添加方式：
create table student(id int primary key,name varchar(50));
第二种添加方式：此方式优势在于，可以创建联合主键
create table student(id int,name varchar(50),primary key(id));
create table student(classid int,stuid int,name varchar(30),primary key(calssid,stuid));
第三种添加方式：

create table student(id int,name varchar(50));
alter table student add primary key(id);

唯一约束

特点：数据不能重复
例：

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

自动增长列

这个概念在一些数据库管理软件里有不同的称呼：
sqlserver数据库（identity-标识列）
oracle数据库（sequence-序列）
给主键添加自动增长的数值，列只能是整数类型

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

3、域完整性

域完整性的作用：限制此单元格的数据正确，不对照此列的其它单元格比较
域代表当前单元格
域完整性约束：数据类型 非空约束（not null）默认值约束（default）
check约束 （仅mysql8.0支持，之前的版本不支持） check(sex=‘男’ or sex=‘女’)

数据类型

数值类型：

日期类型：

字符串类型：

char和varchar类型类似，但它们保存和检索的方式不同，它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

binary和barbinary类类似于char和varchar，不同的是它们包含二进制字符串而不要非二进制字符串，也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。

blob是一个二进制大对象，可以容纳可变数量的数据。有四种blob类型：tintblob、blob、mediumblob、longblob。它们只是可容纳值的最大长度不同。

有四种text类型：tinytext、text、mediumtext和longtext。这些对应4种blob类型，有相同的最大长度和储存需求。

非空约束

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

默认值约束

default
create table student(id int primary key,name varchar(50) not null,sex varchar(10) default '男');

引用完整性

（参照完整性）
外键约束：foreign key

示例：
创建表时定义外键

create table student(
	id int primary key,
	name varchar(10) not null,
	sex varchar(10) default '男'
);
create table score(
	id int primary key,
	score int,
	sid int,
	constraint 自定义外键名称 foregin key(sid) references student(id)
);

添加外键
alter table score add constraint 自定义外键名 foregin key(sid) references student(id);

二、多表查询

多个表之间是有关系的，那么关系靠谁来维护？
夺标约束：外键列

1、多表的关系

一对多/多对一关系

例如客户（一）和订单（多），分类（一）和商品（多），部门（一）和员工（多）

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

多对多关系

例如学生和课程

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

一对一关系

例如夫妻

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

两种建表原则：

1. **唯一外键对应：**假设一对一是一个一对多关系，在多的一方创建一个外键指向一的一方的主键，将外键设置为unique（不允许重复）
2. ** 主键对应：**让一对一的双方的主键进行建立关系。

注意，我在利用navicat写测试代码时，发现通过navicat直接删除外键会报错，导致无法删除
解决办法是利用代码删除外键：
alter table 表名 drop foreign key 自定义的外键名称;
之后就可以正常操作了

多表查询

多表查询有如下几种：

1. 合并结果集：union、union all
2. 连接查询
   1. 内连接：[inter] join on
   2. 外连接：outer join on
      1. 左外连接：left [outer] join
      2. 右外连接：right [outer] join
      3. 全外连接：(mysql不支持) full join
   3. 自然连接：natural join
3. 子查询

合并结果集

作用：合并结果集就是把连个select语句的查询结果合并到一起~
合并结果集有两种方式：

• union：除去重复记录，例如：```select * from t1 union select from t2;``
• union all：不除去重复记录，例如：select * from t1 union all select from t2;
  
  
  注意：被合并的两个结果：列数、列类型必须相同。

连接查询

连接查询就是求出多个表的乘积，例如t1连接t2,那么查询出的结果就是t1*t2。

连接查询会产生笛卡尔积，假设集合A=(a,b)，开合B=(0,1,2)，则两个集合的笛卡尔积为((a,0),(a,1),(a,2),(b,0),(b,1),(b,2)).可以扩展到多个集合的情况。

那么多表查询产生这样的结果并不是我们想要的，那么怎么去除重复的，不想要的记录呢，当然是通过条件过滤，通常要查询的多个表之间都存在关联关系，那么就通过关联关系去除笛卡尔积。

示例一：现有两张表
emp1:

dept1:

我们来查询它们的笛卡尔积：

select * from emp1,dept1;

会发现有很多不合理的和无用的重复信息。

接下来使用主外键作为条件来去除无用信息：
deptno列，作为dept1表的主键，emp1表的外键

where 表1.列名=表2.列名;

上面查询结果会把两张表的所有列都查询出来，也许你不需要那么多列，这时指定要查询的列即可

1、内连接

内连接的特点：查询结果必须满足条件
上面的连接语句就是内连接，但它不是SQL标准中的查询方式，可以理解为方言
SQL标准语法：

select 列名
from 表1
inner join 表2
on 表1.列名=表2.列名 //主外键列的关系
where 限定条件

等价于：

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

注意：

• 表1和表2的顺序可以互换
• 找两张表的等值关系时，找表示相同含义的列作为等值关系
• 点操作符可以表示“的”，格式：表名.列名
• 可以使用as给表起别名，注意定义别名之后，统一使用别名

三表联查：
sql标准语法：

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

等价于

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

练习：




(1)

select student.stuname,`subject`.subjectName,score.score from student
inner join score on student.stuid=score.studentid
inner join `subject` on `subject`.subjectid=score.`subject` where student.stuname = '花儿';

(2)

select * from student
inner join score on student.stuid=score.studentid
inner join `subject` on `subject`.subjectid=score.`subject`;

(3)

select `subject`.*,score.* from score
inner join `subject` on score.`subject`=`subject`.subjectid where `subject`.subjectName='mysql';

(4)

select stuname,sum(score) from score
inner join student on student.stuid=score.studentid group by student.stuname;

(5)

select `subject`.subjectName,avg(score) from score
inner join `subject` on `subject`.subjectid=score.`subject` group by `subject`.subjectName;

2、外连接

包括左外连接和右外连接，外连接的特点：查询出的结果存在不满足条件的可能。
例如 显示还没有员工的部门名称

外联查询

左外联

语法：
select 列名 from 主表 left join 次表 on 主表.列名=次表.列名

• 主表的数据将全部显示，次表的数据匹配显示，能匹配到的显示数据，匹配不成功的显示null

• 主表和次表不能随意调换位置

• 使用场景：一般会作为子查询的语句使用

例：
现有两张表，部门表dept1，员工表emp1


注意上面部门编号为50的部门AA，员工表中并没有员工的部门编号是50，所以AA这条记录并不能满足emp1.deptno=dept1.deptno这个条件。

select * from dept1 
inner join emp1 on emp1.deptno=dept1.deptno ;

部门AA并没有显示出来，此时我们使用左连接查询

select * from dept1 
left join emp1 on emp1.deptno=dept1.deptno;

可以看到AA部门的信息显示出来的，只不过没有与它匹配的部员信息，所以后面属于emp1表的列显示为null

右外连
右外连接就是先把右边的表中所以记录都查询出来，然后左边的表匹配则显示，不匹配则为null

select * from emp1 
right join dept1 on emp1.deptno=dept1.deptno;

连接查询心得：

连接不限于两张表，连接查询也可以是三张、四张，甚至N张表的连接查询。通常连接查询不可能需要整个笛卡尔积，而只是需要其中一部分，那么这时候就需要使用条件来去除不需要的记录，这个条件大多数情况下都是使用主外键关系去除的。

两张表的连接查询一定有一个主外键关系，三张表的连接查询就一定有两个主外键关系，我们要学会使用主外键关系来作为条件去除笛卡尔积，这是熟练掌握连接查询的根本。

3、自然连接

自然连接（natural inner join）：自然连接是一张特殊的等值连接，他要求两个关系表中进行连接的必须是相同的属性列（名字相同），无需添加连接条件，并且在结果中消除重复的属性列。下面给出几个例子

情况一：两表存在名字相同，属性相同的列（deptno）：

select * from dept1 natural inner join emp1;

如图，emp1表的deptno列被去除掉了。

情况二：两表不存相同列：

select * from student natural inner join class;

此时，就会产生笛卡尔积。

子查询

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

子查询出现的位置：

• where后，作为被查询的一部分
• from后，作表

当子查询出现在where后作为条件时，还可以使用如下关键字：

• any
• all
• 当子查询结果集形式为多行单列时可以使用ALL或ANY关键字

子查询结果集的形式：

• 单行单列（用于条件）
• 单行多列（用于条件）
• 多行单列（用于条件）
• 多行多列（用于表）

示例：

现有一个员工表emp1

1.查询出工资高于赵六的员工姓名和工资
分析：工资高于赵六，那么赵六的工资就是子查询的任务
第一步：查询出赵六的工资
select sal from emp1 where ename='赵六'
第二步：查询高于赵六工资员工信息
select ename,sal from emp1 where sal>(第一步结果);
结果：
select ename,sal from emp1 where sal>(select sal from emp1 where ename='赵六');

2.查询与李四同一个部门的员工姓名
分析：与李四同一部门，那么李四的部门编号就是子查询的任务
第一步：查询出李四的部门编号
select deptno from emp1 where ename='李四';
第二步：查询与李四同一部门的员工
select ename from emp1 where deptno=(第一步结果);
结果：
select ename from emp1 where deptno=(select deptno from emp1 where ename='李四');

3.查询出工资高于30号部门所有人的员工信息（用all关键字）
分析：高于30号部门所有人，那么求出30号部门的所有人的工资就是子查询的任务
第一步：求出30号部门的所有人的工资
select sal from emp1 where deptno=30;
第二步：求出工资高于30号部门所有人的员工信息
selelct * from emp1 where sal > all (第一步结果);
结果：
select * from emp1 where sal > all (select sal from emp1 where deptno=30);

三、扩展

多行新增数据

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

多表更新数据

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

或

update 表1
inner join 表2 on 表1.列名=表2.列名
set 列名1=列值1,列名2=列值2
where 其他条件

多表删除数据

语法：

delete 被删除数据的表名 form 删除操作中使用到的表名 
where 其他限定条件

注意：多表间逗号隔开

例如删除人事部的信息

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

日期运算函数

now() 获取当前系统时间
year(日期值) 获得日期值中的年份
date_add(日期,interval 计算值 计算的字段)
注：计算值大于0表示日期往后，小于0表示日期推前
示例：
select date_add(now(),interval -40 year);//显示40年今天的日期

四、数据库优化

1. 对查询进行优化，要尽量避免全表扫描，首先应该考虑在where和order by涉及的列上建立索引
2. 应该尽量避免在where子句对字段进行null判断，否则将导致引擎放弃使用索引而进行全表扫描，如：
   select id from t where num is null
   最好不要给数据库留Null，尽可能使用not null填充数据库
   备注、描述、评论之类的可以设置为Null，其他的，最好不要使用Null。
3. 应该尽量避免在where子句中使用~=或<>操作符，否则引擎将放弃使用索引而进行全表扫描
4. 应该避免在where子句中使用or来连接条件，如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全盘扫描。
5. in 和 not in 也要慎用，否则会导致全表扫描，如：
   select id from t where num in (1,2,3);
   对于连续的数值，能用between就别用in
   select id from t where num between 1 and 3;
   很多时候用exists代替in是一个更好的选择