数据库知识复习03

第三部分 MySQL DQL 数据查询语言1 数据库的 DQL数据查询语言DQLData Query Language数据查询语言是 SQL 中最核心、使用频率最高的语言类型核心关键字为SELECT用于从数据库表中精准检索数据。日常业务中常见的查询场景如基本数据查询、条件筛选、结果排序、分页显示等均依赖 DQL 实现。1.1 SELECT 语法格式SELECT {* | 字段列名} -- 要查询的字段*表示所有字段 FROM 表1, 表2… -- 查询数据的来源表可多个 [WHERE 表达式] -- 可选查询条件过滤 [GROUP BY 分组字段] -- 可选按指定字段分组 [HAVING 表达式] -- 可选过滤分组后的结果 [ORDER BY 排序字段] -- 可选对结果排序 [LIMIT [offset,] row count] -- 可选限制返回条数1.2 环境准备测试数据导入在执行查询练习前先导入测试用 SQL 文件包含emp员工表、dept部门表、salgrade薪资等级表三张核心表-- 导入本地 SQL 文件需指定文件实际路径 mysql source d:\emp.sql; -- 验证表是否导入成功 mysql show tables; ---------------- | Tables_in_test | ---------------- | dept | | emp | | salgrade | ---------------- 3 rows in set (0.00 sec)测试表数据预览1. 员工表empmysql select * from emp; ---------------------------------------------------------------------- | empno | ename | job | mgr | hiredate | sal | comm | deptno | ---------------------------------------------------------------------- | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 | | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 | | 7788 | SCOTT | ANALYST | 7566 | 1987-07-13 | 3000.00 | NULL | 20 | | 7839 | KING | PRESIDENT | NULL | 1981-11-07 | 5000.00 | NULL | 10 | | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | | 7876 | ADAMS | CLERK | 7788 | 1987-07-13 | 1100.00 | NULL | 20 | | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 | | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | | 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 | ---------------------------------------------------------------------- 14 rows in set (0.00 sec)2. 部门表deptmysql select * from dept; ------------------------------ | deptno | dname | loc | ------------------------------ | 10 | ACCOUNTING | NEW YORK | | 20 | RESEARCH | DALLAS | | 30 | SALES | CHICAGO | | 40 | OPERATIONS | BOSTON | ------------------------------ 4 rows in set (0.00 sec)3. 薪资等级表salgrademysql select * from salgrade; --------------------- | grade | losal | hisal | --------------------- | 1 | 700 | 1200 | | 2 | 1201 | 1400 | | 3 | 1401 | 2000 | | 4 | 2001 | 3000 | | 5 | 3001 | 9999 | --------------------- 5 rows in set (0.00 sec)1.3 基本查询1.3.1 查询指定字段1. 查询所有字段两种方式方式 1使用*通配符推荐简洁高效自动返回表中所有字段字段顺序与表定义一致无需手动输入所有字段名。-- 查询部门表所有字段 mysql select * from dept; ------------------------------ | deptno | dname | loc | ------------------------------ | 10 | ACCOUNTING | NEW YORK | | 20 | RESEARCH | DALLAS | | 30 | SALES | CHICAGO | | 40 | OPERATIONS | BOSTON | ------------------------------注意非必要不使用*若表字段较多查询无关字段会降低效率仅在需要所有字段或未知字段名称时使用。方式 2手动列出所有字段灵活可自定义字段顺序适合需要调整字段显示顺序的场景需准确输入字段名可通过DESC 表名查看字段结构。-- 自定义字段顺序loc 在前dname 在后 mysql select loc, dname, deptno from dept; ------------------------------ | loc | dname | deptno | ------------------------------ | NEW YORK | ACCOUNTING | 10 | | DALLAS | RESEARCH | 20 | | CHICAGO | SALES | 30 | | BOSTON | OPERATIONS | 40 | ------------------------------2. 查询指定单个 / 多个字段精准查询所需字段避免冗余数据提升查询效率。-- 查询单个字段部门名称 mysql select dname from dept; ------------ | dname | ------------ | ACCOUNTING | | RESEARCH | | SALES | | OPERATIONS | ------------ -- 查询多个字段员工姓名、职位、薪资 mysql select ename, job, sal from emp; ---------------------------- | ename | job | sal | ---------------------------- | SMITH | CLERK | 800.00 | | ALLEN | SALESMAN | 1600.00 | | WARD | SALESMAN | 1250.00 | | ... | ... | ... | ----------------------------1.3.2 去重查询DISTINCT当表中字段无唯一性约束时可能存在重复值使用DISTINCT关键字过滤重复记录仅保留一条。语法SELECT DISTINCT 字段名1[, 字段名2...] FROM 表名;关键注意事项DISTINCT必须放在所有查询字段的最前面若指定多个字段将按字段组合去重仅当所有字段值完全相同时才去重仅返回去重后的目标字段无法返回其他非目标字段。案例-- 单字段去重查询所有不重复的职位原表14条记录去重后5条 mysql select distinct job from emp; ----------- | job | ----------- | CLERK | | SALESMAN | | MANAGER | | ANALYST | | PRESIDENT | ----------- -- 多字段组合去重查询不重复的“职位部门编号”组合按组合去重 mysql select distinct job, deptno from emp; ------------------- | job | deptno | ------------------- | CLERK | 20 | | SALESMAN | 30 | | MANAGER | 20 | | MANAGER | 30 | | MANAGER | 10 | | ANALYST | 20 | | PRESIDENT | 10 | | CLERK | 30 | | CLERK | 10 | -------------------1.3.3 设置别名AS为表或字段指定别名简化查询语句使结果显示更直观。AS关键字可省略直接用空格分隔原名称与别名。1. 字段别名用于优化结果显示-- 为字段设置中文别名AS 可省略 mysql select grade as 薪资等级, losal 最低薪资, hisal 最高薪资 from salgrade; -------------------------------- | 薪资等级 | 最低薪资 | 最高薪资 | -------------------------------- | 1 | 700 | 1200 | | 2 | 1201 | 1400 | | 3 | 1401 | 2000 | | 4 | 2001 | 3000 | | 5 | 3001 | 9999 | --------------------------------2. 表别名用于多表查询或简化表名-- 为 salgrade 表设置别名 salAS 可省略 mysql select sal.grade, sal.losal from salgrade sal; -------------- | grade | losal | -------------- | 1 | 700 | | 2 | 1201 | | 3 | 1401 | | 4 | 2001 | | 5 | 3001 | --------------注意表别名仅在当前查询中有效不修改数据库中表的实际名称别名不能与数据库中其他表名重复。1.3.4 限制查询结果条数LIMIT当表数据量较大时使用LIMIT限制返回条数减轻数据库压力提升响应速度。支持三种使用方式1. 不指定初始位置从第一条记录开始语法LIMIT 记录数等价于LIMIT 0, 记录数-- 查询前2条薪资等级数据 mysql select * from salgrade limit 2; --------------------- | grade | losal | hisal | --------------------- | 1 | 700 | 1200 | | 2 | 1201 | 1400 | --------------------- -- 若记录数大于实际数据量返回所有记录 mysql select * from salgrade limit 6; --------------------- | grade | losal | hisal | --------------------- | 1 | 700 | 1200 | | 2 | 1201 | 1400 | | 3 | 1401 | 2000 | | 4 | 2001 | 3000 | | 5 | 3001 | 9999 | ---------------------2. 指定初始位置从指定索引开始语法LIMIT 初始位置, 记录数初始位置从 0 开始第一条记录位置为 0-- 从第3条记录开始位置2查询3条数据 mysql select * from salgrade limit 2, 3; --------------------- | grade | losal | hisal | --------------------- | 3 | 1401 | 2000 | | 4 | 2001 | 3000 | | 5 | 3001 | 9999 | ---------------------3. 与 OFFSET 组合更直观的初始位置指定语法LIMIT 记录数 OFFSET 初始位置功能与方式 2 一致仅语法顺序不同-- 从位置3开始查询2条数据等价于 LIMIT 3, 2 mysql select * from salgrade limit 2 offset 3; --------------------- | grade | losal | hisal | --------------------- | 4 | 2001 | 3000 | | 5 | 3001 | 9999 | ---------------------1.3.5 查询结果排序ORDER BY默认情况下查询结果按数据插入顺序显示使用ORDER BY可按指定字段排序支持升序ASC和降序DESC默认升序。语法SELECT 字段名 FROM 表名 ORDER BY 排序字段1 [ASC|DESC], 排序字段2 [ASC|DESC]...;关键注意事项可指定多个排序字段按字段顺序依次排序前一个字段值相同时按后一个字段排序排序字段包含 NULL 值时NULL 视为最小值ORDER BY必须放在查询语句的最后LIMIT 除外。案例-- 单字段升序默认 ASC按薪资从低到高 mysql select ename, sal from emp order by sal; ----------------- | ename | sal | ----------------- | SMITH | 800.00 | | JAMES | 950.00 | | ADAMS | 1100.00 | | ... | ... | ----------------- -- 单字段降序DESC按最低薪资从高到低 mysql select * from salgrade order by losal desc; --------------------- | grade | losal | hisal | --------------------- | 5 | 3001 | 9999 | | 4 | 2001 | 3000 | | 3 | 1401 | 2000 | | 2 | 1201 | 1400 | | 1 | 700 | 1200 | --------------------- -- 多字段排序先按薪资升序薪资相同时按入职日期升序 mysql select ename, sal, hiredate from emp order by sal, hiredate; ----------------------------- | ename | sal | hiredate | ----------------------------- | SMITH | 800.00 | 1980-12-17 | | JAMES | 950.00 | 1981-12-03 | | ADAMS | 1100.00 | 1987-07-13 | | WARD | 1250.00 | 1981-02-22 | | MARTIN | 1250.00 | 1981-09-28 | -- 薪资与 WARD 相同按入职日期排序 | ... | ... | ... | -----------------------------1.4 条件查询在 MySQL 中使用WHERE关键字可以根据指定条件筛选数据只返回符合要求的记录是日常查询中最常用的功能。1.4.1 语法格式SELECT 字段列表 FROM 表名 WHERE 查询条件;常见查询条件类型比较运算符条件逻辑运算符条件范围条件BETWEEN ANDNULL 值判断条件集合包含条件IN模糊查询条件LIKE1.4.2 比较运算符条件用于比较字段值与指定值支持数字、字符串、日期等类型。常用比较运算符运算符说明大于小于等于 / !不等于大于等于小于等于实战案例-- 1. 查询薪资大于 1500 的员工 mysql select * from emp where sal1500; -- 2. 查询薪资等于 5000 的员工 mysql select * from emp where sal5000; -- 3. 查询薪资小于 3000 的员工 mysql select * from emp where sal3000; -- 4. 查询职位不是 CLERK 的员工两种写法等价 mysql select * from emp where jobCLERK; mysql select * from emp where job!CLERK;1.4.3 逻辑运算符条件用于组合多个查询条件实现更精准的筛选。常用逻辑运算符运算符说明AND / 逻辑与所有条件必须同时满足OR /逻辑或满足任意一个条件即可NOT / !逻辑非取反XOR逻辑异或满足一个且不满足另一个实战案例-- 1. 查询职位不是 CLERK 的员工NOT 用法 mysql select * from emp where NOT jobCLERK; -- 2. 查询薪资在 1500~3000 之间的员工AND mysql select * from emp where sal1500 and sal3000; -- 3. 查询职位是 CLERK 或 SALESMAN 的员工OR mysql select * from emp where jobCLERK or jobSALESMAN; -- 4. 组合条件职位是文员/销售员 且 薪资大于1200 mysql select * from emp where (jobCLERK or jobSALESMAN) and sal1200; -- 5. 异或薪资1500 或者 职位是经理但不同时满足 mysql select * from emp where sal1500 xor jobMANAGER;1.4.4 范围条件BETWEEN AND用于查询介于两个值之间的记录包含边界值等价于 最小值 AND 最大值。语法字段名 BETWEEN 最小值 AND 最大值; 字段名 NOT BETWEEN 最小值 AND 最大值;实战案例-- 1. 查询薪资在 1500 ~ 3000 之间的员工 mysql select * from emp where sal between 1500 and 3000; -- 2. 查询薪资不在 1500 ~ 3000 之间的员工 mysql select * from emp where not sal between 1500 and 3000;1.4.5 NULL 值判断条件不能使用 或 判断 NULL必须使用专用关键字IS NULL字段值为 NULLIS NOT NULL字段值不为 NULL实战案例-- 1. 查询奖金comm为 NULL 的员工 mysql select * from emp where comm is null; -- 2. 查询奖金comm不为 NULL 的员工 mysql select * from emp where comm is not null; -- 等价写法 mysql select * from emp where not comm is null;1.4.6 IN 条件判断用于判断字段值是否在指定集合中替代多个OR条件语法更简洁。语法字段名 IN (值1, 值2, 值3...); 字段名 NOT IN (值1, 值2, 值3...);重要注意事项IN中包含 NULL不影响查询NOT IN中包含 NULL查询结果为空实战案例-- 1. 查询员工编号为 7369、7566、7799 的员工IN 替代 OR mysql select * from emp where empno7369 or empno7566 or empno7799; mysql select * from emp where empno in (7369,7566,7799); -- 2. 查询员工编号不在此集合中的员工 mysql select * from emp where empno not in (7369,7566,7799); -- 3. IN 包含 NULL正常查询 mysql select * from emp where empno in (7369,7566,null); -- 4. NOT IN 包含 NULL结果为空 mysql select * from emp where empno not in (7369,7566,null);1.4.7 LIKE 模糊查询用于模糊匹配字符串配合通配符使用是文本搜索的核心功能。通配符说明通配符作用%匹配任意长度的字符串包含 0 个字符_只匹配单个字符语法字段名 [NOT] LIKE 匹配字符串;实战案例-- 1. 查询姓名以 A 开头的员工 mysql select * from emp where ename like A%; -- 2. 查询姓名第二个字符是 A 的员工 mysql select * from emp where ename like _A%; -- 3. 查询姓名不包含 A 的员工 mysql select * from emp where ename not like %A%; -- 4. 区分大小写查询BINARY mysql select * from emp where ename like binary A%; mysql select * from emp where ename like binary a%; -- 无结果使用通配符注意事项默认不区分大小写区分需加BINARY%无法匹配NULL通配符尽量不要放在开头会导致索引失效查询变慢能用精确查询就不用模糊查询提升效率1.5 分组查询在 MySQL 中GROUP BY关键字可以根据一个或多个字段对查询结果进行分组常用于统计分析数据。1.5.1 语法格式SELECT 字段列表 FROM 表名 GROUP BY 分组字段名;多个字段分组时用逗号隔开。1.5.2 分组函数聚合函数分组函数也叫聚合函数通常和GROUP BY配合使用也可以单独使用。函数作用COUNT()统计记录条数AVG()求平均值SUM()求和MAX()求最大值MIN()求最小值重要特性COUNT()永远返回数字无数据时返回0其他聚合函数无数据时返回NULL实战案例-- 统计员工总数、工资总和、平均工资 mysql select count(empno),sum(sal),avg(sal) from emp; ------------------------------------- | count(empno) | sum(sal) | avg(sal) | ------------------------------------- | 14 | 29025.00 | 2073.214286 | ------------------------------------- -- 查询最高工资、最低工资 mysql select max(sal),min(sal) from emp; -------------------- | max(sal) | min(sal) | -------------------- | 5000.00 | 800.00 | -------------------- -- 空表测试 COUNT 和 SUM mysql create table tb_emp1(id int(11),name varchar(25),deptid int(11),salary float); mysql select count(*) from tb_emp1; -- 返回 0 mysql select sum(id) from tb_emp1; -- 返回 NULL1.5.3 分组统计当数据存在重复值时分组才有意义。分组查询规则分组后SELECT后只能出现分组字段 聚合函数未分组的普通字段不能单独出现否则报错实战案例-- 按部门编号分组统计各部门人数、平均工资 mysql select deptno,count(empno),avg(sal) from emp group by deptno; ----------------------------------- | deptno | count(empno) | avg(sal) | ----------------------------------- | 10 | 3 | 2916.666667 | | 20 | 5 | 2175.000000 | | 30 | 6 | 1566.666667 | ----------------------------------- -- 按职位分组统计各职位最高/最低工资 mysql select job,max(sal),min(sal) from emp group by job; ------------------------------- | job | max(sal) | min(sal) | ------------------------------- | CLERK | 1300.00 | 800.00 | | SALESMAN | 1600.00 | 1250.00 | | MANAGER | 2975.00 | 2450.00 | | ANALYST | 3000.00 | 3000.00 | | PRESIDENT | 5000.00 | 5000.00 | -------------------------------正确 / 错误用法对比-- ✅ 正确单独使用聚合函数 mysql select count(empno) from emp; -- ❌ 错误未分组不能出现普通字段 empno mysql select empno,count(empno) from emp; -- ✅ 正确分组后只出现分组字段 聚合函数 mysql select job,count(empno),avg(sal) from emp group by job; -- ❌ 错误select 包含未分组字段 empno mysql select empno,job,count(empno),avg(sal) from emp group by job;1.6 分组过滤HAVING在 MySQL 中使用HAVING关键字对分组后的数据进行过滤。1.6.1 语法格式SELECT 字段列表 FROM 表名 GROUP BY 字段 HAVING 过滤条件;1.6.2 WHERE 与 HAVING 区别对比项WHEREHAVING过滤时机分组前过滤原始数据分组后过滤结果能否用聚合函数不可以可以操作对象数据表文件查询结果集能否用字段别名不可以可以实战案例-- 对分组结果过滤只查看 MANAGER 职位的最高工资 mysql select job,max(sal) from emp group by job having jobMANAGER; ------------------- | job | max(sal) | ------------------- | MANAGER | 2975.00 | ------------------- -- 统计各职位平均工资并查询平均工资最高的值子查询 mysql select max(avgtb.avgsal) from (select job,avg(sal) as avgsal from emp group by job) as avgtb; ------------------- | max(avgtb.avgsal) | ------------------- | 5000.000000 | -------------------1.7 多表查询在 MySQL 中多表查询主要分为交叉连接、内连接、外连接。1.7.1 交叉连接CROSS JOIN交叉连接返回两个表的笛卡尔积。笛卡尔积说明集合 A × 集合 B 所有组合结果行数 表 1 行数 × 表 2 行数实际开发必须避免无限制的笛卡尔积语法SELECT * FROM 表1 CROSS JOIN 表2; -- 等价 SELECT * FROM 表1, 表2;实战案例-- 查询部门表与员工表的笛卡尔积4×1456条 mysql select * from dept cross join emp; -- 带条件的交叉连接 mysql select * from dept cross join emp where sal1500;1.7.2 内连接INNER JOIN内连接通过连接条件消除无效的笛卡尔积只返回匹配的数据。语法SELECT 字段 FROM 表1 INNER JOIN 表2 ON 连接条件;INNER可省略直接写JOIN。实战案例-- 等值连接查询员工及其所属部门 mysql select * from dept inner join emp on dept.deptnoemp.deptno; -- 表别名 指定字段查询 mysql select d.deptno,d.dname,e.ename,e.job from dept d join emp e on d.deptnoe.deptno; -- 非等值连接查询员工工资等级 mysql select e.ename,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal; -- 自连接查询员工及其直属领导 mysql select e.empno 员工编号,e.ename 员工姓名,m.ename 领导姓名 from emp e join emp m on e.mgrm.empno;1.7.3 外连接外连接以基表为基准返回基表所有数据以及匹配的关联表数据无匹配则补NULL。左外连接LEFT JOIN以左表为基表-- 查询所有部门包含没有员工的部门40号部门 mysql select d.deptno,d.dname,e.empno from dept d left join emp e on d.deptnoe.deptno;右外连接RIGHT JOIN以右表为基表-- 效果与上面左连接一致 mysql select d.deptno,d.dname,e.empno from emp e right join dept d on d.deptnoe.deptno;1.8 子查询子查询是指一个查询嵌套在另一个查询中常用于复杂条件查询。1.8.1 子查询出现位置WHERE子查询返回单行单列、多行单列、单行多列FROM子查询返回多行多列当作临时表1.8.2 常用子查询类型1. 标量子查询单行单列-- 查询工资高于 SMITH 的员工 mysql select * from emp where sal(select sal from emp where enameSMITH); -- 查询工资高于公司平均工资的员工 mysql select * from emp where sal(select avg(sal) from emp);2. 行子查询单行多列-- 查询职位和工资都与 ALLEN 相同的员工 mysql select * from emp where(job,sal)(select job,sal from emp where enameALLEN);3. 列子查询多行单列配合IN / ANY / ALL使用-- 查询工资和经理一样的员工 mysql select * from emp where sal in(select sal from emp where jobMANAGER); -- ANY 等价于 IN mysql select * from emp where salany(select sal from emp where jobMANAGER); -- ANY大于子查询中最小值 mysql select * from emp where salany(select sal from emp where jobMANAGER); -- ALL大于子查询中最大值 mysql select * from emp where salall(select sal from emp where jobMANAGER);4. FROM 子查询临时表-- 先分组统计再与部门表连接 mysql select d.deptno,d.dname,temp.count,temp.avg from dept d right join( select deptno dno,count(empno) count,avg(sal) avg from emp group by deptno ) temp on d.deptnotemp.dno;总结分组查询GROUP BY 聚合函数HAVING过滤分组结果多表连接内连接只查匹配数据INNER JOIN左连接左表全量LEFT JOIN右连接右表全量RIGHT JOIN子查询WHERE子查询条件筛选FROM子查询临时表统计关键字IN / ANY / ALL