MySQL查漏补缺

虾壳...大约 52 分钟数据库MySQL

where 不能和聚合函数一起使用
having可以和聚合函数一起使用
having必须与group by一起使用
SQL的执行顺序是：FROM--WHERE--GROUP BY--HAVING--SELECT--ORDER BY

MySQL中的函数：https://www.sjkjc.com/mysql-ref/

MySQL中没有外连接（FULL JOIN 或 FULL OUTER JOIN），要达到外连接的效果，应操作：全外连接=左外连接 union 右外连接。

1、SUBSTRING_INDEX(str ,substr ,n)：返回字符substr在str中第n次出现位置之前的字符串;

2、

sp_helpindex：查看表中的索引信息， sp_help：查看有关数据库对象的摘要信息， sp_helpdb：查看指定数据库或全部数据库信息， sp_helptext：查看存储过程、视图、触发器等文本信息

3、去重有2种方式用group by和distinct

4、Mysql(版本8.0.25)不支持full join

5、为数据库用户授权

https://www.cnblogs.com/elijah-li/p/16286788.html

创建用户格式：create user 用户名@ip地址 identified by '密码';
授权：grant all on *.* To 用户名@'ip地址';
grant select,create on 数据库名.表名 To 用户名@ip地址；
管理权限（如 super， process， file等）不能够指定某个数据库，on后面必须跟 .
有人会问truncate权限呢，其实truncate权限就是create+drop，这点需要注意

# 创建用户
create user root@'192.168.11.%' identified  by '123456';
# 这样root@192.168.11.% 这个网段的用户可以登录

create user elijah@'192.168.11.161' identified by '123456';
# 这样只允许elijah用户登录

create user li@'%' identified by '123456';
# 所有li用户都可登录

# 删除用户
drop user root@'192.168.11.%';
# 授权
grant all on *.* To elijah@'192.168.11.161';
grant select,create on oldboy_test.* To elijah@'192.168.11.161';

# 查看授权
show grants for elijah@'192.168.11.161';
# 刷新权限表
flush privileges;

6、

< 和 > 分别对应 &lt;  和  &gt;

7、

Charindex() 返回字符或字符串在另一个字符的起始位置CHARINDEX(查找字符，被查字符，开始位置) SubString() 截取字符串中的一部分字符。SUBSTRING（字符串，开始位置，截取长度） Stuff() 删除指定长度的字符，并在指定的起点处插入另一组字符。STUFF(列名,开始位置,长度,替代字符串) Soundex() 返回表示字符串声音的一串字符

8、as可以做重命名，不过也可以省略as，空格隔开新名称即可

9、eno+1-2*POWER(0,eno%2)就是奇数+1，偶数-1 eno-1+2*MOD(0,eno%2)也就是奇数会+1，偶数会-1

10、

@@ERROR：返回执行的上一个 Transact-SQLTransact-SQL 语句的错误号。 @@IDENTITY：返回自增id。 @@ROWCOUNT：返回受上一个SQL语句影响的行数。 @@MAX_CONNECTIONS：返回最大用户连接数。

11、SQL的执行顺序是：FROM--WHERE--GROUP BY--HAVING--SELECT--ORDER BY

12、NULL在sql中不是用来比较的，在sql中若要取得NULL，则必须通过IS NULL或者IS NOT NULL进行获取，无法直接使用等号

13、

All()：对所有数据都满足条件，整个条件才成立，>=all()等价于max，<=all()等价于min； Any：只要有一条数据满足条件，整个条件成立，>any()等价于>min，<any()等价于<max； some的作用和Any一样

14、 触发器是与表有关的数据库对象，在 insert/update/delete 之前或之后触发并执行触发器中定义的 SQL语句，有三种触发器类型。

15、先分组，再过滤，用group by ... having ...

16、INSERT INTO 语句用于向一张表中插入新的行。SELECT INTO 语句从一张表中选取数据插入到另一张表中。常用于创建表的备份复件或者用于对记录进行存档。

17、

having子句即可包含聚合函数作用的字段也可包括普通的标量字段；
having子句必须与group by 子句同时使用，不能单独使用，having是用来过滤的，group by是限定分组。having和group by必须一起使用，或者只使用group by不适用having。但是有having的时候必须出现group by
having是在分组后过滤，where在分组前过滤，不冲突，可以同时使用，所以having子句和where子句是等同的。where不能和聚合函数一起使用。
select语句中没有聚合函数的使用时也可以用having

18、在MySQL中**不能使用 = NULL 或 != NULL 等比较运算符在列中查找 NULL 值。**要用IS NULL 或 IS NOT NULL才会进行NULL值或非NULL值得查找。

19、以下情况游标将会自动设定INSENSITIVE选项:

当SELECT语句中使用DISTINCT、GROUP BY、HAVING UNION语句时；使用OUTER JOIN时；所选取的任意表没有索引时；将实数值当作选取的列时。

20、全外连接

MySQL中没有外连接，要达到外连接的效果，应操作：全外连接=左外连接 union 右外连接

21、查看建表语句

show create table student_table

