mysql -- 经典面试题

已于 2022-06-23 23:36:39 修改
阅读量237 收藏
点赞数
分类专栏： 测试基础 文章标签： mysql sql
于 2022-06-21 17:18:26 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_25672165/article/details/125325770
版权

文章目录

1. 基础数据创建

-- 建库
create database if not exists school  
        default charset utf8 
        COLLATE utf8_general_ci;

-- 学生表
CREATE TABLE `student` (
        `s_no` VARCHAR(20) NOT NULL PRIMARY KEY,
        `s_name` VARCHAR(20) NOT NULL DEFAULT '',
        `s_birth` VARCHAR(20) NOT NULL DEFAULT '',
        `s_sex` VARCHAR(10) NOT NULL DEFAULT ''
);

-- 教师表
CREATE TABLE `teacher` (
        `t_no` VARCHAR(20) NOT NULL PRIMARY KEY,
        `t_name` VARCHAR(20) NOT NULL DEFAULT ''
);

-- 课程表
CREATE TABLE `course` (
        `c_no` VARCHAR(20) NOT NULL PRIMARY KEY,
        `c_name` VARCHAR(20) NOT NULL DEFAULT '',
        `t_no` varchar(20) NOT NULL
);


-- 分数表
CREATE TABLE `score` (
        `s_no` VARCHAR(20) NOT NULL,
        `c_no` VARCHAR(20) NOT NULL,
        `s_score` INT(3),
        constraint pk_sc PRIMARY KEY (`s_no`, `c_no`)
);

-- 插入数据
INSERT INTO student (s_no, s_name, s_birth, s_sex)
VALUES 
        ('s202201', '赵雷', '1990-01-01', '男'), 
        ('s202202', '钱电', '1990-12-21', '男'), 
        ('s202203', '孙风', '1990-05-20', '男'), 
        ('s202204', '李云', '1990-08-06', '男'), 
        ('s202205', '周梅', '1991-12-01', '女'), 
        ('s202206', '吴兰', '1992-03-01', '女'), 
        ('s202207', '郑竹', '1989-07-01', '女'), 
        ('s202208', '王菊', '1990-01-20', '女');

INSERT INTO teacher (t_no, t_name) 
VALUES 
        ('t202201', '张三'), 
        ('t202202', '李四'), 
        ('t202203', '王五');

INSERT INTO course (c_no, c_name, t_no)
VALUES 
        ('c202201', '语文',  't202202'), 
        ('c202202', '数学',  't202201'), 
        ('c202203', '英语',  't202203'),
        ('c202204', '物理',  't202202');

INSERT INTO score VALUES 
        ('s202201', 'c202201', 80), 
        ('s202201', 'c202202', 90), 
        ('s202201', 'c202203', 99), 
        ('s202202', 'c202201', 70), 
        ('s202202', 'c202202', 60), 
        ('s202202', 'c202203', 80), 
        ('s202203', 'c202201', 80), 
        ('s202203', 'c202202', 80), 
        ('s202203', 'c202203', 80), 
        ('s202204', 'c202201', 50), 
        ('s202204', 'c202202', 30), 
        ('s202204', 'c202203', 20), 
        ('s202205', 'c202201', 76), 
        ('s202205', 'c202202', 87), 
        ('s202206', 'c202201', 31)，
        ('s202207', 'c202204', 87), 
        ('s202208', 'c202204', 31);

2. 表数据复制

insert into teacher(t_name) select t_name from teacher;

3. 单表多行合并

  • 原始表
mysql> select * from score;

+---------+---------+---------+
| s_no    | c_no    | s_score |
+---------+---------+---------+
| s202201 | c202201 |      80 |
| s202201 | c202202 |      90 |
| s202201 | c202203 |      99 |
| s202202 | c202201 |      70 |
| s202202 | c202202 |      60 |
| s202202 | c202203 |      80 |
| s202203 | c202201 |      80 |
| s202203 | c202202 |      80 |
| s202203 | c202203 |      80 |
| s202204 | c202201 |      50 |
| s202204 | c202202 |      30 |
| s202204 | c202203 |      20 |
| s202205 | c202201 |      76 |
| s202205 | c202202 |      87 |
| s202206 | c202201 |      31 |
+---------+---------+---------+
  • 多行合并为一列
