数据库 实验报告2 SQL3

《数据库系统概论》实验报告
题目：实验二（3）交互式SQL 语言姓名 Vivian 日期 2005-1-1实验内容及完成情况：（写出每一种类型的SQL测试用例）
本实验中的表名、属性名全部用中文。

（一）数据定义
一、基本表操作
1.建立基本表
1）创建仓库表，由以下属性组成：仓库号（CHAR型），城市（CHAR型），面积（INT型）。

其中仓库号为主码，并且有面积>0的限定条件。

CREATE TABLE 仓库(
仓库号 CHAR(5) PRIMARY KEY,
城市 CHAR(10),
面积 INT CHECK(面积>0));
2）创建职工表，由以下属性组成：仓库号（CHAR型），职工号（CHAR型），工资（INT 型）。

其中职工号为主码，仓库号为外码，工资的限定条件为1000<=工资<=5000，并且设定缺省值为1200。

CREATE TABLE 职工(
仓库号 CHAR(5) REFERENCES 仓库(仓库号),
职工号 CHAR(5) PRIMARY KEY,
工资 INT CHECK (工资>=1000 AND 工资<=5000) DEFAULT 1200);
或者：
CREATE TABLE 职工(
仓库号 CHAR(5) ,
职工号 CHAR(5) PRIMARY KEY,
工资 INT CHECK (工资>=1000 AND 工资<=5000) DEFAULT 1200,
FOREIGN KEY (仓库号) REFERENCES 仓库(仓库号));
3）创建供应商表，由以下属性组成：供应商号（CHAR型），供应商名（CHAR型），地址（CHAR型）。

其中供应商号为主码。

CREATE TABLE 供应商(
供应商号 CHAR(5) PRIMARY KEY,
供应商名 CHAR(20),
地址 CHAR(20));
4）创建订购单表，由以下属性组成：职工号（CHAR型），供应商号（CHAR型），订购单号（CHAR型），订购日期（CHAR型）。

其中订购单号为主码，职工号和供应商号为外码，订购日期的缺省值为当前系统日期。

CREATE TABLE 订购单(
职工号 CHAR(5) NOT NULL REFERENCES 职工,
供应商号 CHAR(5) REFERENCES 供应商,
订购单号 CHAR(5) PRIMARY KEY,
订购日期 DATE DEFAULT NOW());
2.修改基本表
1)往订购单表中增加一个新的属性“完成日期”，DATE型，并且允许为空值。

ALTER TABLE 订购单 ADD 完成日期 DATE;
2)将职工表中的工资属性类型改为SMALLINT型。

ALTER TABLE 职工 ALTER COLUMN 工资 SMALLINT;
3)删除刚才在订购单表中加入的“完成日期”属性。

ALTER TABLE 订购单 DROP 完成日期;
3.删除基本表
1）在所有操作结束后删除仓库表。

DROP TABLE 仓库;
2）在所有操作结束后删除职工表。

DROP TABLE 职工;
3）在所有操作结束后删除供应商表。

DROP TABLE 供应商;
4）在所有操作结束后删除订购单表。

DROP TABLE 订购单;
二、索引操作
1.建立索引
1）在订购单表上建立关于供应商号的普通索引。

CREATE INDEX supidx ON 订购单(供应商号);
2)在订购单表的多个字段建立索引，先按照供应商号升序索引，然后按照职工号升序索引。

CREATE INDEX sup_empidx ON 订购单(供应商号，职工号);
2.删除索引
1）删除订购单表上的索引supidx。

DROP INDEX supidx;
2）删除订购单表上的索引sup_empidx。

DROP INDEX sup_empidx;
三、视图操作
1.建立视图
1）创建视图，提供职工号、职工工资和职工工作所在城市的信息。

CREATE VIEW v_emp AS
SELECT 职工号,工资,城市
FROM 职工,仓库
WHERE 职工.仓库号 = 仓库.仓库号;
2)创建视图，给出有北京仓库订购单的北京供应商的名称。

CREATE VIEW v_sample AS
SELECT 供应商名
FROM 供应商
WHERE 地址 = ‘北京’
AND 供应商号 IN
(SELECT 供应商号
FROM 订购单
WHERE 职工号 IN
(SELECT 职工号
FROM 职工
WHERE 仓库号 IN
(SELECT 仓库号
FROM 仓库
WHERE 城市 = ‘北京’)));
2.根据视图完成查询
1）查询在北京工作的职工的职工号和工资。

