oralce自学（2）

[sql] 
//表关联查询  
//1.简单关联查询  
select s.subscriberno,c.customerid from subscribern s,customer c where s.customerkey =c.customerkey and subscriberno = 'cxm';  
//2.Join连接  
//内连接  
select s.subscriberno,c.customerid from subscriber s inner join customer c on s.customerkey = c.customerkey where s.subscrinerno = 'cxm';  
//自然连接  
select s.subscriberno,c.customerid from subscriber s natural join customer c on s.customerkey = c.customerkey where s.subscriberno = 'cxm';  
//外连接  
(1)左外链接  
select s.subscriberno,c.customerid from subscriber s left outer join customer c on s.customerkey = c.customerkey where s.subscriberno = 'cxm';  
(2)右外连接  
select s.subscriberno ,c.customerid from subscriber s rigth outer join customer c on s.customerkey = c.customerkey where s.subscriberno = 'cxm';  
(3)全外连接  
select s.subscriberno ,c.customerid from subscriber s full outer join customer c on s.cusomterkey = c.cusomterkey where s.subscrinerno = 'cxm';  
  
  
//集合操作  
(1)union :可以将第一个查询的结果行和第二个查询的结果行累加起来，并且能够消除重复行  
select subscriberno from subscriber where last_name like 'c%' or last_name like 'b%'  
union   
select subscriberno from subscriber where last_name like 'v%' or last_name like 'n%';  
  
（2）union all : 可以将第一个查询的结果行和第二个查询的结果行累加起来，但是不能排除重复的行数据  
select subscriberno from subscriber where last_name like 'c%' or last_name like 'b%'  
union all  
select subscriberno from subscriber where last_name like 'v%' or last_name like 'n%';  
  
//子查询  
（1）in 关键字  
select * from subscriber where customerkey in (select customerkey from customer where customerid = 'cxm');  
(2）exists 关键字 : 有些情况下，只需要判断是否存在数据，是否满足条件，并不关心数据本身，这时候就可以使用 exists 做子查询   
select * from subscriber where exists (select * from customer where customerid ='cxm');  
(3)比较运算符 : 在使用比较运算符连接子查询时，必须保证子查询的返回结果只包含一个值，否则整个查询语句就会失败  
select * from subscriber where subscriberkey = 'cxm'and salary>=(select avg(salary) from salary_table where salary_id = 'cx');  
  
  
//数据操作  
  
//增  
(1)一般的插入语句  
insert into table_name （column_name1,column_name2,column_name3）values (value1,value2,value3);  
(2)批量插入语句  
insert into table_name  ( column_name1,column_name2,column_name3) Subquery;(subquery为子查询语句);  
比如：  
insert into subscriber ( column_name1,column_name2,column_name3) select column_name1,column_name2,column_name3 from subscriber1;  
这样让我想起了以前的一个面试题，当时面试官的题目是这样的：新建一个表把A表中的数据全部搞进去？  
现在想想这个也不实一个办法；  
step 1 :  
创建表B，其表结构和A表的表结构是一样的。  
create table B(...);  
step 2 ：  
insert into B (...) select ... from A;  
  
//改  
(1)简单更新  
update table_name set column_name = '' where condition ;   
(2)组合查询语句更新  
update table_name set column_name = (select Avg(salary) from salary where salaryid = 'cxm') where condition ;  
比如：  
update subscriber set salary = (select avg(salary) from salary where salaryid = 'cxm') where subscriberid = 'cxm';  
需要注意的是在通过 select 提供 set 值的时候必须保证 select 语句返回的值是单一的。  
  
//删  
delete from table_name where condition ;  
如果要删除表中的所有记录：  
建议使用 truncate ; truncate 的效率比 delete 高，因为他不会产生回滚信息，所以执行 truncate 后不能回滚  
truncate table table_name;  
  
  
//数据控制  
（1）付权限  
grant 权限 on 对象 to 用户 with grant option ;(其中的 with grant option 是否允许用户传播权限）  
比如;  
grant select on table subscriber to user1;//将表的查询权限赋给user1  
grant all privileges on table to user1;//将表的所有的权限赋给user1;  
grant select on table subscriber to public ;//将表的查询权限赋给所有的用  
grant update(column_name),select on table_name to user1;//将表的修改某个字段的，和查询的权限赋给某个用户  
grant createtab on database database_name to user1;//将建表的权限赋给user1;  
  
  
（2）回收权限  
revoke 权限 on 对象 from 用户;  
比如：  
revoke select on table subscibre from user1;