SELECT…FROM…

FROM后面可以跟一张表，或者多张表。可以给表取别名，但是如果取别名了，后面的条件用原表名.列名定位不到，需要用表别名.列名定位。
SELECT也可以单独使用。

  1. SELECT *
  2. FROM departments;
  1. SELECT department_id, location_id
  2. FROM departments;

多表查询

内连接

合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行 

  1. SELECT emp.employee_id, dep.department_name
  2. FROM employee emp, department dep
  3. WHERE emp.`department_id` = dep.`department_id`;
  1. SELECT emp.employee_id, dep.department_name
  2. FROM employee emp JOIN department dep
  3. ON emp.`department_id` = dep.`department_id`;

左右外连接

  • 两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的 行 ，这种连接称为左（或右） 外连接。没有匹配的行时, 结果表中相应的列为空(NULL)。
  • 如果是左外连接，则连接条件中左边的表也称为 主表 ，右边的表称为 从表。
  • 左外连接：LEFT OUTER JOIN ON
  • 右外连接：RIGHT OUTER JOIN ON

  • 其中OUTER可以省略

    1. SELECT last_name, department_name
    2. FROM employees emp LEFT OUTER JOIN department dep
    3. ON emp.`department_id` = dep.`department_id`;

    满外连接

  • 满外连接的结果 = 左右表匹配的数据 + 左表没有匹配到的数据 + 右表没有匹配到的数据。

  • SQL99是支持满外连接的。使用FULL JOIN 或 FULL OUTER JOIN来实现。

  • 需要注意的是，MySQL不支持FULL JOIN，但是可以用 LEFT JOIN UNION RIGHT join代替。

    UNION合并查询结果

    1. SELECT column,... FROM table1
    2. UNION [ALL]
    3. SELECT column,... FROM table2

  • UNION 操作符返回两个查询的结果集的并集，去除重复记录。

  • UNION ALL操作符返回两个查询的结果集的并集。对于两个结果集的重复部分，不去重。

    注意：执行UNION ALL语句时所需要的资源比UNION语句少。如果明确知道合并数据后的结果数据不存在重复数据，或者不需要去除重复的数据，则尽量使用UNION ALL语句，以提高数据查询的效率。

举例：

  1. #方式1
  2. SELECT * FROM employees WHERE email LIKE '%a%' OR department_id>90;
  3. #等价于
  4. #方式2
  5. SELECT * FROM employees WHERE email LIKE '%a%'
  6. UNION
  7. SELECT * FROM employees WHERE department_id>90;

七种SQL JOINS的实现

image-20220531224324213.png

SQL99语法的新特性

自然连接

SQL99 在 SQL92 的基础上提供了一些特殊语法，比如 NATURAL JOIN 用来表示自然连接。我们可以把 自然连接理解为 SQL92 中的等值连接。它会帮你自动查询两张连接表中 所有相同的字段 ，然后进行 等值 连接 。
在SQL92标准中：

  1. SELECT employee_id,last_name,department_name
  2. FROM employees e JOIN departments d
  3. ON e.`department_id` = d.`department_id`
  4. AND e.`manager_id` = d.`manager_id`;

在 SQL99 中你可以写成：

  1. SELECT employee_id,last_name,department_name
  2. FROM employees e NATURAL JOIN departments d;

USING连接

当我们进行连接的时候，SQL99还支持使用 USING 指定数据表里的 同名字段 进行等值连接。但是只能配 合JOIN一起使用。比如：

  1. SELECT employee_id,last_name,department_name
  2. FROM employees e JOIN departments d
  3. USING (department_id);

你能看出与自然连接 NATURAL JOIN 不同的是，USING 指定了具体的相同的字段名称，你需要在 USING 的括号 () 中填入要指定的同名字段。同时使用 JOIN…USING 可以简化 JOIN ON 的等值连接。它与下 面的 SQL 查询结果是相同的：

  1. SELECT employee_id,last_name,department_name
  2. FROM employees e ,departments d
  3. WHERE e.department_id = d.department_id;

总结

控制多表连接的数量，多表连接非常耗资源，连接的表多了会严重影响数据库性能。

列的别名

  • AS：全称：alias（别名），可以省略AS，不建议省略

  • 别名紧跟列名，也可以在别名和列名中间加AS，别名建议加上""，以便在别名中包含空格或特 殊的字符

    1. SELECT last_name AS "name", commission_pct comm
    2. FROM employees;

    DISTINCT去重复行

    在要去重的列前面加上DISTINCT关键字

    1. SELECT DISTINCT department_id FROM employees;

    查询常数

    加上常数查询，每行都会有这个常数值

    1. SELECT '三体科技' as "公司名", employee_id, last_name FROM employees;

    WHERE

    表示条件

    1. SELECT employee_id, last_name, job_id, department_id
    2. FROM employees
    3. WHERE department_id = 90;

    ORDER BY排序

  • ASC：升序，默认

  • DESC：降序
  • 可以按多列排序，用逗号分隔
    1. SELECT *
    2. FROM students
    3. ORDER BY std_score DESC

    LIMIT限制查询

    分页。
    LIMIT [位置偏移量,]获取条数
    1. #第11至20条记录
    2. SELECT * FROM 表名 LIMIT 10,10;

    MySQL 8.0中可以使用**LIMIT OFFSET**组合 当 LIMIT和OFFSET组合使用的时候，LIMIT后面只能有一个参数，表示要取的的数量，OFFSET表示要跳过的数量

  1. #101-111行数据
  2. SELECT * FROM 表名 LIMIT 10 OFFSET 100

  • LIMIT 子句必须放在整个SELECT语句的最后！

    GROUP BY分组

    分组查询，把某一字段进行分组，假如”学科”字段里面的数据，有32个语文，67个数学，82个英语，通过GROUP BY ‘学科’，把语文分一组，数学分一组，英语分一组

  • 如果SELECT后面出现的列里面包含了聚合函数，那么不是聚合函数的列必须，在GROUP BY后面写上

  • GROUP BY后面的列不一定要写到SELECT后面
  • 使用多列分组，挨个在GROUP BY后面写上，用逗号分隔，第一列分组完成后，在第一列分组的基础上进行第二列的分组，以此类推
    1. SELECT subject
    2. FROM class
    3. GROUP BY subject

    GROUP BY中使用WITH ROLLUP

    使用WITH ROLLUP关键字之后，在所有查询出的分组记录之后增加一条记录，该记录计算查询出的所有记录的总和，即统计记录数量。

    注意： 当使用ROLLUP时，不能同时使用ORDER BY子句进行结果排序，即ROLLUP和ORDER BY是互相排斥的。

  1. SELECT department_id,AVG(salary)
  2. FROM employees
  3. WHERE department_id > 80
  4. GROUP BY department_id WITH ROLLUP;

