文章目录
- 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语句
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 |
+---------+--------+--------+--------+--------+--------+-----------+--------+