- 1Python3多线程threading介绍（转载）_python3threading详解
- 2给大家分享一篇 ACM在线测评系统评测程序设计与python实现_acm带载测试
- 3Cocos2d-x 3.10把文本设置为空_cocos 3.10 按钮设置文本
- 4URP下Alpha从Gamma空间到Linner空间转换（一）——Alpha贴图不叠加_unity gamma 转linner
- 5Pandas.DataFrame.groupby() 数据分组(数据透视、分类汇总) 详解 含代码 含测试数据集 随Pandas版本持续更新_dataframe 分类汇总
- 6隧道技术 Frp 多层内网穿透_frp隧道
- 7三种常用的UI库的安装使用：iview，element与vant_iview element-ui
- 8【Redis】深入理解 Redis 常用数据类型源码及底层实现(6.详解Set和ZSet数据结构)
- 9CNN卷积神经网络学习笔记（特征提取）_cnn提取三维数据特征
- 10YOLOv5/v7/v8 的改进点合集导航页_yolo改进
MySQL基础篇2——SQL之SELECT使用篇_sql select
赞
踩
此笔记来自b站尚硅谷课程，仅做复习使用。
一、SQL语言的规则与规范
1、SQL大小写规范
SQL 可以写在一行或者多行。为了提高可读性，各子句分行写，必要时使用缩进；
每条命令以 ; 或 \g 或 \G 结束；
关键字不能被缩写也不能分行；
关于标点符号：
必须保证所有的()、单引号、双引号是成对结束的；
必须使用英文状态下的半角输入方式；
字符串型和日期时间类型的数据可以使用单引号（’ '）表示；
列的别名，尽量使用双引号（" "），而且不建议省略as。
MySQL 在 Windows 环境下是大小写不敏感的
MySQL 在 Linux 环境下是大小写敏感的
数据库名、表名、表的别名、变量名是严格区分大小写的
关键字、函数名、列名(或字段名)、列的别名(字段的别名) 是忽略大小写的。
推荐采用统一的书写规范：
数据库名、表名、表别名、字段名、字段别名等都小写
SQL 关键字、函数名、绑定变量等都大写
2、注释
单行注释：#注释文字 (MySQL特有的方式)
单行注释：-- 注释文字 (–后面必须包含一个空格。)
多行注释：/* 注释文字 */
3、命名规则
数据库、表名不得超过30个字符，变量名限制为29个；
必须只能包含 A–Z, a–z, 0–9, _共63个字符；
数据库名、表名、字段名等对象名中间不要包含空格；
同一个MySQL软件中，数据库不能同名；同一个库中，表不能重名；同一个表中，字段不能重名；
必须保证你的字段没有和保留字、数据库系统或常用方法冲突。如果坚持使用，请在SQL语句中使用`（着重号）引起来；
保持字段名和类型的一致性，在命名字段并为其指定数据类型的时候一定要保证一致性。假如数据类型在一个表里是整数，那在另一个表里可就别变成字符型了。
4、SQL分类
SQL语言在功能上主要分为如下3大类：
DDL（Data Definition Languages、数据定义语言），这些语句定义了不同的数据库、表、视图、索引等数据库对象，还可以用来创建、删除、修改数据库和数据表的结构。
主要的语句关键字包括 CREATE 、 DROP 、 ALTER 等。
DML（Data Manipulation Language、数据操作语言），用于添加、删除、更新和查询数据库记录，并检查数据完整性。
主要的语句关键字包括 INSERT 、 DELETE 、 UPDATE 、 SELECT 等。
DCL（Data Control Language、数据控制语言），用于定义数据库、表、字段、用户的访问权限和安全级别。
主要的语句关键字包括 GRANT 、 REVOKE 、 COMMIT 、 ROLLBACK 、 SAVEPOINT 等。
5、数据导入指令
在命令行客户端登录mysql，使用source指令导入
mysql> source d:\mysqldb.sql
基于具体的图形化界面的工具导入数据
二、基本的SELECT语句
SELECT…
SELECT 1; #没有任何子句
SELECT 9/2; #没有任何子句
1、SELECT…FROM
语法：
SELECT 标识选择哪些列
FROM 标识从哪个表中选择
- 1
- 2
选择全部列：
SELECT *
FROM departments;
- 1
- 2
选择特定的列：
SELECT department_id, location_id
FROM departments;
- 1
- 2
2、列的别名
重命名一个列，便于计算
紧跟列名，也可以在列名和别名之间加入关键字AS，别名使用双引号，以便在别名中包含空格或特殊的字符并区分大小写。
AS 可以省略
SELECT last_name AS name, commission_pct comm
FROM employees;
- 1
- 2
SELECT last_name "Name", salary*12 "Annual Salary"
FROM employees;
- 1
- 2
3、去除重复行
默认情况下，查询会返回全部行，包括重复行。
SELECT department_id
FROM employees;
- 1
- 2
在SELECT语句中使用关键字DISTINCT去除重复行
SELECT DISTINCT department_id
FROM employees;
- 1
- 2
4、空值参与运算
所有运算符或列值遇到null值，运算的结果都为null。
SELECT employee_id,salary,commission_pct,12 * salary * (1 + commission_pct) "annual_sal"
FROM employees;
- 1
- 2
在 MySQL 里面， 空值不等于空字符串。一个空字符串的长度是 0，而一个空值的长度是空。而且，在 MySQL 里面，空值是占用空间的。
5、着重号
错误的：
mysql> SELECT * FROM ORDER;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'ORDER' at
line 1
- 1
- 2
- 3
- 4
正确的：
SELECT * FROM `ORDER`;
- 1
我们需要保证表中的字段、表名等没有和保留字、数据库系统或常用方法冲突。如果真的相同，请在SQL语句中使用一对``（着重号）引起来。
6、查询常数
SELECT 查询还可以对常数进行查询。对的，就是在 SELECT 查询结果中增加一列固定的常数列。这列的取值是我们指定的，而不是从数据表中动态取出的。
SQL 中的 SELECT 语法的确提供了这个功能，一般来说我们只从一个表中查询数据，通常不需要增加一个固定的常数列，但如果我们想整合不同的数据源，用常数列作为这个表的标记，就需要查询常数。
比如说，我们想对 employees 数据表中的员工姓名进行查询，同时增加一列字段 corporation ，这个字段固定值为“尚硅谷”，可以这样写：
SELECT '尚硅谷' as corporation, last_name FROM employees
- 1
7、显示表结构
使用DESCRIBE 或 DESC 命令，表示表结构。
DESCRIBE employees;
- 1
或
DESC employees;
- 1
8、过滤数据：WHERE
SELECT 字段1,字段2
FROM 表名
WHERE 过滤条件
- 1
- 2
- 3
使用WHERE 子句，将不满足条件的行过滤掉
WHERE子句紧随 FROM子句
三、4大类运算符
1、算术运算符
算术运算符主要用于数学运算，其可以连接运算符前后的两个数值或表达式，对数值或表达式进行加（+）、减（-）、乘（*）、除（/）和取模（%）运算。
加法与减法运算符
mysql> SELECT 100, 100 + 0, 100 - 0, 100 + 50, 100 + 50 -30, 100 + 35.5, 100 - 35.5
FROM dual;
- 1
- 2
一个整数类型的值对整数进行加法和减法操作，结果还是一个整数；
一个整数类型的值对浮点数进行加法和减法操作，结果是一个浮点数；
加法和减法的优先级相同，进行先加后减操作与进行先减后加操作的结果是一样的；
在Java中，+的左右两边如果有字符串，那么表示字符串的拼接。但是在MySQL中+只表示数值相加。如果遇到非数值类型，先尝试转成数值，如果转失败，就按0计算。（补充：MySQL中字符串拼接要使用字符串函数CONCAT()实现）
乘法与除法运算符
SELECT 100, 100 * 1, 100 * 1.0, 100 / 1.0, 100 / 2,
100 + 2 * 5 / 2,100 / 3, 100 DIV 0 # 分母如果为0，则结果为null
FROM DUAL;
- 1
- 2
- 3
一个数乘以整数1和除以整数1后仍得原数；
一个数乘以浮点数1和除以浮点数1后变成浮点数，数值与原数相等；
一个数除以整数后，不管是否能除尽，结果都为一个浮点数；
一个数除以另一个数，除不尽时，结果为一个浮点数，并保留到小数点后4位；
乘法和除法的优先级相同，进行先乘后除操作与先除后乘操作，得出的结果相同。
在数学运算中，0不能用作除数，在MySQL中，一个数除以0为NULL。
求模（求余）运算符
#练习：查询员工id为偶数的员工信息
SELECT employee_id,last_name,salary
FROM employees
WHERE employee_id % 2 = 0;
- 1
- 2
- 3
- 4
2、比较运算符
比较运算符用来对表达式左边的操作数和右边的操作数进行比较，比较的结果为真则返回1，比较的结果为假则返回0，其他情况则返回NULL。
比较运算符经常被用来作为SELECT查询语句的条件来使用，返回符合条件的结果记录。
等号运算符
等号运算符（=）判断等号两边的值、字符串或表达式是否相等，如果相等则返回1，不相等则返回0。
在使用等号运算符时，遵循如下规则：
如果等号两边的值、字符串或表达式都为字符串，则MySQL会按照字符串进行比较，其比较的是每个字符串中字符的ANSI编码是否相等。
如果等号两边的值都是整数，则MySQL会按照整数来比较两个值的大小。
如果等号两边的值一个是整数，另一个是字符串，则MySQL会将字符串转化为数字进行比较。
如果等号两边的值、字符串或表达式中有一个为NULL，则比较结果为NULL。
安全等于运算符
安全等于运算符（<=>）与等于运算符(=）的作用是相似的，唯一的区别是<=>可以用来对NULL进行判断。在两个操作数均为NULL时，其返回值为1，而不为NULL；当一个操作数为NULL时，其返回值为0，而不为NULL。
SELECT 1 <=> NULL, NULL <=> NULL
FROM DUAL;
- 1
- 2
不等于运算符
不等于运算符（<>和!=）用于判断两边的数字、字符串或者表达式的值是否不相等，如果不相等则返回1，相等则返回0。不等于运算符不能判断NULL值。如果两边的值有任意一个为NULL，或两边都为NULL，则结果为NULL。 SQL语句示例如下：
SELECT 1 <> 1, 1 != 2, 'a' != 'b', (3+4) <> (2+6), 'a' != NULL, NULL <> NULL;
- 1
此外，还有非符号类型的运算符：
空运算符
空运算符（IS NULL或者ISNULL）判断一个值是否为NULL，如果为NULL则返回1，否则返回0。
SQL语句示例如下：
SELECT NULL IS NULL, ISNULL(NULL), ISNULL('a'), 1 IS NULL;
- 1
#查询commission_pct等于NULL。比较如下的四种写法
SELECT employee_id,commission_pct FROM employees WHERE commission_pct IS NULL;
SELECT employee_id,commission_pct FROM employees WHERE commission_pct <=> NULL;
SELECT employee_id,commission_pct FROM employees WHERE ISNULL(commission_pct);
SELECT employee_id,commission_pct FROM employees WHERE commission_pct = NULL;
- 1
- 2
- 3
- 4
- 5
非空运算符
非空运算符（IS NOT NULL）判断一个值是否不为NULL，如果不为NULL则返回1，否则返回0。
SQL语句示例如下：
SELECT NULL IS NOT NULL, 'a' IS NOT NULL, 1 IS NOT NULL;
- 1
最小值运算符
语法格式为：LEAST(值1，值2，…，值n)。其中，“值n”表示参数列表中有n个值。在有两个或多个参数的情况下，返回最小值。
SELECT LEAST (1,0,2), LEAST('b','a','c'), LEAST(1,NULL,2);
- 1
当参数是整数或者浮点数时，LEAST将返回其中最小的值；当参数为字符串时，返回字母表中顺序最靠前的字符；当比较值列表中有NULL时，不能判断大小，返回值为NULL。
最大值运算符
语法格式为：GREATEST(值1，值2，…，值n)。其中，n表示参数列表中有n个值。当有两个或多个参数时，返回值为最大值。假如任意一个自变量为NULL，则GREATEST()的返回值为NULL。
SELECT GREATEST(1,0,2), GREATEST('b','a','c'), GREATEST(1,NULL,2);
- 1
当参数中是整数或者浮点数时，GREATEST将返回其中最大的值；当参数为字符串时，返回字母表中顺序最靠后的字符；当比较值列表中有NULL时，不能判断大小，返回值为NULL。
ETWEEN AND运算符
BETWEEN运算符使用的格式通常为SELECT xxx FROM TABLE WHERE C BETWEEN A AND B，此时，当C大于或等于A，并且C小于或等于B时，结果为1，否则结果为0。
SELECT last_name, salary
FROM employees
WHERE salary BETWEEN 2500 AND 3500;
- 1
- 2
- 3
IN运算符
IN运算符用于判断给定的值是否是IN列表中的一个值，如果是则返回1，否则返回0。如果给定的值为NULL，或者IN列表中存在NULL，则结果NULL。
#练习：查询部门为10,20,30部门的员工信息
SELECT last_name,salary,department_id
FROM employees
#WHERE department_id = 10 or department_id = 20 or department_id = 30;
WHERE department_id IN (10,20,30);
- 1
- 2
- 3
- 4
- 5
NOT IN运算符
符 NOT IN运算符用于判断给定的值是否不是IN列表中的一个值，如果不是IN列表中的一个值，则返回1，否则返回0。
#练习：查询工资不是6000,7000,8000的员工信息
SELECT last_name,salary,department_id
FROM employees
WHERE salary NOT IN (6000,7000,8000);
- 1
- 2
- 3
- 4
LIKE运算符
LIKE运算符主要用来匹配字符串，通常用于模糊匹配，如果满足条件则返回1，否则返回0。如果给定的值或者匹配条件为NULL，则返回结果为NULL。
LIKE运算符通常使用如下通配符：
“%”：匹配0个或多个字符。
“_”：只能匹配一个字符。
- 1
- 2
#练习：查询last_name中包含字符'a'且包含字符'e'的员工信息
SELECT last_name
FROM employees
WHERE last_name LIKE '%a%' AND last_name LIKE '%e%';
- 1
- 2
- 3
- 4
#练习：查询第3个字符是'a'的员工信息
SELECT employee_id,last_name
FROM employees
WHERE last_name LIKE '__a%';
- 1
- 2
- 3
- 4
#练习：查询第3个字符是_且第4个字符是'a'的员工信息
#需要使用转义字符: \
SELECT employee_id,last_name
FROM employees
WHERE last_name LIKE '__\_a%';
- 1
- 2
- 3
- 4
- 5
ESCAPE
回避特殊符号的：使用转义符。例如：将[%]转为[
%]、[]转为[
]，然后再加上[ESCAPE‘$’]即可。
如果使用\表示转义，要省略ESCAPE。如果不是\，则要加上ESCAPE。
#ESCAPE '$'：表示以&为转义字符;
SELECT last_name
FROM employees
WHERE last_name LIKE '__$_a%' ESCAPE '$';
- 1
- 2
- 3
- 4
REGEXP运算符
REGEXP运算符用来匹配字符串，语法格式为： expr REGEXP 匹配条件 。如果expr满足匹配条件，返回1；如果不满足，则返回0。若expr或匹配条件任意一个为NULL，则结果为NULL。
REGEXP运算符在进行匹配时，常用的有下面几种通配符:
（1）‘^’匹配以该字符后面的字符开头的字符串。(^s)
（2）‘$’匹配以该字符前面的字符结尾的字符串。(t$)
（3）‘.’匹配任何一个单字符。 ('atguigu' REGEXP 'gu.gu':前后都有gu，中间随便一个字母)
（4）“[...]”匹配在方括号内的任何字符。例如，“[abc]”匹配“a”或“b”或“c”。为了命名字符的范围，使用一个‘-’。“[a-z]”匹配任何字母，而“[0-9]”匹配任何数字。('[ab]':里面包含a或者b就行)
（5）‘*’匹配零个或多个在它前面的字符。例如，“x*”匹配任何数量的‘x’字符，“[0-9]*”匹配任何数量的数字，而“*”匹配任何数量的任何字符。
- 1
- 2
- 3
- 4
- 5
3、逻辑运算符
逻辑运算符主要用来判断表达式的真假，在MySQL中，逻辑运算符的返回结果为1、0或者NULL。
MySQL中支持4种逻辑运算符如下：
逻辑非运算符
逻辑非（NOT或!）运算符表示当给定的值为0时返回1；当给定的值为非0值时返回0；当给定的值为NULL时，返回NULL。
# not
SELECT last_name,salary,department_id
FROM employees
#WHERE salary not between 6000 and 8000;
#WHERE commission_pct is not null;
WHERE NOT commission_pct <=> NULL;
- 1
- 2
- 3
- 4
- 5
- 6
逻辑与运算符
逻辑与（AND或&&）运算符是当给定的所有值均为非0值，并且都不为NULL时，返回1；当给定的一个值或者多个值为0时则返回0；否则返回NULL。
SELECT last_name,salary,department_id
FROM employees
WHERE department_id = 50 AND salary > 6000;
- 1
- 2
- 3
逻辑或运算符
逻辑或（OR或||）运算符是当给定的值都不为NULL，并且任何一个值为非0值时，则返回1，否则返回0；当一个值为NULL，并且另一个值为非0值时，返回1，否则返回NULL；当两个值都为NULL时，返回NULL。
SELECT last_name,salary,department_id
FROM employees
WHERE department_id = 10 OR department_id = 20;
- 1
- 2
- 3
OR可以和AND一起使用，但是在使用时要注意两者的优先级，由于AND的优先级高于OR，因此先对AND两边的操作数进行操作，再与OR中的操作数结合。
逻辑异或运算符
逻辑异或（XOR）运算符是当给定的值中任意一个值为NULL时，则返回NULL；如果两个非NULL的值都是0或者都不等于0时，则返回0；如果一个值为0，另一个值不为0时，则返回1。
# XOR : 追求的"异"
#要么满足department_id = 50并且不满足salary > 6000，
#要么不满足department_id = 50并且满足salary > 6000，
SELECT last_name,salary,department_id
FROM employees
WHERE department_id = 50 XOR salary > 6000;
- 1
- 2
- 3
- 4
- 5
- 6
4、位运算符
位运算符是在二进制数上进行计算的运算符。位运算符会先将操作数变成二进制数，然后进行位运算，最后将计算结果从二进制变回十进制数。
MySQL支持的位运算符如下：
按位与运算符 (同1为1)
按位与（&）运算符将给定值对应的二进制数逐位进行逻辑与运算。当给定值对应的二进制位的数值都为1时，则该位返回1，否则返回0。
按位或运算符 (有1为1)
按位或（|）运算符将给定的值对应的二进制数逐位进行逻辑或运算。当给定值对应的二进制位的数值有一个或两个为1时，则该位返回1，否则返回0。
按位异或运算符 (不同为1)
按位异或（^）运算符将给定的值对应的二进制数逐位进行逻辑异或运算。当给定值对应的二进制位的数值不同时，则该位返回1，否则返回0。
按位取反运算符
按位取反（~）运算符将给定的值的二进制数逐位进行取反操作，即将1变为0，将0变为1。
按位右移运算符
按位右移（>>）运算符将给定的值的二进制数的所有位右移指定的位数。右移指定的位数后，右边低位的数值被移出并丢弃，左边高位空出的位置用0补齐。
按位左移运算符
按位左移（<<）运算符将给定的值的二进制数的所有位左移指定的位数。左移指定的位数后，左边高位的数值被移出并丢弃，右边低位空出的位置用0补齐。
5、运算符的优先级
数字编号越大，优先级越高，优先级高的运算符先进行计算。可以看到，赋值运算符的优先级最低，使用“()”括起来的表达式的优先级最高。
四、排序与分页
1、排序：ORDER BY
排序规则：
使用 ORDER BY 子句排序
ASC（ascend）: 升序
DESC（descend）:降序
ORDER BY 子句在SELECT语句的结尾。
单列排序
#3. 强调格式：WHERE 需要声明在FROM后，ORDER BY之前。
#第二步
SELECT employee_id,salary
#第一步
FROM employees
WHERE department_id IN (50,60,70)
#第三步
ORDER BY department_id DESC;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
多列排序
#练习：显示员工信息，按照department_id的降序排列，salary的升序排列
SELECT employee_id,salary,department_id
FROM employees
ORDER BY department_id DESC,salary ASC;
- 1
- 2
- 3
- 4
可以使用不在SELECT列表中的列排序。
在对多列进行排序的时候，首先排序的第一列必须有相同的列值，才会对第二列进行排序。如果第一列数据中所有值都是唯一的，将不再对第二列进行排序。
2、分页：LIMIT
实现规则
分页原理：
所谓分页显示，就是将数据库中的结果集，一段一段显示出来需要的条件。
MySQL中使用 LIMIT 实现分页
格式：
LIMIT 位置偏移量, 行数
- 1
第一个“位置偏移量”参数指示MysQL从哪一行开始显示，是一个可选参数，如果不指定“位置偏移量”，将会从表中的第一条记录开始（第一条记录的位置偏移量是0，第二条记录的位置偏移量是1，以此类推)；第二个参数“行数”指示返回的记录条数。
MySQL 8.0中可以使用“LIMIT 3 OFFSET 4”，意思是获取从第5条记录开始后面的3条记录，和“LIMIT4,3;”返回的结果相同。
分页显式公式：（当前页数-1）*每页条数，每页条数
注意：LIMIT 子句必须放在整个SELECT语句的最后！
使用 LIMIT 的好处：
约束返回结果的数量可以减少数据表的网络传输量 ，也可以 提升查询效率 。如果我们知道返回结果只有1 条，就可以使用 LIMIT 1 ，告诉 SELECT 语句只需要返回一条记录即可。这样的好处就是 SELECT 不需要扫描完整的表，只需要检索到一条符合条件的记录即可返回。
五、多表查询
多表查询，也称为关联查询，指两个或更多个表一起完成查询操作。
前提条件：这些一起查询的表之间是有关系的（一对一、一对多），它们之间一定是有关联字段，这个关联字段可能建立了外键，也可能没有建立外键。比如：员工表和部门表，这两个表依靠“部门编号”进行关联。
1、笛卡尔积（或交叉连接）
笛卡尔乘积是一个数学运算。假设我有两个集合 X 和 Y，那么 X 和 Y 的笛卡尔积就是 X 和 Y 的所有可能组合，也就是第一个对象来自于 X，第二个对象来自于 Y 的所有可能。组合的个数即为两个集合中元素个数的乘积数X * Y。
SQL92中，笛卡尔积也称为交叉连接 ，英文是CROSS JOIN 。在 SQL99 中也是使用 CROSS JOIN表示交叉连接。它的作用就是可以把任意表进行连接，即使这两张表不相关。在MySQL中如下情况会出现笛卡尔积：
#查询员工姓名和所在部门名称
SELECT last_name,department_name FROM employees,departments;
SELECT last_name,department_name FROM employees CROSS JOIN departments;
SELECT last_name,department_name FROM employees INNER JOIN departments;
SELECT last_name,department_name FROM employees JOIN departments;
- 1
- 2
- 3
- 4
- 5
笛卡尔积的错误会在下面条件下产生：
省略多个表的连接条件（或关联条件）；
连接条件（或关联条件）无效；
所有表中的所有行互相连接。
为了避免笛卡尔积， 可以在WHERE 加入有效的连接条件。
加入连接条件后，查询语法：
#案例：查询员工的姓名及其部门名称
SELECT last_name, department_name
FROM employees, departments
WHERE employees.department_id = departments.department_id
- 1
- 2
- 3
- 4
在表中有相同列时，在列名之前加上表名前缀。
2、多表查询分类讲解
分类1：等值连接 vs 非等值连接
等值连接：
多个连接条件与 AND 操作符
#练习：查询员工的employee_id,last_name,department_name,city
SELECT e.employee_id,e.last_name,d.department_name,l.city,e.department_id,l.location_id
FROM employees e,departments d,locations l
WHERE e.department_id = d.department_id AND d.location_id = l.location_id;
- 1
- 2
- 3
- 4
区分重复的列名
多个表中有相同列时，必须在列名之前加上表名前缀;
在不同表中具有相同列名的列可以用表名加以区分。
SELECT employees.last_name, departments.department_name,employees.department_id
FROM employees, departments
WHERE employees.department_id = departments.department_id;
- 1
- 2
- 3
表的别名
使用别名可以简化查询;
列名前使用表名前缀可以提高查询效率。
SELECT e.employee_id, e.last_name, e.department_id,
d.department_id, d.location_id
FROM employees e , departments d
WHERE e.department_id = d.department_id;
- 1
- 2
- 3
- 4
需要注意的是，如果我们使用了表的别名，在查询字段中、过滤条件中就只能使用别名进行代替，不能使用原有的表名，否则就会报错。
【 强制 】对于数据库中表记录的查询和变更，只要涉及多个表，都需要在列名前加表的别名（或表名）进行限定。
说明 ：对多表进行查询记录、更新记录、删除记录时，如果对操作列没有限定表的别名（或表名），并且操作列在多个表中存在时，就会抛异常。
正例 ：select t1.name from table_first as t1 , table_second as t2 where t1.id=t2.id;
反例 ：在某业务中，由于多表关联查询语句没有加表的别名（或表名）的限制，正常运行两年后，最近在某个表中增加一个同名字段，在预发布环境做数据库变更后，线上查询语句出现出1052 异常：Column ‘name’ in field list is ambiguous。
连接多个表
总结：连接 n个表,至少需要n-1个连接条件。比如，连接三个表，至少需要两个连接条件。
非等值连接：
SELECT e.last_name,e.salary,j.grade_level
FROM employees e,job_grades j
WHERE e.salary between j.lowest_sal and j.highest_sal;
#WHERE e.`salary` >= j.`lowest_sal` AND e.`salary` <= j.`highest_sal`;
- 1
- 2
- 3
- 4
自连接 vs 非自连接
自连接
当table1和table2本质上是同一张表，只是用取别名的方式虚拟成两张表以代表不同的意义。然后两个表再进行内连接，外连接等查询。
#自连接的例子：
#练习：查询员工id,员工姓名及其管理者的id和姓名
SELECT emp.employee_id,emp.last_name,mgr.employee_id,mgr.last_name
FROM employees emp ,employees mgr
WHERE emp.manager_id = mgr.employee_id;
- 1
- 2
- 3
- 4
- 5
- 6
非自连接
内连接: 合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行
外连接: 两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的行 ，这种连接称为左（或右） 外连接。没有匹配的行时, 结果表中相应的列为空(NULL)。
如果是左外连接，则连接条件中左边的表也称为主表，右边的表称为从表。
如果是右外连接，则连接条件中右边的表也称为主表，左边的表称为从表。
3、SQL92语法
使用(+)创建连接
在 SQL92 中采用（+）代表从表所在的位置。即左或右外连接中，(+) 表示哪个是从表。
Oracle 对 SQL92 支持较好，而 MySQL 则不支持 SQL92 的外连接。
# MySQL 则不支持 SQL92 的外连接。
SELECT employee_id,department_name
FROM employees e,departments d
WHERE e.`department_id` = d.department_id(+);
- 1
- 2
- 3
- 4
而且在 SQL92 中，只有左外连接和右外连接，没有满（或全）外连接。
4、SQL99语法
使用JOIN…ON子句创建连接的语法结构：
SELECT table1.column, table2.column,table3.column
FROM table1
JOIN table2 ON table1 和 table2 的连接条件
JOIN table3 ON table2 和 table3 的连接条件
- 1
- 2
- 3
- 4
语法说明：
可以使用 ON 子句指定额外的连接条件；
这个连接条件是与其它条件分开的；
ON 子句使语句具有更高的易读性；
关键字 JOIN、INNER JOIN、CROSS JOIN 的含义是一样的，都表示内连接。
5、SQL99——7种SQL JOINS的实现
1、内连接(INNER JOIN)的实现
语法：
SELECT 字段列表
FROM A表 INNER JOIN B表
ON 关联条件
WHERE 等其他子句;
# 中图：内连接
SELECT employee_id,department_name
FROM employees e JOIN departments d
ON e.department_id = d.department_id;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
外连接(OUTER JOIN)的实现
2、左外连接(LEFT OUTER JOIN)
语法：
#实现查询结果是A
SELECT 字段列表
FROM A表 LEFT JOIN B表
ON 关联条件
WHERE 等其他子句;
# 左上图：左外连接
SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
3、右外连接(RIGHT OUTER JOIN)
语法：
SELECT 字段列表
FROM A表 RIGHT JOIN B表
ON 关联条件
WHERE 等其他子句;
# 右上图：右外连接
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
需要注意的是，LEFT JOIN 和 RIGHT JOIN 只存在于 SQL99 及以后的标准中，在 SQL92 中不存在，只能用 (+) 表示。
4、满外连接(FULL OUTER JOIN)
满外连接的结果 = 左右表匹配的数据 + 左表没有匹配到的数据 + 右表没有匹配到的数据。
SQL99是支持满外连接的。使用FULL JOIN 或 FULL OUTER JOIN来实现。
需要注意的是，MySQL不支持FULL JOIN，但是可以用 LEFT JOIN UNION RIGHT join代替。
#满外连接：mysql不支持FULL OUTER JOIN
SELECT last_name,department_name
FROM employees e FULL OUTER JOIN departments d
ON e.`department_id` = d.`department_id`;
- 1
- 2
- 3
- 4
# 左下图：满外连接 # 方式1：左上图 UNION ALL 右中图 SELECT employee_id,department_name FROM employees e LEFT JOIN departments d ON e.department_id = d.department_id UNION ALL SELECT employee_id,department_name FROM employees e RIGHT JOIN departments d ON e.department_id = d.department_id WHERE e.`department_id` IS NULL; # 方式2：左中图 UNION ALL 右上图 SELECT employee_id,department_name FROM employees e LEFT JOIN departments d ON e.department_id = d.department_id WHERE d.department_id IS NULL UNION ALL SELECT employee_id,department_name FROM employees e RIGHT JOIN departments d ON e.department_id = d.department_id;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
UNION的使用
合并查询结果 利用UNION关键字，可以给出多条SELECT语句，并将它们的结果组合成单个结果集。合并时，两个表对应的列数和数据类型必须相同，并且相互对应。各个SELECT语句之间使用UNION或UNIONALL关键字分隔。
语法格式：
SELECT column,... FROM table1
UNION [ALL]
SELECT column,... FROM table2
- 1
- 2
- 3
UNION操作符
UNION 操作符返回两个查询的结果集的并集，去除重复记录。
UNION ALL操作符——尽量多用（效率高）
UNION ALL操作符返回两个查询的结果集的并集。对于两个结果集的重复部分，不去重。
注意：执行UNION ALL语句时所需要的资源比UNION语句少。如果明确知道合并数据后的结果数据不存在重复数据，或者不需要去除重复的数据，则尽量使用UNION ALL语句，以提高数据查询的效率。
5、
# 左中图：
SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id
#中间的共同区域是department_id I不为NULL的，要实现左中图就可以只选择department_id I为NULL的
WHERE d.department_id IS NULL;
- 1
- 2
- 3
- 4
- 5
- 6
6、
# 右中图：
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id
#中间的共同区域是department_id I不为NULL的，要实现右中图就可以用右外连接后，只选择department_id 为NULL的
WHERE e.department_id IS NULL;
- 1
- 2
- 3
- 4
- 5
- 6
7、
# 右下图：左中图 UNION ALL 右中图
SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id
WHERE d.department_id IS NULL
UNION ALL
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id
WHERE e.department_id IS NULL;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
6、SQL99语法新特性
自然连接
SQL99 在 SQL92 的基础上提供了一些特殊语法，比如NATURAL JOIN用来表示自然连接。我们可以把自然连接理解为 SQL92 中的等值连接。它会帮你自动查询两张连接表中所有相同的字段，然后进行等值连接。
在SQL92标准中：
SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
ON e.department_id = d.department_id
AND e.manager_id = d.manager_id;
- 1
- 2
- 3
- 4
在 SQL99 中你可以写成：
SELECT employee_id,last_name,department_name
FROM employees e NATURAL JOIN departments d;
- 1
- 2
USING连接
当我们进行连接的时候，SQL99还支持使用 USING 指定数据表里的同名字段进行等值连接。但是只能配合JOIN一起使用。比如：
在SQL92标准中：
SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
ON e.department_id = d.department_id;
- 1
- 2
- 3
在 SQL99 中你可以写成：
SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
USING (department_id);
- 1
- 2
- 3
六、单行函数
操作数据对象；
接受参数返回一个结果；
只对一行进行变换；
每行返回一个结果；
可以嵌套；
参数可以是一列或一个值。
1、数值函数
基本函数
#1.数值函数 #基本的操作 SELECT ABS(-123),ABS(32),SIGN(-23),SIGN(43),PI(),CEIL(32.32), CEILING(-43.23),FLOOR(32.32),FLOOR(-43.23),MOD(12,5),12 MOD 5, 12 % 5 FROM DUAL; #取随机数 #RAND(x)：返回0~1的随机值，其中x的值用作种子值，相同的X值会产生相同的随机数 SELECT RAND(),RAND(),RAND(10),RAND(10),RAND(-1),RAND(-1) FROM DUAL; #四舍五入 #ROUND(x,y)：返回一个对x的值进行四舍五入后最接近X的值，并保留到小数点后面Y位 SELECT ROUND(123.556),ROUND(123.456,0),ROUND(123.456,1), ROUND(123.456,2),ROUND(123.456,-1),ROUND(153.456,-2) FROM DUAL; #截断操作 #返回数字x截断为y位小数的结果 SELECT TRUNCATE(123.456,0),TRUNCATE(123.496,1),TRUNCATE(129.45,-1) FROM DUAL; #单行函数可以嵌套 SELECT TRUNCATE(ROUND(123.456,2),0) FROM DUAL;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
角度与弧度互换函数
#角度与弧度的互换
SELECT RADIANS(30),RADIANS(45),RADIANS(60),RADIANS(90),
DEGREES(2*PI()),DEGREES(RADIANS(60))
FROM DUAL;
- 1
- 2
- 3
- 4
- 5
- 6
三角函数
#三角函数
SELECT SIN(RADIANS(30)),DEGREES(ASIN(1)),TAN(RADIANS(45)),DEGREES(ATAN(1))
FROM DUAL;
- 1
- 2
- 3
指数与对数
进制间的转换
#进制间的转换
#CONV(x,f1,f2) 返回f1进制数变成f2进制数
SELECT BIN(10),HEX(10),OCT(10),CONV(10,10,8)
FROM DUAL;
- 1
- 2
- 3
- 4
2、字符串函数
#1个中文 = 3个字节 SELECT ASCII('Abcdfsf'),CHAR_LENGTH('hello'),CHAR_LENGTH('我们'), LENGTH('hello'),LENGTH('我们') FROM DUAL; # xxx worked for yyy SELECT CONCAT(emp.last_name,' worked for ',mgr.last_name) "details" FROM employees emp JOIN employees mgr WHERE emp.manager_id = mgr.employee_id; SELECT CONCAT_WS('-','hello','world','hello','beijing') CWS FROM DUAL; #字符串的索引是从1开始的！ SELECT INSERT('helloworld',2,3,'aaaaa'),REPLACE('hello','ell','mmm') FROM DUAL; SELECT UPPER('HelLo'),LOWER('HelLo') FROM DUAL; SELECT last_name,salary FROM employees WHERE LOWER(last_name) = 'King'; SELECT LEFT('hello',2),RIGHT('hello',3),RIGHT('hello',13) FROM DUAL; # LPAD:实现右对齐效果 # RPAD:实现左对齐效果 SELECT employee_id,last_name,LPAD(salary,10,' ') FROM employees; SELECT CONCAT('---',LTRIM(' h el lo '),'***'), TRIM('o' FROM 'ooheollo') FROM DUAL; SELECT REPEAT('hello',4),LENGTH(SPACE(5)),STRCMP('abc','abe') FROM DUAL; SELECT SUBSTR('hello',2,2),LOCATE('l','hello') FROM DUAL; SELECT ELT(2,'a','b','c','d'),FIELD('mm','gg','jj','mm','dd','mm'), FIND_IN_SET('mm','gg,mm,jj,dd,mm,gg') FROM DUAL; SELECT employee_id,NULLIF(LENGTH(first_name),LENGTH(last_name)) "compare" FROM employees;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
3、日期和时间函数
获取日期、时间
#3.1 获取日期、时间
SELECT CURDATE(),CURRENT_DATE(),CURTIME(),NOW(),SYSDATE(),
UTC_DATE(),UTC_TIME()
FROM DUAL;
SELECT CURDATE(),CURDATE() + 0,CURTIME() + 0,NOW() + 0
FROM DUAL;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
日期与时间戳的转换
#3.2 日期与时间戳的转换
SELECT UNIX_TIMESTAMP(),UNIX_TIMESTAMP('2022-03-17 15:34:32'),
FROM_UNIXTIME(1647502404),FROM_UNIXTIME(1647502404)
FROM DUAL;
- 1
- 2
- 3
- 4
获取月份、星期、星期数、天数等函数
#3.3 获取月份、星期、星期数、天数等函数
SELECT YEAR(CURDATE()),MONTH(CURDATE()),DAY(CURDATE()),
HOUR(CURTIME()),MINUTE(NOW()),SECOND(SYSDATE())
FROM DUAL;
SELECT MONTHNAME('2022-03-17'),DAYNAME('2022-03-17'),WEEKDAY('2022-03-17'),
QUARTER(CURDATE()),WEEK(CURDATE()),DAYOFYEAR(NOW()),
DAYOFMONTH(NOW()),DAYOFWEEK(NOW())
FROM DUAL;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
日期的操作函数
EXTRACT(type FROM date)函数中type的取值与含义：
#3.4 日期的操作函数
SELECT EXTRACT(SECOND FROM NOW()),EXTRACT(DAY FROM NOW()),
EXTRACT(HOUR_MINUTE FROM NOW()),EXTRACT(QUARTER FROM '2022-03-17')
FROM DUAL;
- 1
- 2
- 3
- 4
- 5
时间和秒钟转换的函数
#3.5 时间和秒钟转换的函数
SELECT TIME_TO_SEC(CURTIME()),
SEC_TO_TIME(56907)
FROM DUAL;
- 1
- 2
- 3
- 4
- 5
计算日期和时间的函数
上述函数中type的取值：
#3.6 计算日期和时间的函数 SELECT NOW(),DATE_ADD(NOW(),INTERVAL 1 YEAR), DATE_ADD(NOW(),INTERVAL -1 YEAR), DATE_SUB(NOW(),INTERVAL 1 YEAR) FROM DUAL; SELECT DATE_ADD(NOW(), INTERVAL 1 DAY) AS col1,DATE_ADD('2021-10-21 23:32:12',INTERVAL 1 SECOND) AS col2, ADDDATE('2021-10-21 23:32:12',INTERVAL 1 SECOND) AS col3, DATE_ADD('2021-10-21 23:32:12',INTERVAL '1_1' MINUTE_SECOND) AS col4, DATE_ADD(NOW(), INTERVAL -1 YEAR) AS col5, #可以是负数 DATE_ADD(NOW(), INTERVAL '1_1' YEAR_MONTH) AS col6 #需要单引号 FROM DUAL; SELECT ADDTIME(NOW(),20),SUBTIME(NOW(),30),SUBTIME(NOW(),'1:1:3'),DATEDIFF(NOW(),'2021-10-01'), TIMEDIFF(NOW(),'2021-10-25 22:10:10'),FROM_DAYS(366),TO_DAYS('0000-12-25'), LAST_DAY(NOW()),MAKEDATE(YEAR(NOW()),32),MAKETIME(10,21,23),PERIOD_ADD(20200101010101,10) FROM DUAL;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
日期的格式化与解析
上述非GET_FORMAT函数中fmt参数常用的格式符：
#3.7 日期的格式化与解析 # 格式化：日期 ---> 字符串 # 解析： 字符串 ----> 日期 #此时我们谈的是日期的显式格式化和解析 #之前，我们接触过隐式的格式化或解析 SELECT * FROM employees WHERE hire_date = '1993-01-13'; #格式化： SELECT DATE_FORMAT(CURDATE(),'%Y-%M-%D'), DATE_FORMAT(NOW(),'%Y-%m-%d'),TIME_FORMAT(CURTIME(),'%h:%i:%S'), DATE_FORMAT(NOW(),'%Y-%M-%D %h:%i:%S %W %w %T %r') FROM DUAL; #解析：格式化的逆过程 SELECT STR_TO_DATE('2021-October-25th 11:37:30 Monday 1','%Y-%M-%D %h:%i:%S %W %w') FROM DUAL; SELECT GET_FORMAT(DATE,'USA') FROM DUAL; SELECT DATE_FORMAT(CURDATE(),GET_FORMAT(DATE,'USA')) FROM DUAL;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
4、流程控制函数
流程处理函数可以根据不同的条件，执行不同的处理流程，可以在SQL语句中实现不同的条件选择。MySQL中的流程处理函数主要包括IF()、IFNULL()和CASE()函数。
4.流程控制函数 #4.1 IF(VALUE,VALUE1,VALUE2) SELECT last_name,salary,IF(salary >= 6000,'高工资','低工资') "details" FROM employees; SELECT last_name,commission_pct,IF(commission_pct IS NOT NULL,commission_pct,0) "details", salary * 12 * (1 + IF(commission_pct IS NOT NULL,commission_pct,0)) "annual_sal" FROM employees; #4.2 IFNULL(VALUE1,VALUE2):看做是IF(VALUE,VALUE1,VALUE2)的特殊情况 SELECT last_name,commission_pct,IFNULL(commission_pct,0) "details" FROM employees; #4.3 CASE WHEN ... THEN ...WHEN ... THEN ... ELSE ... END # 类似于java的if ... else if ... else if ... else SELECT last_name,salary,CASE WHEN salary >= 15000 THEN '白骨精' WHEN salary >= 10000 THEN '潜力股' WHEN salary >= 8000 THEN '小屌丝' ELSE '草根' END "details",department_id FROM employees; SELECT last_name,salary,CASE WHEN salary >= 15000 THEN '白骨精' WHEN salary >= 10000 THEN '潜力股' WHEN salary >= 8000 THEN '小屌丝' END "details" FROM employees; #4.4 CASE ... WHEN ... THEN ... WHEN ... THEN ... ELSE ... END # 类似于java的swich ... case... /* 练习1 查询部门号为 10,20, 30 的员工信息, 若部门号为 10, 则打印其工资的 1.1 倍, 20 号部门, 则打印其工资的 1.2 倍, 30 号部门,打印其工资的 1.3 倍数, 其他部门,打印其工资的 1.4 倍数 */ SELECT employee_id,last_name,department_id,salary,CASE department_id WHEN 10 THEN salary * 1.1 WHEN 20 THEN salary * 1.2 WHEN 30 THEN salary * 1.3 ELSE salary * 1.4 END "details" FROM employees; /* 练习2 查询部门号为 10,20, 30 的员工信息, 若部门号为 10, 则打印其工资的 1.1 倍, 20 号部门, 则打印其工资的 1.2 倍, 30 号部门打印其工资的 1.3 倍数 */ SELECT employee_id,last_name,department_id,salary,CASE department_id WHEN 10 THEN salary * 1.1 WHEN 20 THEN salary * 1.2 WHEN 30 THEN salary * 1.3 END "details" FROM employees WHERE department_id IN (10,20,30);
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
5、加密与解密函数
加密与解密函数主要用于对数据库中的数据进行加密和解密处理，以防止数据被他人窃取。这些函数在保证数据库安全时非常有用。
可以看到，ENCODE(value,password_seed)函数与DECODE(value,password_seed)函数互为反函数。
#5. 加密与解密的函数
# PASSWORD()在mysql8.0中弃用
SELECT PASSWORD('mysql')
FROM DUAL;
SELECT MD5('mysql'),SHA('mysql'),MD5(MD5('mysql'))
FROM DUAL;
#ENCODE()\DECODE() 在mysql8.0中弃用。
/*
SELECT ENCODE('atguigu','mysql'),DECODE(ENCODE('atguigu','mysql'),'mysql')
FROM DUAL;
*/
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
6、MySQL信息函数
MySQL中内置了一些可以查询MySQL信息的函数，这些函数主要用于帮助数据库开发或运维人员更好地对数据库进行维护工作。
#6. MySQL信息函数
SELECT VERSION(),CONNECTION_ID(),DATABASE(),SCHEMA(),
USER(),CURRENT_USER(),CHARSET('尚硅谷'),COLLATION('尚硅谷')
FROM DUAL;
- 1
- 2
- 3
- 4
- 5
7、其它函数
MySQL中有些函数无法对其进行具体的分类，但是这些函数在MySQL的开发和运维过程中也是不容忽视的。
#7. 其他函数
#如果n的值小于或者等于0，则只保留整数部分
SELECT FORMAT(123.125,2),FORMAT(123.125,0),FORMAT(123.125,-2)
FROM DUAL;
SELECT CONV(16, 10, 2), CONV(8888,10,16), CONV(NULL, 10, 2)
FROM DUAL;
#以“192.168.1.100”为例，计算方式为192乘以256的3次方，加上168乘以256的2次方，加上1乘以256，再加上100。
SELECT INET_ATON('192.168.1.100'),INET_NTOA(3232235876)
FROM DUAL;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
七、聚合函数
聚合函数
聚合函数作用于一组数据，并对一组数据返回一个值。
1、AVG / SUM / MAX / MIN / COUNT
AVG、SUM
可以对数值型数据使用AVG 和 SUM 函数。
#1.1 AVG / SUM ：只适用于数值类型的字段（或变量）
SELECT AVG(salary),SUM(salary),AVG(salary) * 107
FROM employees;
#如下的操作没有意义
SELECT SUM(last_name),AVG(last_name),SUM(hire_date)
FROM employees;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
MAX、MIN
可以对任意数据类型的数据使用 MIN 和 MAX 函数。
#1.2 MAX / MIN :适用于数值类型、字符串类型、日期时间类型的字段（或变量）
SELECT MAX(salary),MIN(salary)
FROM employees;
SELECT MAX(last_name),MIN(last_name),MAX(hire_date),MIN(hire_date)
FROM employees;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
COUNT
COUNT(*)返回表中记录总数，适用于任意数据类型。
COUNT(expr) 返回expr不为空的记录总数。
#1.3 COUNT： # ① 作用：计算指定字段在查询结构中出现的个数（不包含NULL值的） SELECT COUNT(employee_id),COUNT(salary),COUNT(2 * salary),COUNT(1),COUNT(2),COUNT(*) FROM employees ; #如果计算表中有多少条记录，如何实现？ #方式1：COUNT(*) #方式2：COUNT(1) #方式3：COUNT(具体字段) : 不一定对！ #② 注意：计算指定字段出现的个数时，是不计算NULL值的。 SELECT COUNT(commission_pct) FROM employees; SELECT commission_pct FROM employees WHERE commission_pct IS NOT NULL; #③ 公式：AVG = SUM / COUNT SELECT AVG(salary),SUM(salary)/COUNT(salary), AVG(commission_pct),SUM(commission_pct)/COUNT(commission_pct), SUM(commission_pct) / 107 FROM employees; #需求：查询公司中平均奖金率 #错误的！ SELECT AVG(commission_pct) FROM employees; #正确的： SELECT SUM(commission_pct) / COUNT(IFNULL(commission_pct,0)), AVG(IFNULL(commission_pct,0)) FROM employees; # 如何需要统计表中的记录数，使用COUNT(*)、COUNT(1)、COUNT(具体字段) 哪个效率更高呢？ # 如果使用的是MyISAM 存储引擎，则三者效率相同，都是O(1) # 如果使用的是InnoDB 存储引擎，则三者效率：COUNT(*) = COUNT(1)> COUNT(字段) #其他：方差、标准差、中位数
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
问题：用count(*)，count(1)，count(列名)谁好呢?
其实，对于MyISAM引擎的表是没有区别的。这种引擎内部有一计数器在维护着行数。
Innodb引擎的表用count(*),count(1)直接读行数，复杂度是O(n)，因为innodb真的要去数一遍。但好于具体的count(列名)。
问题：能不能使用count(列名)替换count(*)?
不要使用 count(列名)来替代 count() ， count() 是 SQL92 定义的标准统计行数的语法，跟数据库无关，跟 NULL 和非 NULL 无关。
说明：count(*)会统计值为 NULL 的行，而 count(列名)不会统计此列为 NULL 值的行。
2、GROUP BY WITH ROLLUP
可以使用GROUP BY子句将表中的数据分成若干组
SELECT column, group_function(column)
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[ORDER BY column];
- 1
- 2
- 3
- 4
- 5
明确：WHERE一定放在FROM后面
SELECT中出现的非组函数的字段必须声明在GROUP BY 中。
GROUP BY 声明在FROM后面、WHERE后面，ORDER BY 前面、LIMIT前面
即FROM、WHERE、GROUP BY、 ORDER BY、LIMIT
#需求：查询各个部门的平均工资，总工资
SELECT department_id,AVG(salary),SUM(salary)
FROM employees
GROUP BY department_id;
- 1
- 2
- 3
- 4
包含在 GROUP BY 子句中的列不必包含在SELECT 列表中
使用多个列分组
#需求：查询各个department_id,job_id的平均工资 #方式1： SELECT department_id,job_id,AVG(salary) FROM employees GROUP BY department_id,job_id; #方式2： SELECT job_id,department_id,AVG(salary) FROM employees GROUP BY job_id,department_id; #错误的！ SELECT department_id,job_id,AVG(salary) FROM employees GROUP BY department_id; #结论1：SELECT中出现的非组函数的字段必须声明在GROUP BY 中。 # 反之，GROUP BY中声明的字段可以不出现在SELECT中。 #结论2：GROUP BY 声明在FROM后面、WHERE后面，ORDER BY 前面、LIMIT前面 #结论3：MySQL中GROUP BY中使用WITH ROLLUP
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
使用 WITH ROLLUP 关键字之后，在所有查询出的分组记录之后增加一条记录，该记录计算查询出的所有记录的总和，即统计记录数量。
SELECT department_id,AVG(salary) FROM employees GROUP BY department_id WITH ROLLUP; #需求：查询各个部门的平均工资，按照平均工资升序排列 SELECT department_id,AVG(salary) avg_sal FROM employees GROUP BY department_id ORDER BY avg_sal ASC; #说明：当使用ROLLUP时，不能同时使用ORDER BY子句进行结果排序，即ROLLUP和ORDER BY是互相排斥的。 #错误的： SELECT department_id,AVG(salary) avg_sal FROM employees GROUP BY department_id WITH ROLLUP ORDER BY avg_sal ASC;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
当使用ROLLUP时，不能同时使用ORDER BY子句进行结果排序，即ROLLUP和ORDER BY是互相排斥的。
3、HAVING
基本使用
过滤分组：HAVING子句（使用条件）
行已经被分组；
使用了聚合函数；
满足HAVING 子句中条件的分组将被显示；
HAVING 不能单独使用，必须要跟 GROUP BY 一起使用。
非法使用聚合函数：不能在 WHERE 子句中使用聚合函数。如下：
#练习：查询各个部门中最高工资比10000高的部门信息 #错误的写法： SELECT department_id,MAX(salary) FROM employees WHERE MAX(salary) > 10000 GROUP BY department_id; #要求1：如果过滤条件中使用了聚合函数，则必须使用HAVING来替换WHERE。否则，报错。 #要求2：HAVING 必须声明在 GROUP BY 的后面。 #要求3：开发中，我们使用HAVING的前提是SQL中使用了GROUP BY。 #正确的写法： SELECT department_id,MAX(salary) FROM employees GROUP BY department_id HAVING MAX(salary) > 10000;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
WHERE和HAVING的对比
区别1：WHERE 可以直接使用表中的字段作为筛选条件，但不能使用分组中的计算函数作为筛选条件；HAVING 必须要与 GROUP BY 配合使用，可以把分组计算的函数和分组字段作为筛选条件。
这决定了，在需要对数据进行分组统计的时候，HAVING 可以完成 WHERE 不能完成的任务。这是因为，在查询语法结构中，WHERE 在 GROUP BY 之前，所以无法对分组结果进行筛选。HAVING 在 GROUP BY 之后，可以使用分组字段和分组中的计算函数，对分组的结果集进行筛选，这个功能是 WHERE 无法完成的。另外，WHERE排除的记录不再包括在分组中。
区别2：如果需要通过连接从关联表中获取需要的数据，WHERE 是先筛选后连接，而 HAVING 是先连接后筛选。
这一点，就决定了在关联查询中，WHERE 比 HAVING 更高效。因为 WHERE 可以先筛选，用一个筛选后的较小数据集和关联表进行连接，这样占用的资源比较少，执行效率也比较高。HAVING 则需要先把结果集准备好，也就是用未被筛选的数据集进行关联，然后对这个大的数据集进行筛选，这样占用的资源就比较多，执行效率也较低。
小结如下：
4、SELECT语句的执行顺序
查询的结构
#方式1： SELECT ...,....,... FROM ...,...,.... WHERE 多表的连接条件 AND 不包含组函数的过滤条件 GROUP BY ...,... HAVING 包含组函数的过滤条件 ORDER BY ... ASC/DESC LIMIT ...,... #方式2： SELECT ...,....,... FROM ... JOIN ... ON 多表的连接条件 JOIN ... ON ... WHERE 不包含组函数的过滤条件 AND/OR 不包含组函数的过滤条件 GROUP BY ...,... HAVING 包含组函数的过滤条件 ORDER BY ... ASC/DESC LIMIT ...,... #其中： #（1）from：从哪些表中筛选 #（2）on：关联多表查询时，去除笛卡尔积 #（3）where：从表中筛选的条件 #（4）group by：分组依据 #（5）having：在统计结果中再次筛选 #（6）order by：排序 #（7）limit：分页
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
你需要记住 SELECT 查询时的两个顺序：
关键字的顺序是不能颠倒的：
SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT...
- 1
SELECT 语句的执行顺序（在 MySQL 和 Oracle 中，SELECT 执行顺序基本相同）：
FROM -> WHERE -> GROUP BY -> HAVING -> SELECT 的字段 -> DISTINCT -> ORDER BY -> LIMIT
- 1
SQL 的执行原理
SELECT 是先执行 FROM 这一步的。在这个阶段，如果是多张表联查，还会经历下面的几个步骤：
首先先通过 CROSS JOIN 求笛卡尔积，相当于得到虚拟表 vt（virtual table）1-1；
通过 ON 进行筛选，在虚拟表 vt1-1 的基础上进行筛选，得到虚拟表 vt1-2；
添加外部行。如果我们使用的是左连接、右链接或者全连接，就会涉及到外部行，也就是在虚拟表 vt1-2 的基础上增加外部行，得到虚拟表 vt1-3。
当然如果我们操作的是两张以上的表，还会重复上面的步骤，直到所有表都被处理完为止。这个过程得到是我们的原始数据。
当我们拿到了查询数据表的原始数据，也就是最终的虚拟表vt1，就可以在此基础上再进行WHERE 阶段。在这个阶段中，会根据vt1表的结果进行筛选过滤，得到虚拟表vt2。
然后进入第三步和第四步，也就是 GROUP 和 HAVING 阶段。在这个阶段中，实际上是在虚拟表 vt2 的基础上进行分组和分组过滤，得到中间的虚拟表 vt3 和 vt4 。
当我们完成了条件筛选部分之后，就可以筛选表中提取的字段，也就是进入到 SELECT 和 DISTINCT阶段。
首先在 SELECT 阶段会提取想要的字段，然后在 DISTINCT 阶段过滤掉重复的行，分别得到中间的虚拟表vt5-1和vt5-2 。
当我们提取了想要的字段数据之后，就可以按照指定的字段进行排序，也就是ORDER BY 阶段 ，得到虚拟表vt6 。
最后在 vt6 的基础上，取出指定行的记录，也就是LIMIT阶段 ，得到最终的结果，对应的是虚拟表vt7 。
当然我们在写 SELECT 语句的时候，不一定存在所有的关键字，相应的阶段就会省略。
同时因为 SQL 是一门类似英语的结构化查询语言，所以我们在写 SELECT 语句的时候，还要注意相应的关键字顺序，所谓底层运行的原理，就是我们刚才讲到的执行顺序。
八、子查询
子查询指一个查询语句嵌套在另一个查询语句内部的查询，这个特性从MySQL 4.1开始引入。
SQL 中子查询的使用大大增强了 SELECT 查询的能力，因为很多时候查询需要从结果集中获取数据，或者需要从同一个表中先计算得出一个数据结果，然后与这个数据结果（可能是某个标量，也可能是某个集合）进行比较。
子查询的基本使用
子查询的基本语法结构：
子查询（内查询）在主查询之前一次执行完成。
子查询的结果被主查询（外查询）使用 。
注意事项：
子查询要包含在括号内；
将子查询放在比较条件的右侧；
单行操作符对应单行子查询，多行操作符对应多行子查询。
SELECT last_name,salary
from employees
WHERE salary > (
SELECT salary
FROM employees
WHERE last_name = 'Abel'
);
- 1
- 2
- 3
- 4
- 5
- 6
- 7
子查询的分类
按内查询的结果返回一条还是多条记录，将子查询分为单行子查询、 多行子查询 。
我们按内查询是否被执行多次，将子查询划分为相关(或关联)子查询和 不相关(或非关联)子查询。
子查询从数据表中查询了数据结果，如果这个数据结果只执行一次，然后这个数据结果作为主查询的条件进行执行，那么这样的子查询叫做不相关子查询。
同样，如果子查询需要执行多次，即采用循环的方式，先从外部查询开始，每次都传入子查询进行查询，然后再将结果反馈给外部，这种嵌套的执行方式就称为相关子查询。
1、单行子查询、多行子查询
单行子查询
单行比较操作符
#4. 单行子查询 #4.1 单行操作符： = != > >= < <= #题目：查询与141号员工的manager_id和department_id相同的其他员工 #的employee_id，manager_id，department_id。 #方式1： SELECT employee_id,manager_id,department_id FROM employees WHERE manager_id = ( SELECT manager_id FROM employees WHERE employee_id = 141 ) AND department_id = ( SELECT department_id FROM employees WHERE employee_id = 141 ) AND employee_id <> 141; #方式2：了解（成对查询） SELECT employee_id,manager_id,department_id FROM employees WHERE (manager_id,department_id) = ( SELECT manager_id,department_id FROM employees WHERE employee_id = 141 ) AND employee_id <> 141; #题目：查询最低工资大于110号部门最低工资的部门id和其最低工资 SELECT department_id,MIN(salary) FROM employees GROUP BY department_id HAVING MIN(salary) > ( SELECT MIN(salary) FROM employees WHERE department_id = 110 );
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
CASE中的子查询
#题目：显式员工的employee_id,last_name和location。
#其中，若员工department_id与location_id为1800的department_id相同，
#则location为’Canada’，其余则为’USA’。
SELECT employee_id,last_name,CASE department_id WHEN (SELECT department_id
FROM departments
WHERE location_id = 1800)
THEN 'Canada'
ELSE 'USA' END "location"
FROM employees;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
子查询中的空值问题
子查询不返回任何行
#4.2 子查询中的空值问题
SELECT last_name, job_id
FROM employees
WHERE job_id =
(SELECT job_id
FROM employees
WHERE last_name = 'Haas');
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
非法使用子查询
#4.3 非法使用子查询
#错误：Subquery returns more than 1 row
SELECT employee_id, last_name
FROM employees
WHERE salary =
(SELECT MIN(salary)
FROM employees
GROUP BY department_id);
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
多行子查询
也称为集合比较子查询；
内查询返回多行；
使用多行比较操作符。
多行比较操作符
# IN: SELECT employee_id, last_name FROM employees WHERE salary IN (SELECT MIN(salary) FROM employees GROUP BY department_id); # ANY / ALL: #题目：返回其它job_id中比job_id为‘IT_PROG’部门任一工资低的员工的员工号、 #姓名、job_id 以及salary SELECT employee_id,last_name,job_id,salary FROM employees WHERE job_id <> 'IT_PROG' AND salary < ANY ( SELECT salary FROM employees WHERE job_id = 'IT_PROG' ); #题目：返回其它job_id中比job_id为‘IT_PROG’部门所有工资低的员工的员工号、 #姓名、job_id 以及salary SELECT employee_id,last_name,job_id,salary FROM employees WHERE job_id <> 'IT_PROG' AND salary < ALL ( SELECT salary FROM employees WHERE job_id = 'IT_PROG' ); #题目：查询平均工资最低的部门id #MySQL中聚合函数是不能嵌套使用的。MIN(AVG(salary))是错误的 #方式1： SELECT department_id FROM employees GROUP BY department_id HAVING AVG(salary) = ( SELECT MIN(avg_sal) FROM ( SELECT AVG(salary) avg_sal FROM employees GROUP BY department_id ) t_dept_avg_sal ) #方式2： SELECT department_id FROM employees GROUP BY department_id HAVING AVG(salary) <= ALL( SELECT AVG(salary) avg_sal FROM employees GROUP BY department_id )
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
空值问题
#5.3 空值问题
SELECT last_name
FROM employees
WHERE employee_id NOT IN (
SELECT manager_id
FROM employees
);
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
2、相关子查询、不相关子查询
相关子查询
如果子查询的执行依赖于外部查询，通常情况下都是因为子查询中的表用到了外部的表，并进行了条件关联，因此每执行一次外部查询，子查询都要重新计算一次，这样的子查询就称之为关联子查询。
#结论：在SELECT中，除了GROUP BY 和 LIMIT之外，其他位置都可以声明子查询！
/*
SELECT ....,....,....(存在聚合函数)
FROM ... (LEFT / RIGHT)JOIN ....ON 多表的连接条件
(LEFT / RIGHT)JOIN ... ON ....
WHERE 不包含聚合函数的过滤条件
GROUP BY ...,....
HAVING 包含聚合函数的过滤条件
ORDER BY ....,...(ASC / DESC )
LIMIT ...,....
*/
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
相关子查询按照一行接一行的顺序执行，主查询的每一行都执行一次子查询。
子查询中使用主查询中的列
#题目：查询员工中工资大于本部门平均工资的员工的last_name,salary和其department_id #方式1：使用相关子查询 SELECT last_name,salary,department_id FROM employees e1 WHERE salary > ( SELECT AVG(salary) FROM employees e2 WHERE department_id = e1.department_id ); #方式2：在FROM中声明子查询 SELECT e.last_name,e.salary,e.department_id FROM employees e,( SELECT department_id,AVG(salary) avg_sal FROM employees GROUP BY department_id) t_dept_avg_sal WHERE e.department_id = t_dept_avg_sal.department_id AND e.salary > t_dept_avg_sal.avg_sal
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
EXISTS 与 NOT EXISTS关键字
关联子查询通常也会和 EXISTS操作符一起来使用，用来检查在子查询中是否存在满足条件的行。
如果在子查询中不存在满足条件的行：
条件返回 FALSE
继续在子查询中查找
如果在子查询中存在满足条件的行：
不在子查询中继续查找
条件返回 TRUE
NOT EXISTS关键字表示如果不存在某种条件，则返回TRUE，否则返回FALSE。
#题目：查询公司管理者的employee_id，last_name，job_id，department_id信息 #方式1：自连接 SELECT DISTINCT mgr.employee_id,mgr.last_name,mgr.job_id,mgr.department_id FROM employees emp JOIN employees mgr ON emp.manager_id = mgr.employee_id; #方式2：子查询 SELECT employee_id,last_name,job_id,department_id FROM employees WHERE employee_id IN ( SELECT DISTINCT manager_id FROM employees ); #方式3：使用EXISTS SELECT employee_id,last_name,job_id,department_id FROM employees e1 WHERE EXISTS ( SELECT * FROM employees e2 WHERE e1.employee_id = e2.manager_id );
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
相关更新
UPDATE table1 alias1
SET column = (SELECT expression
FROM table2 alias2
WHERE alias1.column = alias2.column);
- 1
- 2
- 3
- 4
使用相关子查询依据一个表中的数据更新另一个表的数据。
题目：在employees中增加一个department_name字段，数据为员工对应的部门名称
# 1）
ALTER TABLE employees
ADD(department_name VARCHAR2(14));
# 2）
UPDATE employees e
SET department_name = (SELECT department_name
FROM departments d
WHERE e.department_id = d.department_id);
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
相关删除
DELETE FROM table1 alias1
WHERE column operator (SELECT expression
FROM table2 alias2
WHERE alias1.column = alias2.column);
- 1
- 2
- 3
- 4
使用相关子查询依据一个表中的数据删除另一个表的数据。
题目：删除表employees中，其与emp_history表皆有的数据
DELETE FROM employees e
WHERE employee_id in
(SELECT employee_id
FROM emp_history
WHERE employee_id = e.employee_id);
- 1
- 2
- 3
- 4
- 5
