-- 创建部门表createtableifnotexists dept3(
deptno varchar(20)primarykey,-- 部门号
name varchar(20)-- 部门名字);-- 创建员工表createtableifnotexists emp3(
eid varchar(20)primarykey,-- 员工编号
ename varchar(20),-- 员工名字
age int,-- 员工年龄
dept_id varchar(20)-- 员工所属部门);-- 给部门表添加数据insertinto dept3 values('1001','研发部');insertinto dept3 values('1002','销售部');insertinto dept3 values('1003','财务部');insertinto dept3 values('1004','人事部');-- 给员工表添加数据insertinto emp3 values('1','乔峰',20,'1001');insertinto emp3 values('2','段誉',21,'1001');insertinto emp3 values('3','虚竹',23,'1001');insertinto emp3 values('4','阿紫',18,'1001');insertinto emp3 values('5','扫地僧',85,'1002');insertinto emp3 values('6','李秋水',33,'1002');insertinto emp3 values('7','鸠摩智',50,'1002');insertinto emp3 values('8','天山童姥',60,'1003');insertinto emp3 values('9','慕容博',58,'1003');insertinto emp3 values('10','丁春秋',71,'1005');-- 1、交叉连接查询-- 交叉连接查询返回被连接的两个表所有数据行的笛卡尔积-- 笛卡尔积可以理解为一张表的每一行去和另外一张表的任意一行进行匹配-- 假如A表有m行数据,B表有n行数据,则返回m*n行数据-- 笛卡尔积会产生很多冗余的数据,后期的其他查询可以在该集合的基础上进行条件筛选-- 格式:select * from 表1,表2,表3….; select*from dept3,emp3;-- 2、内连接查询-- 内连接查询求多张表的交集/*
格式
隐式内连接(SQL92标准):select * from A,B where 条件;
显示内连接(SQL99标准):select * from A [inner] join B on 条件;
*/-- 查询每个部门的所属员工-- 隐式内连接select*from dept3 d,emp3 e where d.deptno=e.dept_id;-- 显示内连接select*from dept3 d innerjoin emp3 e on d.deptno=e.dept_id;select*from dept3 d join emp3 e on d.deptno=e.dept_id;-- 查询研发部门的所属员工-- 隐式内连接select*from dept3 d,emp3 e where d.deptno=e.dept_id and name='研发部';-- 显示内连接select*from dept3 d join emp3 e on d.deptno=e.dept_id and name='研发部';-- 查询研发部和销售部的所属员工select*from dept3 d join emp3 e on d.deptno=e.dept_id and(name='研发部'or name='销售部');select*from dept3 d join emp3 e on d.deptno=e.dept_id and name in('研发部','销售部');-- 查询每个部门的员工数,并升序排序select d.name,d.deptno,count(1) 员工数 from dept3 d join emp3 e on d.deptno=e.dept_id groupby d.name,d.deptno orderby 员工数 asc;-- 查询人数大于等于3的部门,并按照人数降序排序select
d.name,d.deptno,count(1)as total_cnt
from dept3 d
join emp3 e on d.deptno=e.dept_id
groupby
d.name,d.deptno
having
total_cnt>=3orderby
total_cnt desc;
外连接查询
-- 外连接查询/*
格式:
左外连接:left [outer] join
select * from A left outer join B on 条件;
右外连接:right [outer] join
select * from A right outer join B on 条件;
满外连接: full [outer] join
select * from A full outer join B on 条件;
*/-- 查询哪些部门有员工,哪些部门没有员工(左外连接)select*from dept3 d leftouterjoin emp3 e on d.deptno=e.dept_id;select*from dept3 d leftjoin emp3 e on d.deptno=e.dept_id;-- 查询哪些员工有对应的部门,哪些没有(右外连接)select*from dept3 d rightouterjoin emp3 e on d.deptno=e.dept_id;select*from dept3 d rightjoin emp3 e on d.deptno=e.dept_id;/*
满外连接:full join
使用union关键字实现左外连接和右外连接的并集
*/-- select * from dept3 d full join emp3 e on d.deptno=e.dept_id;-- 执行失败-- union是将两个查询结果上下拼接,去重select*from dept3 d leftjoin emp3 e on d.deptno=e.dept_id
unionselect*from dept3 d rightjoin emp3 e on d.deptno=e.dept_id;-- union all是将两个查询结果上下拼接,不去重select*from dept3 d leftjoin emp3 e on d.deptno=e.dept_id
unionallselect*from dept3 d rightjoin emp3 e on d.deptno=e.dept_id;
基本子查询
/*
子查询
子查询就是指的在一个完整的查询语句之中,嵌套若干个不同功能的小查询,从而一起完成复杂查询的一种编写形式,通俗一点就是包含select嵌套的查询。
*/-- 查询年龄最大的员工信息,显示信息包含员工号、员工名字,员工年龄-- 1:查询最大年龄selectmax(age)from emp3;-- 2:让每一个员工的年龄和最大年龄进行比较,相等则满足条件-- 单行单列,可以作为一个值来用select*from emp3 where age=(selectmax(age)from emp3);-- 查询研发部和销售部的员工信息,包含员工号、员工名字-- 方式1:关联查询select*from dept3 d join emp3 e on d.deptno=e.dept_id and(name='研发部'or name='销售部');-- 方式2:子查询-- 2.1先查询研发部和销售部的部门号deptno 1001和1002select deptno from dept3 where name='研发部'or name='销售部';-- 2.2查询哪个员工的部门号是1001或1002-- 多行单列,多个值select*from emp3 where dept_id in(select deptno from dept3 where name='研发部'or name='销售部');-- 查询研发部23岁以下的员工信息,包括员工号、员工名字,部门名字-- 方式1-关联查询select*from dept3 d join emp3 e on d.deptno=e.dept_id and(d.name='研发部'and e.age<23);-- 方式2-子查询-- 2.1在部门表中查询研发部信息-- 一行多列select*from dept3 where name='研发部';-- 2.2在员工表中查询年龄小于23岁的员工信息select*from emp3 where age<23;-- 2.3将以上两个查询的结果进行关联查询-- 多行多列select*from(select*from dept3 where name='研发部') t1 join(select*from emp3 where age<23) t2 on t1.deptno=t2.dept_id;
子查询-关键字
-- 子查询-关键字/*
关键字:all
ALL: 与子查询返回的所有值比较为true 则返回true
ALL可以与=、>、>=、<、<=、<>结合是来使用,分别表示等于、大于、大于等于、小于、小于等于、不等于其中的其中的所有数据。
ALL表示指定列中的值必须要大于子查询集的每一个值,即必须要大于子查询集的最大值;如果是小于号即小于子查询集的最小值。同理可以推出其它的比较运算符的情况。
select …from …where c > all(查询语句)
--等价于:
select ...from ... where c > result1 and c > result2 and c > result3
*/-- 1、查询年龄大于‘1003’部门所有年龄的员工信息select*from emp3 where age >all(select age from emp3 where dept_id='1003');-- 2、查询不属于任何一个部门的员工信息select*from emp3 where dept_id !=all(select deptno from dept3);/*
关键字:any和some
ANY:与子查询返回的任何值比较为true 则返回true
ANY可以与=、>、>=、<、<=、<>结合是来使用,分别表示等于、大于、大于等于、小于、小于等于、不等于其中的其中的任何一个数据。
表示制定列中的值要大于子查询中的任意一个值,即必须要大于子查询集中的最小值。同理可以推出其它的比较运算符的情况。
SOME和ANY的作用一样,SOME可以理解为ANY的别名
select …from …where c > any(查询语句)
--等价于:
select ...from ... where c > result1 or c > result2 or c > result3
*/-- 查询年龄大于‘1003’部门任意一个员工年龄的员工信息select*from emp3 where age >any(select age from emp3 where dept_id='1003')and dept_id!='1003';/*
关键字:in
IN关键字,用于判断某个记录的值,是否在指定的集合中
在IN关键字前边加上 not 可以将条件反过来
select …from …where c in(查询语句)
--等价于:
select ...from ... where c = result1 or c = result2 or c = result3
*/-- 查询研发部和销售部的员工信息,包含员工号、员工名字select eid,ename from emp3 where dept_id in(select deptno from dept3 where name='研发部'or name='销售部');/*
关键字:exists
该子查询如果“有数据结果”(至少返回一行数据), 则该EXISTS() 的结果为“true”,外层查询执行
该子查询如果“没有数据结果”(没有任何数据返回),则该EXISTS()的结果为“false”,外层查询不执行
EXISTS后面的子查询不返回任何实际数据,只返回真或假,当返回真时 where条件成立
注意,EXISTS关键字,比IN关键字的运算效率高,因此,在实际开发中,特别是大数据量时,推荐使用EXISTS关键字
select …from …where exists(查询语句)
*/select*from emp3 whereexists(select1);-- 全表输出select*from emp3 whereexists(select*from emp3);-- 全表输出-- 查询公司是否有大于60岁的员工,有则输出select*from emp3 e whereexists(select*from emp3 where e.age>60);select*from emp3 e where eid in(select eid from emp3 where e.age>60);-- 查询有所属部门的员工信息select*from emp3 e whereexists(select*from dept3 d where e.dept_id=d.deptno);select*from emp3 e where e.dept_id in(select d.deptno from dept3 d where e.dept_id=d.deptno);
自关联查询
/*
MySQL有时在信息查询时需要进行对表自身进行关联查询,即一张表自己和自己关联,一张表当成多张表来用。注意自关联时表必须给表起别名。
select 字段列表 from 表1 a , 表1 b where 条件;
或者
select 字段列表 from 表1 a [left] join 表1 b on 条件;
*/-- 创建 三国表,并建立自关联约束createtable t_sanguo(
eid intprimarykey,-- 员工id
ename varchar(20),-- 员工名
manager_id int,-- 上级领导id,外键列foreignkey(manager_id)references t_sanguo (eid)-- 添加自关联约束);-- 添加数据 insertinto t_sanguo values(1,'刘协',NULL);insertinto t_sanguo values(2,'刘备',1);insertinto t_sanguo values(3,'关羽',2);insertinto t_sanguo values(4,'张飞',2);insertinto t_sanguo values(5,'曹操',1);insertinto t_sanguo values(6,'许褚',5);insertinto t_sanguo values(7,'典韦',5);insertinto t_sanguo values(8,'孙权',1);insertinto t_sanguo values(9,'周瑜',8);insertinto t_sanguo values(10,'鲁肃',8);-- 进行关联查询-- 1.查询每个三国人物及他的上级信息,如: 关羽 刘备 select a.ename,b.ename 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 on a.manager_id=b.eid;-- 2.查询所有人物及上级select a.ename,b.ename from t_sanguo a leftjoin t_sanguo b on a.manager_id=b.eid;-- 3.查询所有人物、上级,上上级 比如:张飞、刘备、刘协select
a.ename,b.ename,c.ename
from t_sanguo a
leftjoin t_sanguo b on a.manager_id=b.eid
leftjoin t_sanguo c on b.manager_id=c.eid;