数据库原理笔记3

课程笔记_数据库原理
Word count: 5.2k   |   Reading time≈ 19 min

依照学校教学安排，第三章为数据库操作SQL语言

本帖仅供个人学习使用

使用软件 pgAdmin4, Power Designer

3.1 SQL语言概述

3.1.1 SQL简介

结构化查询语言SQL是一种对关系数据库进行访问的数据操作语言，于上世纪70年代由SEQUEL语言演变而来。当下主流关系型数据库管理系统均支持SQL标准语言实现数据库操作。
SQL的语言特点：

  • 一体化：SQL语言命令集可以完成关系数据库的数据查询、数据定义、数据操纵、数据库管理、数据库控制等操作。
  • 使用方式灵活：既可以以交互命令方式操作访问数据库，也可以嵌入到程序语言中编程访问数据库。
  • 非过程化：SQL对数据库的操作只需告诉DBMS做什么而不需要告诉它怎么做。
  • 语言语法简单：语句少且简洁。

3.1.2 SQL对关系数据库的操作原理

用户（数据库应用程序）对关系数据库进行任何操作，都需要将SQL语句提交给DBMS执行，DBMS执行时将SQL语句转为对数据库文件的输入输出操作，并将读取的数据组装为操作结果集返回给用户（数据库应用程序）
SQL主要操作功能：

  • 数据库对象创建、修改、删除
  • 数据库表的数据插入、修改、删除、查询、统计
  • 存储过程、触发器、函数等程序执行
  • 数据库权限、角色、用户等管理

3.1.3 SQL语言语句类型

  • 数据定义语句DDL：是SQL语言中用于创建、修改或删除数据库对象的语句，DDL不但可以用于数据库对象的管理，也可以定义数据库对象约束
    • CREATE DATABASE - 创建新数据库
    • DROP DATABASE - 删除数据库
    • ALTER DATABASE - 修改数据库属性
    • CREATE TABLE - 创建新表
    • ALTER TABLE - 修改数据库表结构
    • DROP TABLE - 删除表
    • CREATE INDEX - 创建索引
    • DROP INDEX - 删除索引
  • 数据操纵语句DML：是SQL语言中用于增加、修改、删除的语句
    • INSERT - 向数据库表中插入语句
    • UPDATE - 更新数据库表中的数据
    • DELETE - 从数据库表中删除数据
  • 数据查询语句DQL：是SQL语言中用于对数据库进行数据查询的语句
    • SELECT - 对表的内容进行查询
    • DESC - 查看关系表结构
  • 数据控制语句DCL：是SQL语言中用于对数据库对象访问权进行控制的语句
    • GRANT - 授予用户对数据库对象的权限
    • DENY - 拒绝授予用户对数据库对象的权限
    • REVOKE - 撤销用户对数据库对象的权限
  • 事务处理语句TPL：是SQL语言中用于数据库内部事务处理的语句
    • BEGIN TRANSACTION - 开始事务
    • COMMIT - 提交事务
    • ROLLBACK - 回滚事务
  • 游标控制语言CCL：是SQL语言中用于数据库游标操作的语句，游标是是一个存储在MySQL服务器上的数据库查询，它不是一条SELECT语句，而是被该语句检索出来的结果集。在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据
    • DECLARE CURSOR - 定义游标
    • FETCH INTO - 提交游标数据
    • CLOSE CURSOR - 关闭游标

3.1.4 SQL语言的数据类型

  • SQL语言基本数据类型
    • 字符：CHAR\VARCHAR\TEXT…
    • 整数：SMALLINT\INTEGER…
    • 浮点数：NUMBER\FLOAT…
    • 日期：DATE\DATETIME…
    • 货币：MONEY…


3.2 数据定义SQL语句

3.2.1 数据库创建SQL语句

  • 语句基本格式
    1
    CREATE DATABASE <数据库名>;
  • 语句执行：在PostgreSQL服务器中，执行SQL语句创建数据库

