1、Check规则
Check (Agebetween15and30 )把年龄限制在15~30岁之间
2、新SQL语法
在调用某一函数时，可以通过“=>”符号来为特定的函数参数指定数据。而在Oracle11g中，在SQL语句中也可以这样的语法，例如：
Select f( x => 6)from dual ;
3、将sequence的值赋给变量
11g之前的赋值方式Select seq_x.next_val intov_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、表空间
createtablespacelq_oradatadatafile'd:\oracle_test\user_data01.dbf'
size32mautoextendonnext32mmaxsize2048mextentmanagementlocaluniformsize16m ;
Ø2、临时表空间
createtablespacelq_oradata_temptempfile
'd:\oracle_test\temp_data01.dbf'
size32mautoextendonnext32mmaxsize2048mextentmanagementlocaluniformsize16m ;
Ø3、创建用户
create user lq identified by lq
default tablespacelq_oradata
temporary tablespace temp;
注：此处的temp为系统临时表空间，也可以自定义临时表空间；
Ø4、给用户授权
grant connect,resource to lq;
Ø5、创建表
createtableit_employees (
employee_idnumber(6)notnullunique,
first_namevarchar2(30) ,
last_namevarchar2(30)notnull,
emailvarchar2(30),
phone_numbervarchar2(15),
job_idvarchar2(10),
salary_numbernumber(8,2),
manager_idnumber(6)
)
添加数据
insertintoit_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 tolq;
B:再次授予查询所有字典表的权限
grant select any
dictionary tolq;
C：如果已经分配以上权限，还是权限不足,则可分配管理员权限
grant dba tolq;
createviewprog_employees
as
selectemployee_id,first_name,last_name,email,phone_number,salary_number,manager_id
fromit_employees
wherejob_id='it_prog'
with check option;
注：可加上with check option;
Ø7、创建索引
聚簇索引：指索引向的顺序与表中记录的物理顺序相一致的索引组织。
用户可以在查询频率最高的列上建立聚簇索引，从而提高查询效率。由于，聚簇索引是将索引和表记录放在一起存储，所以在一个基表上只能创建一个聚簇索引；在建立聚簇索引后，由于更新索引列数据时会导致表中物理顺序的变更，系统代价较高，因此对于经常更新的列不宜建立聚簇索引。
Create [unique] [cluster] index [索引名]
On <表名> (<列名>[<次序>]，<列名>[<次序>]，<列名>[<次序>]……)
其中，unique表示此索引的每一个索引值不能重复，对应唯一的数据记录。Cluster表示要创建的索引为聚簇索引。索引可以建立在对应表的一列或者多列上，如果是多个列，各个列之间需要用逗号分隔。<次序>用于指定索引值的排列次序，ASC表示升序，DESC表示降序，默认为ASC.
Create indexit_lastnameonit_employees(last_name)
描述：执行后会在表it_employees的last_name列上建立一个索引。
而it_employees表中的数据将按照last_name值升序存放。
6、Oracle删除语法Drop
Ø6.1：删除表
Droptable<表名>
删除表时，表中的数据、在该表上建立的索引一并都会被删除。
Ø6.2：删除视图
Drop view <视图名>
Ø6.3：删除索引
Drop index <索引名>
7、Oracle修改语法Alter
Alter table <表名>
[add <新列><数据类型>[完整性约束]]
新增birth_date列
Altertableit_employeesaddbirth_date date;
[Drop<完整性约束>]
删除employee_id字段的unique约束
Altertableit_employeesDrop unique(employee_id);
[Modify <列名> [数据类型]]
将manager_id字段改为8位
Altertableit_employeesmodifymanager_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指定列
Selectcolumn name_1, ……
column name_n
Fromtable_name_1 , table_name_2 .
8.1.3算术表达式
Selectem.first_name,em.last_name,
em.salary_number*(1+0.2) "new_salar y"
fromit_employees em;
查询出员工薪资上调10%之后的结果，为了提高刻度性，可以为列重新制定显示标题"new_salar y"；
注：如何列标题中包含一些特殊字符，例如空格等，则必须使用双引号将列标题扩起来。
8.1.3distinct关键字
Select distinct job_id from it_employees;
Ø8.2：where子句
8.2.1条件表达式
selectem.employee_id, em.first_name,em.last_namefromit_employees emwhereem.first_namelike'B%';
判断first_name以“B”开头的雇员；
8.2.2连接运算and / or
selectem.employee_id, em.first_name,em.last_namefromit_employees em
whereem.phone_number='15321981677'
andem.salary_number >6000;
用and做连接符，电话号码和薪酬为判断条件；
-------------------------------------------------
selectem.employee_id, em.first_name,em.last_namefromit_employees em
whereem.first_name='Britney'
orem.salary_number <6000;
8.2.3 NULL值
首先插入一条记录，此处Email只为NULL
insertintoit_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*fromit_employees emwhereem.emailisnull;
select*fromit_employees emwhereem.emailisnotnull;
Ø8.3：order by子句
selectem.last_name, em.job_id , em.salary_number
fromit_employees em
whereem.salary_number >5000
orderbyem.job_id,em.salary_numberdesc;
order by子句后边可指定多个列名，首先根据第一列排序，当第一列值相同时，再对第二列进行比较排序，以此类推；
Ø8.4：groupby子句
通过Group By进行的查询:
selectem.job_id,avg(em.salary_number),sum(em.salary_number),max(em.salary_number),count(em.job_id)
fromit_employees emgroupbyem.job_id ;
可以在group by后使用rollup或者cube进行汇总，在查询结果中都会附加一条汇总信息，sql如下：
selectem.job_id,avg(em.salary_number),sum(em.salary_number),max(em.salary_number),count(em.job_id)
fromit_employees emgroupbyrollup(em.job_id) ;
Ø8.5：Having子句
selectjob_id ,avg(SALARY_NUMBER) ,sum(SALARY_NUMBER),max(SALARY_NUMBER),count(*)
fromIT_EMPLOYEESgroupbyJOB_IDhavingavg(SALARY_NUMBER) >5500
通常与Group by子句一起使用，在完成对分组统计后，可以使用Having子句对分组的结果做进一步筛选；
Ø8.6：多表连接查询
8.6.1基本形式
将表employees和表departments相连接，从而生成一个笛卡积：
Selectemployee_id, last_name , department_name fromemployees , departments ;
8.6.2条件限定
selectit.employee_id,it.last_name, dm.department_name
fromit_employees it , departments dm
whereit.department_id = dm.department_id;
selectit.employee_id,it.last_name, dm.department_name
fromit_employees it , departments dm
whereit.department_id = dm.department_id
anddm.department_name ='Shipping';
8.6.3表别名
8.6.4 Join连接
Fromjoin_table1join_typejoin_table2
[ON(join_Condition)]
8.6.4.1内连接
select em.employee_id, em.last_name,dep.department_name
from it_employees eminnerjoindepartments dep
on em.department_id = dep.department_id
where em.job_id='it_prog';
8.6.4.2自然连接
Selectem.employee_id, em.first_name, em.last_name, dep.department_name fromit_employees emnatural
joindepartmentsdepwhere dep.department_name=’Sales’
8.6.4.3外连接
左外连接：
selectem.employee_id, em.last_name, dep.department_name
fromit_employees emleftouterjoindepartments dep
onem.department_id = dep.department_id
whereem.job_id='it_prog';
右外连接：
selectem.employee_id, em.last_name, dep.department_name
fromit_employees emrightouterjoindepartments dep
onem.department_id = dep.department_id
wheredep.location_id =1700;
完全外连接：
selectem.employee_id, em.last_name, dep.department_name
fromit_employees emfullouterjoindepartments dep
onem.department_id = dep.department_id
wheredep.location_id =1700or em.job_id=’it_prog’;
8.6.4.4自连接
selectem1.last_name "manager" , em2.last_name "employee"
fromit_employees em1leftjoinit_employees em2
onem1.employee_id = em2.manager_id
orderbyem1.employee_id;
Ø8.7：集合操作
Union (并运算)、UNION ALL、InTerSect(交运算)、Minus (差运算)
8.7.1Union
/*** < unionall>***/形成的结果集中包含有两个结果集中重复的行。
selectit.employee_id, it.last_namefromit_employeesit
whereit.last_namelike'%e'orit.last_namelike'j%'
union
selectem.employee_id , em.last_namefromit_employees em
whereem.last_namelike'j%'orem.last_namelike'%o%';
8.7.2Intersect
Intersect查询结果保留Last_name以j开头的雇员
selectit.employee_id, it.last_namefromit_employeesit
whereit.last_namelike'%e'orit.last_namelike'j%'
intersect
selectem.employee_id , em.last_namefromit_employees em
whereem.last_namelike'j%'orem.last_namelike'%o%';
8.7.3Minus
例：返回Last_name以e结尾的集合
selectit.employee_id, it.last_namefromit_employeesit
whereit.last_namelike'%e'orit.last_namelike'j%'
minus
selectem.employee_id , em.last_namefromit_employees em
whereem.last_namelike'j%'orem.last_namelike'%o%';
Ø8.8：子查询
8.7.1in关键字
selectemployee_id, last_name , department_id
from it_employees
where department_idIn
(
selectdepartment_id fromdepartments
wherelocation_id=1700
)
8.7.2Exists关键字
selectemployee_id, last_name , department_id
from it_employeesem
whereexists
(
select * fromdepartmentsdep
where em.department_id= dep.department_id
andlocation_id=1700
)
8.7.3比较运算符
selectemployee_id , last_name , job_id , SALARY_NUMBER
fromit_employees
whereJOB_ID ='it_prog'
andSALARY_NUMBER >= (selectavg(SALARY_NUMBER)fromit_employeeswherejob_id='it_prog')
9、数据操纵
Ø9.1：Insert
9.1.1一般Insert
Insertinto[user.]table[@db_link] [([column1, column2… culumnx])]
Values([express1],[ express2]……)
insertintojobs (job_id,job_title,min_salary,max_salary)
values('it_test','测试员',3500.00,8000.00);
/***注：以下Desc… insert未测试成功***/
descjobs
insertintojobsvalues('it_dba','管理员',5000.00,15000.00)
9.1.2批量Insert
insertintoemployees(employee_id,first_name,last_name,email,phone_number,job_id,salary_number,manager_id)
selectem.employee_id,
em.first_name,
em.last_name,
em.email,
em.phone_number,
em.job_id,
em.salary_number,
em.manager_id
fromit_employees em , departments dep
whereem.department_id = dep.department_id
anddep.department_name='Shipping';
Ø9.2：Update
updateemployeessetsalary_number = salary_number*1.15
wherejob_id ='it_prog'
updateit_employeessetsalary_number=
(
select avg(salary_number) fromit_employees
where job_id='it_prog'
)
whereemployee_id=11;
Ø9.3：Delete
deletefromit_employeeswhereemployee_id=31;
Ø9.4: Truncate
truncatetableemployees;
10、数据控制
Ø10.1 Grant语句
Grantselect on table it_employees to User1 ;
Grantallprivileges on table it_employees , jobs toUser2 ;
Grant selecton table department to public ;
Grant update(employee_id), select on tableit_employees to User4;
Grant insert on table departmentto user5 with grant option ;
Grant createtab on database db_employees toUser8 ;
Ø10.2Revoke语句
Revokeupdate(employee_id)on tableit_employees from user4 ;
Revokeselect on table departmentfrompublic;
Revokeinsert on table department from User5 ;
11、Oracle常用函数
Ø11.1字符类函数
11.1.1 ASCII()
Selectascii('A')big_a,ascii('a') small_afromdual ;
11.1.2CHR()
Selectchr(65) ,chr(97),chr(100)fromdual ;
11.1.3Concat( c1,c2 )
selectconcat('oracle','11g')fromdual ;
返回的是：
11.1.4initcap(C1)
selectinitcap('oracleuniversal installer')fromdual ;
返回的结果：
11.1.5 instr(c1,[c2,,[j]])
select instr('Moisossoppo','o',3,3) from dual;
返回的结果：
select instr('Moisossoppo','o',-2,3) fromdual ;
返回的结果：
11.1.6length(C1)
selectlength('oracle
11g') lgfromdual ;
结果：
11.1.7 lower(C1)
select*fromjobs;
结果：
selectlower(job_id)fromjobswherelower(job_id)like'it%'
结果：
11.1.8Ltrim(c1,c2)
selectltrim('Moisossoppo','Mois')fromdual ;
结果：
11.1.9replace(c1,c2,[c3])
selectreplace('feelblue','blue','yellow')fromdual ;
结果：
11.1.10substr(c1,,[j])
selectsubstr('Message',1,4)fromdual ;
Result :
Ø11.2数字类函数
Ø11.3日期类函数
Ø11.4转换类函数
Ø11.5聚集类函数
第四章：Oracle PL/SQL语言及编程
Ø4.1 PL/SQL简介
4.1.1pl/sql的基本结构
4.1.2pl/sql注释
A：单行注释
createtableclasses (
class_idnumber(6)primarykey,
departmentchar(3)
)
altertableclassesaddcoursenumber ;
createsequenceseq_class_id
incrementby1--每次增加1
startwith100--从100开始
nomaxvalue--不设置最大值
nocycle--一直累加不循环
cache10;
declare--单行注释声明
v_departmentchar(3);--保存3个字符的变量
--系统代码
v_Coursenumber;--保存课程号的变量
begin
v_department:='tes';
v_Course :=19;
insertintoclasses(class_id,department , course)
values(seq_class_id.nextval,v_department,v_Course);
end;
commit;
B：多行注释
/**
多行注释
**/
declare
v_departmentchar(3);
v_Coursenumber;
begin
v_department :='duo';
v_Course :=99;
insertintoclasses(class_id,department ,course)
values(seq_class_id.nextval,v_department,v_Course);
end
;
commit;
4.1.3pl/sql字符集
A：合法字符集
B：分界符(delimiter)
4.1.4pl/sql数据类型
A：数字类型
B：字符类型
C：日期类型
D：布尔类型
E：type定义的数据类型（rowtype）
例：
/**
pl/sql数据类型使用type定义teacher_record记录变量
**/
typeteacher_recordisRecord--该record定义之后，在以后的使用中就可以定义基于teacher_record的记录变量
(
tidnumber(5)notnull:=0,
namevarchar2(50),
titlevarchar2(50),
sexchar(1)
)
--定义一个teacher_record类型的记录变量
ateacher
teacher_record ;
4.1.5pl/sql变量和常量
A：定义常量
Pass_Scoreconstantinteger:=60;
B：定义变量
C：变量初始化
4.1.6pl/sql语句控制结构
A：选择结构
(1)If语句
(2)Case语句
declare
v_gradevarchar2(20) :='及格';
v_scorevarchar2(50);
begin
v_score:=Casev_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_number1number;
v_number2number;
v_resultvarchar2(7);
begin
ifv_number1isnullorv_number2isnullthen
v_result ='Unknown';
elsifv_number1
v_result ='yes'
elsev_result ='no'
end;
end;
C：循环结构
(1)Loop…exit … end语句
declare
control_valnumber:=0;--初始化control_val值为0
begin
loop--开始循环
dbms_output.put_line(control_val);--打印测试
ifcontrol_val >5then--如果control_val的值大于5则退出循环
exit;
endif;
control_val :=control_val+1;--否则改变control_val的值
endloop;--结束循环
end;
(2)Loop… exitwhen …end语句
declare
control_valnumber:=0;--初始化control_val值为0
begin
loop--开始循环
dbms_output.put_line(control_val);--打印测试
exitwhencontrol_val >6;--如果control_val的值大于6则退出循环
control_val:=control_val+1;
endloop;
end;
(3)While…loop…end;
(4)declare
(5)control_valnumber:=0;
(6)begin
(7)whilecontrol_val <=5--如果变量小于或等于5就循环
(8)loopcontrol_val:=control_val+1;
(9)dbms_output.put_line(control_val);--打印测试
(10)endloop;
(11)end;
(4)for… in … loop …end
这是个预知循环次数的循环控制语句：
declare
control_valnumber:=0;
begin
forcontrol_valin0.. .9loop
dbms_output.put_line(control_val);
null;
endloop;
end;
null为空操作语句，它表示什么也不做，在程序中用来标识此处可以加执行语句，起到一种记号的作用；
(5)GOTO语句示例
4.1.7pl/sql表达式
A：字符表达式
declare
hnvarchar2(20) :='hello';
wnvarchar2(20) :='world!';
rtnvarchar2(20) ;
begin
ifhisnotnullandwisnotnull
thenrt:=h||w;
dbms_output.put_line(rt);
endif;
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_NAMEVARCHAR2(30);--定义四个变量来存放employees表中的内容
LAST_NAMEVARCHAR2(30);
EMAILVARCHAR2(30);
phone_numvarchar2(30);
cursoremployee_curis
selecte.first_name,e.last_name,e.email,e.phone_number
fromemployeese
wheree.employee_id<10;--选出编号大于5的所有雇员
--2,游标的开启
begin
openemployee_cur;
--3,提取游标(fetch语句每执行一次，游标向后移动一行，直到结束；游标只能逐个向后移动，而不能跳跃移动或者向前移动)
fetchemployee_curintoFIRST_NAME,LAST_NAME,EMAIL,phone_num;
--4,关闭游标
loop
exitwhennotemployee_cur%found;--如果游标到尾则结束
ifFIRST_NAME='lq'then
dbms_output.put_line('名称为：lq');
else
dbms_output.put_line('没有合法用户');
endif;
fetchemployee_curintoFIRST_NAME,LAST_NAME,EMAIL,phone_num;
endloop;
closeemployee_cur;
end;
得出的结果为：
注：使用显式游标时，需要注意以下事项：
4.2.3隐式游标
4.2.4游标属性
A：是否找到游标(%found)
B：是否没找到游标(%found)
C：游标行数(%RowCount)
--1,游标的声明
declare
FIRST_NAMEVARCHAR2(30);--定义四个变量来存放employees表中的内容
LAST_NAMEVARCHAR2(30);
EMAILVARCHAR2(30);
phone_numvarchar2(30);
cursoremployee_curis
selecte.first_name,e.last_name,e.email,e.phone_number
fromemployeese
wheree.employee_id<10;--选出编号大于5的所有雇员
--2,游标的开启
begin
openemployee_cur;
--3,提取游标(fetch语句每执行一次，游标向后移动一行，直到结束；游标只能逐个向后移动，而不能跳跃移动或者向前移动)
fetchemployee_curintoFIRST_NAME,LAST_NAME,EMAIL,phone_num;
--4,关闭游标
loop--如果游标到尾则结束(判断游标当前行数，即当前只抽取3条记录)
exitwhennotemployee_cur%foundoremployee_cur%rowcount=3;
ifFIRST_NAME='lq'then
dbms_output.put_line('名称为：lq');
else
dbms_output.put_line('没有合法用户');
endif;
fetchemployee_curintoFIRST_NAME,LAST_NAME,EMAIL,phone_num;
endloop;
closeemployee_cur;
end;
D：游标是否打开（%IsOpen）
E：参数化游标
--敲回车后会弹出输入对话框，输入即可。
--ACCEPT my_tid prompt 'please input the tid';
ACCEPTmy_tidprompt'please input the tid';
declare
my_tid
number:=10;--定义的参数或者直接用以上prompt输入参数
FIRST_NAMEVARCHAR2(30);--定义四个变量来存放employees表中的内容
LAST_NAMEVARCHAR2(30);
EMAILVARCHAR2(30);
phone_numvarchar2(30);
cursoremployee_cur(cursor_idnumber)is--定义游标时带上参数
selecte.first_name,e.last_name,e.email,e.phone_number
fromemployeese
wheree.employee_id
begin
ifemployee_cur%isopenthen
loop
fetchemployee_cur
intoFIRST_NAME,LAST_NAME,EMAIL,phone_num;
exitwhenemployee_cur%notfound;
endloop;
else
openemployee_cur(my_tid);--带上实参数
loop
fetchemployee_cur
intoFIRST_NAME,LAST_NAME,EMAIL,phone_num;
exitwhenemployee_cur%notfound;
endloop;
endif;
closeemployee_cur;
end;
4.2.5游标变量
Oracle 11 G从入门到精通阅读至126页，文档未完待续。
