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