SQL自用小结

推荐一下这个知识点总结
《数据库系统概论》第五版 +学习笔记总目录

1. SQL概述

SQL(Structured Query Language,结构化查询语言)是一种用于定义、查询、更新和控制关系数据库的标准化语言。

  • 它包含了数据定义语言(DDL)、数据操纵语言(DML)、数据查询语言(DQL)、多个方面的功能。

    SQL的设计初衷是让用户能够以一种高效、简洁的方式与数据库交互,实现数据管理和操作。

SQL是—个通用的、功能极强的关系数据库语言

2. SQL的特点

  • 综合性:SQL集成了数据定义(DDL)、数据操纵(DML)、数据查询(DQL)功能

    • 可以独立完成数据库生命周期中的全部活动:
      • 定义、修改和删除
      • 查询和更新
      • 重构和维护
      • 安全性、完整性控制,以及事务控制
    • 用户数据库投入运行后,可根据需要随时修改模式,不影响数据的运行
    • 数据操作符统一
  • 非过程化:SQL是一种声明性语言,用户只需指定“做什么”,而不必关心“怎么做”

    • 即无需定义数据的存取路径,数据的查询和操作由数据库管理系统(DBMS)自动优化完成。

    • 非关系数据模型的数据操纵语言“面向过程',必须制定存取路径

    • 存取路径的选择以及SQL的操作过程由DBMS自动完成。

  • 面向集合

    • 层次、网状模型采用面向记录的操作方式,操作对象是—条记录

    • SQL的操作对象是集合(元组的集合)

  • 统一的语法结构:SQL具有统一的语法规则,既可以作为独立的语言,也可嵌入到高级编程语言(如C、C++、Java)中使用,提供了高度的灵活性。

  • 简洁易学:SQL语言相对简洁,核心功能主要由9个关键命令组成(如SELECT、INSERT、UPDATE、DELETE、CREATE、DROP等),使其易于学习和掌握。

在这里插入图片描述

  • 视图(view)

    • 视图是—个虚表,从—个或几个基本表导出的表

    • 数据库中只存放视图的定义而不存放视图对应的数据

  • 基本表(basetable)

    • 本身独立存在的表,一个关系就对应一个基本表
    • 一个(或多个)基本表对应一个存储文件
    • 一个表可以带若干索引,索引也存放在存储文件中
  • 存储文件(storedfile)

    • 存储文件的逻辑结构组成了关系数据库的内模式
    • 物理结构是任意的,对用户透明

3. 数据定义(DDL)

  • 数据库

    • 数据文件

      主要数据文件(.mdf) (只有一个)

      次要数据文件(.ndf) (可以有多个)

    • 事务日志文件

  • SQL的数据定义功能

    • 数据库模式定义(Schema)
    • 表定义(Table)
    • 视图(View)和索引(Index)的定义

在这里插入图片描述

3.1 数据库的定义与删除

创建数据库

使用CREATE DATABASE语句,例如:

CREATE DATABASE SchoolDB;
删除数据库

使用DROP DATABASE语句,例如:

DROP DATABASE SchoolDB;

3.2 表的定义

创建表

通过CREATE TABLE语句定义表的结构、列名、数据类型及约束条件。例如:

CREATE TABLE Students (StudentID CHAR(8) PRIMARY KEY,  commit '主码'  '<列级完整性约束条件>'Name VARCHAR(50) NOT NULL,    commit '非空'BirthDate DATE UNIQUE,               commit '取唯一值'Major VARCHAR(50),FOREIGN KEY (Cpno) REFERENCES Course(~no)  commit'<表级完整性约束条件>'
);
  • PRIMARY KEY与UNIQUE的区别?
    • 可使用UNIQUE约束确保在非主键列中不输入重复值。
    • 一个表可定义多个UNIQUE约束,而只能定义—个PRIMARY
    • 允许空值的列上可以定义UNIQUE约束,而不能定义PRIMARY KEY约束。
修改表
  1. 增加列和完整性约束

    • 语法:ALTER TABLE <表名> ADD <新列名> <数据类型> [完整性约束]
    • 示例:ALTER TABLE Student ADD S_entrance DATE;
      解释:向Student表增加名为S_entrance的列,数据类型为DATE,新列默认值为空。
  2. 删除列

    • 语法:ALTER TABLE <表名> DROP COLUMN <列名> [CASCADE | RESTRICT]
    • 示例:ALTER TABLE Student DROP COLUMN Sage CASCADE;
      解释:删除Student表中的Sage列,若使用CASCADE,引用该列的对象也会被删除;若使用RESTRICT,则当该列被引用时会拒绝删除。
  3. 删除约束条件

    • 语法:ALTER TABLE <表名> DROP CONSTRAINT <约束名> [CASCADE | RESTRICT]
    • 示例:ALTER TABLE Student DROP CONSTRAINT PK_Student;
      解释:删除Student表上的主键约束PK_Student
  4. 修改列定义

    • 语法:ALTER TABLE <表名> ALTER COLUMN <列名> <新数据类型>
    • 示例:ALTER TABLE Student ALTER COLUMN Sage INT;
      解释:将Student表中Sage列的数据类型从字符型改为整型,这可能会影响已有数据。
  5. 增加表级完整性约束

    • 语法:ALTER TABLE <表名> ADD <表级完整性约束>
    • 示例:ALTER TABLE Course ADD NOT NULL(Cname);
      解释:为Course表中的Cname列增加非空约束。

