mysql创建班级，院系，专业

发表于 |
字数统计: 1.9k | 阅读时长 ≈ 9

解释：
以下数据库语句，可以在终端运行，打开mysql，然后运行
也可以连接上mysql数据库的可视化工具中选中执行，输入数据库代码运行即可。
运行
以下数据库只插入了18级计算机科学与技术专业，软件工程专业，网络工程专业。还有1-9个班。所以程序操作数据库在操作这几个班时不会出错。
若要操作更多专业年级，更新数据库内容即可

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
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);
-------------本文结束感谢您这么好看还看我的文章-------------