mysql连接查询、自查询

sql的写法：

1. 找表

2. 找查询的列

3. 找查询条件

4. 拼接语句

聚合函数

 count()、sum()、avg()、min()、max()

对一组值执行计算  结合group by 使用 可结合having 对分组之后筛选

不可用于WHERE语句中，因为WHERE是对逐条的行记录进行筛选。

sql语句的顺序

编写顺序：SELECT ——》FROM ——》 WHERE ——》 GROUP BY ——》HAVING ——》ORDER BY ——》LIMIT

执行顺序： FROM ——》 WHERE——》 GROUP BY——》HAVING ——》SELECT——》 ORDER BY ——》LIMIT

多表查询

表连接不止两张表，可以是多张表

内连接：符合连接条件

SELECT * FROM emp e JOIN dept d ON e.deptno = d.deptno; #标准写法
SELECT * FROM emp e, dept d WHERE e.deptno = d.deptno; #非标准写法

外连接：符合连接条件+不符合其中某张表的条件

左外连接 A LEFT OUTER JOIN B 左边表的数据全部显示 主表为左表

右外连接 A RIGHT OUTER JOIN B右边表的数据全部显示 主表为右表

自连接  一张表连接查询多次

#查询员工的姓名和对应领导的姓名
-- 自连接(内连接)
SELECT e.ename as '员工',e2.ename as '领导' from emp e JOIN emp e2 on e2.empno = e.mgr;
#要全部员工都出来 外连接
SELECT e.ename as '员工',e2.ename as '领导' from emp e left JOIN emp e2 on e2.empno = e.mgr;

表连接查询

#根据员工编号查询员工的姓名，薪水，岗位，部门名称，薪水等级
#n张表的连接 n-1个连接条件 
-- 表：emp dept grade 2个连接条件
SELECT
    e.ename,e.sal,e.job,d.dname,g.grade 
FROM
    emp e
    JOIN dept d ON e.deptno = d.deptno
    JOIN grade g ON e.sal BETWEEN g.lower AND g.higher 
WHERE
    e.empno = 7788;

子查询

子查询出现的位置 ：

where后，作为条件的一部分；

from后，作为被查询的一条表；

select之后，作为被查询的一列。

子查询的结果：

结果	作为
单行单列	条件
多行多列	表
单行多列	表、条件
多行单列	作为条件

比较运算符 < > = <= >= <>只能与单个值比较

= —— IN

<> != —— NOT IN

< > —— ANY 、 ALL

-- 查询员工信息 他的岗位与薪水与MARTIN 一样
SELECT * FROM emp WHERE job = (SELECT job from emp where ename = 'MARTIN') AND sal = (SELECT sal from emp where ename = 'MARTIN') AND ename <> 'MARTIN';
#最终简化版 使用多列in 多列作为整体，必须用括号引起
#子查询的结果与列的个数与in前面的列的个数一样，顺序一样
SELECT * FROM emp WHERE (job,sal) in (SELECT job,sal FROM emp WHERE ename = 'MARTIN' ) and ename <> 'MARTIN';
 
-- 查询工资高于部门30的所有人的员工信息
SELECT * from emp WHERE sal > ALL(SELECT sal FROM emp WHERE deptno =30);
-- 工资比部门30的任何一个人低    
SELECT * from emp WHERE sal < ANY(SELECT sal FROM emp WHERE deptno =30);
#小于部门30的工资最大值  
SELECT * from emp where sal < (SELECT max(sal) FROM emp WHERE deptno =30);

# 小于任意一个   <最大值   大于任意一个  >最小值

---

例1

 

行列转换

方法1   使用聚合函数

 SELECT  `name` ,sum(IF(course='hibernate',score,0))AS'hibernate' ,sum(IF(course='spring',score,0))AS'spring' ,sum(IF(course='struts',score,0))AS'struts'FROM tb_course GROUP BY `name`;

方法2  表连接查询

 select DISTINCT t1.name,
 (SELECT t2.score from tb_course t2 where t2.course = 'hibernate' AND t2.name = t1.name) hi,
(SELECT t3.score from tb_course t3 where t3.course = 'spring' AND t3.name = t1.name) sp,
 (SELECT t4.score from tb_course t4 where t4.course = 'struts' AND t4.name = t1.name) st  
from tb_course t1;

例2

 

与例1方法1同样写法

SELECT  `year` ,sum(IF(month='1',amount,0))AS'm1' ,sum(IF(month='2',amount,0))AS'm2' ,sum(IF(month='3',amount,0))AS'm3',sum(IF(month='4',amount,0))AS'm4'  FROM test4 GROUP BY `year`;

 

 例3

-- 17.查询出 king 所在部门的部门号\部门名称\部门人数  
#找出king的部门号
SELECT deptno from emp WHERE ename = 'KING'；
#通过部门号找名字
SELECT e.deptno,d.dname from emp e left join dept d on d.deptno = e.deptno where e.deptno = (SELECT deptno from emp WHERE ename = 'KING');
#通过部门号找人数
SELECT deptno,count(1) num from emp where deptno = (SELECT deptno from emp WHERE ename = 'KING') GROUP BY deptno
#奇怪的多余写法
SELECT t.deptno,d.dname,num from dept d  left join  ( SELECT deptno,count(1) num from emp where deptno = (SELECT deptno from emp WHERE ename = 'KING') GROUP BY deptno)t on t.deptno = d.deptno WHERE t.deptno = d.deptno;

#正确写法 表连接嵌套查询 
select t.countno,d.dname,d.loc from dept d,(
     select count(*) countno,deptno from emp where deptno in(
           select deptno from emp where ename='KING') group by deptno )t where d.deptno=t.deptno;

例4

-- 13.查找出工资等级不为 4 级的员工的员工名字，部门名字，部门位置  
#1.先找等级为4的界限
SELECT LOWER,higher FROM grade WHERE grade = 4;
#找出工资等级不为4的员工名字
SELECT ename from emp WHERE sal NOT BETWEEN (SELECT LOWER FROM grade WHERE grade = 4) AND (SELECT higher FROM grade WHERE grade = 4) 
#结合部门名字，部门位置
SELECT e.ename,d.dname,d.loc FROM emp e JOIN dept d on d.deptno = e.deptno and e.sal NOT BETWEEN (SELECT LOWER FROM grade WHERE grade = 4) AND (SELECT higher FROM grade WHERE grade = 4);
 

例5

-- 10.分部门得到平均工资等级为 3 级（等级表）的部门编号  
-- 
SELECT
    deptno,
    avg_sal,
    g.grade 
FROM
    ( SELECT deptno, AVG( sal ) avg_sal FROM emp GROUP BY deptno) t
JOIN grade g ON t.avg_sal BETWEEN g.lower 
AND g.higher 
WHERE
    g.grade = 3;

 例6

-- 25. 列出薪金高于公司平均薪金的所有员工信息emp，所在部门名称dept，上级领导emp，工资等级grade

 

SELECT e1.*, d.dname,e2.*,g.grade
FROM emp e1
LEFT JOIN dept d ON e1.deptno = d.deptno
LEFT JOIN emp e2 ON e1.mgr = e2.empno
JOIN grade g ON e1.sal BETWEEN g.lower AND g.higher
WHERE e1.sal > (
   SELECT AVG(sal) FROM emp
);

 e2找出领导的信息 d找出部门信息