MySQL面试题-阿里云开发者社区

1.理论知识点

1.1DELETE、DROP、TRUNCATE三者的区别

DELETE、DROP和TRUNCATE是SQL语句中用于删除数据和对象的操作，它们之间有一些重要的区别：

1.DELETE：DELETE语句用于从表中删除满足条件的数据行。它是一个事务操作，可以使用WHERE子句指定删除的条件。DELETE语句执行会触发表上的触发器，并且可以配合使用ROLLBACK命令撤销删除操作。DELETE语句只删除表中的数据，而不会删除表本身。

2.DROP：DROP语句用于删除数据库对象，可以是表、索引、视图、存储过程等。DROP TABLE语句可以删除整个表及其相关的索引、触发器、约束等。DROP语句是一个DDL（数据定义语言）操作，它会立即删除对象，且无法恢复。DROP语句一般不回滚，一旦执行成功，相关对象将永久删除。

3.TRUNCATE：TRUNCATE语句用于从表中删除所有数据，但保留表的结构。它是一个DDL操作，比DELETE更快，因为它仅仅是删除数据页的引用，而不是逐行删除数据。TRUNCATE语句不会触发表上的触发器，也无法撤销。TRUNCATE语句在执行成功后，表的计数器会被重置，自动增量插入的计数器也会被重置。

总结起来，DELETE用于删除表中的特定行数据，DROP用于删除数据库对象，TRUNCATE用于删除表中的所有数据。DELETE是一个DML（数据操作语言）操作，DROP和TRUNCATE是DDL操作。DELETE语句可以撤销，而DROP和TRUNCATE删除的数据或对象无法恢复。根据需要选择合适的语句来执行删除操作。

1.2行转列的使用

行转列是一种重塑或重新组织数据的操作，将原先以行的形式存储的数据转换为以列的形式存储。行转列的技术可以使用在各种领域，如数据分析、报表生成等。

比如：

课程/成绩	小美	小帅	小雅	....
语文	80	70	69	...
数学	90	87	96	...

从上面这样变成下面这样：

课程/成绩	语文	数学
小美	80	90
小帅	70	87
小雅	69	96
...	...	...

这样看是不是简便，这就是行转列

1.3那么在Mysql中怎么使用

SELECT
  t1.sid,
  t1.cid  '"01"科目',
  t2.cid  '"02"科目'
FROM
  ( SELECT * FROM t_mysql_score WHERE cid = '01' ) t1
  LEFT JOIN ( SELECT * FROM t_mysql_score WHERE cid = '02' ) t2 ON t1.sid = t2.sid
--上面是用别名的方式，但是不符合规范，
--最好采用下面的方式
SELECT
  t1.sid,
  (CASE WHEN t1.cid = '01' THEN t1.score END ) '"01"科目',
  (CASE WHEN t2.cid = '02' THEN t2.score END ) '"02"科目'
FROM
  ( SELECT * FROM t_mysql_score WHERE cid = '01' ) t1
  LEFT JOIN ( SELECT * FROM t_mysql_score WHERE cid = '02' ) t2 ON t1.sid = t2.sid

1.4.什么是主键和外键？

主键是标识数据库表中唯一记录的列，用于确保数据的唯一性。

外键是用于建立表与表之间关系的列，它引用了另一个表的主键。

1.5.连表查询的使用

在MySQL中，使用JOIN语句可以实现连表查询，它允许你从多个表中获取相关联的数据。以下是常见的连表查询语句：

内连接（INNER JOIN）：返回两个或多个表中满足连接条件的行。

SELECT *
FROM table1
JOIN table2 ON table1.column = table2.column;

左连接（LEFT JOIN）：返回左表中的所有行，以及右表中满足连接条件的行。

SELECT *
FROM table1
RIGHT JOIN table2 ON table1.column = table2.column;

全连接（FULL OUTER JOIN）：返回两个表中的所有行，无论是否满足连接条件。

SELECT *
FROM table1
FULL OUTER JOIN table2 ON table1.column = table2.column;

自连接（SELF JOIN）：将表视为两个独立的实体，通过连接条件关联同一个表中的不同行。

SELECT *
FROM table1 AS t1
JOIN table1 AS t2 ON t1.column = t2.column;

连接条件可以基于一列或多列的相等性，你可以根据实际需求调整连接条件。使用连表查询可以从多个表中获取相关的数据，以便进行更复杂的数据分析和处理。在编写连表查询时，请确保表之间的连接条件是正确的，并仔细考虑查询的性能和效率。

1.6.聚合函数的使用及注意事项

在MySQL中，聚合函数用于对数据进行聚合计算，返回一个单一的结果。以下是一些常见的聚合函数及其使用方法：

COUNT：计算给定列或表中的行数。

SELECT COUNT(column_name) FROM table_name;
SELECT COUNT(*) FROM table_name; -- 计算表中的总行数

