数据库进阶

外键和E.R图

约束管理

1. 添加约束

添加普通约束的方式有两种，一种是创建表的时候直接给字段添加相应的约束，另一种是通过修改表的方式添加约束

-- 1. 创建表的时候添加约束

-- 建表的时候可以在字段类型后面加一个或者多个约束

-- 2.通过添加约束索引的方式添加约束

-- alter table 表名 add constraint 索引名 约束(字段);

-- 说明: 索引名 - 自己随便命名;  约束 - 当前想要添加的约束(但是只支持唯一约束、主键约束和外键约束)

-- 示例:

alter table t_teacher add constraint uni_tel UNIQUE(teatel);   

2. 删除约束

alter table 表名 drop index 约束索引;

-- 示例:

alter table t_teacher drop index uni_tel;

外键约束

1. 什么是外键：表中的某个字段的值是根据其他表中主键的值来确定的。那么这个字段就是外键

多对一的外键的添加： 将外键添加到多的一方对应的表中

一对一的外键的添加： 将外键随便添加到哪一方，同时添加值唯一约束

多对多的外键的添加： 关系型数据库中，两张表没法实现多多的关系，需要一个中间表。(中间表有两个外键分别参照多多的两个表的主键)

2. 添加外键约束

alter table 表名1 add constraint 外键约束索引名 foreign key (字段1) references 表名2 (字段2);

-- 将表1中的字段1设置为外键，并且让这个外键的值参照表2中的字段2

-- 也可在创建表的时候就添加外键约束

3. 删除外键约束

alter table 表名 drop foreign key 外键索引名;

E.R图

E-R图也称实体-联系图(Entity Relationship Diagram)，提供了表示实体类型、属性和联系的方法，用来描述现实世界的概念模型。

它是描述现实世界关系概念模型的有效方法。是表示概念关系模型的一种方式。用“矩形框”表示实体型，矩形框内写明实体名称；用“椭圆图框”或圆角矩形表示实体的属性，并用“实心线段”将其与相应关系的“实体型”连接起来。

高级查询

聚合

max() / min() / sum() / avg() / count()

SELECT max(score) as max_score FROM tb_score;  -- 获取tb_score表中字段score的最大值

SELECT min(score) as min_score FROM tb_score;  -- 获取tb_score表中字段score的最小值

SELECT sum(score) as sum_score FROM tb_score;  -- 获取tb_score表中字段score的和

SELECT AVG(score) as avg_score FROM tb_score;  -- 获取tb_score表中字段score的平均值

SELECT COUNT(score) as count_score FROM tb_score WHERE score>80;  -- 统计tb_score表中字段score大于80的个数

分组

-- SELECT 字段操作  FROM 表名 WHERE 条件 GROUP BY(字段2);

-- 将指定表中满足条件的记录按照字段2的进行分组(值是一样的在一个组里面), 然后再讲每个分组作为整体按照指定字段进行指定聚合操作

-- 注意:a.字段操作的位置除了分组字段不用聚合，其他字段都必须聚合  b.分组的时候where要放到分组前对需要分组的数据进行筛选

select stuid, avg(score) from tb_score group by(stuid);

-- having: 分组的时候，在分组后用having代替where来对分组后的数据进行筛选！！！

select stuid, max(score) from tb_score group by(stuid) having max(score)>90;

select stuid, avg(score) from tb_score group by(stuid) having avg(score)>80;

去重

SELECT DISTINCT addr FROM t_student;

限制和分页

-- 限制: select * from 表名 limit N;    -   查询的时候只获取前N条数据

-- 偏移: select * from 表名 limit M offset N;  -  跳过前N获取M条数据

select * from tb_record limit 5;

select * from tb_record limit 4 offset 3; --  跳过前3条获取4条数据

子查询

子查询：将一个查询的结果作为另外一个查询的条件或者查询对象

-- 1.将查询结果作为另外一个查询的条件

-- 获取成绩大于90分的学生姓名

select stuname from tb_student where stuid in

(select stuid from tb_score where score>90);

-- 2.将一个查询的结果作为查询对象提供给另外一个查询。但是第一个查询结果需要重命名

select score from (SELECT stuid,score from tb_score where score>80) as t2;

连接查询