注意事项:

  • 增加的列默认值为NULL,即使已有数据存在。
  • 修改数据类型时需谨慎,可能导致数据丢失或冲突。
删除基本表
  • RESTRICT:删除表有约束。如果表被其他对象引用,无法删除。

    • 例如:表存在外键关系或依赖对象时,系统会拒绝删除。
  • CASCADE:没有删除限制。删除表时,依赖该表的对象(如索引、视图、触发器)也一并删除。

  • 默认行为:如果不指定,默认为RESTRICT

示例:
DROP TABLE Student CASCADE;
  • 基本表定义被删除,数据被删除
  • 表上建立的索引、视图、触发器等—般也将被删除

3.3 模式的定义

  • 关系数据库管理系统提供层次化的数据库对象命名机制 -

    一个关系数据库管理系统的实例(Instance)中可以建立多个数据库

    —个数据库中可以建立多个模式

    —个模式下通常包括多个表、视图和索引等数据库对象

  • 定义模式实际上就定义了—个命名空间

  • 简而言之,模式就是数据库对象的集合

创建模式

语法

CREATE SCHEMA schema_name AUTHORIZATION user_name;
  • schema_name:指定要创建的模式名称。
  • 创建模式必须具有DBA权限,或获得了DBA授予的CREATE SCHEMA权限。
  • AUTHORIZATION user_name:指定拥有该模式的用户,表示该用户对模式拥有所有权限。

示例

CREATE SCHEMA School AUTHORIZATION admin;

该示例为用户admin创建了一个名为School的模式。School模式可以包含与学校管理相关的数据库对象(如学生表、教师表等)。

删除模式

语法

DROP SCHEMA schema_name [CASCADE | RESTRICT];
  • CASCADE:删除模式时,自动删除模式中包含的所有对象。
  • RESTRICT:如果模式中包含对象(下属的数据库对象(如表、视图等)),则拒绝删除操作(默认行为)。

示例

DROP SCHEMA School CASCADE;

该示例删除了School模式以及模式中定义的所有对象(如表、视图、索引等)。

3.4 模式与表

每—个基本表都属于某—个模式,—个模式包含多个基本表

定义基本表所属模式
  1. 方法一:在表名中显式指定模式名
    直接在表名中给出模式名,创建表时指定所属的模式。

    • Create table "S-C-SC".Student(...); // Student 表所属的模式是 S-C-
  2. 方法二:在创建模式时同时创建表
    在同一个创建语句中创建模式和表。这样表会自动属于创建的模式。

  3. 方法三:设置默认所属模式
    若没有指定模式,系统根据当前的搜索路径确定表所属的模式。

    • 系统会使用搜索路径中第一个存在的模式作为数据库对象的默认模式。
    • 如果路径中指定的模式不存在,系统会报错。
搜索路径:
  • 查看当前搜索路径:SHOW SEARCH_PATH;
  • 默认值:$user, PUBLIC

在创建表时,若未明确指定模式,系统会按照搜索路径的顺序查找合适的模式。

3.5 数据类型

  • SQL中域的概念用数据类型来实现
  • 定义表的属性时需要指明其数据类型及长度

以下是将SQL的数据类型和属性分为两个详细表格:

数据类型表格
数据类型描述使用格式
整数类型存储整数值,分为tinyint, smallint, mediumint, int, bigintINT(5):括号内数字表示显示宽度。
小数类型存储带精度的小数值,分为float, double, decimalDECIMAL(10, 2):10位数字,2位小数。
字符串类型char为定长字符串,varchar为变长字符串,text存储大段文字。VARCHAR(255):最大255个字符。
二进制类型blob存储二进制数据,常用于图像和文件等。BLOB:存储最大64KB的数据。
日期/时间类型year, timestamp, date, datetime, time用于存储日期和时间。DATETIME:存储日期和时间。
属性表格
属性描述使用格式
NULL允许字段为空值,默认不允许为空。column_name INT NULL
DEFAULT设置字段的默认值。DEFAULT 'default_value'
PRIMARY KEY用于唯一标识记录,不可重复。PRIMARY KEY (column_name)
AUTO_INCREMENT自动为新记录生成唯一的递增值。AUTO_INCREMENT
UNIQUE确保字段值唯一,防止重复。UNIQUE (column_name)
COMMENT为字段添加注释。COMMENT 'This is a comment'

3.6 索引

  • 谁可以建立索引?

    • 数据库管理员(DBA)或表的所有者(即建立该表的人)可以创建索引。
    • 数据库管理系统(DBMS)通常会自动在以下列上建立索引:
      • PRIMARY KEY(主键)
      • UNIQUE(唯一键)
  • 谁维护索引?

    • 索引的维护工作由DBMS自动完成。
  • 谁使用索引?

    • DBMS会自动决定是否使用索引以及使用哪些索引。