CREATE TABLE `student_table` (
  `id` int(11) NOT NULL,
  `name` varchar(20) CHARACTER SET utf8 DEFAULT NULL,
  `birth` varchar(20) CHARACTER SET utf8 DEFAULT NULL,
  `sex` varchar(20) CHARACTER SET utf8 DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

22、存储过程(stored procedure)是一组为了完成特定功能的SQL语句集合，经编译后存储在服务器端的数据库中，利用存储过程可以加速SQL语句的执行。它可以提高SQL的速度，存储过程是编译过的，如果某一个操作包含大量的SQL代码或分别被执行多次，那么使用存储过程比直接使用单条SQL语句执行速度快的多。

23、选取σ、投影π是专门的关系运算符、与∧、或∨是逻辑关系运算符、非¬是逻辑关系运算符、 ∪并Union 、− 差Difference 、∩ 交Intersection 、× 笛卡尔积 Cartesian Product

24、游标是一种从包括多条数据记录的结果集中每次提取一条记录以便处理的机制，可以看做是查询结果的记录指针；游标允许定位在结果集的特定行；为由其他用户对显示在结果集中的数据所做的更改提供不同级别的可见性支持；支持对结果集中当前位置的行进行数据修改。

25、

RENAME用于表的重命名：RENAME <NAME> 或 RENAME TO <NAME> MODIFY用于字段类型的修改：ALTER TABLE table_name MODIFY column_name new_data_type; ALTER用于对字段类型、默认值的修改：ALTER TABLE table_name ALTER COLUMN column_name SET DEFAULT default_value; CHANGE用于对列名及类型的修改：ALTER TABLE table_name CHANGE old_column_name new_column_name new_data_type;

ALTER TABLE employee RENAME employee_info;		-- 更改表名为 employee_info

26、索引

数据库索引采用B+树是因为B+树在提高了磁盘IO性能的同时解决了元素遍历效率低下的问题；如果WHERE子句中使用了索引，那么ORDER BY子句中不会使用索引；索引滥用会降低更新表的速度。

27、左连接

left join意思是包含inner join的结果，left join 返回左表的全部记录, on的作用是左表关联右表的条件

28、SQL语言共分为四大类：数据查询语言DQL，数据操纵语言DML，数据定义语言DDL，数据控制语言DCL。

（1）数据查询语言DQL 数据查询语言DQL基本结构是由SELECT子句，FROM子句，WHERE子句组成的查询块：SELECT <字段名表>FROM <表或视图名>WHERE <查询条件>

（2）数据操纵语言DML 数据操纵语言DML主要有三种形式： ①插入：INSERT ②更新：UPDATE ③删除：DELETE

（3）数据定义语言DDL 数据定义语言DDL用来创建数据库中的各种对象-----表、视图、索引、同义词、聚簇等如：CREATE TABLE / VIEW / INDEX / SYN / CLUSTER| 表视图索引同义词簇。DDL操作是隐性提交的！不能rollback

（4）数据控制语言DCL 数据控制语言DCL用来授予或回收访问数据库的某种特权，并控制数据库操纵事务发生的时间及效果，对数据库实行监视等。如： ①GRANT：授权。 ②ROLLBACK [WORK] TO [SAVEPOINT]：回退到某一点。回滚---ROLLBACK回滚命令使数据库状态回到上次最后提交的状态。其格式为：SQL>ROLLBACK; ③COMMIT [WORK]：提交。在数据库的插入、删除和修改操作时，只有当事务在提交到数据库时才算完成。在事务提交前，只有操作数据库的这个人才能有权看到所做的事情，别人只有在最后提交完成后才可以看到。

（5）总结

①数据定义（SQL DDL）用于定义SQL模式、基本表、视图和索引的创建和撤消操作。 ②数据操纵（SQL DML）数据操纵分成数据查询和数据更新两类。数据更新又分成插入、删除、和修改三种操作。 ③数据控制（DCL）包括对基本表和视图的授权，完整性规则的描述，事务控制等内容。 ④嵌入式SQL的使用规定（TCL）涉及到SQL语句嵌入在宿主语言程序中使用的规则。

29、删除触发器： DROP TRIGGER 输入： DROP TRIGGER 触发器名称;

30、where name <> '张三' 会把NULL的值对应的记录排除掉，所以不包含null和NULL的记录；

31、右连接

1）右连接以右表为主左连接以左表为主； 2）不论左右连接，两表排序都是先写的放在左边，后写的放在右边； 3）左右连接中，以谁为主表，则其信息全部保存。从表中有多于一项符合条件的，则额外重复一次主表从而列出全部从表情况

('1004' , '张三' ,'2000-08-06' , '男');
('1009' , '李四', '2000-01-01', '男');
('1010' , '李四', '2001-01-01', '男');
('1006' , '王五', '2000-08-06' , '女');
('1008' , '张三', '2002-12-01', '女');
('1012' , '张三', '2001-12-01', '女');
('1011' , '李四', '2002-08-06' , '女');
-- 执行
select t1.*,t2.*
from (
select * from student_table where sex = '男' ) t1 
right join 
(select * from student_table where sex = '女')t2 
on  t1.name = t2.name ;

32、null代表不确定，两个不确定显然不是相等的。要判断null要用is null

33、case when的正确语法是: case when ... then ... when ... then ... else ... end，case和end不能缺少

34、insert插入时，可以给部分或所有字段名加``，不能加''，加''时会执行报错。

35、length无法对null做筛选，<>无法对null做筛选

36、在视图中也可以定义新的视图，却无法创建表，因为视图是个虚表

37、授予用户SQLTest对数据库Sales的CUSTOMERS表的列cid、cname的查询权限

grant select on CUSTOMERS(cid,cname) to SQLTest

38、视图

视图是虚拟表，视图所引用的表称为视图的基表；视图可以解决检索数据时一个表中得不到一个实体所有信息的问题；若基表的数据发生变化，则变化也会自动反映到视图中；数据库存储的是视图的定义，不存放视图对应的数据。

视图不能对临时表或表变量进行引用；当SELECT语句的选择列表有TOP子句时，视图可以包含ORDER BY子句；视图可以被嵌套，一个视图中可以嵌套另一个视图； sp_helptext用于获取自定义视图创建的T_SQL文本，更新视图数据可用sp_refreshview

39、SQL语言的组成部分有：数据定义语言、数据处理语言、数据控制语言、数据库事务。数据字典是关于数据库中数据的描述，是元数据，而不是数据本身。

40、下面哪些字符最可能会导致sql注入?

'（单引号）
/
"(双引号)
$

41、MySQL UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据。在SQL中，UNION操作符可以合并多个SELECT语句的结果集，但是只使用UNION操作符的时候，他只会列出不重复的值；如果想要获取重复值，就要用UNION ALL操作符才行。

42、group by用于分组，和聚合函数一起使用。

43、distinct需要写在case前面

44、视图是虚表，它在存储时只存储视图的定义，而没有存储对应的数据；

创建视图用CREATE VIEW
修改视图用ALTER VIEW
删除视图用DROP VIEW

45、count()函数有两种使用方式：①使用count(*)对表中的行数进行计数，此种用法不忽略null值；②使用count(column)对特定列中具有值的行进行计数，空字符“”也算，但忽略null值，null和NULL是一样的。

46、在大多数的关系型数据库里面，如果在使用left join的同时，又把附表的条件放在where子句中，而不是 on子句中时，实际的执行效果相当于inner join .

原题的D选项https://www.nowcoder.com/questionTerminal/45fa358b24364928bcb6f99c7d19ea0f?

47、MySQL 中使用 REGEXP 或 NOT REGEXP 运算符 (或 RLIKE 和 NOT RLIKE) 来操作正则表达式。

[^charlist] 通配符表示不在字符列中的任一单一字符。

MySQL中选取不以charlist中任一字符开头的语法为：

SELECT * FROM tab_name WHERE col_name REGEXP '^[charlist]';

同理，不以某、或另一某字符开头的语法为：

SELECT * FROM tab_name WHERE col_name REGEXP '^[^charlist]';
SELECT * FROM tab_name WHERE col_name NOT REGEXP '^[charlist]';

48、创建视图语句如下 create view 视图名 as select * from 表名 where 条件

49、drop、delete、truncate

①drop是完全删除表，包括表结构 ②delete是删除表数据，保留表的结构，而且可以加where，只删除一行或者多行 ③truncate 只能删除表数据，会保留表结构，而且不能加where

50、用户定义函数不用于执行修改数据库状态的操作；用户定义函数属于数据库，只能在该数据库下调用；标量函数和存储过程一样，可以使用EXECUTE语句执行。与系统函数一样，用户定义函数可以从查询中调用。

60、inner join时只会对非NULL的记录做join,并且2边都有的才会匹配上

61、sum是可以用于行转列的函数

62、is 只能在null的时候用正常情况要用 =‘ ’（注意是一个等号，而不是==）

63、where 子句不能直接跟聚合函数Max、Min，但where子句能跟含聚合函数的子查询，所以：

SELECT shirt_id，shirt_name，shirt_price FROM SHIRTABLE WHERE shirt_price > AVG(shirt_price) -- 错误
SELECT shirt_id，shirt_name，shirt_price FROM SHIRTABLE WHERE shirt_price > (SELECT AVG(shirt_price) FROM SHIRTABLE)   -- 正确

64、对表的结构修改属于DDL语句，常用公式。添加列： ALTER TABLE 表名 add 字段名修改列： ALTER TABLE 表名 modify 字段名删除列： ALTER TABLE 表名 drop 字段名

65、修改时加排他锁，直到事务提交后释放；写数据时加排他锁，事务提交后释放锁。读取数据时加共享锁，执行完语句后释放锁。

66、窗口函数：窗口函数对其所作用的窗口中的每一行记录输出一条结果；OVER 子句定义查询结果集内的窗口，然后，开窗函数（sum、max、min、avg、count、rank等）将计算窗口中每一行的值，以便取得各种聚合值。

开窗函数 OVER (   
       [ <PARTITION BY clause> ]  
       [ <ORDER BY clause> ]   
       [ <ROW or RANGE clause> ]  
)

67、索引

为提高效率可建立组合索引，遵循“最左前缀”原则；唯一索引不允许两行具有相同的索引值，包括NULL值，允许有空值；每个表只允许有一个全文索引；非聚集索引具有完全独立于数据行的结构，所以不需要将物理数据页中的数据按列重新排序。

68、对聚合后的结果做筛选用having，where是在聚合前做筛选。

69、索引的组织方式是B+树索引，还是Hash索引与数据库的内模式有关；外模式是面向数据库用户或应用程序的局部数据视图；模式并不涉及数据的物理存储细节；模式/内模式映像保证了数据库具有较高的物理独立性。

70、coalesce函数，返回第一个非空值，如果都是空，返回空值

71、SQL合法标识符第一个字必须是字母、下划线、@和#开头

72、MySQL 中使用 REGEXP 来操作正则表达式的匹配。

其中

^ 该符号表示匹配输入字符串的开始位置；
$表示匹配输入字符串的末尾位置；
[...] 表示匹配所包含的任意一个字符；
[^...]表示不能匹配括号内的任意单个字符；
x|y 这条竖线表示匹配x 或匹配y。

73、关于日期函数，datediff(日期1，日期2)，得到的结果是日期一与日期二相差的天数，若日期1大于日期2，结果为正，否则结果为负。

74、count(*)、count(1)、count(列名)

**count()**包括了所有列，相当于行数，在统计结果的时候，会统计包括NULL值在内的所有行。 **count(1)**用1代表代码行，忽略所有列的值，在统计结果的时候，统计所有行，效果同count()。 count(列名)只包括列名对应一列，在统计结果的时候，会忽略列值为空的行。

75、数据库备份类型

完整数据库备份：备份整个数据库，包括事务日志部分；差异数据库备份：仅记录自最近一次完整数据库备份以后发生改变的数据：事务日志备份：不备份数据库本身，只记录事务日志内容；文件和文件组备份：如果在创建数据库时，为数据库创建了多个数据库文件或文件组，可以使用该备份方式，对数据库中的部分文件或文件组备份。

76、DENSE_RANK()是一个窗口函数,它为分区或结果集中的每一行分配排名，而排名值没有间隙。

77、

修改表：ALTER TABLE 表名 修改选项

修改选项集合：
ADD COLUMN <列名> <类型> -- 增加列
CHANGE [COLUMN] <旧列名> <新列名> <新列类型> -- 修改列名或类型
ALTER [COLUMN] <列名> { SET DEFAULT <默认值> | DROP DEFAULT } -- 修改/删除 列的默认值
MODIFY [COLUMN] <列名> <类型> -- 修改列类型
DROP [COLUMN] <列名> -- 删除列
RENAME TO <新表名> -- 修改表名
CHARACTER SET <字符集名> -- 修改字符集
COLLATE <校对规则名> } -- 修改校对规则（比较和排序时用到）

