本文详细介绍了SQL的DQL查询语言，包括无条件和有条件查询、排序分组、行列转换、分析函数（开窗函数）、偏移函数、转换函数、子查询以及表链接。讲解了各种查询技巧，如比较运算、模糊查询、分组排序、窗口函数的使用等。
1、简单查询
- 无条件查询：select * | 字段1,...,字段n from 表
--查询emp表全部字段的内容
select * from emp;
--查询emp表中ename,job,sal三个字段的内容
select ename,job,sal from emp;
--查询emp表中job字段，并进行去重处理
select distinct job from emp;- 有条件查询：select * | 字段1,...,字段n from 表 where 条件表达式
- 多条件查询：and(并且)必须要满足全部条件才会返回结果，or(或者)只要满足一个条件就会返回结果
--查询emp表中工资大于1500的员工的全部信息
select * from emp where sal >= 1500;
--查询emp表中部门编号等于30 且 工资大于3000 的员工的全部信息
select * from emp where deptno = 30 and sal > 3000;- 条件表达式：
- 比较运算 >大于 , >=大于等于 ,<小于 ,<= 小于等于,=等于 , （<> | != | ^=）都是不等于
- 行数过滤 rownum 本身为虚列，因此要在数据表生成后才能使用
- 离散查找 in | not in（包含 | 不含）
- 连续范围 between……and（闭区间查询，包含起始值和结束值）
- 模糊查询 like（%匹配任意多个字符，_匹配一个字符）
--查询emp表的前10行记录
select * from emp where rownum <= 10;
--查询emp表中工资大于等于1500 并且 工资小于等于3000
select * from emp where sal >= 1500 and sal <= 3000 ;
select * from emp where sal between 1500 and 3000 ;
--查询emp表中名字是以符合条件的员工信息
select * from emp where ename like 'A%'; --以A开头的
select * from emp where ename like '%N'; --以N结尾的
select * from emp where ename like '%O%'; --包含O的
select * from emp where ename like '_____'; --长度为5的
--查询emp表中员工编号不包括 7521 7788 7369 7499 7654 的员工信息
select * from emp where empno not in(7521 ,7788 ,7369, 7499, 7654);
--查询emp表中comm列为空的员工信息
select * from emp where comm is null;2、排序分组
排序语法：select * | 字段1,...,字段n from 表 [where] order by 字段1|别名1|数字 [asc | desc] , 字段2|别名2|数字 [asc | desc];
如果排序用数字来表示的话，那么这个数字是对应那个排序字段
--查询emp表中的所有员工信息，并按工资排序
select * from emp order by sal asc; --升序
select * from emp order by sal desc; --降序分组语法：select 字段1,...,字段n , max(字段) from 表 [where] group by 字段1,...,字段n [order by 字段，数值，别名 ，聚合函数 ];
--查询emp表中10号部门的最高工资,并且显示部门编号
select deptno, max(sal) from emp where deptno = 10 group by deptno;
--查询emp表中，每个部门的平均工资，总工资，最高工资，最低工资及每个部门的总人数，按总工资升序排序
select deptno, avg(sal), sum(sal), max(sal), min(sal), count(*)
from emp
group by deptno
order by sum_sal;
--查询emp表中，每个部门的平均工资，只取出10、20号两个部门 且 平均工资大于2500
select deptno,avg(sal)
from emp
where deptno <> 30 -- 组前过滤（尽量把已知的过滤条件优先放到where条件中）
group by deptno -- 10 20 30
--having avg(sal) > 2500 ; -- 组后过滤条件3、行列转换
Oracle 独有：decode(字段,字段中的值1,转换后的结果1,...,字段中的值n,转换后的结果n,[默认参数：字段,也可以是一个固定值])
--将job字段中工作为CLERK以中文显示为'办事员'，其它工作全部用'其它'显示
select decode(job,'CLERK','办事员','其它') from emp;
--将job字段中所有的工作以中文显示
select decode(job, 'CLERK', '办事员',
'SALESMAN', '销售',
'MANAGER', '经理',
'ANALYST', '分析师',
'PRESIDENT', '大BOSS') from emp;case when 过滤： case 字段 when 值1 then 新值1
when 值2 then 新值2
[else] 新值n end [别名]
--对所有部门进行转换，结果以中文显示
select deptno,
case deptno
when 10 then '十'
when 20 then '二十'
when 30 then '三十'
end new_deptno
from emp ;case when 判断：case when 字段 = 值1 then 新值1
when 字段 = 值2 then 新值2
[else] 新值n end [别名]
--对sal进行工资等级划分 ，工资小于1500 返回 a ,大于等于1500并且小于等于3000 返回b，其它情况返回c
select sal,
case when sal < 1500 then 'a'
when sal >= 1500 and sal <= 3000 then 'b'
when sal > 3000 then 'c'
end new_sal1,
case when sal < 1500 then 'a'
when sal >= 1500 and sal <= 3000 then 'b'
else 'c'
end new_sal2,
case when sal < 1500 then 'a'
when sal between 1500 and 3000 then 'b'
else 'c'
end new_sal3
from emp ;4、分析函数（开窗函数）
排序类
注意：该类函数，可以没有 partition by，但是必须要有 order by
- row_number() over([partition by 字段1,...,字段n] order by 字段... asc |desc) 并列不跳号 1、2、3、4
- rank() over([partition by 字段1,...,字段n] order by 字段... asc |desc) 并列会跳号 1、2、2、4
- dense_rank() over([partition by 字段1,...,字段n] order by 字段... asc |desc) 并列不跳号 1、2、2、3
--生成一个连续的序号，忽视并列
select e.*, rownum as row_1, row_number()over(order by empno ) rn from emp e ;
--按部门分组
select e.*, row_number() over(partition by deptno order by sal) from emp e; --忽视并列，序号
select e.*, rank() over(partition by deptno order by sal) from emp e; --并列后，会跳号
select e.*, dense_rank() over(partition by deptno order by sal) from emp e; --并列后，不跳号聚合类
- sum() over([partition by 字段1...] order by 字段.... asc | desc )
- avg() over([partition by 字段1...] order by 字段.... asc | desc )
- max() over([partition by 字段1...] order by 字段.... asc | desc )
- min() over([partition by 字段1...] order by 字段.... asc | desc )
- count() over([partition by 字段1...] order by 字段.... asc | desc )
--对emp表中所有员工的工资进行向下累加求和
select e.*, sum(sal)over(order by sal,empno ) from emp e;
--对emp表中每个部门工资进行升序排序，并且向下累加求和
select e.*, sum(sal) over(partition by e.deptno order by sal,empno) from emp e ;5、偏移函数
- 向下偏移：lag (列[，偏移量 默认值是1][，填充值默认为空 ]) over(选项)
- 向上偏移：lead(列[，偏移量 默认值是1][，填充值默认为空 ]) over(选项)
select empno, lag(empno)over(order by empno) from emp；
--返回值： 原列 偏移后 执行lead(empno)
7369 7499
7499 7369 7521
7521 7499 7566
select empno, lag(empno, 1, 0)over(order by empno) from emp；
--返回值： 原列 偏移后 执行lead(empno)
7369 0 7499
7499 7369 7521
7521 7499 7566
select empno, lag(empno, 2, 0)over(order by empno) from emp；
--返回值： 原列 偏移后 执行lead(empno)
7369 0 7521
7499 0 7566
7521 7369 7654
7566 7499 76986、转换函数
注意：使用行列转换函数只能使用select *，不能指定具体的字段
- 行转列： pivot (聚合函数(字段) for 要转换的字段 in (转换后的字段))
- 列转行：unpivot (新增值所在列的列名 for 新增列转为行后所在列的列名 in (需转为行的列名))；
--原数据
ename scourse score
张三 语文 87
张三 数学 75
...........
王五 化学 88
--行转列
select * from t1
pivot(sum(score) for course in('语文' yw, '数学' sx, '英语' yy, '物理' wl, '化学' hx))
--转换后
姓名 语文 数学 英语 物理 化学
王五 77 93 67 100 88
李四 77 93 67 55 76
张三 87 75 90 95 65
--列转行
select * from temp1
unpivot(score for course in(yw as '语文', sx as '数学', yy as '英语', wl as '物理', hx as '化学'))
--转化后回到原数据的形式7、子查询
定义：在一个查询中嵌套另外一个查询
--查询与30号部门员工编号，工作，工资都相同的员工信息，where后面
select empno,job,sal from emp where deptno = 30;
select *
from emp
where (empno, job, sal) in
(select empno, job, sal from emp where deptno = 30);
--查询出每个部门的平均工资，并且平均工资要大于所有员工的平均工资，having后面子查询
select deptno, avg(sal)
from emp
group by deptno
having avg(sal) > (select avg(sal) from emp);
--不用 having 查询出每个部门的平均工资，并且平均工资要大于所有员工的平均工资，from 后面
select t.*
from (select deptno,avg(sal) avg_sal
from emp
group by deptno) t
where t.avg_sal > ( select avg(sal) from emp ) ;8、表链接
定义：将两张或多张表按照一定的条件链接在一起返回结果集
- 内连接：inner join 将关联的表中的能关联上的字段全部取出
- 外连接：left | rigth join 除了要把能关联上的数据返回之外，还要把主表中未关联上的数据也查询出来
- 全连接：full join 返回全部表的全部数据，未关联上的也返回
- 交叉链接--笛卡尔积：cross join 返回主表的每条数据与次表每条数据的链接结果
扫一扫
1543
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
