第三章、关系数据库标准语言SQL
|
23
|
0
|
数据库

6293 字
|
27 分钟

第三章、关系数据库标准语言SQL

3.1、SQL概述

1、SQL（Structured Query Language）：结构化程序语句；是关系数据库的标准语言

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

3.1.1、SQL的产生和发展

1、SQL标准的进展过程

1、内容

image-20230823222810881

2、SQL语言的特点

1、综合统一

1、集合了定义语言（DDL）、数据操纵语言（DML）、和数据控制语言（DCL）为一体

2、独立完成数据库生命周期的全部活动（定义关系模式、插入数据、建立数据库、数据更新、重构和维护、安全性、完整性）

3、用户数据运行后，可以根据需求随时更改

4、数据操作符统一

2、高度非过程化

1、非关系数据模型的数据操纵语言是“面向过程”，必须制定存取路径

2、SQL只要“做是什么”，路径相关内容不需要了解

3、存取路径操作过程，由系统完成

3、面向集合的操作方式

1、非关系数据模型面向记录的操作方式，操作对象是一条记录

2、SQL采用集合的操作方式

3、操作、查询可以是元组的集合

4、一次插入、删除、更新操作的对象可以是元组的集合

4、以一种语法结构提供多种使用方式

1、是独立的语言，能够独立用于联机交互的使用方式

2、SQL是嵌入式语言，可以嵌入到高级语言，让程序设计时使用

5、语言简单， 易学易用

1、功能极强，只用9个动词

2、图示

image-20230823224815262

3.1.2、SQL的基本概念

1、三级模式结构

1、SQL支持关系数据库三级模式结构

2、图示

image-20230823230507575

2、基本表

1、本身独立存在的表

2、SQL中一个关系就对应一个基本表

3、一个（或多个）基本表对应一个存储文件

4、一个表可以带若干索引

3、存储文件

1、逻辑结构组成了关系数据库的内模式

2、物理结构是任意的，对用户透明

4、视图

1、从一个或几个基本表导出的表

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

3、视图是一个虚表

4、用户可以在视图上再定义视图

3.2、学生—课程数据库

1、学生—课程模式S-T

2、学生表：Student（Sno/Sname/Ssex/Sage/Sdept）

image-20230823231429656

3、课程表：Course(Cno/Cname/Cpno/Ccredit)

image-20230823231513199

4、学生选课表：SC(Sno/Cno/Crade)

image-20230823231617966

3.3、数据定义

1、定义功能：模式定义、表定义、视图和索引的定义

2、内容包括

image-20230823231830690

3.3.1、模式的定义与删除

1、模式定义

1、语句格式

CREATE SCHEMA<模式名> AUTHORIZATION<用户名>;

2、例子

image-20230823232200913

3、如果没有定义模式名，那么模式名默认为，用户名

4、CREATE SCHEMA中可以接受CREATE TABLE; CREATE VIEW和GRANT子句，格式如下

image-20230823232512311

5、例子

image-20230823232550900

6、执行创建模式语句必须拥有DBA权限（数据库管理员权限）、或者DBA授予在CREATE SCHEMA的权限

2、删除模式

1、格式

1、语句格式

DROP SCHEMA<模式名><CASCADE|RESTRICT>;
2、说明

1、CASCADE和RESTRICT必须二选一

2、CASCADE(级别)：删除模式的同时并把数据删除

3、RESTRICT(限制)：如果该模式内有数据库对象（视图、表等）就必须清理了才能执行删除操作，否则会报错

4、例子

image-20230823233052422

3.3.2、基本表的定义、删除与修改

1、定义基本表

1、语句格式
CREATE TABLE<表命名>
(<列名><数据类型>[<列级完整性约束条件>],
[<列名><数据类型>[<列级完整性约束条件>]],<表级完整性约束条件>])
2、说明

1、如果完整性约束条件设计到该表的多个属性列，则必须定义在表级别上，否则即可以定义在列级也可以定义在表级

2、例子

image-20230825092355121

3、例子

image-20230825092757064

4、例子

