mysql（day04）

1.复杂查询

1.1 聚合查询 / 分组查询
实例: 查询出所有员工的数量
SELECT COUNT(eid) FROM emp;

练习: 使用员工的部门编号来计算员工的数量
  SELECT COUNT(deptId) FROM emp;
  
推荐写法
  SELECT COUNT(*) FROM emp;

练习: 计算所有员工的工资总和 
  SELECT SUM(salary) FROM emp;
练习: 查询所有男员工的平均工资
  SELECT SUM(salary) / COUNT(*) FROM emp WHERE sex=1;
  SELECT AVG(salary) FROM emp WHERE sex=1;

练习：查询女员工的最高工资
  SELECT MAX(salary) FROM emp WHERE sex=0;

练习: 查询男员工的最低工资
  SELECT MIN(salary) FROM emp WHERE sex=1;

聚合函数
函数：是一个功能体，提供若干个数据，产出结果
COUNT() 总数量
SUM() 总和
AVG() 求平均
MAX() 最大
MIN() 最小
YEAR() 获取一个日期中的年分
MONTH() 获取日期中的月份

分组查询
只能查询分组条件和聚合函数
GROUP BY 条件

实例：查询出男女员工的最高工资
  SELECT **sex,MAX(salary)** FROM emp **GROUP BY sex**;
  SELECT sex,MAX(salary) FROM emp GROUP BY sex;

练习: 查询出部门年龄最大的人和最小的人
  SELECT deptId,MIN(birthday),MAX(birthday) FROM emp GROUP BY deptId;

练习: 查询出 1991年出生的员工
  SELECT * FROM emp WHERE YEAR(birthday)=1991;

练习: 查询出 3 月份出生的员工
  SELECT * FROM emp WHERE MONTH(birthday)=3;

1.2 子查询
- 把一个SQL语句的结果作为另一个SQL语句的查询条件
实例: 查询出研发部所有的员工有哪些？
1. 步骤一: 查询出研发部的部门编号是多少？ —————— 10
SELECT did FROM dept WHERE dname=“研发部”;

  2. 步骤二: 查询出研发部的员工
  SELECT * FROM emp WHERE deptId=10;

  综合: 
  SELECT * FROM emp WHERE deptId=(SELECT did FROM dept WHERE dname="研发部");

  练习: 查询出比 tom 工资高的员工有哪些？
    SELECT * FROM emp WHERE salary>(SELECT salary FROM emp WHERE ename="tom");

    1. 步骤一: 查询出 tom 的工资
      SELECT salary FROM emp WHERE ename="tom";
    2. 步骤二: 查询出 工资高于 同tom 的 员工
      SELECT * FROM emp WHERE salary > 6000;

  练习: 和tom同一年出生的员工有哪些？
    1. 步骤一: 获取 tom 的年分
      SELECT YEAR(birthday) FROM emp WHERE ename="tom";

    2. 步骤二: 查询 1990 年出生的员工
      SELECT * FROM emp WHERE YEAR(birthday)=1990;

    综合: 
    SELECT * FROM emp WHERE YEAR(birthday) =
    (SELECT YEAR(birthday) FROM emp WHERE ename="tom") AND salary > 6000;

1.3 多表查询
实例: 查询所有的员工姓名及部门名称
SELECT ename,dname FROM emp,dept; // 产生 笛卡尔积

  这个现象叫做:  笛卡尔积 现象
  解决方案: 添加查询条件
  SELECT ename,dname FROM emp,dept WHERE deptId=did;

| 问题： 无法查询出没有部门的员工的, 也无法查询出没有员工的部门 |

(1) 内连接 INNER a JOIN b ON c;
  SELECT ename, dname FROM emp INNER JOIN dept ON deptId=did;

(2) 外连接 
  2.1 左外连接 a LEFT （OUTER） JOIN b ON c;
    SELECT ename,dname FROM emp LEFT OUTER JOIN dept ON deptId=did;

    OUTER 可以略
    特点: 显示左侧所有记录
  2.2 右外连接
    - 显示右侧所有的数据  OUTER 也可以省略
    SELECT ename, dname FROM emp RIGHT OUTER JOIN dept ON deptId=did;

  2.3 全连接
    - 显示左侧和右侧表中所有的记录 —————— FULL JOIN ON 
    MySQL 不支持

  -- 关键字解决 ->
    UNION 合并相同的记录
    UNION ALL 部和并相同的记录

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


    SELECT ename * FROM emp_us
    UNION ALL
    SELECT ename * FROM emp_cn