学生信息管理系统_业界新闻

发布时间:2024-07-13 20:31

阅读量:1

DDL和DML

-- 创建学生表 CREATE TABLE students (     student_id INT PRIMARY KEY AUTO_INCREMENT,     name VARCHAR(50),     age INT,     gender VARCHAR(10) );  -- 创建课程表 CREATE TABLE courses (     course_id INT PRIMARY KEY AUTO_INCREMENT,     course_name VARCHAR(50) );  -- 创建教师表 CREATE TABLE teachers (     teacher_id INT PRIMARY KEY AUTO_INCREMENT,     name VARCHAR(50),     department VARCHAR(50) );  -- 创建成绩表 CREATE TABLE grades (     grade_id INT PRIMARY KEY AUTO_INCREMENT,     student_id INT,     course_id INT,     grade DECIMAL(5, 2),     FOREIGN KEY (student_id) REFERENCES students(student_id),     FOREIGN KEY (course_id) REFERENCES courses(course_id) );  -- 创建授课表 CREATE TABLE teachings (     teaching_id INT PRIMARY KEY AUTO_INCREMENT,     teacher_id INT,     course_id INT,     FOREIGN KEY (teacher_id) REFERENCES teachers(teacher_id),     FOREIGN KEY (course_id) REFERENCES courses(course_id) );  -- 插入学生数据 INSERT INTO students (name, age, gender) VALUES ('张三', 18, '男'),        ('李四', 19, '女'),        ('王五', 20, '男');  -- 插入课程数据 INSERT INTO courses (course_name) VALUES ('数学'),        ('英语'),        ('物理');  -- 插入教师数据 INSERT INTO teachers (name, department) VALUES ('赵老师', '数学系'),        ('钱老师', '英语系'),        ('孙老师', '物理系');  -- 插入成绩数据 INSERT INTO grades (student_id, course_id, grade) VALUES (1, 1, 85.5),        (1, 2, 78.0),        (2, 1, 90.0),        (2, 2, 88.5),        (3, 1, 75.0),        (3, 3, 80.0);  -- 插入授课数据 INSERT INTO teachings (teacher_id, course_id) VALUES (1, 1),        (2, 2),        (3, 3);

触发器

1.当插入学生记录时，自动将其年龄字段的值限制在19岁至30岁之间

-- 当插入新学生记录时，自动将其年龄字段的值限制在19至30岁之间。 delimiter$$-- 修改分隔符从;改为$$这样在触发器中;就不会阻断程序了 CREATE TRIGGER validate_age_insert-- 创建触发器 BEFORE INSERT ON students  FOR EACH ROW-- 每行都要触发这个触发器 begin-- 开始触发器 	if new.age < 19 or new.age > 30 THEN 		SIGNAL SQLSTATE '45000' SET message_text='年龄必须在 19 至 30 岁之间'; 	END IF; END;-- 由于我已将;改为$$所以这里的分号就不会代表结束 $$-- 结束了 delimiter;-- 最后再讲分隔符改回;

2. 当删除学生记录时，同时删除其在成绩表中的相关记录

-- 当删除学生记录时，同时删除其在成绩表中的相关记录 delimiter$$ -- 修改分隔符从;改为$$这样在触发器中;就不会阻断程序了 CREATE TRIGGER delete_student_scores -- 创建触发器 BEFORE DELETE ON students  FOR EACH ROW-- 每行都要触发这个触发器 begin-- 开始触发器 	DELETE FROM grades WHERE student_id = OLD.student_id; 	-- delete from 表名{where 条件} END;-- 由于我已将;改为$$所以这里的分号就不会代表结束 $$-- 结束了 delimiter;-- 最后再讲分隔符改回;

3.当教师id发生变更时，显示报错

-- 当教师id发生变更时，显示报错 delimiter$$ CREATE TRIGGER check_teacher_id_change BEFORE UPDATE ON teachers   FOR EACH ROW begin 		IF (OLD.teacher_id!= NEW.teacher_id) THEN  		SIGNAL SQLSTATE '45000' SET message_text='教师 ID 不允许变更！'; 	END IF; END; $$ delimiter;

简单查询

1.查询学生信息，仅显示学生的姓名与年龄，用中文显示列名。

#查询学生信息，仅显示学生的姓名与年龄，用中文显示列名。 select studentname as'姓名',age'年龄' from students;

2.根据课程进行模糊查询，模糊查询需要可以走索引，需要给出explain语句。

# 根据课程进行模糊查询，模糊查询需要可以走索引，需要给出explain语句。 EXPLAIN SELECT * FROM courses WHERE course_name LIKE '数%';

3.统计学生成绩信息，查询所有学生成绩的科目总分，并将科目总分进行倒序排列。

# 统计学生成绩信息，查询所有学生成绩的科目总分，并将科目总分进行倒序排列。 select student_id as '学生id',sum(grade) as '科目总分'  from grades  group by student_id order by sum(grade) desc;

多表联合查询

1.查询学生的基本信息，成绩信息

#查询学生的基本信息，成绩信息 SELECT u.student_id,u.studentname,u.age, ug.grade_id,ug.grade from students u join grades ug on u.student_id=ug.student_id;

2.查询学生成绩中成绩最高的科目

#查询学生成绩中成绩最高的科目 select course_name'成绩最高的科目'from grades g INNER JOIN courses pn on g.course_id=pn.course_id where grade=( select grade from grades GROUP BY grade ORDER BY max(grade) desc limit 1);

3.查询成绩总分最高的学生，并查询学生的全部信息与各科成绩。

#查询成绩总分最高的学生，并查询学生的全部信息与各科成绩。 select *  from students  JOIN grades gs on students.student_id=gs.student_id WHERE gs.student_id=(SELECT student_id from grades GROUP BY student_id ORDER BY sum(grade) desc LIMIT 1);

存储过程

#一条添加语句的存储过程，要求传入的3个参数分别为studentName,age,gender，其它数据随机，输入的数据不允许为空，且studentName的名称不允许以张李开头。 DELIMITER $$ -- 创建名为 insert_student 的存储过程 CREATE PROCEDURE insert_student(     IN studentName VARCHAR(50),-- 输入参数：学生姓名     IN age INT,-- 输入参数：学生年龄     IN gender ENUM('男', '女')-- 输入参数：学生性别 ) BEGIN     -- 如果 studentName、age、gender为空     IF studentName IS NULL OR age IS NULL OR gender IS NULL THEN         SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '输入的数据不允许为空';-- 则输出的数据     -- 如果学生名字以张李开头     ELSEIF LEFT(studentName, 1) IN ('张', '李') THEN         SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '学生姓名不允许以张李开头';-- 则输出的数据     ELSE          -- 插入学生信息到 students 表中，其他字段随机生成         INSERT INTO students (student_name, age, gender)         VALUES (studentName, age, gender);     END IF; END $$  DELIMITER ;  call insert_student('张三',18,'男');-- 输入的参数