MySql基础入门到进阶

柔情只为你懂 2022-05-12 20:08 246阅读 0赞

## 1. 基本操作 ##

mysql -u root -p

输入密码，登陆数据库

创建数据库 `study`：

CREATE DATABASE study CHARACTER SET UTF8;

删除数据库：

DROP DATABASE 数据库名;

查看所有的数据库：

SHOW DATABASES;

使用数据库 `study`：

USE study;

创建数据表 `news`：

CREATE TABLE news (
    	nid INT AUTO_INCREMENT,
    	title VARCHAR(30) NOT NULL,
    	content VARCHAR(300),
    	CONSTRAINT pk_nid PRIMARY KEY(nid)
    )ENGINE = INNODB DEFAULT CHARSET=utf8;

插入数据：

INSERT INTO news(title, content) VALUES('mysql study', 'this is test data');
    INSERT INTO news(title, content) VALUES('mysql study', 'this is test data');
    INSERT INTO news(title, content) VALUES('mysql study', 'this is test data');

主键设置了自增，所以插入数据时可以省略此字段

获得自增后的主键：

SELECT LAST_INSERT_ID();

删除数据：

DELETE FROM news WHERE nid = 2;

修改数据：

UPDATE news SET content='this is update test' WHERE nid=1;

## 2. 查询操作 ##

数据表准备：

--
    -- Table structure for table `dept`
    --
    DROP TABLE IF EXISTS `dept`;
    CREATE TABLE `dept` (
      `deptno` INT(2) NOT NULL,
      `dname` VARCHAR(14) DEFAULT NULL,
      `loc` VARCHAR(13) DEFAULT NULL,
      PRIMARY KEY  (`deptno`)
    ) ENGINE=INNODB DEFAULT CHARSET=utf8;
    --
    -- Dumping data for table `dept`
    --
    INSERT INTO `dept` VALUES (10,'ACCOUNTING','NEW YORK'),(20,'RESEARCH','DALLAS'),(30,'SALES','CHICAGO'),(40,'OPERATIONS','BOSTON'),(60,'HR','SY');
    
    --
    -- Table structure for table `emp`
    --
    DROP TABLE IF EXISTS `emp`;
    CREATE TABLE `emp` (
      `empno` INT(4) NOT NULL,
      `ename` VARCHAR(20) DEFAULT NULL,
      `job` VARCHAR(9) DEFAULT NULL,
      `mgr` INT(4) DEFAULT NULL,
      `hiredate` DATE DEFAULT NULL,
      `sal` DOUBLE(7,2) DEFAULT NULL,
      `comm` DOUBLE(7,2) DEFAULT NULL,
      `deptno` INT(2) DEFAULT NULL,
      PRIMARY KEY  (`empno`)
    ) ENGINE=INNODB DEFAULT CHARSET=utf8;
    --
    -- Dumping data for table `emp`
    --
    INSERT INTO `emp` VALUES (7369,'SMITH','CLERK',7902,'1980-12-17',800.00,NULL,20),(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600.00,300.00,30),(7521,'WARD','SALESMAN',7698,'1981-02-22',1250.00,500.00,30),(7566,'JONES','MANAGER',7839,'1981-04-02',2975.00,NULL,20),(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250.00,1400.00,30),(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850.00,NULL,30),(7782,'CLARK','MANAGER',7839,'1981-06-09',2450.00,NULL,10),(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000.00,NULL,20),(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000.00,NULL,10),(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500.00,0.00,30),(7876,'ADAMS','CLERK',7788,'1987-05-23',1100.00,NULL,20),(7900,'JAMES','CLERK',7698,'1981-12-03',950.00,NULL,30),(7902,'FORD','ANALYST',7566,'1981-12-03',3000.00,NULL,20),(7934,'MILLER','CLERK',7782,'1982-01-23',1300.00,NULL,10);
    
    --
    -- Table structure for table `salgrade`
    --
    
    DROP TABLE IF EXISTS `salgrade`;
    CREATE TABLE `salgrade` (
      `grade` INT(11) DEFAULT NULL,
      `losal` INT(11) DEFAULT NULL,
      `hisal` INT(11) DEFAULT NULL
    ) ENGINE=INNODB DEFAULT CHARSET=utf8;
    --
    -- Dumping data for table `salgrade`
    --
    INSERT INTO `salgrade` VALUES (1,700,1200),(2,1201,1400),(3,1401,2000),(4,2001,3000),(5,3001,9999);
    
    --
    -- Table structure for table `bonus`
    --
    CREATE TABLE `bonus` (
      `ename` VARCHAR(20) DEFAULT NULL,
      `job` VARCHAR(9) DEFAULT NULL,
      `sal` DOUBLE(7,2) DEFAULT NULL,
      `comm` DOUBLE(7,2) DEFAULT NULL
    ) ENGINE = INNODB default charset utf8;

