oracle实验1

实验一 Oracle数据库的体系结构——逻辑结构（2学时）

实验目的:在理解在Oracle数据库的体系结构的基础上,在sqlplus环境下查看oracle的逻辑体系结构中括表空间、段、区、块的信息，熟练掌握各种查看语句。 实验内容：1.通过sql语句查看表空间信息 2. 通过sql语句查看段信息

3. 通过sql语句查看区信息 4. 通过sql语句查看块信息

1.理解逻辑体系结构

2.表空间的查看 2.1表空间内涵理解 公司 表空间 一个楼有多个公司 一个数据库有多个表空间 一个公司必须占一个房间 一个公司所占面积是房间和 公司可以自由安排上下班，但为其服务的食堂等则不能 一个表空间必须有一个数据文件 表空间大小是数据文件和 System必须在线 一个公司可以占多个房间，房间数可固定，也可扩展或缩小，但应事先规划

每个公司的设备、员工都应安排在相应的位置，

仓库里的东西堆不下，可以放到别的房间 一个员工可以为本公司服务，业务需求也可能与别的公司服务 一个用户拥有的办公空间是定额的

盖大楼时，可以根据需要盖不同功能的房间，如办公楼，商铺，娱乐区，进驻大楼的公司可以是永久的，可以是临时的

为了更好交流，将相近办公室放在一起，

表空间有多个数据文件，可以增加数据文件

数据对象应存储在相应的段中，如数据段、索引段等

一个数据文件放不下，可以放到别的数据文件中，只要是一个表空间即可

一个用户默认一个表空间，但其拥有的对象可以放在不同表空间，如create table指定表空间

一个用户使用的表空间是有限制的，不能超出

一个数据库有多个表空间，表空间有不同类型

将相关的数据文件放在一个表空间中

2.2通过Oracle视图查看表空间信息 段 类 型 V$TABLESPACE DBA_TABLESPACES USER_TABLESPACES DBA_TABLESPACE_GROUPS DBA_SEGMENTS USER_SEGMENTS 说 明 控制文件中保存的所有表空间的名称和数量 所有表空间的属性和在线状态信息 所有用户可访问表空间的描述信息 所有表空间组及其所属的表空间信息 所有表空间中的区间信息 所有用户表空间中的区间信息 DBA_FREE_SPACE USER_FREE_SPACE V$DATAFILE V$TEMPFILE DBA_DATA_FILES DBA_TEMP_FILES 所有表空间中的空闲区间信息 所有用户表空间中的空闲区间信息 所有数据文件信息 所有临时文件信息 显示所有属于表空间的数据文件信息 显示所有属于临时表空间的临时文件信息 （1）使用V$TABLESPACE视图查看表空间信息 SELECT * FROM V$TABLESPACE;

（2）查看表空间的属性dba_tablespaces

SELECT TABLESPACE_NAME,CONTENTS, STATUS FROM DBA_TABLESPACES; （3）查看表空间组及其所属的表空间信息dba_tablespace_groups （4）查看表空间中所包含的段信息dba_segments

Select segment_name,segment_type,extents,tablespace_name from dba_segments where tablespace_name=?SYSTEM?; Select * from v$rollname ;查看回滚段的名称列表 Select * from v$rollstat ;查看回滚段的统计信息；

Select segment_name,tablespace_name,bytes,blocks,segment_type from dba_segments where segment_type=?ROLLBACK? （5）查看表空间中空闲区间的信息

dba_free_space

select * from dba_segments where user=?SCOTT?;

SELECT TABLESPACE_NAME, FILE_ID, BYTES, BLOCKS FROM DBA_FREE_SPACE;

实验二 Oracle数据库的体系结构——物理结构（2学时）

实验目的:在理解在Oracle数据库的体系结构的基础上,在sqlplus环境下对oracle的物理体系结构的操作，其中包括控制文件、数据文件、日志文件，熟练掌握以上操作的各种语句。 实验内容：1. 控制文件操作

1.1 查看控制文件 1.2 复制控制文件 1.3 新建控制文件 2. 数据文件

2.1 查看数据文件 2.2 新建控制文件

2.3 修改数据文件 3.日志文件

3.1查看日志文件

