Oracle常用指令集.docx
- 文档编号:30154741
- 上传时间:2023-08-05
- 格式:DOCX
- 页数:20
- 大小:21.51KB
Oracle常用指令集.docx
《Oracle常用指令集.docx》由会员分享,可在线阅读,更多相关《Oracle常用指令集.docx(20页珍藏版)》请在冰豆网上搜索。
Oracle常用指令集
Oracle常用指令
Ø登录用户
sqlplusscott/tiger
Ø切换用户
CONNsys/change_on_instalASSYSDBA
CONNsystem/manager
Ø设置格式
SETLINESIZE300;
SETPAGESIZE30;
Ø清空屏幕
clearscr;
Ø查询用户下的所有表
SELECT*FROMtab;
Ø查询emp表的结构
DESCemp;
Ø查询emp表的所有列
SELECT*FROMemp;
Ø查询emp表的部分列
SELECTempno,ename,jobFROMemp;
Ø查询emp表的部分列,并消除重复的数据行
SELECTDISTINCTjobFROMemp;
Ø查询中插入四则运算并定义别名
SELECTempno,ename,sal*12incomeFROMemp;
Ø查询中使用常量
SELECT‘雇员’,empno,enameFROMemp;
Ø查询使用连接符
SELECT‘雇员编号:
’||empno,‘姓名:
’||ename,‘收入:
’||salinfo
FROMemp;
Ø查询使用关系运算符:
>、<、>=、<=、<>(!
=)
SELECT*FROMempWHEREsal>1500;
Ø查询用逻辑运算符AND、OR、NOT
SELECT*FROMempWHEREsal>=2000ORjob=’CLERK’;
Ø查询使用范围运算符:
BETWEEN…AND
SELECT*FROMempWHEREsalBETWEEN1500AND2000;
Ø查询使用谓词:
IN、NOTIN
SELECT*FROMempWHEREempno(NOT)IN(7369,7566,7788,9999);
SELECT*FROMempWHERENOTempnoIN(7369,7566,7788,9999);
Ø查询使用空判断ISNULL、ISNOTNULL
SELECT*FROMempWHEREcommIS(NOT)NULL;
SELECT*FROMempWHERENOTcommISNULL;
Ø查询使用模糊查询:
LIKE
SELECT*FROMempWHEREenameLIKE‘_A%’;//姓名第二个字母为A
SELECTenameFROMempWHEREenameNOTLIKE'%R%';//姓名不包含字母R
Ø将查询的结果进行排序
SELECT*FROMempWHEREjob=’SALESMAN’
ORDERBYsalDESC,hiredateASC;//ASC升序(默认)DESC降序
SELECTempno,ename,sal*12incomeFROMemp
ORDERBYincome;
Ø字符串函数——大小写转换:
UPPER、LOWER
SELECT*FROMemp
WHEREename=UPPER('&inputname');
Ø字符串函数——首字母大写其他字母小写:
INITCAP
SELECTename,INITCAP(ename)FROMemp;
Ø字符串函数——取得字符串的长度:
LENGTH
SELECTLENGTH('helloworld!
!
!
')FROMdual;
SELECT*FROMempWHERELENGTH(ename)=5;
Ø字符串函数——字符串截取:
SUBSTR
SELECTename,SUBSTR(ename,1,3)FROMemp;//截取名字前三个字母
SELECTename,SUBSTR(ename,-3)FROMemp;//截取名字后三个字母
Ø数值函数——实现四舍五入截取:
ROUND
SELECTROUND(789.564168)FROMdual;//结果:
790
SELECTROUND(789.564168,2)FROMdual;//结果:
789.56
SELECTROUND(789.564168,-2)FROMdual;//结果:
800
Ø数值函数——不进位截取:
TRUNC
SELECTTRUNC(789.564168),//结果:
789
TRUNC(789.564168,2),//结果:
789.56
TRUNC(789.564168,-2)//结果:
700
FROMdual;
Ø数值函数——求模:
MOD
SELECTMOD(10,3)FROMdual;//结果:
1
Ø日期函数伪列:
SYSDATE、SYSTIMESTAMP
SELECTename,job,sal,SYSDATEFROMemp;
Ø日期函数——指定日期增加若干月之后的日期:
ADD_MONTHS()
SELECTADD_MONTHS(SYSDATE,4)FROMdual;
Ø日期函数——返回两个日期之间所经历的月数:
MONTHS_BETWEEN()
SELECTempno,ename,MONTHS_BETWEEN(SYSDATE,hiredate)FROMemp;
Ø日期函数——取得指定日期所在月的最后一天:
LAST_DAY
SELECTLAST_DAY(SYSDATE)FROMdual;
Ø日期函数——返回下一周指定星期数所对应的日期:
NEXT_DAY
SELECTNEXT_DAY(SYSDATE,‘星期二’)FROMdual;
Ø转换函数——将日期或数字格式化为指定结构字符串:
TO_CHAR()
SELECTTO_CHAR(SYSDATE,'yyyy-mm-ddhh:
mi:
ss')FROMdual;
SELECTTO_CHAR(SYSDATE,'yyyy'),//日期数据拆分
TO_CHAR(SYSDATE,'mm'),
TO_CHAR(SYSDATE,'dd')
FROMdual;
SELECTTO_CHAR(5418761321813248,'L999,999,999,999,999,999')FROMdual;
Ø转换函数——按照指定的转换格式编写字符串后将其变为日期:
TO_DATE()
SELECTTO_DATE('2017-07-13','yyyy-mm-dd')FROMdual;
Ø转换函数——将字符串变为数字:
TO_MUMBER()
SELECTTO_NUMBER('1')+TO_NUMBER('2')FROMdual;
Ø通用函数——替换null:
NVL
SELECTempno,ename,sal,comm,(sal+NVL(comm,0))*12FROMemp;
Ø通用函数——替换内容:
DECODE()
SELECTempno,job,DECODE(job,'CLERK','办事员','SALESMAN','销售','---')
FROMemp;
Ø多表查询,使用WHERE子句消除笛卡尔积
SELECT*FROMemp,deptWHEREemp.deptno=dept.deptno;
Ø使用别名
SELECT*FROMempe,deptdWHEREe.deptno=d.deptno;
Ø内连接
SELECTe.empno,e.ename,e.job,d.deptno,d.dname,d.loc
FROMempe,deptd
WHEREe.deptno=d.deptno;
Ø外连接
SELECTe.empno,e.ename,e.job,d.deptno,d.dname,d.loc
FROMempe,deptd
WHEREe.deptno(+)=d.deptno;//dept表中的内容都会显示
ØSQL:
1999语法——交叉连接,产生笛卡尔积:
CROSSJOIN
SELECT*FROMempCROSSJOINdept;
ØSQL:
1999语法——自然连接,自动使用关联字段消除笛卡尔积:
NATURALJOIN
SELECT*FROMempNATURALJOINdept;
ØSQL:
1999语法,明确设置关联字段:
JOIN...USING
SELECT*FROMempJOINdeptUSING(deptno);
ØSQL:
1999语法,在没有关联字段的情况下设置关联条件:
JOIN...ON
SELECT*FROMempeJOINsalgradesON(e.salBETWEENs.losalANDs.hisal);
ØSQL:
1999语法,外连接:
LEFT|RIGHT|FULLOUTERJOIN
SELECT*FROMempLEFTOUTERJOINdeptUSING(deptno);//emp内容全部显示
SELECT*FROMempRIGHTOUTERJOINdeptUSING(deptno);//dept内容全部显示
SELECT*FROMempFULLOUTERJOINdeptUSING(deptno);//emp和dept内容都显示
ØUNION:
不显示重复的内容
SELECTdeptno,empno,ename,jobFROMempWHEREdeptno=10
UNION
SELECTdeptno,empno,ename,jobFROMemp;
ØUNIONALL:
显示重复的内容
SELECTempno,ename,job,deptnoFROMempWHEREdeptno=10
UNIONALL
SELECTempno,ename,job,deptnoFROMemp;
ØINTERSECT:
返回结果取交集
SELECTempno,ename,job,deptnoFROMempWHEREdeptno=10
INTERSECT
SELECTempno,ename,job,deptnoFROMemp;
ØMINUS:
返回结果取差集
SELECTempno,ename,job,deptnoFROMemp
MINUS
SELECTempno,ename,job,deptnoFROMempWHEREdeptno=10;
Ø统计函数——统计个数:
COUNT()
SELECTCOUNT(*)FROMemp;
✓COUNT(*):
明确的返回表中的数据个数,是最准确的;
✓COUNT(字段):
不统计为null的数据个数,如果某一列的数据不可能为空,那么结果与COUNT(*)相同;
✓COUNT(DISTINCT字段):
统计消除掉重复数据后的数据个数。
Ø统计函数——最大值:
MAX()、最小值:
MIN()
SELECTMAX(sal),MIN(sal)FROMemp;
SELECTMAX(hiredate),MIN(hiredate)FROMemp;
Ø统计函数——总和:
SUM()、平均值:
AVG()
SELECTSUM(sal),AVG(sal)FROMemp;
Ø分组统计函数:
GROUPBY
SELECTdeptno,COUNT(empno),MAX(sal),MIN(sal)FROMemp
GROUPBYdeptno;
使用限制:
✓限制一:
在没有编写GROUPBY子句的时候(全表作为一组),如果SELECT子句之中出现了统计函数,那么不允许出现任何的其他字段;
✓限制二:
在使用GROUPBY子句分组的时候,SELECT子句之中只允许出现分组字段与统计函数,其他字段不允许出现;
✓限制三:
统计函数允许嵌套查询,但是嵌套后的统计查询中,SELECT子句里面不允许再出现任何的字段,包括分组字段,只能够使用嵌套的统计函数;
Ø分组统计函数:
HAVING()
关于WHERE与HAVING的区别:
✓WHERE发生在GROUPBY操作之前,属于分组前的数据筛选,即:
从所有的数据中筛选出可以分组的数据,WHERE子句不允许使用统计函数;
✓HAVING发生在GROUPBY操作之后,是针对于分组后的数据进行筛选,HAVING可以使用统计函数。
//错误代码
SELECTjob,AVG(sal)
FROMemp
WHEREAVG(sal)>2000
GROUPBYjob;
//正确代码
SELECTjob,AVG(sal)
FROMemp
GROUPBYjob
HAVINGAVG(sal)>2000;
综合练习:
显示非销售人员的工作名称以及从事同一工作雇员的月工资总和,并且要满足从事同一工作的雇员的月工资合计大于$5000,输出结果按月工资的合计升序排列。
SELECTjob,SUM(sal)sum
FROMemp
WHEREjob<>'SALESMAN'
GROUPBYjob
HAVINGSUM(sal)>5000
ORDERBYsumASC;
Ø子查询——IN、NOTIN
SELECT*FROMemp
WHEREsal(NOT)IN(
SELECTsalFROMempWHEREjob='MANAGER');
Ø子查询——ANY
SELECT*FROMemp
WHEREsal=ANY(
SELECTsalFROMempWHEREjob='MANAGER');
SELECT*FROMemp
WHEREsal>ANY(
SELECTsalFROMempWHEREjob='MANAGER');//比最小值大
SELECT*FROMemp
WHEREsal SELECTsalFROMempWHEREjob='MANAGER');//比最大值小 Ø子查询——ALL SELECT*FROMemp WHEREsal>ALL( SELECTsalFROMempWHEREjob='MANAGER');//比最大值大 SELECT*FROMemp WHEREsal SELECTsalFROMempWHEREjob='MANAGER');//比最小值小 Ø复制表 CREATETABLEmyempASSELECT*FROMemp; Ø增加数据 INSERTINTOmyemp (empno,sal,job,comm,ename,mgr,hiredate,deptno) VALUES(8888,9000.0,'清洁工',10.0,'王五',7369,null,40); Ø修改数据 UPDATEmyempSETsal=8000,comm=9000 WHEREename='SMITH'; Ø删除数据 DELETEFROMmyempWHEREempnoIN(7782,7902); Ø事物提交操作 COMMIT; Ø事物回滚操作 ROLLBACK; Ø数据伪列——行号: ROWNUM SELECTROWNUM,empno,ename,jobFROMemp; SELECTROWNUM,empno,ename,jobFROMempWHEREROWNUM<=5; Ø数据伪列——行ID: ROWID SELECTROWID,empno,ename,jobFROMemp; Ø创建数据表 CREATETABLEmember( midNUMBER, nameVARCHAR2(50)DEFAULT'无名氏', ageNUMBER(3), birthdayDATEDEFAULTSYSDATE, noteCLOB ); Ø向表中增加数据 INSERTINTOmember(mid,name,age,birthday,note) VALUES(10,'张三',30,TO_DATE('1987-11-11','yyyy-mm-dd'),'男人'); INSERTINTOmember(mid,age,note) VALUES(10,20,'女人'); Ø表的重命名 RENAMEmemberTOperson; Ø截断表: 收回表所占用的全部数据空间(表结构还在,里面没有数据) TRUNCATETABLEperson; Ø表数据的复制 CREATETABLEemp10ASSELECT*FROMempWHEREdeptno=10; Ø表结构的复制(不复制里面的数据) CREATETABLEempnullAS SELECT*FROMempWHERE1=2; Ø表的删除(经过回收站) DROPTABLEemp10; Ø闪回技术(从回收站中找回已删除的表) FLASHBACKTABLEemp10TOBEFOREDROP; Ø表的强制删除(不经过回收站) DROPTABLEemp10PURGE; Ø删除回收站中的表 PURGETABLEemp10; Ø清空回收站 PURGERECYCLEBIN; Ø增加数据表中的列 ALTERTABLEmemberADD(emailVARCHAR2(20));//无默认值 ALTERTABLEmemberADD(sexVARCHAR2 (2)DEFAULT'男');//有默认值 Ø修改数据表中的列 ALTERTABLEmemberMODIFY(nameVARCHAR2(20)DEFAULT'无名氏'); Ø删除数据表中的列 ALTERTABLEmemberDROPCOLUMNsex; Ø非空约束(NK) DROPTABLEmemberPURGE; CREATETABLEmember( midNUMBER, nameVARCHAR2(20)NOTNULL ); Ø唯一约束(UK) DROPTABLEmemberPURGE; CREATETABLEmember( midNUMBER, nameVARCHAR2(20)NOTNULL, emailVARCHAR2(30)UNIQUE ); Ø为约束设置名称 DROPTABLEmemberPURGE; CREATETABLEmember( midNUMBER, nameVARCHAR2(20)NOTNULL, emailVARCHAR2(30), CONSTRAINTuk_emailUNIQUE(email) ); Ø主键约束(PK) DROPTABLEmemberPURGE; CREATETABLEmember( midNUMBERPRIMARYKEY, nameVARCHAR2(20)NOTNULL ); DROPTABLEmemberPURGE; CREATETABLEmember( midNUMBER, nameVARCHAR2(20)NOTNULL, emailVARCHAR2(30), CONSTRAINTpk_midPRIMARYKEY(mid) Ø检查约束(CK) DROPTABLEmemberPURGE; CREATETABLEmember( midNUMBER, nameVARCHAR2(20), ageNUMBER(3), CONSTRAINTck_ageCHECK(ageBETWEEN0AND250) ); Ø外键约束(FK) DROPTABLEmemberPURGE; DROPTABLEbookPURGE; CREATETABLEmember( midNUMBER, nameVARCHAR2(20)NOTNULL, CONSTRAINTpk_midPRIMARYKEY(mid) ); CREATETABLEbook( bidNUMBER, titleVARCHAR2(20), midNUMBER, CONSTRAINTpk_bidPRIMARYKEY(bid), CONSTRAINTfk_midFOREIGNKEY(mid)REFERENCESmember(mid) ); Ø对于存在外键的父表执行强制删除 DROPTABLEmemberCASCADECONSTRAINT; Ø数据的级联删除: ONDELETECASCADE(删除父表的同时所有子表相关内容同时删除) DROPTABLEmemberPURGE; DROPTABLEbookPURGE; CREATETABLEmember( midNUMBER, nameVARCHAR2(20)NOTNULL ); CREATETABLEbook( bidNUMBER, titleVARCHAR2(20), midNUMBER, CONSTRAINTpk_bidPRIMARYKEY(bid), CONSTRAINTfk_midFOREIGNKEY(mid)REFERENCESmember(mid)ONDELETECASCADE ); Ø数据的级联更新: ONDELETESETNULL(删除父表的同时所有子表相关内容设置为null) DROPTABLEmemberPURGE; DROPTABLEbookPURGE; CREATETABLEmember( midNUMBER, nameVARCHAR2(20)NOTNULL, CONST
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Oracle 常用 指令