关系型数据库标准语言SQL

SQL的特点

  1. 综合统一
    SQL集定义语言、数据操作语言、数据控制语言的功能于一体,语言风格统一,可以独立完成数据库生命周期中的全部活动。
    • 定义和修改、删除关系模式,定义和删除视图,插入数据,创建数据库。
    • 对数据库中的数据进行查询和更新。
    • 数据库重构和维护。
    • 数据库安全性、完整性控制,以及事物控制。
    • 嵌入式SQL和动态SQL。
  2. 高度非过程化
  3. 面向集合的操作方式
  4. 以同一种语言结构提供多种使用方式
  5. 语言简洁、易学易用

数据定义

属性操作方式
创建删除修改
模式CREATE SCHEMADROP SCHEMA
CREATE TABLEDROP TABLEALTER TABLE
视图CREATE VIEWDROP VIEW
索引CREATE INDEXDROP INDEXALTER INDEX

模式的定义与删除

  1. 定义模式
    CREATE SCHEMA <模式名> AUTHORIZATION <用户名>[<表定义子句>|<视图定义子句>|<授权定义子句>];
    如果没有指定<模式名>,那么<模式名>隐含为<用户名>。
    定义模式实际上定义了一个命名空间,在这个空间中可以进一步吧定义该模式包含的数据库对象(基本表、视图、索引等)。
  2. 删除模式
    DROP SCHEMA <模式名> <CASCADE | RESTRICT>;
    CASCADE 和 RESTRICT两者必选其一
    选择了 CASCADE(级联),表示在删除模式的同时把该模式中的所有数据库对象全部删除;
    选择了 RESTRICT (限制),表示如果该模式中已经定义了下属的数据库对象(如表、视图等),则拒绝该删除语句的执行。只有当该模式中没有任何下属对象时才能执行 DROP SCHEMA 语句。

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

  1. 定义基本表
    CREATE TABLE <表名> (<列名> <数据类型> [列级完整性约束条件]
    					[,<列名> <数据类型> [列级完整性约束条件]]
    					……
    					[,<表级完整性约束条件>]);
    
  2. 数据类型
    常见数据类型:
    数据类型含义
    CHAR(n),CHARACTER(n)长度为 n 的定长字符串
    VARCHAR(n), CHARACTERVARYING(n)最长长度为 n 的变长字符串
    CLOB字符串大对象
    BLOB二进制大对象
    INT, INTEGER长整数(4字节)
    SMALLINT短整数(2字节)
    BIGINT大整数(8字节)
    NUMERIC(p, d)定点数,由 p 位数字(不包括符号、小数点)组成,小数点后面有 d 位数
    DECIMAL(p, d), DEC(p, d)同NUMERIC
    REAL取决于机器精度的单精度浮点数
    DOUBLE PRECISION取决于机器精度的双精度浮点数
    FLOAT(n)可选精度的浮点数,精度至少为 n 位数字
    BOOLEAN逻辑布尔量
    DATE日期,包含年、月、日,格式为 YYYY-MM-DD
    TIME时间,包含一日的时、分、秒,格式为HH : MM : SS
    TIMESTAMP时间戳类型
    INTERVAL时间间隔类型
  3. 模式与表
    每一个基本表都属于某一个模式,一个模式可包含多个基本表。
  4. 修改基本表
    ALTER TABLE <表名>
    [ADD [COLUMN] <新列名> <数据类型> [完整性约束]]
    [ADD <表级完整性约束>]
    [DROP [COLUMN] <列名> [CASCADE | RESTRICT]]
    [DROP CONSTRAINT<完整性约束名> [RESTRICT | CASCADE]]
    [ALTER COLUMN <列名> <数据类型>];
    
    其中<表名>是要修改的基本表。
    ADD 子句用于增加新列、新的列级完整性约束条件和新的表级完整性约束条件。
    DROP COLUMN 子句用于删除表中的列,如果指定了 CASCADE 短语,则自动删除引用了该列的其他对象,比如视图;如果指定了 RESTRICT 短语,则如果该列被其他对象引用,RDBMS 将拒绝删除该列。
    DROP CONSTRAINT 子句用于删除指定的完整性约束条件。
    ALTER COLUMN 子句用于修改原有的列定义,包括修改列名和数据类型。
  5. 删除基本表
    DROP TABLE <表名> [RESTRICT | CASCADE];
    若选择 RESTRICT,则该表的删除是有限制条件的。欲删除的基本表不能被其他表的约束所引用(如CHECK,FOREIGN KEY等约束)不能有视图,不能有触发器,不能有存储过程或函数等。如果存在这些依赖该表的对象,则此表不能被删除。
    若选择 CASCADE,则该表的删除没有限制条件。在删除基本表的同时,相关的依赖对象,例如视图,都将被一起删除。
    默认情况是 RESTRICT。
    注意:不同的数据库产品在遵循 SQL 标准的基础上具体实现细节和处理策略会与标准有差别。

