《MySQL》复习必刷知识点
1.数据库SQL语言的缩写
DDL：数据定义语言Data Definition Language，定义语言就是定义关系模式、删除关系、修改关系模式以及创建数据库中的各种对象，比如表、聚簇、索引、视图、函数、存储过程和触发器
关键字包括：Create，Alter，Drop，Truncate
DML：数据操纵语言全称是Data Manipulation Language，主要是进行插入元组、删除元组、修改元组的操作
关键字包括：insert、update、delete
DQL：数据查询语言全称是Data Query Language，所以是用来进行数据库中数据的查询的，
关键字包括：select
DCL：数据控制语言Data Control Language。用来授权或回收访问数据库的某种特权，并控制数据库操纵事务发生的时间及效果，能够对数据库进行监视
关键字包括：grant，rovke，commit，rollback
SQL：全称是结构化查询语言
2.两个with选项的不同侧重点
with grant option：它是在授权的时候存在的，作用是有此话的授权所包含的权力可以被转赠出去
with check option：它是在视图的创建中存在的，作用是包含此话的视图，要想操作视图必须要符合检查选项，其中以基本表建立起来的视图使用这句话，而使用视图建立起来的视图还需要加上另外两个关键字cascade 和 local
- cascade：以此命令的视图开始，每一层都需要检查
- loacl：只检查有此命令的视图的检查选项
3.注意聚合函数返回值
聚合函数包括：count（）计数，sum（）总和，avg（）平均值，max（）最大值，min（）最小值
聚合函数的返回值都是只有一个结果的，所以在使用聚合函数的时候一定要注意和它搭配的属性组拒绝聚合函数和 * 通配符一起使用
例子：
select *,avg(成绩) from student;
如上例子，select * 查出来的结果有多个，一个班肯定不知一个学生，这是默认的
但是select avg（成绩）查出来的结果是一个，它们的结果集是匹配不了的，但是不会报错，selct * 只会把第一个符合条件的记录和聚合函数匹配，虽然买报错，但是结果已经是错误的了
聚合函数使用的位置也是一个注意点，它不能使用于where 关键字后面
其次是聚合函数直接跟在select关键字或者having关键字都是有意义的，也是最常用的要记住
最后是无意义的，聚合函数跟在order by 关键字后面是无意义的，但是并不是不可以
select count(工作年限) from employees order by count(工作年限);
这段代码是不报错的，但是是无意义的，order by 不管是升序还是降序都是，对于只有一条记录的结果是不明显的，上述的代码返回值就一个，虽然order by后面跟的是一个聚合函数，但是无意义的
4.关系代数中基本运算
五种基本运算：并，差，选择，投影，笛卡尔积
- 并：
- 差：
- 选择：
选择是对于记录行操作的，用于筛选条件，它对应select语句的where子句
符号：σ
- 投影：
投影是对于属性列来操作的，用于查询表的展示，它对应select子句
符号：Π
- 笛卡尔集：
笛卡尔乘积是指在数学中，两个集合X和Y的笛卡尔积（Cartesian product），又称直积，表示为X × Y，第一个对象是X的成员而第二个对象是Y的所有可能有序对的其中一个成员。
假设集合A={a, b}，集合B={0, 1, 2}，则两个集合的笛卡尔积为{(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)}。
我一般称它为全域
符号：X
- 交：
- 连接
连接是对于两个关系，从两个关系的笛卡尔集中选取一些满足条件的元组
符号：
- 除运算
设置关系R除以S的结果为关系T，则T包含所有在R但不在S中的属性及其值，且T的元组与S的元组的所有组合在R中
省流：找出除数的象集，利用被除数的相应列中的元组来匹配象集，匹配上了就是其结果
符号：÷
4.MySQL规范的四个范式
第一范式1NF：数据库的数据列都是不可分割的列，原子性
第二范式2NF：非主属性全部依赖于主属性，没有非主属性依赖于非主属性的情况了，唯一性（必须满足第一范式要求）
第三范式3NF：不存在传递依赖，A->B,B->C,不存在A->C的情况，独立性
BNF范式：非主属性不能被主属性组的分量确定，若主键是一个属性组，那么属性组中的任何一个分量不能单独确定其它非主属性
5.SQL Server的主要拓展名
1.主要数据文件
扩展名：.mdf，是 primary data file 的缩写
主要数据文件包含数据库的启动信息，并指向数据库中的其他文件。用户数据和对象可存储在此文件中，也可以存储在次要数据文件中。每个数据库有一个主要数据文件。主要数据文件的建议文件扩展名是 .mdf。
2.次要数据文件
扩展名 .ndf，是Secondary data files的缩写。
次要数据文件是可选的，由用户定义并存储用户数据。通过将每个文件放在不同的磁盘驱动器上，次要文件可用于将数据分散到多个磁盘上。
另外，如果数据库超过了单个 Windows 文件的最大大小，可以使用次要数据文件，这样数据库就能继续增长。
3.事务日志
扩展名 .ldf，是Log data files的缩写。
事务日志文件保存用于恢复数据库的日志信息。每个数据库必须至少有一个日志文件。事务日志的建议文件扩展名是 .ldf。
6.SQL Server补充关键字
add constraint：给表格添加约束
7.select语句的七大子句
1、from：从哪些表中查询数据
2、on：主要用在关联查询中，后面跟关联条件
3、where：主要用于数据筛选，后面可以以某个字段为条件，但是一定不能以分组函数为条件
4、group by：可以根据某个或某几个字段进行分组，会将该字段之一样的数据分为一组
group by单独使用没有任何意义，必须和分组函数结合使用
但是分组函数可以单独使用，此时是将所有数据作为一组
5、having：是在查询的结果中再次进行筛选，因此后面的条件必须是select后面出现的字段或分组函数
注意：on后主要跟关联条件，where后跟字段作为条件，having后跟分组函数作为条件
6、order by：排序，根据某个或某几个字段进行排序，asc表示升序（默认），desc表示降序
7、limit，分页
很多时候没有嵌套子查询的情况下，select本身也是一个子句
select current_time; 查看当前系统时间
8.条件子句的区间
BETWEEN 0 AND 100 ：等同于 num <= 100 and num >= 0 ，可以看到是表示区间的，并且是闭区间
in（3，5）：等同于 num = 3 and num = 5 ，不表示区间，且是等值
9.MySQL时间类型转换为数字类型操作
year（'2002-11-04'）：提取年份，即结果为2002
month（'2002-11-04'）：提取月份，即结果为11
day（'2002-11-04'）：提取日期，即结果为4
hour（'20:15:14'）：提取小时，即结果为20
minute（'20:15:14'）：提取分钟数，即结果为15
second（'20:15:14'）:提取秒数，即结果为14
dayname（'2023-6-24'）：日期转换为星期，星期六，即结果为Saturday
10.插入时嵌套子查询
对于不确定返回值有多少个的情况下
可以直接把子查询替换掉values（）子句
insert into score（id，name） select id，name from student;
对于只利用子查询的一部分结果作为参数时：
insert into score（id，name） values（1，（select name from student））;
但是这个方式只有返回值为单个值得子查询语句使用
11.SQL语句执行流程
- from关键字：对表进行互联生成笛卡尔集
- on关键字
- join关键字
- where关键字
- group by关键字
- 聚合函数
- having关键字
- select关键字
- distinct去重
- order by排序
- limit分页
12.返回的记录是前百分之10
这是一条SQL语句的关键字：percent，它控制返回值返回为结果的百分之多少
SELECT TOP 10 PERCENT * FROM T1
如上在SQL中的意思是返回结果集的前百分之10
13.子查询都是优先查询的
错误；
子查询是否拥有最高优先级要看子查询处于的位置
如果处于where子句后面，则优先级高于主查询
如处于from子句后面，则主查询优先级高
14.select... into ... from 的作用（SQL语法）
此句话的意思从查询的结果然后插入到新表中
SELECT * INTO new_table_name [IN externaldatabase] FROM old_tablename
15.当一个表上需要创建聚集和非聚集索引时，应该先创建非聚集索引，然后再创建聚集索引
错误，反过来了应该先创建聚集索引
聚集索引就是存放的物理顺序和列中的顺序一样。一般设置主键索引就为聚集索引。
通过聚集索引可以一次查到需要查找的数据， 而通过非聚集索引第一次只能查到记录对应的主键值 ， 再使用主键的值通过聚集索引查找到需要的数据。
16.若关系模式R中属性A是N类属性，则A：
一定包含在R的任何候选码中
这是书上的定理
17.设F是某关系模式的极小函数依赖集。下列关于F的说法
F中每个函数依赖的左部都必须是单个属性
18.事务的隔离级别不包括
repeatable read：可重复读取
反之，包括：read uncommitted（未提交读取），read committed（已提交后读取），repeatable only（重复读取）
19.union和union all的区别
union在连接两个select语句的时候会自己去重
而union all 在连接两个select 语句的结果是直接输出没有去重的操作
20.什么是MySQL派生表
MySQL派生表指的是在sql语句运行的时候根据结果集动态产生的一张表，
派生表的产生只在于sql语句的执行的过程中，当派生表被生成出来以后就可以把它当成一张基本表操作，比如联表查询
它在from子句和join子句后面是就可以当作一张表来连接，它的特点是灵活性很高，需要什么派生什么
select s.sno 学号,c.cname 课程名,s.grade 成绩 from sc s join ( select sno ,avg(grade) av from sc group by sno ) s1 using(sno)
注意：派生表是必须取别名的，因为派生表是动态生成的结果集，并没有自己的实质性表名，所以必须取别民来映射这个结果集
21.MySQL的条件关键字：in，between ... and ...
in关键字：指的是匹配括号中的一个就可以了，如 in（a，b，c）如果条件结果有a，b，c任意一个都可以返回，但是对于 （10，20）他只得是10和20这两个单独的数字，而不是指的 10~20这个区间
between...and...：故名思意就是指的区间，between 10 and 20 就是指的10~20这个区间
21.update语句中使用条件更改
update sc set grade= case when grade <70 then grade *1.05 else grade*1.04 end
如上，若成绩小于70分则提高5%，若成绩在70分以上（含70分）则提高4%
使用case语句进行条件划分，然后计算出结果，尤其要注意在update语句中的书写格式
22.update语句中使用select子查询时，数据返回超过一列的情况
当update要更给为的数据刚好时select查询回来的数据时，有时候会面临一个问题，那就是查询回来的数据多于一列了，导致update语句中 set ' ' = ' ' 等号只能接收一个参数，所以sql语句会发生错误
这个时候就需要对查询的表也进行联表，它的思路是，查询出来的列来匹配外部update语句的列，此时不需要连接表语句，而是直接使用两表的关联字段进行连接
update stu set totalcredit = (select sum(credit) from cou right join sc using(cno) where stu.sno = sc.sno and sc.grade >60 )
如上：where子句后的 stu.sno = sc.sno就是update语句要用到的表和select要使用的表在进行关联
但是它们并没有join操作，只是因为在这一整句sql中，stu表和sno表已经是匹配关系了，也可以说，是内部数据到外部数据的一个映射
23.MySQL的like模糊查询
- %通配符
以%所占位的地方可以不限字母，不限字数的匹配，但是对%本身的位置要求比严格，会严格筛查%号的位置来区分模糊匹配值，
- %开始："%123"，%号所在的位置可以代表很多字符，但是必须是以123结尾的字符，可以匹配的例如：1123，asd123，123，@#￥%123
- %在中间："a%b"，%号所在的位置依旧可以代表很多字符，但开始必须以a，结束字符必须为b，可以匹配的例如：a123b，aaab，abbb，a#$%^&*b
- %结束："ab%"，%号可以代替很多结束字符，但是开始字符必须是由ab开始，可以匹配的例如：ab123，ababab，ab#￥%……&
- _下划线占位符
下划线可以在任何位置，但是它所代表的意思就只能是单个字符，这是于%号最大的区别
"_ac"：它的前提是当前字段的长度只用三个，因为下划线本身就只占用一个字符，然后下划线处可以匹配单个任意字符
满足匹配的如：1ac，aac，cac，#ac，*ac
- [ ] 范围占位符，它可以填写一个范围在中括号内，但是生效的字符只能是一个，自己也只占位一个字符的空间和下划线的使用一致
" [ abcd ] 23"：它依旧需要为3个字符的字符串来匹配，它与下划线不同的在于它的匹配区间不同，他限定了被匹配的区间23字符前的字符只能是a，b，c或者d
满足匹配的如：a23，b23，c23，d23
- [ ^ ] ，此位置不是此字符
" a[ ^c ]123"：第二个字符不是c就可以匹配上
满足匹配的如：aa123，ab123，ad123，a#123
24.mysql的常用数学函数
ABS（x）：返回x的绝对值
CEIL（x）：返回大于等于x的最小整数
FLOOR（x）：返回小于等于x的最大整数
RAND（）：返回0到1之间的随机数
SQRT（x）：返回x的平方根
TRUNCATE（x，y）：返回x截断y到小数点后y位的结果，或截取小数点后y位
ROUND（x，y）：返回x四舍五入后y位的值，或截取小数点后y位，且y+1位四舍五入
拓展：字符串函数
CONCAT（st1，st2）：返回拼接好的st1和st2
INSTR（st1，st2）：查找字符串2在字符串1中第一次出现的位置，且是st2的整串匹配
LENGTH（char）：用于返回字符串的长度
LOWER（char）：将字符串转为小写，非字符或本是小写不改变
LPAD（char1，n，char2）：用char1字符填充 char2字符，填充长度为 n
SUBSTRING（str，n，m）：返回字符串str从n开始，m个字符
TRIM（str）：用于删除字符串首尾的空格
UPPER（char）：将字符串转为大写
REPEAT(str, n)：将str字符复制n份
REPLACE(str, old, new)：将str变量中原来字符old，变为新的字符new，可以是单独的局部字符串，可以不为整串
MySQL的4个基本概念？
Data：指的是数据，也是实际需要管理的对象，包括文字，图形，音频，视频等，
DB：指的是数据库，也就是真实存数据的文件系统，也指长期存储在计算机中有组织，有序，可共享的数据集合
DBS：数据库系统，它涵盖了数据，数据库，还有数据库的管理和开发人员，它是一个最大的集合
DBMS：数据库管理系统，它位于操作系统和用户之间，用于管理数据的存取，以及数据库的维护
数据库的三级模式和二级映像？
数据库的三级模式包括：外模式，模式，内模式
外模式指的是：外模式是用户级的，也是用户可以之间感知的，它的体现形式大多是视图，外模式是模式导出的一个子集，展示的是给用户看的特定数据，反映用户观
模式：是对数据库中全体数据的逻辑结构和特征总体的描述，是视图的公用数据来源，可以理解为基本表，模式反映的数据库的整体观
内模式：是物理级的，在数据库中位于底层，用于实际记录数据存放的位置，可以精确到地址，它定义了数据库数据在物理存储的真实结构和存储方式，内模式反映了数据库的存储观
二级映像包括：外模式/模式映像，模式/内模式映像
外模式/模式映像：指的是当数据组织数据的方式发生改变的时候，我们不需要对应用进行调整，而是改变外模式和模式的映射关系，就可以正常使用了，这就是逻辑独立性
模式/内模式映像：指的是当数据的存储结构发生改变时，我们不需要对基本表进行改变，而是只用改变它们之间的映射关系，这是物理独立性
二级映像的存在使得用户程序和数据的物理存储无关，它们不是互相依赖的，而是独立的。
数据库的数据独立性是指？
数据库的数据独立性指的是：逻辑独立性和物理独立性
逻辑独立性指的是：当数据库中外模式和模式之间的逻辑结构发生改变时，并不会影响程序的应用，它们之间是相互独立的
物理独立性指的是：当数据库的真实存储方式发生改变时，应用程序不会受到影响，也就是用户程序与数据库的物理层无关
数据独立性有利于数据库的稳定性和可维护性，同时保证数据的安全和完整
实体完整性，参照完整性，用户自定义完整性？
实体完整性：用于标识在数据库中实体的唯一性，在数据库中，主键用来标识每一条记录的唯一性，通常用一个字段，或一组字段来标识实体完整性
参照完整性：指的是关系之间，表与表之间的引用，对应的就是外键约束，外码要么为空，要么为引用表主码的值，参照完整性保证了数据一致性和完整性
用户自定义完整性：这是用户自己来定义的完整性，会根据公司业务，和需求来确定，比如网站登录页面用户名不可重复，灵活的贴合实际开发
什么是悬浮元组？
悬浮元组的产生发生在多表连接的时候，通常的连接有内连接和外连接，
在内连接中，是以两个表的某个字段值相等来连接，当其中一个表有的记录值在另外一个表中不存在，那么就没有等价关系，这条记录（元组）就会被结果集显示时丢弃，这种没有被结果集选择的元组就是悬浮元组
在外连接中，是以两个表中其中一个表的某个字段为主字段，另外的一个表的相同字段来匹配这个表，为主的表是没有悬浮元组丢失的，但是另一个表 要是有为主表的没有的元组，那么这些元组就是悬浮元组
什么是自主存取控制，什么是强制存取控制？
自主存取控制是加在用户的一套权限，通过用户自己的权限认证，可以对表有不同的权利，通过访问控制表来确定用户是否能访问此数据
常见的有，增删查改权力，并且，一个用户拥有此项权力后，就可以把这些权力给别人，灵活度很高，但是不便于管理
关键字：grant，revoke
授权：
grant 权力（select/updata...）
on table/database table_name/database
to 用户（张三，李四）
with grant option
回收权力：
revoke 权力 （select）
on table/database table_name/database
from 用户（张三，李四）
强制存取控制是一套加在数据上的权限，用户也会有一个对应的凭证，用户只能读取和其有相同或低于用户密级的数据，并且在写数据是，它只能写等级高于或等于自己密级的数据
它有效的防止了未经允许的用户访问，从而增加了系统的安全性，但是缺点是不够灵活，强制存取控制是底层的，是数据库开发人员才有权利干预的
MySQL中的几种函数依赖？
非平凡的函数依赖：指的是当一个字段组x，可以确定其它的字段y，但是字段y不是x属性组中的一个属性，而是独立的属性，就是非平凡的函数依赖，记作：x -> y ，但是 y ∉ x
平凡的函数依赖：指的是当一个字段组可以确定其它字段y时，但是字段y是属性组x的一个子属性，并不独立，就是平凡的函数依赖，记作：x -> y，但是y∈x
部分函数依赖：指的是当一个属性组x可以确定其它字段y，并且字段z属于x，且z也可以确定字段y，就称y部分依赖于x，记作：x->y ，z∈x，z->y
完全函数依赖：指的是当一个属性组x可以确定其它字段y时，并且构成x的任何真子集都不能确定y，就称y完全依赖于x，记作：x->y，
传递函数依赖：指的是x可以确定y，y又可以确定z，故可得x可以确定z，记作：x->y , y-> z , x->z
补充：数据库公理系统
合并规则：X->Y , X ->Z ; X ->YZ
伪传递规则：X->Y,WY->Z ; XW->Z
分解规则：X->Y , Z∈Y ；X->Z
数据库故障的三种恢复情况？
redo log：重做日志
undo log：回滚日志
bin log：记录表结构，及表数据的更改情况，DDL,DML
事务故障：
事务故障指的是单事务级别，需要逆向烧苗日志文件，对做了还未提交的事务进行回滚
对事务队列的事务进行重做，直到看到事务的开始标志，恢复结束
系统故障：
正向扫描日志文件，对已经提交的事务进行重做，对没有提交的事务进行回滚
硬件故障：
使用数据库备份资源进项数据恢复
什么是封锁和三级封锁协议?
封锁指的是在并发数据处理的过程中，在多用户操作数据对象时，会出现三个违反数据库一致性的特列：丢失修改，不可重复读，读“脏”数据（幻读）
丢失修改：指的是在并发数据操作时，当两个事务同时读到一个数据时，由于各种原因，导致它们写回的速度不一样，就会使得有一个事务写回的结果被覆盖了
不可重复读：指的时在并发操作数据时，当一个事务T1读取了一个数据后，T2事务对此数据进行了更改。当事务T1再次读这个数据的时候导致数据和前一次的数据不一样
幻读：指的是当一个事务T1更改数据后，T2读取了这个事务，但是T1因为某些原因回滚了事务，所以数据被还原了，T2读取的就是一个错误的数据
所以在这三种情况下，数据衍生出来了锁机制：分别为写锁X，以及读锁S
当事务对一个数据对象进行写操作时，会加上X锁，被加上锁的对象就不能被其它事务再加上S锁和X锁，一定要等释放完了才可以加上其它锁
当一个事务对数据对象进行读操作的时候，会加上S锁，被加上S锁的对象就不能再被加上X锁也就是不能被写的，但是可以加其它的S锁
三级封锁协议：
一级封锁协议：在对数据对象进行写操作的时候，需要对其加上X锁直到事务结束才能释放（解决丢失修改）
二级封锁协议：在一级封锁协议基础上，在对数据对象进行读操作的时候，需要对其加上S锁，读完后再释放S锁（解决幻读）
三级封锁协议：在一级封锁协议基础上，在对数据对象进行读操做时，需要对其加上S锁，必须要事务结束后才能释放S锁（解决不可重复读）
什么是事务？什么是ACID原则？
事务指的是定义一组sql命令并使其称为原子组合，特点是这组命令集合要么都成功，要么都失败，事务是一个最小的执行单位。
ACID原则：
- 原子性（Atomicity）：事务是一个包含一系列操作的原子操作，这些操作要么全部执行完毕，要么都不执行，不存在中间过程被影响的现象。
- 一致性（Consistency）：事务的执行不能破坏数据库数据的完整性和一致性，一个事务在执行之前和执行之后，数据库都必须处于一致性状态。
- 隔离性（Isolation）：数据库允许多个并发事务具有同时读写和修改数据的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。
- 持久性（Durability）：事务一旦提交成功，对数据的修改就是永久的，即便系统故障也不会丢失
什么是封锁粒度？
封锁粒度（指封锁对象的大小）有三个部分组成：
- 全局锁：对整个数据库实例加锁，用于逻辑备份等场景。
- 表级锁：对整个表加锁，开销小、加锁快，不会出现死锁，但并发度最低。
- 行级锁：对数据库中的某一行进行加锁，开销大、加锁慢，会出现死锁，但并发度最高。
多粒度封锁：对一个结点加上锁后，意味着此节点的后裔也会被加相同的锁，所以在检测要使用的对象是否有锁的时候，，不仅要检测当前结点，还需要检测其前驱
显式封锁：直接给要使用的对象加锁
隐式封锁：本身没有被加锁，而是其上级结点的数据对象被上锁了
意向锁：有意向锁的结点说明它的下级结点正在被加锁，对于任意结点，加锁都必须对其上级 先加上意向锁，有了意向锁可以方便检测结点的下级是否有显式锁了
