基础函数
lower() & upper()
select 'ABC',lower('ABC') from dept; --数据转小写
select upper(dname) from dept; --数据转大写
length()
select length(dname) from dept; --数据的长度,汉字占3位
substr()
select dname,substr(dname,3,3) from dept; --从第三位开始,截取3位
concat()
select dname,concat(dname,'123') 拼接 from dept; --拼接数据
replace()
select dname,replace(dname,'a','666') 替换 from dept; --把a替换成666
ifnull()
select ifnull(comm,10) comm from emp; --判断,如果comm是null,用10替换
round & ceil & floor
select money,round(money) from tb_door; --对小数四舍五入后取整数
select money,round(money,1) from tb_door; --对小数四舍五入后保留一位小数
select money,ceil(money) from tb_door; --向上取整
select money,floor(money) from tb_door; --向下取整
uuid()
select uuid();
now() & curdate() & curtime()
select now(); --年月日 时分秒
select curdate(); --年月日
select curtime(); --时分秒
year() & month() & day()
select year(now()),month(now()),day(now());
hour() & minute() & second()
select hour(now()),minute(now()),second(now());
\ 转义字符
select 'ab\'cd';
条件查询
distinct 去重
select distinct job from emp;
where
select * from emp where 1=1;
select * from emp where 1=0; --empty
select * from emp where empno=101;
select * from emp where job="员工" and deptno=2;
select * from emp where job="员工" or deptno=1;
select ename,sal from emp where sal=3000 or sal=8000 or sal=10000;
select ename,sal from emp where sal in(3000,8000,10000);
select ename sal from emp where sal not in(3000,8000,10000);
like 模糊
% 通配符百分号代表0到n个字符
select * from emp where ename like 'l%';
select * from emp where ename like "%a";
select * from emp where ename like "%a%";
_ 通配符下划线代表1个字符
select * from emp where ename like "l__";
select * from emp where ename like "___";
select * form emp where ename like "___k";
null
select * from emp where mgr is null; --过滤字段值为空的
select * from emp where mgr is not null; --过滤字段值不为空的
between and
select * from emp where sal>=3000 and sal<=10000;
select * from emp where sal between 3000 and 10000;
limit 分页
select * from tb_door limit 5; --列出前5条记录
select * from tb_door limit 0,5; --从第1条开始,列出5条记录
select * from tb_door limit 5,5; --从第6条开始,列出5条记录
order by 排序
select ename,sal from emp order by sal --默认升序(从小到大)
select ename,sal from emp order by sal desc --降序(从大到下)
date_format() & str_to_date()
select * from emp where hiredate < "2015-01-01";
select date_format(hiredate,"%y-%m-%d") from emp;
select * from emp where date_format(hiredate,"%y-%m-%d") <"17-01-01";
select year(date_format(hiredate,"%y-%m-%d")) from emp;
select * from emp where year(hiredate) < 2015;
select * from emp where year(date_format(hiredate,"%y-%m-%d")) <=2017;
select str_to_date("2017-09-01 10:29:59","%Y-%m-%d %H:%i:%s");
select str_to_date(hiredate,"%Y-%m-%d %H:%i:%s") from emp;
select str_to_date(hiredate,"%Y-%m-%d") from emp;
年薪统计
select empno,ename,job,sal*13+comm*13 as 年薪 from emp;
select ename,sal,comm,sal+ifnull(comm,0) from emp;
聚合
count()
注意:当查询时具体的字段名,则统计非null的数据
select count(*) from emp;
select count(1) from emp;
select count(comm) from emp; --当具体字段时只统计非null的
max() & min()
select max(sal) from emp;
select max(sal) sal,max(comm) comm from emp;
select min(sal) from emp;
select min(sal) sal,min(comm) comm from emp;
sum() & avg()
select sum(sal) from emp;
select avg(sal) from emp;
group 分组
select deptno,max(sal),avg(sal) from emp group by deptno;
select deptno,job,max(sal),avg(sal) from emp group by deptno,job;
having 过滤分组
注意:having的前提条件是分组,从分组中过滤
select deptno,count(deptno) from emp group by deptno having count(deptno)>=1;