索引的建立与删除

当表的数据量比较大是,查询操作会比较耗时。建立索引是加快查询速度的有效手段。
数据库索引有多种类型,常见索引包括顺序文件上的索引、B+树索引、hash索引、位图索引等。
索引虽然能够加速数据库查询,但需要占用一定的存储空间,当基本表更新事,索引要进行相应的维护,这些都会增加数据库的负担,因此要更具实际应用的需要有选择的创建索引。

  1. 创建索引
    CREATE [UNIQUE] [CLUSTER] INDEX <索引名>
    ON [表名](<列名>[<次序>][,<列名>[<次序>]]);
    
    其中,<表名>是要建立的基本表的名字。索引可以建立在该表的一列或多列上,各列名之间用逗号分隔。每个<列名>后面还可以用<次序>指定索引的排列次序,可选 ASC(升序)或 DESC(降序),默认值为ASC。
    UNIQUE 表明此索引的每一个索引值只对应唯一的数据记录。
    CLUSTER 表示要建立的索引是聚簇索引。
  2. 修改索引
    ALTER INDEX <旧索引名> RENAME TO <新索引名>;
  3. 删除索引
    DROP INDEX <索引名>;

数据查询

数据查询是数据库的核心操作。SQL提供了 SELECT 语句进行数据查询。其一般格式为:
SELECT [ALL | DISTINCT] <目标列表达式> [,<目标列表达式>]FROM <表名或视图名> [,<表名或视图名>]|(<SELECT语句>)[AS]<别名>
[WHERE <条件表达式>]
[GROUP BY <列名1>[HAVING <条件表达式>]]
[ORDER BY <列名2> [ASC | DESC]];

整个 SELECT 语句的含义是,根据 WHERE 子句的表达式从 FROM 子句指定的基本表、视图或派生表中找出满足条件的元组,再按 SELECT 子句中的目标列表达式选出元组中的属性值形成结果表。
如果有 GROUP BY 子句,则将结果按<列名 1>的值进行分组,该属性列值相等的元组为一个组。通常会在每组中作用聚集函数。如果GROUP BY 子句带 HAVING 短语,则只有满足指定条件的组才予以输出。
如果有 ORDER BY 子句,则结果表还要按<列名 2>的值的升序或降序排序。

单表查询

  1. 选择表中的若干列
    • 查询指定列
      SELECT col1, col2 FROM table;
    • 查询全部列
      SELECT * FROM table;
    • 查询经过计算的值
      SELECT 100-col1, 2*col2 FROM table;
  2. 选择表中的若干元组
    • 消除取值重复的行
      SELECT DISTINCT col1 FROM table;

    • 查询满足条件的元组
      查询满足指定条件的元组可以通过 WHERE 子句实现。

      查询条件谓词
      比较=, >, <, >=, <=, !=, <>, !>, !<; NOT + 上述比较运算符
      确定范围BETWEEN AND, NOT BETWEEN AND
      确定集合IN, NIT IN
      字符匹配LIKE, NOT LIKE
      空值IS NULL, IS NOT NULL
      多重条件(逻辑运算)AND, OR, NOT

      重点注意字符匹配
      [NOT] LIKE ‘<匹配串>’ [ESCAPE ‘<转码字符>’]

      • % 表示匹配任意长度的字符串
      • _ 代表任意单个字符
  3. ORDER BY 子句
  4. 聚集函数
    常用聚集函数功能
    COUNT(*)统计元组个数
    COUNT([DISTINCT | ALL] <列名>)统计一列中值的个数
    SUM([DISTINCT | ALL] <列名>)计算一列值的总和(此列必须是数值型)
    AVG([DISTINCT | ALL] <列名>)计算一列值的平均值(此列必须是数值型)
    MAX([DISTINCT | ALL] <列名>)求一列值中的最大值
    MIN([DISTINCT | ALL] <列名>)求一列值中的最小值
    如果指定 DISTINCT 短语,则表示在计算时要取消指定列中的重复值。默认为 ALL。

    当聚集函数遇到空值时,除COUNT(*)外,都跳过空值而只处理非空值。

  5. GROUP BY 子句
    GROUP BY 子句将查询结果按某一列或多列的值分组,值相等的一组。
    对查询结果分组的目的是为了细化聚集函数的作用对象。如果未对查询结果分组,聚集函数将作用于整个查询结果。分组后聚集函数将作用于每一个组,即每一组都有一个函数值。

