数据库学习用的示例脚本

vonlinee

已于 2024-09-27 14:44:09 修改

阅读量157

点赞数 2

分类专栏： MySQL 文章标签：数据库 sql

于 2024-09-22 22:56:00 首次发布

本文链接：https://blog.csdn.net/qq_40926260/article/details/142445464

版权

MySQL 专栏收录该内容

10 篇文章 0 订阅

订阅专栏

这里记录一些平时学习数据库原理会使用到的数据库表

学生信息管理系统数据库表

# 建数据库
CREATE DATABASE student_management CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

################################
# 表结构
################################

USE student_management;

-- student_management.class_courses definition
DROP TABLE IF EXISTS `class_courses`;
CREATE TABLE `class_courses` (
  `class_course_id` int NOT NULL AUTO_INCREMENT COMMENT '班级课程ID，主键',
  `class_id` int NOT NULL COMMENT '班级ID',
  `course_id` int NOT NULL COMMENT '课程ID',
  PRIMARY KEY (`class_course_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='班级课程表，存储班级开设的课程信息';

-- student_management.classes definition
DROP TABLE IF EXISTS `classes`;
CREATE TABLE `classes` (
  `class_id` int NOT NULL AUTO_INCREMENT COMMENT '班级ID，主键',
  `class_name` varchar(50) COLLATE utf8mb4_general_ci NOT NULL COMMENT '班级名称，如：一年级一班',
  `grade` int NOT NULL COMMENT '年级',
  `head_teacher_id` int DEFAULT NULL COMMENT '班主任教师ID',
  `class_size` int DEFAULT NULL COMMENT '班级人数',
  `school_id` int DEFAULT NULL COMMENT '所属学校ID',
  PRIMARY KEY (`class_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='班级表，存储班级信息';

-- student_management.courses definition
DROP TABLE IF EXISTS `courses`;
CREATE TABLE `courses` (
  `course_id` int NOT NULL AUTO_INCREMENT COMMENT '课程ID，主键',
  `course_name` varchar(100) COLLATE utf8mb4_general_ci NOT NULL COMMENT '课程名称',
  `teacher_id` int DEFAULT NULL COMMENT '授课教师ID',
  `course_credits` int DEFAULT NULL COMMENT '课程学分',
  `description` text COLLATE utf8mb4_general_ci COMMENT '课程描述',
  PRIMARY KEY (`course_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='课程表，存储课程信息';

-- student_management.grades definition
DROP TABLE IF EXISTS `grades`;
CREATE TABLE `grades` (
  `grade_id` int NOT NULL AUTO_INCREMENT COMMENT '成绩ID，主键',
  `student_id` int NOT NULL COMMENT '学生ID',
  `course_id` int NOT NULL COMMENT '课程ID',
  `score` decimal(5,2) DEFAULT NULL COMMENT '成绩',
  `exam_date` date DEFAULT NULL COMMENT '考试日期',
  `semester_id` int DEFAULT NULL COMMENT '学期ID',
  PRIMARY KEY (`grade_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='成绩表，存储学生的课程成绩';

-- student_management.schools definition
DROP TABLE IF EXISTS `schools`;
CREATE TABLE `schools` (
  `school_id` int NOT NULL AUTO_INCREMENT COMMENT '学校ID，主键',
  `school_name` varchar(200) COLLATE utf8mb4_general_ci NOT NULL COMMENT '学校名称',
  `address` varchar(300) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '学校地址',
  `phone_number` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '联系电话',
  `email` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '电子邮件',
  `founded_year` year DEFAULT NULL COMMENT '成立年份',
  PRIMARY KEY (`school_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='学校表，存储学校的基本信息';

-- student_management.semesters definition
DROP TABLE IF EXISTS `semesters`;
CREATE TABLE `semesters` (
  `semester_id` int NOT NULL AUTO_INCREMENT COMMENT '学期ID，主键',
  `semester_name` varchar(50) COLLATE utf8mb4_general_ci NOT NULL COMMENT '学期名称，如：2023-2024学年第一学期',
  `start_date` date NOT NULL COMMENT '学期开始日期',
  `end_date` date NOT NULL COMMENT '学期结束日期',
  PRIMARY KEY (`semester_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='学期表，记录学期信息';

-- student_management.students definition
DROP TABLE IF EXISTS `students`;
CREATE TABLE `students` (
  `student_id` int NOT NULL AUTO_INCREMENT COMMENT '学生ID，主键',
  `student_name` varchar(100) COLLATE utf8mb4_general_ci NOT NULL COMMENT '学生姓名',
  `gender` enum('M','F') COLLATE utf8mb4_general_ci NOT NULL COMMENT '性别：M-男，F-女',
  `birth_date` date DEFAULT NULL COMMENT '出生日期',
  `class_id` int DEFAULT NULL COMMENT '班级ID',
  `phone_number` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '联系电话',
  `email` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '电子邮件',
  `address` varchar(300) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '住址',
  `enrollment_year` year DEFAULT NULL COMMENT '入学年份',
  `school_id` int DEFAULT NULL COMMENT '所属学校ID',
  PRIMARY KEY (`student_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='学生表，存储学生的基本信息';

-- student_management.teachers definition
DROP TABLE IF EXISTS `teachers`;
CREATE TABLE `teachers` (
  `teacher_id` int NOT NULL AUTO_INCREMENT COMMENT '教师ID，主键',
  `teacher_name` varchar(100) COLLATE utf8mb4_general_ci NOT NULL COMMENT '教师姓名',
  `phone_number` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '联系电话',
  `email` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '电子邮件',
  `title` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '职称',
  `field_of_expertise` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '专业领域',
  `school_id` int DEFAULT NULL COMMENT '所属学校ID',
  PRIMARY KEY (`teacher_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='教师表，存储教师的基本信息';


-- 数据初始化存储过程
-- 学校信息
DELIMITER $$
CREATE PROCEDURE populate_schools()
BEGIN
    INSERT INTO schools (school_name, address, phone_number, email, founded_year)
    VALUES 
    ('Springfield High School', '123 Main St, Springfield', '123-456-7890', 'info@springfield.edu', 1985),
    ('Riverside Academy', '456 Riverside Dr, Riverside', '234-567-8901', 'contact@riverside.edu', 1990),
    ('Downtown College', '789 Downtown Ave, Downtown', '345-678-9012', 'admin@downtown.edu', 2000);
END$$
DELIMITER ;

-- 班级信息
DELIMITER $$
CREATE PROCEDURE populate_classes()
BEGIN
    INSERT INTO classes (class_name, grade, head_teacher_id, class_size, school_id)
    VALUES
    ('一年级一班', 1, 1, 30, 1),
    ('一年级二班', 1, 2, 35, 2),
    ('二年级一班', 2, 3, 32, 1),
    ('三年级一班', 3, 4, 28, 2),
    ('四年级一班', 4, 5, 30, 3);
END$$
DELIMITER ;

-- 教师信息
DELIMITER $$
CREATE PROCEDURE populate_teachers()
BEGIN
    INSERT INTO teachers (teacher_name, phone_number, email, title, field_of_expertise, school_id)
    VALUES
    ('Alice Johnson', '123-456-7890', 'alice.johnson@springfield.edu', 'Professor', 'Mathematics', 1),
    ('Bob Smith', '234-567-8901', 'bob.smith@riverside.edu', 'Associate Professor', 'Physics', 2),
    ('Carol Williams', '345-678-9012', 'carol.williams@downtown.edu', 'Lecturer', 'Computer Science', 3),
    ('David Brown', '456-789-0123', 'david.brown@springfield.edu', 'Professor', 'Biology', 1),
    ('Eva Green', '567-890-1234', 'eva.green@riverside.edu', 'Lecturer', 'History', 2);
END$$
DELIMITER ;

-- 课程信息
DELIMITER $$
CREATE PROCEDURE populate_courses()
BEGIN
    INSERT INTO courses (course_name, teacher_id, course_credits, description)
    VALUES
    ('Mathematics', 1, 4, 'An introduction to mathematics covering algebra, geometry, and calculus.'),
    ('Physics', 2, 3, 'Basic principles of physics including mechanics and electromagnetism.'),
    ('Computer Science', 3, 3, 'Introduction to computer science including programming and algorithms.'),
    ('Biology', 4, 4, 'Study of living organisms, their structure, function, and evolution.'),
    ('History', 5, 2, 'A survey of world history from ancient times to the modern era.');
END$$
DELIMITER ;

-- 学生信息
DELIMITER $$
CREATE PROCEDURE populate_students()
BEGIN
    INSERT INTO students (student_name, gender, birth_date, class_id, phone_number, email, address, enrollment_year, school_id)
    VALUES
    ('Tom Lee', 'M', '2010-05-21', 1, '123-123-1234', 'tom.lee@student.com', '123 Elm St', 2019, 1),
    ('Jerry Brown', 'M', '2011-07-14', 2, '234-234-2345', 'jerry.brown@student.com', '456 Pine St', 2019, 2),
    ('Lucy Green', 'F', '2010-09-10', 3, '345-345-3456', 'lucy.green@student.com', '789 Oak St', 2018, 1),
    ('Mia White', 'F', '2011-11-25', 4, '456-456-4567', 'mia.white@student.com', '321 Cedar St', 2020, 2),
    ('Sam Black', 'M', '2009-12-19', 5, '567-567-5678', 'sam.black@student.com', '654 Maple St', 2018, 3);
END$$
DELIMITER ;

-- 课程分数信息
DELIMITER $$
CREATE PROCEDURE populate_grades()
BEGIN
    INSERT INTO grades (student_id, course_id, score, exam_date, semester_id)
    VALUES
    (1, 1, 85.50, '2023-01-10', 1),
    (2, 2, 92.00, '2023-01-12', 1),
    (3, 3, 78.75, '2023-01-15', 1),
    (4, 4, 88.00, '2023-01-20', 1);
END$$
DELIMITER ;

-- 填充数据
CALL populate_schools();
CALL populate_classes();
CALL populate_teachers();
CALL populate_courses();
CALL populate_students();
CALL populate_grades();

Sakila 数据库

这个是mysql官方提供的一个示例数据库
下载参考：https://dev.mysql.com/doc/sakila/en/sakila-installation.html，如下图所示

在这里插入图片描述
直接下载即可，下载sakila那个，解压后有3个文件

通过navicat等工具导入，或者通过mysql导入也可以

mysql> SOURCE C:/temp/sakila-db/sakila-schema.sql;
mysql> SOURCE C:/temp/sakila-db/sakila-data.sql;

vonlinee

关注

2
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录