MySQL

MySQL表数据查询操作

从MySQL数据库中查询元组的操作

🔍 基本查询

基本语法：

1
2
3
4
5
6
7
8
9

select  -- 可以添加distinct来去除重复项
	字段列表  -- 查询所有字段可以简写为 *
from table_name
where condition  -- 条件列表
-- 以下均为可选选项
group by 聚合函数（字段列表）
having 对分组条件的过滤操作
order by 对字段列表的排序  -- ASC升序，DESC降序
limit 分页参数 (起始索引，查询记录数)  -- 索引从0开始，起始索引=（查询页码-1)*每页记录数

🎯 条件查询

常见条件运算符

比较运算符 功能
> 大于
>= 大于等于
< 小于
<= 小于等于
<>或!= 不等于

常见逻辑运算符

逻辑运算符 功能
and 或 && 与
or 或 || 或
not 或 | 非

📋 单表查询 

1
2
3
4
5
6
7
8
9

select  -- 可以添加distinct来去除重复项
	字段列表  -- 查询所有字段可以简写为 *
from table_name
where condition  -- 条件列表
-- 以下均为可选选项
group by 聚合函数（字段列表）
having 对分组条件的过滤操作
order by 对字段列表的排序  -- ASC升序，DESC降序
limit 分页参数 (起始索引，查询记录数)  -- 索引从0开始，起始索引=（查询页码-1)*每页记录数

  • select 子句：指定所需的属性，对应关系代数中的投影操作

    • 默认允许重复的元组
    • 显式指示使用 all 关键字
    • 强制去除重复使用 distinct 关键字
    • 查询所有属性可以简写为 *

  • from 子句：确定查询中需要访问的表

  • where 子句：指定结果满足的特定条件

⚙️ 附加的基本操作

🔄 更名运算

作用：

  • 长命名改为短命名
  • 比较同一个关系的元组

放置位置：位置灵活，无限制

1
2
3
4
5
6
7
8
9

select  -- 可以添加distinct来去除重复项
	字段列表  -- 查询所有字段可以简写为 *
from old_name as new_name
where condition  -- 条件列表
-- 以下均为可选选项
group by 聚合函数（字段列表）
having 对分组条件的过滤操作
order by 对字段列表的排序  -- ASC升序，DESC降序
limit 分页参数 (起始索引，查询记录数)  -- 索引从0开始，起始索引=（查询页码-1)*每页记录数

📊 显示顺序

在查询语句的结尾使用 order by 子句来决定显示按顺序还是倒序，默认为顺序。

  • desc：降序
  • asc：升序（默认）
1
2
3

select columns
from table
order by column_name [asc | desc];

🔤 字符串相关操作

放置位置：

  • where 子句中充当条件
  • select 子句中充当元素

常见操作：

  • 标识一个字符串：

    1

    '字符串'

  • 模式匹配：

    • like 关键字：

      1
2
3
4

      select  -- 可以添加distinct来去除重复项
	字段列表  -- 查询所有字段可以简写为 *
from table_name as s
where s.name like %|_% escape '|'  -- 匹配任何包含_的字符串
      • 百分号 %：匹配任意子串
        • 例如以 substring 开头：substring%
        • 匹配任何包含 substring 的字符串：%substring%
      • 下划线 _：匹配任意字符
      • 定义转义字符：escape '转义字符'

    • regexp：正则表达式匹配：

      1
2
3

      select *
from users
where name regexp '^张.$';

  • 字符串串联：

    • concat(str1, str2, ...)：

      1
2

      select concat(first_name, ' ', last_name) as full_name
from users;

    • str1 || str2：

      1
2

      select first_name || ' ' || last_name as full_name
from users;

    • concat_ws(separator, str1, str2, ...)：允许指定分隔符：

      1
2

      select concat_ws(', ', first_name, last_name) as full_name
from users;

    • 大小写以及首尾去除空格：

       1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

      -- 转小写
