MySQL Learn

本文是mysql学习笔记.

MySql服务器介绍

MySQL软件说白了，就是一段带有main方法的Java类，用来定位和操作表文件。

表文件，数据库和数据服务器

表文件
- 表文件后缀名为.frm
- 表文件以行（记录）为单位存储数据
- 表文件由一个标题行+若干数据行组成
数据库：存储表文件的目录
数据库服务器：专门操作表文件

数据库服务器分类

关系型数据库服务器

尽可能为用户提供详尽的信息描述
运行时往往需要从大量的表文件提取数据,因此运行速度相对较慢
MySql是关系数据库服务器

非关系型数据库服务器

尽可能快速为用户提供关键数据
使用key-value形式进行数据存储
Redis是非关系数据库服务器

关系数据库服务器分类

甲骨文的：Oracle和MySql

微软的：SqlServer

MySql基础使用

配置

配置文件在\your\path\to\MySQL\MySQL Server 5.5\my.ini下。

启动关闭：Windows安装的MySql可以在services.msc中控制开启关闭。

SQL命令和分类

SQL == Struct Query Language，大多命令都是查询数据。不同关系型数据库服务器使用的SQL命令语法95%+都是一样的。 SQL不区分大小写，数据也没有大小写。

SQL命令分类如下：

DDL命令 (Database Defined Language)：管理当前数据库和表文件
DML命令 (Data Modify Language)：数据维护命令，用于修改表文件的数据，主要就是insert, delete, update
DQL命令 (Data Query Language)：数据查询命令，查询表文件中的数据

SQL语句有很多,最好进行分门别类,这样更容易记忆。分为:

DQL:
- 数据查询语言(凡是带有select关键字的都是查询语句)
- select …
DML:
- 数据操作语言(凡是对表当中的数据进行增删改的都是DML)，这个主要是操作表中的数据data
- insert delete update
- insert 增
- delete 删
- update 改
DDL:
- 数据定义语言
- 凡是带有create、drop、alter的都是DDL。
- DDL主要操作的是表的结构。不是表中的数据。
- create:新建,等同于增
- drop:删除
- alter:修改
- 这个增删改和DML不同,这个主要是对表结构进行操作。
TCL:
- 事务控制语言
- 事务提交: commit
- 事务回滚: rollback
DCL：
- 数据控制语言
- 授权grant，撤权revoke

DDL命令

通知MySQL服务器管理数据库

show databases; ：查看所有数据库名称；
create database YourDBName; ：新建数据库
drop database YourDBName; ：删除数据库

通知Mysql管理表文件

use TargetDBName; ：通知MySQL对指定数据库进行操作；
show tables; ：查看所有表文件名称
create table YourTableName(字段名数据类型);

例如：

1
2
3
4


create table student_tb (
    sid     int,            -- 学号
    sname   varchar(10)     -- 名字，字符串类型用varchar，10表示最多存10个字符
)

show create table YourTableName; ：查看当前表文件的字段结构信息
drop table YourTableName; ：删掉一张表

通知MySQLfwq对表文件重的字段进行维护

alter table 表名 add 新字段名数据类型; ：为表格添加字段
alter table 表名 drop 要删的字段名; ：删除一个字段
alter table 表名 change 旧字段名新约束; ：更改字段的约束（例如类型，规范等）
alter table 表名 modify 旧字段名新字段名新约束; ：改字段的名字和约束（例如类型，规范等）

DML命令

insert: 对指定表文件插入数据行

格式：insert into 表名 (字段名1, 字段名2, ...) values (值1, 值2, ...);；
简化版格式：insert into 表名 values (值1, 值2, ...);，使用场景是为所有字段赋值（值数量等于字段数量）

批处理插入：一次性添加多条数据；
格式：insert into 表名 values (值1, 值2, ...)(值1, 值2, ...)(值1, 值2, ...);每组值数量等于字段数量

表文件备份

一般先备份表，操作，没问题后再覆盖！
格式：create table 新表 select * from 旧表，备份旧表，表结构和内容完全一样

数据行备份

有时候只需要备份数据行而非整个表
格式：insert into 新表 select * from 旧表，将旧表中的数据行复制到新表，要求新表的字段类型、字段排列顺序与旧表结构一致（但是字段名可以不一样）。

删除命令

删除表所有数据行
格式：delete from 表名，删掉表文件所有数据行

有条件删除
格式：delect from 表名 where 条件，将符合条件的数据行删掉

举例：对于这样一张表格：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13


+------+--------+------+--------+
| sid  | sname  | age  | gender |
+------+--------+------+--------+
|   10 | MIKE   |   23 | NULL   |
|   20 | SMITH  | NULL | W      |
|   20 | SMITH  |   19 | M      |
|   40 | JACK   | NULL | W      |
|   50 | god    |   12 | W      |
|   60 | evil   |   30 | M      |
|   70 | Comee  |   22 | W      |
|   80 | Wdcd   |   24 | W      |
|   90 | Ablach |   17 | M      |
+------+--------+------+--------+

