sql条件查询、单行函数、多行函数

1.条件查询

条件查询语句的基本格式：

select * from 数据源 where 行过滤条件；

执行流程：1.from 2.where 3.select结果集

1.1条件

1.1.1条件判断

​ 在进行行过滤时，可以使用以下标识符对条件进行筛选：

符号	功能
=	表示相等条件
！=	不等
<>	不等
>	大于
<	小于
>=	大于等于
<=	小于等于

1.1.2条件关系

​ 条件关系主要是，与、或、非，在sql中与、或、非的表达方式与java不同，在java中通过&&、||、！表达与或非条件，而在sql中则是and、or、not。

​ som(值列表)：任何一个、all(值列表)：大于最大值，或小于最小值、any(值列表)：任何一个

​ 判断一个值或字段是否在区间，可以使用：between 值1 and 值2

--查询所有信息
SELECT * FROM EMP;
--查询姓名为KING的员工
select ename from emp where ename ='KING';
--查询名字不为KING的员工姓名
select ename from emp where not ename <> 'KING';
--查询工资大于1000的员工信息
select empno,ename,sal,job from emp where sal>=1000;
--查询工资在1000到200之间的员工信息
select empno,ename,sal,comm,job from emp where sal>=1000 and sal<=2000;
select empno,ename,sal,comm,job from emp where sal between 1000 and 2000;
-- all()
select empno,ename,sal comm from emp where sal> all(700,1000,1500);
--any()
select empno,ename,sal,comm from emp where comm > any(0);
--some()
select empno,ename,sal,comm from emp where sal > some(500);

​ 需要值得注意的是，Null值不能使用=或！=判断，需要使用is判断：

--查询工资大于等于1000且有奖金的员工
select empno,ename,sal,comm,job from emp where sal>=1000 and comm is not null;
select empno,ename,sal,comm,job from emp where sal>=1000 and not comm is null;
--查询工资大于等于1000，奖金不为null且大于0的员工信息
select empno,ename,sal,comm from emp where sal>=1000 and comm>0 and comm is not null;
--查询工资大于1000或者有奖金的员工信息
select empno,ename,job,sal,comm from emp where sal>=1000 or comm is not null;

​ 因为执行流程，在where后面不可以使用字段的别名！

​ 数据源可以是表也可以是结果集：

--以结果集为数据源进行筛选，年薪大于15000  where后面不能使用字段的别名
select * from (select empno,ename,sal,comm,(sal+nvl(comm,0))*12 nianxin from emp) where nianxin >15000; 

1.2集合

• union 并集（去重） 对两个结果进行并集操作，不包括重复行同时进行默认规则的排序；
• union all 并集（不去重） 对两个结果集进行并集操作，包括重复行，不进行排序；
• Intersect 交集（找出重复） 对两个结果集进行交集操作，不包括重复行，同时进行默认规则的排序；
• Minus 差集（减去重复） 对两个结果集进行差操作，不包括重复行，同时进行默认规则的排序。

--集合的使用  去重求并集
select empno,ename,sal,comm from emp where sal>1500
union
select empno,ename,sal,comm from emp where comm is not null;
--不去重求并集
select empno,ename,sal,comm from emp where sal>1500
union all
select empno,ename,sal,comm from emp where comm is not null;
--求交集
select * from emp where job='SALESMAN'
Intersect
select * from emp where sal>1500;

1.3排序

​ order by 表示对字段进行排序，且默认升序。

​ 格式：

select 数据 from 数据源 where 行过滤条件 order by 排序字段 desc/asc;

​ 执行流程：from ->where ->select ->order by

​ order by 后面可以使用字段别名指定字段做排序。

--排序,按照工资降序排序
select * from emp order by sal desc;
--排序，带有条件的排序,asc升序
select * from emp where comm >0 order by sal asc;
--同样结果再排序
select * from emp order by sal desc,comm asc;

1.4子查询

​ 子查询：查询语句中嵌套查询语句。