select 字段列表
from table_name as s
where s.name = lower('hEllO world')  -- 与hello world匹配的字符串

-- 转大写
select 字段列表
from table_name as s
where s.name = upper('hEllO world')  -- 与HELLO WORLD匹配的字符串

-- 去除首尾空格
select 字段列表
from table_name as s
where s.name = trim(' hEllO world ')  -- 与hEllO world匹配的字符串
      • 大写：upper(str)
      • 小写：lower(str)
      • 去除空格：trim(str)

📝 where子句谓词

  • between and：说明一个值位于某个范围，大于等于 min_value，小于等于 max_value：

    1
2
3

    select columns
from table as t
where t.age between 10 and 100  -- 年龄在10岁到100岁

  • 元组比较：元组之间使用字典序进行比较：

    1
2
3

    select name, course_id
from instructor, teaches
where (instructor.ID, dept_name) = (teaches.ID, 'Biology');

🔗 集合运算

所有的集合运算默认去除重复，若保留重复则在集合运算符后面加上关键字 all。

  • 查找在 2009 年秋季或 2010 年春季开设的课程（使用并运算）：

    1
2
3

    (select course_id from section where sem = 'Fall' and year = 2009)
union
(select course_id from section where sem = 'Spring' and year = 2010)

  • 查找在 2009 年秋季和 2010 年春季开设的课程（使用交运算）：

    1
2
3

    (select course_id from section where sem = 'Fall' and year = 2009)
intersect
(select course_id from section where sem = 'Spring' and year = 2010)

  • 查找在 2009 年秋季开课但未在 2010 年春季开课的课程（使用差运算）：

    1
2
3

    (select course_id from section where sem = 'Fall' and year = 2009)
except
(select course_id from section where sem = 'Spring' and year = 2010)

📊 分组查询

基本聚合函数

函数 功能
sum 求和
avg 平均值
count 计数
max 最大值
min 最小值

提示

  • null 不参与聚合函数的运算
  • 使用关键字 * 指代所有
  • 可以在聚集表达式中使用关键字 distinct 修饰被聚合的属性来删除重复的元组，如 count(distinct id)

group by子句

将聚集函数用于一组元组集上而利用属性进行的分组操作：

1
2
3
4

-- 正确示范
select dept_name, avg(salary) as avg_salary
from instructor
group by dept_name;

提示

  • select 子句中的字段为用于分组的字段（group by 中的属性，当只有一个聚合函数时可以不存在）和聚合函数（保证 select 子句中非聚集的属性只能是出现在 group by 子句）

    1
2
3
4
5

    -- 错误查询，select 子句中的非聚合属性ID
-- 没出现在group by子句中
select dept_name, ID, avg(salary)
from instructor
group by dept_name;

  • 在默认情况下，系统按照 group by 子句中指定的列升序排列，但是可以使用 order by 子句指定新的排列顺序。

having子句

对 group by 后的分组进行过滤操作，可以使用聚集函数：

1
2
3
4
5

select course_id, semester, year, sec_id, avg(tot_cred)
from takes natural join student
where year = 2009
group by course_id, semester, year, sec_id
having count(ID) >= 2;

提示

having 和 where 的区别：

  • 执行时机不同：where 是分组之前进行过滤，不满足 where 条件不参与分组；而 having 是分组之后对结果进行过滤。
  • 判断条件不同：where 不能对聚合函数进行判断，而 having 可以。

保证出现在 having 子句中的非聚集的属性只能出现在 group by 子句中：

1
2
3
4
5
6

-- 错误示范，having子句中非聚集的属性salary
-- 没有出现在group by中
select department, AVG(salary) as avg_salary
from employees
group by department
having salary > 5000;

