mysql练习-学生信息管理系统

文章目录

• 学生信息管理系统
• • 一、分析
  • • 1.1业务分析
    • 1.2分析系统
    • • 1.2.1登录模块
      • 1.2.2学生模块
      • 1.2.3教师模块
      • 1.2.4系统管理人员模块
  • 二、构建模型
  • • 2.1 概念模型：ERP模型
    • 2.2 数据模型：设计表格
    • • 2.2.1 将ERP模型转化为关系模式
      • 2.2.2 设计表格
      • • 2.2.2.1学生表
        • 2.2.2.2教师表
        • 2.2.2.3住宿表
        • 2.2.2.4课程表
        • 2.2.2.5成绩表
        • 2.2.2.6师生关系表
        • 2.2.2.7已选课程表
  • 三、创建表格
  • • 3.1创建学生信息管理系统数据库
    • 3.2创建学生表
    • 3.2创建教师表
    • 3.3创建住宿表
    • 3.4创建课程表
    • 3.5创建成绩表
    • 3.6创建师生关系表
  • 四、插入数据
  • • 4.1插入学生表数据
    • 4.2插入教师表数据
    • 4.3插入住宿表信息
    • 4.4插入课程信息
    • 4.5插入成绩表信息
    • 4.6插入师生关系信息
  • 五、维护数据
  • • 5.1 查询学生所有信息
    • 5.2 查询老师所有信息
    • 5.3 查询选课了电路分析科目的学生信息
    • 5.4 对应科目的老师信息
    • 5.5 修改学生信息
    • 5.6 删除学号为1的学生信息
    • 5.7 学号为2的学生选择数据库应用开发课程
    • 5.8 查询年龄大于20岁的学生信息
    • 5.9 学号为2的学生修改其电话号码

学生信息管理系统

一、分析

1.1业务分析

学生信息管理系统是面向学生和学校教师的一个多功能数据管理平台，学生信息管理是高等教学管理系统中很重要的功能之一，该系统主要满足教师和学生用户需求。

1.2分析系统

系统面向的用户、系统的功能

本系统面向的用户有学校在校学生、教师以及系统管理人员，对于不同的用户，系统所提供给的功能也有所不同，其中对于学生来说，学生可以提供过该系统进行自身数据，成绩与课程以及学生对应老师的查询以及部分自身信息的修改，教师可以通过该系统实现对自身数据查询、学生信息与学生成绩管理等，系统管理人员可以查看与修改系统所有数据。

1.2.1登录模块

通过登录界面进行选择登录用户类型，使用各用户对应的唯一主键与密码信息进行登录系统。该系统未实现注册功能，如需要注册新用户可联系系统管理人员，系统管理人员电话号码于登录界面右下角展示。

1.2.2学生模块

学生用户登录成功后可以执行的功能有：

①查看个人成绩

②选课

③退课

④查看已选课程对应老师信息

⑤查看个人信息

⑥修改密码

⑦退出，返回登录界面

1.2.3教师模块

教师用户登录成功后可以执行的功能有：

①查看个人信息

②录入授课课程学生成绩

③查看成绩单

④修改密码

⑤修改授课课程学生成绩

⑥退出，返回登录界面

1.2.4系统管理人员模块

管理人员登录成功后可对系统所有非管理员信息进行增删查改。

二、构建模型

2.1 概念模型：ERP模型

该E-R模型暂不完整

2.2 数据模型：设计表格

2.2.1 将ERP模型转化为关系模式

按照将E-R图转换为关系模式的规则，将上图所示E-R图转换，得到的关系模式为：学生（学号，姓名，性别，年龄，电话，登录密码）

教师（教师编号，姓名，性别，年龄，职位，电话，登录密码）

住宿（公寓房间编号，具体地址，宿管）

课程（课程编号，课程名，教师编号）

成绩（学生编号，学生姓名，课程编号，课程名，成绩，授课老师）

对上述关系模式进行以下优化。将住宿的实体主键‘房间编号’分别加入到学生和教师中，创建实体集师生关系（教师编号，学号）对应学生与老师的关系模式，可以创建已选课程关系（课程编号，学生编号）对应课程与学生的关系模式将其与成绩表进行关系，所以成绩表优化为（学生编号，课程编号，成绩），成绩可以为空值，添加学生的课程向成绩表中添加学生与课程的主键，成绩设置为空，这样学生所拥有的课程通过成绩表查询。优化后的关系模式为（暂未设计管理员关系模型）：