# 将同一学生的不同科目成绩展示为1行，用分号隔开
select s_no, 
group_concat(s_score Separator ';') as score 
from score 
group by s_no; 

+---------+----------+
| s_no    | score    |
+---------+----------+
| s202201 | 80;90;99 |
| s202202 | 70;60;80 |
| s202203 | 80;80;80 |
| s202204 | 50;30;20 |
| s202205 | 76;87    |
| s202206 | 31       |
+---------+----------+
  • 多行合并为多列
# 将同一学生的不同科目成绩展示为1行，并按照科目分列
select s_no, 
MAX(CASE c_no WHEN 'c202201' THEN s_score ELSE 0 END ) 语文,
MAX(CASE c_no WHEN 'c202202' THEN s_score ELSE 0 END ) 数学,
MAX(CASE c_no WHEN 'c202203' THEN s_score ELSE 0 END ) 英语 
from score group by s_no; 

+---------+--------+--------+--------+
| s_no    | 语文   | 数学   | 英语   |
+---------+--------+--------+--------+
| s202201 |     80 |     90 |     99 |
| s202202 |     70 |     60 |     80 |
| s202203 |     80 |     80 |     80 |
| s202204 |     50 |     30 |     20 |
| s202205 |     76 |     87 |      0 |
| s202206 |     31 |      0 |      0 |
+---------+--------+--------+--------+

4. Sql查询语句

  • 查询课程1⽐课程2成绩⾼的所有学⽣的学号和成绩
select a.s_no, a.s_score 语文, b.s_score 数学 from 
(select s_no, s_score from score where c_no ='c202201') a,
(select s_no, s_score from score where c_no ='c202202') b
where a.s_no =b.s_no 
and a.s_score >b.s_score;
  • 查询存在"c202201"课程的学生信息及其"c202201"、"c202201"课程的成绩
select a.* , b.s_score 语文分数,c.s_score 数学分数 from student a
left join score b on a.s_no = b.s_no and b.c_no = 'c202201'
left join score c on a.s_no = c.s_no and c.c_no = 'c202202'
where b.s_score is not null;
  • 查询平均分及格了的学生编号、姓名和平均分；
select a.s_no , a.s_name , cast(avg(b.s_score) as decimal(18,2)) avg_score
from student a , score b
where a. s_no = b.s_no
group by a. s_no
having avg(b.s_score) >= 60
order by a.s_no;
  • 查询存在成绩的学生信息和平均分
select b.s_no , b.s_name , avg(a.s_score)  avg_score
from score a left join student b
on a.s_no = b.s_no
group by a.s_no
order by a.s_no;
  • 查询姓名包含风的学生信息
select * from student where s_name REGEXP '风*';
select * from student where s_name like '%风%';
  • 查询所有同学的学号、姓名、选课数、总成绩；
select s.s_no,s.s_name,count(sc.c_no),sum(sc.s_score)
from Student s 
left join score sc 
on s.s_no= sc.s_no
group by s.s_no;
  • 查询没学过“张三”老师课的同学的学号、姓名
select s.s_no, s.s_name
from student s
where s_no not in 
(select distinct( sc.s_no) from score sc, course c, teacher t 
	where sc.c_no =c.c_no and t.t_no=c.t_no and t.t_name ='张三');
  • 查询没有学全所有课的同学的学号、姓名
select s.s_no, s.s_name
from student s, score sc
where s.s_no = sc.s_no
group by sc.s_no
having count(sc.c_no) < 
(select count(*) from course);
  • 查询即学了课程"c202202"，又学了课程"c202202"的学生