执行delete from student_tb where sid>=70;将删除学号大于等于70的数据行。

更新命令

修改所有数据行
格式：update 表名 set 字段一=新值, 字段二=新值, ...;，将表所有数据行的指定字段变为指定值

修改条件数据行：将满足条件的数据行数据更新
格式：update 表名 set 字段一=新值, 字段二=新值 where 条件

DQL命令

TIPS：样例数据库可以通过执行创建文件得到，以下都是使用这个数据库进行操作。

单表查询：7个命令

临时表的概念：除了from命令，其它6个命令都是使用上一个命令生成的临时表进行操作
临时表的生存周期：被下一条命令使用后立即销毁
只有group by 命令可以生成多个临时表，，另外5个命令执行都生成一个查询表。
只有having命令不产生新的查询表。

查询语句的书写顺序：select 字段名, 函数, 子查询 from 表名；其中条件查询增加一个where命令：select 字段名, 函数, 子查询 from 表名 where 条件，示例SELECT ename, job FROM emp WHERE sal>=3000;
下面对各个元素进行介绍：

from, select, where 三个命令

from命令：相对于读取流，把表文件加载到内存中形成临时表，供后续使用；执行优先级：一个查询语句中永远是from最先执行。from tbName name2可为表tbName取别名name2
select命令：取出指定字段所有数据，生成新的临时表
where命令：遍历临时表，定位满足条件的数据行，并读取生成新的临时表；执行优先级：from > where > select，where使用的是from生成的临时表

条件表达式

表达式可以是：关系表达式，逻辑表达式，MySQL提供的特殊运算符。如下

等于关系：=；例如ename='smith'，deptno=20；
不等于关系：!=；
逻辑运算符：与and、或or；例如sal<3000 or deptno=10；
特殊 between … and：查询区间，例如sal=<3000 and sal>=1000等效于sal between 1000 and 3000；
特殊 in：查询字段值在某集合中，例如job='salesman' or job='clerk'等效于job in ('salesman','clerk')；
特殊 not … in：查询字段值不在某集合中，例如job!='salesman' and job!='clerk'等效于job not in ('salesman','clerk')；
特殊 is null：查询字段值为NULL（未定即未赋值）；不能写成comm = null，因为mysql中null不允许参与任何运算，要写成comm is null；
特殊 is not null：查询字段值不为NULL（未定即未赋值）；
特殊 like：模糊查询，例如ename like 's%'查询姓名以s开头的数据行，百分号是通配符；%s和%s%同理。下面介绍一些通配符
- %：任意长度的字符串
- _：一个任意字符

聚合函数

max(字段名)：统计当前字段下所有数据的最大值；示例select max(sal) from emp where deptno=20;；函数max(字段名)同理；
avg(字段名)：统计当前字段下所有数据的平均值
sum(字段名)：统计当前字段下所有数据求和
count(字段名)：统计当前字段下数据不为NULL的个数
count(*)：统计当前临时表总行数
group by 字段名：

group by 命令

考虑如下案例：分类汇总，查询各部门编号和部门下人数

1
2
3
4


select job,count(*) 
from emp 
where deptno=20 
group by job;

group by命令执行优先级：where > group by > select
命令书写顺序：就像上面这样select –> from –> where –> group by
工作原理：

group by首先计算分组字段中的数据种类，
然后把有相同特征的数据合并到同一个临时表（因此若有多个特征，会生成多张临时表！）,
select的行为取决于提供临时表的命令（重点 @@@@@）
1. 如果select操作的临时表由 where 或 from 提供，则肯定只有一张临时表，select会把指定字段所有数据“剪出来”生成新的数据表
2. 如果select操作的临时表由 group by 提供，则一般是多张临时表，select将依次操作每一个临时表，操作每个临时表时只读取第一行数据行，然后把读到的数据行合并为新的临时表

上述命令的临时表生成过程见此。

group by 字段一, 字段二, ...多字段分组规则：
group by 后续有多个字段，字段顺序对查询结果没有任何影响
group by 每次执行只能对一个字段开始分组，因此group by deptno,job要执行两次group by 命令
group by 从第二次执行开始使用的前一个group by得到的临时表

练习：查询各部门编号，部门下职位名称，以及职位人数

1
2
3


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

以上代码，先对deptno分组得到三张临时表，然后将三表分别对job分组，一共得到9张临时表，所以执行结果就是9条数据行的。

having 命令

考虑如下案例：查询部门20下，各个职位人数（自动忽略低于2人的职位）：

1
2
3
4
5


select deptno,job,count(*)
from emp
where deptno=20
group by job
having count(*)>=2;

having 命令执行优先级：group by > having > select
命令书写顺序：就像上面这样 select –> from –> where –> group by –> having
having 命令不能独立出现在查询语句中，必须配合 group by使用，having 总是跟在 group by 后面
工作原理：

