MySQL - DQL 的各种使用方式（包含函数说明）

前言

主要目标

• 学会安装/卸载 MySQL
• 灵活使用 SQL 语句进行增删改查
• 掌握事务、索引、视图的使用
• 能独立根据需求编写SQL
• 了解存储引擎

概述

什么是 SQL

        SQL，一般发音为 sequel，SQL 的全称 Structured Query Language)，SQL 用来和数据库打交道， 完成和数据库的通信，SQL 是一套标准。但是每一个数据库都有自己的特性别的数据库没有, 当使用这个数据库特性相关的功能,这时 SQL 语句可能就不是标准了(90% 以上的 SQL 都是通用的)

简单理解：

• SQL：结构化查询语言

• 程序员通过编写 SQL 语句，然后 DBMS 负责执行 SQL 语句，最终来完成数据库中数据的增删改查操作。

• SQL 只是一套标准，程序员主要学习的是 SQL 语句，这个 SQL 在 MySQL 中可以实现，也可以在 Oracle 和 SQLServer 等数据库中使用。

什么是数据库

• 英文单词 DataBase，简称 DB。
• 说明：按照一定格式存储数据的一些文件的组合。

顾明思议，也就是存储数据的仓库，实际上就是一堆文件，这些文件中存储了具有特定格式的数据

什么是数据库管理系统

• 英文单词 DataBaseManagement，简称 DBMS。

• 数据库管理系统是专门用来管理数据库中数据的，数据库管理系统可以对数据库当做的数据进行增删改查

常见的数据库管理系统：MySQL、Oracle、SqlServer、DB2 等

三者之间的关系是什么？

BDMS --执行--> SQL --操作--> DB

MySQL概述

        MySQL 最初是由 “MySQL AB” 公司开发的一套关系型数据库管理系统（RDBMS-Relational Database Mangerment System）。 MySQL 不仅是最流行的开源数据库，而且是业界成长最快的数据库，每天有超过 7 万次的下载量，其应用范围从大型企业到专有的嵌入应用系统。 MySQL AB 是由两个瑞典人和一个芬兰人：David Axmark、Allan Larsson 和 Michael “Monty” Widenius 在瑞典创办的。 在 2008 年初，Sun Microsystems 收购了 MySQL AB 公司。在 2009 年，Oracle 收购了 Sun 公 司，使 MySQL 并入 Oracle 的数据库产品线。

简单理解：

• Mysql 是 mysqlAB 公司开发的，后来被 SUN公司收购了，之后 SUN 公司又被 Oracle。
• 数据库当中最基本的单元是表：table

什么是表 table？为什么用表来存储数据呢？

        表 (table) 是一种结构化的文件，可以用来存储特定类型的数据，如：学生信息，课程信息，都可以放到表中。另外表都有特定的名称，而且不能重复。表中具有几个概念：列、行、 主键。 列叫做字段(Column)，行叫做表中的记录，每一个字段都有：字段名称/字段数据类型/ 字段约束/字段长度

简单理解：

• 数据库当中是以表格的形式展示数据的，因为比较直观。

• 任何一张表的行和列：

  行（row）：被称为数据 / 记录

  列（column）：被称为字段

了解：

每一个字段都有：字段名、数据类型、约束等属性。

字段名：是一个普通的名称，见名知意即可。

数据类型：字符串、数字、日期等。

约束：主键约束、外键约束等。

MySQL 安装

https://blog.youkuaiyun.com/weixin_44552039/article/details/121649818?spm=1001.2014.3001.5501

命令行方式启动mysql

如果想要在命令行中启动 Mysql，则需要先配置对应的 mysql 环境变量，网上一大堆配置教程，这里就不多说了

使用 Win + R 输入 cmd 打开命令行页面，然后输入 “mysql -u root -p” 然后回车输入密码即可

因为我只配置了系统环境变量，所以这里需要使用管理员方式启动命令行页面，否则无法使用相关命令进行操作

MySQL 卸载

1. 双击安装包，然后点击 remove

2. 删除目录（C:\Program Files (x86) 目录下的 MySQL 目录 和 C:\ProgramData 目录下的 MySQL 目录）

   C:\ProgramData 文件夹 是隐藏文件夹，所以需要先设置查看隐藏文件夹，然后才能看到该目录

常用命令

-- 查询当前mysql中的所有数据库
show databases;

-- 查看当前mysql版本号
select version();

-- 使用指定数据库
use 数据库名;

-- 查看当前使用的是哪一个数据库
select database();

-- 创建数据库
create database 数据库名;

-- 查看当前数据库中的所有表
show tables;

-- 导入sql文件（注意：导入前需要先选择对应的数据库，并且路径中不能存在中文！！！）
source 文件目录\sql文件
-- 比如：source E:\test.sql;

-- 不看表数据，只看表结构
desc 表名;

-- 退出mysql
exit

说明：

• 如果是在命令行中使用 sql 命令进行操作，那么写完对应的操作 sql 命令后，必须加上分号 “;“ 表示结束，否则该语句不会被执行，且不会结束！
• 如果不想执行当前 sql 了，可以使用 Ctrl + C 终止 sql 执行
• SQL 语句不区分大小写

