oracle11g总结

1、Check 规则
Check (Age between 15 and 30 ) 把年龄限制在15~30岁之间
2、新SQL语法
在调用某一函数时，可以通过“=>” 符号来为特定的函数参数指定数据。而在Oracle11g中，在SQL 语句中也可以这样的语法，例如：
Select f( x => 6) from dual ;
3、 将sequence 的值赋给变量
11g之前的赋值方式 Select seq_x.next_val into v_x from dual ;
11g 赋值方式 v_x := seq_x.next_val ;

4、 SQL 的功能

4.1 数据定义
Create , Drop , Alter
4.2 数据操纵
Select , insert , update , delete
4.3 数据控制
Grant, Revoke

5、Oracle 创建语法
1、表空间
create tablespace lq_oradata datafile ‘d:\oracle_test\user_data01.dbf’
size 32m autoextend on next 32m maxsize 2048m extent management local uniform size 16m ;
2、临时表空间
create tablespace lq_oradata_temp tempfile
‘d:\oracle_test\temp_data01.dbf’
size 32m autoextend on next 32m maxsize 2048m extent management local uniform size 16m ;
3、创建用户
create user lq identified by lq
default tablespace lq_oradata
temporary tablespace temp;
注：此处的temp为系统临时表空间，也可以自定义临时表空间；
4、给用户授权
grant connect,resource to lq;

5、创建表
create table it_employees (
employee_id number(6) not null unique,
first_name varchar2(30) ,
last_name varchar2(30) not null,
email varchar2(30),
phone_number varchar2(15),
job_id varchar2(10),
salary_number number(8,2),
manager_id number(6)
)

添加数据
insert into it_employees(employee_id,first_name,last_name,email,phone_number,job_id,salary_number,manager_id,birth_date)
values(seq_employee.nextval,’Micheal’,’Joe’,’mj_aui01@188.com’,’13149258943’,’it_prog’,5000.67,seq_employee.nextval,to_date(‘2011-11-01’,’yyyy-mm-dd’));
6、创建视图
给用户分配创建视图的权限
A:首先授予查询所有表的权限
grant select any table to lq;
B: 再次授予查询所有字典表的权限
grant select any dictionary to lq;
C：如果已经分配以上权限，还是权限不足,则可分配管理员权限
grant dba to lq;
create view prog_employees
as
select employee_id,first_name,last_name,email,phone_number,salary_number,manager_id
from it_employees
where job_id=’it_prog’
with check option;
注：可加上with check option;
7、创建索引
聚簇索引：指索引向的顺序与表中记录的物理顺序相一致的索引组织。
用户可以在查询频率最高的列上建立聚簇索引，从而提高查询效率。由于，聚簇索引是将索引和表记录放在一起存储，所以在一个基表上只能创建一个聚簇索引； 在建立聚簇索引后，由于更新索引列数据时会导致表中物理顺序的变更，系统代价较高，因此对于经常更新的列不宜建立聚簇索引。
Create [unique] [cluster] index [索引名]
On <表名> (<列名>[<次序>]，<列名>[<次序>]，<列名>[<次序>]……)
其中，unique 表示此索引的每一个索引值不能重复，对应唯一的数据记录。 Cluster 表示要创建的索引为聚簇索引。 索引可以建立在对应表的一列或者多列上，如果是多个列，各个列之间需要用逗号分隔。<次序>用于指定索引值的排列次序，ASC表示升序，DESC表示降序，默认为ASC.
Create index it_lastname on it_employees(last_name)
描述： 执行后会在表it_employees的 last_name列上建立一个索引。
而it_employees表中的数据将按照last_name值升序存放。

