MySQL之查询语练习(1)

博客参考

请看我之前的博客

1. MySQL之多表查询
2. MySQL之SQL语句
3. MySQL之函数

数据准备

-- 建表 初始化表
-- 学生表
CREATE TABLE `Student`(
`s_id` VARCHAR(20),
`s_name` VARCHAR(20) NOT NULL DEFAULT '',
`s_birth` VARCHAR(20) NOT NULL DEFAULT '',
`s_sex` VARCHAR(10) NOT NULL DEFAULT '',
PRIMARY KEY(`s_id`)
);
-- 课程表
CREATE TABLE `Course`(
`c_id` VARCHAR(20),
`c_name` VARCHAR(20) NOT NULL DEFAULT '',
`t_id` VARCHAR(20) NOT NULL,
PRIMARY KEY(`c_id`)
);
-- 教师表
CREATE TABLE `Teacher`(
`t_id` VARCHAR(20),
`t_name` VARCHAR(20) NOT NULL DEFAULT '',
PRIMARY KEY(`t_id`)
);
-- 成绩表
CREATE TABLE `Score`(
`s_id` VARCHAR(20),
`c_id` VARCHAR(20),
`s_score` INT(3),
PRIMARY KEY(`s_id`,`c_id`)
);
-- 插入学生表测试数据
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
insert into Student values('08' , '王菊' , '1990-01-20' , '女');

-- 课程表测试数据
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');

-- 教师表测试数据
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');

-- 成绩表测试数据
insert into Score values('01' , '01' , 80);
insert into Score values('01' , '02' , 90);
insert into Score values('01' , '03' , 99);
insert into Score values('02' , '01' , 70);
insert into Score values('02' , '02' , 60);
insert into Score values('02' , '03' , 80);
insert into Score values('03' , '01' , 80);
insert into Score values('03' , '02' , 80);
insert into Score values('03' , '03' , 80);
insert into Score values('04' , '01' , 50);
insert into Score values('04' , '02' , 30);
insert into Score values('04' , '03' , 20);
insert into Score values('05' , '01' , 76);
insert into Score values('05' , '02' , 87);
insert into Score values('06' , '01' , 31);
insert into Score values('06' , '03' , 34);
insert into Score values('07' , '02' , 89);
insert into Score values('07' , '03' , 98);

习题1

-- 1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
-- 分两步 首先找到"01"课程比"02"课程成绩高的学生的学号
select s1.s_id from score s1, score s2 where s1.s_score > s2.s_score and s1.c_id = "01" and s2.c_id = "02" and s1.s_id = s2.s_id;
-- 第二步 使用列子查询 找到该学号的所有信息。
select * from student where s_id in(select s1.s_id from score s1, score s2 where s1.s_score > s2.s_score and s1.c_id = "01" and s2.c_id = "02" and s1.s_id = s2.s_id)

-- 2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
-- 分两步,第一步先找到"01"课程比"02"课程成绩低的学号
select s1.* from score s1, score s2 where s1.s_score < s2.s_score and s1.c_id = "01" and s2.c_id = "02" and s1.s_id = s2.s_id;
-- 将student,score,course连在一起进行查询，然后把上面得到的结果作为条件进行查询。
select student.*,course.c_name,score.s_score from student,score,course where student.s_id = score.s_id and score.c_id = course.c_id and score.s_id in (select s1.s_id from score s1, score s2 where s1.s_score < s2.s_score and s1.c_id = "01" and s2.c_id = "02" and s1.s_id = s2.s_id);


-- 3、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
-- 第一步 找到所有学号
select student.s_id from student;
-- 将学号作为过滤条件 进行查询
select stu.s_id "学生编号", stu.s_name "学生姓名", count(*) "选课总数",sum(sco.s_score) "所有课程的总成绩" from score sco,student stu where sco.s_id = stu.s_id GROUP BY sco.s_id;


