*本人古早时候sql学习笔记，摘抄自课本书，后续会进行完善；复制到这里方便自己随时查阅
书写语法表及顺序整理
表格 1 各语句语法表
| 创建数据库 | CREATE DATABASE <数据库名称> |
|---|---|
| 创建表 | CREATE TABLE <表名>（<列名1> <数据类型> <该列所需约束>， <列名2> <数据类型> <该列所需约束>，…<该表的约束1>， <该表的约束2>，……） |
| 删除表 | DROP TABLE <表名>； |
| 添加列 | ALTER TABLE <表名> ADD COLUMN <列的定义>; |
| 删除列 | ALTER TABLE <表名> DROP COLUMN <列名>； |
| 基本SELECT | SELECT <列名>，……FROM <表名>； |
| 查询全部的列 | SELECT * FROM <表名>; |
| + WHERE子句 | SELECT <列名>, …… FROM <表名> WHERE <条件表达式>; |
| + GROUP BY子句 | SELECT <列名1>, <列名2>, <列名3>, …… FROM <表名> GROUP BY <列名1>, <列名2>, <列名3>, ……; |
| 以上两者都 | SELECT <列名1>, <列名2>, <列名3>, …FROM <表名> WHERE GROUP BY <列名1>, <列名2>, <列名3>, ……; |
| HAVING子句 | SELECT <列名1>, <列名2>, <列名3>, …… FROM <表名> GROUP BY <列名1>, <列名2>, <列名3>, ……HAVING <分组结果对应的条件> |
| ORDER BY子句 | SELECT <列名1>, <列名2>, <列名3>, ……FROM <表名>ORDER BY <排序基准列1>, <排序基准列2>, …… |
| INSERT语句 | INSERT INTO <表名> (列1, 列2, 列3, ……) VALUES (值1, 值2, 值3, ……); |
| DELETE语句 | DELETE FROM <表名>; |
| 删除部分数据行 | DELETE FROM <表名> WHERE < 条件>; |
| 改变表中数据 | UPDATE <表名> SET <列名> = < 表达式>; |
| 更新部分数据行 | UPDATE <表名> SET <列名> = <表达式> WHERE <条件>; |
| 创建视图 | CREATE VIEW 视图名称(<视图列名1>, <视图列名2>, ……) AS <SELECT语句> |
| 删除视图 | DROP VIEW 视图名称(<视图列名1>, <视图列名2>, ……) |
| 绝对值 | ABS(数值) |
| 取余 | MOD(数值) |
| 四舍五入 | ROUND(对象数值，保留小数的位数) |
| 拼接 | CONCAT(字符串1,字符串2) |
| 字符串长度 | LENGTH(字符串) |
| 转小写 | LOWER(字符串) |
| 替换 | REPLACE(对象字符串，替换前的字符串，替换后的字符串) |
| 截取* | SUBSTRING（对象字符串 FROM 截取的起始位置 FOR 截取的字符数） 从1开始 |
| LEFT(字符串，截取长度) | 从左边截取字符RIGHT 从右边截取字符 |
| 转大写 | UPPER(字符串) |
| 当前日期 | CURRENT_DATE 无参数 |
| 当前时间 | CURRENT_TIME 无参数 |
| 当前日期+时间 | CURRENT_TIMESTAMP 无参数 |
| 截取日期 | EXTRACT(日期元素 FROM 日期) |
| 类型转换 | CAST（转换前的值 AS 想要转换的数据类型） |
| 将NULL转换为其他值 | COALESCE(数据1，数据2，数据3……) |
| 搜索CASE表达式 | CASE WHEN <求值表达式> THEN <表达式> WHEN <求值表达式> THEN <表达式> WHEN <求值表达式> THEN <表达式> . . .ELSE <表达式>END |
| 简单CASE表达式 | CASE <表达式> WHEN <表达式> THEN <表达式> WHEN <表达式> THEN <表达式> WHEN <表达式> THEN <表达式> . . . ELSE <表达式>END |
| 窗口函数 | <窗口函数> OVER ([PARTITION BY <列清单>] ORDER BY <排序用列清单>) |
另：
*: SUBSTRING函数是（PostgreSQL/MySQL专用语法）
书写顺序：SELECT > FROM > JOIN > ON > WHERE > GROUP BY > HAVING > UNION > ORDER BY > LIMIT >;
执行顺序：FROM > ON > JOIN > WHERE > GROUP BY > HAVING > SELECT > UNION > ORDER BY > LIMIT >;
此外，无论是执行顺序还是书写顺序，UNION都是在 ORDER BY 前面，SQL语句会把所有UNION合并后，在进行排序。
零、基础概念
0.1 数据库
数据库DB, database 数据库管理系统DBMS, Database Management System
DBMS分为好几类，包括层次数据库、关系数据库*等
关系数据库RDB, Relational Database 行与列组成的二维表来管理数据，使用专用sql, structured query language对数据进行操作
关系数据库管理系统RDBMS，代表的包括oracle、sql server、mysql、PostgreSql等
客户端<—sql语句—>服务器RDBMS<——>数据库
管理数据的二维表在RDB中称为表，表的列是字段，行是记录。RDB以行为单位进行数据读写。
0.1.1 外键
外键是一种索引，是通过一张表中的一列指向另一张表的主键，使得这两张表产生关联；是某个表中的一列，它包含在另一个表的主键中；一张表中可以有一个外键也可以有多个外键。
外键的作用是减少单张表的冗余信息。使用外键后想要删除/更新主表的相关列会报错，因为从表正在使用。使用外键后若想向从表中插入新列，但是外键值主表中没有，会报错，更新从表的外键值，但是值不在从表中会报错。
创建表时添加外键：
CONSTRAINT 约束名 FOREIGN KEY(字段名) REFERENCES 主表（被引用列）
[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
ASCADE：父表delete、update的时候，子表会delete、update掉关联记录；
SET NULL：父表delete、update的时候，子表会将关联记录的外键字段所在列设为null，所以注意在设计子表时外键不能设为not null；
RESTRICT：如果想要删除父表的记录时，而在子表中有关联该父表的记录，则不允许删除父表中的记录；
NO ACTION：同 RESTRICT，也是首先先检查外键；
其中跟随外键改动是：删除主表中，外键行时，从表相关行也删除。更新主表中外键相关行时，从表的使用行也改变
若，事触发限制为默认值，限制外表中外键的改动，则在添加外键之后对表中数据的增删改查都需要注意
0.1.2 内存表or磁盘表
临时表既可以在内存中，也可以在磁盘上。内存临时表使用的是Memory引擎。磁盘临时表使用的是innodb引擎。
MySQL默认会提供一个内存临时表来存放分组的数据，但是当临时表的大小超过16MB的时候，会分组数据存放到磁盘中。
这样的话，需要额外的磁盘IO的开销。
0.2 数据库连接
0.2.1 端口
一、关系数据库：
1、MySql数据库，默认端口为： 3306；
2、Oracle数据库，默认端口号为1521；
3、Sql Server数据库，默认端口号为1433；
4、DB2数据库，默认端口号为5000；
5、PostgreSQL数据库，缺省端口号为5432；
6、国产的DM达梦数据库，默认端口号为5236。
二. NoSql数据库(非关系数据库) :
- Redis数据库，缺省端口号：6379
- Memcached数据库，缺省端口号：11211
- MongoDB数据库，缺省端口号：27017；
三、如何查看端口号(windows ) ) ) )
1 .打开以下窗口，输入cmd进行确认。
2 .在命令行中，输入“netstat -ano|findstr端口号”将显示下图中的LISTENING，表示正在使用此端口。
【备注】:在计算机上安装上述数据库后，可以将端口号更改为其他。 当然，建议使用默认端口号。
【续】:后期补充了其他数据库的默认端口号。
0.2.2 主机名
在MySQL中，主机名是指连接到数据库服务器的计算机的名称或IP地址。主机名主要用于控制数据库的访问权限，它可以在MySQL服务器的配置文件、GRANT语句和命令行选项中设置。在使用MySQL中的主机名时，需要注意主机名的格式和安全性。正确配置主机名可以确保数据库的安全，避免未经授权的访问。
- 主机名的概念
在MySQL中，主机名主要用于控制数据库的访问权限。当用户尝试连接到MySQL服务器时，该服务器会检查主机名以确定用户是否可以访问特定数据库。如果用户的主机名与某个MySQL服务器的主机名匹配，则用户将被授予访问权限，否则他将被拒绝访问。
主机名的设置通常在MySQL服务器的配置文件（my.cnf）中完成。在这个文件中，可以指定允许连接到服务器的主机名，也可以禁止某些主机名连接到服务器。以下是一个典型的MySQL服务器配置文件例子：
my.cnf
[mysqld]
bind-address = 127.0.0.1
port = 3306
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
log-error = /var/log/mysql/mysql-error.log
socket = /var/run/mysqld/mysqld.sock
在这个文件中，bind-address参数指定了服务器监听的IP地址，也可以设置为0.0.0.0以便允许来自任何IP地址的连接。port参数指定了服务器监听的端口号，user参数指定了服务器运行的用户，pid-file参数指定了服务器使用的进程ID文件，log-error参数指定了服务器使用的错误日志文件，socket参数指定了服务器使用的套接字文件。 - 主机名的设置
在MySQL中设置主机名的方法有很多种。以下是其中的一些方法：
2.1. 通过修改my.cnf文件
在MySQL服务器的my.cnf文件中设置主机名是非常常见的一种方法。在这个文件中，可以通过指定以下参数来设置主机名：
my.cnf
[mysqld]
skip-name-resolve
bind-address = 0.0.0.0
…
skip-name-resolve参数用于禁止MySQL服务器使用DNS查询来解析主机名，这将提高MySQL服务器的性能但降低安全性。bind-address参数用于指定要监听的IP地址，可以设置为主机名或者是IP地址。
2.2 通过GRANT语句设置主机名
可以使用MySQL中的GRANT语句授予用户访问MySQL服务器的权限，也可以通过此语句指定要允许的主机名。以下是一个使用GRANT语句设置主机名的例子：
GRANT ALL PRIVILEGES ON . TO ‘user’@’192.168.1.100’ IDENTIFIED BY ‘password’;
在这个例子中，’user’是用户的用户名，’password’是用户的密码，’192.168.1.100’是要允许连接到服务器的主机名。
2.3 通过命令行选项设置主机名
在启动MySQL服务器时，可以使用命令行选项来指定要允许的主机名。以下是一个使用命令行选项设置主机名的例子：
/usr/local/mysql/bin/mysqld –bind-address=192.168.1.100
在这个例子中，–bind-address选项用于指定要监听的IP地址或主机名。 - 使用主机名的注意事项
使用MySQL中的主机名时，需要注意以下几点：
3.1 主机名的格式
在MySQL中，主机名的格式必须符合以下格式之一：
主机名
主机名通配符%
IP地址
IP地址通配符%
其中，主机名可以是服务器的本地主机名或另一个连接到服务器的远程主机名。主机名通配符%表示允许该主机名的所有子域名连接到服务器。IP地址可以是一个有效的IP地址或者IP地址通配符%表示允许该IP地址的所有子网连接到服务器。
3.2 主机名的安全性
在MySQL中，正确配置主机名是确保数据库访问安全的关键。MySQL服务器应该只允许通过需要访问MySQL服务器的主机名连接到服务器，并定期检查和更新允许连接的主机名列表，以防止未经授权的访问。
一、使用数据库
1.1 创建表和数据库
1.1.1 创建数据库
CREATE DATABASE database_name
在MySQL命令行中执行
注：在sqlyog中创建时除了库名可以指定基字符集和数据库排序规则。
1.2 删除和更新表
● 使用DROP TABLE语句来删除表。
● 使用ALTER TABLE语句向表中添加列或者从表中删除列。
删除了的表是无法恢复的。在执行DROP TABLE语句之前请务必仔细确认。
Oracle和SQL Server中不用写COLUMN。
二、聚合与排序
2.1 聚合
使用聚合函数对表中的列进行计算合计值或者平均值等的汇总操作。通常，聚合函数会对NULL以外的对象进行汇总。但是只有COUNT函数
例外，使用COUNT（*）可以查出包含NULL在内的全部数据的行数。
使用DISTINCT关键字删除重复值。
常用的函数：
用于汇总的函数称为聚合函数或者聚集函数。所谓聚合，就是将多行汇总为一行。实际上，所有的聚合函数都是这样，输入多行输出一行。
COUNT函数的结果根据参数的不同而不同。COUNT()会得到包含NULL的数据行数，而COUNT(<列名>)会得到NULL之外的数据行数。
聚合函数会将NULL排除在外。但COUNT()例外，并不会排除NULL。
MAX/MIN函数几乎适用于所有数据类型的列。SUM/AVG函数只适用于数值类型的列。
想要计算值的种类时，可以在COUNT函数的参数中使用DISTINCT。
在聚合函数的参数中使用DISTINCT，可以删除重复数据。
2.2 分组
使用GROUP BY子句可以像切蛋糕那样将表分割。通过使用聚合函数和GROUP BY子句，可以根据“商品种类”或者“登记日期”等将表分割后再进行汇总。
聚合键中包含NULL时，在结果中会以“不确定”行（空行）的形式表现出来。
使用聚合函数和GROUP BY子句时需要注意以下 4点。① 只能写在SELECT子句之中；② GROUP BY子句中不能使用SELECT子句中列的别名；③ GROUP BY子句的聚合结果是无序的；④ WHERE子句中不能使用聚合函数。
2.2.1 GROUP BY子句
使用group by会对查询得到的表进行切分。在 GROUP BY 子句中指定的列称为聚合键或者分组列。
一定要写在FROM 语句之后（如果有 WHERE 子句的话需要写在 WHERE 子句之后）。GROUP BY 子句也和 SELECT 子句一样，可以通过逗号分隔指定多列。聚合键中包含NULL时，在结果中会以“不确定”行（空行）的形式表现出来。
注意项：
使用GROUP BY子句时，SELECT子句中不能出现聚合键之外的列名。（其他的列名与聚合建不一定是一对一的）；
在GROUP BY子句中不能使用SELECT子句中定义的别名。
GROUP BY子句结果的显示是无序的。
只有SELECT子句和HAVING子句（以及ORDER BY子句）中能够使用聚合函数。
2.3 聚合结果指定条件
使用COUNT函数等对表中数据进行汇总操作时，为其指定条件的不是WHERE子句，而是HAVING子句。
聚合函数可以在SELECT子句、HAVING子句和ORDER BY子句中使用。
HAVING子句要写在GROUP BY子句之后。
WHERE子句用来指定数据行的条件，HAVING子句用来指定分组的条件。
2.3.1 HAVING子句
HAVING 子句必须写在 GROUP BY 子句之后，其在 DBMS 内部的执行顺序也排在 GROUP BY 子句之后。对集合指定条件的子句。HAVING 子句中能够使用的 3 种要素如下所示。常数、聚合函数、GROUP BY子句中指定的列名（即聚合键）
2.3.2 相对于HAVING更适合写在WHERE子句，子句中的条件
WHERE 子句 = 指定行所对应的条件
HAVING 子句 = 指定组所对应的条件
就是一个是在group之前做的，一个是之后?
聚合键所对应的条件不应该书写在HAVING子句当中，而应该书写在WHERE子句当中。
当需要对group by统计的信息进行条件过滤的时候，需要用having关键字，而不是where。相较于where，having加入了聚集函数，比如sum()、avg()、max()、count()。having是where的一个超集，having是对分组进行过滤，在分组之后进行。where是在分组前过滤，在分组之前进行。
select num from MyNumbers group by num having count(*)=1 DESC limit 1
2.4 查询结果排序
使用ORDER BY子句对查询结果进行排序。
在ORDER BY子句中列名的后面使用关键字ASC可以进行升序排序，使用DESC关键字可以进行降序排序。
ORDER BY子句中可以指定多个排序键。
排序健中包含NULL时，会在开头或末尾进行汇总。
ORDER BY子句中可以使用SELECT子句中定义的列的别名。
ORDER BY子句中可以使用SELECT子句中未出现的列或者聚合函数。
ORDER BY子句中不能使用列的编号。
2.4.1 ORDER BY子句
从表中抽取数据时，如果没有特别指定顺序，最终排列顺序便无从得知。即使是同一条 SELECT 语句，每次执行时排列顺序很可能发生改变。但是不进行排序，很可能出现结果混乱的情况。这时，便需要通过在SELECT 语句末尾添加 ORDER BY 子句来明确指定排列顺序。ORDER BY子句中书写的列名称为排序键。
ORDER BY子句通常写在SELECT语句的末尾。
在列名后添加关键字指明排序：ASC ascendent升序，DESC descendent降序（以列为单位）。
未指定ORDER BY子句中排列顺序时会默认使用升序进行排列。
可指定多个排序键，规则是优先使用左侧的键，如果该列存在相同值的话，再接着参考右侧的键。
排序键中包含NULL时，会在开头或末尾进行汇总。
在ORDER BY子句中可以使用SELECT子句中定义的别名。
在ORDER BY子句中可以使用SELECT子句中未使用的列和聚合函数。
在ORDER BY子句中不要使用列编号。
注意：如果order by的列有相同的值时，mysql会随机选取这些行。为了保证每次都返回的顺序一致可以额外增加一个排序字段。
2.4.2 Limit
LIMIT 语句可以被用于强制 SELECT 语句返回指定的记录数。LIMIT 接受一个或两个数字参数。
select * from table_name limit [offset，] rows
参数必须是一个整数常量。参数说明：
offset：指定第一个返回记录行的偏移量（即从哪一行开始返回），注意：初始行的偏移量为0。
rows：返回具体行数。
总结：如果limit后面是一个参数，就是检索前多少行。如果limit后面是2个参数，就是从offset+1行开始，检索rows行记录。
select * from table_name limit 10;//检索前10行记录
select * from table_name limit 5 ，10;//从第6行开始，检索10行记录，即：检索记录行 6-15。
三、数据更新
3.1 插入
3.1.1 基础语句
INSERT INTO <表名> (列1, 列2, 列3, ……) VALUES (值1, 值2, 值3, ……);
将列名和值用逗号隔开，分别括在（）内，这种形式称为清单。
mysql> INSERT INTO t_goods (t_category_id, t_category, t_name, t_price, t_stock, t_upper_time)
-> VALUES
-> (1, '女装/女士精品', '连衣裙', 79.90, 2500, '2020-11-10 00:00:00');
指定了向t_goods数据表中的哪些字段插入数据，由于数据表中的id列是整数类型并且是AUTO_INCREMENT自增类型的，所以可以不用指定id列进行数据插入。
对于新记录中未指定的字段数据，将插入定义数据表时指定的默认值。【在创建表的CREATE TABLE 语句中设置 DEFAULT 约束来设定默认值：形式“DEFAULT < 默认值 >】，也可在 VALUES 子句中指定 DEFAULT 关键字。
原则上，执行一次 INSERT 语句会插入一行数据 C。因此，插入多行时，通常需要循环执行相应次数的 INSERT 语句。
对表进行全列 INSERT 时，可以省略表名后的列清单。这时 VALUES子句的值会默认按照从左到右的顺序赋给每一列。
INSERT 语句中想给某一列赋予 NULL 值时，可以直接在 VALUES子句的值清单中写入 NULL。但是，想要插入 NULL 的列一定不能设置 NOT NULL 约束。向设置了 NOT NULL 约束的列中插入 NULL 时，INSERT 语句会出错，导致数据插入失败。插入失败指的是希望通过 INSERT 语句插入的数据无法正常插入到表中，但之前已经插入的数据并不会被破坏。
3.1.2 一次插入多条数据记录
INSERT INTO table_name (column1 [, column2, …, columnn]) VALUES (value1 [,value2, …, valuen]), (value1 [,value2, …, valuen]),…… (value1 [,value2, …, valuen])
注意：向MySQL数据表中插入多条数据记录且不指定插入数据的字段时，插入数据的值列表同样需要与数据表中的字段类型和字段顺序保持一致，并且值列表需要对应数据表中的所有字段。
3.1.3 将查询结果插入另一个表中INSERT … SELECT语句
INSERT INTO target_table (tar_column1 [, tar_column2, …, tar_columnn]) SELECT (src_column1 [, src_column2, …, src_columnn]) FROM source_table [WHERE condition]
该 INSERT 语句中的 SELECT 语句，也可以使用 WHERE 子句或者GROUP BY 子句等。
3.2 删除
如果想将整个表全部删除，可以使用DROP TABLE语句，如果只想删除表中全部数据(会留下表’容器’)，需使用DELETE语句。
如果想删除部分数据行，只需在WHERE子句中书写对象数据的条件即可。通过WHERE子句指定删除对象的DELETE语句称为搜索型DELETE语句。
3.2.1 DELETE语句的基本语法
DELETE FROM <表名>;
可以通过WHERE子句指定对象条件来删除部分数据。
DELETE FROM <表名> WHERE <条件>;
3.2.2表的删除
DROP TABLE <表名>；
删除了的表是无法恢复的。
TRUNCATE <表名>;
删除表中全部数据，处理速度比 DELETE 要快得多。（舍弃）
3.3 更新UPDATE
● 使用UPDATE语句可以更改（更新）表中的数据。
● 更新部分数据行时可以使用WHERE来指定更新对象的条件。通过WHERE子句指定更新对象的UPDATE语句称为搜索型UPDATE语句。
● UPDATE语句可以将列的值更新为NULL。
● 同时更新多列时，可以在UPDATE语句的SET子句中，使用逗号分隔更新对象的多个列。
3.3.1 基本语法
UPDATE <表名> SET <列名> = <表达式>;
将更新对象的列和更新后的值都记述在 SET 子句中。
更新数据时也可以像DELETE 语句那样使用 WHERE 子句，这种指定更新对象的 UPDATE 语句称为搜索型 UPDATE 语句。该语句的语法如下所示（与 DELETE 语句十分相似）。
也可以添加case when else end语句，如下：
3.3.2 NULL更新
使用 UPDATE 也可以将列更新为 NULL（该更新俗称为 NULL 清空）。此时只需要将赋值表达式右边的值直接写为 NULL 即可。
使用UPDATE语句可以将值清空为NULL（但只限于未设置NOT NULL约束的列）。
3.3.2 多列更新
UPDATE 语句的 SET 子句支持同时将多个列作为更新对象。
第一种方法——使用逗号将列进行分隔排列，这一方法在所有的 DBMS 中都可以使用。第二种方法——将列清单化，这一方法在某些 DBMS 中是无法使用的（可以在 PostgreSQL和 DB2中使用）。
3.4 事务
● 事务是需要在同一个处理单元中执行的一系列更新处理的集合。通过使用
事务，可以对数据库中的数据更新处理的提交和取消进行管理。
● 事务处理的终止指令包括COMMIT（提交处理）和ROLLBACK（取消处
理）两种。
● DBMS的事务具有原子性（Atomicity）、一致性（Consistency）、隔离性
（Isolation）和持久性（Durability）四种特性。通常将这四种特性的首字母
结合起来，统称为ACID特性。
3.4.1 事务
事务就是需要在同一个处理单元中执行的一系列更新处理的集合。
使用事务开始语句和事务结束语句，将一系列 DML 语句（INSERT/ UPDATE/DELETE 语句）括起来，就实现了一个事务处理
在标准 SQL 中并没有定义事务的开始语句，而是由各个 DBMS 自己来定义的。比较有代表性的语法如下所示。
● SQL Server、PostgreSQL：BEGIN TRANSACTION；
● MySQL：START TRANSACTION；
● Oracle、DB2：无。
COMMIT 是提交事务包含的全部更新处理的结束指令，相当于文件处理中的覆盖保存。一旦提交，就无法恢复到事务开始前的状态了。因此，在提交之前一定要确认是否真的需要进行这些更新。
ROLLBACK 是取消事务包含的全部更新处理的结束指令，相当于文件处理中的放弃保存。一旦回滚，数据库就会恢复到事务开始之前的状态。通常回滚并不会像提交那样造成大规模的数据损失。
像这样不使用指令而悄悄开始事务的情况下，应该如何区分各个事务呢？通常会有如下两种情况。
A 每条SQL语句就是一个事务（自动提交模式）
B 直到用户执行COMMIT或者ROLLBACK为止算作一个事务
3.4.2 ACID特性
■原子性（Atomicity）：原子性是指在事务结束时，其中所包含的更新处理要么全部执行，要么完全不执行，也就是要么占有一切要么一无所有。从事务中途停止的角度去考虑，就能比较容易理解原子性的重要性了。由于用户在一个事务中定义了两条 UPDATE 语句，DBMS 肯定不会只执行其中一条，否则就会对业务处理造成影响。
■一致性（Consistency）：一致性指的是事务中包含的处理要满足数据库提前设置的约束，如主键约束或者 NOT NULL 约束等。例如，设置了 NOT NULL 约束的列是不能更新为 NULL 的，试图插入违反主键约束的记录就会出错，无法执行。对事务来说，这些不合法的 SQL 会被回滚。也就是说，这些 SQL 处理会被取消，不会执行。一致性也称为完整性。
■隔离性（Isolation）：隔离性指的是保证不同事务之间互不干扰的特性。该特性保证了事务之间不会互相嵌套。此外，在某个事务中进行的更改，在该事务结束之前，对其他事务而言是不可见的。因此，即使某个事务向表中添加了记录，在没有提交之前，其他事务也是看不到新添加的记录的。
■持久性（Durability）：持久性也可以称为耐久性，指的是在事务（不论是提交还是回滚）结束后，DBMS 能够保证该时间点的数据状态会被保存的特性。即使由于系统故障导致数据丢失，数据库也一定能通过某种手段进行恢复。如果不能保证持久性，即使是正常提交结束的事务，一旦发生了系统故障，也会导致数据丢失，一切都需要从头再来。保证持久性的方法根据实现的不同而不同，其中最常见的就是将事务的执行记录保存到硬盘等存储介质中（该执行记录称为日志）。当发生故障时，可以通过日志恢复到故障发生前的状态。
四、数据查询
4.1 视图
● 从 SQL的角度来看，视图和表是相同的，两者的区别在于表中保存的是实际的数据，而视图中保存的是SELECT语句（视图本身并不存储数据）。
● 使用视图，可以轻松完成跨多表查询数据等复杂操作。
● 可以将常用的SELECT语句做成视图来使用。
● 创建视图需要使用CREATE VIEW语句。
● 视图包含“不能使用ORDER BY”和“可对其进行有限制的更新”两项限制。
● 删除视图需要使用DROP VIEW语句。
我们在创建表时，会通过 INSERT 语句将数据保存到数据库之中，而数据库中的数据实际上会被保存到计算机的存储设备（通常是硬盘）中。因此，我们通过 SELECT 语句查询数据时，实际上就是从存储设备（硬盘）中读取数据，进行各种计算之后，再将结果返回给用户这样一个过程。
但是使用视图时并不会将数据保存到存储设备之中，而且也不会将数据保存到其他任何地方。实际上视图保存的是 SELECT 语句（图 5-1）。我们从视图中读取数据时，视图会在内部执行该 SELECT 语句并创建出一张临时表。
视图的优点大体有两点:第一点是由于视图无需保存数据，因此可以节省存储设备的容量；第二个优点就是可以将频繁使用的 SELECT 语句保存成视图，这样就不用每次都重新书写了。
4.1.1 视图创建与使用
创建视图需要使用 CREATE VIEW 语句，
SELECT 语句中列的排列顺序和视图中列的排列顺序相同，SELECT 语句中的第 1 列就是视图中的第 1 列，SELECT 语句中的第 2 列就是视图中的第 2 列，以此类推。
创建出视图之后，就可视图的列名视图定义中的主体（内容只是一条SELECT语句）在FROM子句中使用视图来代替表以通过非常简单的 SELECT 语句，随时得到想要的汇总结果。并且，Product 表中的数据更新之后，视图也会自动更新，非常灵活方便。
在 FROM 子句中使用视图的查询，通常有如下两个步骤：① 首先执行定义视图的 SELECT 语句；② 根据得到的结果，再执行在 FROM 子句中使用视图的 SELECT 语句。也就是说，使用视图的查询通常需要执行 2 条以上的 SELECT 语句。
应该避免在视图的基础上创建视图。这是因为对多数 DBMS 来说，多重视图会降低 SQL 的性能。
视图的限制① ——定义视图时不能使用ORDER BY子句；
视图的限制② ——对视图进行更新。
标准 SQL 中有这样的规定：如果定义视图的 SELECT 语句能够满足某些条件，那么这个视图就可以被更新。下面列举一些比较具有代表性的条件。
① SELECT 子句中未使用 DISTINCT
② FROM 子句中只有一张表
③ 未使用 GROUP BY 子句
④ 未使用 HAVING 子句
视图和表需要同时进行更新，因此通过汇总得到的视图无法进行更新。UPDATE 语句和 DELETE 语句也可以像操作表时那样正常执行，但是对于原表来说却需要设置各种各样的约束（主键和 NOT NULL 等），需要特别注意。
4.1.2 视图删除
删除视图需要使用 DROP VIEW 语句，其语法如下所示。
4.2 子视图
● 子查询就是一次性视图（SELECT语句）。与视图不同，子查询在SELECT语句执行完毕之后就会消失。
● 由于子查询需要命名，因此需要根据处理内容来指定恰当的名称。
● 标量子查询就是只能返回一行一列的子查询。
4.2.1 子查询和视图
子查询就是将用来定义视图的SELECT语句直接用于FROM子句当中。子查询就是将用来定义视图的 SELECT 语句直接用于FROM 子句当中。虽然“AS ProductSum”就是子查询的名称，但由于该名称是一次性的，因此不会像视图那样保存在存储介质（硬盘）之中，而是在 SELECT 语句执行之后就消失了。
子查询作为内层查询会首先执行。由于子查询的层数原则上没有限制，因此可以像“子查询的 FROM 子句中还可以继续使用子查询，该子查询的 FROM 子句中还可以再使用子查询……”这样无限嵌套下去。
原则上子查询必须设定名称，因此请大家尽量从处理内容的角度出发为子查询设定恰当的名称。为子查询设定名称时需要使用 AS 关键字，该关键字有时也可以省略。
4.2.2 标量子查询
scalar subquery。标量子查询就是返回单一值的子查询。标量子查询则有一个特殊的限制，那就是必须而且只能返回 1 行 1列的结果。
能够使用常数或者列名的地方，无论是 SELECT 子句、GROUP BY 子句、HAVING 子句，还是ORDER BY 子句，几乎所有的地方都可以使用。
注意“该子查询绝对不能返回多行结果。也就是说，如果子查询返回了多行结果，那么它就不再是标量子查询，而仅仅是一个普通的子查询了，因此不能被用在 = 或者 <> 等需要单一输入值的运算符当中，也不能用在 SELECT 等子句当中”。
4.3 关联子查询
● 关联子查询会在细分的组内进行比较时使用。
● 关联子查询和GROUP BY子句一样，也可以对表中的数据进行切分。
● 关联子查询的结合条件如果未出现在子查询之中就会发生错误。
在对表中某一部分记录的集合进行比较时，就可以使用关联子查询。因此，使用关联子查询时，通常会使用“限定（绑定）”或者“限制”这样的语言。子查询内部设定的关联名称，只能在该子查询内部使用。
五、函数、谓词、CASE表达式
5.1 函数
● 根据用途，函数可以大致分为算术函数、字符串函数、日期函数、转换函数和聚合函数。
● 函数的种类很多，无需全都记住，只需要记住具有代表性的函数就可以了，其他的可以在使用时再进行查询。
abs,mod都支持 round 四舍五入
字符串运算：
|| 字符串拼接 （|| 函数在 SQL Server 和 MySQL 中无法使用。 sql server使用+作拼接， mysql使用CONCAT(str1, str2, str3) 作拼接。）
length 字符串长度
lower 转小写 upper 转大写
REPLACE 将字符串的一部分替换为其他的字符串
SUBSTRING 函数可以截取出字符串中的一部分字符串
CURRENT_DATE 函数能够返回 SQL 执行的日期，也就是该函数执行时的日期。 【没有括号】 CURRENT_TIME 当前时间，结果如17:26:50.995+09；CURRENT_TIMESTAMP 日期+时间
EXTRACT 函数可以截取出日期数据中的一部分，例如“年”，“月”，或者“小时”“秒”等。返回数值类型数据结果。
数据类型转换： CAST（转换前的值 AS 想要转换的数据类型）
COALESCE(数据1，数据2，数据3……) 该函数会返回可变参数 A 中左侧开始第1个不是 NULL 的值。参数个数是可变的，因此可以根据需要无限增加。
5.2 谓词
● 谓词就是返回值为真值的函数。
● 掌握LIKE的三种使用方法（前方一致、中间一致、后方一致）。
● 需要注意BETWEEN包含三个参数。
● 想要取得NULL数据时必须使用IS NULL。
● 可以将子查询作为IN和EXISTS的参数。
通俗来讲谓词就是 6-1 节中介绍的函数中的一种，是需要满足特定条件的函数，该条件就是返回值是真值。对通常的函数来说，返回值有可能是数字、字符串或者日期等，但是谓词的返回值全都是真值（TRUE/FALSE/UNKNOWN）。这也是谓词和函数的最大区别。
LIKE谓词——字符串的部分一致查询 模糊查询
前方一致查询(在字符串首)：like ‘ddd%’
中间一致查询(包含,包括前方与后方)：like ‘%ddd%’
后方一致查询： like ‘%ddd’
此外，还可以使用 _（下划线）来代替 %，与 % 不同的是，它代表了“任意 1 个字符”。
BETWEEN谓词——范围查询：between a and b 结果包含a与b
IS NULL、IS NOT NULL——判断是否为NULL
5.2.1 谓词与子查询
IN谓词——OR的简便用法 但需要注意的是，在使用IN 和 NOT IN 时是无法选取出 NULL 数据的。实际结果也是如此，上述两组结果中都不包含进货单价为 NULL 的叉子和圆珠笔。
IN 谓词（NOT IN 谓词）具有其他谓词所没有的用法，那就是可以使用子查询作为其参数。如果在 SELECT 语句中使用了子查询，那么即使数据发生了变更，还可以继续使用同样的 SELECT 语句。这样也就减少了我们的常规作业（单纯的重复操作）。像这样可以完美应对数据变更的程序称为“易维护程序”，或者“免维护程序”。
EXIST、NOT EXIST谓词 EXIST（存在）谓词的主语是“记录”。EXIST 只需要在右侧书写 1 个参数，该参数通常都会是一个子查询。通常指定关联子查询作为EXIST的参数。
5.3 CASE表达式
CASE表达式分为简单CASE表达式和搜索CASE表达式两种。搜索CASE表达式包含简单CASE表达式的全部功能。
虽然CASE表达式中的ELSE子句可以省略，但为了让 SQL语句更加容易理解，还是希望大家不要省略。
CASE表达式中的END不能省略。
使用CASE表达式能够将SELECT语句的结果进行组合。
虽然有些 DBMS提供了各自特有的CASE表达式的简化函数，例如Oracle中的DECODE和MySQL中的IF，等等，但由于它们并非通用的函数，功能上也有些限制，因此有些场合无法使用。
5.3.1 搜索CASE表达式
CASE 表达式是在区分情况时使用的，这种情况的区分在编程中通常称为（条件）分支。【类似于其他语言的if语句】
应用CASE语句可以让SQL变得简洁高效，从而大大提高了执行效率。而且，CASE的使用一般不会引起性能（相比没有用CASE的语句）低下，反而增加了操作的灵活性.
CASE when < 求值表达式 > then <表达式>
WHEN 子句中的“< 求值表达式 >”就是类似“列 = 值”这样，返回值为真值（TRUE/FALSE/UNKNOWN）的表达式。我们也可以将其看作使用 =、!= 或者 LIKE、BETWEEN 等谓词编写出来的表达式。CASE 表达式会从对最初的 WHEN 子句中的“< 求值表达式 >”进行求值开始执行。所谓求值，就是要调查该表达式的真值是什么。如果结果为真（TRUE），那么就返回 THEN 子句中的表达式，CASE 表达式的执行到此为止。如果结果不为真，那么就跳转到下一条 WHEN 子句的求值之中。如果直到最后的 WHEN 子句为止返回结果都不为真，那么就会返回 ELSE中的表达式，执行终止。
5.3.2 简单CASE表达式
简单 CASE 表达式比搜索 CASE 表达式简单，但是会受到条件的约束，因此通常情况下都会使用搜索 CASE 表达式。与搜索 CASE 表达式一样，简单 CASE 表达式也是从最初的 WHEN 子句开始进行，逐一判断每个 WHEN 子句直到返回真值为止。此外，没有能够返回真值的WHEN 子句时，也会返回 ELSE 子句指定的表达式。两者的不同之处在于，简单CASE 表达式最初的“CASE< 表达式 >”也会作为求值的对象。
如果when表达式中判断的字段是统一的，可以在此写在case后的表达式中，则when表达式可省略该字段的书写。但是，想要在 WHEN 子句中指定不同列时，简单 CASE 表达式就无能为力了。
六、集合运算
● 集合运算就是对满足同一规则的记录进行的加减等四则运算。
● 使用UNION（并集）、INTERSECT（交集）、EXCEPT（差集）等集合运算符来进行集合运算。
● 集合运算符可以去除重复行。
● 如果希望集合运算符保留重复行，就需要使用ALL选项。
6.1 联结join（列）
联结（JOIN）就是将其他表中的列添加过来，进行“添加列”的集合运算。UNION是以行（纵向）为单位进行操作，而联结则是以列（横向）为单位进行的。联结大体上分为内联结和外联结两种。
6.1.1 内联结——INNER JOIN
对于指定的两个表，则有
所谓联结运算，一言以蔽之，就是“以A中的列作为桥梁，将 B中满足同样条件的列汇集到同一结果之中”。
要点1. 进行联结时需要在FROM子句中使用多张表。
要点2. 进行内联结时必须使用ON子句，并且要书写在FROM和WHERE*之间。
要点3. 使用联结时SELECT子句中的列需要按照“<表的别名>.<列名>”的格式进行书写。
像这样使用联结运算将满足相同规则的表联结起来时，WHERE、GROUP BY、HAVING、ORDER BY 等工具都可以正常使用。
另：Oracle 取别名没有as， 去掉as直接写别名即可。
6.1.2 外联结——OUTER JOIN
要点1. 选取出单张表中全部的信息。对于外联结来说，只要数据存在于某一张表当中，就能够读取出来。【类似并集，联结为交集】
要点2.存在主表。最终的结果中会包含主表内所有的数据。指定主表的关键字是 LEFT （左侧的表是主表）和 RIGHT（右侧的表是主表）。
多表左/右连接，表的连接顺序也可以影响查询速度；左连接时，应该把小表放在前面连接；右连接时，把小表放在后面连接。
6.1.3 交叉联结——CROSS JOIN
即笛卡尔积（向量乘中的叉乘），交叉联结是对两张表中的全部记录进行交叉组合，因此结果中的记录数通常是两张表中行数的乘积。本例中，因为 ShopProduct表存在 13 条记录，Product 表存在 8 条记录，所以结果中就包含了13 × 8 = 104 条记录。
6.2 表的加减法
集合运算就是对满足同一规则的记录进行的加减等四则运算。使用UNION（并集）、INTERSECT（交集）、EXCEPT（差集）等集合运算符来进行集合运算。集合运算符可以去除重复行。如果希望集合运算符保留重复行，就需要使用ALL选项。
集合运算符会除去重复的记录。
集合运算有一些注意事项：
注意事项①——作为运算对象的记录的列数必须相同；
注意事项②——作为运算对象的记录中列的类型必须一致；
注意事项③——可以使用任何SELECT语句，但ORDER BY子句只能在最后使用一次；
A UNION B 默认重复行仅保留一次，若需要保留重复行，只需要在 UNION 后面添加 ALL 关键字。与使用 AND 可以选取出一张表中满足多个条件的公共部分不同，INTERSECT 应用于两张表，选取出它们当中的公共记录。
A EXCEPT B A集合减B集合中AB的共同记录。
同时使用 UNION 和 INTERSECT 时，INTERSECT 会优先执行。
此外，。UNION ALL和UNION的不同之处在于它不会对结果进行排序，因此比UNION的性能更好。
七、SQL高级处理
7.1 窗口函数
● 窗口函数可以进行排序、生成序列号等一般的聚合函数无法实现的高级操作。
● 理解PARTITION BY和ORDER BY这两个关键字的含义十分重要。
窗口函数也称为 OLAP 函数。OLAP 是 OnLine Analytical Processing 的简称，意思是对数据库数据进行实时分析处理。窗口函数就是为了实现 OLAP 而添加的标准 SQL 功能
注意：OVER 子句中的 ORDER BY 只是用来决定窗口函数按照什么样的顺序进行计算的，对结果的排列顺序并没有影响。如果需要指定排序，在 SELECT 语句的最后，使用 ORDER BY 子句进行指定。此外，将聚合函数作为窗口函数使用时，会以当前记录为基准来决定汇总对象的记录。
7.1.1 语法
窗口函数大体可以分为以下两种。
① 能够作为窗口函数的聚合函数（SUM、AVG、COUNT、MAX、MIN）；
② RANK、DENSE_RANK、ROW_NUMBER 等专用窗口函数。
PARTITION BY 能够设定排序的对象范围。通过PARTTION BY分组后的记录的集合可以称为窗口。此处的窗口并非“窗户”的意思，而是代表范围。
窗口函数兼具之前我们学过的 GROUP BY 子句的分组功能以及ORDER BY 子句的排序功能。但是，PARTITION BY 子句并不具备GROUP BY 子句的汇总功能。
使用窗口函数时起到关键作用的是 PARTITION BY 和 GROUP BY。其中，PARTITION BY 并不是必需的，即使不指定也可以正常使用窗口函数。当希望先将表中的数据分为多个部分（窗口），再使用窗口函数时，可以使用 PARTITION BY 选项。
原则上窗口函数只能在SELECT子句中使用。不能在 WHERE 子句或者 GROUP BY 子句中使用。
7.1.2 种类
● RANK函数
计算排序时，如果存在相同位次的记录，则会跳过之后的位次。
例）有 3 条记录排在第 1 位时：1 位、1 位、1 位、4 位……
rank() over(partition by … order by …)
● DENSE_RANK函数
同样是计算排序，即使存在相同位次的记录，也不会跳过之后的位次。
例）有 3 条记录排在第 1 位时：1 位、1 位、1 位、2 位……
dense_rank() over(partition by … order by …)
● ROW_NUMBER函数
赋予唯一的连续位次。
例）有 3 条记录排在第 1 位时：1 位、2 位、3 位、4 位……
row_number() over(partition by … order by …)
● 其他
first_value() over(partition by … order by …) 求分组后的第一个值
last_value() over(partition by … order by …) 求分组后的最后一个值
lag() over(partition by … order by …) 取出分组后 前n行数据
lead() over(partition by … order by …) 取出分组后 后n行数据
7.1.3 作为窗口函数使用的聚合函数
所有的聚合函数都能用作窗口函数，其语法和专用窗口函数完全相同。以“自身记录（当前记录）”作为基准进行统计，就是将聚合函数当作窗口函数使用时的最大特征。即一般不分区，使用order by聚合。
7.2 GROUPING运算符
● 只使用GROUP BY子句和聚合函数是无法同时得出小计和合计的。如果想要同时得到，可以使用GROUPING运算符。
● 理解GROUPING运算符中CUBE的关键在于形成“积木搭建出的立方体”的印象。
● 虽然GROUPING运算符是标准 SQL的功能，但还是有些 DBMS尚未支持这一功能。
GROUPING 运算符包含以下 3 种：ROLLUP、CUBE、GROUPING SETS。mySQL只支持第一个，我这里先只放第一个。
书写为
select ifnull(字段,’总计’) as xxx from tb
group by 字段 with ROLLUP
rollup后会将这个汇总的字段总计命名为null，但是总计在各个关系数据库中结果的位置是不固定的，如果想固定让其排列在首行或尾行，则使用union表的加法实现。
扩展
一、Hive SQL
1.0 Hive
Hive 是一个基于 apache hadoop 的数据仓库基础设施。Hadoop 支持在硬件上的数据存储和处理，提供了大规模扩展和兼容能力。
Hive 的设计可以方便地对大量数据进行数据摘要、即席查询和分析。它提供了 SQL，使用户可以方便地进行即席查询、摘要和数据分析。同时，Hive 的 SQL 为用户提供了多个地方来集成他们自己的功能来进行定制分析，比如用户定义函数（udf）。
配置单元不是为联机事务处理而设计的，因此它最适合用于传统的数据仓库任务。Hive 是一个数据仓库基础的应用工具，在Hadoop中用来处理结构化数据，它架构在Hadoop之上，通过 SQL 来对数据进行操作。
Hive 并非为联机事务处理而设计，Hive 并不提供实时的查询和基于行级的数据更新操作。Hive 的最佳使用场合是大数据集的批处理作业。
1.1 Hive SQL 与SQL
Hive SQL，严谨来说是 HiveQL，简称 Hql，是 Hive 提供一个 SQL 方言。Hive 查询操作过程严格遵守 Hadoop MapReduce 的作业执行模型，Hive 将用户的Hive SQL 语句通过解释器转换为 MapReduce 作业提交到Hadoop 集群上，Hadoop 监控作业执行过程，然后返回作业执行结果给用户。
近年来，随着大数据业务的发展，应用范围和需求不断增加，从业人员也随之增长。作为大数据最有影响的 Hadoop 生态，越来越被各大公司应用，开发人员、产品经理、数据分析师、运营人员都参与进来，进行数据使用，HQL 便成为一个必备技能。
Hive的SQL提供了基本的SQL操作。这些操作在表或分区上工作。这些操作包括：
使用WHERE子句从表中筛选行的能力。
使用select子句从表中选择特定列的能力。
能够在两个表之间进行等联接。
能够评估多个“分组依据”列上存储在表中的数据的聚合。
能够将查询结果存储到另一个表中。
能够将表的内容下载到本地（例如，nfs）目录。
能够在hadoop dfs目录中存储查询结果。
能够管理表和分区（创建、删除和更改）。
能够为自定义map/reduce作业插入所选语言的自定义脚本。
1.1.1 HIVE SQL 与SQL的区别
结构化查询语言（SQL）：SQL 是一种特定于领域的语言，用于编程，用于管理关系数据库管理系统（也称为 RDBMS）中的数据。它在处理结构化数据时也很有用，即包含实体和变量之间关系的数据。SQL是在数据库中存储、操作和检索数据的标准语言。
配置单元查询语言（HiveQL）：HiveQL 是一种用于 Hive 分析和处理元存储中结构化数据的查询语言。它与 SQL 非常相似，具有高度的可扩展性。它重用关系数据库世界中熟悉的概念，如表、行、列和模式，以简化学习。Hive 支持四种文件格式，即文本文件、序列文件、ORC 和 RC 文件（记录列文件）。
Hive 和 SQL 之间的主要区别：
架构：Hive 是一个用于数据分析的数据仓库项目；SQL是一种编程语言。不过，Hive通过一种名为HiveQL的编程语言（类似于SQL）执行数据分析。
设置：Hive 是一个基于开源软件程序 Hadoop 构建的数据仓库。
数据分析：Hive比SQL更有效地处理复杂数据，SQL适合不太复杂的数据集。
SQL 和 HiveQL 之间的区别：
其他
.1 mysql 数据类型
.1.1 char 与 varchar
CHARACTER VARYING n. （根据情况）变异，多样性
MySQL 中并没有一种叫做“string”的数据类型。相反，MySQL使用了 TEXT 和 VARCHAR 数据类型来表示字符串数据。TEXT 数据类型，它可以存储非常长的文本数据。
比CHAR更加灵活，VARCHAR可以保存可变长度的字符串，而CHAR不能。VARCHAR和CHAR同样用于表示字符数据。使用比固定长度类型(char)占用更少存储空间(除了使用ROW_FORMAT=FIXED 创建的MyISAM表)。使用额外的1-2字节来存储值长度，列长度<=255使用1字节保存，其它情况使用2字节保存。例如varchar(10)会占用11字节存储空间，varchar(500)会占用502字节存储空间。节约空间，所以性能会有帮助。在更新的时候会产生额外的工作。最大长度为 500 字符
.1.2 decimal(15,2)
由于资金需要精确的表示，因此不要使用像 float 这样的近似数据类型 . 您可以使用定点数字数据类型。
decimal (15,2) n. 小数、十进制
其中，15 是精度（包括小数位的值的总长度）；2 是小数点后的位数
.2 问题
.2.0 书写问题等
-
GROUP BY子句中未指定的列不能书写在 SELECT 子句之中。
-
WHERE 子句必须写在 GROUP BY 子句之前。
-
单独地使用group by (不加聚合函数)，只能显示出每组记录的第一条记录。
-
case when的用法使得第一次满足就结束
-
小细节: sum后要直接跟括号 不能有空格 其他函数应该也如此
-
一般也要加上group by 先独立分组再聚合
-
简单case 搜索表达式，如果字段判断是between and 则会运行报错（语法错误）
.2.1 left join和left outer join
两者之间没有本质上的区别，用法都一样。
left join: 包含左表的所有行，对应的右表行可能为空；
right join: 包含右表的所有行，对应的左表行可能为空；
inner join: 只包含左右表都匹配并且不为空的行；
join: 只包含左右表都匹配并且不为空的行；
left outer join: 包含左表的所有行，对应的右表行可能为空。
.2.2. DATE_SUB()函数可以表示一个日期的前一天
SELECT DATE_SUB(‘2023-08-10’, INTERVAL 1 DAY) AS previous_date;
在这个示例中，'2023-08-10’是后者，INTERVAL 1 DAY表示要获取前一天的日期。DATE_SUB()函数将返回’2023-08-09’作为前者的值。
下划线可以改为字段名。
其他的date函数如下：
NOW()
返回当前的日期和时间
CURDATE()
返回当前的日期
CURTIME()
返回当前的时间
DATE(date)
提取日期或日期/时间表达式的日期部分
EXTRACT(unit FROM date)
返回日期/时间按的单独部分
DATE_ADD(date,INTERVAL expr type)
给日期添加指定的时间间隔
DATEDIFF(date1,date2)
返回两个日期之间的天数 date1-date2
DATE_FORMAT(date,format)
用不同的格式显示日期/时间
year(date) 返回年参数
TIMESTAMPDIFF(unit,date1,date2) 返回date2与date1之间unit单位的时间差
SECOND秒 MINUTE分钟 HOUR小时DAY天 WEEK星期 MONTH月 QUARTER季度 YEAR年
dayofweek(date) 从1开始，1为周日
weekday(date) 从0开始，0为周一
format如下表
如：想输出2020-08样式的，使用DATE_FORMAT(date, ‘%Y-%m’)来实现
格式 描述
%a 缩写星期名 %S 秒(00-59)
%b 缩写月名 %s 秒(00-59)
%c 月，数值 %T 时间, 24-小时 (hh:mm:ss)
%D 带有英文前缀的月中的天 %U 周 (00-53) 星期日是一周的第一天
%d 月的天，数值(00-31) %u 周 (00-53) 星期一是一周的第一天
%e 月的天，数值(0-31) %V 周 (01-53) 星期日是一周的第一天，与 %X 使用
%f 微秒 %v 周 (01-53) 星期一是一周的第一天，与 %x 使用
%H 小时 (00-23) %W 星期名
%h 小时 (01-12) %w 周的天 （0=星期日, 6=星期六）
%I 小时 (01-12) %X 年，其中的星期日是周的第一天，4 位，与 %V 使用
%i 分钟，数值(00-59) %x 年，其中的星期一是周的第一天，4 位，与 %v 使用
%j 年的天 (001-366) %Y 年，4 位
%k 小时 (0-23) %y 年，2 位
%l 小时 (1-12) %p AM 或 PM
%M 月名 %r 时间，12-小时（hh:mm:ss AM 或 PM）
%m 月，数值(00-12)
https://www.w3school.com.cn/sql/sql_dates.asp
.2.3. 重命名结果字段为字符类型需加单引号
.2.4. 计数不能用count(*)，这样空值也会记为1
.2.5. 内连接与外连接
内连接（inner join）：取出连接表中匹配到的数据，匹配不到的不保留
外连接（outer join）：取出连接表中匹配到的数据，匹配不到的也会保留，其值为NULL。
.2.6. 引号的使用
表名和字段名，即使是中文也不用加单引号；
对于表格内的value值，要不要加单引号取决于这个字段设置的数据类型， 不加引号是数字类型,加了就是字符串或日期类型了 如果是 int 类型，那么是不需要引号的，不过，加起引号也不会报错，会自动转换类型。
在标准 SQL 中，字符串使用的是单引号。如果字符串本身也包括单引号，则使用两个单引号（注意，不是双引号，字符串中的双引号不需要另外转义）。
但在其它的数据库中可能存在对 SQL 的扩展，比如在 MySQL 中允许使用单引号和双引号两种。
.2.7. 事务
BEGIN TRANSACTION 启动了事务处理，然后开始执行 INSERT语句。因此，在 A 先生使用 COMMIT 确定该更新之前，B 先生等其他用户都无法看到 A 先生进行更新的结果。这就是基于 ACID 特性中的 I，也就是独立性（Isolation）的现象。
.2.8. NOT IN 与NULL
NOT IN的参数中包含NULL时结果通常会为空，也就是无法选取出任何记录。此外，使用子查询作为 NOT IN 的参数时，该子查询的返回值也不能是 NULL。
题目：
使用如下代码时，id为3 4 5的也会被判为inner，即没有满足not in 语句为true的条件。
NOT IN 需要id !=p_id对所有的p_id都 true，逻辑上是 and。但是MySQL 使用三值逻辑（TRUE, FALSE 和 UNKNOWN），任何与 NULL 值进行的比较都会与第三种值 UNKNOWN 做比较，包括 NULL 本身。!=NULL一定会返回false，所以这里的 NOT IN 找不出来。可以用 WHERE 把 NULL 过滤掉。
.2.9. ROWS n PROCEDING/FOLLOWING
PROCEDING意义为截止到之前n行，如n为2时为：自身、自身前1行、自身前2行。
FOLLOWING为之后，同理。
可将两者联合，如下表示前一行+自身+后一行
.2.10. NULL FIRST
order by 后可加null first 使得值为null的记录排在最前面（排序键中包含NULL时，会在开头或末尾进行汇总，不同DBMS的前/后不同）。
.2.11. 保留小数点后n位
使用 Round() 函数，如 Round(number,2) ，其中参数2表示保留两位有效数字，四舍五入到两位小数。
cast(number as decimal(10,2)) 实现转换，其中参数2表示保留两位有效数字，四舍五入到两位小数。
.2.12. 设立主键
AUTO_INCREMENT 主键自增
.2.13. 字段连续超过n行 id-rank()
如果n数值较小，可以使用自连接或者笛卡尔积（from自身n次，再where id连续）来判断；最好借助窗口函数rank()与字段的差来实现连续得判断，因为rank()一定连续，如果字段同样连续无间断，则差是固定的，如果发生间断，则差会发生改变。
例题如下：
答案如下：
红框oder为id-rank()的值，该值相同则表示对应的id为连续的；
橙框oder2为oder的个数，即表示连续的id有多少个（行）。
也可以先 判断参数-rank(order by 判断参数) ，值相同表示 判断参数 连续，再对其进行分组group by，数每组的个数，判断count(*)是否满足条件，如下图：
.2.14. 聚合where和on筛选的区别
外连接时要注意where和on的区别，on是在连接构造临时表时执行的，不管on中条件是否成立都会返回主表（也就是left join左边的表）的内容，where是在临时表形成后执行筛选作用的，不满足条件的整行都会被过滤掉。
简单来说：where将不符合的结果集排除 而 on会返回null。
.2.15. COUNT(1)、COUNT(*)、COUNT(field)的区别（类似于2.4）
count()是一个聚合函数，对于返回的结果集，会逐行判断，若返回的不是 NULL，就会加 1，否则不加。因此，count()、count(主键 id)和count(1)都表示返回满足条件的结果集的总行数；而count(字段），则表示返回满足条件的数据行里面，参数“字段”不为 NULL 的总个数。
按照效率排序的话，count(字段)<count(主键 id)<count(1)≈count()。
.2.16. 排序-多字段排序，字典序排序
多个字段排序在order by 后直接逗号连接即可，升序降序标注如正常单字段排序，如下所示。默认情况下，MySQL会按照升序（从A到Z）对字符串字段进行排序，即下图会根据name字段的字典序对各记录进行升序排序。
.2.17. ifnull函数
IFNULL() 函数用于判断第一个表达式是否为 NULL，如果为 NULL 则返回第二个参数的值，如果不为 NULL 则返回第一个参数的值。常用在左连接联合后对空值数值赋零值。如下所示：
.2.18. 字符串聚合拼接
借助GROUP_CONCAT() 函数:将组中的字符串连接所有非NULL的字符串，如果没有非Null的字符串，那么它就会返回Null.
具体参数：
.2.19. 窗口函数 滑动窗口
rows between unbounded preceding and current row
range between unbounded preceding and current row
rows是物理窗口，即根据order by 子句排序后，取的前N行及后N行的数据计算（与当前行的值无关，只与排序后的行号相关）;
range是逻辑窗口，是指定当前行对应值的范围取值，列数不固定，只要行值在范围内，对应列都包含在内。适用于对日期、时间、数值排序分组。
unbounded：无界限，可以设置为n
preceding：从分区第一行头开始，则为 unbounded。 N为：相对当前行向前的偏移量
following ：与preceding相反,到该分区结束，则为 unbounded。N为：相对当前行向后的偏移量
current row：顾名思义，当前行，偏移量为0
如图表示visited_on当天与前6天的求和
https://blog.csdn.net/WHYbeHERE/article/details/127896098
判断visited_on是几号以显示特定日期的滑动窗口结果 的语句一定要在滑动窗口外层！否则滑动窗口将不对不满足该条件的记录进行滑动统计！！！
.2.20. 字符串截取SUBSTRING与SUBSTRING_INDEX
SUBSTRING(s, start, length)
从字符串s的start位置截取长度为length的子字符串；如果SUBSTRING()函数接收2个参数:SUBSTRING(s,start)，则第一个参数为待截取的字符串，第二个参数为截取的起始位置。如果第二个参数为负整数，则为倒数的起始位置
SUBSTRING_INDEX(str, delimiter, number)
返回从字符串str的第number个出现的分隔符delimiter之前的子串;如果number是正数，那么就是从左往右数，返回第number个分隔符的左边的全部内容;相反，如果number是负数，那么就是从右边开始数，第number个分隔符右边的所有内容。
注意:如果number超过了实际分隔符的个数，则返回实际个数的字符串
参数说明：
str: 待截取的字符串,STRING类型
delimiter: 分隔符,STRING类型
number: 指定分隔符位置,INT类型
返回值：返回STRING类型字符串,如果任一输入参数为NULL，则返回NULL
如图，截图avg_play_progress 中第一个%出现之前的字符串。
.3 做题小问题知识
.3.1. group by 使用别名执行顺序
疑问：mysql的执行顺序是group by 后再select，但是该题对select获取到的level_cut字段进行了分组，是否执行顺序不对？
答：mysql 在 ordery by 和 group by 中使用别名，相当于把别名的语句放在ordery by 和 group by 中，group by 和 order by 中使用别名时，赋值语句不会重复执行，只是将赋值语句的赋值顺序提前触发了。
.3.2. TIMESTAMPDIFF 和 DATEDIFF 在 精度上的区别
2022-05-08 21:16 和2022-05-09 21:15使用timestampdiff的话就是0，相差不到一天，用datediff话是1，后者只返回二者相差的天数。
timestampdiff要把小的时间（更早的时间）放在前面
datediff要把大的时间（更靠后的时间）放在前面
.3.2. between and是闭区间
between and 表示的是一个闭区间[ ]的范围，如
price between 100 and 200 ，表示价格在 [100,200]这样一个范围。in 表示的则不是一个区间，而是孤立的确定的值。
.4 题目大知识
.4.1 case when 统计个数
答案如下
.4.2 替换null
答案如下：
.4.3 行列转换
行转列用groupby+sumif（或sum(case)），列转行用union all。行转列原理：CASE WHEN或IF,这两种都是判断条件，满足条件的时候我们把它当做新的一列。列转行原理：UNION或UNION ALL,这两个都是把结果集合并起来。
列转行 行转列 一般都需要聚合函数 否则取到第一个符合条件的就结束了
如下题，为列转行情况，使用union all 实现。
——>
此外，在MySQL中，我们可以使用PIVOT和UNPIVOT函数将表格中的行数据转换成列数据。PIVOT函数将表格中的行数据转换成列数据，而UNPIVOT函数则将表格中的列数据转换成行数据。
.4.4 连续日期
题目为：
基本解法：
注意，红框区使用date+1时会发生错误，对于[1,2,“2016-02-29”,5],[1,2,“2016-03-01”,6]案例，+1之后再套用date函数会返回null值其未对其计算在内。
解法2：count(distinct)改为avg计数。先过滤出每个用户的首次登陆日期，然后左关联，筛选次日存在的记录的比例。
解法2的运行时间要更少。左侧框为用户首次登陆日期，右侧框为满足id相同，如果次日有登陆则其他字段有记录。
此外， 
.4.5 善用 where 多字段子查询
如图：取出change_date在2019-08-16之前，且日期为最新日期的id及价格，如果只用group by获取，则获取到的price为同一id最早日期的价格，因此我加了单表自连接以获取到日期为最新日期的id及价格【p.change_date=c1.d1】
可以使用where子查询简化该操作，如下图：
不过都是对products表进行了两次查询。
.4.6 正则表达式——字符串匹配问题
一般来说，如果你被要求匹配一个字符串，应该最先想到写一个正则表达式模式进行匹配。正则表达式提供各种功能，以下是一些相关功能：
^：表示一个字符串或行的开头
[a-z]：表示一个字符范围，匹配从 a 到 z 的任何字符。
[0-9]：表示一个字符范围，匹配从 0 到 9 的任何字符。
[a-zA-Z]：这个变量匹配从 a 到 z 或 A 到 Z 的任何字符。请注意，你可以在方括号内指定的字符范围的数量没有限制，您可以添加想要匹配的其他字符或范围。
[^a-z]：这个变量匹配不在 a 到 z 范围内的任何字符。请注意，字符 ^ 用来否定字符范围，它在方括号内的含义与它的方括号外表示开始的含义不同。
[a-z]*：表示一个字符范围，匹配从 a 到 z 的任何字符 0 次或多次。
[a-z]+：表示一个字符范围，匹配从 a 到 z 的任何字符 1 次或多次。
.：匹配任意一个字符。
.：表示句点字符。请注意，反斜杠用于转义句点字符，因为句点字符在正则表达式中具有特殊含义。还要注意，在许多语言中，你需要转义反斜杠本身，因此需要使用\.。
$：表示一个字符串或行的结尾。
核心思想是将 name 列的第一个字符从其余字符分开，相应地改变它们的大小写，最后把他们拼回在一起。
如图表示 1任意字母开头 [a-zA-Z0-9_.-]*表示方框中的字符0次或多次 最后以力扣域名结束，其中@与.使用\进行转义以防止发生其他意义代指。
MySQL 中使用 REGEXP 关键字指定正则表达式的字符匹配模式，下表列出了 REGEXP 操作符中常用的匹配列表
.4.7 大小写
1.MySQL大小写敏感规则
MySQL中，一个库会对应一个文件夹，库里的表会则以文件的方式存放在文件夹内，所以，操作系统对大小写的敏感性决定了数据库和表的大小写敏感（MySQL有一个只读的系统变量lower_case_file_system，其值反映的正是当前文件系统是否区分大小写）因此：在Windows下Mysql的数据库和表名是大小写不敏感的，而在大多数类型的Unix系统中是大小写敏感的。
以下是MySQL详细的大小写区分规则：
在Linux下：
1、数据库名与表名是严格区分大小写的；
2、表的别名是严格区分大小写的；
3、列名与列的别名在所有的情况下均是忽略大小写的；
4、变量名也是严格区分大小写的；
在Windows下：全部不区分大小写
补充：
1.MySQL中有一个系统变量：lower_case_table_names，专门用来配置是否区分据库名与表名的大小写。 如果你的系统是Windows，而你又希望mysql能够区分大小写，那么你可以在my.ini文件中，在[mysqld]一节的最后，加入如下部分：
#If set to 0, table names are stored as specified and comparisons are case sensitive.
#If set to 1, table names are stored in lowercase on disk and comparisons are not case sensitive.
#If set to 2, table names are stored as given but compared in lowercase. This option also applies to database names and table aliases.
lower_case_table_names=0
2.MySQL在查询字符串时是大小写不敏感的。如某字段值需要区分大小写，可在定义该字段时指定BINARY属性。
2.一种MySQL命名规范
为了避免大小写引发的问题，一种推荐的命名规则是：在定义数据库、表、列的时候全部采用小写字母加下划线的方式，不使用任何大写字母。
.4.8 找到最大可以用窗口函数 rank()=1
之前判断最大，如果表比较简单或者是原始表的话，则可以直接使用
where 判断参数= (select max(判断参数) from tb group by XXX)
然而如果表是自己查询构造的表话，查询语句将相当复杂，且上式为标量子查询，如果group by的是两个或多个参数时，即可能select语句得到的又是一张表，则结果将会报错“Subquery returns more than 1 row”即子查询的记录不止一行，则该句不可用（可换成where in子查询，然而换用还需要增加更多的字段判断），建议使用rank窗口函数来进行判断查询，如下图：
该图使用avg_grade降序与限制r为1找到最大；
此外，在sql处理中，窗口函数都是最后一步执行，而且仅位于order by子句之前。因此，可以group by 可以和窗口函数连用。
即可简化为：
.4.9 with t as 派生表
with t as 其实就等同于一个派生表 ，将派生表起名 t ，这个名字 t 提前；什么是派生表 举例： select * from (select * from student join score using(s_id) where s_id = ‘01’) as t 加粗部分就是派生表 且需要起别名
using（s_id） 的作用 等同于 on student.s_id = score.s_id
如果是要产生两个派生表，则注意格式 with a as ( select 语句），b as ( select 语句） a和b后紧跟select语句，且a和b之间之间使用 逗号 隔开 （不要忘了 是英文状态下的逗号）
.4.10 同时在线问题
进入定义为1，离开定义为-1，并对时间dt进行正序排序，最后算累加实时在线UV。按照时间顺序排序用窗口函数sum对人数进行累计。在这里一个要注意的点是，如果同一时刻有进入也有离开，先记录用户数增加，再记录减少。这意味着，要遵循先+1，再-1的原则，所以在窗口函数的ORDER BY中，要写为order by dt, num DESC。
“先+1再-1”和“先-1再+1”这两种不同计算方法会导致求最大在看人数的结果不同。“先+1再-1”的计算方法算出来的最大在看人数比“先-1再+1”算出来的大。
2）sum()over(order by 字段1)：按照字段1的排序依次进行累加，这个其实是开窗函数框架限定的缩写应用。 完整的写法是sum()over(order by 字段名 rows between unbounded preceding and current row)
.5 优秀答案
.5.1 统计每天的日活数及新用户占比
字段：uid-用户ID, artical_id-文章ID, in_time-进入时间, out_time-离开时间, sign_in-是否签到
注：新用户占比=当天的新用户数÷当天活跃用户数（日活数）；如果in_time-进入时间和out_time-离开时间跨天了，在两天里都记为该用户活跃过。
新用户占比保留2位小数，结果按日期升序排序。
我的思路是
绝大多数题只是起到锻炼sql使用能力的作用，实际工作中增删改查的要求非常简单，因为绝大多数逻辑是在代码中完成的，sql需要关注的是数据而不是操作逻辑，在sql查询方面需要关注sql查询的优化和效率。也不绝对，我们这边业务的取数逻辑还是依靠sql实现。
a-zA-Z ↩︎
扫一扫
606
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
