SQL练习题

🌈1.1 建表

🚀🚀🚀 学生表

create table student(
no VARCHAR(20) primary key COMMENT '学号',
name VARCHAR(20) NOT NULL COMMENT '姓名',
sex char(1) not null COMMENT '姓名',
birthday DATE COMMENT '生日',
class VARCHAR(20) COMMENT '班级'
) CHARSET=utf8mb4 comment '学生表'

🚀🚀🚀 教师表

CREATE TABLE teacher (
no VARCHAR(20) PRIMARY KEY COMMENT '教师编号',
name VARCHAR(20) NOT NULL COMMENT '姓名',
sex VARCHAR(10) NOT NULL COMMENT '性别',
birthday DATE COMMENT '出生日期',
profession VARCHAR(20) NOT NULL COMMENT '职称',
department VARCHAR(20) NOT NULL COMMENT '部门'
) charset=utf8mb4 COMMENT '教师表';

🚀🚀🚀 课程表

CREATE TABLE course (
no VARCHAR(20) PRIMARY KEY,
name VARCHAR(20) NOT NULL,
t_no VARCHAR(20) NOT NULL
);

🚀🚀🚀 成绩表

CREATE TABLE score (
s_no VARCHAR(20) NOT NULL,
c_no VARCHAR(20) NOT NULL,
degree DECIMAL
);

🌈1.2 插入数据

-- 添加学生表数据
INSERT INTO student VALUES('101', '曾华', '男', '1977-09-01', '95033');
INSERT INTO student VALUES('102', '匡明', '男', '1975-10-02', '95031');
INSERT INTO student VALUES('103', '王丽', '女', '1976-01-23', '95033');
INSERT INTO student VALUES('104', '李军', '男', '1976-02-20', '95033');
INSERT INTO student VALUES('105', '王芳', '女', '1975-02-10', '95031');
INSERT INTO student VALUES('106', '陆军', '男', '1974-06-03', '95031');
INSERT INTO student VALUES('107', '王飘飘', '男', '1976-02-20', '95033');
INSERT INTO student VALUES('108', '张全蛋', '男', '1975-02-10', '95031');
INSERT INTO student VALUES('109', '赵铁柱', '男', '1974-06-03', '95031');
-- 添加教师表数据
INSERT INTO teacher VALUES('804', '李诚', '男', '1958-12-02', '副教授', '计算机系');
INSERT INTO teacher VALUES('856', '张旭', '男', '1969-03-12', '讲师', '电子工程系');
INSERT INTO teacher VALUES('825', '王萍', '女', '1972-05-05', '助教', '计算机系');
INSERT INTO teacher VALUES('831', '刘冰', '女', '1977-08-14', '助教', '电子工程系');
-- 添加课程表数据
INSERT INTO course VALUES('3-105', '计算机导论', '825');
INSERT INTO course VALUES('3-245', '操作系统', '804');
INSERT INTO course VALUES('6-166', '数字电路', '856');
INSERT INTO course VALUES('9-888', '高等数学', '831');
-- 添加添加成绩表数据
INSERT INTO score VALUES('103', '3-105', '92');
INSERT INTO score VALUES('103', '3-245', '86');
INSERT INTO score VALUES('103', '6-166', '85');
INSERT INTO score VALUES('105', '3-105', '88');
INSERT INTO score VALUES('105', '3-245', '75');
INSERT INTO score VALUES('105', '6-166', '79');
INSERT INTO score VALUES('109', '3-105', '76');
INSERT INTO score VALUES('109', '3-245', '68');
INSERT INTO score VALUES('109', '6-166', '81');

🐴 2.基础查询

🚀 1.查询 student 表的所有行

select * from student

🚀 2.查询 student 表中的 name、sex 和 class 字段的所有行

select name,sex,class from student;

🚀 3.查询 teacher 表中不重复的 department 列

select DISTINCT department from teacher;

🚀 4.查询 score 表中成绩在 50-80 之间的所有行

select * from score where degree BETWEEN 50 and 80;
select * from score where degree >=50 and degree <=80;

🚀 5.查询 score 表中成绩为 85, 86 或 88 的行

select * from score where degree in (85,86,88);
select * from score where degree =85 or degree=86 or degree =88;

🚀 6.查询 student 表中 ‘95033’ 班或性别为 ‘女’ 的所有行

select * from student where class=95033 or sex =’女’;

🚀 7.以 class 降序的方式查询 student 表的所有行

