MySQL练习题——课后一练

学习目标：

很久没有练习写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的路上修行