一、DQL语言的详解
SQL标准语言中的DQL,用于查询数据。其基本语法可以分成六个部分,我们称之为六个子句
1、基本查询语句的语法格式
select..... from......[where......][group by.......][having........][order by.......]
一个基本查询语句中,至少要有select子句和from子句。其他四个子句根据需求来选择。
2、子句执行顺序
在六个子句同时存在的查询语句中,他们的执行时机也是由先后顺序的。我来整理一下:
1)、查询时,要基于表操作,因此,第一步先 执行 from子句
2)、表确定后,可以使用where子句,进行条件筛选。
3)、然后,再对数据进行分组查询,因此,要执行group by子句
4)、分组查询后,我们可以对分组查询后的数据,再次过滤,限制,所以,要使用having子句
5)、过滤后,我们使用select子句来选择要显示的字段信息
6)、显示的字段值,可以进行相应的排序,这个时候可以使用 order by子句
3、where子句
当我们在对数据表进行CRUD(PS:增加Create、读取查询Retrieve、更新Update和删除Delete)操作时,where子句有对数据进行条件筛选和约束的作用。
where子句的条件表达式形式如下
1)关系表达式。 符号有 >,>=,
如要求年龄大于20,写法:where age > 20;
年龄等于20,写法:where age=20;
年龄不等于20,写法: where age!=20 或者 where age<>20
2)多条件连接符:and、or、[not] between ....and ....
需求1:查询工资在[1500,3500]区间内的员工姓名、职位、工资、部门号。
mysql > select ename,job,sal,deptno from emp where sal>=1500 and sal<=3500;
mysql > select ename,job,sal,deptno from emp where sal between 1500 and 3500;
需求2:查询工资不在[1500,3500]区间内的员工姓名、职位、工资、部门号。
mysql > select ename,job,sal,deptno from emp where sal<1500 or sal>3500;
mysql > select ename,job,sal,deptno from emp where sal not between 1500 and 3500;
需求3:查询姓名是‘james’和‘smith’员工的信息。
mysql > select * from emp where ename='james' or ename='smith';
PS: and 和 or 一定要慎重考虑,以免出差错。
3)集合操作:
[not] in (集合元素)、>all(集合元素)、any(集合元素)、
in (集合元素) 相当于 使用连接符or,对同一字段的"="等式 进行多个值的连接。
not int (集合元素) 相当于使用连接符and,对同一个字段的"!="不等式进行多个值的连接
需求1:查询姓名是 ”james“,"smith","lucy"的员工信息。
mysql > select * from emp where ename='james' or ename='smith' or ename='lucy';
mysql > select * from emp where ename in ('james' , 'smith' , 'lucy');
需求2:查询姓名是不是 ”james“,"smith","lucy"的员工信息。
mysql > select * from emp where ename!='james' and ename!='smith' and ename<>'lucy';
mysql > select * from emp where ename not in ('james' , 'smith' , 'lucy');
PS:对于>all(集合元素)、any(集合元素)、
>all(..):查询结果为大于集合中最大的值。
>any(..):大于集合中最小的即可。
4)模糊查询
关键字:like
通配符 %:匹配0或0个以上的字符。
占位符 _: 匹配一个字符。
需求1:查询姓名中含有s的员工姓名,职位,入职日期,部门编号
select ename,job,hiredate,deptno from emp where ename like '%s%';-----单双引号都可以
需求2:查询姓名中第二个字母是a,工资大于1000的员工姓名,职位,入职日期,部门编号
select ename,job,hiredate,deptno from emp where ename like '_a%' and sal>1000;
4、order by子句
对于查询的数据,我们可以按照需求来排序,如升序,降序,此时需要使用order by子句。
排序关键字:
desc 降序
asc: 升序
语法: order by colName1 [desc|asc] [,colName2 [desc|asc] ]
如果order by子句中不写排序关键字,默认是升序,即默认情况下为asc。
需求1:查询10号部门员工的姓名、职位,工资,奖金 按照工资降序,奖金升序(工资相同时,奖金排序生效)
mysql > select ename,job,sal,comm from emp where deptno=10 order by sal desc,comm;
5、group by子句
有的时候,我们的需求可能是这样的:查询员工表中每个部门的总人数,总工资,平均工资等等;或者是在全校成绩表中,查询每个班级的平均成绩等等。此时,在查询时,就要将表中的数据按照相应的字段,根据值的不同,来划分成不同的小组。如上述需求,我们要按照部门号这个字段,根据不同的值,10号一组,20号一组,30号一组.......来统计。全校成绩表中,我们要按照班级编号来划分小组。
在做分组查询时,一般都会涉及到相应的分组函数(也叫聚合函数),而所有聚合函数都会忽略null,不进行统计。
count(colName):统计指定字段的字段值的记录总数,null值会被忽略。
max(colName):返回指定字段的字段值中的最大值,null值会被忽略。
min(colName):返回指定字段的字段值中的最小值,null值会被忽略。
sum(colName):返回指定字段的字段值的和,null值会被忽略。
avg(colName):返回指定字段的字段值的平均值,null值会被忽略。
注意:在使用avg()函数时,因为其忽略空值记录,因此要根据需求考虑是否要将null值记录计算在内,如果想要计算在内的话,我们可以使用这个函数:ifnull(colName,value),它的作用是如果指定的colName对应的值有null,我们就使用value值,否则使用其本身
需求1:求员工表中的所有员工的平均奖金。分析:即使有的员工的奖金为null,我们也要将之计算进来。
mysql > select avg(ifnull(comm,0)) from emp ;
参照下面需求
需求2:求有奖金的员工的平均奖金。分析:此时,不计算奖金为null的员工数。
mysql > select avg(comm) from emp
注意:当使用了分组函数时,却没有使用group by子句,此时是将整张表划成了一组。
下面我整理了一些需求,供大家理解分组查询。
需求1:查询所有员工的最高工资,最低工资,工资总和,平均工资及其总员工数;--分析:整张表为一组。
mysql > select max(sal),min(sal),sum(sal),avg(ifnull(sal,0)),count(ename) from emp;
需求2:查询每个部门的最高工资,入职最早的日期,奖金之和,平均奖金及其总人数;--分析:按照部门分组
mysql > select max(sal),min(hiredate),sum(comm),avg(ifnull(comm,0)),count(ename) from emp group by deptno;
从第二个需求,我们可以看到,在数据显示上,我们不知道是哪个部门的信息,因此,我们可以将分组字段,添加到select子句中。再次注意,在分组查询中,select子句中除了分组函数外,只能存在分组字段,其他不相关的字段不允许存在。
改进后:
mysql > selectdeptno,max(sal),min(hiredate),sum(comm),avg(ifnull(comm,0)),count(ename) from emp group by deptno;
需求3:查询每个部门的总人数,工资之和,按照 部门号降序排序
mysql > select deptno "部门号",count(*) '总人数',sum(sal) 工资之和 from emp group by deptno order by deptno desc;
需求4:查询每个部门,每种职位的人数 --分析:按照部门和职位分组
mysql > select deptno,job,count(*) 总人数 from emp group by deptno,job;
6、having子句
在做分组查询时,我们可能需要再次过滤,这个时候需要使用having子句。
如下需求
需求1:查询部门工资总和大于10075的部门号,工资总和,最高工资,最低工资,总人数,按照部门号降序
mysql > select deptno,sum(sal),max(sal),min(sal),count(*) from emp group by deptno having sum(sal)>10075 order by deptno;
二、去重查询
关键字 distinct。需要注意的是此关键字必须紧挨着select关键字。看下面需求:
需求1:查询员工表中有哪些部门编号------分析:同一个部门编号不需要显示很多次,因此需要去重查询。
mysql > select distinct deptno from emp;
需求2:查看员工表中有哪些职位
mysql > select distinct job from emp;