select s.s_no, s_name 
from student s, score sc 
where c_no = "c202201" and s.s_no = sc.s_no 
and exists     
    (select * from score sc2 
    where sc2.s_no =sc.s_no and sc2.c_no = "c202202");
  • 查询各科最高分、最低分
SELECT c_no AS "课程ID", MAX(s_score) AS "最高分", MIN(s_score) AS "最低分"
FROM score
GROUP BY c_no;
  • 按各科平均成绩从低到高和及格率的百分数从高到低顺序
SELECT sc.c_no AS "课程ID",c.c_name AS "课程名", AVG(sc.s_score) AS "平均成绩",
SUM(CASE WHEN sc.s_score > 60 THEN 1 ELSE 0 END)/COUNT(1)*100 AS "及格百分数"
FROM score sc, course c
WHERE sc.c_no=c.c_no
GROUP BY sc.c_no
ORDER BY AVG(sc.s_score) ASC,
SUM(CASE WHEN sc.s_score > 60 THEN 1 ELSE 0 END)/COUNT(1)*100 DESC;

+----------+-----------+--------------+-----------------+
| 课程ID   | 课程名    | 平均成绩     | 及格百分数      |
+----------+-----------+--------------+-----------------+
| c202204  | 物理      |      59.0000 |         50.0000 |
| c202201  | 语文      |      64.5000 |         66.6667 |
| c202202  | 数学      |      69.4000 |         60.0000 |
| c202203  | 英语      |      69.7500 |         75.0000 |
+----------+-----------+--------------+-----------------+
  • 查询所有学生的各科成绩、平均分、总分，并按照总分由高到低排序
  • IFNULL()： 判断第一个值是否为null，不是则返回；是则返回第二个

SELECT s.s_no AS "学号", s.s_name AS "姓名",
SUM(CASE c.c_name WHEN "语文" THEN sc.s_score ELSE 0 END) AS "语文",
SUM(CASE c.c_name WHEN "数学" THEN sc.s_score ELSE 0 END) AS "数学",
SUM(CASE c.c_name WHEN "英语" THEN sc.s_score ELSE 0 END) AS "英语",
SUM(CASE c.c_name WHEN "物理" THEN sc.s_score ELSE 0 END) AS "物理",
IFNULL(AVG(sc.s_score),0) AS "平均分",
IFNULL(SUM(sc.s_score),0) AS "总分"
FROM student s
LEFT OUTER JOIN score sc ON s.s_no=sc.s_no
LEFT OUTER JOIN course c ON sc.c_no=c.c_no
GROUP BY s.s_no, s.s_name
ORDER BY IFNULL(SUM(sc.s_score),0) DESC;

+---------+--------+--------+--------+--------+--------+-----------+--------+
| 学号    | 姓名   | 语文   | 数学   | 英语   | 物理   | 平均分    | 总分   |
+---------+--------+--------+--------+--------+--------+-----------+--------+
| s202201 | 赵雷   |     80 |     90 |     99 |      0 |   89.6667 |    269 |
| s202203 | 孙风   |     80 |     80 |     80 |      0 |   80.0000 |    240 |
| s202202 | 钱电   |     70 |     60 |     80 |      0 |   70.0000 |    210 |
| s202205 | 周梅   |     76 |     87 |      0 |      0 |   81.5000 |    163 |
| s202204 | 李云   |     50 |     30 |     20 |      0 |   33.3333 |    100 |
| s202207 | 郑竹   |      0 |      0 |      0 |     87 |   87.0000 |     87 |
| s202208 | 王菊   |      0 |      0 |      0 |     31 |   31.0000 |     31 |
| s202206 | 吴兰   |     31 |      0 |      0 |      0 |   31.0000 |     31 |
+---------+--------+--------+--------+--------+--------+-----------+--------+
  • 查询总分在200-250之间的学生的各科成绩、平均分、总分，并按照总分由高到低排序

