① 本文整理了經(jīng)典的 50 道 SQL 題目，文本分為建庫建表、插入數(shù)據(jù)以及 SQL 50 題這三個部分。
② 這些題目許多博主也整理過，但本人不太了解這些題目具體的出處。第一次了解這些題目是本科期間老師出的題目。如果有網(wǎng)友知道這些題目的最原始出處，可以在評論評論區(qū)中告知。
③ 本文所使用的 MySQL 版本為 5.5，雖然版本有一點舊，但是對 SQL 知識點的復習沒有太大的影響（除了一些舊版沒有的函數(shù)）。
④ 由于本文旨在對 SQL 基礎知識進行復習，并且所涉及的數(shù)據(jù)量也十分的小，所以在編寫 SQL 語句時，并未過多考慮 SQL 優(yōu)化的方面。如果讀者有其它的解法或者發(fā)現(xiàn)錯誤之處，可在評論區(qū)留言，筆者在看到后會及時更新！

1.建庫建表

（1）建庫：創(chuàng)建一個名為 sqlpractice 的數(shù)據(jù)庫。
（2）建表：建立 student、course、teacher 和 score 這 4 張表。它們的字段以及之間的關系如下圖所示。

（3）建庫建表的完整 SQL 語句如下所示。

# 建庫
create database sqlpractice;
use sqlpractice;

# 建立 Student 學生表
CREATE TABLE Student(
s_id VARCHAR(20),
s_name VARCHAR(20) NOT NULL,
s_birth VARCHAR(20) NOT NULL, 
s_sex VARCHAR(10) NOT NULL,
PRIMARY KEY(s_id)	# 主鍵
);

# 建立 Course 課程表
CREATE TABLE Course(
c_id VARCHAR(20),
c_name VARCHAR(20) NOT NULL,
t_id VARCHAR(20) NOT NULL,
PRIMARY KEY(c_id)	# 主鍵
);

# 建立 Teacher 教師表
CREATE TABLE Teacher(
t_id VARCHAR(20),
t_name VARCHAR(20) NOT NULL DEFAULT '',
PRIMARY KEY(t_id)	# 主鍵
);

# 建立 Score 分數(shù)表
CREATE TABLE Score(
s_id VARCHAR(20),
c_id VARCHAR(20),
s_score INT(3),
PRIMARY KEY(s_id, c_id)  # 聯(lián)合主鍵
);

# 添加外鍵
# 語法：ALTER TABLE 從表 ADD FOREIGN KEY(外鍵字段) REFERENCES 主表(主鍵字段)
ALTER TABLE Course ADD FOREIGN KEY(t_id) REFERENCES Teacher(t_id)
ALTER TABLE Score ADD FOREIGN KEY(s_id) REFERENCES Student(s_id)
ALTER TABLE Score ADD FOREIGN KEY(c_id) REFERENCES Course(c_id)

2.插入數(shù)據(jù)

（1）向上面創(chuàng)建的 4 張表中插入測試數(shù)據(jù)的 SQL 語句如下所示（需要注意表之間的關系，以免插入數(shù)據(jù)失?。?/p>

# 分別向四張表中插入數(shù)據(jù)
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 Teacher VALUES('01', '張三');
INSERT INTO Teacher VALUES('02', '李四');
INSERT INTO Teacher VALUES('03', '王五');