建立索引
CREATE [UNIQUE] [CLUSTER] INDEX <索引名> ON <表名>(<列名> [<次序>] [, <列名> [<次序>]]…);
  • <表名>:需要建立索引的基本表的名称。
  • <列名>:索引可以建立在表的一列或多列上,列名之间用逗号分隔。
  • <次序>:指定索引值的排列顺序,升序(ASC)或降序(DESC)。默认值为升序(ASC)。
  • UNIQUE:表示此索引是唯一索引,每个索引值只对应唯一的数据记录。
  • CLUSTER:表示要创建的是聚簇索引。
  1. Student表按学号升序建立唯一索引:
    CREATE UNIQUE INDEX Stusno ON Student(Sno);
    
  2. Course表按课程号升序建立唯一索引:
    CREATE UNIQUE INDEX Coucno ON Course(Cno);
    
  3. SC表按学号升序和课程号降序建立唯一索引:
    CREATE UNIQUE INDEX SCno ON SC(Sno ASC, Cno DESC);
    
唯一索引
  • 已包含重复值的列不能建立UNIQUE索引。
  • 对某列建立UNIQUE索引后,DBMS会在插入新记录时自动检查该列是否包含重复值,这相当于增加了UNIQUE约束
聚簇索引
  • 建立聚簇索引后,基本表中的数据将按指定的聚簇属性值的顺序(升序或降序)存放。即,聚簇索引的顺序与表中记录的物理顺序一致。
  • 示例:
    CREATE CLUSTER INDEX Stusname ON Student(Sname);
    
    在此例中,Student表的Sname列上建立了聚簇索引,Student表中的记录将按照Sname列的升序存储。
修改索引

语法格式:

ALTER INDEX <旧索引名> RENAME TO <新索引名>;
  • 示例3.14:将SC表的SCno索引名修改为SCSno:
    ALTER INDEX SCno RENAME TO SCSno;
    
删除索引

语法格式:

DROP INDEX <索引名>;
  • 删除索引时,系统会自动从数据字典中删除该索引的描述信息。

  • 示例3.15:删除Student表的Stusname索引:

    DROP INDEX Stusname;
    
  • 如果需要修改索引,除了使用ALTER INDEX,还可以先删除旧索引,再创建新索引的方式进行修改。

索引的优缺点

优点:

  • 保证数据的唯一性:通过创建唯一索引,可以确保每条数据记录的唯一性。
  • 加快数据检索速度:索引能够显著提高查询性能,尤其是在需要频繁检索、分组或者排序数据时。
  • 提高表连接效率:索引在表与表之间的连接操作中能加速查询。

缺点:

  • 创建与维护索引耗时:系统在创建和维护索引时需要花费一定的时间和资源。
  • 占用存储空间:每个索引都需要额外的存储空间。
  • 影响数据操作效率:虽然索引加快了检索速度,但在插入、修改或删除数据时,由于索引也需要更新,可能会消耗额外的时间。

3.7 数字字典

数据字典是关系数据库管理系统(RDBMS)中的一组系统表,它用于记录数据库中的各种定义信息,包括:

  • 关系模式定义:描述数据库中各表的结构和属性。
  • 视图定义:记录数据库中视图的结构和相关信息。
  • 索引定义:保存关于数据库中已创建的索引的信息。
  • 完整性约束定义:记录数据库中各类约束条件,如主键、外键和唯一性等。
  • 用户权限:管理各类用户对数据库的操作权限,控制访问权限。
  • 统计信息:存储与数据分布、查询性能等相关的统计数据。

执行SQL中的数据定义语言(DDL)语句时,实际上是对数据字典中的相应信息进行更新。

此外,数据字典中的信息是查询优化和处理过程中非常重要的参考依据,它帮助数据库管理系统提高查询效率。

4. 数据查询

下面是重写后的查询语句部分:

4.1 基本查询语句格式

SELECT [ALL | DISTINCT] <目标列表达式> [, <目标列表达式>] … 
FROM <表名或视图名> [, <表名或视图名>] … 
[ WHERE <条件表达式> ] 
[ GROUP BY <列名> [ HAVING <条件表达式> ] ] 
[ ORDER BY <列名> [ ASC | DESC ] ] ;

解释:

  • SELECT 子句:用于指定要显示的属性列。
  • FROM 子句:指定查询的数据来源(表或视图)。
  • WHERE 子句:用于指定筛选条件。
  • GROUP BY 子句:用于根据某列的值对查询结果进行分组。通常与聚合函数一同使用。
  • HAVING 子句:对分组后的数据进行筛选。
  • ORDER BY 子句:对查询结果进行排序,可选择升序(ASC)或降序(DESC)。

