Mysql学习之表结构设计、索引、外键与数据插入

最新推荐文章于 2024-05-22 00:22:59 发布

TT陌人不故

最新推荐文章于 2024-05-22 00:22:59 发布

阅读量450

点赞数 1

分类专栏： Mysql 文章标签： Mysql 数据库表结构索引外键

本文链接：https://blog.csdn.net/m0_37949305/article/details/79212596

版权

Mysql 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

本文介绍了如何设计一个高中学校管理系统的数据库，包括年级、班级、学生和成绩四个主要表的结构，强调了表之间的关联，如年级与班级、班级与学生、学生与成绩的关系，并提供了SQL文件的下载链接，帮助读者理解和实践。

摘要由CSDN通过智能技术生成

需求：

在一个高中里有3个年级，每个年级有n个班级，每个班级有m个学生，

每个学生有学号、姓名、性别，每个学生有不同科目的成绩。

分析：

有年级、班级、学生的基本信息、科目、学生的科目成绩，

年级与班级关联、班级与学生关联、学生与成绩关联、成绩与科目关联

建立年级表、班级表、学生表、成绩表、科目表

sql文件下载链接: https://pan.baidu.com/s/1pMW9ZQV 密码: xev5

创建数据库

推荐使用gui工具（如Navicat for MySQL）直接创建数据库,当然，你也可以直接通过命令行创建数据库

我们先创建数据库名为 school 的数据库并设置编码格式为utf-8，以下是命令行（如用GUI工具可直接操作）

CREATE DATABASE `school` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

创建数据库表

建立完数据库后，我们需要创建数据库表：

年级表 grade、班级表 clazz、学生表 student、科目表 subject 和成绩表 score

创建年级表 grade