having执行完不生成新的临时表
having 会遍历group by生成的所有临时表，对每个表检查条件，把不符合条件的临时表整个删掉
having 的判断条件一般是一个聚合函数，如果是单行判定条件则用首行

TIPS: having 和 where 的区别

操作单位不同，where 每次操作一行数据，因此条件不能有聚合函数；having 每次操作一个临时表

功能不同，where 将满足条件的数据行读取出来生成一个全新临时表；having 将不满足条件的临时表进行销毁处理

order by 排序查询命令

考虑如下案例：查询部门20下各个职位人数，查询结果按职位人数升序（降序）排列

1
2
3
4
5


select deptno,job,count(*)
from emp
where deptno=20
group by job
order by count(*) asc; /*降序则为desc，默认asc*/

order by的执行顺序：select > order by
命令书写顺序：就像上面这样select –> from –> where –> group by –> having –> order by 排序字段名标记
工作原理：

ORDER BY 专门操作SELECT生成的临时表；
ORDER BY 根据排序字段内容，按照标记的顺序要求，对临时表数据行进行重新排序，
并将排序后数据行生成一个全新临时表

例如上述案例中，select执行后生成如下的临时表：

1
2
3
4
5
6
7
8
9


+-----------+-------------+----------+
| job       | avg(sal)    | count(*) |
+-----------+-------------+----------+
| ANALYST   | 3000.000000 |        2 |
| CLERK     | 1037.500000 |        4 |
| MANAGER   | 2758.333333 |        3 |
| PRESIDENT | 5000.000000 |        1 |
| SALESMAN  | 1400.000000 |        4 |
+-----------+-------------+----------+

order by count(*) asc;将按照count(*)字段进行升排序（因此这里的count(*)不是聚合函数而是字段名），生成如下临时表：

1
2
3
4
5
6
7
8
9


+-----------+-------------+----------+
| job       | avg(sal)    | count(*) |
+-----------+-------------+----------+
| PRESIDENT | 5000.000000 |        1 |
| ANALYST   | 3000.000000 |        2 |
| MANAGER   | 2758.333333 |        3 |
| CLERK     | 1037.500000 |        4 |
| SALESMAN  | 1400.000000 |        4 |
+-----------+-------------+----------+

此外，oder by 跟的字段名可以用字段编号来代替，例如avg(sal)是临时表第2个字段，order by 2 asc;就是对临时表的avg(sal)字段升排序。

多字段排序order by 字段一标记, 字段二标记, ...，优先级依次递减
：例如若要对所有员工信息按部门编号升序、工资降序、入职日期升序排列，则写为order by deptno asc, sal desc, hiredate asc;

limit 命令

考虑如下案例：查询平均工资最高的两个部门

1
2
3
4
5
6


select deptno,avg(sal) 
from emp 
group by 
deptno 
order by avg(sal) asc 
limit 0 2; 

执行优先级：order by > limit
工作原理：limit start,cnt把临时表的第start行开始的共cnt行数据取出来构成新的临时表；
注意：首行数据是第0行；start end 不得为负，若limit无法取出任何数据（例如编号溢出）则返回空表，不报错

小结

1 – 执行优先级from > where > group by > having > order by > limit

2 – 书写格式

1
2
3
4
5
6
7


select 字段【可多个逗号分隔】, 函数, 子查询 
from 表 
where 行定位条件 【关系, 逻辑, 特殊运算; 不能用聚合函数】
group by 字段【可多个逗号分隔】
having 表条件
order by 排序字段 标记【可多组逗号分隔】
limit start cnt;

3 – 复杂的查询一定要按照执行顺序来写，才不会混乱

多表查询：连接查询合并 / 联合查询合并

隶属关系

隶属关系的两端，拥有方与从属方，在MySQL中被分为三类：

一对一，例如一个人一个身份证
一对多，例如一个老师多个学生
多对多，例如

一对多：一方表（拥有方） — 多方表（从属方），一方表每条数据在多方表也只有一条数据
例如，dept表作为拥有表，emp作为从属表，一个部门有多位员工

一对一：一方表（拥有方） — 多方表（从属方），只不过一方表每条数据在多方表也只有一条数据
例如，“学号-姓名”表作为拥有方，“学号-身份证号”表作为从属方，一个学生只对应一个身份证号码

多对多：实际是两个互相的一对多关系，一般要三张表来描述这个关系例如，student表、course表都是一方表，score表是student的多方表，也是course的多方表：因为一个学生可以有多门课成绩，一个课程可由多个学生考试得分

字段分类	描述
主键字段	主键字段相当于数据行的身份证，主键字段的值相当于唯一编号来唯一标识一行数据，不能为NULL或重复
非主键字段	非主键字段用来解释主键字段（附加信息）
外键字段	只存在于多方表，属于非主键字段，建立数据间隶属关系