4.2 单表查询

  • 注意事项:
    • 使用 IS NULLIS NOT NULL 判断列是否为空。
    • IS 不能替代为 =
    • 在使用 GROUP BY 后,SELECT 子句中只能出现分组属性和聚合函数。
    • HAVINGWHERE 的区别:
      • WHERE 子句作用于基表或视图,筛选满足条件的行。
      • HAVING 子句作用于分组后的数据,筛选满足条件的组。

4.3 连接查询 ⭐️

  • 连接查询:涉及多个表的查询。
  • 连接条件或谓词:用于指定表之间的连接条件。
  • 格式
    [<表1>.]<列名1> <比较运算符> [<表2>.]<列名2>
    
  • 比较运算符:=><>=<=!=
  • 连接字段:连接谓词中的列名,字段类型需可比,名称可以不同。
1. 等值连接与非等值连接
  • 等值连接:连接条件使用 = 运算符。
  • 示例:
    SELECT Student.*, SC.* 
    FROM Student, SC 
    WHERE Student.Sno = SC.Sno;
    

连接操作的执行方法

  • 嵌套循环法 (Nested-Loop):对两个表进行循环,每次匹配一个元组。
  • 排序合并法 (Sort-Merge):对两个表进行排序,然后按顺序合并匹配。
  • 索引连接法 (Index-Join):对表2建立索引,根据表1的连接字段在表2中查找匹配行。
2. 自然连接
  • 自然连接是等值连接的一种特殊情况,去除重复的属性列。
  • 示例:
    SELECT Student.Sno, Sname, Ssex, Sbirthdate, Smajor, Cno, Grade 
    FROM Student, SC 
    WHERE Student.Sno = SC.Sno;
    
3. 复合条件连接
  • 复合条件连接:WHERE 子句中包含多个连接条件。
  • 示例:
    SELECT Student.Sno, Sname 
    FROM Student, SC 
    WHERE Student.Sno = SC.Sno AND SC.Cno = '81002' AND SC.Grade > 90;
    
4. 自身连接
  • 自身连接:一个表与自身进行连接,需要为表设置别名以区分,由于所有属性名都是同名属性,因此必须使用别名前缀
  • 示例:查询每门课的间接先修课(即先修课的先修课)。
    SELECT A.Cno, B.Cpno 
    FROM Course A, Course B 
    WHERE A.Cpno = B.Cno;
    
5. 外连接
  • 外连接:区别于普通连接,外连接会输出不满足连接条件的元组,通常分为左外连接和右外连接。
    • 左外连接:列出左表中的所有元组,即左侧表为主表
    • 右外连接:列出右表中的所有元组,即右侧表为主表
6. 多表连接查询
  • 示例:查询每个学生的学号、姓名、选修的课程名及成绩。

    SELECT Student.Sno, Sname, Cname, Grade 
    FROM Student, SC, Course 
    WHERE Student.Sno = SC.Sno AND SC.Cno = Course.Cno;
    
  • 执行过程

    1. 先将 StudentSC 表进行连接,获取学号、姓名和课程号、成绩。
    2. 然后与 Course 表连接,获取最终的结果。

4.4 嵌套查询

  • 嵌套查询是指将一个SELECT查询嵌套在另一个查询的WHERE子句、HAVING子句或FROM子句中。
    • 外层查询称为父查询或主查询。
    • 内层查询称为子查询或嵌套查询,子查询的SELECT语句中不能使用ORDER BY子句。
    • 嵌套查询分为相关子查询不相关子查询
      • 不相关子查询:子查询不依赖外层查询,可以独立执行。
        • 由里向外逐层处理
        • 子查询的结果用于建立其父查询
      • 相关子查询:子查询依赖外层查询的某些值,需对外层查询的每一行执行一次。
        • 取外层查询第一个元组,根据它与内层查询相关的属性值处理内层查询,WHERE子句返回值为真,取元组放入结果表

嵌套查询的示例
查询选修了课程号为81003的学生的姓名:

SELECT Sname
FROM Student
WHERE Sno IN 
(SELECT Sno FROM SC WHERE Cno = '81003');

子查询可以出现在WHEREHAVINGFROM子句中,主要有以下几种形式:

  1. 带有IN谓词的子查询

    • 用于检查某个值是否属于子查询结果集中的某个值。典型示例是使用IN来查找符合条件的记录:
    SELECT Sno, Sname
    FROM Student
    WHERE Smajor IN 
    (SELECT Smajor FROM Student WHERE Sname = '刘晨');
    
  2. 带有比较运算符的子查询

    • 当子查询返回单一值时,可以使用=><等运算符。例如:
    SELECT Sno, Sname, Smajor
    FROM Student
    WHERE Smajor = (SELECT Smajor FROM Student WHERE Sname = '刘晨');
    
  3. 带有ANY或ALL谓词的子查询

    • ANY用于与子查询结果中的任意一个值进行比较,ALL用于与子查询结果中的所有值进行比较。例如:
    SELECT Sname, Sbirthdate, Smajor
    FROM Student
    WHERE Sbirthdate > ANY (SELECT Sbirthdate FROM Student WHERE Smajor = '计算机科学与技术')
    AND Smajor <> '计算机科学与技术';
    
  4. 带有EXISTS谓词的子查询

    1. EXISTS谓词
    • 带有EXISTS谓词的子查询不返回任何数据,只产生逻辑真值“true”或逻辑假值“false”:
      • 若内层查询结果非空,则外层的WHERE子句返回真值
      • 若内层查询结果为空,则外层的WHERE子句返回假值
    • 由EXISTS引出的子查询,其目标列表表达式通常都用“*”,因为带EXISTS的子查询只返回真假值,给出列名无实际意义。
    2. NOT EXISTS谓词
    • 若内层查询结果非空,则外层的WHERE子句返回假值
    • 若内层查询结果为空,则外层的WHERE子句返回真值
    SELECT Sname
    FROM Student
    WHERE EXISTS 
    (SELECT * FROM SC WHERE SC.Sno = Student.Sno AND Cno = '81001');
    

