oracle笔记.doc

单行函数
字符，数字，日期，转换，通用
1，字符
lower（）
upper（）
initcap（）
replace（字段，目标字符，替换字符）
substr（字段，开始数字，结束数字）也可以用负
数表示从后面截取
length（）
2，数字
round（数字，保留的位数字）四舍五入
trunc（数字，保留的数字）不对数字进行四舍五入 主要是记不住单词名字
mod（）求余数
3，日期
日期+数字 该日期后的数字天数
日期-数字 该日期前的数字天数
日期-日期 两日期间的天数（大日期-小日期）？
last_day(日期) 该月最后一天
netx_day(日期，星期x) 求出下一个指定星期x的日期 ？
select next_day(sysdate,'星期三') from dual;

months_between(日期，日期) 两日期间的月份？
求出每个雇员到今天为止雇佣的天数
select ename ,trunc(months_between(sysdate,hiredate)) from emp;

add_months(日期，数字) 日期+数字月数
4，转换
to_char()转换成字符，fmyyyy-mm-dd hh24：mi：ss
（L）999,999,999 加L表示当地的钱
to_date()
to_date（'1987-08-17','yyyy-mm-dd'）
to_number()
5，通用
nvl（含有null字段，0）
decode（字段，‘数字1’，‘名字’，‘数字2’，‘名字2’ ……）想不起来具体的实例
select decode(deptno,'10','十号','20','二十','30','sanshi','40','ywu'),deptno from emp;

限定查询
< ，>， =，<>， between and ，and， or，in，like，
Is null， not
关系运算
< 小于
> 大于
= 等于
<> 不等于
判断查询？
Between and
Between 数值and数值
Between 日期 and 日期 ？
对数字日期也同样适用，
如查在1981年
Select * from emp
Where hiredate between ‘01-1月-1981’ and ’31-12月-1981’;
使用在日期上是不太熟悉
And
Or
限定范围
In
In（数值1，数值2，.数值3……）表示在数值1到数值n的范围中
Not in （数值1，数值2，数值3……）表示不数值1到数值n的范围中
模糊查询
Like
|-_可以替换一个
|-%可以替换0,1……n个

空
Null
Nvl（数字，0） 表示处理null为零
当字段中包含null是不能使用not null,否则数包漏洞

创建表
Create table 表名（字段1 类型 [default ‘默认名称’],
字段2 类型 [default ‘默认名称’],
……
）
表的复制
Create table 表名称 as （子查询）
复制带数据的
相应子查询为 select * from 表名where 条件;
复制只带表结构的
相应子查询为 select* from 表名 where 不成立的条件（如where 1=2）；
只是oracle 数据库支持的操作！
表的重命名
Rename 旧表名称to 新表名称： 没有 table
删除表
Drop table 表名；
Flashback （闪回技术）这样删除表并没有删除干净，因为每个用户都有一个回收站
查看回收站; show recyclebin;
还原删除表：falshback table 原表名称 to before drop； 对，主要记不住语法
删除回收站中的表; purge table 表名称；？同样记住不住语法
清空回收站; purge recyclebin;
右上可知删除一个表的方法为： drop table 表名称 purge；