image-20230825093326489

2、数据类型

1、常用数据类型

image-20230825093743594

3、模式与表

1、内容

1、每个基本表都属于某一个模式（类似于权限），一个模式包含多个基本表

2、创建基本表（其他数据库对象也一样），若没有指定模式，系统会根据搜索路径来确定该对象所属的模式

3、当前所在搜素路径：

SHOW search_path;

4、搜索路径当前默认值

$user,PUBLIC;

5、DBA用户可以设置搜索路径

SET search_path TO "S-T",PUBLIC;

6、若搜索路径中模式名不存在， 系统则会报错

7、若搜索路径中存在模式，RDBMS会使用模式列表的第一个在的模式作为数据库对象的模式名

2、创建基本表

1、创建表时给出模式名

①例子

image-20230825094659674

2、创建模式的同时创建表

①例子

image-20230825094821031

3、设置所属模式，在创建表名中不必给出模式名

image-20230825094916798

4、总结：模式相当于仓库，表相当于仓库中存放的零件，可以把模式授权给管理员，让管理员对表进行管理、三种建表方式

4、修改基本表

1、语句格式

image-20230829200851207

2、说明

1、<表名>是要修改的基本表

2、ADD子句，用于增加新列，新的列级完整性约束条件和新的表级完整性约束条件

3、DROP COLUMN子句用于删除表中的列

4、如果指定了CASCADE语句：就不管表内数据，全部删除

5、如果指定了RESTRICT语句：如果表内有数据，就会删除失败，并报错

6、DROP CONSTRAINT子句，用于删除指定的完整性约束

7、ALTER COLUMN子句，用于修改原有的列定义，包括修改列名和数据类型

3、例子

image-20230829201800635

5、删除基本表

1、格式
DROP TABLE <表名> [RESTRICT|CASCADE];
2、说明

1、基本表被删除，数据被删除，表上建立的索引、视图、触发器等也一般被删除

3、例子

image-20230829202259523

4、DROP TABLE时，SQL2011与3个RDBMS的处理策略比较

image-20230829203058451

3.3.3、索引的建立与删除

1、概念

1、建立索引的目的：加快查询速度

2、谁能建立索引：DBA或表的属主（即建立表的人）

3、DBMS会自动建立以下索引

PRIMARY KEY
UNIQUE

4、谁维护索引：DBMS自动完成

5、使用索引：DBMS自动选择是否使用索引哪些索引

6、RDBMS中的实现：B+树、HASH索引，B+树具有动态稳定优点；HASH具有快速查找特点

7、索引是共享数据库内部实现技术，属于内模式范畴

8、CREATE INDEX语句定义索引时，可以定义索引是唯一索引、非唯一索引、聚簇索引

2、建立索引

1、语句格式

image-20230829204628065

2、说明

1、UNIQUE表明此索引每一个索引值只对应唯一的数据

2、例子

image-20230829210212050

3、CLUSTER表示要建立的索引是聚簇索引。聚簇索引是指索引顺序和表中记录的物理顺序一致的索引组织（聚簇索引的物理存储地址和表中记录顺序是一致的）

4、在经常查询的列建立聚簇索引，用来提高查询效率

5、一个表最多只能建立一个聚簇索引

6、经常更新的列不宜建立聚簇索引

3、删除索引

1、语句格式
DROP INDEX<索引名>;

1、删除索引时，系统会从数据字典中删去有关该索引的描述

2、例子

image-20230829212522172

3.3.4、数据字典

1、是关系数据库管理系统内部的一组系统表

2、记录了数据库中所有定义信息，包括模式定义、视图定义、索引定义、完整性约束定义、各类用户对数据库的操作权限、统计信息等

3、RDBMS执行Sql数据定义时，实际就是更新数据字典

3.4、数据查询

1、语句格式

image-20230829213244774

2、注意

①语句中字母不区分大小写

②语句中的【, ;】必须是英文半角

③[]中不是必须实现的语句

3.4.1、单表查询

1、功能：对一个表的内容进行查询

1、选择表中的若干列

