「MySQL学习笔记03」SQL（下）— DQL

¶3.5 DQL-单表记录查询

¶3.5.1 基础查询

• 查询表中所有行和列的数据（使用 * 表示所有列）

  SELECT * FROM 表名;

• 查询指定列

  SELECT 列名1, 列名2, ... FROM 表名;

  

• 查询指定列并起别名

  SELECT 原列名1 AS 别名1, 原列名2 AS 别名2, ... FROM 表名;
  
  SELECT 原列名1 AS 别名1, 原列名2 AS 别名2, ... FROM 表名 AS 表的别名; /* 查询后得到的表 也起别名，常用于多表查询操作中 */

• 查询指定列且结果不出现重复数据

  SELECT DISTINCT 列名 FROM 表名;

• 查询的结果参与数值运算

  SELECT 列名1 + 固定值 FROM 表名;
  
  SELECT 列名1 + 列名2 FROM 表名;

  某列数据与 NULL 运算后得到的结果仍为 NULL 。

  假如总分是数学与英语的总和，而张三考了数学，但缺考英语，此时的结果应该是数学成绩+0，为了避免出现 NULL，可以用 IFNULL 来处理。

  IFNULL(表达式1, 表达式2) ——如果表达式1的值为NULL，则 结果 会替换成表达式2的值。

  SELECT IFNULL(MATH, 0) + IFNULL(ENGLISH, 0) AS SCORE FROM STU;

¶3.5.2 条件查询

条件语句的语法：

SELECT 列名 FROM 表名 WHERE 条件;

以下为运算符列表，可用于 WHERE 子句中，从而有条件地从表中查询数据。

