MySQL练习题——课后一练

最新推荐文章于 2024-01-19 20:22:37 发布
最新推荐文章于 2024-01-19 20:22:37 发布
阅读量450 收藏 1
点赞数
分类专栏： 工具 文章标签： mysql sql sql练习题
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/u013541707/article/details/109210324
版权
学习目标：

很久没有练习写sql了，现在根据题库练习几题，并以后随时看看，知道sql的一些常用语法，特殊技巧

学习内容：
以上面的表为例，练习sql题目和答案如下： 
CREATE TABLE class (
cid INT not null,
caption VARCHAR(11) NOT null
);

CREATE TABLE student(
	sid INT NOT NULL,
	sname VARCHAR(11) NOT null,
	gender char(1) DEFAULT '男' NOT NULL,
	class_id INT,
	PRIMARY KEY(sid)

);

create table teacher(
tid int not null,
tname VARCHAR(11) not null,
PRIMARY key(tid)
);

CREATE TABLE course(
	cid INT NOT NULL,
	cname VARCHAR(11) NOT null,
	teacher_id int NOT NULL,
	PRIMARY KEY(cid)
);

CREATE TABLE score(



	sid INT NOT NULL,
	student_id INT NOT null,
	course_id int NOT NULL,
	number INT not null,
	PRIMARY KEY(sid)
);
INSERT INTO `ins_cashier`.`score`(`sid`, `student_id`, `course_id`, `number`) VALUES (1, 1, 1, 60);
INSERT INTO `ins_cashier`.`score`(`sid`, `student_id`, `course_id`, `number`) VALUES (2, 1, 2, 59);
INSERT INTO `ins_cashier`.`score`(`sid`, `student_id`, `course_id`, `number`) VALUES (3, 2, 1, 98);
INSERT INTO `ins_cashier`.`score`(`sid`, `student_id`, `course_id`, `number`) VALUES (4, 2, 2, 100);
INSERT INTO `ins_cashier`.`score`(`sid`, `student_id`, `course_id`, `number`) VALUES (5, 3, 1, 43);
INSERT INTO `ins_cashier`.`score`(`sid`, `student_id`, `course_id`, `number`) VALUES (6, 3, 2, 99);
INSERT INTO `ins_cashier`.`score`(`sid`, `student_id`, `course_id`, `number`) VALUES (7, 3, 3, 65);
INSERT INTO `ins_cashier`.`score`(`sid`, `student_id`, `course_id`, `number`) VALUES (8, 4, 3, 46);
INSERT INTO `ins_cashier`.`score`(`sid`, `student_id`, `course_id`, `number`) VALUES (9, 4, 4, 89);
INSERT INTO `ins_cashier`.`score`(`sid`, `student_id`, `course_id`, `number`) VALUES (10, 5, 4, 54);

-- https://www.cnblogs.com/wupeiqi/articles/5729934.html

-- 查询“1”课程比“2”课程成绩高的所有学生的学号；
SELECT a.student_id
FROM (
	SELECT student_id, number
	FROM score
	WHERE course_id = 1
) a, (
		SELECT student_id, number
		FROM score
		WHERE course_id = 2
	) b
WHERE a.student_id = b.student_id
	AND a.number < b.number;


-- 查询平均成绩大于60分的同学的学号和平均成绩；
SELECT student_id, avg(number) AS a
FROM score
GROUP BY student_id
HAVING a > 60;




-- 连表查询
select * FROM course c, teacher t WHERE c.teacher_id = t.tid;
select * FROM course c LEFT JOIN teacher t on c.teacher_id = t.tid;

-- 查询所有同学的学号、姓名、选课数、总成绩；

SELECT a.student_id, a.countNum, a.sumNum, b.sname
FROM (
	SELECT student_id, count(course_id) AS countNum, SUM(number) AS sumNum
	FROM score
	GROUP BY student_id
) a
	LEFT JOIN student b ON a.student_id = b.sid;



