oracle
p1
日期，字符类型左对齐输出
数字类型右对齐输出
列名大写输出
算术运算符可用在数字和日期类型上
Null值参与计算，结果也是null值
||用于拼接多个列，用于格式化输出
Distinct 可过滤掉重复行
SQL*Plus 可用很多缩写
p2 sort
Like %代表0或多个字符，_代表1个字符
运算符优先级
比较运算符>NOT>AND>OR
优先级越大则先算
Order by必须写在最后
p3 functions
首字母大写
initcap()
concat(‘ga’,’ga’)
连接
Substr(‘String’,1,3)
Str
截取
Instr(‘String’,’r’)
3
找到出现的位置
lpad(sal,10,’*’)
******5000
trunc(45.926,2)
45,92
mod(1600,300)
100
输出当前时间
select sysdate
from dual
NEXT_DAY ('01-SEP-95','FRIDAY’)
'08-SEP-95'
去除空格和前缀0
fm**
序数词输出
**spth
日期格式模板内符号直接写，字符串加引号
英文输出日期
to_char(sysdate,'dd month yy','nls_date_language=american')
数字格式模板
9代表数字位，0强制输出0，L表示当地货币符号，’,’表示千分符
rr取代yy，可输出接近当前年份的选中年份
nvl：null值替换
nvl(comm,0)
nvl(hiredate,’01-JAN-97’)
nvl(job,’No Job Yet’)
decode简化选择流程
decode(trunc(sal/1000,0),
0,0.00,
1,0.09,
…
6,0.44,
0.45) TAX_RATE
|| 多结果拼接成一个结果
p4 multiply table
outer joins
(+)
加在Where 的=子句后，表后，
表示当前表补齐缺失的内容强制显示另一个表所有内容。
在当前条件里，外连接的字段不能使用IN和OR。
查询表结构
select column_name "Name",nullable "Null?",data_type "Type"
from user_tab_cols
where table_name='SALGRADE';
p5 group
count(*)
含重复行和null值行
count(deptno)
不含deptno为null的行
avg(nvl(comm,0)
group函数除了count函数一般不包括null值行，可加入nvl函数将null值行加入计算
group by 内不能使用别名（列别名不能使用，表别名可以使用）
使用group by时，select的列都必须在group by里（除了被group函数包含的），反过来可以不写（但可读性极差）
先按部门group，再按职业group，求和
select deptno,job,sum(sal)
from emp
groupby deptno,job
orderby deptno;
group函数列不能用where来约束，
而要使用having（最好跟在group by后）。
实现
TOTAL 1980 1981 1982 1983
----- ----- ----- ----- -----
14 1 10
2 1
selectcount(*) total,
sum(
decode(to_char(hiredate,'yyyy'),
'1980',1,
0)
) "1980",
sum(
decode(to_char(hiredate,'yyyy'),
'1981',1,
0)
) "1981",
sum(
decode(to_char(hiredate,'yyyy'),
'1982',1,
0)
) "1982",
sum(
decode(to_char(hiredate,'yyyy'),
'1983',1,
0)
) "1983"
from emp;
实现
Job Dept 10
Dept 20 Dept 30 Total
--------- ------- -------- -------- -------
ANALYST 6000 6000
CLERK 1300 1900
950 4150
MANAGER 2450 2975
2850 8275
PRESIDENT 5000 5000
SALESMAN 5600 5600
select job,
sum(
decode(deptno,
10,sal,
0)
) "Dept 10",
sum(
decode(deptno,
20,sal,
0)
) "Dept 20",
sum(
decode(deptno,
30,sal,
0)
) "Dept 30",
sum(nvl(sal,0)) "Total"
from emp
groupby job
orderby job;
p6 subquery
子查询位置：
Where
Having
From
子查询不要使用order by
子查询要在运算符右边
单行运算符：
(>, =, >=, <, <>, <=)
多行运算符
(IN, ANY, ALL)
单行运算符对应单行子查询
多行运算符对应多行子查询
多列子查询？？？
Any 至少一个
<Any 小于最大值
>Any 大于最小值
=Any In
All 每一个
<All 小于最小值
>All 大于最大值
p7 multiply column subquery
多列子查询
成对多列子查询
查询与ordid为605时相同的prodid和qty且要求ordid不为605
Prodid和qty要求一一对应
select *
from item
where (prodid,qty) in
(
select prodid,qty
from item
where ordid=605
)
and ordid <>605;
不成对多列子查询（拆开的多列子查询）
查询与ordid为605时相同的prodid和qty且要求ordid不为605
Prodid和qty不要求一一对应
select *
from item
where prodid in
(
select prodid
from item
where ordid=605
)
and qty in
(
select qty
from item
where ordid=605
)
and ordid <> 605;
使用子查询时
not in等价于 != ALL，not in内不要含null，影响结果，输出为空
in 等价于 any，in内可以含null，但没有作用，不会找到null
null不等于null，null不参与比较，可以用is null或is not null
from内使用子查询
查询大于自己部门平均工资的员工
select a.ename,a.sal,a.deptno,b.salavg
from emp a,
(
select deptno,avg(sal) salavg
from emp
group by deptno
) b
where a.deptno=b.deptno
and a.sal>b.salavg;
子查询要查出含null时利用nvl（两边都要）
子查询时注意外面要排除自身
p8 sql plus format output
Sql*plus内，
变量名前加ampersand(&)，表示&替代变量，数字直接加，
字符和日期还要在外面加单引号
运行时提示输入实际值
&替代变量可放在
Where
Order by
Column
Table name
Entire statement
&&替代变量可以取消输出提示信息直接再次使用替代变量值
Set verify on
输出替代变量提示信息
Accept command
定义一个替代变量
Accept variable [datatype] [format] [prompt text] [hide]
默认为字符变量
&不出现在这,出现在使用处，单引号同理
Define变量当退出sql*plus或执行undefine时消失，
在login.sql内define可以一直存在
Define command
定义一个替代变量并且赋值
Define deptname=北京
Define deptname
两行都要写
Set command
操作当前session环境
- ARRAYSIZE {20 | n}
- COLSEP {_ | text}
- FEEDBACK {6 | n |OFF | ON}
- HEADING {OFF | ON}
- LINESIZE {80 | n}
- LONG {80 | n}
- PAGESIZE {24 | n}
- PAUSE {OFF | ON | text}
- TERMOUT {OFF | ON}
- Show command
验证set command
Format command
- COLUMN [column option]
- TTITLE [text | OFF | ON]
- BTITLE [text | OFF | ON]
- BREAK [ON report_element]
Column command
- Cle[ar] 清除格式
- For[mat] format An设定显示宽度为n
- Hea[ding] text
- Jus[tify]{align}
- COLUMN ename HEADING 'Employee|Name' FORMAT A15
- COLUMN sal JUSTIFY LEFT FORMAT $99,990.00
- COLUMN mgr FORMAT 999999999 NULL 'No manager'
Column ename
显示ename列的设置
Break command
直接on列名即可隐藏重复
BREAK on column[|alias|row] [skip n|dup|page] on .. [on report]
Clear break
去除break设定
Ttitle、btitle command
Ttitle 格式化页头
Btitle 格式化页脚
@/start e:\hmk\oracle\work\test.sql
运行脚本
Sql*plus 无法进入 估计是权限不够
输入最小值最大值
找出emp表中hiredate介于两者之间的员工
accept low prompt 'Please enter the low date range :'
accept high prompt 'Please enter the high date range :'
select ename||', '||job employees,hiredate
from emp
where hiredate between TO_DATE('&low','mm/dd/rr')
and TO_DATE('&high','mm/dd/rr')
column命令在pl/sql内无效
p9 dml transaction
Transaction是dml(manipulation)语句的组合
Insert语句
insertinto dept values('hmk',60);
insertinto dept(name,deptno) values(26,’fe’,'hmk',60);
-符号 在sql*plus另起一行
从子查询获得数据来insert，可以插入多行
不使用values
insertinto managers(id,name,salary,hiredate)
select empno,ename,sal,hiredate
from emp
where job='MANAGER';
update 语句
update emp
set deptno=10
where empno=7782;
从多列子查询获得数据来update
update emp
set (job,deptno)=
(
select job,deptno
from emp
where empno=7499
)
where empno=7698
完整性约束
更新的值必须在父表中存在
Delete语句
delete from dept
where name='aa';
Truncate语句比delete快，属于ddl (definition)
不产生回滚信息，不触发trigger
Truncate table my_employee
Dcl(control）
事务包含
多个dml构成的一致性操作
一个ddl
一个dcl
事务终止条件：
Commit或rollback发布
Ddl或dcl执行
用户正常退出
机器崩溃
显式控制事务可以用commit,savapoint,rollback
隐式控制事务：
自动commit：
Ddl
Dcl
正常退出
自动rollback：
异常终止
系统错误
事务中数据之前状态可以被恢复，
当前用户可以用select查看，其他用户不能查看dml结果，
受影响行被锁定，其他用户不能修改受影响行
读操作：select
写操作：insert,update,delete
一致性读确保对同一个数据
读者不需要等待写者
写者不需要等待读者
写操作时产生回滚的快照，其余读者看到的是快照
排它锁，共享锁
]
多个command 窗口独立，相当于多个用户
p10 ddl
数据库对象
Table
View 逻辑上代表多个表的数据子集
Sequence 序列 生成主键值
Index 提升查询效果
Synonym 同义词 给对象提供备选名
Table column命名规范
字母开头
1-30长度
A-Z,,a-z,0-9,_ , $,#
同一个用户下命名不能与另一个对象重复
不能是保留字
大小写不敏感 eMP和eMp是同一个表
建表时column可加default项
Default值不能是其他列名或伪列，sysdate算函数可以加
Create
createtable hmk_dept
(
deptno number(2),
dname varchar2(14),
loc varchar2(13)
);
用户表和数据字典
数据字典存储数据库信息，sys用户所有
用户通常获得数据字典视图而不是表，视图进行了格式化更好理解
通常包含
用户名
用户权限
数据库对象名
表约束
审计信息
有4种数据字典视图 user_,all_,dba_
常用
User_tables
User_objects
User_catalog 显示表，视图，同义词，序列
数据类型
Varchar2
Char
NUMBER （ precision， scale）precision有效位，scale小数位
Date
Long
Clob
Raw and long raw
Blob
Bfile
Create时利用另一个已有表的结构，并且直接插入数据，类似建子表
createtable dept415
as
select empno,ename,sal*12 annsal,hiredate
from emp
where deptno=30;
alter
add新column
altertable dept415
add (job varchar2(9));
modify 同理
drop table *** 不能回滚（ddl都不能回滚）
rename可以重命名table，view，sequence，synonym
rename dept415 to dept0415;
truncate 清空表，不能回滚，可用delete代替
comment 可以添加备注
comment on table emp
is 'Employee Information';
可在all_tab_comments中查看
ORA-00942: 表或视图不存在
ORA-00955: 名称已由现有对象使用
待解决：drop table if exists 在oracle中怎么写
p11 constraint
Constraints约束
Not null 非空
Unique 非重复
Primary key 主键一个表只有一个，但可以联合主键，包含非空非重复
Foreign key 外键，又称引用完整性约束，匹配父表值，逻辑上
子表中的复合外键必须是表约束，references表明父表和列
Constraint emp_deptno_fk foreign key(deptno) references dept(deptno)
ON DELETE CASCADE 级联删除选项
允许父表中的删除（子表中存在对父表的引用的情况下，子表中相应数据也会被删除）
Check 添加检查限制
可以加多个，与查询条件基本相同，但是以下情况不允许
使用Currval,nectval,level,rownum等伪列
使用sysdate，uid，user,userenv等函数
查询其他行的其他值
列约束直接跟在列名后，表约束起名并写在最后，
Not null没有表约束
例
Deptno number(7,2) （Constraint deptno_pk）not null
（Constraint emp_empno_pk）primary key (empno)
起名可以省略
Alter Add
可以add,drop,enable,disable(validate,nonvalidate)约束，但不能modify
Alter table emp1
add constraint emp1_sal_ck check(salary>0)
alter table emp
add constraint emp_mgr_fk foreign key(mgr) references emp(empno)
alter table emp
disable constraint emp_empno_pk cascade;
cascade表示把相关的约束一起disable了，本例会把上例中的外键也disable
not null比较特别，不用add，drop而是用modify
alter table emp1
modify (salary not null)
查看约束
select constraint_name,constraint_type,
search_condition
from user_constraints
where table_name='EMP';
查看约束关联的列
select constraint_name,column_name
from user_cons_columns
where table_name='EMP';
p12 view
View视图
基于一个表或另一个视图的逻辑表
视图在数据字典中以select语句存储
使用视图目的
限制获取数据库
更轻易地进行复杂的查询
允许数据独立
显示相同数据的不同视图
简单视图
从一个表中获得数据
不包括函数和数据分组
使用dml
复杂视图
从多个表中获得数据
包括函数和数据分组
不总是使用dml
Create view ***
As select ***
后面不能跟order by
create view empvu415
as select empno,ename,job
from emp
where deptno=10;
modify view
create or replace view empvu415
(employee_number,employee_name,job_title)
as select empno,ename,job
from emp
where deptno=10;
复杂视图
create view dept_sum_vu415
(name,minsal,maxsal,avgsal)
as select d.name,min(e.sal),max(e.sal),
avg(e.sal)
from emp e,dept d
where e.deptno=d.deptno
group by d.name;
view中的dml操作
简单视图总可以进行dml操作
复杂视图中
以下情况不能delete
有group函数
有group by
有distince关键字
以下情况不能update
Delete中的情况
表达式定义的列
Rownum伪列
以下情况不能add
Delete和update中的情况
基表中有Not null列，而view中没有包含
Create view时
with check option后缀表示
通过视图进行的dml，必须也能通过该视图看到dml后的结果。
1.对于update,有with check option，要保证update后，数据要被视图查询出来
2.对于delete,有无with check option都一样
3.对于insert,有with check option，要保证insert后，数据要被视图查询出来
4.对于没有where 子句的视图，使用with check option是多余的
With read only后缀表示
不允许dml操作
Drop view不会丢失数据
p13 sequence index synonym
Sequence序列
自动生成唯一数字
共享对象
常用于创建主键值
替代application code
提升获取缓冲区中序列值的效率
创建序列
create sequence dept_deptno
increment by 1
start with 91
maxvalue 100
nocache
nocycle;
查看序列
select *
from user_sequences
where sequence_name='DEPT_DEPTNO';
nextval和currval伪列
nextval返回下一个可用序列值，非重复值（就算是不同用户）
currval 获得当前序列值
nextval要在currval前
以下情况可以使用nextval和currval
Select中的非子查询
Insert中的子查询
Insert中的values
Update中的set
以下情况不能使用nextval和currval
视图中的select
Select中含distinct
Select中含group by，having或order by
Select，delete，update中的子查询
Create和alter中的default
使用序列
insert into dept(deptno,name,loc)
values(dept_deptno.nextval,'MARK',
'SANDIEGO');
以下情况会出现序列缺口
回滚操作
系统崩溃
其他表中使用
更改sequence
alter sequence dept_deptno
increment by 1
maxvalue 999999
nocache
nocycle;
start with项不能通过alter修改，只能drop再create
还有一些其他验证，比如新的maxvalue不能小于currval
删除sequence
Drop sequence ***
Index 索引
Schema对象，通常一个用户一个schema
服务器通过使用指针提升查询速度
独立于表
服务器自动使用和维持
自动生成索引
当定义主键或非重复约束时，一个非重复索引会自动生成
手动生成索引
用户可以生成可重复索引来提升获取行的速度
手动生成索引
create index emp_lname_idx
on employee(last_name);
什么时候要生成索引
列经常在where或join条件中使用
列含大范围的值
列含很多null值
多个列经常在where或join条件中一起使用
表特别大，但大部分查询只查询其中2-4%的数据
什么时候不要生成索引
小表
列不经常在查询条件中出现
大部分查询查询表中多于2-4%的数据
表经常更新
查看索引
User_indexes含索引名和是否可重复
User_ind_columns 含索引名，表名，列名
select ic.index_name,ic.column_name,
ic.column_position col_pos,ix.uniqueness
from user_indexes ix,user_ind_columns ic
where ic.index_name=ix.index_name
and ic.table_name='EMP'
drop index ***
synonym同义词
给对象起别名
create synonym d_sum
for dept_sum_vu
drop synonym ***
p14 user privilege
控制用户访问
数据库安全分为系统安全和数据安全
系统安全
用户名，密码，磁盘空间分配，系统操作
数据安全
数据库对象的访问和使用，用户对对象的操作
Schema
对象的集合，tables，views，sequences等
被一个用户所有，且和用户同名
两大权限，系统权限和对象权限
系统权限
针对用户
表示对表和表空间等有无操作权的权限。一般是SYS用户这种DBA来授权。
DBA: 拥有全部特权，是系统最高权限，只有DBA才可以创建数据库结构。
RESOURCE:拥有Resource权限的用户只可以创建实体，不可以创建数据库结构。
CONNECT:拥有Connect权限的用户只可以登录Oracle，不可以创建实体，不可以创建数据库结构。
数据库访问
Dba(administrator)： create new users
Remove users
Remove tables
Back up tables
create user user415
identified by password;
grant create table,create sequence,create view
to user415;
Role角色，一组相关的命名权限组，可分配给用户，dba创建
create role manager;
grant create table,create view
to manager;
grant manager to blake,clark;
更改密码
alter user user415
identified by lion;
对象权限
针对表或视图
表示对表和视图的非拥有者赋予表和视图的使用权的权限。一般是由表和视图的拥有者来授权。
操作数据库对象
grant update(empno,sal)
on emp
to scott,manager;
with grant option 让被授权用户可以授权给别的用户
to public 将权限给所有用户
查看权限分配情况
Revoke 取消授权
通过with grant option的授权也会被取消
revoke update(empno,sal)
on emp
from scott,manager;
cascade constraints项可以取消授权与外键相关的references权限
浙公网安备 33010602011771号