表的截断
删除表中一个字段的数据，还可以使用rollback还原回来，那是因为删除的字段并没有真正提交给后台数据库，只是暂时放到缓存中，截断表就是用来清空缓存，释放空间
Truncate table 表名称； √
修改表
增加：Alter table 表名称 add（字段1 类型 [default ‘默认名称’]，
字段2 类型 [default ‘默认名称’，]
……
）
修改; alter table 表名称 modify (字段1 类型 [default ‘默认名称’]，
字段1 类型 [default ‘默认名称’]
……)
增加和修改表结构不一样
一个仅仅是增加字段，
alter table 表名称 add/modify（字段1 类型 [default ‘默认名称，
……）
一个是对表结构的修改
Alter table 表名称 add/drop constraint 约束名称 约束类型 （字段）
例如：增加一个主键约束
Alter table meber add constraint pk_mid primary keyi(mid);
这里的约束名称 位UK/CK/NK/PK/FK_字段名
约束类型为：unique/check/foreig key/primary key ( 字段名 ) 这个还是记不清楚啊
约束
NK
Not null
Create table 表名称（字段1 类型 not null
）
？Not null 与default 的顺序
Not null 与 default 不能同时出现，否者出错 记住
顺序没有记住
UK
唯一约束 ;Unique

Create table 表名称（字段1 类型 [default ‘默认名称’] unique，
字段2 类型 [default ‘默认名称’，]
Constraint uk_字段 unique(字段)

已经记住unique 这个英文字母
……
）
如果约束直接放到 字段后则不用加字段，放到最后的话要在约束中加字段
CK
检查约束;cherk
Create table 表名 (字段1 类型 [default ‘默认名称’]，
字段2 类型 [default ‘默认名称’，]
Constraint ck_字段 cherk（字段 in （‘数字1’，‘数字2’，‘数字n’））
……
)
PK
主键约束;primary key
Create table 表名（字段1 类型 [default ‘默认名称’]，
字段2 类型 [default ‘默认名称’，]
Constraint pk_字段 primary key (字段)
……
）
FK
外键约束：forign key

Create table 表名(字段1 类型 [default ‘默认名称’]，
字段2 类型 [default ‘默认名称’，]
Constraint fk_字段 forign key（字段）references 父表名（字段）
……
)
删除主表之前必须要先删除子表
Drop table 子表名；
Drop table 父表名;
加入级联设置
删除父表也删除子表
Create table 表名（字段1 类型 not null [default ‘默认名称’]，
字段1 类型 not null [default ‘默认名称’]，

……
Constraint fk_字段 forign key (字段) references 父表（字段） on delete cascade
）

删除父表设置子表相关字段为 null
Create table 表名（字段1 类型 not null [default ‘默认名称’]，
字段1 类型 not null [default ‘默认名称’]，

……
Constraint fk_字段 forign key（字段） references 父表名（字段）on delete set null
）
快速删除字段
Drop table tablename cascade constraint purge： 主要是删除关联的位置没有记清楚
约束条件的修改 ？ 这个还是没有记住太清楚
Create table 表名称 ( 字段 类型 [not null/default ‘默认名称’],
字段2 类型2 [not null/default ‘默认名称’]
……
Constraint fk_bname Forign key(字段) references 表名(字段) on delete cascade/set null)
Drop table bname coscade constraint purge;
Alter table 表名add constraint 约束名称 ， 约束类型（字段）
Alter table表名 drop constraint 约束名称

这与修改表结构不一样，修改约束是修改CK
UK PK FK NK的约束
而修改表结构是修改创建表示的 数据类型，字段名，和默认值
范例 create table test2 (
mid number,
name varchar2(10),
age number(3));
增加主键约束
Alter table test2 add constraint pk_mid primary key(mid);
增加检查约束
Alter table test2 add constraint ck_age check(age between 0 and 150);

insert into test2 (mid,age,name) values(2,'223','aa2aa');

SQL> select * from test2;

MID NAME AGE
---------- ---------- ----------
1 aaaa 23
查询约束名
数据字典 user_constraints
User_cons_columms 记不住字母
Owner ：用户名 scott
Table_name:表名 emp
Constraint_name:约束名 pk_empno
Columm_name:字段 empno
其中从user_constraints 中找到owner ,table_name,constraint_name
从user_cons_columms 中可以找到 columms
主要是记住几个表
当让也可以记住一个查询语句
Select owner，constraint_name, table_name, columm_name from user_cons_columms:

序列
创建序列：
Cretae sequence 表名 increment by 增量值 不是increatment
start with 开始值
Maxvalue 数值n/noMaxvalue 数值n
Minvalue 数值n/noMinvalue 数值n
Cycle/nocycle
Cache/nocache
主要记不住语法
查询序列：
nextval下一个序列,Currval 当前序列
Create sequence mycy;
select mycy.nextval from dual;
select mycy.currval from dual;
在使用自动增长序列是，都必须提前建立一个序列学列表，序列和表联合起来使用
create sequence 序列名；

create table 表名 (id number primary key,
name varchar2(10) not null);
insert into 表名 (ID,NAME) values (序列名.nextval,'AAA);
创建一个表从10开始每次增长2；
Drop sequence myseq;
Create sequence myseq increment by 2 start with 10;
Select myseq.nextval from dual;
创建一个表从1、3、5、7、9循环出现
Error:Create sequence myeseq incretment by 2 start with 1 还是记不住单词
Maxvalue 10 minvalue 1 cycle nocache;

Drop sequence myseq;
Create sequence myseq increment by 2 start with 1
Maxvalue 10 minvalue 1 cycle nocache;
select myseq.nextval from dual;
可以实现从1到9不间断的循环
创建一个表，其编号从1到9不停地循环
Create sequence myseq increment by 2 start with 1
Maxvalue 10 minvalue 1 cycle nocache;
create table test (id number not null,
name varchar2(10) not null);
insert into test(id,name) values(myseq.nextval,'AA');

QL> select * from test;

ID NAME
--------- ----------
1 AA
3 AA
5 AA
7 AA
9 AA
1 AA
3 AA
5 AA
7 AA

连接
Union 并相同部分只显示一个[ju:nen]
Union all 并全部
Intersect 相同部分 相交
Minus 不同部分 maines
这个没有记
视图
Create or replayce view 视图名称 as
子查询
With check option/with read only
Or replayce 为创建视图时，如果有重复着不创建，没有在创建
With check option限制的是创建视图时使用的条件，然后更新的创建时的条件
Create or replayce view bname as 子查询
With check option/with read only;
With check option 限制创建条件和修改条件都是同一个时，不能修改创建条件，但是其他数值是可以修改的
With read only 完全限制视图不能被修改
例如;
创建条件部门20
Create or replace view myview as select * from emp where deptno=20;
Update myview set deptno=20；
这样是错误的，创建条件是20部门，更新条件也是部门20
create or replace view myview as select * from emp where deptno=20;
虽然创建条件不能更新，但是其他的条件可以更新
create or replace view myview as select * from emp where sal=9000;
With read only 限值视图中的任何值都不允许更新！
如更新公职为9000将会提示错误
create or replace view myview as select * from emp where sal=9000;
如果视图时统计条件创建的，那么视图就字段数值不可能更改的
同义词
创建同义词表Create [public ] synonym 名称 for 用户.表名；
删除同义词表 drop synonym名称；
记不住单词
索引？

用户权限
Grant 授权
Revoke 撤销
需要登录dba创建用户
Conn sys/sys as sysdba;
Create user 用户名 identified by 密码; 记不住单词
Grant create session to 用户名； 创建回环给用户
Grant create table to 用户名； 创建表给用户
Grant connect , resource to 用户名；把connect， resource角色给用户
修改用户
Alter user 用户名 identified by 新密码；identifided 确认
Aleter user 用户名 password expire； expire期满
Alter user 用户名 account lock/unlock；account把什么认为
Grant select ,delete ,update,insert on 查询的用户.表名 to 创建的用户
回收权限
Revoke select，delete，update ，insert on 查询的用户.表名 from创建的用户
Revoke connect ，resource ，create table，create session from 创建的用户；
删除用户 Drop user 用户名 cascade； cascade 少了个s
上机操作
创建一个用户dog 密码wangwang
Conn sys/sys as sysdba;
create user dog identified by wangwang;
创建一张表myscott，从scott.emp表中的所有信息
create table mydog as selcet * from scott.emp;
Grant
Revoke
Create user 用户名 identified by 密码；
Grant create session to 用户名 ;
Grant create table to 用户名；
Grant conncet ,resource to 用户名；
Alter user用户名 indentified by 新密码；
Alter user 用户名 password expire；
Alter user 用户名 account lock/unlock；
Grant select ，delete，update，insert on 查询的用户.表名 to 用户；
子查询
Select table1.columm, table2.columm from table1 [ cross join table2]
[natural join table2]
[join table2 using(columm_name)]
[join table2 on ( table1.columm.name=table2.columm.name )]
[left|right|full outer join table2 on ( table1.columm.name=table2.columm.name)]
1. 交叉连接
Cross join table2 是产生笛卡尔集，有两个及多个记录是相同的，且都存在查询结果中
2. 自然连接
Natural join table2 是自然连接，如果结果中有两个相同的部分，则合二为一，结果中之存在一个
3. JOIN ...USING
Join table2 using（columm_name）
4. JOIN...ON
Join table2 on（table1.columm_name = table2.columm_name）
例句
Select * from emp join dept on （emp.deptno=detp.deptno）;
5. LEFT|RTGHT|FULL OUTER JOIN table2 on (table1.columm.name = table2.columm.name)
===============================================================
限定查询
复习时间12/9/15
1. 关系运算
一个条件
多个条件用and or链接
范例 1 selec* from emp where sal>=1500 and sal<=3000;
范例2 select * from emp where (job =’CLERK’ or job=’ASLESMAN’) and sal>1200;
2. 范围判断
1）对数值有用Between最小值and 最大值 范围判断
2） 对日期同样适用between‘01-12月-1988’and ‘31-12月-1988’
注意判断日期值的格式为
dd-mm月-yyyy
3. 判断是否为空 is（not）null
空和零是不同的概念 ，
4. 指点范围判断
IN （数值1，数值2，数值3……）
范例 select * from emp where empno in (7369.7566,7799)
注意 在in中有null不影响查询结果
但是在not in中有null则不返回任何结果
5. 模糊查询
Like 子句配合使用的匹配字符
.单个字符 _ 表示1个字符
.多个字符% 表示0.1多个字符
%%表示查询全部记录
数据排序
Order by 子句写在所有SQL语句之后，
排序多个字段
排序两种方式
升序asc 默认的排序方式
降序desc 有大到小
多表查询
1）如果数据库量很大时，一般不用多表查询
2）多表查询一般都要取别名，通过别名.字段方式查询
Sql-1999语法
Columm 字段
Select table1.columm，table2.columm
from table1[]
交叉查询[cross join table2]
自然查询[natural join table2]
Using [join table2 using(字段)]
On [join table2 on(tabl.字段=table2字段 )]
左右链接查询 [Left|right|full outer join table2 on (tabl.字段=table2字段 )]
统计函数及分组统计
语法: select [distinct] 分组字段1[别名1， 分组字段2 别名2……] 统计函数
From 表名称
Where 判断条件
Grop by 分组字段1，分组字段2
Having 分组后的过滤条件 （可以使用统计函数）
Order by 字段 desc|asc；
Count（） 查询表中的数据记录
sum（） 求和
avg（）求平均值
max() 求最大值
min()求最小值
Count（）函数主要功能进行数据的统计，但是在进行数据统计时，如果一张 表中没有数据，也返回记录只不过是0
1） 分组函数可以在没有分组的时候单独使用，可是却不能出现其他的查询字段
分组函数单独使用
Select count(*) from emp;
错误的使用，出现了其他的字段
Select empno，count(*) from emp;

2） 如果现在要进行分组的话，则select 子句之后，只能出现分组的字段和统计函数
正确做法
Select job,count(empno),abg(sal)
From emp
Group by job;
错误的做法
Select deptno，job,count(empno),abg(sal)
From emp
Group by job；

3） 统计函数允许嵌套，但是嵌套之后的分组含所的查询之中不能再出现任何的其他字段
正确做法
Select max（avg（sal））
from emp
Group by deptno ；
错误做法
Select deptno ，max（avg（sal））
from emp
Group by deptno ；

Where和having 区别
Where是在执行group之前进行过滤，即表示全部数据中筛选出部分数据，where 之后不能使用统计函数
Having 是在group by之后对数据再次进行过滤，可以在having 子句中使用统计函数
统计部门平均工资大于1600的人数和部门编号
正确做法，统计函数条件放在having 后， having之后的条件子句可以是统计函数和限定子句
SQL> select deptno ,count(*),avg(sal)
2 from emp
3 group by deptno
4 having avg(sal)> 1600;

DEPTNO COUNT(*) AVG(SAL)
---------- ---------- ----------
20 5 2175
10 3 2916.66667
==================================
SQL> select deptno,count(*),avg(sal)
2 from emp
3 group by deptno
4 having avg(sal)>1600 and deptno =20;

DEPTNO COUNT(*) AVG(SAL)
---------- ---------- ----------
20 5 2175

错误做法统计函数放在where后
SQL> select deptno ,count(*),avg(sal)
2 from emp
3 group by deptno
4 where avg(sal)> 1600;

DEPTNO COUNT(*) AVG(SAL)
---------- --------- ----------
20 5 2175
10 3 2916.66667

子查询
子查询可以出现的位置 select ，from，where
Select 一般都不会出现
From 多行多列，当作一个表使用
Where 多行多列，多行单列，单行多列
1） 多行多列一般不会出现，
2） 当行多列 只有一行中的几个数值都匹配
3） 单行多列倒是很常见
In （子查询） 等同于in （数值1，数值2……）
Any （子查询） 大于any 只要比any中最小的大即可，小于any只要比any中最大值
小即可，等于any与in一样
All （子查询） 大于all必须比最大值大，小于all必须比最小值小
子查询的规定
大部分情况下，如果最终的查询结果中需要出现select子句，但又不能直接使用统计函数的时候，就在自查新中统计信息，即有复杂统计的时候大部分都需要子查询
例如;
Select d.deptno,d.dname,d.loc,temp.count,temp.avg
From dept d,(
Select deptno dno, count(empno) count,avg(sal) avg
From emp
Group by deptno ) temp
Where d.deptno=temp.dno(+);
红色为查询结果要求的select子句
紫色为子查询中的统计函数
事务处理
Rollback 回滚
Commit 提交
数据伪列
Rownum 行号，它是滚动着改变的

SQL> select * from (select rownum m,deptno from emp where rownum<10) r
2 where r.m>5;

M DEPTNO
---------- ----------
6 30
7 10
8 20
9 10
rownum和子查询必须有别名，where必须子查询别名。Rownum别名
Rowid 物理块号 例如AAAL+XAAEAAAAANAAA
四部分组成
数据对象号AAAL+X
相对文件号 AAE
数据块号 AAAAAN
数据行号AAA
Delete from dept where rowid not in(
Select min(rowid) from dept
Group by dname,loc);
按照dname和loc排列得到最小的rowid，并删除不在这些最小的rowid中的数据
索引
两种方式创建
主键约束:如果一张表中的列上存在了主键约束的话自动创建索引
手工创建：在莫一个操作列上指定一个索引
Create index emp_sal_ind on emp (sal);
数据库备份
数据库冷备份
1）控制文件 v$controlfile
2）日志文件 v$logfile
3）数据文件v$datafile v$tablespace
4）核心操作的配置文件（pfile） 通过 show parameter pfile