-- 查询姓“李”的老师的个数；
select count(tname) FROM teacher WHERE tname like "李%";

-- 查询没学过“苍空”老师课的同学的学号、姓名；

SELECT s.student_id, st.sname
FROM score s, student st
WHERE st.sid = s.student_id
	AND s.student_id NOT IN (
		SELECT DISTINCT s.student_id
		FROM score s
		WHERE s.course_id IN (
			SELECT c.cid
			FROM teacher t, course c
			WHERE t.tid = c.teacher_id
				AND tname = '苍空'
		)
	)
GROUP BY s.student_id



-- 查询学过编号“1”课程并且也学过编号“3”课程的同学的学号、姓名；
SELECT a.sid, a.sname
FROM student a
	INNER JOIN (
		SELECT s.student_id
		FROM (
			SELECT *
			FROM score
			WHERE course_id IN (1, 3)
		) s
		GROUP BY s.student_id
		HAVING count(s.student_id) = 2
	) b
	ON b.student_id = a.sid;



-- 查询学过“波多”老师所教的所有课的同学的学号、姓名；
SELECT s.sid, s.sname
FROM student s
	 JOIN (
		SELECT a.student_id
		FROM score a
		WHERE a.course_id IN (
			SELECT c.cid
			FROM teacher t, course c
			WHERE c.teacher_id = t.tid
				AND t.tname = '波多'
		)
		GROUP BY a.student_id
		HAVING COUNT(a.student_id) = (
			SELECT count(c.cid)
			FROM teacher t, course c
			WHERE c.teacher_id = t.tid
				AND t.tname = '波多'
		)
	) c
	ON c.student_id = s.sid;



-- 9、查询课程编号“2”的成绩比课程编号“1”课程低的所有同学的学号、姓名；
SELECT st.sid, st.sname
FROM (
	SELECT a.student_id
	FROM (
		SELECT s.student_id, s.number
		FROM score s
		WHERE s.course_id = 1
	) a, (
			SELECT s.student_id, s.number
			FROM score s
			WHERE s.course_id = 2
		) b
	WHERE a.student_id = b.student_id
		AND a.number > b.number
) b, student st
WHERE st.sid = b.student_id;



-- 查询有课程成绩小于60分的同学的学号、姓名；
SELECT st.sid, st.sname
FROM (
	SELECT sc.student_id
	FROM score sc
	GROUP BY sc.student_id
	HAVING MIN(sc.number) < 60
) scc, student st
WHERE st.sid = scc.student_id;




-- 查询没有学全所有课的同学的学号、姓名；
SELECT st.sid, st.sname
FROM (
	SELECT sc.student_id
	FROM score sc
	GROUP BY sc.student_id
	HAVING count(sc.student_id) = (
		SELECT count(cid)
		FROM course
	)
) a, student st
WHERE st.sid = a.student_id;




-- 查询至少有一门课与学号为“1”的同学所学相同的同学的学号和姓名；
SELECT st.sid, st.sname
FROM student st, (
		SELECT DISTINCT s.student_id
		FROM score s
		WHERE s.course_id IN (
			SELECT sc.course_id
			FROM score sc
			WHERE sc.student_id = 1
		)
	) a
WHERE st.sid = a.student_id;




-- 查询至少学过学号为“1”同学所选课程中任意一门课的其他同学学号和姓名；
SELECT st.sid, st.sname
FROM student st, (
		SELECT DISTINCT sc2.student_id
		FROM score sc2
		WHERE sc2.student_id != 1
			AND sc2.course_id IN (
				SELECT sc.course_id
				FROM score sc
				WHERE sc.student_id = 1
			)
	) sc3
WHERE sc3.student_id = st.sid




-- 查询和“2”号的同学学习的课程完全相同的其他同学学号和姓名；
SELECT st4.sid, st4.sname
FROM student st4, (
		SELECT sc2.student_id
		FROM score sc2
		WHERE sc2.course_id IN (
				SELECT sc.course_id
				FROM score sc
				WHERE sc.student_id = 2
			)
			AND sc2.student_id != 2
		GROUP BY sc2.student_id
		HAVING count(sc2.student_id) = (
			SELECT count(sc.course_id)
			FROM score sc
			WHERE sc.student_id = 2
		)
	) sc5
