sql语句小记

sql中的函数

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

单行函数

实验表emp样例：

字符函数

大小写函数：upper()、lower()

SELECT 'The job id for '||upper(ename)||' is '||lower(job) "EMPLOYEE DETAILS" from emp;

将每个单词的首字母转换为大写，其他转换为小写：initcap

SELECT 'The job id for '||initcap(ename)||' is '||initcap(job) "EMPLOYEE DETAILS" from emp;

字符处理函数 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

拼接函数lpad、rpad

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
-- >格式化字符

具体添加符：

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;

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