数据库高级

数据库的完整性

确保数据的完整性 = 在创建表时给表中添加约束

完整性的分类

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

create table student(
studentno int primary key auto_increment,
pwd varchar(20) not null default '123',
name 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)
);

实体完整性 

实体：即表中的一行(一条记录)代表一个实体（entity）

实体完整性的作用：标识每一行数据不重复

约束类型：

主键约束（primary key）注：每个表中要有一个主键。 特点：数据唯一，且不能为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(50), primary
key(classid,stuid) );
方法三
CREATE TABLE student( id int, name varchar(50) );
ALTER TABLE student ADD PRIMARY KEY (id);

唯一约束(unique) 特点：数据不能重复。

CREATE TABLE student( Id int primary key, Name varchar(50) unique );

自动增长列(auto_increment)列只能是整数类型

CREATE TABLE student( Id int primary key auto_increment, Name varchar(50) );

域完整性 

域代表当前单元格

作用：限制单元格的数据正确，不对照此列的其它单元格比较

域完整性约束：数据类型 非空约束（not null） 默认值约束(default)

引用完整性

外键约束：FOREIGN KEY

CREATE TABLE student(id int primary key, name varchar(50) not null, sex
varchar(10) default '男' );
constraint 自定义外键名称 foreign key(外键列名) references 主键表名(主键列名)
(外键列的数据类型一定要与主键的类型一致)
create table score(
id int primary key,
score int,
sid int ,
constraint fk_score_sid foreign key(sid) references student(id) );
第二种添加外键方式
ALTER score ADD CONSTRAINT fk_stu_score FOREIGN KEY(sid) REFERENCES
stu(id);

多表查询

关系

• 一对多/多对一
• 一对一
• 多对多

一对一的两种建表原则：

• 唯一外键对应：假设一对一是一个一对多的关系，在多的一方创建一个外键指向一的一方的主键，将外键设置为unique（外键原本是重复的）.
• 主键对应：让一对一的双方的主键建立关系（一张表的主键reference另一张表的主键，既是主键列又是外键列）

多表查询

1. 合并结果集:UNION 、 UNION ALL
2. 连接查询
 2.1内连接 [INNER] JOIN ON
 2.2外连接 OUTER JOIN ON
-左外连接 LEFT [OUTER] JOIN
- 右外连接 RIGHT [OUTER] JOIN
 - 全外连接（MySQL不支持）FULL JOIN
 2.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。

select * from emp,dept;

连接查询会产生笛卡尔积

使用主外键关系做为条件来去除无用信息

SELECT * FROM emp,dept WHERE emp.deptno=dept.deptno;

SELECT emp.ename,emp.sal,emp.comm,dept.dname
FROM emp,dept
WHERE emp.deptno=dept.deptno;

内连接

select 列名
from 表1
inner join 表2
on 表1.列名=表2.列名 //外键列的关系
where.....
等价于
select 列名
from 表1,表2
where 表1.列名=表2.列名 and ...(其他的条件)
注意：找两张表的等值关系时,找表示相同含义的列作为等值关系。

//查询学生表中的学生姓名和分数表中的分数
select name,score
from student as s
inner join scores as c
on s.studentid=c.stuid
等价于：
select name,score
from student as s,scores as c
where s.studentid=c.stuid

外连接

包括左外连接和右外连接

外连接的特点：查询出的结果存在不满足条件的可能。

-- 外联查询

-- 左外联:select 列名 from 主表 left join 次表 on 主表.列名=次表.列名

-- 1.主表数据全部显示，次表数据匹配显示，能匹配到的显示数据，匹配不成功的显示null

-- 2.主表和次表不能随意调换位置

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

自然连接

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

子查询

一个select语句中包含另一个完整的select语句。

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

子查询出现的位置：

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

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

• any
• all

子查询结果集的形式：

a. 单行单列（用于条件） b. 单行多列（用于条件） c. 多行单列（用于条件） d. 多行多列（用于表）

工资高于30部门所有人工资，其中30部门所有人工资是子查询。高于所有需要使用all关键
字。
第一步：查询30部门所有人工资
SELECT sal FROM emp WHERE deptno=30;
第二步：查询高于30部门所有人工资的员工信息
SELECT * FROM emp WHERE sal > ALL (第一步)
（也可以使用MAX聚合函数）
结果：
SELECT * FROM emp WHERE sal > ALL (SELECT sal FROM emp WHERE deptno=30)

子查询作为条件
子查询形式为多行单列（当子查询结果集形式为多行单列时可以使用ALL或ANY关键字）

多表删除

语法：
 delete 被删除数据的表 from 删除操作中使用的表
 where 限定条件
 注:多张表之间使用逗号间隔
 示例：
 //删除部门1的信息
 delete d,e,s from department d,employee e,salary s
 where d.depid=e.depid and s.empid=e.empid and depname='部门1'

日期运算函数

 date_add(日期,interval 计算值 计算的字段);
 注：计算值大于0表示往后推日期，小于0表示往前推日期
例：
 date_add(now(),interval -20 year);//20年前的日期

数据库优化 （sql层面）

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 来连接条件，如果一个字段有索引，一个字段没有索引，将导致 引擎放弃使用索引而进行全表扫描，如：select id from t where num=100 or Name = 'admin'

可以使用select id from t where num = 10 union all select id from t where Name = 'admin'

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 也是一个好的选择