《Oracle从入门到精通》

20160204

1.6启动和关闭数据库实例

1、startup

• nomount :启动实例不加载实例
• mount：启动实例、加载数据库并保存数据库的关闭状态
• open：启动实例、加载并打开数据库，这个是默认选项
• force：终止实例并重新启动数据库
• restrict：用于指定以受限制的会话方式启动数据库
• pfile：用于指定启动实例时所使用的文本参数文件，filename就是文件名

2、shutdown

• normal：以正常方式关闭数据库
• transactional：在当前所有的活动事务被提交完毕之后，关闭数据库
• immediate：在尽可能短的时间内立即关闭数据库
• abort：已终止方式来关闭数据库

2.2逻辑存储结构

逻辑存储结构组成：表空间、段、区间和数据块。

• 数据块：oracle逻辑存储结构中最小的逻辑单位，也是执行数据库输入输出操作的最小存储单位。
• 数据区：由一组连续的oracle数据块所构成的oracle存储结构，一个或多个数据块组成一个数据区，一个或多个数据区再组成一个段。
• 段：用户存储表、索引或簇等占用空间的数据对象。
• 表空间：数据库的最大逻辑划分区域，通常用来存放数据表、索引、回滚段等数据对象，任何数据对象在创建时都必须指定存储在某个表空间中。

2.3物理存储结构

物理存储结构的文件组成：数据文件、控制文件、重做日志文件、归档文件、参数文件、口令文件和警告文件等。

1、查询数据库文件：dba_data_files或v$datafile

2、控制文件：记录数据库名、数据库文件与日志文件的名字和位置、数据库建立日期等信息。

    查询控制文件：v$controlfile

3、日志文件：包括重做日志和归档日志文件。

　 查询日志文件：v$logfile

　 查看oracle是否采用归档模式：v$database

　 查看归档文件的所在路径：show parameter log_archive_dest

4、查看服务器参数：v$parameter或show parameter

    修改服务器参数： alter system set db_block_size=4096

2.4oracle11g服务器结构

1、oracle服务器的组成：实例、数据库、程序全局区和前台进程。

• 实例分为系统全局区（SGA）和后台进程（PMON、SMON等）两部分。其中，SGA使用操作系统的内存资源，而后台进程需要使用CPU与内存资源。
• 数据库（Database）包含数据文件（Datafiles）、控制文件（Controlfile）和重做日志文件（redo log file），数据库文件存放在硬盘中。
• 程序全局区是一个非共享内存区域，用于管理用户进程的私有资源。
• 前台进程可以再划分为用户进程和服务器进程，他们CPU和内存资源。

2、系统全局区（SGA）组成：shared pool、buffer cache、redolog buffer、java pool、large pool、streams pool。

• 数据高速缓冲区（Database buffer cache）：脏数据区、空闲区和保留区。
• 重做日志缓冲区（Redo log buffer cache）：存放对数据库进行修改操作时所产生的日志信息。
• 共享池（shared pool）：用于缓存sql语句、pl/sql语句、数据字典、资源锁、字符集以及其他控制结构等。

　　　修改共享池的内存空间大小：alter system set shared_pool_size=30m。

3、程序全局区（PGA）：它的内存在私有区而不在共享区中。show parameter pga。

4、前台进程：包括用户进程和服务进程。

5、后台进程：SMON、PMON、DBWR、LGWR、CKPT。

• DBWR：将内存中的“脏”数据块回写到数据文件中。
• LGWR：将重做日志缓冲区中的数据写入重做日志文件。

　　　触发条件：1）进行提交2）信息满1/3 3）日志在内存中存放操作3s。

3.2设置SQL*PLUS的运行环境

1、set命令

• set pagesize value：设置从顶部标题至页结束之间的行数。
• set newpage value：设置一页中空行的数量。
• set linesize value：设置一行所显示的最多字符总数。
• set pause value：设置输出结果是否滚动显示。
• set numformat format：设置显示数值的默认格式。

