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

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 ='张三');