实验二-Oracle-SQLPLUS环境与查询

实验二OracleSQL*PLUS环境与查询一．实验目的及要求熟悉Oracle的根本知识。熟悉Oracle的命令操作环境SQL*PLUS及常用命令。熟悉并掌握oracle常用的查询语句。二、实验主要内容1、OracleSQL*PLUS环境及常用命令。2、Oracle查询。三、实验仪器设备在局域网环境下，有一台效劳器和假设干台客户机。效劳器成功安装Oracle10g数据库效劳器〔企业版〕，客户机成功安装Oracle10g客户端软件，网络效劳配置正确，数据库和客户端正常工作。四、实验步骤SQL*Plus的启动当登录到操作系统后，有三种方法启动SQL*Plus。SQLPLUS〔或SQLPLUSW，下同〕将看到如下的信息：SQL*Plus:Release.0.1-Productionon星期二9月1314:27:512005(c)Copyright2001OracleCorporation.Allrightsreserved.请输入用户名：输入你的用户名字并按回车。SQL*Plus将提示：请输入口令：输入你的密码并按回车。那么，将看到SQL*Plus的提示符：SQL>例如：SQLPLUSSQL*Plus:Release.0.1-Productionon星期二9月1314:27:512005(c)Copyright2001OracleCorporation.Allrightsreserved.EnterUsername:scottEnterPassword:tigerSQL>SQLPLUSUsername再提示你输入密码。例如：SQLPLUSscottEnterPassword:tigerSQL*Plus:Release.0.1-Productionon星期二9月1314:27:512005(c)Copyright2001OracleCorporation.Allrightsreserved.SQL>SQLPLUSusername/password例如：SQLPLUSscott/tigerSQL*Plus:Release.0.1-Productionon星期二9月1314:27:512005(c)Copyright2001OracleCorporation.Allrightsreserved.SQL>断开与数据库的连接SQL>DISCONNECT退出SQL*PlusSQL>EXIT或：SQL>QuitSQL命令SQL命令包括数据定义语言〔如Create、Alter等〕和数据操作语言〔SelectInsertUpdateDelete等〕，这些都可在SQL*Plus中使用。如：SQL>SELECTEMPNO,ENAME,JOB,SAL2 FROMEMPWHERESAL<2500;SQL*Plus命令列出缓冲区的内容：SQL>LISTSQL*Plus显示当前缓冲区中的SQL命令〔注意：不缓存SQLPlus命令〕：1 SELECTEMPNO,ENAME,JOB,SAL2 FROMEMPWHERESAL<2500；编辑当前行如果上面的例子错误的输入为：SQL>SELECTEPNO,ENAME,JOB,SAL2 FROMEMPWHERESAL<2500;在屏幕上显示：SELECTEPNO,ENAME,JOB,SAL*ERRORatline1:ORA-0904:invalidcolumnname分析错误可以发现EMPNO错为EPNO。那么用CHANGE命令修改编辑当前行。如：SQL>CHANGE/EPNO/EMPNO修改的行在屏幕上显示：1* SELECTEMPNO,ENAME,JOB,SAL再用RUN命令运行当前命令。SQL>RUN〔或/〕SQL*PLUS列出其命令然后运行它。1 SELECTEMPNO,ENAME,JOB,SAL2 FROMEMPWHERESAL<2500;增加一行在当前行之后插入一新行，使用INPUT命令。例如对上面例子增加第3行到该SQL命令中。形式如下：SQL>INPUT3接着可进入新行，然后按ENTER键，SQL*PLUS再次提示新行：3 ORDERBYSAL4按ENTER键，表示不进入任何行，然后用RUN检验和重新运行查询。在一行上添加一原文用APPEND命令，将一原文加到缓冲区中当前行的末端：SQL>LIST3* ORDERBYSALSQL>APPENDDESC3* ORDERBYSALDESC使用RUN检验和重新运行查询。删除一行用LIST命令列出要删除的行。用DEL命令删除。SQL>LIST3* ORDERBYSALDESCSQL>DEL用系统编辑程序编辑命令在SQL*PLUS中运行操作系统缺省的文本编辑程序〔EDIT〕，命令形式为：SQL>EDITEDIT将缓冲区中的内容装入系统缺省的文本编辑器，然后用文本编辑器的命令编辑文本。完成后保存编辑的文本，然后退出。该文本保存到当前的缓冲区。保存SAVE命令SQL>SAVE文件名例如：SQL>LIST1 SELECTEMPNO,ENAME,JOB,SAL2 FROMEMPWHERESAL<2500;然后用SQVE保存到EMPINFO文件中：SQL>SAVEempinfoCreatedfileempinfo运行命令文件可用命令START文件名或者@文件名的命令格式。如上例：SQL>STARTEMPINFO或SQL>@EMPINFO清缓冲区SQL>CLEARBUFFERDESCRIBE列出表的结构如：SQL>DESCEMPNameNull?TypeEMPNONOTNULLNUMBER(4)ENAMEVARCHAR2(10)JOBVARCHAR2(10)MGRNUMBER(4)HIREDATEDATESALNUMBER(7,2)COMMNUMBER(7,2)DEPTNONOTNULLNUMBER(2)Oracle查询显示EMP表中所有的部门号、职工名称和管理者号码：SELECTDEPTNO,ENAME,MGRFROMEMP;DEPTNOENAMEMGR-----------------------------------------------20SMITH790230ALLEN769830WARD769820JONES783930MARTIN769830BLAKE783910CLARK783920SCOTT756610KING30TURNER769820ADAMS778830JAMES769820FORD756610MILLER7782算术运算符在SQL中的使用SELECTENAME,SAL+250*12FROMEMP;连字符的使用把职工号和职工名字连接起来，如下：SELECTEMPNO||ENAMEEMPLOYEEFROMEMP;把职工号和职工名字中间用‘-’连接起来，并输出‘WORKSINDEPARTMENT’，如下：SELECTEMPNO||’-‘||ENAMEEMPLOYEE,‘WORKSINDEPARTMENT’,DEPTNOFROMEMP;禁止重复如果列举出EMP表中所有部门号：Selectdeptnofromemp;DEPTNO----------------2030302030301020103020302010从上表中可以看出部门号之间存在着许多的相同的，可以用DISTINCT子句来消除重复的。SELECTDISTINCTdeptnofromemp;DEPTNO---------------102030排序按单个字段排序，如按照ENAME排序，SELECTENAME,JOB,SAL*12,DEPTNOFROMEMPORDERBYENAME;ENAMEJOBSAL*12DEPTNO----------------------------------------------------------------------------------------------------ADAMSCLERK1320020ALLEMSALESMAN1920030BLAKEMANAGER3420030CLARKMANAGER2940010FORDANALYST3600020JAMESCLERK1140030JONESMANAGER3570020KINGPRESIDENT6000010MARTINSALESMAN1500030MILLERCLERK1560010SCOTTANALYST3600020SMITHCLERK960020TURNERSALESMAN1800030WARDSALESMAN1500030按多个字段排序：如按部门号升序，按工资降序排序SELECTDEPTNO,JOB,ENAMEFROMEMPORDERBYDEPTNO,SALDESC;DEPTNOJOBENAME---------------------------------------------------------------------------10PRESIDENTKING10MANAGERCLARK10CLERKMILLER20ANALYSTSCOTT20ANALYSTFORD20MANAGERJONES20CLERKADAMS20CLERKSMITH30MANAGERBLAKE30SALESMANALLEN30SALESMANTURNER30SALESMANWARD30SALESMANMARTIN30CLERKJAMES带条件的查询1〕查询工作是CLERK的所有职工的姓名，职工号和部门号SELECTENAME,EMPNO,JOB,DEPTNOFROMEMPWHEREJOB=‘CLERK’;ENAMEEMPNOJOBDEPTNO----------------------------------------------------------------------------------------------------SMITH7369CLERK20ADAMS7876CLERK20JAMES7900CLERK30MILLER7934CLERK102〕从DEPT表中查询出部门号大于20的部门名称SELECTDNAME,DEPTNOFROMDEPTWHEREDEPTNO>20;DNAMEDEPTNO--------------------------------------------------SALES30OPERATIONS403〕复合条件查询查询工作是MANAGER并且工资大于1500，或者工作是SALESMAN的职工信息：SELECTEMPNO，ENAME，JOB，SAL，DEPTNOFROMEMPWHERESAL>1500ANDJOB=‘MANAGER’ORJOB=‘SALESMAN’；EMPNOENAMEJOBSALDEPTNO-------------------------------------------------------------------------------------7499ALLENSALESMAN1600．00307521WARDSALESMAN1250．00307566JONESMANAGER2975．00207654MARTINSALESMAN1250．00307698BLAKEMANAGER2850．00307782CLARKMANAGER2450．00107844TURNERSALESMAN1500．0030操作符的应用BETWEEN的应用查询工资在1000到2000之间的职工名字和工资信息。SELECTENAME，SALFROMEMPWHERESALBETWEEN1000AND2000；ENAMESAL----------------------------------------------ALLEN1600．00WARD1250．00MARTIN1250．00TURNER1500．00ADAMS1100．00MILLER1300．00IN查询有7902，7566，7788三个MGR号之一的所有职工：SELECTEMPNO，ENAME，SAL，MGRFROMEMPWHEREMGRIN〔7902，7566，7788〕；EMPNOENAMESALMGR-------------------------------------------------------------------------------------------------7369SMITH800．0079027788SCOTT3000．0075667876ADAMS1100．00778889-2FORD3000．007566LIKE通配符%代表任意0或多个字符。通配符_代表任意单个字符。查询名字以“S〞开始的所有职工：SELECTENAMEFROMEMPWHEREENAMELIKE‘S%’；ENAME------------------------SMITHSCOTT查询名字只有4个字符的所有职工：SELECTENAMEFROMEMPWHEREENAMELIKE‘____’；ENAME------------------------WARDKINGFORDISNULL查询没有管理者的所有职工：SELECTENAME，MGRFROMEMPWHEREMGRISNULL；ENAMEMGR----------------------------------KING单&号替代变量数字变量输入：SELECTEMPNO，ENAME，SALFROMEMPWHEREDEPTNO=&DEPARTMENT_NUMBER;Entervaluefordepartment_number:10EMPNOENAMESAL------------------------------------------------------------------7782CLARK2450.007839KING5000.007934MILLER1300.00字符串变量输入：SELECTEMPNO，ENAME，SAL*12FROMEMPWHEREJOB=‘&JOB_TITLE’;Entervalueforjob_title:MANAGERENAMEDEPTNOSAL*12------------------------------------------------------------------JONES2035700.00BLAKE3034200.00CLARK1029400.00字符函数的应用见表4-5数字函数的应用见表4-4日期函数的应用见表4-6，4-7数据类型转换TO_CHAR数字数据转换为字符串selectto_char(8897)fromdual;TO_NUMBER字符串数据转换为数字selectto_number(‘8897’)fromdual;TO_DATE字符串数据转换为日期数据selectto_date(‘12-DEC-02分组函数的应用分组函数见表4-8分组函数应用求平均值SELECTAVG(SAL)FROMEMP;AVG(SAL)------------------2073.2149求最小值SELECTMIN(SAL)FROMEMPWHEREJOB=‘CLERK’;MIN(SAL)---------------------800求数目SELECTCOUNT(*)FROMEMPWHEREDEPTNO=20;COUNT(*)---------------------5GROUPBY子句求每个部门中的平均工资：SELECTJOB，AVG〔SAL〕FROMEMPGROUPBYJOB；JOBAVG〔SAL〕----------------------------------------------ANALYST3000CLERK1037．5MANAGER2758．33333PRESIDENT5000SALESMAN1400HAVING子句查询人数超过3人的部门中的平均工资：SELECTDEPTNO，AVG〔SAL〕FROMEMPGROUPBYDEPTNOHAVINGCOUNT〔*〕>3；DEPTNOAVG〔SAL〕------------------------------------------202175301566．66667连接从EMP和DEPT中查询出职工名字、工作和部门名称：SELECTENAME，JOB，DNAMEFROMEMP，DEPTWHE