4.5 派生表

  • 派生表是从子查询生成的临时表,可以在FROM子句中使用。派生表只在查询时存在,不会存储在数据库中。例如,找出每个学生超过自己选修课程平均成绩的课程号:
SELECT Sno, Cno
FROM SC, (SELECT Sno, AVG(Grade) FROM SC GROUP BY Sno) AS Avg_SC
WHERE SC.Sno = Avg_SC.Sno AND SC.Grade > Avg_SC.Avg_Grade;

4.6 数据更新操作

  • 插入数据(INSERT):可以插入单个元组,也可以插入子查询的结果。例如:

    INSERT INTO Smajor_age (Smajor, Avg_age)
    SELECT Smajor, AVG(EXTRACT(YEAR FROM current_date) - EXTRACT(YEAR FROM Sbirthdate))
    FROM Student
    GROUP BY Smajor;
    

    RDBMS在执行插入语句时会检查所插元组是否破坏表上已定义的完整性规则

    • 实体完整性

      参照完整性

      用户定义的完整性

      • 对于有NOT NULL约束的属性列是否提供了非空值
      • 对千有UNI QUE约束的属性列是否提供了非重复值
      • 对于有值域约束的属性列所提供的属性值是否在值域范围内
  • 修改数据(UPDATE):用于修改表中满足条件的元组。例如,将学生“20180005”的姓名修改为“刘新奇”:

    UPDATE Student
    SET Sname = '刘新奇'
    WHERE Sno = '20180005';
    
  • 删除数据(DELETE):用于删除表中满足条件的元组。例如,删除学号为“20180207”的记录:

    DELETE FROM Student WHERE Sno = '20180207';
    

4.7 空值处理

  • **空值(NULL)**表示“未知”或“无意义”的值,在插入时可以显式设置为空值。查询时,可以使用IS NULLIS NOT NULL来判断。例如,找出漏填数据的学生:

    SELECT * FROM Student WHERE Sname IS NULL OR Ssex IS NULL;
    
  • 有NOT NULL约束条件的不能取空值
    加了UNIQUE限制的属性不能取空值(MYSQL允许)

  • 空值在比较和运算时有特殊处理:

    • 算术运算:空值与任何数值进行算术运算,结果都是NULL
    • 逻辑运算:空值与布尔值运算时,结果依赖于运算符。ANDOR运算时,空值会带来不确定性。
    • 聚合函数:除了COUNT(*)外,聚合函数会跳过空值。例如,SUM()AVG()等函数只处理非空值。

4.8 视图

  • 视图是基于—个或几个基本表(或视图)导出的表,不存储数据,只存储定义基表中的数据发生变化,从视图中查询出的数据也随之改变
1. 视图的建立
  • 语法

    CREATE VIEW <视图名> [(列名1, 列名2, ...)] AS <子查询> [WITH CHECK OPTION];
    
  • 详细说明

    • <视图名>:视图的名称。
    • (列名):可选项,用于指定视图中每一列的名称。如果子查询中没有指定列名,则使用基础表的列名。
    • <子查询>:用于生成视图的数据来源,通常是一个SELECT查询。
    • WITH CHECK OPTION:可选项,用于确保通过视图进行的INSERTUPDATE操作符合视图定义的条件。如果不符合条件,更新或插入会被拒绝。
  • 示例

    CREATE VIEW IS_Student AS
    SELECT Sno, Sname, Ssex, Sbirthdate, Smajor
    FROM Student
    WHERE Smajor = '信息管理与信息系统'
    WITH CHECK OPTION;
    
    • 解释:这条语句创建了一个视图IS_Student,只包含“信息管理与信息系统”专业的学生信息。WITH CHECK OPTION确保对该视图进行的任何更新或插入操作,必须满足视图的条件(即Smajor = '信息管理与信息系统')。
  • 示例:[例3.91] 将学生的学号及平均成绩定义为一个视图:

    ```sql
    CREATE VIEW S_GradeAVG(Sno, Gavg)
    AS
    SELECT Sno, AVG(Grade)
    FROM SC
    GROUP BY Sno;
    ```- `GROUP BY Sno`:按学号对数据进行分组,计算每个学生的成绩平均值。
    
    • 创建视图时避免使用SELECT *以避免视图与基础表属性不对应问题。
  • 示例:[例7] 将Student表中所有女生记录定义为一个视图:

    CREATE VIEW F_Student1(stdnum, name, sex, age, dept)
    AS
    SELECT *
    FROM Student
    WHERE Ssex = '女';
    
    • 问题:如果在创建视图时使用SELECT *,并且基础表的结构发生改变,视图的属性与基础表的属性列不再一一对应,那么这个视图可能会失效。
    • 缺点:视图的属性列和基础表的属性列逐一对应,一旦基础表结构改变,视图和基础表之间的映射关系会被破坏,导致视图无法正常工作。