SELECT s.s_no AS "学号", s.s_name AS "姓名",
SUM(CASE c.c_name WHEN "语文" THEN sc.s_score ELSE 0 END) AS "语文",
SUM(CASE c.c_name WHEN "数学" THEN sc.s_score ELSE 0 END) AS "数学",
SUM(CASE c.c_name WHEN "英语" THEN sc.s_score ELSE 0 END) AS "英语",
SUM(CASE c.c_name WHEN "物理" THEN sc.s_score ELSE 0 END) AS "物理",
IFNULL(AVG(sc.s_score),0) AS "平均分",
IFNULL(SUM(sc.s_score),0) AS "总分"
FROM student s
LEFT OUTER JOIN score sc ON s.s_no=sc.s_no
LEFT OUTER JOIN course c ON sc.c_no=c.c_no
GROUP BY s.s_no, s.s_name
HAVING IFNULL(SUM(sc.s_score),0) BETWEEN 200 AND 250
ORDER BY IFNULL(SUM(sc.s_score),0) DESC;

+---------+--------+--------+--------+--------+--------+-----------+--------+
| 学号    | 姓名   | 语文   | 数学   | 英语   | 物理   | 平均分    | 总分   |
+---------+--------+--------+--------+--------+--------+-----------+--------+
| s202203 | 孙风   |     80 |     80 |     80 |      0 |   80.0000 |    240 |
| s202202 | 钱电   |     70 |     60 |     80 |      0 |   70.0000 |    210 |
+---------+--------+--------+--------+--------+--------+-----------+--------+
  • 查询总分排名3、4的学生的各科成绩、平均分、总分，并按照总分由高到低排序

SELECT s.s_no AS "学号", s.s_name AS "姓名",
SUM(CASE c.c_name WHEN "语文" THEN sc.s_score ELSE 0 END) AS "语文",
SUM(CASE c.c_name WHEN "数学" THEN sc.s_score ELSE 0 END) AS "数学",
SUM(CASE c.c_name WHEN "英语" THEN sc.s_score ELSE 0 END) AS "英语",
SUM(CASE c.c_name WHEN "物理" THEN sc.s_score ELSE 0 END) AS "物理",
IFNULL(AVG(sc.s_score),0) AS "平均分",
IFNULL(SUM(sc.s_score),0) AS "总分"
FROM student s
LEFT OUTER JOIN score sc ON s.s_no=sc.s_no
LEFT OUTER JOIN course c ON sc.c_no=c.c_no
GROUP BY s.s_no, s.s_name
ORDER BY IFNULL(SUM(sc.s_score),0) DESC
limit 2, 2

+---------+--------+--------+--------+--------+--------+-----------+--------+
| 学号    | 姓名   | 语文   | 数学   | 英语   | 物理   | 平均分    | 总分   |
+---------+--------+--------+--------+--------+--------+-----------+--------+
| s202202 | 钱电   |     70 |     60 |     80 |      0 |   70.0000 |    210 |
| s202205 | 周梅   |     76 |     87 |      0 |      0 |   81.5000 |    163 |
+---------+--------+--------+--------+--------+--------+-----------+--------+
  • 查询学生平均成绩及其名次
set @xuhao=0;

select @xuhao:=@xuhao+1 as 序号, s.s_no 学号,ifnull(avg(sc.s_score),0) 平均成绩
from student s left join score sc on s.s_no = sc.s_no
group by s.s_no;
+--------+---------+--------------+
| 序号    | 学号    | 平均成绩       |
+--------+---------+--------------+
|      1 | s202201 |      89.6667 |
|      2 | s202202 |      70.0000 |
|      3 | s202203 |      80.0000 |
|      4 | s202204 |      33.3333 |
|      5 | s202205 |      81.5000 |
|      6 | s202206 |      31.0000 |
|      7 | s202207 |      87.0000 |
|      8 | s202208 |      31.0000 |
+--------+---------+--------------+
zyanwei2018
关注
专栏目录
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值