3.2.2 数据库、关系表修改SQL语句

  • 语句基本格式
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    //修改方式语句类型
    //ADD修改方式，用于添加新列或列完整性约束
    ALTER TABLE <表名> ADD <新列名称> <数据类型> 完整性约束;

    //DROP修改方式，用于删除指定列或列的完整性约束条件
    ALTER TABLE <表名> DROP COLUMN <列名>;
    ALTER TABLE <表名> DROP CONSTRAINT;<完整性约束名>;

    //RENAME修改方式，用于修改表名称、列名称
    ALTER TABLE <表名> RENAME TO <新表名>;
    ALTER TABLE <表名> RENAME <原列名> TO <新列名>;

    //ALTER修改方式，用于修改数据库名、表名、列的数据类型
    ALTER DATABASE <数据库名> <修改内容>;
    ALTER TABLE <表名> 修改方式;
    ALTER TABLE <表名> ALTER COLUMN <列名> TYPE <新的数据类型>;
  • 语句执行：在PostgreSQL服务器中，执行SQL语句修改数据库或数据库内容

3.2.3 数据库、关系表删除SQL语句

  • 语句基本格式
    1
    2
    DROP DATABASE <数据库名>;
    DROP TABLE <表名>;
  • 语句执行：在PostgreSQL服务器中，执行SQL语句删除数据库或数据库内容

3.2.4 数据库表创建SQL语句

  • 语句基本格式
    1
    2
    3
    4
    5
    6
    CREATE TABLE <表名>
     ( <列名1> <数据类型> 列完整性约束,
       <列名2> <数据类型> 列完整性约束,
       <列名3> <数据类型> 列完整性约束,
       ...
     );
  • 语句执行：在PostgreSQL服务器中，执行SQL语句创建数据库关系表

3.2.5 列完整性约束

  • 约束类型
    1
    2
    3
    4
    5
    6
    PRIMARY KEY //主键
    NOT NULL //非空值
    NULL //空值
    UNIQUE //值唯一
    CHECK //有效性检查
    DEFAULT //缺省值
  • 语句执行：在PostgreSQL服务器中，执行SQL语句添加列完整性约束

3.2.6 表约束定义主键

  • 语句基本格式
    1
    2
    3
    4
    5
    6
    7
    8
    //PRIMARY KEY定义表的主键列只能定义单列主键，若要定义由多个列构成的复合主键，则需要使用表约束方式来定义
    CREATE TABLE <表名>
     ( <列名1> <数据类型> 列完整性约束,
       <列名2> <数据类型> 列完整性约束,
       <列名3> <数据类型> 列完整性约束,
    ...
       CONSTRAINT <约束名> PRIMARY Key (主键列名1,主键列名2)
     );
  • 语句执行：在PostgreSQL服务器中，执行SQL语句定义主键、复合主键
  • 使用表约束定义主键的优点：
    • 便于定义复合主键
    • 可命名主键约束
    • 便于定义代理键

3.2.7 表约束定义代理键

  • 语句基本格式
    1
    2
    3
    4
    5
    6
    7
    8
    //在一些关系表中，为了方便数据处理，可以使用代理键去替代复合主键。在SQL语句中，关系表的代理键采用表约束方式来定义
    CREATE TABLE <表名>
     ( <代理键列名> <Serial数据类型> NOT NULL,
       <列名2> <数据类型> 列完整性约束,
       <列名3> <数据类型> 列完整性约束,
    ...
       CONSTRAINT <约束名> PRIMARY Key (代理键列名)
     );
  • 语句执行：在PostgreSQL服务器中，执行SQL语句定义代理键

3.2.8 表约束定义外键

  • 语句基本格式
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    //在数据库中，一些关系表之间存在关联，在一个表中作为主键的列，在另外的关联表中则作为外键
    CREATE TABLE <表名>
     ( <列名1> <数据类型> 列完整性约束,
       <列名2> <数据类型> 列完整性约束,
       <列名3> <数据类型> 列完整性约束,
    ...
       CONSTRAINT <约束名> FOREIGN Key (外键列名)
           REFERENCES  外键列所在表名(外键列名)
         ON DELETE CASCADE//联级删除，如果主表中的一个列被删除了，那么应用该列的从表中的所有记录也被删除
     );
  • 语句执行：在PostgreSQL服务器中，执行SQL语句定义外键