INSERT INTO Course VALUES('01', '語文', '02');
INSERT INTO Course VALUES('02', '數(shù)學', '01');
INSERT INTO Course VALUES('03', '英語', '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);

（2）檢驗插入數(shù)據(jù)是否成功

SELECT * FROM Student;
SELECT * FROM Course;
SELECT * FROM Teacher;
SELECT * FROM Score;

Student 表

Course 表

Teacher 表

Score 表

3.SQL 50 題

3.1.?SQL 01——查詢"01"課程比"02"課程成績高的學生的信息及課程分數(shù)

# 本題需要比較"01"課程比"02"課程的成績，故在 where 中將 score 表中的字段 s_score 使用 2 次（即分別對應"01"課程的成績和"02"課程的成績）
# 所以可以使用為 s_score 表取別名的方式來多次使用 score 表中的字段
SELECT
	student.*,
	score1.s_score 
FROM
	student,
	score AS score1,
	score AS score2 
WHERE
	student.s_id = score1.s_id 
	AND score1.s_id = score2.s_id # student, score1, score2 表連接的條件是它們的 s_id 均相等
	AND score1.c_id = '01' 
	AND score2.c_id = '02' 
	AND score1.s_score > score2.s_score;

3.2.SQL 02——查詢"01"課程比"02"課程成績低的學生的信息及課程分數(shù)

SELECT
	student.*,
	score1.s_score 
FROM
	student,
	score AS score1,
	score AS score2 
WHERE
	student.s_id = score1.s_id 
	AND score1.s_id = score2.s_id # student, score1, score2 表連接的條件是它們的 s_id 均相等
	AND score1.c_id = '01' 
	AND score2.c_id = '02' 
	AND score1.s_score < score2.s_score;

3.3.SQL 03——查詢平均成績大于等于 60 分的同學的學生編號、學生姓名和平均成績

# 1.創(chuàng)建臨時表 ss
EXPLAIN SELECT
	student.s_id,
	student.s_name,
	ss.avg_score 
FROM
	student,
	(SELECT s_id, AVG(s_score) AS avg_score FROM score GROUP BY s_id) AS ss 
WHERE
	student.s_id = ss.s_id 
	AND ss.avg_score >= 60;

# 2.先進行內(nèi)連接，然后再分組
SELECT
	student.s_id,
	s_name,
	round(AVG(score.s_score), 2) as avg_score
FROM
	student
	INNER JOIN score ON student.s_id = score.s_id 
GROUP BY
	student.s_id,
	s_name 
HAVING
	AVG(score.s_score) >= 60

3.4.?SQL 04——查詢平均成績小于 60 分的同學的學生編號和學生姓名和平均成績（包括有成績的和無成績的）

# isnull(exper) 判斷 exper 是否為空，是則返回 1，否則返回 0
# ifnull(exper1, exper2) 判斷 exper1 是否為空，是則用 exper2 代替
# nullif(exper1, exper2) 如果 expr1 = expr2 成立，那么返回值為 NULL，否則返回值為 expr1。
SELECT
	student.s_id,
	s_name,
	round(AVG(score.s_score), 2) as avg_score
FROM
	student
	LEFT OUTER JOIN score ON student.s_id = score.s_id 
GROUP BY
	student.s_id,
	s_name 
HAVING
	AVG(IFNULL(score.s_score,0)) < 60

3.5.SQL 05——查詢所有同學的學生編號、學生姓名、選課總數(shù)、所有課程的總成績

SELECT
	student.s_id,
	student.s_name,
	COUNT(DISTINCT c_id) AS totalCourses,
	SUM(s_score) AS totalScores 
FROM
	student
	# 由于要查詢所有的學生，故無論其是否有課程信息都要查詢，所以使用 LEFT OUTER JOIN
	LEFT OUTER JOIN score ON student.s_id = score.s_id 
GROUP BY
	student.s_id,
	student.s_name;

3.6.SQL 06——查詢"李"姓老師的數(shù)量

# 1.模糊查詢
SELECT
	COUNT(*) 
FROM
	teacher 
WHERE
	t_name LIKE '李%'

# 2.正則表達式查詢，字符 '^' 匹配以特定字符或者字符串開頭的文本
SELECT
	count(*) 
FROM
	teacher 
WHERE
	t_name REGEXP '^李'

3.7.?SQL 07——查詢學過"張三"老師授課的同學的信息

# 1.使用多表連接(score, course, teacher)找到上張三老師課的同學的 s_id，然后再根據(jù) s_id 從 student 表中查詢同學信息
SELECT
	student.* 
FROM
	student 
WHERE
	s_id IN (
		SELECT
			s_id 
		FROM
			score,
			course,
			teacher 
		WHERE
			teacher.t_name = '張三' 
			AND teacher.t_id = course.t_id 
			AND course.c_id = score.c_id 
	)

# 2.多層嵌套子查詢(當數(shù)據(jù)量較大時，一般不推薦使用子查詢)
# 在 student 表中根據(jù)上過張三老師教的課的學生 s_id 來查詢他們的信息
SELECT
	student.* 
FROM
	student 
WHERE
	student.s_id IN (
			# 在 score 表中根據(jù)張三老師教的課程 c_id 來查找上這些課的學生 s_id
			SELECT DISTINCT
				s_id 
			FROM
				score 
			WHERE
				score.c_id IN (
					# 在 course 表中根據(jù)張三老師的 t_id 查詢他所教的課程 c_id
					SELECT 
						c_id 
					FROM 
						course 
					WHERE 
						course.t_id = (
							# 在 teacher 表中查詢張三老師的 t_id
							SELECT 
								t_id 
							FROM 
								teacher 
							WHERE 
								t_name = '張三'
						)
				)
	)

3.8.SQL 08——查詢沒學過"張三"老師授課的同學的信息

SELECT
	student.* 
FROM
	student 
WHERE
	student.s_id NOT IN (
			SELECT DISTINCT
				s_id 
			FROM
				score 
			WHERE
				score.c_id IN (
					SELECT 
						c_id 
					FROM 
						course 
					WHERE 
						course.t_id = (
							SELECT 
								t_id 
							FROM 
								teacher 
							WHERE 
								t_name = '張三'
						)
				)
	)

3.9.SQL 09——查詢學過編號為"01"并且也學過編號為"02"的課程的同學的信息

SELECT
	student.* 
FROM
	student 
WHERE
	student.s_id IN (
		SELECT
			s1.s_id 
		FROM
			score AS s1,
			score AS s2 
		WHERE
			s1.s_id = s2.s_id 
			AND s1.c_id = '01' 
			AND s2.c_id = '02' 
	)

3.10.?SQL 10——查詢學過編號為"01"但是沒有學過編號為"02"的課程的同學的信息

SELECT
	stu.s_id,
	stu.s_name,
	stu.s_birth,
	stu.s_sex 
FROM
	student AS stu
	JOIN score AS sc ON stu.s_id = sc.s_id
	JOIN course AS co ON co.c_id = sc.c_id 
WHERE
	co.c_id = '01' 
	AND stu.s_id NOT IN (
		# 查詢學過編號為 "02" 的課程的同學 id
		SELECT
			stu.s_id
		FROM
			student AS stu
			JOIN score AS sc ON stu.s_id = sc.s_id
			JOIN course AS co ON co.c_id = sc.c_id 
		WHERE
			co.c_id = '02' 
	)

3.11.SQL 11——查詢沒有學全所有課程的同學的信息

# 下面的課程數(shù)量 3 也可以用 (SELECT count(*) FROM course) 來代替
SELECT
	* 
FROM
	student 
WHERE
	s_id IN (SELECT s_id FROM score GROUP BY s_id HAVING count(c_id) < 3)

3.12.SQL 12——查詢至少有一門課與學號為"01"的同學所學相同的同學的信息

# 不包括學號為 '01' 學生自己
SELECT
	* 
FROM
	student 
WHERE
	s_id IN (
		SELECT DISTINCT s_id FROM score 
		WHERE c_id IN (SELECT c_id FROM score WHERE s_id = '01') 
			  AND s_id != '01'
	)

3.13.?SQL 13——查詢和"01"號的同學學習的課程完全相同的其他同學的信息

SELECT
	* 
FROM
	student 
WHERE
	s_id IN (
		SELECT
			s_id 
		FROM
			score 
		WHERE
			# 保證學習的課程相同
			c_id IN (SELECT DISTINCT c_id FROM score WHERE s_id = '01') 
			AND s_id != '01' 
		GROUP BY
			s_id 
		HAVING
			# 保證學習的課程數(shù)量相同
			count(c_id) = (select count(*) from score where s_id = '01')
	)

3.14.SQL 14——查詢沒學過"張三"老師講授的任一門課程的學生姓名

SELECT
	s_name 
FROM
	student 
WHERE
	s_id NOT IN (
		# 查詢學習過"張三"老師講授的任一門課程的學生 id
		SELECT
			s_id 
		FROM
			score 
		WHERE
			c_id IN ( 
				# 查詢由姓名為張三的老師所講授的課程 id
				SELECT
					c_id 
				FROM
					course 
				WHERE
					t_id IN (SELECT t_id FROM teacher WHERE t_name = '張三')
			)
	)

3.15.?SQL 15——查詢兩門及其以上不及格課程的同學的學號，姓名及其平均成績

SELECT
	stu.s_id,
	stu.s_name,
	tmp_t.avg_score 
FROM
	student AS stu
	INNER JOIN 
		(
			SELECT 
				s_id, 
				round(avg(s_score), 2) AS avg_score 
			FROM 
				score 
			WHERE 
				s_score < 60 
			GROUP BY 
				s_id 
			HAVING 
				count(s_score) >= 2
		) AS tmp_t 
	ON 
		stu.s_id = tmp_t.s_id

3.16.SQL 16——檢索"01"課程分數(shù)小于60，按分數(shù)降序排列的學生信息

SELECT
	stu.* 
FROM
	student AS stu
	INNER JOIN score ON stu.s_id = score.s_id 
WHERE
	c_id = '01' 
	AND 
	s_score < 60 
ORDER BY
	s_score DESC

3.17.SQL 17——按平均成績從高到低顯示所有學生的所有課程的成績以及平均成績

SELECT
	s_id,
	max(CASE c_id WHEN '01' THEN s_score ELSE 0 END) AS '01',
	max(CASE c_id WHEN '02' THEN s_score ELSE 0 END) AS '02',
	max(CASE c_id WHEN '03' THEN s_score ELSE 0 END) AS '03',
	avg(s_score) AS avg_score 
FROM
	score 
GROUP BY
	s_id 
ORDER BY
	avg_score DESC

3.18.?SQL 18——查詢各科成績最高分、最低分和平均分：以如下形式顯示：課程ID，課程名稱，最高分，最低分，平均分，及格率，中等率，優(yōu)良率，優(yōu)秀率。及格為 60-70，中等為 71-80，優(yōu)良為 81-90，優(yōu)秀為 >= 91。

SELECT
	sc.c_id AS "課程ID",
	c.c_name AS '課程名稱',
	MAX(sc.s_score) AS "最高分",
	MIN(sc.s_score) AS '最低分',
	AVG(sc.s_score) AS '平均分',
	SUM(IF (sc.s_score BETWEEN 60 AND 70, 1, 0)) / COUNT(*) as '及格率',
	SUM(IF (sc.s_score BETWEEN 71 AND 80, 1, 0)) / COUNT(*) as '中等率',
	SUM(IF (sc.s_score BETWEEN 81 AND 90, 1, 0)) / COUNT(*) as '優(yōu)良率',
	SUM(IF (sc.s_score >= 91, 1, 0)) / COUNT(*) as '優(yōu)秀率' 
FROM
	score AS sc
	JOIN course AS c ON sc.c_id = c.c_id 
GROUP BY
	sc.c_id

3.19.?SQL 19——按各科成績進行排序，并顯示排名，成績重復時合并名次

SELECT
	sc1.c_id,
	sc1.s_id,
	sc1.s_score,
	count(sc2.s_score) + 1 AS rank 
FROM
	score AS sc1 LEFT JOIN score AS sc2 
	ON sc1.s_score < sc2.s_score
	   AND sc1.c_id = sc2.c_id 
GROUP BY
	sc1.c_id,
	sc1.s_id,
	sc1.s_score 
ORDER BY
	sc1.c_id,
	rank

3.20.?SQL 20——查詢學生的總成績并進行排名，總分重復時不保留名次空缺

SELECT
    stu.s_id,
    stu.s_name,
    total_score,
    (
			SELECT COUNT(DISTINCT total_score) 
			FROM (SELECT SUM(s_score) AS total_score FROM score GROUP BY s_id) AS sub 
			WHERE total_score >= tmp.total_score
		) AS rank
FROM
    student as stu
    INNER JOIN (
        SELECT
            s_id,
            SUM(s_score) AS total_score
        FROM
            score
        GROUP BY
            s_id
    ) AS tmp ON stu.s_id = tmp.s_id
ORDER BY
    total_score DESC;

3.21.SQL 21——查詢不同老師所教不同課程平均分，并從高到低顯示

SELECT
	teacher.t_id,
	t_name,
	round(avg(s_score), 2) AS avg_score 
FROM
	teacher,
	course,
	score 
WHERE
	teacher.t_id = course.t_id 
	AND course.c_id = score.c_id 
GROUP BY
	teacher.t_id,
	t_name,
	score.c_id 
ORDER BY
	avg(score.s_score) DESC

3.22.SQL 22——查詢所有課程的成績第 2 名到第 3 名的學生信息及該課程成績

# 1.分別對每門課程進行查詢，然后再合并查詢結果，但是如果課程太多，該方法就不太合適
SELECT
	t1.* 
FROM
	(
		SELECT
			st.*,
			c.c_id,
			c.c_name,
			sc.s_score 
		FROM
			student st
			LEFT JOIN score sc ON sc.s_id = st.s_id
			INNER JOIN course c ON c.c_id = sc.c_id 
			AND c.c_id = "01" 
		ORDER BY
			sc.s_score DESC 
			LIMIT 1,
			2 
	) as t1

UNION ALL

SELECT
	t2.* 
FROM
	(
		SELECT
			st.*,
			c.c_id,
			c.c_name,
			sc.s_score 
		FROM
			student st
			LEFT JOIN score sc ON sc.s_id = st.s_id
			INNER JOIN course c ON c.c_id = sc.c_id 
			AND c.c_id = "02" 
		ORDER BY
			sc.s_score DESC 
			LIMIT 1,
			2 
	) as t2

UNION ALL

SELECT
	t3.* 
FROM
	(
		SELECT
			st.*,
			c.c_id,
			c.c_name,
			sc.s_score 
		FROM
			student st
			LEFT JOIN score sc ON sc.s_id = st.s_id
			INNER JOIN course c ON c.c_id = sc.c_id 
			AND c.c_id = "03" 
		ORDER BY
			sc.s_score DESC 
			LIMIT 1,
			2 
	) as t3

# 2.一次性查詢，需要注意的是 row_number() 在 MySQL 8.0 中才支持
SELECT
	c_id,
	student.*,
	s_score 
FROM
	student
	INNER JOIN (
			SELECT 
				s_id, 
				s_score, 
				c_id, 
				row_number() over (PARTITION BY c_id ORDER BY s_score DESC) AS rank 
			FROM 
				score
	) AS tmp_t ON tmp_t.s_id = student.s_id 
WHERE
	tmp_t.rank IN (2, 3)

3.23.?SQL 23——統(tǒng)計各科成績各分數(shù)段人數(shù)：課程編號，課程名稱，[100-85]，[85-70]，[70-60]，[0-60]及所占百分比

SELECT
	score.c_id,
	course.c_name,
	sum(CASE WHEN s_score BETWEEN 0 AND 60 THEN 1 ELSE 0 END) AS '[0-60]人數(shù)',
	sum(CASE WHEN s_score BETWEEN 61 AND 70 THEN 1 ELSE 0 END) AS '[61-70]人數(shù)',
	sum(CASE WHEN s_score BETWEEN 71 AND 85 THEN 1 ELSE 0 END) AS '[71-85]人數(shù)',
	sum(CASE WHEN s_score BETWEEN 86 AND 100 THEN 1 ELSE 0 END) AS '[86-100]人數(shù)',
	round(sum(CASE WHEN s_score BETWEEN 0 AND 60 THEN 1 ELSE 0 END) / count(*), 2) AS '[0-60]人數(shù)所占百分比',
	round(sum(CASE WHEN s_score BETWEEN 61 AND 70 THEN 1 ELSE 0 END) / count(*), 2) AS '[61-70]人數(shù)所占百分比',
	round(sum(CASE WHEN s_score BETWEEN 71 AND 85 THEN 1 ELSE 0 END) / count(*), 2) AS '[71-85]人數(shù)所占百分比',
	round(sum(CASE WHEN s_score BETWEEN 86 AND 100 THEN 1 ELSE 0 END) / count(*), 2) AS '[86-100]人數(shù)所占百分比' 
FROM
	score LEFT JOIN course 
	ON score.c_id = course.c_id 
GROUP BY
	score.c_id,
	course.c_name

3.24.SQL 24——查詢學生平均成績及其名次

# 在 MySQL 8 中可以使用 rank 函數(shù)來實現(xiàn)排名
SELECT
    stu.s_id,
    stu.s_name,
    round(avg(sc.s_score), 2) AS average_score,
    (
		SELECT COUNT(DISTINCT avg_score) 
		FROM (SELECT AVG(s_score) AS avg_score FROM score GROUP BY s_id) AS sub 
		WHERE avg_score >= AVG(sc.s_score)
	) AS rank
FROM
    student as stu
    INNER JOIN score as sc ON stu.s_id = sc.s_id
GROUP BY
    stu.s_id,
    stu.s_name
ORDER BY
    average_score DESC;

3.25.SQL 25——查詢各科成績前三名的記錄

# 1.分別對每科進行查詢，然后再合并查詢結果，但是如果課程太多，該方法就不太合適
(SELECT c_id, s_score FROM score WHERE c_id = '01' ORDER BY s_score DESC LIMIT 3) UNION ALL
(SELECT c_id, s_score FROM score WHERE c_id = '02' ORDER BY s_score DESC LIMIT 3) UNION ALL
(SELECT c_id, s_score FROM score WHERE c_id = '03' ORDER BY s_score DESC LIMIT 3)

# 2.一次性查詢出結果
SELECT DISTINCT
	tmp_t.c_id,
	tmp_t.s_score 
FROM
	(
		SELECT DISTINCT
			student.*,
			sc1.c_id,
			sc1.s_score,
			count(DISTINCT sc2.s_score) + 1 AS rank 
		FROM
			score AS sc1
			LEFT JOIN score AS sc2 ON sc1.c_id = sc2.c_id 
			AND sc1.s_score < sc2.s_score
			LEFT JOIN student ON sc1.s_id = student.s_id 
		GROUP BY
			sc1.c_id,
			sc1.s_id 
		ORDER BY
			sc1.c_id,
			sc1.s_score DESC 
	) AS tmp_t 
WHERE
	tmp_t.rank BETWEEN 1 AND 3

3.26.SQL 26——查詢每門課程被選修的學生數(shù)

SELECT
	c_id,
	count( s_id ) AS '選修該門課程的學生數(shù)' 
FROM
	score 
GROUP BY
	c_id

3.27.SQL 27——查詢出只有兩門課程的全部學生的學號和姓名

SELECT
	student.s_id,
	student.s_name 
FROM
	student,
	score 
WHERE
	student.s_id = score.s_id 
GROUP BY
	s_id 
HAVING
	count(c_id) = 2

3.28.SQL 28——查詢男生、女生人數(shù)

SELECT
	sum(CASE WHEN s_sex = '男' THEN 1 ELSE NULL END) AS '男生人數(shù)',
	sum(CASE WHEN s_sex = '女' THEN 1 ELSE NULL END) AS '女生人數(shù)' 
FROM
	student

3.29.SQL 29——查詢名字中含有"風"字的學生信息

# 1.使用模糊匹配
SELECT
	* 
FROM
	student 
WHERE
	s_name LIKE '%風%'

# 2.使用正則表達式
SELECT
	* 
FROM
	student 
WHERE
	s_name REGEXP '風'

在 MySQL 中，LIKE 操作符用于在文本字段中搜索特定的模式。如果需要在文本字段中匹配通配符本身，可以使用反斜杠字符轉義通配符。例如，如果要在一個名為 ’mytable’ 的表中查找包含下劃線字符的字符串，可以使用以下查詢：
SELECT * FROM mytable WHERE mycolumn LIKE '%\_%' ESCAPE '\';
在上面的查詢中，ESCAPE 關鍵字指定了轉義字符為反斜杠，因此我們在通配符前添加了一個反斜杠字符。這將告訴 MySQL 僅匹配下劃線字符本身，而不是作為通配符進行匹配。

3.30.?SQL 30——查詢同姓名同性別學生名單，并統(tǒng)計同名人數(shù)

SELECT
	stu1.s_name,
	tmp_t.cnt AS '同名人數(shù)' 
FROM
	student AS stu1
	LEFT JOIN (
		SELECT s_name, s_sex, count(*) AS cnt 
		FROM student 
		GROUP BY s_name, s_sex
	) AS tmp_t 
	ON stu1.s_name = tmp_t.s_name AND stu1.s_sex = tmp_t.s_sex 
WHERE
	tmp_t.cnt > 1

3.31.SQL 31——查詢 1990 年出生的學生名單

# 1.使用模糊匹配
SELECT
	* 
FROM
	student 
WHERE
	s_birth LIKE '1990%'

# 2.使用正則表達式
SELECT
	* 
FROM
	student 
WHERE
	s_birth REGEXP '^1990'

3.32.?SQL 32——查詢每門課程的平均成績，結果按平均成績降序排列，平均成績相同時，按課程編號升序排列

SELECT
	score.c_id,
	course.c_name,
	round(avg(s_score), 2) AS avg_score 
FROM
	score, course
where score.c_id = course.c_id
GROUP BY
	c_id
ORDER BY
	avg_score DESC,
	c_id ASC

3.33.SQL 33——查詢平均成績大于等于 85 的所有學生的學號、姓名和平均成績

SELECT
	student.s_id,
	student.s_name,
	round(avg(s_score), 2) AS '平均成績' 
FROM
	student
	INNER JOIN score ON student.s_id = score.s_id 
GROUP BY
	score.s_id,
	student.s_id,
	student.s_name 
HAVING
	avg(score.s_score) > 85

3.34.SQL 34——查詢課程名稱為"數(shù)學"，且分數(shù)低于 60 的學生姓名和分數(shù)

SELECT
	s_name,
	s_score 
FROM
	student,
	score 
WHERE
	student.s_id = score.s_id 
	AND c_id IN (SELECT c_id FROM course WHERE c_name = '數(shù)學') 
	AND s_score < 60

3.35.SQL 35——查詢所有學生的課程及分數(shù)情況

SELECT
	student.s_id,
	student.s_name,
	course.c_name,
	score.s_score 
FROM
	student,
	course,
	score 
WHERE
	student.s_id = score.s_id 
	AND score.c_id = course.c_id 
ORDER BY
	s_id

3.36.SQL 36——查詢?nèi)魏我婚T課程成績在 70 分以上的姓名、課程名稱和分數(shù)

