SQL语句基础2

SQL语句基础2

注：本文所有代码执行的表皆来源Oracle数据库自带的SCOTT用户表。

1. 条件查询

条件查询即通过添加条件实现行过滤效果，具体语法如下：

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

where后可使用"="，"!="，">"，"<"，">="，"<="这些比较运算符和"not"取反，"and"与，"or"或这些逻辑运算符作为行过滤条件。使用"between 值1 and 值2"可判断在两个值之间，取值范围[值1,值2]。

--查询在30部门的员工信息
select * from emp where deptno = 30;
--查询员工姓名不为‘KING’的员工信息
select * from emp where ename！='KING';
--查询薪资>2000的员工信息
select * from emp where sal>2000;
--查询薪资在1250~2000之间的员工信息
select * from emp where sal between 1250 and 2000;

注：null值不能使用"=“或”!="来判断，null值使用"is"来判断。

--查询奖金为null的员工信息
select * from emp where comm is null;
--查询奖金不为null的员工信息
select * from emp where comm is not null;

执行流程：from–>where–>select

因为执行流程的原因，在where后面不能够使用字段的别名。数据源可以为表也可以为结果集。

--把结果集当做数据源头,先运算再过滤，查询员工年薪>15000的员工姓名
select ename
  from (select ename, (sal + nvl(comm, 0)) * 12 total from emp)
 where total > 15000;

2. some，all，any函数

• some(值列表)：

--some()，大于最小的值
select * from emp where sal>some(1000,1250,1500);

• all(值列表)：

--all()，大于最大的值
select * from emp where sal>all(1000,1250,1500);

• any(值列表)：

--any()，大于最小的值
select * from emp where sal>any(1000,1250,1500);

3. 集合函数

• Union：并集(去重) 对两个结果集进行并集操作，不包括重复行同时进行默认规则的排序
• Union All：全集(不去重) 对两个结果集进行并集操作，包括重复行，不进行排序
• Intersect：交集(找出重复) 对两个结果集进行交集操作，不包括重复行，同时进行默认规则的排序
• Minus：差集(减去重复) 对两个结果集进行差操作，不包括重复行，同时进行默认规则的排序

--交集
select * from emp where job = 'SALESMAN'
intersect
select * from emp where sal>1500;
--并集（去重）
select * from emp where job = 'SALESMAN'
union
select * from emp where sal>1500;
--并集（不去重）
select * from emp where job = 'SALESMAN'
union all
select * from emp where sal>1500;
--差集
select * from emp where job = 'SALESMAN'
minus
select * from emp where sal>1500;
--差集（顺序改变）
select * from emp where sal>1500
minus
select * from emp where job = 'SALESMAN';

4. 排序

排序（order by）默认升序，语法如下：

select 数据 from 数据源 where 行过滤条件 order by 排序字段1 desc降序|asc升序,排序字段2 desc降序|asc升序...;

注：加上排序后执行流程为from–>where–>select–>order by，排序是对结果集中的数据做排序，order by后可以使用字段别名指定字段做排序。

--查询工资大于2000的员工信息，降序排序
select * from emp where sal>2000 order by sal desc;
--查询年薪大于20000的员工编号，员工姓名，年薪，升序排序
select * from (select empno,ename,(sal+nvl(comm,0))*12 "年薪" from emp) where "年薪">20000 order by "年薪" asc;

5. 子查询

子查询即查询语句中嵌套查询语句。

--查询部门编号与KING相同的员工信息
select * from emp where deptno = (select deptno from emp where ename='KING');
--查询工作岗位与CLARK相同的员工信息
select * from emp where job = (select job from emp where ename = 'CLARK');

6. in

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

--查询工作岗位为SALESMAN或MANAGER的员工信息
select * from emp where job in ('SALESMAN','MANAGER');

7. like

like即模糊匹配，效率相对较低。like语句中"%“代表任意个任意字符，”_"代表一个任意字符。

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

8. 单行函数

单行函数即单条记录返回一个结果。

• sysdate/current_date()：以date类型返回当前的日期

--查询当前的日期
select sysdate from dual;
select current_date from dual;
--日期可以进行+ - 
select sysdate 今天,sysdate+1 明天 from dual;
select sysdate 今天,sysdate-1 昨天 from dual

• add_months(d,x)：返回加上x月后的日期d的值

--查询当前日期5个月后的日期
select add_months(sysdate,5) from dual;

• last_day(d)：返回的所在月份的最后一天

--返回当前日期20天后那个月的最后一天的日期
select last_day(sysdate+20) from dual;

• months_between(date1,date2)：返回date1和date2之间月的数目

--查询当前日期的101天后和当前日期之间有多少个月
select months_between(sysdate+101,sysdate) from dual;

• next_day(sysdate,星期几)：下一个即将要到来的星期几的日期

--查询下一个即将到来的星期一的日期
select next_day(sysdate,'星期一') from dual;

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

--日期对象转为字符串
select to_char (sysdate,'yyyy"年"mm"月"dd"日" hh24:mi:ss') from dual;

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

--字符串转为日期对象
select to_date ('2021-03-16 16:14:30','yyyy-mm-dd hh24:mi:ss') from dual;

• decode(condition,case1,express1,case2 ,express2,….casen,expressn,expressionm)：拿条件的值与 case做定值判断，不能做多值或区间判断

--查询员工的编号，姓名，部门编号，工资，部门编号为10的涨薪1.1倍，部门编号为20的涨薪1.5倍，部门编号为30的涨薪2倍，部门编号为40的涨薪3倍
select empno,
       ename,
       deptno,
       sal,
       decode(deptno, 10, sal * 1.1, 20, sal * 1.5, 30, sal * 2, sal * 3) salraise
  from emp;

• case when 条件判断1 then 结果1 when 条件判断2 then 结果2 …else 默认值 end：拿条件的值做定值判断，可以做区间判断

--查询工资等级的信息，下限如果低于1000变为1000，上限不变
select grade,
       losal,
       hisal,
       (case
         when losal < 1000 then
          1000
         else
          losal
       end) losalraise
  from salgrade;

9. 组函数

组函数/多行函数/聚合函数即多条记录返回一个结构。

注：如果select的后面一旦出现组函数，只能与其他组函数，或者分组字段一起使用group by，where后面不能使用组函数，null值不参与组函数的运算。

• count()：计数

--查询薪资等级为3的员工个数
select count(*)
  from emp
 where sal between (select losal from salgrade where grade = 3) and
       (select hisal from salgrade where grade = 3);

• sum()：求和

--本公司本月30部门薪资的总开销
select sum(sal) from emp where deptno = 30;

• avg()：求平均值

--查询工资低于平均工资的员工编号，姓名及工资
select empno, ename, sal from emp where sal < (select avg(sal) from emp);

• max()：求最大值

--查询最高薪水的员工姓名,及薪水
select ename, sal from emp where sal = (select max(sal) from emp);

• min()：求最小值

--查询最低薪水的员工姓名,及薪水
select ename, sal from emp where sal = (select min(sal) from emp);