--子查询语句，查询语句嵌套查询语句
select  * from emp where deptno = (select deptno from emp where ename = 'KING');

1.5in(值列表)

​ in()用来判断某个数据是否在In(值列表中)，如果存在就满足，不存在就不满足。

--in(值列表)
select * from emp where deptno in (10,20);

1.6模糊匹配

​ like 表示模糊匹配；%表示任意个字符；_表示1个任意字符；

--查询名字中包含A的员工信息
select * from emp where ename like '%A%';
--查询名字中以A开头的员工信息
select  * from emp where ename like 'A%';
--查询名字的倒数第二个字符为A的员工信息
select  * from emp where ename like '%N_';

2.函数

2.1单行函数

​ 单行函数的特点是一条记录返回一个值。

2.1.1sysdate/current_date

​ 以date类型返回当前的日期，且日期可进行+，-运算。

--日期可以进行+ - 
select sysdate 今天,sysdate+1 明天 from dual;
select sysdate 今天,sysdate-30 明天 from dual;
--查询每个员工的转正日期  3个月转正
select hiredate,hiredate+90 from emp;

2.1.2add_months(d,x)

​ 月份计算，返回加上x月后的日期d的值。

--月份 + - add_months(d,x)
select hiredate, add_months(hiredate,-3) from emp;

2.1.3LAST_DAY(d)

​ 返回所在月份的最后一天。

--本月最后一天
select last_day(sysdate) from dual;

2.1.4months_between(date1,date2)

​ 返回date1和date2之间月的数目。

--月份差
select months_between(sysdate,hiredate) from emp;

2.1.5next_day(sysdate,星期一)

​ 下一个即将要到来的星期一的日期。

--即将要过的星期几
select next_day(sysdate,'星期一') from dual;

2.1.6to_char

to_char(日期对象,‘格式模板’)日期对象转为字符串

--系统日期转字符串   日期可以按照模板格式转成字符串
select to_char(sysdate,'yyyy"年"mm"月"dd"日" hh24:mi:ss') from dual;

2.1.7to_date

to_date(字符串,‘格式模板’)字符串转为日期对象

--字符串转日期     格式始终保持不变
select to_date('2021/03/14 16:45:30','yyyy-mm-dd hh24:mi:ss') from dual;

2.1.8decode判定函数

格式：

decode(condition,case1,express1,case2,express2,…,casen,expressn);

--decode判定函数
select decode(sal,800,1000,sal) 调薪后,sal 调薪前 from emp;
select decode(sal,(select sal from emp where sal<),1000,sal),sal 调薪前 from emp;--错误写法，只能做定值判断

2.1.9case函数

格式：

case 条件 when 值1 then 结果1 when 值2 then 结果2…else 默认值 end

--case 语句
select empno,ename,sal,(case deptno when 10 then sal when 20 then sal*2 else sal*3 end) from emp;

当值为区间时：

--如果case 想要与条件判断(区间)  case when 条件判断1 then 结果1 when 条件判断2 then 结果2  ...else 默认值 end
select ename,
       sal,
       deptno,
       (case
         when deptno < 20 then
          sal * 10
         when deptno < 30 then
          sal * 100
         else
          sal
       end) raisesal
  from emp;

2.2多行函数

​ 多行函数|组函数|聚合函数 即多条记录返回一个结果，需要掌握：

• count() 统计函数
• sum() 求和
• avg() 求平均值
• max() 求最大值
• min() 求最小值

值得说明的是：

1）组信息与单条记录不能同时查询

2）组函数不能在where中，能使用的地方select having

3）null不参与计算

--查询 最高薪水的员工姓名,及薪水
select ename,sal from emp where sal =(select max(sal) from emp);
--查询工资低于平均工资的员工编号，姓名及工资
select empno,ename,sal from emp where sal<(select avg(sal)from emp);
--查询薪资在1000~200的员工个数
select count(1) from emp where sal between 1000 and 2000;