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

热门文章

最新文章

相关课程

相关电子书

推荐镜像

探索云世界

热门

云计算

大数据

云原生

人工智能

数据库

开发与运维

活动广场

任务中心

训练营

直播

乘风者计划

下载

镜像站

技术资料

MySQL面试题

一.面试题

创建模拟数据库

面试题讲解

知识点行转列

使用工具Navica

热门文章

最新文章

相关课程

相关电子书

推荐镜像