Oracle语句总结大全

Oracle总结

--------简单查询--------

基本数据类型：
char(n) 定长字符n字节。
varchar2(n) 变长字符，n字节数。
integer或者int 整数
float 浮点数
date 日期类型
表的创建：
create table student ( stuno number(10), stuname varchar2(10) ,......);
表的结构：
desc student;
表的查询：
基本查询：
select * from student;
字符串连接：
select stuno,'姓名'||stuname from student;
字段别名：
select stu 学号,stuname 姓名 from student;
空值（NULL）；
空值不等于零或空格
去掉重复：
select distinct stuname from student；
表的有限制查询：
where限制查询：
select * from student where stusex = '女';
between...and定位查询：
select * from score where type='期末' and score between 90 and 100;
in列表范围的数据：
select * from score where score in(60,70,80,90);
like进行模糊查询：
select * from student where stuno like '李%';
is判断空值：
select * from student where stusex is NULL;
表的排序：
asc：升序（默认情况就是升序排列）
desc：降序
可对多个字段排序，别名排序

--------连接查询--------

内连接：
select * from student inner join course where student.stuno = course.stuno
外连接：
左连接：
select * from student left outer join course on student.stuno = course.stuno where 其他条件;
右连接：
select * from student right outer join course on student.stuno = course.stuno where 其他条件;
完全外连接：
select * from student full outer join course on student.stuno = course.stuno where 其他条件;
笛卡尔连接：
select * from student,course where student.stuno = course.stuno and 其他条件;
(笛卡尔连接一定条件下等同于内连接)

--------聚合函数--------

count计算：
cout(*) 统计记录数
avg计算平均数：
avg(字段)  统计平均数
sum计算总和:
sum(字段)  统计总和
max，min求最大值和最小值
max(字段)  
min(字段)
group by进行分组：
group by子句一定要紧跟在where子句的后面
出现在select列表中的字段，如果出现的位置不是在聚合函数中，那么必须出现在group by子句中
反过来，使用group by时，在group by子句中出现的字段，可以不出现在查询列表中
having子句对聚合结果进行限制：
select courseno, avg(score) as 平均分 from score group by courseno having avg(score)>= 60 order by 平均分 desc;

--------子查询--------

子查询位置：
在where或者having中使用子查询：
select stuname from student where stuno not in (select stuno from score where courseno='001');
在from中使用子查询：
select coursename,count(stuno) as 及格人数 from (select score.coursename,score.stuno 
from course join score on course.courseno = score.courseno 
group by course.coursename,course.stuno having avg(scourse.score)>=60)
group by coursename;
在select中使用子查询:
select stuno,stuname,(select count(*) from student) as 总数) from student;
使用with和rownum实现子查询：
rownum是oracle特有的，它是对结果集加上一个伪列，必须先有结果集。总是从1开始，不能查直接开始访问大于1
exists关键字：
select * from teacher where not exists (select courseno from course where teano=teacher.teano);

--------单行数据处理函数--------

字符串处理函数：
lower显示小写 select lower(coursename) from course;
upper显示大写 select upper(coursename) from course;
initcap字符串首字母大写
substr截取字符串
instr(x,y)寻找子串，x表示主串，y表示子串
trim去除两端空格
replace函数进行字符串替换
数值处理函数：
round(x,y)进行四舍五入,x是被操作的数，y是小数保留的位数
trunc(x,y)进行截取操作，x是被截取的数值，y表示小数保留的位数
mod(x,y)进行求余，表示x除以y后的余数
日期处理函数：
mouths_between(x,y)函数取得2个日期之间相差的月份
add_mouths(x,y)在一个日期上增加或者减去若干个月,取得日期x增加y个月之后的日期
........
类型装换函数：
to_char(x,y)获得字符串，x表示数据，y表示格式控制符
to-number(x)获得数值，数据x转化为数值
to_date(x,y)获得日期，x表示字符串，y表示格式控制符
其他函数：
nvl(x,y)将空值转化为一个替换值