SELECT 职工号,工资
FROM v_emp
WHERE 城市 = ‘北京’;
查询结果为：E3 1210
E7 1250
2）查询有北京仓库订购单的北京供应商的名称。

SELECT * FROM v_sample;
查询结果为：华通电子公司
爱华电子厂
3.删除视图
在操作结束后删除视图v_emp和v_sample。

drop view v_emp;
drop view v_sample;
（二）数据操作
一、更新操作
1.插入数据
1）向仓库表插入下列数据：WH1,北京,370
WH2,上海,500
WH3,广州,200
WH4,武汉,400
INSERT INTO 仓库 VALUES('WH1','北京',370);
INSERT INTO 仓库 VALUES('WH2','上海',500);
INSERT INTO 仓库 VALUES('WH3','广州',200);
INSERT INTO 仓库 VALUES('WH4','武汉',400);
2）向职工表插入下列数据：WH2,E1,1220
WH4,E2,1270
WH1,E3,1210
WH2,E4,1250
WH3,E5,1200
WH3,E6,1230
WH1,E7,1250
INSERT INTO 职工 VALUES('WH2','E1',1220);
INSERT INTO 职工 VALUES('WH4','E2',1270);
INSERT INTO 职工 VALUES('WH1','E3',1210);
INSERT INTO 职工 VALUES('WH2','E4',1250);
INSERT INTO 职工 VALUES('WH3','E5',1200);
INSERT INTO 职工 VALUES('WH3','E6',1230);
INSERT INTO 职工 VALUES('WH1','E7',1250);
3）向供应商表插入下列数据：S1,利民电子元件厂,上海
S2,联华电子公司,武汉
S3,振华电子厂,西安
S4,华通电子公司,北京
S5,607厂,郑州
S7,爱华电子厂,北京 INSERT INTO 供应商 VALUES('S1','利民电子元件厂','上海');
INSERT INTO 供应商 VALUES('S2','联华电子公司','武汉');
INSERT INTO 供应商 VALUES('S3','振华电子厂','西安');
INSERT INTO 供应商 VALUES('S4','华通电子公司', '北京');
INSERT INTO 供应商 VALUES('S5','607厂','郑州');
INSERT INTO 供应商 VALUES('S7','爱华电子厂','北京');
4）向订购单表插入下列数据：E3,S7,OR67,20020623
E1,S4,OR73,20020728
E7,S4,OR76,20020525
E6,NULL,OR77,NULL
E3,S5,OR79,20020613
E1,NULL,OR80,NULL
E3,NULL,OR90,NULL
E3,S3,OR91,20020713 INSERT INTO 订购单 VALUES('E3','S7','OR67','20020623');
INSERT INTO 订购单 VALUES('E1','S4','OR73','20020728');
INSERT INTO 订购单 VALUES('E7','S4','OR76','20020525');
INSERT INTO 订购单 VALUES('E6',NULL,'OR77',NULL);
INSERT INTO 订购单 VALUES('E3','S5','OR79','20020613');
INSERT INTO 订购单 VALUES('E1',NULL,'OR80',NULL);
INSERT INTO 订购单 VALUES('E3',NULL,'OR90',NULL);
INSERT INTO 订购单 VALUES('E3','S3','OR91','20020713');
2.修改数据
1)将武汉仓库的职工工资提高10%。

UPDATE 职工
SET 工资 = 工资*1.10
FROM 仓库
WHERE 仓库.仓库号 = 职工.仓库号
AND 城市 = ‘武汉’;
2）将S6经手的订购单的订购日期改成20050101。

UPDATE 订购单
SET 订购日期 = ‘20050101’
WHERE 职工号 = ‘E6’;
3.删除数据
删除所在城市是上海的仓库的所有职工元组。

DELETE FROM 职工
WHERE仓库号 IN(
SELECT 仓库号
FROM 仓库
WHERE 城市 =‘上海’);
二、查询操作
1.单表查询
1）查询工资多于1210元的职工所在仓库的仓库号。

SELECT DISTINCT 仓库号
FROM 职工
WHERE 工资 > 1210;
查询结果为：WH1
WH2
WH3
WH4
2）查询尚未确定供应商的订购单。

SELECT *
FROM 订购单
WHERE 供应商号 IS NULL;
查询结果为：E6,NULL,OR77,NULL
E1,NULL,OR80,NULL
E3,NULL,OR90,NULL
3）先按仓库号，后按工资排序输出全部职工信息。

