目录
数据准备
1、创建部门表
create table if not exists dept3(
deptno varchar(20) primary key, -- 部门号
name varchar(20) -- 部门名字
);
2、创建员工表
create table if not exists emp3(
eid varchar(20) primary key, -- 员工编号
ename varchar(20) , -- 员工姓名
age int, -- 员工年龄
dept_id varchar(20) -- 员工所属部门
);
3、给dept3表添加数据
insert into dept3 values('1001','研发部');
insert into dept3 values('1002','销售部');
insert into dept3 values('1003','财务部');
insert into dept3 values('1004','人事部');
4、给emp3表添加数据
insert into emp3 values ('1','乔峰',20,'1001');
insert into emp3 values ('2','段誉',21,'1001');
insert into emp3 values ('3','虚竹',23,'1001');
insert into emp3 values ('4','阿紫',18,'1001');
insert into emp3 values ('5','扫地僧',85,'1002');
insert into emp3 values ('6','李秋水',33,'1002');
insert into emp3 values ('7','鸠摩智',50,'1002');
insert into emp3 values ('8','天山童姥',60,'1003');
insert into emp3 values ('9','慕容博',58,'1003');
insert into emp3 values ('10','丁秋水',71,'1005');
交叉连接查询
隐式内连接(SOL92标准):select * from A,B where 条件;
显示内连接(SOL99标准):select * from A [inner] join B on 条件;
区别:,= join where = on
格式
select * from 表1,表2,表3.....
select * from dept3,emp3;
1、查询每个部门的所属员工
a、 隐式内连接
select * from dept3,emp3 where deptno = dept_id;
进阶专业版
select * from dept3,emp3 where dept3.deptno = emp3.dept_id;
注: 当两个表有重名的字段时,在连接查询的时候需要指定是哪一个表
起别名
select * from dept3 a ,emp3 b where a.deptno = b.dept_id;
b、显示内连接
select * from dept3 inner join emp3 on dept3.deptno = emp3.dept_id;
select * from dept3 a join emp3 b on a.deptno = b.dept_id;
2、查询研发部门的所属员工
a、隐式
select * from dept3 a,emp3 b where a.deptno = b.dept_id and name = '研发部';
b、显示
select * from dept3 a join emp3 b on a.deptno = b.dept_id and name = '研发部';
2、查询研发部和销售部的所属员工
a、隐式
select * from dept3 a , emp3 b where a.deptno = b.dept_id and (name = '研发部' or name = '销售部');
b、 简化版本
select * from dept3 a,emp3 b where a.deptno = b.dept_id and name in ('研发部','销售部');
3、查询每个部门的员工数,并升序排列
select
a.deptno,count(1)
from dept3 a
join emp3 b on a.deptno = b.dept_id
group by
a.deptno;
4、查询人数大于等于3的部门,并按照人数降序排列显示
select
a.deptno,
a.name,
count(1) as total_cnt
from dept3 a
join emp3 b on a.deptno = b.dept_id
group by
a.deptno,a.name
having
total_cnt >= 3
order by
total_cnt desc;
外连接查询
概述:
a、左外连接:left [outer] join
语法: select*from A left outer join B on 条件;
注意:左表数据全部输出,右表没有对应数据就会补NULL
b、右外连接: right [outer] join
语法: select * from A right outer B on 条件;
注意:右表数据全部输出,左表没有对应数据就会补NULL
c、满外连接: full outer join
语法:select * from A full outer B on 条件;
注意:两表全部输出
1、左外连接left [outer] join
查询哪些部门有员工,哪些部门没员工
select * from dept3 a left outer join emp3 b on a.deptno = b.dept_id;
-- outer 可以省略
select * from dept3 a left outer join emp3 b on a.deptno = b.dept_id;多表的左外连接
select * from A
left join B on 条件1
left join C on 条件2
.....
2、右外连接right [outer] join
查询哪些员工有对应的部门,哪些没有
select * from dept3 a right outer join emp3 b on a.deptno = b.dept_id;
多表的右外连接
select * from A
right join B on 条件1
right join C on 条件2
.....
3、满外连接 full join
使用union关键字实现左外连接和右外连接的并集
注:
1、union是去重的
2、union把上面的结果和下面的结果拼在一起
3、join把左面的结果和右面的结果拼在一起
a、select * from dept3 a full join emp3 b on a.deptno = b.dept_id; -- MySQL不支持
b、select * from dept3 a left outer join emp3 b on a.deptno = b.dept_id
union
select * from dept3 a right outer join emp3 b on a.deptno = b.dept_id;c、select * from dept3 a left outer join emp3 b on a.deptno = b.dept_id
union all -- (unionall是没有去重的结果<把左外和右外拼在一起>)
select * from dept3 a right outer join emp3 b on a.deptno = b.dept_id;
子查询
1、查询年龄最大的员工信息,显示信息包含员工工号,员工姓名,员工年龄
a:查询最大年龄
select max(age) from emp3; -- 只能查询出最大年龄,不知道是谁
select * from emp3 where age = 85;
b:让每一个员工的年龄和最大年龄进行比较,相等则满足条件
select * from emp3 where age = (select max(age) from emp3);-- 单行单列,可以作为一个值来用
2、查询研发部和销售部的员工信息,包含员工工号,员工姓名
方式一:关联查询
select * from dept3 a join emp3 b on a.deptno = b.dept_id and (name = '研发部' or name = '销售部');
方式二:子查询
a:先查询研发部和销售部的部门编号:deptno 1001 和 1002
select deptno from dept3 where (name = '研发部' or name = '销售部');
b:查询哪个员工的部门号是 1001 或者 1002
select * from emp3 where dept_id in (select deptno from dept3 where (name = '研发部' or name = '销售部')); -- 多行单列
3、查询研发部30岁以下的员工信息,包括员工号,员工姓名,部门名字
方式一:关联查询
select * from dept3 a join emp3 b on a.deptno = b.dept_id and (name = '研发部' and age<20);
select * from dept3 a join emp3 b on a.deptno = b.dept_id where (name = '研发部' and age<20);
方式二:子查询 (查询结果当作表示用)
a:在部门表中查询研发部信息
select * from dept3 where name = '研发部'; -- 一行多列
b:在员工表中查询年龄小于30岁的员工信息
select * from emp3 where age < 30;
c:将以上两个查询的结果进行关联查询 (必须起别名,因为是表)
select * from (select * from dept3 where name = '研发部') t1 join (select * from emp3 where age < 30) t2 on t1.deptno = t2.dept_id; -- 多行多列
子查询中的关键字
关键字all
select ...from ...where c > all (查询语句)
等价于:
select....from.....where 列 > result1 and C > result2 and C > result3
特点:
a. ALL: 与子查询返回的所有值比较为true则返回true
b. ALL可以与=、>、>=、<、<=、<>结合是来使用,分别表示等于、大于、大于等于、小于、小于等于、不等于其中的其中的所有数据。
c. ALL表示指定列中的值必须要大于子查询集的每一个值, 即必须要大于子查询集的最大值;如果是小于号即小于子查询集的最小值。同理可以推出其它的比较运算符的情况。
查询年龄大于、'1003'部门所有年龄的员工信息
select * from emp3 where age > all (select age from emp3 where dept_id = '1003');
查询不属于任何一个部门的员工信息
select * from emp3 where dept_id != all (select deptno from dept3) ;
关键字ANY和SOME
格式
select ..from. ..where 列 > any (查询语句)
-- 等价于 --
select...from...where 列> result1 or 列 > result2 or 列〉result3
特点
a. ANY:与子查询返回的任何值比较为true则返回true
b. ANY可以与=、>、>=、<、<=、<>结合是来使用,分别表示等于、大于、大于等于、小于、小于等于、不等于其中的其中的任何- -个数据。
c. 表示制定列中的值要大于子查询中的任意一一个值, 即必须要大于子查询集中的最小值。同理可以推出其它的比较运算符的情况。
●SOME和ANY的作用一样,SOME可以理解为ANY的别名
操作
1、查询年龄大于'1003'部门任意一个员工年龄的员工信息
select * from emp3 where age > any (select age from emp3 where dept_id = '1003') and dept_id != '1003';
select * from emp3 where age > some (select age from emp3 where dept_id = '1003') and dept_id != '1003';
关键字-IN格式
select ...from ...where 列 in(查询语句)
注:只要有任何一个相等都会查出来
等价于:
select ...from ... where 列 = result1 or 列 = result2 or 列 = result3
特点:
a. IN关键字,用于判断某个记录的值,是否在指定的集合中
b. 在IN关键字前边加上not可以将条件反过来
操作
1、查询研发部和销售部的员工信息,包含员工号、员工名字
select eid,ename from emp3 where dept_id in (select deptno from dept3 where name ='研发部'or name ='销售部');
关键字-exists格式
select ...from ...where exists(查询语句)
注:查询语句至少返回一行,及成立
特点
a. 该子查询如果“有数据结果”(至少返回一行数据),则该EXISTS()的结果为"true",外层查询执行
b. 该子查询如果“没有数据结果”(没有任何数据返回),则该EXISTS()的结果为"false",外层查询不执行
c. EXISTS后面的子查询不返回任何实际数据,只返回真或假,当返回真时where条件成立
## 注意EXISTS关键字,比IN关键字的运算效率高,因此,在实际开发中,特别是大数据量时,推荐使用EXISTS关键字
操作
select * from emp3 where exists (select 1); -- 全表输出
select * from emp3 where exists (select * from emp3);-- 全表输出
1、查询公司是否有大于60岁的员工,有则输出
-- 不起别名就是全表输出,由外面的决定里面的
select * from emp3 a where exists(select * from emp3 where a.age > 60);
select * from emp3 a where eid in(select eid from emp3 where a.age > 60);
2、查询有所属部门的员工信息
select * from emp3 a where exists(select * from dept3 b where a.dept_id = b.deptno);
select * from emp3 a where dept_id in (select deptno from dept3 b where a.dept_id = b.deptno);
自关联查询
自关联查询,即一张表自己和自己关联,一张表当成多张表来用。
注意:自关联时表必须给表起别名。
格式:
select字段列表from表l a , 表1 b where条件;或者
select字段列表from 表1 a [left] join表1 b on条件;
操作
数据准备
1、创建表,并建立自关联约束
create table t_sanguo (
eid int primary key , -- 主键列
ename varchar (20) ,
manager_id int, -- 外键列 (受主键列约束)
foreign key (manager_id) references t_sanguo(eid) -- 添加自关联约束
);
2、添加数据
insert into t_sanguo values (1 ,'刘协',NULL);
insert into t_sanguo values (2,'刘备',1);
insert into t_sanguo values ( 3,'关羽',2);
insert into t_sanguo values (4,'张飞',2);
insert into t_sanguo values (5,'曹操',1);
insert into t_sanguo values (6,'许褚',5);
insert into t_sanguo values (7,'典韦',5);
insert into t_sanguo values (8,'孙权',1);
insert into t_sanguo values (9,'周瑜',8) ;
insert into t_sanguo values (10,'鲁肃',8);
操作:进行关联查询
1.查询每个三国人物及他的上级信息,如:关羽(员工) 刘备(领导)
select * from t_sanguo a,t_sanguo b where a.manager_id = b.eid;
select a.ename,b.ename from t_sanguo a join t_sanguo b where a.manager_id = b.eid;
2、查询所有人物及上级
select a.ename , b.ename from t_sanguo a left join t_sanguo b on a.manager_id = b.eid;
3、查询所有人物、上级、上上级
select
a.ename , b.ename,c.ename
from t_sanguo a
left join t_sanguo b on a.manager_id = b.eid
left join t_sanguo c on b.manager_id = c.eid ;