MichelleF的程序媛之路（Mysql篇）

一、 Mysql 索引

1. 知识点

查询数据库的两种方式为全表扫描和根据索引检索。

在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。

目的：避免数据库进行顺序查找提高查找效率。（顺序查找发生在磁盘上，磁盘访问速度慢）

mysql索引的数据结构为B树，B+树。

索引的分类：

主键索引-普通索引；（主键：一个表只能有一个主键，可以是很多列，可以被引用为外键使用）
	主键索引：非空字段和唯一索引，叶子结点存的是整行数据
	普通索引（二级索引）：叶子结点存的是主键的值
唯一索引-非唯一索引：
	唯一索引：字段内容不能重复但可以为空
	非唯一索引：字段内容可重复

创建索引

create index emp_ename_index on emp(ename);
alter table emp add index emp_ename_index(ename);

删除索引

drop index emp_ename_index on emp;

检查是否添加了索引

explain select  from test where id=1;

查看索引表中的索引信息

2. 面试题

请描述mysql的B+树索引原理，B+树索引有哪些好处
参考答案：

对于HDD磁盘来说，磁盘会被分为512字节的扇区，SSD，没有扇区，是由4kb的page组成。
面对HDD，32条数据，每条占用512字节，则占用32 个扇区，全表扫描需要32次io操作。由于操作系统的原理，系统在找到目标数据后会连续读取4kb的数据，文件系统每次会加载一个块（4kb，8个扇区到内存）在mysql 中最小的存储单元是页（16kb）即每次io缓存16kb，放在bufferpool中。在mysql中数据都是存在page中由page进行管理。一个page16kb。
B+树索引对id创建索引（一级索引存117032，二级索引是1170117032）

B+树的好处：树的高度就是io操作执行的次数。所有的数据信息都存储在叶子结点，非叶子结点都只存储索引信息。

二、 什么是存储过程

存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，它存储在数据库中，一次编译后永久有效，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象。在数据量特别庞大的情况下利用存储过程能达到倍速的效率提升。

系统自有的存储过程：

Sp_helpindex查看索引的索引信息；
sp_help报告有关数据库对象的信息，语法及应用错误；
sp_helpdb，返回指定数据库或者全部数据库的信息；
sp_helptext，显示规则、默认、存储过程、触发器、视图等对象的未加密的文本定义信息

如何使用：call 存储过程名称（参数）
自定义存储过程：

create procedure add(in a int, in b int, out result int)begin set result = a+b; end;
call add (1,2,@result); select @result;

三、 数据库系统的三级模式结构

1. 数据库的三级模式包括：外模式、模式（逻辑模式）、内模式（存储模式）。

（1）外模式也称为子模式或用户模式，是数据库用户能够看见和使用的局部数据的逻辑结构和特征的描述。一个数据库可以有多个外模式，每个外模式通常都是模式的一个子集，并且是针对某一应用的数据逻辑表示。外模式的存在有利于保证数据安全性，因为用户仅能访问其对应外模式中的数据，而数据库中的其他数据对他们来说是不可见的。
（2）模式也称逻辑模式或概念模式，代表了数据库全体数据的逻辑结构和特征，它定义了所有用户的公共数据视图。数据库只有一个模式，它基于某种数据模型定义了数据之间的联系以及与数据有关的安全性、完整性要求。
（3）内模式又称存储模式，描述了数据的物理结构和存储方式。它是数据在数据库内部的表示方式，比如记录的存储方式、索引的组织方式、数据是否压缩或加密等。一个数据库同样只有一个内模式，它负责将逻辑级的数据映射到物理存储介质上。

2. 数据库的两级映像则是确保数据逻辑独立性和物理独立性的关键机制，包括以下两种类型的映像：

（1）外模式/模式映像：此映像定义了外模式与模式之间的对应关系。由于一个数据库可以拥有多个外模式，因而也就存在多个这样的映像。当模式改变时，数据库管理员可以通过调整映像来维持外模式不变，从而不需要修改应用程序，这就实现了数据的逻辑独立性。
（2）模式/内模式映像：此映像是唯一的，并定义了全局逻辑结构与存储结构之间的对应关系。当数据库的内模式需要变更时，通过调整这种映像，可以保持模式不变，进而使得所有的外模式和应用程序都无需改动，实现了数据的物理独立性。
外模式是面向数据库用户或应用程序的局部数据视图；索引的组织方式是B+树还是Hash与数据库的内模式有关；模式不涉及数据的物理存储细节。

四、 Mysql相关语句

1. 在mysql中字符串的有效位置范围为1<=i<=length(input)
   
   
2. having是分组后过滤，where是分组前过滤
3. Having子句既可以包含呢聚合函数作用的字段也可以包含普通的标量字段
4. Order by 默认ASC（升序）
5. Insert操作时可以省略字段名，如果但是不仅可以插入table所以不能限制为插入table
6. 正则表达式的匹配规则
   
7. Update
   
8. Case
   case when的正确语法是: case when … then … when … then … else … end，case和end不能缺少
