1.SQL基础
数据定义语言DDL：create（创建）、alter（更改）和drop（删除）命令。
数据操纵语言DML：insert（插入）、select（选择）、delete（删除）和update（更新）命令。
1.1.数据定义语言
1.1.1表的创建-create：
语法：create table 表名（列名1 数据类型 [约束]，列名2 数据类型 [约束] ...）;
实例：create table test(id number primary key ,name varchar2(20));
--创建一个名为test 的表，包含两列id （主键）和name
1.1.2表的修改-alter：
alter table 表名 + add(添加列) | modify（修改列的数据类型） | drop（删除列） | rename（重命名）；
实例：
Alter Table test Add job Varchar(20); --增加一列job
Alter Table test Modify job NUMBER; --更改job列的数据类型
Alter Table test Drop (job); --删除job列
Alter Table test Rename Column Name To ename; -- 修改列名
Alter Table test Rename To test1; --修改表名
1.1.3表的删除-drop：
语法：drop table 表名；
实例：
Drop Table test1; --删除表
1.2.数据操纵语言
1.2.1数据插入-insert:
语法：insert into table_name[(column1,column2,......)] values (value1,value2,......);
实例：
Insert Into emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
Values (1111,'JACK','SALESMAN',2222,to_date('2017/10/9','yyyy-mm-dd'),1000,100,20);
--向emp中插入一条数据
1.2.2数据选择-select：
语法：select [ ALL | DISTINCT ] column_name[,expression ... ]
from table1_name [,table2_name, ... ]
[where condition] --where 条件子句
[group by column_name1 [,column_name2] ... ] --分组查询
[order by column_name1 [asc (升序) | desc(降序) ] [,column_name2 ... ] ] ; --排序
实例：
1）where 子句的字符匹配：like, 用于模糊查询
select * From Test003 --选择出test003表中含p_Searchwords传入的关键字的记录
Where 市民姓名 Like '%' || p_Searchwords || '%'
Or 市民电话 Like '%' || p_Searchwords || '%'
Or 案件类型名 Like '%' || p_Searchwords || '%'
Or 来话内容 Like '%' || p_Searchwords || '%'
2）
Select * From emp; --查询emp中所有列
Select job From emp; --查询emp表中指定job列
Select sal*0.8 From emp; --对查询目标列进行计算
Select Distinct job From emp; --消除重复行
Select * From emp Where sal > 3000; --查询员工工资大于3000的员工信息
Select * From emp Where sal Between 3000 And 4500; --查询员工工资大于3000并且小于4500的员工信息
Select * From emp Where deptno In (1,20); --查询1号，20号部门的员工信息
Select * From emp Where deptno = 20 And sal >2000; --查询部门号为20且工资大于2000的员工信息
Select * From emp Where deptno = 20 Or sal >2000; --查询部门号为20或者工资大于2000的员工信息
Select * From emp Order By sal Desc; --查询所有员工信息，并按照工资降序排列
Select deptno,Count(*),Avg(sal) From emp --查询各个部门号，人数，平均工资，并按部门号升序排列
Group By deptno Order By deptno;
Select * From emp Where sal > (Select avg(sal) From emp Where deptno = 20 ); --嵌套查询，查询工资大于20号部门员工的平均工资的所有员工信息
Select a.deptno,a.ename,a.sal,b.avgsal
From emp a,(Select deptno,Avg(sal) avgsal From emp Group By deptno ) b
Where a.deptno = b.deptno Order By a.sal ;
--from子句的子查询，先获取部门号与对应平均工资，在获取部门号，姓名，工资，对应部门的平均工资
Select * From emp Where deptno = 20 --并集操作，查询部门号为20的员工信息和工资大于4000的员工信息，重复记录只保留一次
Union
Select * From emp Where sal > 4000;
Select * From emp Where deptno = 20 --并集操作，查询部门号为20的员工信息和工资大于4000的员工信息，重复记录全部保留
Union All
Select * From emp Where sal > 4000;
Select * From emp Where deptno = 20 --交集操作，获取部门号为20的员工信息并且工资大于4000的员工信息
Intersect
Select * From emp Where sal > 4000;
Select * From emp Where deptno = 20 --差集操作，查询部门号为20的员工信息但去除工资大于4000的员工信息
Minus
Select * From emp Where sal > 4000;
Select job,Min(sal) From emp
Group By job Having Min(sal) >1000; --查询最低工资大于1000的工作
Select job,Max(sal) From emp
Group By job Having Max(sal) >5000; --查询最高工资大于5000的工作
1.2.3数据删除-delete：
语法：delete from table_name [ where condition] ; --删除一条或多条记录
实例：
Delete From emp_copy Where deptno = 20; --删除部门号为20的员工信息
1.2.4数据更新-update：
语法：update table_name set column1 = value1 [,column2 = value2, ... ]
[where condition ]; --条件子句
实例：
Update emp Set sal = sal + 500 Where deptno = 20; --将20号部门的所有员工工资增加500
1.2.5数据合并-merge：
语法：
merge into target_table
using source_table
on ( condition)
when matched then --若匹配，利用源表中的记录更新目标表中的数据(约束条件)
update set column1 = expression1 [,column2 = expression2 ... ] [where_clause]
when not matched then --若不匹配，将源表中的记录插入到目标表中（约束条件）
insert [(column1[,column2 ... ])] values (expression1[,expression2 ... ]) [where_clause]
实例：
--数据合并
Merge Into Test003 a --目标表
Using (Select b.案件记录号,b.市民姓名,b.市民电话,c.案件类型名,b.来话内容
From 投诉业务表 b, 案件分类表20160121 c Where b.案件类型 = c.序号) b --源结果集
On (a.案件记录号 = b.案件记录号) --条件
When Matched Then --条件满足时更新内容
Update Set
a.市民姓名 = b.市民姓名,a.市民电话 = b.市民电话,a.案件类型名 = b.案件类型名,a.来话内容 = b.来话内容
When Not Matched Then --条件不满足时，插入记录
Insert
(a.案件记录号, a.市民姓名, a.市民电话, a.案件类型名, a.来话内容)
Values
(b.案件记录号, b.市民姓名, b.市民电话, b.案件类型名, b.来话内容);
2 SQL内置函数
2.1 数值函数
floor(n) 返回小于或等于n 的最大整数
mod(m,n) 返回m除以n的余数
实例：
Select floor(2.5),floor(-2.5) From dual; --返回小于或等于n的最大整数
Select Mod(5,2) From dual; --返回5除以2的余数
If Mod(p_Totalrecords, p_Pagesize) = 0 Then --对取余后的值判断，若余值等于0，总页数为总记录数除每页大小
p_Totalpages := p_Totalrecords / p_Pagesize;
Else
p_Totalpages := Floor(p_Totalrecords / p_Pagesize) + 1; --若余值不等于零，总页数为使用floor（）函数取整后再加1
End If;
2.2 字符函数
length(char) 计算字符串的长度
concat(char1,char2) 拼接字符串
substr(char ,m [,n]) 用于获取字符串char的子串，m表示起始位置，n表示子串的长度
实例：
Select concat('abc','defg') a From dual; --连接字符串abc和defg
v_Sql := Substr(v_Sql, 1, Length(v_Sql) - 1); --去掉v_sql语句中最后的一位字符
2.3 日期函数
sysdate 返回当前系统的日期时间
实例：
Select Sysdate From dual; --返回系统时间
2.4 转换函数
to_char(date[,fmt]) 将日期date按指定格式转换为varchar2类型的字符串
to_date(char[,fmt]) 将符合特定格式的字符串转换为日期
Select to_char(Sysdate,'yyyy/mm/dd hh:mi:ss') From dual; --将系统时间转化为字符串
Select to_date('2010/3/20','yyyy-mm-dd') From dual; --将字符串转换为日期
2.5 聚集函数
count(*) 返回结果集中记录个数
avg(column) 返回列的平均值
max(column) 返回列的最大值
min(column) 返回列的最小值
sum(column) 返回列的总和
实例：
Select Count(*),Avg(sal),Max(sal),Min(sal),Sum(sal) From emp Where deptno = 20;
--返回20号部门的人数,平均工资，最高工资，最低工资，工资总和
2.6 其它函数
3 PL\SQL基础
3.1基本结构
declare --声明常量，变量，定义的数据类型，游标（可选）
begin --接主程序体
exception --异常处理程序
end; --结束标志
实例：
3.2分界符
+ , - , * , / , = , > , < , ， <= , >= , （ ，） ， ； ， ！=（不等于） ， ：=（赋值） ， ..（范围操作符） ， --（单行注释符） ， || （字符串连接符） ， % （通配符） ，. (从属关系符号) ， /* */ （多行注释符）
3.3数据类型
字符类型--char() , varchar()
数值类型--number
%type 针对与某列数据类型一致（如： v_sal employees.salary%type 定义变量v_sal 的数据类型为employees表中salary的数据类型）
%rowtype 针对某表的行的记录类型一致 （如： v_emp employees%type v_emp为记录类型的变量）
3.4控制结构
if语句：
if condition1 then statements1;
[elsif condition2 then statements2; ]
...
[else else_statements;]
end if;
实例：
--输入员工号，修改员工工资
Declare
v_Empno Emp.Empno%Type; --员工号
v_Deptno Emp.Deptno%Type; --部门号
v_Increment Number; --工资增量
Begin
v_Empno := &empno;
Select Deptno Into v_Deptno From Emp Where Empno = v_Empno; --传入部门号
If v_Deptno = 10 Then v_Increment := 100; --若为10号部门，工资增加100
Elsif v_Deptno = 20 Then v_Increment := 200; --若为20号部门，工资增加200
Elsif v_Deptno = 30 Then v_Increment := 300; --若为30号部门，工资增加300
Else v_Increment := 50; --否则增加50
End If;
Update Emp Set Sal = Sal + v_Increment Where Empno = v_Empno; --修改工资
Commit;
End;
case语句：
case
when condition1 then statements1;
when condition2 then statements2;
...
[else else_statements;]
end case;
实例：
--输入员工号，修改员工工资，若为10号部门，工资增加100，若为20号部门，工资增加200，若为30号部门，工资增加300，否则增加50
Declare
v_Empno Emp.Empno%Type;
v_Deptno Emp.Deptno%Type;
v_Increment Number;
Begin
v_Empno := &Empno;
Select Deptno Into v_Deptno From Emp Where Empno = v_Empno;
Case
When v_Deptno = 10 Then v_Increment := 100;
When v_Deptno = 20 Then v_Increment := 200;
When v_Deptno = 30 Then v_Increment := 300;
Else v_Increment := 50;
End Case;
Update Emp Set Sal = Sal + v_Increment Where Empno = v_Empno;
Commit;
End;
简单循环：
loop
sequence_of_state ments;
exit [ when condition]; --一定要有exit语句，否则进入死循环
end loop;
实例：
--利用简单循环求1-100之间偶数的和
set serverout on; --在命令窗口中先要打开打印功能
Declare
v_Sum Number := 0; --总和
v_Counter Binary_Integer := 1; --计数器
Begin
Loop
If Mod(v_Counter, 2) = 0 Then v_Sum := v_Sum + v_Counter;
End If;
v_Counter := v_Counter + 1;
Exit When v_Counter > 100;
End Loop;
Dbms_Output.Put_Line(v_Sum); --输出答案
End;
while循环：
while condition
loop
sequence_of_state ments;
end loop;
实例：
--利用while循环求1-100之间偶数的和
Declare
v_Sum Number := 0; --总和
v_Counter Binary_Integer := 1; --计数器
Begin
While v_Counter <= 100 Loop
If Mod(v_Counter, 2) = 0 Then v_Sum := v_Sum + v_Counter;
End If;
v_Counter := v_Counter + 1;
End Loop;
Dbms_Output.Put_Line(v_Sum); --输出答案
End;
for循环：
for v_temp in 下界..上界 --v_temp为循环变量
loop
sequence_of_state ments;
end loop;
实例：
--利用for循环求1-100之间偶数的和
Declare
v_Sum Number := 0;
Begin
For v_Counter In 1 .. 100 Loop
If Mod(v_Counter, 2) = 0 Then
v_Sum := v_Sum + v_Counter;
End If;
End Loop;
Dbms_Output.Put_Line(v_Sum);
End;
3.5游标
Type Cur_Query Is Ref Cursor; --定义游标引用
p_Data Out Cur_Query --出参
Open p_Data For select_statement --打开游标变量
3.6异常处理
Res Out Number --定义一个程序运行标志的出参
Res := 1 --成功就返回一个1
Exception
When Others Then
Res := 0; --有异常就返回一个0
3.7包
包说明：包含过程，函数，游标
包体：说明中声明的过程与函数和过程的具体实现代码
3.8触发器
一种特殊类型的存储过程，当特定事件发生后，由系统自动调用
实例：
Create Or Replace Trigger scott.mytrigger --创建一个更新触发器
After Update On scott.Dept --在scott中dept表中的部门编号更新后
For Each Row --对于每一行
Begin
Update scott.emp Set deptno = :New.deptno --更新emp表中原对应的部门标号为新的部门编号
Where deptno = :Old.deptno;
End;