78、MySQL中的关键字：select、where、from、on、group、order、having、as、and、between、int、char、varchar、with https://www.qycn.com/xzx/article/17472.html

79、在数据库中存在多列的情况下，删除表中的某一列：ALTER TABLE tableName DROP COLUMN columnName

80、设计数据库概念结构时，常用的数据抽象方法是概括、分类、聚集。

81、保留3位小数，format(数值，3)

82、使用union组合查询时，只能使用一条order by字句，且必须位于最后一条select语句之后。

83、sum()和count()的区别：

Sum(列) 是求和，把所有列的值进行汇总求和；COUNT(列) 是行数汇总，只要列的值不为Null，就会增加1；

sum()用于该列值的求和，count()用于行数的统计。sum()列值为空时不计算，count()为空时认为没有这一行。sum()为空时返回结果为null，count()为空时返回结果为0

为了防止使用sum()时为null，使用ifnull进行判断处理

SELECT IFNULL(sum(id),0) FROM dgztc.user where id="";

79、窗口函数

1）聚合窗口函数

MIN()OVER() :不改变表结构的前提下，计算出最小值
MAX()OVER():不改变表结构的前提下，计算出最大值
COUNT()OVER():不改变表结构的前提下，计数
SUM()OVER():不改变表结构的前提下，求和
AVG()OVER():不改变表结构的前提下，求平均值

2）排序窗口函数

percent_rank() over() 按照数字所在的位置进行百分位分段
ntile(n)over() 将数字按照大小平均分成n段
lead(字段名，n)over()把字段数据向前移n个单元格
lag(字段名，n)over()把字段数据向后移n个单元格
rank() over() 1 2 2 4 4 6 (计数排名，跳过相同的几个，eg.没有3没有5)
row_number() over() 1 2 3 4 5 6 (赋予唯一排名)
dense_rank() over() 1 2 2 3 3 4 (不跳过排名，可以理解为对类别进行计数)

SELECT start_month ,#每个月     COUNT(DISTINCT uid) mau, #月活用户数   
    SUM(new_day) month_add_uv, #新增用户
    MAX(SUM(new_day))OVER(ORDER BY start_month) max_month_add_uv, #截止当月的单月最大新增用户数
    SUM(SUM(new_day))OVER(ORDER BY start_month) cum_sum_uv
FROM (
    SELECT *,DATE_FORMAT(start_time,'%Y%m') start_month,IF(start_time=MIN(start_time)OVER(PARTITION BY uid),1,0) new_day FROM exam_record
	)t1
GROUP BY start_month;

80、插入记录的方式汇总

普通插入（全字段）：INSERT INTO table_name VALUES (value1, value2, ...)
普通插入（限定字段）：INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...)
多条一次性插入：INSERT INTO table_name (column1, column2, ...) VALUES (value1_1, value1_2, ...), (value2_1, value2_2, ...), ...
从另一个表导入：INSERT INTO table_name SELECT * FROM table_name2 [WHERE key=value]

81、SQL中limit 0,3和limit 3有什么区别？

在SQL中，LIMIT关键字用于限制查询结果的行数。其基本语法如下：

SELECT column1, column2, ...
FROM table_name
LIMIT [offset,] row_count;

其中，row_count表示要返回的行数，offset表示要跳过的行数。如果省略offset，则默认从第一行开始返回。如果省略row_count，则默认返回所有符合条件的行。对于LIMIT 0,3和LIMIT 3，它们的区别在于是否指定了offset参数。具体来说：

LIMIT 0,3		-- 表示从第一行开始返回，跳过0行，返回3行数据，即返回第1行到第3行的数据。
LIMIT 3		   -- 表示从第一行开始返回，跳过0行，返回3行数据，即返回第1行到第3行的数据。

因此，LIMIT 0,3和LIMIT 3在结果上是相同的，它们都返回了前3行数据。但是，当需要从第n行开始返回数据时，需要使用LIMIT n-1,m，其中n-1表示要跳过的行数，m表示要返回的行数。

在DELETE语句中使用LIMIT时，通常不需要使用offset参数。因为DELETE语句只涉及到删除行，而不是返回行，所以不存在需要跳过的行的情况。因此，DELETE语句中的LIMIT只需要指定要删除的最大记录数，而不需要指定要跳过的行数。其基本语法如下：

DELETE FROM table_name
WHERE condition
LIMIT row_count;

82、SQL中exist和in的区别

(1) exist

exist是一个用于判断子查询是否返回了结果的条件表达式。它用于判断主查询的结果集中是否存在与子查询相关的记录。如果子查询返回的结果集非空，那么exist返回的结果为真（即1），否则返回的结果为假（即0）。