9. 删除
   
   

五、 链接查询

内链接：inner join（求两个表的交集）（inner可以省略）
外链接：left join，right join，full join（mysql不支持）
交叉连接：cross join
Union：并集操作删除重复结果不删除空数据
Union all： 并集操作不删除重复结果不删除空数据

六、 函数

1. 在MySQL中，系统函数和用户自定义函数是两种不同的函数类型，它们在定义、使用范围和功能上有所区别。

（1）定义：系统函数是MySQL预先定义好的，用户可以直接调用而无需自己实现。这些函数包括字符串函数、数值函数、日期时间函数和聚合函数等，用于处理各种常见的数据操作和业务逻辑。用户自定义函数（UDF）是用户根据自己的特定需求编写的函数。开发者可以使用CREATE FUNCTION语句来创建自定义函数，以实现特定的功能。
（2）使用范围：系统函数由MySQL系统提供，对所有用户可用，且无需任何额外的设置或权限。它们通常用于执行标准的操作，如字符串处理、数学计算、日期格式化等。用户自定义函数是由特定用户创建的，可能需要相应的权限才能创建和调用。它们的使用范围通常限于创建它们的数据库中，除非有适当的授权允许其他数据库或用户访问。
（3）用户定义的函数不用于执行修改数据库状态的操作；用户定义的函数属于数据库只能在数据库下调用；标量函数和存储过程一样可以使用execute语句执行

2. COALESCE函数，通常我们取COALESCE中第一个非NULL的值，根据sql的语法规则，我们不能将数字作为标识的开头。

3. 标量函数通常指的是返回单一值的函数，而标量字段则是指包含单个值的字段；聚合函数是一类特殊的函数，它们在一组值上执行计算，返回一个汇总结果。具体解释如下：

（1）标量函数：在数据库中，标量函数是指那些接收一定数量的输入参数并返回一个单一值作为结果的函数。例如，数学函数如`SIN()`, `COS()`, `LOG()`等，都是接受一个或多个参数，然后返回一个单一的数值结果。
（2）标量字段：标量字段是指表中的一列，它包含的是原子性的数据项，也就是非复合的、不可再分的数据，比如一个整数、一个日期或者一个字符串等。这样的字段在任何一行中都只有一个值。
（3）聚合函数：聚合函数是在SQL中用来对一组值进行计算的函数，它们可以对一个或多个列进行操作，但最终只返回一个汇总结果。常见的聚合函数包括`COUNT()`, `SUM()`, `MAX()`, `MIN()`, `AVG()`等。这些函数常用于数据分析和报表生成，因为它们能够提供关于数据集整体的信息，而不是个别行的信息。

4. 窗口函数

窗口函数在查询结果的某个区间内执行计算。

ROW_NUMBER() 函数可以给查询结果的每一行分配一个唯一的数字，通常，我们可以使用 ROW_NUMBER() 来按照特定的列对查询结果进行排序。

SELECT ROW_NUMBER() OVER (ORDER BY amount DESC) AS row_num, FROM sales;

RANK() 和 DENSE_RANK() 函数可以为查询结果中的每一行分配排名。不同之处在于，如果有多个行具有相同的值，则 DENSE_RANK() 不会跳过排名。

SELECT amount, RANK() OVER (ORDER BY amount) AS rank,DENSE_RANK() OVER (ORDER BY amount) AS dense_rank FROM sales;

5. 系统函数汇总：

Charindex（）：返回指定字符串中指定子字符串出现的起始位置

SELECT CHARINDEX('abc', 'abcdefg');  

-- 返回 1，因为'abc'在'abcdefg'中从位置1开始

SELECT CHARINDEX('xyz', 'abcdefg');  

-- 返回 0，因为'xyz'不在'abcdefg'中

Substring（）：用于截取字符串

SELECT SUBSTRING('abcdefg', 2, 3); 

-- 返回 'bcd'，从位置2开始提取3个字符

Stuff（）：用于删除置顶长度的自负并在删除位置加上新的字符

SELECT STUFF('abcdefg', 3, 2, '123');  

-- 返回 'ab123fg'，从位置3开始删除2个字符并插入'123'

Soundex（）：用于返回指定字符串的soundex值；

SELECT SOUNDEX('Smith');  

-- 返回 'S530'，这是'Smith'的Soundex值

SELECT SOUNDEX('Schmidt');  

-- 返回 'S533'，尽管这两个名字拼写不同，但它们的Soundex值很接近，因此发音相似

Round（）：四舍五入

SELECT ROUND(123.4567, 2); 

 -- 返回 123.46

SELECT ROUND(123.4567, -1);  

-- 返回 120

Truncate（）：将数字截断到指定小数位数

SELECT TRUNCATE(123.4567, 2); 

 -- 返回 123.45

SELECT TRUNCATE(123.4567, -1); 

 -- 返回 120

七、 sql语言的组成部分

sql语言的组成部分：

数据定义语言
数据处理语言
数据控制语言
数据库事务

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

八、 锁

