mysql group by复合函数

目录

复合查询与表的外连接

多表查询

自连接

子查询：

单行子查询

多行子查询

在from子句中使用子查询

表的连接分为内连和外连

内连接

外连接（重点）

左外链接：

右外连接：

复合查询与表的外连接

多表查询

• emp表中的deptno=dept表中的deptno字段记录

select emp.ename,eap.sal,dept.dname from emp,dept 
   where emp.deptno = dept.deptno;

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

select ename, sal,dname from emp, dept where 
    emp.deptno=dept.deptno and dept.deptno = 10;

• 显示各个员工的姓名，工资，及工资级别

select ename,sal, grade from emp, salgrade where 
    emp.sal between losal and hisal;

自连接

自连接是指在同一张表连接查询

显示员工from 上级领导的编号和姓名

• 使用字查询：

select empno,ename from emp where 
    empno =(select mgr from emp where ename = 'from');

• 使用多表查询：

--使用到表的名称

select leader.empno,leader.ename from emp leader,emp worker where 
leader.empno = worker.mgr and worker.ename = 'ford';

子查询：

是嵌入在其他SQL语句中的select语句，也叫嵌套查询

单行子查询

返回一行记录的子查询

• 显示SMITH同一部门的员工

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

多行子查询

返回多行记录的子查询

• in关键字；

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

select ename,job,sal,empno from emp where 
  job in(select distinct job from emp where deptno = 10) 
     and deptno <>10;

• all关键字

select ename, sal, deptno from emp where 
    sal > all(select sal from emp where deptno=30);

• any关键字

select ename, sal, deptno from emp where 
   sal > any(select sal from emp where deptno=30);

在from子句中使用子查询

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

• 显示高于自己部门平均工资的员工的姓名、部门、工资、平均工资

   ---获取各个部门的平均工资，将其看作临时表

select ename, deptno, sal, format(asal,2) from emp, 
(select avg(sal) asal, deptno dt from emp group by deptno) tmp 
   where EMP.sal > tmp.asal and emp.deptno=tmp.dt;

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

select emp.ename, emp.sal, emp.deptno, ms from emp, 
(select max(sal) ms, deptno from emp group by deptno) tmp 
   where emp.deptno=tmp.deptno and EMP.sal=tmp.ms;

• 显示每个部门的信息（部门名，编号，地址）和人员数量

方法1：使用多表

select dept.dname,dept.deptno,dept.loc,count(*)'部门人数' from emp, dept 
 where emp.deptno = dept.deptno group by dept.deptno,dept.dname,dept.loc;

方法2：使用子查询

   1. 对EMP表进行人员统计

select count(*), deptno from emp group by deptno;

   2. 将上面的表看作临时表

select dept.deptno, dname, mycnt, loc from dept, 
 (select count(*) mycnt, deptno from emp group by deptno) 
   tmp where dept.deptno=tmp.deptno;

表的连接分为内连和外连

内连接

实际上就是利用where子句对两种表形成的笛卡儿进行筛选

语法：

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

例子：

显示SMITH的名称和部门名称

•   标准写法

select ename,dname from emp inner join dept on 
    emp.deptno = dept.deptno and ename = 'SMITH';

外连接（重点）

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

左外链接：

左侧的表完全显示

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

右外连接：

右侧的表完全显示

select * from 表名1 right join 表名2 on 连接条件;