SELECT
	student.s_name,
	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 s_score > 70

3.37.SQL 37——查詢不及格的課程的學生姓名、課程名稱和分數(shù)

SELECT
	student.s_name,
	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 s_score < 60

3.38.SQL 38——查詢課程編號為 01 且課程成績在 80 分以上的學生的學號和姓名

SELECT 
	student.s_id, 
	s_name 
FROM 
	student, 
	score 
WHERE 
	student.s_id = score.s_id 
	AND c_id = '01' 
	AND s_score >= 80

3.39.SQL 39——求每門課程的學生人數(shù)

SELECT
	c_name,
	count(s_id) AS '學生人數(shù)' 
FROM
	score,
	course 
WHERE
	score.c_id = course.c_id 
GROUP BY
	score.c_id

3.40.?SQL 40——查詢選修"張三"老師所授課程的學生中，成績最高的學生信息及其成績

# 這里默認的是一門老師只教授一門課程
SELECT
	student.*,
	score.s_score 
FROM
	student,
	score 
WHERE
	student.s_id = score.s_id 
	AND c_id IN (SELECT c_id FROM course WHERE t_id IN (SELECT t_id FROM teacher WHERE t_name = '張三')) 
ORDER BY
	s_score DESC 
	LIMIT 1

3.41.SQL 41——查詢不同課程成績相同的學生的學生編號、課程編號、學生成績

