学生信息管理系统
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岁之间。
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;-- 最后再讲分隔符改回;
delimiter$$
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 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);
存储过程
DELIMITER $$
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
-- 如果学生名字以张李开头
ELSEIF LEFT(studentName, 1) IN (张, 李) THEN
ELSE
-- 插入学生信息到 students 表中，其他字段随机生成
INSERT INTO students (student_name, age, gender)
VALUES (studentName, age, gender);
END IF;
END $$
DELIMITER ;
call insert_student(张三,18,男);-- 输入的参数
ER图
模型图
Ongwu博客 版权声明：以上内容未经允许不得转载！授权事宜或对内容有异议或投诉，请联系站长，将尽快回复您，谢谢合作！