### 2.1 简单查询 ###

最基本的查询，语法：

SELECT [DISTINCT] *|列[别名],列[别名],...
    FROM 表名称 [别名];

*  语句执行顺序：1. `FROM` 2. `SELECT`;
 *  `DISTINCT` :去掉重复行数据；
 *  `*` ：查询所有列，如果不想查询所有列，可以写上具体列名称；
 *  在拿到一个未知的数据库是,千万不要查询全部,不然...
 *  别名：与列名称以空格分开，将替代原列名称显示在结果的表头；
 *  `SELECT` 子句中的“列”可以进行数学计算 + - \* / %。

例一：

select empno 员工编号, ename 员工姓名, job 岗位, mgr 领导, hiredate 入职日期, sal*12 年薪, comm 奖金, deptno 部门编号 from emp;

练习

要求：查询所有雇员编号，雇员姓名， 每月总收入，所在部门。

select empno, ename, sal + ifnull(comm,0) from emp;

*  `NULL` 值和任何数据进行计算，结果都是 `NULL`；
 *  `IFNULL(expr1,expr2)` 如果 `expr1` 不是 `NULL`，`IFNULL()` 返回 `expr1`，否则它返回 `expr2`。

### 2.2 分页查询 ###

数据库级别的分页查询，语法：

查询语句 LIMIT 开始行,长度;

*  开始行索引从 0 开始。
 *  `LIMIT` 子句是整个查询的最后一句.

### 2.3 限定查询 ###

针对查询结果进行条件过滤，语法：

SELECT [DISTINCT] *|列[别名],列[别名],...
    FROM 表名称 [别名]
    [WHERE 条件];

*  语句执行顺序：1. `FROM` 2. `WHERE` 3. `SELECT`  
    WHERE子句中的条件可以是多条，该子句可以以下操作符：
    
     *  关系运算符：`>` `>=` `<` `<=` `<>` `!=`；
     *  范围运算符：`BETWEEN ... AND ...`；
        
         *  `字段 BETWEEN 最小值 AND 最大值`；
         *  注意：范围中包含最大值和最小值；
     *  逻辑运算符：`AND`（与）、 `OR`（或）、 `NOT`（非）；
     *  空判断运算符：`IS NULL`、`IS NOT NULL`；
        
         *  不能用逻辑运算符来进行空判断，比如“comm != null”，正确的是 “comm IS NOT NULL”；
         *  注意，对数据库来说，0 和 null 是不一样的概念；
     *  基数范围：`IN`、`NOT IN`；
        
         *  表示在或者不在某几个可选数值范围之中；
         *  `字段 IN/NOT IN (数值, 数值,...)`
         *  注意：在使用 NOT IN 时，如果基数中包含了 NULL ，则无论有没有满足要求的数据，都不会被显示，这是一种保护机制；
     *  模糊查询：`LIKE`
        
         *  `字段 LIKE '匹配的关键字'`；
         *  `_`：表示匹配任意 **一个** 字符
         *  `%`：表示匹配任意 0个，1个，或多个关键字；
         *  如果不使用任何通配符，则表示精确匹配。

相关练习

1.  要求：查询出所有销售人员（SALESMAN）中工资高于 1200 的雇员信息。

SELECT * FROM emp WHERE job = 'salesman' AND sal > 1200;

1.  要求：查询出工资在 1200 ~ 3000 之间的雇员信息。

SELECT * FROM emp WHERE sal BETWEEN 1200 AND 3000;

1.  要求：查询出所有在 1981 年雇佣的雇员信息。

SELECT * FROM emp WHERE hiredate BETWEEN '1981-01-01' AND '1981-12-31';