-- 4、查询学过"张三"老师授课的同学的信息 
-- 第一步找到张三老师的id
select t_id from teacher where t_name = "张三";
-- 第二步三张表联合查询，把上述结果作为条件查询。
select student.* from student,score,course where student.s_id = score.s_id and score.c_id = course.c_id and course.t_id = (select t_id from teacher where t_name = "张三");

习题2

-- 练习1
-- 1.查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩(包括有成绩的和无成绩的)

-- 这里 用谁分组就用谁的属性，不然报错，
select stu.s_id,stu.s_name,avg(sco.s_score) "平均成绩" from student stu left join score sco on stu.s_id = sco.s_id group by stu.s_id having  avg(sco.s_score) <=60;

-- 2.查询"李"姓老师的数量
select count(*) from teacher t where t.t_name like "李%";

-- 3.查询没学过"张三"老师授课的同学的信息
select * from student stu left join score sco on sco.s_id = stu.s_id left join course c on c.c_id = sco.c_id 
left join teacher t on c.t_id = t.t_id where t.t_name = "张三";

-- 4.查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
SELECT * FROM student WHERE 
s_id IN (SELECT s_id FROM score WHERE c_id = '01')
AND   
s_id IN ( SELECT s_id FROM score WHERE c_id = '02');
-- 也可使用union 将两张表拼接在一起，

习题3

-- 1.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩(难)
-- 第一步先把平均成绩写出来，在和学生表连接
select stu.s_id,avg(sco.s_score) as sco_age from student stu left join score sco on sco.s_id = stu.s_id group by stu.s_id order by avg(sco.s_score) desc;
-- 方法一：使用子查询
select a.s_id,a.s_name,b.s_score,a.sco_age from 
(select stu.s_id,stu.s_name,avg(sco.s_score) as sco_age from student stu left join score sco on sco.s_id = stu.s_id group by stu.s_id order by avg(sco.s_score) desc) a 
left join score b on a.s_id = b.s_id;


-- 方法2 使用窗口函数
-- 1.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
-- 窗口函数 + 单次连接 ROUND(数据,保留几位小数)四舍五入
-- PARTITION BY 是窗口函数的核心组成部分，必须通过 OVER() 子句与聚合函数结合使用，
-- 类似GROUP BY stu.s_id，但不会折叠行（保留每个学生的每门课成绩）。
-- OVER：定义窗口函数的作用域​
-- 声明窗口函数的计算范围，必须搭配聚合函数（如AVG()）或排序函数（如ROW_NUMBER()）使用。
SELECT 
    stu.s_id, 
    stu.s_name, 
    sco.s_score AS 单科成绩,
    ROUND(AVG(sco.s_score) OVER (partition BY stu.s_id ORDER BY sco.s_score), 2) AS 平均成绩
FROM student stu
LEFT JOIN score sco ON stu.s_id = sco.s_id
ORDER BY 平均成绩 DESC, stu.s_id, sco.c_id;

select 
	stu.s_id,
	stu.s_name, 
	avg(sco.s_score) as sco_age 
from student stu 
left join score sco on sco.s_id = stu.s_id 
group by stu.s_id order by avg(sco.s_score) desc;

-- 2.查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
select a.s_id,a.s_name,b.s_score from student a left join score b on a.s_id = b.s_id ORDER BY b.s_score desc LIMIT 1,2;



-- 3.查询学生平均成绩及其名次(难)


-- 使用窗口函数
SELECT 
    a.s_id AS 学号,
    a.s_name AS 姓名,
    ROUND(AVG(b.s_score), 2) AS 平均成绩,
    DENSE_RANK() OVER (ORDER BY AVG(b.s_score) DESC) AS 名次
FROM 
    student a
LEFT JOIN 
    score b ON a.s_id = b.s_id
GROUP BY 
    a.s_id, a.s_name
ORDER BY 
    名次;


-- 4.查询各科成绩前三名的记录 (难)

-- 方法1：子查询
-- 第一步 先把两张score连接到一起 
SELECT 
		a.c_id,a.c_name,b.s_score,b2.s_score