WHERE st4.sid = sc5.student_id;



-- 15、删除学习“苍空”老师课的SC表记录

DELETE FROM score
WHERE course_id IN (
		SELECT co.cid
		FROM teacher te, course co
		WHERE te.tid = co.teacher_id AND te.tname = '苍空'
	);


-- 16、向SC表中插入一些记录，这些记录要求符合以下条件：①没有上过编号“2”课程的同学学号；②插入“2”号课程的平均成绩；

INSERT INTO score (student_id, course_id, number)
SELECT a.student_id, 2, b.avgNum
FROM (
	SELECT DISTINCT sc4.student_id
	FROM score sc4
	WHERE sc4.student_id NOT IN (
		SELECT sc3.student_id
		FROM score sc3
		WHERE sc3.course_id = 2
	)
) a, (
		SELECT avg(number) AS avgNum
		FROM (
			SELECT *
			FROM score sc
			WHERE sc.course_id = 2
		) sc2
		GROUP BY sc2.course_id
	) b




-- 按平均成绩从低到高显示所有学生的“生物”、“体育”、“物理”三门的课程成绩，按如下形式显示： 学生ID,语文,数学,英语,有效课程数,有效平均分；

SELECT sc.student_id
	, SUM(IF(`course_id` = 1, number, NULL)) AS 生物
	, SUM(IF(`course_id` = 2, number, NULL)) AS 体育
	, SUM(IF(`course_id` = 3, number, NULL)) AS 物理
	, COUNT(course_id) AS 有效课程
	, avg(number) AS 平均分
FROM (
	SELECT *
	FROM score
	WHERE course_id IN (1, 2, 3)
) sc
GROUP BY sc.student_id


SELECT student_id,
SUM(CASE `course_id` WHEN (SELECT cid FROM course WHERE cname = "生物") THEN number ELSE null END) as '生物',
SUM(CASE `course_id` WHEN (SELECT cid FROM course WHERE cname = "体育") THEN number ELSE null END) as '体育',
SUM(CASE `course_id` WHEN (SELECT cid FROM course WHERE cname = "物理") THEN number ELSE null END) as '物理'
FROM score 
GROUP BY student_id





-- 查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；
SELECT sc.course_id AS 课程ID, MAX(sc.number) AS 最高分, MIN(sc.number) AS 最低分
FROM score sc
GROUP BY sc.course_id




-- 按各科平均成绩从低到高和及格率的百分数从高到低顺序；

SELECT sc.course_id, avg(sc.number) AS avgNum
	, SUM(IF(number >= 60, 1, 0)) / count(1) AS pass
FROM score sc
GROUP BY sc.course_id
ORDER BY avgNum ASC, pass DESC


select sc.course_id,SUM( IF(number>=60,1,0) ) ,SUM( IF(number<60,1,0) ),count(1) FROM score sc GROUP BY sc.course_id;




-- 课程平均分从高到低显示（现实任课老师）；

SELECT a.course_id, a.avgNum, t.tname
FROM (
	SELECT sc.course_id, AVG(sc.number) AS avgNum
	FROM score sc
	GROUP BY sc.course_id
	ORDER BY avgNum DESC
) a, course c, teacher t
WHERE a.course_id = c.cid
	AND c.teacher_id = t.tid;
	
	
-- 查询各科成绩前三名的记录:(不考虑成绩并列情况) 

SELECT s1.* FROM score s1 WHERE
(
SELECT COUNT(1) FROM score s2 WHERE
s1.course_id=s2.course_id AND s1.number<= s2.number
)<=3
ORDER BY s1.course_id,s1.number DESC;