HAVING分组条件

在GROUP BY后面写的条件。
与WHERE功能、用法相同，执行时机不同。

  • WHERE在开始时执行检测数据，对原数据进行过滤。
  • HAVING对筛选出的结果再次进行过滤。
  • HAVING字段必须是查询出来的，WHERE字段必须是数据表存在的。
  • WHERE不可以使用字段的别名，HAVING可以。因为执行WHERE代码时，可能尚未确定列值。
  • WHERE 不可以使用聚合函数。一般需用聚合函数才会用 HAVING。
  • 如果需要通过连接从关联表中获取需要的数据，WHERE 是先筛选后连接，而 HAVING 是先连接后筛选

  • 当数据量特别大的时候，运行效率会有很大的差别，WHERE效率高于HAVING，因为WHERE是先筛选再查询，HAVING是先查询再筛选

    1. SELECT   department_id, MAX(salary)
    2. FROM     employees
    3. GROUP BY department_id
    4. HAVING   MAX(salary)>10000 ;

    SELECT执行顺序

    关键字的顺序

    关键字顺序是不能颠倒的

    1. SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT...

    SELECT 语句的执行顺序

    在 MySQL 和 Oracle 中，SELECT 执行顺序基本相同

    1. FROM -> WHERE -> GROUP BY -> HAVING -> SELECT 的字段 -> DISTINCT -> ORDER BY -> LIMIT

    在 SELECT 语句执行这些步骤的时候，每个步骤都会产生一个虚拟表，然后将这个虚拟表传入下一个步骤中作为输入。需要注意的是，这些步骤隐含在 SQL 的执行过程中，对于我们来说是不可见的。

    SQL执行原理

  • SELECT 是先执行 FROM 这一步的。在这个阶段，如果是多张表联查，还会经历下面的几个步骤：

    1. 首先先通过 CROSS JOIN 求笛卡尔积，相当于得到虚拟表 vt（virtual table）1-1；
    2. 通过 ON 进行筛选，在虚拟表 vt1-1 的基础上进行筛选，得到虚拟表 vt1-2；
    3. 添加外部行。如果我们使用的是左连接、右链接或者全连接，就会涉及到外部行，也就是在虚拟表 vt1-2 的基础上增加外部行，得到虚拟表 vt1-3。
  • 当然如果我们操作的是两张以上的表，还会重复上面的步骤，直到所有表都被处理完为止。这个过程得到是我们的原始数据。
  • 当我们拿到了查询数据表的原始数据，也就是最终的虚拟表 vt1，就可以在此基础上再进行 WHERE 阶段。在这个阶段中，会根据 vt1 表的结果进行筛选过滤，得到虚拟表 vt2。
  • 然后进入第三步和第四步，也就是 GROUP 和 HAVING 阶段。在这个阶段中，实际上是在虚拟表 vt2 的基础上进行分组和分组过滤，得到中间的虚拟表 vt3 和 vt4。
  • 当我们完成了条件筛选部分之后，就可以筛选表中提取的字段，也就是进入到 SELECT 和 DISTINCT 阶段。
  • 首先在 SELECT 阶段会提取想要的字段，然后在 DISTINCT 阶段过滤掉重复的行，分别得到中间的虚拟表 vt5-1 和 vt5-2。
  • 当我们提取了想要的字段数据之后，就可以按照指定的字段进行排序，也就是 ORDER BY 阶段，得到虚拟表 vt6。
  • 最后在 vt6 的基础上，取出指定行的记录，也就是 LIMIT 阶段，得到最终的结果，对应的是虚拟表 vt7。

当然我们在写 SELECT 语句的时候，不一定存在所有的关键字，相应的阶段就会省略。
同时因为 SQL 是一门类似英语的结构化查询语言，所以我们在写 SELECT 语句的时候，还要注意相应的关键字顺序，所谓底层运行的原理，就是我们刚才讲到的执行顺序。

子查询

就是先select查询出一部分数据，以这查出来的数据作为参数再次查询，得出新的结果。
本来是两个步骤，现在合并到一起写一条sql语句，就成了子查询。

  1. #随便举两个例子
  3. -- WHERE型
  4. -- 先查询出学生的平均成绩，然后再查比平均成绩高的学生
  5. SELECT * 
  6. FROM students 
  7. WHERE std_score>(SELECT avg(std_score) FROM students)
  9. -- from型
  10. -- 先查询3个字段，成绩大于30的学生,输出的新表，取个别名，必须取别名！在查询这个新表，同时限定条件std_sexid=1
  11. SELECT * 
  12. FROM (SELECT std_id,std_name,std_sexid FROM students WHERE std_score>30) AS new_table
  13. WHERE std_sexid=1