mysql计算t_employee表领取奖金雇员的平均奖金数

第一步：先查询出每个部门的平均薪资，起别名t ，用作临时表(一会放入子查询)

-- 查询出每个部门的平均薪资，起别名t ，用作临时表(一会放入子查询)

SELECT

e.departmentid,AVG(e.salary) as avgsal

FROM

employee as e

GROUP BY

departmentid

结果图：

最终SQL代码：再为 t 表里的平均薪资查询出对应的薪资等级

-- 用查询出来的每个部门平均薪资表t，去匹配薪资等级表，找出每个部门的平均薪资是属于什么薪资等级

SELECT

t.departmentid,s.grade

FROM

(SELECT

e.departmentid,AVG(e.salary) as avgsal

FROM

employee as e

GROUP BY

e.departmentid) t

INNER JOIN

salgrade s

on

t.avgsal BETWEEN s.losal and s.hisal

ORDER BY

t.departmentid

最终查询后的结果图：

情景2、查询部门中所有人的薪资等级的平均等级

第一步：先查出每个部门里员工对应的薪资等级，起别名为 t ，用作临时表(一会用作子查询)

SELECT

e.*,s.grade

FROM

employee e

INNER JOIN

salgrade s

ON

e.salary BETWEEN s.losal and s.hisal

ORDER BY

departmentid

结果图：

最终SQL语句：再对 t 表里的每个部门员工的薪资等级求均值

SELECT

t.departmentid,AVG(t.grade) as avgra

FROM

(SELECT

e.*,s.grade

FROM

employee e

INNER JOIN

salgrade s

ON

e.salary BETWEEN s.losal and s.hisal

ORDER BY departmentid ) t

GROUP BY t.departmentid

在MySQL中 select 表示查询，select可以单独成句，是MySQL的方言

比如查询系统时间： select now()

now() 表示当前系统时间 MySQL特有的一个方法

在Oracle中系统时间： select sysdate from dual是Oracle的方言

dual表时Oracle特有的一张表，主要目的就是为了补充SQL语句中的from**

在Oracle的语法中，select之后必须要有from

MySQL中有DUAL关键字，所以SELECT NOW()也可以写作SELECT NOW() FROM DUAL

但MySQL中的DUAL不是表 执行SELECT * FROM DUAL会报错，而Oracle中DUAL是一张实际存在的表，执行SELECT * FROM DUAL会查询一个叫做DUMMY的结果

标准语法：select...from...

语法格式：

开发中建议以查询特定列的方式进行查询，前者的执行效率更快

示例1：查询员工编号，员工姓名，年薪 （sal表示的是月薪）

注意年薪的表头显示的是“sal*12”

可以使用as或者空格，为查询结果的列起别名

或

当数据运算中出现null值时，结果一定是null

MySQL解决问题是用ifnull()函数

Oracle解决问题是用nvl()函数

ifnull(expr1,expr2)

nvl(expr1,expr2)

当expr1的结果是null时，使用expr2的结果

当expr1的结果不是null时，使用expr1的结果

示例2： 查询员工姓名和员工的月收入（月薪+奖金）

此时原始数据中如果comm中有null值，查询结果不正确

使用distinct去掉查询结果中的重复记录

基础语法

查询列1，列2直至列n的数据，并去掉其中重复的组合

select ... from...where 查询条件

示例3：查询所有在部门编号是1工作的员工记录

的查询结果是一样的，数据中Tom，tom，TOM，tOm...等都会被查询出

如果需要严格控制大小写字母敏感，查询语句需要在列前加 binary

这样只能查询出TOM

X AND Y 必须同时满足条件X和条件Y

示例4： 查询在1号部门工作，且月薪不低于2500的员工信息

示例5： 查询在1号部门工作，且月薪不低于2500的保洁信息

X OR Y 满足条件X或条件Y其一即可

示例6：查询在1号部门或2号部门工作的员工信息

NOT(X) 不是条件X

主要使用在特殊的比较运算符中

AND语句的优先级高于OR

如果使用括号控制优先级

SQL注入问题：利用了AND和OR优先级破坏系统中的SQL语句逻辑

正常访问逻辑，用户输入admin和123 正确账号和密码可以登录

SQL注入通过输入正确的账号，和一个带有or逻辑的SQL片段将原来逻辑破坏掉，进行非法登录

输入的是 账号：admin 密码：' OR 1=1 AND lname='admin

这个SQL语句就变成了

lname = 'admin' AND lpass = '' -- X 假 查不出结果

1=1 AND lname='admin' -- Y 真 查出admin的数据

X OR Y -- 真 验证通过 访问了admin的账号

X between A and B 表示X在[A,B]之间

相当于 X >= A and X <= B

一般情况下： B应该大于等于A

如果B小于A语法也没错误，逻辑上冲突，不会由查询结果

示例1：查询月薪在[2000,2500]区间的员工信息

示例2：查询在20号部门入职日期在1994年的员工信息

用于查询数据时进行模糊匹配(模糊查询)，只适用于文本查询

语法规则

-ename中含有字符% 使用\做为转义字符

将'%'看做一个%字符

MySQL中

Oracle中

相当于or

语法规则

相当于

示例3：查询在10号部门或20部门工作，且职位是CLERK的员工信息

示例4：查询员工SMITH和AMY的员工信息

示例5：查询在'1992-01-15'或'1994-12-22'或'1994-07-05'入职的员工信息

如果in中的数据有null值，null值不会被查询出

专门针对null值进行筛选

示例6： 查询所有奖金是null的员工信息

不能使用comm = null查询，这样查询没有结果

not表示特殊比较运算符逻辑取反

示例7：查询月薪 不 在[2000,2500]区间的员工信息

示例8：查询员工名字中 不 包含字母A的员工信息

示例9：查询 不 在10号或20号部门工作的，职位是CLERK的员工信息

示例10：查询奖金 不 是null的员工信息

排序是对 查询结果 进行排序显示

排序规则

select...from...where... order by...

示例11：查询所有的员工信息，按照月薪升序排序。

asc表示升序，排序默认就是升序，asc可以省略。

示例12：查询所有的员工信息，按照月薪降序排序。

desc表示降序

在列1的排序基础上，进行列2排序，.... 在之前所有排序基础上进行列n的排序

示例13 ： 查询所有员工信息，按照部门编号升序排序，按照月薪降序排序

先按照deptno进行升序排序，在deptno排序的基础上，再进行sal的降序排序

示例14：查询20号部门员工姓名，月薪，奖金，月收入，按照月收入的降序排序

可以使用列别名

按照查询结果的第3列进行升序排序，在排序基础上以第2列进行降序排序

实际：deptno升序后，sal降序

编写顺序： select...from...where...order by...

执行顺序： from...where...select...order by...

线上出了一个bug：参与集福气活动的很多玩家奖励少发了。

要补发给玩家的奖励可以通过表A和奖励发放表计算得出（需要补发的奖励 = 表A - 实际奖励发放表）。

现在的做法是写个job把这份补发奖励的名单拉取来（没错不等关服维护了，直接在线跑job）。这里要强调一点：只有在“实际奖励发放表”存在的玩家才有可能需要补发奖励。

第三个方法是我用来凑数的，请各位千万不要这么做。除非实际奖励发放表中玩家人数只有个位数。那到底是用第一个还是第二个呢？这个问题主要看两点：

采用方法二：范围查询的方式把数据加到内存中然后再筛选

需要查询的MySQL数据很多（玩家名单list有几万个），使用范围查找能加快查询速度并且即使将表A的数据全部加载在内存中job服务器也是没有压力。

---