SELECT a.student_id , a.course_id ,a.number  FROM (
	SELECT  s1.student_id, s1.course_id, s1.number, s2.course_id AS course_id2
		, s2.number AS number2
	FROM score s1, score s2
	WHERE s1.course_id = s2.course_id
		AND s1.number <= s2.number
	) a
GROUP BY 	a.course_id ,a.number  HAVING count(1) <=3 ORDER BY a.course_id, a.number DESC





-- 查询每门课程被选修的学生数；
SELECT count(sc.student_id) FROM score as sc  GROUP BY sc.course_id;



-- 查询出只选修了一门课程的全部学生的学号和姓名；
SELECT s.sid, s.sname
FROM student s, (
		SELECT sc.student_id, count(sc.student_id) AS countNum
		FROM score sc
		GROUP BY sc.student_id
		HAVING countNum = 1
	) a
WHERE s.sid = a.student_id




-- 查询男生、女生的人数；
SELECT st.gender, COUNT(1)
FROM student st
GROUP BY st.gender;



-- 询姓“陈”的学生名单
SELECT * FROM student WHERE sname like "陈%";




-- 查询同名同姓学生名单，并统计同名人数；

SELECT st1.sid, st1.sname, st1.gender, count(1) AS countNum
FROM student st1, student st2
WHERE st1.sname = st2.sname
GROUP BY st1.sid
HAVING countNum >= 2





-- 查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列；
SELECT sc.course_id, avg(sc.number) AS avgNum
FROM score sc
GROUP BY sc.course_id
ORDER BY avgNum ASC, sc.course_id DESC




-- 查询平均成绩大于60的所有学生的学号、姓名和平均成绩；

SELECT a.student_id, st.sname, a.avgNum
FROM student st, (
		SELECT sc.student_id, avg(sc.number) AS avgNum
		FROM score sc
		GROUP BY sc.student_id
		HAVING avgNum > 60
	) a
WHERE st.sid = a.student_id;





-- 查询课程名称为“生物”，且分数低于60的学生姓名和分数；
SELECT st.sname, sc.student_id, sc.number
FROM score sc, course co, student st
WHERE co.cname = '生物'
	AND sc.course_id = co.cid
	AND st.sid = sc.student_id
	AND sc.number < 60



-- 查询课程编号为001且课程成绩在80分以上的学生的学号和姓名； 
SELECT sc.student_id,st.sname FROM score sc,student st WHERE st.sid  = sc.student_id and sc.course_id = 1 and sc.number>=80 




-- 求选了课程的学生人数 
SELECT count(DISTINCT(student_id)) FROM score 




-- 查询选修“苍空”老师所授课程的学生中，成绩最高的学生姓名及其成绩；
SELECT st.sname, sc.student_id, sc.course_id, sc.number
FROM score sc, student st
WHERE st.sid = sc.student_id
	AND sc.course_id IN (
		SELECT co.cid
		FROM teacher te, course co
		WHERE te.tid = co.teacher_id
			AND te.tname = '苍空'
	)
ORDER BY sc.number DESC
LIMIT 0, 1



-- 33、查询各个课程及相应的选修人数；
SELECT sc.course_id ,count(1) countNum FROM score sc GROUP BY sc.course_id



-- 34、查询不同课程但成绩相同的学生的学号、课程号、学生成绩；




-- 查询每门课程成绩最好的前两名；

SELECT s1.* FROM score s1 WHERE
(
SELECT COUNT(1) FROM score s2 WHERE
s1.course_id=s2.course_id AND s1.number<= s2.number
)<=3
ORDER BY s1.course_id,s1.number DESC;


SELECT a.student_id , a.course_id ,a.number  FROM (
	SELECT  s1.student_id, s1.course_id, s1.number, s2.course_id AS course_id2
		, s2.number AS number2
	FROM score s1, score s2
	WHERE s1.course_id = s2.course_id
		AND s1.number <= s2.number
	) a
GROUP BY 	a.course_id ,a.number  HAVING count(1) <=2 ORDER BY a.course_id, a.number DESC




-- 检索至少选修两门课程的学生学号