FROM 
    course a
LEFT JOIN score b ON a.c_id = b.c_id
LEFT JOIN score b2 ON 
    b.c_id = b2.c_id      -- 同一课程
    AND b2.s_score > b.s_score;  -- 成绩高于当前行

-- ------------------------------------------------------------------------------
SELECT 
    a.c_id,
    a.c_name,
    b.s_score,
		COUNT(DISTINCT b2.s_score) + 1 AS rnk  -- 统计更高成绩的数量 → 排名
FROM 
    course a
LEFT JOIN score b ON a.c_id = b.c_id
LEFT JOIN score b2 ON 
    b.c_id = b2.c_id      -- 同一课程
    AND b2.s_score > b.s_score  -- 成绩高于当前行
GROUP BY 
    a.c_id, a.c_name, b.s_score
ORDER BY 
    a.c_name, b.s_score DESC;
		

-- ------------------------------------------------------------------------------
SELECT 
    a.c_id,
    a.c_name,
    b.s_score,
    COUNT(DISTINCT b2.s_score) + 1 AS rnk  -- 统计更高成绩的数量 → 排名
FROM 
    course a
LEFT JOIN score b ON a.c_id = b.c_id
LEFT JOIN score b2 ON 
    b.c_id = b2.c_id      -- 同一课程
    AND b2.s_score > b.s_score  -- 成绩高于当前行
GROUP BY 
    a.c_id, a.c_name, b.s_score
HAVING rnk <= 3
ORDER BY 
    a.c_name, b.s_score DESC;
		
-- 方法2 窗口函数
-- 先把各科成绩排序
select distinct tab.c_id,tab.c_name,tab.s_score,tab.rnk from 
(select a.c_id,a.c_name,b.s_score,
	DENSE_RANK() over (PARTITION  BY a.c_name ORDER BY b.s_score desc) as rnk
from course a left join score b on a.c_id = b.c_id) as tab 
WHERE tab.rnk <=3;

-- 5.查询本月过生日的学生
select * from student a where month(a.s_birth)= MONTH(now()) ;

-- 6.查询下周过生日的学生
-- 先来了解几个函数再说
-- weekday(now())  返回今天星期几  (0代表星期1  6代表星期天)
-- SELECT DAYOFWEEK(CURDATE()); 整数 1（星期日）到 7（星期六）。

SELECT 7-(WEEKDAY(now())+1) as 这周剩余天数;
-- date_add(当前日期 , interval number day) 
select date_add("2025-12-01", interval 1 day); -- 结果：2025-12-02

-- 下周周一
select date_add(CURDATE(), interval (7-(WEEKDAY(now())+1)+1) day);

-- 下周周日
select date_add(date_add(CURDATE(), interval (7-(WEEKDAY(now())+1)+1) day), interval 6 day);

-- 下面是数字
select date_format(now(),"%m%d")-5;

-- 下面的格式是"%m%d"代表是数字 1201 ，(1201-2 = 1199)
select date_format("2025-12-01","%m%d")-2; -- 1199


-- 下周一的日期数字
select date_format(date_add(CURDATE(), interval (7-(WEEKDAY(now())+1)+1) day),"%m%d");

-- 下周日的日期数字
select date_format(date_add(date_add(CURDATE(), interval (7-(WEEKDAY(now())+1)+1) day), interval 6 day),"%m%d");

-- 学生表的日期
select date_format(s.s_birth,"%m%d") from student s;

-- 最后结果
select * from student s where date_format(s.s_birth,"%m%d") >= date_format(date_add(CURDATE(), interval (7-(WEEKDAY(now())+1)+1) day),"%m%d")
and date_format(s.s_birth,"%m%d") <= date_format(date_add(date_add(CURDATE(), interval (7-(WEEKDAY(now())+1)+1) day), interval 6 day),"%m%d")

习题4

好多相同题型，我就选择性写了。

