mysql数据库创建班级信息表如何用mysql建表一个班级表

转载

clghxq 2024-04-26 15:53:31

文章标签 mysql数据库创建班级信息表外键软件工程数据结构 文章分类 MySQL 数据库

解释：

以下数据库语句，可以在终端运行，打开mysql，然后运行

也可以连接上mysql数据库的可视化工具中选中执行，输入数据库代码运行即可。

mysql数据库创建班级信息表如何用mysql建表一个班级表_mysql数据库创建班级信息表

以下数据库只插入了18级计算机科学与技术专业，软件工程专业，网络工程专业。还有1-9个班。所以程序操作数据库在操作这几个班时不会出错。
若要操作更多专业年级，更新数据库内容即可

create database StudentSystemDao;

use StudentSystemDao;
create table tb_Department( #所属院系表
Department_ID varchar(30) primary key,	#院系编号
Department_Name nvarchar(50) unique not null 	#院系名
);


use StudentSystemDao;
create table tb_Major(  #专业表
Major_ID varchar(30) primary key,	#专业编号
Major_Name nvarchar(50) unique not null,	#专业名称
Department_ID varchar(30) not null,	#所属院系ID
foreign key(Department_ID) references tb_Department(Department_ID)	#所属院系设置为院系表的外键
);


use StudentSystemDao;
create table tb_Classe(		#班级表
Classe int not null,	#班级
Grade int not null,	#年级
Major_ID varchar(30) not null,	#专业编号
primary key(Classe,Grade,Major_ID)	#班级，年级和专业编号共为主键
);


use StudentSystemDao;
create table tb_Student(		#学生表
Student_Id varchar(30) primary key,	#学生学号
Student_Name nvarchar(50) not null,		#学生姓名
Student_Sex nchar(1) ,	#学生性别
Grade int not null,	#年级
Classe int not null,	#班级
Major_ID varchar(30) not null,	#专业编号
Major_Name nvarchar(50)  not null,	#专业名称
Department_ID varchar(30) not null,	#所属院系ID
Department_Name nvarchar(50)  not null,	#院系名称
foreign key(Department_ID) references tb_Department(Department_ID),	#所属院系编号设置为外键
foreign key(Major_ID) references tb_Major(Major_ID),	#专业编号设置为外键
foreign key(Department_Name) references tb_Department(Department_Name),	#所属院系名称设置为外键
foreign key(Major_Name) references tb_Major(Major_Name)	#专业名称设置为外键
);


use StudentSystemDao;
create table tb_User(					#用户表
User_name nvarchar(30) primary key,		#用户名				
Password_ varchar(30) not null,			#密码
IsLogin bit not null DEFAULT 0 	#是否重复登陆
);


use StudentSystemDao;
create table tb_Course(								#课程表
Course_Name nvarchar(50) not null,			#课程名称
Major_ID varchar(30) not null,						#所属专业编号
foreign key(Major_ID) references tb_Major(Major_ID),#专业编号设置为专业表的外键
Grade int not null,	#年级
primary key(Course_Name,Major_ID,Grade)	#课程名称，专业，年级共为主键
);


use StudentSystemDao;
create table tb_Score( 									  #成绩表
Student_Id varchar(30) not null,						  #学生ID
Student_Name nvarchar(50) not null,		#学生姓名
Course_Name nvarchar(50) not null,							#课程名称
primary key(Course_Name,Student_Id),		  #学生ID和课程名称共同组成主键
foreign key(Student_Id) references tb_Student(Student_Id), #学生ID设置为学生表的外键
foreign key(Course_Name) references tb_Course(Course_Name),
Score dec(4,1) default null  #成绩在0到100之间
);

/*插入院系*/
insert into tb_Department(Department_ID ,Department_Name) values('01','艺术系');
insert into tb_Department(Department_ID ,Department_Name) values('02','中国语言文学系');
insert into tb_Department(Department_ID ,Department_Name) values('03','电子信息系');
insert into tb_Department(Department_ID ,Department_Name) values('04','计算机科学与技术系');
insert into tb_Department(Department_ID ,Department_Name) values('05','物流与信息管理系');
insert into tb_Department(Department_ID ,Department_Name) values('06','旅游管理系');
insert into tb_Department(Department_ID ,Department_Name) values('07','机械与汽车工程系');
insert into tb_Department(Department_ID ,Department_Name) values('08','国际贸易与金融系');
insert into tb_Department(Department_ID ,Department_Name) values('09','工商管理系');
insert into tb_Department(Department_ID ,Department_Name) values('10','公共管理系');
insert into tb_Department(Department_ID ,Department_Name) values('11','建筑学系');
insert into tb_Department(Department_ID ,Department_Name) values('12','化学与药学系');
insert into tb_Department(Department_ID ,Department_Name) values('13','外语系');
insert into tb_Department(Department_ID ,Department_Name) values('14','音乐舞蹈学院');



