（头歌）数据库原理与技术—实验报告一

第1关：创建数据库

实验要求

创建名为 TESTDB 的数据库。

---------- BEGIN ---------- 
CREATE DATABASE TESTDB

---------- END ---------- 

第2关：创建简单的数据表 

实验要求

在 TESTDB 的数据库中创建教师（teacher）表，表结构如下：

字段名	数据类型（长度）	备注
tno	CHAR(4)	工号
tname	VARCHAR(12)	姓名
sex	CHAR(3)	性别
title	VARCHAR(15)	职称
birthday	DATE	出生日期

---------- BEGIN ---------- 
CREATE TABLE teacher(
    tno CHAR(4),
    tname VARCHAR(12),
    sex CHAR(3),
    title VARCHAR(15),
    birthday DATE
);
---------- END ---------- 

 第3关：创建带约束的数据表

实验要求

在 TESTDB 数据库中创建课程表（course）和班级表（class），表结构如下：

course 表结构

字段名	数据类型（长度）	完整性约束说明	备注
cno	CHAR(4)	主键	课程号
cname	VARCHAR(30)	非空	课程名
credit	TINYINT	约束取值范围为1~10	学分
hours	INTEGER	……	学时
examination	CHAR(6)	……	考核方式

class 表结构

字段名	数据类型（长度）	完整性约束说明	备注
gno	CHAR(7)	主键	班级号
gname	VARCHAR(21)	唯一键	班级名
grade	CHAR(5)	……	年级
dept	VARCHAR(12)	……	学院
gnum	TINYINT	约束取值范围为15~40	班级人数

---------- BEGIN ---------- 
CREATE TABLE course(
    cno CHAR(4) PRIMARY KEY,
    cname VARCHAR(30) NOT NULL,
    credit TINYINT CHECK (credit>=1 AND credit<=10),
    hours INTEGER,
    examination CHAR(6)
);
CREATE TABLE class(
    gno CHAR(7) PRIMARY KEY,
    gname VARCHAR(21) UNIQUE,
    grade CHAR(5),
    dept VARCHAR(12),
    gnum TINYINT CHECK(gnum>=15 AND gnum<=40)
);
---------- END ---------- 

第4关：给教师表添加约束

实验要求

在 TESTDB 数据库中给教师表（teacher）添加主键约束，工号（tno）作为主键，给教师表姓名（tname）列添加唯一约束。

---------- BEGIN ---------- 
ALTER TABLE teacher ADD PRIMARY KEY(tno);
ALTER TABLE teacher ADD UNIQUE(tname);
---------- END ---------- 

第5关：在 TESTDB 中创建教师授课表

实验要求

在 TESTDB 数据库中创建教师授课表 teaching，及其对应的约束条件，表结构如下：

字段名	数据类型（长度）	完整性约束说明	备注
cno	CHAR(4)	外键，主键（cno+tno+gno）	课程号
tno	CHAR(4)	外键，主键（cno+tno+gno）	工号
gno	CHAR(7)	外键，主键（cno+tno+gno）	班级号
term	TINYINT	……	开课学期
classroom	CHAR(7)	……	教室

---------- BEGIN ---------- 
create table teaching(
    cno char(4),
    tno char(4),
    gno char(7),
    term tinyint,
    classroom char(7),
    PRIMARY KEY(cno,tno,gno),
    foreign KEY(cno) references course(cno),
    foreign KEY(tno) references teacher(tno),
    foreign KEY(gno) references class(gno)
);
---------- END ---------- 

第6关：修改字段约束

实验要求

在 TESTDB 数据库中修改教师表（teacher）姓名列（tname）的空/非空约束，改为非空，修改教师表性别列（sex）的默认值约束，默认值改为“男”。

---------- BEGIN ---------- 
ALTER table public.teacher
ALTER
column tname set NOT NULL;

ALTER table public.teacher
ALTER
column sex set default '男';
---------- END ---------- 

第7关：删除约束

实验要求

在 TESTDB 数据库中删除教师表（teacher）姓名列的唯一约束（约束名：TEACHER_TNAME_KEY）。

---------- BEGIN ---------- 
ALTER TABLE teacher drop constraint TEACHER_TNAME_KEY;
---------- END ---------- 

第8关：添加字段

实验要求

在 TESTDB 数据库的教师表（teacher）中添加教师的电子邮箱（email，VARCHAR(6)）和办公地点（address，VARCHAR(20)）两列。

---------- BEGIN ---------- 
ALTER TABLE teacher add email VARCHAR(6);
ALTER TABLE teacher add address VARCHAR(20);
---------- END ---------- 

第9关：修改字段

实验要求

在 TESTDB 数据库中将教师表（teacher）中办公地点（address）的列名改为 office，电子邮箱（email）的数据类型改为VARCHAR(20)。

