数据库的完整性

用来保证存放到数据库中的数据是有效的,即数据的有效性和准确性

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

完整性的分类： - 实体完整性(行完整性):

- 域完整性(列完整性):

- 引用完整性(关联表完整性):

主键约束：primary key

唯一约束：unique [key]

非空约束：not null

默认约束：default

自动增长：auto_increment

外键约束: foreign key

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

示例：

create table student(
studentno int primary key auto_increment,--主键自增
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)
);

外键约束：FOREIGN KEY

示例：

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) );
constraint 自定义外键名称 foreign key(外键列名) references 主键表名(主键列名)

ALTER table 被修改的表名 add CONSTRAINT 自定义的外键约束名称 FOREIGN key (外键列） 
REFERENCES 关联表名(关联表的主键列);
--外键列：存储别的表的主键值的列
ALTER table scores ADD CONSTRAINT abc FOREIGN key (stuid) REFERENCES 
student(stuno);

多表查询

多表查询有如下几种：

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.子查询

1.合并结果集

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

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

注意：被合并的两个结果：列数、列类型必须相同。

2.1 连接查询

内连接

select 列名
from 表1
inner join 表2
on 表1.列名=表2.列名 //外键列的关系
where.....
示例：
select name,score
from student as s
inner join scores as c
on s.studentid=c.stuid

等价于
select 列名
from 表1,表2
where 表1.列名=表2.列名 and ...(其他条件)
示例：
select name,score
from student as s,scores as c
where s.studentid=c.stuid

注:表1和表2的顺序可以互换

找两张表的等值关系时,找表示相同含义的列作为等值关系。

点操作符表示“的”,格式:表名.列名

可以使用as,给表名起别名,注意定义别名之后,统一使用别名

三表联查

语法：
 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 外连接

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

                -- 右外联:select 列名 from 次表 right join 主表 on 主表.列名=次表.列名

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

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

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

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

左外连接
SELECT * FROM emp e
LEFT OUTER JOIN dept d
ON e.deptno=d.deptno;

右外连接
SELECT * FROM emp e
RIGHT OUTER JOIN dept d
ON e.deptno=d.deptno;

自然连接

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

select * from emp e natural join dept d;

扩展

多行新增

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

多表更新

(1)update
 表1,表2 set 列名=列值 where 表1.列名=表2.列名 and 其他限定条件
(2)update 表1
 inner join 表2 on 表1.列名=表2.列名
 set 列名=列值
 where 限定条件
示例：update employee e,salary s
 set title='助工',salary=1000
 where e.empid=s.empid and name='李四'

多表删除

 语法：
 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='人事部'

日期运算函数

 now() 获得当前系统时间
 year(日期值) 获得日期值中的年份
 date_add(日期,interval 计算值 计算的字段);
 注：计算值大于0表示往后推日期，小于0表示往前推日期
示例：
 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 来连接条件，如果一个字段有索引，一个字段没有索引，将导致 引擎放弃使用索引而进行全表扫描，如：

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 也要慎用，否则会导致全表扫描，如： select id from t where num in(1,2,3) 对于连续的数值，能用 between 就不要用 in 了： select id from t where num between 1 and 3 很多时候用 exists 代替 in 是一个好的选择