SQL复杂查询例子

SQL语句中复杂查询分为 —— 聚合查询/分组查询

 聚合查询

 示例：查询出所有员工的数量

  SELECT  COUNT(*)  FROM emp;     #推荐写法

  SELECT  COUNT(ename)  FROM  emp;

  SELECT  COUNT(eid)  FROM  emp;

  SELECT  COUNT(deptId)  FROM emp;

 

   COUNT(...)总数/SUM(...)和/AVG(..)平均值/MAX(...)/MIN(...)  MySQL中提供了5个聚合函数

  练习：查询出10号部门员工的数量

    SELECT  COUNT(ename)  FROM emp  WHERE deptId=10;

  练习：查询出10号部门所有工资的总和

    SELECT  SUM(salary)  FROM  emp  WHERE deptId=10;

  练习：查询出所有男员工的平均工资

    SELECT  SUM(salary)/COUNT(*) FROM emp WHERE sex=1;

    SELECT AVG(salary) FROM emp WHERE sex=1;

  练习：查询出1990年出生的员工工资最大值和最小值

    SELECT MAX(salary),MIN(salary) FROM emp WHERE birthday>='1990-1-1' AND birthday<='1990-12-31';

  分组查询

  练习：查询出每个部门的员工数量

    SELECT  deptId,COUNT(*) FROM emp  GROUP BY deptId;

     注意：分组查询中SELECT后面只能跟分组条件，或者其它列的聚合函数；

 练习：查询出每个部门的平均工资，      最大工资，最小工资

    SELECT deptId,AVG(salary),MAX(salary),MIN(salary) FROM emp  GROUP BY deptId;  

  练习：查询出男员工和女员工的人数是多少

    SELECT sex,COUNT(*) FROM emp GROUP BY sex;

2.复杂查询 —— 子查询

   示例：查询出工资比tom高的员工的所有信息

   步骤1：先查询出tom的工资是多少 —— 6000

     SELECT salary FROM emp WHERE ename='tom';

   步骤2：再查询出工资比6000高的员工信息

     SELECT * FROM emp WHERE salary>6000;

   综合：

     SELECT * FROM emp WHERE salary>(SELECT salary FROM emp WHERE ename='tom');

  练习：查询出“研发部”所有的员工信息

   步骤1：查询出“研发部”的部门编号 ——10

     SELECT did FROM dept WHERE dname='研发部';

   步骤2：查询出10号部门所有的员工信息

     SELECT * FROM emp WHERE deptId=10;

   总和：

     SELECT * FROM emp WHERE deptId=(SELECT did FROM dept WHERE dname='研发部');

3.复杂查询 —— 多表查询/跨表查询

  示例：查询出所有的员工姓名及其所在部门的名称

   SELECT ename,dname FROM emp,dept WHERE deptId=did ;   主键=外键

  笛卡尔积

 注意：为了防止出现笛卡尔积，在多表查询的时候，需要添加查询条件

 问题：无法查询出没有部门的员工，无法查询出没有员工的部门。

SQL中的多表查询

 (1)内连接 INNER JOIN ... ON —— 和之前的作用一样

  SELECT ename,dname FROM emp INNER JOIN dept ON deptId=did;

 (2)左外连接 LEFT OUTER JOIN ... ON —— 显示左侧列所有的记录

  SELECT ename,dname FROM emp LEFT OUTER JOIN dept ON deptId=did;

 (3)右外连接 RIGHT OUTER JOIN...ON  显示右侧列所有的记录 

  SELECT ename,dname FROM emp RIGHT JOIN dept ON deptId=did;

  说明：outer关键字可以省略的。

 (4)全连接 FULL JOIN

  MySQL不支持全支持

  UNION / UNION ALL

  union all  两组数据出现了相同项不合并。

  union  两组数据出现了相同项进行合并。

  (SELECT ename,dname FROM emp LEFT JOIN dept ON deptId=did)

  UNION ALL

  (SELECT ename,dname FROM emp RIGHT JOIN dept ON deptId=did);

 

  (SELECT ename,dname FROM emp LEFT JOIN dept ON deptId=did)

  UNION

  (SELECT ename,dname FROM emp RIGHT JOIN dept ON deptId=did);