3.2 新建日志文件、日志组 3.3 删除日志文件、日志组

1.理解物理体系结构

2.实际体验物理体系结构

? Drop table t

? Create table t as select * from all_objects; ? Create index object_id_idx on t(object_id); ? Set autotrace on ? Set timing on

? Select object_name from t where object_id=29; Select /*full(t)*/ object_name from t where object_id=29 时间 物理读降低

3.查看物理体系结构各部分的内容

? ? ?

Show parameter sga Show parameter pga

Show parameter shared_pool_size;

? Show parameter db_cache_size;数据缓冲池 ? Show parameter log_buffer;日志缓冲区 4.控制文件

4.1从视图V$CONTROLFILE中查询控制文件的名称列表

SELECT NAME FROM V$CONTROLFILE;

4.2从视图V$CONTROLFILE_RECORD_SECTION中查询到控制文件中保存数据的记录类型、记录大小、记录总数量、使用记录数量等信息

SELECT TYPE,RECORD_SIZE,RECORDS_TOTAL,RECORDS_USED FROM V$CONTROLFILE_RECORD_SECTION; 4.3 创建控制文件

? （1）创建初始控制文件

?

?

? ? ? ?

（2）创建新的控制文件

① 了解当前数据库日志文件和数据文件的情况 查看当前数据库中日志文件的列表 SELECT MEMBER FROM V$LOGFILE;

? ?

查看当前数据库中数据文件的列表

② 根据日志文件和数据文件列表设计CREATE CONTROLFILE语句

CREATE CONTROLFILE DATABASE ORCL

LOGFILE GROUP 1 ('D:\\app\\Administrator\\oradata\\orcl\\redo01.log'), GROUP 2 ('D:\\app\\Administrator\\oradata\\orcl\\redo02.log'), GROUP 3 ('D:\\app\\Administrator\\oradata\\orcl\\redo03.log') NORESETLOGS

DATAFILE 'D:\\app\\Administrator\\oradata\\orcl\\system01.dbf', 'D:\\app\\Administrator\\oradata\\orcl\\sysaux01.dbf', 'D:\\app\\Administrator\\oradata\\orcl\%undotbs01.dbf', 'D:\\app\\Administrator\\oradata\\orcl\%users01.dbf', 'D:\\app\\Administrator\\oradata\\orcl\\orcltbs01.dbf' MAXLOGFILES 50 MAXLOGMEMBERS 3 MAXLOGHISTORY 400 MAXDATAFILES 200

MAXINSTANCES 6 ARCHIVELOG; ③ 关闭数据库实例

SHUTDOWN NORMAL ④ 备份原来的文件

⑤ 启动数据库实例，但不加载数据库

STARTUP NOMOUNT ⑥ 创建控制文件

? 执行前面设计的CREATE CONTROLFILE语句，创建控制文件。 ⑦ 备份控制文件

为了保证新的数据库文件不被破坏，建议将新的控制文件备份到其他不在线的存储介质中，如U盘、移动硬盘或磁带等 ⑧ 修改初始化参数

? 如果新建的控制文件与CONTROL_FILE参数中定义的控制文件不同，则根据实际

情况修改CONTROL_FILE参数；如果修改了数据库名称，则还需要修改DB_NAME参数。

5.数据文件

5．1查看数据文件信息

5.1.1从视图V$DATAFILE中查看数据文件的信息

SELECT NAME, STATUS, BYTES FROM V$DATAFILE; 5. 2 创建数据文件

CREATE TABLESPACE 表空间名

DATAFILE 数据文件名 SIZE 数据文件大小;

【例】 创建表空间MyTbs，同时创建一个50MB的数据文件，代码如下： CREATE TABLESPACE MyTbs

DATAFILE 'D:\\APP\\ADMINISTRATOR\\ORADATA\\ORCL\\MyDataFile01.DBF' SIZE 50M;

? 【例】 创建表空间TempTbs，同时创建一个10MB的临时文件，代码如下： CREATE TEMPORARY TABLESPACE TempTbs

TEMPFILE 'D:\\APP\\ADMINISTRATOR\\ORADATA\\ORCL\\MyTempFile01.DBF' SIZE 50M EXTENT MANAGEMENT LOCAL;

