mysql练手

CREATE TABLE class (
	cid INT NOT NULL auto_increment PRIMARY KEY,
	caption CHAR (32) NOT NULL
) ENGINE = INNODB DEFAULT charset = utf8;

INSERT INTO score (caption)
VALUES
	(‘三年二班‘),
	(‘一年三班‘),
	(‘三年一班‘) ；

2、查询“生物”课程比“物理”课程成绩高的所有学生的学号

CREATE VIEW vw1 AS SELECT
	score.student_id AS "学号",
	student.sname AS "姓名",
	course.cname AS "科目",
	number AS "生物分数"
FROM
	score
LEFT JOIN course ON course.cid = score.course_id
LEFT JOIN student ON score.student_id = student.sid
WHERE
	course.cname = ‘生物‘;

CREATE VIEW vw2 AS SELECT
	score.student_id AS "学号",
	student.sname AS "姓名",
	course.cname AS "科目",
	number AS "物理分数"
FROM
	score
LEFT JOIN course ON course.cid = score.course_id
LEFT JOIN student ON score.student_id = student.sid
WHERE
	course.cname = ‘物理‘;

SELECT
	*
FROM
	vw1
INNER JOIN vw2 ON vw1.学号 = vw2.学号
WHERE
	vw1.生物分数 > vw2.物理分数;

 　　我们发现按照当前的表结构，没有符合上述条件的学生，不具可比性，但是如果数据量扩大时，具备可比性了，那应该怎么写呢？

3、查询平均成绩大于60分的同学的学号和平均成绩

SELECT
	score.student_id AS "学号",
	student.sname AS "姓名",
	sum(score.number) AS "总分数",
	avg(score.number) AS gva
FROM
	score
LEFT JOIN course ON course.cid = score.course_id
LEFT JOIN student ON score.student_id = student.sid
GROUP BY
	student_id
HAVING
	gva > 60;

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

SELECT
	score.student_id AS "学号",
	student.sname AS "姓名",
	sum(score.number) AS "总成绩",
	count(score.course_id) AS ‘课程数‘
FROM
	score
LEFT JOIN course ON course.cid = score.course_id
LEFT JOIN student ON score.student_id = student.sid
GROUP BY
	student_id

5、查询姓“李”的老师的个数

SELECT
	count(tname) AS "个数"
FROM
	teacher
WHERE
	tname LIKE "波%" ;

6、查询没学过“叶平”老师课的同学的学号、姓名

　　思路：没学过某个老师，我可以找到学过这个老师的学生，并在学生表判断，排除这些学过的就是没学过了

　　　　（学生学的课程id in （先找叶平老师教的课程id））

　　　　最后只要排除 not in这群学生就可了

SELECT
	*
FROM
	student
WHERE
	sid NOT IN (
		SELECT DISTINCT
			student_id
		FROM
			score
		WHERE
			course_id IN (
				SELECT
					cid
				FROM
					course
				LEFT JOIN teacher ON teacher_id = tid
				WHERE
					tname = "波多"
			)
	)

 7、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名

　　思路：先查出所有学了 001 或 002 的学生 course_id  in (001,002)

　　　　然后group by  学生id，having进行科目数统计，等于2的就是符合条件的

SELECT
	student_id,
	sname
FROM
	student
LEFT JOIN score ON student_id = student.sid
WHERE
	course_id IN (1, 2)
GROUP BY
	student_id
HAVING
	count(student_id) = 2;

8、查询学过“叶平”老师所教的所有课的同学的学号、姓名

SELECT
	*
FROM
	student
WHERE
	student.sid IN (
		SELECT DISTINCT
			student_id
		FROM
			score
		WHERE
			course_id IN (
				SELECT
					cid
				FROM
					course
				LEFT JOIN teacher ON teacher.tid = course.teacher_id
				WHERE
					teacher.tname = ‘饭岛‘
			)
	);

9、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名

SELECT
	id1,
	NAME
FROM
	(
		SELECT
			student_id AS id1,
			number AS number1,
			student.sname AS NAME
		FROM
			score
		LEFT JOIN student ON student.sid = score.student_id
		WHERE
			score.course_id = 1
	) AS A
LEFT JOIN (
	SELECT
		student_id AS id2,
		number AS number2
	FROM
		score
	LEFT JOIN student ON student.sid = score.student_id
	WHERE
		score.course_id = 2
) AS B ON A.id1 = B.id2
WHERE
	number1 > number2;

10、查询有课程成绩小于60分的同学的学号、姓名

SELECT DISTINCT
	student.sid,
	sname
FROM
	student
LEFT JOIN score ON student.sid = score.student_id
WHERE
	student.sid IN (
		SELECT
			student_id
		FROM
			score
		WHERE
			number < 60
	);

11、查询没有学全所有课的同学的学号、姓名

SELECT
	sid,
	sname
FROM
	student
WHERE
	sid IN (
		SELECT
			student_id
		FROM
			score
		GROUP BY
			student_id
		HAVING
			count(student_id) = 3
	);

12、查询至少有一门课与学号为“001”的同学所学相同的同学的学号和姓名

　　思路：先找到001同学的course---一个元组

　　　　　　course_id in 元组  ---student id元组

　　　　　　sid in student id元组

SELECT DISTINCT
	sid,
	sname
FROM
	student
WHERE
	sid IN (
		SELECT
			student_id
		FROM
			score
		WHERE
			course_id IN (
				SELECT
					course_id
				FROM
					score
				WHERE
					student_id = 1
			)
	)
AND sid != 1;

13、查询学过学号为“001”同学所有一门课的其他同学学号和姓名

SELECT
	sid,
	sname
FROM
	student
WHERE
	sid IN (
		SELECT
			student_id
		FROM
			score
		WHERE
			course_id IN (
				SELECT
					course_id
				FROM
					score
				WHERE
					student_id = 1
			)
		GROUP BY
			student_id
		HAVING
			count(student_id) = 1
	)
AND sid != 1;

mysql练手

标签：