6、Oracle 删除语法 Drop
6.1： 删除表
Drop table <表名>
删除表时，表中的数据、在该表上建立的索引一并都会被删除。
6.2： 删除视图
Drop view <视图名>
6.3： 删除索引
Drop index <索引名>
7、Oracle 修改语法 Alter
Alter table <表名>
[add <新列><数据类型>[完整性约束]]
新增birth_date列
Alter table it_employees add birth_date date;
[Drop<完整性约束>]
删除employee_id 字段的unique 约束
Alter table it_employees Drop unique(employee_id);
[Modify <列名> [数据类型]]
将manager_id字段改为8位
Alter table it_employees modify manager_id number(8)
8、Oracle 查询 select

8.1：简单查询
8.1.1 使用 from 子句指定表
select * from hr.countries ;
select * from hr.countries ,hr.departments ;
8.1.2 使用select 指定 列
Select column name_1, …… column name_n
From table_name_1 , table_name_2 .
8.1.3 算术表达式
Select em.first_name,em.last_name,
em.salary_number*(1+0.2) “new_salar y”
from it_employees em;
查询出员工薪资上调10% 之后的结果，为了提高刻度性，可以为列重新制定显示标题”new_salar y”；
注：如何列标题中包含一些特殊字符，例如空格等，则必须使用双引号将列标题扩起来。
8.1.3 distinct 关键字
Select distinct job_id from it_employees;

8.2：where 子句
8.2.1 条件表达式

select em.employee_id, em.first_name,em.last_name from it_employees em where em.first_name like ‘B%’;
判断first_name 以 “B” 开头的雇员；
8.2.2 连接运算 and / or
select em.employee_id, em.first_name,em.last_name　from it_employees em
where em.phone_number=’15321981677’
and em.salary_number > 6000;

用and做连接符，电话号码和薪酬为判断条件；

select em.employee_id, em.first_name,em.last_name　from it_employees em
where em.first_name=’Britney’
or em.salary_number < 6000;
8.2.3 NULL 值
首先插入一条记录，此处Email只为NULL
insert into it_employees(employee_id,first_name,last_name,email,phone_number,job_id,salary_number,manager_id,birth_date)
values(seq_employee.nextval,’Britney’,’Joe’,NULL,’15321981677’, ‘it_prog’,10000.67,seq_employee.nextval,to_date(‘2011-11-01’,’yyyy-mm-dd’));

查询值为null的语句：
select * from it_employees em where em.email is null ;
select * from it_employees em where em.email is not null ;
8.3：order by 子句
select em.last_name, em.job_id , em.salary_number
from it_employees em
where em.salary_number > 5000
order by em.job_id,em.salary_number desc ;
order by 子句后边可指定多个列名， 首先根据第一列排序，当第一列值相同时，再对第二列进行比较排序，以此类推；
8.4：group by 子句

通过Group By 进行的查询:
select em.job_id,avg(em.salary_number),sum(em.salary_number),max(em.salary_number),count(em.job_id)
from it_employees em group by em.job_id ;

可以在group by 后使用rollup 或者 cube 进行汇总，在查询结果中都会附加一条汇总信息，sql 如下：
select em.job_id,avg(em.salary_number),sum(em.salary_number),max(em.salary_number),count(em.job_id)
from it_employees em group by rollup(em.job_id) ;
8.5：Having 子句
select job_id , avg(SALARY_NUMBER) , sum(SALARY_NUMBER),max(SALARY_NUMBER),count(*)
from IT_EMPLOYEES group by JOB_ID having avg(SALARY_NUMBER) > 5500
通常与Group by 子句一起使用， 在完成对分组统计后， 可以使用Having 子句对分组的结果做进一步筛选 ；
8.6：多表连接查询
8.6.1 基本形式

将表employees和表departments 相连接， 从而生成一个笛卡积：
Select employee_id, last_name , department_name from employees , departments ;
8.6.2 条件限定

select it.employee_id,it.last_name, dm.department_name
    from it_employees it , departments dm 
    where it.department_id = dm.department_id;

select it.employee_id,it.last_name, dm.department_name
from it_employees it , departments dm
where it.department_id = dm.department_id
and dm.department_name =’Shipping’;
8.6.3 表别名