1、查询指定列

1、格式：在SELECT 后面指定列名，FROM 后面指定列所在的表名

2、例子

2、查询全部列

1、功能：选出表中所有属性列

2、格式：SELECT 关键字后+列名 或者 SELECT * FROM XXXX(表)

3、查询经过计算的值

1、功能：选出表中指定的属性列，并经过计算后输出

2、格式：SELECT子句的<目标列表达式>可以为

①算术表达式

②字符串常量

③函数

④列别名

3、例子

image-20230829215016694

2、选择表中的若干元组（行）

1、消除取值重复的行

1、如果没有指定DISTINCT关键词，则缺省为ALL

2、例子

image-20230829215731094

2、查询满足条件的元组

1、可以通过where子句实现，查询条件如下

image-20230829220303693

1、比较大小

①例子

image-20230829222645566

2、确定范围

①例子

image-20230829222831894

3、确定集合

①例子

image-20230829222932880

4、字符匹配

①说明：匹配串为固定字符串

②格式

[NOT] LIKE '<匹配串>' [ESCAPE '<换码字符>']

③例子

image-20230829223224184

④匹配串为含有通配符的字符串：

⑤【%】：是任意长度的字符

⑥【_】：表示任意的一个字符

⑦例子

image-20230829223618283

⑧使用换码字符将通配符转义为普通字符

就是用\+需要转义的字符

image-20230829224640639

5、涉及空值的查询

①格式

IS NULL 或 NOT NULL

②例子

image-20230829224650705

6、多重条件查询

①用逻辑运算符AND 和OR 来联结多个查询对象；and优先级高于or，可以用括号改变

②例子

image-20230829225238785

3、ORDER BY 子句

1、可以按一个或多个属性排序

2、升序：ASC

3、降序：DESC

4、缺省值默认为：升序

5、当排序列含有空值（null）时：默认为最大值

6、例子

image-20230829225730761

4、聚集函数

1、内容

image-20230829230015217

2、例子

image-20230829231523996

3、注意

1、where条件语句中不能使用聚合函数

5、GROUP BY 子句

1、作用

1、按指定的一列或多列分组，值相等的为一组，来细化聚集函数的作用对象

2、说明

1、未对查询结果分组，聚集函数将作用于整个查询结果

2、对查询结果分组后，聚集函数将分别作用于每个组

3、如果用group by 分组，那么只能有两种情况

①是group by 后的内容

②是聚合函数

③例子

image-20230829232241576

4、GROUP BY 子句分组后，可以用HAVING短语来指定筛选条件

①例子

image-20230829232522088

3、HAVING短语和WHERE子句的区别

1、作用对象不同

①WHERE作用于基表或视图

②HAVING短语作用于组，从中选择满足条件的元组

2、WHERE句子中是不能使用聚合函数作为条件表达式

3、例子

image-20230829232858425

3.4.2、连接查询

1、等值与非等值连接查询

1、where子句用来连接两个表的条件称为连接谓词

2、两个格式

注意

1、符号为=时：等值连接；其他为非等值连接

2、其中列名称的字符类型必须一样，名字可以不一样

image-20230830212459314

2、执行方法：嵌套循环法（NESTED-LOOP）

1、作为了解

2、从表一中找第一个元组，然后扫描表二，找到满足连接条件的元组，就拼接起来

3、就一直循环，直到全部找完

2、自然连接

1、若在等值连接中把目标列重复的属性列去掉

2、建议写的时候尽量多使用：表.列名

3、自身连接

1、定义

1、一个表与自身连接

2、说明

1、需要给表取一个别名用来区别

2、因为所有表相同，所以必须使用表的前缀

image-20230830214104943

4、外连接

1、普通连接和外连接的区别

1、普通连接：输出满足连接条件的元组

2、外连接：指定表位连接主体，将主体表中不满足的元组一起输出

3、不满足的元组称为：悬浮元组

4、外连接分为

1、左外连接

①格式

LEFT OUT JOIN SC ON

②就是左表中的悬浮元组也加进来，所有的元组

