数据库随笔 二

函数

基本函数

lower

select 'ABC', LOWER('ABC') from dept; --数据转小写

upper

select upper(dname) from dept; --数据转大写

length

select length(dname) from dept; --数据的长度

substr

select dname, substr(dname,'123') from dept; --截取【1,3】

concat

select dname, concat(dname,'123') X from dept; --拼接数据

replace

select dname, replace(dname,'a','666') X from dept; --把a字符换成666

ifnull

select ifnull(comm,10) comm from dept2; #判断，如果comm是nulll，用10替换

round & ceil & floor

• round 四舍五入

select comm, round(comm) from emp;--直接四舍五入取整
select comm, round(comm,1) from emp; --四舍五入并保留一位小数

• ceil 向上取整

select comm, ceil(comm) from emp;

• floor 向下取整

select comm, floor(comm) from emp;

uuid

select UUID(); --返回UUID，a08528ca-741c-11ea-a9a1-005056c00001

now

select now(); --年 日 时 分 秒

select curdate(); --年 日

select curtime(); --时 分 秒

year & month & day

• hour()时，mintue()分，second()秒

select now(), hour(now()),minute(now()),second(now()) from emp;

• year()年，month()月，day()日

select now(),year(now()),month(now()),day(now()) from emp;

转义字符

“ ’ ”作为SQL语句符号，内容中容易出现单独 ’ 就会乱套，进行转义即可

select 'ab'cd'; --单引号是一个SQL语句特殊字符

select 'ab'\cd'; --数据中有单引号时，需要使用 \ 进行转义

条件查询

distinct

使用 distinct 关键字，可以去除重复的内容行

select loc from dept;

select distinct loc from dept;

where

注意：where 中不能使用列别名

select * from emp;

select * from emp where 1=1; --类似没条件

select * from emp where 1=0; --条件不成立

select * from emp where empno=100; --唯一条件

select * from emp where ename='tony' and deptno=2; --相当于两个条件的emp关系

select * from emp where ename='tony' or deptno=1; --相当于两个条件的 | 关系


select name, sal from emp where sal=1400 or sal=1600 or sal=1800;
或
select name, sal from emp where sal in(1400,1600,1800);


select name, sal from emp where sal not in(1400,1600,1800);

like

通配符 % 代表0到n个字符，通配符 _ 代表一个字符

select * from emp where  ename like '1%'; --以1开头的

select * from emp where ename like '%a'; --以a结束的

select * from emp where ename like '%a%'; --中间包含a的

select * from emp where ename like 'l__'; --l后面有两个字符的, _ 代表一个字符

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<=3000 and sal>=10000;
或
select * from emp where sal between 3000 and 10000;

limit

分数最高的记录：按照分数排序后，limit n，返回前n条内容

select * from emp limit 2; --列出前2条内容

select * from emp limit 1,2; --从第2条开始，展示条记录

select * from emp limit 0,3; --从第1条数据开始，展示3条记录；前三条记录

order by

select * from emp order by sal; --默认升序排序

select * from emp order by sal desc; --默认降序排序

统计案例

入职统计

• 统计2015年以前入职的员工

select * from emp where DATE_FORMAT(hiredate,'%Y-%m-%d')<'2015-01-01';

select * from emp where YEAR(hiredate)<2015;

• 2019年以后入职的员工，日期进行格式转换后方便书写

select * from emp where year(DATE_FORMAT(hiredate,'%Y-%m-d'))<=2019;

• 2015年至2019年入职的员工

select * from emp where
STR_TO_DATE(hire,'%Y-%m-%d')>='2015-01-01'
and
STR_TO_DATE(hire,'%Y-%m-%d')<='2019-12-31';

年薪统计

SELECT empno,ename,job,sal*13+comm*13 FROM emp;

SELECT empno,ename,job,sal*13+comm*13 **as**  **年薪** FROM emp;--用as给列起个别名

SELECT empno,ename,job,sal*13+comm*13 年薪 FROM emp; --as也可以省略

select ename, sal+comm from emp

select ename, sal , comm, sal+ifnull(comm,0) from emp--用0替换掉null

聚合aggregation

count

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) min from emp; --求字段的最小值

select min(sal) min, max(sal) max from emp; --求最小值和最大值

select ename, max(sal) from emp group by ename; --分组

sum & avg

select count(*) from emp; --总记录数

select sum(sal) from emp; --求和

select avg(sal) from emp; --平均数

分组 group

用于对查询结果进行分组统计

group by 表示分组

每个部门按照最高薪资和平均薪资进行分组

select deptno, max(sal), avg(sal) from emp;

group by depton; --按照depton进行分组

SELECT job,MAX(sal),AVG(sal) FROM emp;

GROUP BY job; --按照job分组

SELECT deptno,job,MAX(sal),AVG(sal) FROM emp;

GROUP BY deptno,job; --deptno和job都满足的

having 类似于where过滤返回的结果

平均工资小于8000的部门

select deptno, AVG(sal) from emp;
group by deptno; --按部门分组
having AVG(sal)<8000; --查询条件,类似where,但是group by只能配合having

--deptno出现的次数
SELECT deptno,COUNT(deptno) FROM emp

GROUP BY deptno; --按deptno分组

HAVING COUNT(deptno)>1; --次数多的