? 使用ALTER TABLESPACE语句修改表空间时，可以使用ADD DATAFIEL关键字

向表空间中添加数据文件。 ? 【例】 向表空间MyTbs中添加一个数据文件MyDataFile02.DBF，大小为50MB，

代码如下： ALTER TABLESPACE MyTbs

ADD DATAFILE 'F:\\APP\\ADMINISTRATOR\\ORADATA\\ORCL\\MyDataFile02.DBF' SIZE 50M;

5.3．修改数据文件的大小

? 使用ALTER DATABASE语句可以修改数据文件的大小，语法如下： ALTER DATABASE DATAFILE 数据文件名 RESIZE 数据文件大小;

? 【例】 将数据文件

D:\\APP\\ADMINISTRATOR\\ORADATA\\ORCL\\USERS01.DBF的大小修改为100M，代码如下：

ALTER DATABASE DATAFILE

'D:\\APP\\ADMINISTRATOR\\ORADATA\\ORCL\\USERS01.DBF' RESIZE 100M;

? 可以执行下面的语句查看当前数据库中数据文件的大小。 SELECT NAME, BYTES FROM V$DATAFILE; 5．4 修改数据文件的在线状态

ALTER DATABASE DATAFILE 数据文件名 ONLINE | OFFLINE;

? 【例】 将数据文件

D:\\APP\\ADMINISTRATOR\\ORADATA\\ORCL\\USERS01.DBF的在线状态修改为脱机，代码如下： ALTER DATABASE DATAFILE

'D:\\APP\\ADMINISTRATOR\\ORADATA\\ORCL\\USERS01.DBF' OFFLINE;

? 也可以设置指定表空间中所有数据文件的在线状态，语法如下： ? ALTER TABLESPACE 表空间名DATAFILE ONLINE | OFFLINE;

【例】 将表空间MYTBS中所有数据文件设置为联机状态，代码如下：ALTER TABLESPACE MYTBS DATAFILE ONLINE; 5．5删除数据文件

? 【例】 删除表空间MyTbs，同时删除其中数据文件的代码如下：

DROP TABLESPACE MyTbs INCLUDING CONTENTS CASCADE CONSTRAINTS;

? 也可以使用ALTER DATABASE命令删除指定的数据文件。

? D:\\APP\\ADMINISTRATOR\\ORADATA\\ORCL\\MyDataFile01.DBF的语句如下： ALTER DATABASE DATAFILE

'F:\\APP\\ADMINISTRATOR\\ORADATA\\ORCL\\MyDataFile01.DBF' OFFLINE

6.日志文件

6.1查看日志文件信息

? 查询视图V$LOGFILE，显示重做日志的成员文件，语句如下： SELECT GROUP#,MEMBER FROM V$LOGFILE;

? 查询视图V$LOG，显示控制文件中重做日志组的信息，语句如下： SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG; 6.2 创建日志组

在ALTER DATABASE语句中使用ADD LOGFILE子句创建重做日志组：

? 添加重做日志文件log1c.rdo和log2c.rdo，初始大小为5M，代码如下： ALTER DATABASE ADD LOGFILE ('log1c.rdo', 'log2c.rdo') SIZE 5000k; Select * from v$logfile;

? 【例】 创建重做日志组10，其中包含重做日志文件log1c.rdo和log2c.rdo，

语句如下： ALTER DATABASE

ADD LOGFILE GROUP 10 ('log1a.rdo', 'log2a.rdo') SIZE 5000k; Select * from v$logfile; 6.3 创建重做日志成员

? 【例】 将重做日志文件log3a.rdo添加到编号为10的重做日志组中，语句如下： ALTER DATABASE ADD LOGFILE MEMBER 'log3a.rdo' TO GROUP 10;

6.4 删除日志组

? 删除编号为10的重做日志组，语句如下：

ALTER DATABASE DROP LOGFILE GROUP 10; 6.5删除重做日志成员

? 【例5.47】 删除重做日志文件log1a.rdo，语句如下： ALTER DATABASE DROP LOGFILE MEMBER 'log1a.rdo';

实验三 常用SQL语言的使用（2学时）

