MySQL③准备工作假设有以下两张表-- 部门表 departmentscreatetabledepartments(dept_idintprimarykey,dept_namevarchar(50)notnull,locationvarchar(100));-- 员工表 employeescreatetableemployees(emp_idintprimarykey,emp_namevarchar(50)notnull,dept_idint,salarydecimal(10,2),hire_datedate,bonusdecimal(10,2),foreignkey(dept_id)referencesdepartments(dept_id));-- 部门数据insertintodepartments(dept_id,dept_name,location)values(10,技术部,北京);insertintodepartments(dept_id,dept_name,location)values(20,市场部,上海);insertintodepartments(dept_id,dept_name,location)values(30,财务部,广州);insertintodepartments(dept_id,dept_name,location)values(40,人事部,深圳);insertintodepartments(dept_id,dept_name,location)values(50,运营部,杭州);-- 员工数据20条insertintoemployees(emp_id,emp_name,dept_id,salary,hire_date,bonus)values(1001,张伟,10,12000.00,2020-03-15,2000.00);insertintoemployees(emp_id,emp_name,dept_id,salary,hire_date,bonus)values(1002,李娜,10,9500.00,2021-06-20,1500.00);insertintoemployees(emp_id,emp_name,dept_id,salary,hire_date,bonus)values(1003,王强,10,8500.00,2022-01-10,1000.00);insertintoemployees(emp_id,emp_name,dept_id,salary,hire_date,bonus)values(1004,刘洋,10,7800.00,2022-08-05,800.00);insertintoemployees(emp_id,emp_name,dept_id,salary,hire_date,bonus)values(1005,陈敏,10,6800.00,2023-03-01,500.00);insertintoemployees(emp_id,emp_name,dept_id,salary,hire_date,bonus)values(1006,赵磊,20,11000.00,2019-11-01,2500.00);insertintoemployees(emp_id,emp_name,dept_id,salary,hire_date,bonus)values(1007,孙悦,20,9000.00,2020-09-15,1800.00);insertintoemployees(emp_id,emp_name,dept_id,salary,hire_date,bonus)values(1008,周慧,20,8200.00,2021-12-20,1200.00);insertintoemployees(emp_id,emp_name,dept_id,salary,hire_date,bonus)values(1009,吴迪,20,7500.00,2022-07-11,900.00);insertintoemployees(emp_id,emp_name,dept_id,salary,hire_date,bonus)values(1010,郑爽,20,6500.00,2023-02-14,400.00);insertintoemployees(emp_id,emp_name,dept_id,salary,hire_date,bonus)values(1011,钱华,30,10000.00,2019-08-20,2200.00);insertintoemployees(emp_id,emp_name,dept_id,salary,hire_date,bonus)values(1012,冯静,30,8800.00,2020-12-01,1600.00);insertintoemployees(emp_id,emp_name,dept_id,salary,hire_date,bonus)values(1013,蒋明,30,8000.00,2021-10-15,1100.00);insertintoemployees(emp_id,emp_name,dept_id,salary,hire_date,bonus)values(1014,韩雪,30,7200.00,2022-09-20,700.00);insertintoemployees(emp_id,emp_name,dept_id,salary,hire_date,bonus)values(1015,沈鹏,30,6200.00,2023-06-01,null);insertintoemployees(emp_id,emp_name,dept_id,salary,hire_date,bonus)values(1016,魏星,40,9500.00,2020-05-10,1400.00);insertintoemployees(emp_id,emp_name,dept_id,salary,hire_date,bonus)values(1017,褚丹,40,7800.00,2021-08-25,1000.00);insertintoemployees(emp_id,emp_name,dept_id,salary,hire_date,bonus)values(1018,卫东,40,7000.00,2022-11-30,null);insertintoemployees(emp_id,emp_name,dept_id,salary,hire_date,bonus)values(1019,任小强,50,8800.00,2020-07-07,1300.00);insertintoemployees(emp_id,emp_name,dept_id,salary,hire_date,bonus)values(1020,姚丽,50,7600.00,2021-04-18,800.00);sql练习题1.查询所有员工的姓名和工资。2.查询所有员工信息按照工资从高到低排序。3.查询工资大于8000的所有员工的姓名和工资。4.查询部门编号为10或20的所有员工的姓名和部门编号。5.查询工资在5000到10000之间包含边界的员工姓名和工资。6.查询姓名以王开头的所有员工信息。7.查询姓名中包含小字的所有员工姓名。8.查询姓名为两个字的员工姓名和工资。9.查询奖金bonus为空的员工姓名和部门编号。10.查询奖金不为空的员工姓名和奖金金额。11.查询部门编号为30且工资大于7000的员工姓名、工资和部门编号。12.查询每个部门的员工人数和平均工资。13.查询每个部门的最高工资。14.查询员工人数大于3的部门编号和员工人数。15.查询平均工资大于8000的部门编号和平均工资。16.查询工资最高的前3名员工的姓名和工资。17.查询每个员工及其所在部门的名称使用内连接。18.查询所有部门及其员工信息要求显示所有部门即使没有员工使用左外连接。19.查询每个部门的名称、员工人数和平均工资。20.查询工资高于公司平均工资的员工姓名、工资和部门名称使用子查询。#1selectemp_name,salaryfromemployees;#2select*fromemployeesorderbysalarydesc;#3selectemp_name,salaryfromemployeeswheresalary8000;#4/* select emp_name,d.dept_id from employees join departments d on employees.dept_id d.dept_id where d.dept_id 10 or 20; IN 等于其中某一个离散集合 BETWEEN 在这个区间内连续范围 */selectemp_name,dept_idfromemployeeswheredept_idin(10,20);#5selectemp_name,salaryfromemployeeswheresalarybetween5000and10000;#6select*fromemployeeswhereemp_namelike王%;#7selectemp_namefromemployeeswhereemp_namelike%小%;#8selectemp_name,salaryfromemployeeswhereemp_namelike__;#9selectemp_name,dept_idfromemployeeswherebonusisnull;#10selectemp_name,bonusfromemployeeswherebonusisnotnull;#11selectemp_name,salary,dept_idfromemployeeswheredept_id30andsalary7000;#12selectdept_id,count(*),avg(salary)fromemployeesgroupbydept_id;#13selectdept_id,max(salary)fromemployeesgroupbydept_id;#14/* select dept_id, count(*) as c from employees where c 3 ; WHERE 子句中不能引用 SELECT 中定义的别名。 WHERE 的执行顺序在 SELECT 之前此时别名 c 还未定义会报错。 */selectdept_id,count(*)ascfromemployeesgroupbydept_idhavingc3;#15selectdept_id,avg(salary)asafromemployeesgroupbydept_idhavinga8000;#16selectemp_name,salaryfromemployeesorderbysalarydesclimit3;#17selectemp_name,dept_namefromemployeesjoindepartments dond.dept_idemployees.dept_id;#18/* select d.dept_id,dept_name,emp_name from employees left join departments d on d.dept_id employees.dept_id; 左连接部门表应该作为主表而不是e表 */selectd.dept_name,e.dept_id,e.emp_namefromdepartments dleftjoinemployees eond.dept_ide.dept_id;#19selectd.dept_name,count(e.emp_name)as人数,avg(salary)as平均工资fromemployees ejoindepartments dond.dept_ide.dept_idgroupbyd.dept_id;#可读性更好的19selectd.dept_name,count(e.emp_name)as人数,avg(e.salary)as平均工资fromemployees ejoindepartments dond.dept_ide.dept_idgroupbyd.dept_id,d.dept_name;#20selectemp_name,salary,dept_namefromemployeesjoindepartments dond.dept_idemployees.dept_idwheresalary(selectavg(salary)fromemployees);错误类型题号 错误类型4 or 条件写法不完整7 关键字写错“王” vs “小”12 缺少别名小问题13 WHERE 中引用别名 逻辑错误14 WHERE 中引用别名重复错18 主表选反了左连接方向错误核心知识点回顾or 条件要写完整col 1 or col 2WHERE 中不能用 SELECT 定义的别名分组后用 HAVING 筛选不用 WHERE左连接以哪个表为主取决于 FROM 后面的表