Mysql — 刷题知识点

SELECT col_name FROM tab_name UNION SELECT col_name FROM tab_name ORDER BY col_name；
SELECT col_name FROM tab_name UNION ALL SELECT col_name FROM tab_name ORDER BY col_name；

6. 字符串

CHARINDEX(): 这个函数用于返回一个字符串在另一个字符串中第一次出现的位置。语法通常为 CHARINDEX(substring, string)，其中 substring 是要查找的子字符串，而 string 是要在其中查找的字符串。

SELECT CHARINDEX('o', 'Hello, world!') AS CharIndexResult;

这将返回字符串 'o' 在 'Hello, world!' 中第一次出现的位置，结果是 5。
SUBSTRING(): SUBSTRING() 函数用于从字符串中提取子字符串。语法通常为 SUBSTRING(string, start, length)，其中 string 是要提取子字符串的原始字符串，start 是要开始提取的位置，length 是要提取的字符数。

SELECT SUBSTRING('Hello, world!', 1, 5) AS SubstringResult;

这将返回 'Hello'，从字符串 'Hello, world!' 的第一个字符开始，提取长度为 5 的子字符串。
STUFF(): STUFF() 函数用于将一个字符串的一部分替换为另一个字符串。语法通常为 STUFF(string, start, length, replacement)，其中 string 是要替换部分的原始字符串，start 是要替换的起始位置，length 是要替换的字符数，replacement 是用于替换的新字符串。

SELECT STUFF('Hello, world!', 7, 5, 'everyone') AS StuffResult;

这将返回 'Hello, everyone!'，它将 'world' 替换为 'everyone'，从字符串 'Hello, world!' 的第 7 个字符开始，替换长度为 5 的部分。
SOUNDEX(): SOUNDEX() 函数用于计算字符串的声音编码。这个编码用于将类似发音的字符串归类到同一组。它主要用于发音相似但拼写不同的词。语法通常为 SOUNDEX(string)，其中 string 是要计算声音编码的字符串。

SELECT SOUNDEX('Hello') AS SoundexResult;

这将返回 'H400'，这是字符串 'Hello' 的声音编码。

7. 行转列

使用 SQL 的条件聚合和CASE表达式来实现行转列的操作

DATE_ADD 是一个日期函数，用于在给定日期上添加指定的时间间隔。它的语法通常如下：

DATE_ADD(date, INTERVAL expr unit)

date: 要进行操作的日期或日期时间值。
expr: 要添加的时间间隔。
unit: 时间间隔的单位，如DAY, WEEK, MONTH, YEAR等。

举个例子，如果要将某个日期增加一天，可以这样使用：

DATE_ADD('2024-04-15', INTERVAL 1 DAY)这将返回 '2024-04-16'。

8.日期函数

datadiff（date1，date2）表示的是date1-date2的差值，前面的日期-后面的日期

二、join ... on 问题

1. right join ..on

RIGHT JOIN 关键字会返回右表 (t2) 所有的行，即使在左表 (t1) 中没有匹配的行。或者更多。如果右表有某记录与左表多条记录匹配（满足 on 子句条件）则 RIGHT JOIN 结果行数多于右表。

具体来说，如果此题再加一条记录 ('1114', '张三', '2000-08-06', '男'), 则答案会是 5 。

4行

2. full join

Mysql(版本8.0.25)不支持full join，使用union连接左连接和右连接，得到全连接

先right join了，所以后面left join时需要过滤出右表字段为NULL的记录，然后union all才是full join的结果。最后的where是先卡条件再做union all，并不是union all后再对所有结果卡where条件。

3. count不包含NULL记录

先left join了，所以后面right join时需要过滤出左表字段为NULL的记录，然后union all才是full join的结果。如果是对查询后的结果做筛选，需要把条件写在where中，不能写在on中。

FULL JOIN 关键字会从左表和右表那里返回所有的行。如果 "t1" 中的行在表 "t2" 中没有匹配，或者如果 "t2" 中的行在表 "t1" 中没有匹配，这些行同样会列出。

等价于左连接后再并上左边为空右表有值的那些记录。

或者左连接并上右连接的值然后去除重复的记录即 union

4 left

left join意思是包含inner join的结果（左右表中的birth、name都不为NULL时才会匹配上）

`5. CROSS JOIN`