实验目的:熟悉常用的sql语句。

实验内容：1. 查询语句：查询所有、指定段、使用别名、模糊查询、排序查询 2. 单行函数的使用

3. 多表查询 4. 子查询

实验三、四的要求：本实验以题目的方式进行。考虑到大家在《数据库概论》中学过部分sql语句，简单的sql语句在实验手册中没写出sql语句，请大家自己思考，自己书写，复杂的sql语句已经给出，请大家思考后练习

1.常用查询语句

看emp表的所有信息

看有哪些部门，部门有哪些人、每个人的工作是什么 老板要给每个员工多发600元，看每个员工要发多少钱 如果要给老板打印一份员工的工资表，使用上述查询语句老板可能看不懂ename,job,sal，因此需给老板一个看懂的工资表，因此要对列使用别名

如果老板要求文档的列标为 Employee' Salary,而且数据为*** annual salary is ***，如SMITH annual salary is 15600 从emp表中查询有几个部门

如果想查询每个部门中的岗位有哪些？ 查找smith的薪水，工作，所在部门

查找在82年1月1号入职的员工姓名、雇佣日期 查找薪水在2000—3000的雇员姓名和薪水 显示首字母为S的员工的姓名和工资

显示第三个字符为O的所有员工姓名和工资 如果老板让你查找没有绩效的员工名称

如果老板让你查找员工姓名、工资、绩效和年收入

查找员工岗位是 SALESMAN、CLERK、MANAGER的员工信息 查找员工岗位不是SALESMAN、CLERK、MANAGER的员工信息

按sal升序排列，如果有工资相同的，按姓名字符从低到高排序，如成绩排序 选择在部门 30 中员工的所有信息

列出职位为（MANAGER）的员工的编号，姓名 找出奖金高于工资的员工

找出每个员工奖金和工资的总和

找出部门 10 中的经理(MANAGER)和部门 20 中的普通员工(CLERK)

找出部门 10 中既不是经理也不是普通员工，而且工资大于等于 2000 的员工 找出没有奖金的不同工作

找出没有奖金或者奖金低于 500 的员工 2.单行函数 2.1字符型函数

? ? ? ?

Upper Lower Initcap Concat

? Substr ? Length ? Replace ? instr 2.2数字型函数

? ROUND(表达式,n):四舍五入 ? TRUNC(表达式,n)：不四舍五入 ? MOD(m,n)：取余数

? select round(168.888,1) ,trunc(168.888,1) ,mod(1900,400) from dual; ? 结果为：168.9,168.8,300

? mod(300,400)余数为300，应记住oracle的这个规定 2.3日期型函数

? ? ? ? ? ?

Months_between()

Select ename,job,hiredate,months_between(sysdate,hiredate) from emp;

Add_months()

select add_months(sysdate,1) from dual; Next_day()

select next_day(sysdate,'星期一') from dual;系统日期的下一个星期一

? Last_day

? select last_day(sysdate) from dual; 2.4转换函数

? 1、to_char (日期,?fmt?):日期转换为字符

? fmt为格式，如：'dd-mm-yyyy'，yyyy/mm/dd

? select to_char(sysdate,'dd-mm-yyyy') from dual;结果为14-08-2014 ? select to_char(sysdate, 'yyyy/mm/dd ') from dual; 结果为2014/08/14 ? 2、to_char (数字,'fmt'):数字转换为字符串

? select to_char(sal*12,'L99999.00') from emp; ￥81468.00 ? 3、to_date(字符串,['fmt'])：

? select to_date('16-6月-03') -sysdate from dual; ? 课后练习：

? 所有员工名字前加上 Dear ,并且名字首字母大写 ? 找出姓名为 6 个字母的员工

? 找出姓名中不带 R 这个字母的员工 ? 显示所有员工的姓名的第一个字

? 假设一个月为 30 天，找出所有员工的日薪，不计小数 ? 找到 2 月份受雇的员工

