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

资源描述

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

《实验二 Oracle SQLPLUS环境与查询.docx》由会员分享，可在线阅读，更多相关《实验二 Oracle SQLPLUS环境与查询.docx（17页珍藏版）》请在冰豆网上搜索。

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

实验二OracleSQLPLUS环境与查询

实验二OracleSQL*PLUS环境与查询

一．实验目的及要求

1．熟悉Oracle的基本知识。

2．熟悉Oracle的命令操作环境SQL*PLUS及常用命令。

3．熟悉并掌握oracle常用的查询语句。

二、实验主要内容

1、OracleSQL*PLUS环境及常用命令。

2、Oracle查询。

三、实验仪器设备

在局域网环境下，有一台服务器和若干台客户机。

服务器成功安装Oracle10g数据库服务器（企业版），客户机成功安装Oracle10g客户端软件，网络服务配置正确，数据库和客户端正常工作。

四、实验步骤

1．SQL*Plus的启动

当登录到操作系统后，有三种方法启动SQL*Plus。

1）SQLPLUS

（或SQLPLUSW，下同）

将看到如下的信息：

SQL*Plus:

Release9.0.1.0.1-Productionon星期二9月1314:

27:

512005

请输入用户名：

输入你的用户名字并按回车。

SQL*Plus将提示：

请输入口令：

输入你的密码并按回车。

那么，将看到SQL*Plus的提示符：

SQL>

例如：

SQLPLUS

SQL*Plus:

Release9.0.1.0.1-Productionon星期二9月1314:

27:

512005

EnterUsername:

scott

EnterPassword:

tiger

SQL>

2）SQLPLUSUsername

再提示你输入密码。

例如：

SQLPLUSscott

EnterPassword:

tiger

SQL*Plus:

Release9.0.1.0.1-Productionon星期二9月1314:

27:

512005

SQL>

3）SQLPLUSusername/password

例如：

SQLPLUSscott/tiger

SQL*Plus:

Release9.0.1.0.1-Productionon星期二9月1314:

27:

512005

SQL>

4）断开与数据库的连接

SQL>DISCONNECT

5）退出SQL*Plus

SQL>EXIT

或：

SQL>Quit

2．SQL命令

SQL命令包括数据定义语言（如Create、Alter等）和数据操作语言（SelectInsertUpdateDelete等），这些都可在SQL*Plus中使用。

如：

SQL>SELECTEMPNO,ENAME,JOB,SAL

2FROMEMPWHERESAL<2500;

3．SQL*Plus命令

1）列出缓冲区的内容：

SQL>LIST

SQL*Plus显示当前缓冲区中的SQL命令（注意：

不缓存SQLPlus命令）：

1SELECTEMPNO,ENAME,JOB,SAL

2FROMEMPWHERESAL<2500；

2）编辑当前行

如果上面的例子错误的输入为：

SQL>SELECTEPNO,ENAME,JOB,SAL

2FROMEMPWHERESAL<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列出其命令然后运行它。

1SELECTEMPNO,ENAME,JOB,SAL

2FROMEMPWHERESAL<2500;

3）增加一行

在当前行之后插入一新行，使用INPUT命令。

例如对上面例子增加第3行到该SQL命令中。

形式如下：

SQL>INPUT

接着可进入新行，然后按ENTER键，SQL*PLUS再次提示新行：

3ORDERBYSAL

按ENTER键，表示不进入任何行，然后用RUN检验和重新运行查询。

4）在一行上添加一原文

用APPEND命令，将一原文加到缓冲区中当前行的末端：

SQL>LIST

3*ORDERBYSAL

SQL>APPENDDESC

3*ORDERBYSALDESC

使用RUN检验和重新运行查询。

5）删除一行

●用LIST命令列出要删除的行。

●用DEL命令删除。

SQL>LIST

3*ORDERBYSALDESC

SQL>DEL

6）用系统编辑程序编辑命令

在SQL*PLUS中运行操作系统缺省的文本编辑程序（EDIT），命令形式为：

SQL>EDIT

EDIT将缓冲区中的内容装入系统缺省的文本编辑器，然后用文本编辑器的命令编辑文本。

完成后保存编辑的文本，然后退出。

该文本保存到当前的缓冲区。

7）保存SAVE命令

SQL>SAVE文件名

例如：

SQL>LIST

1SELECTEMPNO,ENAME,JOB,SAL

2FROMEMPWHERESAL<2500;

然后用SQVE保存到EMPINFO文件中：

SQL>SAVEempinfo

Createdfileempinfo

8）运行命令文件

可用命令START文件名或者@文件名的命令格式。

如上例：

SQL>STARTEMPINFO

或SQL>@EMPINFO

9）清缓冲区

SQL>CLEARBUFFER

10）DESCRIBE列出表的结构

如：

SQL>DESCEMP

Name

Null?

Type

EMPNO

NOTNULL

NUMBER（4）

ENAME

