数据库常用函数

一.基本函数

1.概述

SQL里的函数可以看做java里的方法,每个函数都提供了不同功能.

2.常见函数

2.1 查询所有的列 --低效

select * from dept;

2.2 查询dname的列 --高效

select dname from dept;

2.3 查询id,dname的列 --高效

select id,dname from dept;

2.4 lower(?) 把?转为小写

select id,name,lower(dname) from dept;

2.5 upper(?) 把?转为大写

select id,dname,upper(dname) from dept;

2.6 length(?) 获取?的长度

select dname,length(dname) from dept;
select ename,length(ename),job,length(job) from emp;

length() 用的是utf-8编码表,一个数字或者一个字母占一个字节.一个汉字占三个字节
2.7 substr(1,2,3) 截取子串 (列名,开始位置,截取长度)

select dname,substr(dname,3,2) from dept;

2.8 concat(1,2,…) 拼接字符串 (列名,要拼接的新值,…)

select dname,concat(dname,'hello','123') from dept;

2.9 replace(1,2,3) 替换 (字段名,被替换的数据,新数据)

select dname,replace(dname,'a','6') from dept;

2.10 ifnull(1,2) 如果是null就替换成新值 (字段名,新值)

select comm,ifnull(comm,10000) from emp;

2.11 null不参与运算

select ename,sal,comm,sal+ifnull(comm,0) from emp;

2.12 round(四舍五入) & ceil(向上取整) & floor(向下取整)

select comm,round(comm),ceil(comm0,floor(comm) from emp;

2.13 uuid()

select uuid();

2.14 当前日期
i. now(当前年月日时分秒)
ii. year(当前年) month(当前月) day(当前日)
iii. hour(当前时) minute(当前分钟) second(当前秒)

select now(),year(now()),month(now()),day(now());
select now(),hour(now()),mimute(now()),second(now());

2.15 转义符号
例:查询xi’an,因中间有 ’ ,则需要使用转移符号

错误: select 'xi'an';
正确: select 'xi\'an';

二.条件查询

1. 概述

满足了条件才会去查
distinct(去重) / where(过滤条件) / like(模糊查询) / limit(分页) / order by(排序) / between… and[在…之间]

2. 测试

2.1 给查询loc结果去重

select distinct loc from emp;

2.2 where用来过滤查询的数据

select * from dept where id = 1;
select * from dept where dname='operation';

2.3 满足条件才能被查出来

select * from emp where 1 = 0;

2.4 两个条件之间的关系: and(并且) or(或者)

select * from dept where id=1 and loc='一区';
select * from dept where id=1 or id=3;

2.5 模糊查询 like (%是0~n个字符, _是一个字符)

以a开头	--高效
select * from dept where dname like 'a%';
以s结尾
select * from dept where dname like '%s";
中间包含a
select * from dept where dname like '%a%';
a后面有两个字符
select * from dept where dname like 'a__';

2.6 null对字段值为null的处理

select * from emp where comm is null;
select * from emp where comm is not null;

2.7 between… and[1000,2000] 在1000~2000之间

select * from emp where comm>=1000 and comm<=2000;
select * from emp where between 1000 and 2000;

2.8 limit 分页

取前三条数据
select * from emp limit 3;
注意:
从第2条开始,总共取3条
select * from emp limit(1,3);
从第3条开始,总共取2条
select * from emp limit(2,2);

2.9 order by 排序

默认为升序,忽略了asc
select * from emp order by sal;
降序:
select * from emp order by sal desc;

三.聚合元素

1.概述

指把一列的值,聚合在一起,统一做分析
max() / min() / sum() / avg() / count()

2. 测试

2.1 获取2015年以前入职的员工

select * from emmp where year(hiredate)<2015;

2.2 年薪统计

as用来给列起个别名(as可以省略不写)
select ename,sal,comm,(sal+ifnull(comm,0))*12 as 年薪 from emp;
select ename,sal,comm,(sal+ifnull(comm,0))*12 年薪 from emp;

2.3 聚合函数
i. max(最大值) / min(最小值)
ii. sum(求和) / avg(平均值)

select max(sal),min(sal),sum(sal),avr(sal) from emp;

iii. count() 求总记录数

select count(*) from emp;	--低效
select count(1) from emp;	--与count(*)作用相同,但高效
select count(id) from emp;	--高效
select count(comm) from emp;	--低效,只能统计非null值