? 列出员工加入公司的天数(四舍五入）

? 分别用 case 和 decode 函数列出员工所在的部门，deptno=10 显示'部门 10', ? deptno=20 显示'部门 20' deptno=30 显示'部门 30' deptno=40 显示'部门

40' 否则为'其他部门?

3. 分组函数

3.1 count：

select count(*) from emp;

? 公司中有多少员工由经理管理（即不属于高级管理成层）；

? ? ? ?

select count(mgr) from emp; 查看部门号为10的员工数

select count(*) from emp where deptno=10; 查看有多少个岗位

select count(distict job) from emp; 3.2 avg

? select avg(sal) from emp; 平均工资 3.3 sum

? 查看这个月工人的工资支出

? select sum(sal+nvl(comm,0)) from emp; 3.4 min

? select min(sal) from emp;最少工资 3.5 max

? select max(sal) from emp;最多工资 ? 查找雇佣第一员工和最迟雇佣员工

? Select min(hiredate),max(hiredate) from emp; 3.6 group by：一般与分组函数一起使用

? Select deptno,avg(sal) from emp group by deptno;

? select job,avg(sal) from emp having avg(sal) >2000 group by job; 3.8分组函数嵌套

? 查找工作不是president的员工中按工作分类不同工作的最低平均工资和最高平均

工资 select min(avg(sal)),max(avg(sal)) from emp where job not like 'PRE%' group by job;

课后练习：

? 分组统计各部门下工资>600 的员工的平均工资 ? 统计各部门下平均工资大于 1600 的部门 ? 算出部门 30 中得到最多奖金的员工奖金 ? 算出每个职位的员工数和最低工资 ? 显示每个部门的平均工资和最高工资 ? 查询最高工资的员工姓名、岗位、工资 ? 查询出高于平均工资的员工的信息

? 查出高于本部门员工平均工资的员工信息

4.多表查询 4.1 相等连接

? 查询每个员工所属部门和所在的具体地点

? ?

select emp.ename,dept.dname,dept.loc from emp.dept where emp.deptno=dept.deptno;

select a.ename,b.dname,b.loc from emp a,dept b where ?

? ?

a.deptno=b.deptno;

公司高级管理层想知道工资为1600元或以上的员工所属的部门和地点

select emp.ename,dept.dname,dept.loc from emp.dept where emp.deptno=dept.deptno and emp.sal>=1600

4.2 自连接

老板要找每个分析员的上司谈话，要查询每个分析员及上司的姓名

? select w.ename \雇员名\雇员工作\经理姓名\

m.job \经理工作\w.job='ANALYST';

4.3 不等连接

查询显示工资级别在3-5级之间的所有员工

?

select e.empno, e.ename, e.job, e.sal, s.grade from emp e, salgrade s where( e.sal between s.losal and s.hisal) and( s.grade>2 and s.grade<6);

查询员工姓名、工资、工资级别

? ?

select a1.ename,a1.sal,a2.grade from emp a1,salgrade a2 where a1.sal between a2.losal and a2.hisal;

4.4 外连接

? select * from dept 发现有4个部门，编号为40的部门在emp表中并没有见

过，想查出所有部门的名称、地点和员工信息，怎么办 select a.*,b.* from emp a, dept b where a.deptno(+)=b.deptno;

Select emp.empno,emp.ename,emp.sal,dept.deptno,loc from dept left outer join emp on (emp.deptno=dept.deptno);

? Select emp.empno,emp.ename,dept.deptno,dept.loc from emp right

outer join dept on (emp.deptno=dept.deptno);

4.5 表连接特殊语法 using子句

Select e.empno,e.ename,e.sal,d.loc from emp e join dept d using (deptno);

On 子句

Select e.empno,e.ename,e.sal,e.deptno,d.loc from emp e join dept d on (e.deptno=d.deptno) order by d.loc;

左外连接

Select emp.empno,emp.ename,emp.sal,dept.deptno,loc from dept left outer join emp on (emp.deptno=dept.deptno);

右外连接

?

Select emp.empno,emp.ename,dept.deptno,dept.loc from emp right outer join dept on (emp.deptno=dept.deptno); ? ? ? ? ?

5.子查询

5.1 单行子查询

? 查询与SMITH相同职位的人有谁

select ename from emp where job=(select job from emp where ename='SMITH');

查询职位与'SMITH'相同，而工资不超过ADAMS的员工

select ename, job, sal from emp where job=(select job from emp where ename='SMITH') and sal<= (select sal from emp where ename='ADAMS');

? 查询最高工资的员工姓名、岗位、工资

查出高于本部门员工平均工资的员工姓名、部门号、工资、部门平均工资

select e.ename,e.deptno,e.sal,avg.mysal from emp e,(select deptno,avg(sal) mysal from emp group by deptno) avg where (e.deptno=avg.deptno) and (sal>avg.mysal); 5.2 多行子查询

1、使用in 操作符的多行子查询：select ename, job, sal from emp where sal in (select max(sal) from emp group by job);

查询与部门号10相同的工作

思路：查部门号为10的有哪些工作 再查属于这些工作的人

select * from emp where job in (select distinct job from emp where deptno=10); 2、使用all操作符的多行子查询

查询比10号部门工资低的员工的姓名、工作、工资

select ename, job, sal from emp where sal

select ename, job, sal from emp where sal<(select min(sal) from emp where deptno=10); 3、使用any操作符的多行子查询:

查询比每个部门平均工资都高的员工姓名、工作、薪水

select ename, job, sal from emp where sal >any (select avg(sal) from emp group by job); select ename, job, sal from emp where sal >(select min(avg(sal)) from emp group by job); 4、多列子查询

查询与smith工作、部门相同的员工

select ename,job,deptno from emp where (deptno,job)=(select deptno,job from emp where ename='SMITH');

练习：如何显示高于自己部门平均工资的员工的信息

select a1.ename,a1.sal,a1.deptno,a2.agsal from emp a1,(select deptno,avg(sal) agsal from emp group by deptno) a2 where a1.deptno=a2.deptno and a1.sal>a2.agsal;

当在from子句中使用子查询时，该子查询被当做一个视图来对待，因此也叫内嵌视图，当在from子句中使用子查询时，必须给子查询指定别名，否则没办法查处上述例子中的agsal 5、分页

select * from emp;--查出所有，将其作为内嵌视图

select a1.*,rownum rn from (select * from emp) a1;--产生行编号

select a1.*,rownum rn from (select * from emp) a1 where rownum<=10;--取前10

select a1.*,rownum rn from (select * from emp) a1 where rownum<=10 and rownum>=6 --错误的，oracle中rownum只能用一次--取6-10

select a1.*,rownum rn from (select * from emp) a1 where rownum<=10 and rn>=6;--错误 select a1.*,rownum rn from (select * from emp) a1 where rn>=6 and rn<=10;--错误

只能将select a1.*,rownum rn from (select * from emp) a1 where rownum<=10作为一个视图，

?

?

然后执行select * from(select a1.*,rownum rn from (select * from emp) a1 where rownum<=10) where rn>=6;则得到6——10的行数 5.3、其他语句中使用子查询 1、建表语句中使用子查询

create table mytable(id,name,sal) as (select empno,ename,sal from emp); drop table mytable;

用处：如要进行练习，不破坏原表的结构 2、insert语句中使用子查询 建新表

create table employee (name varchar2(10), sal number(7,2),job varchar2(10)); 插入数据

insert into employee select ename,sal,job from emp where deptno=10; 3、删除语句中使用子查询

delete from employee where sal>(select sal from employee where job='CLERK'); 4、update语句中使用子查询

update employee set sal=( select sal from employee where job='CLERK'); 5.4、合并查询： 用集合图来演示

1、union ：取得结果集的并集，自动去掉结果中的重复行

select ename,sal,job from emp where sal>1600 union select ename,sal,job from emp where job='MANAGER';

讲解过程中，分别使用select语句，让大家看是不是去掉重复行 2、union all：与union相似，但不去掉重复行，也不会排序

select ename,sal,job from emp where sal>1600 union all select ename,sal,job from emp where job='MANAGER'; 3、intersect：取交集

select ename,sal,job from emp where sal>1600 interact select ename,sal,job from emp where job='MANAGER'; 取共同行

4、minus：取差集，前面的减去后面的，如果前面的小于后面的，则是空集，

select ename,sal,job from emp where sal>1600 interact select ename,sal,job from emp where job='MANAGER';

用这些是比and、or快

本文来源：https://www.bwwdw.com/article/jdnt.html