SQL 语句的分类

SQL 语句有很多，最好进行分类，这样更容易记忆。可分为：

• DQL：数据查询语言（凡是带有 select 关键字的都是查询语句，最重要的就是DQL！）

• DML：数据操作语言（凡是对表中数据进行增删改（insert / delete / update）的都是 DML）

• DDL：数据定义语言（凡是带有 create、drop、alter 的都是 DDL）

  DDL 主要操作的是表的结构，不是表中的数据

  create：新增

  drop：删除

  alter：修改

  这个增删改和DML不同，这个主要是对表结构进行操作

• TCL：事务控制语言（包含事务提交（commit）、事务回滚（rollback）

• DCL：数据控制语言（比如：授权（grant）、撤销权限（revoke）等）

查询语句的使用

创建对应的数据库表用来测试

CREATE TABLE `emp` (
  `EMPNO` int(4) NOT NULL COMMENT '员工编号',
  `ENAME` varchar(10) COLLATE utf8_bin DEFAULT NULL COMMENT '员工名称',
  `JOB` varchar(9) COLLATE utf8_bin DEFAULT NULL COMMENT '岗位',
  `MGR` int(4) DEFAULT NULL COMMENT '上级领导编号',
  `HIREDATE` date DEFAULT NULL COMMENT '入职时间',
  `SAL` double(7,2) DEFAULT NULL COMMENT '工资',
  `COMM` double(7,2) DEFAULT NULL COMMENT '津贴',
  `DEPTNO` int(2) DEFAULT NULL COMMENT '部门编号',
  PRIMARY KEY (`EMPNO`),
  KEY `emp_ename_index` (`ENAME`),
  KEY `emp_sal_index` (`SAL`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

CREATE TABLE `dept` (
  `DEPTNO` int(2) NOT NULL COMMENT '部门编号',
  `DNAME` varchar(14) COLLATE utf8_bin DEFAULT NULL COMMENT '部门名称',
  `LOC` varchar(13) COLLATE utf8_bin DEFAULT NULL COMMENT '地址',
  PRIMARY KEY (`DEPTNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

CREATE TABLE `salgrade` (
  `GRADE` int(11) DEFAULT NULL COMMENT '工资等级',
  `LOSAL` int(11) DEFAULT NULL COMMENT '最低工资',
  `HISAL` int(11) DEFAULT NULL COMMENT '最高工资'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

-- 可以使用以下命令查看创建表的语句
show create table 表名;

简单的查询

查看指定表中的所有数据

-- 方式一：使用 * 号（不推荐，因为查询时会先把 * 号转换为字段名然后在进行查询，效率较低）
select * from 表名;

-- 方式二：使用字段名（推荐,字段名直接使用逗号隔开，效率较高）
select 所有字段名 from 表名;

查询指定表中的部分数据

select 字段名 from 表名;

-- 说明：如果指定的不是某个字段，而是一个字面量/字面值，则会导致列中的数据全都输出该字面量/字面值，大家可以在数据库中测试
select 'aaa' from 表名;
select 10000 from 表名;

需要哪些数据，就写对应的字段名即可

给查询的字段取别名

-- 方式一：使用 as 关键字
select 字段名 as 别名 from 表名;

-- 方式二：省略 as 关键字
select 字段名 别名 from 表名;

-- 假设取别名的时候，别名里面有空格，会导致报错（因为 sql 语句也是先进行编译，在运行的，如果不符合sql语法，就会报错！），比如：select uname user name from user 
-- 如果一定要加空格，那么可以使用双引号或单引号包裹起来，这样就相当于把它作为一个整体，就不会导致报错，比如：
select uname 'user name' from user;
select uname "user name" from user;		-- 不推荐双引号

-- 注意：在所有的数据库中，字符串统一使用单引号括起来，单引号是标准，双引号在 oracle 数据库中用不了，但是在mysql中可以使用！

注意：只是将显示的查询结构字段显示为别名，原表字段名不会改变；因为 select 永远都不会进行修改操作（它只负责查询）

计算员工的年薪

数据表随便创一个就好了，只要有员工名称和工资

select ename, sal*12 as '年薪' from emp;

-- 错误写法
select ename, sal*12 as 年薪 from emp;

别名如果是中文，那么必须使用单引号括起来！

条件查询

相关运算符

条件查询需要用到 where 语句，where 必须放到 from 语句表的后面，支持如下运算符：

运算符	说明
=	等于
<> 或 !=	不等于
<	小于
<=	小于等于
>	大于
>=	大于等于
between … and …	两个值之间，等同于 >= and <=
is null	为 null（is not null 不为空）
and	并且
or	或者
in	包含，相当于多个 or（not in 不在这个范围中）
not	not 可以取非，主要用在 is 或 in 中
like	like 称为模糊查询，支持%或下划线匹配（% 匹配任意多个字符，下划线，一个下划线只匹配一个字符）

语法格式：select 字段 from 表名 where 条件

等于（=）

案例：查询薪资等于 800 的员工姓名和编号；

select empno, ename from emp where sal = 800;

案例：查询姓名为SMITH的员工姓名和编号；

select empno, ename from emp where ename = 'SMITH';

不等于（!= 或 <>）

案例：查询薪资不等于 800 的员工姓名和编号；

-- 方式一：<>
select empno, ename from emp where sal <> 800;

-- 方式二：!=
select empno, ename from emp where sal != 800;	-- 一般这种方式比较常见

小于（<）

案例：查询薪资小于 2000 的员工姓名和编号；

select empno, ename from emp where sal < 2000;

小于等于（<=）

案例：查询薪资小于等于 3000 的员工姓名和编号；

select empno, ename from emp where sal <= 3000;

大于（>）

案例：查询薪资大于 3000 的员工姓名和编号；

select empno, ename from emp where sal > 3000;

大于等于（>=）

案例：查询薪资大于等于 5000 的员工姓名和编号；

select empno, ename from emp where sal >= 5000;

范围查询（between）

案例：查询薪资在 2450 - 3000 之间的员工信息，包括 2450 和 3000

-- 方式一：>= & <=
select empno, ename, sal from emp where sal >= 2450 and sal <= 3000;

-- 方式二：between & and（推荐）
select empno, ename, sal from emp where sal between 2450 and 3000;

-- 错误写法
select empno, ename, sal from emp where sal between 3000 and 2450;

注意：使用 between and 的时候，必须要遵循 “左小右大”；between and 是闭区间，包括两端的值

闭区间理解：

空（is null）/非空（is not null）查询

案例：查询哪些员工的津贴为 null

select empno, ename, sal from emp where comm is null;

-- 注意：在数据库当中的 null 不能使用等号进行衡量，需要使用is null；因为数据库中的 null 代表什么都没有，它不是一个值，所以不能使用等号衡量！

案例：查询哪些员工的津贴不为 null

select empno, ename, comm from emp where comm is not null;

-- 注意：0 不是 null，null表示什么都没有，而 0 是一个存在的值，所以 is not null 可以查询到津贴为 0 的员工信息

多条件查询

and（并且）

案例：查询岗位是 MANAGER 并且工资大于 2500 的员工信息

select empno, ename, job, sal from emp where job = 'MANAGER' and sal > 2500;

or（或者）

案例：查询岗位是 MANAGER 或者是 SALESMAN 的员工信息

select empno, ename, job, sal from emp where job = 'MANAGER' or job = 'SALESMAN';

and 和 or 同时出现的话，有优先级问题吗？

案例：查询工资大于 2500 并且部门编号为 10 或 20 的员工信息

-- 错误写法（逻辑错误）
select empno, ename, sal, deptno from emp where sal > 2500 and deptno = 10 or deptno = 20;

说明：
以上语句最后得到的值是工资大于 2500 并且部门编号为 10 的员工，或者部门编号为 20 的员工
相当于条件变成了 (sal > 2500 and deptno = 10) or deptno = 20

-- 正确写法
select empno, ename, sal, deptno from emp where sal > 2500 and (deptno = 10 or deptno = 20);

说明：and 和 or 同时出现时，and 优先级较高，如果想让 or 先执行，需要加上小括号

查询包含/不包含对应值的数据

in（包含）

案例：查询岗位为 MANAGER 或者是 SALESMAN 的员工信息

-- 方式一：or（上面已经展示过用法，这里就不展示了）

-- 方式二：in（in后面跟的是具体的值，不是范围）
select empno, ename, job from emp where job in ('MANAGER ','SALESMAN ');

-- 查询工资为 800 和 5000 的员工信息
select empno, ename, job from emp where sal = 800 or sal = 5000;	-- or写法
select empno, ename, job from emp where sal in (800, 5000);			-- in写法

> 建议：or 在少量数据时使用，in 在大量数据时使用

not in（不包含）

案例：查询工资不为 800 和 5000 以及3000的员工信息

select empno, ename, job from emp where sal not in (800, 5000, 3000);	

模糊查询（like）

案例：查询员工名字包含 “O” 的员工信息

select empno, ename from emp where ename like '%O%';

案例：查询员工名字以 ”T“ 结尾的员工信息

select empno, ename, job from emp where ename like '%T';

案例：查询员工名字以 ”K“ 开头的员工信息

select empno, ename, job from emp where ename like 'K%';

案例：查询员工名字第二个字母是 ”A“ 的员工信息

select empno, ename, job from emp where ename like '_A%';

案例：查询员工名字第三个字母是 ”R“ 的员工信息

select empno, ename, job from emp where ename like '__R%';

案例：查询员工名字中有下划线的员工信息

-- 错误写法（这样会查询出所有的数据）
select empno, ename, job from emp where ename like '%_%';

-- 正确写法：使用 \ 进行转义
select empno, ename, job from emp where ename like '%\_%';

数据排序

说明

排序采用 order by 子句，order by 后面跟上排序字段，排序字段可以放多个，多个采用逗号分隔，order by 默认采用升序，如果存在 where 子句那么 order by 必须放到 where 语句的后面。

根据单个字段排序

案例：根据员工薪资进行排序

-- 默认是从小到大排序（升序）
select empno, ename, sal from emp order by sal

-- 如果想要从大到小排序，可以使用 desc（降序）
select empno, ename, sal from emp order by sal desc

-- 手动设置升序，可以使用 asc
select empno, ename, sal from emp order by sal asc

根据多个字段排序

案例：根据员工薪资进行升序排序，如果薪资出现一样的情况，那么在根据员工姓名进行升序排序

-- 说明：如果 sal 没有重复的，就不会去使用 ename 进行排序，只有在 sal 出现重复值的时候，才会去使用 ename 进行排序
select empno, ename, sal from emp order by sal, ename asc

根据字段的位置排序（了解）

案例：根据第二列进行排序

select empno, ename, sal from emp order by 2;

不建议在开发中使用，了解即可

因为列的顺序很容易发生改变，列的顺序改变之后， 2 就废了（查询的效果就不是最初想要的了）

综合案例

找出工资在 1250 ~ 3000 之间的员工信息，要求按照薪资降序排列

select empno, ename, sal from emp where sal between 1250 and 3000 order by sal desc;

注意：关键字的顺序不能改变！

select ... from ... where ... order by ...

函数的使用

数据处理函数（又被称为“单行处理函数”）

说明

• 单行处理函数的特点：一个输入对应一个输出

  就相当于是一行一行数据进行的处理，每一行都对应一个处理结果，比如下面的案例 sql（多少行进行输入，就会有多少行进行输出）

• 与单行处理函数相对的是 多行处理函数（也就是多个输入对应一个输出）

  多行处理函数相当于多行数据处理成一行数据，比如 分组函数/聚合函数/多行处理函数 中的 sql 案例

函数说明

函数	作用
lower	转换小写
upper	转换大写
substr	截取字符串（substr(被截取的字符串, 起始下标, 截取的长度) 起始下标从 1 开始
length	长度
trim	去空格
str_to_date	将字符串转换为日期
date_format	格式化日期
format	设置千分位
round	四舍五入
rand	生成随机数
Ifnull	可以将 null 转换成一个具体的值
concat	拼接字符串

lower：字符小写

案例：将所有员工名称全部修改为小写

select lower(ename) as ename from emp;

upper：字符大写

案例：将所有员工名称全部修改为大写

select upper(ename) as ename from emp;

substr：截取字符

案例：截取所有员工名字的前5个字符，起始下标从 1 开始

select substr(ename, 1, 5) as ename from emp;

案例：查询员工名字第一个字符为 A 的员工信息

-- 方式一：模糊查询
select ename from emp where ename like 'A%';		

-- 方式二：使用substr函数
select ename from emp where substr(ename, 1, 1) = 'A';		

length：获取字符长度

案例：获取指定列的数据长度

select length(ename) enameLength from emp;

trim：清除前后空格

案例：去除指定列中的数据空格（假设现在用户表中有一个用户的名称为 “TEST_name ”，现在需要清除前后空格进行展示）

select trim(ename) enameTrim from emp;
select ename from emp where ename =  trim('TEST_name  ');		

注意：trim 只能清除前后空格，不能清除数据中间的空格

str_to_date：字符串（varchar）转日期（date）

• str_to_date 函数可以把字符串 varchar 转换成日期 date 类型数据，通常使用在插入（insert）方面，因为插入的时候需要一个日期类型的数据，需要通过该函数将字符串转换成 date
• 语法：str_to_date('字符串日期', '日期格式')
• 如果提供的日期格式为 %Y-%m-%d，就不需要使用 str_to_date 函数

MySQL日期格式

年：%Y
月：%m
日：%d
时：%h
分：%i
秒：%s

案例：插入用户信息，生日值为 ‘‘11-12-2021’’（需要提前创建一张 t_user 用户表，字段：id(int), name(varchar), birth(date) ）

-- 错误写法：表中的生日类型为 date（格式为：%Y-%m-%d），而这里的值因为格式不一致，所以值的类型为 varchar，最后导致类型不一致错误
insert into t_user
values(1, '张三', '11-12-2021');

-- 如何解决? 使用 str_to_date 函数
insert into t_user
values(1, '张三', str_to_date('11-12-2021', '%d-%m-%Y'));

注意：设置的日期格式需要对应上日期字符串中的数据，所以这里不能直接写成 '%Y-%m-%d'
说明：%d 对应 11，%m 对应 12，%Y 对应 2021

暂时了解，结合后面的新增表数据来熟悉使用

date_format：日期（date）转字符串（varchar）

• 将 date 类型转换成具有一定格式的 varchar 类型
• 语法：date_format(日期类型数据, '日期格式')

案例：将用户表中的生日按照 ‘’%m/%d/%Y’’ 格式展示

select id, name, date_format(birth, '%m/%d/%Y') as births from t_user;

-- 了解：
-- 使用默认的日期格式化，自动将数据库中的 date 类型转换成 varchar 类型，并且采用的格式是 mysql 默认的日期格式：%Y-%m-%d
select id, name, birth from t_user;

暂时了解，结合后面的新增表数据来熟悉使用

format：数字格式化

案例：让员工工资按千分位展示

select format(sal, '$999,999') from emp;

round：四舍五入

-- 参数一表示要四舍五入的数据，参数二表示保留的小数位数
select round(1234.567, 1) as result from emp;		-- 1234.6，保留一位小数
select round(1234.567, -1) as result from emp;		-- 1230，保留十位
select round(1234.567, -2) as result from emp;		-- 1200，保留百位
select round(1234.567, -3) as result from emp;		-- 1000，保留千位

参数二说明：
-2 -1 0 1 2（可以把小数点看做是 0，负数往前四舍五入，正数往后四舍五入）
0 保留的是整数，无小数的四舍五入数据
大于 0 的整数表示保留 n 位小数的四舍五入数据
负数表示保留十位百位千位等...位数的四舍五入数据

rand：随机数

-- 默认是生成 0 - 1 之间的随机数，有小数点
select rand() as result from emp;	

-- 生成一百以内的正整数
select round(rand() * 100, 0) as result from emp;	

Ifnull：空处理

Ifunll 是空处理函数，专门处理空的；在所有数据库当中，只要有 null 参与的数学运算，最终结果就是 null。

注意：’ ’ != null

案例：计算每个员工的年薪

-- 假设当前 comm 列中有数据为 null，那么此时打印的数据中一定为有一个为 null
select ename, (sal + comm) * 12 as result from emp;	

-- 解决结果为 null 的问题：使用 ifnull 函数
select ename, (sal + ifnull(comm, 0)) * 12 as result from emp;

说明：
ifnull语法：ifnull(数据, 被当做哪个值)
如果数据为 null 的时候，把这个数据结构当做哪个值
比如：comm 为 null 时，将 comm 作为 0 在进行运算

在数据库中的数学运算中，只要存在 null，那么返回的结果就是 null；为了避免这个现象，需要使用到 Ifnull 函数

比如：null + 1 结果一定是 null

concat：字符拼接

案例：将用户姓名和薪资拼接并展示为一行

select concat(ename, sal) as result from emp;

case…when…then…when…then…else…end

案例：当员工的岗位是 MANAGER 的时候，工资上调 10%，岗位为 SALESMAN 时，工资上调 50%（注意：只进行展示，不修改数据库）

select ename, job, sal, (case job when 'MANAGER' then sal * 1.1 when 'SALESMAN' then sal * 1.5 else ename end) as newsal  from emp;

说明：
相当于 java 中的 if 结构
当岗位是 MANAGER 的时候工资上调 10%
当岗位是 SALESMAN 的时候工资上调 50%

case：根据某个字段
when：判断是否为对应条件
then：如果满足该条件，则进行对应的操作
> 执行完里面的操作后会跳出该结构，在表中展示操作后的数据
else：如果没有满足的条件，则会直接打印 else 中指定的字段数据
end：结束判断

小练习

案例：将所有员工名字的首字母修改为小写

select concat(lower(substr(ename, 1, 1)), substr(ename, 2, length(ename) - 1)) as ename from emp;

分组函数/聚合函数/多行处理函数

说明

• 分组函数在使用的时候必须先进行分组，然后才能使用；如果没有对数据进行分组，整张表默认为一组！

• 分组函数在使用的使用需要注意哪些？

  1、分组函数自动会忽略 null 值，不需要我们手动对 null 进行处理（切记：数据库中 null 不是一个值！！！）

  2、分组函数中 count(*) 和 count(字段) 有什么区别？

  count(*)：表示统计表中的总行数，只要有一行数据，count 就会 +1

  count(字段)：表示统计该字段下所有不为 null 的元素的总数。

  3、分组函数不能直接使用在 where 子句中（比如：select * from table where sum(sal); 这种写法是不符合语法要求的，会报错）

  4、所有的分组函数可以组合起来一起用（比如：select sum(sal), avg(sal), max(sal) from table;）

函数说明

函数	作用
count	记录数
sum	求和
avg	平均值
max	最大值
min	最下值

count：记录数

案例：计算员工数量

select count(ename) 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;

为什么分组函数不能直接使用在 where 后面？

• 因为分组函数在使用的时候必须先分组才能使用，where 执行的时候，还没有进行分组，所以where 后面不能出现分组函数。

• select sum(sal) from table; 为什么这样可以使用分组函数？

  因为 select 关键字是在 group by 关键字之后执行，所以可以使用分组函数。

  杠精：我没看到有 group by 关键字呀？

  这个是因为如果没有手动进行分组，那么默认就会把整张表分为一组，在 mysql 默认的情况下它会自动进行处理。

分组查询（重点）

• group by：按照某个字段或者某些字段进行分组

• having：having 是对分组之后的数据进行再次过滤

• 语法：select ... from ... order by ...

• 注意：分组函数一般都会和 group by 联合使用，这也是为什么它被称为分组函数的原因；并且任何一个分组函数（count sum avg max min）都是在 group by 语句执行结束之后才会执行的；当一条 sql 语句没有 group by 的话，整张表的数据会自成一组。

在实际的应用中，可能也会遇到这样的需求，需要先进行分组，然后对每一组的数据进行操作；这个时候我们就需要使用分组查询。

group by 的使用

案例：计算每个岗位的工资和

select job, sum(sal) from emp group by job;

案例：找出每个部门中的最高薪资

select deptno, max(sal) from emp group by deptno;

案例：找出每个部门，不同工作岗位的最高薪资

select deptno, job, max(sal) from emp group by deptno, job; 

having 的使用

• 如果想对分组数据再进行过滤需要使用 having 子句
• having 必须和 group by 一起使用

案例：找出每个部门最高薪资，要求显示最高薪资大于 3000 的？

-- 方式一：使用 having，但是这种方式执行效率比较低
select deptno, max(sal) from emp group by deptno having max(sal) > 1500;

-- 优化策略：where 和 having，优先选择 where，除非是 where 是在完成不了的，在选择使用 having
select deptno, max(sal) from emp where sal > 1000 group by deptno;

-- where 完成不了的例子：找出每个部门的平均薪资，要求显示平均薪资高于两千五的数据
select deptno, avg(sal) from emp group by deptno having avg(sal) > 2500;

select deptno, avg(sal) from emp where avg(sal) > 2500 group by deptno;	  -- 错误写法

说明：
因为是需要找到每个部门中平均工资高于两千五的数据，而实际编写中不能再 where 后面写分组函数进行判断，所以这个需求 where 实现不了。
如果只是判断最高薪资大于多少，则可以使用 where 进行判断，因为可以直接使用 薪资>n 来进行筛选，而平均工资则是需要先计算才能进行判断的。

重点结论

在一条 select 语句当中，如果有 group by 语句的话，select 后面只能写：参加分组的字段以及分组函数（其它的一律不能写！！）

-- 错误写法
select ename, job, sum(sal) from emp group by job;

说明：以上语句虽然可以在mysql中正常执行，但是执行后的结果会导致有问题；并且该语句不可以在 oracle 数据库中执行（会导致报错）
因为 oracle 的语法要比 mysql 的语法更加严格！！

将前面的关键字全部组合在一起，了解他们的执行顺序

select ... from ... where ... group by ... having ... order by ...

编写的时候，上面这些关键字的顺序不能颠倒！！！

执行顺序：

1. from
2. where
3. group by
4. having
5. select
6. order by

理解：从某张表中查询数据，先经过 where 条件筛选出有价值的数据，在对这些有价值的东西进行分组，分组之后可以使用 having 继续筛选，然后 select 查询出来，最后排序输出。

案例：找出每个岗位的平均薪资，要求显示平均薪资大于 1500 的，除了 MANAGER 岗位之外，要求按照平均薪资降序排列

select job, avg(sal) from emp 
where job != 'MANAGER' 
group by job 
having avg(sal) > 1500 
order by avg(sal) desc;

distinct：去除重复记录

原表数据不会被修改，只是查询结果去重。

案例1：

-- 错误写法：
select ename, distinct deptno from emp;

说明：因为 distinct 只能写在所有字段的最前方

案例2：

-- 去除 deptno 字段中的重复数据
select distinct deptno from emp;

-- 多个字段联合去重
select distinct ename, deptno from emp;

这里可以理解为，如果 ename 和 deptno 有数据相同的两条数据，就进行去重
比如：ename deptno
	   A	 1
	   A	 1

案例3：统计一下工作岗位的数量

select count(distinct job) from emp;

连接查询

什么是连接查询？

• 从一张表中单独查询，称为单表查询。

• emp 表和 dept 表联合起来查询数据，从 emp 表中取员工名字，从 dept 表中取部门名字，这种跨表查询，多张表联合起来查询数据，就被称为连接查询

连接查询的分类

根据语法的年代进行分类：

1. SQL92：1992 年的时候出现的语法
2. SQL99：1999 年的时候出现的语法（现在基本都是用 99 的语法 ）

根据表连接的方式分类：

1. 内连接

   等值连接

   非等值连接

   自连接

2. 外连接

   左外连接（左连接）

   右外连接（右连接）

3. 全连接（不常用）

笛卡尔积现象

当两张表进行连接查询时，没有任何条件限制的时候，最终查询的结果条数，是两张表条数的乘积，这种现象被称为：笛卡尔积现象（笛卡尔发现的，是一种数学现象）

-- 没有任何条件限制的连接查询
select ename, dname from emp, dept;

假设部门有 4 个，员工有 10 个
这个时候执行上面的连接查询，就会导致最后的结果为每个员工对应 4 个部门（比如 A=1，A=2，A=3，A=4，B=1，B=2...以此类推）
但是这并不是我们想要看到的结果

如何避免？

连接时加条件，满足这个条件的记录被筛选出来

-- 条件：如果员工的部门编号 = 部门表的部门编号就能成功查询
select ename, dname from emp, dept where emp.deptno = dept.deptno;

思考：假设部门有 4 个，员工有 10 个，最终查询的结果条数为 10 条，但是匹配的过程中，匹配的次数减少了吗？

解：还是40次（两张表的乘积），但是进行了四选一，匹配的次数并没有减少。

如何提升效率（如何减少匹配次数）？

1. 给字段取别名
2. 查询的字段用 “别名.字段名” 的方式（这种语法是 SQL92 语法）

通过笛卡尔积现象得出，表的连接次数越多效率越低，所以在编写 sql 的时要尽量避免表的连接次数。

案例：

-- 92语法：
select e.ename, d.dname from emp e, dept d 
where e.deptno = d.deptno;

-- 99语法：这里先不演示，大家可参考后面的内连接和外连接使用方式

内连接

• 特点：把完全能够匹配上这个条件的数据查询出来

等值连接

案例：查询每个员工所在的部门名称，显示员工名和部门名

-- SQL92 语法：
select e.ename, d.dname from emp e, dept d
where e.deptno = d.deptno;

说明：
语法：select ... from a, b where a和b的连接条件 and 筛选条件

SQL 92语法的缺点：结构不清晰，表的连接条件和后期筛选的条件都放到了 where 后面

-- SQL99 语法
select e.ename, d.dname from emp e
inner join dept d
on e.deptno = d.deptno;

select e.ename, d.dname from emp e
join dept d
on e.deptno = d.deptno;

说明：
SQL 99语法：select ... from a inner join b on a和b的连接条件 where 筛选条件
> inner 关键字可以省略，但是建议加上，因为可以快速看出这是一个内连接

SQL 99语法的优点：表连接的条件是独立的，连接之后，如果还需要进一步筛选，在往后继续添加 where 即可

非等值连接

条件不是一个等量（=）关系，称为非等值连接

案例：找出每个员工的薪资等级，要求显示员工名、薪资、薪资等级

select e.ename, e.sal, s.grade from emp e
inner join salgrade s
on e.sal >= s.losal and e.sal <= s.hisal;

自连接

案例：查询员工的上级领导，要求显示员工名和员工领导

-- 技巧：一张表看成两张表
select e.ename '员工名', r.ename '领导名' from emp e
inner join emp r
on e.mgr = r.empno;

外连接

• 在外连接当中，两张表连接会产生主次关系
• 带有 right 的是右外连接，又被称为 右连接
• 带有 left 的是左外连接，又被称为左连接
• 任何一个左连接都有左连接的写法，任何一个右连接都有左连接的写法

右外连接

rigth：表示将 join 关键字右边的这张表看成主表，主要是为了将这张表的数据全部查询出来，捎带着关联查询左边的表。

-- 假设 dept 表中有 4 个部门，而 emp 中的员工都在前三个部门中，此时外查询就会把没有员工的部门也查出来（内连接不会查出来）
select e.ename, d.dname from emp e
right outer join dept d		-- outer 可以省略，写上的话可读性比较强
on e.deptno = d.deptno;

select e.ename, d.dname from emp e
right join dept d
on e.deptno = d.deptno;

说明：内连接只会查询出所有满足条件的数据，而外连接会把主表中满足或不满足的数据查询出来

左外链接

left：表示将 join 关键字左边的这张表看成主表，主要是为了将这张表的数据全部查询出来，捎带着关联查询左边的表。

-- 假设 dept 表中有 4 个部门，而 emp 中的员工都在前三个部门中，此时外查询就会把没有员工的部门也查出来（内连接不会查出来）
select e.ename, d.dname from dept d
left outer join emp e     -- outer 可以省略，写上的话可读性比较强
on e.deptno = d.deptno;

select e.ename, d.dname from dept d
left join emp e 
on e.deptno = d.deptno;

思考：外连接的查询结果条数一定是 >= 内连接的查询结果条数吗？

解：是的

案例：查询每个员工的上级领导，要求显示所有员工的名字和领导名

-- 假设现在有 10 条员工信息，其中一个是最高领导（最高领导上面没有领导）

-- 内连接（等值连接）
select e.ename, r.ename from emp e
inner join emp r
on e.mgr = r.empno;

说明：内连接查询后，不会查询出最高领导，因为最高领导没有对应的领导了（最后查出9条数据）

-- 外连接（左外链接）
select e.ename, r.ename from emp e
left join emp r
on e.mgr = r.empno;

说明：外连接查询后，会查询出最高领导，最高领导对应的领导为 null，也就是空，表示没有领导（最后查出10条数据）

三张表或n张表的连接

语法：select ... from a inner join b on a和b的连接条件 right join c on a和c的连接条件（如果要连接更多表，在后面继续加即可）

一条 SQL 中内连接和外连接可以混合，都可以出现

案例：找出每个员工的部门名称，以及工资等级，要求显示员工名、薪资、部门名、薪资等级

select e.ename, e.sal, d.dname, s.grade  from emp e
inner join dept d
on e.deptno = d.deptno
inner join salgrade s
on e.sal between s.losal and s.hisal;

案例：找出每个员工的部门名称，以及工资等级，还有上级领导，要求显示员工名、薪资、部门名、薪资等级

select e.ename '员工名', e.sal, d.dname, s.grade, r.ename '领导名'  from emp e
inner join dept d
on e.deptno = d.deptno
inner join salgrade s
on e.sal between s.losal and s.hisal
left join emp r
on e.mgr = r.empno;

子查询

什么是子查询？

• select 语句中嵌套 select 语句，被嵌套的 select 语句就被称为 子查询；

• 子查询可以出现在哪些位置？

  可以出现在 select 后面、from 后面、where 后面

where 中的子查询

案例：找出比最低工资高的员工姓名和工资

select ename, sal from emp
where sal > (select min(sal) from emp);

大致思路：

第一步：找到最低工资

第二步：找出大于最低工资的员工

第三步：合并第一步和第二步

from 中的子查询

注意：from 后面的子查询，可以将子查询的查询结果当做一张临时表

案例：找出每个岗位的平均工资的薪资等级

select 
	e.*, s.grade
from 
	(select ename, job, avg(sal) as avgsal from emp group by job) e
join salgrade s
on e.avgsal between s.losal and s.hisal

注意：如果子查询中有函数列，则需要设置对应的别名，否则在外面使用的时候会导致出现问题。
比如这里的 avg(sal)，如果不取别名，那么连接条件就是使用 e.avg(sal) 进行判断了，而这样写会导致系统认为它是一个分组函数并不是查出来的具体数据，然后你使用该分组函数进行判断的时候就会导致报错，因为执行 on 的时候还没有执行 group by（一定要清楚sql的执行顺序！！！）

大致思路：

第一步：找出每个岗位的平均薪资（按照岗位分组求平均值）

第二步：把 (select * from salgrade) 当成是一张存在的表

第三步：将两个表进行连接查询，并设置对应的塞选条件

select 中的子查询（了解即可）

• 注意：对于 select 后面的子查询来说，这个子查询只能一次返回一列结果，多于一列，就会报错

案例：找出每个员工的部门名称，要求显示员工名和部门名

select e.ename, (select dname from dept d where e.deptno = d.deptno) from emp e

union 的使用

说明

• 作用：合并查询结果集。

• 使用时的注意事项：

  1、union 在进行结果集合并的时候，要求两个结果集的列数相同，如果两个结果集的列数不一致，则会报错。

  比如：A结果集有两列，而B结果集只有一列，那么就会导致报错

  2、MySQL 可以把不同类型的结果集拼接到一起，但是 Oracle 中不能这样（Oracle严格要求结果集合并时列与列的数据类型一致）

案例

案例：查询工作岗位是 MANAGER 和 SALESMAN 的员工

-- 方式一：使用 or
select ename, job from emp
where job = 'MANAGER' or job = 'SALESMAN';

-- 方式二：使用 in
select ename, job from emp
where job in('MANAGER', 'SALESMAN');

-- 方式三：使用 union
select ename, job from emp where job = 'MANAGER'
union
select ename, job from emp where job = 'SALESMAN';

说明：以上 sql 中，使用 unio 的效率高一些
原因：对于表连接来说，每连接一次新表，则匹配的次数满足笛卡尔积，成倍的翻...但是 union 可以减少匹配的次数，在减少匹配次数的情况下，还可以完成两个结果集的拼接。
比如：A连接B连接C，A、B、C 各有十条记录，连接查询的匹配次数为：1000 次（10*10*10）
	 A连接B一个结果：10*10  匹配一百次
	 A连接C一个结果：10*10  匹配一百次
	 使用 union 的话最后匹配的次数就是 100 + 100，也就是 200 次
	 相对于连接查询的匹配次数来说，省了 800 次匹配。

limit 的使用（重要）

说明

• limit 是将查询结果集的一部分取出来，通常使用在分页查询中。
• 分页的作用：主要是为了提高用户的体验，因为一次性全部都查出来的话，会导致用户体验差。
• 完整语法：limit startIndex, length
• 缺省语法：limit length
• startIndex 是起始下标，length 是长度，起始下标默认从 0 开始
• 注意：limit 是在 order by 后面执行的！！！！

案例

案例：根据薪资降序，取出排名在前五的员工

-- 缺省写法
select ename, sal from emp
order by sal desc
limit 5;	-- 取前五条数据

-- 完整写法：
select ename, sal from emp
order by sal desc
limit 0, 5;

案例：取出工资排名在 3 - 5 名的员工

select ename, sal from emp
order by sal desc
limit 2, 3;

说明：2 表示起始位置从下标 2 开始，也就是第三条记录
	 3 表示长度

案例：取出工资排名在 5 - 9 名的员工

select ename, sal from emp
order by sal desc
limit 4, 5;

通用分页

• 分页公式：每页显示的记录数 * （当前页数 - 1）

假设每页显示 3 条记录
第一页：limit 0, 3	[0  1  2]
第二页：limit 3, 3	[3  4  5]
第三页：limit 6, 3	[6  7  8]
第四页：limit 9, 3	[9  10  11]

公式/规律：每页显示的记录数 * （当前页数 - 1）

每页显示 pageSize 条记录
第 pageSize 页：limit pageSize * (pageNo - 1), pageSize

关于 DQL 语句的大总结

select
	...
from
	...
where
	...
group by
	...
having
	...
order by
	...
limit 
	...

执行顺序：

1. from
2. where
3. group by
4. having
5. select
6. order by
7. limit