Oracle 第二天 基本操作

TopN查询
select *
from ( select *  from emp order by sal desc )
where rownum <= 5;

表间数据拷贝
insert into dept1(id, name)  select deptno, dname from dept;

insert into dept values(88, ' 装备部', ' 北京');
update  emp set sal = sal + 1000, comm = comm + 0.5 where empno = 7778;
delete  emp where empno = 7778;

Sql Plus的自动提交
show autocommit;
  • autocommit OFF
  • autocommit IMMEDIATE
set autocommit on;
set autocommit off;
保存点
insert into dept values(55,'Adv','Beijing');
insert into dept values(56,'Sec','Shanghai');
savepoint p1;
insert into dept values(57,'Acc','Tianjin');
---
select * from dept;
rollback to p1;
select * from dept;

数据库对象
创建表
create table scott.test1(
eid number(10),
name varchar2(20),
hiredate  date  default sysdate ,
salary number(8,2) default 0
);
使用子查询创建表
create table myemp(编号,  姓名,  年薪)
as select empno, ename, sal*12 from emp;
修改表结构
添加字段
alter table test1
add (
grade number(3),
phone varchar2(20) default '无'
);
修改字段
alter table test1
modify (
grade number(2),
phone varchar2(15) default '010-12345678'
);
删除字段
alter table test1
drop (grade, phone);
删除表
drop table test1;
重命名表
rename test1 to test88;
Oracle数据库中的表
用户定义的表
数据字典表:由Oracle数据库自动创建并维护的一组表,包含数据库信息
数据字典
数据字典是Oracle数据库的核心，用于描述数据库及其所有对象,数据字典由一系列只读的表和视图组成，这些表和视图属sys用户
拥有，由Oracle server负责维护，用户可以通过select语句进行访问
数据字典的内容
数据库的物理和逻辑结构
对象的定义和空间分配
完整性约束条件
用户,角色,权限,审计记录

数据字典视图
dba -所有方案包含的对象信息
all - 用户可以访问的对象信息
user -用户方案的对象信息
-- 查看当前用户拥有的所有表的名字
select table_name from user_tables;
-- 查看当前用户可以访问的所有表的名字
select table_name from all_tables;
-- 查看当前用户拥有的所有对象的类型
select distinct object_type from user_objects;
-- 查看所有用户拥有的所有对象的类型
select table_name from dba_tables;

约束
not null  （非空）
unique key    （唯一键）
primary key  （主键）
foreign key   （外键）
check （检查）
查看约束
查询用户字典视图user_constrains
查询用户字典视图user_cons_columns

创建/删除视图
create or replace view myview1(编号,  姓名,  职位,  工资)
as select empno, ename, job, sa l from emp where deptno = 20;
创建只读视图
create or replace force view myview2
as select empno, ename, job, sa l from emp2 where deptno = 20
with read only;

索引
create index myindex
on emp(ename);
自动创建- 在定义主键或唯一键约束时系统会自动在相应的字段
上创建唯一性索引。
创建索引的原则
字段取值分布范围很广
字段中包含大量空值
字段经常出现在 where 子句或连接条件中
表经常被访问、数据量很大，且通常每次访问的数据量小于记录
总量的2%~4%
查看索引
查询用户字典视图user_indexes---可得到用户的所有索引
查询用户字典视图user_ind_columns---获知索引建立在哪些字段上

序列
系统自动生成的、不重复的整数值
序列是一种数据库对象，可以被多个用户共享
典型用途是做为主键值，它对于每一行必须是唯一的
序列可以代替应用程序编号
可以对序列值进行缓冲存储，以提高访问效率
create sequence mysequence1
increment by 1
start with 1
nomaxvalue  nocycle ;
查询数据字典视图user_sequences
select * from user_sequences;
使用序列
select mysequence1.currval from dual;
select mysequence1.nextval from dual;
insert into test1 values(mysequence1.nextval, 'Tom');

同义词--同义词相当于对象的别名
create synonym gt1 for emp;

单行函数
字符函数
Upper 大写
select upper('abcde') from dual;
Lower 小写
select lower('ABCDE') from dual;
Initcap 第一个字母大写
select initcap(ename) from emp;
Concat 连接连个字符
select concat（'a','b') from dual;
select 'a' || 'b' from dual;
Substr 子串
select substr('abcde',length('abcde')-2) from dual ： cde
Replace 替换
select replace(ename,'A','a') from emp
Instr 在字符串中的位置
select instr('Hello World','or') from dual
lpad 左侧填充，rpad右侧填充
select lpad('Smith',10,'*') from dual
trim 去除空格
select trim（'  ss  ') from dual;