8.6.4 Join 连接
From join_table1 join_type join_table2
[ON(join_Condition)]
8.6.4.1 内连接

select em.employee_id, em.last_name, dep.department_name
from it_employees em inner join departments dep
on em.department_id = dep.department_id
where em.job_id=’it_prog’;

8.6.4.2 自然连接
Select em.employee_id, em.first_name, em.last_name, dep.department_name from it_employees em natural join departments dep where dep.department_name=’Sales’

8.6.4.3 外连接

左外连接：

select em.employee_id, em.last_name, dep.department_name
from it_employees em left outer join departments dep
on em.department_id = dep.department_id
where em.job_id=’it_prog’;

右外连接：

select em.employee_id, em.last_name, dep.department_name
from it_employees em right outer join departments dep
on em.department_id = dep.department_id
where dep.location_id =1700 ;
完全外连接：

select em.employee_id, em.last_name, dep.department_name
from it_employees em full outer join departments dep
on em.department_id = dep.department_id
where dep.location_id =1700 or em.job_id=’it_prog’ ;
8.6.4.4 自连接

select em1.last_name “manager” , em2.last_name “employee”
from it_employees em1 left join it_employees em2
on em1.employee_id = em2.manager_id
order by em1.employee_id;

8.7：集合操作
Union (并运算) 、UNION ALL 、InTerSect(交运算) 、Minus (差运算)

8.7.1 Union
/* < union all > */ 形成的结果集中包含有两个结果集中重复的行 。

select it.employee_id, it.last_name from it_employees it
where it.last_name like ‘%e’ or it.last_name like ‘j%’
union
select em.employee_id , em.last_name from it_employees em
where em.last_name like ‘j%’ or em.last_name like ‘%o%’;
8.7.2 Intersect

Intersect 查询结果保留Last_name 以j开头的雇员
select it.employee_id, it.last_name from it_employees it
where it.last_name like ‘%e’ or it.last_name like ‘j%’
intersect
select em.employee_id , em.last_name from it_employees em
where em.last_name like ‘j%’ or em.last_name like ‘%o%’;
8.7.3 Minus

例： 返回Last_name 以e结尾的集合
select it.employee_id, it.last_name from it_employees it
where it.last_name like ‘%e’ or it.last_name like ‘j%’
minus
select em.employee_id , em.last_name from it_employees em
where em.last_name like ‘j%’ or em.last_name like ‘%o%’;
8.8：子查询
8.7.1 in 关键字
select employee_id, last_name , department_id
from it_employees
where department_id In
(
select department_id from departments
where location_id=1700
)
8.7.2 Exists 关键字
select employee_id, last_name , department_id
from it_employees em
where exists
(
select * from departments dep
where em.department_id = dep.department_id
and location_id=1700
)
8.7.3 比较运算符

  select employee_id , last_name , job_id , SALARY_NUMBER 
     from it_employees
     where JOB_ID = 'it_prog'
     and SALARY_NUMBER >= (select avg(SALARY_NUMBER) from it_employees where job_id='it_prog')

9、数据操纵

9.1：Insert
9.1.1 一般Insert
Insert into [user.]table [@db_link] [([column1, column2… culumnx])]
Values ([express1],[ express2]……)

insert into jobs (job_id,job_title,min_salary,max_salary)
values(‘it_test’,’测试员’,3500.00, 8000.00);
/* 注：以下Desc … insert 未测试成功*/

desc jobs
insert into jobs values(‘it_dba’,’管理员’, 5000.00, 15000.00)
9.1.2 批量Insert

insert into employees (employee_id,first_name,last_name,email,phone_number,job_id,salary_number,manager_id)
select em.employee_id,
em.first_name,
em.last_name,
em.email,
em.phone_number,
em.job_id,
em.salary_number,
em.manager_id
from it_employees em , departments dep
where em.department_id = dep.department_id
and dep.department_name=’Shipping’;
9.2：Update