连接查询

  1. 等值与非等值连接查询
    连接查询的 WHERE 子句中用来连接两个表的条件称为连接条件连接谓词,其一般格式为
    [<表名 1>.] <列名 1> <比较运算符> [<表名 2>.] <列名 2>
    当连接运算符为=时,称为等值连接。使用其他运算符称为非等值连接
    若在等值连接中把目标列中重复的属性列去掉则为自然连接
    *连接谓词中的列名称为连接字段。*连接条件中的各连接字段类型必须是可比的,但名字不必相同。
  2. 自身连接
    连接操作不仅可以在两个表之间进行,也可以是一个表与其自己进行连接,称为表的自身连接
  3. 外连接
    左外连接:列出左边关系中所有的元组。
    table1 LEFT OUTER JOIN table2 ON(连接条件)
    右外连接:列出右边关系中所有的元组。
    table1 RIGHT OUTER JOIN table2 ON(连接条件)
  4. 多表连接

嵌套查询

在 SQL 语言中,一个 SELECT-FROM-WHERE 语句称为一个查询块。将一个查询块嵌套在另一个查询块的 WHERE 子句或 HAVING 短语的条件中的查询称为嵌套查询

  1. 带有 IN 谓词的子查询
    例如:
    SELECT Sname
    FROM Student
    WHERE Sno IN (
    			SELECT Sno
    			FROM SC
    			WHERE Cno=2);
    
    有些嵌套查询可以用连接运算替代,有些是不能替代的。
  2. 带有比较运算符的子查询
    当用户能确切知道内层查询返回的是单个值时,可以用>, <, =, >=, <= !=, <>等比较运算符。
  3. 带有 ANY(SOME)或 ALL 谓词的子查询
    谓词语义
    >ANY大于子查询结果中的某个值
    >ALL大于子查询结果中的所有值
    <ANY小于子查询结果中的某个值
    <ALL小于子查询结果中的所有值
    >=ANY大于等于子查询结果中的某个值
    >=ALL大于等于子查询结果中的所有值
    <=ANY小于等于子查询结果中的某个值
    <=ALL小于等于子查询结果中的所有值
    =ANY等于子查询结果中的某个值
    =ALL等于子查询结果中的所有值(通常没有实际意义)
    !=(或<>) ANY不等于子查询结果中的某个值
    !=(或<>) ALL不等于子查询结果中的任何一个值
  4. 带有 EXISTS 谓词的子查询
    带有 EXISTS 谓词的子查询不返回任何数据,只产生逻辑真值“true” 或逻辑假值“false”。
    若内层查询结果为空,则外层的 WHERE 子句返回真值,否则返回假值。

集合查询

SELECT 语句的查询结果是元组的集合,所以多个 SELECT 语句的结果可进行集合操作。主要包括并操作 UNION、交操作INTERSECT 和差操作EXCEPT。
参加集合操作的各查询结果的列数必须相同;对应项的数据类型也必须相同

基于派生表的查询

子查询不仅可以出现在WHERE 子句中,还可以出现在 FROM 子句中,这时子查询生成的临时派生表成为主查询的查询对象。

SELECT Sno, Cno
FROM SC, (SELECT Sno, Avg(Grade) FROM SC GROUP BY Sno)
		AS Avg_sc(avg_sno, avg_grade)
