栏目分类:
子分类:
返回
名师互学网用户登录
快速导航关闭
当前搜索
当前分类
子分类
实用工具
热门搜索
名师互学网 > IT > 软件开发 > 后端开发 > Java

mysql -- 经典面试题

Java 更新时间: 发布时间: IT归档 最新发布 模块sitemap 名妆网 法律咨询 聚返吧 英语巴士网 伯小乐 网商动力

mysql -- 经典面试题

文章目录
  • 1. 基础数据创建
  • 2. 表数据复制
  • 3. 单表多行合并
  • 4. Sql语句

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 |
+---------+--------+--------+--------+--------+--------+-----------+--------+
转载请注明:文章转载自 www.mshxw.com
本文地址:https://www.mshxw.com/it/985018.html
我们一直用心在做
关于我们 文章归档 网站地图 联系我们

版权所有 (c)2021-2022 MSHXW.COM

ICP备案号:晋ICP备2021003244-6号