#条件查询 DISTINCT where like null #查询部门地址 select loc from dept #包含重复结果selectDISTINCT loc from dept #去掉重复结果#where用来过滤数据#查询部门编号=1的记录select*from dept #全表查--低效select*from dept where deptno=1#只查1条--高效#SQL的执行顺序 from where select #查询在二区的部门select*from dept where loc="二区"#查询在二区的research的部门select*from dept where loc='二区'and dname='research'#查询在二区的research的部门编号select deptno from dept where loc='二区'and dname='research'#查询在一区的编号是1的部门名称select dname from dept where loc='一区'and deptno=1#and高效#查询在一区的部门或者编号是3的部门select*from dept where loc='一区'or deptno=3#or低效select*from dept where dname ='o'#明确的条件,查询名称=o的部门
–3,like
#like模糊的条件 %占位符匹配0~n个字符select*from dept where dname like'%o%'#模糊的条件,查询名称里包含o的部门 select*from dept where dname like'a%'#以a开头的部门--高效select*from dept where dname like'%ch'#以ch结尾的部门#查询在一区的部门或者名称包含ting的部门select*from dept where loc='一区'or dname like'%ting%'
–4,null
#查询comm是空的 员工的信息select*from emp where comm isnull#查询comm不为空的 员工的信息select*from emp where comm isnotnull#查询每个员工的月薪select*,sal+comm from emp
select*,sal+ifnull(comm,0)from emp #null不参与运算,需要特殊处理select*,sal+ifnull(comm,0),sal*12+ifnull(comm,0)*12from emp
–5,between and /limit/order by
#查询工资(5000,10000)的员工信息select*from emp where sal>5000and sal<10000select*from emp where sal between5000and10000#都包含[5000,10000]#分页 limit,限制数据的条数select*from emp limit2#取前两条select*from emp limit0,2#从0(第1条记录)开始,取两条select*from emp limit2,2#从2(第3条记录)开始,取两条select*from emp limit1,3#从1(第2条记录)开始,取三条#查询工资>5000的前两条记录select*from emp where sal>5000limit2#order by排序 升序(默认的)、降序select*from emp orderby sal asc#升序select*from emp orderby sal desc#desc降序#练习1:查询工资>5000的两个最高薪的员工信息select*from emp where sal>5000orderby sal desclimit2#练习2:查询名字里包含o的老员工select*from emp where ename like'%o%'orderby hiredate limit1#练习3:查询名字里包含o的员工的入职年份select*,year(hiredate),year(now())-year(hiredate)from emp where ename like'%o%'
–6,综合练习
#练习4:2015年以前入职的老员工select*from emp whereyear(hiredate)<2015select*from emp where hiredate <'2015-1-1'#练习5:查询每个员工入职了几年select*,year(NOW())-year(hiredate)from emp
#练习6:查询2015~2019年入职的员工select*from emp whereyear(hiredate)>=2015andyear(hiredate)<2019select*from emp whereyear(hiredate)between2015and2019#练习7:一年13薪,算年薪 select*,sal*13+ifnull(comm,0)*13from emp
select*,sal*13+ifnull(comm,0)*13as 年薪 from emp #给列/字段设置别名select*,sal*13+ifnull(comm,0)*13 年薪 from emp #省略as
MySql基础函数一,Mysql的基础函数–1,常见函数–2,测试二,条件查询–1,概述–2,distinct where–3,like–4,null–5,between and /limit/order by–6,综合练习三,聚合函数–1,测试一,Mysql的基础函数–1,常见函数lower–全转小写upper–全转大写length–求长度concat–拼接串substr–截取字符串replace–替换字符串ifnull–对null元素的替换round–对小数四舍五入ceil–对小数