1. 创建数据库
CREATE DATABASE `Study` CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';
2. 创建学生表 S
create table S(
SNO char(6) primary key, -- 学号
SN varchar(10) not null, -- 姓名
Sex char(2) , -- 性别
Age int , -- 年龄
Dept varchar(20) -- 系别
);
-- 向 S 表中插入数据
insert into S values('S1','赵亦','女',17,'计算机');
insert into S values('S2','钱尔','男',18,'信息');
insert into S values('S3','孙珊','女',20,'信息');
insert into S values('S4','李思','男',21,'自动化');
insert into S values('S5','周武','男',19,'计算机');
insert into S values('S6','吴丽','女',20,'自动化');
学生表 S
3. 创建教师表 T
create table T(
TNO char(6) primary key, -- 教师号
TN varchar(10) not null, -- 姓名
Sex char(2) , -- 性别
Age int , -- 年龄
Prof varchar(8) , -- 职称
Sal int , -- 工资
Comm int, -- 岗位津贴
Dept varchar(20) -- 系别
);
-- 向 T 表中插入数据
insert into T values('T1','李力','男',47,'教授',1500,3000,'计算机');
insert into T values('T2','王平','女',28,'讲师',800,1200,'信息');
insert into T values('T3','刘伟','男',30,'讲师',900,1200,'计算机');
insert into T values('T4','张雪','女',51,'教授',1600,3000,'自动化');
insert into T values('T5','张兰','女',39,'副教授',1300,2000,'信息');
教师表 T
4. 创建课程表 C
create table C(
CNO char(6) primary key, -- 课程号
CN varchar(20) not null, -- 课程名
CT int -- 学时
);
-- 向 C 表中插入数据
insert into C values('C1','程序设计',60);
insert into C values('C2','微机原理',80);
insert into C values('C3','数字逻辑',60);
insert into C values('C4','数据结构',80);
insert into C values('C5','数据库',60);
insert into C values('C6','编译原理',60);
insert into C values('C7','操作系统',60);
课程表 C
5. 创建选课表 SC
create table SC(
SNO char(6) not null, -- 学号
CNO char(6) not null, -- 课程号
Score numeric(4,1), -- 成绩
primary key(SNO,CNO),
foreign key(SNO) references S(SNO),
foreign key(CNO) references C(CNO)
);
-- 向 SC 表中插入数据
insert into SC values('S1','C1',90);
insert into SC values('S1','C2',85);
insert into SC values('S2','C4',70);
insert into SC values('S2','C5',57);
insert into SC values('S2','C6',80);
insert into SC values('S2','C7',null);
insert into SC values('S3','C1',75);
insert into SC values('S3','C2',70);
insert into SC values('S3','C4',85);
insert into SC values('S4','C1',93);
insert into SC values('S4','C2',85);
insert into SC values('S4','C3',83);
insert into SC values('S5','C2',89);
选课表 SC
6. 创建授课表 TC
create table TC(
TNO char(6) not null, -- 教师号
CNO char(6) not null, -- 课程号号
primary key(TNO,CNO),
foreign key(TNO) references T(TNO),
foreign key(CNO) references C(CNO)
);
-- 向 TC 表中插入数据
insert into TC values('T1','C1');
insert into TC values('T1','C4');
insert into TC values('T2','C5');
insert into TC values('T2','C6');
insert into TC values('T3','C1');
insert into TC values('T3','C5');
insert into TC values('T4','C2');
insert into TC values('T4','C3');
insert into TC values('T5','C5');
insert into TC values('T5','C7');
授课表 TC
