MySQL基础知识-刷题笔记
数据库刷题笔记
查漏补缺，面试八股文，以下内容未说明的均以MySQL数据库为准
where 不能和聚合函数一起使用
having可以和聚合函数一起使用
having必须与group by一起使用substring_index(string, substring, number)：获取字符串string中的substring在其第number个出现的位置之前的部分;
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、
< 和 > 分别对应 < 和 >7、
Char Index返回目标字符或字符串在指定基准字符串中的起始位置值CHAR INDEX(查找基准字符串, 查找子串, 起始位置)
Substring命令用于从给定的位置提取特定长度的子串SUBSTRING(原始字符串, 开始索引, 提取长度)
Stuff命令用于删除指定范围内的字符并用新数据填充空白区域STUFF command replaces a specified range of characters in a column with new input data
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-SQL 语句产生的错误代码。
@@IDENTITY：生成自增身份标识符。
@@ROWCOUNT：由前一次 SQL 语句产生的行数。
@@MAX_CONNECTIONS：允许的最大并发连接数量。
11、SQL的执行顺序是：FROM–WHERE–GROUP BY–HAVING–SELECT–ORDER BY
在SQL中对NULL进行处理时，并不涉及比较操作，在查询结果中获取NULL值时，则需使用IS NULL或IS NOT NULL语句；此外，在SQL语句中无法直接将等号运算符用于判断是否为NULL
13、
All(): 适用于所有数据集的情况，在>=all时相当于取最大值，在<=all时相当于取最小值； Any: 当至少存在一条符合条件的数据即可整体成立，在>any时相当于大于最小值，在
触发器是一种与数据库表相关的对象，在执行插入、更新、删除操作前或后时会触发并执行预先定义好的SQL语句，并根据具体功能分为三类：包括预置触发器、置后触发器以及事务完整性约束型触发器等类型。
15、先分组，再过滤，用group by … having …
16、通过数据注入操作（INSERT INTO）向单个目标数据库执行新增记录。
此外，在源库中提取信息（SELECT）并将其存储到另一个存储位置（destination table）的操作被称为SELECT INTO。
这种操作常见用途包括生成备份或实时记录。
17、
having子句即可包含聚合函数作用的字段也可包括普通的标量字段；
该子项应与另一特定子项共同实施，在某些情况下则无法独立存在；其中一项主要负责筛选数据并将其归类为符合条件的记录；而另一项则用于确定分组依据；两者需配合应用；反之若仅采用另一项则无法实现功能；当应用该功能时须确保另一项的存在。
在分组之后执行过滤操作的是having，在分组之前执行的是where；两者不会互相影响且可以同时应用；因此,having子句与where子句的作用等价于彼此；此外,when子句无法与聚合函数协同工作
select语句中没有聚合函数的使用时也可以用having
在MySQL中**不支持使用如等于NULL或不等于NULL等比较运算符来查询NULL值 。**建议采用IS NULL或 IS NOT NULL来进行相关判断。
19、以下情况游标将会自动设定INSENSITIVE选项:
在执行SELECT语句时使用DISTINCT、GROUP BY、HAVING和UNION功能。
在进行OUTER JOIN操作时；
所选择的所有表均未预先建立索引。
将实数值类型的数据错误地分配给被选中的列。
20、全外连接
MySQL中默认不支持直接执行全 outer join 操作，默认情况下只能进行左 outer join 和 right outer join 的操作以模拟 full outer join 的效果。具体操作步骤如下：执行左表的 left outer join 操作并执行右表的 right outer join 操作，并通过使用 union 操作将两者的结果集合起来即能达到 full outer join 的效果
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=latin122、它是由一组经过优化并为特定功能而设计的SQL指令组合而成。这些预处理好的命令经过编译后被存放在服务器端数据库系统中以便后续调用。通过这些预先编译好的操作步骤（stored procedures），可以在数据库层面上提升数据操作的速度。当某个操作包含了大量复杂的SQL指令或者需要重复执行时，则会明显优于直接逐条运行单独生成的结果集的方式。
- 选择σ和投影π作为特殊的算子，在数据库操作中具有重要的作用；其中与∧构成了逻辑与运算符，在条件匹配中被广泛应用；或者∨则代表了逻辑或运算符，在查询优化中发挥着关键作用；非¬则是表示逻辑非运算符，在条件反转操作中起着重要作用；∪并则对应于集合论中的Union操作，在数据整合过程中被频繁使用；−差代表集合论中的Difference操作，在数据去重过程中具有重要应用；∩交则是集合论中的Intersection操作，在数据筛选时被常用到；×笛卡尔积则对应Cartesian Product操作，在多表联结中扮演着基础角色。
游标 是一种从包含多条数据记录的结果集中每次提取一条记录以便处理的方法，在某种程度上类似于查询结果中的一个指针标记；
该系统通过游标实现了对查询结果集中特定行的位置定位功能；
它能够为其他用户的修改操作实现多层次的安全权限控制；
系统设计充分考虑了当前处理位置处的数据修改需求。
25、
RENAME 该表重命名为：通过以下方式操作：RENAME 或 RENAME TO
MODIFY 对字段类型进行更改：通过以下指令执行：MODIFY COLUMN <列名> 设置新类型
ALTER 对字段类型及默认值进行修改：通过以下操作完成设置：ALTER COLUMN <列名> 更新类型 SET DEFAULT 值
CHANGE 更改表中列名称及其类型：通过以下指令实现变更：CHANGE 列名 新名称 类型
ALTER TABLE employee RENAME employee_info; -- 更改表名为 employee_info26、索引
数据库索引采用B+树是由于B+树不仅提升了磁盘IO性能，并且还解决了元素遍历效率低下的问题；
当WHERE子 clause中采用了索引时, ORDER BY子 clause将不会使用索引;
过多使用索引会导致更新表的速度下降.
27、左连接
left join被定义为涵盖inner join结果的概念。在执行left join操作时，结果会返回左表的所有记录；而on字段的作用在于指定连接左右表的具体条件。
SQL语言被划分为四个类别：包括数据检索语言（DQL）、数据操作语言（DML）、数据结构语言（DDL）以及数据控制语言（DCL）。
（1）数据查询语言DQL
（2）数据操纵语言DML
（3）数据定义语言DDL
（4）数据控制语言DCL
（5）总结
① SQL DDL用于包含 SQL 模式、基本表、视图以及索引的生成与撤销操作。
② 数据操纵（SQL DML）分为两类：一是进行查询操作二是执行更新操作；其中更新操作细分为插入、删除以及修改三种类型。
③ 数据控制（DCL）涉及赋予基本表与视图的操作权限；完整性的约束；事务管理等多方面的内容。
④ 嵌入式 SQL 的使用规定（TCL）涉及规定了将 SQL 语句嵌入到宿主语言程序中的相关规则。
29、删除触发器： DROP TRIGGER
输入 ： DROP TRIGGER 触发器名称;
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和end必须出现。
34、insert插入时，可以给部分或所有字段名加``，不能加’‘，加’'时会执行报错。
35、length无法对null做筛选，<>无法对null做筛选
36、在视图中也可以定义新的视图，却无法创建表，因为视图是个虚表
授予账户SQLTest对数据库SalesCUSTOMERS表中的cid列为 cid 列、cname列为 cname 列的所有查询权限
grant select on CUSTOMERS(cid,cname) to SQLTest38、视图
虚设的表格被称为视图，默认情况下它不直接存储实际的数据内容；其基础数据来源于所谓的基表。当从单一表格中无法获取某个实体的所有信息时（比如需要同时查看多个属性），使用视图能够提供完整的解决方案。当基表发生变更时（比如新增或删除记录），这些变动会被立即反映到对应的视图中。数据库主要维护的是各个视图的定义信息以及它们之间的依赖关系，默认情况下不会直接存储每个特定用户所需的具体数据内容。
视图无法引用temporary tables或table variables；
若SELECT语句的选择列表带有TOP子串，则该类视图表可容纳ORDER BY项；
该类视图表具嵌套能力，在一个层次内可包含另一层别类视图表；
sp_helptext可用于获取自定义创建之T_SQL文本文件内容；sp_refreshview可用于更新此类视图表的数据。
39、SQL语言的主要组成部分包括：用于定义数据库结构的数据定义工具（DDTs）、用于执行日常操作的数据处理工具（DDLs）以及负责事务管理的数据库事务（DBTs）。而数据库字典则主要用于记录和存储关于数据库中各实体及其属性的信息，并不属于实际的数据内容。
40、下面哪些字符最可能会导致sql注入?
'（单引号）
/
"(双引号)
$MySQL的UNION运算符用于将两个及以上的SELECT语句的结果集组合成一个完整的查询结果集合，并自动去除重复的数据项。通过多次执行多个SELECT语句时会自动去除这些重叠的数据项。在SQL语言中使用UNION运算符可以整合多个SELECT语句生成的结果集，并且它只会返回不重复的行值；然而，在需要包含所有可能结果的情况下，则应采用带有关键字"ALL"的联合运算（Union All）。
42、group by 用于分组，和聚合函数一起使用。
43、distinct需要写在case前面
44、视图是虚表，它在存储时只存储视图的定义，而没有存储对应的数据；
创建视图用CREATE VIEW
修改视图用ALTER VIEW
删除视图用DROP VIEW45、count函数具有两种调用方式：第一种方式是通过调用count(*)来统计表中所有记录的数量，并且该方法会将null值视为有效数据；第二种方式是通过指定某一列来统计其中非空且非null的数据数量，并且同时也会计算包含空字符串的数据记录情况（即" "也算），而不会考虑null或NULL的情况。
在关系型数据库中，在左连接的情况下，并将外表条件放置于WHERE子串而非ON串中，则其实际执行效果等同于INNER JOIN。
原题的D选项https://www.nowcoder.com/questionTerminal/45fa358b24364928bcb6f99c7d19ea0f?
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 条件
① drop涉及彻底删除整个表及其结构。
② delete功能用于处理数据清除，并保留下其原有的设置和架构；支持通过where条件筛选后仅清除单条或多条记录。
③ truncate功能仅能清除数据而不破坏原有设置和架构；同时不支持通过where条件进行过滤。
自定义函数不得用于修改数据库的状态；自定义函数归属于数据库范畴，在其所属系统内可被调用；标量运算子与存储过程具有相同功能，在它们的运行过程中均可使用EXECUTE语句；如同系统运算子一般，在查询指令中也可调用自定义运算子。
60、inner join 时只会对非NULL的记录做join,并且2边都有的才会匹配上
61、sum是可以用于行转列的函数
62、is 只能在null的时候用 正常情况要用 =‘ ’
63、where子句无法直接连接Max和Min这类聚合函数。然而where子句能够与包含这些聚合函数的部分结合使用，并因此能够实现更为复杂的计算需求
SELECT 衬衫ID、衬衫名称、衬衫价格 FROM 衬衫表格 WHERE衬衫价格 > 平均衬衫价格 不正确
SELECT 衬衫ID、衬衫名称、衬衫价格 FROM 衬衫表格 WHERE衬衫价格 > (SELECT 平均衬衫价格 FROM 衬衫表格) 是正确的
64、涉及表的结构更改属于. DDL 语句. 常见计算式。
新增字段：使用 ALTER 语句在表中增加字段。
重命名字段：通过 ALTER 语句更改字段名称。
删除字段：使用 ALTER 语句从表中删除字段
65、在进行更新操作前，请确保已将数据字段配置为排他锁定状态，并在事务处理完成后及时解除锁定；对于新增或修改数据的场景同样适用此规则。
在进行写入操作前，请确保已将数据字段配置为排他锁定状态，并在事务处理完成后及时解除锁定；
对于读取操作而言，则需要使用共享权限以确保数据一致性，在完成相关数据库指令执行后自动解除锁定；
66、窗口函数： window functions will generate a result for each row in the window they are applied to. The OVER clause is used to define windows within the result set, and window functions such as sum, max, min, avg, count, and rank will calculate values within these windows to obtain various aggregated results.
开窗函数 OVER (
[ <PARTITION BY clause> ]
[ <ORDER BY clause> ]
[ <ROW or RANGE clause> ]
)67、索引
为了提高效率而设立组合索引，并需遵守最左前缀原则；对于唯一性约束而言，在同一时间点上不允许存在两个不同的数据行拥有相同的键码值（包括 NULL 值），但可以包含空值；每个数据库表最多只能定义一个全文性约束（即全文性键码）；非聚集型键码具备完全独立于单个数据行的结构特征，则无需对物理上的数据页中的记录进行按列重组。
68、对聚合后的结果做筛选用having，where是在聚合前做筛选。
该系统的索引结构采用B+树形式是否更优？而Hash表则与其内部数据库架构密切相关。外模式通常表现为基于外部用户的特定数据子集展示。值得注意的是，在此方案中并未考虑数据的具体存储介质。通过模态与内在模态之间的映射关系实现了系统的高程度物理独立性保障。
70、coalesce函数，返回第一个非空值，如果都是空，返回空值
71、SQL合法标识符第一个字必须是 字母 、下划线 、@和#开头
72、MySQL 中使用 REGEXP 来操作正则表达式的匹配。
其中
- ^ 该符号用于指示输入字符串的起始位置；
- $标识输入字符串的结束位置；
- 方括号中的内容代表可以匹配到任何单个字符；
- 方括号前加反斜杠的内容表明不允许匹配括号中的任何一个字符；
- 竖线分隔符用于指示可以选择匹配x或y中的任一选项。
73、涉及的日期计算函数datediff(日期1, 日期2)，其计算所得的结果即为两个时间点之间相隔的日数。当第一个时间点晚于第二个时间点时（即时间为正值），结果将呈现正值状态；反之则为负值。
74、count(*) 覆盖了全部字段，并等同于记录总数，在数据汇总时不会排除任何字段值为NULL的情况。
count(1) 以数值1表示代码块，并不考虑各字段的具体数值，在数据汇总时将全部记录纳入计算。
count(字段名) 则仅计算对应这一字段的数据，在数据处理过程中将不计入任何字段为空的信息。
75、数据库备份类型
全面的数据库备份会包含所有数据以及相关的事务日志项。
差异型数据备份仅存异于最近一次完整的数据变更内容。
事务日志专门存储会话相关的各种信息。
特殊文件管理 backup 方法适用于当在创建 database 时为其创建了 multiple files 或 file groups 的情况。该方法允许对这些 files 或 file groups 进行 backup.
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 <校对规则名> } -- 修改校对规则（比较和排序时用到）