数据库实验sql命令文档格式.docx

1、（CHAR型），工程项目代码JNO（CHAR型），供应数量QTY（INT型）。CREATE TABLE SPJ PNO CHAR, JNO CHAR, QTY INT2.修改基本表1）在P 表中加入属性零件产地CITY（CHAR 型）。ALTER TABLE P ADD CITY CHAR2）将P 表中的属性WEIGHT 类型改为SMALLINT 型。ALTER TABLE P ALTER COLUMN WEIGHT SMALLINT3）删除刚才在P 表中加入的零件产地CITY 属性。ALTER TABLE P DROP COLUMN CITY3.删除基本表1）在所有操作结束后删除S 表。DR

2、OP TABLE S2）在所有操作结束后删除P 表。DROP TABLE P3）在所有操作结束后删除J 表。DROP TABLE J4）在所有操作结束后删除SPJ 表DROP TABLE SPJ二、索引操作1.建立索引1）在S 表上建立关于SNO 的唯一索引。CREATE UNIQUE INDEX SUPPLY_SNO ON S（SNO）2）在J 表上建立关于JNO 升序的唯一索引。CREATE UNIQUE INDEX PROJECT_JNO ON J（JNO ASC）2.删除索引1）删除S 表上的索引supply_sno。DROP INDEX S.SUPPLY_SNO2）删除J 表上的索引

3、project_jno。DROP INDEX J.PROJECT_JNO三、视图操作1.建立视图在插入数据的S,P,J,SPJ 基本表上为三建工程项目记录建立一个视图V_SPJ，包括供应商代码SNO，零件代码PNO，供应数量QTY。CREATE VIEW V_SPJ AS SELECT SNO,PNO,QTY FROM S,P,J,SPJ2.根据视图完成查询1）查询三建工程项目中供应商S1 的供应情况。SELECT *FROM V_SPJWHERE SNO=S12）查询三建工程项目使用的各种零件代码及其数量。SELECT PNO,QTY3.删除视图在操作结束后删除视图V_SPJ。DROP VI

4、EW V_SPJ（二）数据操作一、更新操作1.插入数据1）向S 表插入下列数据：S1,精益,20,天津S2,盛锡,10,北京S3,东方红,30,北京S4,丰泰盛,20,天津S5,为民,30,上海INSERT INTO S VALUES（S1, 精益,20,天津）INSERT INTO S VALUES（S2, 盛锡,10,北京）INSERT INTO S VALUES（S3, 东方红,30,北京）INSERT INTO S VALUES（S4, 丰泰盛,20,天津）INSERT INTO S VALUES（S5, 为民,30,上海）2）向P 表插入下列数据：P1,螺母,红,12P2,螺栓,绿,

5、17P3,螺丝刀,蓝,14P4,螺丝刀,红,14P5,凸轮,蓝,40P6,齿轮,红,30INSERT INTO P VALUES（P1, 螺母,红,12）INSERT INTO P VALUES（P2, 螺栓,绿,17）INSERT INTO P VALUES（P3, 螺丝刀,蓝,14）INSERT INTO P VALUES（P4, 螺丝刀,红,14）INSERT INTO P VALUES（P5, 凸轮,蓝,40）INSERT INTO P VALUES（P6, 齿轮,红,30）3）向J 表插入下列数据：J1,三建,北京J2,一汽,长春J3,弹簧厂,天津J4,造船厂,天津J5,机车厂,唐山

6、J6,无线电厂,常州J7,半导体厂,南京INSERT INTO J VALUES（J1,三建,北京）INSERT INTO J VALUES（J2,一汽,长春）INSERT INTO J VALUES（J3,弹簧厂,天津）INSERT INTO J VALUES（J4,造船厂,天津）INSERT INTO J VALUES（J5,机车厂,唐山）INSERT INTO J VALUES（J6,无线电厂,常州）INSERT INTO J VALUES（J7,半导体厂,南京）4）向SPJ 表插入下列数据：S1,P1,J1,200S1,P1,J3,100S1,P1,J4,700S2,P3,J1,400

7、S1,P2,J2,100S2,P3,J2,200S2,P3,J4,500S2,P3,J5,400S2,P5,J1,400S2,P5,J2,100S3,P1,J1,200S3,P3,J1,200S4,P5,J1,100S4,P6,J3,300S4,P6,J4,200S5,P2,J4,100S5,P3,J1,200S5,P6,J2,200S5,P6,J4,500INSERT INTO SPJ VALUES（S1, P1,J1,200）INSERT INTO SPJ VALUES（S1, P1,J3,100）INSERT INTO SPJ VALUES（S1, P1,J4,700）INSERT IN

8、TO SPJ VALUES（S2, P3,J1,400）INSERT INTO SPJ VALUES（S1, P2,J2,100）INSERT INTO SPJ VALUES（S2, P3,J2,200）INSERT INTO SPJ VALUES（S2, P3,J4,500）INSERT INTO SPJ VALUES（S2, P3,J5,400）INSERT INTO SPJ VALUES（S2, P5,J1,400）INSERT INTO SPJ VALUES（S2, P5,J2,100）INSERT INTO SPJ VALUES（S3, P1,J1,200）INSERT INTO S

9、PJ VALUES（S3, P3,J1,200）INSERT INTO SPJ VALUES（S4, P5,J1,100）INSERT INTO SPJ VALUES（S4, P6,J3,300）INSERT INTO SPJ VALUES（S4, P6,J4,200）INSERT INTO SPJ VALUES（S5, P2,J4,100）INSERT INTO SPJ VALUES（S5, P3,J1,200）INSERT INTO SPJ VALUES（S5, P6,J2,200）INSERT INTO SPJ VALUES（S5, P6,J4,500）2.修改数据1）将全部红色零件的颜