多表查询

多表查询，就是将两张表文件的数据行合并到同一个临时表并展示的过程。

执行流程：(1) 将两个表的数据行合并到全新的临时表中；(2) 使用6个查询命令（少了from命令）对新的临时表进行操作

有两种合并方案：连接查询合并 / 联合查询合并

连接合并查询

连接查询合并方案:

要求两张表之间必须存在隶属关系
将两张表数据行沿着【水平方向】拼接，例如dept首行与emp首行拼接为10 Account NewYork 7369 smith clerk 800 10
展示给用户一个合法的隶属关系数据

格式：from 一方表 join 多方表或者from 多方表 join 一方表，两个写法效果完全一样
工作原理：

from 把两张表放入内存，即得2个临时表
join 把两个临时表水平拼接得到新的临时表
“拼接”规则：
1. 临时表字段个数 = 一方表字段个数 + 多方表字段个数
2. 临时表字段名称为原始表名.字段名，例如from dept join emp得到的临时表首尾字段为dept.deptno和emp.deptno（不要用打印表的方法查看字段名！）【注释】¹
3. 临时表数据行数 = 一方表数据行数 * 多方表数据行数
4. 拼接方式：一方表每行数据与多方表所有数据进行拼接
5. 连接合并必然产生脏数据²必须去掉

下面介绍过滤脏数据，筛选合法数据的两种方法：内连接过滤 / 外联过滤

连接合并 - 内连接过滤方案

1
2


from 一方表 join 多方表
on 合法数据判定条件

on 的行为与 where 高度相似：工作原理

遍历临时表每个数据行
把符合条件的数据行取出来构成新的临时表
与where的区别：on 目标是抓取合法数据行

合法数据行的判断条件：

若多方表存在明显的外键字段：on 当前行.来自一方表的主键值 = 当前行.来自多方表的外键值，如下

1
2
3


select *
from dept join emp 
on dept.deptno = emp.deptno;

案例：查询部门名+平均工资：

1
2
3


select dname,avg(emp.sal) 
from dept join emp on dept.deptno = emp.deptno /*整一行就是造新临时表*/
group by dept.deptno;

E.G. 查询部门20的名称及其下各职位的人数
方案一

1 2 3 4

select dept.dname,emp.job,count(*) from dept join emp on dept.deptno = emp.deptno where dept.deptno=20 group by emp.job;

方案二，把筛选dept=20的定位条件放到on也是可以的，只不过这样可读性差

1 2 3 4

select dept.dname,emp.job,count(*) from dept join emp on dept.deptno = emp.deptno and dept.deptno=20 group by emp.job;

若多方表没有外键字段，则根据实际生活的隶属关系进行判断：on 实际隶属关系

案例：查询员工姓名、工资、工资等级；
这个问题中工资等级给出了工资范围（拥有方），一个等级对应多个员工（隶属方）。也即我们要根据实际含义推出隶属关系，得到一方表和多方表。

1
2
3


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

连接合并 - 外连接过滤方案

外连接过滤方案:

将两张表划分为【需要帮助表】与【不需要帮助表】
确保【需要帮助表】所有的数据行都要进入到合法临时表
如果【需要帮助表】某个数据与【不需要帮助表】所有数据行都无法匹配成功（意思是 on 条件无法满足），此时需要将这个数据行作为一个独立的数据行填充到新的临时表,缺失的数据都是NULL值

根据以上说法，有如下结论：

外连接必须加 on 条件（不加语法报错）
若把 on 条件设为恒真例如1=1，临时表结果与直接join连接完全一样【详见测试】
加上了 on 条件，满足条件的表项当然会被取出；同时如果“需要帮助表”的某行从未与“不需要帮助表”的所有行达成 on 条件，它也会进入临时表命令格式：

1
2


from 需要帮助表 left join 不需要帮助表
on 合法定位条件

这个命令与from 不需要帮助表 right join 需要帮助表 on 合法定位条件效果相同。

案例：查询所有部门名称和下属人数（0人的部门也要展示）

1
2
3
4
5


select dept.deptno, dept.dname, count(emp.empno)  
/* 这里一定要用empno统计，因为left join 会产生empno为NULL的数据行，不能计入 */
from dept left join emp on dept.deptno = emp.deptno
group by dept.deptno
where emp.empno is not null;

联合查询合并

不要求两张临时表之间存在隶属关系
要求两张临时表字段结构必须保持一致【守段个数,字段类型,字段类型排列顺序】
将两张临时表数据行沿着垂直方向堆砌到新临时表
生成临时表字段只能来自于第一个临时表字段
union 合并出的临时表会把完全相同的数据行只保留一个；如果要禁用此功能要使用union all
联合查询的应用场景主要是 “行转列” 查询

命令格式：

1
2
3


select 字段一, 字段二, 字段三, ... from 表一
union
select 字段甲, 字段乙, 字段丙, ... from 表二