1.  要求：查询所有领取佣金的雇员，即 comm 不为 null。

SELECT * FROM emp WHERE comm IS NOT NULL;

1.  要求：查询出雇员编号为 7369,7566,7839,8899 的雇员。

SELECT * FROM emp WHERE empno IN (7369,7566,7839,8899);

1.  要求：查询出姓名中第二个字母是'A'的雇员。

SELECT * FROM emp WHERE ename LIKE '_A%';

### 2.4 查询排序 ###

对查询的结果进行排序，语法：

SELECT [DISTINCT] *|列[别名],列[别名],...
    FROM 表名称 [别名]
    [WHERE 条件]
    [ORDER BY 字段[ASC|DESC],字段[ASC|DESC],...];

*  语句执行顺序：1. `FROM` 2. `WHERE` 3. `SELECT` 4. `ORDER BY`；
 *  因为`ORDER BY`子句是整个查询的倒数第二句，所以可以使用在 `SELECT` 子句中定义的别名；
 *  `ASC` 表示升序，是默认方式
 *  `DESC` 表示降序。

相关练习

1.  要求：查询出所有雇员的信息，结果按照工资由高到低排序，如果工资相同，则按照入职日期从早到晚排序。

SELECT * FROM emp ORDER BY sal DESC, hiredate;

1.  要求：查询出所有雇员的信息，按照年薪排序。

SELECT * FROM emp ORDER BY sal*12 ;

### 2.5 思考题 ###

1.  要求：查询部门 30 中的所有员工。

select * from emp where deptno = 30;

1.  要求：查询所有办事员（CLERK）的姓名，编号，部门编号。

select ename, empno,job, deptno from emp where job = 'CLERK';

1.  要求：查询出佣金高于工资 60 % 的雇员。

select * from emp where comm > sal*0.6

1.  要求：查询出部门 10 中所有的经理 和 部门 20 中所有的办事员。

select * from emp where (deptno = 10 and job = 'manager') or (deptno = 20 and job = 'CLERK');

1.  要求：查询出部门 10 中所有的经理 、 部门 20 中所有的办事员 、 既不是经理又不是办事员，但其工资大于等于 2000 的雇员信息。

select * from emp where (deptno = 10 and job = 'manager') or (deptno = 20 and job = 'CLERK') or (job != 'manager' and job != 'clerk' and sal > 2000);

1.  要求：查询出收入组成中有佣金的员工的不同工作。

select distinct job from emp where comm is not null;

1.  要求：查询出收入组成中无佣金 或者佣金收入低于 100 的雇员信息。

select * from emp where comm is null or comm < 100;

1.  要求：查询姓名中没有 “R” 的员工姓名。

select ename from emp where ename not like '%R%';

1.  要求：查询姓名中有 “A” 的雇员信息，结果按照工资由高到低排序，如果工资相同，则按照入职早晚排序，如果入职日期相同，则按照岗位排序。

select * from emp where ename like '%A%' order by sal desc,hiredate,job;

### 2.6 多表查询 ###

具有关联字段的表，以关联字段关系判断进行的查询，其中内连接查询语法：

SELECT [DISTINCT] *|列[别名],列[别名],...
    FROM 数据表1 [别名], 数据表2 [别名],...
    WHERE 数据表1.关联字段 = 数据表2.关联字段...
    [ORDER BY 字段[ASC|DESC],字段[ASC|DESC],...];
    或者
    SELECT [DISTINCT] *|列[别名],列[别名],...
    FROM 数据表1 [别名] [INNER] JOIN 数据表2 [别名] ON 关联条件
    [ORDER BY 字段[ASC|DESC],字段[ASC|DESC],...];

*  如果指定关联表，但是并不在 `WHERE` 子句中指定关联字段，则查询的结果将会是两个数据表的"笛卡儿积"，其实指定了关联字段，这个"笛卡儿积"依然存在，只是被过滤掉了。所以，在数据量比较大的情况下，多表关联查询的效率是比较差的。

相关练习

1.  要求：查询每个雇员的编号，姓名，职位,工资，部门名称，部门位置。

SELECT empno, ename, job, sal, dname, loc FROM emp, dept WHERE emp.deptno = dept.deptno; 
    
    -- 或者 
    
    SELECT empno, ename, job, sal, dname, loc FROM emp INNER JOIN dept ON dept.deptno=emp.deptno;

