3.1 SQL概述

(Strutured Query Language)

结构化查询语言，关系数据库的标准语言

特点：通用，功能强

  • SQL 的产生与发展 --> 多版本标准

没有任何一个数据库能够支持所有标准

  • 特点：

    综合统一

    • 能够完成数据定义，数据操纵，数据控制
    • 定义，修改，删除关系模式，定义和删除视图，插入数据，建立数据库
    • 对数据进行查询和更新
    • 对数据库进行重构和维护
    • 安全性，完整性控制，事务控制
    • 嵌入式SQL和动态SQL定义
    • 数据库投入运行后可以随时修改模式
    • 数据操作符统一

    高度非过程化

    • 无需了解具体存储路径

    面向集合的操作方式

    • 面向记录的操作模式
    • 操作对象，查找结果可以是元组的集合
    • 插入，删除，更新操作的对象是元组的集合

    一种语法结构，多种操作方式

    • 可以独立操作
    • 也嵌入式，可以嵌入高级语言如c++，java

    简洁

    功能 关键词
    查询 SELECT
    定义 CREATE，DROP，ALTER
    操纵 INSERT，UPDATE，DELETE
    控制 GRANT，REVOKE

  • 数据库三级模式(外模式，模式，内模式)

  • 基本表(独立存在的表，关系，索引，储存文件)

  • 存储文件

  • 视图(只存放视图的定义，不存放实际数据)--> 虚表

image-20221031103227138

3.2 学生-课程数据库

模式 S-T

学生表 Student(Sno, Sname, Ssex, Sage, Sdept)

课程表 Course(Cno, Cname, Cpno, CCredit)

选课表 SC(Sno, Cno, Grade)

3.3 数据定义

创建 CREATE (TABLE | VIEW | INDEX)

1
2
3
4
5
CREATE TABLE <表名>(
    <列名> <数据类型> [列级约束],
    <列名> <数据类型> [列级约束],
    [表级约束]
);

数据类型

  • INT
  • VARCHAR
  • DATE
  • TIMESTAMP
  • FLOAT

删除 DROP (TABLE | VIEW | INDEX)

修改 ALTER (TABLE | INDEX)

数据字典： 所有定义信息

1
2
3
4
5
6
7
ALTER TABLE <表名>(
  ADD <新列名> <数据类型> [完整性约束],
  ADD [表级完整性约束],
  DROP <列名> [CASCADE | RESTRICT],
  DROP CONSTRAINT <约束名> [RESTRICT | CASCADE],
  ALTER COLUMN <列名> <数据类型>
);

索引： 加快查询速度

B+ 树： 动态平衡

hash 索引： 查找快

数据库管理员建立索引

数据库管理系统自动完成索引的维护

  • 建立索引
1
CREATE INDEX <索引名> ON <表名> (<列名>[<次序>], <列名>[<次序>]);
  • 修改索引
1
ALTER INDEX <旧索引> RENAME TO <新索引>
  • 删除索引
1
DROP INDEX <索引名>

3.4 数据查询

单表查询

语句格式

1
2
3
4
5
SELECT [ALL|DISTINCT] <列名> 
FROM <表或视图名> | (SELECT 语句) [AS] <别名>
[WEHER <条件>]
[GROUP BY <列名> [HAVING <条件>]]
[ORDERED BY <列名> [ASC | DESC]];

tips

* : 所有列

AS 后跟别名

DISTINCT 去除重复行

查询条件： >, BETWEEN AND, IN, LIKE, IS NULL

多重条件： AND， OR， NOT

IN <值表>: where title in ('微积分Ⅰ','数据库概论')

LIKE： LIKE '<匹配串>'

  • %： 任意字符
  • _: 任意单个字符
  • ESCAPE： 将转义字符转换为普通字符

ASC： 升序， DESC：降序， 默认为升序

聚集函数

  • COUNT [DISTINT] <列名>
  • SUM [DISTINT] <列名>
  • AVG [DISTINT] <列名>
  • MAX <列名>
  • MIN <列名>

GROUP BY(分组)

  • 对查询结果按指定的列分组，值相等的分为一组

WEHRE 子句中不能使用聚集函数作为条件表达式-> 写在HAVING里

多表连接

  • 等值连接 WHERE 中写 = (NATURAL JOIN)

  • 自身连接 需要起别名以作区分

  • 外连接

    • 左外连接 LEFT OUT JOIN
    • 右外连接 RIGHT OUT JOIN

  • 多表连接

嵌套查询

  • 带有IN的子查询

  • 将select from where 语句嵌套在where子句中

sql允许多层嵌套查询

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

当内层查询的返回值为单值时，可以使用比较运算符 (>, <, =)

  • 带有ANY和ALL的子查询： 此时必须使用比较运算

例如 > ANY(子查询), <= ALL(子查询), != ANY(子查询)

  • 带有EXISTS的子查询

​ 带有EXISTS谓词的子句只返回逻辑上的真值和假值

集合查询

  • 并操作

    UNION： 去重

    UNION ALL： 不去重

  • 交操作

    INTERSECT

  • 差操作

    EXCEPT

基于派生表的查询

子查询不仅可以出现在WHERE子句中，还可以出现在FROM子句中(临时派生表)

Derived Table

3.5 数据更新

插入数据

  • 插入元组
1
2
3
INSERT 
INTO <表名> [<列名>,<列名>]
VALUES (<常量1>,<常量2>)；
  • 插入子查询结果
1
2
3
INSERT 
INTO <表名> [<列名>,<列名>]
(SELECT 子查询)；

如果指定属性列，按找指定属性列插入

否则按表的定义中的顺序插入

其中如果使用select子句插入，目标列中的值的个数与类型必须与into子句匹配

修改数据

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

修改数据是不能破坏已有的完整性规则

  • 实体完整性
  • 主码不允许修改
  • 用户定义的完整性
    • NOT NULL 约束
    • UNIQUE 约束
    • 值域约束

删除数据

1
2
3
DELETE 
FROM <表名>
[WHERE <条件>]；

删除满足指定要求的元组

3.6 空值的处理

  • 不清楚具体值
  • 不应该有值
  • 不方便填写

不知道或者无意义的值(NULL)

可以使用 IS NULL 或者 IS NOT NULL 来判断

3.7 视图

特点

  • 虚表，由基本表导出
  • 只存放图的定义，不存放对应数据
  • 内容随基本表而变化

定义

  • 建立视图
1
2
3
CREATE VIEW <视图名> [<列名>, <列名>]
AS <子查询>
[WITH CHECK OPTION];

WITH CHEKC OPTION 保证更新，插入或删除的行满足视图中的定义

可以创建基于多个基本表，基于视图的视图

  • 删除视图
1
DROP VIEW <视图名> [CASCADE];

CASCADE 是级联删除语句

查询

与基本表的查询相似

数据库管理系统实现视图查询的方法

  • 有效性检查
  • 转换成为等价的基本表查询
  • 执行修正后的查询

更新

与基本表类似

但是有一定的局限

  • 由两个以上基本表导出，不允许更新
  • 字段来源于表达式或常数，允许DELETE，不允许INSERT和UPDATE
  • 字段来源于集函数，不允许更新
  • 含有GROUP BY和DISTINCT的，不允许更新
  • 有嵌套查询，且内层的FROM子句中涉及该视图的基本表，不允许更新

作用

  • 简化操作
  • 多种视角
  • 重构数据库时逻辑更加独立
  • 对机密数据提供安全保护
  • 更加清晰的表达查询