注意：若要对某字段进行排序等操作，只能使用字段一，二，三等，因为生成临时表的字段名只来自于第一个临时表字段。

子查询：依赖子查询 / 独立子查询

问题引入：查询部门20工资高于公司平均工资的员工信息
需求：如何先得到平均工资来用于定位条件呢？
解决：子查询

子查询的概念

七个查询命令执行时需要从当前临时表获得相关数据才可以正常运行，如果无法从当前临时表得到需要数据则无法运行。此时MySql服务器允许开发人员通过一个完整的查询语句，为当前查询命令提供需要的数据从而保证查询正常执行 —— 这个提供数据帮助的查询语句就是子查询。

子查询与7个查询命令的关系

from 命令功能是：从外存复制表文件到内存形成临时表，或者定位内存中的临时表。对于后者，子查询就可以为select命令提供临时表。考察如下例子：

1
2
3


select SubQueryTempTable.ename, SubQueryTempTable.job
from 
    (select empno, ename, job, sal from emp) as SubQueryTempTable;

这个案例中，(select empno, ename, job, sal from emp)是一个子查询，生成一个临时表供 from 使用；此外，由于子查询得到的临时表是匿名的, 为了 select 能够使用其字段名，要用 as SubQueryTemporaryTable 为子查询得到的临时表命名。

where 命令的定位条件若需要用统计数据（如最值、平均等），就可以用子查询得到结果。考察如下例子：

1
2
3
4
5


select * 
from emp
where 
    deptno = 20 and
    sal > (select avg(sal) from emp);

group by 后面跟的是分组字段名，因此无法使用子查询
having 可以使用子查询，为 having 的表舍选条件提供数据。

案例，查询部门平均工资高于公司平均工资的部门信息

1
2
3
4


select deptno,avg(sal)
from emp 
group by deptno
having avg(sal) > (select avg(sal) from emp);

select：可以把子查询得到的数据作为select展示的信息

案例，打印员工姓名、工资、公司平均工资

1
2


select ename, sal, (select avg(sal) from emp)
from emp;

order by：不能使用子查询【但是子查询语句可以作为字段名供order by 使用】
limit：不能使用子查询

子查询分为独立子查询和依赖子查询。

独立子查询

含义：独立子查询不需要其服务的查询命令提供帮助

独立子查询工作规则：

独立子查询返回结果相当于“常量”，不随着执行的时机和次数而改变
独立子查询会在 “其服务的查询命令” 执行前先执行
独立子查询在 “其服务的查询命令” 执行时不会被调用

例如：from emp where sal > (select avg(sal) from emp);先执行一次子查询得到平均工资；接着，where 遍历员工表并检验条件，检验过程直接使用先前子查询返回的结果，不会在遍历中执行子查询。参考案例

依赖子查询【尽量不用】

案例：查询工资高于所在部门的部门平均工资的员工信息

1
2
3


select ename,sal,(select avg(sal) from emp as tanother where tanother.deptno = emp.deptno)
from emp
where sal > (select avg(sal) from emp as t1 where t1.deptno = emp.deptno);

特点：不能在其服务的查询命令执行前执行，而且会多次执行读外存，效率极低！
连接查询和自关联查询，可以完全替代依赖子查询的功能，仍是上面的例子（重点 @@@@@）：

第一步，写出产生部门-部门平均工资临时表的语句：

1
2
3


select deptno,avg(sal) as avg_sal /*可见 as 除了表，还可以为字段起别名 */
from emp 
group by deptno;

第二步，把这个表与emp表做连接合并，并过滤；注意为临时表添加名字才符合语法规范

1
2
3
4
5
6
7


select * 
from emp join (
    select deptno as sub_deptno,avg(sal) as sub_avsl  /*起名可以玩得花一点*/
    from emp 
    group by deptno 
) as DeptAvgSal
on emp.deptno = DeptAvgSal.sub_deptno;

第三步，where 抓取员工工资高于部门平均工资的行，最后用select切出想要的字段

1
2
3
4
5
6
7
8


select deptno, emp.ename, sal, DeptAvgSal.sub_avsl, sub_avsl /*后两个字段切出来是一样的*/
from emp join (
    select deptno as sub_deptno,avg(sal) as sub_avsl 
    from emp 
    group by deptno 
) as DeptAvgSal
on emp.deptno = DeptAvgSal.sub_deptno
where emp.sal > sub_avsl; /* 如所见，如果字段没有歧义，可以直接用而不需要加表名 dot */

自关联查询

案例：查询员工及其上级的工资，有依赖子查询、自关联两种方法实现

依赖子查询法：

1
2
3
4
5
6


select 
    ename, 
    sal, 
    (select ename from emp as t1 where t1.empno=emp.mgr) as senior_name, 
    (select sal from emp as t2 where t2.empno=emp.mgr) as senior_sal
from emp;