/*插入专业*/
insert into tb_Major(Major_ID,Major_Name,Department_ID) values('01','视觉传达设计专业','01');
insert into tb_Major(Major_ID,Major_Name,Department_ID) values('02','动画专业','01');
insert into tb_Major(Major_ID,Major_Name,Department_ID) values('03','汉语言文学专业','02');
insert into tb_Major(Major_ID,Major_Name,Department_ID) values('04','广告学专业','02');
insert into tb_Major(Major_ID,Major_Name,Department_ID) values('05','汉语国际教育专业','02');
insert into tb_Major(Major_ID,Major_Name,Department_ID) values('06','电子信息科学与技术专业','03');
insert into tb_Major(Major_ID,Major_Name,Department_ID) values('07','微电子科学与工程专业','03');
insert into tb_Major(Major_ID,Major_Name,Department_ID) values('08','自动化专业','03');
insert into tb_Major(Major_ID,Major_Name,Department_ID) values('09','通信工程专业','03');
insert into tb_Major(Major_ID,Major_Name,Department_ID) values('10','测控技术与仪器专业','03');
insert into tb_Major(Major_ID,Major_Name,Department_ID) values('11','计算机科学与技术专业','04');
insert into tb_Major(Major_ID,Major_Name,Department_ID) values('12','软件工程专业','04');
insert into tb_Major(Major_ID,Major_Name,Department_ID) values('13','网络工程专业','04');
insert into tb_Major(Major_ID,Major_Name,Department_ID) values('14','物流管理专业','05');
insert into tb_Major(Major_ID,Major_Name,Department_ID) values('15','信息管理与信息系统专业','05');
insert into tb_Major(Major_ID,Major_Name,Department_ID) values('16','电子商务专业','05');
insert into tb_Major(Major_ID,Major_Name,Department_ID) values('17','旅游管理专业','06');
insert into tb_Major(Major_ID,Major_Name,Department_ID) values('18','机械设计制造及其自动化专业','07');
insert into tb_Major(Major_ID,Major_Name,Department_ID) values('19','汽车服务工程专业','07');
insert into tb_Major(Major_ID,Major_Name,Department_ID) values('20','车辆工程专业','07');
insert into tb_Major(Major_ID,Major_Name,Department_ID) values('21','工业工程专业','07');
insert into tb_Major(Major_ID,Major_Name,Department_ID) values('22','国际经济与贸易专业','08');
insert into tb_Major(Major_ID,Major_Name,Department_ID) values('23','金融学专业','08');
insert into tb_Major(Major_ID,Major_Name,Department_ID) values('24','工商管理专业','09');
insert into tb_Major(Major_ID,Major_Name,Department_ID) values('25','市场营销专业','09');
insert into tb_Major(Major_ID,Major_Name,Department_ID) values('26','人力资源管理专业','09');
insert into tb_Major(Major_ID,Major_Name,Department_ID) values('27','会计学专业','09');
insert into tb_Major(Major_ID,Major_Name,Department_ID) values('28','行政管理专业','10');
insert into tb_Major(Major_ID,Major_Name,Department_ID) values('29','劳动与社会保障专业','10');
insert into tb_Major(Major_ID,Major_Name,Department_ID) values('30','社会工作专业','10');
insert into tb_Major(Major_ID,Major_Name,Department_ID) values('31','建筑学专业','11');
insert into tb_Major(Major_ID,Major_Name,Department_ID) values('32','城乡规划专业','11');
insert into tb_Major(Major_ID,Major_Name,Department_ID) values('33','应用化学专业','12');
insert into tb_Major(Major_ID,Major_Name,Department_ID) values('34','制药工程(生物制药)专业','12');
insert into tb_Major(Major_ID,Major_Name,Department_ID) values('35','药物制剂专业','12');
insert into tb_Major(Major_ID,Major_Name,Department_ID) values('36','中药学专业','12');
insert into tb_Major(Major_ID,Major_Name,Department_ID) values('37','化学工程与工艺专业','12');
insert into tb_Major(Major_ID,Major_Name,Department_ID) values('38','英语专业','13');
insert into tb_Major(Major_ID,Major_Name,Department_ID) values('39','日语专业','13');
insert into tb_Major(Major_ID,Major_Name,Department_ID) values('40','朝鲜语（韩国语）专业','13');
insert into tb_Major(Major_ID,Major_Name,Department_ID) values('41','音乐表演专业','14');
insert into tb_Major(Major_ID,Major_Name,Department_ID) values('42','舞蹈编导专业','14');


