1. 創(chuàng)建數(shù)據(jù)庫(kù)

CREATE DATABASE `Study` CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

2. 創(chuàng)建學(xué)生表 S

create table S(
SNO char(6)  primary key,      -- 學(xué)號(hào)
SN varchar(10) not null,       -- 姓名
Sex char(2)  ,                 -- 性別
Age int  ,                     -- 年齡
Dept varchar(20)               -- 系別
);

-- 向 S 表中插入數(shù)據(jù)
insert into S values('S1','趙亦','女',17,'計(jì)算機(jī)');
insert into S values('S2','錢爾','男',18,'信息');
insert into S values('S3','孫珊','女',20,'信息');
insert into S values('S4','李思','男',21,'自動(dòng)化');
insert into S values('S5','周武','男',19,'計(jì)算機(jī)');
insert into S values('S6','吳麗','女',20,'自動(dòng)化');

學(xué)生表 S

3. 創(chuàng)建教師表 T

create table T(
TNO char(6)  primary key,     -- 教師號(hào)
TN varchar(10) not null,      -- 姓名
Sex char(2)  ,                -- 性別
Age int ,                     -- 年齡
Prof varchar(8) ,             -- 職稱
Sal int ,                     -- 工資
Comm int,                     -- 崗位津貼
Dept varchar(20)              -- 系別
);

-- 向 T 表中插入數(shù)據(jù)
insert into T values('T1','李力','男',47,'教授',1500,3000,'計(jì)算機(jī)');
insert into T values('T2','王平','女',28,'講師',800,1200,'信息');
insert into T values('T3','劉偉','男',30,'講師',900,1200,'計(jì)算機(jī)');
insert into T values('T4','張雪','女',51,'教授',1600,3000,'自動(dòng)化');
insert into T values('T5','張?zhí)m','女',39,'副教授',1300,2000,'信息');

教師表 T

4. 創(chuàng)建課程表 C

create table C(
CNO char(6)  primary key,       -- 課程號(hào)
CN varchar(20) not null,        -- 課程名
CT int                          -- 學(xué)時(shí)
);

-- 向 C 表中插入數(shù)據(jù)
insert into C values('C1','程序設(shè)計(jì)',60);
insert into C values('C2','微機(jī)原理',80);
insert into C values('C3','數(shù)字邏輯',60);
insert into C values('C4','數(shù)據(jù)結(jié)構(gòu)',80);
insert into C values('C5','數(shù)據(jù)庫(kù)',60);
insert into C values('C6','編譯原理',60);
insert into C values('C7','操作系統(tǒng)',60);

課程表 C

5. 創(chuàng)建選課表 SC

 create table SC(
SNO char(6) not null,      -- 學(xué)號(hào)
CNO char(6) not null,      -- 課程號(hào)
Score numeric(4,1),        -- 成績(jī)
primary key(SNO,CNO),
foreign key(SNO) references S(SNO),
foreign key(CNO) references C(CNO)
);

-- 向 SC 表中插入數(shù)據(jù)
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. 創(chuàng)建授課表 TC

create table TC(
TNO char(6)   not null,      -- 教師號(hào)
CNO char(6)   not null,      -- 課程號(hào)號(hào)
primary key(TNO,CNO),
foreign key(TNO) references T(TNO),
foreign key(CNO) references C(CNO)
);

-- 向 TC 表中插入數(shù)據(jù)
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