SQLtest 01-07查询所有员工的姓名、工资以及所在部门的名称。查询每个部门的员工人数和平均工资。查询工资高于公司平均工资的员工姓名、工资和部门名称使用子查询。查询每个部门中工资最高的员工的姓名、工资和部门名称使用子查询作为临时表。查询每个员工的姓名、工资并使用case when将工资分类高于10000为高8000-10000为中低于8000为低。查询每个员工的信息包括工龄当前日期减去入职日期以年为单位。查询所有员工姓名将姓名转换为大写并连接部门名称格式为“姓名-部门名称”。#1selecte.emp_name,e.salary,d.dept_namefromemployees ejoindepartments done.dept_idd.dept_id;#2selectd.dept_name,count(*),avg(e.salary)fromemployees ejoindepartments dond.dept_ide.dept_idgroupbyd.dept_name;#3selecte.emp_name,e.salary,d.dept_namefromemployees ejoindepartments dond.dept_ide.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_ide.dept_idjoin(selectdept_id,max(salary)asmaxsfromemployeesgroupbydept_id)asmaxdonmaxd.dept_ide.dept_idandmaxd.maxse.salary;#5selecte.emp_name,e.salary,casewhene.salary10000then高whene.salary8000then中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_ide.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_ide.dept_idjoin(selecte.dept_id,max(e.salary)asmaxs-- ❌ 错误1子查询中用了外部别名 efrome-- ❌ 错误2子查询中用了外部别名 e 作为表名groupbye.dept_id-- ❌ 错误3子查询中用了外部别名 e)asmaxdonmaxd.dept_ide.dept_idandmaxd.maxse.salary;报错信息Tabletest05.edoesnt exist第二次尝试sqlselecte.emp_name,e.salary,d.dept_namefromemployees ejoindepartments dond.dept_ide.dept_idjoin(selecte.dept_id,max(e.salary)asmaxs-- ❌ 错误子查询中仍然用了外部别名 efromemployees-- ✅ 表名改对了groupbye.dept_id-- ❌ 错误子查询中仍然用了外部别名 e)asmaxdonmaxd.dept_ide.dept_idandmaxd.maxse.salary;报错信息 Unknowncolumne.dept_idinfield list二、修正后的正确代码sqlselecte.emp_name,e.salary,d.dept_namefromemployees ejoindepartments dond.dept_ide.dept_idjoin(selectdept_id,max(salary)asmaxs-- ✅ 去掉所有 e.直接写列名fromemployees-- ✅ 用完整的表名groupbydept_id-- ✅ 直接写列名)asmaxdonmaxd.dept_ide.dept_idandmaxd.maxse.salary;三、总结要点 说明子查询是独立作用域 子查询 ( ) 内部看不到外部定义的别名如 e只能看到自己 from 中的表外部别名只在外部有效 from employees e 定义的 e 只在外部查询有效子查询内部不能使用子查询内部要写完整表名 如 from employees不能用 from e子查询内部列名不加前缀 直接写 dept_id、salary 即可因为子查询只有这一张表不会产生歧义外部查询中可以用别名 e. 在子查询 外面e.emp_name、e.salary、e.dept_id 都是有效的四、记忆口诀子查询里别用外别名表名列名直接写外部别名管外部内外作用域不互通。