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

一.面试题

创建模拟数据库

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

学生表t_mysql_student

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' , '女');

教师表-t_mysql_teacher

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

课程表-t_mysql_course

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

成绩表-t_mysql_score

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);

面试题讲解

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;

知识点行转列

在MySQL中将行转列是通过使用聚合函数和条件判断来实现的。具体步骤如下：

1. 创建一个新的查询，使用聚合函数和条件判断来转换行为列。例如，使用CASE语句将不同的行值转换为列值。

  sql
   SELECT 
     MAX(CASE WHEN row_type = 'row1' THEN row_value END) AS column1,
     MAX(CASE WHEN row_type = 'row2' THEN row_value END) AS column2,
     MAX(CASE WHEN row_type = 'row3' THEN row_value END) AS column3
   FROM your_table

在上面的示例中，假设你要将行类型(row_type)为'row1'、'row2'和'row3'的行值(row_value)转换为列(column1、column2和column3)。

2. 使用GROUP BY子句，根据需要对结果进行分组。如果不需要分组，则可以省略这一步。

   sql
   SELECT 
     grouping_column,
     MAX(CASE WHEN row_type = 'row1' THEN row_value END) AS column1,
     MAX(CASE WHEN row_type = 'row2' THEN row_value END) AS column2,
     MAX(CASE WHEN row_type = 'row3' THEN row_value END) AS column3
   FROM your_table
   GROUP BY grouping_column

在这个示例中，假设你要根据一个分组列(grouping_column)对行进行分组。

请注意，上述示例中的"your_table"应替换为你的实际表名，"row_type"和"row_value"应替换为你的实际列名。

这种方式可以将行转换为列。然而，这不是MySQL的特定功能，而是使用SQL语言的功能来实现的。具体实现可能会因数据库结构和需求而有所不同，你需要根据自己的情况进行适当的调整

使用工具Navica

Navicat是一个流行的数据库管理工具，提供了各种功能，用于连接、管理和维护不同类型的数据库，如MySQL、PostgreSQL、Oracle等。Navicat具有直观的用户界面和丰富的功能，使数据库管理变得更加简单和高效。

下面是一些Navicat的常见功能：

1. 连接数据库：

Navicat允许你连接到多个数据库，并提供了多种连接选项，包括本地或远程服务器、SSH隧道、SSL等。

2. 数据库管理：

你可以创建、修改和删除数据库、表、视图、索引、存储过程等。Navicat还提供了数据导入/导出功能，可以方便地将数据从一个数据库迁移到另一个数据库。

3. SQL开发：

Navicat提供了一个强大的SQL编辑器，支持代码自动完成、语法高亮、语法检查等功能。你可以方便地编写和执行SQL查询、脚本和存储过程。

4. 数据可视化：

Navicat提供了直观的用户界面和数据可视化工具，可以帮助你浏览和编辑数据。你可以使用数据网格、表格视图和图表来分析和操作数据。

5. 数据同步和备份：

Navicat支持数据同步和备份功能，可以帮助你在不同的数据库之间同步数据，并定期备份数据库以保护数据安全。

6. 安全管理：

Navicat提供了用户和权限管理功能，可以控制用户对数据库的访问权限，并管理数据库对象的安全性。

Navicat还有许多其他功能，如数据建模、任务计划、数据转换等。它是一个功能强大、易于使用的数据库管理工具，广泛用于开发人员、数据库管理员和数据分析师等角色。

MySQL面试题

一.面试题

创建模拟数据库

面试题讲解

知识点行转列

使用工具Navica

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像