作用:保证多表之间的数据完整性
测试环境准备:
create table dept(
did int primary key auto_increment,
dname varchar(20)
);
insert into dept values(null,'市场部');
insert into dept values(null,'人事部');
insert into dept values(null,'教研部');
create table employee(
eid int primary key auto_increment,
ename varchar(20),
salary double,
birthday date,
sex varchar(10),
dno int
);
insert into employee values(null,'张三',8000,'1980-09-01','男',3);
insert into employee values(null,'李四',9000,'1980-09-01','男',1);
insert into employee values(null,'王五',6000,'1980-09-01','男',2);
insert into employee values(null,'赵六',10000,'1980-09-01','男',3);
insert into employee values(null,'孙七',10000,'1980-09-01','男',1);
向员工表中插入一条记录,没有部门:
insert into employee values(null,'王五',6000,'1980-09-01','男',null);
删除一个人事部门:
delete from dept where did=2;
向刚才做的这两个操作(插入一个没有部门的员工和删除一个带有员工的部门),这种情况都是不应该发生的。
这个时候就需要在多表之间添加外键约束
在新表中添加外键约束语法: constraint 外键约束名称 foreign key(外键的字段名称) references 主表表名(主键字段名)
在已有表中添加外键约束:alter table 从表表名 add constraints 外键约束名称 foreign key(外键的字段名称) references 主表表名(主键字段名)
删除外键语法: alter table 从表表名 drop foreign key 外键名称;
在员工表上添加外键
alter table employee add foreign key(dno) references dept(did);
//让员工表中员工的部门类型不为空,即设置外键为非空
alter table employee modify dno int not null;
添加外键约束后,如果想要删除主键即某个部门,需要先将该部门下关联的员工记录删除,否则报错。
但是我们可以直接删除掉某个部门下的员工,即外键的删除不影响主键。
而主键的删除会影响外键。
一个部门下可以有多个员工,但是一个员工只能属于一个部门
在多的一方创建外键指向一的一方的主键
一个学生可以选择多门课程,一个课程可以被多个学生选择、
需要创建中间表,中间表中至少有两个字段,分别作为外键指向多对多双方的主键
一个公司只能有一个注册地址,一个注册地址也只能对应一个公司
建表:
select* from 表1 cross join 表2;
或者
select * from 表1,表2;
select *from 表1 inner join 表2 on 关联条件;
select *from 表1,表2 where 关联条件;
select * from 表1 left outer join 表2 on 关联条件;
select * from 表1 right outer join 表2 on 关联条件;
一个查询语句需要依赖另一个查询语句的结果
SELECT * FROM classes CROSS JOIN stu;
SELECT* FROM classes,stu;
效果一样:
1.显示内连接
SELECT * FROM classes c INNER JOIN stu s ON c.cid=s.cno;
可以省略INNER
SELECT * FROM classes c JOIN stu s ON c.cid=s.cno;
2.隐式内连接
SELECT *FROM classes c,stu s WHERE c.cid=s.cno;
返回左表中的所有行,如果左表中行在右表中没有匹配行,则结果中右表中的列返回空值。
SELECT *FROM classes c LEFT OUTER JOIN classesstu s ON c.cid=s.cno;
可以省略OUTER
SELECT *FROM classes c LEFT JOIN classesstu s ON c.cid=s.cno;
恰与左连接相反,返回右表中的所有行,如果右表中行在左表中没有匹配行,则结果中左表中的列返回空值。
SELECT *FROM classes c RIGHT OUTER JOIN stu s ON c.cid=s.cno;
可以省略OUTER
SELECT *FROM classes c RIGHT JOIN stu s ON c.cid=s.cno;
RIGHT JOIN和where使用,与LEFT JOIN基本相同
1.根据on的条件构建临时表,只是这时候临时表的一行数据中,右表的数据必须存在,左表的数据按照on的条件与当前行的右表数据匹配,匹配上就展示左表数据,匹配不上左表数据就是null。所有的右表数据匹配完之后,就返回临时表。
2.根据where条件来筛选整个临时表,这个阶段就是和LEFT JOIN的where一样了
另外,与LFET JOIN的where相似的,如果是要筛选右表,那么也要使用where
LEFT JOIN中
RIGHT JOIN中
使用inner join
UPDATE course c INNER JOIN coursetype ct ON c.type_id=ct.id SET flag =1 WHERE ct.id=2;
使用left join
UPDATE course c LEFT JOIN coursetype ct ON c.type_id=ct.id SET flag =0 WHERE ct.id=2;
多表删除:
delete 表1的别名,表2的别名
from 表1 别名,表2 别名
where 连接条件
and 筛选条件;
delete 表1的别名,表2的别名
from 表1 别名
inner|left|right join 表2 别名 on 连接条件
where 筛选条件;
同时会删除两张表中满足条件的数据:
DELETE e,d FROM emp e INNER JOIN depart d ON e.d_id=d.id WHERE e.d_id=2
不论是delete join还是delete left join,都能实现同时删除多个表的数据,这个就可以代替外键关联里面的级联删除
SELECT * FROM emp e LEFT JOIN depart d ON e.id=d.id ;
等价于
SELECT * FROM emp e LEFT JOIN depart USING(id);
使用USING代替join中的on,只有在两个判断条件键值同名时才可以使用,在查询时不会产生多余的字段
如果join查询中,on的条件是多个and拼接的键值同名的判断
SELECT * FROM emp e LEFT JOIN depart d ON e.id=d.id AND e.name=d.name;
等价于
SELECT * FROM emp e LEFT JOIN depart USING(id,`name`);
基本格式:
SELECT id FROM emp
UNION
SELECT id FROM depart
SELECT id FROM emp
UNION ALL
SELECT id FROM depart