尚硅谷MySQL笔记 3-9

我不会记录的特别详细

大体框架

• 基本的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的所有部门都列出来，格式如下:

可以这么写

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)

位运算符

我的理解就是把操作数转换为补码按照指定的运算符的规则进行运算，再把结果转换为十进制数字
支持的运算符

解释: 以 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次幂来用
就是乘以了2ⁿ,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支持的

1. 查询以特定字符或字符串开头的记录:
   在name列查询以ea开头的记录

SELECT * FROM `user` where name REGEXP '^ea';

2. 查询以特定字符或字符串结尾的记录:
   在name列查询以or结尾的记录

SELECT * FROM `user` where name REGEXP 'or$';

3. 用符号’.'通配一个字符,查询name列字段值中包含ea且e和a之间只有一个字母的记录

SELECT * FROM `user` where name REGEXP 'e.a';

4. 用’*‘和’+'匹配多个字符:在name列中查找以字母c开头且c后面出现字母e至少一次的记录

SELECT * FROM `user` where name REGEXP '^ce+';

5. 匹配指定字符串,可以匹配多个

• 在name列中查找包含ea的记录

SELECT * FROM `user` WHERE name REGEXP 'ea';

• 在name列中查找包含ea和or的记录

SELECT * FROM `user` WHERE name REGEXP 'ea | or';

6. 匹配指定字符中任意一个,就是方括号的用法
   在name列中查找包含字母abced和 wxy的记录

SELECT * FROM `user` WHERE name REGEXP '[a-dw-y]

7. 匹配指定字符以外的字符 "[^字符集合]"匹配不在指定集合中的任何字符
   在name列中查找id字符串包含a-h和数字1-8以外字符的记录

SELECT * FROM `user` WHERE id REGEXP '[^a-h1-8]';

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的

job_grades

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一起使用，并且要求两个关联字段在关联表中名称一致，而且只能表示关联字段值相等,这个应用比较窄

第七章 单行函数

数值函数

基本函数

弧度制与角度制的转换
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;

三角函数

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;

指数与对数

进制转换类

字符串函数

字符串起始位置从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;

日期和时间函数

获取日期和时间

SELECT CURRENT_DATE(),
       CURRENT_TIME(),
       SYSDATE(),
       UTC_DATE(),
       UTC_TIME()
    FROM dual;

日期与时间戳的转换

SELECT UNIX_TIMESTAMP(SYSDATE()),
       UNIX_TIMESTAMP(CURDATE()),
       UNIX_TIMESTAMP(CURTIME()),
       UNIX_TIMESTAMP('2023-8-19 10:43:10')
       FROM dual;

获取月份、星期、星期数、天数等函数

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;

日期与操作函数

SELECT EXTRACT(MINUTE FROM NOW()),EXTRACT( DAY FROM NOW()),
EXTRACT( QUARTER FROM NOW()),EXTRACT( DAY_SECOND FROM NOW())
FROM DUAL;

时间和秒钟转换的函数

SELECT TIME_TO_SEC(SYSDATE()),SEC_TO_TIME(7878)
FROM DUAL;

计算日期和时间的函数

第一组

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组:

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;

日期的格式化与解析

常用的fmt的格式符

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');

流程控制函数

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;

加密与解密函数

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信息函数

SELECT DATABASE(),USER(),CURRENT_USER(),SYSTEM_USER(),SESSION_USER(),CHARSET('ABDC'),
       COLLATION('ABCD');

其他函数

# 如果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是先连接后筛选

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 这一步的。在这个阶段，如果是多张表联查，还会经历下面的几个步骤：

1. 首先先通过 CROSS JOIN 求笛卡尔积，相当于得到虚拟表 vt（virtual table）1-1；
2. 通过 ON 进行筛选，在虚拟表 vt1-1 的基础上进行筛选，得到虚拟表 vt1-2；
3. 添加外部行。如果我们使用的是左连接、右链接或者全连接，就会涉及到外部行，也就是在虚拟
   表 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);

• 子查询在主查询之前一次执行完成
• 子查询的结果被主查询(外查询)使用
• 子查询要包含在括号内
• 子查询要放在比较条件的右侧
• 单行操作符->单行子查询;多行操作符->多行子查询

分类

按 返回的结果是一条数据还是多条数据来分

单行子查询

这个最简单
操作符就是哪些等于，小于(等于)哪些东西,我不记录

1. 查询工资大于149号员工工资的员工信息

// 主查询是查询员工信息的，条件是工资大于149号的
// 子查询是查询149号员工的工资的
SELECT last_name 
FROM employees
WHERE salary >
				(SELECT salary
				FROM employees
				WHERE employee_id = 143);

2. 返回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号员工的工资

3. 返回工资最少的员工的last_name,job_id和salary

SELECT last_name, job_id, salary
FROM employees
WHERE salary =
(SELECT MIN(salary)
FROM employees);

4. 查询与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子句返回结果

5. 查询最低工资大于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中的子查询

1. 显示员工的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. 返回其它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';

2. 返回其它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';

3. 查询平均工资最低的部门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)
);

按按内查询是否被执行多次来分

关联子查询

子查询依赖主查询，一般是子查询中使用了主查询的表中的某些列，并且对这些列做了条件关联。
执行一次外部查询，子查询需要重新计算一次

1. 查询员工中工资大于本部门平均工资的员工的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;

2. 查询员工的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`
);

3. 若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.

1. 查询公司管理者的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
);

2. 查询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);

不相关子查询

相关更新

1. 在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);

相关删除

1. 删除表employees中，其与emp_history表皆有的数据

DELETE FROM employees e
WHERE employee_id in
					(SELECT employee_id
					FROM emp_history
					WHERE employee_id = e.employee_id);

自连接与子查询效率谁好

自连接好，子查询实际上是通过未知表进行查询后的条件判断，而自连接是通过已知的自身数据表
进行条件判断，因此在大部分 DBMS 中都对自连接处理进行了优化。文章来源地址https://www.toymoban.com/news/detail-655820.html

到了这里，关于尚硅谷MySQL笔记 3-9的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！