p88 p306-308创建表及数据的填入练习

 

create database Stuexpm;
use Stuexpm;
create table StudentInfo
(
sno char(6) not null primary key comment'学号',
sname char(8) not null comment'姓名',
ssex char(2) not null default'男' comment'性别',
sbirthday date not null comment'出生日期',
speciality char(12) null comment'专业',
adress char(20) not null comment'家庭住址'
);
use Stuexpm;
create table CourseInfo
(
scn char(6) not null primary key comment'课程号',
scname char(8) not null comment '课程名',
scs tinyint not null comment '学分'
);

use Stuexpm;
create table ScoreInfo
(
sin char(6) not null comment'学号',
sih char(8) not null comment '课程号',
sic tinyint null comment'成绩'
);

use Stuexpm;
create table TeacherInfo
(
tch char(6)  not null primary key default "0" comment'教师编号',
tcn char(8) not null comment'姓名',
tcsex char(2) not null default'男' comment'性别',
tbirthday date not null comment'出生日期',
tspeciality char(12) null comment'专业',
tadress char(20) not null comment'家庭住址'
);

 

insert into TeacherInfo
values('100005','李慧强','男','19680925','计算机学院','北京市海淀区'),
('100024','刘松','男','19760217','计算机学院','北京市海淀区'),
('400021','陈霞飞','女','19751207','通信学院','上海市黄浦区'),
('800004','刘泉明','男','19780816','数学学院','广州市越秀区'),
('120007','张莉','女','19820321','外国语学院','成都市锦江区');
select * from Teacherinfo;

insert into courseinfo
values('1004','数据库系统',4),
('1025','数据库系统',3),
('4002','数据库系统',3),
('8001','数据库系统',4),
('1201','数据库系统',4);
select *from courseinfo;

insert into studentinfo
values('181001','陈志强','男','19980817','计算机','北京市海淀区'),
('181002','陈志强','女','19971123','计算机','成都市锦江区区'),
('181003','陈志强','女','19980219','计算机','北京市海淀区'),
('181004','陈志强','男','19971205','电子信息工程',null),
('181005','陈志强','女','19980224','电子信息工程','上海市浦东区'),
('181006','陈志强','男','19970919','电子信息工程','上海市浦东区');
select * from studentinfo;

insert scoreinfo
value('181001','1004',95),('181002','1004',85),
('181003','1004',91),('184001','4002',93),
('184002','4002',76),('184004','4002',88),
('181001','8001',94),('181002','8001',89),
('181003','8001',86),('181001','8001',85),
('184002','8001',null),('184004','8001',94),
('181001','1201',92),('181002','1201',78),
('181003','1201',94),('184001','1201',85),
('184002','1201',79),('184004','1201',94);
select * from scoreinfo;

 

create database stusys;
use stusys;
create table student
(
sno  char(6) not null primary key comment '学号' ,
sname char(8) not null comment '姓名',
ssex char(2) not null default '男' comment '性别',
sbirthday date not null comment '出生日期',
speciality char(12) null comment '专业',
tc tinyint null comment '总学分'
); 

use stusys;
create table course
(
cno char(4) not null primary key comment'课程号',
cname char(16) not null comment '课程名',
credit tinyint null comment '学分'
);

use stusys;
create table score
(
sno char(6) not null comment'学号',
cno char(4) not null comment'课程号',
primary key(sno,cno),
grade tinyint null comment'成绩'
);

use stusys;
create table teacher
(
tno char(6) not null primary key comment '教师编号',
tname char(8) not null comment '姓名',
tsex char(2) not null default '男' comment '性别',
tbirthday date not null comment '出生日期',
title char(12) null comment '职称',
school char(12) null comment '学院'
);

use stusys;
create table lecture
(
tno char(6) not null comment'教师编号',
cno char(4) not null comment'课程号',
location char(10) null comment'上课地点',
primary key(tno,cno)
);

use stusys;
create table teacher
(
tno char(6) not null primary key comment '教师编号',
tname char(8) not null comment '姓名',
tsex char(2) not null default '男' comment '性别',
tbirthday date not null comment '出生日期',
title char(12) null comment '职称',
school char(12) null comment '学院'
);

use stusys;
create table lecture
(
tno char(6) not null comment'教师编号',
cno char(4) not null comment'课程号',
location char(10) null comment'上课地点',
primary key(tno,cno)
);

insert into courseinfo
values('1004','数据库系统',4),
('1025','数据库系统',3),
('4002','数据库系统',3),
('8001','数据库系统',4),
('1201','数据库系统',4);
select *from courseinfo;

insert into studentinfo
values('181001','陈志强','男','19980817','计算机','北京市海淀区'),
('181002','陈志强','女','19971123','计算机','成都市锦江区区'),
('181003','陈志强','女','19980219','计算机','北京市海淀区'),
('181004','陈志强','男','19971205','电子信息工程',null),
('181005','陈志强','女','19980224','电子信息工程','上海市浦东区'),
('181006','陈志强','男','19970919','电子信息工程','上海市浦东区');
select * from studentinfo;

insert scoreinfo
value('181001','1004',95),('181002','1004',85),
('181003','1004',91),('184001','4002',93),
('184002','4002',76),('184004','4002',88),
('181001','8001',94),('181002','8001',89),
('181003','8001',86),('181001','8001',85),
('184002','8001',null),('184004','8001',94),
('181001','1201',92),('181002','1201',78),
('181003','1201',94),('184001','1201',85),
('184002','1201',79),('184004','1201',94);
select * from scoreinfo;