查询的执行顺序

  1. 根据 from 子句计算出关系
  2. 若出现了 where 子句则将 where 子句中的谓词应用到 from 子句的结果关系上
  3. 若出现了 group by 子句，满足 where 子句的元组通过 group by 子句形成分组，若没有 group by 子句则满足 where 子句谓词的整个元组集被当成一个分组
  4. 若出现了 having 子句，将被应用到每一个分组，不满足子句谓词的分组将被抛弃
  5. select 子句利用剩下的分组产生出查询结果中的元组，在每个分组上应用聚集函数来得到单个结果元组
查询执行顺序

🔗 多表查询

连接查询

笛卡尔积

描述：输出两个关系中所有的元组对（无论在共同属性上的取值是否相同）。

若关系 r 中属性和关系 s 中的属性相同则区分命名：

  • 属性名不相同
笛卡尔积-属性名不相同
  • 属性名相同
笛卡尔积-属性名相同

示例代码：

1
2
3
4

-- 隐式笛卡尔积
table a, table b
-- 显式笛卡尔积
table a join table b

内连接

描述：查询 A 表和 B 表相交的部分（有条件的笛卡尔积）。

性质：不保留未匹配元组的连接运算（合并具有同一列的两个以上的表的行，结果集中不包含一个表与另一个表不匹配的行）。

内连接

语法：

1
2
3
4
5
6
7
8
9

-- 隐式内连接
select 字段列表
from table a, table b
where condition

-- 显式内连接
select 字段列表
from table a join table b
on 连接条件

外连接

描述：通过在表中创建包含空值元组的方式保留未匹配元组的连接运算。

  • 左外连接：只保留出现在左外连接运算之前（左边）的关系中的元组。

    左外连接

  • 右外连接：只保留出现在右外连接运算之后（右边）的关系中的元组。

    右外连接

  • 全外连接：保留出现在两个关系中的元组。

    全外连接

语法：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

-- 左外连接
select 字段列表
from table a left join table b
on 连接条件

-- 右外连接
select 字段列表
from table a right join table b
on 连接条件

-- mysql不直接支持全外连接，可以通过左外连接和右外连接union操作后实现
select 字段列表
from table a left join table b
on 连接条件
union
select 字段列表
from table a right join table b
on 连接条件

自然连接

描述：从 A 表和 B 表中输出在具有相同名字的所有属性上取值相同的元组对，基于两个表中共同属性的值相等的元组进行合并。

自然连接 
1
2
3
4

-- 自然连接
select 字段列表
from table a natural join table b
on 连接条件

提示

自然连接和内连接的区别：

若两张表具有同名的属性，自然连接会删除重复的属性而内连接不会删除而是保留重复的属性。

嵌套子查询

子查询：SQL语句中嵌套 select 语句。

类别：

  • 标量子查询：子查询返回的结果为单个值
  • 列子查询：子查询返回的结果为一列
  • 行子查询：子查询返回的结果为一行
  • 表子查询：子查询返回的结果为多行多列

子查询的位置：

  • select 后面：标量子查询（作为所需要的一个字段）
  • from 后面：表子查询
  • where/having（作为条件的一部分）：标量子查询/列子查询/行子查询

with子句

  • with 定义了临时关系，只对 with 子句所在的查询有效
  • 其中 as 子句内定义临时关系，后面紧跟查询语句
  • 可以使用多个 as 对应多个临时关系
with子句 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10

with it department as (
    select department, AVG(salary) as avg_salary
    from employees
    where department = 'IT'
    group by department
)
select e.name, e.salary, it.avg_salary
from employees e
join it department it on e.department = it.department
where e.salary > it.avg_salary;

where子句中的子查询

子查询类型：

  • 行子查询：子查询返回的结果为一行
  • 列子查询：子查询返回的结果为一列
检测集合的成员资格（列子查询）

  • in：检测元组是否是集合中的成员：

    1
2
3
4
5
6
7

    -- 查找 2009 年秋季和 2010 年春季提供的课程
