Water's blog

深色模式

中级SQL ​

连接表达式 ​

  • 连接: 也称联接, 或者多表查询, 从多个相关的表中查询数据
  • 连接运算: 把多个表中的行按给定的条件进行拼接从而形成新表
  • 连接类型:
    • 内连接 (INNER JOIN): 通过某个字段相等, 查询出两张表中满足连接条件的记录
      • 等值连接
      • 自然连接
      • 不等值连接
    • 外连接 (OUTER JOIN): 返回匹配的行以及不匹配的行
      • 左外连接 (LEFT JOIN): 返回左表的所有行, 以及右表中匹配的行(没有就是NULL)
      • 右外连接 (RIGHT JOIN): 返回右表的所有行, 以及左表中匹配的行(没有就是NULL)
      • 全外连接 (FULL JOIN): 返回两个表中的所有行, 不匹配的行用NULL填充(使用UNION实现)
  • 交叉连接 (CROSS JOIN): 返回两个表的笛卡尔积, 即每个左表行与每个右表行组合

内连接 ​

  • 等值连接: 连接条件使用等号比较, 查询结果包括其中的重复列
  • 非等值连接: 连接条件使用等号之外的运算符进行比较
  • 自然连接: 连接条件使用等号比较, 但删除连接表中的重复列
sql
-- 等值连接
SELECT A.column1, A.column2, B.column3
FROM TableA A
-- INNER表示内连接, 可省略
INNER JOIN TableB B ON A.common_field = B.common_field;
-- 自然连接
SELECT *
FROM TableA A
NATURAL JOIN TableB B;
-- 非等值连接
SELECT A.column1, A.column2, B.column3
FROM TableA A
INNER JOIN TableB B ON A.value < B.value;

自然连接 ​

将多个表的同名列值进行比较, 把表中满足连接条件(相等)的行组合起来作为查询结果

eg:

sql
-- Employees表: EmployeeID, Name, DepartmentID
-- Departments表: DepartmentID, DepartmentName
sql
SELECT *
FROM Employees
NATURAL JOIN Departments;

<=>

sql
SELECT e.EmployeeID, e.Name, e.DepartmentID, d.DepartmentName
FROM Employees e
INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID;

或者使用USING子句:

sql
SELECT * 
FROM Employees e
INNER JOIN Departments d USING (DepartmentID);

在ON子句中, 如果SELECT *会出现重复列, 需要手动指定列名.

当没有同名列时, 会退化为笛卡尔积.

等值与非等值查询 ​

一般查询条件的格式为:

[表1].[列名] [比较运算符] [表2].[列名]

  • 等值查询: 使用等号(=)作为比较运算符
  • 非等值查询: 使用其他比较运算符(>, <, >=, <=, <>)进行比较

自然连接一定是等值连接, 但等值连接不一定是自然连接

等值连接要求相等的值可能不是同名列, 而自然连接要求相等的值必须是同名列

自然连接会自动去除重复列, 而等值连接不会, 会保留a.column1和b.column1

外连接 ​

内连接只会展示两个表中满足条件的行, 而外连接会展示一个表中所有的行, 以及另一个表中满足条件的行

  • 左外连接 (LEFT JOIN): 返回左表的所有行, 以及右表中匹配的行(没有就是NULL)
sql
SELECT A.column1, A.column2, B.column3
FROM TableA A
LEFT [OUTER] JOIN TableB B ON A.common_field = B.common_field;

OUTER可省略

  • 右外连接 (RIGHT JOIN): 返回右表的所有行, 以及左表中匹配的行(没有就是NULL)
sql
SELECT A.column1, A.column2, B.column3
FROM TableA A
RIGHT JOIN TableB B ON A.common_field = B.common_field;
  • 全外连接 (FULL JOIN): 返回两个表中的所有行, 不匹配的行用NULL填充(使用UNION实现)
sql
SELECT A.column1, A.column2, B.column3
FROM TableA A
LEFT JOIN TableB B ON A.common_field = B.common_field
UNION
SELECT A.column1, A.column2, B.column3
FROM TableA A
RIGHT JOIN TableB B ON A.common_field = B.common_field;

交叉连接 ​

