一、数据库操作（5例）
1. 创建数据库school，指定字符集为utf8mb4
CREATE DATABASE IF NOT EXISTS school CHARACTER SET utf8mb4;
2. 查看所有数据库
SHOW DATABASES;
3. 切换到school数据库
USE school;
4. 查看当前使用的数据库
SELECT DATABASE();
5. 删除数据库test（若存在）
DROP DATABASE IF EXISTS test;
二、表操作（10例）
6. 创建学生表student（id, name, age, gender）
CREATE TABLE IF NOT EXISTS student (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
age INT,
gender VARCHAR(2)
);
7. 查看student表结构
DESC student;
8. 向student表添加birthday字段（DATE类型）
ALTER TABLE student ADD birthday DATE;
9. 修改student表的age字段为TINYINT
ALTER TABLE student MODIFY age TINYINT;
10. 创建课程表course（id, course_name）
CREATE TABLE course (
id INT PRIMARY KEY AUTO_INCREMENT,
course_name VARCHAR(100) NOT NULL
);
11. 创建成绩表score（关联学生和课程）
CREATE TABLE score (
id INT PRIMARY KEY AUTO_INCREMENT,
student_id INT,
course_id INT,
score INT,
FOREIGN KEY (student_id) REFERENCES student(id),
FOREIGN KEY (course_id) REFERENCES course(id)
);
12. 查看school库中所有表
SHOW TABLES;
13. 为student表的name字段添加索引
CREATE INDEX idx_name ON student(name);
14. 复制student表结构到student_backup
CREATE TABLE student_backup LIKE student;
15. 删除student_backup表
DROP TABLE IF EXISTS student_backup;
三、数据插入（10例）
16. 向student表插入1条数据
INSERT INTO student (name, age, gender) VALUES ('张三', 18, '男');
17. 向student表批量插入3条数据
INSERT INTO student (name, age, gender) VALUES
('李四', 19, '女'),
('王五', 18, '男'),
('赵六', 20, '女');
18. 向course表插入课程
INSERT INTO course (course_name) VALUES ('数学'), ('英语');
19. 向score表插入成绩（学生1选课程1，分数85）
INSERT INTO score (student_id, course_id, score) VALUES (1, 1, 85);
20. 插入成绩时忽略主键冲突
INSERT IGNORE INTO score (id, student_id, course_id, score) VALUES (1, 1, 2, 90);
21. 向student表插入含NULL的记录
INSERT INTO student (name, age) VALUES ('孙七', NULL); -- gender为NULL
22. 复制student表中年龄>18的学生到student_adult
CREATE TABLE student_adult LIKE student;
INSERT INTO student_adult SELECT * FROM student WHERE age > 18;
23. 向score表插入分数为100的数据
INSERT INTO score (student_id, course_id, score) VALUES (2, 1, 100);
24. 批量插入多条成绩
INSERT INTO score (student_id, course_id, score) VALUES
(1, 2, 90),
(3, 1, 76),
(4, 2, 95);
25. 向student表插入指定id的记录
INSERT INTO student (id, name, age) VALUES (10, '周八', 21);
四、基础查询（20例）
26. 查询student表所有学生的所有信息
SELECT * FROM student;
27. 查询student表中所有学生的姓名和年龄
SELECT name, age FROM student;
28. 查询student表中性别为'男'的学生
SELECT * FROM student WHERE gender = '男';
29. 查询student表中年龄大于18的学生
SELECT * FROM student WHERE age > 18;
30. 查询student表中年龄在18~20之间的学生
SELECT * FROM student WHERE age BETWEEN 18 AND 20;
31. 查询student表中年龄为18或20的学生
SELECT * FROM student WHERE age IN (18, 20);
32. 查询student表中姓名包含'张'的学生
SELECT * FROM student WHERE name LIKE '%张%';
33. 查询student表中姓名以'李'开头的学生
SELECT * FROM student WHERE name LIKE '李%';
34. 查询student表中年龄为空的学生
SELECT * FROM student WHERE age IS NULL;
35. 查询student表中年龄不为空且性别为女的学生
SELECT * FROM student WHERE age IS NOT NULL AND gender = '女';
36. 查询score表中分数大于等于90的记录
SELECT * FROM score WHERE score >= 90;
37. 查询score表中分数在80~90之间的记录
SELECT * FROM score WHERE score > 80 AND score < 90;
38. 查询student表并给列起别名
SELECT name AS '学生姓名', age '年龄' FROM student;
39. 查询student表中所有不重复的年龄
SELECT DISTINCT age FROM student;
40. 查询student表中年龄最大的3位学生
SELECT * FROM student ORDER BY age DESC LIMIT 3;
41. 查询score表中课程ID为2的分数，按分数降序
SELECT * FROM score WHERE course_id = 2 ORDER BY score DESC;
42. 查询student表所有学生，按年龄升序
SELECT * FROM student ORDER BY age;
43. 查询student表，按年龄降序，同年龄按姓名升序
SELECT * FROM student ORDER BY age DESC, name ASC;
44. 查询student表第2~4条数据
SELECT * FROM student LIMIT 1, 3;
45. 查询score表中分数最高的前2名记录
SELECT * FROM score ORDER BY score DESC LIMIT 2;
五、聚合与分组（15例）
46. 统计student表中学生总数
SELECT COUNT(*) AS '学生总数' FROM student;
47. 统计student表中年龄不为NULL的学生数
SELECT COUNT(age) AS '年龄非空人数' FROM student;
48. 计算score表中所有分数的总和
SELECT SUM(score) AS '总分数' FROM score;
49. 计算score表中课程1的平均分
SELECT AVG(score) AS '课程1平均分' FROM score WHERE course_id = 1;
50. 查找score表中的最高分数
SELECT MAX(score) AS '最高分' FROM score;
51. 查找student表中最小的年龄（不含NULL）
SELECT MIN(age) AS '最小年龄' FROM student WHERE age IS NOT NULL;
52. 统计student表中不同性别的人数
SELECT gender, COUNT(*) AS '人数' FROM student GROUP BY gender;
53. 计算每门课程的平均分
SELECT course_id, AVG(score) AS '平均分' FROM score GROUP BY course_id;
54. 统计每个学生的选课数量
SELECT student_id, COUNT(course_id) AS '选课数量' FROM score GROUP BY student_id;
55. 计算每门课程的最高分和最低分
SELECT course_id, MAX(score) AS '最高分', MIN(score) AS '最低分' FROM score GROUP BY course_id;
56. 筛选出平均分大于85的课程
SELECT course_id, AVG(score) AS '平均分' FROM score GROUP BY course_id HAVING 平均分 > 85;
57. 筛选出总分>200的学生
SELECT student_id, SUM(score) AS '总分' FROM score GROUP BY student_id HAVING 总分 > 200;
58. 按性别分组，统计每组的平均年龄
SELECT gender, AVG(age) AS '平均年龄' FROM student WHERE age IS NOT NULL GROUP BY gender;
59. 按课程ID分组，统计选课人数>=2的课程
SELECT course_id, COUNT(student_id) AS '选课人数' FROM score GROUP BY course_id HAVING 选课人数 >= 2;
60. 按学生ID分组，计算最高分，排除分数<60的记录
SELECT student_id, MAX(score) AS '最高分' FROM score WHERE score >= 60 GROUP BY student_id;
六、连接查询（15例）
61. 内连接：查询学生姓名和对应的课程名称（有成绩的）
SELECT s.name, c.course_name
FROM score sc
JOIN student s ON sc.student_id = s.id
JOIN course c ON sc.course_id = c.id
62. 左连接：查询所有学生的姓名及成绩（包括没成绩的）
SELECT s.name, sc.score
FROM student s
LEFT JOIN score sc ON s.id = sc.student_id;
63. 右连接：查询所有课程及对应的分数（包括没分数的）
SELECT c.course_name, sc.score
FROM score sc
RIGHT JOIN course c ON sc.course_id = c.id;
64. 左连接+筛选：查询没有成绩的学生姓名
SELECT s.name
FROM student s
LEFT JOIN score sc ON s.id = sc.student_id
WHERE sc.id IS NULL;
65. 三表连接：查询学生姓名、课程名称和分数
SELECT s.name, c.course_name, sc.score
FROM score sc
JOIN student s ON sc.student_id = s.id
JOIN course c ON sc.course_id = c.id;
66. 内连接+条件：查询课程名称为'数学'的学生姓名和分数
SELECT s.name, sc.score
FROM score sc
JOIN student s ON sc.student_id = s.id
JOIN course c ON sc.course_id = c.id
WHERE c.course_name = '数学';
67. 左连接+分组：查询每个学生的姓名及选课数量
SELECT s.name, COUNT(sc.course_id) AS '选课数量'
FROM student s
LEFT JOIN score sc ON s.id = sc.student_id
GROUP BY s.name;
68. 连接查询+排序：按分数降序显示学生、课程和分数
SELECT s.name, c.course_name, sc.score
FROM score sc
JOIN student s ON sc.student_id = s.id
JOIN course c ON sc.course_id = c.id
ORDER BY sc.score DESC;
69. 自连接：查询年龄相同的学生姓名
SELECT s1.name, s2.name, s1.age
FROM student s1
JOIN student s2 ON s1.age = s2.age
WHERE s1.id < s2.id;
70. 右连接+筛选：查询没有学生选的课程名称
SELECT c.course_name
FROM score sc
RIGHT JOIN course c ON sc.course_id = c.id
WHERE sc.id IS NULL;
71. 内连接+聚合：查询每门课程的名称和平均分
SELECT c.course_name, AVG(sc.score) AS '平均分'
FROM score sc
JOIN course c ON sc.course_id = c.id
GROUP BY c.course_name;
72. 左连接+条件：查询年龄>18的学生姓名及成绩
SELECT s.name, sc.score
FROM student s
LEFT JOIN score sc ON s.id = sc.student_id
WHERE s.age > 18;
73. 连接查询+多条件：查询女生且分数>85的记录
SELECT s.name, c.course_name, sc.score
FROM score sc
JOIN student s ON sc.student_id = s.id
JOIN course c ON sc.course_id = c.id
WHERE s.gender = '女' AND sc.score > 85;
74. 左连接+计算：查询每个学生的姓名和总分（含0分）
SELECT s.name, IFNULL(SUM(sc.score), 0) AS '总分'
FROM student s
LEFT JOIN score sc ON s.id = sc.student_id
GROUP BY s.name;
75. 连接查询+限制：查询分数最高的3名学生姓名和分数
SELECT s.name, sc.score
FROM score sc
JOIN student s ON sc.student_id = s.id
ORDER BY sc.score DESC
LIMIT 3;
七、子查询（15例）
76. 子查询在WHERE中：查询与'张三'同年龄的学生
SELECT name FROM student WHERE age = (SELECT age FROM student WHERE name = '张三');
77. 子查询返回多个值：查询选了课程1或2的学生ID
SELECT DISTINCT student_id FROM score WHERE course_id IN (1, 2);
78. 子查询+比较：查询年龄大于平均年龄的学生
SELECT name FROM student WHERE age > (SELECT AVG(age) FROM student WHERE age IS NOT NULL);
79. 派生表：查询每个学生的总分
SELECT student_id, total_score
FROM (SELECT student_id, SUM(score) AS total_score FROM score GROUP BY student_id) AS s_total;
80. EXISTS子查询：查询选了课程的学生姓名
SELECT name FROM student s WHERE EXISTS (SELECT 1 FROM score sc WHERE sc.student_id = s.id);
81. NOT EXISTS子查询：查询没选任何课程的学生
SELECT name FROM student s WHERE NOT EXISTS (SELECT 1 FROM score sc WHERE sc.student_id = s.id);
82. 子查询+聚合：查询最高分对应的学生和课程ID
SELECT student_id, course_id FROM score WHERE score = (SELECT MAX(score) FROM score);
83. 子查询在SELECT中：查询每个学生的选课数量
SELECT name, (SELECT COUNT(*) FROM score sc WHERE sc.student_id = s.id) AS '选课数量' FROM student s;
84. 多层子查询：查询选了'数学'的学生姓名
SELECT name FROM student WHERE id IN (
SELECT student_id FROM score WHERE course_id = (
SELECT id FROM course WHERE course_name = '数学'
)
);
85. 子查询+ANY：查询分数高于课程1中任何一个分数的记录
SELECT * FROM score WHERE score > ANY (SELECT score FROM score WHERE course_id = 1);
86. 子查询+ALL：查询分数高于课程1中所有分数的记录
SELECT * FROM score WHERE score > ALL (SELECT score FROM score WHERE course_id = 1);
87. 子查询+NOT IN：查询没选课程1的学生ID
SELECT DISTINCT student_id FROM score WHERE student_id NOT IN (
SELECT student_id FROM score WHERE course_id = 1
);
88. 派生表+连接：查询总分大于200的学生姓名
SELECT s.name
FROM student s
JOIN (SELECT student_id, SUM(score) AS total FROM score GROUP BY student_id HAVING total > 200) AS sc_total
ON s.id = sc_total.student_id;
89. 子查询+多条件：查询年龄比'李四'大且性别相同的学生
SELECT name FROM student
WHERE age > (SELECT age FROM student WHERE name = '李四')
AND gender = (SELECT gender FROM student WHERE name = '李四');
90. 子查询+CASE：查询学生姓名及是否及格
SELECT name,
(CASE WHEN sc.score >= 60 THEN '及格' ELSE '不及格' END) AS '是否及格'
FROM student s
LEFT JOIN score sc ON s.id = sc.student_id;
八、数据修改与删除（10例）
91. 修改student表中'张三'的年龄为19
UPDATE student SET age = 19 WHERE name = '张三';
92. 批量修改：所有学生年龄增加1
UPDATE student SET age = age + 1 WHERE age IS NOT NULL;
93. 修改score表中学生1的课程1分数为90
UPDATE score SET score = 90 WHERE student_id = 1 AND course_id = 1;
94. 修改course表中'数学'的名称为'高等数学'
UPDATE course SET course_name = '高等数学' WHERE course_name = '数学';
95. 修改student表中年龄为NULL的学生，设置为18
UPDATE student SET age = 18 WHERE age IS NULL;
96. 删除student表中姓名为'周八'的记录
DELETE FROM student WHERE name = '周八';
97. 删除score表中学生2的所有成绩
DELETE FROM score WHERE student_id = 2;
98. 删除score表中分数<60的记录
DELETE FROM score WHERE score < 60;
99. 清空score表所有数据（保留表结构）
TRUNCATE TABLE score;
100. 修改student表中'王五'的性别和年龄
UPDATE student SET gender = '女', age = 19 WHERE name = '王五';