多表关系

在数据表中各个表结构之间存在着各种关系一对一、一对多、多对多。

一对一关系

示例学生与学生详情的关系一个学生对应一个详细情况一个详细情况对应一个学生。

实现在任意一方加入外键关联另外一方的主键并且设置外键为唯一的UNIQUE。

一对多关系

示例学生与班级的关系一个班级对应多个学生一个学生对应一个班级。

实现在一对多关系中在多的一方建立外键指向一的一方的主键。

多对多关系

示例学生与课程的关系一个学生可以选修多门课程一门课程也可以给多个学生选择。

实现在多对多关系中建立第三张中间表中间表至少包含两个外键分别关联两方主键。

多表查询

多表查询可以分为连接查询、联合查询和子查询。

连接查询

连接查询有

• 内连接相当于查询A、B交集部分数据

• 外连接左(右)外连接查询左(右)表所有数据以及两张表交集部分数据

• 自连接当前表与自身的连接查询自连接必须使用表别名

以下代码演示所要涉及到的表

CREATE TABLE emp(
id INT PRIMARY KEY,
NAME VARCHAR(10),
age INT,
job VARCHAR(10),
salary INT,
entrydate DATE,
managerid_id INT,
dept_id INT
);
INSERT INTO emp(id,NAME,age,job,salary,entrydate,managerid_id,dept_id)
VALUES(1,'金庸',66,'总裁',20000,'2000-01-01',NULL,5),
(2,'张无忌',20,'项目经理',12000,'2005-01-11',1,1),
(3,'杨晓',33,'开发',8400,'2000-01-01',2,1),
(4,'韦一笑',48,'开发',11000,'2002-01-01',2,1),
(5,'常遇春',43,'开发',10500,'2004-01-01',3,1),
(6,'小昭',19,'程序员鼓励师',6600,'2004-01-01',2,1),
(7,'灭绝',60,'财务总监',8500,'2002-01-01',1,3),
(8,'周芷若',19,'会计',48000,'2006-01-01',7,3),
(9,'丁敏君',23,'出纳',5350,'2009-01-01',7,3),
(10,'赵敏',20,'市场部总监',12500,'2004-01-01',1,2),
(11,'鹿杖客',56,'职员',3750,'2006-01-01',10,2),
(12,'鹤笔翁',19,'职员',3750,'2007-01-01',10,2),
(13,'房东白',19,'职员',5000,'2009-01-01',10,2),
(14,'张三丰',88,'销售总监',14000,'2004-01-01',1,4),
(15,'玉莲舟',38,'销售',4600,'2004-01-01',14,4),
(16,'宋巧缘',40 ,'销售',4600,'2004-01-01',14,4),
(17,'陈友谅',42,NULL,20000,'2001-01-01',1,NULL);

CREATE TABLE dept(
id INT,
NAME VARCHAR(10)
);
INSERT INTO dept(id,NAME) VALUES(1,'研发部'),(2,'市场部'),(3,'财务部'),(4,'销售部'),(5,'总经办'),(6,'人事部');
SELECT*FROM dept;

内连接

内连接可以分为隐式内连接和显式内连接语法格式如下

# 隐式内连接
select 字段列表 FROM 表1,表2 where 条件.....;
​
# 显式内连接
select 字段列表 FROM 表1 [inner] join 表2 on 连接条件....;

示例代码如下

-- 内连接演示
-- 1.查询每一个员工的姓名及关联的部门的名称隐式内连接实现
-- 表结构emp,dept
-- 连接条件emp.dept_id=dept.id;
SELECT e.`NAME`,d.`name` FROM emp e,dept d WHERE e.`dept_id`=d.`id`;

-- 2.查询每一个员工的姓名及关联的部门的名称显示内连接实现
-- 表结构emp,dept
-- 连接条件emp.dept_id=dept.id;*/
SELECT e.`NAME`,d.`name` FROM emp e INNER JOIN dept d ON e.`dept_id`=d.`id`;

外连接

外连接查询语法格式如下

# 左外连接
select 字段列表 FROM 表1 left [outer] join 表2 on 条件...;
​
# 右外连接
select 字段列表 FROM 表1 right [outer] join 表2 on 条件...;

示例代码如下

-- 外连接查询
-- 1.查询emp表的所有数据和对应的部门信息左外连接
-- 表结构emp,dept
-- 连接条件emp.dept_id=dept.id;
SELECT e.*,d.`name` FROM emp e LEFT OUTER JOIN dept d ON e.`dept_id`=d.`id`;

-- 2.查询dept表的所有数据和对应的部门信息右外连接
-- 表结构emp,dept
-- 连接条件emp.dept_id=dept.id;
SELECT d.*,e.* FROM dept d RIGHT JOIN emp e ON d.`id`=e.`dept_id`;

自连接

自连接查询可以是内连接查询也可以是外连接查询语法格式如下

