SQL拓荒牛

基础函数

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;

SQL查询语句where,group by,having,order by的执行顺序和编写顺序