-- 1.按平均成绩从低到高 显示所有学生的“高等数学-1”、“高等数学-2”、“高等数学-3”三门的课程成绩，
-- 按如下形式显示： 学生ID,语文,数学,英语,有效课程数,有效平均分


-- 2.查询“高等数学-1”最大最小成绩

-- 3.查询未来3个月要过生日的学生

-- 4.根据学生出生日期，获取学生年龄

-- 5.按各科平均成绩从低到高和及格率的百分数从高到低顺序
-- 输出全部数据 验证结果是否正确
select 
	*
from course a 
left join score b on a.c_id = b.c_id;

-- 结果
select 
	a.c_name,
	AVG(b.s_score),
	SUM(case when b.s_score >= 60 then 1 else 0 end)/COUNT(*)
from course a 
left join score b on a.c_id = b.c_id
GROUP BY a.c_id
ORDER BY AVG(b.s_score) desc;

-- 6.查询本周考试的学生成绩。

建表语句和简单查询练习

1

show databases;
create database if not exists hzshop;
show databases;

use hzshop;
select database;
SELECT DATABASE();
show tables;
-- 创建商品表
create table goods (
	ID bigint(20) auto_increment not null primary key comment '商品ID',
	goods_name varchar(50) null comment '商品名称',
	goods_title varchar(100) null comment '商品标题',
	add_time datetime default '1970-01-01 00:00:00' not null comment '添加时间',
	goods_price decimal(10,2) default 0.00 not null comment '商品价格两位小数',
	goods_stock int(11) default 100 not null comment '商品库存',
	goods_state int(2) default 1 not null comment '库存状态,1待上架2在售'
) comment '商品表';

drop table if exists goods;

-- 创建商品类型表
create table goods_type(
	ID bigint(20) not null primary key auto_increment comment '商品类型ID',
	goods_category varchar(10) null comment '商品类型名称',
	category_logo varchar(255) default null null comment '类型图标'
) comment '商品类型表'

alter table goods add category_id bigint(20) comment '商品类型ID';

-- 插入商品分类数据
INSERT INTO goods_type (goods_category, category_logo) VALUES
('手机数码', 'mobile_logo.png'),
('电脑办公', 'computer_logo.png'),
('家用电器', 'appliance_logo.png'),
('服装鞋包', 'clothing_logo.png'),
('食品生鲜', 'food_logo.png');

-- 插入商品数据（注意category_id需匹配goods_type表的ID）
INSERT INTO goods (goods_name, goods_title, add_time, goods_price, goods_stock, goods_state, category_id) VALUES
('iPhone 15 Pro', '苹果旗舰5G手机', '2025-05-01 10:00:00', 8999.00, 200, 2, 1),
('华为MateBook X', '轻薄办公笔记本电脑', '2025-05-10 14:30:00', 6999.00, 150, 2, 2),
('戴森吸尘器V12', '智能无绳家用吸尘器', '2025-04-20 09:15:00', 4590.00, 80, 2, 3),
('男士纯棉T恤', '夏季透气休闲短袖', '2025-05-25 16:45:00', 129.00, 500, 1, 4),
('澳洲牛排套餐', '进口冷冻西冷牛排1kg', '2025-05-28 11:20:00', 198.00, 300, 2, 5),
('小米手环8', '心率监测运动手环', '2025-05-15 13:10:00', 299.00, 0, 1, 1);

2

-- 1.根据要求使用SQL创建数据库
-- 2.使用sql添加表中数据
create table Student(
	Sno varchar(20) not null PRIMARY key comment '学号',
	Sname varchar(20)  not null comment '学生姓名',
	Ssex varchar(20) not null comment '学生性别',
	Sbirthday datetime comment '学生出生年月',
	Class varchar(20) comment '学生所在班级'
) comment '学生表';

