Oracle数据库学习笔记——sql语句函数

Posted by Linexus on Monday, January 9, 2023

sql语句小记

sql中的函数

sql中有许多的函数,其中包括了单行函数和其他函数。

单行函数

image-20230109143834184

实验表emp样例: image-20230109143952158

字符函数

  • 大小写函数:upper()lower()
SELECT 'The job id for '||upper(ename)||' is '||lower(job) "EMPLOYEE DETAILS" from emp;

image-20230109145440485

  • 将每个单词的首字母转换为大写,其他转换为小写:initcap
SELECT 'The job id for '||initcap(ename)||' is '||initcap(job) "EMPLOYEE DETAILS" from emp;

image-20230109145509362

  • 字符处理函数 dual表:可以借助dual来满足结构化查询语言格式,作为伪表。

  • 拼接函数concat

-- 使用“||”来拼接字符串
select '拼接'||'字符串' as str from dual 
-- 通过concat()函数实现
select concat('拼接', '字符串') as str from dual 
-- 拼接多个参数
select concat(concat('拼接', '多个'), '字符串') from dual 
  • 截取函数substr
select substr(to_char(sysdate, 'yyyy-mm-dd HH:mi:ss'), 12, 5) as time from dual
  • 长度函数length
 length('Hello World!')=1
  • 查找字符函数instr
select substr('AAA-BBB',instr('AAA-BBB','-',-1)+1) 值 from dual;

select INSTR('CORPORATE FLOOR','OR', 3, 2) as loc from dual 

image-20230109151502849

image-20230109151554367

  • 拼接函数lpadrpad
select lpad('Hello', 10, '*') from dual; 

select rpad('Hello', 10, '*') from dual; 
-- 字符串,总长度,字符串
  • 去除函数trim
-- 去掉双端函数1
select trim('H' from 'HelloWorldH')from dual;
-- 去掉双端函数2
select trim(both 'H' from 'HelloWorldH')from dual;
-- 去掉尾函数
select trim(trailing 'H' from 'HelloWorldH')from dual;
-- 去掉头字母
select trim(leading 'H' from 'HelloWorldH')from dual;
  • 替换字符串replace
select replace('HelloWorld','ll','LL')from dual;
-- >HeLLoWorld

数字函数

  • round函数:四舍五入指定小数值
select round(3456.5555,2)from dual;
-- >3456.56
  • trunc函数:四舍五入保留整数
select trunc(3456.5555)from dual;
-- >3456
  • mod函数:取模函数
select mod(1600,300)from dual;
-- >100

日期函数

  • 返回系统当前日期
select sysdate from dual;
  • 时间查询和简单计算
select last_name,round(sysdate hire_date)from employees;
-- >查询当前员工的任职时间
  • MONTHS_BETWEEN函数
select months_between (sysdate,hire_date)from employees;
  • ADD_MONTHS函数
select add_months (sysdate,5)from dual;
  • NEXT_DAY下星期几是几号
select next_day (sysdate,'星期一')from dual;

select next_day (sysdate,3)from dual;
  • LAST_DAY指定月的最后一天
select last_day(sysdate)from dual;
  • ROUND四舍五入日期
SQL>select round(sysdate,'yy')from dual;

SQL>select round(sysdate,'dd')from dual;
  • TRUNC截断日期
SQL>select TRUNC(sysdate,'yy')from dual;

SQL>select TRUNC(sysdate,'dd')from dual;

字符转换(显示类型转换)

  • to_char函数转换日期
-- 提取单个标识
select to_char(sysdate,'mon')from dual;

select to_char(sysdate,'day')from dual;

select to_char(sysdate,'dy')from dual;

其他日期及时间格式可以参考该 blog

  • to_char函数转换数字
se1ect to_char(234234346.555,'999,999,999,999,999.99')from
dual
-- >格式化字符

具体添加符:

image-20230109161718975

  • fm指标
select to_char(46.555,'fm000,999,999.99')from dual;
-- >000,000,046,56
  • to_number函数
select to_number ('89,456.84','L999,999.99')from dual;
  • to_date函数

可以参考该blog

通用函数

空函数

  • NVL(expr1,expr2)
  • NVL2 (expr1,expr2,expr3)
  • NULLIF (expr1,expr2)
  • COALESCE (expr1,expr2,...exprn)
函数 说明
NVL 转换空值为一个实际值
NVL2 如果expr1非空,NVL2返回expr2:
如果expr1为空,NvL2返回expr3
参数expr1可以是任意数据类型。
NULLIF 比较两个表达式,如果相等返回为空;如果不相等,返回第一个表达式
COALESCE 返回表达式列表中的第一个非空表达式。

条件函数

  • case [when then] else
select
CASE sex
WHEN '1' THEN '男'
WHEN '2' THEN '女'
ELSE '其他' END as "性别"
from dual;
--查询当月数据是0的数据(月份不是行,是列名)
select *
from table_name 
where 0 = case  
            when to_char(sysdate, 'MM')='01' then JAN
            when to_char(sysdate, 'MM')='02' then FEB
            when to_char(sysdate, 'MM')='03' then MAR
            when to_char(sysdate, 'MM')='04' then APR
            when to_char(sysdate, 'MM')='05' then MAY
            when to_char(sysdate, 'MM')='06' then JUN
            when to_char(sysdate, 'MM')='07' then JUL
            when to_char(sysdate, 'MM')='08' then AUG
            when to_char(sysdate, 'MM')='09' then SEP
            when to_char(sysdate, 'MM')='10' then OCT
            when to_char(sysdate, 'MM')='11' then NOV
            else DEC
            end;

聚合函数

分组函数

  • AVG 平均值
 select avg(sal) from scott.emp;
 
 select avg(distinct sal) from scott.emp;
  • COUNT 计数
select count(comm) from emp;
  • MAX 最大值
select max(sal) from emp;
  • MIN 最小值
select min(empno) from emp;
  • SUM 合计
select sum(comm)+sum(sal) from emp;

group by 子句

group by语句从英文的字面意义上理解就是“根据(by)一定的规则进行分组(Group)”。它的作用是通过一定的规则将一个数据集划分成若干个小的区域,然后针对若干个小区域进行数据处理。 如果在查询的过程中需要按某一列的值进行分组,以统计该组内数据的信息时,就要使用group by子句。不管select是否使用了where子句都可以使用group by子句。

-- 求出每个部门的人数
select deptno,count(*) as "人数" from emp group by deptno;

Having 子句

HAVING 子句对 GROUP BY 子句设置条件的方式与 WHERE 子句和 SELECT 语句交互的方式类似。WHERE 子句搜索条件在进行分组操作之前应用;而 HAVING 搜索条件在进行分组操作之后应用。HAVING 语法与 WHERE 语法类似,但 HAVING 可以包含聚合函数。HAVING 子句可以引用选择列表中出现的任意项。

select deptno,count(*) from emp group by deptno having count(*)>5;
-- count(*)作为自然函数

order by 子句

通过order by进行以某列为基准的排序。

select deptno,job,count(*) from emp group by deptno,job order by deptno;