学生（学号，姓名，性别，年龄，电话，登录密码，公寓房间编号）

教师（教师编号，姓名，性别，年龄，职位，电话，登录密码，公寓房间编号）

住宿（公寓房间编号，具体地址，宿管）

课程（课程编号，课程名，教师编号）

成绩（学生编号，课程编号，成绩）

师生关系（教师编号，学号）

2.2.2 设计表格

2.2.2.1学生表

字段名	数据类型	长度	是否主键	说明
stu_id	int	11	是	学生表主键
stu_name	varchar	20	姓名
stu_gender	varchar	20	性别
stu_age	int	3	年龄
stu_phone	int	20	电话
stu_password	varchar	20	登录密码
live_id	int	11	公寓房间编号

2.2.2.2教师表

字段名	数据类型	长度	是否主键	说明
t_id	int	11	是	教师表主键
t_name	varchar	20	姓名
t_gender	varchar	20	性别
t_age	int	3	年龄
t_position	varchar	20	职位
t_phone	int	20	电话
t_password	varchar	20	登录密码
live_id	int	11	公寓房间编号

2.2.2.3住宿表

字段名	数据类型	长度	是否主键	说明
live_id	int	11	是	公寓表主键
live_address	varchar	50	居住地址
live_manager	varchar	20	宿管

2.2.2.4课程表

字段名	数据类型	长度	是否主键	说明
c_id	int	11	是	课程表主键
c_name	varchar	20	课程名
t_id	varchar	11	教师表主键

2.2.2.5成绩表

字段名	数据类型	长度	是否主键	说明
stu_id	int	11	是	学生表主键
c_id	int	11	课程表主键
sc	varchar	20	成绩

2.2.2.6师生关系表

字段名	数据类型	长度	是否主键	说明
stu_id	int	11	是	学生表主键
t_id	int	11	教师表主键

2.2.2.7已选课程表

字段名	数据类型	长度	是否主键	说明
stu_id	int	11	是	学生表主键
c_id	int	11	课程表主键

三、创建表格

利用sql语句按照上述构建的模型创建表格

3.1创建学生信息管理系统数据库

CREATE DATABASE studentinfosystem CHARACTER SET ‘utf8’;

3.2创建学生表

CREATE TABLE student (

stu_id int(11) NOT NULL AUTO_INCREMENT COMMENT ‘学生表主键’,

stu_name varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,

stu_gender varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,

stu_age int(3) NOT NULL,

stu_phone varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci,

stu_password varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT ‘123456’,

live_id int(11) NULL DEFAULT NULL,

PRIMARY KEY (stu_id)

) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8;

3.2创建教师表

CREATE TABLE teacher (

t_id int(11) NOT NULL AUTO_INCREMENT COMMENT ‘教师表主键’,

t_name varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,

t_gender varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,

t_age int(3) NOT NULL,

t_position varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,

t_phone varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci,

t_password varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT ‘123456’,

live_id int(11) NULL DEFAULT NULL,

PRIMARY KEY (t_id)

) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8;

3.3创建住宿表

CREATE TABLE live (

live_id int(11) NOT NULL AUTO_INCREMENT,

live_address varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,

live_manager varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,

PRIMARY KEY (live_id)

) ENGINE = InnoDB CHARACTER SET = utf8;

3.4创建课程表

CREATE TABLE course (

c_id int(11) NOT NULL AUTO_INCREMENT,

c_name varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,

t_id int(11) NOT NULL,

PRIMARY KEY (c_id)

) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8;

3.5创建成绩表

CREATE TABLE score (

stu_id int(11) NOT NULL,

c_id int(11) NOT NULL,

sc varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL

PRIMARY KEY (stu_id, c_id)

) ENGINE = InnoDB CHARACTER SET = utf8;

3.6创建师生关系表

CREATE TABLE teacher_stu_relation (

t_id int(11) NOT NULL,

stu_id int(11) NULL DEFAULT NULL,

PRIMARY KEY (t_id, stu_id)

) ENGINE = InnoDB CHARACTER SET = utf8;

四、插入数据

4.1插入学生表数据

