数据库常用函数

数据库常用函数

数字相关

-------数字有关函数 Math.round ceil floor mod ------
--四舍五入，可保留小数点，也可以从整数部分四舍五入
select round(45.678)from dual;
select round(45.678,0)from dual;-- 正常去掉小数位
select round(45.678,2)from dual;-- 保留两位有效数字
select round(45.678,-1)from dual;-- 从个位数开始四舍五入
--没有额外的参数
select ceil(45.678)from dual;--向上取整
select floor(45.678)from dual;--向下取整

--截取
select trunc(15.79,1)from dual;--截取到指定位置 15.7
SELECT trunc(15.79,-1)from dual;--10
--取余数，取模
select mod(4.5678,2) from dual;
SELECT mod(10,3) from dual;
--空值判断
--nvl(comm,0)如果comm为null，取0
select ename,sal,comm,sal*12+nvl(comm,0) from emp_zld;
--nvl2(comm,val1,val2);如果comm为Null，取val2，如果不为null，则取val1
select nvl2(comm,0,1000)from emp_zld;

字符串相关

--dual 虚拟表，借助虚拟表完成对应功能--计算、值
-- || 字符串拼接可以无限拼接  
--contat()参数只能传递两个
select 'ha'||'hei'||'ho' from dual;
--()中只能传两个参数
select concat('ha','hei') from dual;
--函数可以嵌套
select concat(concat('ha','hei'),'ho') from dual;

--字符串长度
select length('lily') FROM DUAL;

--字符串补齐
select lpad('haha',10,'*') from dual;
select rpad('haha',10,'*') from dual;

--字符串切割
--从3开始，一共取4个
--从n个字符开始（n从1开始）取m个字符
select substr('ABCDEFG',3,4) "Substring"
     FROM DUAL;
--n可以为负数，意味着从倒数第n个字符开始往后
select substr('ABCDEFG',-3,4) "Substring"
     FROM DUAL;
--全变大写、变小写
SELECT LOWER('ROSE')from dual;
SELECT UPPer('rose')from dual;
--首字母大写
select INITCAP('rose') from dual;


SELECT
    *
FROM USER_ZLD 
WHERE UPPER(NAME) = 'LUCY';



--TRIM 截取,去除左右两边指定的字符串
select TRIM ('h' from 'hello world!hhh')from dual;
--ltrim 去掉左边的指定字符串,默认为空格
select ltrim('hello world!hhh','h')from dual;
--rtrim去掉右边的指定字符串
select rtrim('hello world   ')from dual;

--indexof功能，不包含子串，返回0
select instr('hello world','o')from dual;
select instr('hello world','o',10)from dual;
--指定位置为负数，倒着数，返回值为倒着数的第一个字符在整个字符串中的位置
select instr('hello world','o',-5)from dual;

时间相关

--时间 date timestamp

SELECT SYSDATE FROM DUAL;

SELECT SYSTIMESTAMP FROM DUAL;

SELECT to_char(SYSDATE,'yyyy-mm-dd') FROM DUAL;
insert into user_zld(birthday)values
('20-JUN-20');--默认格式

insert into user_zld(birthday)values
(todate('2019-02-28', 'yyyy-MM-dd' ));

select to_date('2019-02-28', 'yyyy-MM-dd' ) from dual;--把字符串转成时间
select to_char(SYSTIMeSTAMP,'yyyy-MM-dd hh24:mi:ss')from dual;--2019-07-17 20:51:41
SELECT to_char(SYSDATE,'yyyy-mm-dd') FROM DUAL;--2019-07-17
select to_char(SYSTIMeSTAMP,'yyyy"年"MM"月"dd"日" hh24:mi:ss')from dual;--2019年07月17日 20:52:01
select to_char(SYSTIMeSTAMP,'yyyy"年"MM"月"dd"日" hh24:mi:ss day')from dual;

--所在日期当月最后一天
SELECT last_day(sysdate) from dual;

--提取 Calendar.get() day,month,year
select extract(day from sysdate) from dual;--17提取日
select extract(month from sysdate) from dual;--7提取月
select extract(year from sysdate) from dual;--2019提取年

--在指定时间上添加月份
select add_months(sysdate,-2)from dual;

--得出后面日期减去前面日期的值
select months_between(
    to_date('2019-02-28', 'yyyy-MM-dd' ),
    to_date('2019-07-28', 'yyyy-MM-dd' )
) from dual;

基本查询

筛选

--查询全部 ，* 先给解析成每一个字段，
再来查询，效率低，不建议在开发时候使用
select * from user_zld;

--筛选 where
select * from emp_zld
    where 
    --在部门20的人
    --    deptno = 20;
    --工资小于3000 > >= < =<
    --    sal<3000;
    --工资不等于三千
    sal <> 3000;

select * from emp_zld
    where
        --大于或者小于 or and
        (sal>3000 or sal <3000)
        and deptno = 20;--and优先级高于or
--工资大于任意一个值
    SELECT ENAME ,SAL FROM EMP_ZLD
    where SAL > ANY(3000,1500,1200);
--工资大于所有值
    SELECT ENAME ,SAL FROM EMP_ZLD
    where SAL > ALL(3000,1500,1200);
--工资等于其中一个值
    SELECT ENAME ,SAL FROM EMP_ZLD
    where SAL in (3000,1500,1200);

排序

-- 查询出来的员工，按照入职时间排序
 select * FROM EMP_ZLD
 ORDER BY HIREDATE;--默认升序

 select * FROM EMP_ZLD
 ORDER BY HIREDATE desc;--默认降序

字符串比较

-- 字符串比较，大小写敏感，借助函数
    select * from emp_zld
        where  
            lower(job) = 'salesman';--改成小写
     
--名字长度
SELECT
    *
FROM emp_zld
WHERE length(ename)>5;

--名字以S开头的 通配符：%0个或多个*  _一个
--like通常配合通配符使用，用来进行模糊查询
--转义字符的使用
--sql中转义字符的使用 \_ escape '\'(\可以换成任意字符)

SELECT
    *
FROM emp_zld
 where ename like 'S%';
--名字中有s的
SELECT
    *
FROM emp_zld
 where ename like '%S%';
     
--null值不能用来判断，并且不能计算，通常代表无穷小或者无穷小     
SELECT
    *
FROM emp_zld
where mgr is null;--is not null

分组函数、聚合函数

--分组函数、聚合函数    max min sum avg count
 --所有分组函数都会忽略null值`在这里插入代码片`
 --COUNT 数量
 select count(EMPNO) from EMP_ZLD;
 --统计表中有多少数据，且不受空值影响

--组函数作为条件不能作为where筛选字段
--只有order by可以接别名
select MGR,MIN(SAL) 
FROM EMP_ZLD
--where mgr is not null and sal>=800
GROUP BY MGR
--加上组函数的条件，having很占用资源
HAVING(MIN(SAL)>= 800 AND MGR IS NOT NULL);
--having 后可以加组函数条件

--select 运行顺序只在orderby之后
--       在from、having、groupby之后
---from--where-groupby-having-select-orderby
--筛选条件 最好用where

--select字句中出现的字段，必须同时出现在group by子句中
--查看部门的最高工资
select deptno,max(sal) 
from emp_zld
group by deptno;
 
--查看员工部门有哪些
--DISTINCT 去重 加在字段前面
 select deptno from emp;
 select distinct deptno from emp;--去重