CROSS JOIN是 SQL 中的一种连接（Join）操作，它会返回两个表的笛卡尔积，也就是将第一个表的每一行与第二个表的每一行进行组合。如果第一个表有 m 行，第二个表有 n 行，那么 CROSS JOIN 将返回 m × n 行。

三、排序

1. 最早的日期 + 分区

Rank()函数：为结果集分区中每一行分配一个排名，行等级由一加上前面的等级指定。

RANK（） OVER（ PARTITION BY　表达式 ##将结果集划分为分区 ORDER BY　表达式　［ASC｜DESC］ ##对分区内的进行排序）

SELECT player_id, device_id

FROM (SELECT *, RANK() OVER (PARTITION BY player_id ORDER BY event_date) AS rank_date FROM gamelist) t

WHERE t.rank_date = 1;

目的是从名为 gamelist 的表中选择每个玩家的第一个事件（按照事件日期排序）。

详细解释：

SELECT player_id, device_id: 这是查询语句的主体部分，指定了要从数据库中检索的列，即玩家ID和设备ID。
FROM (SELECT *, RANK() OVER (PARTITION BY player_id ORDER BY event_date) AS rank_date FROM gamelist) t: 这是一个子查询，它首先对 gamelist 表中的数据进行排名，并根据 player_id 进行分区。RANK() OVER (PARTITION BY player_id ORDER BY event_date) 这部分使用窗口函数 RANK() 对每个玩家的事件按照日期进行排序并分配一个排名。子查询使用别名 t 表示。
WHERE t.rank_date = 1: 这是主查询的过滤条件，它筛选出排名为1的行，即每个玩家的第一个事件。

四、存储

1. 知识点

https://www.cnblogs.com/zhoufangcheng0405/p/8028566.html

1. 创建存储过程

create procedure 过程名

@parameter [as ] 参数类型

。。。

begin

end

执行存储过程：execute 过程名

2. 触发事件

在 SQL 中，"触发器"（Trigger）是一种特殊的存储过程，它会在数据库中的特定事件发生时自动执行。这些事件可以是 INSERT、UPDATE 或 DELETE 操作。

删除触发器： DROP TRIGGER

建个存储过程

使用SQL语句建个存储过程proc_stu，然后以student表中的学号Stu_ID为输入参数@s_no，返回学生个人的指定信息。

CREATE PROCEDURE [stu].[proc_student]
@s_no【as】 int
AS
BEGIN
select * from stu.student where Stu_ID=@s_no
END

五、权限

1. 知识点

1.GRANT 赋于权限
常用的系统权限集合有以下三个:
CONNECT(基本的连接),   RESOURCE(程序开发),   DBA(数据库管理)
常用的数据对象权限有以下五个:
ALL   ON   数据对象名,   SELECT   ON   数据对象名,   UPDATE   ON   数据对象名,
DELETE   ON   数据对象名,     INSERT   ON   数据对象名,       ALTER     ON   数据对象名
GRANT   CONNECT,   RESOURCE   TO   用户名;
GRANT   SELECT   ON   表名   TO   用户名;
GRANT   SELECT,   INSERT,   DELETE   ON表名   TO   用户名1,   用户名2;
2.REVOKE   回收权限
REVOKE   CONNECT,   RESOURCE   FROM   用户名;
REVOKE   SELECT   ON   表名   FROM   用户名;
REVOKE   SELECT,   INSERT,   DELETE   ON表名   FROM   用户名1,   用户名2; ...

@'%' ：任何主机的通配符，@：划分用户名和主机， %：代表"任何"

user.*： user数据库的所有表

*.*：所有数据库的所有表

revoke ... on ... from

六、正则表达式

知识点 REGEXP ' '

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

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

取居住地址Address以'C'或'h'开头或以 'et' 结尾的所有数据，

SELECT * FROM persons WHERE Address REGEXP '^[Ch]|et$';

七、case when then用法

https://www.cnblogs.com/Richardzhu/p/3571670.html

八、插入

普通插入（全字段）：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

比如：INSERT INTO employees SELECT * FROM new_employees WHERE age >= 30;

表格插入字段不能用table,全字段可以省略字段名

九、模式

https://www.cnblogs.com/ndxsdhy/archive/2010/12/21/1912571.html

外模式是面向数据库用户或应用程序的局部数据视图；

模式并不涉及数据的物理存储细节

