多表关联查询
-
外键约束
外键指的是在从表中与主表的主键对应的那个字段。
使用外键约束可以让两张表之间产生一个对应关系,从而保证主从表的完整性多表关系中的主表和从表
主表: 主键id所在的表,约束别人的表(一的一方)。
从表: 外键所在的表,被约束的表(多的一方)。
-
设置外键约束的语句
/*
* 在创建表时创建外键
* 下面的emp_dept_fk为外键约束的名字
***/
create table emp(
eid int(4) primary key auto_increment,
ename varchar(20) not null,
tid int(4),
constraint emp_dept_fk foreign key(tid) references department(tid)
);
/*在已经创建好数据库的前提下添加外键约束*/
alter table emp add foreign key(tid) references department(tid);
//此处省略了外键的名字,使用系统默认的名字
一定注意,在创建外键时一定要注意主从表之间的联系列
同时从表的外键类型必须与主表的主键类型相同
添加数据时,应该先添加主键的数据
- 删除外键约束
//真的删除成功了吗
alter table emp drop foreign key 外键名;
> 你会发现,当你使用desc emp 查看表结构时,发现外键MUL还在,那为啥没删
除成功呢,这和mysql的执行有关,mysql在你增加索引时就给你自动增加一个同
名的索引,所以你删了你创建的外键外,mysql给你默认增加的还没删除,导致你
会发现自己的表信息里面还存在外键约束。
1. show index
执行该语句你会发现表中存在一个与外键同名的索引
2. DROP INDEX em_dept_fy ON employee;
em_dept_fy为该索引的名字,使用该语句删除索引,然后再次查表
就会发现外键成功删除了。
介绍一下级联删除
> 级联删除指的是在删除主表的数据的同时,可以删除与之相关的从表中的
数据
级联删除语句: on delete cascade
/*级联删除*/
create table emp (
eid int(4) primary key auto_increment,
ename varchar(20) not null,
age int(4),
dept_id int(4),
constraint emp_dept_fk foreign key(dept_id) references department(id)
on delete cascade
);
/*
* 设置后当我们从主表中删除数据时,从表中相关联的数据也会随之删除
**/
多表关联查询
当几张表之间由主外键约束时,如果我们依然还是选择一张一张的查询的话,开发效率就会大大降低,这是多变关联查询就可以节省开发时间,提高效率。
内连接查询
SELECT * FROM province p INNER JOIN city c ON c.pid = p.pid;
通过指定的条件去匹配两张表中的内容,匹配不上的就不显示。
外连接查询
左、右外连接
SELECT * FROM province p LEFT JOIN city c ON c.
pid= p.
pid;
SELECT * FROM province p RIGHT JOIN city c ON c.
pid= p.
pid;
如果是左外连接,则以左表为基准,匹配右表中的数据,如果可以匹配上就显示,如果匹配不上,则左表中的数据正常显示,右表中的数据为null。
右外连接刚好和左外连接相反。
子查询
- 一条 select 的结果, 作为另一条 select 语句的一部分。
- 注意
子查询必须放在小括号中,子查询一般作为父查询的查询条件使用
-
子查询常见分类 where 型子查询:将子查询的结果,作为父查询的比较条件。 from 型子查询:将子查询的结果, 作为一张表,提供给父查询 使用。 exists 型子查询: 子查询的结果类似一个数组, 父层查询使 用 in 函数,包含子查询的结果。
子查询作为查询条件1:
SELECT * FROM province WHERE pid = (SELECT cid FROM city WHERE cid = 1);
子查询结果作为一张表:
SELECT * FROM province p INNER JOIN (SELECT * FROM city) c ON p.
pid= c.pid;
子查询结果是单列多行:
SELECT * FROM city WHERE cid IN (SELECT pid FROM province);
数据库三范式
-
1NF–第一范式
列具有原子性,设计列要做到列不可拆分。
-
2NF–第二范式
一张表只能描述一个事件。
-
3NF–第三范式
消除传递依赖,如果能被推导出来,就不要设计一个字段去单独记录它。
-
范式设计优缺点
优点: 可以尽量得减少数据冗余 范式化的更新操作比反范式化更快 范式化的表通常比反范式化的表更小 缺点: 对于查询需要对多个表进行关联 更难进行索引优化
-
反三范式
指的是通过增加冗余或者重复数据,来提高数据库的读性能。浪费存储 空间,节省查询时间(以空间换时间) 冗余字段:某张表的字段,但是它又在多张表中都有出现。
-
反范式设计优缺点
优点: 可以减少表的关联 可以更好的进行索引优化 缺点: 存在数据冗余及数据维护异常 对数据的修改需要更多的成本