Oracle-SQLPLUS环境与查询

1、 nixSQL 兴 Plus： Re lease 11.2.0.1.0 Production on 星期一 4 月 29 10：30：45 2014pyright 2010. Oracle. All rights reserved.连接到:Oracle Database llg Enterprise Edition Release 11.2.0.1.0 - ProductionWith the Part it ioning, OLfiP, Data Mining and R eal Application Testing options |SQLPlus命令(1) 向缓冲区中写入内容，列出缓冲

2、区的内容SQL select empnozenane,.deptnozsal fi*on emp2 vihere3 sal between 1090 and 30004 and5 deptno=10；EHPNOENAMEDEPT NOSAL7782CLARK1024507934MILLER1RSQL list1 select empno ename.deptno,sal from emp2 where3 sal between 1000 and 30004 and5兴(2) 编辑当前行，用CHANGE命令修改编辑当前行list12 3 412 3 4select enpno,enane.dep

3、tnosalenpwheresal between 1000 and 3000anddeptno=10change Z10/20deptno=20listselect enpno,enane.deptnosal om emp wheresal between 1000 and 3000anddeptno=20(3) 増加一行，在当前行之后插入一新行2345QLQL12345SQL112 3 4 5list select enpnoenane.deptnosal fpon wheresal between 1000 and 3000 anddeptno=20 order by append sa

4、l order by sal list select enpno,enane,deptno,sal fi*on wheresal between 1000 and 3000 anddeptno=20 order by salempemplistselect enrnoename.deptno.sal fi*on emp wheresal between 1000 and 3000anddeptno=20input order by listselect enpno,enane,deptnosal fi*on emp wheresal between 1000 and 3000anddeptno

5、=20order by(4) 在一行上添加一原文，将一原文加到缓冲区中当前行的末端(5) (6) (7)删除一行，用LIST命令列出要删除的行，用DEL命令删除最后一行SQL list1 select empnoenane.deptnosal from emp2 vihere3 sal between 10R0 and 2BRW4 and5 dcptno=20Gm order by 3alSQL list 66k order by salSQL delSQL list1 s丄ect enpno,enane,deptno.sal from emp2 vihere3 sal between 100

6、0 and 30004 and5兴 deptno=20(8) 用SAVE命令保存SQL save liu 己仓lj建 file liu.sql(9) 运行命令文件J陀丄一星QL start liuFMPNO FNAMEDEPTNOSAL7566 JONES2029757788 SCOTT2030007876 ADAMS2011007902 FORD203000(10)淸除缓冲区QL clear buffer buffet*己清除SQL listSP2-0223： SQL缓冲区中不存在行。(11)列出emp表的结构SQL describe emp名称是否为空？类型EMPNONOT NULL NU