运算符	说明
>、<、<=、>=、= 、<>	<> 在SQL中表示“不等于”，注意MySQL中没有==，只有=
AND 或者 &&	建议使用前者，后者不通用
OR 或者 `
NOT 或者 !	建议使用前者，后者不通用
IN	常用与IN( 集合 )，集合中使用逗号分隔
IS NULL	查询某一列是否NULL，注意，不能写成=NULL

/* 查询年龄不等于20岁 */
SELECT * FROM stu WHERE age != 20;
SELECT * FROM stu WHERE age <> 20;

/* 查询年龄在[20, 30]岁 */
SELECT * FROM stu WHERE age >= 20 AND age <= 30;
SELECT * FROM stu WHERE age BETWEEN 20 AND 30;

/* 查询年龄为22岁、18岁、25岁 */
SELECT * FROM stu WHERE age = 22 OR age = 18 OR age = 25;
SELECT * FROM stu WHERE age IN (22, 18, 25);

/* 查询英语成绩不为NULL */
SELECT * FROM stu WHERE English IS NOT NULL;

⭐模糊查询：

SELECT * FROM 表名 WHERE 列名 LIKE '通配符字符串';

MySQL下的通配符：

• %：匹配任意多个字符
• _：匹配仅一个字符

/* 查询姓"王"的人 */
SELECT * FROM stu WHERE name LIKE '王%';

/* 查询姓名中第二个字为路的人 */
SELECT * FROM stu WHERE name LIKE '_路%';

/* 查询姓名中含有"飞"的人 */
SELECT * FROM stu WHERE name LIKE '%飞%';

/* 查询姓名是3个字的人 */
SELECT * FROM stu WHERE name LIKE '___';

¶3.5.3 排序查询

通过 ORDER BY 子句，可将查询出的结果进行排序。

此处的排序仅是显示方式，并不会影响数据库中原有的数据排列顺序。

SELECT 列名 FROM 表名 ORDER BY 列名1 [ASC|DESC], 列名2 [ASC|DESC], ...

• ASC：默认值，升序；
• DESC：降序

SELECT * FROM stu ORDER BY math DESC;

/* 先对数学成绩升序排序，若数学成绩相同则再对英语成绩升序排序 */
SELECT * FROM stu ORDER BY math ASC, english ASC;

¶3.5.4 聚合函数

以上的查询均是横向查询，即根据条件一行一行地进行判断。而接下来的聚合函数查询是纵向查询，即对一列的值进行计算，最后返回一个返回值。

SELECT 聚合函数(列名) FROM 表名

注意，聚合函数会忽略NULL，若要考进NULL，则建议列名使用主键，见下。

SQL中的聚合函数	作用
MAX(列名) 或 MIN(列名)	求这一列中的最大值 或最小值
SUM(列名)	对这一列的值进行求和
AVG(列名)	求这一列的平均值
COUNT(列名)	统计这一列有多少条记录

SELECT COUNT(id) AS 总人数 FROM stu;
SELECT COUNT(*) AS 总人数 FROM stu; 
SELECT COUNT(IFNULL(id, 0)) FROM stu;
/* 上面三句是等价的，建议使用第一条 */

SELECT COUNT(id) FROM stu WHERE age>18; 

¶3.5.5 分组查询

分组查询，使用GROUP by 语句对信息进行分组，相同数据作为一组，再进行查询。一般分组查询会与聚合函数一同使用。

SELECT 列名1, 列名2, ... FROM 表名 GROUP BY 分组的列名 [HAVING 条件];

• 举例使用：

  SELECT sex, AVG(math) FROM stu GROUP BY sex;

  

HAVING子句与 WHERE 子句的区别

子名	特点①	特点②
WHERE 子句	对查询结果进行分组前，先将不符合WHERE条件的行去掉，即先过滤再分组。	WHERE后面不能使用聚合函数
HAVING 子句	先分组再过滤	HAVING后面可以使用聚合函数

/* 先按性别分组，分别查询男、女同学的平均分，其中分数不大于70的人不参与统计，分组后，人数大于2的组才能显示出来 */
SELECT sex, AVG(math), COUNT(id) FROM stu WHERE math > 70 GROUP BY sex HAVING COUNT(id) > 2;

¶3.5.6 分页查询

LIMIT子句属于MySQL中的“方言”，它限制查询记录的条数。

SELECT 列名 FROM 表名 LIMIT 起始索引, 每页查询的页数;

起始索引 = （当前的页面$- 1$）$\times$ 每页查询的页数

起始索引从0计数，省略不写即是从0计数。

SELECT * FROM dbx LIMIT 0, 3; /* 第一页 */
SELECT * FROM dbx LIMIT 3, 3; /* 第二页 */
SELECT * FROM dbx LIMIT 6, 3; /* 最末页 */

第一页	第二页	最末页

¶3.6 DQL-连接查询与嵌套查询

¶3.6.1 连接相关概念

连接（join），是从两个关系的笛卡尔积中选取属性间满足一定条件的元组。

连接运算中有两种常用且重要的连接： （假定有关系$R$ 和 关系$S$）

• 等值连接：从关系$R$ 与$S$ 的广义笛卡尔积中选取$A$ 、$B$ 属性值相等的那些元组。

  

• 自然连接（也称 内连接）：等值连接 + 要求两个关系中进行比较的分量必须是同名的属性值，且在结果中把重复属性去掉。

  

• 左外连接：在等值连接的基础上，加上左边关系$R$ 中未匹配的数据。

  （右外连接同理）

  

若一个查询同时涉及两个以上的表，则称之为连接查询。

以下以两张表 people 及 department 为例：

¶3.6.2 内连接查询

关注三个要点：①从哪些表中查询数据；②条件是什么；③查询哪些字段

1. 隐式内连接查询：使用 where 条件消除无用的数据

   举例：查询所有员工信息及其所在的部门名称

   SELECT * FROM people, department WHERE people.department_id = department.id;

   

   查询员工的名称，性别及其所在部门的名称

   SELECT 
   	t1.name,
   	t1.gender,
   	t2.name
   FROM 	
   	people t1,
   	department t2
   WHERE 
   	t1.department_id = t2.id;

   

2. 显示内连接查询：

   语法：

   SELECT 字段列表 from 表名1 [inner] join 表名2 on 条件;
   /* 方括号表示可加可不加 */

   举例：查询所有员工信息及其所在的部门名称

   SELECT * FROM people JOIN department ON people.department_id = department.id;

¶3.6.3 外连接查询

1. 左外连接查询：简而言之，即查询左表所有数据以及其交集部分。

   语法：

   SELECT 字段列表 FROM 表1 LEFT JOIN 表2 ON 条件;
   /* 注意，是ON+条件 */

   举例：查询所有员工信息，同时，若员工有所需部门，则需显示；若没有部门，显示 NULL 即可。

   由于 新增 的 “路飞” 没有所属的部门（即 NULL），显然在内连接下，“路飞”无法与 “部门表” 进行连接。而外连接便能够解决该问题。

   SELECT 
   	t1.*,
   	t2.name
   FROM 
   	people t1 LEFT JOIN department t2
   ON 
   	t1.department_id = t2.id;

   

   ⭐再如：给定员工表，

   

   查询所有员工的姓名及其直接上级的姓名,没有领导的员工也需要查询。

   SELECT 
   	a.ename, b.ename
   FROM 
   	emp a LEFT JOIN emp b
   ON
   	a.mgr = b.id;

   

2. 右外连接查询：

   语法：

   SELECT 字段列表 FROM 表1 RIGHT JOIN 表2 ON 条件;
   /* 注意"RIGHT" */

¶3.6.4 嵌套查询

1. 嵌套查询的结果是单行单列的。

   一般是 子查询 作为 条件，再使用运算符进行判断，如：查询员工工资小于平均工资的人。

   SELECT * FROM people
   WHERE
   	people.salary < (
       	SELECT AVG(salary) FROM people
       );

2. 嵌套查询的结果是多行单列的。

   子查询 作为 条件，使用运算符 in 来判断，如：查询“财务部”和“市场部”所有的员工信息。

   SELECT * FROM people
   WHERE
   	department_id IN (
   		SELECT id FROM department WHERE NAME = "财务部" OR NAME = "市场部"
   	);

   

3. 嵌套查询的结果是多行多列的。

   子查询 作为 一张虚拟表 参与查询，

   比如：查询员工入职日期是2011-11-11日之后的员工信息和部门信息。

     SELECT * 
     FROM
     	department t1, 
     	(SELECT * FROM people WHERE people.join_date > "2011-11-11") t2
     WHERE
   t1.id = t2.department_id;

   

   当然，也可以使用普通的内连接：

     SELECT *
     FROM 
     	department t1,
     	people t2
     WHERE 
     	t1.id = t2.department_id 
     	AND
   t2.join_date > "2011-11-11";

   ⭐再比如，给定两个表：

   

   要求查询出部门编号、部门名称、部门位置、部门人数。

   SELECT
   	dept.id, dept.dname, dept.loc, ss.total
   FROM
   	dept, (
   	SELECT 
   		dept_id idx, COUNT(id) total 
   		/* 为虚拟表得到的 两列 分别起别名 */
   	FROM
   		emp 
   	GROUP BY /* 分组！*/
   		emp.dept_id
   	) ss /* 得到 (部门编号 与 部门人数) 的表 */
   WHERE
   	dept.id = ss.idx;