目录
2.显式内连接：inner join 连接表 on 条件，inner可省略
一、多表间的关系
项目开发中，在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表结构之间也存在着各种联系，基本上分为三种：一对多(多对一)、多对多、一对一
1.一对多（多对一）：部门（一）与员工（多）的关系
一个员工只能对应一个部门，但一个部门下会有多名员工
实现联系：通常在多的的一方建立外键，指向一的一方的主键
2.多对多：学生与课程的关系
一个学生可以选择多门课程，一门课程也可被多名学生选择
实现联系：需要建立第三张中间表来维护两张表的关系，中间表至少包含两个外键，分别关联两方主键
下面是多对多创建三张表的示例语句
-- 创建学生表
create table student(
id int auto_increment primary key comment '主键ID',
name varchar(10) comment '姓名',
no varchar(10) comment '学号'
) comment '学生表';
insert into student values (null, '黛绮丝', '2000100101'),(null, '谢逊','2000100102'),(null, '殷天正', '2000100103'),(null, '韦一笑', '2000100104');
-- 创建课程表
create table course(
id int auto_increment primary key comment '主键ID',
name varchar(10) comment '课程名称'
) comment '课程表';
insert into course values (null, 'Java'), (null, 'PHP'), (null , 'MySQL') ,(null, 'Hadoop');
-- 创建两张表的关联表，需要添加好外键
create table student_course(
id int auto_increment comment '主键' primary key,
studentid int not null comment '学生ID',
courseid int not null comment '课程ID',
constraint fk_courseid foreign key (courseid) references course (id),
constraint fk_studentid foreign key (studentid) references student (id)
)comment '学生课程中间表';
insert into student_course values (null,1,1),(null,1,2),(null,1,3),(null,2,2),
(null,2,3),(null,3,4);对于datagrip而言，可以通过右键表-图-显示可视化来将表与表之间的关系进行可视化展示
会展示出下图这样的效果
3.一对一：用户与用户详情的关系
一对一关系常用于单表拆分，对于比较庞大的表而言，会将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率
实现联系：在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的(UNIQUE)。唯一的这一要求就限定了这是一对一的问题而不是一对多，保证一个用户只能对应一个记录
下面是创建的示例语句
create table tb_user(
id int auto_increment primary key comment '主键ID',
name varchar(10) comment '姓名',
age int comment '年龄',
gender char(1) comment '1: 男 , 2: 女',
phone char(11) comment '手机号'
) comment '用户基本信息表';
create table tb_user_edu(
id int auto_increment primary key comment '主键ID',
degree varchar(20) comment '学历',
major varchar(50) comment '专业',
primaryschool varchar(50) comment '小学',
middleschool varchar(50) comment '中学',
university varchar(50) comment '大学',
userid int unique comment '用户ID',
constraint fk_userid foreign key (userid) references tb_user(id)
) comment '用户教育信息表';
insert into tb_user(id, name, age, gender, phone) values
(null,'黄渤',45,'1','18800001111'),
(null,'冰冰',35,'2','18800002222'),
(null,'码云',55,'1','18800008888'),
(null,'李彦宏',50,'1','18800009999');
insert into tb_user_edu(id, degree, major, primaryschool, middleschool,
university, userid) values
(null,'本科','舞蹈','静安区第一小学','静安区第一中学','北京舞蹈学院',1),
(null,'硕士','表演','朝阳区第一小学','朝阳区第一中学','北京电影学院',2),
(null,'本科','英语','杭州市第一小学','杭州市第一中学','杭州师范大学',3),
(null,'本科','应用数学','阳泉第一小学','阳泉区第一中学','清华大学',4);二、多表查询概述
多表查询即从多张表中查询数据，最简单的方式就是
select * from 表1, 表二;但这样查询会出现笛卡尔积的现象，即结果会是两个表的所有组合情况，如下图所示，而这并不是我们想得到的结果，在进行多表查询时需要消除无效的笛卡尔积
为了消除笛卡尔积，我们需要关注多表之间是通过什么产生联系的，通过保留关联的字段在两张表中相等的部分，消除无效的笛卡尔积。
即通过where作为条件筛选（连接条件），只保留两张表产生关联的字段（也就是外键）相等的的记录
select * from emp, dept where dept_id = dept.id;为后面案例示例，准备新数据
-- 创建dept表，并插入数据
create table dept(
id int auto_increment comment 'ID' primary key,
name varchar(50) not null comment '部门名称'
)comment '部门表';
INSERT INTO dept (id, name) VALUES (1, '研发部'), (2, '市场部'),(3, '财务部'), (4,
'销售部'), (5, '总经办'), (6, '人事部');
-- 创建emp表，并插入数据
create table emp(
id int auto_increment comment 'ID' primary key,
name varchar(50) not null comment '姓名',
age int comment '年龄',
job varchar(20) comment '职位',
salary int comment '薪资',
entrydate date comment '入职时间',
managerid int comment '直属领导ID',
dept_id int comment '部门ID'
)comment '员工表';
-- 添加外键
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references
dept(id);
INSERT INTO emp (id, name, age, job,salary, entrydate, managerid, dept_id)
VALUES
(1, '金庸', 66, '总裁',20000, '2000-01-01', null,5),
(2, '张无忌', 20, '项目经理',12500, '2005-12-05', 1,1),
(3, '杨逍', 33, '开发', 8400,'2000-11-03', 2,1),
(4, '韦一笑', 48, '开发',11000, '2002-02-05', 2,1),
(5, '常遇春', 43, '开发',10500, '2004-09-07', 3,1),
(6, '小昭', 19, '程序员鼓励师',6600, '2004-10-12', 2,1),
(7, '灭绝', 60, '财务总监',8500, '2002-09-12', 1,3),
(8, '周芷若', 19, '会计',48000, '2006-06-02', 7,3),
(9, '丁敏君', 23, '出纳',5250, '2009-05-13', 7,3),
(10, '赵敏', 20, '市场部总监',12500, '2004-10-12', 1,2),
(11, '鹿杖客', 56, '职员',3750, '2006-10-03', 10,2),
(12, '鹤笔翁', 19, '职员',3750, '2007-05-09', 10,2),
(13, '方东白', 19, '职员',5500, '2009-02-12', 10,2),
(14, '张三丰', 88, '销售总监',14000, '2004-10-12', 1,4),
(15, '俞莲舟', 38, '销售',4600, '2004-10-12', 14,4),
(16, '宋远桥', 40, '销售',4600, '2004-10-12', 14,4),
(17, '陈友谅', 42, null,2000, '2011-10-12', 1,null);三、多表查询分类
四、内连接查询
1.隐式内连接：where+条件
SELECT 字段列表 FROM 表1 , 表2 WHERE 条件 ... ; 案例： 查询每一个员工的姓名 , 及关联的部门的名称 (隐式内连接实现)
select emp.name, dept.name from dept, emp where emp.dept_id = dept.id;
# 可以通过对表设置别名来简化编写，在表名后空格加别名即可
select e.name,d.name from emp e , dept d where e.dept_id = d.id;
注意：在给表起了别名之后就不能再用原先的表名只能用别名了，因为语句的执行顺序是先执行了from语句设置了别名，所以后面的语句都要遵循这个别名的规则
2.显式内连接：inner join 连接表 on 条件，inner可省略
SELECT 字段列表 FROM 表1 [ INNER ] JOIN 表2 ON 连接条件 ... ;案例：查询每一个员工的姓名 , 及关联的部门的名称 (显式内连接实现)
select emp.name, dept.name from emp inner join dept on emp.dept_id = dept.id;
五、外连接查询
1.左外连接
左外连接相当于查询表1(左表)的所有数据，包含表1和表2交集部分的数据。
SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 条件 ... ;2.右外连接
右外连接相当于查询表2(右表)的所有数据，包含表1和表2交集部分的数据。
SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 条件 ... ;两种案例示例：
-- 1.左外连接
# 查询emp表的所有数据, 和对应的部门信息
select e.*, d.name from emp e left outer join dept d on d.id = e.dept_id;
# 其中的e.*表示查询emp表中的所有字段
-- 2.右外连接
# 查询dept表的所有数据, 和对应的员工信息
select d.*, e.* from emp e right outer join dept d on d.id = e.dept_id;但实际上两种外连接可以相互转换，只是改变一下表1和表2的位置即可
六、自连接
即自己连接自己，也就是把一张表连接查询多次，语法：
SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ... ;而对于自连接查询，可以是内连接查询，也可以是外连接查询
下面是一个内连接案例的错误示例，逻辑会比较混乱：
# 案例1内连接：查询员工及其所属领导的名字，即emp表中的员工-name和领导id-managerid,领导id与emp员工id是对应的
select e1.id, e1.name, e2.managerid, e2.name from emp e1 join emp e2 on e1.id = e2.managerid;
# 输出结果不正确，语句的逻辑有问题对于自连接而言，比较好的方法就是把这一个表看作是两张表
像这个问题，我们可以把e1看作员工表，e2看作领导信息表，这样连接的条件和查询的内容就很明显了，就是要员工里的领导id要与领导表中的id一样的数据，就获得了领导的信息，下面这句是正确的
select e1.id, e1.name, e2.id, e2.name from emp e1 join emp e2 on e1.managerid = e2.id;下面案例类似，只是改为了外连接案例：
# 案例2外连接：查询所有员工及其领导的名字, 如果员工没有领导, 也需要查询出来
select e1.id, e1.name, e2.id, e2.name from emp e1 left join emp e2 on e1.managerid = e2.id;
七、联合查询 union，union all
对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集，简单讲就是合并查询结果，语法如下：
SELECT 字段列表 FROM 表A ...
UNION [ ALL ]
SELECT 字段列表 FROM 表B ....;注意：1. 对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。
2. union all 会将全部的数据直接合并在一起，union 会对合并之后的数据去重。
举个例子：
# 案例：将薪资低于5000的员工 , 和年龄大于50岁的员工全部查询出来
select * from emp where salary < 5000
union
select *
from emp where age > 50;
# where or 的条件筛选也可以到达这个要求
select * from emp where salary < 5000 or age > 50;注意：where or的条件查询偶尔可以实现联合查询的一些需求，但联合查询本身在于合并查询结果，并不只局限于where，当涉及别的需求where就不行了
八、子查询
SQL语句中嵌套SELECT语句，内部的select语句称为嵌套查询，又称子查询，语法如下：
SELECT * FROM t1 WHERE column1 = ( SELECT column1 FROM t2 );注意：子查询外部的语句可以是SELECT，也可以是INSERT / UPDATE / DELETE增删改的任何一个。
1.根据子查询位置分类
WHERE之后
FROM之后
SELECT之后
2. 根据子查询结果不同分类
（1）标量子查询（子查询返回结果为单个值）
因为子查询的结果是单个值，可能是数值、字符串也可能是日期，所以常用操作符号：= <> > >= < <=
案例1：查询 "销售部" 的所有员工信息
完成子查询需求时，通常可以将需求分解为两步
由于emp表中只有部门id，没有对应的部门名称，所以需要先在部门表dept中找到销售部对应的id是多少，再找出emp表中部门id符合的员工信息，即：
①. 查询 "销售部" 部门ID
select id from dept where name = '销售部'; ②. 根据查询到的 "销售部" 部门ID（为4）, 查询员工信息
select * from emp where dept_id = 4;然后将两句合并 ，也就是把查出来的4换成原本的查询语句，即
select * from emp where dept_id = (select id from dept where name = '销售部');案例2：查询在 "方东白" 入职之后的员工信息
select * from emp where entrydate > (select entrydate from emp where name = '方东白');
（2）列子查询(子查询返回结果为一列)
由单个值变为一列数据，常用的操作符就要变化为：IN 、NOT IN 、 ANY 、SOME 、 ALL
IN 、NOT IN 即判断字段是否在集合范围内
ANY 和 SOME 等同，子查询返回的结果列表中，有任意一个满足条件即可
ALL 要求子查询返回列表结果的所有值都必须满足条件
ANY 、SOME 、 ALL通常会和大于小于号结合，起到大于或小于任意值或所有值的作用，字段 = any 子查询结果 这个语句和in的作用相同
案例1：查询 "销售部" 和 "市场部" 的所有员工信息
select * from emp where emp.dept_id in (select dept.id from dept where dept.name = '销售部' or dept.name = '市案例2： 查询比 财务部 所有人工资都高的员工信息
select * from emp where salary > all (select salary from emp where dept_id = (select dept.id from dept where dept.name = '财务部'));案例3：查询比研发部其中任意一人工资高的员工信息
select * from emp where salary > any (select salary from emp where dept_id = (select dept.id from dept where dept.name = '研发部'));（3）行子查询(子查询返回结果为一行)
常用操作符：=、<>、IN、NOT IN， =、<>适用于子查询结果单行，IN、NOT IN适用子查询结果多行
案例：查询与 "张无忌" 的薪资及直属领导相同的员工信息 ;
select * from emp where (salary, managerid) = (select salary, managerid from emp where name = '张无忌');
这里必须用（）将多列（多字段）括起表示一行多列的信息，条件是要等于后续子查询出的一行信息
普通的查询也可以利用where结合（）整体判断条件，即where salary = 12500 and managerid = 1; 可以改写为 where（salary，managerid）= (12500, 1）来设置多个字段的条件
（4）表子查询(子查询结果为多行多列)
常出现在from之后，将子查询返回的表结果作为一个新表进行查询
常用操作符：IN
案例1：查询与 "鹿杖客" , "宋远桥" 的职位和薪资相同的员工信息
select * from emp where (job, salary) in
(select job, salary from emp
where name = '鹿杖客' or name = '宋远桥');案例2： 查询入职日期是 "2006-01-01" 之后的员工信息 , 及其部门信息
将查询的结果作为新表进行外连接
select e.*, d.* from (select * from emp where entrydate > '2006-01-01') e
left join dept d on e.dept_id = d.id;
扫一扫
4198
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