3.2.9 数据库索引操作语句、

什么是索引

  • 索引是一种按照关系表中指定列的取值顺序组织元组数据存储的数据结构，使用它可以加快表中数据的查询访问
  • 在索引数据结构中，采用树结构的各个结点存储索引值及其指针，索引叶结点的指针指向数据库文件中的元组数据块地址
  • 索引作用及其特点
    • 索引作用：支持对数据库表中数据快速查找
    • 索引优点
      • 提高数据检索速度
      • 可快速连接关联表
      • 减少分组和排序时间
    • 索引开销
      • 创建和维护索引都需要较大开销
      • 索引会占用额外存储空间
      • 数据操纵困难因维护索引带来系统性能开销

数据库索引创建、修改、删除语句

1
2
3
4
5
6
7
8
// 数据库索引创建语句
CREATE INDEX <索引名> ON <表名> <(列名)>;

// 数据库索引修改语句
ALTER INDEX <索引名> 修改项;//具体修改语法见3.2.2

// 数据库索引删除语句
DROP INDEX <索引名>;

3.3 数据操纵SQL语句

3.3.1 数据插入SQL语句

  • 语句基本格式
    1
    2
    //插入一个元组语法如下，插入几个元组就写几行
    INSERT INTO 表名或视图名    如果元组只有个别列有数据，这里可以再写一个列名表表示要给哪几个列赋值，不写的话缺省值默认为NULL并自动补全 VALUES (该元组的所有数据从左到右排列，之间用逗号隔开);

3.3.2 数据修改SQL语句

  • 语句基本格式
    1
    2
    3
    4
    UPDATE 表名或视图名 //UPDATE关键字指定修改的是哪个表或视图
    SET 列名1=新数据 //SET关键字指定对哪些列设定新值，注意字符串要给单引号
    WHERE 条件表达式;//WHERE关键字给出修改的前提条件，比如需要指定行用到的 xxx=xxx
    //看不懂的见下图

3.3.3 数据删除SQL语句

  • 语句基本格式
    1
    2
    3
    4
    //一次删除一个元组，删几个元组写几遍
    DELETE
    FROM 表名或视图名
    WHERE 条件表达式

3.4 数据查询SQL语句

3.4.1 单表数据查询

数据查询SQL语句格式

1
2
3
4
5
6
7
SELECT ALL或DISTINCT 目标列 //选ALL就是有啥输出啥，有DISTINCT就是去掉重复的数据行，如果有多个目标列可以用逗号隔开，接着往后续
INTO 新表
FROM 表名或视图名 //多个表名或视图名可以用逗号隔开，往后续
//如果是条件查询 就写 "WHERE 条件表达式"
//如果是分组统计查询就写 "GROUP BY 列名",如果有条件接着写 "HAVING 条件表达式"
//如果查询结果需要排序输出就写 "ORDER BY 列名"这个子句要放在WHERE后面（如果有的话）,如果接着写"ASC或DESC", ASC是升序排列，DESC是降序排列
;

从单个表读取指定列

在关系数据库中，最简单的数据查询操作就是从单个关系表中读取指定列的数据，即关系的投影操作 （说白了就是把列拎出来组成结果集）

  • 语句基本格式
    1
    2
    SELECT 目标列//如果有多个目标列可以用逗号隔开，接着往后续，如果要查询所有列数据，就写个 *
    FROM 关系表名;

从单个表读取指定行

SQL查询语句也可以从一个关系表中读取满足条件的指定行数据，即完成关系数据的元组选择操作

  • 语句基本格式
    1
    2
    3
    SELECT *
    FROM 关系表
    WHERE 条件表达式;

从单个表读取指定行和列