10、色改成蓝色。UPDATE P SET COLOR=蓝色WHERE COLOR =红色2）将由S5 供给J4 的零件P6 改成由S3 供应。UPDATE SPJSET SNO=S3WHERE SNO=S5 ADD JNO=J4 ADD PNO=P63.删除数据从供应商关系S 中删除S2 的纪录，并从供应情况关系SPJ 中删除相应的纪录。DELETEFROM SWHERE SNO=S2FROM SPJ二、查询操作1.单表查询1）找出所有供应商的姓名和所在城市。SELECT SNAME,CITY2）找出所有零件的名称、颜色、重量。SELECT PNAME,COLOR,WEIGHTFROM P3）查询

11、供应工程J1 零件的供应商号码SNO。SELECT SNOWHERE JNO=J14）查询供应工程J1 零件P1 的供应商号码SNO。WHERE JNO=J1 AND PNO=P12.连接查询1）查询供应工程J1 零件为红色的供应商号码SNO。FROM P,SPJWHERE P.PNO=SPJ.PNO AND JNO=J1 AND COLOR=红2）查询工程项目J2 使用的各种零件的名称及其数量。SELECT PNAME,QTYWHERE P.PNO=SPJ.PNO AND JNO=J23）查询使用上海产的零件的工程名称。SELECT JNAMEFROM S,P,SPJWHERE S.SNO=

12、SPJ.SNO AND P.PNO=SPJ.PNO AND S.CITY=上海3.嵌套查询WHERE PNO IN （SELECT PNOWHERE COLOR=红）2）查询没有使用天津供应商生产的红色零件的工程号JNO。SELECT JNOWHERE SNO IN （SELECT SNOWHERE CITY!=天津）AND PNO IN （SELECT PNO3）查询至少用了供应商S1 所提供的全部零件的工程号JNO。WHERE SNO=S14）查询上海厂商供应的所有零件号码。SELECT PNOWHERE CITY=上海）5）查询使用上海产的零件的工程名称。FROM JWHERE JNO

13、IN （SELECT JNOWHERE CITY=上海）6）查询没有使用天津产的零件的工程号码。实验二：安全性（一）授权与回收。 【例1】 授权。在 KingbaseES 中建立多个用户，给他们赋予不同的权限，然后查看是否真正拥有被授予的权限了。1） 建立用户 U1、U2、U3、U4、U5、U6、U7，选择全部为 Connect 角色。点击管理-用户管理-新建用户，建立U1-U7用户 2） 在 SYSTEM（即 DBA）与七个 Connect 用户之间进行授权。1-1把查询 Student 表的权限授给用户 U1。 grant select on student to U11-2把对Stude

14、nt 表和Course表的全部操作权限授予用户 U2 和 U3。 grant all on student to U2 grant all on student to U3 grant all on course to U2 grant all on course to U31-3把对表 SC 的查询权限授予所有用户。 grant select on sc to U1grant select on sc to U2grant select on sc to U3grant select on sc to U4grant select on sc to U5grant select on sc

15、to U6grant select on sc to U71-4把查询 Student 表和修改学生学号的权限授给用户 U4。grant select on student to U4grant update（sno） on student to U41-5把对表 SC 的 INSERT 权限授予 U5，并允许 U5 将此权限再授予其他用户。 grant insert on sc to U5 with grant option1-6用户U5 将对表 SC 的 INSERT 权限授予 U6,并允许将权限转授给其他用户。切换至用户5，grant insert on sc to u6 with gr

16、ant option实验三：完整性1. 实体完整性 定义表的主码。关系模型的实体完整性在 CREATE TABLE 中用 PRIMARY KEY 定义。 定义主码的方法分为定义为列级约束条件和定义为表级约束条件两种。4-1定义表 Student，并将其中的 Sno 属性定义为主码。create table student（sno char（7） primary key,sname char（8）,ssex char（2）,sage smallint,sdept char（20）4-2定义表 SC,将其中的属性 Sno,Cno 定义为主码。对于多个属性构成的码，只能够将其定义为表级约束条件，而无

17、法用列级约束条件来实现。create table sc（sno char（9）, cno char（4）, grade smallint, primary key（sno,cno）参照完整性。定义表的外码。关系模型的参照完整性是在 CREATE TABLE 中用 FOREIGN KEY 语句来定义的，并用REFERENCES来指明外码参照的是哪些表的主码。定义表 SC，其中 Sno 参照表 Student的主码 Sno，Cno 参照表 Course 的主码Cno。create table course（cno int unique, cname char（20） not null, tno i

18、nt, credit int, primary key（cno,tno）（sno char（7）, primary key（cno,sno）, foreign key（sno） references student（sno）, foreign key（cno） references course（cno）3. 用户定义完整性。用户定义的属性上的约束条件。6-1列值非空。在定义 SC 表时，Sno、Cno 和Grade 属性都不允许取空值。 在不特别声明的情况下，非码属性的值是允许取空值的。（sno char（9） not null, cno char（4） not null, grade sm

19、allint not null,6-2列值唯一。建立部门表 DEPT，要求部门名称 Dname 取值唯一，部门编号 Deptno 属性为主码。create table dept（dname char（10） unique,deptno numeric（2） primary key6-3CHECK短语指定列值应该满足的条件。 定义表Student， 属性Ssex的值只允许取 “男”或“女” ；定义表 SC，属性 Grade 的值定义在 0100 之间。（sno char（9） primary key, sname char（8）, ssex char（2） check（ssexin（男，女）， sage smallint,） grade smallint check（grade=0 and grade=100）,