EXISTS是一个条件表达式，其结果只有两种情况：真和假。如果子查询返回非空结果集，则EXISTS的结果为真；如果子查询返回空结果集，则EXISTS的结果为假。因此，在使用EXISTS时，通常会将其作为WHERE子句中的一个条件，用于判断是否存在符合条件的记录。例如：

SELECT * FROM employees WHERE EXISTS (SELECT * FROM salaries WHERE salaries.employee_id = employees.employee_id)

如果在这个查询中，子查询SELECT * FROM salaries WHERE salaries.employee_id = employees.employee_id返回了非空结果集，那么主查询的EXISTS条件为真，表示存在符合条件的员工记录。在这种情况下，主查询会返回所有满足条件的员工记录，即所有存在工资记录的员工信息。

具体来说，如果一个员工存在工资记录，那么他的信息会被返回，如果一个员工不存在工资记录，那么他的信息不会被返回。返回的结果集中每一条记录都是一个满足条件的员工记录，包含了员工的所有信息。

(2) in

in是一个用于指定条件表达式的取值范围的关键字。它用于指定一个值列表，并判断某个字段的值是否在这个值列表中。如果是，则满足条件，否则不满足条件。例如，SELECT * FROM orders WHERE customer_id IN ('C001', 'C002')表示查询客户编号为C001或C002的订单记录。

总之，exist是一个用于判断子查询是否返回结果的条件表达式，而in是一个用于指定条件表达式取值范围的关键字。两者的含义和用法完全不同，不能混淆。

83、自然连接和等值连接的区别

自然连接是一种特殊的等值连接。

自然连接是一种连接操作，它会自动匹配两个表中具有相同名称和数据类型的列，并将这些列作为连接条件进行连接。自然连接不需要指定连接条件，因为连接条件已经隐含在表中的列名和数据类型中了。自然连接的结果是一个新表，其中包含了两个原表中所有匹配的行。

等值连接是一种连接操作，它需要明确指定一个或多个连接条件，这些连接条件是两个表中具有相同值的列。等值连接的结果是一个新表，其中包含了两个原表中所有匹配的行，但连接条件必须显式地指定。

自然连接会自动匹配两个表中具有相同名称和数据类型的列，而等值连接需要明确指定连接条件。

84、MySQL中不等于的表示

MySQL中可以使用"!="来表示不等于，还可以使用"<>"来表示不等于。这两种符号都可以用于比较任何类型的数据，包括数字、字符串、日期等。例如，以下SQL语句可以查询出表中所有age不等于20的记录：

SELECT * FROM table_name WHERE age != 20;

如果要查询出表中所有age不等于20且name不等于'John'的记录，可以使用以下SQL语句：

SELECT * FROM table_name WHERE age != 20 AND name != 'John';

85、模式

教材P29、P81

数据库中的模式（Schema）是指数据库中的一个逻辑结构，用于描述数据库中所有数据的组织方式和关系。模式包括表、视图、索引、约束、触发器等数据库对象的定义，它们之间的关系和交互操作规则，以及数据之间的联系。

在数据库中，每个模式都有一个唯一的名称，用于标识该模式。模式是数据库的重要组成部分，可以用来对数据库进行分类和管理，方便用户对数据库中的数据进行访问和操作。

86、数据库中断言和触发器的区别

数据库中断言和触发器都是用于约束数据的工具，但它们有以下区别：

编号	断言	触发器
1	当知道给定的特定条件总是为真时，可以使用断言。	即使特定条件可能是也可能不是，可以使用触发器
2	当SQL条件不满足时，整个表甚至数据库都有可能被锁定。	如果查询条件不成立，触发器可以捕获错误。
3	断言没有链接到特定的表或事件。它执行用户指定或定义的任务。	它有助于维护数据库表中的完整性约束，尤其是在未定义主键和外键约束时。
4	断言不维护对表中所做更改的任何跟踪。	触发器跟踪表中发生的所有更改。
5	与触发器相比，断言的语法更小。	它们有很大的语法来指示创建的触发器的每一个特定的。
6	现代数据库不使用断言。	触发器在现代数据库中得到了很好的应用。

断言不能修改数据，它们不链接到数据库中的任何特定表或事件，但触发器更强大，因为它们可以检查条件并修改数据库内表中的数据，这与断言不同。

87、数据库的事务中重做和回滚有什么区别？

重做针对之前已经完成（已提交）的事务，是为了数据恢复而进行重做事务。撤销和回滚是针对未完成（未提交）的事务，是为了保证事务的原子性而操作的。

在数据库事务中，重做（Redo）和回滚（Rollback）是两个重要的概念，它们分别用于保证事务的可靠性和一致性。

**重做是指将事务对数据库的修改操作写入到日志文件中，以便在发生故障时进行恢复。**重做是由数据库引擎自动完成的，它会将已提交的事务修改操作写入到日志文件中，并在数据库崩溃后重新执行这些操作，以保证数据库的一致性。

**回滚是指撤销事务对数据库的修改操作，以确保事务的原子性。**回滚可以由数据库管理员或应用程序开发人员手动执行，也可以由数据库引擎自动执行。当事务被回滚时，数据库引擎会撤销该事务对数据库的修改操作，并将数据库恢复到事务开始前的状态。

重做和回滚的区别在于它们的作用对象和目的不同。重做是为了保证数据库的可靠性，一旦数据库崩溃或发生其他故障，可以通过重做来恢复数据。而回滚是为了保证事务的一致性，一旦事务执行失败或被撤销，可以通过回滚来撤销该事务对数据库的修改操作，以确保数据库的一致性。

88、数据库的事务中回滚和撤销有什么区别？

回滚（Rollback）指的是将整个事务中的所有操作全部撤销，回到事务开始之前的状态，恢复数据库的一致性。回滚是一个高级别的操作，它可以用来撤销整个事务的修改，而不是针对单个操作的撤销。

撤销（Undo）指的是撤销一个操作的修改，将其还原为修改前的状态。撤销是一个低级别的操作，它可以用来撤销单个操作的修改，而不是撤销整个事务的修改。

89、介绍一下多版本控制（MVCC）

多版本并发控制（Multi-Version Concurrency Control，MVCC）是一种数据库管理系统中的并发控制技术，它通过为每个事务创建一个可见版本来实现事务之间的隔离。每个事务只能看到它自己创建的可见版本，而不能看到其他事务创建的版本。这样就能够避免读写冲突和脏读等并发问题，提高了数据库系统的并发性能和可靠性。

MVCC的实现基本上都是基于快照隔离的，即每个事务在开始时都会创建一个数据库的快照，当事务执行读操作时，它只能看到相应快照中的数据，而不能看到其他事务已经修改的数据。当事务执行写操作时，它会创建一个新的可见版本，这个版本包含了当前的修改和之前的快照数据。其他事务可以看到之前的版本，但是不能看到当前的版本，直到当前事务提交或者回滚之后，版本才会被清除或者标记为不可见。

MVCC的优点在于它能够提高数据库系统的并发性能和可靠性，尤其是在高并发的情况下，它可以有效地避免读写冲突和脏读等并发问题。此外，MVCC还支持多版本的读操作，可以在不阻塞写操作的情况下，同时允许多个事务进行读操作。

MVCC的缺点在于它需要占用更多的存储空间，因为每个事务都需要创建一个版本，而这些版本需要存储在数据库中。此外，MVCC还需要更复杂的实现机制，因为它需要实现版本的创建、标记和清除等功能。

90、关系代数中选择和投影的区别

投影是对列操作，而选择是对行的操作。