---------- BEGIN ---------- 
ALTER TABLE teacher rename column address to office;
ALTER TABLE teacher ALTER column email type VARCHAR(20);
---------- END ---------- 

第10关：删除字段

实验要求

在 TESTDB 数据库中删除教师表（teacher）中的办公地点（office）和电子邮箱（email）两个列。

---------- BEGIN ---------- 
ALTER TABLE teacher drop column office;
ALTER TABLE teacher drop column email;
---------- END ---------- 

第11关：添加单条记录

实验要求

在 TESTDB 数据库中给班级表（class）添加一条记录，班级号为 '0051807'，班级名为'电气18级7班'，年级为 '18 级'，学院为'电气'，班级人数为 '38'。

---------- BEGIN ---------- 
INSERT INTO class VALUES ('0051807','电气18级7班','18级','电气','38');
---------- END ---------- 

第12关：添加部分数据

实验要求

在 TESTDB 数据库中给教师表（teacher）中，添加一条教师记录，其中教师工号为'0250'，姓名为'任远'，性别为'男'。

---------- BEGIN ---------- 
INSERT INTO teacher (tno,tname,sex) VALUES ('0250','任远','男');
---------- END ---------- 

第13关：添加多条记录

实验要求

在 TESTDB 数据库中分别给教师表（teacher），班级表（class），课程表（course），教师授课表（teaching）中添加多条记录。

teacher 表所需数据如下：

工号	姓名	性别	职称	出生日期
0014	李欣	男	教授	1969-07-25
0078	张云	女	副教授	1975-11-25
0118	王立	男	高级工程师	1985-04-28
0193	赵玲	女	讲师	1992-09-26
0213	杨梅	女	副教授	1986-06-07
0030	覃刚	男	副教授	1980-02-15

course 表所需数据如下：

课程号	课程名	学分	学时	考核方式
G001	线性代数	3	48	考试
R003	数据结构	3	48	考试
R009	离散数学	3	48	考试
S023	嵌入式系统与编程实验	1	32	考察
G012	大学物理	4	64	考试

class 表所需数据如下：

班级号	班级名	年级	学院	班级人数
0211801	软件18级1班	18级	软件	37
0211903	软件19级3班	19级	软件	35
0211705	软件17级5班	17级	软件	38
0131901	机械19级1班	19级	机械	37

teaching 表所需数据如下：

课程号	工号	班级号	开学日期	教室
G001	0078	0211903	1	A101
G001	0078	0131901	1	A101
R003	0118	0211801	2	S001
R009	0213	0211903	2	S002
S023	0193	0211801	1	S001
G012	0030	0131901	2	B003

---------- BEGIN ---------- 
INSERT INTO teacher VALUES
('0014','李欣','男','教授','1969-07-25'),
('0078','张云','女','副教授','1975-11-25'),
('0118','王立','男','高级工程师','1985-04-28'),        
('0193','赵玲','女','讲师','1992-09-26'), 
('0213','杨梅','女','副教授','1986-06-07'),
('0030','覃刚','男','副教授','1980-02-15');

INSERT INTO course VALUES
('G001','线性代数','3','48','考试'),
('R003','数据结构','3','48','考试'),
('R009','离散数学','3','48','考试'),        
('S023','嵌入式系统与编程实验','1','32','考察'), 
('G012','大学物理','4','64','考试');

INSERT INTO class VALUES
('0211801','软件18级1班','18级','软件','37'),
('0211903','软件19级3班','19级','软件','35'),
('0211705','软件17级5班','17级','软件','38'),
('0131901','机械19级1班','19级','机械','37');

INSERT INTO teaching VALUES
('G001','0078', '0211903','1','A101'),
('G001','0078', '0131901','1','A101'),
('R003','0118', '0211801','2','S001'),
('R009','0213', '0211903','2','S002'),
('S023','0193', '0211801','1','S001'),
('G012','0030', '0131901','2','B003');
---------- END ---------- 

第14关：修改数据

实验要求

1. 大学物理课程的考核方式发生了改变，从原先“考试”改为“考察”，请修改课程表（course）中对应的数据。
2. 新学年开始，有 3 名同学转专业进入 软件19级3班，更新班级表（class）中班级人数。

---------- BEGIN ---------- 
UPDATE course
SET examination = '考察'
WHERE cname = '大学物理';

UPDATE class
SET gnum = '38'
WHERE gname = '软件19级3班';
---------- END ---------- 

第15关：删除数据

实验要求

新学年开始，17级班级已毕业，从班级表（class）中删除17级班级的信息。

---------- BEGIN ---------- 
DELETE FROM class WHERE grade = '17级';
---------- END ----------