在SQL查询语句中，还可以从一个关系表中读取指定行与指定列范围内的数据。既完成关系的行选择，又完成关系的列投影操作。

  • 语句基本格式
    1
    2
    3
    SELECT 目标列//如果有多个目标列可以用逗号隔开，接着往后续，如果要查询所有列数据，就写个 *
    FROM 关系表
    WHERE 条件表达式;

WHERE条件子句

使用WHERE子句中可以使用如下方式，指定范围数据

  • 使用BETWEEN…AND关键词来限定列值范围，还可以使用关键词LIKE与通配符来限定查询条件
  • 使用通配符来限定字符串数据范围。_通配符用于代表一个未指定的字符，%通配符用于代表一个或多个未指定的字符
    在SQL查询WHERE子句中，还可以使用多个条件表达式，并通过逻辑运算符AND\OR\NOT连接操作，以及使用IN\NOT IN关键词，进一步限定结果集的数据范围

对结果集进行排序

在SELECT查询语句返回的结果集中，行的顺序是任意的。如果需要结果集排序，可以在SELECT语句中加入ORDER BY 关键字
在ORDER子句中可以使用ASC和DESC指定排序是升序还是降序，也可以ASC和DESC搭配使用，若要先按某列数据升序\降序排列元组，再对其中数据相同的元组以别的列的数据升序\降序排列元组，则在ORDER BY后的这几个列之间用逗号隔开。

3.4.2 内置函数与分组统计

SQL内置函数类型

SQL语言提供了大量内置函数，支持对SELECT查询结果数据进行处理
典型SQL内置函数类型如下：

  • 聚合函数
  • 算术函数
  • 字符串函数
  • 日期时间函数
  • 数据类型转换函数

SQL聚合函数

聚合函数是一些对关系表中数值属性列进行计算并以表的形式返回查询结果的函数
AVG() 计算结果集指定列数据的平均值
COUNT() 计算结果集行数
MIN() 找出结果集指定列数据的最小值
MAX() 找出结果集指定列数据的最大值
SUM() 计算结果集指定列数据的总和

SQL内置函数与分组统计

在SQL语言中，可使用内置函数对查询结果集进行分组数据统计。这是通过在SELECT语句中加入Group By子语句来实现。

1
2
3
4
5
6
7
//分组统计SQL语句基本格式：
SELECT 统计函数/目标列
FROM 表名
//如果需要添加条件就写"WHERE 条件表达式"
GROUP BY 目标列
//HAVING字句 是对GROUP BY的结果进行限制，如要添加，则写"HAVING 条件表达式"
;

3.4.3 多表关联查询

子查询与多表关联

在实际应用中，通常需要关联多表才能获得所需的信息。在SELECT查询语句中，可使用子查询方式实现多表关联查询。

1
2
3
4
5
6
//子查询SQL语句基本格式：
SELECT 目标列 //如果有多个目标列可以用逗号隔开，接着往后续
FROM 表名
WHERE 条件中嵌套另一关系表的SELECT查询结果集
;
//例子见下表

使用连接关联多表查询

在使用多个表查询时，子查询只有在结果数据均来自一个表的情况下才有用。但如果需要从两个或多个表中获取结果数据，就不能使用子查询，而需要采用连接关联多表查询

1
2
3
4
5
6
//关联多表查询SQL语句基本格式：
SELECT 目标列 //如果有多个目标列可以用逗号隔开，接着往后续
FROM 表名  //如果数据来自多个表可以用逗号隔开，接着往后续
WHERE 关系表之间的连接关联条件
;
//例子见下表

SQL JOIN…ON连接查询语句

在SQL语言中，实现多表连接关联查询还可以使用JOIN…ON关键词的语句格式。

1
2
3
4
5
//两表连接关联查询的JOIN...ON语句格式：
SELECT 目标列 //如果有多个目标列可以用逗号隔开，接着往后续
FROM 表名1 JOIN 表名2 ON 连接条件
//有具体排序需要可以添加ORDER BY子句;
//例子见下表

外部连接

