MySQL(02)—数据处理之查询

发表于 更新于 分类于 Changyan：
本文字数： 8.4k 阅读时长 ≈ 8 分钟
数据查询

数据查询

使用前，对当前数据库写入测试用的数据库脚本，点击下载脚本

1. 基本的SELECT语句

1
2
SELECT * | {[DISTINCT] cloumn | expression [alias],...}
FROM table;

SELECT 标识选择哪些列
FROM 标识从哪个表中选择

  1. 选择全部列

    1
    2
    3
    use myemployees;
    select *
    from departments;

    image-20210817223641403

  2. 选择特定列

    1
    2
    select department_id, location_id
    from departments;
    image-20210817224055778

  3. 使用别名

    重命名一个列。 便于计算。紧跟列名，也可以在列名和别名之间加入关键字 ‘AS’，别名使用双引号，以便在别名中包含空 格或特殊的字符并区分大小写

    1
    2
    select last_name as name, commission_pct comm
    from employees;
    image-20210817224341768 
    1
    2
    SELECT last_name "Name", salary*12 "Annual Salary"
    FROM employees;
    image-20210817224607348

字符串

字符串可以是 SELECT 列表中的一个字符,数字,日期。

日期和字符只能在单引号中出现。

每当返回一行时，字符串被输出一次

显示表结构

1
DESCRIBE employees;

image-20210817225013421

2. 过滤和排序数据

2.1 过滤

使用where子句，将不满足条件的行过滤掉

1
2
3
SELECT *|{[DISTINCT] column|expression [alias],...}
FROM table
[WHERE condition(s)];

WHERE 子句紧随 FROM 子句

返回在90号部门工作的所有员工的信息

1
2
3
select employee_id,last_name,job_id,department_id
from employees
where department_id = 90;

image-20210817225405029

2.1.1 比较运算符
操作符 含义
= 等于 (不是 ==)
> 大于
>= 大于、等于
< 小于
<= 小于等于
<> 不等于（也可以是!=)

查询薪资在3000以下的员工的姓氏和薪资

1
2
3
SELECT last_name, salary
FROM employees
WHERE salary <= 3000;
image-20210817225941018
2.1.2 1其他比较运算符
操作符 含义
BETWEEN …AND… 在两个值之间 (包含边界)
IN(set) 等于值列表中的一个
LIKE 模糊查询
IS NULL 空值

  1. 使用BETWEEN AND运算来显示在一个区间内的值

    1
    2
    3
    select last_name,salary
    from employees
    where salary between 2500 and 3500;

​ image-20210818101509030

  1. 使用IN运算显示列表中的值

    1
    2
    3
    select employee_id,last_name,salary,manager_id
    from employees
    where manager_id in (100,101,201);

    image-20210818102130760

  2. 使用LIKE运算选择类似的值

    选择条件可以包含字符或数字：

    • %代表零个或多个字符（任意个字符）
    • _代表一个字符
    1
    2
    3
    select first_name
    from employees
    where first_name like 'S%';
    image-20210818102756282

  3. 使用IS(NOT)NULL判断空值

    1
    2
    3
    select last_name,manager_id
    from employees
    where manager_id is null;
    image-20210818104508246
2.1.3 逻辑运算
操作符 含义
AND 逻辑与
OR 逻辑或
NOT 逻辑否

  1. AND要求并的关系为真

    1
    2
    3
    4
    select employee_id,last_name,job_id,salary
    from employees
    where salary >= 1000
    and job_id like '%MAN%';

    image-20210818105203409

  2. OR要求或关系为真

    1
    2
    3
    4
    select employee_id,last_name,job_id,salary
    from employees
    where salary >= 10000
    or job_id like '%MAN%';

    image-20210818110427489

  3. NOT

    1
    2
    3
    4
    select last_name,job_id
    from employees
    where job_id
    not in ('IT_PROG','ST_CLERK','SA_REP');
    image-20210818110731163
2.1.4 ORDER BY子句

使用ORDER BY子句排序

  • ASC(ascend)：升序
  • DESC(descend)：降序
  • ORDER BY子句在SELECT语句的结尾

  1. 默认使用升序排列

    1
    2
    3
    select last_name,job_id,department_id,heirdate
    from employees
    order by hiredate;

    image-20210818111440587

  2. 使用降序排列

    1
    2
    3
    select last_name,job_id,department_id,hiredate
    from employees
    order by hiredate desc;

    image-20210818112612280

  3. 按别名排序

    1
    2
    3
    select employee_id,last_name,salary*12 'annual salary'
    from employees
    order by 'annual salary';

    image-20210818113244031

  4. 按照ORDER BY列表的顺序排序

    1
    2
    3
    select last_name,department_id,salary
    from employees
    order by department_id,salary desc;

    image-20210818113616110

    可以使用不在SELECT列表里中的列排序

