-- mysql数据库程序设计笔记：
-- mysql安装路径 my.ini 中把：default-character-set=utf8 改为 default-character-set=gbk 后重启客户端。
-- 创建数据库：
create database db_school default charset gb2312 collate gb2312_chinese_ci;
use db_school;
-- 1、学生表：
create table tb_student
(
id int NOT NULL auto_increment comment '自增id',
studentNo char(10) not null comment '学号',
studentName varchar(20) not null comment '姓名',
sex char(3) comment '性别',
birthday date comment '出生年月',
native varchar(20) comment '籍贯',
nation varchar(30) comment '民族',
classNo char(8) comment '所属班级',
studentID varchar(18) comment '身份证号',
primary key (id)
)engine=InnoDB charset=utf8 comment='学生表';
insert into tb_student(studentNo,studentName,sex,birthday,native,nation,classNo,studentID) values('201338941','张骞','男','1980-01-22','山西侯马','汉','3班','1423373457656789');
insert into tb_student(studentNo,studentName,sex,birthday,native,nation,classNo,studentID) values('201348589','王博','男','1981-01-22','山西大同','汉','2班','1423377764456632');
insert into tb_student(studentNo,studentName,sex,birthday,native,nation,classNo,studentID) values('201345232','黎明','女','1983-11-22','山西太原','汉','1班','142337776669966');
insert into tb_student(studentNo,studentName,sex,birthday,native,nation,classNo,studentID) values('201357356','李强','男','1980-03-22','山西侯马','汉','5班','1423379476543589');
insert into tb_student(studentNo,studentName,sex,birthday,native,nation,classNo,studentID) values('201338947','李天','女','1984-07-22','山西太原','汉','4班','1423377766656789');
insert into tb_student(studentNo,studentName,sex,birthday,native,nation,classNo,studentID) values('201323145','刘强','男','1990-04-22','山西应县','汉','3班','142337346246789');
insert into tb_student(studentNo,studentName,sex,birthday,native,nation,classNo,studentID) values('201323462','刘泽','男','1997-09-22','山西太原','汉','2班','1423374573456789');
insert into tb_student(studentNo,studentName,sex,birthday,native,nation,classNo,studentID) values('201323457','刘慧','女','1985-01-22','山西大同','汉','1班','14233723414556789');
-- 2、新建成绩表：
create table tb_score
(
id int NOT NULL auto_increment comment '自增id',
studentNo varchar(10) comment '学号',
courseNo varchar(6) comment '课程号',
term varchar(5) comment '学期',
score int comment '成绩',
primary key (id)
)engine=InnoDB charset=utf8 comment='成绩表';
insert into tb_score(studentNo,courseNo,term,score)values('201338941','9397','2','70');
insert into tb_score(studentNo,courseNo,term,score)values('201348589','9311','1','96');
insert into tb_score(studentNo,courseNo,term,score)values('201345232','9323','3','84');
insert into tb_score(studentNo,courseNo,term,score)values('201357356','9311','4','61');
insert into tb_score(studentNo,courseNo,term,score)values('201338947','9322','3','77');
insert into tb_score(studentNo,courseNo,term,score)values('201323145','9334','2','69');
insert into tb_score(studentNo,courseNo,term,score)values('201323462','9362','3','88');
insert into tb_score(studentNo,courseNo,term,score)values('201323457','9312','4','95');
insert into tb_score(studentNo,courseNo,term,score)values('201338947','9322','7','67');
insert into tb_score(studentNo,courseNo,term,score)values('201323145','9334','8','69');
insert into tb_score(studentNo,courseNo,term,score)values('201323462','9362','9','58');
insert into tb_score(studentNo,courseNo,term,score)values('201323457','9312','10','55');
-- 3、新建班级表：
create table tb_class
(
id int NOT NULL auto_increment comment '自增id',
studentNo varchar(90) comment '学号',
classNo varchar(8) comment '班级编号',
className varchar(20) comment '班级名称',
department varchar(30) comment '院系',
enrollTime date comment '入学时间',
grade int(20) comment '年级',
classNum int(10) comment '班级人数',
primary key (id)
)engine=InnoDB charset=utf8 comment='班级表';
insert into tb_class(studentNo,classNo,className,department,enrollTime,grade,classNum) values('201338941','919302','加强班','计算机系','1999-09-01','3','21');
insert into tb_class(studentNo,classNo,className,department,enrollTime,grade,classNum) values('201348589','919122','加强班','会计系','1999-09-01','1','32');
insert into tb_class(studentNo,classNo,className,department,enrollTime,grade,classNum) values('201345232','919234','加强班5','信息系','1999-09-01','1','61');
insert into tb_class(studentNo,classNo,className,department,enrollTime,grade,classNum) values('201357356','919234','加强班4','土建系','1999-09-01','3','43');
insert into tb_class(studentNo,classNo,className,department,enrollTime,grade,classNum) values('201323145','919112','加强班3','土建系','1999-09-01','2','33');
insert into tb_class(studentNo,classNo,className,department,enrollTime,grade,classNum) values('201323462','919153','加强班2','会计系','1999-09-01','3','21');
insert into tb_class(studentNo,classNo,className,department,enrollTime,grade,classNum) values('201323457','919890','加强班','计算机系','1999-09-01','3','56');
-- 添加数据：
insert into tb_student(studentNo,studentName,sex,birthday,native,nation,classNo,studentID) values('201664','王梦','女','1980-01-22','山西大同','汉','1班','1423372333344');
insert into tb_student(studentNo,studentName,sex,birthday,native,nation,classNo,studentID) values('201385','张氏一','男','1981-01-22','运城','傣族','2班','1423374456789');
insert into tb_student(studentNo,studentName,sex,birthday,native,nation,classNo,studentID) values('201399','柳依依','女','1985-01-22','太原','傣族','3班','1423456289');
insert into tb_student(studentNo,studentName,sex,birthday,native,nation,classNo,studentID) values('201664','刘明辉','女','1989-01-22','临汾','维吾尔族','4班','1423367789');
insert into tb_student(studentNo,studentName,sex,birthday,native,nation,classNo,studentID) values('201555','张通透','男','1987-01-22','临淄','傣族','5班','14233723334');
insert into tb_student(studentNo,studentName,sex,birthday,native,nation,classNo,studentID) values('201442','张泽好','女','1988-01-22','晋中','汉','6班','14233723449');
insert into tb_student(studentNo,studentName,sex,birthday,native,nation,classNo,studentID) values('201328864','刘名义','女','1985-01-22','山西大同','汉','1班_超越','14233723414556789');
insert into tb_student(studentNo,studentName,sex,birthday,native,nation,classNo,studentID) values('201324565','天一','男','1985-01-22','山西大同','汉','1班_火箭','14233723414556789');
insert into tb_student(studentNo,studentName,sex,birthday,native,nation,classNo,studentID) values('201323772','刘泽好','女','1985-01-22','山西大同','汉','1班_加强','14233723414556789');
insert into tb_class(studentNo,classNo,className,department,enrollTime,grade,classNum) values('',null,' ','','1999-09-01','3','56');
insert into tb_class(studentNo,classNo,className,department,enrollTime,grade,classNum) values('','911113',null,'','1999-09-01','3','56');