MySQL基础知识-刷题笔记

数据库刷题笔记

查漏补缺，面试八股文，以下内容未说明的均以MySQL数据库为准

where 不能和聚合函数一起使用
having可以和聚合函数一起使用
having必须与group by一起使用

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-2POWER(0,eno%2)就是奇数+1，偶数-1
eno-1+2MOD(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_tableCREATE 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 或 RENAME TO
MODIFY用于字段类型的修改：MODIFY COLUMN <列名> <类型>
ALTER用于对字段类型、默认值的修改：ALTER COLUMN <列名> <类型> SET DE***T <默认值>
CHANGE用于对列名及类型的修改：CHANGE COLUMN <旧列名> <新列名> <类型>

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是删除表数据，保留表的结构，而且可以加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(*)包括了所有列，相当于行数，在统计结果的时候，不会忽略某些列值为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 <校对规则名> } -- 修改校对规则（比较和排序时用到）