实验二 Oracle SQLPLUS环境与查询.docx

1、实验二 Oracle SQLPLUS环境与查询实验二 Oracle SQL*PLUS环境与查询一实验目的及要求1熟悉Oracle的基本知识。2熟悉Oracle的命令操作环境SQL*PLUS及常用命令。3熟悉并掌握oracle常用的查询语句。二、实验主要内容1、Oracle SQL*PLUS环境及常用命令。2、Oracle查询。三、实验仪器设备在局域网环境下，有一台服务器和若干台客户机。服务器成功安装Oracle 10g数据库服务器（企业版），客户机成功安装Oracle 10g客户端软件，网络服务配置正确，数据库和客户端正常工作。四、实验步骤1SQL*Plus的启动当登录到操作系统后，有三种方法

2、启动SQL*Plus。1） SQLPLUS（或 SQLPLUSW，下同）将看到如下的信息：SQL*Plus: Release 9.0.1.0.1 - Production on 星期二 9月 13 14:27:51 2005(c) Copyright 2001 Oracle Corporation. All rights reserved.请输入用户名：输入你的用户名字并按回车。SQL*Plus将提示：请输入口令：输入你的密码并按回车。那么，将看到SQL*Plus的提示符：SQL例如：SQLPLUSSQL*Plus: Release 9.0.1.0.1 - Production on 星期二

3、9月 13 14:27:51 2005(c) Copyright 2001 Oracle Corporation. All rights reserved.Enter Username:scottEnter Password:tigerSQL2）SQLPLUS Username再提示你输入密码。例如：SQLPLUS scottEnter Password: tigerSQL*Plus: Release 9.0.1.0.1 - Production on 星期二 9月 13 14:27:51 2005(c) Copyright 2001 Oracle Corporation. All right

4、s reserved.SQL3）SQLPLUS username/password例如：SQLPLUS scott/tigerSQL*Plus: Release 9.0.1.0.1 - Production on 星期二 9月 13 14:27:51 2005(c) Copyright 2001 Oracle Corporation. All rights reserved.SQL4）断开与数据库的连接 SQLDISCONNECT5）退出SQL*PlusSQLEXIT或：SQLQuit2SQL命令SQL命令包括数据定义语言（如Create、Alter等）和数据操作语言（Select Inser

5、t Update Delete等），这些都可在SQL*Plus中使用。如：SQLSELECT EMPNO, ENAME, JOB, SAL 2 FROM EMP WHERE SAL LISTSQL*Plus显示当前缓冲区中的SQL命令（注意：不缓存SQL Plus命令）：1 SELECT EMPNO, ENAME, JOB, SAL 2 FROM EMP WHERE SAL SELECT EPNO, ENAME, JOB, SAL 2 FROM EMP WHERE SAL CHANGE /EPNO/EMPNO修改的行在屏幕上显示：1* SELECT EMPNO, ENAME, JOB, SAL

6、 再用RUN命令运行当前命令。SQLRUN（或 /）SQL*PLUS列出其命令然后运行它。1 SELECT EMPNO, ENAME, JOB, SAL 2 FROM EMP WHERE SAL INPUT3接着可进入新行，然后按ENTER键，SQL*PLUS再次提示新行：3 ORDER BY SAL4按ENTER键，表示不进入任何行，然后用RUN检验和重新运行查询。4）在一行上添加一原文用APPEND命令，将一原文加到缓冲区中当前行的末端：SQLLIST3* ORDER BY SALSQLAPPEND DESC3* ORDER BY SAL DESC使用RUN检验和重新运行查询。5）删除一行

7、用LIST命令列出要删除的行。用DEL命令删除。SQLLIST3* ORDER BY SAL DESCSQLDEL6）用系统编辑程序编辑命令在SQL*PLUS中运行操作系统缺省的文本编辑程序（EDIT），命令形式为：SQLEDITEDIT将缓冲区中的内容装入系统缺省的文本编辑器，然后用文本编辑器的命令编辑文本。完成后保存编辑的文本，然后退出。该文本保存到当前的缓冲区。7）保存SAVE命令SQLSAVE 文件名例如：SQLLIST1 SELECT EMPNO, ENAME, JOB, SAL 2 FROM EMP WHERE SAL SAVE empinfoCreated file empinf