update employees set salary_number = salary_number*1.15
where job_id = ‘it_prog’

update it_employees set salary_number =
(
select avg(salary_number) from it_employees
where job_id=’it_prog’
)
where employee_id=11;
9.3：Delete

delete from it_employees where employee_id=31;
9.4: Truncate

truncate table employees;
10、数据控制
10.1 Grant 语句

Grant select on table it_employees to User1 ;

Grant all privileges on table it_employees , jobs to User2 ;

Grant select on table department to public ;

Grant update(employee_id), select on table it_employees to User4;

Grant insert on table department to user5 with grant option ;

Grant createtab on database db_employees to User8 ;
10.2 Revoke 语句

Revoke update(employee_id) on table it_employees from user4 ;

Revoke select on table department from public ;

Revoke insert on table department from User5 ;
11、Oracle 常用函数
11.1 字符类函数
11.1.1 ASCII()

Select ascii(‘A’) big_a, ascii(‘a’) small_a from dual ;
11.1.2 CHR()

Select chr(65) ,chr(97), chr(100) from dual ;
11.1.3 Concat( c1, c2 )

select concat (‘oracle’,’11g’) from dual ;
返回的是 ：
11.1.4 initcap(C1)

select initcap(‘oracle universal installer’) from dual ;
返回的结果：
11.1.5 instr(c1,[c2,,[j]])

select instr(‘Moisossoppo’,’o’,3,3) from dual ;
返回的结果：
select instr(‘Moisossoppo’,’o’,-2,3) from dual ;
返回的结果：

11.1.6 length(C1)

select length(‘oracle 11g’) lg from dual ;
结果：
11.1.7 lower(C1)

select * from jobs;
结果：
select lower(job_id) from jobs where lower(job_id) like ‘it%’
结果：
11.1.8 Ltrim(c1,c2)

select ltrim(‘Moisossoppo’,’Mois’) from dual ;
结果：
11.1.9 replace(c1,c2,[c3])

select replace(‘feelblue’,’blue’,’yellow’) from dual ;
结果：
11.1.10 substr(c1,,[j])

select substr(‘Message’,1,4)from dual ;
Result :

11.2 数字类函数

11.3 日期类函数

11.4 转换类函数

11.5 聚集类函数

第四章：Oracle PL/SQL 语言及编程

4.1 PL/SQL 简介
4.1.1 pl/sql 的基本结构

4.1.2 pl/sql 注释
A： 单行注释

create table classes (
class_id number(6) primary key ,
department char(3)
)
alter table classes add course number ;
create sequence seq_class_id
increment by 1 –每次增加 1
start with 100 – 从100开始
nomaxvalue –不设置最大值
nocycle –一直累加不循环
cache 10;

declare – 单行注释 声明
v_department char(3); –保存3个字符的变量
–系统代码
v_Course number; –保存课程号的变量
begin
v_department :=’tes’;
v_Course := 19;
insert into classes(class_id,department , course)
values(seq_class_id.nextval,v_department,v_Course);
end ;

commit ;
B： 多行注释

/**
多行注释
**/

declare
v_department char(3);

  v_Course number;     

begin
v_department :=’duo’;
v_Course := 99;
insert into classes(class_id,department , course)
values(seq_class_id.nextval,v_department,v_Course);
end
;

commit ;

4.1.3 pl/sql 字符集
A： 合法字符集

B： 分界符(delimiter)

4.1.4 pl/sql 数据类型
A： 数字类型

B： 字符类型

C： 日期类型

D： 布尔类型

E：type 定义的数据类型（rowtype）

例：
/**
pl/sql 数据类型 使用type 定义teacher_record 记录变量

**/

type teacher_record is Record –该 record 定义之后， 在以后的使用中就可以定义基于 teacher_record 的记录变量
(
tid number(5) not null :=0 ,
name varchar2(50),
title varchar2(50),
sex char(1)
)