SELECT student_id FROM score GROUP BY student_id HAVING  COUNT(student_id)>=2



-- 查询全部学生都选修的课程的课程号和课程名；

SELECT sc.course_id, count(sc.student_id) AS countNum
FROM score sc
GROUP BY sc.course_id
HAVING countNum = (
	SELECT COUNT(1)
	FROM student
)


-- 查询没学过“苍空”老师讲授的任一门课程的学生姓名；

SELECT *
FROM student st
WHERE st.sid NOT IN (
	SELECT DISTINCT sc.student_id
	FROM score sc
	WHERE sc.course_id IN (
		SELECT c.cid
		FROM teacher t, course c
		WHERE t.tid = c.teacher_id
			AND t.tname = '苍空'
	)
)

-- 查询两门以上不及格课程的同学的学号及其平均成绩

SELECT sc.student_id
	, SUM(IF(number < 60, 1, 0)) AS fail
	, avg(number) AS avgNum
FROM score sc
GROUP BY sc.student_id
HAVING fail >= 2;



-- 检索“1”课程分数小于60，按分数降序排列的同学学号；

SELECT sc.student_id
FROM score sc
WHERE sc.course_id = 1
	AND sc.number < 60
ORDER BY sc.number DESC


-- 删除“8”同学的“1”课程的成绩；
DELETE FROM score WHERE student_id = 8 and course_id = 1;

这些sql，时不时的练练手，还是有用的。

【完】

正在前往BAT的路上修行