WHERE SC.Sno = Avg_sc.avg_sno AND SC.Grade >= Avg_sc.avg_grade;

通过 FROM 子句生成派生表时,AS关键字可以省略,但必须为派生关系指定一个别名。

SELECT 语句的一般格式

数据查询是数据库的核心操作。SQL提供了 SELECT 语句进行数据查询。其一般格式为:
SELECT [ALL | DISTINCT] <目标列表达式> [,<目标列表达式>]FROM <表名或视图名> [,<表名或视图名>]|(<SELECT语句>)[AS]<别名>
[WHERE <条件表达式>]
[GROUP BY <列名1>[HAVING <条件表达式>]]
[ORDER BY <列名2> [ASC | DESC]];
  1. 目标列表达式的可选格式
    • <表名>.*
    • COUNT([DISTINCT | ALL] *)
    • [<表名>.] <属性列名表达式> [, [<表名>.] <属性列名表达式>]…
      其中,<属性列名表达式>可以是由属性列、作用于属性列的聚集函数和常量的任意算术运算(+,-,*,/)组成的运算公式。
  2. 聚集函数的一般格式
    聚集函数的一般格式
  3. WHERE 子句的条件表达式的可选格式
    WHERE子句的条件表达式的可选格式

数据更新

插入数据

  1. 插入元组
    INSERT INTO <表名> [(<属性列1> [,<属性列2>]...)]
    VALUES (<常量1>, [,<常量2>]...);
    
    其功能是将新元组插入指定表中。其中新元组的属性列1 的值为常量1 ,属性列2 的值为常量2 ,…。INTO 子句中没有出现的属性列,新元组在这些列上将取空值。但必须注意的是,在表定义时说明了NOT NULL 的属性列不能取空值,否则会出错。
    如果 INTO 子句中没有指明任何属性列名,则新插入的元组必须在每个属性列上均有值。
  2. 插入子查询结果
    INSERT INTO <表名> [(<属性列1>[,<属性列2>...])]
    子查询;
    

修改数据

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

删除数据

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

DELETE 语句删除的是表中的数据,而不是关于表的定义。

视图

定义视图

  1. 创建视图
    CREATE VIEW <视图名> [(<列名> [,<列名>]...)]
    AS <子查询>
    [WITH CHECK OPTION];
    
    组成视图的属性列名或者全部省略或者全部指定,没有第三种选择。
    以下三种情况下必须明确指定组成视图的所有列名:
    • 某个目标列不是单纯的属性名,而是聚集函数或列表达式。
    • 多表连接时选出了几个同名列作为视图的字段。
    • 需要在视图中为某个列启用新的更合适的名字。
      DBMS 执行 CREATE VIEW 语句的结构只是把视图的定义存入数据字典,并不执行其中的 SELECT 语句。
  2. 删除视图
    DROP VIEW <视图名> [CASCADE];
    视图删除后视图的定义将从数据字典中删除。如果该视图上还导出了其他视图,则使用 CASCADE 级联删除语句把该视图和由它导出的所有视图一起删除。

查询视图

视图定义后,用户就可以像对基本表一样对视图表进行查询。

关系数据库管理系统执行对视图的查询时,首先进行有效性检查,检查查询中涉及的表、视图等是否存在。如果存在,则从数据字典中取出视图的定义,把定义中的子查询和用户的查询结合起来,转换成等价的对基本表的查询,然后再执行修正了的查询。这一转换过程称为 视图消除
目前多数关系数据库系统对行列子集视图的查询均能进行正确转换。但对非行列子集视图的查询就不一定能做转换了。

更新视图

更新视图是通过视图来插入(INSERT)、删除(DELETE)、和修改(UPDATE)数据

由于视图是不存在存储数据的虚表,因此对视图的更新最终要转换为对基本表的更新。
像查询视图那样,对视图的更新操作也是通过视图小姐,转换为对基本表的更新操作。
一般的,行列子集视图是可更新的。

视图的作用

  1. 简化用户的操作
  2. 使用户能以多种角度看待同一数据
  3. 视图对重构数据库提供了一定程度的逻辑独立性
  4. 视图能够对机密数据提供安全保护
  5. 适当利用视图可以更清晰地表达查询
  • 1
    点赞
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值