Mysql基础（三）多表查询

学习之前需要创建的数据表和数据

-- 部门表
CREATE TABLE `dept`  (
  `depton` mediumint(8) UNSIGNED NOT NULL DEFAULT 0,
  `dname` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
  `loc` varchar(13) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT ''
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `dept` VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO `dept` VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO `dept` VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO `dept` VALUES (40, 'OPERATIONS', 'BOSTON');

select * from dept

-- 员工表
CREATE TABLE `emp`  (
  `empon` mediumint(8) UNSIGNED NOT NULL DEFAULT 0,
  `ename` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
  `job` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
  `mgr` mediumint(8) UNSIGNED NULL DEFAULT NULL,
  `hiredate` date NOT NULL,
  `sal` decimal(7, 2) NOT NULL,
  `comm` decimal(7, 2) NULL DEFAULT NULL,
  `depton` mediumint(8) UNSIGNED NOT NULL DEFAULT 0
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `emp` VALUES (7369, 'SMITH', 'CLERK', 7902, '1990-12-17', 800.00, NULL, 20);
INSERT INTO `emp` VALUES (7499, 'ALLEN', 'SALESMAN', 7698, '1991-02-20', 1600.00, 300.00, 30);
INSERT INTO `emp` VALUES (7521, 'WARD', 'SALESMAN', 7968, '1991-02-22', 1250.00, 500.00, 30);
INSERT INTO `emp` VALUES (7566, 'JONES', 'MANAGER', 7839, '1991-04-02', 2975.00, NULL, 20);
INSERT INTO `emp` VALUES (7654, 'MARTIN', 'SALESMAN', 7968, '1991-09-28', 1250.00, 1400.00, 30);
INSERT INTO `emp` VALUES (7698, 'BLAKE', 'MANAGER', 7839, '1991-05-01', 2850.00, NULL, 30);
INSERT INTO `emp` VALUES (7782, 'CLARK', 'MANAGER', 7839, '1991-06-09', 2450.00, NULL, 10);
INSERT INTO `emp` VALUES (7788, 'SCOTT', 'ANALYST', 7566, '1991-04-19', 3000.00, NULL, 20);
INSERT INTO `emp` VALUES (7839, 'KING', 'PRESIDENT', NULL, '1991-11-17', 5000.00, NULL, 10);
INSERT INTO `emp` VALUES (7844, 'TURNER', 'SALESMAN', 7698, '1991-09-08', 1500.00, NULL, 30);
INSERT INTO `emp` VALUES (7900, 'JAMES', 'CLERK', 7698, '1991-12-03', 950.00, NULL, 30);
INSERT INTO `emp` VALUES (7902, 'FORD', 'ANALYST', 7566, '1991-12-03', 3000.00, NULL, 20);
INSERT INTO `emp` VALUES (7934, 'MILLER', 'CLERK', 7782, '1991-01-23', 1300.00, NULL, 10);

select * from emp

-- 薪水等级表
CREATE TABLE `salgrade`  (
  `grade` mediumint(8) UNSIGNED NOT NULL DEFAULT 0,
  `losal` decimal(17, 2) NOT NULL,
  `hisal` decimal(17, 2) NOT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `salgrade` VALUES (1, 700.00, 1200.00);
INSERT INTO `salgrade` VALUES (2, 1201.00, 1400.00);
INSERT INTO `salgrade` VALUES (3, 1401.00, 2000.00);
INSERT INTO `salgrade` VALUES (4, 2001.00, 3000.00);
INSERT INTO `salgrade` VALUES (5, 3001.00, 9999.00);

select * from salgrade

一、查询加强

1、条件查询

• 使用where子句

  如何查询1992.1.1后入职的员工

  在mysql中，日期类型可以直接比较

  select * from emp where hiredate <= '1992-01-01'
  

• 使用like操作符
  %：表示0到多个字符，_（下划线）：表示单个任意字符
  如何显示首字符为 S 的员工姓名和工资

  select ename,sal from emp where ename like 'S%'
  

  如何显示第三个字符为大写 O 的所有员工名字和工资

  select ename,sal from emp where ename like '__O%'
  

• 如何显示没有伤及的雇员情况

  select * from emp where mgr is null
  

• 查询表结构

  desc emp
  

• 使用order by子句

  如何按照工资的从高到低顺序，显示雇员信息

  -- 从低到高
  select * from emp order by sal asc
  -- 从高到低
  select * from emp order by sal desc
  

  按照部门号升序而雇员的工资降序排列，显示雇员信息

  select * from emp order by depton asc,sal desc
  

2、分页查询

基本语法：

select .... limit start,rows

表示从 start+1 行开始取，取出rows行，start 从0开始计算

练习：按雇员的id号降序取出，每页显示5条记录。请分别显示第3页，第5页对应的sql语句

-- 第一页
select * from emp order by empon desc limit 0,5
-- 第二页
select * from emp order by empon desc limit 5,5
-- 第三页
select * from emp order by empon desc limit 20,5

推导公式：

-- 推到分页公式
select * from emp order by empon asc limit (rows*start-1),rows

3、分组加强

使用分组函数和分组子句 group by

练习代码：

-- 显示每种岗位的雇员总数，平均工资
select count(*),avg(sal),job from emp group by job

-- 显示雇员总数，以及获得补助的雇员数
select count(*),count(comm) from emp

-- 统计没有获得补助的雇员数
-- count() 函数会过滤掉为null的数据，不进行统计
-- if(comm is null,1,null) 如果这个字段为null，返回一个null，不为null，返回 1
select count(*),count(if(comm is null,1,null)) from emp

-- 显示管理者的总人数
-- distinct 去重
select count(distinct mgr) from emp

-- 显示雇员工资的最大差额
select max(sal) - min(sal) from emp

4、多子句查询

子句顺序：

select column1,column2,.....from table_name
	group by column
	having condition
	order by column
	limit start,rows

练习：

-- 统计各个部门的平均工资，并且是大于1000的，并且按照平均工资从高到底排序，取出前2条记录

select depton,avg(sal) avg_sal from emp
    group by depton
    having avg_sal > 1000
    order by avg_sal desc
    limit 0,2

二、多表查询

说明：多表查询指的是基于两个和两个以上的表查询，在实际应用中，查询单个表可能不能满足你的需求

1、多表笛卡尔集

在没有加上限定条件下出现的问题：

select * from emp,dept

在默认情况下，当两个表查询时，规则

1、从第二张表中，取出一行和第二张表的每一行进行组合，返回的结果含有两张表的所有列

2、一共返回的记录数：第一张表的行数 x 第二张表的行数

3、这样多表查询默认处理返回的结果，称为笛卡尔集

4、解决这种多表的关键就是要写出正确的过滤条件 where，需要程序员进行分析

解决问题示例代码：

select 
ename,sal,dname,dept.depton
from emp,dept where emp.depton = dept.depton

注意事项：

多表查询的条件不能少于 表的个数-1，否则会出现笛卡尔集

多表查询练习：

-- 显示各个员工的姓名，工资以及其工资的级别
select ename,sal,grade from emp,salgrade
where sal between losal and hisal

-- 显示雇员名，雇员工资所在部门的名字，并按部门排序【降序】
select ename,sal,dname from emp,dept
where dept.depton = emp.depton
order by sal desc

2、自连接

说明：自连接是指在同一张表的连接查询【将同一张表看做两张表】

分析：

1、员工名字在emp表，上级的名字在 emp表

2、员工和上级通过emp表的mgr列关联

3、自连接使用别名进行区分表

select worker.ename '员工名',boss.ename '上级名' from emp worker,emp boss
where worker.mgr = boss.empon

特点：

1、把同一张表看做两张表进行使用

2、需要给表取别名，表名 表别名

3、列名不明确，可指定列的别名

3、子查询

说明：子查询是指嵌入在其他sql语句总的select语句，也叫嵌套查询

• 单行子查询

  单行子查询是指返回一行数据的子查询语句

• 多行子查询

  多行子查询指返回多行数据的子查询，使用关键字in

练习：

-- 查询和部门编号为10的工作相同的雇员
-- 的名字、岗位、工资、部门号，但是不包含自己的

-- 1、先查询10号部门的岗位，去重
select distinct job from emp where depton = 10
-- 2、把上面查询的结果当做子查询使用
select ename,job,sal,depton
	from emp 
	where job in (
			select distinct job from emp where depton = 10
	) and depton != 10
	
	
-- 查询每个部门最高工资的人员工的名字和部门名称
select emp.ename,temp.max_sal,dname from (
	select max(sal) max_sal,depton from emp group by depton
) temp,emp,dept 
where temp.max_sal = emp.sal and temp.depton = dept.depton
order by emp.depton

all 和 any 的使用

all 的使用

all 的意思就是：查询的员工工资，必须要比所有30部门的员工工资高可以

-- 显示工资比部门30的所有员工的工资高的员工姓名、工资和部门号
-- 第一种方式
select ename,sal,depton
from emp
where sal > all(
	select sal from emp where depton = 30
)
-- 第二种方式
select ename,sal,depton
from emp
where sal > (
	select max(sal) from emp where depton = 30
)

any 的使用

any 的意思是，查询的员工工资，只要有一个员工工资比30号部门工资高就可以

-- 显示工资比部门30的其中一个员工的工资高的员工的姓名、工资和部门号
-- 第一种方式
select ename,sal,depton
from emp
where sal > any(
	select sal from emp where depton = 30
)
-- 第二种方式
select ename,sal,depton
from emp
where sal > (
	select min(sal) from emp where depton = 30
)

多列子查询

多列子查询是指查询返回多个列数据的子查询语句

-- 查询与allen的部门和岗位完全相同的所有雇员（不包含allen本人）
select * from emp
			where (depton,job) = (
				select depton,job
				from emp
				where ename = 'allen'
			) and ename != 'allen'

子查询练习题

-- 查询每个部门工资高于本部门平均工资的人的资料
-- 先查询每个部门的平均工资
select avg(sal) avg_sal,depton from emp group by depton

select ename,sal,t.avg_sal,t.depton
from emp e,(select avg(sal) avg_sal,depton from emp group by depton) t
where e.sal > t.avg_sal and e.depton = t.depton

-- 查找每个部门工资最高的人的详细资料
-- 先查找每个部门的最高工资
select max(sal) max_sal,depton from emp group by depton

select *
from emp e,(select max(sal) max_sal,depton from emp group by depton) t
where e.sal = t.max_sal and e.depton = t.depton

-- 查询每个部门的信息（部门编号，名称，地址）和人员数量
-- 先统计每个部门的人数
select count(depton) count_num,depton from emp group by depton
-- 在多表查询中，当多个表的列名不重复时，才可以直接写列名
select d.*,t.count_num 
from dept d,(
	select count(depton) count_num,depton from emp group by depton
)t where d.depton = t.depton

三、表复制和去重

1、自我复制数据（蠕虫复制）

使用场景：有时，为了对某个sql语句进行效率测试，我们需要海量数据时，可以使用此法为表创建海量数据

（1）首先创建一张表

-- 包含emp的一些字段
create table my_tab01(
	id int,
	`name` varchar(32),
	sal double,
	job varchar(32),
	depton int
)

（2）将emp表的数据复制 my_tab01

insert into my_tab01
(id,`name`,sal,job,depton)
select empon,ename,sal,job,depton from emp

（3）自我复制

insert into my_tab01 select * from my_tab01

2、表的去重

如何去掉表中重复的数据

（1）创建一张表，结构与emp一致

-- 把emp表的结构复制到my_tab02
create table my_tab02 like emp 

（2）将emp表的数据插入my_tab02表中

insert into my_tab02 select * from emp

（3）去重 my_tab02 的记录

思路：

（1）先创建一张临时表 my_tmp，该表的结构 和 my_tab02 一样

create table my_tmp like my_tab02

（2）把 my_tmp 的记录通过 distinct 关键字处理后，把记录复制到 my_tmp

insert into my_tmp 
	select distinct * from my_tab02

（3）清除掉 my_tab02 的记录

delete from my_tab02

（4）把 my_tmp 表的记录复制到 my_tab02

insert into my_tab02 select * from my_tmp

（6）删除 my_tmp 表

drop table my_tmp

四、合并查询

说明：有时在实际应用中，为了合并多个select语句的结果，可以使用集合操作符号 union，union all

1、union all

该操作符用于取得两个结果集的并集，当使用该操作符时，不会取消重复行

select ename,sal,job from emp where sal > 2500
union all
select ename,sal,job from emp where job = 'MANAGER'

2、union

该操作符用于取得两个结果集的并集，当使用该操作符时，会取消重复行

select ename,sal,job from emp where sal > 2500
union
select ename,sal,job from emp where job = 'MANAGER'

五、外连接

只要的连接方式

1、左外连接

（左侧的表数据完全显示称为左外连接）

即使左边的表跟右边的表没有匹配的记录，那么左边的表数据也会完全显示出来

基本语法：

select cloumn1,cloumn2,cloumn3 from table_name01 left join table_name02 on 条件

2、右外连接

（右侧的表数据完全显示称为右外连接）

即使右边的表跟左边的表没有匹配的记录，那么右边的表数据也会完全显示出来

基本语法：

select cloumn1,cloumn2,cloumn3 from table_name01 right join table_name02 on 条件

3、内连接

（只返回两个表的结果集的交集）

也就是说，当左边表的数据与右边表的数据没有匹配的记录，那么就把左边表没有匹配的记录过滤掉，只显示与右边表匹配的记录

select cloumn1,cloumn2,cloumn3 from table_name01 inner join table_name02 on 条件

测试代码：

create table stu(
	id int,
	`name` varchar(21)
)

insert into stu values(1,'Jack'),(2,'Tom'),(3,'Kity'),(4,'nono')

create table exam(
	id int,
	grade int
)

insert into exam values(1,56),(2,76),(11,8)

-- 使用左外连接（显示所有人的成绩，如果没有成绩，也要显示该人的姓名和id号，成绩为空）
select s.id,s.`name`,e.grade from stu s
left join exam e
on s.id = e.id

-- 使用右外连接（显示所有成绩，如果没有名字，显示为空）
select s.id,s.`name`,e.grade from stu s
right join exam e
on s.id = e.id

-- 内连接
select s.id,s.`name`,e.grade from stu s
inner join exam e
on s.id = e.id