MySql-学天生绩管理系统

前言

   暑假的时间学习过这个 MySql 数据库，然后学校这个学期居然开了这门课程，那就做一个学天生绩管理系统来练一练手吧。在这里做一个简单的训练记录。
  需求分析

SQL 编写

新建数据库

为该数据库建表

INSERT INTO 数据

创建索引

创建视图

创建触发器

创建存储过程

SQL 查询进行调试

结语

   完备代码如下

1. -- 1. 新建数据库
2. drop database studentManage;
3. CREATE DATABASE studentManage;
4. use studentManage;
6. -- 2. 创建表
8. -- 创建班级表
9. CREATE TABLE classes (
10. class_id INT PRIMARY KEY,  -- 班级ID，作为主键
11. class_name VARCHAR(50)  -- 班级名称，最大长度为50个字符
12. );
14. -- 插入班级数据
15. INSERT INTO classes (class_id, class_name) VALUES (0, '尖子班');
16. INSERT INTO classes (class_id, class_name) VALUES (1, '垃圾班');
17. INSERT INTO classes (class_id, class_name) VALUES (2, '垃圾班');
18. INSERT INTO classes (class_id, class_name) VALUES (3, '废物班');
19. INSERT INTO classes (class_id, class_name) VALUES (4, '废物班');
20. INSERT INTO classes (class_id, class_name) VALUES (5, '脑残班');
22. -- 创建学生表
23. CREATE TABLE students (
24. student_id INT PRIMARY KEY NOT NULL,  -- 学生ID，作为主键，不允许为空
25. name VARCHAR(50),  -- 姓名，最大长度为50个字符
26. class_id INT,  -- 班级ID
27. age INT,  -- 年龄
28. FOREIGN KEY (class_id) REFERENCES classes(class_id)  -- 外键关联到班级表的班级ID
29. );
31. -- 插入学生数据
32. INSERT INTO students (student_id, name, class_id, age) VALUES (1, '王八', 5, 19);
33. INSERT INTO students (student_id, name, class_id, age) VALUES (2, '王一', 1, 20);
34. INSERT INTO students (student_id, name, class_id, age) VALUES (3, '小六', 0, 18);
35. INSERT INTO students (student_id, name, class_id, age) VALUES (4, '王五', 2, 19);
36. INSERT INTO students (student_id, name, class_id, age) VALUES (5, '赵六', 3, 20);
37. INSERT INTO students (student_id, name, class_id, age) VALUES (6, '小七', 4, 18);
39. -- 创建教师表
40. CREATE TABLE teachers (
41. teacher_id INT PRIMARY KEY NOT NULL,  -- 教师ID，作为主键，不允许为空
42. teacher_name VARCHAR(50)  -- 教师姓名，最大长度为50个字符
43. );
45. -- 插入教师数据
46. INSERT INTO teachers (teacher_id, teacher_name) VALUES (1, '陈老师');
47. INSERT INTO teachers (teacher_id, teacher_name) VALUES (2, '杨老师');
48. INSERT INTO teachers (teacher_id, teacher_name) VALUES (4, '何老师');
49. INSERT INTO teachers (teacher_id, teacher_name) VALUES (4, '刘老师');
50. INSERT INTO teachers (teacher_id, teacher_name) VALUES (5, '陈老师');
51. INSERT INTO teachers (teacher_id, teacher_name) VALUES (6, '周老师');
53. -- 创建课程表
54. CREATE TABLE courses (
55. course_id INT PRIMARY KEY NOT NULL,  -- 课程ID，作为主键，不允许为空
56. course_name VARCHAR(50),  -- 课程名称，最大长度为50个字符
57. teacher_id INT,  -- 教师ID
58. FOREIGN KEY (teacher_id) REFERENCES teachers(teacher_id)  -- 外键关联到教师表的教师ID
59. );
61. -- 插入课程数据
62. INSERT INTO courses (course_id, course_name, teacher_id) VALUES (100, '英语', 1);
63. INSERT INTO courses (course_id, course_name, teacher_id) VALUES (101, '语文', 2);
64. INSERT INTO courses (course_id, course_name, teacher_id) VALUES (102, '数学', 3);
65. INSERT INTO courses (course_id, course_name, teacher_id) VALUES (103, '生物', 4);
66. INSERT INTO courses (course_id, course_name, teacher_id) VALUES (104, '物理', 5);
67. INSERT INTO courses (course_id, course_name, teacher_id) VALUES (105, '化学', 6);
69. -- 创建成绩表
70. CREATE TABLE scores (
71. student_id INT,  -- 学生ID
72. course_id INT,  -- 课程ID
73. score INT,  -- 分数
74. FOREIGN KEY (student_id) REFERENCES students(student_id),  -- 外键关联到学生表的学生ID
75. FOREIGN KEY (course_id) REFERENCES courses(course_id)  -- 外键关联到课程表的课程ID
76. );
78. -- 插入成绩数据
79. INSERT INTO scores (student_id, course_id, score) VALUES (1, 1, 92);
80. INSERT INTO scores (student_id, course_id, score) VALUES (2, 1, 8);
81. INSERT INTO scores (student_id, course_id, score) VALUES (3, 1, 5);
82. INSERT INTO scores (student_id, course_id, score) VALUES (4, 1, 0);
83. INSERT INTO scores (student_id, course_id, score) VALUES (5, 1, 8);
84. INSERT INTO scores (student_id, course_id, score) VALUES (6, 1, 7);
86. INSERT INTO scores (student_id, course_id, score) VALUES (1, 2, 100);
87. INSERT INTO scores (student_id, course_id, score) VALUES (2, 2, 8);
88. INSERT INTO scores (student_id, course_id, score) VALUES (3, 2, 5);
89. INSERT INTO scores (student_id, course_id, score) VALUES (4, 2, 2);
90. INSERT INTO scores (student_id, course_id, score) VALUES (5, 2, 8);
91. INSERT INTO scores (student_id, course_id, score) VALUES (6, 2, 7);
93. INSERT INTO scores (student_id, course_id, score) VALUES (1, 1, 100);
94. INSERT INTO scores (student_id, course_id, score) VALUES (2, 2, 1);
95. INSERT INTO scores (student_id, course_id, score) VALUES (3, 3, 7);
96. INSERT INTO scores (student_id, course_id, score) VALUES (4, 4, 10);
97. INSERT INTO scores (student_id, course_id, score) VALUES (5, 5, 10);
98. INSERT INTO scores (student_id, course_id, score) VALUES (6, 6, 60);
100. INSERT INTO scores (student_id, course_id, score) VALUES (1, 1, 100);
101. INSERT INTO scores (student_id, course_id, score) VALUES (2, 2, 22);
102. INSERT INTO scores (student_id, course_id, score) VALUES (3, 3, 71);
103. INSERT INTO scores (student_id, course_id, score) VALUES (4, 4, 20);
104. INSERT INTO scores (student_id, course_id, score) VALUES (5, 5, 30);
105. INSERT INTO scores (student_id, course_id, score) VALUES (6, 6, 40);
107. INSERT INTO scores (student_id, course_id, score) VALUES (1, 1, 100);
108. INSERT INTO scores (student_id, course_id, score) VALUES (2, 2, 14);
109. INSERT INTO scores (student_id, course_id, score) VALUES (3, 3, 72);
110. INSERT INTO scores (student_id, course_id, score) VALUES (4, 4, 11);
111. INSERT INTO scores (student_id, course_id, score) VALUES (5, 5, 11);
112. INSERT INTO scores (student_id, course_id, score) VALUES (6, 6, 62);
114. INSERT INTO scores (student_id, course_id, score) VALUES (1, 1, 100);
115. INSERT INTO scores (student_id, course_id, score) VALUES (2, 2, 12);
116. INSERT INTO scores (student_id, course_id, score) VALUES (3, 3, 73);
117. INSERT INTO scores (student_id, course_id, score) VALUES (4, 4, 14);
118. INSERT INTO scores (student_id, course_id, score) VALUES (5, 5, 15);
119. INSERT INTO scores (student_id, course_id, score) VALUES (6, 6, 66);
121. -- 3. 创建索引
122. -- 为学生表的学生ID创建索引
123. CREATE INDEX idx_student_id ON students (student_id);
125. -- 为课程表的课程ID创建索引
126. CREATE INDEX idx_course_id ON courses (course_id);
128. -- 为成绩表的学生ID和课程ID创建联合索引
129. CREATE INDEX idx_student_course ON scores (student_id, course_id);
131. -- 4. 创建视图
132. CREATE VIEW class_scores AS   
133. SELECT     
134.     c.class_id,     
135.     c.class_name,     
136.     COUNT(s.student_id) AS student_count,     
137.     AVG(sc.score) AS average_score   
138. FROM     
139.     classes c   
140. JOIN     
141.     students s ON c.class_id = s.class_id   
142. JOIN
143.     scores sc ON s.student_id = sc.student_id
144. GROUP BY     
145.     c.class_id, c.class_name;
147. CREATE VIEW teacher_courses AS  
148. SELECT   
149.     t.teacher_id,   
150.     t.teacher_name,   
151.     COUNT(c.course_id) AS course_count  
152. FROM   
153.     teachers t  
154. JOIN   
155.     courses c ON t.teacher_id = c.teacher_id  
156. GROUP BY   
157.     t.teacher_id, t.teacher_name;
160. -- 5. 创建触发器
162. DELIMITER //  
163. CREATE TRIGGER check_student_class_id_after_insert  
164. AFTER INSERT ON students  
165. FOR EACH ROW  
166. BEGIN  
167.     IF NEW.class_id IS NULL OR NEW.class_id = 0 THEN  
168.         SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '必须为新学生分配班级ID';  
169.     END IF;  
170. END;  
171. //  
172. DELIMITER ;
174. DELIMITER //  
175. CREATE TRIGGER check_course_name_length_before_update  
176. BEFORE UPDATE ON courses  
177. FOR EACH ROW  
178. BEGIN  
179.     IF NEW.course_name IS NOT NULL AND LENGTH(NEW.course_name) > 50 THEN  
180.         SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '课程名称长度不能超过50个字符';  
181.     END IF;  
182. END;  
183. //  
184. DELIMITER ;
186. DELIMITER //  
187. CREATE TRIGGER check_student_scores_after_delete  
188. AFTER DELETE ON students  
189. FOR EACH ROW  
190. BEGIN  
191.     DECLARE score_count INT;  
192.     SELECT COUNT(*) INTO score_count FROM scores WHERE student_id = OLD.student_id;  
193.     IF score_count > 0 THEN  
194.         SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '不能删除有分数记录的学生';  
195.     END IF;  
196. END;  
197. //  
198. DELIMITER ;
200. -- 6. 创建存储函数
201. -- 存储过程1 - 添加学生
202. DELIMITER //
204. CREATE PROCEDURE add_student (
205.     IN p_student_id INT,
206.     IN p_name VARCHAR(50),
207.     IN p_class_id INT,
208.     IN p_age INT
209. )
210. BEGIN
211.     INSERT INTO students (student_id, name, class_id, age) VALUES (p_student_id, p_name, p_class_id, p_age);
212. END //
214. DELIMITER ;
216. -- 存储过程2 - 添加课程
217. DELIMITER //
219. CREATE PROCEDURE add_course (
220.     IN p_course_id INT,
221.     IN p_course_name VARCHAR(50),
222.     IN p_teacher_id INT
223. )
224. BEGIN
225.     INSERT INTO courses (course_id, course_name, teacher_id) VALUES (p_course_id, p_course_name, p_teacher_id);
226. END //
228. DELIMITER ;
230. -- 存储过程3 - 添加成绩
231. DELIMITER //
233. CREATE PROCEDURE add_score (
234.     IN p_student_id INT,
235.     IN p_course_id INT,
236.     IN p_score INT
237. )
238. BEGIN
239.     INSERT INTO scores (student_id, course_id, score) VALUES (p_student_id, p_course_id, p_score);
240. END //
242. DELIMITER ;
244. -- 数据更新
245. -- 更新学生的姓名
246. UPDATE students SET name = '新名字' WHERE student_id = 1;
248. -- 更新学生的班级
249. UPDATE students SET class_id = 1 WHERE student_id = 2;
251. -- 更新学生的年龄
252. UPDATE students SET age = 21 WHERE student_id = 3;
254. -- 更新课程的名称
255. UPDATE courses SET course_name = '历史' WHERE course_id = 100;
257. -- 更新课程的教师
258. UPDATE courses SET teacher_id = 2 WHERE course_id = 101;
260. -- 更新成绩
261. UPDATE scores SET score = 90 WHERE student_id = 1 AND course_id = 100;
264. -- 查询特定学生的信息：
265. SELECT * FROM students WHERE student_id = 1;
267. --  查询特定课程的信息：
268. SELECT * FROM courses WHERE course_id = 5;
270. -- 查询特定学生在特定课程上的成绩：
271. SELECT scores.score   
272. FROM scores   
273. JOIN students ON scores.student_id = students.student_id   
274. JOIN courses ON scores.course_id = courses.course_id   
275. WHERE students.student_id = 1 AND courses.course_id = 5;
277. -- 查询某个班级的所有学生信息：
278. SELECT * FROM students WHERE class_id = 1;
280. -- 查询某个教师的所有课程信息：
281. SELECT courses.* FROM courses JOIN teachers ON courses.teacher_id = teachers.teacher_id WHERE teachers.teacher_name = '刘老师';

复制代码

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。