共享锁S：共享锁锁定的资源可以被其他用户读取，但是其他用户无法修改，在执行select时，sql server会对对象加共享锁。（其他人可读不可写） 
排它锁X：(独占锁)其他人不能读也不能写（所以不会多重更新）。 
更新锁U：当SQL Server准备更新数据时，它首先对数据对象作更新锁锁定，这样数据将不能被修改，但可以读取。等到SQL Server确定要进行更新数据操作时，他会自动将更新锁换为独占锁，当对象上有其他锁存在时，无法对其加更新锁。 
架构锁：在执行依赖于表架构的操作时使用。架构锁的类型为：架构修改 (Sch-M) 和架构稳定性 (Sch-S)。

九、 视图

在视图中可以进行基本的增删改查操作；在视图中可以定义新的视图但是无法创建表；视图是一个虚表。

视图的特点：

（1）是一个虚表，是从一个或几个基本表（或视图）导出的表
（2）至存放视图的定义不存放视图对应的数据
（3）基表中的数据发生变化，从视图中查询出的数据也随之改变

建立视图：

CREATE VIEW <视图名> [(<列名> [,<列名>]…)] AS <子查询> [WITH CHECK OPTION];

With check option:对视图进行update、insert、delete操作是保证更新、插入或删除的行满足视图定义的谓词条件（及子查询中的条件表达式）。

子查询可以是任意的select
组成视图的属性列名：全部省略（由全部列组成）或全部指定（包含函数或表达式、多表连接、新名）。
关系数据库管理系统执行CREATE VIEW语句时只是把视图定义存入数据字典，并不执行其中的SELECT语句。

行列子集视图：只展示了单个基本表中的某几列。

删除视图：

DROP VIEW <视图名>[CASCADE]

(如果删除的视图中还有其他视图就要使用集联删除将所有的一起删掉)

查询视图：

视图更新插入：由于视图是虚表，不存储数据因此对视图的更新最终都是转换为对基本表更新，本质是基本表更新变动了。

Insert into is_student values(“1122334”,”zhangsan”,20);
Update is_student set sname=’liuchen’ where sno=’201215122’

当视图的更新不能唯一且有意义的转换成相应基本表的更新是视图无法更新。
例如：

由两个及以上基本表导出，不能更新
若视图的字段来自表达式或常数，不能进行insert和update的操作但是可以delete
有聚集函数、group by、distinct不能更新

有嵌套查询并且内层查询的from子句中涉及的表也是导出该视图的基本表不能更新

视图的作用：

简化用户操作、不同用户可以以一个不同的方式看数据，实现数据库共享、提供数据的逻辑独立性、对机密数据进行安全保护。

十、 事务

概念：事务时一组操作的集合，会把所有的操作作为一个整体提交或者撤销。

set @AUTOCOMMIT设定事务状态（默认为1）
set @AUTOCOMMIT = 0（此时，更新数据库，更新的数据只会在此控制台实现而不会上传数据库）
commit；（将更新的数据上传数据库）
rollback；（事务回滚撤销操作）
start transaction（无需commit也会更改数据库）

事务的特性：

原子性（是最小操作单元要么完全成功要么完全失败）
一致性（事务完成后数据保持一致的状态）
隔离性（不受外部并发操作影响）
持久性（改变是永久的）

并发事务问题：

脏读：一个事务读到另一个事务还没有提交的数据
不可重复读：一个事务先后读同一个记录但是数据不同
幻读：查询时没有对应行但是插入时数据存在

查看事务隔离级别

SELECT @@TRANSACTION ISOLATION

设置事务隔离级别

SET [SESSION|GLOBAL] TRANSACTION ISOLATION LEVEL  {READ UNCOMMITTED|READ COMMITTED|REPEATABLE READ|SERIALIZABLE}

事务的隔离界别越高并发能力越弱
事务在开发中的应用

十一、 完整性约束

1. 参照完整性约束

constraint cus_ord_fk foreign key (customer_id) REFERENCES customer(id)

创建一个名叫customer_id_fk的外键约束,其中外键指的是customer_id,并且参照的是 customers表中的id列
当外键在两个表中名称一致时需要指明表的来源

2. 字表的删除更新策略：

1）CASCADE 级联策略。使用此种策略时主表的记录被删除或者主键字段被修改时会同步删除或修改子表（当修改被参照表的一个元租造成了与参照表的不一致，则修改参照表中的所有造成不一致的元组）
2）NO ACTION 无动作策略。使用此种策略时要删除主表必须先删除子表，要删除主表的记录必须先删除子表关联的记录，不能更新主表主键字段的值。
3） RSTRICT 主表约束策略。此种策略对主表的约束跟 NO ACTION 一样
4）SET NO 置空策略。使用此种策略时，如果主表被删除或者主键被更改，则将子表中的外键设置为NULL。需要注意的是，如果子表的外键是主键或者是设置为NOT NULL的，则主表的删除和主键的更改跟 NO ACTION 一样。

主码约束：PRIMARY KEY
唯一性约束：UNION
非空值约束：FOREIGN KEY

十二、 执行顺序