SELECT
	sc1.s_id,
	sc1.c_id,
	sc2.c_id,
	sc1.s_score,
	sc2.s_score 
FROM
	score AS sc1,
	score AS sc2 
WHERE
	sc1.s_id = sc2.s_id 
	AND sc1.s_score = sc2.s_score 
	AND sc1.c_id != sc2.c_id

3.42.?SQL 42——查詢每門課程成績最好的前兩名

SELECT
	sc1.c_id,
	sc1.s_id,
	count(sc2.s_score) + 1 AS rank 
FROM
	score AS sc1
	LEFT JOIN score AS sc2 ON sc1.c_id = sc2.c_id 
	AND sc1.s_score < sc2.s_score 
GROUP BY
	sc1.c_id,
	sc1.s_score,
	sc1.s_id 
HAVING
	count(sc2.s_score) < 2 
ORDER BY
	sc1.c_id,
	rank

3.43.SQL 43——統(tǒng)計每門課程的學生選修人數(shù)（超過 5 人的課程才統(tǒng)計），要求輸出課程號和選修人數(shù)，查詢結果按人數(shù)降序排列，若人數(shù)相同，按課程號升序排列

SELECT 
	c_id, 
	count(*) AS '選修人數(shù)' 
FROM 
	score 
GROUP BY 
	c_id 
