目录
1.2 什么是 MySQL，以及 MySQL 的基本架构示意图？（*）
1.3 MySQL 的存储引擎有哪几种有什么区别？（InnoDB，MyISAM，Memory）（**）
2.3 B树、B+树、平衡二叉树、红黑树的区别和联系，为什么选择B+树？（***）
项目中用到的技术，一定要多看看原理，特别是能和数据结构和算法挂钩的那部分。学习一个技术，理解背后的设计理念、原理和解决问题的方法。
一、MySQL基础概念
1.1 什么是 SQL ?
SQL是结构化查询语言，具有语言功能一体化、面向元组集合的操作方式、语言简洁、易学易用等等。
| 数据定义 | CREATE |
| DROP | |
| ALTER | |
| 数据操作 | INSERT |
| DELETE | |
| UPDATE | |
| SELECT | |
| 数据控制 | GRANT |
| REVOKE |
1.2 什么是 MySQL，以及 MySQL 的基本架构示意图？（*）
1、MySQL 是一个关系型数据库管理系统，由瑞典 MySQL AB 公司开发，目前属于 Oracle 公司。MySQL 是一种关联数据库管理系统，关联数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。
2、MySQL 功能模块：
| 分层 | MYSQL功能模块 | 作用 |
| Server 层 | 连接器 | 管理连接，权限验证 |
| 查询缓存 | 命中直接返回 | |
| 分析器 | 词法分析，语法分析 | |
| 优化器 | 执行计划生成，索引选择 | |
| 执行器 | 操作引擎，返回结果 | |
| 引擎层 | 存储引擎 | 存储数据提供读写接口 |
1.3 MySQL 的存储引擎有哪几种有什么区别？（InnoDB，MyISAM，Memory）（**）
| 存储引擎 | InnoDB | MyISAM | Memory |
| 主键 | 支持 | 不支持 | |
| 事务 | 支持 | 不支持 | |
| 全文类型索引 | 不支持 | 支持 | |
| 行表锁 | MVCC（多版本并发控制）行锁 | 表锁 | |
| 索引 | B+Tree | B+Tree | Hash索引 |
| 使用场景 | 数据量大，事务完整性高，实现并发 | 小型应用，高速检索和存储 | 很快的读写速度 |
1.4 什么是范式？
1）第一范式：所有属性不可再分；
2）第二范式：消除了非主属性对码的部份依赖；
3）第三范式：消除了非主属性对码的局部依赖和传递依赖；
4）BC范式：消除了任何属性对码的局部依赖和传递依赖；
5）第五范式：消除了非平凡且非函数依赖的多值依赖，在BC范式的基础上，对于每一个非平凡多值依赖：X—>—>Y，X 都含有主码；
1.5 什么是存储过程和触发器？用什么来调用？
| 存储过程 | 触发器 | |
| 调用 | 使用CALL命令显式调用 | 当数据库操作语句发生时，隐式地调用 |
| 事务 | 存储过程内允许事务提交和回调 | 触发器内禁止事务提交和回调 |
| 参数 | 可以有参数输入 | 不接受参数输入 |
1.6 什么是游标？什么是视图？
| 视图 | 游标 |
| 将查询的结果生成一个虚拟的逻辑表 | 一个指向查询结果集的指针 |
| 对视图的修改不影响基本表 | 可对结果集做修改 |
| 是一个或多个表的行或列的子集 | 结果集 |
1.7 日志（**）
1）Server 层的 binlog：逻辑日志，用于归档，
2）引擎层的 redlog：物理日志，用于保证 crash-safe 能力。
3）crash-safe：保证即使数据库发生异常重启，之前提交的记录都不会丢失；
4）两阶段提交：为了让两份日志之间的逻辑一致。
|- 执行器读取数据。
|- 执行器修改数据，并调用引擎接口。
|- 引擎将新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务。
|- 执行器生成这个操作的 binlog，并把 binlog 写入磁盘。
|- 执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，更新完成。
1.8 MySQL数据库优化（***）
1）选取最适用的字段属性：将表中字段的宽度设得尽可能小；
2）使用连接（JOIN）来代替子查询(Sub-Queries)；
3）使用索引：选择合适的字段进行索引；
4）优化的查询语句：尽量将多条SQL语句压缩到一句SQL中、用where字句替换HAVING字句、要使用 select * from table，用具体的字段列表等等。
5）使用联合(UNION)来代替手动创建的临时表；
6）事务；
7）锁定表；
8）使用外键：保证数据的关联性；
二、索引
2.1 什么是索引，有哪几种索引？（*）
1、通过建立索引，可以提高数据的查询速度，例如：Hash 索引和 B/B+ 树索引；
2、Hash 索引：适合静态存储，因为每次插入数据都会发生大量数据移动的情况；
3、B/B+ 树索引：平衡多路查找树，适合动态插入和查找数据。
2.2 B/B+ 树索引（***）
1、B/B+树索引的作用？
|- B/B+树是因为平衡多路查找树，通过降低树的高度来提高数据的查找速度。
|- 比如：B+树索引：当有100个元素时，二叉树有8层，四叉树只有4层，四叉树只需要访问磁盘4次就能查找到数据。机械硬盘每次访问的时间大约10ms左右，数据页的大小默认是 16KB，因此，B+效率很高。
2、B+树索引和 Hash 索引对比，谁更优越？
|- B+ 树的叶结点之间形成链表，访问一个数据元素，Hash 索引会更方便，但是，当访问多个连续的元素时，B+树可以根据链表一次访问，效率更高，应用场景更广。
3、B/B+树索引对比，谁更优越？
|- 访问磁盘每次都是将一个磁盘页加载到内存之中，如果B退化到一个数组且数据量过大，那么无法加到内存中，而通过B+树，我们可以将B树的一个子树所对应的页加载到内存中。
2.2 聚族索引和非聚族索引的区别？（***）
1、聚族索引：对主键建立的 B+ 树索引，叶节点存储的是行信息；
2、非聚族索引：对非主键建立一个B+树索引，叶节点存储的是主键信息，再根据主键进行查找一次聚族索引。
2.3 B树、B+树、平衡二叉树、红黑树的区别和联系，为什么选择B+树？（***）
| 平衡二叉树 | 红黑树 | B树 | B+树 | |
| 树高（h=） |  |  |  | |
| 访问磁盘次数 | <= h | <= h | <= h-1 | h |
| 恢复平衡旋转次数 | | 2-3 | 1 | 1 |
| 优缺点 | 无法利用局部性 | 无法利用局部性 | 无法利用局部性 | 利用局部性 |
| 红黑树和平衡二叉树，由于逻辑上很近的节点（父子）物理上可能很远，无法利用局部性 | ||||
| 相比于B树，由于B+Tree内节点去掉了data域，因此可以拥有更大的出度，拥有更好的性能 | ||||
| B+Tree 带有顺序访问指针的，当访问多个连续的元素时，B+树可以根据链表一次访问，效率更高，应用场景更广 | ||||
想更了解 B+ Tree，推荐Tiger--Chen 的博客：B+Tree在数据库索引上拥有独特优势的原因
三、事务和锁
3.1 事务以及事务的四大特性？
事务：事务内的操作，要么全部执行成功，要么全部失败。
原子性、一致性、隔离性、永久性；
3.2 事务隔离等级（**）
1）未提交可读：可看见其它未提交事务的值（V1 = V2 = V3 = 2）；
2）已提交可读：只能读取其他事务提交后的值（V1 =1， V2 = V3 = 2）；
3）可重复读：自身事务未提交之前，看到其他的事务数据是一致的（V1 = V2 = 1, V3 = 2）；
4）串行化： 强制事务串行执行；
| 事务A | 事务B |
| 启动事务A，查询得到值1 | 启动事务B |
| 查询得到值1 | |
| 将1改为2 | |
| 查询得到值V1 | |
| 提交事务B | |
| 查询得到值V2 | |
| 提交事务A | |
| 查询得到值V3 |
3.3 MySQL 的锁？（**）
数据库锁、表锁、行锁
悲观锁：事务A（使用行锁）先将数据库的某一行加锁，事务B需要等待事务A释放锁之后，才能对这行数据进行修改操作。
乐观锁：事务A和B都对数据库的某一行数据进行修改，提交之前，检查是否有其他事务在修改当前数据，有则回滚，没有就提交。
3.4 并发事务处理带来的问题（**）
1、修改丢失：多个事务对同一数据多次修改，只有最后的修改有效；
2、脏读：事务A因为某种原因回滚，事务B的数据和数据库中不一致；
3、不可重复读：事务A在事务B提交后读取到新值，可重复读事务隔离等级可以解决该问题；
4、幻读：事务A在操作数据库表的同时，事务B插入某条数据，前后读取的数据信息不一致；
| 时间 | 丢失修改 | 不可重复读 | 读脏数据 | |||
| 事务A | 事务B | 事务A | 事务B | 事务A | 事务B | |
| 1 | 读 V = 10 | 读 V = 10 | 读 V = 10 | |||
| 2 | 读 V=10 | 读 V = 10 | V = V - 1 | |||
| 3 | V = V - 1 | V = V - 1 | 写 V = 9 | |||
| 4 | V = V - 1 | 写 V = 9 | 读 V = 9 | |||
| 5 | 写 V = 9 | 读 V = 9 | ROLLBACK | |||
| 6 | 写 V = 9 | V = 10 | ||||
| 7 | 事务A操作：V = V - 1，丢失了 | 事务A在事务B提交后读取到新值 可重复读事务隔离等级可以解决该问题 | 事务A因为某种原因回滚 事务B的数据和数据库中不一致 | |||
四、数据库查询实战
4.1 数据库创建
CREATE TABLE `s` (
`Sno` char(5) NOT NULL,
`Sname` varchar(20) NOT NULL,
`Sex` char(2) DEFAULT NULL,
`Age` int(3) DEFAULT NULL,
`Spec` varchar(16) DEFAULT NULL,
`Sdept` varchar(16) DEFAULT NULL,
PRIMARY KEY (`Sno`),
UNIQUE KEY `Sname` (`Sname`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
CREATE TABLE `c` (
`Cno` char(2) NOT NULL,
`Cname` varchar(20) DEFAULT NULL,
`Teacher` varchar(8) DEFAULT NULL,
PRIMARY KEY (`Cno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
CREATE TABLE `sc` (
`Sno` char(5) NOT NULL,
`Cno` char(2) NOT NULL,
`Grade` int(3) DEFAULT NULL,
CONSTRAINT `Cno_pk` PRIMARY KEY (`Sno`,`Cno`),
CONSTRAINT `Cno_pk` FOREIGN KEY (`Cno`) REFERENCES `c` (`Cno`),
CONSTRAINT `Sno_pk` FOREIGN KEY (`Sno`) REFERENCES `s` (`Sno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf84.2 简单查询
简单查询：
SELECT Sname, 2020-Age AS "Year of Birth" FROM S // AS <别名>
SELECT DISTINCT Sdept FROM S // DISTINCT 消除重复行
SELECT Sname, Age FROM S WHERE Age BETWEEN 20 AND 25 // 确定范围
SELECT Sno FROM SC WHERE Grade IN(80, 88, 90) // 确定集合
SELECT Sname FROM S WHERE Sname NOT LIKE '李%' // 字符匹配
SELECT Sno, Cno FROM SC WHERE Grade IS NOT NULL // 空值查询
SELECT Sno, Sname, Spec FROM S WHERE Age=19 AND Sex='女' // 多重条件查询
SELECT * FROM S ORDER BY Sdept, Spec, Age ASC/DESC // 查询结果排序4.3 连接查询
连接查询：
SELECT S.*, C.* FROM S, C // 广义笛卡尔链接
SELECT S.*, SC.* FROM S, SC WHERE S.Sno = SC.Sno // 等值连接
SELECT S.Sno, Sname, Sex, Age, Spec, Sdept, Cno, Grade FROM S, SC WHERE S.Sno = SC.Sno // 自然连接
SELECT X.Sno, X.Grade FROM SC X, SC Y WHERE X.Grade > Y.Grade AND Y.Sno='00101' // 自身连接
SELECT S.Sno, Sname, Sex, Age, Spec, Sdept, Cno, Grade FROM S LEFT|RIGHT|FULL OUTER JOIN SC ON S.Sno = SC.Sno // 外连接
// 多表连接
SELECT S.Sno, Sname, Cno, Grade FROM S, SC WHERE S.Sno = SC.Sno AND S.Sdept='计算机系'
SELECT S.Sno, Sname FROM S, C, SC WHERE S.Sno = SC.Sno AND C.Cno=SC.`Cno` AND C.Cname='数据库' AND SC.`Grade`>60
4.4 分组查询
分组查询
SELECT COUNT(*) FROM S WHERE S.`Sdept`='计算机系' // COUNT()
SELECT COUNT(Sno), AVG(Grade) FROM SC WHERE SC.`Cno`='2' // COUNT() 和 AVG()
SELECT Cno, COUNT(DISTINCT Sno), AVG(Grade) FROM SC GROUP BY Cno
// 查询开设专业数少于3个的系名以及专业数
SELECT Sdept, COUNT(DISTINCT Spec) FROM S GROUP BY Sdept HAVING COUNT(DISTINCT Spec) < 34.5 嵌套查询
套嵌查询
SELECT Sno, Sname FROM S WHERE spec IN (SELECT spec FROM s WHERE sname='李勇敏')
// 查询学习了和 01301 同学相同数目课程的同学
SELECT Sno FROM SC GROUP BY Sno HAVING COUNT(Sno) >= (SELECT COUNT(Cno) FROM SC WHERE sno='01301')
// 查询了所有学习2号课程的学生姓名、学号
SELECT Sno,sname FROM S WHERE EXISTS(SELECT * FROM SC WHERE Sno=s.sno AND cno='2')
// 查询其他系中大于等于计算机系所有学生年龄中最小的学生
SELECT sno,sname,age FROM S WHERE age <= ALL(SELECT MIN(age) FROM S WHERE sdept='计算机系') AND sdept<>'计算机系'4.6 存储过程
MySql 中有一个命令是DELIMITER，作用是设置命令段的结束符号，即遇到这个所设置的结束符号后，按回车，则命令段就可以执行了，通常默认情况下，命令的结束符号是分号（;），但是在存储过程中，过程体内可能会包含分号（;），因此需要将命令结束符号替换成其他的字符，如$$、//等，存储过程创建完成后，可以将命令段的结束符号重新设为分号。
创建存储过程
DELIMITER //
CREATE PROCEDURE test()
BEGIN
SELECT * FROM sc;
END //
DELIMITER ;
调用存储过程：call test();
创建存储过程带参数
DELIMITER //
CREATE PROCEDURE querysc(IN my_grade INT)
BEGIN
SELECT * FROM sc WHERE Grade = my_grade;
END //
DELIMITER ;
调用存储过程：call querysc(88);
扫一扫
51万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