–定义一个teacher_record 类型的记录变量
ateacher teacher_record ;

4.1.5 pl/sql 变量和常量
A： 定义常量

Pass_Score constant integer :=60;
B： 定义变量

C： 变量初始化

4.1.6 pl/sql 语句控制结构

A： 选择结构

(1) If 语句

(2) Case 语句

declare
v_grade varchar2(20) := ‘及格’;
v_score varchar2(50);
begin
v_score:= Case v_grade
when ‘不及格’ then ‘成绩 < 60 ’
when ‘及格’ then ‘60<= 成绩 < 70 ’
when ‘中等’ then ‘70<= 成绩 < 80 ’
when ‘良好’ then ‘80<= 成绩 < 90 ’
when ‘优秀’ then ‘9 0<= 成绩 < 100 ’

else ‘输入有误’
end ;
dbms_output.put_line(v_score);
end;
运行结果如下：

B： NULL 结构

/**
null 结构(添加变量是否为null的判断)
**/
declare
v_number1 number ;
v_number2 number ;
v_result varchar2(7);
begin
if v_number1 is null or v_number2 is null then
v_result = ‘Unknown’;
elsif v_number1 <　v_number2　then
v_result = ‘yes’
else v_result = ‘no’
end;
end;
C： 循环结构

(1) Loop… exit … end 语句
declare
control_val number :=0 ; – 初始化 control_val 值为0
begin
loop –开始循环
dbms_output.put_line(control_val); –打印 测试
if control_val > 5 then –如果control_val 的值 大于 5则退出循环
exit;
end if ;
control_val := control_val+1; – 否则 改变control_val的值
end loop ; –结束循环
end;
(2) Loop… exit when … end 语句

declare
control_val number := 0; – 初始化 control_val 值为0
begin
loop –开始循环
dbms_output.put_line(control_val); –打印测试
exit when control_val > 6; –如果control_val 的值 大于 6则退出循环
control_val:=control_val+1;
end loop;
end;

(3) While…loop…end;
(4)declare
(5)control_val number :=0 ;
(6)begin
(7)while control_val <= 5 –如果变量小于或等于5 就循环
(8) loop control_val:=control_val+1 ;
(9) dbms_output.put_line(control_val); –打印测试
(10) end loop ;
(11)end;
(4) for… in … loop … end
这是个预知循环次数的循环控制语句：
declare
control_val number := 0;
begin
for control_val in 0 .. .9 loop
dbms_output.put_line(control_val);
null;
end loop;
end;
null 为空操作语句， 它表示什么也不做， 在程序中用来标识此处可以加执行语句，起到一种记号的作用；
(5) GOTO 语句示例

4.1.7 pl/sql 表达式

A： 字符表达式
declare
h nvarchar2(20) := ‘hello’ ;
w nvarchar2(20) :=’world!’;
rt nvarchar2(20) ;
begin
if h is not null and w is not null
then rt:=h||w;
dbms_output.put_line(rt);
end if ;
end;
运行结果：
B： 布尔表达式

begin
if ‘Scott’ not in (‘Mike’,’John’,’Mary’)
then dbms_output.put_line(‘false’);
end if ;
end;
运行结果：
4.2 PL/SQL 的游标

4.2.1 基本原理

4.2.2 显示游标
显示游标的处理包括： 声明游标、 打开游标、 提取游标、 关闭游标 4 个步骤 ；其操作过程如下图：

注：声明游标需要在块的声明部分进行， 其他3步骤都在执行部分或异常处理中进行。
A： 声明游标

B： 打开游标
Open <游标名>；

1)检查联偏变量的取值；
2)根据联偏变量的取值，确定活动集；
3)活动集指针指向第一行；

C： 提取游标

D： 关闭游标
Close <游标名> ；
查出employees 表中的所有数据：

执行下边定义的sql:

–1,游标的声明
declare
FIRST_NAME VARCHAR2(30) ; –定义四个变量来存放employees 表中的内容
LAST_NAME VARCHAR2(30) ;
EMAIL VARCHAR2(30) ;
phone_num varchar2(30) ;
cursor employee_cur is
select e.first_name,e.last_name,e.email,e.phone_number
from employees e
where e.employee_id<10; –选出编号大于5 的所有雇员

–2,游标的开启
begin
open employee_cur;

–3,提取游标 (fetch 语句每执行一次， 游标向后移动一行， 直到结束； 游标只能逐个向后移动， 而不能跳跃移动或者向前移动)
fetch employee_cur into FIRST_NAME,LAST_NAME,EMAIL,phone_num ;

–4, 关闭游标
loop
exit when not employee_cur%found; –如果游标到尾则结束
if FIRST_NAME=’lq’ then
dbms_output.put_line(‘名称为： lq’);
else
dbms_output.put_line(‘没有合法用户’);
end if ;
fetch employee_cur into FIRST_NAME,LAST_NAME,EMAIL,phone_num ;
end loop;
close employee_cur;
end ;

得出的结果为：

注： 使用显式游标时， 需要注意以下事项：

4.2.3 隐式游标

4.2.4 游标属性

A： 是否找到游标(%found)

B： 是否没找到游标(%found)

C： 游标行数(%RowCount)
–1,游标的声明
declare
FIRST_NAME VARCHAR2(30) ; –定义四个变量来存放employees 表中的内容
LAST_NAME VARCHAR2(30) ;
EMAIL VARCHAR2(30) ;
phone_num varchar2(30) ;
cursor employee_cur is
select e.first_name,e.last_name,e.email,e.phone_number
from employees e
where e.employee_id<10; –选出编号大于5 的所有雇员
–2,游标的开启
begin
open employee_cur;

–3,提取游标 (fetch 语句每执行一次， 游标向后移动一行， 直到结束； 游标只能逐个向后移动， 而不能跳跃移动或者向前移动)
fetch employee_cur into FIRST_NAME,LAST_NAME,EMAIL,phone_num ;

–4, 关闭游标
loop –如果游标到尾则结束(判断游标当前行数，即当前只抽取3条记录)
exit when not employee_cur%found or employee_cur%rowcount=3;
if FIRST_NAME=’lq’ then
dbms_output.put_line(‘名称为： lq’);
else
dbms_output.put_line(‘没有合法用户’);
end if ;
fetch employee_cur into FIRST_NAME,LAST_NAME,EMAIL,phone_num ;
end loop;
close employee_cur;
end ;
D： 游标是否打开（%IsOpen）

E： 参数化游标
–敲回车后会弹出输入对话框，输入即可。
–ACCEPT my_tid prompt ‘please input the tid’;
ACCEPT my_tid prompt ‘please input the tid’;

declare
my_tid number:=10; –定义的参数 或者 直接用 以上prompt 输入参数
FIRST_NAME VARCHAR2(30) ; –定义四个变量来存放employees 表中的内容
LAST_NAME VARCHAR2(30) ;
EMAIL VARCHAR2(30) ;
phone_num varchar2(30) ;

cursor employee_cur(cursor_id number) is –定义游标时带上参数
select e.first_name, e.last_name, e.email, e.phone_number
from employees e
where e.employee_id < cursor_id;
begin
if employee_cur%isopen then
loop
fetch employee_cur
into FIRST_NAME, LAST_NAME, EMAIL, phone_num;
exit when employee_cur%notfound;
end loop;
else
open employee_cur(my_tid); – 带上实参数
loop
fetch employee_cur
into FIRST_NAME, LAST_NAME, EMAIL, phone_num;
exit when employee_cur%notfound;
end loop;
end if;
close employee_cur;
end;

4.2.5 游标变量

Oracle 11 G 从入门到精通阅读至126页， 文档未完待续。