7、MBERENAMEUABCHAR2(10JOBUARCHAR2(9MGRNUMBERHIREDATEDATESfiLNUMBER(人 2COMMNUMBERDEPTNONUMBER2. Orac 1 e 查询(1) 显示EMP表中所有的部门号.职工名称和管理者号码SQL select deptnoDEPTNO ENfiMEngp fvon emp；MGR3J20 SMITH790230 fiLLEN769830 WARD769820 JONES783930 MARTIN769830 BLAKE783910 CLARK783920 SCOTI756610 KING3U lUENhK76982b

8、RDRMS7788DEPTNO ENflMEHGR30 JAMES769820 rOBD7566IO MILLER7782己选择14行。(2) 算术运算符在SQL中的使用:SQL se lec t enane ,f100x2 f ruin cmp；ENAMESfiLSrtL*100x2SMITH8001600ALLEN1GO01800WARD12S01450JONES29753175MARTIN12SR145BLAKE28SR3050CLARK24502650SCOTT30003200KING50005200TURNER15001700ADAMS11001300ENAMESALSAL+100*

9、2JAMES9501150FORD30003200MILLER13001500己选择14行。(3) 使用连字符把职工号和职工名字连接起来：SQL 3clcct cmpno I Icnanc employee from emp； lEMrLOVEE 79G9SMITII ?499nLLEN 7521WnPD r?5GGJONES 7&54MnETIN ?G98DLni(E 7?82CLnRJ( 7788SC0TI 7832KING 7844TUENER 7876ADAMSlEMrLOVEE i7? 00 JAMES 7?02rOED 7734MILLER己选择行。(4) 禁止重复，列举出EMP表

10、中所有部门号f?QL select distinct de pt no f ron enp；DEPTNO(5) 按单个字段ENAME排序SQL select cnanc, jobA3ol,dcptno from emp2 order by cnamc；ENHMEJODSfiLDEPTNOfiDftMSCLE R】110020ALLENSfiLESMftN1GO030BLOKEMANAGER285030CLHRKMfiNAGER245010ronDftNALVCT300020JftMESCLERK95030JONESMftNfiGER297520KINGPRESIDENTS0O010MfiRTI

11、NSfiLESMAN125030MILLEBCLE RM130010SCOTTnNftLVST30O020ENHMEJODSALDEPTNOSMITHCLERK80020TURNERSALESMAN150030WfiRDSflLESMnN125030(6) 按多个字段排序，按部门号升序，按工资降序排序胃QL select deptnuJobeiiamesal from emp2 order by deptno,sal desc;SfiLDEPTNO JOBENAHE10 PRESIDENTKING500010 MANAGERCLARK245010 CLERKMILLER13RR2R flNAL

12、VSrSCOTT20 ANALYSTFORD300020 MANAGERJONES297520 CLERKADAMS110020 CLERKSMITH80030 HANRGEKBLAKE285030 SALESMANALLEN16MW30 SALESMANTURNER1500DEPTNO JOBENAHESfiL3R SALESMANMART IM125R30 SALESMANWfiRD125030 CLERKJAMES950己选择14行。(7) 查询工作是CLERK的所有职工的姓名，职工号和部门号SQL selectenane,enpno,deptno,job from enp2 where

13、job=,CLERK,；ENAMEEMPNODEPTNO JOBSMITH736?2b CLERKADAMS787620 CLERKJAMES790038 CLERKMILLER793410 CLERK(8) 从DEPT表中查询出部门号大于20的部门名称deptno20；DEPTNODNAME3040SALES OPEIinTIONG：SQL select deptnodnanie fron dept2 where 复合条件查询，查询工作是MANAGER并且工资大于1500,或者工作是SALESMAN的职工 信息SQL ee lect enpno,. ename deptno. job, ca

14、l ron emp2 v/horo eal1500 and job MfiNftCER or job = J SftLESMftH,;EMPNOENrtMEDEPTNOJOBSAL7499ALLEN30SALESMAN16007521WARD30SALESMAN12507566JONES20HANAGER29757654MAKI IN30SALESMAN1250如8BLAKE30HANAGhK2850?82CLAKKIMnfiNAGEK245 W7844TURNER30SALESMAN1500己选择?行。(10) BETWEEN的应用，查询工资在1000到2000之间的职工名字和工资信息|sQ

15、L select enamesal fi*nm emp where sal betvieen 1 RUB and 2RRH；ENPMESftLHLLEH1600WHPD1250MARTIN1250TURNER1500ADAMS1100MILLERJL 300己选择6行。(11) 查询有7902, 7566, 7788三个MGR号之一的所有职工SQL select enpno,enanesalngr from enp2 where ngr in ；EMPNO ENAMESALMGR7369SMIIHHWi7788SGO117876ADAMS11007902FORD3000他668866 丹！27

16、775(12) 查询名字以“S”开始的所有职工卜QL select empno.ename, job from emp vihere ename like JSxJ ；EMPNO ENAMEJOB736? SMITHCLERK7788 SCOTTANALYST(13) 查询名字只有4个字符的所有职工SQLlect. einunu. enciric Job from emu wle re enane like f J yEMrNOENI1MEJOB7521WftRDSrtLESMftN7839KINGPRESIDENT7902FOPDANfiLVSr(14) 查询没有管理者的所有职工SQL select e .enpno .ename .dnane from emp e inner Join dept d2 on e.deptno=d.deptno3 where length 5；EMPNO ENAMEDNfiME7654 MARTINSALES7844 TUKNEKSALfcS7?34 MILLEKACCOUNTING4. 数字函数的应用使用power函数计算2的5次方的值:SQL select power2,5) from dual；POUER325