8、o8）运行命令文件可用命令START 文件名或者 文件名的命令格式。如上例：SQLSTART EMPINFO或SQL EMPINFO9）清缓冲区SQLCLEAR BUFFER10）DESCRIBE列出表的结构如：SQLDESC EMP NameNull?TypeEMPNONOT NULLNUMBER(4)ENAMEVARCHAR2(10)JOBVARCHAR2(10)MGRNUMBER(4)HIREDATEDATESALNUMBER(7,2)COMMNUMBER(7,2)DEPTNONOT NULLNUMBER(2)4Oracle查询1显示EMP表中所有的部门号、职工名称和管理者号码：SELE

9、CT DEPTNO,ENAME,MGR FROM EMP;DEPTNOENAMEMGR-20SMITH790230ALLEN769830WARD769820JONES783930MARTIN769830BLAKE783910CLARK783920SCOTT756610KING30TURNER769820ADAMS778830JAMES769820FORD756610MILLER77822算术运算符在SQL中的使用SELECT ENAME, SAL+250*12 FROM EMP;3连字符的使用把职工号和职工名字连接起来，如下：SELECT EMPNO|ENAME EMPLOYEE FROM E

10、MP;把职工号和职工名字中间用-连接起来，并输出WORKS IN DEPARTMENT，如下：SELECT EMPNO|-|ENAME EMPLOYEE, WORKS IN DEPARTMENT, DEPTNOFROM EMP;4禁止重复如果列举出EMP表中所有部门号：Select deptno from emp;DEPTNO-2030302030301020103020302010从上表中可以看出部门号之间存在着许多的相同的，可以用DISTINCT子句来消除重复的。SELECT DISTINCT deptno from emp;DEPTNO-1020305排序按单个字段排序，如按照ENAME

11、排序，SELECT ENAME, JOB, SAL*12, DEPTNOFROM EMPORDER BY ENAME;ENAMEJOBSAL*12DEPTNO-ADAMSCLERK1320020ALLEMSALESMAN1920030BLAKEMANAGER3420030CLARKMANAGER2940010FORDANALYST3600020JAMESCLERK1140030JONESMANAGER3570020KINGPRESIDENT6000010MARTINSALESMAN1500030MILLERCLERK1560010SCOTTANALYST3600020SMITHCLERK960

12、020TURNERSALESMAN1800030WARDSALESMAN1500030按多个字段排序：如按部门号升序，按工资降序排序SELECT DEPTNO, JOB, ENAMEFROM EMPORDER BY DEPTNO, SAL DESC;DEPTNOJOBENAME-10PRESIDENTKING10MANAGERCLARK10CLERKMILLER20ANALYSTSCOTT20ANALYSTFORD20MANAGERJONES20CLERKADAMS20CLERKSMITH30MANAGERBLAKE30SALESMANALLEN30SALESMANTURNER30SALESM

13、ANWARD30SALESMANMARTIN30CLERKJAMES6带条件的查询1）查询工作是CLERK的所有职工的姓名，职工号和部门号SELECT ENAME, EMPNO, JOB, DEPTNOFROM EMPWHERE JOB = CLERK;ENAMEEMPNOJOBDEPTNO-SMITH7369CLERK20ADAMS7876CLERK20JAMES7900CLERK30MILLER7934CLERK102）从DEPT表中查询出部门号大于20的部门名称SELECT DNAME, DEPTNOFROM DEPTWHERE DEPTNO 20;DNAMEDEPTNO-SALES30

14、OPERATIONS403）复合条件查询查询工作是MANAGER并且工资大于1500，或者工作是SALESMAN的职工信息：SELECT EMPNO，ENAME， JOB，SAL，DEPTNOFROM EMPWHERE SAL1500 AND JOB = MANAGER OR JOB = SALESMAN；EMPNOENAMEJOBSALDEPTNO-7499ALLENSALESMAN160000307521WARDSALESMAN125000307566JONESMANAGER297500207654MARTINSALESMAN125000307698BLAKEMANAGER28500030