可乐多点冰
关注
专栏目录
课后习题MySQL练习
qq_43108668的博客
09-03 2244
创建表的操作 CREATE TABLE student( id INT, NAME VARCHAR(20), chinese FLOAT, english FLOAT, math FLOAT ); 用创建的表 USE student; 插入数据 INSERT INTO student(id,NAME,chinese,english,math) VALUES(1,’张小...
mysql——连接查询/多表查询
小顽固的博客
03-11 764
进阶6：连接查询/多表查询 SELECT * FROM beauty; SELECT * FROM boys;
MySQL课后练习题
04-16
针对于MySQL增删改查的熟练过程，练习子查询，对关键字的查询等。
MySQL课后作业、MySQL查询语句练习45题、MySQL50道题目练习
Liu_wen_wen的博客
07-18 569
1.MySQL课后作业0718第三章 2.MySQL查询语句练习45题 3.MySQL 50道题目练习
MySQL必学！！！（练习题50道精选）
weixin_52263599的博客
01-19 3943
数据表介绍–1.学生表--SId 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别–2.课程表--CId 课程编号,Cname 课程名称,TId 教师编号–3.教师表--TId 教师编号,Tname 教师姓名–4.成绩表--SId 学生编号,CId 课程编号,score 分数。
MYSQL练习一答案
南州博客
07-23 266
answer开头表为对应题号答案形成的数据表。
【MySQL作业】MySQL函数——美和易思系统信息函数和加密函数应用习题
01-21
MySQL 系统信息函数 version() 用于返回当前 MySQL 的版本号，“show processlist” 用于显示 MySQL 运行时相关线程的运行状态，这些信息包括连接数、连接状态等，通过这些信息可以帮助使用者识别出有问题的查询语句...
java web开发实战经典java+jsp+mysql实现课后习题
09-03
本项目“java web开发实战经典——java+jsp+mysql实现课后习题”是基于第七章关于Java Bean的课后练习，它旨在通过实际操作来巩固和加深对Java Bean的理解。 首先，Java Bean必须是公共类（public class），并且不...
第二版-系统架构设计师-课后习题-无答案练习册
最新发布
04-12
通过对《第二版-系统架构设计师-课后习题-无答案练习册》的内容分析，我们可以看到系统架构设计不仅涉及理论知识的学习，还需要通过实践不断积累经验。本书通过一系列习题帮助读者巩固所学知识，提升解决实际问题的...
第九章课后习题1
08-08
第九章的课后习题涵盖了MySQL数据库中的多个关键概念，包括程序设计中的常量、变量、控制流程语句，以及数据库管理中的事务处理、锁机制、存储过程和游标操作。以下是这些知识点的详细说明： 1. **常量**：在程序...
MySQL数据库练习集1
Sakurapaid的博客
09-18 1847
基础练手，适合新手，比如当时的我
MySQL课后习题
continueLR的博客
10-25 1167
创建四张表 分别存储 学生信息 课程信息 分数 讲师信息表 存储相应数据 student学生信息表 course课程信息表 scores分数表 teacher讲师表 1、 查询Student表中的所有记录的Sname、Ssex和Class列。 select sname.ssex.class from student; 查询结果： 2、 查询教师所有的单位即不重复的Depart列。 select distinct Depart from Teacher; ...
MySQL数据库学习（二） MySQL数据库课后练习(1) DDL与DML练习
qq_41537102的博客
05-31 4151
MySQL数据库课后练习1一.第1题二.第2题 一.第1题 案例： 1、创建数据库day01_test01_library 2、创建表格books 字段名字段说明数据类型允许为空唯一 b_id书编号int(11)否是b_name书名varchar（50）否否authors作者varchar(100)否否price价格float否否pubdate出版日期year否否note说明varchar(100)是否num库存int(11)否否 3、向books表中插入记录 1） 指定所有字段名称插入第一条记录 2
CREATE TABLE常用命令
ucan23
11-18 1294
CREATE TABLE students( stuID INTEGER     NOT NULL , stuname char(30)     not null, sex     int     NOT NULL ); CREATE TABLE students( stuID INTEGER     NOT NULL , stuname char(30)     not null,
Mysql 练习题 及 答案
热门推荐
天道酬勤的博客
10-21 4万+
 --1.学生表Student(S,Sname,Sage,Ssex) --S 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别--2.课程表 Course(C,Cname,T) --C --课程编号,Cname 课程名称,T 教师编号--3.教师表 Teacher(T,Tname) --T 教师编号,Tname 教师姓名--4.成绩表 SC(S,C,score) --S 学生...
第2章 课后作业
华少的博客
08-02 718
第3题 SELECT bName AS '图书名称',bid AS '图书编号',author AS '作者名称',pubComp AS '出版社',price AS '单价' FROM book WHERE bid NOT IN(     SELECT nif FROM borrow ); 第4题 SELECT reader.rName AS '读者姓名',book.bName AS '图...
Mysql 第二章 数据库 DML和DQL
doudou19930614的博客
07-02 3902
一、MySQL的存储引擎     1 、存储引擎的类型：有MyISAM、InnoDB 、Memory、CSV等9种     2、 MyISAM与InnoDB类型主要区别   适用场合   使用MyISAM:   不需事务，空间小，以查询访问为主   使用InnoDB:     多删除、更新操作，安全性高，事务处理及并发控制   查看当前默认存储引擎 语法：SHOW VARIA...
测试开发笔记-数据库例题（一）
Myhappinessisyou的博客
06-07 1051
创建如下数据表 #建学生信息表student -- create table student( -- sno varchar(20) not null primary key, -- sname varchar(20) not null, -- ssex varchar(20) not null, -- sbirthday datetime, -- class varchar(20) -- ); -- -- #建立教师表 -- create table teacher -- ( -- tno varcha
MySQL复习之插入、查询、更新和删除数据
Java学习之乐的博客
05-08 528
今日目录        1、插入数据        2、查询数据        3、更新数据        4、删除数据用到的数据库：表一：/*SQLyog 企业版 - MySQL GUI v8.14 MySQL - 5.1.49-community **********************************************************************//*!...
MySQL连接查询实战：部门与员工表格练习题
本资源是一份针对MySQL数据库的查询练习题，主要围绕表设计、数据结构和多表连接查询展开。内容涉及到了两个核心表：部门（dept）和员工（emp）。首先，我们来详细解读这些知识点： 1. **表设计与字段说明**： - *...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值