SQL查询、简单查询、排序分组、集合、联合查询、分页SQL

1.简单查询

select 列名,列名,...,列名 from 表名;
select ... from ：它是查询语句的关键字
select后面跟是你要从数据库里取的数据
from后面跟表名，表示你要从哪张中读取这些数据
​
select empno,ename from emp;

单行注释：用 --

多行注释：以/* 开始，以*/结尾

别名语法：对象名 as 别名 （as可省略）

带条件的查询

select 列名,列名,...,列名 from 表名 where 条件表达式;
where:条件关键字，后面跟条件表达式，这个条件一般是和数据库中列相关
--in/not in :集合或者子查询
--like/not like :对字符串进行模糊匹配（%，_）,转义字符 \
--all/any : 集合或者子查询
--exists/not exists :子查询
--is null/is not null
--条件连接符：and,or,between and 
​
select empno,ename,sal from emp where sal>1900;

2.排序分组

排序

select 列名,列名,..,列名 from 表名 where 条件表达式 order by 排序列的列名 asc|desc;
asc|desc: asc表示升序，desc表示降序
--查询员工信息，按照员工的工资升序排序
select * from emp order by sal;
--order by ：后面可以跟多个排序列，跟多个排序列时，按照第一排序列排序，如果第一个排序字段的值相同时，它会按照第二个排序字段进行排序

聚合函数

count(列名|*|常数):它求记录数（数据条数）
max(列名):取最大值
min(列名):取最小值
avg(列名):取平均值
sum(列名):求和
distinct:去重关键字,跟在列的最前面
--distinct后面跟多个列时，判断重复数据，所有列的值完全相同时，它才会认为是重复数据
​
select sno,avg(score) from sc group by sno having avg(score)>60;

分组

group by:分组的关键字，后面跟分组列名，可以是一个分组列，也可以是多个列
--查询各个部门的部门编号和部门的平均工资
select deptno,avg(sal) from emp group by deptno;
--group by 后面跟多个列时，只有当多个列的值同时相等时，它才会分为同一个组;
having:它是对分组后的数据进行筛选 ，条件表达式中可以使用聚合函数
​
--求平均工资大于2000的部门编号和平均工资
select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;
--一般 having 和 group by 联用

3.集合、联合查询

集合：每一个sql查询的结果就是一个集合

集合运算，关键字前后的两个sql表示的结果集要完全相同。

--并集：取两个集合中所有元素
union all:表示取两个集合的并集，不删除重复元素
union ：取两个集合的并集，删除重复的元素
--查询员工工资大于1000小于3000的员工信息
select * from emp where sal>1000 
union all
select * from emp where sal<3000;
--交集：取两个集合重叠部分的元素
intersect:表示交集
--查询工资在1000到3000之间的员工信息
select * from emp where sal>1000
intersect
select * from emp where sal<3000;
--差集：第一个集合的所有元素减去两个集合重叠部分的元素
Minus:表示差集
select * from emp where sal>1000
minus
select * from emp where sal<3000;

联合查询：分为交叉连接（笛卡尔积连接）、内连接、自连接、外连接（全连接、左外连接、右外连接）、自然连接

--交叉连接（笛卡尔积连接）: 将会返回被连接的两个表的笛卡尔积，返回结果的行数等于两个表行数的乘积.
select 列名,...,列名 from a表 cross join b表;
select e.*,d.deptno dno,d.dname,d.loc from emp e cross join dept d;
​
--内连接: 是把两个表中符合条件的数据连接为一条数据，如果哪个表中存在不符合连接条件的数据，那么这些数据就会被过滤掉（不显示）
select 列名,...,列名 from a表 inner join b表 on 连接条件;(inner可以省略不写)
--查询员工信息及他的部门信息
select e.*,d.* from emp e join dept d on e.deptno=d.deptno;
​
--自连接: 是一个特殊的内连接，内连接一般是两个表的连接，自连接将一个表和它自已进行连接
--查询员工信息，并显示它的上级姓名
select e1.*,e2.ename 上级姓名 from emp e1 join emp e2 on e1.mgr=e2.empno;
​
--不等连接： 连接条件是不等条件,（大于、小于、不等于）
--查询员工信息，并显示员工的工资等级
select e.*,s.grade,s.losal,s.hisal from emp e join salgrade s on e.sal between s.losal and s.hisal;
​
--全连接： 它会查出两个表中的所有数据，
 /*1.如果两个表中的数据都符合连接条件，那么它会和内连接一样，将符合条件的数据连接为一条记录
 2.如果第一表中的一条数据，在第二张表中没有找到和它对应的记录（第二张表没有和第一张表这条数据符合连接条件），
 那么它同样会显示第一张表的这条记录，同时它对应的第二张表数据的位置会显示为空
 3.同理，如果第二张的一条记录，在第一张表中没有找到符合连接条件的数据，那么同样会显示，在显示第一张表数据的位置显示为空*/
select 列名,列名,列名 from 表A full outer join 表B on 连接条件;(outer可以省略)
--查询所有部门和员工信息
select e.*,d.* from emp e full outer join dept d on e.deptno=d.deptno;
​
--左外连接： 查出左表（left outer join关键字前面的表）的所有数据，根据连接条件去右表中找对应的数据,如果找到变显示出来，如果找不到就显示空 
select 列名,列名 from 表A left outer join 表B on 连接条件;(outer可以省略)
--查询所有部门信息，以及各部门下的员工信息
select d.*,e.* from dept d left join emp e on d.deptno=e.deptno;
​
--右外连接： 它会查出右表的所有数据，根据连接条件去左表查找对应的数据，如果找到就显示，如果找不到就显示为空语法：
select 列名,列名,列名 from 左表 right outer join 右表 on 连接条件;(其中outer可以省略)
--查询员工信息，并显示它的部门信息
select d.*,e.* from dept d right outer join emp e on d.deptno=e.deptno;
​
--自然连接: 在两张表中寻找那些数据类型和列名都相同的字段，然后自动地将他们连接起来，并返回所有符合条件按的结果。     
select * from emp natural join dept;        --自然连接
select * from emp join dept using(deptno); --自然连接using用来指定关系列

子查询

--from后面的子查询
--查询员工的姓名和他上级的姓名
select ename, mname from (select e.*, d.empno, d.ename mname, d.job, d.sal, d.deptno from emp e, emp d where e.mgr = d.empno);
​
--where后面的子查询
--查询工资大于10号部门平均工资的员工信息
select * from emp where sal>(select avg(sal) from emp where deptno=10); 
--查询工资比20号部门所有人工资都高的员工信息
select * from emp where sal>( select max(sal) from emp where deptno=20);
select * from emp where sal>all(select sal from emp where deptno=20);
​
--having后面的子查询
--查询各个部门的部门编号和员工人数，要求部门的平均工资大于30号部门的平均工资
select deptno,count(*) from emp group by deptno having avg(sal)>(select avg(sal) from emp where deptno=30);
​
--select 和from 之间的子查询(子查询的结果只能是单行单列)
select e.*,(select count(*) from emp where sal>e.sal) from emp e order by 9;

4.分页SQL

伪列：rownum 它是oracle数据库内置列，任何表都可以使用，它作用是显示表中数字的行号

--查询工资最低的5名员工信息
select t.*,rownum r from (select  *  from emp order by sal) t where rownum<=5;
--注意如果给伪列加条件，小于一个值是可以，不能让它大于某个值
/*
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;