大型機(jī)數(shù)據(jù)庫系統(tǒng)DB2上機(jī)實(shí)驗(yàn)(共13頁)

1、大型機(jī)數(shù)據(jù)庫技術(shù)(jsh)實(shí)驗(yàn)報告1 學(xué)號 201393099 姓名(xngmng) 袁 繡 時間(shjin) 2015年 11月27日 成績 在數(shù)據(jù)庫DSNDUTDB中創(chuàng)建自己(zj)的表空間 在自己(zj)的表空間中創(chuàng)建的表查詢(chxn)當(dāng)前日期，當(dāng)前時間戳 Sql:select current timestamp from sysibm.sysdummy1通過菜單方式查詢數(shù)據(jù)庫DBN8D91A通過SQL語句進(jìn)行查詢數(shù)據(jù)庫DBN8D91ADSNDB06中的有哪些表空間？ DSNDB06中有哪些(nxi)系統(tǒng)表？SQL:SELECT * FROM SYSIBM.SYSDATABASE W

2、HERE NAME = DSNDB06AND CREATOR= SYSIBM 在DSN8D91A中找輸入(shr)EMP查找,驗(yàn)證(ynzhng)EMP表存在SPUFI方式創(chuàng)建表空間 DB2 ADMIN創(chuàng)建(chungjin)表空間 查詢(chxn)表空間SQL:SELECT * FROM SYSIBM.SYSTABLESPACE WHERE NAME LIKE ST312TS%更改(gnggi)表空間刪除(shnch)表空間 在數(shù)據(jù)庫DSN8D91A中自己建立(jinl)的表空間內(nèi)，建立表emp和deptCREATE TABLE EMP (EMPNO CHAR(4) NOT NULL, EN

3、AME VARCHAR(10), JOB VARCHAR(9), MGR CHAR(4), HIREDATE DATE, SAL DECIMAL(7,2), COMM DECIMAL(7,2), DEPTNO CHAR(2) ) IN DSN8D91A.ST312TS3; INSERT INTO EMP (EMPNO ,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO) VALUES (7369,SMITH,CLERK,7902,1980-12-17,800,20); INSERT INTO EMP (EMPNO ,ENAME,JOB,MGR,HIREDATE,SAL,COM

4、M,DEPTNO) VALUES (7499,ALLEN,SALESMAN,7698,1981-02-20,1600,300,30);INSERT INTO EMP (EMPNO ,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) VALUES (7521,WARD,SALESMAN,7698,1981-02-22,1250,500,30);INSERT INTO EMP (EMPNO ,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO) VALUES (7566,JONES,MANAGER,7839,1981-04-02,2975,20); I

5、NSERT INTO EMP (EMPNO ,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) VALUES (7654,MARTIN,SALESMAN,7689,1981-09-28,1250,1400,20);INSERT INTO EMP (EMPNO ,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO) VALUES (7698,BLACK,MANAGER,7839,1981-05-01,2850,30); INSERT INTO EMP (EMPNO ,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO) VALUES

6、(7782,SCOTT,ANALYST,7566,1981-06-09,3000,20); INSERT INTO EMP (EMPNO ,ENAME,JOB,HIREDATE,SAL,DEPTNO) VALUES (7793,KING,PERSIDENT,1981-11-17,5000,10);表deptCREATE TABLE DEPT (DEPTNO CHAR(2) NOT NULL, DNAME VARCHAR(14),LOC VARCHAR(13) IN DSN8D91A.ST312TS4; INSERT INTO DEPT (DEPTNO,DNAME,LOC)VALUES (10,

7、ACCOUNTING,NEW YORK); INSERT INTO DEPT (DEPTNO,DNAME,LOC) VALUES (20,RESEARCH,DALLAS ); INSERT INTO DEPT (DEPTNO,DNAME,LOC) VALUES (30,SALES ,CHICAGO ); INSERT INTO DEPT DEPTNO,DNDEPT,LOC) VALUES (40,OPERATIONS,BOSTON ) 為表employee添加(tin ji)salary0約束，刪除該約束ALTER TABLE EMP ADD CONSTRAINT SALCHECK CHECK

8、(SAL0); ALTER TABLE EMP DROP CONSTRAINT SALCHECK;添加(tin ji)20000salary0約束ALTER TABLE EMP ADD CONSTRAINT SALCHECK CHECK (SAL 0 AND SAL 20000);插入一行，sal =3000，提示(tsh)錯誤如下 SQL:INSERT INTO EMP (EMPNO,SAL) VALUES (1000,30000);為表employee添加(tin ji)一列deptno，其定義(dngy)與dept中deptno一樣，并添加外鍵引用關(guān)系CREATE UNIQUE INDE

9、X DEPT_UNI ON DEPT(DEPTNO); CREATE UNIQUE INDEX EMP_UNI ON EMP(EMPNO); ALTER TABLE DEPT ADD CONSTRAINT DEPTNO_PK PRIMARY KEY (DEPTNO); ALTER TABLE EMP ADD CONSTRAINT EMPNO_PK PRIMARY KEY (EMPNO); ALTER TABLE EMP ADD CONSTRAINT DEPTNO_FK FOREIGN KEY (DEPTNO) REFERENCES DEPT; 刪除外鍵：ALTER TABLE EMP DROP

10、 CONSTRAINT DEPTNO_PK詢出剛建立的表的信息以及其所含有的各列信息SQL:SELECT * FROM DEPT SQL:SELECT * FROM EMP 建立一個名為test的表，然后刪除 查詢(chxn)當(dāng)前日期，當(dāng)前時間戳創(chuàng)建(chungjin)一個包含所有工資高于4000的員工(yungng)視圖創(chuàng)建一個工資低于4000的所有員工視圖（待with check option） SQL:CREATE VIEW STAFF2 AS SELECT * FROM EMP WHERE SAL 4000 WITH CHECK OPTION 創(chuàng)建一個員工及其所在部門信息的視圖SQL:CREATE VIEW STAFF AS SELECT ENAME,DNAME FROM EMP,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO 查詢系統(tǒng)表中，自己所創(chuàng)建的視圖信息SQL:SELECT * FROM STAFF 刪除(shnch)視圖練習(xí)(linx)對帶with check option和不帶with check option的視圖進(jìn)行(jnxng)更新在employee表的empno上創(chuàng)建一個唯一單索引在dept表的deptno上創(chuàng)建一個唯一單索引在系統(tǒng)表中查詢已經(jīng)(y jing)創(chuàng)建的索引 刪除已