SQLtest 01-07
查询所有员工的姓名、工资以及所在部门的名称。
查询每个部门的员工人数和平均工资。
查询工资高于公司平均工资的员工姓名、工资和部门名称（使用子查询）。
查询每个部门中工资最高的员工的姓名、工资和部门名称（使用子查询作为临时表）。
查询每个员工的姓名、工资，并使用case when将工资分类：高于10000为"高"，8000-10000为"中"，低于8000为"低"。
查询每个员工的信息，包括工龄（当前日期减去入职日期，以年为单位）。
查询所有员工姓名，将姓名转换为大写，并连接部门名称，格式为：“姓名-部门名称”。
#1selecte.emp_name,e.salary,d.dept_namefromemployees ejoindepartments done.dept_id=d.dept_id;#2selectd.dept_name,count(*),avg(e.salary)fromemployees ejoindepartments dond.dept_id=e.dept_idgroupbyd.dept_name;#3selecte.emp_name,e.salary,d.dept_namefromemployees ejoindepartments dond.dept_id=e.dept_idwheree.salary>(selectavg(employees.salary)fromemployees);#where 不能用函数 只能间接的使用select from 这种套娃叫做子查询selectavg(employees.salary)fromemployees;#4selectemployees.dept_id,max(employees.salary)asmaxsfromemployeesgroupbydept_id;selecte.emp_name,e.salary,d.dept_namefromemployees ejoindepartments dond.dept_id=e.dept_idjoin(selectdept_id,max(salary)asmaxsfromemployeesgroupbydept_id)asmaxdonmaxd.dept_id=e.dept_idandmaxd.maxs=e.salary;#5selecte.emp_name,e.salary,casewhene.salary>10000then'高'whene.salary>8000then'中'else'低'endas'锐评'fromemployees e;#6select*,timestampdiff(year,employees.hire_date,current_date)as'工龄'fromemployees;#7selectconcat(upper(e.emp_name),'-',d.dept_name)as'搞搞新意思'fromemployees ejoindepartments dond.dept_id=e.dept_id;序号 复盘要点1聚合函数不能直接放在WHERE中，需借助子查询2子查询是独立作用域，不能使用外部表的别名3子查询作为临时表（派生表）必须加别名4GROUPBY按什么分组，结果就按什么维度聚合5CASEWHEN结构要完整：有END，字符串有引号6工龄计算用 TIMESTAMPDIFF 最准确7CONCAT 和 UPPER 实现字符串拼接与大小写转换8日期函数：CURDATE()、TIMESTAMPDIFF()以下是修正过程：
1.独立王国（子查询）不可以使用别名.属性
第一次尝试：
sqlselecte.emp_name,e.salary,d.dept_namefromemployees ejoindepartments dond.dept_id=e.dept_idjoin(selecte.dept_id,max(e.salary)asmaxs-- ❌ 错误1：子查询中用了外部别名 efrome-- ❌ 错误2：子查询中用了外部别名 e 作为表名groupbye.dept_id-- ❌ 错误3：子查询中用了外部别名 e)asmaxdonmaxd.dept_id=e.dept_idandmaxd.maxs=e.salary;报错信息：Table'test05.e'doesn't exist第二次尝试：
sqlselecte.emp_name,e.salary,d.dept_namefromemployees ejoindepartments dond.dept_id=e.dept_idjoin(selecte.dept_id,max(e.salary)asmaxs-- ❌ 错误：子查询中仍然用了外部别名 efromemployees-- ✅ 表名改对了groupbye.dept_id-- ❌ 错误：子查询中仍然用了外部别名 e)asmaxdonmaxd.dept_id=e.dept_idandmaxd.maxs=e.salary;报错信息： Unknowncolumn'e.dept_id'in'field list'二、修正后的正确代码
sqlselecte.emp_name,e.salary,d.dept_namefromemployees ejoindepartments dond.dept_id=e.dept_idjoin(selectdept_id,max(salary)asmaxs-- ✅ 去掉所有 e.，直接写列名fromemployees-- ✅ 用完整的表名groupbydept_id-- ✅ 直接写列名)asmaxdonmaxd.dept_id=e.dept_idandmaxd.maxs=e.salary;三、总结
要点 说明
子查询是独立作用域 子查询 ( ) 内部看不到外部定义的别名（如 e），只能看到自己 from 中的表
外部别名只在外部有效 from employees e 定义的 e 只在外部查询有效，子查询内部不能使用
子查询内部要写完整表名 如 from employees，不能用 from e
子查询内部列名不加前缀 直接写 dept_id、salary 即可，因为子查询只有这一张表，不会产生歧义
外部查询中可以用别名 e. 在子查询 外面，e.emp_name、e.salary、e.dept_id 都是有效的
四、记忆口诀
子查询里别用外别名，表名列名直接写；
外部别名管外部，内外作用域不互通。