自关联法：

1
2
3
4


select t1.ename as my_name, t1.sal my_sal, t2.ename as senior_name,  t2.sal as senior_sal
/* 注意要用 left join，因为老板 President 没有上级 ^_^ */
from emp as t1 left join emp as t2 /* 搞出两张表来用 */
on t1.mgr = t2.empno;

查询结果中解释字段的来源

解释字段：在SELECT执行之前,在临时表动态生成一个列。列中内容用于对临时表每一行数据进行解释分析

解释数据来源:

来自于聚合函数

1
2
3


select deptno, max(sal) as 'DEPT TOP SALARY', avg(sal) as 'AVERAGE SALARY'
from emp
group by deptno;

来自于独立子查询

1
2
3


select deptno, max(sal) as 'DEPT TOP SALARY', avg(sal) as 'DEPT AVERAGE SALARY', (select avg(sal) from emp) as 'COMPANY AVERAGE SALARY'
from emp
group by deptno;

开发人员手动设置

1
2
3


select deptno, max(sal) as 'DEPT TOP SALARY', 'Morning' as 'Notice'
from emp
group by deptno;

来自 case … end 语句 case … end 语句在 select 前执行，它会遍历临时表每行数据，对每一行数据生成动态的解释内容。执行完毕时， case … end 生成的解释数据构成新的一列。

格式：case … end 进行多分支值判断（类似于switch case）

1
2
3
4
5
6
7


select 
    (case
        when 当前数据行字段值区间判断 then '解释数据一' 
        when 当前数据行字段值区间判断 then '解释数据二' 
        when 当前数据行字段值区间判断 then '解释数据三' 
        else '默认解释数据'
    end) as '解释字段'

案例：按照员工薪资打印提示

1
2
3
4
5
6
7
8


select ename, sal, 
    (case 
    when sal > 4000 then 'Rich Man'
    when sal between 2400 and 3999 then 'Fine Class' 
    when sal between 1500 and 2399 then 'Modest Person'
    else 'Fresh Clerk'
    end) as COMMENT
from emp

格式：case … end 进行等值判断（类似于switch case）

1
2
3
4
5
6
7


select 
    (case
        when 值一 then '解释数据甲' 
        when 值二 then '解释数据乙' 
        when 值三 then '解释数据丙' 
        else '默认解释数据'
    end) as '解释字段'

案例：按照员工薪资打印提示

1
2
3
4
5
6
7
8


select ename, sal, 
    (case job
    when 'president' then 'TOP'
    when 'manager' then 'SENIOR' 
    when 'analyst' then 'SMART'
    else 'WORST'
    end) as COMMENT
from emp

特殊查询案例：行列转化

行转列

案例：成绩单如下

sname	phy_score	cns_score
mike	12	13
alice	34	78

需要转为如下形式

sname	course	score
mike	phy	12
mike	cns	13
alice	phy	34
alice	cns	78

这就要用到行列转化。

行转列 —— 有固定公式即联合合并方案：把成绩单的1、2字段作为临时表A，1、3字段作为临时表B，然后把AB联合合并

1
2
3
4


select sname as NAME, 'physics' as COURSE, phy_score as SCORE from scoresheet
union
select sname, 'chinese' as 'COURSE', cns_score from scoresheet
order by sname desc;

列转行

案例：成绩单如下

sname	course	score
mike	phy	12
mike	cns	13
alice	phy	34
alice	cns	78

需要转为如下形式

sname	phy_score	cns_score
mike	12	13
alice	34	78

这就用到列转行。列转行有自关联查询、分组+case-end 实现。

自关联法

1
2
3


select s1.sname as NAME, s1.score as PHY_SCORE, s2.score as CNS_SCORE
from student as s1 join student as s2
where s1.sname = s2.sname and s1.course = 'phy' and s2.course = 'cns'

case end 法分组的作用是把多行数据变为一行（压缩）

仔细分析下面的代码，从命令执行顺序入手，逐步分析临时表。提示：执行顺序和功能

from 加载表
group by 分成两张临时表
select 下有函数max，下套子查询，所以先执行子查询
case end 为两张表的每行数据按规则创建新的一列
select + max对两张临时表进行操作：
- 牢记：select 面对多张临时表时，利用聚合函数 max() 处理每一张表的首行，然后只生成一行数据
- select 取出sname姓名，以及分数（max聚合函数给出）
- 把两张临时表得到的两行数据拼接
参考文件

1
2
3
4
5


select 
    sname, 
    max ((case course when 'phy' then score else then 0 end)) as phy_score, 
    max ((case course when 'cns' then score else then 0 end)) as cns_score
from student group by sname

附录

MySQL 基础语法练习题

参考答案
 作答

一、单选题（20 题，每题 2 分，共 40 分）

1 – 以下不属于关系型数据库的是（）

A. MySQL B. Oracle C. Redis D. SQL Server