15、7782CLARKMANAGER245000107844TURNERSALESMAN150000307操作符的应用1）BETWEEN的应用查询工资在1000到2000之间的职工名字和工资信息。SELECT ENAME， SALFROM EMPWHERE SAL BETWEEN 1000 AND 2000；ENAMESAL-ALLEN160000WARD125000MARTIN125000TURNER150000ADAMS110000MILLER1300002）IN查询有7902，7566，7788三个MGR号之一的所有职工：SELECT EMPNO， ENAME， SAL， MGRFROM E

16、MPWHERE MGR IN （7902，7566，7788）；EMPNOENAMESALMGR-7369SMITH8000079027788SCOTT30000075667876ADAMS110000778889-2FORD30000075663）LIKE通配符%代表任意0或多个字符。通配符_代表任意单个字符。查询名字以“S”开始的所有职工：SELECT ENAMEFROM EMPWHERE ENAME LIKE S%；ENAME-SMITHSCOTT查询名字只有4个字符的所有职工：SELECT ENAMEFROM EMPWHERE ENAME LIKE _ _ _ _；ENAME-WARD

17、KINGFORD4）IS NULL查询没有管理者的所有职工：SELECT ENAME，MGRFROM EMPWHERE MGR IS NULL；ENAMEMGR-KING8单&号替代变量1）数字变量输入：SELECT EMPNO，ENAME，SALFROM EMPWHERE DEPTNO = &DEPARTMENT_NUMBER;Enter value for department_number:10EMPNOENAMESAL-7782CLARK2450.007839KING5000.007934MILLER1300.002）字符串变量输入：SELECT EMPNO，ENAME，SAL*12F

18、ROM EMPWHERE JOB = &JOB_TITLE;Enter value for job_title: MANAGERENAMEDEPTNOSAL*12-JONES2035700.00BLAKE3034200.00CLARK1029400.009字符函数的应用见表4-510数字函数的应用见表4-411日期函数的应用见表4-6，4-712数据类型转换1）TO_CHAR 数字数据转换为字符串select to_char(8897) from dual;2）TO_NUMBER字符串数据转换为数字select to_number(8897) from dual;3）TO_DATE字符串数据转

19、换为日期数据select to_date(12-DEC-02) from dual;13分组函数的应用1）分组函数见表4-82）分组函数应用求平均值SELECT AVG(SAL) FROM EMP;AVG(SAL)-2073.2149求最小值SELECT MIN(SAL) FROM EMP WHERE JOB = CLERK;MIN(SAL)-800求数目SELECT COUNT(*) FROM EMP WHERE DEPTNO = 20;COUNT(*)- 53）GROUP BY子句求每个部门中的平均工资：SELECT JOB，AVG（SAL） FROM EMPGROUP BY JOB；JO

20、BAVG（SAL）-ANALYST3000CLERK10375MANAGER275833333PRESIDENT5000SALESMAN14004）HAVING子句查询人数超过3人的部门中的平均工资：SELECT DEPTNO，AVG（SAL） FROM EMPGROUP BY DEPTNOHAVING COUNT（*）3；DEPTNOAVG（SAL）-2021753015666666714连接从EMP和DEPT中查询出职工名字、工作和部门名称：SELECT ENAME，JOB，DNAME FROM EMP， DEPTWHERE EMP.DEPTNO = DEPT.DEPTNO;ENAMEJO

21、BDNAME-CLARKMANAGERACCOUNTINGMILLERCLERKACCOUNTINGKINGPRESIDENTACCOUNTINGSMITHCLERKRESEARCHSCOTTANALYSTRESEARCHJONESMANAGERRESEARCHADAMSCLERKRESEARCHFORDANAYLSTRESEARCHALLENSALESMANSALESBLAKEMANAGERSALESTURNERSALESMANSALESJAMESCLERKSALESMARTINSALESMANSALESWARDSALESMANSALES15子查询的应用从EMP中查询出工资最低的职工：SELECT ENAME，JOB，SAL FROM EMPWHERE SAL = （SELECT MIN（SAL）FROM EMP ）；ENAMEJOBDNAME-SMITHCLERK800从EMP中查询出每个部门工资最低的职工：SELECT ENAME，SAL，DEPTNO FROM EMPWHERE SAL IN （SELECT MIN（SAL） FROM EMP GROUP BY DEPTNO）；ENAMESALDEPTNO-SMITH80020JAMES95030MILLER130010五、拓展题复杂查询的不同格式的实现及增删改操作。