3. 分组函数

3.1 什么是分组函数

分组函数作用于一组数据，并对一组数据返回一个值

image-20210818114256311

3.2 组函数类型
  • AVG()
  • COUNT()
  • MAX()
  • MIN()
  • SUM()
3.3 组函数语法
1
2
3
4
5
SELECT [column,] group_function(column), ...
FROM table
[WHERE condition]
[GROUP BY column]
[ORDER BY column];

  1. AVG(平均值)、MIN(最小值)、 MAX(最大值)、SUM(合计)函数

    1
    2
    3
    select avg(salary),max(salary),min(salary),sum(salary)
    from employees
    where job_id like '%REP%';

    image-20210818114856880

  2. COUNT(*)返回表中记录总数，适用于任意数据类型

    1
    2
    3
    select count(*)
    from employees
    where department_id = 50;
    image-20210818115408365

    COUNT(expr)返回expr不为空的记录总数

    1
    2
    3
    select last_name,count(commission_pct)
    from employees
    where department_id = 50;
    image-20210818115815622
3.4 分组数据
3.4.1 单列分组

image-20210818115909354

GROUP BY子句语法，可以使用GROUP BY子句将表中的数据分成若干组

1
2
3
4
5
SELECT column, group_function(column)
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[ORDER BY column];

明确：WHERE一定放在FROM后面

  1. 在SELECT 列表中所有未包含在组函数中的列都应该包含 在 GROUP BY 子句中。

    1
    2
    3
    select department_id,AVG(salary)
    from employees
    group by department_id;
    image-20210818120451894

  2. 包含在GROUP BY子句中的列不必包含在SELECT列表中

    1
    2
    3
    select avg(salary)
    from employees
    group by department_id;
    image-20210818121325998
3.4.2 多列分组

image-20210818121522543

  1. 在GROUP BY子句中包含多个列

    1
    2
    3
    select department_id,dept_id,job_id,sum(salary)
    from employees
    group by department_id,job_id;
    image-20210818122333093
3.4.3 过滤分组

image-20210818154507053

使用HAVING过滤分组

  1. 行已经被分组

  2. 使用了组函数

  3. 满足HAVING子句中条件的分组将被显示

    1
    2
    3
    4
    5
    6
    SELECT column, group_function
    FROM table
    [WHERE condition]
    [GROUP BY group_by_expression]
    [HAVING group_condition]
    [ORDER BY column];

  4. HAVING子句

    1
    2
    3
    4
    select department_id,MAX(salary)
    from employees
    group by department_id
    having max(salary)>10000;
    image-20210818155219393
3.4.4 SELECT语句执行顺序

having子句与where都是设定条件筛选的语句，有相似之处也有区别。

having与where的区别:
having是在分组后对数据进行过滤
where是在分组前对数据进行过滤
having后面可以使用聚合函数
where后面不可以使用聚合

在查询过程中执行顺序：from>where>group（含聚合）>having>order>select。

所以聚合语句(sum,min,max,avg,count)要比having子句优先执行，而where子句在查询过程中执行优先级别优先于聚合语句(sum,min,max,avg,count)。
where子句：
select sum(num) as rmb from order where id>10
//只有先查询出id大于10的记录才能进行聚合语句

having子句:
select reports， count() from employees group by reports having count() > 4
上例having条件表达示为聚合语句，肯定的说having子句查询过程执行优先级别低于聚合语句。
再换句说话说把上面的having换成where则会出错，统计分组数据时用到聚合语句。
对分组数据再次判断时要用having。如果不用这些关系就不存在使用having。直接使用where就行了。
having就是来弥补where在分组数据判断时的不足。因为where执行优先级别要快于聚合语句。

聚合函数：
例如SUM, COUNT, MAX, AVG等。这些函数和其它函数的根本区别就是它们一般作用在多条记录上。

HAVING子句可以让我们直接筛选成组后的各组数据，也可以在聚合后对组记录进行筛选，而WHERE子句在聚合前先筛选记录，也就是说作用在GROUP BY 子句和HAVING子句前。