1.  要求：查询每个雇员的编号，姓名，职位，工资，入职日期，工资等级。

SELECT empno, ename, job, sal, hiredate, grade FROM emp, salgrade WHERE emp.sal BETWEEN salgrade.losal AND salgrade.hisal; 
    
    -- 或者
    
     SELECT empno, ename, job, sal, hiredate, grade FROM emp INNER JOIN salgrade ON emp.sal BETWEEN salgrade.losal AND salgrade.hisal;

多表查询的连接方式

多表查询的连接方式有两种：

*  内连接：默认为等值连接、上面的案例就是内连接；

在使用内连接进行多表查询的时候，如果其中一条数据，在关联表中没有符合条件的对应值，这时本条数据就不会被显示。

案例:查询每个雇员的编号,姓名,职位,领导姓名.  
使用内连接:

SELECT e.empno, e.ename, e.job, m.ename FROM emp e, emp m WHERE e.mgr = m.empno; 
    
    -- 或者
    
    SELECT e.empno, e.ename, e.job, m.ename FROM emp e inner join emp m on e.mgr = m.empno;

可以看到,编号 `7839` 的雇员信息没有出现在结果中,因为他没有领导(老大).

*  外连接：分为 左外连接、右外连接、全连接。  
    外连接语法：

注意：全外连接的 `FULL`，MySQL 暂不支持，需要使用 查询结果连接符 `UNION`，`UNION` 操作会将两个 `SELECT` 语句的查询结果连接起来，并且会去掉重复数据。

用外连接改写上面的案例：

SELECT e.empno, e.ename, e.job, m.ename FROM emp e left outer join emp m on e.mgr = m.empno;

为了更清楚的说明问题，新建一张 `myemp` 的表，减少 `emp` 表中的字段：

CREATE TABLE myemp
    ( `empno` INT(4) NOT NULL AUTO_INCREMENT, 
    `ename` VARCHAR(20) DEFAULT NULL, 
    `deptno` INT(2) DEFAULT NULL,
     CONSTRAINT pk_empno PRIMARY KEY (`empno`) 
    )ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

插入测试数据：

INSERT INTO myemp(ename, deptno) VALUES('SMITH', 10); 
    INSERT INTO myemp(ename, deptno) VALUES('ALLEN', 10); 
    INSERT INTO myemp(ename, deptno) VALUES('WARD', 10); 
    INSERT INTO myemp(ename, deptno) VALUES('JONES', 20); 
    INSERT INTO myemp(ename, deptno) VALUES('MARTIN', 20); 
    INTO myemp(ename, deptno) VALUES('BLAKE', 30); 
    INSERT INTO myemp(ename, deptno) VALUES('CLARK', 40); 
    INSERT INTO myemp(ename, deptno) VALUES('SCOTT', 50);

先观察内连接（等值连接）：

SELECT * FROM myemp m INNER JOIN dept d ON m.`deptno`=d.`deptno`;

查询结果：

mysql> SELECT * FROM myemp e INNER JOIN dept d ON e.`deptno`=d.`deptno`;
    +-------+--------+--------+--------+------------+----------+
    | empno | ename  | deptno | deptno | dname      | loc      |
    +-------+--------+--------+--------+------------+----------+
    |     1 | SMITH  |     10 |     10 | ACCOUNTING | NEW YORK |
    |     2 | ALLEN  |     10 |     10 | ACCOUNTING | NEW YORK |
    |     3 | WARD   |     10 |     10 | ACCOUNTING | NEW YORK |
    |     4 | JONES  |     20 |     20 | RESEARCH   | DALLAS   |
    |     5 | MARTIN |     20 |     20 | RESEARCH   | DALLAS   |
    |     6 | BLAKE  |     30 |     30 | SALES      | CHICAGO  |
    |     7 | CLARK  |     40 |     40 | OPERATIONS | BOSTON   |
    +-------+--------+--------+--------+------------+----------+
    7 rows in set (0.00 sec)

可以发现，结果是两个表关联字段的交集：  
![70][]

左外连接：

SELECT * FROM myemp m LEFT OUTER JOIN dept d ON m.`deptno`=d.`deptno`;

查询结果：