HAVING 
	count(*) > 5 
ORDER BY
	'選修人數(shù)' DESC,
	c_id ASC

3.44.SQL 44——查詢至少選修兩門課程的學生學號

SELECT s_id FROM score GROUP BY s_id HAVING count(c_id) >= 2

3.45.SQL 45——查詢選修了全部課程的學生信息

SELECT
	* 
FROM
	student 
WHERE
	# SELECT count(*) FROM course) 查詢的是總課程的數(shù)量
	s_id IN (SELECT s_id FROM score GROUP BY s_id HAVING count(c_id) = (SELECT count(*) FROM course))

3.46.?SQL 46——查詢各學生的年齡

# 1.按照年份來計算
SELECT
	s_id,
	s_name,
	(YEAR(now()) - YEAR(s_birth)) AS age 
FROM
	student

/*
2.使用 timestampdiff()
(1) TIMESTAMPDIFF(): 第一個參數(shù)設置時間單位，可以精確到年(YEAR)、天(DAY)、小時(HOUR)，分鐘(MINUTE)和秒(SECOND)。對于比較
	的兩個時間，時間小的放在前面，時間大的放在后面。
(3) datediff(): 返回值是相差的天數(shù)，無法定位到小時、分鐘和秒。
*/
SELECT
	s_id,
	s_name,
	timestampdiff(YEAR, s_birth, now()) AS age 