3.4.5 非法使用组函数

不能在WHERE子句中使用组函数

可以在HAVING子句中使用组函数

1
2
3
4
5
SELECT department_id, AVG(salary)
FROM employees
WHERE AVG(salary) > 8000
GROUP BY department_id;

1
2
3
4
WHERE AVG(salary) > 8000
*
ERROR at line 3:
ORA-00934: group function is not allowed here

4. 多表查询

两表做笛卡尔积

image-20210818165345140 image-20210819115650359
4.1 多表连接

语法：

1
2
select name,boyName 
from beauty,boys;
image-20210818161943007
4.2 笛卡尔集

笛卡尔集会在下面的条件下产生

①省略连接条件；②连接条件无效；③所有表中的所有行互相链接

为了避免笛卡尔集，可以在WHERE加入有效的连接条件

4.3 MySQL连接

使用连接在多个表中查询数据

1
2
3
SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column1 = table2.column2;

在 WHERE 子句中写入连接条件。 在表中有相同列时，在列名之前加上表名前缀

  1. 等值连接

    1
    2
    3
    select beauty.id,name,boyName
    from beauty,boys
    where beauty.boyfriend_id=boys.id;
    image-20210818165426323

    区分重复的列名：使用表名前缀在多个表中区分相同的列，在不同表中具有相同列名的列可以用表的别名加以区分，如果使用了表别名，则在select语句中需要使用表别名代替表名，表别名最多支持32个字符长度，但建议越少越好

  2. 表的别名

    1
    2
    3
    select bt.id,name,boyName
    from beauty bt, boys b
    where bt.boyfriend_id=b.id;
    image-20210818165426323
4.4 连接多个表

image-20210818170424732

连接 n个表,至少需要 n-1个连接条件。 例如：连接 三个表，至少需要两个连接条件。

  1. 查询出公司员工的 last_name, department_name, city

    1
    2
    3
    4
    select last_name,department_name,city
    from employees,departments,locations
    where employees.department_id = departments.department_id 
    and departments.location_id = locations.location_id;

    image-20210818171002376

4.5 使用ON子句创建连接

自然连接中是以具有相同名字的列为连接条件的，可以使用ON子句指定额外的连接条件，这个连接条件是与其他条件分开的，ON子句使语句具有更高的易读性

4.5.1 JOIN连接

分类：

  • 内连接 [inner] join on

    （典型的连接运算，使用像 = 或 <> 之类的比较运算符）。包括相等联接和自然联接。内联接使用比较运算符根据每个表共有的列的值匹配两个表中的行。例如，检索 students和courses表中学生标识号相同的所有行。

  • 外连接

    外联接可以是左向外连接、右向连接或完整外部连接。在 FROM子句中指定外联接时，可以由下列几组关键字中的一组指定：

    1. LEFT JOIN或LEFT OUTER JOIN

      左向外联接的结果集包括 LEFT OUTER子句中指定的左表的所有行，而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值。

    2. RIGHT JOIN 或 RIGHT OUTER JOIN

      右向外联接是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中没有匹配行，则将为左表返回空值。

    3. FULL JOIN 或 FULL OUTER JOIN

      完整外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值。如果表之间有匹配行，则整个结果集行包含基表的数据值。

  • 交叉联接

    交叉联接返回左表中的所有行，左表中的每一行与右表中的所有行组合。交叉联接也称作笛卡尔积。

    FROM 子句中的表或视图可通过内联接或完整外部联接按任意顺序指定；但是，用左或右向外联接指定表或视图时，表或视图的顺序很重要。有关使用左或右向外联接排列表的更多信息，请参见使用外联接。

示例

