Oracle实验指导书和实验报告(8)

16.查找员工岗位不是SALESMAN、CLERK、MANAGER的员工信息 select empno,ename,job,hiredate,sal,NVL(comm,0),deptno from emp where job NOT IN('SALSMAN','CLERK','MANAGER');

17.按sal 从低到高排序升序显示 select empno,ename,job,hiredate,sal,NVL(comm,0),deptno from emp order by sal ASC;

18.按sal降序显示 select empno,ename,job,hiredate,sal,NVL(comm,0),deptno from emp order by sal DESC;

19.多列排序：按sal升序排列，如果有工资相同的，按姓名字符从低到高排序，如成绩排序

select empno,ename,job,hiredate,sal,NVL(comm,0),deptno from emp order by sal DESC,ename DESC;

实验四扩展SQL语言的使用（2学时）

实验目的:熟悉oracle自己特殊的sql语句。 实验内容：1.Decode函数case函数替代变量 2.索引的建立、查看、使用

3.用户管理 4.簇的使用 5.序列的使用

1.Decode函数

? select ename,job,decode(job,'CLERK','增加工资','SALESMAN','减工资','暂

时不涨工资') \备注\

? ?

select ename,job,decode(job,?CLERK?,?增加工资?,?SALESMAN?,?减工资?,?暂时不涨工资?) “备注” order by “备注” from emp;

select ename,job,sal,decode(job,

'CLERK',sal*10,'SALESMAN',sal*0.1,sal) \新工资\

2.case函数

? ? ? ? ? ?

select empno,ename,sal, case deptno when 10 then '财务部' when 20 then '研发部' when 30 then '销售' else '未知部门' end 部门

from emp;

3.替代变量

老板一开始让你打印一份工资在1600元以上的员工的清单，你写了SQL语句，但老板改

变主意了，要打印1900以上的，怎么办，来回改吗？

? select empno,ename,sal from emp where sal>=&v_salary; 如果是查询不同岗位的工资

? 如果你想避免多次使用的替换变量被多次提示输入，使用&&开头的替代变量。

? ? ? ?

select ename,job,&&v_col from emp order by &v_col;

执行输入sal，则查出sal，下次再查就不输入了，但你想换成comm，怎么办， undefine v_col,然后再执行，输入comm，则查到了comm

select &v_col1, & v_col2, &v_col3 from &v_tname where &condition order by &sorting;

4.使用ACCEPT定义变量

? ACCEPT 变量名 PROMPT '变量说明文字? ? Accept.sql文件中存储

? ACCEPT v_col1 PROMPT '请输入你想查找的列：' ? ACCEPT v_col2 PROMPT '请输入你想查找的列：' ? ACCEPT v_col3 PROMPT '请输入你想查找的列：' ? ACCEPT v_tname PROMPT '请输入你想查找的表：'

? ACCEPT condition PROMPT '请输入你想查找的表条件：' ? ACCEPT sorting PROMPT '请输入你想按什么排序：'

?

select &v_col1, & v_col2, &v_col3 from &v_tname where &condition order by &sorting;

5.索引

5.1 如何建立索引 自动建立：用户建立表的时候如果有primary key、unique约束时，会自动建立唯一索引unique index

手动建立：用户建立

create index 索引名 on 表名(列名)

create index book_bid_idx on book(bid);//索引名包括了表名、列名、对象名，也可自己命名

createindex abc on student(sid,sname);

create index abc1 on student(sname,sid);他们是不同的

索引abc对Select* fromstudentwheresid=1; 这样的查询语句更有效

索引abc1 对Select* fromstudentwheresname=?louis?; 这样的查询语句更有效因此建立索引的时候，字段的组合顺序是非常重要的。一般情况下，需要经常访问的字段放在组合字段的前面

5.2 查看索引

select index_type,index_name,table_name,uniqueness from user_indexes;

select index_name,table_name,column_name,column_position from user_ind_columns; 5.3 查看索引是否被使用

步骤一：@ D:\\oracle\\product\\10.2.0\\db_1\\RDBMS\\ADMIN\%utlxplan.sql—创建了索引查询表

? 步骤二：explain plan for select ename,job,sal,comm from empcon where (sal-700)<0;--已解释

? 步骤三：desc plan_table; ? 步骤四：查看索引是否使用

? Select id,operation,options,object_name,position from plan_table;

? 步骤五：truncate table plan_table; ? 步骤六：. explain plan for select ename,job,sal from empcon where ename like ‘PRE%’; ? 步骤七：Select id,operation,options,object_name,position from plan_table; 5.4删除索引

drop index 索引名 6. 用户管理

6.1 忘记管理员密码：

? Sqlplus “/ as sysdba ”

? Alter user sys identified by newpwd ? Conn sys / as sysdba 6.2 创建用户:

CREATE USERS 用户名IDENTIFIEDBY 密码 password expire。 ? password expire密码过期，第一次登录强制更改密码 ? create user dog identified by dog 用户名dog ,密码 dog 6.3更改密码

? 更改当前用户更改密码：passw;更改密码为a

? 管理员更改密码：Alter user dog identified by a123; 6.4权限赋予

? 新创建的用户是没有任何权限的，连登录权限都没有 ? conn dog/dog 是登不上的。

系统权限：访问和使用数据库或系统资源的能力

系统权限授予：如登录权限： grant create session to dog另一种：登录权限：grant connect to dog; 严格说是角色，

对象权限：维护数据库中的对象的能力，如增、删、改、查 对象权限授予

对象权限grant select,update,delete,insert on scott.emp to dog;将对scott用户下的表emp的增、删、改、查授予与用户dog; 6.5 查看权限

? 查看系统权限select grantee, privilege from dba_sys_privs where grantee='SCOTT'; ? select * from session_privs ? 查看所有用户：

? select * from dba_users; dba用户使用 ? select * from all_users; ? select * from user_users; ? ? ? ? ? ? ? ? ? ?

查看用户系统权限：不能实现

select * from dba_sys_privs; select * from all_sys_privs; select * from user_sys_privs; 查看oracle用户对象权限：

select * from dba_tab_privs; select * from all_tab_privs; select * from user_tab_privs; 查看所有角色：

select * from dba_roles;