实现功能:
对于学生而言可以实现选课功能,日常学习中,我们选课的时候,需要登陆自己的学号,密码,然后进行选课,选课的时候,会有老师的信息,课程号,课程名,授课老师,等。
学生的信息应该有
(学号,姓名,性别,系别,年龄,入学时间,专业,登陆密码)
教师的信息应该有
(教室工号,姓名,性别,密码,职称,所在系院,课程号)
课程表
(课程号,课程名,先修课号,学分)
学生选课
(学号,课程号,成绩)
授课表
(教室工号,课程号,上课地点,上课时间)
学生课表
(学号,已选课号,上课时间,上课地点)
create database students charset utf8;
create table stu_info (
stu_id int not null auto_increment primary key,
stu_name char(30) not null default '',
stu_sex char(1) not null default '',
stu_depar char(30) not null default '',
stu_age tinyint(3) not null default 0,
stu_time date,
stu_maj char(30) not null default '',
stu_pas char(30) not null default ''
)engine myisam charset utf8;
create table tea_info (
teacher_id int not null auto_increment primary key,
teacher_name char(30) not null default '',
teacher_sex char(30) not null default '',
teacher_pas char(30) not null default '',
teacher_tit char(30) not null default '',
teacher_depar char(30) not null default '',
cou_id int not null default 0
)engine myisam charset utf8;
create table course_info (
cou_id int not null auto_increment primary key,
cou_name char(30) not null default '',
cou_pid int not null default 0,
cou_credit tinyint unsigned not null default 0
)engine myisam charset utf8;
create table stu_sco (
stu_id int not null default 0,
cou_id int not null default 0,
stu_score tinyint not null default 0,
primary key (stu_id,cou_id)
)engine myisam charset utf8;
create table teach (
teacher_id int not null default 0,
cou_id int not null default 0,
tea_area char(30) not null default '',
tea_time char(30) not null default '',
primary key(teacher_id,cou_id)
)engine myisam charset utf8;
create table schedules (
stu_id int not null default 0,
cou_id int not null default 0,
primary key(stu_id,cou_id)
)engine myisam charset utf8;
//插入 学生信息表
insert into stu_info values (1406915099,'张鹏飞','男','计算机与信息工程学院',22,'-09','信息对抗与管理','123456');
insert into stu_info values ('','王同学','女','经济管理学院',21,'-09','国际经济与贸易','987654');
insert into stu_info values ('','李同学','男','计算机与信息工程学院',22,'-09','计算机科学与技术','fdsf');
insert into stu_info values ('','赵同学','男','计算机与信息工程学院',23,'-09','物联网工程','sdfa');
//插入教师信息表
insert into tea_info values (,'','','','','',);
insert into tea_info values (13145621,'张老师','女','123465','讲师','计算机与信息工程学院',3);
insert into tea_info values ('','王老师','女','123133','讲师','计算机与信息工程学院',4);
insert into tea_info values ('','李老师','男','324234','副教授','计算机与信息工程学院',5);
insert into tea_info values (123456,'赵老师','男','456465','副教授','计算机与信息工程学院',2);
//插入 课程 表
insert into course_info values (,'',,,);
insert into course_info values (3,'数据库原理与应用',2,3);
insert into course_info values (4,'数据结构',2,3);
insert into course_info values (5,'计算机操作系统',4,4);
insert into course_info values (2,'C语言程序设计',0,3);
//插入 学生成绩表
insert into stu_sco values (1406915099,2,90);
insert into stu_sco values (1406915100,3,80);
insert into stu_sco values (1406915101,4,75);
insert into stu_sco values (1406915102,5,86);
//插入 授课表
insert into teach values (123456,2,'7#101','每周周一3,4节');
insert into teach values (13145621,3,'7#305','每周周二5,6节');
insert into teach values (13145622,4,'13#421','每周周三7,8节');
insert into teach values (13145623,5,'6#201','每周周五1,2节');
//学生课表
insert into schedules values (1406915099,2);
insert into schedules values (1406915100,3);
insert into schedules values (1406915101,4);
insert into schedules values (1406915102,5);
//修改语句
update stu_info set 字段名=字段值 where id='';
//删除语句
delete from stu_info where id=;
功能分析:
可以通过学号查询自己的个人信息
select * from stu_info where stu_id='';
select * from stu_info where stu_id='1406915099';
可以修改自己的个人信息
update stu_info set stu_age=23 where stu_id=1406915099;
教师可以通过自己的教工号查看自己的个人信息
select * from tea_info where teacher_id=123456;
教师可以修改自己的个人信息
update tea_info set teacher_pas='12345w' where teacher_id=123456;
教师可以查看自己所教的课程名字(使用左连接把tea_info 和 course_info 连接起来查询教师所教的课程)
select tea_info.teacher_id,tea_info.teacher_name,tea_info.teacher_sex,course_info.cou_name from tea_info left join course_info
on tea_info.cou_id=course_info.cou_id;
select tea_info.teacher_id,tea_info.teacher_name,course_info.cou_name from tea_info left join course_info
on tea_info.cou_id=course_info.cou_id;
//查询学某门课之前应该学会的课程号以及课程名
select m.cou_id as '课程编号',m.cou_pid as '先修课程编号',t.cou_name as '先修课程名字'
from course_info as m left join course_info as t on m.cou_pid=t.cou_id;
select m.*,t.* from course_info as m left join course_info as t on m.cou_pid=t.cou_id;
//学生通过自己的学号,课程号来查询自己该课程的成绩
select * from stu_sco where stu_id=1406915099 and cou_id=2;
//学生可以通过自己的学号,查出自己课程表 左连接三张表查出学生的学号,课程号,课程名,上课时间,上课地点
select schedules.stu_id,schedules.cou_id,course_info.cou_name,teach.tea_area,teach.tea_time from schedules left join
course_info on schedules.cou_id=course_info.cou_id left join teach on schedules.cou_id=teach.cou_id;
//查询某一门课的上课时间
select * from teach where cou_id=2;
如果觉得《数据库选课系统mysql_学生选课系统数据库的设计与实现》对你有帮助,请点赞、收藏,并留下你的观点哦!