投影是从一个关系中选择出一部分属性，形成一个新的关系，而新的关系中元组的个数与原来的关系相同，只是每个元组包含的属性被减少了。（行数没变，列数减少了）

选择则是从一个关系中选择出符合某些条件的元组，形成一个新的关系，而新的关系中包含的元组只有符合条件的元组。（列数没变，行数减少了）

在SQL语句中，选择和投影对应的是SELECT语句。

例如，以下是一个使用SELECT语句实现选择操作的示例：

SELECT * FROM student WHERE age > 20;		-- 选择操作

该语句从名为"student"的表格中选择出所有年龄大于20岁的学生信息，其中"*"表示选择所有列。

以下是一个使用SELECT语句实现投影操作的示例：

SELECT name, score FROM student;	-- 投影操作

该语句从名为"student"的表格中选择出学生姓名和成绩两列，形成一个新的表格。

91、视图可以进行插入操作吗

视图是一个虚拟的表格，其内容是从一个或多个基本表格中选择、筛选、计算等得到的，因此，对于某些视图，可以进行插入操作，但并不是所有的视图都可以进行插入操作，且所谓的插入操作实际上是对基本表格进行了插入操作，而不是对视图本身进行了插入操作。

视图的插入操作可能会影响到多个基本表格，因此需要谨慎操作，避免数据出现冲突或不一致的情况。

92、关系模型中的三类完整性约束

实体完整性（Entity Integrity）
参照完整性（Referential Integrity）
用户自定义完整性（User-defined Integrity）

93、SUBSTRING_INDEX()

SUBSTRING_INDEX()用于返回指定字符串中某个子字符串在第 N 次出现时之前或之后的所有字符。

SUBSTRING_INDEX(str, delim, count)

str：要进行操作的字符串，可以是一个字符串常量、一个列名或一个字符串表达式。
delim：子字符串的分隔符，可以是一个字符串常量或一个列名。
count：要返回的子字符串的个数，可以为正数或负数。如果为正数，则返回第 N 次出现之前的所有字符；如果为负数，则返回第 N 次出现之后的所有字符。

例如，如果有一个字符串 "www.example.com"，想要返回该字符串中第二个 "." 之前的所有字符，可以使用以下语句：

SELECT SUBSTRING_INDEX('www.example.com', '.', 2);

该语句的返回结果为 "www.example"

94、REPLACE()

REPLACE() 是一个字符串函数，用于替换字符串中的子字符串。

REPLACE(str, old_substring, new_substring)

str：要进行操作的字符串，可以是一个字符串常量、一个列名或一个字符串表达式。
old_substring：要被替换的子字符串，可以是一个字符串常量或一个列名。
new_substring：替换后的新子字符串，可以是一个字符串常量或一个列名。

95、SUBSTR()

这个函数的字符串的下标从1开始。

SUBSTR() 用于返回指定字符串中从指定位置开始的一定长度的子字符串。

SUBSTR(str, start, length)

str：要进行操作的字符串，可以是一个字符串常量、一个列名或一个字符串表达式。
start：要返回的子字符串的起始位置，可以是一个整数常量或一个列名。如果为正数，则表示从字符串的左侧开始计算位置；如果为负数，则表示从字符串的右侧开始计算位置。
length：要返回的子字符串的长度，可以是一个整数常量或一个列名。如果省略该参数，则函数将返回从起始位置到字符串末尾的所有字符。

例如，如果有一个字符串 "Hello, World!"，想要返回该字符串中从第 7 个字符开始的 5 个字符，可以使用以下语句：

SELECT SUBSTR('Hello, World!', 7, 5);

该语句的返回结果为 "World"。

96、TRIM()

TRIM() 是一个字符串函数，用于去除字符串中的指定字符（默认为空格字符）或字符集。其语法如下：

TRIM([BOTH | LEADING | TRAILING] [removal_chars FROM] str)

BOTH、LEADING、TRAILING：可选参数，用于指定去除字符串的位置。BOTH 表示从字符串的两端去除指定字符，LEADING 表示只从字符串的左侧去除指定字符，TRAILING 表示只从字符串的右侧去除指定字符。如果省略该参数，则默认为 BOTH。
removal_chars：可选参数，用于指定要去除的字符或字符集。如果省略该参数，则默认为去除空格字符。
str：要进行操作的字符串，可以是一个字符串常量、一个列名或一个字符串表达式。

（1）如果有一个字符串 " Hello, World! "，想要去除字符串两端的空格字符，可以使用以下语句：

SELECT TRIM('  Hello, World!   ');

该语句的返回结果为 "Hello, World!"。

（2）假设有一个字符串 "abacadaeaf"，想要去除其中的字母 "a"，可以使用以下语句：

SELECT TRIM('a' FROM 'abacadaeaf');

该语句的返回结果为 "bacadef"。

97、ROW_NUMBER()

ROW_NUMBER()就是弄一个行号。

在 MySQL 中，ROW_NUMBER() 是一个窗口函数，用于为查询结果中的每一行分配一个唯一的行号。

ROW_NUMBER() OVER ([PARTITION BY partition_expression, ...]
                   ORDER BY sort_expression [ASC | DESC], ...)

PARTITION BY：可选项，用于指定分区表达式。将查询结果分成多个分区，并在每个分区中为行分配行号。如果省略该选项，则所有行都在同一个分区中。
ORDER BY：必选项，用于指定排序表达式。按照指定的表达式进行排序，以便分配行号。可以指定多个排序表达式，以便在遇到相同值时进行进一步排序。
ASC 或 DESC：可选项，用于指定排序顺序。默认为 ASC 升序排列。

例如，如果有一个名为 students 的表，包含学生的姓名和成绩信息，想要为该表中的每一行分配一个行号，可以使用以下语句：

SELECT ROW_NUMBER() OVER (ORDER BY score DESC) AS rank, name, score
FROM students;

该语句的返回结果将包含每个学生的排名、姓名和成绩信息，按照成绩从高到低进行排序，并为每一行分配一个唯一的行号。

98、ROW_NUMBER()`、`RANK()、DENSE_RANK()的区别

在 MySQL 中，ROW_NUMBER()、RANK() 和 DENSE_RANK() 都是窗口函数，用于为查询结果中的每一行分配一个唯一的行号或排名。它们的主要区别在于如何处理相同排名的情况。

ROW_NUMBER()

ROW_NUMBER() 为查询结果中的每一行分配一个唯一的行号，行号从 1 开始依次递增。与 RANK() 和 DENSE_RANK() 不同的是，ROW_NUMBER() 不会跳过相同排名的行，而是为每一行都分配一个唯一的行号。因此，如果有多个行具有相同的排名，则它们的行号也将不同。

RANK()

RANK() 为查询结果中的每一行分配一个排名，排名从 1 开始依次递增。如果有多个行具有相同的排名，则它们将具有相同的排名，并且下一个排名将从相同数量的行之后开始。即如果有两个行的排名为 1，则下一个行的排名将为 3。因此，使用 RANK() 函数可能会跳过一些排名。

DENSE_RANK()

DENSE_RANK() 类似于 RANK()，也为查询结果中的每一行分配一个排名，排名从 1 开始依次递增。与 RANK() 不同的是，DENSE_RANK() 不会跳过相同排名的行，而是为它们分配相同的排名。因此，使用 DENSE_RANK() 函数不会跳过任何排名。

例如，如果有一个名为 students 的表，包含学生的姓名和成绩信息，想要为该表中的每一行分配一个排名，可以使用以下语句：

SELECT name, score,
       ROW_NUMBER() OVER (ORDER BY score DESC) AS row_number,
       RANK() OVER (ORDER BY score DESC) AS rank,
       DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
