一.基本函数
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值