模式/内模式映像保证了数据库具有较高的物理独立性。

索引的组织方式是B+树索引，还是Hash索引与数据库的内模式有关

十、其他

null

在sql中若要取得NULL，则必须通过IS NULL或者IS NOT NULL进行获取，无法直接使用等号,SQL中的null不能用于比较

删除

DELETE 可用于删除表中的行数据；

DROP 一般删除表的，视图是虚拟表，本质还是从真实表中获取数据，它是在使用的时候动态的从真实表中查出来的

CLOSE 可用于关闭游标；

REVOKE 可用于收回语句或对象许可。

批处理

不能定义一个check约束后，立即在同一个批处理中使用；

修改一个表中的字段名后，不可以在同一个批处理中引用这个新字段

Create default，Create rule，Create trigger，Create procedure，Create view等语句同一个批处理中只能提交一个；

不能把规则和默认值绑定到表字段或自定义字段上之后，立即在同一个批处理中使用。

@@ERROR：返回执行的上一个 Transact-SQLTransact-SQL 语句的错误号。

@@IDENTITY：返回自增id。

@@ROWCOUNT：返回受上一个SQL语句影响的行数。

@@MAX_CONNECTIONS：返回最大用户连接数。

update

update用法：

update 表名1 set 字段名1=value1,字段名2=value2

where 条件表达式

case when的正确语法是:

case when ... then ...( when ... then ... )else ... end，case和end不能缺少！

锁

共享锁S：共享锁锁定的资源可以被其他用户读取，但是其他用户无法修改，在执行select时，sql server会对对象加共享锁。（其他人可读不可写）

排它锁X：(独占锁)其他人不能读也不能写（所以不会多重更新）。

更新锁U：当SQL Server准备更新数据时，它首先对数据对象作更新锁锁定，这样数据将不能被修改，但可以读取。等到SQL Server确定要进行更新数据操作时，他会自动将更新锁换为独占锁，当对象上有其他锁存在时，无法对其加更新锁。

架构锁：在执行依赖于表架构的操作时使用。架构锁的类型为：架构修改 (Sch-M) 和架构稳定性 (Sch-S)。

备份

1、完全备份
这可能是大多数人常用的方式，它可以备份整个数据库，包含用户表、系统表、索引、视图和存储过程等所有数据库对象。但是呢，它也需要花费更多的时间和空间，所以，一般推荐一周做一次完全备份。
2、事务日志备份
事务日志是一个单独的文件，它记录数据库的改变，备份的时候只需要复制自上次备份以来对数据库所做的改变，所以只需要很少的时间。为了使数据库具有鲁棒性，推荐每小时甚至更频繁的备份事务日志。
3、差异备份
也叫增量备份。它是只备份数据库一部分的另一种方法，它不使用事务日志，相反，它使用整个数据库的一种新映象。它比最初的完全备份小，因为它只包含自上次完全备份以来所改变的数据库。它的优点是存储和恢复速度快。推荐每天做一次差异备份。
4、文件备份
数据库可以由硬盘上的许多文件构成。如果这个数据库非常大，并且一个晚上也不能将它备份完，那么可以使用文件备份每晚备份数据库的一部分。由于一般情况下数据库不会大到必须使用多个文件存储，所以这种备份不是很常用 ...

顺序

写法顺序：select--from--where--group by--having--order by

执行顺序：from--where--group by--having--select--order by

(1) from：对左表left-table和右表right-table执行笛卡尔积(a*b)，形成虚拟表VT1
(2) on: 对虚拟表VT1进行on条件进行筛选，只有符合条件的记录才会插入到虚拟表VT2中
(3) join: 指定out join会将未匹配行添加到VT2产生VT3,若有多张表，则会重复(1)~(3)
(4) where: 对VT3进行条件过滤，形成VT4, where条件是从左向右执行的
(5) group by: 对VT4进行分组操作得到VT5
(6) cube | rollup: 对VT5进行cube | rollup操作得到VT6
(7) having: 对VT6进行过滤得到VT7
(8) select: 执行选择操作得到VT8
(9) distinct: 对VT8进行去重，得到VT9
(10) order by: 对VT9进行排序，得到VT10
(11) limit: 对记录进行截取，得到VT11返回给用户