这部分内容总结了视图的查询、更新以及物化视图的相关概念和实现方法,以下是每个部分的具体总结:

2. 视图的查询
  • 用户角度:查询视图的方式与查询基础表相同。
  • RDBMS(关系型数据库管理系统)实现视图查询的方式
    • 视图消解法(View Resolution)
      • 进行有效性检查。
      • 将视图查询转换为对应的基础表的查询。
      • 执行修正后的查询。
    • 物化视图(Materialized View)
      • 将视图的查询结果预先计算并存储,避免重复执行复杂操作,提升查询性能。

物化视图的特点

  • 物理存在,需要占用存储空间。

  • 响应透明,即使增加或删除物化视图,应用程序中的SQL语句依然能正确运行。

  • 自动刷新,当基础表发生变化时,物化视图也会更新。

  • 在某些情况下,视图消解法无法生成正确的查询结果,特别是在对聚合视图进行进一步筛选时。比如,试图使用WHERE子句对聚合后的数据(如平均成绩)进行筛选时会出错。这是因为WHERE无法筛选聚合结果。

示例问题

  • 错误查询:直接对视图S_GradeAVG使用WHERE Gavg >= 90
  • 正确方法:应在基础表上使用HAVING子句进行筛选:
    SELECT Sno, AVG(Grade)
    FROM SC
    GROUP BY Sno
    HAVING AVG(Grade) >= 90;
    

示例

  • [例3.94] 查询年龄小于等于20岁的学生信息(基于IS_Student视图):
    SELECT Sno, Sbirthdate
    FROM IS_Student
    WHERE (EXTRACT(YEAR FROM CURRENT_DATE) - EXTRACT(YEAR FROM Sbirthdate)) <= 20;
    
3. 视图的更新
  • 用户角度:更新视图与更新基础表相同。
  • RDBMS 实现更新视图的方法
    • 更新视图最终要转换为对基础表的更新操作。
    • 指定WITH CHECK OPTION子句后,DBMS在更新视图时会检查,防止用户通过视图对不属于视图范围的数据进行更新。

示例

  • [例3.97] 将学生视图IS_Student中学号为20180005的学生姓名改为“刘新奇”:
    UPDATE IS_Student
    SET Sname = '刘新奇'
    WHERE Sno = '20180005';
    
    • 通过视图进行的更新会被转换为对基础表Student的更新操作。
视图的插入操作
  • [例3.98] 向IS_Student视图中插入新记录,插入操作会转换为基础表Student的插入操作:
    INSERT INTO IS_Student
    VALUES ('20180207', '赵新', '男', '2001-7-19', '信息管理与信息系统');
    
视图更新的限制
  • 并非所有视图都是可更新的,通常行列子集视图是可更新的。
  • 例如,基于分组或聚合操作的视图通常不可更新。
  • 示例:[例3.91] 中定义的视图S_GradeAVG包含学号和平均成绩列,由于包含聚合操作,视图中的数据无法直接更新:
    CREATE VIEW S_GradeAVG(Sno, Gavg) AS
    SELECT Sno, AVG(Grade)
    FROM SC
    GROUP BY Sno;
    
4. 视图的删除
  • 如果该视图上还导出了其他视图,使用CASCADE级联删除语句,把该视图和由它导出的所有视图一起删除

  • 删除基表时,由该基表导出的所有视图定义都必须显式地使用 DROP VIEW语句删除

    • 语法

      DROP VIEW <视图名>;
      
    • 解释:这个语句会删除指定的视图定义,但不会删除视图依赖的基础表中的数据。

    • 示例

      DROP VIEW IS_Student;
      
      • 解释:删除IS_Student视图,视图定义会从数据库中移除,但基础表Student中的数据不会受到影响。
5. 视图的作用

视图的作用包括:

  1. 数据安全保护:视图可以通过限制查询字段和结果集,提供数据的访问控制。例如,不同用户可以定义不同的视图,只允许他们查询特定数据。
  2. 逻辑独立性:通过视图,可以隐藏底层表结构的变化,保证应用程序的查询不受影响。视图只能在—定程度上提供数据的逻辑独立性
    对视图的更新是有条件的,因此应用程序中修改数据的语句可能仍会因基本表结构的改变而相应修改
  3. 简化操作:用户可以通过视图简化复杂查询,封装复杂的业务逻辑。
  4. 多视角看待数据:视图可以为不同用户提供同一数据的不同视角。
    EW S_GradeAVG(Sno, Gavg) AS
    SELECT Sno, AVG(Grade)
    FROM SC
    GROUP BY Sno;
    
    