insert into Student (Sno, Sname, Ssex, Sbirthday, Class) values 
("108","曾华","男",'1977-09-01',"95033"),
("105","匡明","男",'1975-10-02',"95031"), 
("107","王丽","女",'1976-01-23',"95033"), 
("101","李军","男",'1976-02-20',"95033"), 
("109","王芳","女",'1975-02-10',"95031"), 
("103","陆君","男",'1974-06-03',"95031")

create table Course(
	Cno varchar(20) not null primary key comment '课程号',
	Cname varchar(20) not null comment '课程名称',
	-- 设置外键，当教师表的教工编号(Tno)更新时 
	-- 课程表中的教工编号(Tno) 应该同步更新。
	
	-- 当教师表中教工编号(Tno)删除时，
	-- 课程表中的教工编号(Tno)应该设置为空。
	Tno  varchar(20) null comment '教工编号'
) comment '课程表'; 

insert into Course (Cno, Cname, Tno) VALUES
("3-105","计算机导论","825"),
("3-245","操作系统","804"),
("6-166","数字电路","856"),
("9-888","高等数学","831")


create table Score (
	-- 当学生表中的学号字段更新或者删除时，
	-- 成绩表中的学号字段应该联级更新或者删除
	Sno varchar(20) not null comment '学号',
	-- 当课程表中Cno(课程号)字段删除时，
	-- 成绩表中的Cno(课程号)字段应该联级删除
	
	-- 当课程表中Cno(课程号)字段更新时，
	-- 成绩表中的Cno(课程号)字段应该联级更新，
	-- 并使用触发器把成绩表中的(Degree)成绩设置为NULL,
	-- 因为课程更新了，学生的成绩应该为空。
	Cno varchar(20) not null comment '课程号',
	Degree decimal(4,1) comment '成绩'
) comment '成绩表';

insert into Score (Sno, Cno, Degree) VALUES
("103","3-245",86),
("105","3-245",75),
("109","3-245",68),
("103","3-105",92),
("105","3-105",88),
("109","3-105",76),
("101","3-105",64),
("107","3-105",91),
("108","3-105",78),
("101","6-166",85),
("107","6-166",79),
("108","6-166",81)


create table Teacher (
	Tno varchar(20) not null primary key comment '教工编号',
	Tname varchar(20) not null comment '教工姓名',
	Tsex varchar(20) not null comment '教工性别',
	Tbirthday datetime comment '教工出生年月',
	Prof varchar(20) comment '职称',
	Depart varchar(20) not null comment '教工所在部门'
	
) comment '教师表';

insert into Teacher (Tno, Tname, Tsex, Tbirthday, Prof, Depart) VALUES
("804","李诚","男",'1958-12-02',"副教授","计算机系"),
("856","张旭","男",'1969-03-12',"副教授","计算机系"),
("825","王萍","女",'1972-05-05',"副教授","计算机系"),
("831","刘冰","女",'1977-08-14',"副教授","计算机系")



-- 3.将course表中高等数学修改为JAVA高级编程
update course set Cname="JAVA高级编程" where Cname = '高等数学'; 


-- 4.删除学生陆君的信息（学生信息及学生考试成绩）
select Sno from student where Sname = "陆君"
delete from Score where Sno = (select Sno from student where Sname = "陆君"); 
delete from student where Sname = "陆君";


-- 5.查询95031班级所有男生信息，返回学号，姓名，性别，出生日期，班级编号
select Sno "学号", Sname "姓名", Ssex "性别", Sbirthday "出生日期", class "班级编号" 
from student where Ssex = "男" and Class = "95031";

-- 6.查询Score表中成绩为85，86或88的记录
select * from Score where Degree = 85 or Degree = 86 or Degree = 88;


-- 7.查询Score表中成绩为85，86或88的记录，返回学号，课程名称（注意不是课程编号），成绩
-- select * from Score where Degree = 85 or Degree = 86 or Degree = 88;
select s.cno "学号",c.Cname "课程名称"  from Course c,(select * from Score where Degree = 85 or Degree = 86 or Degree = 88) s where s.Cno = c.Cno;