mysql> SELECT * FROM myemp m LEFT OUTER JOIN dept d ON m.`deptno`=d.`deptno`;
    +-------+--------+--------+--------+------------+----------+
    | empno | ename  | deptno | deptno | dname      | loc      |
    +-------+--------+--------+--------+------------+----------+
    |     1 | SMITH  |     10 |     10 | ACCOUNTING | NEW YORK |
    |     2 | ALLEN  |     10 |     10 | ACCOUNTING | NEW YORK |
    |     3 | WARD   |     10 |     10 | ACCOUNTING | NEW YORK |
    |     4 | JONES  |     20 |     20 | RESEARCH   | DALLAS   |
    |     5 | MARTIN |     20 |     20 | RESEARCH   | DALLAS   |
    |     6 | BLAKE  |     30 |     30 | SALES      | CHICAGO  |
    |     7 | CLARK  |     40 |     40 | OPERATIONS | BOSTON   |
    |     8 | SCOTT  |     50 |   NULL | NULL       | NULL     |
    +-------+--------+--------+--------+------------+----------+
    8 rows in set (0.00 sec)

可以发现，结果是左边的表是完整的，右边的表只保留交集部分：

![70 1][]  
右外连接：

SELECT * FROM myemp m RIGHT OUTER JOIN dept d ON m.`deptno`=d.`deptno`;

查询结果：

mysql> SELECT * FROM myemp m RIGHT OUTER JOIN dept d ON m.`deptno`=d.`deptno`;
    +-------+--------+--------+--------+------------+----------+
    | empno | ename  | deptno | deptno | dname      | loc      |
    +-------+--------+--------+--------+------------+----------+
    |     1 | SMITH  |     10 |     10 | ACCOUNTING | NEW YORK |
    |     2 | ALLEN  |     10 |     10 | ACCOUNTING | NEW YORK |
    |     3 | WARD   |     10 |     10 | ACCOUNTING | NEW YORK |
    |     4 | JONES  |     20 |     20 | RESEARCH   | DALLAS   |
    |     5 | MARTIN |     20 |     20 | RESEARCH   | DALLAS   |
    |     6 | BLAKE  |     30 |     30 | SALES      | CHICAGO  |
    |     7 | CLARK  |     40 |     40 | OPERATIONS | BOSTON   |
    |  NULL | NULL   |   NULL |     60 | HR         | SY       |
    +-------+--------+--------+--------+------------+----------+
    8 rows in set (0.00 sec)

可以发现，结果是右边的表是完整的，左边的表只保留交集部分：  
![70 2][]

全外连接：

SELECT * FROM myemp m LEFT OUTER JOIN dept d ON m.`deptno`=d.`deptno` 
    UNION 
    SELECT * FROM myemp m RIGHT OUTER JOIN dept d ON m.`deptno`=d.`deptno`;

查询结果：

mysql> SELECT * FROM myemp m LEFT OUTER JOIN dept d ON m.`deptno`=d.`deptno`
        -> UNION
        -> SELECT * FROM myemp m RIGHT OUTER JOIN dept d ON m.`deptno`=d.`deptno`;
    +-------+--------+--------+--------+------------+----------+
    | empno | ename  | deptno | deptno | dname      | loc      |
    +-------+--------+--------+--------+------------+----------+
    |     1 | SMITH  |     10 |     10 | ACCOUNTING | NEW YORK |
    |     2 | ALLEN  |     10 |     10 | ACCOUNTING | NEW YORK |
    |     3 | WARD   |     10 |     10 | ACCOUNTING | NEW YORK |
    |     4 | JONES  |     20 |     20 | RESEARCH   | DALLAS   |
    |     5 | MARTIN |     20 |     20 | RESEARCH   | DALLAS   |
    |     6 | BLAKE  |     30 |     30 | SALES      | CHICAGO  |
    |     7 | CLARK  |     40 |     40 | OPERATIONS | BOSTON   |
    |     8 | SCOTT  |     50 |   NULL | NULL       | NULL     |
    |  NULL | NULL   |   NULL |     60 | HR         | SY       |
    +-------+--------+--------+--------+------------+----------+
    9 rows in set (0.00 sec)

可以发现，全外连接中，两个表都保证了完整性：

![70 3][]