SUM：计算给定列的总和。

SELECT SUM(column_name) FROM table_name;

AVG：计算给定列的平均值。

SELECT AVG(column_name) FROM table_name;

MAX：返回给定列中的最大值。

SELECT MAX(column_name) FROM table_name;

MIN：返回给定列中的最小值。

SELECT MIN(column_name) FROM table_name;

这些聚合函数可以与其他SQL语句（如SELECT、WHERE、GROUP BY等）一起使用，以实现对数据的聚合计算和筛选。在使用聚合函数时，可以根据需要进行分组（使用GROUP BY子句）或过滤（使用HAVING子句）。此外，聚合函数还可以嵌套使用，以进行更复杂的计算。

注意事项:

聚合函数通常忽略NULL值，除非使用特定的修饰符（如COUNT(*)）来计算行数。如果要使用多个聚合函数，可以将它们作为同一SELECT语句的不同部分进行计算。

1.7.HAVING子句使用及注意事项

在MySQL中，HAVING子句与GROUP BY子句一起使用，用于对分组后的结果进行筛选。它允许在分组计算后对分组结果进行过滤，并返回满足特定条件的分组。

使用格式：

SELECT column1, column2, ..., aggregate_function(column)
FROM table_name
GROUP BY column1, column2, ...
HAVING condition;

注意事项:

HAVING条件：HAVING子句用于指定条件，过滤满足特定条件的分组。你可以在HAVING子句中使用聚合函数、列和运算符来构造条件表达式。HAVING条件将在分组计算后进行筛选，仅返回满足条件的分组。

聚合函数：在SELECT列表中使用聚合函数进行计算，如SUM、AVG、COUNT等。HAVING子句中可以使用这些聚合函数。

HAVING vs WHERE：HAVING子句用于筛选分组结果集，而WHERE子句用于筛选行。因此，HAVING子句只能在包含GROUP BY子句的查询中使用。

以下是一个示例，展示了如何使用HAVING子句筛选出销售额超过1000的部门：

SELECT department, SUM(sales) AS total_sales
FROM sales_table
GROUP BY department
HAVING total_sales > 1000;

在上述查询中，首先按部门进行了分组，并计算每个部门的销售总额。然后，HAVING子句筛选出总销售额超过1000的部门。

使用HAVING子句时，注意事项:

HAVING子句只能在包含GROUP BY子句的查询中使用。

HAVING子句可以使用聚合函数、列和运算符来构造条件表达式。

可以使用AND、OR、NOT等逻辑运算符连接多个条件，并构建复杂的筛选条件。

HAVING子句中的条件可以引用SELECT列表中的别名。

注意HAVING条件的位置和顺序，它应该出现在GROUP BY子句之后。

通过使用HAVING子句，你可以对分组计算后的结果进行进一步的筛选和条件过滤，以得到满足特定条件的分组。这对于数据分析和统计非常有用。

2.技术方面(MySQL面试真题)

2.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 成绩

二、表数据：

-- 学生表
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面试题

1.查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数

首先编写SQL语句

SELECT
  t3.*,
  ( CASE WHEN t1.cid = '01' THEN t1.score END ) '"01"课程',
  ( CASE WHEN t2.cid = '02' THEN t2.score END ) '"02"课程' 
FROM
  ( SELECT * FROM t_mysql_score WHERE cid = '01' ) t1,
  ( SELECT * FROM t_mysql_score WHERE cid = '02' ) t2,
  t_mysql_student t3 
WHERE
  t1.sid = t2.sid 
  AND t1.score > t2.score 
  AND t1.sid = t3.sid

输出结果为：

2.查询同时存在" 01 "课程和" 02 "课程的情况

首先编写SQL语句:

SELECT
  t3.*,
  ( CASE WHEN t1.cid = '01' THEN t1.score END ) '"01"课程',
  ( CASE WHEN t2.cid = '02' THEN t2.score END ) '"02"课程' 
FROM
  ( SELECT * FROM t_mysql_score WHERE cid = '01' ) t1,
  ( SELECT * FROM t_mysql_score WHERE cid = '02' ) t2,
  t_mysql_student t3 
WHERE
  t1.sid = t2.sid 
  AND t1.sid = t3.sid

输出结果为：

03.查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )

首先编写SQL语句:

SELECT
  t1.sid,
  (CASE WHEN t1.cid = '01' THEN t1.score END ) '"01"课程',
  (CASE WHEN t2.cid = '02' THEN t2.score END ) '"02"课程'
FROM
  ( SELECT * FROM t_mysql_score WHERE cid = '01' ) t1
  LEFT JOIN ( SELECT * FROM t_mysql_score WHERE cid = '02' ) t2 ON t1.sid = t2.sid

输出结果为：

