MySQL之面试真题详解-阿里云开发者社区

1.前言

1.1InnDB存储引擎与MyISAM存储引擎的区别

InnoDB 引擎和 MyISAM 引擎是 MySQL 中的两种常见存储引擎，它们在很多方面有着不同的特性，下面是它们之间的主要区别：

1.事务支持：InnoDB 引擎支持事务（ACID），可以保证数据的一致性和完整性。支持事务意味着可以使用 COMMIT 和 ROLLBACK 语句来控制数据的提交和回滚。

MyISAM 引擎不支持事务，不能进行回滚操作。

2.锁定粒度：InnoDB 引擎支持行级锁定（row-level locking），允许并发读写不同行。这样可以提高多用户并发访问时的性能。

MyISAM 引擎使用表级锁定（table-level locking），当一个用户在执行写操作时，其他用户无法对同一表进行读写操作，会带来并发性能的限制。

3.外键支持：InnoDB 引擎支持外键约束（Foreign Key Constraint），可以在表之间建立关联关系，确保数据的完整性。通过外键约束，可以实现级联更新和级联删除操作。

MyISAM 引擎不支持外键约束，无法建立关联关系。

4.完整性约束：InnoDB 引擎支持各种完整性约束，如主键约束、唯一约束、非空约束等。它可以在数据层面上保证数据的完整性。

MyISAM 引擎在数据层面上对数据的完整性没有强制性要求，不支持主键约束和唯一约束。完整性约束需要在应用层面上进行控制。

5.性能：通常情况下，对于大量读操作的应用，MyISAM 引擎具有较好的性能表现，因为它使用表级锁定，对于并发读取不会产生冲突。

对于大量写操作和并发写入的应用，InnoDB 引擎通常表现更好，因为它支持行级锁定和事务处理。

综上所述，选择适合的存储引擎应根据应用的具体需求来判断。如果需要事务支持、并发控制和数据完整性，推荐使用 InnoDB 引擎；如果对性能要求比较高，并且没有复杂的事务和约束需求，可以考虑使用 MyISAM 引擎。

1.2关系型数据库常用的三个操作

DELETE、DROP和TRUNCATE，详解如下：

1.DELETE：

功能：DELETE 用于从表中删除一条或多条数据。

影响：DELETE 操作是 DML（数据操作语言）语句，它会触发触发器、产生回滚日志，且可以使用 WHERE 子句对要删除的数据进行筛选。

回滚：DELETE 操作可以通过事务回滚来撤消已删除的行。

返回值：DELETE 操作在执行时会返回删除的行数。

2.DROP：

功能：DROP 用于删除整个数据库对象（如表、视图、索引等）。

影响：DROP 操作是 DDL（数据定义语言）语句，它会永久性地从数据库中删除对象。

回滚：DROP 操作不可回滚，一旦执行成功，对象将被永久性删除。

返回值：DROP 操作不返回任何值。

3.TRUNCATE：

功能：TRUNCATE 用于删除表中的所有数据。

影响：TRUNCATE 操作是 DDL 语句，它会将表完全清空，并且比 DELETE 操作更快，因为它不会触发触发器、不写回滚日志，并且不使用 WHERE 子句进行筛选。

回滚：TRUNCATE 操作不可回滚，一旦执行成功，数据将被永久性删除。

返回值：TRUNCATE 操作在执行时不返回删除的行数。

1.3行转列

让我给出一个更具体的MySQL行转列示例，并进行效果展示。

假设有一个名为sales的表格，包含以下字段：product_id、month和quantity。表中的记录表示每个产品在每个月的销售数量。

现在我们想要将每个产品在不同月份的销售数量进行行转列操作，生成一个报表。假设有三个月份：January、February和March。

下面是一个实际的MySQL行转列示例及效果展示：

首先，创建一个示例表格并插入一些数据：

CREATE TABLE sales (
  product_id INT,
  month VARCHAR(20),
  quantity INT
);
INSERT INTO sales (product_id, month, quantity) VALUES
  (1, 'January', 100),
  (1, 'February', 150),
  (1, 'March', 200),
  (2, 'January', 50),
  (2, 'February', 75),
  (2, 'March', 100);

接下来，执行行转列的SQL查询语句：