VARCHAR2（10）

JOB

VARCHAR2（10）

MGR

NUMBER（4）

HIREDATE

DATE

SAL

NUMBER（7,2）

COMM

NUMBER（7,2）

DEPTNO

NOTNULL

NUMBER

（2）

4．Oracle查询

1．显示EMP表中所有的部门号、职工名称和管理者号码：

SELECTDEPTNO,ENAME,MGR

FROMEMP;

DEPTNO

ENAME

MGR

----------------

-----------------

--------------

SMITH

7902

ALLEN

7698

WARD

7698

JONES

7839

MARTIN

7698

BLAKE

7839

CLARK

7839

SCOTT

7566

KING

TURNER

7698

ADAMS

7788

JAMES

7698

FORD

7566

MILLER

7782

2．算术运算符在SQL中的使用

SELECTENAME,SAL+250*12FROMEMP;

3．连字符的使用

把职工号和职工名字连接起来，如下：

SELECTEMPNO||ENAMEEMPLOYEEFROMEMP;

把职工号和职工名字中间用‘-’连接起来，并输出‘WORKSINDEPARTMENT’，如下：

SELECTEMPNO||’-‘||ENAMEEMPLOYEE,‘WORKSINDEPARTMENT’,DEPTNO

FROMEMP;

4．禁止重复

如果列举出EMP表中所有部门号：

Selectdeptnofromemp;

DEPTNO

----------------

从上表中可以看出部门号之间存在着许多的相同的，可以用DISTINCT子句来消除重复的。

SELECTDISTINCTdeptnofromemp;

DEPTNO

---------------

5．排序

按单个字段排序，如按照ENAME排序，

SELECTENAME,JOB,SAL*12,DEPTNO

FROMEMP

ORDERBYENAME;

ENAME

JOB

SAL*12

DEPTNO

-------------------------

ADAMS

CLERK

13200

ALLEM

SALESMAN

19200

BLAKE

MANAGER

34200

CLARK

MANAGER

29400

FORD

ANALYST

36000

JAMES

CLERK

11400

JONES

MANAGER

35700

KING

PRESIDENT

60000

MARTIN

SALESMAN

15000

MILLER

CLERK

15600

SCOTT

ANALYST

36000

SMITH

CLERK

9600

TURNER

SALESMAN

18000

WARD

SALESMAN

15000

按多个字段排序：

如按部门号升序，按工资降序排序

SELECTDEPTNO,JOB,ENAME

FROMEMP

ORDERBYDEPTNO,SALDESC;

DEPTNO

JOB

ENAME

-------------------------

PRESIDENT

KING

MANAGER

CLARK

CLERK

MILLER

ANALYST

SCOTT

ANALYST

FORD

MANAGER

JONES

CLERK

ADAMS

CLERK

SMITH

MANAGER

BLAKE

SALESMAN

ALLEN

SALESMAN

TURNER

SALESMAN

WARD

SALESMAN

MARTIN

CLERK

JAMES

6．带条件的查询

1）查询工作是CLERK的所有职工的姓名，职工号和部门号

SELECTENAME,EMPNO,JOB,DEPTNO

FROMEMP

WHEREJOB=‘CLERK’;

ENAME

EMPNO

JOB

DEPTNO

-------------------------

SMITH

7369

CLERK

ADAMS

7876

CLERK

JAMES

7900

CLERK

MILLER

7934

CLERK

2）从DEPT表中查询出部门号大于20的部门名称

SELECTDNAME,DEPTNO

FROMDEPT

WHEREDEPTNO>20;

DNAME

DEPTNO

-------------------------

SALES

OPERATIONS

3）复合条件查询

查询工作是MANAGER并且工资大于1500，或者工作是SALESMAN的职工信息：

SELECTEMPNO，ENAME，JOB，SAL，DEPTNO

FROMEMP

WHERESAL>1500ANDJOB=‘MANAGER’ORJOB=‘SALESMAN’；

EMPNO

ENAME

JOB

SAL

DEPTNO

-----------------

----------------

-----------------

------------------

-----------------

7499

ALLEN

SALESMAN

1600．00

7521

WARD

SALESMAN

1250．00

7566

JONES

MANAGER

2975．00

7654

MARTIN

SALESMAN

1250．00

7698

BLAKE

MANAGER

2850．00

7782

CLARK

MANAGER

2450．00

7844

TURNER

SALESMAN

1500．00

7．操作符的应用

1）BETWEEN的应用

查询工资在1000到2000之间的职工名字和工资信息。

SELECTENAME，SAL

FROMEMP

WHERESALBETWEEN1000AND2000；

ENAME

SAL

-------------------------------

---------------

ALLEN

1600．00

WARD

1250．00

MARTIN

1250．00

TURNER

1500．00

ADAMS

1100．00

MILLER

1300．00

2）IN

查询有7902，7566，7788三个MGR号之一的所有职工：