select 字段列表 from 表A 别名A join 表名A 别名B on 条件...;

示例代码如下

-- 自连接
-- 1.查询员工及其所属领导的名字
-- 表结构emp
-- 连接条件emp.managerid_id=emp.id;
SELECT a.`NAME`,b.`NAME` FROM emp a,emp b WHERE a.`managerid_id`=b.`id`;

-- 2.查询所有员工emp 及其领导emp,如果员工没有领导也需要查询出来
-- 表结构emp
-- 连接条件emp.managerid_id=emp.id;
SELECT a.`NAME` '员工',b.`NAME` '领导' FROM emp a LEFT JOIN emp b ON a.`managerid_id`=b.`id`;

联合查询

联合查询就是把多次查询的结果合并起来形成一个新的查询结果集使用union、union all关键字连接语法格式如下

select 字段列表 from 表A union [all] select 字段列表 from 表B

示例代码如下

-- 联合查询 union,union all
-- 1.将薪资低于5000的员工和年龄大于50岁的员工全部查询出来
SELECT*FROM emp WHERE salary<5000
UNION
SELECT*FROM emp WHERE age>50;

子查询

在SQL语句中嵌套select语句称为嵌套查询又称子查询。语法格式如下

select * from t1 where column1=(select column1 from t2);

子查询外部的语句可以是insert、update、delete、select的任何一个。

根据子查询结果不同分为

标量子查询子查询结果为单个值

列子查询子查询结果为一列

行子查询子查询结果为一行

表子查询子查询结果为多行多列。

根据子查询位置可以分为where之后from之后select之后。

标量子查询

子查询返回的结果是单个值数字、字符串、日期等最简单的形式这种子查询成为标量子查询。

常用的操作符= 、<> 、 > 、 >= 、 < 、 <=。

示例代码如下

-- 标量子查询
-- 1.查询'销售部'的所有员工信息
SELECT id FROM dept WHERE NAME='销售部';
SELECT*FROM emp WHERE dept_id =(SELECT id FROM dept WHERE NAME='销售部');
-- 2.查询在“房东白”入职之后的员工信息
-- a.查询房东白的入职日期
SELECT entrydate FROM emp WHERE NAME='房东白';
-- b.查询指定入职日期之后的入职员工
SELECT*FROM emp WHERE entrydate>(SELECT entrydate FROM emp WHERE NAME='房东白');

列子查询
子查询返回的结果是一列或多列这种子查询称为列子查询。

常用的操作符

IN在指定的集合范围之内多选一

NOT IN 不在指定的集合范围之内

ANY子查询返回列表中有任意一个满足即可

SOME子查询返回列表中有任意一个满足即可

ALL子查询返回列表的所有值都必须满足。
示例代码如下

-- 列子查询
-- 1.查询‘销售部’和‘市场部’的所有员工信息
SELECT id FROM dept WHERE NAME='销售部' OR NAME='市场部';
SELECT*FROM emp WHERE dept_id IN(SELECT id FROM dept WHERE NAME='销售部' OR NAME='市场部');
-- 2.查询比财务部所有人工资都高的员工信息
SELECT id FROM dept WHERE NAME='财务部';
SELECT salary FROM emp WHERE dept_id=(SELECT id FROM dept WHERE NAME='财务部');
SELECT*FROM emp WHERE salary>ALL(SELECT salary FROM emp WHERE dept_id=(SELECT id FROM dept WHERE NAME='财务部'));

行子查询

子查询返回的结果是一行或多列这种子查询称为行子查询。

常用的操作符=、<>、IN 、NOT IN。

示例代码如下

-- 行子查询
-- 1.查询与'张无忌'的薪资及直属领导相同的员工信息
SELECT salary,managerid_id FROM emp WHERE NAME='张无忌';
SELECT*FROM emp WHERE (salary,managerid_id) = (SELECT salary,managerid_id FROM emp WHERE NAME='张无忌');

表子查询

子查询返回的结果是多行多列这种子查询称为表子查询。

常用的操作符为IN。

示例代码如下

-- 表子查询
-- 1.查询与‘鹿杖客’‘宋巧缘’的职位和薪资相同的员工信息
SELECT job,salary FROM emp WHERE NAME='鹿杖客' OR NAME='宋巧缘';
SELECT*FROM emp WHERE (job,salary) IN (SELECT job,salary FROM emp WHERE NAME='鹿杖客' OR NAME='宋巧缘');

-- 2.查询入职日期是'2006-01-01'之后的员工信息及其部门(两种写法)
SELECT e.*,d.`name` FROM emp e,dept d WHERE e.`dept_id`=d.`id` AND e.`entrydate`>'2006-01-01' ;
SELECT e.*,d.`name` FROM (SELECT*FROM emp WHERE entrydate>'2006-01-01') e LEFT JOIN dept d ON e.dept_id=d.`id`;