4. 视图的删除
  • 如果该视图上还导出了其他视图,使用CASCADE级联删除语句,把该视图和由它导出的所有视图一起删除

  • 删除基表时,由该基表导出的所有视图定义都必须显式地使用 DROP VIEW语句删除

    • 语法

      DROP VIEW <视图名>;
      
    • 解释:这个语句会删除指定的视图定义,但不会删除视图依赖的基础表中的数据。

    • 示例

      DROP VIEW IS_Student;
      
      • 解释:删除IS_Student视图,视图定义会从数据库中移除,但基础表Student中的数据不会受到影响。
5. 视图的作用

视图的作用包括:

  1. 数据安全保护:视图可以通过限制查询字段和结果集,提供数据的访问控制。例如,不同用户可以定义不同的视图,只允许他们查询特定数据。
  2. 逻辑独立性:通过视图,可以隐藏底层表结构的变化,保证应用程序的查询不受影响。视图只能在—定程度上提供数据的逻辑独立性
    对视图的更新是有条件的,因此应用程序中修改数据的语句可能仍会因基本表结构的改变而相应修改
  3. 简化操作:用户可以通过视图简化复杂查询,封装复杂的业务逻辑。
  4. 多视角看待数据:视图可以为不同用户提供同一数据的不同视角。

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:http://www.rhkb.cn/news/439617.html

如若内容造成侵权/违法违规/事实不符,请联系长河编程网进行投诉反馈email:809451989@qq.com,一经查实,立即删除!

相关文章

51单片机系列-按键检测原理

&#x1f308;个人主页&#xff1a;羽晨同学 &#x1f4ab;个人格言:“成为自己未来的主人~” 独立按键是检测低电平的。 下面我们来看一张对应的电路原理图&#xff1a; 在这张图当中&#xff0c;P1&#xff0c;P2&#xff0c;P3内部都上拉了电阻&#xff0c;但是P0没有&am…

码随想录算法训练营第62天|卡码网:97. 小明逛公园、127. 骑士的攻击

1. 卡码网 97. 小明逛公园 题目链接&#xff1a;https://kamacoder.com/problempage.php?pid1155 文章链接&#xff1a;https://www.programmercarl.com/kamacoder/0097.小明逛公园.html 思路&#xff1a; 使用Floyd 算法&#xff0c;目的是解决多源最短路问题&#xff0c;即 …

Java项目实战II基于Java+Spring Boot+MySQL的房产销售系统(源码+数据库+文档)

目录 一、前言 二、技术介绍 三、系统实现 四、文档参考 五、核心代码 六、源码获取 全栈码农以及毕业设计实战开发&#xff0c;CSDN平台Java领域新星创作者 一、前言 随着房地产市场的蓬勃发展&#xff0c;房产销售业务日益复杂&#xff0c;传统的手工管理方式已难以满…

机器学习/数据分析--用通俗语言讲解时间序列自回归(AR)模型,并用其预测天气,拟合度98%+

时间序列在回归预测的领域的重要性&#xff0c;不言而喻&#xff0c;在数学建模中使用及其频繁&#xff0c;但是你真的了解ARIMA、AR、MA么&#xff1f;ACF图你会看么&#xff1f;&#xff1f; 时间序列数据如何构造&#xff1f;&#xff1f;&#xff1f;&#xff0c;我打过不少…

EEPROM读写实验——FPGA学习笔记18

一、EEPROM简介 Electrically Erasable Progammable Read Only Memory:是指带电可擦可编程只读存储器&#xff0c;是一种常用的非易失性存储器&#xff08;掉电数据不丢失&#xff09; EEPROM发展历史 我们这次实验所用的AT24C64存储容量为64Kbit&#xff0c;内部分成256页&am…

Java--IO高级流

缓冲流 缓冲流,也叫高效流&#xff0c;是对4个基本的FileXxx 流的增强&#xff0c;所以也是4个流&#xff0c;按照数据类型分类&#xff1a; 字节缓冲流&#xff1a;BufferedInputStream&#xff0c;BufferedOutputStream 字符缓冲流&#xff1a;BufferedReader&#xff0c;Buf…

【2024】uniapp 接入声网音频RTC【H5+Android】Unibest模板下Vue3+Typescript

需求 最近开发一个项目&#xff0c;需要实现声网的接入。由于采用uniapp模式&#xff0c;按照最佳实践采用优先开发H5再适配的模式。因此实现了H5和Android两种模式的接入&#xff0c;Android里采用离线打包自定义基座来进行调试。怕自己忘记了&#xff0c;在这里详细的记录完…

Hadoop之WordCount测试

1、Hadoop简介&#xff1a; Hadoop是Apache旗下的一个用Java语言实现的开源软件框架&#xff0c;是一个开发和运行处理大规模数据的软件平台。 Hadoop的核心组件包括Hadoop分布式文件系统&#xff08;HDFS&#xff09;和MapReduce编程模型。HDFS是一个高度容错的系统&#xf…

