Mysql - 04

目录

1   null值处理函数

2   加密

3   分支判断

4   多行函数、聚合函数

4.1     多行函数分组计算

4.2     having 对多行函数结果过滤

5   分页查询

6   子查询

6.1     条件子查询

6.2     行内视图子查询

6.3     字段子查询

7   联合查询

---

1   null值处理函数

ifnull(1,2)

   判断参数1是否为null，

   不是null返回第1项

   是null返回第2项

coalesce(1,2,3,4,5.....)

   从左到右找到第一个非null值返回

 

null值函数测试

求年薪加提成

select

employee_id,first_name,

salary*12*(1+

ifnull(commission_pct,0)) sal

from employees;

---

2   加密

md5()

   单向加密，不能解密

   任何数据加密城32位16进制数字字符串

一般用来对用户的密码进行加密

sha()

   单向加密，不能解密

  

加密函数测试

select md5('1234');

select sha('632sdasdqsadtqwe');

---

3   分支判断

if(1,2,3)

   判断1的真假

   真，运算2

   假，运算3

 

case

   when 条件1 then ...

   when 条件2 then ...

   else ...

end

 

 

case 数据

   when 值1 then ...

   when 值2 then ...

   else ...

end

 

分支判断测试

>=15000 显示有钱

>=8000 小康

>=3000 温饱

否则 穷逼

select

employee_id,first_name,salary,

case

   when salary>=15000 then '有钱'

   when salary>=8000 then '小康'

   when salary>=3000 then '温饱'

   else '穷逼'

end c

from employees

order by salary desc;

---

4   多行函数、聚合函数

多行数据，求出一个结果

 

五个多行函数：

l  sum()

l  avg()

l  max()

l  min()

l  count()

 

l  多行函数不能和其他字段一起查询

u  求工资平均值

select avg(salary) from ...

select first_name,avg(salary) from ...

      错误查询，其他数据库中，不允许执行，会直接报错，

      mysql可以执行，把第一条数据的first_name，放在结果中，毫无意义

l  多行函数，会忽略null值

l  count(字段) 非空值行数，重复的值会重复计数

可以用 distinct 去除重复

count(distinct 字段)

l  count(*) 数据行数

l  count(*) 数据量大时(>千万)，效率低  

多行函数测试

求最低工资

select first_name, min(salary)

from employees;

 

求工资最低、最高、平均、求和、计数

select

   min(salary),

   max(salary),

   avg(salary),

   sum(salary),

   count(salary)

from employees;

 

对commission_pct计数

select count(commission_pct)

from employees;

 

对commission_pct求平均

select avg(commission_pct),

avg(ifnull(commission_pct,0)),

sum(commission_pct)/35,

sum(commission_pct)/107

from employees;

 

有多少部门

select count(distinct department_id)

from employees;

 

有多少主管

select count(distinct manager_id)

from employees;

 

1997年入职人数

select count(*) from employees

where

extract(year from hire_date)=1997;

 

50部门人数

select count(*) from employees

where department_id=50;

 

最后的入职时间

select max(hire_date) from employees;

 

员工122是一个主管，求他手下人数

select count(*) from employees

where manager_id=122;

 

课程表的行数

use db1;

select count(*) from kecheng;

4.1     多行函数分组计算

按指定字段相同的值分成一组，每一组分别执行多行函数计算

 

  指定的分组字段，可以和多行函数一起查询

l  group by a

按a字段相同值分组

l  group by a,b

按a,b字段组合相同值分组

l  group by 必须和多行函数一起使用

多行函数分组计算测试

use hr;

每个部门的人数

select department_id,count(*) c

from employees

where department_id is not null

group by department_id

order by c;

 

每个部门的平均工资

select department_id,avg(salary) a

from employees

where department_id is not null

group by department_id

order by a;

 

每个工作岗位的最大工资

select job_id,max(salary) m

from employees

group by job_id

order by m desc;

 

每个部门中，每个岗位的人数

select department_id,job_id,count(*) c

from employees

where department_id is not null

group by department_id,job_id

order by c;

 

每一年入职的人数，按年排序

select

extract(year from hire_date) year,

count(*)

from employees

group by year

order by year;

 

每个部门每个主管的手下人数，按人数降序

select

department_id,manager_id,count(*) c

from employees

where

department_id is not null and

manager_id is not null

group by department_id,manager_id

order by c;

4.2     having 对多行函数结果过滤

 having 对多行函数分组计算结果进行过滤

在分组、求多行函数之后，才执行having过滤

与 where 过滤不同：

 where 执行时间在最初执行

普通条件过滤用 where