FROM students;

该语句的返回结果将包含每个学生的姓名、成绩以及使用 ROW_NUMBER()、RANK() 和 DENSE_RANK() 函数分配的行号和排名信息。

99、join examination_info using(exam_id)

JOIN 是用于将两个或多个表中的行连接起来的操作，USING 是 JOIN 操作的一个关键字，用于指定连接的列。

JOIN examination_info USING(exam_id) 表示将当前表与 examination_info 表连接起来，并指定连接列为 exam_id。

100、JOIN ... USING`和`JOIN ... ON的区别

JOIN ... USING 是使用连接列的名称作为连接条件的，而 JOIN ... ON 是使用任意表达式作为连接条件的。因此，JOIN ... USING 只能用于指定两个表中共同拥有的列作为连接条件，而 JOIN ... ON 可以使用任何表达式作为连接条件，包括两个表中不同名称的列、函数或运算符等。

101、date_format()

DATE_FORMAT() 函数用于将日期类型的数据格式化成指定的字符串格式。该函数的语法如下：

DATE_FORMAT(date, format)

date 参数是表示日期或日期时间的字段或表达式，format 参数是指定的日期格式字符串。

例如，要将日期格式化为 "YYYY-MM-DD" 格式的字符串，可以使用以下语句：

SELECT DATE_FORMAT(date_column, '%Y-%m-%d') AS formatted_date FROM table_name;

在这个语句中，date_column 是日期类型的列名，%Y-%m-%d 是日期格式字符串，formatted_date 是格式化后的日期字符串的别名。

以下是常用的日期格式字符串及其含义：

%Y：四位数的年份，例如 2023
%y：两位数的年份，例如 23
%m：月份，01-12
%d：月份中的第几天，01-31
%H：小时，00-23
%h：小时，01-12
%i：分钟，00-59
%s：秒数，00-59
%p：AM 或 PM

例如，以下是一些常见的日期格式化示例：

SELECT DATE_FORMAT(date_column, '%Y-%m-%d') AS formatted_date FROM table_name; -- 格式化为 YYYY-MM-DD
SELECT DATE_FORMAT(date_column, '%Y-%m-%d %H:%i:%s') AS formatted_date FROM table_name; -- 格式化为 YYYY-MM-DD HH:MM:SS
SELECT DATE_FORMAT(date_column, '%W, %M %e, %Y') AS formatted_date FROM table_name; -- 格式化为 Weekday, Month Day, Year

如果 date 参数不是日期类型的值，则会返回 NULL。此外，format 参数中的格式化字符必须用单引号括起来。

102、coalesce()

COALESCE() 用于返回参数列表中的第一个非空值。如果所有参数均为 NULL，那么 COALESCE() 返回 NULL。

COALESCE(value1, value2, ..., valuen)

value1 到 valuen 是要比较的值，可以是字段、表达式、常量等。COALESCE() 返回 value1 到 valuen 中第一个非空的值。

例如，假设存在一个表 employees，其中包含员工的姓名、手机号和邮箱，但是有些员工的手机号或邮箱可能为空。我们可以使用 COALESCE() 函数来选择一个非空的联系方式，例如：

SELECT
    name,
    COALESCE(phone, email) AS contact
FROM
    employees;

在上面的查询语句中，如果 phone 不为空，则选择 phone，否则选择 email 作为联系方式。这样可以确保在输出结果时至少存在一个非空的联系方式。

COALESCE() 还可以用于替换为特定值，例如：

SELECT
    name,
    COALESCE(phone, '暂无联系方式') AS contact
FROM
    employees;

在上面的查询语句中，如果 phone 不为空，则选择 phone，否则选择字符串 '暂无联系方式' 作为联系方式。

103、dayofmonth()

DAYOFMONTH() 用于获取指定日期的月份中的天数。

DAYOFMONTH(date)

其中，date 是一个日期或日期时间值，可以是一个字段、常量或表达式。DAYOFMONTH() 函数返回一个整数，表示指定日期的月份中的天数，范围从 1 到 31。

例如，假设存在一个表 orders，其中包含订单的编号和下单日期。我们可以使用 DAYOFMONTH() 函数来统计每个月份中的订单数量，例如：

SELECT
    YEAR(order_date) AS order_year,
    MONTH(order_date) AS order_month,
    DAYOFMONTH(order_date) AS order_day,
    COUNT(*) AS order_count
FROM
    orders
GROUP BY
    order_year, order_month, order_day;

在上面的查询语句中，使用了 YEAR()、MONTH() 和 DAYOFMONTH() 函数来分别获取订单的年份、月份和日期，然后对这三个字段进行分组，以便于统计每个日期的订单数量。

104、with rollup

WITH ROLLUP 用于在查询结果中添加汇总行。它的作用是在每个分组结束时添加一行汇总数据，显示所有分组数据的总和。WITH ROLLUP 通常与 GROUP BY 子句一起使用，例如：

SELECT
    category,
    subcategory,
    SUM(sales) AS total_sales
FROM
    sales
GROUP BY
    category, subcategory WITH ROLLUP;

在上面的查询语句中，我们使用了 GROUP BY 子句将 sales 表按照 category 和 subcategory 两个字段进行分组，并计算每个分组的销售总额。使用 WITH ROLLUP 扩展语法后，查询结果将包含汇总数据，例如：

+-----------+--------------+-------------+
| category  | subcategory  | total_sales |
+-----------+--------------+-------------+
| A         | X            |        1000 |
| A         | Y            |        2000 |
| A         | NULL         |        3000 |  -- 汇总行
| B         | X            |        1500 |
| B         | Y            |        2500 |
| B         | NULL         |        4000 |  -- 汇总行
| NULL      | NULL         |        7000 |  -- 汇总行
+-----------+--------------+-------------+

在上面的查询结果中，汇总行的 category 和 subcategory 均为 NULL，表示所有数据的总和。total_sales 列为整个表的销售总额。with rollup会将总体的数据单独统计为一行，但因为其是总体数据，所以不会根据分组来查询对应的数据，因此该函数对应的分组字段为"NULL"

105、group_concat()

GROUP_CONCAT() 用于将分组后的多行数据合并为单行，并以逗号分隔。

GROUP_CONCAT(expr [ORDER BY {unsigned_integer | col_name | expr}
            [ASC | DESC] [,col_name ...]]
            [SEPARATOR str_val])

expr 是要合并的字段、常量或表达式；ORDER BY 子句用于指定合并后的字符串排序方式；ASC 或 DESC 表示排序方式；SEPARATOR 子句用于指定多个值之间的分隔符，默认为逗号。

例如，假设我们有一个表 students，其中包含学生的姓名和所选的课程。我们可以使用 GROUP_CONCAT() 函数将每个学生所选的课程名称合并为一个字符串，并以逗号分隔。例如：

SELECT
    name,
    GROUP_CONCAT(course_name) AS courses
FROM
    students
GROUP BY
    name;

在上面的查询语句中，我们使用了 GROUP BY 子句将 students 表按照 name 字段进行分组，并使用 GROUP_CONCAT() 函数将每个学生所选的课程名称合并为一个字符串。查询结果如下：

+------+-----------------------+
| name | courses               |
+------+-----------------------+
| Alice| Math,Physics          |
| Bob  | English,History,Math  |
| Tom  | Biology,Chemistry,Math|
+------+-----------------------+

在上面的查询结果中，每个学生的课程名称以逗号分隔，合并为一个字符串。

106、concat_ws()

CONCAT_WS() 用于将多个字符串或字段进行连接，并使用指定的分隔符进行分隔。它的语法如下：