3.3常用SQL*PLUS命令

• help：help index
• desc：desc dba_tablespaces
• spool/spool off
• define
• show
• edit [file_name]
• save file_name
• get  file_name
• start/@:用来执行一个sql脚本文件

3.4格式化查询结果

• col 列名 format
• col 列名 heading 别名：定义列标题
• col 列名 null '空值'：如果该列为null，用字符串‘空值’代替
• col 列名 off/on :控制定义的显示属性的状态
• col 列名 wrapped/word_wrapped:按照指定长度这行/按照完整字符串这行

4.3数据检索

select {[distinct|all] column |*} [into table_name] from {tables | views | other select }[where conditions] [group by columns][having conditions][oder by column]

1、简单查询

• select * from dept;
• select sal*(1+0.1),sal from emp;
• select empno as "员工编号"，ename as “员工名称”，job “职务” from emp；
• select distinct job from emp;
• select empno ,ename,sal from emp where sal <>all(3000,950,800);

　　　特殊筛选关键字：like、in、（not）between ...and　...、is null

2、筛选查询

• select *　from emp group by deptno,job order by deptno ;
• select deptno as 部门编号，avg(sal) as 平均工资 from emp group by deptno;
• select deptno as 部门编号，avg(sal) as 平均工资 from emp group by deptno having avg(sal)>2000;

        having后面可以使用聚合函数count、avg、sum，where子句中不可以使用聚合函数。

 

3、多表关联查询

• select e.empno,d.dname from emp e join dept d on e.deptno=d.deptno;

4.5子查询

• select empno,ename,job from emp where deptno=(select depno from dept where dname='RESEARCH');
• select empno,ename,job from emp join dept on emp.deptno=dept.deptno where dept.dname='RESEARCH';

4.6数据库操作

• insert into jobs values('PRO','程序员'，5000,10000)；
• insert into jobs_temp select * from jobs where jobs.max_salary >10000;
• update emp set sal=sal*1.2 where job='salesman';
• update emp set sal =(select avg(sal) from emp where job ='MANAGER') where sal<2000;
• delete from jobs where job_id='PRO';
• truncate table jobs_temp;

 其他相关表操作：

1、创建表

create table 表名 （列名1 类型 约束，列名2 类型 ，...）

如：

create table T2(
userid int,
counts varchar(20),
grade varchar(20)
)

2、删除

• drop table 表名：删除表结构及数据
• truncate table 表名：删除数据，删除操作不能撤销，重新设置高水平线及索引
• delete from 表名：删除数据，高水平线位置不变，所以数据检索速度不变

3、查询

• 简单查询：select * from 表名 where 条件，注意where后面不可以使用聚合函数，如avg、sum、count等。
• 去重查询：select distinct 列名 from 表名
• 查前10项：select * from 表名 where rownum<11;
• 显示特殊格式：select 'userid:'||userid||',counts:'||counts||',grade:'||grade from T2 where userid=3;
  
  
• 查询某列的最大最小值：select max（列名） from 表名，最小用min，其中列的类型只能为数值，如 select max(userid) from t2

4、更新

• 更新数据：update 表名 set 列名=新值 where 条件，如：update t2 set userid=24 where userid=17
• 更新表名：rename 旧表名 to 新表名

 5、列操作

• 添加列：alter table 表名 add（列1 类型，列2 类型），如：alter table T2 add(usertype varchar(20))
• 修改列类型：alter table 表名 modify(列 类型)，如：alter table t2 modify(user_type varchar(10))
• 修改列名：alter table 表名 rename column 旧列名 to 新列名，如：alter table t2 rename column usertype to user_type
• 删除列：alter table 表名 drop column 列名 ，如：alter table t2 drop column user_type

6、复制表及数据

　　create table 新表名 as select * from 要复制的表，如：create table t3 as select * from t2