五、SQL多表查询语法

外键

可以明确的声明表和表之间的字段的关系，使数据库帮我们维护这种关系，这就是外键。如果一个操作破坏这种外键约束，则数据库会阻止这个操作。

为表添加外键

创建表的时候声明外键

FOREIGN KEY(ordersid) REFERENCES ORDERS(id);

--例如：为职员表添加外键关联部门表
create table dep(
    id int primary key auto_increment,
    name varchar(40)
);

create table emp(
    id int primary key auto_increment,
    name varchar(40),
    dept_id int,
    foreign key(dept_id) references dep(id)
);

添加外键约束

ALTER TABLE 表名 ADD CONSTRAINT FK_ID FOREIGN KEY(字段名) REFERENCES 外表表名(主键字段名);

--FK_ID指该外键的名称，
--例：为员工表(emp)添加外键为部门表(dept)的id
alter table emp add constraint aaa_fk foreign key(dep_id) references dept(id);

删除外键约束

ALTER TABLE 表名 DROP FOREIGN KEY 外键名;

--例一：删除员工表的外键
alter table emp drop foreign key aaa_fk;

关联表

关联关系

1. 多对一：例：员工与部门的关系，将外键建立在多的一方
2. 多对多：例：学生与课程之间的关系，需要定义一张中间表，该表存在两个外键分别连接两张表，这两个外键也是表的联合主键
3. 一对一：例：人和省份证的关系，在任一张表中建立外键即可

添加数据

1. 多对一：先添加少的一方，再添加多的一方
2. 多对多：先添加两个表，在添加关系表
3. 一对一：先添加有外键的表，在添加无外键的

删除数据

1. 多对一：先删除多，再删一
2. 多对多：先删关系，再删实体
3. 一对一：先删无外键的表的数据，再删有外键的

连接查询（多表查询）

跨越多张表查询数据

笛卡尔积查询：是两张表相乘的结果

--假设有员工表(emp)的dept_id和部门表(dept)的id对应
--查询两张表
SELECT * FROM dept,emp;

内连接查询：查询出左边表也有且右边表也有的记录

SELECT * FROM dept,emp WHERE dept.id=emp.dept_id;
--或者
SELECT * FROM dept INNER JOIN emp ON dept.id=emp.dept_id;

外连接查询

左外连接查询：在内连接的基础上增加上左边表有而右边表没有的记录

SELECT * FROM dept LEFT JOIN emp ON dept.id=emp.dept_id;

右外连接查询

SELECT * FROM dept RIGHT JOIN emp ON dept.id=emp.dept.id;

全外连接查询：MySQL不支持全外连接

SELECT * FROM dept FULL JOIN ON dept.id=emp.dept_id;

--合并查询结果（union）
SELECT * FROM dept LEFT JOIN emp ON dept.id=emp.dept_id;
UNION
SELECT * FROM dept RIGHT JOIN emp ON dept.id=emp.dept.id;

--例一：查询4号部门的名称和其中员工的姓名
select dept.name 部门名称,emp.name 员工名称 from dept inner join emp on dept.id=emp.dept_id where dept.id=4;

子查询

1. 指一个查询语句嵌套在另一个查询语句内部的查询。
2. 带IN关键字的子查询：内层查询语句仅仅返回一个数据列，这个数据列中的值供外层查询语句进行比较操作

--例一：查询存在年龄为20岁的员工的部门
SELECT * FROM dept WHERE emp_id IN(SELECT emp_id FROM emp WHERE age=20);
--例二：使用NOT IN查询没有20岁的员工的部门
SELECT * FROM dept WHERE emp_id NOT IN(SELECT emp_id FROM emp WHERE age=20);

3. 带EXISTS关键字的子查询：该关键字后面参数可以是任意子查询，相当于测试，返回TRUE或者FALSE，若为TRUE则执行外查询。

--例一：查询emp表中是否存在大于21岁的员工，存在则查询dept表中所有的记录
SELECT * FROM dept WHERE EXISTS (SELECT * FROM emp WHERE age>21);

4. 带ANY关键字的子查询：只要满足子查询中的任意一个就返回结果

--例一：查询部门表中id大于员工表中任意一的id的结果
select * from dept where id>ANY(select dept_id from emp);

5. 带ALL关键字的子查询：需要同时满足所有子查询结果。

--例一：查询部门表中id大于员工表中所有id的结果
select * from dept where id>ALL(select id from emp);

6. 带比较运算符的子查询，即：>，<，=，<=，>=，=，!=。

--例一：查询员工赵四所在的部门
select name from dept where id=(select dept_id from emp where name='赵四');