CONCAT_WS(separator, str1, str2, ..., strN)

其中，separator 是指定的分隔符，str1 到 strN 是要连接的字符串或字段。如果某个参数为 NULL，则该参数将被忽略。

例如，我们有一个 students 表，其中包含学生的姓名、年龄和性别。我们可以使用 CONCAT_WS() 函数将这三个字段连接为一个字符串，并使用逗号进行分隔，例如：

SELECT CONCAT_WS(',', name, age, gender) AS student_info
FROM students;

在上面的查询语句中，我们使用了 CONCAT_WS() 函数将 name、age 和 gender 字段进行连接，并使用逗号进行分隔。查询结果如下：

+------------------+
|   student_info   |
+------------------+
| Alice,18,Female  |
|   Bob,20,Male    |
|  Carol,19,Female |
+------------------+

在上面的查询结果中，每个学生的姓名、年龄和性别已经合并为一个字符串，并以逗号分隔。

CONCAT_WS() 函数与 CONCAT() 函数的区别在于，CONCAT() 函数在连接字符串时不会添加分隔符，而 CONCAT_WS() 函数则会在字符串之间添加指定的分隔符。

107、date()

DATE() 用于从日期和时间值中提取日期部分。

DATE(date)

date 是一个日期时间值或可以被转换为日期时间值的表达式，如2022-05-06 15:30:00。

108、TIME()

TIME()用于从日期时间值中提取时间部分。

109、union可以使用任何selcet语句，但order by子句只能在最后一次使用。如果想要对未union前两个sql语句的查询结果进行排序，分别单独排序需要的数据，查出以后再使用union连接，如：

select * from
( select * from t1  order by 字段 )t1 -- 一定要对表重新命名，否则报错 
union select * from
( select * from t2  order by 字段 )t2

110、TimeStampDiff()

TIMESTAMPDIFF() 用于计算两个日期时间值之间的时间差。

TIMESTAMPDIFF(unit, start_datetime, end_datetime)

unit 表示时间差的单位，可以是以下值之一：

MICROSECOND：微秒
SECOND：秒
MINUTE：分钟
HOUR：小时
DAY：天
WEEK：周
MONTH：月
QUARTER：季度
YEAR：年

start_datetime 和 end_datetime 分别表示两个日期时间值，可以是实际的日期时间值、日期时间列或可以被转换为日期时间值的表达式。

例如，有一个 orders 表，其中包含订单的下单时间和发货时间。我们可以使用 TIMESTAMPDIFF() 函数计算出每个订单的发货时间与下单时间之间的时间差，例如：

SELECT order_id, TIMESTAMPDIFF(HOUR, order_time, ship_time) AS delivery_time
FROM orders;

在上面的查询语句中，我们使用了 TIMESTAMPDIFF() 函数计算出每个订单的发货时间与下单时间之间的时间差，并以小时为单位进行统计。查询结果如下：

+----------+---------------+
| order_id | delivery_time |
+----------+---------------+
|        1 |            24 |
|        2 |            48 |
|        3 |            72 |
+----------+---------------+

在上面的查询结果中，每个订单的发货时间与下单时间之间的时间差已经计算出来，并以小时为单位进行了统计。

TIMESTAMPDIFF() 函数返回的时间差值是一个整数，表示两个日期时间值之间的时间差。如果需要计算两个日期时间值之间的实际时间间隔，可以使用 TIMEDIFF() 函数。

111、TIMEDIFF()

TIMEDIFF() 用于计算两个时间值之间的时间差。

TIMEDIFF(time1, time2)

time1 和 time2 是两个时间值或可以被转换为时间值的表达式。TIMEDIFF() 函数返回一个时间差值，表示 time1 与 time2 之间的时间差。

例如，有一个 orders 表，其中包含订单的下单时间和发货时间。我们可以使用 TIMEDIFF() 函数计算出每个订单的发货时间与下单时间之间的实际时间间隔，例如：

SELECT order_id, TIMEDIFF(ship_time, order_time) AS delivery_time
FROM orders;

在上面的查询语句中，使用了 TIMEDIFF() 函数计算出每个订单的发货时间与下单时间之间的实际时间间隔。查询结果如下：

+----------+---------------+
| order_id | delivery_time |
+----------+---------------+
|        1 |      01:00:00 |
|        2 |      02:00:00 |
|        3 |      03:00:00 |
+----------+---------------+

在上面的查询结果中，每个订单的发货时间与下单时间之间的实际时间间隔已经计算出来，并以 hh:mm:ss 的格式进行了显示。

112、cast()

CAST() 用于将一个表达式转换为指定的数据类型。它的语法如下：

CAST(expression AS data_type)

expression 是需要转换的表达式，可以是任何数据类型的表达式，例如数值、日期、字符串等。data_type 是需要转换成的目标数据类型，可以是以下数据类型之一：

BINARY：二进制字符串
CHAR：定长字符串
DATE：日期
DATETIME：日期时间
DECIMAL：定点数
SIGNED：有符号整数
UNSIGNED：无符号整数

例如，有一个 orders 表，其中包含订单的下单时间和订单金额。我们可以使用 CAST() 函数将订单金额转换为 DECIMAL 类型，并计算出每个订单的平均金额，例如：

SELECT order_id, CAST(order_amount AS DECIMAL(10,2)) AS amount, AVG(CAST(order_amount AS DECIMAL(10,2))) AS avg_amount
FROM orders;

在上面的查询语句中，我们使用了 CAST() 函数将订单金额转换为 DECIMAL 类型，并计算出每个订单的平均金额。查询结果如下：

+----------+--------+------------+
| order_id | amount | avg_amount |
+----------+--------+------------+
|        1 |  100.00|     150.00 |
|        2 |  200.00|     150.00 |
|        3 |  250.00|     150.00 |
+----------+--------+------------+

在上面的查询结果中，每个订单的订单金额已经转换为 DECIMAL 类型，并以两位小数的格式进行了显示。DECIMAL(10,2) 是一个数据类型，表示一个精度为 10 位，小数点后保留 2 位的定点数。

CAST() 函数只能用于将一个表达式转换为指定的数据类型，而不能用于将整个列或表中的数据类型进行转换。如果需要对整个列或表中的数据类型进行转换，可以使用 ALTER TABLE 语句或其他相关的数据类型转换函数。

113、CHAR_LENGTH()

CHAR_LENGTH() 用于计算字符串的字符数，汉字、数字、字母都算一个字符。

CHAR_LENGTH(str)

例如，有一个字符串变量 str，它的值为 'Hello, world!'，我们可以使用 CHAR_LENGTH() 函数计算出这个字符串的字符数，例如：

SELECT CHAR_LENGTH('Hello, world!') AS len;

查询结果如下：

+-----+
| len |
+-----+
|  13 |
+-----+

在上面的查询语句中，使用了 CHAR_LENGTH() 函数计算出字符串 'Hello, world!' 的字符数，并将计算结果命名为 len。查询结果显示该字符串的字符数为 13。

CHAR_LENGTH() 函数计算的是字符串的字符数，而不是字节数。length(str)计算字节数。

114、concat()

CONCAT() 用于将两个或多个字符串连接起来。

CONCAT(str1, str2, ...)

str1、str2 等是需要连接的字符串，可以是任意数量的字符串，用逗号分隔。

例如，有一个名为 users 的表，其中包含用户名和邮箱地址。可以使用 CONCAT() 函数将用户名和邮箱地址连接起来，生成完整的邮箱地址，例如：

