Oracle表关联查询语句

[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;