oralce自学（2）

//表关联查询
//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;