select语句_函数

1.1 查询语句

1.1.1 select

select 用于从数据看查询数据。语法：

select field1,filed2,.. .

from tablename

[where condition]

select ename "姓名",job "岗位" from emp;

表的别名：

select emp.ename,emp.job from emp;
select e.ename,e.job from emp e;

* 通配符表示查询所有字段。如果要查特定字段时，不要使用*，影响查询效率

 

select e.* from emp e;

 

1.1.2 distinct 去重

把重复性的记录去掉，只保留一条。

修饰多字段时，多个字段的值都不一样才保留。

 

查询工种：

 

select distinct e.job from emp e;

 

1.1.3 where 子句

where 表示查询的条件。

【1】 =,!= ,<>,<,>,<=,>= 关系运算符

<> 表示不等于

 

查询底薪大于等于1000的员工：

 

select e.* from emp e where e.sal >= 1000;

 

any/some/all (list)

any/some(list) 满足list列表中的任意一个条件

all(list) 满足list列表的中所有条件

select e.* from emp e where e.sal>any(some)(800,1000);

select e.* from emp e where e.sal>all(800,1000);

 null

【2】null 在sql中表示的是不确定 => 可以认为没有值

 

select e.* from emp e where e.comm is (not) null;

 

【3】between x and y 

表示一个值位于[x,y]区间，x/y 一般都是数字。

 

select e.* from emp e where e.sal between 1000 and 5000;

 

【4】 in/not in list

表示字段值是否在list列表中

 

select e.* from emp.e where e.deptno (not) in (10,20);

 

【5】模糊查询 

like 关键字用于模糊查询，其中

%:表示任意字符出现多次(含0次),

_:表示任意字符出现1次

 

