1.执行顺序案例:找出工资在1250到3000之间的员工信息,要求按照薪资降序排列。
1.select
ename,sal
from
emp
where
sal between 1250 and 3000
order by
sal desc;
±-------±--------+
| ename | sal |
±-------±--------+
| FORD | 3000.00 |
| SCOTT | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| ALLEN | 1600.00 |
| TURNER | 1500.00 |
| MILLER | 1300.00 |
| MARTIN | 1250.00 |
| WARD | 1250.00 |
±-------±--------+
关键字顺序不能变:
select
…
from
…
where
…
order by
…
以上语句的执行顺序必须掌握:
第一步:from
第二步:where
第三步:select
第四步:order by(排序总是在最后执行!)
通过from在表里查询——>where进行筛选——>select查出来——>order by排序输出
-
select
…
from
…
where
…
group by
…
order by
…以上关键字的顺序不能颠倒,需要记忆。
执行顺序是什么?
1. from
2. where
3. group by
4. select
5. order by从哪个表中查询,where过滤之后用group by进行分组,之后select查询,后用order by排序输出
**3.**select
…
from
…
where
…
group by
…
having
…
order by
…
以上关键字只能按照这个顺序来,不能颠倒。
执行顺序?
1. from
2. where
3. group by
4. having
5. select
6. order by
从某张表中查询数据,
先经过where条件筛选出有价值的数据。
对这些有价值的数据进行分组。
分组之后可以使用having继续筛选。
select查询出来。
最后排序输出!
4.
select
…
from
…
where
…
group by
…
having
…
order by
…
limit
…
执行顺序?
1.from
2.where
3.group by
4.having
5.select
6.order by
7.limit..
2.ifnull
ifnull 可以将 null 转换成一个具体值
ifnull是空处理函数。专门处理空的。
在所有数据库当中,只要有NULL参与的数学运算,最终结果就是NULL。
mysql> select ename, sal + comm as salcomm from emp;
±-------±--------+
| ename | salcomm |
±-------±--------+
| SMITH | NULL |
| ALLEN | 1900.00 |
| WARD | 1750.00 |
| JONES | NULL |
| MARTIN | 2650.00 |
| BLAKE | NULL |
| CLARK | NULL |
| SCOTT | NULL |
| KING | NULL |
| TURNER | 1500.00 |
| ADAMS | NULL |
| JAMES | NULL |
| FORD | NULL |
| MILLER | NULL |
±-------±--------+
计算每个员工的年薪?
年薪 = (月薪 + 月补助) * 12
select ename, (sal + comm) * 12 as yearsal from emp;
+--------+----------+
| ename | yearsal |
+--------+----------+
| SMITH | NULL |
| ALLEN | 22800.00 |
| WARD | 21000.00 |
| JONES | NULL |
| MARTIN | 31800.00 |
| BLAKE | NULL |
| CLARK | NULL |
| SCOTT | NULL |
| KING | NULL |
| TURNER | 18000.00 |
| ADAMS | NULL |
| JAMES | NULL |
| FORD | NULL |
| MILLER | NULL |
+--------+----------+
注意:NULL只要参与运算,最终结果一定是NULL。为了避免这个现象,需要使用ifnull函数。
ifnull函数用法:ifnull(数据, 被当做哪个值)
如果“数据”为NULL的时候,把这个数据结构当做哪个值。
补助为NULL的时候,将补助当做0
select ename, (sal + ifnull(comm, 0)) * 12 as yearsal from emp;
+--------+----------+
| ename | yearsal |
+--------+----------+
| SMITH | 9600.00 |
| ALLEN | 22800.00 |
| WARD | 21000.00 |
| JONES | 35700.00 |
| MARTIN | 31800.00 |
| BLAKE | 34200.00 |
| CLARK | 29400.00 |
| SCOTT | 36000.00 |
| KING | 60000.00 |
| TURNER | 18000.00 |
| ADAMS | 13200.00 |
| JAMES | 11400.00 |
| FORD | 36000.00 |
| MILLER | 15600.00 |
+--------+----------+
3.case…when…then…when…then…else…end
当员工的工作岗位是MANAGER的时候,工资上调10%,当工作岗位是SALESMAN的时候,工资上调50%,其它正常。
(注意:不修改数据库,只是将查询结果显示为工资上调)
select
ename,
job,
sal as oldsal,
(case job when ‘MANAGER’ then sal1.1 when ‘SALESMAN’ then sal1.5 else sal end) as newsal
from
emp;
+--------+-----------+---------+---------+
| ename | job | oldsal | newsal |
+--------+-----------+---------+---------+
| SMITH | CLERK | 800.00 | 800.00 |
| ALLEN | SALESMAN | 1600.00 | 2400.00 |
| WARD | SALESMAN | 1250.00 | 1875.00 |
| JONES | MANAGER | 2975.00 | 3272.50 |
| MARTIN | SALESMAN | 1250.00 | 1875.00 |
| BLAKE | MANAGER | 2850.00 | 3135.00 |
| CLARK | MANAGER | 2450.00 | 2695.00 |
| SCOTT | ANALYST | 3000.00 | 3000.00 |
| KING | PRESIDENT | 5000.00 | 5000.00 |
| TURNER | SALESMAN | 1500.00 | 2250.00 |
| ADAMS | CLERK | 1100.00 | 1100.00 |
| JAMES | CLERK | 950.00 | 950.00 |
| FORD | ANALYST | 3000.00 | 3000.00 |
| MILLER | CLERK | 1300.00 | 1300.00 |
+--------+-----------+---------+---------+
4.分组函数(count,sum,avg,max,min)需要注意什么?
注意:
分组函数在使用的时候必须先进行分组,然后才能用。
如果你没有对数据进行分组,整张表默认为一组。
第一点:分组函数自动忽略NULL,你不需要提前对NULL进行处理。
mysql> select sum(comm) from emp;
±----------+
| sum(comm) |
±----------+
| 2200.00 |
±----------+
mysql> select count(comm) from emp;
+-------------+
| count(comm) |
+-------------+
| 4 |
+-------------+
mysql> select avg(comm) from emp;
+------------+
| avg(comm) |
+------------+
| 550.000000 |
+------------+
第二点:分组函数中count(*)和count(具体字段)有什么区别?
mysql> select count(*) from emp;
+----------+
| count(*) |
+----------+
| 14 |
+----------+
mysql> select count(comm) from emp;
+-------------+
| count(comm) |
+-------------+
| 4 |
+-------------+
count(具体字段):表示统计该字段下所有不为NULL的元素的总数。
count(*):统计表当中的总行数。(只要有一行数据count则++)
因为每一行记录不可能都为NULL,一行数据中有一列不为NULL,则这行数据就是有效的。
第三点:分组函数不能够直接使用在where子句中。
找出比最低工资高的员工信息。
select ename,sal from emp where sal > min(sal);
表面上没问题,运行一下?
ERROR 1111 (HY000): Invalid use of group function
####!!!!!!!!!!!!!!!!where语句还没有分组,所以不可以用分组函数min
说完分组查询(group by)之后就明白了了。
第四点:所有的分组函数可以组合起来一起用。
select sum(sal),min(sal),max(sal),avg(sal),count(*) from emp;
+----------+----------+----------+-------------+----------+
| sum(sal) | min(sal) | max(sal) | avg(sal) | count(*) |
+----------+----------+----------+-------------+----------+
| 29025.00 | 800.00 | 5000.00 | 2073.214286 | 14 |
+----------+----------+----------+-------------+----------+
重点结论:
在一条select语句当中,如果有group by语句的话,
select后面只能跟:参加分组的字段,以及分组函数。
其它的一律不能跟。
5.怎么避免笛卡尔积现象?
内连接
// 增加条件并且表起别名。很重要。效率问题。
SQL92:select
e.ename,d.dname
from
emp e, dept d
where
e.deptno = d.deptno;//SQL92语法。sql92的缺点:结构不清晰,表的连接条件,和后期进一步筛选的条 件,都放到了where后面。
注意:通过笛卡尔积现象得出,表的连接次数越多效率越低,尽量避免表的连接次数。
SQL99语法:
select
e.ename,d.dname
from
emp e
join
dept d
on//后加条件
e.deptno = d.deptno;//内连接的特点:完成能够匹配上这个条件的数据查询出来。
sql99优点:表连接的条件是独立的,连接之后,如果还需要进一步筛选,再往后继续添加where
SQL99语法:
select
...
from
a
join
b
on
a和b的连接条件
where
筛选条件
外连接
外连接(右外连接):
select
e.ename,d.dname
from
emp e
right join
dept d
on
e.deptno = d.deptno;
right代表什么:表示将join关键字右边的这张表看成主表,主要是为了将
这张表的数据全部查询出来,捎带着关联查询左边的表。
在外连接当中,两张表连接,产生了主次关系。
思考:外连接的查询结果条数一定是 >= 内连接的查询结果条数?
正确。
例题:
1.找出“每个部门,不同工作岗位”的最高薪资?
+--------+-----------+---------+--------+
| ename | job | sal | deptno |
+--------+-----------+---------+--------+
| MILLER | CLERK | 1300.00 | 10 |
| KING | PRESIDENT | 5000.00 | 10 |
| CLARK | MANAGER | 2450.00 | 10 |
| FORD | ANALYST | 3000.00 | 20 |
| ADAMS | CLERK | 1100.00 | 20 |
| SCOTT | ANALYST | 3000.00 | 20 |
| JONES | MANAGER | 2975.00 | 20 |
| SMITH | CLERK | 800.00 | 20 |
| BLAKE | MANAGER | 2850.00 | 30 |
| MARTIN | SALESMAN | 1250.00 | 30 |
| ALLEN | SALESMAN | 1600.00 | 30 |
| TURNER | SALESMAN | 1500.00 | 30 |
| WARD | SALESMAN | 1250.00 | 30 |
| JAMES | CLERK | 950.00 | 30 |
+--------+-----------+---------+--------+
!!!!!!!!!!!!技巧:两个字段联合成1个字段看。(两个字段联合分组)
select
deptno, job, max(sal)
from
emp
group by
deptno, job;
+--------+-----------+----------+
| deptno | job | max(sal) |
+--------+-----------+----------+
| 10 | CLERK | 1300.00 |
| 10 | MANAGER | 2450.00 |
| 10 | PRESIDENT | 5000.00 |
| 20 | ANALYST | 3000.00 |
| 20 | CLERK | 1100.00 |
| 20 | MANAGER | 2975.00 |
| 30 | CLERK | 950.00 |
| 30 | MANAGER | 2850.00 |
| 30 | SALESMAN | 1600.00 |
+--------+-----------+----------+
2.找出每个部门最高薪资,要求显示最高薪资大于3000的?
使用having可以对分完组之后的数据进一步过滤。
having不能单独使用,having不能代替where,having必须
和group by联合使用。
第一步:找出每个部门最高薪资
按照部门编号分组,求每一组最大值。
select deptno,max(sal) from emp group by deptno; +--------+----------+ | deptno | max(sal) | +--------+----------+ | 10 | 5000.00 | | 20 | 3000.00 | | 30 | 2850.00 | +--------+----------+
第二步:要求显示最高薪资大于3000
select
deptno,max(sal)
from
emp
group by
deptno
having
max(sal) > 3000;
+--------+----------+
| deptno | max(sal) |
+--------+----------+
| 10 | 5000.00 |
+--------+----------+
思考一个问题:以上的sql语句执行效率是不是低?
比较低,实际上可以这样考虑:先将大于3000的都找出来,然后再分组。
select
deptno,max(sal)
from
emp
where
sal > 3000
group by
deptno;
+--------+----------+
| deptno | max(sal) |
+--------+----------+
| 10 | 5000.00 |
+--------+----------+
!!!!!!!!!!!!!!!!!优化策略:
where和having,优先选择where,where实在完成不了了,再选择
having。