1.基本查询
select xxx
from xxx
where xxx
group by xxx
having xxx
order by xxx
2.子查询
select e.*
from t_employee e
where e.employee_id in (select t.employee_id from t_salary t);
--使用exists替换上式中的in
select e.*
from t_employee e
where 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.salary
from t_employee e left join t_salary s on e.employee_id=s.employee_id
order by e.employee_id;
--oracle写法 e.employee_id=s.employee_id(+)带加号的为附表，不带的为主表
select e.employee_id,e.employee_name,s.salary
from t_employee e,t_salary s
where 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 王蒙 5000
EMPLOYEE_ID EMPLOYEE_NAME SALARY
----------- -------------------- ----------
10 王蒙 5000
10 王蒙 5000
右连接 left左边的为附表，join右边的为主表
select e.employee_id,e.employee_name,s.salary
from t_employee e right join t_salary s on e.employee_id=s.employee_id
order by e.employee_id;
--oracle写法 e.employee_id(+)=s.employee_id带加号的为附表，不带的为主表
select e.employee_id,e.employee_name,s.salary
from t_employee e,t_salary s
where 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
9 商乾 4000
9 商乾 4000
9 商乾 4000
10 王蒙 5000
10 王蒙 5000
10 王蒙 5000
18 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 market
start with market_id=14
connect 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 market
start with market_id=14
connect 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 market
start with market_name='中国'
connect by prior market_id=parent_market_id;
--结果
MARKET_ID
----------
5
14
15
16
--中国市场的子节点的客户信息
select * from market_customer
where market_id in(select market_id from market
start 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 market
start 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 全球 0
select market_id,parent_market_id,sys_connect_by_path(market_name,'\')
from market
start 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_path
from market
start with market_name='天津'
connect by prior parent_market_id=market_id);
--结果
\天津\中国\亚洲\全球
6.使用rownum进行分页查询
--使用rownum进行分页查询
select rn,student_id,student_name,student_age
from (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
![【MySQL数据库】数据库索引事务1.索引2.事务[图]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)