外键约束
概念
特点
定义一个外键时，需要遵守下列规则：
主表必须已经存在于数据库中，或者是当前正在创建的表。
必须为主表定义主键。
主键不能包含空值，但允许在外键中出现空值。也就是说，只要外键的每个非空值出现在指定的主键中，这 个外键的内容就是正确的。
在主表的表名后面指定列名或列名的组合。这个列或列的组合必须是主表的主键或候选键。
外键中列的数目必须和主表的主键中列的数目相同。
外键中列的数据类型必须和主表主键中对应列的数据类型相同。
操作
- 建立外键约束
1. create database mydb3; 2. use mydb3; 3. create table if not exists dep 4. ( 5. pid int primary key, 6. name varchar(20) 7. ); 8. create table if not exists per 9. ( 10. id int primary key, 11. name varchar(20), 12. age int, 13. depid int, 14. constraint fok foreign key(depid) references dep(pid) 15. ); 16. 17. 18. 19. create table if not exists dep3 20. ( 21. pid int primary key, 22. name varchar(20) 23. ); 24. create table if not exists per3 25. ( 26. id int primary key, 27. name varchar(20), 28. age int, 29. depid int 30. ); 31. alter table per3 add constraint fok3 foreign key(depid) references dep3(pid);
- 数据插入
必须先给主表添加数据，且从表外键列的值必须依赖于主表的主键列
1. insert into dep3 values('1001','研发部'); 2. insert into dep3 values('1002','销售部'); 3. insert into dep3 values('1003','财务部'); 4. insert into dep3 values('1004','人事部'); 5. 6. -- 给per3表添加数据 7. insert into per3 values('1','乔峰',20, '1001'); 8. insert into per3 values('2','段誉',21, '1001'); 9. insert into per3 values('3','虚竹',23, '1001'); 10. insert into per3 values('4','阿紫',18, '1001'); 11. insert into per3 values('5','扫地僧',85, '1002'); 12. insert into per3 values('6','李秋水',33, '1002'); 13. insert into per3 values('7','鸠摩智',50, '1002'); 14. insert into per3 values('8','天山童姥',60, '1003'); 15. insert into per3 values('9','慕容博',58, '1003');
- 数据删除
主表数据被从表依赖时不能删除，否则可以删除；从表的数据可以随便删除。
如下，第一句和第二句执行成功，第三句执行失败
1. delete from per3 where depid=1003; 2. delete from dep3 where pid=1004; 3. delete from dep3 where pid=1002;
- 删除外键约束
语法：alter table 从表 drop foreign key 关键词名;
alter table per3 drop foreign key fok3;
多表联合查询
概念
操作
- 交叉连接查询
select * from dept,emp;
- 内连接查询
注释；上面是隐式内连接，下面是显式内连接
1. select * from dept,emp where dept.deptno=emp.dept_id; 2. select * from dept join emp on dept.deptno=emp.dept_id; 3. 4. select * from dept join emp on dept.deptno=emp.dept_id and name='研发部'; 5. select * from dept join emp on dept.deptno=emp.dept_id and name='研发部'; 6. 7. select * from dept join emp on dept.deptno=emp.dept_id and (name='研发部' or name='销售部'); 8. select * from dept join emp on dept.deptno=emp.dept_id and (name='研发部' or name ='销售部'); 9. select * from dept join emp on dept.deptno=emp.dept_id and name in ('研发部','销售部'); 10. 11. select a.name,a.deptno,count(*) from dept a join emp on a.deptno=emp.dept_id group by dept_id; 12. select a.name,a.deptno,count(*) total from dept a join emp on a.deptno=emp.dept_id group by dept_id having total >=3 order by total desc;
- 外连接查询
若是对应的外表没有数据就补NULL
1. select * from dept a left join emp b on a.deptno=b.dept_id; 2. select * from dept a right join emp b on a.deptno=b.dept_id; 3. -- select * from dept a full join emp b on a.deptno=b.dept_id; --不能执行 4. -- 用下面的方法代替上面的full join 5. select * from dept a left join emp b on a.deptno=b.dept_id union select * from dept a right join emp b on a.deptno=b.dept_id; 6. -- 对比union all，发现union all没有去重过滤 7. select * from dept a left join emp b on a.deptno=b.dept_id union all select * from dept a right join emp b on a.deptno=b.dept_id;
- 子查询
1. select * from emp where age<(select avg(age) from emp); 2. select * from emp a where a.dept_id in (select deptno from dept where name in ('研发部','销售部')); 3. -- 对比关联查询和子查询如下 4. select * from emp a join dept b on a.dept_id=b.deptno and (b.name='研发部' and age<30); 5. select * from (select * from dept where name='研发部') a join (select * from emp where age<30) b on b.dept_id=a.deptno;
- 子查询关键字
all关键字的用法
1. select * from emp where age>all(select age from emp where dept_id='1003'); 2. select * from emp a where a.dept_id!=all(select deptno from dept);
any（some）关键字的用法
select * from emp where age>any(select age from emp where dept_id='1003') and dept_id!='1003';
in关键字的用法
select ename,eid from emp where dept_id in (select deptno from dept where name in ('研发部','销售部'));
exists关键字的用法
1. select * from emp a where a.age<30; 2. select * from emp a where exists(select * from emp where a.age<30); 3. 4. select * from emp a where a.dept_id in (select deptno from dept b); 5. select * from emp a where exists (select * from dept b where a.dept_id = b.deptno);
- 自关联查询
多表操作总结