2、右外连接

①格式

RIGHT OUT JOIN SC ON

②就是右表中的悬浮元组也加进来，所有的元组

image-20230830214803165

5、多表连接

1、定义

1、两个以上的表进行连接

2、例子

image-20230830215043306

3.4.3、嵌套查询

1、一个SELECT-FROM-WHERE语句称为一个查询块

1、定义

1、将一个查询块嵌套在另外一个查询块的WHERE子句，HAVING短语的条件中查询

2、说明

1、子查询中不能使用ORDER BY 子句

2、层次嵌套方式反映了SQL语言的结构化

3、有些嵌套查询可以用连接运算替代

4、外层查询（父查询）、内层查询（子查询）

image-20230830220246858

3、带有IN谓词的子查询

1、子查询是一个集合，用IN谓词表示父查询条件在子查询结果的集合中

1、例子：查询与“刘晨”在同一个系学习的学生

1、方法一

image-20230830220744000

2、方法二

image-20230830220750945
2、例子：查询选修了课程名为“信息系统”的学生学号和姓名

1、方法一：嵌套查询

image-20230830221138948

2、连接查询

image-20230830221149995

3、说明

1、不相关子查询：子查询的查询条件不依赖于父查询

2、相关子查询：子查询的查询条件依赖父查询，嵌套查询

4、带有比较运算符的子查询

1、可以换成比较运算符

1、例子

image-20230830221609235

5、带有ANY(SOME)或ALL的子查询

1、语义

1、ANY—任意一个值

2、ALL—所有值

2、需要配合使用的比较运算符

image-20230830222115816

1、例子

image-20230830222740903

3、说明

1、聚集函数实现子查询比直接用ANY、ALL效率更高

2、ANY、ALL谓词用聚集函数、IN谓词的等价转换关系如下白哦

image-20230830222913730

6、带有EXISTS谓词的子查询

1、EXISTS谓词

1、存在量词∃，返回的逻辑为TRUE或FALSE

2、例子

image-20230830231158042

3、说明

1、使用EXISTS后，若内层结果非空，则外层结果为TRUE，否则为FALSE

2、 因为EXISTS查询结果没有值的意义，所以子查询目标列表都用*表示

4、NOT EXISTS谓词

1、说明

①非空，则为FALSE

②空，则为TRUE

2、例子

image-20230830231305906

5、不同形式的查询间的替换

1、一些EXISTS或NOT EXISTS谓词子查询不能被其他形式子查询等价替代

2、所有带IN谓词、比较运算符、ANY和ALL谓词的子查询都能被带EXISTS谓词的子查询等价交换

6、EXISTS/NOT EXISTS实现全称量词

1、SQL语句没有全称量词（类似ALL）。可以把带有全称量词的谓词转换为等价的带有存在量词的谓词

2、举例

image-20230830232721653

3、例子

image-20230830233335137

7、用EXISTS/NOT EXISTS实现逻辑蕴含

1、SQL没有蕴含（充分条件）的逻辑运算，可以利用谓词演算将逻辑蕴含谓词等价交换为：

image-20230830233605807

2、例子

image-20230830234142769

3.4.4、集合查询

1、种类：并操作、交操作、差操作

1、并操作UNION

1、例子

image-20230830234423122

2、UNION：系统会自动去重元组

3、UNION ALL ：系统会保留重复元组

4、UNION相当于or操作

2、交操纵INTERSECT

1、INTERSECT：相当于and操作

image-20230830234921209

3、差操作EXCEPT

1、说明：参与集合操作的各项查询结果的列数必须相同；对应项的数据类型也必须相同

2、例子

image-20230901081745411

3.4.5、基于派生表的查询

1、介绍

1、子查询可以出现在WHERE子句中，还可以出现在FROM子句中，这时子查询生成临时派生表，成为主查询的查询对象

2、例子

image-20230901082552006

3、补充：通过FROM子句生成派生表的时候，AS关键字可以省略，但必须为派生表生成一个别名

3.4.6、SELECT语句的一般格式

1、格式

image-20230901083232427

