1、简单查询
1、简单查询语句的语法
select 列名、列名… from 表名
select … from … 它是查询语句的关键词
select 后面跟的是你要从数据库里取的数据
from 后面跟表名，表示你要从哪张表中读取这张数据
如：
--查询emp表中员工的编号、姓名、工作、工资select empno,ename,job,sal from emp;--查询emp表中所有列的信息select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp;如果要查询表中所有列的信息，那么select和from之间的列名可以用 * 来表示。
注意：* 号的执行效率比写列名的执行效率低。（* 在执行SQL语句时，Oracle会去查询数据库中的数据字典，查询这张表都有哪些列，然后查出这张表的所有列的数据）
2、注释
单行注释：–
多行注释：以 /* 开始，以 */ 结尾
3、SQL中的别名
使用别名可以简化查询
使用表名前缀可以提高执行效率
使用表名前缀在多个表中区分相同的咧
别名语法： 对象名 as 别名 或 对象名 别名
如：
select empno as 员工编号,ename 姓名 from emp;注意：给表、列名起别名时不能加as
select e.* from emp e;select e.empno,e.ename from emp e;select emp.empno,emp.ename,emp.job,emp.sal from emp;4、带条件的查询
select 列名,列名… from 表名 where 条件表达式
where 条件关键字，后面跟条件表达式，这个条件一般是和数据库列相关
1、条件表达式： >、=、<=、！=、、=
如：
--查询工资大于1500的员工信息select * from emp where sal>1500;--查询名字叫 SMITH 的员工的信息select * from emp where ename='SMITH';说明：在Oracle数据库中，数字类型直接写就可以，字符串类型的数据要用单引号括起来
--查询不是10号部门的员工的信息select * from emp where deptno!=0;select * from emp where deptno10;2、in 后面跟一个集合或者子查询
Oracle中表示一个简单的集合(元素,元素,元素…)
例如：(1,2,3,4,5) (‘a’,‘b’,‘c’,‘d’)
如：
--查询emp表中工资是800,1600或者3000的员工信息select * from emp where sal in(800,1600,300);not in 和 in 是相反的。select * from emp where sal not in(800,1600,300);3、like 它只用在字符串列，表示对字符串进行模糊匹配。有两个点位符：
%：表示在这个 % 出现的地方可以有0个或者多个字符
_:表示在_出现的地方有且只有一个任意字符
如：
--查询员工姓名以S结尾的员工信息select * from emp where ename like '%S';--查询emp表中员工姓名的第二个字符是L的员工信息select * from emp where ename like '_L%';--查询emp表中员工姓名第三个字符是A的员工信息select * from emp where ename like '__A%';like中的转义字符：
在新版的Oracle中，使用转义字符需要手动去定义：escape ‘’;
转义字符，它会把紧跟在它后面的字符当做一个普通的字符去处理
如：
--查询员工姓名中有_的员工信息select * from emp where ename like '%\_%' escape '\';--查询员工姓名中第三个字符是_的员工信息select * from emp where ename like '__\_%' escape '\';--查询员工姓名中以%开头的员工信息select * from emp where ename like '%a%%' escape 'a';--查询名字中没有S的员工信息select * from emp where ename not like '%S%';4、all、any后面跟一个集合或者子查询
(>all)：表示大于集合中最大的元素 >all(1,2,3,4,5)等价于 >5
( (>any)：表示大于集合中最小的元素 >any(1,2,3,4,5)等价于 >1
( 如：
--查询员工信息，要求员工的信息比以下的值都低 1500、2000、3000select * from emp where sal<all(1500,2000,3000);--查询比以下任意一个(1500,2000,3000)工资高的员工信息select * from emp where sal>any(1500,2000,3000);--查询工资比所有人(1500,2000,3000)都高的员工信息select * from emp where sal>all(1500,2000,3000);5、exists：它不和任意列一起使用，后面跟的是一个子查询（查询语句为select语句）
如果子查询能查出结果，那么这个条件是成立的，查不出结果条件不成立
not exists 和 exists 相反
如：
select * from emp where exists(select * from emp where 1=2);select * from emp where exists(select * from emp);6、条件表达式
and：表示并且，当 and 连接的两个条件同时成立时，总条件才算成立，只要有一个条件不成立，整体不成立。
or：表示或者，当 or 连接的两个条件有一个条件成立，整体条件成立，只有两个条件都不成立时，整体才不成立
between value1 and value2：value1 的值比 value2 的值小，value1 和 value2一般是数字类型，还可以是日期，表示值在 value1 和 value2 之间，它的取值是包含 value1 和 value2 的。
如：
--查询10号部门，工资大于1000的员工信息select * from emp where deptno=10 and sal>1000;--查询10号部门或工资小于2000的员工信息select * from emp where deptno=10 or sal<2000;--查询工资在1000到3000之间的员工信息select * from emp where sal between 1000 and 3000;select * from emp where sal>=1000 and sal<=3000;--查询工资大于1000并且小于3000的员工信息select * from emp where sal>1000 and sal<3000;select * from emp where sal between 1000 and 3000 and sal!=1000 and sal!=3000;7、is null：表示这个值是空时条件成立
is not null：表示值不为空时条件成立
如：
select * from emp where comm is null;select * from emp where comm is not null;5、dual表：它是Oracle内置的一个单行表
如：
select 1 from dual;select 'abc' from dual;select 2*3 from dual;select 1,2,3,4 from dual;2、排序分组
1、排序
select 列名,列名,… from 表名 where 条件表达式 order by 排序列的列名 asc/desc;
其中，asc 表示升序，desc 表示降序,如果是升序排序，asc 可以省略
如：
--查询员工信息，按照员工的工资升序排序select * from emp order by sal asc;select * from sal order by sal;--查询员工信息，按照员工编号降序排序select * from emp order by empno desc;order by 后面可以跟多个排序列。当跟多个排序列时，它的含义是：按照第一排序列（紧跟 order by 关键字的列）排序，如果第一个排序字段的值相同时，它会按照第二个排序字段进行排序。
如：
--查询员工信息，按照部门编号升序排序，如果部门编号相同时，按照工资升序排序select * from emp order by deptno asc,sal asc;--查询员工信息，按照部门编号降序排序，如果部门编号相同，按照员工编号升序排序select * from emp order by deptno desc,empno;--查询员工的编号、姓名、工作、工资、部门编号，按照部门编号升序排序，如果部门相同时，按照工资降序排序select empno,ename,job,sal,deptno from emp order by deptno,sal asc;注意：order by 后面除了可以跟表中的列名外，还可以跟 select 和 from 之间查询结果的序号
select empno,ename,job,sal,deptno from emp order by 5,4 desc;select empno,sal,job,ename,deptno from emp order by 5,2 desc;2、聚合函数（分组函数）
聚合函数是对一组值执行计算，并返回单个值
count(列名|*|常数)：它求记录数（数据条数）
max(列名)：取最大值
min(列名)：取最小值
avg(列名)：取平均值
sum(列名)：求和
stddev(列名):求标准差
如：
--查询员工表中的员工人数select count(empno) from emp;select count(*) from emp;select count(1) from emp;count(*)、count(常数)、count(普通列)执行效率依次增强
distinct：去重关键字，跟在列的最前面
--查询emp表中的部门数select count(distinct deptno) from emp;select empno,distinct deptno from emp; --这个sql语句是错误的select distinct empno,deptno from emp;注意：distinct 后面跟多个列时，判断重复数据，所有列的值完全相同时，它才会认为是重复数据
--查询工资总和select sum(sal) from emp;--查询员工的平均工资select avg(sal) from emp;--查询10号部门的最高工资select max(sal) from emp where deptno=10;--查询最低工资select min(sal) from emp;3、分组
select 列名,列名,... from 表名 where 条件 group by 分组列order by 排序列 asc|desc;group by：分组的关键字，后面跟分组列名，可以是一个分组列，也可以是多个列如：
--查询各个部门的部门编号和部门的平均工资select deptno,avg(sal) from emp group by deptno;select deptno,min(sal),max(sal),avg(sal),sum(sal),count(*) from empgroup by deptno;--查询各个部门的员工人数select deptno,count(empno) from emp group by deptno;--查询不同职位不同部门的员工人数select job,deptno,count(*) from emp group by job,deptno注意：group by 后面跟多个列时，只有当多个列的值同时相等时，它才会分为同一个组
4、分组和聚合函数的使用规则
1、包含在 group by 子句中的列不必包含在 select 列表中
select avg(sal) from emp group by deptno;2、所有包含于 select 列表中，而未包含于聚合函数（也叫组函数）中的列都必须包含于 group by 子句中
select deptno,count(ename) from emp; --错误示范：select deptno,count(ename) from emp group by deptno; --正确示范3、不能在 where 子句中使用组函数，可以在 having 子句中使用组函数
5、having：行已经被分组且使用了组函数，满足 having 子句中条件的分组将被显示
select 列名,列名,… from 表名 where 条件
group by 分组列 having 条件 order by 排序列
having：它是对分组后的数据进行筛选，条件表达式中可以使用聚合函数
--求平均工资大于2000的部门编号和平均工资select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;where 和 having 的异同：
where 后面跟的条件比 having 后面的条件先执行，条件中不允许使用聚合函数
having 条件中可以使用聚合函数，一般 having 和 group by 连用。
3、集合
每一个SQL查询的结果就是一个集合
1、并集：取两个集合中所有元素
union all：表示取两个集合的并集，不删除重复元素
union：取两个集合的并集，删除重复的元素
如：
--查询员工工资大于1000或小于3000的员工信息select * from emp where sal>1000union allselect * from emp where sal<3000select * from emp where sal>1000unionselect * from emp where sal<3000union all 的执行效率比 union 的高，union在执行的时候先进行 union all ，把两个集合合并，之后对合并后的数据进行排序，去除重复元素。
2、交集：取两个集合重复部分的元素
intersect：表示交集
如：
--查询工资在1000到3000之间的员工信息select * from emp where sal>1000 and sal<3000;select * from emp where sal>1000intersectselect * from emp where sal<3000;3、差集：第一个集合的所有元素减去两个集合重叠部分的元素
如：
select * from emp where sal>1000minusselect * from emp where sal<3000;等价于select * from emp where sal>=3000;select * from emp where sal<3000minus select * from emp where sal>1000;等价于select * from emp where sal<=1000;4、集合运算：关键字前后的两个SQL表示的结果集需要完全相同
如：
select ename,job from empunion allselect ename,job from emp;4、联合查询
联合查询分为交叉连接（笛卡尔积连接）、内连接、自连接、外连接（全连接、左外连接、右外连接）、自然连接
1、交叉连接（笛卡尔积连接）
将会返回被连接的两个表的笛卡尔积，返回结果的行数等于两个表行数的乘积
语法：
select 列名,列名,…
from a表 cross join b表
如：
select e.*,d.deptno,d.dname,d.loc from emp e cross join dept d;select e.*,d.deptno,d.dname,d.loc from emp e,dept d;2、内连接
合并具有同一列的两个以上的表的行，结果集中不包含一个表与另一个表不匹配的行
select 列名,列名,… from a表 inner join b表 on 连接条件
inner join 中的 inner 可以省略不写
如：
--查询员工信息及他的部门信息select e.*,d.* from emp e inner join dept d on e.deptno=d.deptno;select e.*,d.* from emp e join dept d on e.deptno=d.deptno;select e.*,d.* from emp e,dept d where e.deptno=d.deptno;3、自连接
自连接是一种特殊的内连接，内连接一般是两个表的连接，自连接将一个表和它自己进行连接
如：
--查询员工信息，并显示它的上级姓名select e1.*,e2.ename 上级姓名 from emp e1 join emp e2 on e1.mgr=e2.empno;select e1.*,e2.ename 上级姓名 from emp e1,emp e2 where e1.mgr=e2.empno;4、不等连接
不等连接的连接条件是不等条件（大于、小于、不等于）
如：
-- 查询员工信息并显示员工的工资等级select e.*,s.grade,s.losal,s.hisal from emp e join salgrade s one.sal between s.losal and s.hisal;5、全连接
全连接会查出两个表的所有数据
1、如果两个表中的数据都符合连接条件，那么它会河内连接一样，将符合条件的
数据连接为一条记录
2、如果第一张表中的一条数据在第二张表中没有找到和它对应的记录（第二张表
没有和第一张表这条数据符合连接条件），那么它同样会显示第一张表的这条记录，
同时它对应的第二张表数据的位置会显示为空
3、同理，如果第二张表的一条记录，在第一张表中没有找到符合连接条件的数据
那么同样会显示，在显示第一张表数据的位置显示为空。
语法：
select 列名,列名,…
from 表A full outer join 表B on 连接条件
其中 full outer join 中的 join 可以省略
如：
--查询所有部门的员工信息select e.*,d.* from emp e full outer join dept d on e.deptno=d.deptno;6、左外连接
两张表（或多张表）在连接过程中除了返回满足连接条件的行以外，还返回左表中不满足条件的行，这种连接成为左外连接。没有匹配的行时，结果集中相应的列为空（null）。
select 列名,列名,… from 表A
left outer join 表B on 连接条件
left outer join 中的 outer 可以省略
如：
--查询所有部门信息以及各部门下的员工信息select d.*,e.* from dept d left join emp e on d.deptno=e.deptno;select d.*,e.* from dept d,emp e where d.deptno=e.deptno(+);注意：用（+）表示左连接或者右连接。如果是左连接，那么符号加在右表的关系列上，如果是右连接，那么符号加在左表的关系列上
7、右外连接
两张表（或多张表）在连接过程中除了返回满足连接条件的行以外，还返回右表中不满足条件的行，这种连接成为右外连接。没有匹配的行时，结果集中相应的列为空（null）。
select 列名,列名,… from 左表
right outer join 右表 on 连接条件
right outer join 中的 outer 可以省略
如：
--查询员工信息，并显示它的部门信息select d.*,e.* from dept d right outer join emp e on d.deptno=e.deptno;select d.*,e.* from dept d,emp e where d.deptno(+)=e.deptno;8、自然连接
自然连接是在两张表中寻找那些数据类型和列名都相同的字段，然后自动地将它们连接起来，并返回所有符合条件的结果。
select 列名,列名,…
from 表A natural join 表B
或：
select 列名,列名,…
from 表A natural join 表B
using(关系列)
如：
select * from emp natural join dept; select * from emp join dept using(deptno);5、子查询
子查询指一个查询嵌套在另一个查询语句内部的查询
1、from 后面的子查询
--查询员工的姓名和他上级的姓名select ename,mnamefrom (select e.*,d.empno,d.ename mname,d.job,d.sal,d.deptno from emp e,emp d)where e.mgr=d.empno(+)2、where 后面的子查询
1、单行子查询：子查询的SQL语句只查出一条记录
--查询工资大于10号部门平均工资的员工信息select * from emp where sal>(select avg(sal) from emp where deptno=10);2、多行子查询：子查询的SQL语句查出若干条记录
in、not in、exists、not exists、all、any
--查询工资比20号部门所有人工资都高的员工信息select * from emp where sal>all(select sal from emp where deptno=20);3、exists、not exists后面的子查询
exists 后面跟子查询，如果子查询能查出数据，那么条件是真的，如果子查询查不出数据，条件不成立
--查询有员工的部门信息select * from dept d where (select count(*) from emp where deptno=d.deptno)!=0;select * from dept d where exists(select * from emp where deptno=d.deptno);--查询没有员工的部门信息select * from dept d where not exists(select * from emp where deptno=d.deptno);4、having 后面的子查询
如：
--查询各个部门的部门编号和员工人数，要求部门的平均工资大于30号部门的平均工资select deptno,count(*) from emp group by deptnohaving avg(sal)>(select avg(sal) from emp where deptno=30);5、select 和 from 之间的子查询（子查询的结果只能是单行单列）
如：
select empno,ename,(select 1 from dual) from emp;select e.*,(select count(*) from emp where sal>e.sal) from emp e order by 9;6、分页sql
伪列 rownum，它是 Oracle 数据库内置咧，任何数都可以使用，它的作用是显示表中数字的行号
select emp.*,rownum from emp;select t.*,rownum r from(select * from emp order by sal) t;--查询工资最低的5名员工的信息select t.*,rownum r from(select * from emp order by sal) t where rownum<=5;注意：如果给伪列加条件，小于一个值是可以的，不能让它大于某个值
--伪列不能直接大于一个值，如下写法是错误的select t.*,rownum r from(select * from emp order by sal) t where rownum>=2;--伪列不能直接等于一个不等于1的值，如下写法是错误的select t.*,rownum r from(select * from emp order by sal) t where rownum=5;--伪列可以直接等于1select t.*,rownum r from(select * from emp order by sal) t where rownum=1;使用 rownum 的分页sql，rownum 伪列的序号值连续，不考虑值相等的情况（值相同序号不相同）
--查询5到10名的员工信息select * from(select t.*,rownum r from(select * from emp order by sal)t)where r between 5 and 10; --宝三层查询row_number()：需要和 over 分析函数联用，排序的序号和 rownum 伪列相同，连续序号，不考虑值相等的情况（值相同序号不相同）
dense_rank()：可以用来做排序，它序号连续，考虑重复数据，如果值相等序号就相同
rank()：可以用来做排序，它序号不连续，考虑重复数据，如果值相等序号相同
over(partition by 分组列 order by 排序列)：over 子句中的分组 partition by 和group by 的分组不同，它不会把数据聚合成一条，在 over 子句中可以省略
如：
select e.*,row_number() over(order by sal desc) r from emp e;select e.*,dense_rank() over(order by sal desc) r from emp e;select e.*,rank() over(order by sal desc) r from emp e;select e.*,row_number() over(partition by deptno order by sal desc) r from emp e;select e.*,dense_rank() over(partition by deptno order by sal desc) r from emp e;select e.*,rank() over(partition by deptno order by sal desc) r from emp e;--使用 row_number()，rank()，dense_rank() 的分页sql只需要一层子查询select * from (select e.*,row_number() over(order by sal) r from emp e) twhere t.r between 5 and 10;7、case when 语句
case
when 条件表达式1 then
值1;
when 条件表达式2 then
值2;
…
else
默认值;
end case;
如果表达式成立，那么整个语句值的表达式为 then 后面的值，如果所有表达式都不成立，会取 else 后的默认值。其中，else 语句可以省略。
--统计男生女生人数select s.*,(select count(*) from student where ssex='男') 男生人数, (select count(*) from student where ssex='女') 女生人数 from student s; select sum(case when ssex='男' then 1 else 0 end) 男生人数,sum(case when ssex='男' then 0 else 1 end) 女生人数,sum(case when ssex='女' then 1 else 0 end) 女生人数 from student s;