**总结：  
如果想要保证 `JOIN` 左边的表的完整性，使用 `LEFT OUTER JOIN` 连接；  
如果想要保证 `JOIN` 右边的表的完整性，使用 `RIGHT OUTER JOIN` 连接；  
如果想要保证 `JOIN` 两边的表的完整性，使用 `UNION` 连接左外连接查询和右外连接查询；  
如果对表的完整性没有要求，或者两张表的关联字段取值相等，则使用 `INNER JOIN`。**

三个表的关联查询

案例：要求：查询雇员编号，雇员姓名，雇员职位，所在部门名称，工资等级。

SELECT e.`empno`, e.`ename`, e.`job`, d.`dname`, s.grade
    FROM (emp e LEFT OUTER JOIN dept d ON e.`deptno` = d.`deptno`) 
    	LEFT OUTER JOIN salgrade s ON e.`sal` BETWEEN s.`losal` AND s.hisal;

### 2.7 分组统计查询 ###

常见的统计函数

*  `COUNT()` ： 计数，统计记录数

SELECT COUNT(*) FROM emp;

*  `SUM()` ： 求和

SELECT SUM(sal) 总工资 FROM emp;

*  `AVG()` ： 求平均值

SELECT AVG(sal) 平均工资 FROM emp;

*  `MAX()` ： 求最大值

SELECT MAX(sal) 最高工资 FROM emp;

*  `MIN()` ： 求最小值

SELECT MIN(sal) 最高工资 FROM emp;

注意，`COUNT(*)`、`COUNT(字段)`、`COUNT(DISTINCT 字段)`的区别？

*  `COUNT(*)`：统计除表中实际的数据量；
 *  `COUNT(字段)`：不会统计 `NULL` 值字段；
 *  `COUNT(DISTINCT 字段)`：消除重复数据后的统计结果。

分组统计

需要注意的是，有相同特征的事物才可以进行分组，对于数据库来说，有重复数据才可以进行分组。  
分组统计查询的语法如下：

SELECT [DISTINCT] *|列[别名],列[别名],... | 统计函数
    FROM 数据表 [别名], 数据表 [别名],...
    [WHERE 条件]
    [GROUP BY 分组字段,分组字段,...]
    [HAVING 分组后过滤条件]
    [ORDER BY 字段 [ASC|DESC],字段 [ASC|DESC],...]
    [LIMIT 开始行, 显示行数]

*  语句执行顺序：1. `FROM` 2. `WHERE` 3. `GROUP BY` 4. `HAVING` 5. `SELECT` 6.`ORDER BY` 7. `LIMIT`
 *  统计查询在Oracle数据库中的限制：
    
     *  统计函数单独使用时（没有`GROUP BY` 子句），查询语句中只能够出现统计函数，不能够出现其他字段；
     *  使用分组统计查询时（有`GROUP BY` 子句），`SELECT` 子句中只能够出现统计函数和分组字段，其他任何字段都不允许出现；

相关练习

1.  要求：查询出每个部门的名称，部门人数，平均工资。

SELECT d.`dname`, COUNT(e.`empno`) 部门人数, AVG(e.`sal`) 平均工资
    FROM emp e RIGHT OUTER JOIN dept d ON e.`deptno` = d.`deptno`
    GROUP BY d.`dname`;

1.  要求：查询出每个部门的编号，名称，位置，部门人数，平均工资。

SELECT d.`deptno`, d.`dname`, d.`loc`, COUNT(e.`empno`) 部门人数, AVG(e.`sal`) 平均工资
    FROM emp e RIGHT OUTER JOIN dept d ON e.`deptno` = d.`deptno`
    GROUP BY d.`deptno`, d.`dname`, d.`loc`;

1.  要求：按照职位分组，统计每个职位的平均工资，要求显示的是平均工资高于2000的职位信息。

SELECT job, AVG(sal)
    FROM emp
    GROUP BY job
    HAVING AVG(sal) > 2000;

注意 `WHERE` 和 `HAVING` 的区别：  
`WHERE` 子句在分组之前执行，所以数据先经过 `WHERE` 子句筛选后才进行的分组，且 `WHERE` 子句不能使用统计函数。  
`HAVING` 子句必须结合 `GROUP BY` 子句一起出现，是分组后的过滤，可以使用统计函数。  
4. 要求：统计公司每个工资等级的人数，平均工资。

