实验五表的操作.docx

1、实验五表的操作实验五(表的操作)部门： xxx 时间： xxx整理范文，仅供参考，可下载自行编辑1. 查询Scott用户下的所有表：2. 查询相关表的具体内容及相关表记录的操作Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 b5E2RGbCAPConnected as system+SQL SHOW SCOTT.EMP。SQL DESC SCOTT.EMP。Name Type 77 Default Comments - - - - - EMPNO NUMBER(4 ENAME VARCHAR2(10

2、Y JOB VARCHAR2(9 Y MGR NUMBER(4 Y HIREDATE DATE Y SAL NUMBER(7,2 Y COMM NUMBER(7,2 Y DEPTNO NUMBER(2 Y SQL DESC SCOTT.DEPT。Name Type Null able Default Comments - - - - - DEPTNO NUMBER(2 DNAME VARCHAR2(14 Y LOC VARCHAR2(13 Y SQL DESC SCOTT.BONUS。Name Type Null able Default Comments - - - - - ENAME VA

3、RCHAR2(10 Y JOB VARCHAR2(9 Y SAL NUMBER Y COMM NUMBER Y SQL DESC SCOTT.SALGRADE。Name Type Null able Default Comments - - - - - GRADE NUMBER Y LOSAL NUMBER Y HISAL NUMBER Y SQL SELECT TO_DATE(1992-09-08,YYYY-MM-DD。SELECT TO_DATE(1992-09-08,YYYY-MM-DDORA-00923: 未找到要求的 FROM 关键字SQL SELECT TO_DATE(1992-0

4、9-08,YYYY-MM-DD FROM DUAL。p1EanqFDPwTO_DATE(1992-09-08,YYYY-MM-1992-9-8Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 DXDiTa9E3dConnected as systemSQL SELECT TO_DATE(1992-09-09,YYYY-MM-DD FROM DUAL。RTCrpUDGiTTO_DATE(1992-09-09,YYYY-MM-1992-9-9SQL SELECT TO_DATE(19910218,YYYYM

5、MDD FROM DUAL。5PCzVD7HxATO_DATE(19910218,YYYYMMDD-1991-2-18SQL SELECT China|Beijing FROM DUAL。CHINA|BEIJING-ChinaberrySQL SELECT 1+2 FROM DUAL。 1+2- 3SQL CREATE TABLE SCOTT.XS( 2 XM VARCHAR(10, 3 XH VARCHAR(10 PRIMARY KEY, 4 NL NUMERICTABLESPACE SCOTT。CREATE TABLE SCOTT.XS(XM VARCHAR(10,XH VARCHAR(1

6、0 PRIMARY KEY,NL NUMERICTABLESPACE SCOTTORA-00959: 表空间 SCOTT 不存在SQL CREATE TABLE XS( 2 2 XM VARCHAR(10, 3 3 XH VARCHAR(10 PRIMARY KEY, 4 4 NL NUMERICTABLESPACE 5 SYSTEM。CREATE TABLE XS(XM VARCHAR(10, 3 XH VARCHAR(10 PRIMARY KEY, 4 NL NUMERICTABLESPACESYSTEMORA-00904: : 标识符无效SQL CREATE TABLE SCOTT.XS

7、( 2 XH VARCHAR(10 PRIMARY KEY, 3 XM VARCHAR(10, 4 NL NUMERICTABLESPACE SYSTEM。Table createdSQL INSERT INTO SCOTT.XS VALUES(1,张三,18。1 row insertedSQL INSERT INTO XS1 SELECT * FROM SCOTT.XS 。INSERT INTO XS1 SELECT * FROM SCOTT.XSORA-00942: 表或视图不存在SQL INSERT INTO XS VALUES(2,李四,19。INSERT INTO XS VALUES

8、(2,李四,19ORA-00942: 表或视图不存在SQL INSERT INTO SCOTT.XS VALUES(2,李四,19。1 row insertedSQL INSERT INTO SCOTT.XS VALUES(3,王五,20。1 row insertedSQL CREATE TABLE SCOTT.XS1 AS SELECT * FROM SCOTT.XS。jLBHrnAILgTable createdSQL UPDATE SCOTT.XS1 SET XH=4 WHERE XM=王五。1 row updatedSQL DELETE FROM SCOTT.XS1 WHERE XH=

9、2。1 row deletedSQL MERGE INTO SCOTT.XS1 2 USING SCOTT.XS ON(SCOTT.XS1.XH=SCOTT.XS.XH 3 WHEN MATCHED THEN UPDATE SET (SCOTT.XS1.XH=SCOTT.XS.XH,SCOTT.XS1.XM=SCOTT.XS.XM,SCOTT.XS1.NL=SCOTT.XS.NL。xHAQX74J0XMERGE INTO SCOTT.XS1USING SCOTT.XS ON(SCOTT.XS1.XH=SCOTT.XS.XHWHEN MATCHED THEN UPDATE SET (SCOTT.

10、XS1.XH=SCOTT.XS.XH,SCOTT.XS1.XM=SCOTT.XS.XM,SCOTT.XS1.NL=SCOTT.XS.NLLDAYtRyKfEORA-01747: user.table.column, table.column 或列说明无效SQL MERGE INTO SCOTT.XS1 2 USING SCLTT.XS ON(SCOTT.XS1.XH=SCOTT.XS.XH 3 WHEN MATCHED THEN UPDATE SET SCOTT.XS1.XH=SCOTT.XS.XH,SCOTT.XS1.XM=SCOTT.XS.XM,SCOTT.XS1.NL=SCOTT.XS.

11、NL。Zzz6ZB2LtkMERGE INTO XS1USING XS ON(XS1.XH=XS.XHWHEN MATCHED THEN UPDATE SET SCOTT.XS1.XH=SCOTT.XS.XH,SCOTT.XS1.XM=SCOTT.XS.XM,SCOTT.XS1.NL=SCOTT.XS.NLdvzfvkwMI1ORA-00942: 表或视图不存在SQL SQL MERGE INTO SCOTT.XS1 2 2 USING SCLTT.XS ON(SCOTT.XS1.XH=SCOTT.XS.XHrqyn14ZNXI 3 3 WHEN MATCHED THEN UPDATE SET

12、 SCOTT.XS1.XH=SCOTT.XS.XH,SCOTT.XS1.XM=SCOTT.XS.XM,SCOTT.XS1.NL=SCOTT.XS.NL。EmxvxOtOcoMERGE INTO SCOTT.XS1 2 USING SCLTT.XS ON(SCOTT.XS1.XH=SCOTT.XS.XH 3 WHEN MATCHED THEN UPDATE SET SCOTT.XS1.XH=SCOTT.XS.XH,SCOTT.XS1.XM=SCOTT.XS.XM,SCOTT.XS1.NL=SCOTT.XS.NLSixE2yXPq5ORA-02018: 缺失 USING 关键字SQL MERGE

13、INTO SCOTT.XS1 2 USING SCOTT.XS ON(SCOTT.XS1.XH=SCOTT.XS.XH 3 WHEN MATCHED THEN UPDATE SET SCOTT.XS1.XH=SCOTT.XS.XH SCOTT.XS1.XM=SCOTT.XS.XM SCOTT.XS1.NL=SCOTT.XS.NL6ewMyirQFL 4 WHEN NOT MATCHED THEN INSERT VALUES(SCOTT.XS1.XH=SCOTT.XS.XH SCOTT.XS1.XM=SCOTT.XS.XM SCOTT.XS1.NL=SCOTT.XS.NL。kavU42VRUsM

14、ERGE INTO SCOTT.XS1USING SCOTT.XS ON(SCOTT.XS1.XH=SCOTT.XS.XHWHEN MATCHED THEN UPDATE SET SCOTT.XS1.XH=SCOTT.XS.XH SCOTT.XS1.XM=SCOTT.XS.XM SCOTT.XS1.NL=SCOTT.XS.NLy6v3ALoS89WHEN NOT MATCHED THEN INSERT VALUES(SCOTT.XS1.XH=SCOTT.XS.XH SCOTT.XS1.XM=SCOTT.XS.XM SCOTT.XS1.NL=SCOTT.XS.NLM2ub6vSTnPORA-00

15、933: SQL 命令未正确结束SQL MERGE INTO SCOTT.XS1 2 USING SCOTT.XS ON(SCOTT.XS1.XH=SCOTT.XS.XH 3 WHEN MATCHED THEN SET SCOTT.XS1.XH=SCOTT.XS.XH, SCOTT.XS1.XM=SCOTT.XS.XM,SCOTT.XS1.NL=SCOTT.XS.NL。0YujCfmUCwMERGE INTO SCOTT.XS1USING SCOTT.XS ON(SCOTT.XS1.XH=SCOTT.XS.XHWHEN MATCHED THEN SET SCOTT.XS1.XH=SCOTT.X

16、S.XH, SCOTT.XS1.XM=SCOTT.XS.XM,SCOTT.XS1.NL=SCOTT.XS.NLeUts8ZQVRdORA-00905: 缺失关键字SQL MERGE INTO SCOTT.XS1 2 USING SCOTT.XS ON(SCOTT.XS1.XH=SCOTT.XS.XH 3 WHEN MATCHED THEN UPDATE SET SCOTT.XS1.XH=SCOTT.XS.XH, SCOTT.XS1.XM=SCOTT.XS.XM,SCOTT.XS1.NL=SCOTT.XS.NL。sQsAEJkW5TMERGE INTO SCOTT.XS1USING SCOTT.

17、XS ON(SCOTT.XS1.XH=SCOTT.XS.XH WHEN MATCHED THEN UPDATE SET SCOTT.XS1.XH=SCOTT.XS.XH, SCOTT.XS1.XM=SCOTT.XS.XM,SCOTT.XS1.NL=SCOTT.XS.NLGMsIasNXkAORA-38104: 无法更新 ON 子句中引用的列: SCOTT.XS1.XHSQL MERGE INTO SCOTT.XS1 2 USING SCOTT.XS ON(XS1.XH=XS.XH 3 WHEN MATCHED THEN UPDATE SET SCOTT.XS1.XH=SCOTT.XS.XH,

18、SCOTT.XS1.XM=SCOTT.XS.XM,SCOTT.XS1.NL=SCOTT.XS.NL。TIrRGchYzgMERGE INTO SCOTT.XS1USING SCOTT.XS ON(XS1.XH=XS.XH WHEN MATCHED THEN UPDATE SET SCOTT.XS1.XH=SCOTT.XS.XH, SCOTT.XS1.XM=SCOTT.XS.XM,SCOTT.XS1.NL=SCOTT.XS.NL7EqZcWLZNXORA-38104: 无法更新 ON 子句中引用的列: XS1.XHSQL MERGE INTO SCOTT.XS1 D 2 USING SCOTT.

19、XS S ON(D.XH=S.XH 3 WHEN MATCHEN THEN UPDATE SET D.XH=S.XH,D.XM=S.XM,D.NL=S.NL。lzq7IGf02EMERGE INTO SCOTT.XS1 DUSING SCOTT.XS S ON(D.XH=S.XHWHEN MATCHEN THEN UPDATE SET D.XH=S.XH,D.XM=S.XM,D.NL=S.NLzvpgeqJ1hkORA-00905: 缺失关键字SQL MERGE INTO SCOTT.XS1 D 2 USING SCOTT.XS S ON(D.XH=S.XH 3 WHEN MATCHED TH

20、EN UPDATE SET D.XH=S.XH,D.XM=S.XM,D.NL=S.NL。NrpoJac3v1MERGE INTO SCOTT.XS1 DUSING SCOTT.XS S ON(D.XH=S.XHWHEN MATCHED THEN UPDATE SET D.XH=S.XH,D.XM=S.XM,D.NL=S.NL1nowfTG4KIORA-38104: 无法更新 ON 子句中引用的列: D.XH注意：在on条件中已经将源表和目标表的主键设为相等，故而在当两个表匹配时只需更新目标表中除了主键以外的其他属性！fjnFLDa5ZoSQL MERGE INTO SCOTT.XS1 D 2

21、USING SCOTT.XS S ON( S.XH=D.XH 3 WHEN NOT MATCHED THEN INSERT ( D.XH,D.XM,D.NL 4 VALUES( S.XH,S.XM,S.NL。DoneSQL MERGE INTO SCOTT.XS1 D 2 USING SCOTT.XS S ON(S.XH=D.XH 3 WHEN MATCHED THEN UPDATED D.XH=S.XH,D.XM=S.XM,D.NL=S.NL。tfnNhnE6e5MERGE INTO SCOTT.XS1 DUSING SCOTT.XS S ON(S.XH=D.XHWHEN MATCHED T

22、HEN UPDATED SET D.XH=S.XH,D.XM=S.XM,D.NL=S.NLHbmVN777sLORA-00905: 缺失关键字SQL MERGE INTO SCOTT.XS1 D 2 USING SCOTT.XS S ON(S.XH=D.XH 3 WHEN MATCHED THEN UPDATE D.XH=S.XH,D.XM=S.XM,D.NL=S.NL。V7l4jRB8HsMERGE INTO SCOTT.XS1 DUSING SCOTT.XS S ON(S.XH=D.XHWHEN MATCHED THEN UPDATE D.XH=S.XH,D.XM=S.XM,D.NL=S.

23、NL83lcPA59W9ORA-00971: 缺失 SET 关键字SQL MERGE INTO SCOTT.XS1 D 2 USING SCOTT.XS S ON(S.XH=D.XH 3 WHEN MATCHED THEN UPDATE SET D.XH=S.XH,D.XM=S.XM,D.NL=S.NL。mZkklkzaaPMERGE INTO SCOTT.XS1 DUSING SCOTT.XS S ON(S.XH=D.XH WHEN MATCHED THEN UPDATE SET D.XH=S.XH,D.XM=S.XM,D.NL=S.NLAVktR43bpwORA-38104: 无法更新 O

24、N 子句中引用的列: D.XHSQL MERGE INTO SCOTT.XS1 D 2 USING SCOTT.XS S ON( D.XH=S.XH 3 WHEN MATCHED THEN UPDATE SET D.XH=S.XH,D.XM=S.XM,D.NL=S.NL。ORjBnOwcEdMERGE INTO SCOTT.XS1 DUSING SCOTT.XS S ON( D.XH=S.XHWHEN MATCHED THEN UPDATE SET D.XH=S.XH,D.XM=S.XM,D.NL=S.NL2MiJTy0dTTORA-38104: 无法更新 ON 子句中引用的列: D.XHSQ

25、L MERGE INTO SCOTT.XS1 D 2 USING SCOTT.XS S ON(D.XH=S.XH 3 WHEN MATCHED THEN 4 UPDATE SET 5 D.XH=S.XH,D.XM=S.XM,D.NL=S.NL。MERGE INTO SCOTT.XS1 DUSING SCOTT.XS S ON(D.XH=S.XHWHEN MATCHED THENUPDATE SETD.XH=S.XH,D.XM=S.XM,D.NL=S.NLORA-38104: 无法更新 ON 子句中引用的列: D.XHSQL INSERT INTO SCOTT.XS VALUES(5ALI,20

26、。INSERT INTO SCOTT.XS VALUES(5ALI,20ORA-00947: 没有足够的值 INSERT INTO SCOTT.XS VALUES(5,ALI,20。1 row insertedSQL MERGE INTO SCOTT.XS1 D 2 USING SCOTT.XS S ON(D.XH=S.XH 3 WHEN MATCHED THEN 4 UPDATE SET 5 D.XH=S.XH,D.XM=S.XM,D.NL=S.NL。MERGE INTO SCOTT.XS1 DUSING SCOTT.XS S ON(D.XH=S.XHWHEN MATCHED THENUPD

27、ATE SETD.XH=S.XH,D.XM=S.XM,D.NL=S.NLORA-38104: 无法更新 ON 子句中引用的列: D.XHSQL SQL MERGE INTO XS1 D 2 USING XS S ON(D.XH=S.XH 3 WHEN MATCHED THEN 4 UPDATE SET 5 D.XH=S.XH,D.XM=S.XM,D.NL=S.NL 6 。MERGE INTO XS1 DUSING XS S ON(D.XH=S.XHWHEN MATCHED THENUPDATE SETD.XH=S.XH,D.XM=S.XM,D.NL=S.NLORA-00942: 表或视图不存在

28、SQL SELECT * FROM XS1。SELECT * FROM XS1ORA-00942: 表或视图不存在SQL SELECT * FROM XS。SELECT * FROM XSORA-00942: 表或视图不存在SQL SELECT * FROM SCOTT.XS。XH XM NLgIiSpiue7A- - -uEh0U1Yfmh1 张三 18IAg9qLsgBX2 李四 19WwghWvVhPE3 王五 20asfpsfpi4k5 ALI 20ooeyYZTjj1SQL SQL MERGE INTO SCOTT.XS1 D 2 USING SCOTT.XS S ON(D.XH=S

29、.XH 3 WHEN MATCHED THEN 4 UPDATE SET 5 D.XH=S.XH,D.XM=S.XM,D.NL=S.NL 6 。MERGE INTO SCOTT.XS1 DUSING SCOTT.XS S ON(D.XH=S.XHWHEN MATCHED THENUPDATE SETD.XH=S.XHORA-38104: 无法更新 ON 子句中引用的列: D.XHSQL DESC XS。Object XS does not exist.SQL DESC SCOTT.XS。Name Type Null able Default Comments - - - - - XH VARCHAR2(10 XM VARCHAR2(10 Y NL INTEGER Y SQL DESC SCOTT.XS1。Name Type Null able Default Comments -