我要评论1. 基本查询回顾
1.1 复杂条件查询
-- 查询工资高于500或岗位为manager的雇员，同时满足姓名首字母为大写j select * from emp where (sal > 500 or job = 'manager') and ename like 'j%';
1.2 排序查询
-- 按照部门号升序而雇员的工资降序排序 select * from emp order by deptno, sal desc; -- 使用年薪进行降序排序 select ename, sal * 12 + ifnull(comm, 0) as '年薪' from emp order by 年薪 desc;
1.3 子查询应用
-- 显示工资最高的员工的名字和工作岗位 select ename, job from emp where sal = (select max(sal) from emp); -- 显示工资高于平均工资的员工信息 select ename, sal from emp where sal > (select avg(sal) from emp);
1.4 分组统计
-- 显示每个部门的平均工资和最高工资 select deptno, format(avg(sal), 2), max(sal) from emp group by deptno; -- 显示平均工资低于2000的部门号和它的平均工资 select deptno, avg(sal) as avg_sal from emp group by deptno having avg_sal < 2000; -- 显示每种岗位的雇员总数，平均工资 select job, count(*), format(avg(sal), 2) from emp group by job;
2. 多表查询（重点）
2.1 多表查询的基本概念
实际开发中数据往往来自不同的表，需要进行多表查询。
我们使用公司管理系统中的三张表演示：
emp表：员工信息
dept表：部门信息
salgrade表：工资等级
2.2 笛卡尔积与连接条件
-- 错误的查询：会产生笛卡尔积（14×4=56条记录） select * from emp, dept; -- 正确的多表查询：添加连接条件 select emp.ename, emp.sal, dept.dname from emp, dept where emp.deptno = dept.deptno;
2.3 多表查询示例
-- 显示部门号为10的部门名，员工名和工资 select ename, sal, dname from emp, dept where emp.deptno = dept.deptno and dept.deptno = 10; -- 显示各个员工的姓名，工资，及工资级别 select ename, sal, grade from emp, salgrade where emp.sal between losal and hisal;
3. 自连接查询
3.1 自连接概念
自连接是指在同一张表上进行连接查询，通常用于处理层次关系数据。
3.2 自连接示例
案例：显示员工ford的上级领导的编号和姓名
方法1：使用子查询
select empno, ename from emp where emp.empno = (select mgr from emp where ename = 'ford');
方法2：使用自连接（推荐）
-- 使用表别名区分子查询 select leader.empno, leader.ename from emp leader, emp worker where leader.empno = worker.mgr and worker.ename = 'ford';
自连接技巧：
给同一张表起不同的别名（如leader、worker）
通过别名区分不同角色的数据
性能通常优于子查询
4. 子查询（嵌套查询）
4.1 单行子查询
返回一行记录的子查询
-- 显示smith同一部门的员工 select * from emp where deptno = (select deptno from emp where ename = 'smith');
4.2 多行子查询
返回多行记录的子查询，需要配合特定关键字使用
4.2.1 in关键字
-- 查询和10号部门的工作岗位相同的雇员 -- 但不包含10号部门自己的员工 select ename, job, sal, deptno from emp where job in (select distinct job from emp where deptno = 10) and deptno <> 10;
4.2.2 all关键字
-- 显示工资比部门30的所有员工的工资都高的员工 select ename, sal, deptno from emp where sal > all(select sal from emp where deptno = 30);
4.2.3 any关键字
-- 显示工资比部门30的任意员工的工资高的员工 select ename, sal, deptno from emp where sal > any(select sal from emp where deptno = 30);
关键字区别：
in：等于子查询结果中的任意一个all：比子查询结果中的所有值都...any：比子查询结果中的任意一个值都...
4.3 多列子查询
查询返回多个列数据的子查询
-- 查询和smith的部门和岗位完全相同的所有雇员，不含smith本人 select ename from emp where (deptno, job) = (select deptno, job from emp where ename = 'smith') and ename <> 'smith';
4.4 在from子句中使用子查询
将子查询结果作为临时表使用
案例1：显示每个高于自己部门平均工资的员工
select ename, deptno, sal, format(asal, 2)
from emp, (
select avg(sal) asal, deptno dt
from emp
group by deptno
) tmp
where emp.sal > tmp.asal
and emp.deptno = tmp.dt;案例2：查找每个部门工资最高的人
select emp.ename, emp.sal, emp.deptno, ms
from emp, (
select max(sal) ms, deptno
from emp
group by deptno
) tmp
where emp.deptno = tmp.deptno
and emp.sal = tmp.ms;案例3：显示每个部门的信息和人员数量
方法1：使用多表连接
select dept.dname, dept.deptno, dept.loc, count(*) as '部门人数' from emp, dept where emp.deptno = dept.deptno group by dept.deptno, dept.dname, dept.loc;
方法2：使用子查询（推荐）
select dept.deptno, dname, mycnt, loc
from dept, (
select count(*) mycnt, deptno
from emp
group by deptno
) tmp
where dept.deptno = tmp.deptno;5. 合并查询
5.1 union操作符
取得两个结果集的并集，自动去掉重复行
-- 将工资大于2500或职位是manager的人找出来 select ename, sal, job from emp where sal > 2500 union select ename, sal, job from emp where job = 'manager';
5.2 union all操作符
取得两个结果集的并集，不会去掉重复行
-- 将工资大于2500或职位是manager的人找出来（包含重复记录） select ename, sal, job from emp where sal > 2500 union all select ename, sal, job from emp where job = 'manager';
5.3 union vs union all
特性 | union | union all |
|---|---|---|
去重 | 自动去掉重复行 | 保留所有行 |
性能 | 较慢（需要去重） | 较快 |
排序 | 结果集自动排序 | 不保证顺序 |
使用场景 | 需要唯一结果时 | 需要完整结果时 |
6. 实战技巧与性能优化
6.1 查询执行顺序理解
-- 理解sql执行顺序 select deptno, avg(sal) as avg_sal -- 5. 选择字段 from emp -- 1. 数据源 where sal > 1000 -- 2. 条件过滤 group by deptno -- 3. 分组 having avg_sal > 2000 -- 4. 分组后过滤 order by avg_sal desc; -- 6. 排序
6.2 性能优化建议
连接条件优先：多表查询时先写连接条件，再写过滤条件
合理使用索引：连接字段和常用查询字段建立索引
避免select*：只选择需要的字段
子查询优化：能用连接查询尽量不用子查询
分页查询：大数据量时使用limit分页
6.3 复杂查询调试技巧
-- 分步调试复杂查询 -- 步骤1：先验证子查询结果 select deptno from emp where ename = 'smith'; -- 步骤2：再验证主查询 select * from emp where deptno = 20; -- 步骤3：组合成完整查询 select * from emp where deptno = (select deptno from emp where ename = 'smith');
7. 实战oj题目示例
7.1 牛客网典型题目
-- 查找所有员工入职时候的薪水情况
select e.emp_no, s.salary
from employees e, salaries s
where e.emp_no = s.emp_no
and e.hire_date = s.from_date
order by e.emp_no desc;
-- 获取所有非manager的员工emp_no
select emp_no
from employees
where emp_no not in (
select emp_no from dept_manager
);
-- 获取所有员工当前的manager
select e.emp_no, m.emp_no as manager_no
from dept_emp e, dept_manager m
where e.dept_no = m.dept_no
and e.to_date = '9999-01-01'
and m.to_date = '9999-01-01';8. 总结
8.1 查询类型选择指南
场景 | 推荐查询方式 | 理由 |
|---|---|---|
简单单表查询 | 基本select | 性能最好 |
多表关联查询 | 多表连接 | 直观易懂 |
层次关系查询 | 自连接 | 性能优于子查询 |
存在性检查 | exists子查询 | 效率高 |
结果集合并 | union/union all | 根据去重需求选择 |
8.2 最佳实践
明确需求：先分析需要什么数据，来自哪些表
选择最优方案：根据数据量和关系选择查询方式
分步验证：复杂查询先验证各部分结果
性能测试：大数据量时测试查询性能
代码可读性：合理使用别名和格式化
掌握复合查询是mysql数据库开发的核心技能，通过大量实践可以熟练运用各种查询技巧，编写出高效、可维护的sql语句。
9. 总结
以上为个人经验，希望能给大家一个参考，也希望大家多多支持代码网。
发表评论