2 – SQL 命令中，ALTER TABLE属于哪类命令（）

A. DML B. DDL C. DQL D. TCL

3 – 关于WHERE和HAVING的区别，错误的是（）

A. WHERE 操作行，HAVING 操作分组临时表
B. WHERE 可以使用聚合函数，HAVING 不可以
C. WHERE 执行优先级高于 HAVING
D. HAVING 必须配合 GROUP BY 使用

4 – 模糊查询中，匹配 “姓名以 S 开头且长度为 5” 的条件是（）

A. ename like 'S%' B. ename like 'S____' C. ename like '% S' D. ename like 'S__'

5 – 以下关于 NULL 值判断的正确写法是（）

A. comm = NULL B. comm == NULL C. comm is NULL D. comm <> NULL

6 – DQL 命令执行优先级正确的是（）

A. FROM > WHERE > GROUP BY > HAVING > SELECT > ORDER BY > LIMIT
B. WHERE > FROM > GROUP BY > HAVING > SELECT > ORDER BY > LIMIT
C. FROM > GROUP BY > WHERE > HAVING > SELECT > ORDER BY > LIMIT
D. FROM > WHERE > HAVING > GROUP BY > SELECT > ORDER BY > LIMIT

7 – 关于视图插入数据报错Field doesn’t have a default value，核心原因是（）

A. 视图本身不支持插入操作 
B. 底层表主键字段无默认值且未赋值
C. 视图字段数量与底层表不一致 
D. MySQL 版本不兼容

8 – 多表连接查询中，LEFT JOIN的核心特点是（）

A. 只保留两张表匹配的数据行 
B. 保证左表所有行都保留，无匹配则补 NULL
C. 保证右表所有行都保留，无匹配则补 NULL 
D. 自动去重重复数据行

9 – 以下LIMIT用法正确的是（）

A. LIMIT 2,5 B. LIMIT 5 2 C. LIMIT -1,5 D. LIMIT 5,-2

10 – 联合查询（UNION）的前提条件是（）

A. 两张表必须有外键关联 
B. 两张表字段个数、类型、顺序一致
C. 两张表必须是同一张库中的表 
D. 必须配合 WHERE 条件使用

11 – 关于GROUP BY多字段分组，正确的是（）

A. 字段顺序会影响查询结果 
B. 每次只能对一个字段分组，多字段需多次分组 
C. 分组后 SELECT 可读取每个临时表所有行数据 
D. 不能与聚合函数配合使用

12 – 以下属于 TCL 命令的是（）

A. GRANT B. COMMIT C. ALTER D. SELECT

13 – 备份表结构和数据的正确命令是（）

A. INSERT INTO 新表 SELECT * FROM 旧表 
B. CREATE TABLE 新表 SELECT * FROM 旧表
C. ALTER TABLE 新表 COPY * FROM 旧表 
D. BACKUP TABLE 旧表 TO 新表

14 – 自关联查询中，EMP t1 LEFT JOIN EMP t2 ON t1.mgr = t2.empno的作用是（）

A. 查询员工和其上级信息 B. 查询员工和下属信息C. 去重员工表数据 D. 统计员工薪资

15 – 以下UPDATE命令语法正确的是（）

A. UPDATE emp SET sal=5000 WHERE deptno=10;
B. UPDATE emp sal=5000 WHERE deptno=10;
C. UPDATE emp SET sal=5000 deptno=10;
D. UPDATE emp SET sal=5000; WHERE deptno=10;

16 – 关于子查询，说法错误的是（）

A. 独立子查询只执行一次，效率高 
B. 依赖子查询会多次执行，效率低
C. GROUP BY 后可以使用子查询 
D. FROM 后可以使用子查询生成临时表

17 – 列转行查询中，CASE…END配合MAX()的核心作用是（）

A. 过滤不符合条件的数据行 B. 将多行数据压缩为一行C. 对数据进行排序 D. 去重重复数据

18 – 以下DELETE命令正确的是（）

A. DELETE emp WHERE sid>=70; 
B. DELETE FROM emp WHERE sid>=70;
C. DELETE * FROM emp WHERE sid>=70; 
D. DELETE emp SET sid>=70;

19 – 关于ORDER BY，错误的是（）

A. 可以使用字段编号代替字段名 B. 多字段排序优先级依次递减 C. 执行优先级高于 SELECT D. 默认升序（ASC）

20 – 外键字段的特点是（）

A. 存在于一方表（拥有方） 
B. 属于主键字段
C. 既存在于一方表（拥有方），也存在于多方表（从属方） 
D. 可以为 NULL 且重复

二、填空题（10 题，每空 1 分，共 20 分）

考查重点：需要记忆的语法、规则、概念