SELECT CONCAT(username, '@', email_domain) AS email
FROM users;

在上面的查询语句中，使用了 CONCAT() 函数将用户名和邮箱域名连接起来，生成完整的邮箱地址，并将结果命名为 email。查询结果如下：

+------------------------+
| email                  |
+------------------------+
| john@example.com       |
| mary@example.com       |
| david@example.com      |
| jane.doe@example.com   |
+------------------------+

在上面的查询结果中，每个用户的用户名和邮箱域名已经通过 CONCAT() 函数连接起来，生成了完整的邮箱地址。

CONCAT() 函数可以将任意数量的字符串连接起来，但是如果其中有一个参数为 NULL，则整个结果将返回 NULL。如果需要处理 NULL 值，可以使用 IFNULL() 函数或者 COALESCE() 函数对参数进行处理，确保不会出现 NULL 值。

115、rlike

RLIKE是一个用于模式匹配的操作符，允许使用正则表达式来匹配文本数据。RLIKE操作符用于比较一个字符串是否与一个正则表达式匹配，如果匹配返回1，否则返回0。

SELECT * FROM my_table WHERE my_column RLIKE '^abc.*[0-9]$'	-- 匹配abc123、abc12345678

上面的例子中使用RLIKE来查找在my_column列中以abc开头并以数字结尾的所有行。这里的^和$符号分别表示字符串的开头和结尾，.*表示匹配任意字符任意次数，[0-9]表示匹配0到9之间的任意数字。

在MySQL的正则表达式中，+是一个元字符，表示匹配前面的表达式一次或多次。例如，在正则表达式[a-z]+中，+表示匹配一个或多个小写字母。

[a-z]+和.*[a-z]的匹配结果一样吗

不完全一样。[a-z]+表示匹配一个或多个小写字母，而.*[a-z]表示匹配任意数量（包括0个）的字符，后面跟着一个小写字母。

例如，对于字符串abc123def，[a-z]+将匹配abc和def，而.*[a-z]将匹配整个字符串。

.*是贪婪匹配，它会尽可能多地匹配字符，直到无法匹配为止。因此，如果字符串中有多个小写字母，.*[a-z]可能会匹配整个字符串，而[a-z]+只会匹配每个小写字母之间的部分。

如果匹配abc和def之间的部分，可以使用[a-z]+.*[a-z]+这样的正则表达式。

116、case表达式

CASE表达式是一种条件表达式，用于在满足一定条件时返回不同的结果。

CASE expression
    WHEN value1 THEN result1
    WHEN value2 THEN result2
    ...
    ELSE default_result
END

expression是需要检查的表达式，可以是一个列名、一个常量或一个表达式。value1、value2等是可选的条件值，如果expression的值等于某个条件值，则返回对应的result；否则，如果存在ELSE子句，则返回default_result，否则返回NULL。

CASE表达式也可以使用以下形式：

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE default_result
END

这种形式下，CASE表达式只包含条件表达式，而不包含待检查的表达式。每个条件表达式都是一个布尔表达式，如果该表达式成立，则返回对应的result，否则继续检查下一个条件表达式，直到找到一个成立的条件或者所有条件都被检查完毕。如果所有条件都不成立，则返回default_result，如果没有指定ELSE子句，则返回NULL。如：

case
	when score >= 90 then '优'
	when score >= 75 then '良'
	when score >= 60 then '中'
	else '差'
end

117、count over()

count(*) over (
    partition by level	-- 使用PARTITION BY子句来指定以level列进行分组
) as total
-- 以上代码将会计算每个不同level值的行数，并在查询结果中作为新的一列展示，列名为total

COUNT()函数可以与OVER()子句结合使用，形成COUNT() OVER()语法，这样可以在结果集中添加一列，显示每个行分组中符合条件的行数。OVER()子句用于指定在计算行数时应该使用哪些行作为分组的范围。

例如，假设有一个名为orders的表，其中包含订单信息（订单号、客户ID、订单日期等）。想要计算每个客户的订单数量，并将其作为新的列添加到结果集中。我们可以使用以下SQL语句：

SELECT customer_id, COUNT(*) OVER(PARTITION BY customer_id) as order_count FROM orders;

在这个例子中，COUNT(*) OVER(PARTITION BY customer_id)表示在每个不同的customer_id分组中，计算符合条件的行数。这个新的列将被命名为order_count，并添加到查询结果集中。这样就可以轻松地计算每个客户的订单数量，而不需要使用子查询或者连接操作。

COUNT()函数既可以是聚合函数，也可以是窗口函数，具体取决于它的使用方式。在这里COUNT()是窗口函数中的一种，它用于计算指定列或所有列的行数。窗口函数可以与OVER()子句结合使用，以指定计算聚合函数时的行范围，从而对查询结果进行更细粒度的控制。

118、强制转换函数

cast(value as type) convert(value, type)

type类型选择：

binary：二进制类型； char：字符类型； date：日期类型； time：时间类型； datetime：日期时间类型； decimal：浮点型； signed：整型； unsigned：无符号整型。

119、last_day()

last_day(time)函数返回指定日期对应月份的最后一天。

120、length(str)

length(str)函数：计算字节。数字、字母都占一个字节。utf8编码中一个汉字三个字节，gbk编码中一个汉字两个字节。

121、locate()

LOCATE() 函数用于在一个字符串中查找另一个字符串，并返回它在原始字符串中第一次出现的位置。

LOCATE(substr, str[, start])

substr 是要查找的子字符串，str 是要在其中查找子字符串的原始字符串，start 是可选的参数，指定查找的起始位置，默认为 1。

如果查找到子字符串，则返回在原始字符串中的位置，如果没有找到，则返回 0。

例如，假设有一个名为 "product_name" 的列，其中包含产品名称和型号，可以使用 LOCATE() 函数将它们分离出来。例如：

SELECT LEFT(product_name, LOCATE(' ', product_name) - 1) AS product, RIGHT(product_name, LENGTH(product_name) - LOCATE(' ', product_name)) AS model
FROM products;

这将返回一个包含产品和型号的查询结果集，其中使用 LOCATE() 函数将产品名称和型号分离出来。

122、lag()

LAG() 函数用于获取前一行的值。它可以用于在查询中检索上一行的值，以及与当前行进行比较。

LAG(value, offset, default) OVER (ORDER BY expression)

其中，value 是要获取前一行值的列或表达式，offset 是指定要获取前面第几行的值（默认为 1），default 是可选的参数，指定当前行没有前一行时的默认值（默认为 NULL），expression 是用于指定排序顺序的表达式。

例如，考虑以下简单的 sales 表：

+----+-------+------------+
| id | sales |    date    |
+----+-------+------------+
|  1 |  1000 | 2021-01-01 |
|  2 |   800 | 2021-02-01 |
|  3 |  1200 | 2021-03-01 |
|  4 |   900 | 2021-04-01 |
+----+-------+------------+

要检索每个月的销售量与前一个月的销售量之间的差值，可以使用 LAG() 函数，如下所示：

SELECT 
    date,
    sales,
    LAG(sales, 1, 0) OVER (ORDER BY date) AS prev_sales,
    sales - LAG(sales, 1, 0) OVER (ORDER BY date) AS diff
FROM sales;

这将返回一个包含每个月销售量、前一个月销售量、以及两者之间的差异的结果集。

如果要在使用 LAG() 函数时指定特定的行数，可以将 offset 参数设置为大于 1 的值。如果要检索下一行的值而不是前一行的值，则可以使用 LEAD() 函数。

昵称

邮箱

网址

按正序
按倒序
按热度

MySQL查漏补缺

预览: