pl/sql编程

查询

select employee_name from employees

where 子句

select employee_name from employees where employee_age>25

根据2个数据表查询

select e.employee,s.salary from employees e,salary s where e.employee_id=s.employee_id;

该语句的数据源有2个数据表employees和salary

distinct获取唯一记录

select distinct e.employee,s.salary from employees e,salary s where e.employee_id=s.employee_id;

group by 分组

select e.employee_id,e.employee_name,sum(s.salary) total_salary from employees e,salary s where e.employee_id=s.employee_id group by e.employee_id,e.employee_name;

对于数据源按列employee_id和employee_name进行分组

过滤分组-having

select e.employee_id,e.employee_name,sum(s.salary) total_salary from employees e,salary s where e.employee_id=s.employee_id group by e.employee_id,e.employee_name having (sum(s.salary))>10000

筛选工资总额大于10000的记录

排序-Order by

select distinct e.employee,s.salary from employees e,salary s where e.employee_id=s.employee_id order by s.salary desc;

desc表示由高到低降序排列

Order by 和 group by

select e.employee_name,sum(s.salary) total_salary from employees e,salary s where e.employee_id=s.employee_id group by e.employee_name order by total_salary desc

同时存在order by和group by时先执行 group by

order by 与 distinct

根据哪一列分组就distinct哪列，否则会报错

子查询

select * from employees where employee_id in (select employee_id from salary);

建表语句中的子查询

create table tmp_user_objects as select * from user_objects where 1<>1;

子语句select * from user_objects where 1<>1;获得的是一个空结果集；利用改结果集创建数据表时，将创建一个空的数据表

插入语句中的子查询

insert into tmp_user_objects select * from user_objects where object_type='table';

select * from user_objects where object_type=’table’将这个查询的所有记录插入表tmp_user_objects

联合语句

联合语句是指对于多个查询的结果集进行集合操作，运行结果仍然是一个记录集合

求并集（记录唯一）-union

union还是合并2个结果集的所有记录并去重复

select student_id,student_name form a_student union select student_id,student_name from b_student

注意：union运算的2个结果集必须有完全相同的列数，并且各列具有相同的数据类型

求并集-union all

union all和union都是并集运算，但union all不去重复，因此比union效率高，因此一旦确定2个结果集不会有重复数据时，应使用union all

求交集-intersect

select student_name from a_student intersect select student_name from b_student

求差集-minus

minus是集合间的减法运算，返回第一个集合中存在，而第二个集合中不存在的记录

select student_name from a_student minus select student_name from b_student

联合语句的混合运算

在混合运算时这4种运算的优先级是相同的，因此按照资左至右的顺序执行
当然可以用括号进行优先级的调整

select student_name from a_student intersect（select student_name from a_student union all select student_name from b_student）

连接

自然连接-natural join

select employee_id,e.employee_name,s.month,s.salary from employees e natural join salary s

自然连接时不能给公共列指定限定词，比如employee_id（2个表都含有这个列），不是公共列的可以指定限定词，也可以不指定

自然连接实际自动指定了搜寻条件，首先，自然连接列必须同名，另外所有同名列都作为搜寻条件比如where t1.column=t2.column,如果2个表不存在同名列，其效果相当于笛卡尔积

内连接 -inner Join

自然连接强制使用公共列作为搜寻条件，因此不常用，但内连接可以自行制定连接列和连接条件

select employee_id,e.employee_name,s.month,s.salary from employees e inner join salary s on e.employee_id=s.employee_id;

默认情况下Oracle的连接为内连接，因此 inner可以省略，使用Join代替inner Join。多表查询时，尽量使用内连接

外连接

外连接总是以一个数据源为基础，将另外一个数据源与之进行条件匹配。即使条件不匹配，基础数据源中的数据总是出现在结果集中。

select employee_id,e.employee_name,s.month,s.salary from employees e left join salary s on e.employee_id=s.employee_id;

左连接

select employee_id,e.employee_name,s.month,s.salary from employees e right join salary s on e.employee_id=s.employee_id;

右连接

外连接简略写法

select employee_id,e.employee_name,s.month,s.salary from employees e salary s where e.employee_id=s.employee_id（+）;

where e.employee_id=s.employee_id（+）表示salary为附属表employees为基础表

完全连接-full Join

完全连接实际是一个左连接和一个右连接的组合，即先左连接，再右连接，再去重复

select employee_id,e.employee_name,s.month,s.salary from employees e full join salary s on e.employee_id=s.employee_id;

层次化查询

语法：select 列1，列2… from 表名 start with 开始条件 connect by 递归条件

start with 指定查询的起点，即从哪些记录开始查询；connect by指定递归条件，以获得下一条记录

select market_id,market_name from market start with market_name='亚洲' connect by prior market_id=parent_market_id;

start with market_name=’亚洲’指定查询的起点，connect by prior market_id=parent_market_id;前一条记录的market_id等于下一条记录的parent_market_id

connect by 遵循深度优先的搜索策略

层次化查询相关函数

sys_connect_by_path()对起始至当前记录之间的结果进行聚合操作，该操作仅限于串联字符串，语法：

sys_connect_by_path(列明，分隔符)

select market_id,market_name,sys_connect_by_path(market_name,'/') market_path from market start with market_name='北京' connect_by prior parent_market_id=market_id

其中market_path列的值为/北京/中国/亚洲

二进制转十进制

bin_to_num()函数，将二进制数转换为数值型

select bin_to_num(1,0,1) a,bin_to_num(1,0) b from dual

或者

select sum(data) from (select substr('101',rownum,1)* power(2,length('101')-rownum) data from dual connect by rownum <= length('101'))

表dual仅有1条记录x。但是当connect by rownum <= length(‘101’))获取下一条记录时，仍会获得x，直至获得记录总数=3（二进制101的位数）。针对每次获得的记录，Oracle依次计算各位置对应的实际数字（利用2的乘方运算）。最后求和便是最终的结果。

十进制转二进制

十进制转二进制实际是不停的除以2获取余数的过程，最后的余数应该是倒序的。

select replace(reverse(max(sys_connect_by_path(mod(floor(156/power(2,rownum-1)),2),'/'))),'/','') data form dual connect by floor(156/power(2,rownum-1))>1 order by rownum desc

其中connect by floor(156/power(2,rownum-1))>1 用于获取下一条记录。条件是以2为底数、以当前rownum-1为指数的乘方结果仍小于156，然后综合利用sys_connect_by_path()、max()、reverse()函数获得最终的二进制数据