INSERT INTO student VALUES (1, ‘纳尼’, ‘男’, 20, ‘12345678910’, ‘123456’, 1);

INSERT INTO student VALUES (2, ‘范尼斯特鲁一’, ‘男’, 22, ‘10987654321’, ‘123456’, 1);

INSERT INTO student VALUES (3, ‘索尔斯克亚’, ‘男’, 25, ‘13579246810’, ‘123456’, 2);

INSERT INTO student VALUES (4, ‘弗雷德’, ‘男’, 18, ‘24681013579’, ‘123456’, 3);

4.2插入教师表数据

INSERT INTO teacher VALUES (1, ‘弗格森’, ‘男’, 60, ‘校长’, ‘13791379137’, ‘123456’, 4);

INSERT INTO teacher VALUES (2, ‘穆里尼奥’, ‘男’, 40, ‘教授’, ‘24682468246’, ‘123456’, 4);

INSERT INTO teacher VALUES (3, ‘朗尼克’, ‘男’, 45, ‘副教授’, ‘39713971397’, ‘123456’, 5);

4.3插入住宿表信息

INSERT INTO live VALUES (1, ‘房间1’, ‘宿管人员1’);

INSERT INTO live VALUES (2, ‘房间2’, ‘宿管人员1’);

INSERT INTO live VALUES (3, ‘房间3’, ‘宿管人员2’);

INSERT INTO live VALUES (4, ‘房间4’, ‘宿管人员2’);

INSERT INTO live VALUES (5, ‘房间5’, ‘宿管人员2’);

4.4插入课程信息

INSERT INTO course VALUES (1, ‘JavaSE基础’, 1);

INSERT INTO course VALUES (2, ‘数据库应用’, 1);

INSERT INTO course VALUES (3, ‘数据结构’, 1);

INSERT INTO course VALUES (4, ‘电路分析’, 2);

INSERT INTO course VALUES (5, ‘计算机网络’, 2);

INSERT INTO course VALUES (6, ‘数据库应用开发’, 3);

4.5插入成绩表信息

INSERT INTO score VALUES (1, 1, ‘40’);

INSERT INTO score VALUES (1, 2, ‘50’);

INSERT INTO score VALUES (1, 3, ‘60’);

INSERT INTO score VALUES (1, 4, ‘70’);

INSERT INTO score VALUES (2, 2, ‘65’);

INSERT INTO score VALUES (2, 3, ‘50’);

INSERT INTO score VALUES (2, 4, ‘60’);

INSERT INTO score VALUES (3, 3, ‘70’);

INSERT INTO score VALUES (3, 4, ‘75’);

INSERT INTO score VALUES (4, 6, ‘90’);

4.6插入师生关系信息

INSERT INTO teacher_stu_relation VALUES (1, 1);

INSERT INTO teacher_stu_relation VALUES (1, 2);

INSERT INTO teacher_stu_relation VALUES (1, 3);

INSERT INTO teacher_stu_relation VALUES (1, 4);

INSERT INTO teacher_stu_relation VALUES (2, 3);

INSERT INTO teacher_stu_relation VALUES (2, 4);

INSERT INTO teacher_stu_relation VALUES (3, 1);

五、维护数据

5.1 查询学生所有信息

select * from student;

5.2 查询老师所有信息

select * from teacher;

5.3 查询选课了电路分析科目的学生信息

select c_name as 科目名, stu.stu_id as 学号, stu_name as 姓名 from score as s inner join course as c on s.c_id = c.c_id inner join student as stu on s.stu_id = stu.stu_id where c.c_name = ‘电路分析’;

5.4 对应科目的老师信息

select c_id as 科目编号, c_name as 课程名, t_name as 教师名, t_phone as 电话 from course as c inner join teacher as t on c.t_id = t.t_id;

5.5 修改学生信息

update student set stu_name = ‘纳尼二世’ where stu_name = ‘纳尼’;

5.6 删除学号为1的学生信息

delete from student where stu_id = 1;

5.7 学号为2的学生选择数据库应用开发课程

insert into score(stu_id, c_id) values(2, 6);

5.8 查询年龄大于20岁的学生信息

select * from student where stu_age > 20;

5.9 学号为2的学生修改其电话号码

update student set stu_phone = 87654321000 where stu_id = 2;