select distinct course_id
from section
where semester = 'Fall' and year = 2009 and
course_id in (select course_id
              from section
              where semester = 'Spring' and year = 2010);

  • not in：检测元组是否不是集合中的成员：

    1
2
3
4
5
6
7

    -- 查找 2009 年秋季提供的课程，但不在 2010 年春季提供的课程
select distinct course_id
from section
where semester = 'Fall' and year = 2009 and
course_id not in (select course_id
                   from section
                   where semester = 'Spring' and year = 2010);
空关系测试（行子查询）

  • exist：在作为参数的子查询非空时返回 true

  • not exist：在作为参数的子查询为空时返回 true

  • 关系 A 包含关系 B 可以写为 not exists (B except A)：

     1
 2
 3
 4
 5
 6
 7
 8
 9
10

    -- 查找所有参加过生物系提供的所有课程的学生
select distinct S.ID, S.name
from student as S
where not exists ((select course_id
                    from course
                    where dept_name = 'Biology')
                    except
                    (select T.course_id
                     from takes as T
                     where S.ID = T.ID));
测试重复元组（行子查询）

unique：如果作为参数的子查询结果中没有重复的元组，则返回 true：

1
2
3
4
5
6

select T.course_id
from course as T
where not unique (select R.course_id
				  from section as R
				  where T.course_id = R.course_id
				  and R.year = 2009);
集合的比较（列子查询）

  • all：指代集合中的每一个元素：

    1
2
3
4
5
6

    -- 查找薪水大于 Biology 系中所有教员薪水的所有教员的姓名
select name
from instructor
where salary > all (select salary
                    from instructor
                    where dept_name = 'Biology');

  • some：指代集合中的其中一个元素：

    1
2
3
4
5
6

    -- 查找薪水高于 Biology 系中某些（至少一名）讲师的讲师的姓名
select name
from instructor
where salary > some (select salary
                     from instructor
                     where dept_name = 'Biology');

👁️ 视图

描述

一种虚拟表，它基于 SQL 查询结果创建。视图并不存储数据，而是动态地从基础表中提取数据。这使得视图像一个窗口，可以用来查看和操作表的数据。

特点

  • 虚拟表：
    • 视图本身不存储数据，它的内容来自于定义视图时的查询语句。
    • 每次访问视图时，都会动态执行查询以生成视图的数据。
  • 逻辑隔离：视图是基础表的一种逻辑表示，对视图的操作不会直接修改基础表。
  • 安全性：通过视图，可以限制用户访问表的部分数据，而不是直接授予表的访问权限。
  • 可简化复杂查询：视图可以封装复杂的 SQL 查询逻辑，从而简化对数据的访问。

创建过程

视图关系在概念上包含查询结果中的元组，但并不进行预计算和存储（数据库系统存储视图的定义本身，而不存储定义该视图的查询表达式的执行结果）。相反，数据库系统存储与视图关系相关联的查询表达式。当视图关系被访问时，其中的元组是通过计算查询结果而被创建出来的。从而，视图关系是在需要的时候才被创建的。

在查询中使用视图

在任何给定时刻，视图关系中的元组集是该时刻视图定义中的查询表达式的计算结果。当我们定义一个视图时，数据库系统存储视图的定义本身，而不存储定义该视图的查询表达式的执行结果。一旦视图关系出现在查询中，它就被已存储的查询表达式代替。因此，无论我们何时执行这个查询，视图关系都被重新计算。

物化视图

  • 定义：创建一个物理表，其中包含定义视图的查询结果中的所有元组。
  • 维护：如果查询中使用的关系已更新，物化视图结果将过时，因此需要维护视图，通过在底层关系更新时更新视图。

语法 

1
2
3

-- 创建视图（可以通过其他视图进行创建）
create view view_name as
<查询表达式>
© 2022 - 2026 Tyritic
그 경기 끝나고 좀 멍하기 있었는데 여러분 이제 살면서 여러가
使用 Hugo 构建
主题 Stack 由 Jimmy 设计