image-20210819115832571

  1. 内连接

    1
    2
    select bt.*,boys.*
    from beauty bt inner join boys on bt.boyfriend_id = boys.id;

    image-20210819113439230

  2. 左连接

    1
    2
    select bt.*,boys.*
    from beauty bt left join boys on bt.boyfriend_id = boys.id;

    image-20210819113745505

    左表独有

    1
    2
    3
    select bt.*,boys.*
    from beauty bt left join boys on bt.boyfriend_id = boys.id
    where boys.id is null;

    image-20210819120507351

  3. 右连接

    1
    2
    select bt.*,boys.*
    from beauty bt right join boys on bt.boyfriend_id = boys.id;

    image-20210819120023490

    右表独有

    1
    2
    3
    select bt.*,boys.*
    from beauty bt right join boys on bt.boyfriend_id = boys.id
    where bt.boyfriend_id is null;

    image-20210819120702172

  4. 完全连接

    oracle里面有full join,但是在mysql中没有full join。我们可以使用union来达到目的。

    1
    2
    3
    select bt.* from beauty bt left join boys on bt.boyfriend_id = boys.id
    union
    select bt.* from beauty bt right join boys on bt.boyfriend_id = boys.id;

    image-20210819120133259

    并集去交

    1
    2
    3
    4
    5
    select * from beauty bt left join boys on bt.boyfriend_id = boys.id
    where boys.id is null
    union
    select * from beauty bt right join boys on bt.boyfriend_id = boys.id
    where bt.boyfriend_id is null;

    image-20210819121248356

  • image-20210818173141054
  • image-20210818173235317

5. SELECT语句执行顺序

查询操作是关系数据库中使用最为频繁的操作，也是构成其他SQL语句（如DELETE、UPDATE）的基础。我们知道，SQL 查询的大致语法结构如下：

1
2
3
4
5
6
7
(5)SELECT DISTINCT <select_list>                     
(1)FROM <left_table> <join_type> JOIN <right_table> ON <on_predicate>
(2)WHERE <where_predicate>
(3)GROUP BY <group_by_specification>
(4)HAVING <having_predicate>
(6)ORDER BY <order_by_list>
(7)LIMIT n, m

这些步骤执行时，每个步骤都会产生一个虚拟表，该虚拟表被用作下一个步骤的输入。这些虚拟表对调用者（客户端应用程序或者外部查询）不可用。只是最后一步生成的表才会返回给调用者。如果没有在查询中指定某一子句，将跳过相应的步骤。

SELECT各个阶段分别干了什么：

5.1 FROM阶段

FROM阶段标识出查询的来源表，并处理表运算符。在涉及到联接运算的查询中（各种JOIN），主要有以下几个步骤：

  • 求笛卡尔积。不论是什么类型的联接运算，首先都是执行交叉连接（CROSS JOIN），求笛卡儿积（Cartesian product），生成虚拟表VT1-J1。
  • ON筛选器。 这个阶段对上个步骤生成的VT1-J1进行筛选，根据ON子句中出现的谓词进行筛选，让谓词取值为true的行通过了考验，插入到VT1-J2。
  • 添加外部行。如果指定了OUTER JOIN，如LEFT OUTERJOIN、RIGHT OUTER JOIN，还需要将VT1-J2中没有找到匹配的行，作为外部行添加到VT1-J2中，生成VT1-J3。如果FROM子句包含两个以上表，则对上一个连接生成的结果表VT1-J3和下一个表重复依次执行3个步骤，直到处理完所有的表为止。

经过以上步骤，FROM阶段就完成了。

5.2 WHERE阶段

WHERE阶段是根据中条件对VT1中的行进行筛选，让条件成立的行才会插入到VT2中。此时数据还没有分组，所以不能在WHERE中出现对统计的过滤。

5.3 GROUP BY阶段

GROUP阶段按照指定的列名列表，将VT2中的行进行分组，生成VT3。最后每个分组只有一行。在GROUP BY阶段，数据库认为两个NULL值是相等的，因此会将NULL值分到同一个分组中。

5.4 HAVING阶段

该阶段根据HAVING子句中出现的谓词对VT3的分组进行筛选，并将符合条件的组插入到VT4中。COUNT(expr) 会返回expr不为NULL的行数，count(1)、count(*)会返回包括NULL值在内的所有数量。

5.5 SELECT阶段

这个阶段是投影的过程，处理SELECT子句提到的元素，产生VT5。这个步骤一般按下列顺序进行：

  • 计算SELECT列表中的表达式，生成VT5-1。
  • 若有DISTINCT，则删除VT5-1中的重复行，生成VT5-2。
5.6 ORDER BY阶段

根据ORDER BY子句中指定的列明列表，对VT5-2中的行，进行排序，生成VT6。如果不指定排序，数据并非总是按照主键顺序进行排序的。NULL被视为最小值。

5.7 LIMIT阶段

取出指定行的记录，产生虚拟表VT7，并返回给查询用户。LIMIT n, m的效率是十分低的，一般可以通过在WHERE条件中指定范围来优化 \WHERE** id > ? limit 10。**