--------添加、删除和修改数据--------

insert语句插入数据：
insert into 表名（列名） values（列值）;
commit;
delete语句删除数据：
delete from 表名 [where 条件];
truncate删除表中全部数据
update语句修改数据：
update 表名 set 列1=值1 where 条件;
事务：
commit提交数据
rollback回滚事务
savepoint设置保存事务保存点

--------表的创建与约束--------

创建表：
create table 表名（列名 类型）;
varchar2(n),char(n),number(p,q),float,integer或者int,date,long,clob,blob
rename 原名字 to 新名字;
drop 表名;
修改表格结构：
添加列：
alter table 表名（ add 字段 类型）;
删除列：
alter table 表名 drop （字段）；
约束：
permary key 主键约束
not null 非空约束
unique key 唯一性约束
foreign key 外键约束
check 检查约束
主键约束：
创建时指定：
create table student(stuno id primary key);
修改添加：
alter table student add constraint PK_STUDNET permary key (stuno);
stuno定义为主键，约束名是PK_STUDNET
非空约束：
创建时指定：
create table student(stuname varchar2(10) not null);
修改添加：
alter table student modify (stuname not null);
唯一性约束：
创建时指定：
create table student(stuname varchar2(10) unique);
修改添加：
alter table  student add constraint UK_STUNAME unique(stuname);
stuname指定唯一性约束，约束名是UK_STUNAME
外键约束：
创建时指定：
create table student(stuno int primary key);
create table course (couno int primary key ,stuno int references student(stuno));
修改添加：
alter table course add constraint FK_STUNO foreign key(stuno) references student(stuno);
stuno指定外键，约束名是FK_STUNO
check约束：
创建时指定：
create table student(stusex varchar2(10),check(stusex in('男','女');
修改添加：
alter table student add constraint CK_STUSEX (stusex in('男','女'));
为stusex指定check约束

--------索引、视图、同义词、序列--------

索引：
自动创建索引：
表中定义primary key或者unique约束条件时，自动创建对应的索引。
手动创建索引：
create index 索引名称 on 表名 （列名）；
删除索引：
drop index 索引名称（需要拥有drop any index 权限）
视图：
视图是个虚表，与基表数据完全同步，数据一致。
创建视图：
create （or replace） view 视图名称 as 子查询 [with read only];
同义词：
同义词是给数据库中的一个对象指定的别名
创建同义词：
create [public] synonym 同义词名 for 数据库对象名
序列：
创建序列：
create sequence 序列名 start with 初始值 increment by 增量
创建完成后只需要用“序列名.NEXTVAL”来获取下一个值。
（注：序列产生的是数值，插入表格后隐式装换成字符串）

--------PL/SQL编程--------

基本结构：
DECLARE ----可选部分
变量、常量、游标、声明
...
BEGIN ----必要部分
SQL语句和PL/SQL语句构成的执行程序
...
EXCEPTION ----可选部分
程序出现异常时，捕捉异常并处理异常
...
END; ----必要部分
声明变量：
DECLARE
变量名[constant] 数据类型[not null][:=|DEFULT expr];
注：constant声明为常量，默认是变量。
：= 初始化标识符，为变量或者常量赋予初始值
结合SQL使用：
变量声明参考某列的类型：
DECLARE
stuname STUDENT.STUNAME %TYPE
注：stuname类型和studentstuname类型相同，使用“表名.列名%TYPE”表示某列的值
SQL查询结果存入变量：
select 查询列表 into 变量列表 from 表名（连接等） where 条件；
使用记录变量：
例子：DECLARE
TYPE STUTYPE IS RECORD（
stuno STUDENT.STUNO %TYPE，
stuname STUDENT.STUNAME %TYPE
）；
stu STUTYPE；
注：定义了一个变量，包含了一行信息
或者使用%ROWTYPE参照某表的记录类型
PL/SQL判断逻辑：
if 条件 then 代码 [else if] end；
case 表达式 when 值 then 结果 end；
PL/SQL实现循环逻辑：
LOOP
代码；
EXIT[WHEN 条件];
END LOOP;


WHILE 条件
LOOP
代码；
END LOOP;

FOR 变量 IN [REVERSE] 下限..上限
LOOP
代码；
END LOOP;

--------存储过程、函数和包--------

认识存储过程：（没有返回值）
创建存储过程：
create or replace procedure 存储过程名称
AS（或者IS）
变量、常量定义；
BEGIN
代码；
END；
调用存储过程：
BEGIN
存储过程名称（参数列表）；
END;
带输入参数的存储过程：
create or replace procedure 
存储过程名称（参数1 类型1，参数2 类型2，...）
AS（或者IS）
变量、常量定义；
BEGIN
代码；
END；
存储过程内部的返回：
存储过程内部返回通过RETURN语句实现的
带输出参数的存储过程：（必须从存储过程获得一些值）
create or replace procedure 
存储过程名称（参数1 out 类型1，参数2 out 类型2，...）
AS（或者IS）
变量、常量定义；
BEGIN
代码；
END；
认识函数：
存储过程：完成特定任务、独立调用、头部用PROCEDURE说明、没有返回值、不能返回内容、可以使用IN/OUT/IN OUT参数
函数：完成复杂计算、作为表达式的一部分、头部使用FUNCTION说明、必须描述返回值类型、必须包含return语句、只能使用IN参数
创建函数：
create or replace function 函数名称（参数1 类型1，参数2 类型2，...） return 数据类型
AS（或者IS）
变量、常量定义；
BEGIN
代码；
END；
调用函数：
例如：调用FUN_CHANGE_NAME，select fun_change_name（‘李四’） AS 结果 from DUAL;
认识包：
包含有包头和包体
定义包头：
create or replace package 包名
as
变量、常量声明；
函数声明；
过程声明；
end；
定义包体：
create or replace package body 包名
as
函数实际代码；
过程实际代码；
end；

--------触发器--------

使用触发器：
触发器能够对数据库操作、数据定义、系统事件进行检验、监控、记录，从而保证数据的完整性、提高数据库安全性。
简单的触发器：
create or replace trigger 触发器名称
before/after insert/update/delete
on 表名/视图名
for each row
declare
--变量定义；
begin
--代码；
end；
注：before/after表示触发器触发时机
insert/update/delete定义触发器面向的操作
for each row表示此触发器为行级触发器，对表中每一行进行操作。

注意：1）NEW.列名 ---触发器运行时，数据还没有存入数据库中，比如insert语句等等，此时记录的默认值是：OLD和：NEW。OLD表示数据被操作前记录的内容，NEW表示数据操作后记录的内容

 2）RAISE_APPLICATION_ERROR（错误号，错误信息）---触发器发生错误时，需要进行回滚，但是触发器不能显示调用事务控制语句如COMMIT和ROLLBACK，因为这些控制语句应该是在触发器操作之后由用户调用的。此时使用上述语句生成错误信息。
 3）如果触发器有内部错误，创建时不会提醒，但是会在相应的触发器上打叉。
数据操作语言触发器的定义：
INSERT时自动触发的触发器；DELETE时自动触发的触发器；UPDATE是自动触发的触发器。
修改数据之前的before触发器；修改数据之后的after触发器。
行级触发器，对表中每一行操作都会触发这个触发器；语句级触发器，对数据操作的每一条语句都会触发这个触发器。
行级触发器：
在数据操作前对数据进行检验，此时一般使用before触发器。如insert、delete、update
在数据操作后进行级联操作，此时一般使用after触发器。（级联操作易造成数据库混乱，尽量少用）
语句级触发器：
触发器定义中没有for each row此句。
作用：
1.限制数据操作
如周一到周五的8:00~18:00为工作时间，任何人非工作时间不能对成绩操作
create or replace trigger tgr_ope_dco
before insert or update or delete
on score
begin 
if(to_char(SYSDATE,'HH24:MI') not between '08:00' and '18:00')
then 
raise_application_error(-20004,'不能在非工作时间对score表操作');
end if;
end；
2.instead of触发器
数据定义语言触发器：
用于监视数据库用户的一些重要操作，如表的建立、删除等。

--------游标、异常--------

游标：
隐式游标：所有的select语句和DML语句，内在都含有游标
显示游标：有开发人员声明和控制。用于从结果集中取出多行数据，并将多行数据一行一行单独进行处理。
定义游标：
declare
cursor 游标名称
is 
查询语句；
begin
--其他语句；
end；
使用游标：
1）打开游标，执行查询语句，并将结果暂存在游标区域中。
打开游标相当于执行了游标定义的查询语句，激活了游标，并且游标的指针指向了游标中的第一条记录。
open 游标名称
2）取游标中的一行数据
fetch 游标名称
into 变量1，变量2，...;
游标属性：
%ISOPEN，%NOTFOUND，%FOUND，%ROWCOUNT
例如：判断游标CUR是否还能找到记录，可以使用CUR%FOUND来判断。
3）关闭游标
CLOSE 游标名称
注：关闭游标意味着施放游标区域，将占用的游标内存空间回收。下次使用，必须重新打开游标。
用FOR循环简化游标操作
FOR 记录变量名 IN 游标名称 LOOP
代码；
END LOOP;
异常处理：
异常处理部分的语法结构：
EXCEPTION
WHEN 异常1名称 [OR 异常2名称] THEN
处理代码；
[WHEN 异常3名称 [OR 异常4名称] THEN
处理代码；
]
处理特定异常：
预先定义异常名

--------Oracle的安全管理--------

登陆：
conn system/root as sysdba;
住：使用sysdba的身份登录进入数据库时，可以使用任何的用户名/密码进行登录。例如：conn sss/sss as sysdba;
创建用户：
create user 用户名
IDENTIFIED BY 密码 | IDENTIFIED EXTERNALLY
[DEFAULT TABLESPACE 默认表空间]
[TEMPORARY TABLESPACE 临时表空间]
[PROFILE 配置文件名]
[PASSWORD EXPIRE]
[ACCOUNT LOCK|UNLOCK]
其中：IDENTIFIED BY设置密码，IDENTIFIED EXTERNALLY主要用于远程用户
 DEFAULT TABLESPACE设置默认表空间，默认为USER
 TEMPORARY TABLESPACE设置用户临时表空间，默认为TEMP
 PROFILE设置用户的配置文件名
 PASSWORD EXPIRE设置密码即刻失效，创建后必须马上修改密码
 ACCOUNT LOCK|UNLOCK设置用户是否锁定，锁定后必须解锁，才可以使用，默认是为锁定。
赋予用户权限：
GANK 权限名称 [ON][对象]TO 用户名 [WITH 级联选项 OPTION];
创建角色：
CREATE ROLE 角色名 [IDENTIFIED BY 密码|IDENTIFIED EXTERNALLY]；
其中：IDENTIFIED BY设置角色密码，IDENTIFIED EXTERNALLY主要用于远程用户
赋予用户角色：
GRANT 权限名称 [ON][对象] TO 角色；

--------表空间管理和SQL Loader--------

创建表空间：
CREATE TABLESPACE 表空间名
DATAFILE '数据库文件名路径' SIZE 数据文件大小；
修改用户表空间：
ALTER USER 用户名 DEFAULT TABLESPACE 表空间名；
SQL Loader：
SQL Loader是Oracle自带的一个应用程序，可以帮助用户有效地将大量的数据一次性添加到数据库中

--------数据备份与恢复--------

逻辑备份与恢复：
逻辑备份的程序是EXP，逻辑回复的程序是IMP
物理备份与恢复：
.DBF文件是数据库的数据文件
.CTL文件是数据库的控制文件
.LOG文件是数据库的日志文件，在此又称为联机文件