sql减法语句_SQL入门之多表查询

SELECT
	CS.教师号,
	CS.课程号,
	CS.课程名称,
	TC.教师姓名
FROM
	course AS CS
LEFT JOIN teacher AS TC ON CS.教师号 = TC.教师号;

SELECT
	CS.教师号,
	CS.课程号,
	CS.课程名称,
	TC.教师姓名
FROM
	course AS CS
RIGHT JOIN teacher AS TC ON CS.教师号 = TC.教师号;

SELECT
	CS.教师号,
	CS.课程号,
	CS.课程名称,
	TC.教师姓名
FROM
	course AS CS
INNER JOIN teacher AS TC ON CS.教师号 = TC.教师号;

(SELECT
	CS.教师号,
	CS.课程号,
	CS.课程名称,
	TC.教师姓名
FROM
	course AS CS
LEFT JOIN teacher AS TC ON CS.教师号 = TC.教师号)
UNION
 
(SELECT
	TC.教师号,
	CS.课程号,
	CS.课程名称,
	TC.教师姓名
FROM
	course AS CS
RIGHT JOIN teacher AS TC ON CS.教师号 = TC.教师号)

SELECT
	a.id,
	a.mdate,
	count(b.player)
FROM
	game AS a
INNER JOIN goal AS b ON a.id = b.matchid
WHERE
	(team1 = 'POL' OR team2 = 'POL')
GROUP BY
	a.id,
	a.mdate

SELECT
	a.mdate,
	a.team1,
	sum(
		CASE
		WHEN b.teamid = a.team1 THEN
			1
		ELSE
			0
		END
	) AS score1,
	a.team2,
	sum(
		CASE
		WHEN b.teamid = a.team2 THEN
			1
		ELSE
			0
		END
	) AS score2
FROM
	game AS a
LEFT JOIN goal AS b ON a.id = b.matchid
GROUP BY
	a.id,
	a.mdate,
	a.team1,
	a.team2;

-- 查询所有学生的学号、姓名、选课数、总成绩，保存为视图，方便后续使用
CREATE VIEW score_stu_view (
	出生日期,
	姓名,
	学号,
	性别,
	成绩,
	课程号
) AS SELECT
	stu.出生日期,
	stu.姓名,
	stu.学号,
	stu.性别,
	sc.成绩,
	sc.课程号
FROM
	student AS stu
INNER JOIN score AS sc ON stu.学号 = sc.学号;
SELECT
	学号,
	姓名,
	COUNT(课程号) AS 选课数,
	SUM(成绩) AS 总成绩
FROM
	score_stu_view
GROUP BY
	学号;
-- 查询平均成绩大于85的所有学生的学号、姓名和平均成绩
SELECT
	学号,
	姓名,
	AVG(成绩) AS 平均成绩
FROM
	score_stu_view
GROUP BY
	学号
HAVING
	平均成绩 > 85;
-- 查询学生的选课情况：学号，姓名，课程号，课程名称
CREATE VIEW course_score_view(姓名,学号,成绩,课程号,课程名称) AS SELECT
	stu.姓名,
	stu.学号,
	sc.成绩,
	sc.课程号,cs.课程名称
FROM
	student AS stu
INNER JOIN score AS sc ON stu.学号 =sc.学号
INNER JOIN course AS cs ON sc.课程号 =cs.课程号;
-- 查询出每门课程的及格人数和不及格
SELECT
	课程号,
	sum(
		CASE
		WHEN 成绩 >= 60 THEN
			1
		ELSE
			0
		END
	) AS 及格人数,
	sum(CASE WHEN 成绩 < 60 THEN 1 ELSE 0 END) AS 不及格人数
FROM
	course_score_view
GROUP BY
	课程号;
/*使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩，分别统计：各分数段人数，
课程号和课程名称*/
SELECT
	课程号,
	课程名称,
	sum(
		CASE
		WHEN 成绩 > 85
		AND 成绩 <= 100 THEN
			1
		ELSE
			0
		END
	) AS 一等,
	sum(
		CASE
		WHEN 成绩 > 70
		AND 成绩 <= 85 THEN
			1
		ELSE
			0
		END
	) AS 二等,
	sum(
		CASE
		WHEN 成绩 >= 60
		AND 成绩 <= 70 THEN
			1
		ELSE
			0
		END
	) AS 三等,
	sum(CASE WHEN 成绩 < 60 THEN 1 ELSE 0 END) AS 不及格
FROM
	course_score_view
GROUP BY
	课程号;