4.查询不存在" 01 "课程但存在" 02 "课程的情况

首先编写SQL语句:

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'

输出结果为：

5.查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩

首先输入代码:

SELECT
  stu.sid,
  stu.sname,
  ROUND( AVG( sc.score ), 2 ) '平均成绩'
FROM
  t_mysql_score sc,
  t_mysql_student stu 
WHERE
  sc.sid = stu.sid 
GROUP BY
  sid,
  sname 
HAVING
  AVG( sc.score ) >= 60

输出结果为：

6.查询在t_mysql_score表存在成绩的学生信息

首先输入代码：

SELECT
  * 
FROM
  t_mysql_student 
WHERE
  sid IN ( SELECT sid FROM t_mysql_score GROUP BY sid )

输出结果为：

7.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null)

首先输出代码：

SELECT
*
FROM
  ( SELECT * FROM t_mysql_student ) t1
  LEFT JOIN ( SELECT sid, COUNT( cid ) '选课总数', sum( score ) '所有课程的总成绩' FROM t_mysql_score GROUP BY sid ) t2 ON t1.sid = t2.sid

输出结果为：

8.查询「李」姓老师的数量

首先编写SQL语句：

SELECT COUNT(*) '李姓老师的数量' from t_mysql_teacher where tname LIKE '李%'

输出结果为：

9.查询学过「张三」老师授课的同学的信息

首先写SQL语句:

SELECT
  * 
FROM
  t_mysql_student 
WHERE
  sid IN (
SELECT
  sid 
FROM
  t_mysql_score 
WHERE
  cid =  ( SELECT cid FROM t_mysql_course WHERE tid = ( SELECT tid FROM t_mysql_teacher WHERE tname LIKE '李%' ) ) 
  )

输出结果为：

10.查询没有学全所有课程的同学的信息

首先编写SQl语句：

SELECT
  s.* 
FROM
  t_mysql_student s
  LEFT JOIN ( SELECT sid, COUNT( DISTINCT cid ) AS course_count FROM t_mysql_score GROUP BY sid ) sc ON s.sid = sc.sid 
WHERE
  sc.course_count < ( SELECT COUNT( * ) FROM t_mysql_course );

输出结果为：

11.查询没学过"张三"老师讲授的任一门课程的学生姓名

首先编写SQl语句：

SELECT
  * 
FROM
  t_mysql_student 
WHERE
  sid NOT IN (
SELECT
  s.sid 
FROM
  t_mysql_score sc,
  t_mysql_teacher t,
  t_mysql_course c,
  t_mysql_student s 
WHERE
  c.cid = sc.cid 
  AND c.tid = t.tid 
  AND sc.sid = s.sid 
  AND t.tname = '张三' 
  )

输出结果为：

12.查询两门及其以上不及格（低于60分）课程的同学的学号，姓名及其平均成绩

首先编写SQl语句：

SELECT
  s.sid,
  s.sname,
  COUNT( * ) 不及格课程数,
  ROUND( AVG( sc.score ), 2 ) 平均成绩 
FROM
  t_mysql_score sc,
  t_mysql_student s 
WHERE
  sc.sid = s.sid 
  AND sc.score < 60 GROUP BY s.sid, s.sname HAVING 不及格课程数 >=2

输入结果为：

13.检索" 01 "课程分数小于 60，按分数降序排列的学生信息

首先编写SQl语句：

SELECT
  s.*,
  sc.score 
FROM
  t_mysql_score sc,
  t_mysql_student s 
WHERE
  sc.sid = s.sid 
  AND sc.score < 60 
  AND sc.cid = '01' 
ORDER BY
  sc.score DESC

输出结果为：

14.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

首先编写SQl语句：

SELECT
  s.sid,
  s.sname,
  max(CASE WHEN sc.cid = '01' THEN sc.score END ) 语文 ,
  max(CASE WHEN sc.cid = '02' THEN sc.score END ) 数学 ,
  max(CASE WHEN sc.cid = '03' THEN sc.score END ) 英语,
  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
要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列。

首先编写SQl语句：

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.1DELETE、DROP、TRUNCATE三者的区别

1.2行转列的使用

1.3那么在Mysql中怎么使用

1.4.什么是主键和外键？

1.5.连表查询的使用

1.6.聚合函数的使用及注意事项

1.7.HAVING子句使用及注意事项

2.技术方面(MySQL面试真题)

2.1数据库脚本

2.2面试题

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

MySQL面试题

1.理论知识点

1.1DELETE、DROP、TRUNCATE三者的区别

1.2行转列的使用

1.3那么在Mysql中怎么使用

1.4.什么是主键和外键？

1.5.连表查询的使用

1.6.聚合函数的使用及注意事项

1.7.HAVING子句使用及注意事项

2.技术方面(MySQL面试真题)

2.1数据库脚本

2.2面试题

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像