SELECTEMPNO，ENAME，SAL，MGR

FROMEMP

WHEREMGRIN（7902，7566，7788）；

EMPNO

ENAME

SAL

MGR

-------------------------

--------------------------

--------------------

7369

SMITH

800．00

7902

7788

SCOTT

3000．00

7566

7876

ADAMS

1100．00

7788

89-2

FORD

3000．00

7566

3）LIKE

通配符%代表任意0或多个字符。

通配符_代表任意单个字符。

查询名字以“S”开始的所有职工：

SELECTENAME

FROMEMP

WHEREENAMELIKE‘S%’；

ENAME

------------------------

SMITH

SCOTT

查询名字只有4个字符的所有职工：

SELECTENAME

FROMEMP

WHEREENAMELIKE‘____’；

ENAME

------------------------

WARD

KING

FORD

4）ISNULL

查询没有管理者的所有职工：

SELECTENAME，MGR

FROMEMP

WHEREMGRISNULL；

ENAME

MGR

------------------------

----------

KING

8．单&号替代变量

1）数字变量输入：

SELECTEMPNO，ENAME，SAL

FROMEMP

WHEREDEPTNO=&DEPARTMENT_NUMBER;

Entervaluefordepartment_number:

EMPNO

ENAME

SAL

---------------------

------------------------

7782

CLARK

2450.00

7839

KING

5000.00

7934

MILLER

1300.00

2）字符串变量输入：

SELECTEMPNO，ENAME，SAL*12

FROMEMP

WHEREJOB=‘&JOB_TITLE’;

Entervalueforjob_title:

MANAGER

ENAME

DEPTNO

SAL*12

--------------------

-----------------------

JONES

35700.00

BLAKE

34200.00

CLARK

29400.00

9．字符函数的应用

见表4-5

10．数字函数的应用

见表4-4

11．日期函数的应用

见表4-6，4-7

12．数据类型转换

1）TO_CHAR数字数据转换为字符串

selectto_char（8897）fromdual;

2）TO_NUMBER字符串数据转换为数字

selectto_number（‘8897’）fromdual;

3）TO_DATE字符串数据转换为日期数据

selectto_date（‘12-DEC-02’）fromdual;

13．分组函数的应用

1）分组函数见表4-8

2）分组函数应用

求平均值

SELECTAVG（SAL）FROMEMP;

AVG（SAL）

------------------

2073.2149

求最小值

SELECTMIN（SAL）FROMEMPWHEREJOB=‘CLERK’;

MIN（SAL）

---------------------

800

求数目

SELECTCOUNT（*）FROMEMPWHEREDEPTNO=20;

COUNT（*）

---------------------

3）GROUPBY子句

求每个部门中的平均工资：

SELECTJOB，AVG（SAL）FROMEMP

GROUPBYJOB；

JOB

AVG（SAL）

-------------------

---------------------------

ANALYST

3000

CLERK

1037．5

MANAGER

2758．33333

PRESIDENT

5000

SALESMAN

1400

4）HAVING子句

查询人数超过3人的部门中的平均工资：

SELECTDEPTNO，AVG（SAL）FROMEMP

GROUPBYDEPTNO

HAVINGCOUNT（*）>3；

DEPTNO

AVG（SAL）

-------------------

-----------------------

2175

1566．66667

14．连接

从EMP和DEPT中查询出职工名字、工作和部门名称：

SELECTENAME，JOB，DNAMEFROMEMP，DEPT

WHEREEMP.DEPTNO=DEPT.DEPTNO;

ENAME

JOB

DNAME

-----------------------

--------------------

-----------------------

CLARK

MANAGER

ACCOUNTING

MILLER

CLERK

ACCOUNTING

KING

PRESIDENT

ACCOUNTING

SMITH

CLERK

RESEARCH

SCOTT

ANALYST

RESEARCH

JONES

MANAGER

RESEARCH

ADAMS

CLERK

RESEARCH

FORD

ANAYLST

RESEARCH

ALLEN

SALESMAN

SALES

BLAKE

MANAGER

SALES

TURNER

SALESMAN

SALES

JAMES

CLERK

SALES

MARTIN

SALESMAN

SALES

WARD

SALESMAN

SALES

15．子查询的应用

从EMP中查询出工资最低的职工：

SELECTENAME，JOB，SALFROMEMP

WHERESAL=（SELECTMIN（SAL）FROMEMP）；

ENAME

JOB

DNAME

----------------------

------------------------

---------------------

SMITH

CLERK

800

从EMP中查询出每个部门工资最低的职工：

SELECTENAME，SAL，DEPTNOFROMEMP

WHERESALIN

（SELECTMIN（SAL）FROMEMPGROUPBYDEPTNO）；

ENAME

SAL

DEPTNO

------------------------

--------------------

SMITH

800

JAMES

950

MILLER

1300

五、拓展题

复杂查询的不同格式的实现及增删改操作。

展开阅读全文