6.mysql复合查询

6.复合查询

简单查询回顾

查询工资高于500或者岗位为manger的孤雁，同时满足首字母为大写J
select ename,sal job from emp where sal>500 or (job=manger and ename like 'J%')

按照部门号升序而员工工资降序排序

select ename,deptno,sal from emp order by deptno ，sal desc;

使用年薪排序

select ename,sal*12+ifnull(comm,0) 年薪from deptno order by 年薪; 

显示最高工资的员工的名字和岗位

select * from emp where sal=(select max(sal) from emp);

查询工资高于平均工资的姓名

select name,sal where sal>(select avg(sal) from emp) order by sal;

显示每个部门的最高工资和平均工资

select deptno, max(sal),avg(sal) from emp group by deptno;

显示工资低于2000的部门号和它的平均工资

select deptno,avg(sal) my_avg from emp group by deptno having my_avg<2000;

显示每种岗位雇人数及平均工资

select job, count(*) person ,avg(sal)from emp group by(job); 

多表查询

在实际开发中，数据往往来自于不同的表，所以就需要用到多表查询了

select * from emp,dept;

上面这条命令就是一般多表查询的雏形，它的具体含义如下：

1.从第一张表中选出第一条记录，和第二张表的所有记录进行组合

2.然后从第一张表中取出第二条记录，和第二张表中的所有记录组合

3.不加过滤条件，得到的结果称为笛卡尔积

结论：一般而言，我们所进行的后续多表查询，都应该是笛卡尔积表的子集

显示部门号为为10的部门明，员工名和工资

selecft deptno,ename sal from emp,dept where dept.deptno=emp.deptno;

自连接

一张表和别人笛卡尔积，当然可以和自己笛卡尔积

select* from emp ,emp emp_bak;

select ename,empno from emp worker,emp leader ehere worder.mgr=leader.empno and work.enmae='ford';

子查询:嵌套入其它sql中的select语句

单行子查询

返回一行记录的子查询

显示SMITH同一部门的员工

select * from emp where deptno=(select deptno from emp where ename='smith');

多行子查询

in关键字：
查询和10号部门的工作岗位的雇员的名字，岗位，工资，部门号，但是不包括自己
select ename,job,sal,deptno from emp where job in (select distinct job from emp where deptno=10) and deptno<>10;

all关键字:比所有人都...
显示工资比部门30的所有员工工资高的员工的姓名，工资和部门号
select ename,sgl,deptno from emp where sal> all(select distinct sal from emp where deptno=30 )

any关键字：比其中一个怎么咋们样
显示工资比部门30的任意员工工资高的员工的姓名，工资和部门号(包括自己部门的员工)
select ename,sal,deptno from emp where sal >any (select distinct sal from emp where deptno=30)

查询和SMITH的部门和岗位完全相同的所有雇员,不包括本人

select * from where (deptno,job) (select deptno,job from emp where ename='SMITH') and ename<>'SMITH';

多列子查询

单行子查询是指子查询只返回单列，单行数据；多行自查村是指返回单列多行数据，都是针对单列而言的，而多列自查村则是查村返回多个列数据的自查询语句

查询和smith部门和岗位完全相同的所有雇员，不含Smith本人
select ename from EMP where (deptno,job)=select(deptno,job from EMP where ename='SMITH') and ename <> 'SMITH';

在from子句中使用子查询

子查询语句出现在from子句中，这里要用到数据查询的技巧，把一个子查询当作以一个临时表使用

显示每个高于自己部门平均工资的员工的姓名，工资,部门，平均工资

select deptno,format(avg(sal),2) _avg from emp group by deptno;

select * from emp,(select deptno dt ,avg(sal) _avg from emp group by deptno) agv_tab where emp.dpetno=avg_tab.deptno and emp.sal>avg_tab._avg;

查找每个部门工资最高的人的姓名，工资，部门

select ename,sal,deptno,_max from emp, select deptno dp,max(sal)  _max,from emp group by dt max_tb where emp.deptno=max_tb.dp and emp.sal=max_tb._max ;

显示每个部门的信息与总人数

select * from dept,select deptno,count(*) from emp group by deptno sub_table where  dept.deptno=sub_table.septno;

子查询可以出现在两个地方：

1.where子句中，作为筛选条件使用

2.from子句中，用在作笛卡尔积

合并查询

把多个select的执行结果合并

union

改采哦做用于取得两个结果集的并集，当使用该操作符时，会自动去掉结果集中的重复行

将工资大于5000或者职位是manger的人

select ename from emp where sla>5000 or job='manger';

select ename,sal,job,from emp where sal>2500;
select ename,sal job from emp where job='manger'

union:上下两个表合并成一张表，去掉重复的内容
select ename,sal,job,from emp where sal>2500 union
select ename,sal job from emp where job='manger'
只要列数相同就可以

union all

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

union all与union的区别是不会进行去重
select ename,sal,job,from emp where sal>2500 union all
select ename,sal job from emp where job='manger'

8.表的内连接和外连接

查询的时候，有较大概率会涉及到多张表，往往需要把多张表“合并”称为一张表,所有的本质都可以转成为一张表的查询

自连接，子查询，内外链接本质都是回答如何完成多张表的合并工作

内连接

利用where子句对两张表形成的笛卡尔积进行筛选，查询都是内连接，也是在开发中最常用的链接

语法

select 字段 from 表1 inner join 表2 on 连接条件 and 连接条件

例如

select * from emp inner join dept on emp.deptno =dept.deptno;

显示SIMTH的名字和部门名
select * from emp,dept where emp.deptno=dept.deptno and emp.ename='SMITH';

select ename,dname from emp innor join dept on emp.deptno=dept.deptno;

外连接

外连接分为左外连接和右外连接

左外连接

如果联合查询，左侧的完全显示我们就说是左外连接

语法：

select 字段名 from 表名1 left join 表名2 on 连接条件

将两张表

-- 建两张表 
create table stu (id int, name varchar(30));-- 学生表
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);

select * from stu left join exam  on stu.id=exam.id;
//如果这个学生没有成绩，也要讲这个学生的个人信息显示出来

右外连接

如果联合查询，右侧的表完全显示我们就说是右外连接

select 字段 from 表1 right join 表名2 on 连接条件

对stu表和exam表联合查询，把所有的成绩显示出来，即使这个成绩没有学生与之对应也要显示出来
select * from stu right join exam on stu.id=exam.id;