OpenGL笔记十九之相机系统

OpenGL笔记十九之相机系统 —— 2024-10-02 晚上 bilibili赵新政老师的教程看后笔记 code review! 文章目录 OpenGL笔记十九之相机系统1.运行1.1.游戏相机1.2.轨迹球相机 2.游戏相机与轨迹球相机切换3.博主笔记本要运行需要更改的文件更改1:28_OpenGL_CameraSystem/applicat…

基于SpringBoot的学习资源共享平台

运行环境: jdk8tomcat9mysqlIntelliJ IDEAmavennodejs 设计选用前后端分离的单体架构方式 后端&#xff1a;SpringBootMybatis-PluslogbackElasticsearchRedisMySQLJwtsmtp阿里云OSS 前端&#xff1a;WebPackVueJsAnt Designaxios 主要模块&#xff1a;反馈管理、资源管理、…

GitLab flow工作流及其使用

问题背景 Git flow和Github flow及其问题 使用GitLab flow 目录 什么是GitLab工作流 功能分支 生产分支 使用GitLab flow环境分支 使用GitLab flow发布分支 使用GitLab flow合并/拉取请求 使用GitLab flow进行问题跟踪 链接和关闭合并请求中的问题 用rebase压缩提交…

实战案例:结合大模型与爬虫技术实现12306智能查票系统

大语言模型&#xff0c;例如 GPT-4&#xff0c;拥有强大的知识储备和语言理解能力&#xff0c;能够进行流畅的对话、创作精彩的故事&#xff0c;甚至编写代码。然而&#xff0c;它们也面临着一些难以克服的困境&#xff0c;就像一个空有知识却无法行动的巨人 信息滞后&#xf…

[ESP32]ESP-IDF使用组件添加U8g2图形库

U8g2 在ESP32使用u8g2的时候可以使用添加component的方式进行, 由于官方的component库没有, 这里我找到了一个可以使用的github库, 使用git的方式进行添加这一个库 具体的原理可以看[官方手册](https://docs.espressif.com/projects/esp-idf/zh_CN/stable/esp32/api-guides/to…

使用seata管理分布式事务

做应用开发时&#xff0c;要保证数据的一致性我们要对方法添加事务管理&#xff0c;最简单的处理方案是在方法上添加 Transactional 注解或者通过编程方式管理事务。但这种方案只适用于单数据源的关系型数据库&#xff0c;如果项目配置了多个数据源或者多个微服务的rpc调用&…

C语言 | Leetcode C语言题解之第459题重复的子字符串

题目&#xff1a; 题解&#xff1a; bool kmp(char* query, char* pattern) {int n strlen(query);int m strlen(pattern);int fail[m];memset(fail, -1, sizeof(fail));for (int i 1; i < m; i) {int j fail[i - 1];while (j ! -1 && pattern[j 1] ! pattern…

63.5 注意力提示_by《李沐:动手学深度学习v2》pytorch版

系列文章目录 文章目录 系列文章目录注意力提示生物学中的注意力提示查询、键和值注意力的可视化使用 show_heatmaps 显示注意力权重代码示例 代码解析结果 小结练习 注意力提示 &#x1f3f7;sec_attention-cues 感谢读者对本书的关注&#xff0c;因为读者的注意力是一种稀缺…

在Linux系统安装Nginx

注意&#xff1a;Nginx端口号是80(云服务器要放行) 我的是基于yum源安装 安装yum源(下面这4步就好了) YUM源 1、将源文件备份 cd /etc/yum.repos.d/ && mkdir backup && mv *repo backup/ 2、下载阿里源文件 curl -o /etc/yum.repos.d/CentOS-Base.repo ht…

LabVIEW机床加工监控系统

随着制造业的快速发展&#xff0c;机床加工的效率与稳定性成为企业核心竞争力的关键。传统的机床监控方式存在效率低、无法远程监控的问题。为了解决这些问题&#xff0c;开发了一种基于LabVIEW的机床加工监控系统&#xff0c;通过实时监控机床状态&#xff0c;改进生产流程&am…

安卓 /proc 目录详解:从内核到进程的桥梁

在安卓系统中&#xff0c;/proc 目录是开发者、调试者、甚至是普通用户深入了解系统状态、性能及行为的一个重要入口。这个虚拟文件系统不仅包含了丰富的内核信息&#xff0c;还反映了运行中的每个进程的状态。 /proc 文件系统 /proc 文件系统&#xff08;procfs&#xff09;是…

振动分析-30-振动信号的幅值概率密度函数CWRU西楚大学轴承数据(实战)

文章目录 1 背景2 幅值概率密度函数3 实现流程3.1 自定义函数3.2 模拟正弦信号4 CWRU轴承数据4.1 加载数据4.2 相同工况不同故障4.3 相同数据不同份数5 参考附录1 背景 很多初学者刚接触故障诊断可能觉得很简单,套用深度学习模型进行训练,分类准确率达到99%即可。 在写论文时…