一.MySQL数据库引擎
1.1.什么是MySQL数据库引擎
MySQL是一个流行的关系型数据库管理系统，支持多种不同的存储引擎。数据库引擎是用于存储、管理和操作数据库数据的核心组件。
MySQL提供了多个数据库引擎，每个引擎都有其独特的特性、优点和适用场景。
1.2.常见的MySQL数据库引擎
- InnoDB引擎：InnoDB是MySQL的默认事务型引擎，支持事务和行级锁。它提供了高度可靠和高性能的数据存储，适用于大部分应用场景。
- MyISAM引擎：MyISAM是MySQL的默认非事务型引擎，支持全文索引和表锁。它适用于读写比较少，对并发性要求不高的应用场景。
- Memory引擎：Memory引擎将数据存储在内存中，速度非常快，但数据在MySQL重启或崩溃时会丢失。它适用于缓存和临时数据存储等场景。
- Archive引擎：Archive引擎适用于存储大量历史和归档数据，具有高度压缩和查询速度较慢的特点。
- NDB Cluster引擎：NDB Cluster引擎是MySQL集群解决方案中的一部分，支持数据分片和高可用性。
选择合适的数据库引擎取决于应用程序的需求，例如读写比例、数据一致性要求、并发性等。在创建数据库表时，可以根据需要选择合适的引擎。
1.3.ACID事务
ACID是指数据库事务必须满足的四个属性，确保数据库操作的一致性和可靠性。
下面是ACID事务的四个属性：
- 原子性（Atomicity）：原子性要求事务中的所有操作要么全部成功，要么全部回滚。如果一个操作失败，事务会被中断并回滚到事务开始之前的状态，保证数据的一致性。
- 一致性（Consistency）：一致性强调了事务的操作必须使数据库从一个一致状态转换到另一个一致状态。事务中的操作要符合预定义的规则和约束条件，保证数据的正确性。
- 隔离性（Isolation）：隔离性确保在并发环境下，事务的操作互不干扰。每个事务的操作对其他事务是隔离的，避免了多个事务并发操作时可能出现的数据冲突和不一致问题。
- 持久性（Durability）：持久性表示一旦事务提交，其所做的修改将永久保存在数据库中，即使在系统故障或中断之后，也能够恢复事务所做的修改。数据的永久性是通过将事务的修改异步写入到持久化存储介质（如磁盘）来实现的。
这些ACID属性保证了数据库事务的可靠性和一致性，使得数据库系统能够处理并发操作、故障恢复和数据一致性的问题。在设计和开发数据库应用时，需要考虑ACID属性以保证数据的正确性和可靠性。
1.4.四种隔离级别
在数据库管理系统中，定义了四个标准的隔离级别，用于控制并发事务之间的相互影响。这些隔离级别提供了不同程度的并发控制和数据一致性保证。下面是四种常见的隔离级别：
- 读未提交（Read Uncommitted）：在这个最低级别的隔离级别中，事务可以读取其他事务尚未提交的数据。这种隔离级别具有最高的并发性，但是可能会导致脏读（Dirty Read）问题。也就是说，一个事务读取到了未经提交的数据，而这些数据可能在之后被回滚或修改。
- 读已提交（Read Committed）：在这个隔离级别中，事务只能读取已经提交的数据。这样可以避免脏读问题，但是可能会导致不可重复读（Non-repeatable Read）问题。也就是说，同一个事务内多次读取同一数据可能得到不同的结果，因为其他事务可能在之间提交修改了数据。
- 可重复读（Repeatable Read）：在这个隔离级别中，事务在开始时确定一个一致性快照（Consistent Snapshot），在整个事务期间都使用这个快照。这样可以避免不可重复读问题，但是可能会导致幻读（Phantom Read）问题。幻读是指在同一个事务中多次查询时，由于其他事务插入或删除了数据，导致查询结果集发生变化。
- 串行化（Serializable）：这是最高级别的隔离级别，要求事务串行化执行，相当于每个事务依次执行。这样可以避免脏读、不可重复读和幻读问题，但是牺牲了并发性能。由于事务串行执行，会导致大量的锁竞争，对于高并发负载可能性能较低。
在实际应用中，根据业务需求和数据一致性要求，可以选择适当的隔离级别。一般而言，大部分应用会选择默认的可重复读隔离级别，它提供了较高的数据一致性和并发性能的平衡。但对于特定的场景，可能需要调整对应的隔离级别以满足需求。
1.5.何为脏读，幻读和不可重复读
脏读（Dirty Read）是指一个事务读取了另一个事务尚未提交的数据。假设事务A修改了某一数据，但尚未提交，事务B读取了这个数据，后来事务A回滚了修改，导致事务B读取到了一个无效或错误的数据。
幻读（Phantom Read）是指在同一个事务内，多次执行相同的查询，但结果集却不一致。这是由于其他事务在两次查询之间插入、删除或修改了数据，导致事务内部发生了幻觉般的数据行出现或消失。
不可重复读（Non-repeatable Read）是指一个事务内多次读取同一数据，在读取过程中发现数据已经被其他事务修改或删除，导致事务内部的读取操作得到了不一致的数据。
总结起来：
- 脏读是读取了其他未提交事务的数据。
- 幻读是指两次相同的查询产生了不一致的结果集，可能是由于其他事务插入或删除了数据。
- 不可重复读是指在同一个事务内两次读取同一数据，但两次读取的结果不一致，可能是由于其他事务修改或删除了数据。
这些问题可以通过使用适当的隔离级别来解决，并发控制机制（如锁定）来保证数据的一致性和准确性。
以下举例说明方便理解： 老二和老六
- 脏读（Dirty Read）：
老二： ------------- | 读取数据X | ------------- 老六： -------------- | 修改数据X | -------------- 老六修改了数据X，但还没有提交。 此时，老二读取到了老六未提交的数据X，即脏读。
- 幻读（Phantom Read）：
老二： ----------------- | 查询条件1的数据 | ----------------- 老六： --------------------- | 插入符合条件1的数据 | --------------------- 老二继续查询条件1的数据，发现有新插入的数据，导致结果集发生变化。此时，就发生了幻读。
- 不可重复读（Non-repeatable Read）：
老二： ----------------- | 读取数据X | ----------------- 老六： -------------- | 修改数据X | -------------- 老六修改了数据X，但还没有提交。 老二再次读取相同的数据X，发现数据发生了变化。此时，就发生了不可重复读。
二.常见的用法
1.创建数据库：使用MySQL命令行或者可视化工具（如phpMyAdmin或Navicat等），您可以创建一个新的数据库。可以使用以下语句创建数据库：
CREATE DATABASE database_name;
2.创建表格：在数据库中的表格是用来存储数据的结构化对象。您可以使用以下语句创建一个新的表格：
CREATE TABLE table_name ( column1 datatype, column2 datatype, column3 datatype, ... );
在上述语句中，table_name是您要创建的表格的名称，column1、column2等是表格中的列名，datatype是列的数据类型。
3.插入数据：一旦您创建了表格，您可以使用以下语句将数据插入表格中：
INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);
在上述语句中，table_name是要插入数据的表格的名称，column1、column2等是要插入数据的列名，value1、value2等是要插入的实际值。
4.查询数据：使用SELECT语句可以从表格中检索数据。以下是一个简单的SELECT语句示例：
SELECT column1, column2, ... FROM table_name WHERE condition;
在上述语句中，column1、column2是您要选择的列，table_name是要查询的表格的名称，condition是一个可选的筛选条件。
5.更新数据：使用UPDATE语句可以更新表格中的数据。以下是一个UPDATE语句示例：
UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;
在上述语句中，table_name是要更新数据的表格的名称，column1、column2是要更新的列名，value1、value2是要更新的值，condition是一个可选的筛选条件。
6.删除数据：使用DELETE语句可以删除表格中的数据。以下是一个DELETE语句示例：
DELETE FROM table_name
WHERE condition;
在上述语句中，table_name是要删除数据的表格的名称，condition是一个可选的筛选条件。
三.账号管理
在MySQL中，您可以使用以下方法进行账号管理：
';
- 创建账号：使用
CREATE USER语句可以创建新的账号，并指定账号的用户名和密码。例如：
CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
上述语句创建了一个名为username的账号，并指定了密码为password。
2.授权权限：在创建账号之后，您可以使用GRANT语句为账号赋予相应的权限。例如，以下语句授予账号username对某个数据库的读写权限
GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'localhost';
上述语句将对database_name数据库的所有权限授予了username账号。
3.撤销权限：如果您想要撤销某个账号的权限，可以使用REVOKE语句。例如，以下语句撤销了username账号对某个数据库的写权限：
REVOKE INSERT, UPDATE, DELETE ON database_name.* FROM 'username'@'localhost';
上述语句将撤销username账号在database_name数据库中的插入、更新和删除权限。
4.修改密码：如果您想要修改账号的密码，可以使用ALTER USER语句。例如，以下语句修改了username账号的密码为新密码new_password
ALTER USER 'username'@'localhost' IDENTIFIED BY 'new_password';
上述语句修改了username账号的密码为new_password。
5.删除账号：如果您想要删除一个账号，可以使用DROP USER语句。例如，以下语句删除了username账号：
DROP USER 'username'@'localhost';
上述语句删除了username账号。
注意：在执行这些账号管理操作时，请确保具有足够的权限来执行这些操作。默认情况下，只有具有适当权限的管理员才能执行这些操作。
四.MySql精选15道面试题
4.1.表数据
学生表：
insert into t_student values('01' , '赵雷' , '1990-01-01' , '男'); insert into t_student values('02' , '钱电' , '1990-12-21' , '男'); insert into t_student values('03' , '孙风' , '1990-12-20' , '男'); insert into t_student values('04' , '李云' , '1990-12-06' , '男'); insert into t_student values('05' , '周梅' , '1991-12-01' , '女'); insert into t_student values('06' , '吴兰' , '1992-01-01' , '女'); insert into t_student values('07' , '郑竹' , '1989-01-01' , '女'); insert into t_student values('09' , '张三' , '2017-12-20' , '女'); insert into t_student values('10' , '李四' , '2017-12-25' , '女'); insert into t_student values('11' , '李四' , '2012-06-06' , '女'); insert into t_student values('12' , '赵六' , '2013-06-13' , '女'); insert into t_student values('13' , '孙七' , '2014-06-01' , '女');
教师表：
insert into t_teacher values('01' , '张三'); insert into t_teacher values('02' , '李四'); insert into t_teacher values('03' , '王五');
课程表：
insert into t_course values('01' , '语文' , '02'); insert into t_course values('02' , '数学' , '01'); insert into t_course values('03' , '英语' , '03');
成绩表：
insert into t_score values('01' , '01' , 80); insert into t_score values('01' , '02' , 90); insert into t_score values('01' , '03' , 99); insert into t_score values('02' , '01' , 70); insert into t_score values('02' , '02' , 60); insert into t_score values('02' , '03' , 80); insert into t_score values('03' , '01' , 80); insert into t_score values('03' , '02' , 80); insert into t_score values('03' , '03' , 80); insert into t_score values('04' , '01' , 50); insert into t_score values('04' , '02' , 30); insert into t_score values('04' , '03' , 20); insert into t_score values('05' , '01' , 76); insert into t_score values('05' , '02' , 87); insert into t_score values('06' , '01' , 31); insert into t_score values('06' , '03' , 34); insert into t_score values('07' , '02' , 89); insert into t_score values('07' , '03' , 98);
4.2.题目解答
01）查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数
SELECT st.*, s1.score '01课程', s2.score '02课程' FROM ( SELECT * FROM t_mysql_score WHERE cid = "01" ) s1, ( SELECT * FROM t_mysql_score WHERE cid = "02" ) s2, t_mysql_student st WHERE s1.sid = s2.sid AND s1.sid = st.sid AND s1.score > s2.score
02）查询同时存在" 01 "课程和" 02 "课程的情况
SELECT s.*, ( CASE WHEN s1.cid = "01" THEN s1.score END ) '01课程', ( CASE WHEN s2.cid = "02" THEN s1.score END ) '02课程' FROM ( SELECT * FROM t_mysql_score WHERE cid = "01" ) s1, ( SELECT * FROM t_mysql_score WHERE cid = "02" ) s2, t_mysql_student s WHERE s1.sid = s2.sid AND s1.sid = s.sid
03）查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )
SELECT s1.sid, s1.cid, s1.score '01课程', s2.score '02课程' FROM ( SELECT * FROM t_mysql_score sc WHERE sc.cid = "01" ) s1 LEFT JOIN ( SELECT * FROM t_mysql_score sc WHERE sc.cid = "02" ) s2 ON s1.sid = s2.sid
04）查询不存在" 01 "课程但存在" 02 "课程的情况
SELECT * FROM t_mysql_score WHERE sid IN ( SELECT sid FROM t_mysql_student WHERE sid NOT IN ( SELECT sid FROM t_mysql_score WHERE cid = '01' ) ) AND cid = '02'
05）查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
SELECT s.sid, s.sname, ROUND( AVG( c.score ), 2 ) '平均成绩' FROM t_mysql_student s, t_mysql_score c WHERE s.sid = c.sid GROUP BY s.sid, s.sname HAVING avg( c.score ) >= 60
06）查询在t_score表存在成绩的学生信息
SELECT s.* FROM t_mysql_score sc, t_mysql_student s WHERE sc.sid = s.sid GROUP BY s.sid
07）查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
SELECT s.sid, s.sname, count( sc.cid ) '选课总数', sum( sc.score ) '总成绩' FROM t_mysql_student s, t_mysql_score sc WHERE s.sid = sc.sid GROUP BY s.sid, s.sname
08）查询「李」姓老师的数量
SELECT * FROM t_mysql_teacher t WHERE tname LIKE '李%'
09）查询学过「张三」老师授课的同学的信息
SELECT * FROM t_mysql_student WHERE sid IN ( SELECT sc.sid FROM t_mysql_teacher t, t_mysql_score sc, t_mysql_course c WHERE t.tid = c.tid AND sc.cid = c.cid AND t.tname = "张三" GROUP BY sc.sid)
10）查询没有学全所有课程的同学的信息
SELECT s.*, count( sc.score ) '课程数量' FROM t_mysql_student s, t_mysql_score sc WHERE s.sid = sc.sid GROUP BY s.sid, s.sname HAVING count( sc.score ) < ( SELECT count( 1 ) FROM t_mysql_course )
11）查询没学过"张三"老师讲授的任一门课程的学生姓名
SELECT sname FROM t_mysql_student WHERE sid NOT IN ( SELECT sc.sid FROM t_mysql_score sc, t_mysql_course c, t_mysql_teacher t WHERE t.tid = c.tid AND sc.cid = c.cid AND t.tname = "张三" )
12）查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
SELECT s.sid, s.sname, ROUND( AVG( sc.score ), 2 ) 平均成绩 FROM t_mysql_student s, t_mysql_score sc WHERE sc.sid = s.sid AND sc.score < 60 GROUP BY s.sid, s.sname HAVING count( sc.score ) >= 2
13）检索" 01 "课程分数小于 60，按分数降序排列的学生信息
SELECT s.*, sc.score FROM t_mysql_student s, t_mysql_score sc WHERE s.sid = sc.sid AND sc.cid = "01" AND sc.score < 60 ORDER BY sc.score DESC
14）按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
SELECT s.sid, s.sname, IF ( max( CASE WHEN sc.cid = '01' THEN sc.score END ) > 0, max( CASE WHEN sc.cid = '01' THEN sc.score END ), 0 ) 语文, IF ( max( CASE WHEN sc.cid = '02' THEN sc.score END ) > 0, max( CASE WHEN sc.cid = '02' THEN sc.score END ), 0 ) 数学, IF ( max( CASE WHEN sc.cid = '03' THEN sc.score END ) > 0, max( CASE WHEN sc.cid = '03' THEN sc.score END ), 0 ) 英语, ROUND( AVG( sc.score ), 2 ) 平均成绩 FROM t_mysql_student s, t_mysql_score sc WHERE s.sid = sc.sid GROUP BY s.sid, s.sname
15）查询各科成绩最高分、最低分和平均分：以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90 要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
SELECT c.cid, c.cname, max( sc.score ) '最高分', min( sc.score ) '最低分', ROUND( AVG( sc.score ), 2 ) '平均分', CONCAT(ROUND(sum( IF ( sc.score >= 60, 1, 0 ) ) / COUNT( sc.score ) * 100,2 ),'%' ) 及格率, CONCAT(ROUND(sum( IF ( sc.score >= 70 AND sc.score < 80, 1, 0 ) ) / COUNT( sc.score ) * 100,2 ),'%' ) 中等率, CONCAT(ROUND(sum( IF ( sc.score >= 80 AND sc.score < 90, 1, 0 ) ) / COUNT( sc.score ) * 100,2 ),'%' ) 优良率, CONCAT(ROUND(sum( IF ( sc.score >= 90, 1, 0 ) ) / COUNT( sc.score ) * 100,2 ),'%' ) 优秀率, COUNT( sc.score ) 选修人数 FROM t_mysql_score sc, t_mysql_course c WHERE sc.cid = c.cid GROUP BY sc.cid ORDER BY 选修人数 DESC, c.cid ASC;