又称笛卡尔连接或者叉乘, 返回两个表的笛卡尔积, 即每个左表行与每个右表行组合

sql
SELECT A.column1, B.column2
FROM TableA A
CROSS JOIN TableB B;

<=>

sql
SELECT A.column1, B.column2
FROM TableA A, TableB B;

自连接 ​

  • 自连接: 把同一个表作为两个不同的表来使用, 通过别名区分

视图 ​

视图是一个虚拟表, 它的内容由查询定义, 可以简化复杂查询, 提高数据安全性

基本概念

省流: 相当于给业务提供一个便携的接口, 不用自己拼字段了

创建视图 ​

sql
CREATE VIEW [<owner>].<view_name> [ (column1, column2, ...) ]
AS
SELECT column1, column2, ...
FROM table_name
WHERE condition
[WITH CHECK OPTION];

其中,

  • owner: 视图的所有者, 可选
  • view_name: 视图的名称
  • column1, column2, ...: 视图的列名, 可选, 如果省略, 则使用SELECT语句中的列名
  • table_name: 视图基表的名字
  • WITH CHECK OPTION: 可选, 确保通过视图插入或更新的数据符合视图的定义条件, 迫使通过视图执行的所有数据修改语句必须符合视图定义中设置的条件

水平视图 ​

  • 水平视图: 只包含基表的部分行, 通过WHERE子句过滤行(不限制列)
sql
CREATE VIEW ActiveEmployees AS
SELECT *
FROM Employees
WHERE Status = 'Active';

投影视图 ​

  • 投影视图: 只包含基表的部分列, 通过SELECT子句选择列
sql
CREATE VIEW EmployeeNames AS
SELECT EmployeeID, Name
FROM Employees;

联合视图 ​

  • 联合视图: 由多个表连接而成的视图
sql
CREATE VIEW EmployeeDepartments AS
SELECT e.EmployeeID, e.Name, d.DepartmentName
FROM Employees e
INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID;

查看视图结构 ​

mysql:

sql
desc view_name;

sql server:

sql
sp_help view_name;

修改视图 ​

sql
ALTER VIEW [<owner>].<view_name> [ (column1, column2, ...) ]
AS
SELECT column1, column2, ...
FROM table_name
WHERE condition
[WITH CHECK OPTION];

删除视图 ​

sql
DROP VIEW [IF EXISTS] [<owner>.]<view_name>;

使用视图 ​

查询视图 ​

跟普通表一样查询视图

sql
SELECT *
FROM view_name
WHERE condition;

更新视图 ​

更新视图

视图的优缺点 ​

  • 优点:
    • 简化复杂查询
    • 提高数据安全性
    • 提供数据抽象
    • 便于维护
  • 缺点:
    • 性能开销
    • 修改的限制

物化视图与普通视图的区别 ​

  • 普通视图: 是一个虚拟表, 每次查询时动态生成数据, 不占用存储空间
  • 物化视图: 是一个实际存在的表, 存储查询结果, 占用存储空间, 需要定期刷新以保持数据同步

ORACLE支持物化视图, MySQL, SQL server不支持

物化视图的优点:

  • 提高查询性能: 预先计算并存储查询结果, 减少实时计算开销
  • 减少资源消耗: 可以将部分数据存储到内存中, 避免频繁访问磁盘
  • 支持复杂查询: 适用于复杂的聚合和连接操作

物化视图的缺点:

  • 一致性问题: 需要定期刷新以保持数据同步, 可能导致数据不一致
  • 存储开销: 占用额外的存储空间
  • 更新效率问题: 物化视图的更新可能比较复杂, 影响性能

省流: 比较麻烦的缓存

完整性约束 ​

为了维护数据库中数据与现实世界的一致性, 关系数据库中的数据必须满足一定的限制, 这些限制称为完整性约束

完整性约束可以由用户检查, 也可以由系统检查. 只在数据库更新时才需要进行.

域完整性约束 ​

域完整性约束规定每一个属性的值应该是其值域中的值.

通常是指定一个数据类型, 或者是能否为NULL

实体完整性约束 ​

关系中主码的值不能为NULL, 且每个元组的主码值必须唯一

