数据库笔记2

数据完整性

1.1 数据库的完整性

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

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

完整性的分类：

​	实体完整性(行完整性);

​	域完整性(列完整性);

​	引用完整性(关联表完整性);

​		主键约束：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 varcahr(50)
);

1.2 实体完整性

实体：表中的一行(一条记录)代表一个实体(enity)

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

约束类型：

主键约束(primary key)

唯一约束(unique)

1.2.1 主键约束(primary key)

注：每个表要有一个主键

特点：数据唯一，且不能为空

//第一种添加方式
create table student(id int primary key,name varchar(50));
//第二种添加方式  优势在于可以创建联合主键
create table student(id int,name varchar(50),primary key(id));
//第三种添加方式
create table student(id int,name varchar(50));
alter table student add primary key(id);

1.2.2 唯一约束(unique)

特点：数据不能重复，主键约束是数据不能为空，唯一约束允许一次为空

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

1.2.3 自动增长列(auto_increment)

sqlserver数据库(identiy-标识列)

oracle数据库(sequence-序列)

给主键添加自动增长的数值，列只能是整数类型

create table student(id int primary key auto_increment,name varchar(50));
insert into student(name) values('tom');

1.3 域完整性

域完整性的作用：限制此单元格的数据正确，不能照此列的其它单元格比较

域代表当前单元格

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

check约束(mysql不支持) check(sex = ‘男’ or sex = ‘女’)

1.4 引用完整性

外键约束：foreign key

表与表之间的约束

语法：
	alter table 被修改的表名 add constraint 自定义的外键约束名称 foreign key(外键列) references 关联表名(关联表的主键列);
	外键列：存储别的表的主键值的列

constraint 自定义外键名称 foreign key(外键列名) references 主键表名(主键列名)

外键列的数据类型一定要与主键的类型一致

//第二种添加方式
alter table 表名 add constraint 自定义外键名称  foreign key (外键列名) references(主键列名);

多表查询

多个表之间是有关系的，多表约束：外键列

2.1 多表的关系

2.1.1 一对多/多对一的关系

客户和订单，分类和商品，部门和员工

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

2.1.2 多对多关系

学生和课程

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

2.1.3 一对一关系

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

两种建表原则：

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

主键对应：让一对一的双方的主键进行建立关系

2.2 多表查询

多表查询有如下几种：

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

2.2.1 合并结果集

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

合并结果集有两种方式：

union：去除重复记录  例如：select * from t1 union select * from t2;

union all：不去除重复记录，例如：select * from t1 union all select * from t2;

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

2.2.2 连接查询

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

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

通常要查询的多个表之间都存在关联关系，那么就通过关联关系去除笛卡尔积。

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

一、内连接

语法：

	select 列名 from 表1 inner join 表2 on 表1.列名 = 表2.列名 where ....;
	等价于
	select 列名 from 表1,表2 where 表1.列名 = 表2.列名 and ...(其它条件);
	
	注意: 1. 表1和表2的顺序可以互换
	      2. 找两张表的等值关系时，找表示相同含义的列作为等值关系
	      3.点操作符表示"的"  格式：表名.列名
	      4.可以使用as，给表名起别名，定义别名之后，统一使用别名

二、外连接

包括左外连接和右外连接，外连接的特点：查询出的结果存在不满足条件的可能

外联查询

左外联：select 列名 from 主表 left join 次表 on 主表.列名 = 次表.列名;

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

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

右外联：select 列名 from 次表 right on 主表 on 主表.列名 = 次表.列名

左连接是先查询出左表(以左表为主)，然后查询右表，右表中满足条件的显示出来，不满足条件的显示null

三、自然连接

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

语句：
	select * from 表名 名称 natural join 表名 名称;	

2.2.3 子查询

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

子查询就是嵌套查询，就是一个select里面再套一个select，一条查询语句中存在两个或两个以上的select就是子查询语句了

子查询出现的位置：

​	1.where后，作为查询的条件去使用

​	2.from后，作表

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

​	1.any

​	2.all

子查询结果集的形成

​	1.单行单列(用于条件)

​	2.单行多列(用于条件)

​	3.多行单列(用于条件)

​	4.多行多列(用于表)

扩展

3.1 多行新增

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

3.2 多表更新

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='李四'

3.3 多表删除

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

3.4 日期运算函数

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