【5-3】数据库高级

数据库高级

1. 有效性和准确性；

完整性的分类：

1. 实体完整性(行完整性)

1. 标识每一行数据不重复；

2. 约束类型：主键约束（primary key），唯一约束(unique)，自动增长列(auto_increment)。

3. 主键约束：primary key
   唯一约束：unique [key]
   非空约束：not null
   默认约束：default
   自动增长：auto_increment
   外键约束: foreign key

4. 建议这些约束应该在创建表的时候设置

   多个约束条件之间使用空格间隔

主键约束

1. 每个表中要有一个主键。

2. 特点：数据唯一，且不能为null

3. //第一种添加方式：（用的多）
   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);
   

唯一约束

1. 数据不能重复。

自动增长列

1. sqlserver数据库 (identity-标识列)，oracle数据库(sequence-序列)；
2. 给主键添加自动增长的数值，列只能是整数类型。

2. 域完整性(列完整性):

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

2. 域代表当前单元格

3. 域完整性约束：

   • 数据类型

     类型	大小	范围（有符号）	范围（无符号 ）	用途
     tinyint	1 字节	(-128，127)	(0，255)	小整数值
     smallint	2 字节	(-32 768，32 767)	(0，65 535)	大整数值
     mediumint	3 字节	(-8 388 608，8 388 607)	(0，16 777 215)	大整数值
     INT	字节	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值
     bigint	8 字节	(-9 233 372 036 854 775 808，9 223 372 036 854 775 807)	(0，18 446 744	极大整数值
     float	4 字节	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402823 466 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	单精度浮点数值
     double	8 字节	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4	双精度浮点数值

     ​

   • 非空约束（not null）

   • 默认值约束(default)

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

3. 引用完整性(关联表完整性):

1. 外键约束：FOREIGN KEY

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

   （外键列：存储别的表的主键值的列）

3. 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) );
   
   //第二种添加外键方式
   ALTER 被修改的表名 ADD CONSTRAINT 自定义的外键约束名称 FOREIGN KEY(外键链) REFERENCES 关联表名(关联表的主键列);
   

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

5. 通过工具添加外键

多表查询

多个表之间有关系，靠多表约束：外键列 维护；

多表的关系

1. 一对多/多对一

1. 如：客户和订单，分类和商品，部门和员工.
2. 建表原则：在多的一方创建一个字段，字段作为外键指向一的一方的主键.

2. 多对多

1. 如：学生和课程
2. 建表原则：需要创建第三张表,中间表中至少两个字段，这两个字段分别作为外键指向各自一
   方的主键.

3. 一对一

1. 应用不多

2. 两种建表原则：

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

   键设置为unique.

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

多表查询

1. 合并结果集:UNION 、 UNION ALL
2. 连接查询
   • 内连接 [INNER] JOIN ON
   • 外连接 OUTER JOIN ON
     • 左外连接 LEFT [OUTER] JOIN
     • 右外连接 RIGHT [OUTER] JOIN
     • 全外连接（MySQL不支持）FULL JOIN
   • 自然连接 NATURAL JOIN
3. 子查询

合并结果集

1. 作用：把两个select语句的查询结果合并到一起；
2. 两种方式：
   • UNION：去除重复记录，例如：SELECT* FROM t1 UNION SELECT * FROM t2
   • UNION ALL：不去除重复记录，例如：SELECT * FROM t1 UNION ALL SELECT * FROM t2
3. 列数、列类型必须相同。

连接查询

1. 求出多个表的乘积;

   [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-CqcJS5Oa-1632224601852)(C:\Users\ADMINI~1\AppData\Local\Temp\1631721664689.png)]

2. 会产生笛卡尔积

   去除重复的，不想要的记录呢，通过条件过滤。

内连接

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

2. 三表联查:

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

外连接

1. 查询出的结果存在不满足条件的可能

2. • 左外联:select 列名 from 主表 left join 次表 on 主表.列名=次表.列名
   • 右外联:select 列名 from 次表 right join 主表 on 主表.列名=次表.列名

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

   主表和次表不能随意调换位置
   使用场景:一般会作为子查询的语句使用

自然连接

1. 一种特殊的等值连接
2. 在结果中消除重复的属性列（和笛卡尔的区别）
3. select * from emp e natural join dept d;

子查询（*）

嵌套查询

select * from users where userid in (
select uid from orders where totalprice>300
);

where后作为条件时，还可以使用如下关键字：any/all

扩展

多表更新

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

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

多表删除

delete 被删除数据的表 from 删除操作中使用的表
where 限定条件
//注:多张表之间使用逗号间隔

注意：当前删除记录是否被别的表使用，

假删除：0表示删除等

日期运算函数

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 来连接条件,用union all
5. in 和 not in 也要慎用，对于连续的数值，能用 between 就不要用 in 了
6. 用 exists 代替 in 是一个好的选择

m t where num is null最好不要给数据库留NULL，尽可能的使用NOT NULL填充数据库. **备注、描述、评论之类的可以设置为 NULL，其他的，最好不要使用NULL** 3. 尽量避免在 where 子句中使用 != 或 <> 操作符 4. 尽量避免在 where 子句中使用 or 来连接条件,用union all`
5. in 和 not in 也要慎用，对于连续的数值，能用 between 就不要用 in 了
6. 用 exists 代替 in 是一个好的选择