SQL 不区分______，数据也没有______。
聚合函数中，统计临时表总行数的是______，统计字段非 NULL 值个数的是______。
模糊查询中，%代表______，_代表______。
数据库服务器分为关系型和非关系型，其中 Redis 属于______，MySQL 属于______。
DQL 命令中，______命令执行优先级最高，______命令执行优先级最低（限 7 大查询命令）。
表文件的后缀名是______，数据库是存储______的目录。
联合查询中，______会自动去重重复数据行，______会保留所有数据行。
行转列的核心实现方案是______，列转行的核心实现方案是______或______。
LIMIT start, cnt中，首行数据的 start 值是______，若 start 超出数据行范围，返回______。
数据隶属关系分为_____、_____、_____三类，其中多对多关系需要______张表描述。

纠错题（3 题，每题 10 分，共 30 分）

题目要求：指出每道题中 SQL 语句的错误，并写出修正后的完整语句。
使用的数据库仍由create.sql提供。

查询部门 20 中薪资高于公司平均工资的员工姓名和薪资
SELECT ename, sal FROM emp WHERE deptno=20 AND sal > AVG(sal);
统计各部门各职位的人数，要求只显示人数≥2 的分组
SELECT deptno, job, COUNT(*) FROM emp GROUP BY deptno, job WHERE COUNT(*)>=2;
插入员工数据（EMP 表字段：EMPNO, ENAME, JOB, SAL, DEPTNO）
INSERT INTO emp (ename, job, sal) VALUES ('LUCY', 'CLERK', 2000);
删除 EMP 表中薪资低于 1000 且部门编号为 30 的员工
DELETE emp WHERE sal<1000 and deptno=30;
左连接查询所有部门名称及下属人数（含 0 人部门）
SELECT dept.dname, COUNT(*) FROM dept LEFT JOIN emp ON dept.deptno=emp.deptno WHERE emp.empno IS NOT NULL GROUP BY dept.deptno;
查询员工姓名、薪资及薪资等级（SALGRADE 表字段：GRADE, LOSAL, HISAL）
SELECT emp.ename, emp.sal, salgrade.grade FROM emp JOIN salgrade ON emp.sal >= salgrade.losal and emp.sal <= salgrade.hisal;
列转行查询（成绩表：SNAME, COURSE, SCORE），将多行转为 “姓名 - 物理成绩 - 语文成绩” 格式
SELECT sname, CASE course when 'phy' then score else then 0 end as phy_score, CASE course when 'cns' then score else then 0 end as cns_score FROM score GROUP BY sname;

编程题（3 题，每题 15 分，共 45 分）

配套数据库脚本生成文件exam_db.sql

第38题

员工薪资分析业务需求：查询各部门的部门编号、部门名称、最高薪资、平均薪资，以及该部门平均薪资与公司平均薪资的差值（命名为 SAL_DIFF），要求只显示平均薪资高于 2000 的部门，结果按最高薪资降序排列。
员工上级信息查询业务需求：使用自关联查询所有员工的姓名、薪资、上级姓名、上级薪资，若员工无上级（如 KING），则上级姓名和薪资显示为 “无”，结果按员工部门编号升序、薪资降序排列。

第39题：

子需求 1（基础查询 + 条件过滤）

查询 “2026 年 1 月” 参加考试的所有学生姓名、课程名、成绩，要求：
排除成绩为 NULL（缺考）的记录；
只显示成绩≥80 分的记录；
结果按课程名升序、成绩降序排列。

子需求 2（分组 + 聚合 + Having）

统计每门课程的参考人数、平均分、最高分、最低分，要求：
参考人数按 “成绩非 NULL” 统计；
只显示平均分≥85 分的课程；
结果按平均分降序排列；
字段别名分别为：课程名、参考人数、平均分、最高分、最低分。

子需求 3（多表连接 + 外连接）

查询所有学生的姓名、所选课程数、总成绩（缺考课程不计入总成绩），要求：
即使学生未选任何课程（如新增学生），也需显示（课程数为 0，总成绩为 0）；
结果按总成绩降序排列，总成绩相同按姓名升序排列。

子需求 4（子查询 + 连接）

查询 “Mathematics” 课程中成绩高于该课程平均分的学生信息，要求：
显示学生姓名、性别、成绩、该课程平均分（别名：课程平均分）；
禁止使用依赖子查询（必须用独立子查询 + 连接实现）。

子需求 5（行列转换 + 联合查询）

列转行：将成绩表转换为 “学生姓名 - 数学成绩 - 英语成绩 - 物理成绩 - 语文成绩” 格式（缺考科目成绩显示 0）；
行转列：将上述列转行结果，转回 “学生姓名 - 课程名 - 成绩” 格式；【不熟练！需要反复记忆】
统计每个性别的学生各课程平均分（显示：性别、课程名、平均分）。

表名.字段的写法是在哪都可以用的，只不过单表查询不存在歧义是可以省略表名 ↩︎
一方表每行数据与多方表所有数据进行拼接，会捏造出例如clerk smith（7369）在部门20,30,40工作的错误数据！ ↩︎