FROM
	student

3.47.SQL 47——查詢本周過生日的學生

# week(時間): 默認從 0 開始，表示星期天為一個星期的第一天，國外算法
# week(時間, 1): 從 1 開始，表示星期一為一個星期的第一天，國內(nèi)算法
SELECT
	s_id,
	s_name 
FROM
	student 
WHERE
	WEEK (s_birth) = WEEK (now(), 1)

3.48.SQL 48——查詢下周過生日的學生

SELECT
	s_id,
	s_name 
FROM
	student 
WHERE
	WEEK (s_birth) = WEEK (now(), 1) + 1

3.49.SQL 49——查詢本月過生日的學生

SELECT
	s_id,
	s_name 
FROM
	student 
WHERE
	MONTH (s_birth) = MONTH (now())

3.50.SQL 50——查詢下月過生日的學生

SELECT 
    s_id, 
    s_name 
FROM 
    student 
WHERE 
    (MONTH(s_birth) = (((MONTH(NOW()) + 12) % 12) + 1))

文章來源地址http://www.zghlxwxcb.cn/news/detail-725438.html

到了這里，關于SQL 50 題（MySQL 版，包括建庫建表、插入數(shù)據(jù)等完整過程，適合復習 SQL 知識點）的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關文章，希望大家以后多多支持TOY模板網(wǎng)！