oracle查询(select)

1.基本查询

select xxxfrom xxxwhere xxxgroup by xxxhaving xxxorder by xxx

2.子查询

select e.* from t_employee ewhere e.employee_id in (select t.employee_id from t_salary t);--使用exists替换上式中的inselect e.*from t_employee ewhere exists (select t.employee_id from t_salary s where t.employee_id=e.employee_id);

3.联合语句

union 并集，剔除重复元素（相当于并集后执行了一次distinct）
union all 并集，包含重复元素（因此比union快）
intersect 交集
minus 差集（第一个查询作为被减数，第二个查询作为减数，差集的结果为第一个集合存在而第二个集合不存在的记录）

4.连接

①自然连接:将两个数据源中具有相同列的进行连接
select * from t_employee natural join t_manager;

②内连接

③外连接

左连接 left左边的为主表，join右边的为附表

select e.employee_id,e.employee_name,s.salaryfrom t_employee e left join t_salary s on e.employee_id=s.employee_idorder by e.employee_id;--oracle写法 e.employee_id=s.employee_id(+)带加号的为附表，不带的为主表select e.employee_id,e.employee_name,s.salaryfrom t_employee e,t_salary swhere e.employee_id=s.employee_id(+)order by e.employee_id;--结果EMPLOYEE_ID EMPLOYEE_NAME            SALARY----------- -------------------- ----------          1 金瑞                       8000          1 金瑞                       8000          1 金瑞                       8000          2 钟君                       7000          2 钟君                       7000          2 钟君                       7000          3 王山                       7000          3 王山                       7000          3 王山                       7000          4 刘迪                       7000          4 刘迪                       7000          4 刘迪                       7000          5 钟会                           6 张玉                           7 柳青                           8 胡东                           9 商乾                       4000          9 商乾                       4000          9 商乾                       4000         10 王蒙                       5000EMPLOYEE_ID EMPLOYEE_NAME            SALARY----------- -------------------- ----------         10 王蒙                       5000         10 王蒙                       5000

右连接 left左边的为附表，join右边的为主表

select e.employee_id,e.employee_name,s.salaryfrom t_employee e right join t_salary s on e.employee_id=s.employee_idorder by e.employee_id;--oracle写法 e.employee_id(+)=s.employee_id带加号的为附表，不带的为主表select e.employee_id,e.employee_name,s.salaryfrom t_employee e,t_salary swhere e.employee_id(+)=s.employee_idorder by e.employee_id;--结果EMPLOYEE_ID EMPLOYEE_NAME            SALARY----------- -------------------- ----------          1 金瑞                       8000          1 金瑞                       8000          1 金瑞                       8000          2 钟君                       7000          2 钟君                       7000          2 钟君                       7000          3 王山                       7000          3 王山                       7000          3 王山                       7000          4 刘迪                       7000          4 刘迪                       7000          4 刘迪                       7000          9 商乾                       4000          9 商乾                       4000          9 商乾                       4000         10 王蒙                       5000         10 王蒙                       5000         10 王蒙                       500018 rows selected

性能比较：二者的执行计划和统计信息一样，看不出效率快慢，推荐使用left join可读性高。

完全连接：左外连接和右外连接在一起

5.层次化查询 connect by

--创建market表create table market(       market_id number,       market_name varchar2(10),       parent_market_id number);insert all  into market values(1,'全球',0)  into market values(2,'亚洲',1)  into market values(3,'欧洲',1)  into market values(4,'美洲',1)  into market values(5,'中国',2)  into market values(6,'韩国',2)  into market values(7,'朝鲜',2)  into market values(8,'英国',3)  into market values(9,'德国',3)  into market values(10,'法国',3)  into market values(11,'美国',4)  into market values(12,'墨西哥',4)  into market values(13,'巴西',4)  into market values(14,'北京',5)  into market values(15,'天津',5)  into market values(16,'上海',5)select * from dual;  select * from market;--使用connect by进行层次化查询market_id=14时，即北京的父节点select * from marketstart with market_id=14connect by prior parent_market_id=market_id;--结果 MARKET_ID MARKET_NAME PARENT_MARKET_ID---------- ----------- ----------------        14 北京                       5         5 中国                       2         2 亚洲                       1         1 全球                       0--使用connect by 过滤出market_id=14且洲以下的父节点select * from marketstart with market_id=14connect by market_id=prior parent_market_id and instr(market_name,'洲')=0;--结果 MARKET_ID MARKET_NAME PARENT_MARKET_ID---------- ----------- ----------------        14 北京                       5         5 中国                       2--connect by 使用场景--创建market_customer表create table market_customer(       customer_id number,       customer_name varchar2(50),       customer_address varchar2(10),       market_id number);--向market_customer表插入数据insert all  into market_customer values(1,'Air USA','Waston',11)  into market_customer values(2,'飞卢财经','北京',5)  into market_customer values(3,'晓金公司','北京',14)  into market_customer values(4,'中国五金','天津',15)  into market_customer values(5,'申业公司','上海',16)select * from dual;--使用connect by层次化查询中国市场的market_customer信息--中国市场的子节点select market_id from marketstart with market_name='中国'connect by prior market_id=parent_market_id;--结果 MARKET_ID----------         5        14        15        16--中国市场的子节点的客户信息select * from market_customerwhere market_id in(select market_id from marketstart with market_name='中国'connect by prior market_id=parent_market_id);--结果CUSTOMER_ID CUSTOMER_NAME                                      CUSTOMER_ADDRESS  MARKET_ID----------- -------------------------------------------------- ---------------- ----------          2 飞卢财经                                           北京                      5          3 晓金公司                                           北京                     14          4 中国五金                                           天津                     15          5 申业公司                                           上海                     16--sys_connect_by_path：将connect by查询的结果集的每行记录的列值用字符串串在一行表示，如：select * from marketstart with market_name='天津'connect by prior parent_market_id=market_id;-- 结果MARKET_ID MARKET_NAME PARENT_MARKET_ID---------- ----------- ----------------        15 天津                       5         5 中国                       2         2 亚洲                       1         1 全球                       0select market_id,parent_market_id,sys_connect_by_path(market_name,'\')from marketstart with market_name='天津'connect by prior parent_market_id=market_id;--结果 MARKET_ID PARENT_MARKET_ID SYS_CONNECT_BY_PATH(MARKET_NAM---------- ---------------- --------------------------------------------------------------------------------        15                5 \天津         5                2 \天津\中国         2                1 \天津\中国\亚洲         1                0 \天津\中国\亚洲\全球--只获取所有父节点的字符串select max(max_path) from(select market_id,parent_market_id,sys_connect_by_path(market_name,'\') max_pathfrom marketstart with market_name='天津'connect by prior parent_market_id=market_id);--结果\天津\中国\亚洲\全球

6.使用rownum进行分页查询

--使用rownum进行分页查询select rn,student_id,student_name,student_agefrom (select rownum rn,s.* from (select * from student order by student_name) s where rownum<=10)where rn>5;--结果集        RN STUDENT_ID STUDENT_NAME STUDENT_AGE---------- ---------- ------------ -----------         6          4 赵六                  12         7          6 周八                  14         8          8 胥十                  12