查询
-
使用DISTINCT剔除重复行(DISTINCT必须紧跟在select之后)
select distinct deptno from emp; select distinct deptno,job from emp;
-
处理空值
select ename,job,sal, IFNULL (comm,'无') as comm from emp; -- 判断comm这一列中如果有空值 就把空值替换为‘无’
-
数据过滤
-
比较运算符
-
“>” 表示前者大于后者
-
“!=”或“<>” 用于比较两者是否相等
-
any 用于匹配列表中任意值
查询在10 或 20 或 30 部门工作的员工信息 select * from emp where deptno in (10,20,30);
-
-
SQL运算符
-
between...and... 用于匹配值的范围
select ename,job,sal from emp where sal between 1500 and 2000;
-
is null 和 is not null 用于判断值是否为空
select * from emp where comm is null; select * from emp where comm is not null;
-
like 实现模糊查询
-
默认采用%匹配任意多个字符
select ename,empno,job from emp where ename like '%o%';
-
默认采用_匹配任意单个字符
查询ename第三个字母是o的员工信息 select ename,empno,job from emp where lower(ename) like '__o%'; select ename,empno from emp where ename like '_/_%'; -- /_ 用/转义 select ename,empno from emp where ename like '_$_%' escape '$'; -- 转义
-
-
-
数据分组
-
max求一列的最大值
-
min求一列的最小值
-
sum 用于求和
-
count 用于统计非空函数的行数
select count(ename) from emp;
-
GROUP BY 分组查询
select deptno,min(sal),max(sal),avg(sal) from emp group by deptno; -- 四舍五入函数 round() select deptno,min(sal),max(sal),round(avg(sal)) from emp group by deptno; -- 先用where过滤再分组 select deptno,job,round(avg(sal)) from emp where deptno is not null group bydeptno,job;
-
HAVING 数据筛选
-- 对分组后的数据再进行筛选 select deptno,avg(sal) from emp where deptno is not null group by deptno having avg(sal) > 2000;
-
ORDER BY 排序
-- 先根据sal排序 如果sal相同再用deptno排序 select * from emp order by sal DESC,deptno DESC;
-
-