(已c开头)
select e.* from emp e where e.ename like 'c%';
(第二位为c)
select e.* from emp e where e.ename like '_c%';
(含有c)
select e.* from emp e where e.ename like '%c%';
(含有%的)
select e.* from emp e where e.ename like '%\%%' escape('\');

 

1.2 复杂查询(and/or)

where 后面的条件可以跟多个通过and 或者 or 连接

and:且、并且

or: 或、或者

 

查询部门10且薪资大于等2000的雇员：

 

select e.* from emp e where e.deptno = 10 and e.sal >= 2000;

 

查询部门在10或20的雇员：

 

select e.* from emp e where e.deptno = 10 or e.deptno = 20

 

where 中and、or的执行效率问题：

 

and 表示且，条件越多，检索的数据量越来越少

 

or 表示或，条件越多，检索的数据量越来越多

 

where 条件的执行顺序从后向前

 

AND:  把检索结果较少的条件放到后面。

 

如果and 和 or同时存在时，and先执行。

案例：

 

使用in查询部门名称为 SALES 和 RESEARCH 的雇员姓名、工资、部门编号    

思考:部门名称位于dept，雇员信息位于emp表

 

select e.ename,e.sal,e.deptno
from emp e
where e.deptno in 
(
select d.deptno
from dept d
where d.dname = 'SALES' or d.dname = 'RESEARCH'
);

 

1.3 计算字段

我们经常需要把数据库中检索出来的信息进行再加工，允许的操作+、-、*、/。通过四个运算得到新的字段(计算字段)。

计算字段在数据表中不存在。

 

select e.ename,e.sal+e.comm as "收入",e.deptno from emp e;

 

注意:很多记录中的comm是null,表示不确定的值，经常四则运算后的值也不确定。

当遇到字段时null时,可以通过nvl函数把null转化便于运算的类型。

(当comm值为null时，使用nvl函数：如果e.comm为null，则取后一个值，不为null，则取e.comm)

select e.ename,e.sal+nvl(e.comm,0) "收入",e.deptno from emp e;

 

1.4 函数

函数一般是在数据上执行的，它给数据的转换和处理提供了方便。只是将取出的数据进行处理，不会改变数据库中的值。

函数根据处理的数据分为单行函数和聚合函数（组函数）。

 

组函数又被称作聚合函数，用于对多行数据进行操作，并返回一个单一的结果，组函数仅可用于选择列表或查询的having子句

 

单行函数对单个数值进行操作，并返回一个值。

dual是一个系统表。用于测试。

 

--字符串连接
select concat('aa','12') from dual;
select 'aa'||'12' from dual;

--首字母大写
select initcap('abcd') from dual;

--把大写转化小写
select lower('ABC') from dual;
--把小写转化大写
select upper('abcd') from dual;

--填充字符lpad/rpad
select lpad('abc',5,'/') from dual;

--去掉空白字符
select ltrim('  hellow',' ') from daul;
select rtrim('hellow  ',' ') from daul;
--删除左右两边
select trim('p' from 'pop') from daul;

--求子串 substr(str,loc,len)-->loc从1开始,len表示截取长度
select substr('saber',1,2) from daul;

--查找字符串  找到返回>=1的索引；如果没找到返回0
select instr('abcd','b') from dual;

--求长度
select length('abcd') from dual;

小需求
select substr('18112345678',1,3)||'-'||substr('18112345678',4,4)||'-'||substr('18112345678',8) from  daul;
输出结果为：181-1234-5678

 

 

 

1.5  数值型函数

-- 四舍五入round(x,y)对x保留y为小数
elect round(23.652) from dual;
--四舍五入保留一位小数
select round(23.652,1) from dual;

select round(25.652,-1) from dual;
-- 返回x按精度y截取后的值
select trunc(23.652) from dual;
select trunc(23.652,2) from dual;
select trunc(23.652,-1) from dual;
-- mod（x，y）求余数
select mod(9,2) from dual;
-- ceil 向上取整
select ceil(1.9) from dual;
-- floor 向下取整
select floor(1.9) from dual;

1.6  转换函数

转换函数就是把字符、日期、数值型数据进行相互转换。类型转换分两种：隐式类型转换和显式类型转换。

 

1.6.1     隐式类型转换

字符和数字/日期之间的隐式转换

-- 字符隐式转换成数值
select '100' - 10 from dual;
-- 字符隐式转化为日期
-- DD-MON-RR 默认的日期格式
select 1 from dual where sysdate > '13-May-19';

1.6.2     显示类型转换

 

 

1.6.2.1 to_char(A)

把日期转化成字符

 

把数值格式化成字符串

 

　

 

select to_char(sysdate,'YYYY"年"MM"月"DD"日" HH24:MI:SS') as t from dual;

select to_char(12345,'99999.99') from dual;

把18112341234格式化成181-1234-1234

select replace(to_char(18112341234,'999,9999,9999'),',','-') from dual;

1.7 decode/case when

decode 多用于等值匹配；case when可以用于等值，多用于条件分支。

decode(条件,值1,“返回值1”, 值2,“返回值2”,,,“默认值”)

--查询员工所在的部门名称
select e.ename,e.deptno,decode(e.deptno,10,'部门1',20,'部门2',30,'部门3','未知') from emp e;

case when

select e.ename,e.deptno,
case e.deptno
  when 10 then '部门1'
  when 20 then '部门2'
  when 30 then '部门3'
  else '未知'
end
from emp e;

1.8组函数

组函数把多行数据经过运算后返回单个值。也称聚合函数。

 

注意:

 

[1] 组函数或聚合函数是对一个数据集(表数据、查询出来的表、分组的表)进行聚合。

 

[2] 聚合函数对字段是null的值进行忽略。count(*) 

[3] max/min 适合任意数据类型，sum/avg 只适用于数值类型。

聚合函数的结果可以作为其他查询条件。

 

--最早入职的员工
select e.ename,e.hiredate
from emp e
where e.hiredate = (select min(e.hiredate) from emp e);

 

 

1.9分组(group by)

 

 

select field1,...
from tableName
group by field1[,field2,…]

对数据进行分组后，select语句的字段值只能是分组字段或者聚合函数。

 

select e.deptno,avg(e.sal+nvl(e.comm,0))
from emp e
group by e.deptno

 

    null值归为一组

select e.comm,count(1)
from emp e
group by e.comm

1.9.1having

如果需要对分组的数据进行条件过滤，必须使用having!

-- 查询部门平均薪资大于3000的部门
select e.deptno
from emp e
group by e.deptno
having avg(e.sal) > 3000;


--查询部门薪资大于3000的雇员按部门分组的平均薪资
select e.deptno,avg(e.sal)
from emp e
where e.sal > 3000
group by e.deptno;

注意:

[1] Where过滤行，having过滤分组。

[2] Having支持所有where操作符。

 

1.10排序 (order by)

当需要对数据集进行排序操作时:

 

select field1, field2,...
from tablename
order by field1,field2

 

对数据集进行排序，先按field1排序，如果field1排序相同，按照field2排序，依次类推。

 

-asc 升序,    默认

 

-desc 降序

-- 按薪资升序，名称降序
select e.ename,e.sal
from emp e
order by e.sal,e.ename desc;

order by 一般都是最后执行.

--薪资大于1200的雇员所在部门的平均薪资大于1500的部门，按照平均薪资升序排序 
select e.deptno,avg(e.sal)
from emp e
where e.sal > 1200
group by e.deptno
having avg(e.sal) > 1500
order by avg(e.sal) asc;

order by 既可以用于数据行(记录)排序。也可以对分组的结果进行排序，此时需要聚合函数配合。

 

 

 

 

 

1.10.1Select 语言的执行顺序

 

from -> where -> group by -> having -> select -> order by

1.10.2交集、全集、并集、差集

并集:把集合A的结果和集合B的结果合并，并去掉重复的记录。

 

 

-- 有重复记录取并集
select e.* from emp e where e.deptno = 10 or e.deptno = 20
union
select e.* from emp e where e.deptno = 20;

 

全集: 把集合A的结果和集合B的结果合并,保留重复记录

 

select e.* from emp e where e.deptno = 10 or e.deptno = 20
union all
select e.* from emp e where e.deptno = 20;

 

交集: 把集合A的结果和集合B的结果取相同部分

 

select e.* from emp e where e.deptno = 10 or e.deptno = 20
intersect
select e.* from emp e where e.deptno = 10;

 

差集: 在集合A的结果中去掉集合B的结果 (A-B)

select e.* from emp e where e.deptno = 10 or e.deptno = 20
minus
select e.* from emp e where e.deptno = 10;

1.11多表关联

 

1.11.1笛卡尔积

1.11.2等值连接

 

-- 需求:查询雇员的部门名称
select e.ename,e.deptno,d.dname
from emp e,dept d
where e.deptno = d.deptno

 

1.11.3不等值连接

 

select e.ename,e.sal,sg.grade
from emp e,salgrade sg
where e.sal >= sg.losal and e.sal <= sg.hisal

 

1.11.4外连接

左外连接:左边的表作为主表，右边表作为从表，主表数据都显示，从表数据没有，用null填充,用+号表示。

 

select *
from dept d,emp e
where d.deptno = e.deptno(+)

 

右外连接: 右边的表作为主表，左边表作为从表，主表数据都显示，从表数据没有，用null填充,用+号表示。

 

select *
from emp e,dept d
where e.deptno(+) = d.deptno;

 

1.11.5自连接

一个表自身连接自身时，称为自连接。自连接以不同的视角看待同一张表。

 

select e.ename "雇员",nvl(m.ename,'boss') "领导"
from emp e,emp m
where e.mgr = m.empno(+);

 

1.11.6多于两张表的关联

如果有多个表参与查询，先把t1xt2笛卡尔积得到一个大表T1，再把T1xt3笛卡尔积得到一个另外的大表T2，依次类推。

 

所有的多表查询最终都是两种表的查询。

 

select e.*,d.*,sg.*
from emp e,dept d,salgrade sg
where e.deptno = d.deptno and e.sal between sg.losal and sg.hisal
and e.ename = 'SCO%T'

 

1.12多表关联 - 99 语法

 

92的问题:

 

[1]表的过滤条件和表的连接条件混合在一起，维护麻烦

 

[2]数据库的数据适合变化，根据where子句的执行规则，sql语言也会相应发生变化，给维护造成一定成本。

 

1.12.1笛卡尔积

table 1 cross join table2

--99 笛卡尔积
select *
from dept d cross join emp e

1.12.2自然连接

 

NATURAL JOIN子句基于两个表中列名完全相同的列产生连接

 

[1]两个表有相同名字的列

 

[2]数据类型相同

 

[3]从两个表中选出连接列的值相等的所有行

 

select *
from dept d natural join emp e

 

自然连接只保留一个重复列，整个数据集只有一个deptno。

 

注意:

 

自然连接最优的使用场景是：主外键关系且主外键字段只有一个。

 

1.12.3using

using 主要用于指定连接字段。

[1] 按照指定的字段连接两个表。

[2] 选指定字段值相同的数据行。

 

using 同样适用于自然连接。

1.12.4on 指定连接条件

 

自然连接的条件是基于表中所有同名列的等值连接.

为了设置任意的连接条件或者指定连接的列，需要使用ON子句，两个表的关联用关键字 join ，默认内连接(inner) 语法：

 

select filed1,fild2,…
from table1 
join table2 on condition1
     [join table3 on condition2]*

 

-- 查询SCO%T的管理者名称和其管理者的薪资等级
select e.ename,m.ename,sg.grade
from emp e join emp m on e.mgr = m.empno
join salgrade sg on m.sal between sg.losal and sg.hisal
where e.ename = 'SCO%T'

1.12.5外连接

外连接在99语法中通过outer 关键字，按照主从表的位置可以分为left outer/right outer,语法：

 

select filed1,field2
from table1 left/right outer join table2 on condition1
      [left/right outer join table3 on condition2]*

 

select *
from dept d left outer join emp e
on d.deptno = e.deptno