SELECT s.`grade`, COUNT(e.`empno`) 人数, AVG(e.`sal`) 平均工资
    FROM emp e INNER JOIN salgrade s ON e.`sal` BETWEEN s.`losal` AND s.`hisal`
    GROUP BY s.`grade`;

### 2.8 子查询 ###

子查询是复杂查询中最为重要的。所谓子查询，就是在一条查询语句中嵌套若干条查询。子查询可以改善关联查询的性能。

子查询可以嵌套在 `SELECT`、`FROM`、`WHERE`、`HAVING` 子句中，嵌套的语句用 `()` 包裹起来。

根据子查询语句返回的结果，其应该出现的位置及用法如下：

*  子查询返回的结果是 **单行单列**，也就是说，返回的结果就是一个数据，那么它通常用于条件判断，所以经常出现在 `WHERE`、`HAVING` 子句中，这是直接使用它做条件判断即可；
 *  子查询返回的结果是 **多行单列**，也就是说，返回的结果是一组数据，那么它通常出现在 WHERE 子句中，需要配合如下符号使用：
    
     *  `IN` ： 在指定的范围内，可以配合 `NOT` 使用，表示不在范围内。需要注意的是， `NOT IN` 的范围中不能包含 `NULL` 值；
     *  `ANY` ： 指“任何”的意思，配合 `>`、`=`、`<` 使用；
        
         *  `=ANY` : 功能和 `IN` 一样
        
            SELECT * FROM emp
             WHERE sal = ANY(SELECT sal FROM emp WHERE job = 'manager')
        
         *  `>ANY` : 比子查询结果中的最小值大
        
            SELECT * FROM emp
             WHERE sal >ANY(SELECT sal FROM emp WHERE job = 'manager')
        
         *  `<ANY` : 比子查询结果中的最大值小
        
            SELECT * FROM emp
             WHERE sal >ANY(SELECT sal FROM emp WHERE job = 'manager')
     *  `ALL` ： 指“所有”的意思，同样配合 `>`、`=`、`<` 使用；
        
         *  `>ALL` : 比子查询结果中的最大值大
         *  `<ALL` : 比子查询结果中的最小值小
 *  子查询的结果返回多行多列，即，返回的结果是一个数据表，那么它通常出现在 `FROM` 子句中，作为一个虚拟表使用，最好配合别名使用。

相关练习

1.  要求：查询最早入职的雇员信息。

SELECT * 
    FROM emp 
    WHERE hiredate = (
    	SELECT MIN(hiredate) 
    	FROM emp);

1.  要求：查询与 Scott 从事同一工作，且工资相同的雇员信息。

SELECT * 
    FROM emp
    WHERE (job, sal) = (
    	SELECT job, sal 
    	FROM emp 
    	WHERE ename = 'scott') AND ename <> 'scott';

1.  要求：查询出平均工资高于公司平均工资的职位名称，职位人数，平均工资。

SELECT job, COUNT(empno), AVG(sal)
    FROM emp
    GROUP BY job
    HAVING AVG(sal) > (SELECT AVG(sal) FROM emp);

### 2.9 复杂查询综合练习 ###

1.  要求：列出至少有一个员工的部门信息。

SELECT d.`deptno`, d.`dname`, d.`loc`, COUNT(e.`empno`)
    FROM dept d LEFT OUTER JOIN emp e ON d.`deptno` = e.`deptno`
    GROUP BY d.`deptno`
    HAVING COUNT(e.`empno`) > 0;

1.  要求：查询出入职日期早于直接上级的所有员工的编号，姓名，部门名称，上级姓名。

SELECT e.`empno`, e.`ename`,e.`hiredate`, d.`dname`, m.`ename`, m.`hiredate`
    FROM (emp e LEFT OUTER JOIN dept d ON e.`deptno` = d.`deptno`)
    	LEFT OUTER JOIN emp m ON e.`mgr` = m.`empno`
    WHERE e.`hiredate` < m.`hiredate`;

1.  要求：查询出工资比 SMITH 高的所有员工信息。

SELECT * 
    FROM emp
    WHERE sal > (SELECT sal FROM emp WHERE ename = 'smith');