SELECT *
FROM 职工
ORDER BY 仓库号,工资;
查询结果为：WH1 E3 1210
WH1 E7 1250
WH2 E1 1220
WH2 E4 1250
WH3 E5 1200
WH3 E6 1230
WH4 E2 1270
4）查询至少有两个职工的仓库的职工平均工资。

SELECT 仓库号,COUNT(*),AVG(工资)
FROM 职工
GROUP BY 仓库号
HAVING COUNT(*) >= 2;
查询结果为：WH1 2 1230
WH3 2 1215
WH2 2 1235
2.连接查询
1）查询工作在面积大于400的仓库的职工号及这些职工工作的城市。

SELECT 职工号,城市
FROM 仓库,职工
WHERE 面积 > 400
AND 职工.仓库号 = 仓库.仓库号;
查询结果为：E1 上海
E4 上海
2）查询与上海的仓库有联系的供应商个数。

SELECT COUNT(DISTINCT 供应商号)
FROM 职工,仓库,订购单
WHERE 职工.职工号 = 订购单.职工号
AND 仓库.仓库号 = 职工.仓库号
AND 城市 = '上海';
查询结果为：1
3）查询由工资多于1230元的职工向北京的供应商发出的订购单号。

SELECT 订购单号
FROM 订购单,供应商,职工
WHERE 订购单.供应商号 = 供应商.供应商号
AND 订购单.职工号 = 职工.职工号
AND 工资>1230
AND 地址 = '北京';
查询结果为：OR76
3.嵌套查询
1）查询所有职工工资都多于1210元的仓库的信息。

SELECT *
FROM 仓库
WHERE 仓库号 NOT IN
(SELECT 仓库号
FROM 职工
WHERE 工资 <= 1210);
查询结果为：WH2 上海 500
WH4 武汉 400
2）查询向北京的供应商发出了订购单的仓库所在城市。

SELECT 城市
FROM 仓库
WHERE 仓库号 IN
(SELECT 仓库号
FROM 职工
WHERE 职工号 IN
(SELECT 职工号
FROM 订购单
WHERE 供应商号 IN
(SELECT 供应商号
FROM 供应商
WHERE 地址 = '北京')));
查询结果为：上海
北京
3）查询有职工工资大于或等于“WH1”仓库中所有职工工资的仓库号。

SELECT DISTINCT 仓库号
FROM 职工
WHERE 工资 >= ALL
(SELECT 工资
FROM 职工
WHERE 仓库号 ='WH1');
查询结果为：WH1
WH2
WH4
4)查询仓库中还没有职工的仓库信息。

SELECT *
FROM 仓库
WHERE NOT EXISTS
(SELECT *
FROM 职工
WHERE 仓库号 = 仓库.仓库号);
查询结果为：无
出现的问题及解决方案：
1.在创建基本表时是否可以缺省主码？
可以。

在定义基本表时可以定义主码也可以先不定义主码。

2.书中的例题基本表的修改，执行ALTER TABLE 职工 MODIFY 工资 SMALLINT执行失败
当基本表中没有数据时，KingbaseES允许对基本表的属性类型进行修改，如上述修改可以写成ALTER TABLE 职工 ALTER COLUMN 工资 SMALLINT，执行成功。

但如果基本表中已经存有数据时，系统则会给出数据将丢失的警告，不允许进行属性类型的修改。

3.在SQL中没有提供删除属性列的语句，KingbaseES支持删除属性列。

如要在基本表仓库上删除属性面积，可以执行ALTER TABLE 仓库 DROP 面积。

4.在创建索引的时候，不支持DESC关键字，只能建立升序索引。

SQL标准对索引的升序和降序并没有规定，因此KingbaseES系统为了提高查询性能，节省开支，没有索引的升序降序概念，不能创建降序索引。

5.在创建表指定外码时，SQL Server和KingbaseES有所不同。

在SQL Server中，指定外码是在该属性后使用FOREIGN KEY REFERENCES 表名[属性名]。

而在KingbaseES中，可以在外码属性的后面加上REFERENCES 表名[属性名]， 也可以在定义完属性后加上“FOREIGN KEY (外码) REFERENCES 表名(属性名)。

具体情况请参考本实验报告数据定义部分。

6.SQL Server和KingbaseES支持的数据类型和支持的函数有所不同。

在创建订购单表时，订购日期的类型和默认值在SQL Server中类型是用DATETIME，当前时间的函数为getdate()。

而在KingbaseES中，属性类型使用的是DATE，获取当前时间的函数为NOW()。

需要了解更详细的情况， V4.1版本读者可以参考KingbaseES联机帮助文档。