我不会记录的特别详细
大体框架
- 基本的Select语句
- 运算符
- 排序与分页
- 多表查询
- 单行函数
- 聚合函数
- 子查询
第三章 基本的SELECT语句
SQL分类
这个分类有很多种，大致了解下即可
- DDL（Data Definition Languages、数据定义语言），定义了不同的数据库、表、视图、索引等数据库对象，也可以用来创建、删除、修改数据库和数据表的结构。
常见的CREATE、DROP、ALTER - DML（Data Manipulation Language、数据操作语言），用于添加、删除、更新和查询数据库记录，并检查数据完整性。
主要包括INSERT、DELETE、UPDATE、SELECT等。SELECT用的最多. - DCL（Data Control Language、数据控制语言），用于定义数据库、表、字段、用户的访问权限和安全级别。
主要包括GRANT、REVOKE、COMMIT、ROLLBACK、SAVEPOINT等。
SQL的规则与规范
规则
这个必须遵守
- 每条命令以;或\g或\G结束
- 关键字不能被缩写也不能分行，更不能在中间插入空格，如select写成sel ect，错误
- 字符串和日期时间类型的数据可以使用单引号(‘’)表示
- 列的别名用双引号(“”),建议加上as
规范
- windows下对大小写不敏感
- Linux下大小写敏感，比如数据库名大写和小写不一样，但是关键字、函数名、列名及其别名是忽略大小写的
- 推荐的规范
- -SQL关键字、函数名、绑定变量都大写，其余的都小写
注释
单行注释 #和–,多行注释 /**/
# 单行注释
-- 单行注释
/*
多行注释
*/
命名规则
这个了解即可
- 库名,表名不得超过30个字符，变量名限制为29个字符
- 同一个MySQL软件中，库名必须唯一，同一个库中，表名必须唯一，同一个表中，字段名唯一
- 若关键字与字段名冲突且坚持使用，用着重号(``)把字段引起来
例子
create database a;//正确
create database a;//错误，已经存在
use a;
create table order(id int);//错误，order是关键字
create table `order`(id int);//正确
数据导入指令
source sqlFile
基本的Select语句
select … from TableName;
为什么不推荐使用*
因为获取所有的列会降低性能，让效率变低
列的别名
就比如你要计算年工资，你总不能存一个年工资和月工资把，这样浪费空间，还不是起一个别名
注意事项
别名使用双引号，列名和别名之间加入AS，若别名有空格必须使用双引号，别名要见名知义
例子:
SELECT name,salary,salary * 12 AS "annual salary" FROM employee;
DISTINCT
去重的，对所在列以及后面的列去重
例如:
SELECT DISTINCT departmentId From employee;//对departmentId进行去重
SELECT DISTINCT departmentId,salary FROM employee;//对部门id和工资这个组合进行去重
/*
若有两条记录
departmentId salary
1 8000
1 7800
那么这两条记录会保留
若两条记录是
departmentId salary
1 8000
1 8000
这个是保留一个
*/
NULL参与运算
只要有NULL结果必定为NULL
注意:NULL不是空，他占存储空间，NULL的长度为空
查询常数
这个我没有试过，明天试试
就比方说我想要把公司A的所有部门都列出来，格式如下:
| 公司A | 部门名称 |
|---|---|
| 公司A | 部门1 |
| 公司A | 部门2 |
| 公司A | 部门3 |
可以这么写
SELECT "公司A",departmentName from deparments;
显示表结构
DESC或者DESCRIBE
DESCRIBE departments;
desc departments;
结果中有两列是Key和Extra，只记录这两个
- Key：表示该列是否已编制索引。PRI表示该列是表主键的一部分(主键可以有多个列组成)；UNI表示该列是UNIQUE索引的一部分(复合索引)；MUL表示在列中某个给定值允许出现多次。
- Extra:该列的附加信息，例如AUTO_INCREMENT等等
where
这个太简单了，不记录了
第四章 运算符
算数运算符
加->+
减->-
乘->*
除->/
取余->%,比如 17 % 5 = 2 (17 -3*5)
另外注意下面的语句
SELECT 100 + 'a' FROM DUAL; // 这里的a看成0
SELECT 100 + '45' FROM DUAL; //'45'可以转换为数字那就是100+45=145
比较运算符
这个很简单，简要记录
真为1，假为0，其他为null
- 不等于:<>,!=
- 大于(等于):>(>=)
- 小于(等于):<(<=)
等号运算符
- 若两边都是字符串，比较的是ASCII码是否相等
若是整数就是比较数值大小 - 若一个是字符串，另一个是整数，MySQL会把字符串转换为数字进行比较
- 若有一个为null，那么结果必定为null
安全等于运算符 <=>
他比等于多一个对null的判断,只有他是针对null的，就是当两边都是null的时候返回1;一边为null一边不为null返回0
SELECT NULL <=> NULL, 1<=>1;
逻辑运算符
与或非,异或没了，这里简单记录,
注意这里只要有一个参数是NULL那么结果必定为NULL，优先级:NOT > AND > OR(XOR)
| 运算符 | 规则 | 例子 |
|---|---|---|
| NOT 或! | 若为0结果为1，若为1，结果为0 | SELECT NOT |
| AND 或 && | 同一则一，有一个0就是0 | SELECT 1 && 1 |
| OR 或 || | 有一个一就是一，全为0结果就是0 | SELECT 1 OR 0 |
| XOR | 两个不一样就是1，一样就是0 | SELECT 1 XOR 0 |
位运算符
我的理解就是把操作数转换为补码按照指定的运算符的规则进行运算，再把结果转换为十进制数字
支持的运算符
| 运算符 | 含义 | 例子 |
|---|---|---|
| & | 按位与(AND) | SELECT A & B |
| | | 按位或(OR) | SELECT A | B |
| ^ | 按位异或(XOR) | SELECT A ^ B |
| ~ | 按位取反 | SELECT ~A |
| >> | 按位左移 | SELECT A <<1 |
| << | 按位右移 | SELECT B >>1 |
解释: 以 8 和-2 为例
8转换为补码是 0 1000
-2的原码是 1 0010
-2的反码是 1 1101
-2的补码是 1 1110
8 & -2
0 1000
& 1 1110
——————
0 1000
这里直接转二进制即可，结果是8
8 | -2
0 1000
| 1 1110
——————
1 1110
最高位是1，是负数，要求真值，对1 1110取补等于1 0010 转换为二进制数为-2 ，但是显示的结果是18446744073709551614，应该是默认情况下数字以bigint类型存储的，bigint类型是8个字节，他的结果解释方式应该是把二进制解释成无符号数，要不然不会这么大的数字，如果是按照这么来的话，那1 1110需要进行符号位扩展,扩展如下
1111 1111 1111 1111 1111 1111 1111 1111 1111 1111 1111 1111 1111 1111 1111 1110 ,就是在1 1110的最高位1前面加了79个1，把这一串数字转换为十进制等于18446744073709551614
8 ^ -2
0 1000
| 1 1110
——————
1 0110
符号位扩展，扩展成64位，扩展结果
1111 1111 1111 1111 1111 1111 1111 1111 1111 1111 1111 1111 1111 1111 1111 0110,把这东西转换为十进制18446744073709551606
~ -2
-2的补码(64位)等于1111 1111 1111 1111 1111 1111 1111 1111 1111 1111 1111 1111 1111 1111 1111 1110
# 1111 1111 -> 0000 0000
# 1111 1111 -> 0000 0000
# 1111 1111 -> 0000 0000
# 1111 1111 -> 0000 0000
# 1111 1111 -> 0000 0000
# 1111 1111 -> 0000 0000
# 1111 1110 -> 0000 0001
# 从上到下,把->后面的数字串起来为1前面63个0，当成无符号数，转换为十进制为1，所以答案就是1
1 >> 2
tips:在一定的条件下，可以当作除以2的n次幂来用
右移的规则:右移指定的位数后，右边低位的数值被移出并丢弃，左边高位空出的位置用0补齐。
这里不做符号位扩展了，简要的记录一下
1的补码是0 1,右移一位，01的最低位1就没了，高位补零，那么直接移成了0
1 << 2
tips: 在一定的条件下，可以当成乘以2的n次幂来用
就是乘以了2n,n就是右移的位数，可能会溢出
优先级
其他运算符
- IS NULL: 判空的
- IS NOT NULL:判非空的
- LEAST:返回多值中的最小值
SELECT LEAST(1,2,3,4) FROM DUAL;
- GREATEST:返回多值中的最大值
SELECT GREATEST(1,2,3,4) FROM DUAL;
- BETWEEN AND:判断值是否在指定的区间内，区间是左闭右闭的
SELECT 2 between 1 and 2;
- ISNULL:判空
- IN: 判断一个值是否为列表中的任意一个值
一般是在子查询里面用的比较多
SELECT 2 NOT IN (1,2);
- NOT IN:判断一个值是否不是一个列表中的任意一个值
SELECT 2 NOT IN (1,2);
- LIKE 运算符:匹配字符串的，模糊匹配
%是通配零个或多个字符的
_是只能匹配一个字符的
SELECT 'abcd' LIKE 'a__d';
- ESCAPE:回避特殊符号的,\也可以
比方说，查找前两个字符是EM，第三个字符是%或者_的
:取消_,%的匹配作用
ESCAPE:指定一个字符充当\的作用，这个言简意赅点,
指定字符后面紧挨着的第一个匹配符号(_,%)失效,这个不太常用
SELECT DepartmentId FROM DEPT WHERE DepartmentId like 'EM\%'
or DepartmentId like 'EM\_';
//这个用的比较少,了解
SELECT DepartmentId FROM DEPT WHERE DepartmentId like 'EMa%' ESCAPE 'a'
or DepartmentId like 'EMb_' ESCAPE 'b';
- REGEXP运算符
这里要用到点正则表达式的东西,这个用到了去网上搜
正则表达式简要了解下，这东西能写出一本书出来
‘^’:匹配以该字符后面的字符开头的字符串
‘$’:匹配以该字符前面的字符结尾的字符串
'. ': 匹配任何一个但字符
“[…]”:匹配出现在方括号里面的字符，如匹配所有字母:[a-zA-Z]
‘*’:匹配零个或多个在它前面的字符,
扩展 正则表达式
常应用于检索字符串，提取数字，验证等等
MySQL支持的
- 查询以特定字符或字符串开头的记录:
在name列查询以ea开头的记录
SELECT * FROM `user` where name REGEXP '^ea';
- 查询以特定字符或字符串结尾的记录:
在name列查询以or结尾的记录
SELECT * FROM `user` where name REGEXP 'or$';
- 用符号’.'通配一个字符,查询name列字段值中包含ea且e和a之间只有一个字母的记录
SELECT * FROM `user` where name REGEXP 'e.a';
- 用’*‘和’+'匹配多个字符:在name列中查找以字母c开头且c后面出现字母e至少一次的记录
SELECT * FROM `user` where name REGEXP '^ce+';
- 匹配指定字符串,可以匹配多个
- 在name列中查找包含ea的记录
SELECT * FROM `user` WHERE name REGEXP 'ea';
- 在name列中查找包含ea和or的记录
SELECT * FROM `user` WHERE name REGEXP 'ea | or';
- 匹配指定字符中任意一个,就是方括号的用法
在name列中查找包含字母abced和 wxy的记录
SELECT * FROM `user` WHERE name REGEXP '[a-dw-y]
- 匹配指定字符以外的字符 "[^字符集合]"匹配不在指定集合中的任何字符
在name列中查找id字符串包含a-h和数字1-8以外字符的记录
SELECT * FROM `user` WHERE id REGEXP '[^a-h1-8]';
- 使用{n,}或者{n,m}来指定字符串连续出现的次数,“字符串
{n,}”表示至少匹配n次前面的字符；“字符串{n,m}”表示匹配前面的字符串不少于n次，不多于m次。例如，or{2,}表示or连续出现至少2次，也可以
大于2次；ea{2,4}表示ea连续出现最少2次，最多不能超过4次。
第五章 排序与分页
排序
使用order by 子句进行排序
- asc(ascend):升序
- desc(descend):降序
一般是在select语句的结尾处
比如 查找员工的姓名，工资,部门号,按部门id进行升序排序，若相同按工资降序排序
SELECT last_name,department_id,salary
FROM employees
ORDER BY department_id,salary DESC;
分页
就是一次性查找出来的数据很多时，用户一看数据太多将不想看了，降低可读性
格式
LIMIT [位置偏移量,] 行数
位置偏移量可选,若不写就是0，行数就是你要显示的条数，看例子
- 前5条记录
SELECT * FROM TableName LIMIT 0,5; --前5条记录
SELECT * FROM TableName LIMIT 5;--实现效果和上面的一样
- 第6~15条数据
SELECT * FROM TableName LIMIT 5,10;
MySQL8中可以使用LIMIT 4 OFFSET 4，就是从第5条记录后面开始的4条数据
等同于 LIMIT 4,4
分页显示的公式
(当前页数-1)*每页条数,每页条数
SELECT * FROM TableName LIMIT (PageNo-1)*PageSize,PageSize;
第六章 多表查询
引子
案例：查询员工的姓名及其部门名称
需求:查询员工的姓名及其部门名称,你可能会这么写
SELECT last_name, department_name
FROM emploees, departments;
查询结果
employee只有107条数据，那么结果也应该只有107条，但是结果是2889条，实际上是做了一个交叉连接(不推荐使用)
笛卡尔积的理解
SQL92中，笛卡尔积也称为交叉连接，英文是 CROSS JOIN。在 SQL99 中也是使用 CROSS JOIN表示交叉连接。它的作用就是可以把任意表进行连接，即使这两张表不相关。在MySQL中如下情况会出现笛卡尔积：
出现问题的原因:
忽略多个表的连接条件(或关联条件)
连接条件无效
所有表中的所有行互相连接
没有条件就加条件
#案例：查询员工的姓名及其部门名称
SELECT last_name, department_name
FROM employees, departments
WHERE employees.department_id = departments.department_id;
多表查询分类详解
等值连接 vs 非等值连接
等值连接
等值连接我的理解是表之间有关联字段的且连接条件是什么等于什么的
SELECT employees.employee_id, employees.last_name,
employees.department_id, departments.department_id,
departments.location_id
FROM employees, departments
WHERE employees.department_id = departments.department_id;
- 区分重复的列名:
多表中有相同的列的时候，必须在列名之前加上表名前缀
独有的也建议加上表名前缀
- 表的别名
可以简化查询,如果使用了别名，那么在查询字段中、过滤条件中就只能使用别名进行代替
SELECT emp.employee_id, emp.last_name, emp.department_id,
dep.department_id, dep.location_id
FROM employees emp , departments dep
WHERE emp.department_id = dep.department_id;
- 连接多个表
总结：连接 n个表,至少需要n-1个连接条件。比如，连接三个表，至少需要两个连接条件。
非等值连接
查询员工姓名,工资以及对应的等级
所涉及到的表结构
employee的
| 字段名 | 字段类型 |
|---|---|
| employee_id, | int |
| first_name | varchar(20) |
| last_name | varchar(25) |
| varchar(25) | |
| phone_number | varchar(20) |
| hire_date | date |
| job_id | varchar(10) |
| salary | “double(8,2)” |
| commission_pct | “double(2,2)” |
| manager_id | int |
| department_id | int |
job_grades
| 字段名 | 字段类型 |
|---|---|
| grade_level | varchar(3) |
| lowest_sal | int |
| highest_sal | int |
SELECT last_name,salary,grade_level
FROM employee e,job_grades j
WHERE e.salary BETWEEN lowest_sal AND highest_sal;
自连接 vs 非自连接
自连接:自己连接自己
非自连接:自己不连接自己的
WORKER表中的MANAGER_ID和MANAGER表中的EMPLOYEE_ID相等
需求: 查询employees表，返回xxx works for xxx
SELECT CONCAT(worker.last_name,'works for ',manager.last_name)
FROM employees worker,employees manager
WHERE worker.manager_id = manager.employee_id;
内连接 vs 外连接
内连接
合并具有同一列的两个以上的表的行，结果集中不包含一个表与另一个表不匹配的行
我自己复述出来的:合并相同列，只包含满足条件的
需求: 查询所有员工的last_name,department_name信息
SELECT employee_id,department_name
FROM employees e,departments d
WHERE e.department_id = d.department_id;
内连接(INNER JOIN)实现
语法
SELECT 字段列表
FROM A表 INNER JOIN B表
ON 关联条件
WHERE 等其他子句;-
需求： 查询员工id，员工姓名，员工的部门id，部门id，部门的位置id
SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_id
FROM employees e JOIN departments d
ON e.department_id = d.department_id;
需求:查询员工id，部门名字，部门所在的城市
SELECT employee_id, city, department_name
FROM employees e
JOIN departments d
ON d.department_id = e.department_id
JOIN locations l
ON d.location_id = l.location_id;
外连接
可以查询满足条件的记录也可以查询某一方不满足条件的记录
- 内连接: 合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行
- 外连接: 两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的
行 ，这种连接称为左（或右） 外连接。没有匹配的行时, 结果表中相应的列为空(NULL)。 - 如果是
左外连接，则连接条件中左边的表也称为主表，右边的表称为从表。 - 如果是
右外连接，则连接条件中右边的表也称为主表，左边的表称为从表。 - SQL92:使用(+)创建连接
在 SQL92 中采用（+）代表从表所在的位置。即左或右外连接中，(+) 表示哪个是从表。
Oracle 对 SQL92 支持较好，**而 MySQL 则不支持 SQL92 的外连接。**
#左外连接
SELECT last_name,department_name
FROM employees ,departments
WHERE employees.department_id = departments.department_id(+);
#右外连接
SELECT last_name,department_name
FROM employees ,departments
WHERE employees.department_id(+) = departments.department_id;
接下来记录MySQL支持的写法
SQL99语法实现多表查询
通过JOIN…ON子句来实现的，下面是语法结构
SELECT table1.column, table2.column,table3.column
FROM table1
JOIN table2 ON table1 和 table2 的连接条件
JOIN table3 ON table2 和 table3 的连接条件
左外连接(LEFT OUTER JOIN)
语法:
#实现查询结果是A
SELECT 字段列表
FROM A表 LEFT JOIN B表
ON 关联条件
WHERE 等其他子句;
例子:查询员工的姓名,所在的部门id，所在的部门名称
SELECT e.last_name,e.department_id,d.department_name
FROM employees e
LEFT OUTER JOIN departments d
ON e.department_id = d.department_id;
右外连接(RIGHT OUTER JOIN)
语法:
#实现查询结果是B
SELECT 字段列表
FROM A表 RIGHT JOIN B表
ON 关联条件
WHERE 等其他子句;
例子:查询员工的姓名,所在的部门id，所在的部门名称
SELECT e.last_name,e.department_id,d.department_name
FROM employees e
RIGHT OUTER JOIN departments d
ON e.department_id = d.department_id;
注意 LEFT JOIN 和RIGHT JOIN 只存在于SQL99及以后的标准
- 满外连接(FULL OUTER JOIN)
满外连接的结果 = 左右表匹配的数据 + 左表没有匹配到的数据 + 右表没有匹配到的数据。
满外连接(FULL JOIN)
但是 MySQL不支持FULL JOIN，我们可以通过LEFT JOIN UNION RIGHT JOIN 来实现
UNION的作用
合并查询结果,合并的时候两个表对应的列数和数据类型必须相同，并且相互对应,各个SELECT语句之间使用UNION或UNION ALL关键字分割
语法格式:
SELECT column,... FROM table1
UNION [ALL]
SELECT column,... FROM table2
UNION的图解
UNION ALL的图解
但是UNION ALL的效率高
举例:查询部门编号>90或邮箱包含a的员工信息
# 方式1
SELECT * FROM employees WHERE email LIKE '%a%' OR department_id>90;
# 方式2
SELECT * FROM employees WHERE email LIKE '%a%'
UNION
SELECT * FROM employees WHERE department_id>90;
举例:查询中国用户中男性的信息以及美国用户中中年男性的用户信息
SELECT id,cname FROM t_chinamale WHERE csex='男'
UNION ALL
SELECT id,tname FROM t_usmale WHERE tGender='male';
7种JOIN的实现方式
# 中图 内连接A ∩ B
SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`;
# 左上图，左外连接
SELECT employee_id,last_name,department_name
FROM employees e LEFT OUTER JOIN departments d
ON e.`department_id` = d.`department_id`;
# 右上图:右外连接
SELECT employee_id,last_name,department_name
FROM employees e RIGHT OUTER JOIN departments d
ON e.`department_id` = d.`department_id`;
# 左中图 A - A ∩ B (A ∩ B 的department_id必然不等于null,我们想要结果中的department_id要等于null的)
SELECT employee_id,last_name,department_name
FROM employees e LEFT OUTER JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.department_id IS NULL;
# 右中图
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL
# 左下图
SELECT employee_id,last_name,department_name
FROM employees e LEFT OUTER JOIN departments d
ON e.`department_id` = d.`department_id`
UNION ALL
SELECT employee_id,last_name,department_name
FROM employees e RIGHT OUTER JOIN departments d
ON e.`department_id` = d.`department_id`
# 右下图 = 左下图 - 中图 = 左中图 ∪ 右中图
# 左中图 ∪ 右中图
SELECT employee_id,last_name,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,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL;
# 左下图 - 中图 这个我写的实现不了
新特性
- NATURAL JOIN
帮我们自动查询两张连接表中 所有相同的字段 ，然后进行等值连接 。
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`;
# 用NATUAL JOIN
SELECT employee_id,last_name,department_name
FROM employees e NATURAL JOIN departments d;
- USING 连接
SELECT employee_id,last_name,department_name
FROM employees e ,departments d
WHERE e.department_id = d.department_id;
# 用 USING
SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
USING (department_id);
USING只能和JOIN一起使用，并且要求两个关联字段在关联表中名称一致，而且只能表示关联字段值相等,这个应用比较窄
第七章 单行函数
数值函数
基本函数
| 函数名称 | 函数作用 |
|---|---|
| ABS(x) | 返回x的绝对值 |
| SIGN(X) | 返回X的符号。正数返回1，负数返回-1，0返回0 |
| PI() | 返回圆周率的值 |
| CEIL(x)，CEILING(x) | 返回大于或等于某个值的最小整数 |
| FLOOR(x) | 返回小于或等于某个值的最大整数 |
| LEAST(e1,e2,e3…) | 返回列表中的最小值 |
| GREATEST(e1,e2,e3…) | 返回列表中的最大值 |
| MOD(x,y) | 返回X除以Y后的余数 |
| RAND() | 返回0~1的随机值 |
| RAND(x) | 返回0~1的随机值，其中x的值用作种子值，相同的X值会产生相同的随机数 |
| ROUND(x) | 返回一个对x的值进行四舍五入后，最接近于X的整数 |
| ROUND(x,y) | 返回一个对x的值进行四舍五入后最接近X的值，并保留到小数点后面Y位 |
| TRUNCATE(x,y) | 返回数字x截断为y位小数的结果,若y<0则对整数进行操作 |
| SQRT(x) | 返回x的平方根。当X<0时，返回NULL |
| RADIANS(x) | 将角度转化为弧度，其中，参数x为角度值 |
| DEGREES(x) | 将弧度转化为角度，其中，参数x为弧度值 |
弧度制与角度制的转换
r = 1,一圈是2Π,一圈是360°, 1° = 2Π/360°= Π/180° = 3.1415926…/180° = 0.017453
弧度值->角度值
角度值 = 弧度值 * (180/Π)
举例:
SELECT
ABS(-1),ABS(3),SIGN(-23),SIGN(43),PI(),CEIL(32.5),CEILING(-43.5),FLOOR(32.5)
FROM DUAL;
结果
SELECT
FLOOR(-43.5),MOD(12,-5),MOD(-12,5),MOD(17,-3),RAND(),RAND(),RAND(-1),RAND(10),RAND(-1),RAND(-1)
FROM DUAL;
结果
SELECT
ROUND(12.33),ROUND(12.343,2),ROUND(12.324,-1),TRUNCATE(12.66,1),TRUNCATE(12.66,-1)
FROM DUAL;
结果
SELECT RADIANS(25),RADIANS(60),RADIANS(90),DEGREES(2*PI()),DEGREES(RADIANS(90))
FROM DUAL;
三角函数
| 函数 | 用法 |
|---|---|
| SIN(x) | 返回x的正弦值，其中，参数x为弧度值 |
| ASIN(x) | 返回x的反正弦值，即获取正弦为x的值。如果x的值不在-1到1之间，则返回NULL |
| COS(x) | 返回x的余弦值，其中，参数x为弧度值 |
| ACOS(x) | 返回x的反余弦值，即获取余弦为x的值。如果x的值不在-1到1之间，则返回NULL |
| TAN(x) | 返回x的正切值，其中，参数x为弧度值 |
| ATAN(x) | 返回x的反正切值，即返回正切值为x的值 |
| ATAN2(m,n) | 返回两个参数的反正切值 |
| COT(x) | 返回x的余切值，其中，X为弧度值 |
ATAN2(M,N)函数返回两个参数的反正切值。 与ATAN(X)函数相比，ATAN2(M,N)需要两个参数，例如有两个
点point(x1,y1)和point(x2,y2)，使用ATAN(X)函数计算反正切值为ATAN((y2-y1)/(x2-x1))，使用ATAN2(M,N)计
算反正切值则为ATAN2(y2-y1,x2-x1)。由使用方式可以看出，当x2-x1等于0时，ATAN(X)函数会报错，而
ATAN2(M,N)函数则仍然可以计算。
就是ATAN2的应用范围要比ATAN要大
SELECT
SIN(RADIANS(30)),DEGREES(ASIN(1)),TAN(RADIANS(45)),DEGREES(ATAN(1)),DEGREES(ATAN2(1,1)
)
FROM DUAL;
指数与对数
| 函数 | 用法 |
|---|---|
| POW(x,y),POWER(X,Y) | 返回x的y次方 |
| EXP(X) | 返回e的X次方，其中e是一个常数，2.718281828459045 |
| LN(X)，LOG(X) | 返回以e为底的X的对数，当X <= 0 时，返回的结果为NULL |
| LOG10(X) | 返回以10为底的X的对数，当X <= 0 时，返回的结果为NULL |
| LOG2(X) | 返回以2为底的X的对数，当X <= 0 时，返回NULL |
进制转换类
| 函数 | 用法 |
|---|---|
| BIN(x) | 返回x的二进制编码 |
| HEX(x) | 返回x的十六进制编码 |
| OCT(x) | 返回x的八进制编码 |
| CONV(x,f1,f2) | 返回f1进制数变成f2进制数 |
字符串函数
| 函数 | 用法 |
|---|---|
| ASCII(S) | 返回字符串S中的第一个字符的ASCII码值 |
| CHAR_LENGTH(s) | 返回字符串s的字符数。作用与CHARACTER_LENGTH(s)相同 |
| LENGTH(s) | 返回字符串s的字节数，和字符集有关 |
| CONCAT(s1,s2,…,sn) | 连接s1,s2,…,sn为一个字符串 |
| CONCAT_WS(separate,s1,s2,…,sn) | 同CONCAT(s1,s2,…)函数，用separate来连接s1~sn |
| INSERT(str, idx, len,replacestr) | 将字符串str从第idx位置开始，len个字符长的子串替换为字符串replacestr |
| REPLACE(str, a, b) | 用字符串b替换字符串str中所有出现的字符串a |
| UPPER(s) 或 UCASE(s) | 将字符串s的所有字母转成大写字母 |
| LOWER(s) 或LCASE(s) | 将字符串s的所有字母转成小写字母 |
| LEFT(str,n) | 返回字符串str最左边的n个字符 |
| RIGHT(str,n) | 返回字符串str最右边的n个字符 |
| LPAD(str, len, pad) | 用字符串pad对str最左边进行填充，直到str的长度为len个字符 |
| RPAD(str ,len, pad) | 用字符串pad对str最右边进行填充，直到str的长度为len个字符 |
| LTRIM(s) | 去掉字符串s左侧的空格 |
| RTRIM(s) | 去掉字符串s右侧的空格 |
| TRIM(s) | 去掉字符串s开始与结尾的空格 |
| TRIM(s1 FROM s) | 去掉字符串s开始与结尾的s1 |
| TRIM(LEADING s1 FROM s) | 去掉字符串s开始处的s1 |
| TRIM(TRAILING s1 FROM s) | 去掉字符串s结尾处的s1 |
| REPEAT(str, n) | 返回str重复n次的结果 |
| SPACE(n) | 返回n个空格 |
| STRCMP(s1,s2) | 比较字符串s1,s2的ASCII码值的大小 |
| SUBSTR(s,index,len) | 返回从字符串s的index位置其len个字符，作用与SUBSTRING(s,n,len),MID(s,n,len)相同 |
| LOCATE(substr,str) | 返回字符串substr在字符串str中首次出现的位置，作用于POSITION(substrIN str)、INSTR(str,substr) |
| ELT(m,s1,s2,…,sn) | 返回指定位置的字符串，如果m=1，则返回s1，如果m=2，则返回s2，如果m=n，则返回sn |
| FIELD(s,s1,s2,…,sn) | 返回字符串s在字符串列表中第一次出现的位置 |
| FIND_IN_SET(s1,s2) | 返回字符串s1在字符串s2中第一次出现的位置。其中，字符串s2是一个以逗号分隔的字符串 |
| REVERSE(s) | 返回s反转后的字符串 |
| NULLIF(value1,value2) | 比较两个字符串，如果value1与value2相等，则返回NULL，否则返回value1 |
字符串起始位置从
1开始
我挑几个我不会的记录
SELECT TRIM('A' FROM 'AABAA'),TRIM(LEADING 'C' FROM 'CACC'),TRIM(TRAILING 'B' FROM 'AEBBB')
FROM dual;
SELECT FIND_IN_SET('AB','AA,AB,AB'),
ELT(2,'A','B','C'),
LOCATE('NOTDY','THE NOTDY WEBSITE'),
NULLIF('A','B')
FROM dual;
日期和时间函数
获取日期和时间
| 函数 | 用法 |
|---|---|
| CURDATE() ，CURRENT_DATE() | 返回当前日期，只包含年、月、日 |
| CURTIME() ， CURRENT_TIME() | 返回当前时间，只包含时、分、秒 |
| NOW() / SYSDATE() / CURRENT_TIMESTAMP() / LOCALTIME() /LOCALTIMESTAMP() | 返回当前系统日期和时间 |
| UTC_DATE() | 返回UTC（世界标准时间） |
| 日期 | |
| UTC_TIME() | 返回UTC（世界标准时间） |
| 时间 |
SELECT CURRENT_DATE(),
CURRENT_TIME(),
SYSDATE(),
UTC_DATE(),
UTC_TIME()
FROM dual;
日期与时间戳的转换
| 函数 | 用法 |
|---|---|
| UNIX_TIMESTAMP() | 以UNIX时间戳的形式返回当前时间。SELECT UNIX_TIMESTAMP() -1634348884 |
| UNIX_TIMESTAMP(date) | 将时间date以UNIX时间戳的形式返回。 |
| FROM_UNIXTIME(timestamp) | 将UNIX时间戳的时间转换为普通格式的时间 |
SELECT UNIX_TIMESTAMP(SYSDATE()),
UNIX_TIMESTAMP(CURDATE()),
UNIX_TIMESTAMP(CURTIME()),
UNIX_TIMESTAMP('2023-8-19 10:43:10')
FROM dual;
获取月份、星期、星期数、天数等函数
| 函数 | 用法 |
|---|---|
| YEAR(date) / MONTH(date) / DAY(date) | 返回具体的日期值 |
| HOUR(time) / MINUTE(time) /SECOND(time) | 返回具体的时间值 |
| MONTHNAME(date) | 返回月份：January，… |
| DAYNAME(date) | 返回星期几：MONDAY TUESDAY…SUNDAY |
| WEEKDAY(date) | 返回周几，注意，周1是0，周2是1，。。。周日是6 |
| QUARTER(date) | 返回日期对应的季度，范围为1～4 |
| WEEK(date) ， WEEKOFYEAR(date) | 返回一年中的第几周 |
| DAYOFYEAR(date) | 返回日期是一年中的第几天 |
| DAYOFMONTH(date) | 返回日期位于所在月份的第几天 |
| DAYOFWEEK(date) | 返回周几，注意：周日是1，周一是2，。。。周六是7 |
SELECT YEAR(CURDATE()),MONTH(CURDATE()),DAY(CURDATE()),
HOUR(CURTIME()),MINUTE(NOW()),SECOND(SYSDATE())
FROM DUAL;
SELECT MONTHNAME('2023-10-26'),DAYNAME('2023-10-26'),WEEKDAY('2023-10-26'),
QUARTER(CURDATE()),WEEK(CURDATE()),DAYOFYEAR(NOW())
FROM DUAL;
SELECT DAYOFMONTH(NOW()),DAYOFWEEK(NOW())
FROM DUAL;
日期与操作函数
| 函数 | 用法 |
|---|---|
| EXTRACT(type FROM date) | 返回指定日期中特定的部分，type指定返回的值 |
| type的取值与含义 |
SELECT EXTRACT(MINUTE FROM NOW()),EXTRACT( DAY FROM NOW()),
EXTRACT( QUARTER FROM NOW()),EXTRACT( DAY_SECOND FROM NOW())
FROM DUAL;
时间和秒钟转换的函数
| 函数 | 用法 |
|---|---|
| TIME_TO_SEC(time) | 将 time 转化为秒并返回结果值。转化的公式为： 小时3600+分钟60+秒 |
| SEC_TO_TIME(seconds) | 将 seconds 描述转化为包含小时、分钟和秒的时间v |
SELECT TIME_TO_SEC(SYSDATE()),SEC_TO_TIME(7878)
FROM DUAL;
计算日期和时间的函数
第一组
| 函数 | 用法 |
|---|---|
| DATE_ADD(datetime, INTERVAL expr type)，ADDDATE(date,INTERVAL expr type) | 返回与给定日期时间相差INTERVAL时间段的日期时间 |
| DATE_SUB(date,INTERVAL expr type)，SUBDATE(date,INTERVAL expr type) | 返回与date相差INTERVAL时间间隔的日期 |
type的取值
例子:
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 DATE_SUB('2021-01-21',INTERVAL 31 DAY) AS col1,
SUBDATE('2021-01-21',INTERVAL 31 DAY) AS col2,
DATE_SUB('2021-01-21 02:01:01',INTERVAL '1 1' DAY_HOUR) AS col3
FROM DUAL;
第2组:
| 函数 | 用法 |
|---|---|
| ADDTIME(time1,time2) | 返回time1加上time2的时间。当time2为一个数字时，代表的是秒 ，可以为负数 |
| SUBTIME(time1,time2) | 返回time1减去time2后的时间。当time2为一个数字时，代表的是 秒 ，可以为负数 |
| DATEDIFF(date1,date2) | 返回date1 - date2的日期间隔天数 |
| TIMEDIFF(time1, time2) | 返回time1 - time2的时间间隔 |
| FROM_DAYS(N) | 返回从0000年1月1日起，N天以后的日期 |
| TO_DAYS(date) | 返回日期date距离0000年1月1日的天数 |
| LAST_DAY(date) | 返回date所在月份的最后一天的日期 |
| MAKEDATE(year,n) | 针对给定年份与所在年份中的天数返回一个日期 |
| MAKETIME(hour,minute,second) | 将给定的小时、分钟和秒组合成时间并返回 |
| PERIOD_ADD(time,n) | 返回time加上n后的时间 |
SELECT
ADDTIME(NOW(),20),SUBTIME(NOW(),30),SUBTIME(NOW(),'1:1:3'),DATEDIFF(NOW(),'2023-8-
01'),
TIMEDIFF(NOW(),'2023-8-19 10:10:18')FROM DUAL;
SELECT
FROM_DAYS(366),TO_DAYS('0000-12-25'),
LAST_DAY(NOW()),MAKEDATE(YEAR(NOW()),12), # 这个MAKEDAET我试了感觉是在这一年的第一天的基础上加上n，n就是后面的数字
MAKETIME(10,21,23),PERIOD_ADD(20200101010101,
10)
FROM DUAL;
日期的格式化与解析
| 函数 | 用法 |
|---|---|
| DATE_FORMAT(date,fmt) | 按照字符串fmt格式化日期date值 |
| TIME_FORMAT(time,fmt) | 按照字符串fmt格式化时间time值 |
| GET_FORMAT(date_type,format_type) | 返回日期字符串的显示格式 |
| STR_TO_DATE(str, fmt) | 按照字符串fmt对str进行解析，解析为一个日期 |
常用的fmt的格式符
| 格式符 | 说明 | 格式符 | 说明 |
|---|---|---|---|
| %Y | 4位数字表示年份 | %y | 表示两位数字表示年份 |
| %M | 月名表示月份（January,…） | %m | 两位数字表示月份（01,02,03。。。） |
| %b | 缩写的月名（Jan.，Feb.，…） | %c | 数字表示月份（1,2,3,…） |
| %D | 英文后缀表示月中的天数（1st,2nd,3rd,…） | %d | 两位数字表示月中的天数(01,02…) |
| %e | 数字形式表示月中的天数（1,2,3,4,5…） | %H | 两位数字表示小数，24小时制（01,02…） |
| %h和%I | 两位数字表示小时，12小时制（01,02…） | %k | 数字形式的小时，24小时制(1,2,3) |
| %l | 数字形式表示小时，12小时制（1,2,3,4…） | %i | 两位数字表示分钟（00,01,02） |
| %S和%s | 两位数字表示秒(00,01,02…) | %W | 一周中的星期名称（Sunday…） |
| %a | 一周中的星期缩写（Sun.，Mon.,Tues.，…） | %w | 以数字表示周中的天数(0=Sunday,1=Monday…) |
| %j | 以3位数字表示年中的天数(001,002…) | %U | 以数字表示年中的第几周，（1,2,3。。）其中Sunday为周中第一天 |
| %u | 以数字表示年中的第几周，（1,2,3。。）其中Monday为周中第一天 | %T | 24小时制 |
| %r | 12小时制 | %p | AM或PM |
| %% | 表示% |
data_type和format_type参数取值如下:
SELECT DATE_FORMAT(NOW(),'%H:%i:%s'),
STR_TO_DATE('09/01/2009','%m/%d/%Y'),
STR_TO_DATE('20190522154706','%Y%m%d%H%i%s'),
STR_TO_DATE('2014-04-22 15:47:06','%Y-%m-%d %H:%i:%s');
SELECT GET_FORMAT(DATE, 'USA'),
DATE_FORMAT(NOW(),GET_FORMAT(DATE,'USA')),
STR_TO_DATE('2020-01-01 00:00:00','%Y-%m-%d');
流程控制函数
| 函数 | 用法 |
|---|---|
| IF(value,value1,value2) | 如果value的值为TRUE，返回value1，否则返回value2 |
| IFNULL(value1, value2) | 如果value1不为NULL，返回value1，否则返回value2 |
| CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2 … [ELSE resultn] END | 相当于Java的if…else if…else… |
| CASE expr WHEN 常量值1 THEN 值1 WHEN 常量值1 THEN 值1 … [ELSE 值n] END | 相当于Java的switch…case… |
SELECT IF(2>-1,'TRUE','FALSE');
-- TRUE
SELECT IFNULL(null,'abc');
-- abc
SELECT CASE
WHEN 1>0 THEN '1>0'
WHEN 2>0 THEN '2>0'
ELSE '3>0' END
-- 1>0;
SELECT employee_id,salary, CASE WHEN salary>=15000 THEN '高薪'
WHEN salary>=10000 THEN '潜力股'
WHEN salary>=8000 THEN '屌丝'
ELSE '草根' END "描述"
FROM employees;
SELECT oid,`status`, CASE `status` WHEN 1 THEN '未付款'
WHEN 2 THEN '已付款'
WHEN 3 THEN '已发货'
WHEN 4 THEN '确认收货'
ELSE '无效订单' END
FROM t_order;
SELECT CASE WHEN 1 > 0 THEN 'yes' WHEN 1 <= 0 THEN 'no' ELSE 'unknown' END;
SELECT CASE WHEN 1 < 0 THEN 'yes' WHEN 1 = 0 THEN 'no' ELSE 'unknown' END;
SELECT CASE 1 WHEN 0 THEN 0 WHEN 1 THEN 1 ELSE -1 END;
SELECT CASE -1 WHEN 0 THEN 0 WHEN 1 THEN 1 ELSE -1 END;
SELECT employee_id,12 * salary * (1 + IFNULL(commission_pct,0))
FROM employees;
SELECT last_name, job_id, salary,
CASE job_id WHEN 'IT_PROG' THEN 1.10*salary
WHEN 'ST_CLERK' THEN 1.15*salary
WHEN 'SA_REP' THEN 1.20*salary
ELSE salary END "REVISED_SALARY"
FROM employees;
加密与解密函数
| 函数 | 用法 |
|---|---|
| PASSWORD(str) | 返回字符串str的加密版本，41位长的字符串。加密结果 不可逆 ，常用于用户的密码加密 |
| MD5(str) | 返回字符串str的md5加密后的值，也是一种加密方式。若参数为NULL，则会返回NULL |
| SHA(str) | 从原明文密码str计算并返回加密后的密码字符串，当参数为NULL时，返回NULL。 SHA加密算法比MD5更加安全 。 |
| ENCODE(value,password_seed) | 返回使用password_seed作为加密密码加密value |
| DECODE(value,password_seed) | 返回使用password_seed作为加密密码解密value |
SELECT PASSWORD('mysql'), PASSWORD(NULL);
| PASSWORD('mysql') | PASSWORD(NULL) |
+-------------------------------------------+----------------+
| *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA | |
+-------------------------------------------+----------------+
SELECT md5('123');
43cca4b3de2097b9558efefd0ecc3588
SELECT SHA('Tom123')
->c7c506980abc31cc390a2438c90861d0f1216d50
SELECT ENCODE('mysql', 'mysql');
+--------------------------+
| ENCODE('mysql', 'mysql') |
+--------------------------+
| íg ¼ ìÉ |
+--------------------------+
1 row in set, 1 warning (0.01 sec)
mysql> SELECT DECODE(ENCODE('mysql','mysql'),'mysql');
+-----------------------------------------+
| DECODE(ENCODE('mysql','mysql'),'mysql') |
+-----------------------------------------+
| mysql |
+-----------------------------------------+
1 row in set, 2 warnings (0.00 sec)
MySQL信息函数
| 函数 | 用法 |
|---|---|
| VERSION() | 返回当前MySQL的版本号 |
| CONNECTION_ID() | 返回当前MySQL服务器的连接数 |
| DATABASE()，SCHEMA() | 返回MySQL命令行当前所在的数据库 |
| USER()，CURRENT_USER()、SYSTEM_USER()，SESSION_USER() | 返回当前连接MySQL的用户名，返回结果格式为“主机名@用户名” |
| CHARSET(value) | 返回字符串value自变量的字符集 |
| COLLATION(value) | 返回字符串value的比较规则 |
SELECT DATABASE(),USER(),CURRENT_USER(),SYSTEM_USER(),SESSION_USER(),CHARSET('ABDC'),
COLLATION('ABCD');
其他函数
| 函数 | 用法 |
|---|---|
| FORMAT(value,n) | 返回对数字value进行格式化后的结果数据。n表示 四舍五入 后保留到小数点后n位 |
| CONV(value,from,to) | 将value的值进行不同进制之间的转换 |
| INET_ATON(ipvalue) | 将以点分隔的IP地址转化为一个数字 |
| INET_NTOA(value) | 将数字形式的IP地址转化为以点分隔的IP地址 |
| BENCHMARK(n,expr) | 将表达式expr重复执行n次。用于测试MySQL处理expr表达式所耗费的时间 |
| CONVERT(value USING char_code) | 将value所使用的字符编码修改为char_code |
# 如果n的值小于或者等于0，则只保留整数部分
mysql> SELECT FORMAT(123.123, 2), FORMAT(123.523, 0), FORMAT(123.123, -2);
+--------------------+--------------------+---------------------+
| FORMAT(123.123, 2) | FORMAT(123.523, 0) | FORMAT(123.123, -2) |
+--------------------+--------------------+---------------------+
| 123.12 | 124 | 123 |
+--------------------+--------------------+---------------------+
1 row in set (0.00 sec)
mysql> SELECT CONV(16, 10, 2), CONV(8888,10,16), CONV(NULL, 10, 2);
+-----------------+------------------+-------------------+
| CONV(16, 10, 2) | CONV(8888,10,16) | CONV(NULL, 10, 2) |
+-----------------+------------------+-------------------+
| 10000 | 22B8 | NULL |
+-----------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> SELECT INET_ATON('192.168.1.100');
+----------------------------+
| INET_ATON('192.168.1.100') |
+----------------------------+
| 3232235876 |
+----------------------------+
1 row in set (0.00 sec)
# 以“192.168.1.100”为例，计算方式为192乘以256的3次方，加上168乘以256的2次方，加上1乘以256，再加上
100。
mysql> SELECT INET_NTOA(3232235876);
+-----------------------+
| INET_NTOA(3232235876) |
+-----------------------+
| 192.168.1.100 |
+-----------------------+
1 row in set (0.00 sec)
mysql> SELECT BENCHMARK(1, MD5('mysql'));
+----------------------------+
| BENCHMARK(1, MD5('mysql')) |
+----------------------------+
| 0 |
+----------------------------+
1 row in set (0.00 sec)
mysql> SELECT BENCHMARK(1000000, MD5('mysql'));
+----------------------------------+
| BENCHMARK(1000000, MD5('mysql')) |
+----------------------------------+
| 0 |
+----------------------------------+
1 row in set (0.10 sec)
mysql> SELECT CHARSET('mysql'), CHARSET(CONVERT('mysql' USING 'utf8'));
+------------------+----------------------------------------+
| CHARSET('mysql') | CHARSET(CONVERT('mysql' USING 'utf8')) |
+------------------+----------------------------------------+
| utf8mb4 | utf8 |
+------------------+----------------------------------------+
1 row in set, 1 warning (0.00 sec)
第八章 聚合函数
语法
注意不能嵌套使用
常用的几个
AVG和SUM函数
SELECT AVG(salary), MAX(salary),MIN(salary), SUM(salary)
FROM employees
WHERE job_id LIKE '%REP%';
MIN和MAX函数
SELECT MIN(hire_date), MAX(hire_date)
FROM employees;
COUNT函数
count(*)返回表中记录总数，适用于任意数据类型
count(expr)返回expr不为空的记录总数
二者谁好呢?
对于MyISAM引擎没有区别,对于Innodb引擎的表用count(*),count(1)直接读取行数，复杂度是O(n)
能不能使用count(列名)替换count()?
不能,count()是SQL92定义的标准统计行数的语法,跟数据库无关，跟NULL和非NULL无关
说明：count(*)会统计值为 NULL 的行，而 count(列名)不会统计此列为 NULL 值的行。
GROUP BY
比如要求出EMPLOYEES表中各部门的平均工资
语法:
SELECT column, group_function(column)
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[ORDER BY column];
WHERE 一定放在FROM后面,并且SELECT列表中所有未包含在组函数中的列都应该包含在GROUP BY子句中,包含在GROUP BY子句中的列不必包含在SELECT列表中
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id ;
-- department_id没有包含在组函数中，但是在GROUP BY当中
使用多个列进行分组
(若列1相同，看列2，都相同归为一组，一个不同，归为多组)
SELECT department_id dept_id, job_id, SUM(salary)
FROM employees
GROUP BY department_id, job_id ;
使用WITH ROLLUP
查询出的分组记录之后增加一条记录:计算查询出的所有记录的总和
SELECT department_id,AVG(salary)
FROM employees
WHERE department_id > 80
GROUP BY department_id WITH ROLLUP;
HAVING
这东西必须和GROUP BY一起使用
统计部门最高工资比1000高的部门
SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary)>10000 ;
WHERE 和HAVING的对比
- HAVING必须要与GROUP BY配合使用，可以把分组计算的函数和分组字段作为筛选条件,而WHERE 可以把表中的字段作为筛选条件，但是不能使用分组中的计算函数作为筛选条件
- WHERE是先筛选后连接,而HAVING是先连接后筛选
| 优点 | 缺点 | |
|---|---|---|
| WHERE | 先筛选数据再关联，执行效率高 | 不能使用分组中的计算函数进行筛选 |
| HAVING | 可以使用分组中的计算函数 | 在结果中集中进行筛选，效率低 |
SELECT执行顺序
关键字的顺序是不能颠倒的
SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY … LIMIT …
SELECT语句执行的顺序
FROM <left_table>
ON <join_condition>
<join_type> JOIN <right_table>
WHERE <where_condition>
GROUP BY <group_by_list>
HAVING <having_condition>
SELECT
DISTINCT <select_list>
ORDER BY <order_by_condition>
LIMIT <limit_number>
sql语句如下:
SELECT DISTINCT player_id, player_name, count(*) as num # 顺序 5
FROM player JOIN team ON player.team_id = team.team_id # 顺序 1
WHERE height > 1.80 # 顺序 2
GROUP BY player.team_id # 顺序 3
HAVING num > 2 # 顺序 4
ORDER BY num DESC # 顺序 6
LIMIT 2 # 顺序 7
每一步都产生了一个虚拟表，把这个虚拟表传入下一个步骤作为输入，对我们来说是不可见的
SQL的执行原理
SELECT 是先执行 FROM 这一步的。在这个阶段，如果是多张表联查，还会经历下面的几个步骤：
- 首先先通过 CROSS JOIN 求笛卡尔积，相当于得到虚拟表 vt（virtual table）1-1；
- 通过 ON 进行筛选，在虚拟表 vt1-1 的基础上进行筛选，得到虚拟表 vt1-2；
- 添加外部行。如果我们使用的是左连接、右链接或者全连接，就会涉及到外部行，也就是在虚拟
表 vt1-2 的基础上增加外部行，得到虚拟表 vt1-3。
当然如果我们操作的是两张以上的表，还会重复上面的步骤，直到所有表都被处理完为止。这个过程得
到是我们的原始数据。
SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY … LIMIT…
FROM -> WHERE -> GROUP BY -> HAVING -> SELECT 的字段 -> DISTINCT -> ORDER BY -> LIMIT
SELECT DISTINCT player_id, player_name, count(*) as num # 顺序 5
FROM player JOIN team ON player.team_id = team.team_id # 顺序 1
WHERE height > 1.80 # 顺序 2
GROUP BY player.team_id # 顺序 3
HAVING num > 2 # 顺序 4
ORDER BY num DESC # 顺序 6
LIMIT 2 # 顺序 7
当我们拿到了查询数据表的原始数据，也就是最终的虚拟表 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 语句的时候，还要注意相应的
关键字顺序，所谓底层运行的原理，就是我们刚才讲到的执行顺序。
第九章 子查询
就是嵌套查询
基本使用
SELECT select_list
FROM table
WHERE expr operator
(SELECT select_list
FROM table);
- 子查询在主查询之前一次执行完成
- 子查询的结果被主查询(外查询)使用
- 子查询要包含在括号内
- 子查询要放在比较条件的右侧
- 单行操作符->单行子查询;多行操作符->多行子查询
分类
按 返回的结果是一条数据还是多条数据来分
单行子查询
这个最简单
操作符就是哪些等于，小于(等于)哪些东西,我不记录
- 查询工资大于149号员工工资的员工信息
// 主查询是查询员工信息的，条件是工资大于149号的
// 子查询是查询149号员工的工资的
SELECT last_name
FROM employees
WHERE salary >
(SELECT salary
FROM employees
WHERE employee_id = 143);
- 返回job_id与141号员工相同,salary比143号员工多的员工姓名,job_id和工资
SELECT last_name,job_id,salary
FROM employees
WHERE job_id =
(SELECT job_id FROM employees WHERE employee_id = 141)// 找到141号员工的job_id
AND
salary >
(SELECT salary FROM employees
WHERE employee_id = 143);//找到143号员工的工资
- 返回工资最少的员工的last_name,job_id和salary
SELECT last_name, job_id, salary
FROM employees
WHERE salary =
(SELECT MIN(salary)
FROM employees);
- 查询与141号或174号员工的manager_id和department_id相同的其他员工的employee_id，
manager_id，department_id
//方式1:
SELECT employee_id, manager_id, department_id
FROM employees
WHERE manager_id IN
(SELECT manager_id
FROM employees
WHERE employee_id IN (174,141))
AND department_id IN
(SELECT department_id
FROM employees
WHERE employee_id IN (174,141))
AND employee_id NOT IN(174,141);
//方式2:成对比较
SELECT employee_id, manager_id, department_id
FROM employees
WHERE (manager_id, department_id) IN
(SELECT manager_id, department_id
FROM employees
WHERE employee_id IN (141,174))
AND employee_id NOT IN (141,174);
HAVING中的子查询
- 首先执行子查询
- 向主查询中的HAVING子句返回结果
- 查询最低工资大于50号部门最低工资的部门id和其最低工资
// 描述50号部门最低工资
SELECT department_id, MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) >
(SELECT MIN(salary)
FROM employees
WHERE department_id = 50);
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;
子查询的空值问题
不返回任何行
多行子查询
就是子查询返回多行，使用多行比较符
比较操作符
| 操作符 | 含义 |
|---|---|
| IN | 等于列表中的任意一个 |
| ANY | 需要和单行比较操作符一起使用，和子查询返回的某一个值比较 |
| ALL | 需要和单行比较操作符一起使用，和子查询返回的所有值比较 |
| SOME | 实际上是ANY的别名，作用相同，一般常使用ANY |
- 返回其它job_id中比job_id为‘IT_PROG’部门
任一工资低的员工的员工号、姓名、job_id 以及salary
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary < ANY (SELECT salary FROM employees WHERE job_id = 'IT_PROG')
AND job_id!='IT_PROG';
- 返回其它job_id中比job_id为‘IT_PROG’部门所有工资都低的员工的员工号、姓名、job_id以及salary
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary < ALL (SELECT salary FROM employees WHERE job_id = 'IT_PROG')
AND job_id!='IT_PROG';
- 查询平均工资最低的部门id
//方式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
) 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)
);
按按内查询是否被执行多次来分
关联子查询
子查询依赖主查询，一般是子查询中使用了主查询的表中的某些列，并且对这些列做了条件关联。
执行一次外部查询，子查询需要重新计算一次
- 查询员工中工资大于本部门平均工资的员工的last_name,salary和其department_id
// 方式1:
SELECT last_name,salary,department_id
FROM employees e
WHERE salary > (SELECT AVG(salary)
FROM employees
WHERE department_id = e.department_id);
//方式2:在FROM中使用子查询
SELECT last_name,salary,e1.department_id
FROM employees e1,
(SELECT department_id,AVG(salary) dept_avg_sal FROM employees
GROUP BY department_id) e2
WHERE e1.department_id = e2.department_id
AND e2.dept_avg_sal < e1.salary;
//也可以写成这样
SELECT last_name,salary,e1.department_id
FROM employees e1 JOIN (SELECT department_id,AVG(salary) dept_avg_sal FROM employees
GROUP BY department_id) e2
ON e1.department_id = e2.department_id
AND e2.dept_avg_sal < e1.salary;
- 查询员工的id,salary,按照department_name 排序
SELECT employee_id,salary
FROM employees e
ORDER BY (
SELECT department_name
FROM departments d
WHERE e.`department_id` = d.`department_id`
);
- 若employees表中employee_id与job_history表中employee_id相同的数目不小于2，输出这些相同id的员工的employee_id,last_name和其job_id
SELECT e.employee_id, last_name,e.job_id
FROM employees e
WHERE 2 <= (SELECT COUNT(*)
FROM job_history
WHERE employee_id = e.employee_id);
EXISTS与NOT EXISTS关键字
检查在子查询中是否存在满足条件的行，
如果在子查询中不存在满足条件的行，NOT EXISTS返回true，EXISTS返回false
如果在子查询中存在满足条件的行，NOT EXISTS返回false，EXISTS返回true.
- 查询公司管理者的employee_id，last_name，job_id，department_id信息
// 方式1:
SELECT employee_id, last_name, job_id, department_id
FROM employees e1
WHERE EXISTS ( SELECT *
FROM employees e2
WHERE e2.manager_id =
e1.employee_id);
//方式2:自连接
SELECT DISTINCT e1.employee_id, e1.last_name, e1.job_id, e1.department_id
FROM employees e1
JOIN employees e2
WHERE e1.employee_id = e2.manager_id;
//方式3:
SELECT employee_id,last_name,job_id,department_id
FROM employees
WHERE employee_id IN (
SELECT DISTINCT manager_id
FROM employees
);
- 查询departments表中，不存在于employees表中的部门的department_id和department_name
SELECT department_id, department_name
FROM departments d
WHERE NOT EXISTS (SELECT 'X'
FROM employees
WHERE department_id = d.department_id);
不相关子查询
相关更新
- 在employees中增加一个department_name字段，数据为员工对应的部门名称
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);
相关删除
- 删除表employees中，其与emp_history表皆有的数据
DELETE FROM employees e
WHERE employee_id in
(SELECT employee_id
FROM emp_history
WHERE employee_id = e.employee_id);
自连接与子查询效率谁好
自连接好，子查询实际上是通过未知表进行查询后的条件判断，而自连接是通过已知的自身数据表
进行条件判断，因此在大部分 DBMS 中都对自连接处理进行了优化。
1094
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
