社团成员信息系统

ER实体关系图与数据库模型

DDL

sql">CREATE TABLE `club` (
  `club_id` int(11) NOT NULL AUTO_INCREMENT,
  `club_name` varchar(100) NOT NULL,
  `president_name` varchar(50) DEFAULT NULL,
  `foundation_date` date DEFAULT NULL,
  `description` text,
  PRIMARY KEY (`club_id`),
  KEY `president_name` (`president_name`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8;

CREATE TABLE `course` (
  `course_id` int(11) NOT NULL AUTO_INCREMENT,
  `course_name` varchar(100) NOT NULL,
  `room_number` int(11) NOT NULL,
  `teacher_name` varchar(50) DEFAULT NULL,
  `credit_hours` int(11) NOT NULL,
  `description` text,
  PRIMARY KEY (`course_id`),
  KEY `room_number` (`room_number`),
  CONSTRAINT `course_ibfk_1` FOREIGN KEY (`room_number`) REFERENCES `room` (`room_number`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

CREATE TABLE `date_student` (
  `student_id` int(11) NOT NULL,
  `in_date` date NOT NULL,
  `out_date` date DEFAULT NULL,
  KEY `student_id` (`student_id`),
  CONSTRAINT `date_student_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `student` (`student_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `grade` (
  `grade_id` int(11) NOT NULL AUTO_INCREMENT,
  `student_id` int(11) NOT NULL,
  `course_id` int(11) NOT NULL,
  `score` decimal(5,2) DEFAULT NULL,
  PRIMARY KEY (`grade_id`),
  KEY `course_id` (`course_id`),
  KEY `student_id` (`student_id`),
  CONSTRAINT `grade_ibfk_1` FOREIGN KEY (`course_id`) REFERENCES `course` (`course_id`),
  CONSTRAINT `grade_ibfk_2` FOREIGN KEY (`student_id`) REFERENCES `student` (`student_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

CREATE TABLE `room` (
  `room_id` int(11) NOT NULL AUTO_INCREMENT,
  `room_number` int(11) NOT NULL,
  `capacity` int(11) NOT NULL,
  `building` varchar(50) DEFAULT NULL,
  `floor` int(11) DEFAULT NULL,
  PRIMARY KEY (`room_id`),
  KEY `room_number` (`room_number`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

CREATE TABLE `student` (
  `student_id` int(11) NOT NULL,
  `name` varchar(50) NOT NULL,
  `age` int(11) DEFAULT NULL,
  `gender` enum('Male','Female','Other') DEFAULT NULL,
  `address` varchar(100) DEFAULT NULL,
  `club_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`student_id`),
  KEY `name` (`name`),
  KEY `student_id` (`student_id`),
  KEY `club_id` (`club_id`),
  CONSTRAINT `student_ibfk_1` FOREIGN KEY (`club_id`) REFERENCES `club` (`club_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DML

sql">INSERT INTO `club` VALUES ('1', '编程俱乐部', 'Alice', '2022-09-01', '学习编程和算法');
INSERT INTO `club` VALUES ('2', '摄影爱好者', 'Bob', '2021-10-15', '交流摄影技巧和作品');
INSERT INTO `club` VALUES ('3', '数学研究会', 'Charlie', '2023-01-01', '深入探索数学之美');
INSERT INTO `club` VALUES ('4', '环境保护协会', 'Diana', '2022-05-20', '倡导环保意识和行动');
INSERT INTO `club` VALUES ('5', '音乐社', 'Eve', '2021-03-10', '分享音乐，组织演出');

INSERT INTO `course` VALUES ('1', 'Math ', '101', 'Prof. Smith', '3', 'Intro to Calculus');
INSERT INTO `course` VALUES ('2', 'English ', '202', 'Prof. Johnson', '4', 'Advanced Composition');
INSERT INTO `course` VALUES ('3', 'History ', '303', 'Prof. Davis', '3', 'World History');
INSERT INTO `course` VALUES ('4', 'Science ', '404', 'Prof. Anderson', '4', 'Biology for Non-Majors');
INSERT INTO `course` VALUES ('5', 'Art ', '505', 'Prof. Thompson', '2', 'Art Appreciation');

INSERT INTO `date_student` VALUES ('1', '2023-09-01', '2024-06-26');
INSERT INTO `date_student` VALUES ('2', '2023-08-15', '2024-06-20');
INSERT INTO `date_student` VALUES ('3', '2023-09-10', '2024-06-09');
INSERT INTO `date_student` VALUES ('4', '2023-08-20', '2024-06-13');
INSERT INTO `date_student` VALUES ('5', '2023-09-05', '2024-06-03');

INSERT INTO `grade` VALUES ('1', '1', '1', '90.50');
INSERT INTO `grade` VALUES ('2', '2', '2', '85.75');
INSERT INTO `grade` VALUES ('3', '3', '3', '92.00');
INSERT INTO `grade` VALUES ('4', '4', '1', '88.25');
INSERT INTO `grade` VALUES ('5', '5', '4', '95.00');

INSERT INTO `room` VALUES ('1', '101', '30', 'Building A', '1');
INSERT INTO `room` VALUES ('2', '202', '40', 'Building B', '2');
INSERT INTO `room` VALUES ('3', '303', '25', 'Building C', '1');
INSERT INTO `room` VALUES ('4', '404', '35', 'Building A', '3');
INSERT INTO `room` VALUES ('5', '505', '50', 'Building B', '2');

INSERT INTO `student` VALUES ('1', '1', 'Alice', '20', 'Female', '123 Main St', null);
INSERT INTO `student` VALUES ('2', '2', 'Bob', '21', 'Male', '456 Elm St', null);
INSERT INTO `student` VALUES ('3', '3', 'Charlie', '19', 'Male', '789 Oak St', null);
INSERT INTO `student` VALUES ('4', '4', 'Diana', '20', 'Female', '321 Pine St', null);
INSERT INTO `student` VALUES ('5', '5', 'Eve', '22', 'Female', '654 Maple St', null);

简单查询

sql">-- 简单查询1（查询所有同学的性别）
SELECT `name`,gender FROM student;

sql">-- 简单查询2（查询所有同学总成绩并且按照降序排序）
SELECT student_id,sum(score) sums FROM grade GROUP BY student_id ORDER BY sums DESC;

sql">-- 简单查询3（查询所有在2楼的教室）
SELECT room_number, `floor` FROM room WHERE floor=2;

复杂查询

sql">复杂查询1（查询每个学生对应科目的成绩）
SELECT s.name,g.course_id,g.score FROM student s JOIN grade g ON s.student_id=g.student_id;

sql">-- 复杂查询2（查询所有同学总成绩并且按照降序排序，并显示同学名称）
SELECT s.name,t.student_id,t.sums FROM student s JOIN (SELECT student_id,sum(score) sums FROM grade
 GROUP BY student_id ORDER BY sums DESC) tON t.student_id=s.student_id ORDER BY sums DESC;

sql">-- 简单查询3（查询所有在2楼的教室）
SELECT room_number, `floor` FROM room WHERE floor=2;

触发器

插入触发器

sql">-- 插入触发器 (club 表)
-- 当向 club 表插入数据时，更新 student 表中的相关记录。
DROP TRIGGER IF EXISTS `trg_insert_club`;
DELIMITER //
CREATE TRIGGER `trg_insert_club` AFTER INSERT ON `club` FOR EACH ROW BEGIN
    UPDATE `student`
    SET `club_id` = NEW.club_id
    WHERE `name` = NEW.president_name;
END
//
DELIMITER ;

测试语句

sql">-- 插入一个新的俱乐部记录
INSERT INTO `student`(student_id,name) VALUES (14,'zxc');
INSERT INTO `club` VALUES (15, '舞蹈社', 'zxc', '2023-02-14', '学习各种舞蹈');


-- 检查学生表中是否更新了 Frank 的 club_id
SELECT * FROM `student` WHERE `name` = 'zxc';

更新触发器

sql">-- 更新触发器 (course 表)
-- 当更新 course 表中的 room_number 时，更新 room 表中的相关记录。
DELIMITER //
CREATE TRIGGER `trg_update_course`
AFTER UPDATE ON `course`
FOR EACH ROW
BEGIN    
      UPDATE `room`   
      SET `room_number` = NEW.room_number    
      WHERE `room_number` = 
OLD.room_number;
END //
DELIMITER ;

测试语句

sql">-- 测试语句：
-- 测试更新触发器
-- 更新课程表中的一条记录
UPDATE `course`SET `room_number` = '303’
WHERE `course_id` = '1’;
-- 检查房间表中是否更新了相应的 room_number
SELECT * FROM `room` 
WHERE `room_number` = '303';

删除触发器

sql">-- 删除触发器 (student 表)
-- 当删除 student 表中的记录时，删除 date_student 表中的相关记录。
DELIMITER //
CREATE TRIGGER `trg_delete_student`
BEFORE DELETE ON `student`
FOR EACH ROW
BEGIN 
    DELETE FROM `date_student`    
    WHERE `student_id` = OLD.student_id;
END //
DELIMITER;

测试语句

sql">-- 删除学生表中的一条记录
UPDATE `student`
SET `club_id` = NULL
WHERE `student_id` = 1;
DELETE  FROM `grade`
WHERE `student_id` = '1';

DELETE  FROM `student`
WHERE `student_id` = '1';

-- 检查 date_student 表中是否删除了相应的记录
SELECT * FROM `date_student` WHERE `student_id` = '1';

存储过程

存储过程 (update_student_club)

-- 更新学生的俱乐部信息，涉及 student 和 club 表。

sql">DELIMITER //

CREATE PROCEDURE `update_student_club`(
    IN p_student_id INT,
    IN p_club_id INT,
    IN p_student_name VARCHAR(255)
)
BEGIN
    UPDATE `student`
    SET `club_id` = p_club_id
    WHERE `student_id` = p_student_id;

    UPDATE `club`
    SET `president_name` = p_student_name
    WHERE `club_id` = p_club_id;
END //

DELIMITER ;

测试语句

sql">-- 测试存储过程
-- 假设 student 表有以下数据
INSERT INTO `student` 
(`student_id`, `name`, `club_id`)
 VALUES (7, '张三', NULL);
-- 假设 club 表有以下数据
INSERT INTO `club` 
(`club_id`, `club_name`, `president_name`)
 VALUES (7, '篮球俱乐部', NULL);
-- 调用存储过程
CALL `update_student_club`(7,7, '张三’);
-- 检查更新后的结果
SELECT * FROM `student` 
WHERE `student_id` = 7;
SELECT * FROM `club` 
WHERE `club_id` = 7;