主码包含的 所有属性 都不能取空值

大部分DBMS支持实体完整性约束检查, 但是不是强制的.

引用完整性约束 ​

引用完整性约束是不同关系之间或同一关系的不同元组间的约束, 它规定不允许引用不存在的元组.

设F是关系R的一个或一组属性, 如果F与关系S的主码K相对应, 则称F是关系R的外码, 并且称关系S为基本关系(基本表), 关系R为以来关系(依赖表)

外码F要么取空值, 要么取基本关系S中某个元组的主码K的值

在引用完整性约束中, 基本关系S与依赖关系R可以是同一个关系

用户自定义完整性约束(CHECK子句) ​

用户自定义完整性约束是指用户根据实际应用的需要, 对某些属性值所满足的条件进行定义.

eg:

sql
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Name VARCHAR(100),
    Age INT CHECK (Age >= 18), -- 年龄必须大于等于18
    Salary DECIMAL(10, 2) CHECK (Salary > 0) -- 薪水必须大于0
);

SQL用户与授权 ​

SQL用户 ​

Mysql的用户包括普通用户和root用户.

  • root用户: 超级管理员, 拥有所有权限, 包括创建/删除用户, 修改用户密码等.
  • 普通用户: 只能执行被授权的操作, 不能创建/删除用户, 也不能修改其他用户的权限.

用户管理包括管理用户的账号, 权限等.

权限表 ​

安装mysql时会自动创建一个名为mysql的数据库, 存储权限表. 最重要的是user表和db表.

  • user表: 39个字段, 大致分为用户列, 权限列, 安全列和资源控制列.
    • 用户列: 存储用户名, 主机名等信息
    • 权限列: 存储用户的全局权限信息
      • 高级管理权限: 主要对数据库进行管理, 如创建数据库, 创建用户等
      • 普通权限: 主要操作数据库, 如查询权限, 修改权限等
    • 安全列: 存储用户的密码信息
    • 资源控制列: 存储用户的资源使用限制信息
  • db表: 存储数据库级别的权限信息.
    • 用户列: 存储用户名, 主机名, 数据库名等信息
    • 权限列: 存储用户在特定数据库上的权限信息

创建用户 ​

  • 使用CREATE USER
sql
CREATE USER <username> [IDENTIFIED BY '<password>'];

username由user和host构成, IDENTIFIED BY设置用户的密码

  • 使用INSERT
sql
INSERT INTO mysql.user (User, Host, authentication_string, ssl_cipher, x509_issuer, x509_subject)
VALUES ('username', 'host', PASSWORD('password'), '', '', '');
  • 使用GRANT
sql
GRANT priv_type ON database.TABLE
TO user[IDENTIFIED BY 'password'];
  • priv_type: 权限类型
  • database.table: 新用户的权限范围

删除普通用户 ​

  • 使用DROP USER
sql
DROP USER <username>;
  • 使用DELETE
sql
DELETE FROM mysql.user
WHERE User = 'user' AND Host = 'host';

分配权限 ​

Mysql 中权限分配是按照 user 表, db 表, table_priv 表和 columns_priv 表的顺序进行分配的. 逐个判断是否为Y, 如果是Y则赋予该权限, 否则继续往下判断.

授权 ​

sql
GRANT priv_type [column_list] ON database.TABLE
TO username
[WITH with_option]
  • column_list: 可选, 指定哪些列, 如果省略则表示所有列
  • priv_type: 权限类型, 可以是ALL PRIVILEGES表示所有权限, 也可以是具体的权限类型, 多个权限类型用逗号分隔

SELECT, INSERT等

  • with_option: 可选

GRANT OPTION: 允许被授权用户将其权限授予其他用户

WITH MAX_QUERIES_PER_HOUR n: 限制用户每小时最多执行n个查询

WITH MAX_UPDATES_PER_HOUR n: 限制用户每小时最多执行n个更新操作

...

撤销权限 ​

sql
REVOKE priv_type [column_list] ON database.TABLE
FROM username;

查看权限 ​

sql
SELECT * FROM mysql.user;
SHOW GRANTS FOR 'username'@'host';

Copyright © 2024-present Water