聚合函数通常与 GROUP BY 子句一起使用，以便对分组的数据执行聚合操作，在 SQL 中，WHERE 子句用于过滤行，它在数据被分组之前应用。这意味着在 WHERE 子句中使用聚合函数是不合法的，因为聚合函数是对已经分组的数据执行计算的。

having是在分组后过滤，where在分组前过滤，不冲突，可以同时使用；
having是用来过滤的，group by是限定分组；
select语句中没有聚合函数的使用时也可以用having

where是在生成结果集之前用的所以用不了聚合函数 where是生成结果集之前

SQL语言的组成部分

SQL语言的组成部分有：数据定义语言、数据处理语言、数据控制语言、数据库事务。

数据字典是关于数据库中数据的描述，是元数据，而不是数据本身。

索引

可通过索引快速查找数据，减少查询执行时间
数据库索引采用B+树是因为B+树在提高了磁盘IO性能的同时解决了元素遍历效率低下的问题
如果WHERE子句中使用了索引，那么ORDER BY子句中不会使用索引
索引会提高查询速度但不会提高更新表的速度，索引滥用会降低更新表的速度，更新表时，MySQL不仅要更新数据，保存数据，还要更新索引，保存索引，索引会占用磁盘空间。

事务

并发控制机制的好坏是衡量一个数据库管理系统性能的重要标志之一
可串行性是判断并发事务是否正确的准则
SQL Server中每一条select、insert、update、delete语句都是隐形事务的一部分，显性事务用BEGIN TRANSACTION明确指定事务
事务的隔离级别越高，并发能力也就越低

十一、子查询

where型子查询：指把内部查询的结果作为外层查询的比较条件。子查询：单列单值
查出每个栏目最新的商品(以good_id为最大为最新商品)：
goods货物表，good_id表的主键,cat_id栏目的编号

select cat_id,good_id,good_name from goods where good_id in(selct max(good_id) from goods group by cat_id);

from型子查询：把内层的查询结果当成临时表，供外层sql再次查询。子查询：多行多列

查出每个栏目最新的商品(以good_id为最大为最新商品)：

select * from (select cat_id,good_id,good_name from goods order by cat_id asc, good_id desc) ) as tep group by cat_id;

in子查询：内层查询语句仅返回一个数据列，这个数据列的值将供外层查询语句进行比较子查询：单列多行

查询年龄为20岁的员工部门

select * from department where did in(SELECT did from employee where age=20);

exists子查询：把外层的查询结果，拿到内层，看内层是否成立，简单来说后面的返回true,外层（也就是前面的语句）才会执行，否则不执行。

查询是否存在年龄大于21岁的员工

select * from department where EXISTS (SELECT did from employee where age>21);

any子查询：只要满足内层子查询中的任意一个比较条件，就返回一个结果作为外层查询条件。

查询满足条件的部门

select * from department where did> any (SELECT did from employee );
all子查询：内层子查询返回的结果需同时满足所有内层查询条件。

select * from department where did> all(SELECT did from employee );

比较运算符子查询：子查询中可以使用的比较运算符如 “>” “<” “= ” “!=”

select * from department where did= all(SELECT did from employee where name='赵四');

子查询（7种类型）_子查询有哪几种类型-CSDN博客

十二、视图

在视图中可以进行基本的增删改查

在视图中也可以定义新的视图

视图却无法创建表，因为视图是个虚表

视图可以被嵌套，一个视图中可以嵌套另一个视图

当SELECT语句的选择列表有TOP子句时，视图可以包含ORDER BY子句；

视图不能对临时表或表变量进行引用；

sp_helptext用于获取自定义视图创建的T_SQL文本，更新视图数据可用sp_refreshview。

创建视图：create view 视图名 as select * from 表名 where 条件

十三、游标

INSENSITIVE
表明MS SQL SERVER 会将游标定义所选取出来的数据记录存放在一临时表内（建立在tempdb 数据库下）。对该游标的读取操作皆由临时表来应答。因此，对基本表的修改并不影响游标提取的数据，即游标不会随着基本表内容的改变而改变，同时也无法通过
游标来更新基本表。如果不使用该保留字，那么对基本表的更新、删除都会反映到游标中。

当遇到以下情况发生时，游标将自动设定INSENSITIVE 选项。
在SELECT 语句中使用DISTINCT、 GROUP BY、 HAVING UNION 语句；
使用OUTER JOIN；
所选取的任意表没有索引；
将实数值当作选取的列