日期函数
Last_day
select last_day(sysdate) from dual
找出每个月倒数第三天入职的员工
select * from emp where last_day(hiredate) -2 = hiredate
找出25年前雇佣的员工
select * from emp where hiredate <= add_month(sysdate,-25*12);
所有员工名字前加 ’Dear‘，并且名字首字母大写
select 'Dear ' || initcap(ename) from emp;
找出姓名为5个字母的员工
select * from emp where length(ename)=5;
找出姓名中不带’R’字母的员工
select * from emp where ename not like '%R%';
显示所有员工姓名的第一个字母
select substr(ename,0,1) from emp;
找到二月份受雇的员工
select * from emp where to_char(hiredate,'fmmm') = '2';
随机返回5条数据
select * from (select ename,job from emp order by dbms_random.value()) where rownum <=5

over()函数
over()函数,从oracle 8i开始支持,后面的版本支持的比较好. 
通常在做统计分析时我们都想尽可能多滴选择出原始列和统计值列,但是这样group by后面就必须跟随更多的列,使用分析函数可以避免使用group by时选择出来的列名必须出现在group by列表中的痛苦.
查询跳过表中的偶数行
select ename from (select row_number() over (order by ename) rn,ename from emp) x where mod(rn,2)=1
分部门连续求和
select deptno,sal,sum(sal) over(partition by deptno order by ename) as s from emp
得到当前行的上一条和下一条数据
select ename,sal,lead(sal) over(order by sal) aaa,lag(sal) over(order by sal) bbb from emp

trunc(x, y)将日期x 截断到y 所指定的日期
单位（月或年）的第一天
确定一年内的天数
select add_months(trunc(sysdate,'y'),12)-trunc(sysdate,'y') from dual

查询某用户下的所有表
select table_name from all_tables where owner = upper('scott')
列出表的索引咧
select * from sys.all_ind_columns where table_name = 'EMP'
列出表中的约束
select * from all_constraints where table_name = 'EMP'

索引类型
B树索引（B Tree index)
平衡B树算法
右子树大于等于父节点
左子树小于等于父节点
位图索引
表字段取值范围较小，如性别，B树索引没有意思，建议用位图索引
create BitMap Index student on（sex）

SQL优化
select * from t a1 where exists( select * from t a2 where a1.id>a2.id and a1.name =a2.name and a1.age = a2.age)

尽量少用in操作符
尽量用not exists 替代not in ，因为not in 不能用索引
尽量不用 <> 或者 !=操作符 ---不等是永远用不到索引，全表扫描
索引列设置为not null ，因为判断是否为空用不到索引
尽量不要用% _ 操作符，用不到索引
where子句避免在索引列上使用计算，会使索引失效
用>= 替换>
利用SGA 共享池，避开parse阶段
where 后面的条件顺序要求，where后面的条件，表连接写在最前，过滤掉最多记录数的条件写在最后
使用表的别名，并将之作为每列的前缀，减少解析时间
用union all 代替union
使用sql优化工具 sqlexpert，toad，PL/SQL，OEM

通过改变oracle的SGA的大小，
SGA=数据库的系统全局区
ＳＧＡ三部分：共享池，数据缓冲区，日志缓冲区
共享池分为　 共享ＳＱＬ区，和数据字典缓冲区，共享ＳＱＬ区存放用户ＳＱＬ命令，
－－查看共享ＳＱＬ使用率，最好９０％以上，否则增加共享池的大小
select (sum(pins-reloads))/sum(pins) "Lib Cache" from v$librarycache
－－查看数据字典缓冲区命中率，最好９０％以上，否则增加共享池大小
select (sum(gets-getmisses-usage-fixed))/sum(gets) "Row Cache" from v$rowcache
数据缓冲区－－存放ｓｑｌ运行结果抓取的ｄａｔａ　 ｂｌｏｃｋ
select name,value from v$sysstat where name in ('db block gets','consistent gets','physical reads')
使用命中率＝１－（physical reads／（db block gets＋consistent gets）
命中率应在９０％以上，否则增加数据缓冲区大小
日志缓冲区；存放数据库运行生成的日志
select name,value from v$sysstat where name in ('redo entries','redo log space requests')
申请失败率　 ＝　redo log space requests／ redo entries，应接近于０，否则书名日志缓冲区太小，应增加