SELECT
  product_id,
  SUM(CASE WHEN month = 'January' THEN quantity ELSE 0 END) AS January,
  SUM(CASE WHEN month = 'February' THEN quantity ELSE 0 END) AS February,
  SUM(CASE WHEN month = 'March' THEN quantity ELSE 0 END) AS March
FROM
  sales
GROUP BY
  product_id;

运行以上SQL查询后，将会得到如下的结果：

+------------+---------+----------+-------+
| product_id | January | February | March |
+------------+---------+----------+-------+
|     1      |   100   |   150    |  200  |
|     2      |   50    |    75    |  100  |
+------------+---------+----------+-------+

以上结果展示了每个产品在不同月份的销售数量，通过行转列的操作，将原来多行的数据转换为了以月份为列的形式。

希望这个示例能够帮助你理解MySQL中的行转列操作以及其效果！需要注意的是，具体的语法和表结构可能会有所不同，适当根据实际情况进行调整。

2.面试题详解

2.1建立数据表

2.1.1要求

-- 1.学生表-t_mysql_student

-- sid 学生编号,sname 学生姓名,sage 学生年龄,ssex 学生性别

-- 2.教师表-t_mysql_teacher

-- tid 教师编号,tname 教师名称

-- 3.课程表-t_mysql_course

-- cid 课程编号,cname 课程名称,tid 教师名称

-- 4.成绩表-t_mysql_score

-- sid 学生编号,cid 课程编号,score 成绩

2.1.2代码

学生表：

insert into t_mysql_student values('01' , '赵雷' , '1990-01-01' , '男');
insert into t_mysql_student values('02' , '钱电' , '1990-12-21' , '男');
insert into t_mysql_student values('03' , '孙风' , '1990-12-20' , '男');
insert into t_mysql_student values('04' , '李云' , '1990-12-06' , '男');
insert into t_mysql_student values('05' , '周梅' , '1991-12-01' , '女');
insert into t_mysql_student values('06' , '吴兰' , '1992-01-01' , '女');
insert into t_mysql_student values('07' , '郑竹' , '1989-01-01' , '女');
insert into t_mysql_student values('09' , '张三' , '2017-12-20' , '女');
insert into t_mysql_student values('10' , '李四' , '2017-12-25' , '女');
insert into t_mysql_student values('11' , '李四' , '2012-06-06' , '女');
insert into t_mysql_student values('12' , '赵六' , '2013-06-13' , '女');
insert into t_mysql_student values('13' , '孙七' , '2014-06-01' , '女');

教师表：

insert into t_mysql_teacher values('01' , '张三');
insert into t_mysql_teacher values('02' , '李四');
insert into t_mysql_teacher values('03' , '王五');

课程表：

insert into t_mysql_course values('01' , '语文' , '02');
insert into t_mysql_course values('02' , '数学' , '01');
insert into t_mysql_course values('03' , '英语' , '03');

成绩表：

insert into t_mysql_score values('01' , '01' , 80);
insert into t_mysql_score values('01' , '02' , 90);
insert into t_mysql_score values('01' , '03' , 99);
insert into t_mysql_score values('02' , '01' , 70);
insert into t_mysql_score values('02' , '02' , 60);
insert into t_mysql_score values('02' , '03' , 80);
insert into t_mysql_score values('03' , '01' , 80);
insert into t_mysql_score values('03' , '02' , 80);
insert into t_mysql_score values('03' , '03' , 80);
insert into t_mysql_score values('04' , '01' , 50);
insert into t_mysql_score values('04' , '02' , 30);
insert into t_mysql_score values('04' , '03' , 20);
insert into t_mysql_score values('05' , '01' , 76);
insert into t_mysql_score values('05' , '02' , 87);
insert into t_mysql_score values('06' , '01' , 31);
insert into t_mysql_score values('06' , '03' , 34);
insert into t_mysql_score values('07' , '02' , 89);
insert into t_mysql_score values('07' , '03' , 98);

2.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_mysql_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;

运行结果如下:

最后MySQL之面试真题详解就到这里，祝大家在敲代码的路上一路通畅!

感谢大家的观看 !

MySQL之面试真题详解

1.前言

1.1InnDB存储引擎与MyISAM存储引擎的区别

1.2关系型数据库常用的三个操作

1.3行转列

2.面试题详解

2.1建立数据表

2.1.1要求

2.1.2代码

2.2试题讲解与展示

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像