/*添加班级*/
insert into tb_Classe(Classe,Grade,Major_ID) values(1,2018,'11');
insert into tb_Classe(Classe,Grade,Major_ID) values(2,2018,'11');
insert into tb_Classe(Classe,Grade,Major_ID) values(3,2018,'11');
insert into tb_Classe(Classe,Grade,Major_ID) values(4,2018,'11');
insert into tb_Classe(Classe,Grade,Major_ID) values(5,2018,'11');
insert into tb_Classe(Classe,Grade,Major_ID) values(6,2018,'11');
insert into tb_Classe(Classe,Grade,Major_ID) values(7,2018,'11');
insert into tb_Classe(Classe,Grade,Major_ID) values(8,2018,'11');
insert into tb_Classe(Classe,Grade,Major_ID) values(9,2018,'11');
insert into tb_Classe(Classe,Grade,Major_ID) values(1,2018,'12');
insert into tb_Classe(Classe,Grade,Major_ID) values(2,2018,'12');
insert into tb_Classe(Classe,Grade,Major_ID) values(3,2018,'12');
insert into tb_Classe(Classe,Grade,Major_ID) values(4,2018,'12');
insert into tb_Classe(Classe,Grade,Major_ID) values(5,2018,'12');
insert into tb_Classe(Classe,Grade,Major_ID) values(6,2018,'12');
insert into tb_Classe(Classe,Grade,Major_ID) values(7,2018,'12');
insert into tb_Classe(Classe,Grade,Major_ID) values(8,2018,'12');
insert into tb_Classe(Classe,Grade,Major_ID) values(9,2018,'12');
insert into tb_Classe(Classe,Grade,Major_ID) values(1,2018,'13');
insert into tb_Classe(Classe,Grade,Major_ID) values(2,2018,'13');
insert into tb_Classe(Classe,Grade,Major_ID) values(3,2018,'13');
insert into tb_Classe(Classe,Grade,Major_ID) values(4,2018,'13');
insert into tb_Classe(Classe,Grade,Major_ID) values(5,2018,'13');
insert into tb_Classe(Classe,Grade,Major_ID) values(6,2018,'13');
insert into tb_Classe(Classe,Grade,Major_ID) values(7,2018,'13');
insert into tb_Classe(Classe,Grade,Major_ID) values(8,2018,'13');
insert into tb_Classe(Classe,Grade,Major_ID) values(9,2018,'13');


/*添加课程*/
insert into tb_Course(Course_Name,Major_ID,Grade) values ('高等数学','11',2018);
insert into tb_Course(Course_Name,Major_ID,Grade) values ('英语','11',2018);
insert into tb_Course(Course_Name,Major_ID,Grade) values ('数据结构','11',2018);
insert into tb_Course(Course_Name,Major_ID,Grade) values ('计算机组成原理','11',2018);
insert into tb_Course(Course_Name,Major_ID,Grade) values ('面向对象程序设计','11',2018);

insert into tb_Course(Course_Name,Major_ID,Grade) values ('高等数学','12',2018);
insert into tb_Course(Course_Name,Major_ID,Grade) values ('英语','12',2018);
insert into tb_Course(Course_Name,Major_ID,Grade) values ('数据结构','12',2018);
insert into tb_Course(Course_Name,Major_ID,Grade) values ('软件工程导论','12',2018);
insert into tb_Course(Course_Name,Major_ID,Grade) values ('面向对象程序设计','12',2018);

insert into tb_Course(Course_Name,Major_ID,Grade) values ('高等数学','13',2018);
insert into tb_Course(Course_Name,Major_ID,Grade) values ('英语','13',2018);
insert into tb_Course(Course_Name,Major_ID,Grade) values ('数据结构','13',2018);
insert into tb_Course(Course_Name,Major_ID,Grade) values ('计算机网络','13',2018);
insert into tb_Course(Course_Name,Major_ID,Grade) values ('面向对象程序设计','13',2018);

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。