实验3 SQL基本查询与SQL Plus的使用
姓名：学号：
专业：
同组人：无实验日期：2010-03-18
【实验目的与要求】
1．熟悉Oracle SQL Plus的使用
2．掌握SQL基本查询。
【实验内容与步骤】
3.1启动SQL*Plus
要调用SQL Plus，需依次完成如下步骤：
✧单击Start（“开始”）按钮。
✧指向Programs（“程序”）组图标。
✧选择Oracle—oracle10g。
图3-1
✧选择Application Development（“应用程序开发”）选项。
✧单击SQL Plus。
启动SQL 之后，将会出现图3-2 所示的屏幕。
填写相应的详细资料。
按Tab 键转到下一个选项。
安装Oracle 时创建的默认用户为Scott，密码为tiger。
主机字符串是oracle。
图 3-2
单击 OK （“确定”）。
将会出现如下图所示的屏幕，显示产品信息。
图 3-3
SQL > 是 SQL 提示符。
在此提示符下提供要执行的 SQL 命令和语句。
下面给出可通过 scott/tiger 登录访问的默认表及其列名。
在本实验中，我们将使用这些表。
EMP DEPT SALGRADE EMPNO ENAME JOB MGR
HIREDATE SAL COMM DEPTNO
DEPTNO DNAME LOC GRADE LOSAL HISAL
3.2 从表中查看数据
输入表中的数据可通过使用SELECT语句进行查看。
3.2.1 查看所有列
语法如下：
SELECT * FROM tablename;
其中“*” 表示将显示所有列。
实验3-1 部门表中查看所有记录和所有字段，请执行如下步骤：
✧在SQL 提示符下键入“Select * from dept”（从部门选择*）。
✧按Enter。
您将得到图3-4 中显示的结果。
图3-4
3.2.2 查看选择列
要查看选择列，请输入列名，并用逗点而不是“*”隔开。
语法如下：
SELECT columnname1, columnname2, ?, columnnameN FROM tablename;
实验3-2只显示部门表中的部门名和位置，执行如下所示的语句：
图3-5
请给出运行结果：
某些列标题由于其列大小限制而使用缩写词。
要将字符列的显示宽度设置为12，请给出如下语句：
COLUMN columnname FORMAT A12
COLUMN 语句不能以分号结尾，因为它是格式化命令。
通过COLUMN 命令可以使用的选项包括：
选项功能
FORMAT 指定数据格式
HEADING text text为列标题
JUSTIFY L R C 将标题对齐为LEFT RIGHT CENTER
WRAP TRUNC 在列中回行或截断数据
通过FORMAT 选项可用的掩码包括：
掩码功能
A n n 个字母数字字符
99[9…]不含前导零的数字
00[…]含前导零的数字
. 显示的小数点
V 隐藏的小数点
$ 先导$
MI 数字后面是减号
PR 负值包括在“<>”之内
B 显示空白而不是零
要显示包含前导零的薪金，请在SQL 提示符下给出如下命令。
SQL> column sal format 009999
实验3-3设置格式之后，执行如下所示的命令。
图3-5(a)
请给出运行结果：
这样，所有雇员的薪金就以包含前导零的格式显示。
要删除此格式，请在提示符下输入如下给定命令。
SQL> column sal format 999999
3.2.3 查看符合条件的行
可以使用WHERE 子句完成对某些相关数据行的条件检索。
WHERE 子句中指定的条件称为谓词。
谓词可以是任意数据类型、字符、数字或日期。
像AND、OR 和NOT 这样的逻辑运算符也可以用于合并谓词。
语法如下：
SELECT columnname1, columnname2, ?, columnnameN
FROM tablename
WHERE <condition>;
实验3-4要从雇员表中只显示那些在部门20 工作的雇员，执行如下所示的语句：
图3-6
请给出运行结果：
在谓词中可以有效使用的其他运算符还有：
1.IN 运算符：此运算符用于显式规定可以接受的值组。
实验3-5要只显示属于“CLERK”（办事员）或“MANAGER”（经理）的雇员，执行如下命令。
图3-7
请给出运行结果：
另一方面，NOT IN 运算符将返回与条件不匹配的行。
2.BETWEEN 运算符：此运算符与IN 运算符相似，不过可以指定值的范围而不是匹
配的值组。
实验3-6要只显示在“03-JAN-82”（1982 年1 月1 日）和“33-DEC-87”（1987 年12 月31 日）之间加入的雇员，请给出如下命令。
图3-8
请给出运行结果：
NOT BETWEEN 运算符将获取与条件不匹配的行。
3.LIKE 运算符：该运算符用于匹配字符串或字符串的一部分（称为子字符串）。
该
运算符使用通配符扩展其字符串匹配功能。
有两种通配符与LIKE 运算符一起使用：
下划线(_)：这表示任意单字符。
百分号(%)：这表示多字符的序列。
实验3-7 要显示姓名以“J”开头的雇员，请给出如下命令。
图3-9
请给出运行结果：
3.2.4 查看汇总数据（使用统计函数）
GROUP BY 子句根据字段值对行进行分组。
该子句在应用时将与聚合函数联合。
有时，也要求对已分组的查询进行条件检索。
可以使用“HAVING”子句对已分组查询进行条件检索。
“HAVING”子句与“WHERE”子句相似，只不过“WHERE”子句用于行，而“HAVING”子句用于已分组的结果。
语法如下：
SELECT columnname1, aggregate_function(columnname2)
FROM tablename
GROUP BY columnname1;
实验3-8要从雇员表中显示付给经理的最高薪金，执行如下所示的语句：
图3-10
请给出运行结果：
聚合函数通常与Group by 和Having 子句一起使用。
要讨论的聚合函数是：
1. Count：此函数对包括Null 值的查询所返回的行数进行记数。
实验3-9要查找每个工作类别的人数，执行如下所示的语句：
图3-11
请给出运行结果：
2. Max：从查询返回的值中返回最大值。
Selelct job,count(job)
From emp
Group by job
Having
3.Min：从查询返回的值中返回最小值。
4. Avg：用来确定某个列中出现的值的平均数。
5. Sum：用来获取查询输出的行的算术和。
实验3-10要从雇员表中显示付给经理的最高、最低、平均和总薪金，执行如下所示的语句：
图3-12
请给出运行结果：
3.2.5 以指定顺序查看数据
ORDER BY子句可帮助按照希望出现的顺序检索查询。
可以通过指定DESC 参数或ASC 参数来按升序顺序或降序顺序进行排序。
如果没有指定任何参数，则默认为升序排序。
还可对多列进行排序。
语法如下：
SELECT columnname1, columnname2, ?, columnnameN
FROM tablename
WHERE <condition>;
ORDER BY columnname1, columnname2, … , columnnameN;
实验3-11要以雇员的工作的顺序显示收入大于或等于3000 的雇员，执行如下所示的语句：
图3-13
请给出运行结果：
3.2.6 使用计算列查看数据
除了从数据库表中直接提取的列外，SQL 还可以用于计算多个列中的数据的值。
可在查询中使用SQL 表达式执行所有类型的算术操作。
唯一必须注意的是，这些表达式涉及的列应该只属于数字数据类型。
语法如下：
SELECT columnname1, (columnname2 <arithmetic operator> columnname3)
FROM tablename
实验3-12要从雇员表中显示雇员总薪金，执行如下语句：
图3-14
请给出运行结果：
注意：在上面的命令中，NVL 函数用来将空值转换为数字值，我们在此将其转换为“0”。
我们必须按任意表达式执行此操作，表达式中包含空值，因为操作数返回NULL。
因此，为避免在任何操作中出现这种错误，我们使用NVL 函数。
3.3退出SQL
在SQL 提示符下键入Exit （“退出”）或执行如下步骤：
✧从Main Menu（“主菜单”）中选择File（“文件”）选项。
✧单击Exit（“退出”）。
✧您也可以在命令提示符下输入“Exit”。
3.4 实验练习：
在scott用户模式下，完成下查询需求，给出相关查询语句和运行结果：
(1)列出各种类别"SALES"工作的雇员的姓名，假定不知道销售部的部门编号.
(2)列出薪金高于公司平均水平的所有雇员.
(3)列出与"SCOTT"从事相同工作的所有雇员.
select ename
from emp
where job=
(select job
from emp
where ename=upper('scott'));
(4)列出分配有雇员数量的所有部门的详细信息,即使是分配有0个雇员. select dept.deptno,dname,loc,count(empno)
from dept,emp
where dept.deptno=emp.deptno(+)
group by dept.deptno,dname,loc ;
(5)列出各种类别工作的最低工资.
Select min(sal) from emp group by job;
(6)列出各个部门MANAGER的最低薪金.
select deptno,min(sal)
from emp
where job=upper('manager')
group by deptno;
(7)列出按计算的字段排序的所有雇员的年薪.
(8)选择部门 30 中的雇员。
select * from emp
where deptno=30;
(9)列出所有办事员的姓名、编号和部门。
select ename,empno,dname
from emp e
inner join dept d on e.deptno = d.deptno where job=upper('clerk');
(10)找出佣金高于薪金的雇员。
select * from emp where comm>sal;
(11)找出佣金高于薪金 60% 的雇员。
select *
from emp
where comm>sal*0.6;
(12)找出部门 10 中所有经理和部门 20 中所有办事员的详细资料。
select * from emp
where (deptno=10 and job=upper('manager'))
or (deptno=20 and job=upper('clerk'));
(13)找出部门 10 中所有经理、部门 20 中所有办事员以及既不是经理又不是办事员但
其薪金大于或等于 2000 的所有雇员的详细资料。
select * from emp
where (deptno=10 and job=upper('manager'))
or (deptno=20 and job=upper('clerk'))
or (job<>upper('manager')
and job<>upper('clerk') and sal>=2000);
(14)找出不收取佣金或收取的佣金低于 100 的雇员。
select * from emp
where nvl(comm,0)<100;
(15)找出早于 12 年之前受雇的雇员。
select *
from emp
where months_between(sysdate,hiredate)/12>12;
(16)显示只有首字母大写的所有雇员的姓名。
select ename
from emp
where ename=initcap(ename);
(17)显示雇员的详细资料，按姓名排序。
select *
from emp
order by ename;
(18)显示雇员姓名，根据其服务年限，将最老的雇员排在最前面。
select ename
from emp
order by hiredate;
(19)显示所有雇员的姓名、工作和薪金，按工作内的工作的降序顺序排序，同工作按薪
金排序。
select ename,job,sal
from emp
order by job desc ,sal asc;
(20)显示姓名字段的任何位置包含“A”的所有雇员的姓名。
select ename
from emp
where ename like '%A%';
select ename
from emp
where instr(ename,'A',1)>0;
第21 页共21 页。