在一些特殊情况下，如关联表中的一些行的主键与外键不匹配（比如某个属性作为主键时的行数和在其它表中做外键时的行数不同），查询结果集就会丢失部分数据，此时就不能找出在某表中存在但在其它表没有的元组。在SQL应用中，有时候也希望输出那些不满足连接条件的元组数据。此时，可使用JOIN…ON外连接方式实现。其实现方式有三种形式，具体如下：

1
2
3
4
//搭配2.2.5食用风味更佳
LEFT JOIN //即使没有与右表关联列值匹配，也从左表返回所有的行
RIGHT JOIN //即使没有与左表关联列值匹配，也从右表返回所有的行
FULL JOIN //全外连接，同时进行左连接和右连接，返回所有行

3.5 数据控制SQL语句

3.5.1 什么是数据控制SQL语句

在SQL语言中，数据控制SQL语句是一种可对用户数据访问权进行控制的操作语句，它可以控制特定用户或角色对数据表、视图、存储过程、触发器等数据库对象的访问权限

  • 数据控制SQL语句分类
    • GRANT授权语句：是一种由数据库对象创建者或管理员执行的权限授予语句，它可以把访问数据库对象权限赋予给其他用户或角色
    • REVOKE收权语句：是一种由数据库对象创建或管理员赋予其它用户或角色的权限进行收回的语句，它可以收回原赋予给其他用户或角色的权限
    • DENY拒绝权限语句：是一种用于拒绝给当前数据库内的用户或者角色授予权限并防止用户或角色通过其组或角色成员继承权限的语句

GRANT授权语句

1
2
3
GRANT 权限列表  ON 数据库对象 TO 用户或角色
//若后面接"WITH GRANT OPTION"子句，则被授权角色或用户还可将这些权限赋给其他用户或角色
;

REVOKE收权语句

1
2
REVOKE 权限列表 ON 数据库对象 FROM 用户或角色
;

DENY拒绝权限语句

1
2
DENY 权限列表 ON 数据库对象 TO 用户或角色
;

3.6 视图SQL语句

3.6.1 什么是视图

视图是一种通过基础表或其它视图构建的虚拟表，它本身没有自己的数据，而是使用了存储在基础表中的数据

3.6.2 视图创建SQL语句

  • 语句基本格式
    1
    2
    3
    4
    CREATE VIEW 视图名 AS SELECT查询//SELECT语句用于指定目标列
    FROM 表名
    //如果有条件限制 就加上 "WHERE 条件表达式"
    ;

3.6.3 视图调用SQL语句

当视图在数据库中创建后，用户可以像访问关系表一样去操作访问视图

  • 语句基本格式
    1
    2
    3
    4
    SELECT 列名表/*
    FROM 视图名
    //如果要特殊排列，则添加"ORDER BY 列名"子句
    ;

3.6.4 视图删除SQL语句

当数据库不再需要某视图时，可以在数据库中删除该视图

  • 语句基本格式
    1
    2
    DROP VIEW 视图名//
    ;

3.6.5 视图应用

使用视图简化复杂SQL查询操作

数据库开发人员可以将复杂的SQL查询语句封装在视图内，外部程序只需使用简单的试图访问方式，便可获取所需要的数据


Emm…感觉视图写法更复杂了…
当视图被创建完成后，外部程序就可以通过SELECT语句查询视图数据 (具体写法见3.6.3)

使用视图提高数据访问安全性

通过视图可以将数据表内敏感数据隐藏起来，外部用户无法得知数据表的完整数据，降低数据库被攻击的风险，还可以保护用户隐私数据

提供一定程度的数据逻辑独立性

通过视图，可提供一定程度的数据逻辑独立性。当数据表结构发生改变，只要视图结构不变，应用程序可以不作修改

集中展示用户所感兴趣的特定数据

通过视图，可以将用户不关心的部分数据进行过滤

3.7 PostgreSQL数据库实践（略）

Donate
  • Copyright： Copyright is owned by the author. For commercial reprints, please contact the author for authorization. For non-commercial reprints, please indicate the source.
  • Copyrights © 2022 Daniel Qi
  • Visitors: | Views:

请我喝杯咖啡吧~

支付宝
微信