3.5、数据更新

3.5.1、插入数据

1、插入元组

1、格式
INSERT INTO <表名> [(<属性列1>[,<属性列2>]……)]
VALUES(<常量1>[,<常量2>]……)
2、功能

1、将新元组插入指定表中（新行插入表中）

3、说明

1、INTO子句：属性列可以和表中顺序不一致，没有指定属性列的默认插入全部

2、VALUES子句：值必须和INTO子句匹配，值和属性列的数据类型需要一直

3、如果插入的属性没有赋值，则在自动赋NULL，主码必须有值，且不能重复

4、例子

image-20230901084243648

2、插入子查询结果

1、格式
INSERT INTO <表名> [(<属性列1>[,<属性列2>]……)]……
子查询;
2、说明

1、就是将子查询的结果插入到指定表中

2、SELECT目标列必须和INTO匹配，值的个数、类型一致

3、例子

image-20230901085709745

3.5.2、修改数据

1、语句格式

UPDATE<表名>
SET <列名>=<表达式>[,<列名>=<表达式>]……[WHERE<条件>];

2、功能

1、修改指定表中满足WHERE子句条件的元组

3、说明

1、SET子句：指定修改方式，修改的列，修改后的取值

2、WHERE子句：指定要修改的元组，缺省表示修改所有的元组

3、在执行修改语句时，会检查修改操作是否破坏表已定义的完整性规则

4、修改某一个元组的值

1、例子

image-20230901091508558

5、修改多个元组的值

1、例子

image-20230901091506086

6、带子查询的修改语句

1、例子

image-20230901091503300

3.5.3、删除数据

1、语句格式

DELETE FROM <表名> [WHERE <条件>];

2、功能

1、删除指定表中满足WHERE子句条件的元组

3、说明

1、WHERE子句：指定要删除的元组，缺省表示要删除表中的所有元组，表达定义依然在

4、例子

1、删除某一个元组的值

image-20230901092326281

2、删除多个元组的值

image-20230901092322387

3、带子查询的删除语句

1、例子

image-20230901092319622

2、删除该表元组，也需要将该表的相关的元组删除

3.6、空值的处理

1、3.6.1、内容介绍

1、空值存在取值不确定性，对关系运算会带来问题，需要特殊处理

1、SQL允许空值的三种情况

1、该属性有值，但不知道它的具体值

2、该属性不应该有值

3、某种原因不便于填写

2、空值的产生

1、数据没有填写

image-20230901093316210

3、空值的判断

1、使用IS NULL 或 IS NOT NULL来表示

4、空值的约束条件

1、属性定义（域定义）为NOT NULL约束条件，则不能添加NULL

2、加了UNIQUE限制的属性不能取空值

3、码属性不能取空值

5、空值的算术运算、比较运算、逻辑运算

1、算术运算

1、空值与另一个值（也包括空值）运算结果为NULL

2、比较运算

1、空值与另一个值（也包括空值）比较结果为：UNKNOWN

3、逻辑运算

1、如图

image-20230901094131690

2、其中T：为TRUE

3、其中F：为FALSE

4、其中U：为UNKNOWN

5、UNKNOWN，的非也是，UNKNOWN

6、例子

image-20230901094752340

3.7、视图

3.7.1、特点

1、是虚表，从一个或几个基本表导出的

2、值存放视图的定义，不存放视图对应的申诉局

3、表中的数据发生变化，实体中程序ch的数据也随之改变

3.7.2、基于视图的操作

1、查询、删除、受限更新、定义基于该视图的新视图

3.7.3、定义视图

1、建立视图

1、语句格式
CREATE VIEW<视图名> [(<列名>[,<列名>]……)] AS <子查询> [WITH CHECK OPTION];
2、说明

1、组成视图的属性列名：全部省略或指定

2、子查询不允许有ORDER BY 和 DISTINCT短语

3、RDBMS执行CREATE VIEW 语句是，只是把视图定义插入数据，并不执行其中的SELECT雨具

4、在对视图查询时，按视图的定义从基本表化总将数据查出