多行函数结果过滤用 having

having 跟在group by 之后

having 后面可以做普通条件过滤，但不应该这样做

 

having 测试

只有1人入职的年份

select

extract(year from hire_date) year,

count(*) c

from employees

group by year

having c=1

order by year;

只有1人的部门

select department_id,count(*) c

from employees

where department_id is not null

group by department_id

having c=1

order by c;

平均工资>8000的工作岗位

select job_id, avg(salary) a

from employees

group by job_id

having a>8000

order by a desc;

5   分页查询

sql语法规范中，没有分页查询的规范语法，各数据库厂商都有自己的分页语法扩展，这些分页语法，不能通用

l  mysql 分页使用 limit关键字

l  limit 10 

u  前10条

l  limit 0,10 

u  从第1条开始的10条

l  limit 10,10

u  从第 11 条开始的 10 条

l  limit 100,10

u  从第101条开始的10条

l  数据量大时(>千万)，效率低

l  oracal 分页

使用 oracle的特殊列 rownum

select * from

       (select *,rownum R from

          (select * from a)

       where rownum<=1000)

   where R>990;

分页测试

select employee_id,first_name,salary

from employees limit 20;

select employee_id,first_name,salary

from employees limit 20,20;

select employee_id,first_name,salary

from employees limit 100,20;

use db1;

select * from kecheng limit 20;

select *

from kecheng limit 1000000,20;

select *

from kecheng limit 50000000,20;

---

6   子查询

嵌套的查询

 条件子查询

行内视图子查询

字段子查询

6.1     条件子查询

一个查询的结果，作为另一个查询的过滤条件

select * from a where c=(select ...)

l  单值子查询

=

> >= < <=

l  多值子查询

in

> all  大于最大值

   > any  大于最小值

l  多列子查询

(c,d)=(select x,y ...)

(c,d) in(select x,y ...)

 

条件子查询测试

拿最低工资的所有员工

1)  最低工资值

2)  用这个工资值过滤查询员工

select min(salary) from employees

 

select employee_id,first_name,salary

from employees

where salary=(

select min(salary) from employees

);

工资低于平均工资

1)  平均工资

2)  用平均工资过滤查询员工

 

select avg(salary) from employees

 

select employee_id,first_name,salary

from employees

where salary<( 

select avg(salary) from employees

) ;

 

只有一个人的部门中的员工

1)  只有一人的部门编号

2)  用部门编号过滤员工

select department_id

from employees

where department_id is not null

group by department_id

having count(*)=1

 

select employee_id, first_name,salary

from employees

where department_id in(

select department_id

from employees

where department_id is not null

group by department_id

having count(*)=1

);

 

每个部门拿最低工资的人

1)  部门分组查最低工资

2)  用部门和工资过滤员工

 

select department_id,min(salary)

from employees

where department_id is not null

group by department_id

 

select employee_id,first_name,salary,

department_id

from employees

where (department_id,salary) in(

select department_id,min(salary)

from employees

where department_id is not null

group by department_id

)

order by department_id,salary;

 

 

 

6.2     行内视图子查询

从一个查询的结果当中，再查询

select * from (select ...) T

l  mysql的行内视图，必须起别名

l  表的别名或行内视图的别名，可以在where 中使用

行内视图子查询测试

下属人数最多的主管id

1)  按主管分组查人数

2)  得到人数最大值

3)  用人数过滤查主管id

select manager_id,count(*) c

from employees

where manager_id is not null

group by manager_id

 

select max(c) from (

select manager_id,count(*) c

from employees

where manager_id is not null

group by manager_id

) T

 

select manager_id,count(*) c

from employees

where manager_id is not null

group by manager_id

having c=(

select max(c) from (

select manager_id,count(*) c

from employees

where manager_id is not null

group by manager_id

) T

);

 

进一步，查询这些主管的信息

select employee_id,first_name,salary

from employees

where employee_id in(

select manager_id

from employees

where manager_id is not null

group by manager_id

having count(*)=(

select max(c) from (

select manager_id,count(*) c

from employees

where manager_id is not null

group by manager_id

) T

)

);

 

6.3     字段子查询

查询结果，作为另一个查询中的一列

select a,b,(select ...) x from ...

字段子查询测试

员工信息后面，添加一列平均工资

select employee_id,first_name,salary,

(select avg(salary) from employees) a

from employees;

---

7   联合查询

把多个查询的结果，联合成一个查询结果

l  union
(select ....)

union

(select ....);

 

u  会去除重复的行

l  union all

u  保留重复的行

 

Union 联合查询测试

工资最高的5个员工和工资最低的5个员工

()

union

();