DROP TABLE IF EXISTS `grade`;
CREATE TABLE `grade` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `grade_id` int(10) NOT NULL,
  `grade_name` varchar(10) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `grade_id` (`grade_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

创建班级表 clazz

DROP TABLE IF EXISTS `clazz`;
CREATE TABLE `clazz` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `clazz_id` int(10) NOT NULL,
  `grade_id` int(10) NOT NULL,
  `clazz_name` varchar(10) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `clazz_id` (`clazz_id`) USING BTREE,
  KEY `grade_id` (`grade_id`) USING BTREE,
  CONSTRAINT `grade_id` FOREIGN KEY (`grade_id`) REFERENCES `grade` (`grade_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

创建学生表 student

DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `clazz_id` int(10) NOT NULL,
  `stu_id` int(10) NOT NULL,
  `stu_name` varchar(20) NOT NULL,
  `stu_sex` varchar(10) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `stu_id` (`stu_id`) USING BTREE,
  KEY `clazz_id` (`clazz_id`) USING BTREE,
  KEY `stu_name` (`stu_name`) USING BTREE,
  CONSTRAINT `clazz_id` FOREIGN KEY (`clazz_id`) REFERENCES `clazz` (`clazz_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;

创建科目表 subject

DROP TABLE IF EXISTS `subject`;
CREATE TABLE `subject` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `subject_name` varchar(36) NOT NULL,
  `subject_id` int(10) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `stubject_id` (`subject_id`) USING BTREE,
  KEY `subject_name` (`subject_name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

创建成绩表 score

DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
  `id` int(10) NOT NULL,
  `stu_id` int(10) NOT NULL,
  `subject_id` int(10) NOT NULL,
  `score` double(5,2) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `subject_id` (`subject_id`) USING BTREE,
  KEY `stu_id` (`stu_id`) USING BTREE,
  CONSTRAINT `stu_id` FOREIGN KEY (`stu_id`) REFERENCES `student` (`stu_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `subject_id` FOREIGN KEY (`subject_id`) REFERENCES `subject` (`subject_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

这样，我们5张表就建立完成了

插入数据

接下来，我们向表里插入数据：

grade 表

INSERT INTO `grade` VALUES ('1', '1201', '高一');
INSERT INTO `grade` VALUES ('2', '1202', '高二');
INSERT INTO `grade` VALUES ('3', '1203', '高三');

clazz 表

INSERT INTO `clazz` VALUES ('1', '12010001', '1201', '高一1班');
INSERT INTO `clazz` VALUES ('2', '12010002', '1201', '高一2班');
INSERT INTO `clazz` VALUES ('3', '12020001', '1202', '高二1班');
INSERT INTO `clazz` VALUES ('4', '12020002', '1202', '高二2班');
INSERT INTO `clazz` VALUES ('5', '12030001', '1203', '高三1班');
INSERT INTO `clazz` VALUES ('6', '12030002', '1203', '高三2班');

student 表

INSERT INTO `student` VALUES ('1', '12010001', '1201000101', '周树人', '男');
INSERT INTO `student` VALUES ('2', '12010001', '1201000102', '张脑丸', '男');
INSERT INTO `student` VALUES ('3', '12010002', '1201000201', '张钦', '男');
INSERT INTO `student` VALUES ('4', '12010002', '1201000202', '许留仙', '男');
INSERT INTO `student` VALUES ('5', '12020001', '1202000101', '赵小春', '男');
INSERT INTO `student` VALUES ('6', '12020001', '1202000102', '赵钱孙', '男');
INSERT INTO `student` VALUES ('7', '12020002', '1202000201', '童心', '女');
INSERT INTO `student` VALUES ('8', '12020002', '1202000202', '伊万兆', '男');
INSERT INTO `student` VALUES ('9', '12030001', '1203000101', '唐宁', '女');
INSERT INTO `student` VALUES ('10', '12030001', '1203000102', '柯琪', '女');
INSERT INTO `student` VALUES ('11', '12030002', '1203000201', '花旗营', '男');
INSERT INTO `student` VALUES ('12', '12030002', '1203000202', '花岗岩', '男');

subject 表

INSERT INTO `subject` VALUES ('1', '语文', '1');
INSERT INTO `subject` VALUES ('2', '数学', '2');
INSERT INTO `subject` VALUES ('3', '英语', '3');
INSERT INTO `subject` VALUES ('4', '物理', '4');
INSERT INTO `subject` VALUES ('5', '化学', '5');
INSERT INTO `subject` VALUES ('6', '生物', '6');
INSERT INTO `subject` VALUES ('7', '政治', '7');
INSERT INTO `subject` VALUES ('8', '历史', '8');
INSERT INTO `subject` VALUES ('9', '地理', '9');

score 表

INSERT INTO `score` VALUES ('1', '1201000101', '1', '88');
INSERT INTO `score` VALUES ('2', '1201000101', '2', '58');
INSERT INTO `score` VALUES ('3', '1201000101', '3', '83');
INSERT INTO `score` VALUES ('4', '1201000101', '4', '52');
INSERT INTO `score` VALUES ('5', '1201000101', '5', '53');
INSERT INTO `score` VALUES ('6', '1201000101', '6', '55');
INSERT INTO `score` VALUES ('7', '1201000101', '7', '73');
INSERT INTO `score` VALUES ('8', '1201000101', '8', '72');
INSERT INTO `score` VALUES ('9', '1201000101', '9', '59');
INSERT INTO `score` VALUES ('10', '1201000102', '1', '78');
INSERT INTO `score` VALUES ('11', '1201000102', '2', '73');
INSERT INTO `score` VALUES ('12', '1201000102', '3', '85');
INSERT INTO `score` VALUES ('13', '1201000102', '4', '79');
INSERT INTO `score` VALUES ('14', '1201000102', '5', '82');
INSERT INTO `score` VALUES ('15', '1201000102', '6', '83');
INSERT INTO `score` VALUES ('16', '1201000102', '7', '84');
INSERT INTO `score` VALUES ('17', '1201000102', '8', '86');
INSERT INTO `score` VALUES ('18', '1201000102', '9', '69');
INSERT INTO `score` VALUES ('19', '1201000201', '1', '84');
INSERT INTO `score` VALUES ('20', '1201000201', '2', '83');
INSERT INTO `score` VALUES ('21', '1201000201', '3', '72');
INSERT INTO `score` VALUES ('22', '1201000201', '4', '66');
INSERT INTO `score` VALUES ('23', '1201000201', '5', '64');
INSERT INTO `score` VALUES ('24', '1201000201', '6', '84');
INSERT INTO `score` VALUES ('25', '1201000201', '7', '80');
INSERT INTO `score` VALUES ('26', '1201000201', '8', '52');
INSERT INTO `score` VALUES ('27', '1201000201', '9', '58');
INSERT INTO `score` VALUES ('28', '1201000202', '1', '60');
INSERT INTO `score` VALUES ('29', '1201000202', '2', '54');
INSERT INTO `score` VALUES ('30', '1201000202', '3', '68');
INSERT INTO `score` VALUES ('31', '1201000202', '4', '59');
INSERT INTO `score` VALUES ('32', '1201000202', '5', '51');
INSERT INTO `score` VALUES ('33', '1201000202', '6', '71');
INSERT INTO `score` VALUES ('34', '1201000202', '7', '82');
INSERT INTO `score` VALUES ('35', '1201000202', '8', '78');
INSERT INTO `score` VALUES ('36', '1201000202', '9', '59');
INSERT INTO `score` VALUES ('37', '1202000101', '1', '80');
INSERT INTO `score` VALUES ('38', '1202000101', '2', '61');
INSERT INTO `score` VALUES ('39', '1202000101', '3', '81');
INSERT INTO `score` VALUES ('40', '1202000101', '4', '86');
INSERT INTO `score` VALUES ('41', '1202000101', '5', '64');
INSERT INTO `score` VALUES ('42', '1202000101', '6', '69');
INSERT INTO `score` VALUES ('43', '1202000101', '7', '65');
INSERT INTO `score` VALUES ('44', '1202000101', '8', '85');
INSERT INTO `score` VALUES ('45', '1202000101', '9', '64');
INSERT INTO `score` VALUES ('46', '1202000102', '1', '52');
INSERT INTO `score` VALUES ('47', '1202000102', '2', '59');
INSERT INTO `score` VALUES ('48', '1202000102', '3', '75');
INSERT INTO `score` VALUES ('49', '1202000102', '4', '78');
INSERT INTO `score` VALUES ('50', '1202000102', '5', '73');
INSERT INTO `score` VALUES ('51', '1202000102', '6', '85');
INSERT INTO `score` VALUES ('52', '1202000102', '7', '79');
INSERT INTO `score` VALUES ('53', '1202000102', '8', '82');
INSERT INTO `score` VALUES ('54', '1202000102', '9', '83');
INSERT INTO `score` VALUES ('55', '1202000201', '1', '84');
INSERT INTO `score` VALUES ('56', '1202000201', '2', '86');
INSERT INTO `score` VALUES ('57', '1202000201', '3', '69');
INSERT INTO `score` VALUES ('58', '1202000201', '4', '84');
INSERT INTO `score` VALUES ('59', '1202000201', '5', '83');
INSERT INTO `score` VALUES ('60', '1202000201', '6', '72');
INSERT INTO `score` VALUES ('61', '1202000201', '7', '66');
INSERT INTO `score` VALUES ('62', '1202000201', '8', '64');
INSERT INTO `score` VALUES ('63', '1202000201', '9', '84');
INSERT INTO `score` VALUES ('64', '1202000202', '1', '80');
INSERT INTO `score` VALUES ('65', '1202000202', '2', '52');
INSERT INTO `score` VALUES ('66', '1202000202', '3', '58');
INSERT INTO `score` VALUES ('67', '1202000202', '4', '60');
INSERT INTO `score` VALUES ('68', '1202000202', '5', '54');
INSERT INTO `score` VALUES ('69', '1202000202', '6', '68');
INSERT INTO `score` VALUES ('70', '1202000202', '7', '59');
INSERT INTO `score` VALUES ('71', '1202000202', '8', '51');
INSERT INTO `score` VALUES ('72', '1202000202', '9', '68');
INSERT INTO `score` VALUES ('73', '1203000101', '1', '59');
INSERT INTO `score` VALUES ('74', '1203000101', '2', '51');
INSERT INTO `score` VALUES ('75', '1203000101', '3', '71');
INSERT INTO `score` VALUES ('76', '1203000101', '4', '82');
INSERT INTO `score` VALUES ('77', '1203000101', '5', '78');
INSERT INTO `score` VALUES ('78', '1203000101', '6', '59');
INSERT INTO `score` VALUES ('79', '1203000102', '1', '80');
INSERT INTO `score` VALUES ('80', '1203000102', '2', '61');
INSERT INTO `score` VALUES ('81', '1203000102', '3', '81');
INSERT INTO `score` VALUES ('82', '1203000102', '4', '86');
INSERT INTO `score` VALUES ('83', '1203000102', '5', '64');
INSERT INTO `score` VALUES ('84', '1203000102', '6', '69');
INSERT INTO `score` VALUES ('85', '1203000201', '1', '65');
INSERT INTO `score` VALUES ('86', '1203000201', '2', '85');
INSERT INTO `score` VALUES ('87', '1203000201', '3', '64');
INSERT INTO `score` VALUES ('88', '1203000201', '7', '88');
INSERT INTO `score` VALUES ('89', '1203000201', '8', '78');
INSERT INTO `score` VALUES ('90', '1203000201', '9', '89');
INSERT INTO `score` VALUES ('91', '1203000202', '1', '78');
INSERT INTO `score` VALUES ('92', '1203000202', '2', '65');
INSERT INTO `score` VALUES ('93', '1203000202', '3', '88');
INSERT INTO `score` VALUES ('94', '1203000202', '7', '77');
INSERT INTO `score` VALUES ('95', '1203000202', '8', '45');
INSERT INTO `score` VALUES ('96', '1203000202', '9', '12');