1.  要求：查询出所有办事员（CLERK）的姓名及其所在部门名称，部门人数。

SELECT e.ename, temp.tname, temp.count
    FROM emp e LEFT OUTER JOIN (SELECT d.`deptno` tno, d.`dname` tname, COUNT(e.`empno`) COUNT
    	     FROM dept d LEFT OUTER JOIN emp e ON d.`deptno` = e.`deptno`
    	     GROUP BY d.`deptno`) temp ON e.`deptno` = temp.tno
    WHERE e.`job` = 'CLERK';

1.  要求：查询出工资大于 1500 的各种工作，以及从事此工作的雇员人数。

SELECT job, COUNT(empno)
    FROM emp
    GROUP BY job
    HAVING MIN(sal) > 1500;

1.  要求：查询出在 SALES 部门工作的员工姓名，工资。

SELECT e.ename, e.sal
    FROM emp e LEFT OUTER JOIN dept d ON e.`deptno` = d.`deptno`
    WHERE d.`dname` = 'SALES';

1.  要求：查询出工资高于公司平均工资的所有雇员姓名，所在部门，上级领导，工资等级，与具备此工资等级的雇员人数。  
    分析步骤，首先查询出工资高于公司平均工资的所有雇员姓名，所在部门，上级领导，工资等级

SELECT e.`ename`, d.`dname`, m.`ename`, s.`grade`
    FROM (emp e LEFT OUTER JOIN dept d ON e.`deptno`=d.`deptno`) 
    	INNER JOIN salgrade s ON e.`sal` BETWEEN s.`losal` AND s.`hisal`
    		LEFT OUTER JOIN emp m ON e.`mgr` = m.`empno`
    WHERE e.`sal` > (SELECT AVG(sal) FROM emp);

然后查询出每个工资等级的人数：

SELECT s.`grade`, COUNT(e.`empno`)
    FROM emp e INNER JOIN salgrade s ON e.`sal` BETWEEN s.`losal` AND s.`hisal`
    GROUP BY s.`grade`;

最后在第一个查询中再关联第二个查询的结果：

SELECT e.`ename`, d.`dname`, m.`ename`, s.`grade`, temp.count
    FROM (emp e LEFT OUTER JOIN dept d ON e.`deptno`=d.`deptno`) 
    	INNER JOIN salgrade s ON e.`sal` BETWEEN s.`losal` AND s.`hisal`
    		LEFT OUTER JOIN emp m ON e.`mgr` = m.`empno`
    			INNER JOIN (SELECT s.`grade` tgrade, COUNT(e.`empno`) COUNT
    				    FROM emp e INNER JOIN salgrade s ON e.`sal` BETWEEN s.`losal` AND s.`hisal`
    			            GROUP BY s.`grade`) temp ON s.`grade` = temp.tgrade
    WHERE e.`sal` > (SELECT AVG(sal) FROM emp);

1.  要求：查询出各种工作的最低工资及从事此工作的雇员姓名。

SELECT temp.min, e.`ename`
    FROM emp e INNER JOIN (SELECT job, MIN(sal) MIN
    		       FROM emp
    		       GROUP BY job) temp
    WHERE e.`job` = temp.job AND e.`sal` = temp.min;

1.  要求：查询出各个部门办事员（CLERK）的最低工资。

SELECT d.`dname`, MIN(e.`sal`)
    FROM emp e LEFT OUTER JOIN dept d ON e.`deptno`=d.`deptno`
    WHERE e.`job` = 'clerk'
    GROUP BY d.`deptno`;

1.  要求：查询出部门名称中有 s 的部门的雇员工资合计，及部门人数。

SELECT d.`dname`,SUM(e.`sal`), COUNT(e.`empno`)
    FROM dept d LEFT OUTER JOIN emp e ON d.`deptno`=e.`deptno`
    WHERE d.`dname` LIKE '%s%'
    GROUP BY d.`dname`;

[70]: /images/20220513/dcf99b1426a24ed7aff10aa0ef2ac161.png
[70 1]: /images/20220513/e999f260d5e14dc58b923fe3f6334af2.png
[70 2]: /images/20220513/4d55c80583064c849156ef9dd16ae4bb.png
[70 3]: /images/20220513/243c51ee5ffe480f91738d0660804485.png