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