5、例子

image-20230901111222187

3、基于多个基表的视图

1、例子

image-20230901111618892

4、基于视图的视图

1、例子

image-20230901111836941

5、带表达式的视图

1、例子

image-20230901112115552

6、分组视图

1、例子

image-20230901112329828

7、不指定属性列

1、注意：如果基本表的列和视图的列映像关系被破坏，视图则无法正常工作

2、例子

image-20230901112532597

2、删除视图

1、语句格式
DROP VIEW<视图名> [CASCADE];#连根拔起
2、说明

1、删除的是指视图的定义

2、如果该视图和其他视图有关系，使用CASCADE级联删除语句，所有有关系的视图都会被删除

3、如果要删除基本表，则由该表导出的视图定义必须用DROP VIEW 删除语句进行删除（系统不会自动删除）

4、例子

image-20230901113226879

3.7.4、查询视图

1、视图定义后，用户就可以像基本表一样对视图进行查询了

1、RDBMS实现视图查询的方法—视图消解法

1、是让你知道数据库信息系统会自动实现视图消解法

1、三步骤

1、进行有效性检查

2、转换成等价的对基本表的查询

3、执行修正后的查询

2、例子

image-20230901113939527

2、视图消解法的局限

1、有以下情况视图小结法不能生成正确查询

image-20230901114551488

3.7.5、更新视图

1、说明

1、是指通过视图来插入、删除数据，因为视图不适合存储数据，所以更新操作将通过视图消解法，来对实际表的更新操作

2、注意

1、为了防止更新视图出错，定义视图的时候要加WITH CHECK OPTION子句

2、例子

image-20230901115152047

3、例子

image-20230901115405048

3、更新视图的限制

1、一些视图无法更新，因为视图更新不能唯一地有意义地转换成相对应基本表的更新

2、看例子

image-20230901115635571

3、该例子需要把121同学的平均成绩修改为90分，但是平均成绩是由各科成绩求出，系统不知道应该改哪一科让平均成绩=90，所以会出现问题

3.7.6、视图的作用

1、简化用户操作

2、使用户能以多种角度看待同一数据

3、视图对重构数据库提供了一定程度的逻辑独立性

4、视图能够对机密数据提供安全保护

5、适当的利用视图可以更清晰的表达查询

暂无评论

发送评论 编辑评论 


				

			

						

				

					

						

							

								
							

							
						

					

				

				

					

						

							

								
							

							
						

					

				

				

					

						

							

								
							

							
							
													

					

				

			

			
			

				

					
				

			

				

											

							
							
						

																				
					
											
						

	

		
|´・ω・)ノ
ヾ(≧∇≦*)ゝ
(☆ω☆)
（╯‵□′）╯︵┴─┴
￣﹃￣
(/ω＼)
∠( ᐛ 」∠)＿
(๑•̀ㅁ•́ฅ)
→_→
୧(๑•̀⌄•́๑)૭
٩(ˊᗜˋ*)و
(ノ°ο°)ノ
(´இ皿இ｀)
⌇●﹏●⌇
(ฅ´ω`ฅ)
(╯°A°)╯︵○○○
φ(￣∇￣o)
ヾ(´･ ･｀｡)ノ"
( ง ᵒ̌皿ᵒ̌)ง⁼³₌₃
(ó﹏ò｡)
Σ(っ °Д °;)っ
( ,,´･ω･)ﾉ"(´っω･｀｡)
╮(╯▽╰)╭
o(*////▽////*)q
＞﹏＜
( ๑´•ω•) "(ㆆᴗㆆ)
😂
😀
😅
😊
🙂
🙃
😌
😍
😘
😜
😝
😏
😒
🙄
😳
😡
😔
😫
😱
😭
💩
👻
🙌
🖕
👍
👫
👬
👭
🌚
🌝
🙈
💊
😶
🙏
🍦
🍉
😣
Source: github.com/k4yt3x/flowerhd
	

	

		
颜文字
Emoji
小恐龙
花!
	


									

			

			
			
		

	






上一篇
下一篇