所有例子都是scott中的表
0x01.单行函数
- 作用于一行,返回一个值。
1.字符函数
接收字符输入返回字符或者数值。
-
upper(列|字符串): 把小写的字符转换成大小的字符。
-
lower(列|字符串): 把大写字符变成小写字符。
-
initcap(列|字符串): 开头字母大写,其余全部小写。
-
length(列|字符串): 取得字符串长度。
-
substr(列|字符串,开始索引,[长度]): 截取字符串,若没有设置长度,则从开始截取到结尾。(Oracle默认从1开始而不是0)。
-
replace(列|字符串,旧内容,新内容) : 新内容替换旧内容。
-
示范:
-- upper
select upper('atfwus') from dual;--输出‘ATFWUS’
-- lower
select lower('ATFWUS') from dual;--输出‘atfwus’
select lower (ename) from emp;--将会从emp表中选择14行,大写变小写。
-- initcap
select ename,initcap(ename) from emp;--将会从emp表中选择14行,开头字母大写,其余小写。
-- length
select length('ATFWUS') from dual;--输出6
-- substr
select substr('ATFWUS',3) from dual;--输出‘FWUS’
select substr('ATFWUS',1,2) from dual;--输出‘AT’
-- replace
select substr('ATFWUS','AT','A') from dual;--输出'AFWUS'
2.数值函数
- round():默认情况下 ROUND 四舍五入取整,可以自己指定保留的位数。
- trunc(列 | 数字 [,小数位]): 实现数据的截取,即不进位。
- mod(列 | 数字 ,列 | 数字): 求模(计算余数)。
- 示范:
-- round
select round(123.1231) from dual;--输出123
select round(123.1231,2) from dual;--输出123.12
select round(55.52,-2) from dual;--输出100
-- trunc
select trunc(123.9231) from dual;--输出123
select trunc(123.9931,1) from dual;--输出123.9
select trunc(56.3,-1) from dual;--输出50
-- mod
select mod(10,3) from dual;--输出1
3.日期函数
-
模糊日期的运算规则:
- 日期 – 数字 = 日期(若干天之前的日期)
- 日期 + 数字 = 日期(干天之后的日期)
- 日期 – 日期 = 数字 (天数)
-
SYSDATE :系统时间。
-
日期 AND_MONTHS(列 | 日期,月数) 在指定日期上增加若干月之后的日期
-
数字 MONTHS_BETWEEN (列|日期,列|日期) 返回两个日期之间所经历的月数
-
日期 LAST_DAY(列|日期) 取得所在日期月的最后一天
-
日期 NEXT_DAY(列|日期,星期X) 返回下一个指定的一周时间数对应的日期
-
示范:
-- 查询系统时间
select sysdate from dual;
-- 输出明天这个时候的时间
select sysdate+1 from dual;
-- 查询出emp表所有员工入职距离现在几个月
select months_between(sysdate,e.hiredate) from emp e;
-- 四个月后的日期
select add_months(sysdate,4) from dual;
-- 查询本月最后一天的这个时候
select last_day(sysdate) from dual;
-- 查询下前日期下周四所对应的日期
select next_day(sysdate,'thusday') from dual;
4.转换函数
-
TO_CHAR(列|日期|数字,转换格式): 将日期或数字转换成指定结构的字符串。
- 日期:年(yyyy)、月(mm)、日(dd)。
- 时间:时(hh、hh24)、分(mi)、秒(ss)。
- 数字:任意数字(9)、本地货币符号(L)。
-
TO_DATE(列|字符串,转换格式) 按照指定的转换格式编写字符串后将其变为日期型数据。
-
TO_NUMBER(列|字符串) 将字符串变为数字。
-
示范:
-- 转换当前的日期格式
select to_char(sysdate,'yyyy年-mm月-d日') from dual;
-- 拆分日期
select to_char(sysdate,'yyyy'),to_char(sysdate,'mm'),to_char(sysdate,'dd') from dual;
-- 数字格式化(输出: ¥9,999,999,999)
select to_char(9999999999,'L999,999,999,999,999') from dual;
-- TO_DATE(字符串转日期)
select to_date('2020-05-20','yyyy-mm-dd') from dual;
--TO_NUMBER(字符串转数字)
select to_number('1')+to_number('2') from dual;--输出3
5.通用函数
- 数字 NVL(列|NULL,默认值): 如果传入的内容是NULL,则使用默认数值处理;如果不是NULL则使用原始数据处理;
- 数据类型 DECODE(列|字符串|数值,比较内容1,显示内容1,比较内容2,显示内容2,…[,默认显示内容]) 设置的内容会与每一个比较内容进行比较,如果内容相同,则会使用显示内容进行输出,如何都不相同,则使用最后的默认信息输出。
- 示范:
-- NVL 处理NULL函数(任何数与NULL运算都为NULL此时把NULL替换成0,所以要处理NULL值)
select empno,ename,comm,(sal+nvl(comm,0))*12 from emp;
-- 将职位信息换成中文(要设置则全部设置,不设置的为NULL,若不设置也可用‘---’表示)
elect ename,job,decode(job,'CLERK','职员','SALESMAN','销售员') from emp;
6.条件句表达式(case when)
- 语法:
CASE expr WHEN comparison_expr1 THEN return_expr1
[WHEN comparison_expr2 THEN return_expr2
WHEN comparison_exprn THEN return_exprn
ELSE else_expr]
END
- 示范:
select t.empno,t.ename
case
when t.job = 'CLERK' then '职员'
when t.job = 'PRESIDENT' then '总裁'
else
'没有工作'
end
from emp t
0x02.多行函数
1.count()
- 统计记录数。
- 示范:
select count(*) from emp;
2.min(),max()
- 最大最小值查询。
- 示范:
select min(val) from emp;
3.avg()
- 平均值查询。
- 示范:
select avg(val) from emp;
4.sum()
- 求和。
- 示范:
select sum(val) from emp;
0x03.分组查询
- 语法:
SELECT * |列名
FROM 表名
{WEHRE 查询条件}
列名 1 ASC|DESC,
列名 2...ASC|DESC
{GROUP BY 分组字段} ORDER BY 列
-
注意事项:
- 如果使用分组函数,SQL 只可以把 GOURP BY 分组条件字段和分组函数查询出来,不能有其他字段。
- 如果使用分组函数,不使用 GROUP BY 只可以查询出来分组函数的值。
-
示范:
-- 查询每个部门的人数
select deptno ,count(ename)
from emp
group by deptno;
-- 查询每个部门的平均工资
select deptno,avg(val)
from emp
group by deptno;
0x04.多表查询
- 使用一张以上的表做查询就是多表查询 .
- 语法:
SELECT {DISTINCT} *|列名..
FROM 表名 别名,表名 1 别名
{WHERE 限制条件
ORDER BY 排序字段 ASC|DESC...}
- Oracle 中的内外连接基本相似。
- Oracle有一个专用的外连接。
- 示范:
select *
from emp e,dept d
where e.deptno(+)=d.deptnp;
- 使用(+)表示左连接或者右连接,当(+)在左边表的关联条件字段上时是左连接,如果是在右边表的关联条件字段上就是右连接。
自连接:
- 站在不同的角度,把一张表看成多张表。
- 示范:
-- 查询员工姓名,员工领导姓名
select e1.ename,e2.ename
from emp e1,emp e2
where e1.mgr=e2.empno;
0x05.子查询
- 在一个查询的内部还包括另一个查询,则此查询称为子查询。 Sql 的任何位置都可以加入子查询。
- 在返回多条记录的子查询可以把它的结果集当做一张表,起个别名。
1.子查询返回值
-示范:
-- 查询出工资和ATFWUS一样的员工信息
select *
from emp
where sal =
(select val
from emp
where name='ATFWUS');
2.子查询返回一个集合
- 示范:
-- 查询出工资和1号部门任意员工一样的所有员工信息
select *
from emp
where val in
(select sal
from emp
where deptno=1);
3.子查询返回一张表
- 示范:
-- 查询出每个部门最低工资,和最低工资的员工姓名,和员工所在部门。
select t.deptno,t.msal,e.ename,d.dname
from (select deptno,min(sal) msal
from emp
group by deptno
) t,emp e,dept d
where t.deptno=e.deptno
and t.msal=e.sal
and e.deptno=d.deptno;
0x06.分页查询
- ROWNUM:表示行号,实际上此是一个列,但是这个列是一个伪列,此列可以在每张表中出现。
- rownum不支持大于号。
- 示范:
-- emp表工资倒序排列后,每页五条记录,查询第二页
select * from(
select rownum rn,e.* from(
select * from emp order by sal desc
) e where rownum<11
) where rn>5;
ATFWUS --Writing By 2020–05-04