select * from student order by class desc

🚀 8.查询 “95031” 班的学生人数

select * from student where class=95031

🚀 9.查询 score 表中的最高分的学生学号和课程编号

select s_no,c_no from score where degree = (select max(degree) from score)；
select s_no,c_no from score order by degree desc LIMIT 1;

🐴 3.分组计算

🚀 1. 查询每门课的成绩平均成绩

select c_no,avg(degree) as avg from score group by c_no

🚀 2. 查询 score 表中至少有 2 名学生选修，并以 3 开头的课程的平均分数

select c_no,avg(degree) avg from score group by c_no
having count(c_no) >=2
and c_no like ‘3%’

select c_no,avg(degree) avg from score where c_no like ‘3%’
group by c_no having count(*) >2

🚀 3. 查询student表中至少有3名男生的class

select class from student where sex = ‘男’
GROUP BY class
having count(*) >3

🐴 4.多表关联

🚀 1.查询所有学生的 name，以及该学生在 score 表中对应的 c_no 和 degree

select b.c_no,b.degree from student a,score b
where a.no = b.s_no

🚀 2.查询所有任课 ( 在 course 表里有课程 ) 教师的 name 和 department

select m.name,m.department from teacher m,course n
where m.no = n.t_no

🚀 3.查询所有学生的 name 、课程名 ( course 表中的 name ) 和 degree

select a.name,b.name as kecheng,c.degree from student a,course b,score c
where a.no = c.s_no
and b.no = c.c_no

🚀 4.查询所有学生的 no 、课程名称 ( course 表中的 name ) 和成绩 ( score 表中的 degree ) 列

select a.no,b.name as kecheng,c.degree from student a,course b,score c
where a.no = c.s_no
and b.no = c.c_no

🐴 5.子查询

🚀 1.查询95031班的学生每门课程的平均成绩

select c_no,avg(degree) avg from score
where s_no in (select no from student where class=95031)
group by c_no

🚀 2.查询所有成绩高于 109 号同学的 3-105 课程成绩记录

select * from score where degree > (
select degree from score where c_no = ‘3-105’ and s_no =’109’)

🚀 3.查询 “计算机系” 课程的成绩表

select * from score where c_no in (
select no from course where t_no in (
select no from teacher where department =’计算机系’
)
)

🚀 4.查询某选修课程多于 5 个同学的教师姓名

select name from teacher where no in
(
select t_no from course where no in (
select c_no from score group by c_no HAVING count(*)>5
)

🐴 6.ANY/ALL用法

🚀 1.查询课程3-105且成绩至少高3-245的score信息，成绩降序

SELECT * FROM score
WHERE c_no = ‘3-105’
AND degree >
ANY ( SELECT degree FROM score WHERE c_no = ‘3-245’ ) order by degree desc

any：表示至少一个，子查询的最小值

🚀 2.查询课程3-105且成绩高于3-345的score信息

SELECT * FROM score
WHERE c_no = ‘3-105’
AND degree > ALL ( SELECT degree FROM score WHERE c_no = ‘3-245’ )

ALL:所有值，子查询最大值

🐴 7.自连接

🚀 查询某课程成绩比该课程平均成绩低的score信息

select * from score a where a.degree <
(
select avg(degree) from score b where a.c_no = b.c_no
)

🐴 8.自连接

🚀 1 查询studnet表中年龄最大的和年龄最小

select max(birthday),min(birthday) from student

🚀 2 查询student表中每个学生的姓名和年龄

select name,year(now())-YEAR(birthday) as age from student

🚀 3 查询最高分同学的成绩

select * from score where
degree = (select max(degree) from score)

🚀 4 时间函数

select DAYOFWEEK(now())
select DAYOFYEAR(now()); ##返回当年第多少天
select HOUR(now()); ##返回时间本周第几天

🐴 9.排名统计

注：开窗函数只在mysql8.0以上才有
🚀 1.scores_tb根据成绩从高到低排序，row_number

select a.xuehao,a.score,
row_number() over(order by a.score desc) as row_id
from scores_tb a

🚀 2.scores_tb根据成绩从高到低排序，dense_rank无间隔

select a.xuehao,a.score,
row_number() over(order by a.score desc) as row_id
from scores_tb a

🚀 3.scores_tb根据成绩从高到低排序，RANK有间隔

select a.xuehao,a.score,
RANK() over(order by a.score desc) as row_id
from scores_tb a