1. 直接连接

-- select * from 表名1,表名2,表名3 where 连接条件 查询条件;

-- 查询所有学生的名字和学院名字

select stuname, collname from tb_student, tb_college where tb_student.colid=tb_college.collid;

2. 内连接

-- SELECT * FROM 表1 inner join 表2 on 表2的连接条件 inner join 表3 on 表3的连接条件 ...;

-- 查询所有学生的名字和学院名字

select stuname, collname from tb_student inner join tb_college

on tb_student.colid=tb_college.collid;

3. 外连接

外连接分为左外连接、右外连接和全连接， 但是在MySQL中支持左外连接和右外连接

-- 左外连接：将左表中对应字段的所有数据取出，然后再对应的右表中字段的值，如果右表对应的值不存在结果就为null

-- 右外连接：将右表中对应字段的所有数据取出，然后再对应的左表中字段的值，如果左表对应的值不存在结果就为null

select * from 表1 left join 表2 on 连接条件;

select * from 表1 right join 表2 on 连接条件;

-- 查询所有学生的姓名和选课数量(左外连接和子查询)

select stuname, ifnull(c_count,0) from tb_student as t1 left join

(select sid, count(cid) as c_count from tb_record group by (sid)) as t2 on stuid=sid;

-- 如果不用外连接，没有选课的学生选不出来！！

事务

完成一个任务需要执行多条sql，但是要求这多个操作中只要有一个操作失败，这个任务就失败，数据全部还原；所有的操作都成功，整个任务才成功的时候就使用事务

-- 开启事务环境

begin;

-- ...（需要执行的多个操作对应的sql语句）

-- 提交事务(只有begin到commit之间的所有的sql都执行成功，才会执行commit; 否则执行rollback)

COMMIT;

-- 事务回滚(放弃beigin到commit之间执行成功的所有sql语句的结果)

ROLLBACK;

视图

视图是关系型数据库中将一组查询指令构成的结果集组合成可查询的数据表的对象。简单的说，视图就是虚拟的表，但与数据表不同的是，数据表是一种实体结构，而视图是一种虚拟结构，你也可以将视图理解为保存在数据库中被赋予名字的SQL语句。

使用视图可以获得以下好处：

• 可以将实体数据表隐藏起来，让外部程序无法得知实际的数据结构，让访问者可以使用表的组成部分而不是整个表，降低数据库被攻击的风险。

• 在大多数的情况下视图是只读的（更新视图的操作通常都有诸多的限制），外部程序无法直接透过视图修改数据。

• 重用SQL语句，将高度复杂的查询包装在视图表中，直接访问该视图即可取出需要的数据；也可以将视图视为数据表进行连接查询。

• 视图可以返回与实体数据表不同格式的数据，

1. 创建视图

create view 视图名 as sql查询语句;

-- 示例:

create view vw_student

as SELECT * FROM tb_student;

2. 使用视图 - 视图在用的时候可以直接当成表来使用

-- 示例:

select * FROM vw_student;

select stuname, collname from vw_student, tb_college where vw_student.colid=tb_college.collid;

索引

索引相当于书本的目录，为表创建索引可以加速查询（用空间换时间）。

索引虽然很好，但是不能滥用：

• 索引会占用额外的空间

• 索引会让增删改变得更慢

如果哪个列经常被用于查询的筛选条件那么就应该在这个列上建立索引。

主键上有默认索引(唯一索引)

1. 创建索引

-- create index 索引名 on 表名 (字段);    -- 给指定表的指定字段添加索引

-- create unique index 索引名 on 表名 (字段);  -- 给指定表的指定字段添加唯一索引

-- 示例:

create index idx_stuname on tb_student(stuname);

create unique index idx_stuname on tb_student(stuname);

2. 删除索引

-- alter table 表名 drop index 索引名; -- 删除指定索引，唯一索引也是这样删

-- 示例:

alter table tb_student drop index idx_stuname;

3. 执行索引

-- explain： 获取执行计划

explain select * from tb_student where stuid=110;

explain select * from tb_student where stuname='张三';

-- 注意：模糊查询如果以%和_开头，索引无效!!!

explain select * from tb_student where stuname='%三';  -- error

explain select * from tb_student where stuname='_三';  -- error