实验五表的操作.docx
《实验五表的操作.docx》由会员分享,可在线阅读,更多相关《实验五表的操作.docx(14页珍藏版)》请在冰豆网上搜索。
![实验五表的操作.docx](https://file1.bdocx.com/fileroot1/2022-12/6/04fb566a-606a-4302-8496-9c0b319365a1/04fb566a-606a-4302-8496-9c0b319365a11.gif)
实验五表的操作
实验五(表的操作)
部门:
xxx
时间:
xxx
整理范文,仅供参考,可下载自行编辑
1.查询Scott用户下的所有表:
2.查询相关表的具体内容及相关表记录的操作
ConnectedtoOracleDatabase10gEnterpriseEditionRelease10.2.0.1.0b5E2RGbCAP
Connectedassystem
+
SQL>SHOWSCOTT.EMP。
SQL>DESCSCOTT.EMP。
NameType77DefaultComments
-------------------------------------------
EMPNONUMBER(4>
ENAMEVARCHAR2(10>Y
JOBVARCHAR2(9>Y
MGRNUMBER(4>Y
HIREDATEDATEY
SALNUMBER(7,2>Y
COMMNUMBER(7,2>Y
DEPTNONUMBER(2>Y
SQL>DESCSCOTT.DEPT。
NameTypeNullableDefaultComments
-----------------------------------------
DEPTNONUMBER(2>
DNAMEVARCHAR2(14>Y
LOCVARCHAR2(13>Y
SQL>DESCSCOTT.BONUS。
NameTypeNullableDefaultComments
----------------------------------------
ENAMEVARCHAR2(10>Y
JOBVARCHAR2(9>Y
SALNUMBERY
COMMNUMBERY
SQL>DESCSCOTT.SALGRADE。
NameTypeNullableDefaultComments
----------------------------------
GRADENUMBERY
LOSALNUMBERY
HISALNUMBERY
SQL>SELECTTO_DATE('1992-09-08','YYYY-MM-DD'>。
SELECTTO_DATE('1992-09-08','YYYY-MM-DD'>
ORA-00923:
未找到要求的FROM关键字
SQL>SELECTTO_DATE('1992-09-08','YYYY-MM-DD'>FROMDUAL。
p1EanqFDPw
TO_DATE('1992-09-08','YYYY-MM-
------------------------------
1992-9-8
ConnectedtoOracleDatabase10gEnterpriseEditionRelease10.2.0.1.0DXDiTa9E3d
Connectedassystem
SQL>SELECTTO_DATE('1992-09-09','YYYY-MM-DD'>FROMDUAL。
RTCrpUDGiT
TO_DATE('1992-09-09','YYYY-MM-
------------------------------
1992-9-9
SQL>SELECTTO_DATE('19910218','YYYYMMDD'>FROMDUAL。
5PCzVD7HxA
TO_DATE('19910218','YYYYMMDD'>
------------------------------
1991-2-18
SQL>SELECT'China'||'Beijing'FROMDUAL。
'CHINA'||'BEIJING'
------------------
Chinaberry
SQL>SELECT1+2FROMDUAL。
1+2
----------
3
SQL>CREATETABLESCOTT.XS(
2XMVARCHAR(10>,
3XHVARCHAR(10>PRIMARYKEY,
4NLNUMERIC>TABLESPACESCOTT。
CREATETABLESCOTT.XS(
XMVARCHAR(10>,
XHVARCHAR(10>PRIMARYKEY,
NLNUMERIC>TABLESPACESCOTT
ORA-00959:
表空间'SCOTT'不存在
SQL>CREATETABLEXS(
2
2XMVARCHAR(10>,
33XHVARCHAR(10>PRIMARYKEY,
44NLNUMERIC>TABLESPACE
5SYSTEM。
CREATETABLEXS(
XMVARCHAR(10>,
3XHVARCHAR(10>PRIMARYKEY,
4NLNUMERIC>TABLESPACE
SYSTEM
ORA-00904:
:
标识符无效
SQL>CREATETABLESCOTT.XS(
2XHVARCHAR(10>PRIMARYKEY,
3XMVARCHAR(10>,
4NLNUMERIC>TABLESPACESYSTEM。
Tablecreated
SQL>INSERTINTOSCOTT.XSVALUES('1','张三',18>。
1rowinserted
SQL>INSERTINTOXS1SELECT*FROMSCOTT.XS。
INSERTINTOXS1SELECT*FROMSCOTT.XS
ORA-00942:
表或视图不存在
SQL>INSERTINTOXSVALUES('2','李四','19'>。
INSERTINTOXSVALUES('2','李四','19'>
ORA-00942:
表或视图不存在
SQL>INSERTINTOSCOTT.XSVALUES('2','李四','19'>。
1rowinserted
SQL>INSERTINTOSCOTT.XSVALUES('3','王五','20'>。
1rowinserted
SQL>CREATETABLESCOTT.XS1ASSELECT*FROMSCOTT.XS。
jLBHrnAILg
Tablecreated
SQL>UPDATESCOTT.XS1SETXH='4'WHEREXM='王五'。
1rowupdated
SQL>DELETEFROMSCOTT.XS1WHEREXH='2'。
1rowdeleted
SQL>MERGEINTOSCOTT.XS1
2USINGSCOTT.XSON(SCOTT.XS1.XH=SCOTT.XS.XH>
3WHENMATCHEDTHENUPDATESET(SCOTT.XS1.XH=SCOTT.XS.XH,SCOTT.XS1.XM=SCOTT.XS.XM,SCOTT.XS1.NL=SCOTT.XS.NL>。
xHAQX74J0X
MERGEINTOSCOTT.XS1
USINGSCOTT.XSON(SCOTT.XS1.XH=SCOTT.XS.XH>
WHENMATCHEDTHENUPDATESET(SCOTT.XS1.XH=SCOTT.XS.XH,SCOTT.XS1.XM=SCOTT.XS.XM,SCOTT.XS1.NL=SCOTT.XS.NL>LDAYtRyKfE
ORA-01747:
user.table.column,table.column或列说明无效
SQL>MERGEINTOSCOTT.XS1
2USINGSCLTT.XSON(SCOTT.XS1.XH=SCOTT.XS.XH>
3WHENMATCHEDTHENUPDATESETSCOTT.XS1.XH=SCOTT.XS.XH,SCOTT.XS1.XM=SCOTT.XS.XM,SCOTT.XS1.NL=SCOTT.XS.NL。
Zzz6ZB2Ltk
MERGEINTOXS1
USINGXSON(XS1.XH=XS.XH>
WHENMATCHEDTHENUPDATESETSCOTT.XS1.XH=SCOTT.XS.XH,SCOTT.XS1.XM=SCOTT.XS.XM,SCOTT.XS1.NL=SCOTT.XS.NLdvzfvkwMI1
ORA-00942:
表或视图不存在
SQL>
SQL>MERGEINTOSCOTT.XS1
22USINGSCLTT.XSON(SCOTT.XS1.XH=SCOTT.XS.XH>rqyn14ZNXI
33WHENMATCHEDTHENUPDATESETSCOTT.XS1.XH=SCOTT.XS.XH,SCOTT.XS1.XM=SCOTT.XS.XM,SCOTT.XS1.NL=SCOTT.XS.NL。
EmxvxOtOco
MERGEINTOSCOTT.XS1
2USINGSCLTT.XSON(SCOTT.XS1.XH=SCOTT.XS.XH>
3WHENMATCHEDTHENUPDATESETSCOTT.XS1.XH=SCOTT.XS.XH,SCOTT.XS1.XM=SCOTT.XS.XM,SCOTT.XS1.NL=SCOTT.XS.NLSixE2yXPq5
ORA-02018:
缺失USING关键字
SQL>MERGEINTOSCOTT.XS1
2USINGSCOTT.XSON(SCOTT.XS1.XH=SCOTT.XS.XH>
3WHENMATCHEDTHENUPDATESETSCOTT.XS1.XH=SCOTT.XS.XHSCOTT.XS1.XM=SCOTT.XS.XMSCOTT.XS1.NL=SCOTT.XS.NL6ewMyirQFL
4WHENNOTMATCHEDTHENINSERTVALUES(SCOTT.XS1.XH=SCOTT.XS.XHSCOTT.XS1.XM=SCOTT.XS.XMSCOTT.XS1.NL=SCOTT.XS.NL>。
kavU42VRUs
MERGEINTOSCOTT.XS1
USINGSCOTT.XSON(SCOTT.XS1.XH=SCOTT.XS.XH>
WHENMATCHEDTHENUPDATESETSCOTT.XS1.XH=SCOTT.XS.XHSCOTT.XS1.XM=SCOTT.XS.XMSCOTT.XS1.NL=SCOTT.XS.NLy6v3ALoS89
WHENNOTMATCHEDTHENINSERTVALUES(SCOTT.XS1.XH=SCOTT.XS.XHSCOTT.XS1.XM=SCOTT.XS.XMSCOTT.XS1.NL=SCOTT.XS.NL>M2ub6vSTnP
ORA-00933:
SQL命令未正确结束
SQL>MERGEINTOSCOTT.XS1
2USINGSCOTT.XSON(SCOTT.XS1.XH=SCOTT.XS.XH>
3WHENMATCHEDTHENSETSCOTT.XS1.XH=SCOTT.XS.XH,SCOTT.XS1.XM=SCOTT.XS.XM,SCOTT.XS1.NL=SCOTT.XS.NL。
0YujCfmUCw
MERGEINTOSCOTT.XS1
USINGSCOTT.XSON(SCOTT.XS1.XH=SCOTT.XS.XH>
WHENMATCHEDTHENSETSCOTT.XS1.XH=SCOTT.XS.XH,SCOTT.XS1.XM=SCOTT.XS.XM,SCOTT.XS1.NL=SCOTT.XS.NLeUts8ZQVRd
ORA-00905:
缺失关键字
SQL>MERGEINTOSCOTT.XS1
2USINGSCOTT.XSON(SCOTT.XS1.XH=SCOTT.XS.XH>
3WHENMATCHEDTHENUPDATESETSCOTT.XS1.XH=SCOTT.XS.XH,SCOTT.XS1.XM=SCOTT.XS.XM,SCOTT.XS1.NL=SCOTT.XS.NL。
sQsAEJkW5T
MERGEINTOSCOTT.XS1
USINGSCOTT.XSON(SCOTT.XS1.XH=SCOTT.XS.XH>
WHENMATCHEDTHENUPDATESETSCOTT.XS1.XH=SCOTT.XS.XH,SCOTT.XS1.XM=SCOTT.XS.XM,SCOTT.XS1.NL=SCOTT.XS.NLGMsIasNXkA
ORA-38104:
无法更新ON子句中引用的列:
"SCOTT"."XS1"."XH"
SQL>MERGEINTOSCOTT.XS1
2USINGSCOTT.XSON(XS1.XH=XS.XH>
3WHENMATCHEDTHENUPDATESETSCOTT.XS1.XH=SCOTT.XS.XH,SCOTT.XS1.XM=SCOTT.XS.XM,SCOTT.XS1.NL=SCOTT.XS.NL。
TIrRGchYzg
MERGEINTOSCOTT.XS1
USINGSCOTT.XSON(XS1.XH=XS.XH>
WHENMATCHEDTHENUPDATESETSCOTT.XS1.XH=SCOTT.XS.XH,SCOTT.XS1.XM=SCOTT.XS.XM,SCOTT.XS1.NL=SCOTT.XS.NL7EqZcWLZNX
ORA-38104:
无法更新ON子句中引用的列:
"XS1"."XH"
SQL>MERGEINTOSCOTT.XS1D
2USINGSCOTT.XSSON(D.XH=S.XH>
3WHENMATCHENTHENUPDATESETD.XH=S.XH,D.XM=S.XM,D.NL=S.NL。
lzq7IGf02E
MERGEINTOSCOTT.XS1D
USINGSCOTT.XSSON(D.XH=S.XH>
WHENMATCHENTHENUPDATESETD.XH=S.XH,D.XM=S.XM,D.NL=S.NLzvpgeqJ1hk
ORA-00905:
缺失关键字
SQL>MERGEINTOSCOTT.XS1D
2USINGSCOTT.XSSON(D.XH=S.XH>
3WHENMATCHEDTHENUPDATESETD.XH=S.XH,D.XM=S.XM,D.NL=S.NL。
NrpoJac3v1
MERGEINTOSCOTT.XS1D
USINGSCOTT.XSSON(D.XH=S.XH>
WHENMATCHEDTHENUPDATESETD.XH=S.XH,D.XM=S.XM,D.NL=S.NL1nowfTG4KI
ORA-38104:
无法更新ON子句中引用的列:
"D"."XH"
注意:
在on条件中已经将源表和目标表的主键设为相等,故而在当两个表匹配时只需更新目标表中除了主键以外的其他属性!
fjnFLDa5Zo
SQL>MERGEINTOSCOTT.XS1D
2USINGSCOTT.XSSON(S.XH=D.XH>
3WHENNOTMATCHEDTHENINSERT(D.XH,D.XM,D.NL>
4VALUES(S.XH,S.XM,S.NL>。
Done
SQL>MERGEINTOSCOTT.XS1D
2USINGSCOTT.XSSON(S.XH=D.XH>
3WHENMATCHEDTHENUPDATEDD.XH=S.XH,D.XM=S.XM,D.NL=S.NL。
tfnNhnE6e5
MERGEINTOSCOTT.XS1D
USINGSCOTT.XSSON(S.XH=D.XH>
WHENMATCHEDTHENUPDATEDSETD.XH=S.XH,D.XM=S.XM,D.NL=S.NLHbmVN777sL
ORA-00905:
缺失关键字
SQL>MERGEINTOSCOTT.XS1D
2USINGSCOTT.XSSON(S.XH=D.XH>
3WHENMATCHEDTHENUPDATED.XH=S.XH,D.XM=S.XM,D.NL=S.NL。
V7l4jRB8Hs
MERGEINTOSCOTT.XS1D
USINGSCOTT.XSSON(S.XH=D.XH>
WHENMATCHEDTHENUPDATED.XH=S.XH,D.XM=S.XM,D.NL=S.NL83lcPA59W9
ORA-00971:
缺失SET关键字
SQL>MERGEINTOSCOTT.XS1D
2USINGSCOTT.XSSON(S.XH=D.XH>
3WHENMATCHEDTHENUPDATESETD.XH=S.XH,D.XM=S.XM,D.NL=S.NL。
mZkklkzaaP
MERGEINTOSCOTT.XS1D
USINGSCOTT.XSSON(S.XH=D.XH>
WHENMATCHEDTHENUPDATESETD.XH=S.XH,D.XM=S.XM,D.NL=S.NLAVktR43bpw
ORA-38104:
无法更新ON子句中引用的列:
"D"."XH"
SQL>MERGEINTOSCOTT.XS1D
2USINGSCOTT.XSSON(D.XH=S.XH>
3WHENMATCHEDTHENUPDATESETD.XH=S.XH,D.XM=S.XM,D.NL=S.NL。
ORjBnOwcEd
MERGEINTOSCOTT.XS1D
USINGSCOTT.XSSON(D.XH=S.XH>
WHENMATCHEDTHENUPDATESETD.XH=S.XH,D.XM=S.XM,D.NL=S.NL2MiJTy0dTT
ORA-38104:
无法更新ON子句中引用的列:
"D"."XH"
SQL>MERGEINTOSCOTT.XS1D
2USINGSCOTT.XSSON(D.XH=S.XH>
3WHENMATCHEDTHEN
4UPDATESET
5D.XH=S.XH,D.XM=S.XM,D.NL=S.NL。
MERGEINTOSCOTT.XS1D
USINGSCOTT.XSSON(D.XH=S.XH>
WHENMATCHEDTHEN
UPDATESET
D.XH=S.XH,D.XM=S.XM,D.NL=S.NL
ORA-38104:
无法更新ON子句中引用的列:
"D"."XH"
SQL>INSERTINTOSCOTT.XSVALUES('5''ALI','20'>。
INSERTINTOSCOTT.XSVALUES('5''ALI','20'>
ORA-00947:
没有足够的值<各个属性间忘加逗号)
SQL>INSERTINTOSCOTT.XSVALUES('5','ALI','20'>。
1rowinserted
SQL>MERGEINTOSCOTT.XS1D
2USINGSCOTT.XSSON(D.XH=S.XH>
3WHENMATCHEDTHEN
4UPDATESET
5D.XH=S.XH,D.XM=S.XM,D.NL=S.NL。
MERGEINTOSCOTT.XS1D
USINGSCOTT.XSSON(D.XH=S.XH>
WHENMATCHEDTHEN
UPDATESET
D.XH=S.XH,D.XM=S.XM,D.NL=S.NL
ORA-38104:
无法更新ON子句中引用的列:
"D"."XH"
SQL>
SQL>MERGEINTOXS1D
2USINGXSSON(D.XH=S.XH>
3WHENMATCHEDTHEN
4UPDATESET
5D.XH=S.XH,D.XM=S.XM,D.NL=S.NL
6。
MERGEINTOXS1D
USINGXSSON(D.XH=S.XH>
WHENMATCHEDTHEN
UPDATESET
D.XH=S.XH,D.XM=S.XM,D.NL=S.NL
ORA-00942:
表或视图不存在
SQL>SELECT*FROMXS1。
SELECT*FROMXS1
ORA-00942:
表或视图不存在
SQL>SELECT*FROMXS。
SELECT*FROMXS
ORA-00942:
表或视图不存在
SQL>SELECT*FROMSCOTT.XS。
XHXMNLgIiSpiue7A
-----------------------------------------------------------uEh0U1Yfmh
1张三18IAg9qLsgBX
2李四19WwghWvVhPE
3王五20asfpsfpi4k
5ALI20ooeyYZTjj1
SQL>
SQL>MERGEINTOSCOTT.XS1D
2USINGSCOTT.XSSON(D.XH=S.XH>
3WHENMATCHEDTHEN
4UPDATESET
5D.XH=S.XH,D.XM=S.XM,D.NL=S.NL
6。
MERGEINTOSCOTT.XS1D
USINGSCOTT.XSSON(D.XH=S.XH>
WHENMATCHEDTHEN
UPDATESET
D.XH=S.XH
ORA-38104:
无法更新ON子句中引用的列:
"D"."XH"
SQL>DESCXS。
ObjectXSdoesnotexist.
SQL>DESCSCOTT.XS。
NameTypeNullableDefaultComments
---------------------------------------
XHVARCHAR2(10>
XMVARCHAR2(10>Y
NLINTEGERY
SQL>DESCSCOTT.XS1。
NameTypeNullableDefaultComments
----