目录
⑦ str_to_date 将字符串转换成日期 date_format 格式化日期 format 设置千分位
⑧ case..when..then..when..then..else..end
⑩ ifnull 空处理函数 (将null 转换为 具体值)
③ 怎么避免笛卡尔积现象?连接时加条件。(select ... from ... where ...)
1、 DQL语句大总结(不能颠倒!)
2、DQL语句执行顺序(不能颠倒!)
一、DQL(文字查询语言)— — select
1、简单查询
①查询表中的字段(查询两个字段时,用逗号隔开)
select 字段 from 表名;
要想查询所有字段?
- 方法1:列出所有字段
- 方法2:用*代替所有字段
② 给查询的列起别名?
#用as 或者 空格 隔开 原名 和 别名;
select JOB,SAL as SL from emp;
select JOB,SAL SL from emp;
#别名中有空格时,用单引号或者双引号把别名括起来
- 用单引号才标准,双引号在Oracle数据库用不了
- 别名是中文也用单引号括起来
select JOB,SAL "S L" from emp;
注意:
- 只是把显示的查询结果列名显示为‘SL’,原列名还是‘SAL’;
- select语句永远不会进行修改操作,只是负责查询。
③ 字段的计算(字段可以使用数学表达式!)
例如:计算员工年薪?
2、条件查询 (where)
语法格式:
select
字段1,字段2,字段3....
from
表名
where
条件;
查询条件
- (小于)< (大于)> (等于)= (不等于)!=或<> >= <=
- (并且)and (或者)or 注意:and优先级比or高
- (两值之间)between ... and ... 或者 <= and >=
- (为0)is null (不为0)is not null
- (包含)in (不包含)not in
select ename,sal from emp where sal = 800 or sal = 5000 or sal=3000;
select ename,sal from emp where sal in(800, 3000,5000);
like (模糊查询)
- % 代表多个字符
- 下划线_代表一个字符
- \ 转义字符
想找出名字中有_的?(用转义字符对_转义)
mysql> select ename,empno from emp where ename like '%\_%';
3、排序(order by)
语法:
select
ename,sal
from
emp
order by
sal; // 默认是升序!!!
如何降序?
select
ename,sal
from
emp
order by
sal desc;
select ename,sal from emp order by sal;//默认升序排序
select ename,sal from emp order by sal desc;//指定降序
select ename,sal from emp order by sal asc;//指定升序
两个字段排序?条件A如果相等,按照条件B排序。
select ename,sal from emp order by sal asc,ename asc;
注意:在前的条件起主导
了解:根据字段的位置也可以排序
- select ename,sal from emp order by 2;
// 2表示第二列。第二列是sal按照查询结果的第2列sal排序。了解一下,不建议在开发中这样写,因为不健壮。因为列的顺序很容易发生改变,列顺序修改之后,2就废了。
练习题:
找出工资在1250到3000之间的员工信息,要求按照薪资降序排列。
select ename,sal from emp where sal between 1250 and 3000 order by sal desc;
4、数据处理函数(又称单行处理函数)
- 特点:一个输入对应一个输出
- 对应的函数是:多行处理函数:多个输入一个输出
常见的单行处理函数:
① 转换小写 lower
select lower(ename) as ename from emp;
② 转换大写 upper
③ 取子串 substr
语法:
- substr( 被截取的字符串, 起始下标,截取的长度)
select substr(ename,1,1) as ename from emp;
练习:(找出员工名字第一个字母是A的员工信息?)
方法一:
方法二:
④ 字符串的拼接 concat
⑤ 取长度 length
⑥ 去空格 trim
⑦ str_to_date 将字符串转换成日期
date_format 格式化日期
format 设置千分位
⑧ case..when..then..when..then..else..end
练习:当员工的工作岗位是MANAGER的时候,工资上调10%,当工作岗位是SALESMAN的时候,工资上调50%,其它正常。
select ename,job,sal as oldsal,(case job when 'manager' then sal*1.1 when 'salesman' then sal*1.5 else sal end) as newsal from emp;
⑨ 四舍五入 round 生成随机数 rand()
select后面可以跟某个表的字段名(可以等同看做变量名),也可以跟字面量/字面值(数据)。
⑩ ifnull 空处理函数 (将null 转换为 具体值)
语法:ifnull(数据, 被当做哪个值)
计算每个员工的年薪?
select ename,(sal+ifnull(comm,0))*12 as salcomm from emp;
5、分组函数(多行处理函数)
多行处理函数的特点:输入多行,最终输出一行。
5个:
count 计数
sum 求和
avg 平均值
max 最大值
min 最小值
注意:
分组函数在使用的时候必须先进行分组,然后才能用。如果你没有对数据进行分组,整张表默认为一组。
计算最高工资?最低工资?总工资?平均工资?人数?
使用分组函数时应注意:
- 分组函数自动忽略NULL
- 分组函数中count(*)和count(具体字段)有区别;
- 分组函数不能够直接使用在where子句中。找出比最低工资高的员工信息?
- 所有的分组函数可以组合起来一起用。
6、分组查询****(非常重要!!!)group by
先分组,再查询
计算每个工作岗位的工资和?
计算每个工作岗位的平均薪资?
找出每个工作岗位的最高薪资?
练习:找出“每个部门,不同工作岗位”的最高薪资?
7、having:对分完组之后的数据进一步过滤。
having不能单独使用,having不能代替where,having必须和group by联合使用。
练习:找出每个部门最高薪资,要求显示最高薪资大于3000的?
方法一:
方法二:
优化策略:
where和having,优先选择where,where实在完成不了了,再选择having。
为什么分组函数不能直接使用在where后面?
因为分组函数在使用的时候必须先分组之后才能使用。where执行的时候,还没有分组。所以where后面不能出现分组函数。
select sum(sal) from emp; 这个没有分组,为啥sum()函数可以用呢?
因为select在group by之后执行。
练习:找出每个岗位的平均薪资,要求显示平均薪资大于1500的,除MANAGER岗位之外,要求按照平均薪资降序排。
select job,avg(sal) from emp where job <>'manager' group by job having avg(sal)>1500 order by avg(sal) desc;
8、去重(将查询记录去除重复结果) distinct
// distinct只能出现在所有字段的最前方。表示字段联合去重
select distinct job from emp;
select distinct job,deptno from emp;
练习:统计一下工作岗位的数量?
select count(distinct(job)) from emp;
7、连接查询
这种跨表查询,多张表联合起来查询数据,被称为连接查询。
① 连接查询的分类
② 两张表没有任何限制条件时
select ename,dname from emp,dept;
笛卡尔积现象:
当两张表进行连接查询,没有任何条件限制的时候,最终查询结果条数 = 两张表条数的乘积。
③ 怎么避免笛卡尔积现象?连接时加条件。(select ... from ... where ...)
(查询次数没有减少,只是显示出来的结果被条件筛选了)
(1)内连接之等值连接
条件是等量关系,所以被称为等值连接。
示例:查询每个员工所在部门名称,显示员工名和部门名?
SQL92:
select ename,dname
from emp,dept
where emp.deptno=dept.deptno;
SQL99:
select ename,dname
from emp
inner join dept
on emp.deptno=dept.deptno;
//inner 可以省略,带着可读性更强
SQL92:表的连接条件和后期进一步筛选的条件,都放到了where后面。
SQL99:表连接的条件是独立的,连接之后如果需要进一步筛选,可以用where
(2)内连接之非等值连接
示例:找出每个员工的薪资等级,要求显示员工名、薪资、薪资等级?
select grade,ename,sal
from emp e
join salgrade s
on e.sal between s.losal and s.hisal;
(3)内连接之自连接
自连接,技巧:一张表看做两张表。
示例:查询员工的上级领导,要求显示员工名和对应的领导名?
select a.ename as '员工名',b.ename as '领导名'
from emp a
join emp b
on a.mgr=b.empno;//员工的领导编号=领导的员工编号
(4)外连接之右外连接
right:表示将join关键字右边的表看成主表,主要是为了将这张表的数据全部查询出来,捎带着关联查询左边的表。
在外连接当中,两张表连接,产生了主次关系。
select a.ename,d.dname
from emp a
right outer join dept d
on a.deptno=d.deptno;
outer 可以省略,加上可读性更强。
(5)外连接之左连接
select a.ename,d.dname
from emp a
left join dept d
on a.deptno=d.deptno;
案例:查询每个员工的上级领导,要求显示所有员工的名字和领导名?
select a.ename as '员工名',b.ename as '领导名'
from emp a
left join emp b
on a.mgr=b.empno;
三张、四张表怎么连接?
一条SQL语句的内外连接可以混合出现。
案例:找出每个员工的部门名称以及工资等级,要求显示员工名、部门名、薪资、薪资等级?
select e.ename as '员工名',d.dname as '部门名',e.sal as '薪资',s.grade as '薪资等级'
from emp e
left join dept d
on e.deptno=d.deptno
join salgrade s
on e.sal between s.losal and s.hisal;
案例:找出每个员工的部门名称以及工资等级,还有上级领导,要求显示员工名、领导名、部门名、薪资、薪资等级?
select a.ename as '员工名',b.ename as '领导名',d.dname as '部门名',a.sal as '薪资',s.grade as '薪资等级'
from emp a
join emp b
on a.mgr=b.empno
left join dept d
on a.deptno=d.deptno
join salgrade s
on a.sal between s.losal and s.hisal;
8、子查询
子查询:select语句中嵌套select语句,被嵌套的select语句称为子查询。
① where子句中的子查询
案例:找出比最低工资高的员工姓名和工资?
select ename,sal
from emp
where sal>(select min(sal) from emp);
② from子句中的子查询
from后面的子查询,可以将子查询的查询结果当做一张临时表
案例:找出每个岗位的平均工资的薪资等级。
select a.job,a.avgsal,s.grade
from (select job,avg(sal) as avgsal from emp group by job) a
join salgrade s
on a.avgsal between s.losal and s.hisal;
③ select后面出现的子查询(了解即可)
案例:找出每个员工的部门名称,要求显示员工名,部门名?
select e.ename,e.deptno,(select dname from dept d where e.deptno=d.deptno)
from emp e;
注意:对于select后面的子查询来说,这个子查询只能一次返回1条结果,多于1条,就报错了。!
9、合并查询结果集 union
案例:查询工作岗位是MANAGER和SALESMAN的员工?
select ename,job from emp where job in ('MANAGER','SALESMAN');
select ename,job from emp where job='MANAGER'or job='SALESMAN';
select ename,job from emp where job='manager'
union
select ename,job from emp where job='salesman';
union的效率要高一些。
对于表连接来说,每连接一次新表,则匹配的次数满足笛卡尔积,成倍的翻。。。
而union可以减少匹配的次数。在减少匹配次数的情况下,还可以完成两个结果集的拼接。
使用union的注意事项:
union在进行结果集合并的时候,要求两个结果集的列数相同。
结果集合并时列和列的数据类型也要一致。(MYSQL可以,oracle语法严格 ,不可以)
10、limit(**非常重要!!)
- 分页:为了提高用户的体验,因为一次全部都查出来,用户体验差。可以一页一页翻页看。
- 百度默认:一页显示10条记录。
- limit作用:将查询结果集的一部分取出来。通常使用在分页查询当中。
① limit用法
- limit startIndex, length(startIndex是起始下标,length是长度,起始下标从0开始。)
- 缺省用法:limit 5; 这是取前5
② 分页查询
案例1:按照薪资降序,取出排名在前5名的员工?
select ename,sal
from emp
order by sal desc
limit 5;
案例2:取出工资排名在[3-5]名的员工?
select ename,sal
from emp
order by sal desc
limit 2,3;
分页
公式:limit (pageNo-1)*pageSize , pageSize(第pageno页,每页显示pagesize条记录)
案例:员工按薪资降序排序,排序结果分页展示,每页3条记录,显示第二页内容。
select ename,sal
from emp
order by sal desc
limit 3,3;
二、DQL语句总结
1、 DQL语句大总结(不能颠倒!)
- select
- from
- where
- group by
- having
- order by
- limit
2、DQL语句执行顺序(不能颠倒!)
- from
- where
- group by
- having
- select
- order by
- limit