数据库的基本概念#

1. ⭐数据库、DBMS、数据库系统

   概念	含义
   数据库 DB	长期存储、有组织、可共享的大量数据的集合
   数据库管理系统 DBMS	管理数据库的软件，位于应用程序和数据库之间（如 MySQL、Oracle、SQLSever）
   **数据库系统 DBS **	数据库、DBMS、应用程序、数据库管理员、硬件等构成的整体
   数据库应用系统	面向具体业务开发的应用程序及其数据库环境 = 数据库 + DBMS + 应用程序 + 数据库管理员 +硬件环境

2. ⭐文件系统 vs 数据库

   对比角度	文件方式	数据库方式
   分散/统一	数据分散在不同文件	数据统一组织和管理
   共享	共享困难	支持多用户共享
   冗余	冗余较多	可以控制冗余
   耦合/独立	程序与数据耦合较强	具有数据独立性
   性质	一致性、安全性难保证	DBMS 统一保证完整性、安全和并发
   适用场景	适合简单、独立的小规模数据	适合长期、共享、结构化业务数据

3. 数据管理技术的产生和发展：人工管理阶段 → 文件系统阶段 → 数据库系统阶段

4. 数据库系统的特点

   1. 数据结构化
   2. 数据共享性高，冗余度低、易扩充
   3. 数据独立性高
   4. 数据由数据库管理系统 统一管理和控制

数据模型的组成要素 & 常用数据模型#

1. ⭐数据模型#

   • 两大类、共三种数据模型：概念数据模型、 逻辑数据模型、物理数据模型

   • 数据模型的组成要素：数据结构、数据操作、数据约束 （二维关系表、原则/插入等、实体完整性）

   • ⭐关系模型基本概念#

     

     

     —>

数据库的三级模式#

1. 从数据库应用开发人员角度看，数据库通常采用 三级模式结构

   层次	描述内容
   外模式	某一类用户看到的局部数据，也叫用户视图
   模式/概念模式	整个数据库的全局逻辑结构
   内模式	数据的物理存储结构、文件组织和存取路径

两种数据独立性#

1. 逻辑数据独立性：概念模式改变时，外模式和应用程序尽量不用改变。
   例如增加一个属性，只需调整外模式与概念模式之间的映像，原有应用仍可使用原来的视图。
2. 物理数据独立性：内模式改变时，概念模式和应用程序不用改变。
   例如把顺序文件改成 B+ 树索引，应用程序仍然执行相同的 SQL。

关系数据结构#

1. 关系
   1. 关系模型只有单一的数据结构：关系；
      关系模型的逻辑结构：二维表
   2. 关系：在域 $D_1, D_2, \dots, D_n$ 上的关系 $R$ 也是一个 $n$ 元有序组的集合， 并且是其笛卡尔积的一个 子集：$R \subseteq D_1 \times D_2 \times \dots \times D_n$
      • 属性：二维表中的 每一列，被称为是该关系中的一个‘属性’
      • 元组 tuple：二维表中的 每一行，$(d_1,d_2,…,d_n)$，$t[A_i]$表示元组t在属性 $A_i$上的取值；$t[i]$ 表示元组t在第i列上的取值。如：(李敏君，软件工程专业，19)
      • 分量：元组中的每一个值$d_i$叫做一个分量.如：李敏君、软件工程专业、19
        元组连接：$t_r t_s$上弧线，表示把两个表连接起来。
      • 基数：一个域允许的不同取值个数
      • 关系的表示：在域 $D_1,D_2,…,D_n$ 上的关系R可以被表示为 $R(A_1,A_2,…,A_n)$
        其中 $A_i$ 是属性名 eg:“专业”
   3. 域
      相同数据类型的值的集合，域内元素互不相同。如：整数集合、{男，女}、char (25) 字符串集合
   4. （候选）码 Key
      若关系中的某一 属性组 的值能唯一地标识一个元组，而其所有的真子集都不能，则称该 属性组 为关系的 ‘候选码’ ，简称 ‘码’
   5. 主码：
   6. 全码 All-key：关系中的所有属性构成的属性组是这个关系的候选码，称为 ‘全码’（All-key）
   7. 主属性 与 非主属性/非码属性
      • 候选码中的诸属性称为该关系的 ‘主属性’（Prime attribute）
      • 不包含在任何侯选码中的属性称为该关系的 ‘非主属性’/‘非码属性’

⭐关系的完整性约束#

1. 实体完整性
   主码属性不能为空 NULL
2. 参照完整性
   1. 外码值要么为空，要么等于被参照关系的主码值
   2. 外码：
      参照关系：R（拿着外码的表，比如学生表）
      被参照关系：S（被引用主键的表，比如专业表）
      

      

      
3. 用户定义的完整性

⭐关系代数#

• 关系运算 选择：$\sigma_{Sdept='IS'}(Student)$、投影：$\pi_{Sdept}(Student)$

• 例

  • 所有：除运算÷；只：

• 最大/最高 在当前的所有顾客中, 查询折扣(discnt)最高的顾客的编号？
  令 $S := C$, “折扣不是最高的顾客的id”
  $R_2 := \pi_{C.cid}(\sigma_{C.discnt < S.discnt}(C \times S))$
  答案是 $\pi_{cid}(C) - \pi_{C.cid}(\sigma_{C.discnt < S.discnt}(C \times S))$

  

  
  

  

• 象集
  该属性值对应的其他属性值集合，相当于除法

  

  

2. 除运算 R ÷ S
   找出R、S中相同的属性列(B、C)，R 剩下的属性列(A) 作为结果。
   $a_i$的所有($b_j$,$c_j$)，$a_i$包含S中的全部b、c。

   

   
   • 例
   

   

基本表 TABLE#

1. 定义基本表
   PRIMARY KEY 主键，唯一标识一行数据，不能重复、不能为空
   UNIQUE 唯一，不能重复，但能为空
   外键 FOREIGN KEY (cno) PREFERENCE course(cno) ON UPDATE/DELETE 规约条件

   1
   CREATE TABLE Student
   2
       (Sno CHAR(9) PRIMARY KEY,  /* 列级完整性约束条件, Sno是**主码**/
   3
       Sname CHAR(20) UNIQUE,  /* **约束**，Sname的取值具有唯一性*/
   4
       Ssex CHAR(2) NOT NULL CHECK (Ssex IN ('男', '女')),/*  非空且只能是男/女 */
   5
       Sage SMALLINT,
   6
       Sdept CHAR(20)
   7
       );
   

   1
   CREATE TABLE enrollment(
   2
       sno char(9) NOT NULL,
   3
       clsno char(8) NOT NULL,
   4
       grade INT CHECK (grade BETWEEN 0 AND 100),
   5
       PRIMARY KEY(sno,clsno), -- 课程班代码和学号联合构成PRIMARY KEY
   6
       );
   

   1
   CREATE TABLE courseclass(
   2
   clsno char(8) PRIMARY KEY,
   3
   cno char(8) NOT NULL,
   4
   tno char(9) ,
   5
   -- 先定义再外键 + 违约规则
   6
   FOREIGN KEY (cno) REFERENCES coures(cno) ON UPDATE CASCADE ON DELETE RESTRICT,
   7
   FOREIGN KEY (tno) REFERENCES teachers(tno) ON UPDATE CASCADE ON DELETE SET NULL
   8
   -- 父表更新cno，子表cno自动同步更新；父表删除cno，子表还有该课程就禁止删除
   9
   -- 父表更新tno，子表tno自动同步更新；父表删除tno，子表对应的tno自动设为NULL
   10
   );
   

   • 违约规则
     什么是违约？ 父表更新主键/删除父表记录会破坏参照关系，触发违约

     关键字	中文	含义
     CASCADE	级联	父表改 / 删 → 子表自动跟着改 / 删
     SET NULL	置空	父表删 → 子表外键自动设为 NULL
     RESTRICT	限制 / 拒绝	数据库直接禁止父表的改/删操作

2. 修改基本表（ALTER TABLE）

   • ADD新增列/完整性约束

     1
     ALTER TABLE Student
     2
     ADD COLUMN S_email VARCHAR(50) NOT NULL UNIQUE;  -- 增加邮箱列，要求非空且唯一
     3
     ADD CONSTRAINT chk_sex CHECK (Ssex IN ('男', '女'));  -- 为 Ssex 增加 CHECK 约束（性别只能是男 / 女）
     

   • DROP 删除列

     1
     ALTER TABLE Student
     2
     DROP COLUMN S_email CASCADE; --删除“邮箱”列，默认RESTRICT
     3
     DROP CONSTRAINT chk_sex; -- 删除性别CHECK约束
     

   • 修改列的定义

     1
     ALTER TABLE Student
     2
     ALTER COLUMN Sage INT;  -- 修改数据类型：年龄从SMALLINT改为INT
     3
     ALTER COLUMN Sname CHAR(30); -- 修改列长度（姓名从 CHAR (20) 改为 CHAR (30)）
     4
     ALTER TABLE Course ADD UNIQUE(Cname); -- 增加课程名称必须取唯一值的约束条件
     

3. 删除基本表

   1
   DROP TABLE <表名>［RESTRICT| CASCADE］;
   

   • RESTRICT：只有在该表没有被其他数据库对象依赖时，才能删除。
   • CASCADE：删除该表的同时，还会删除依赖于该表的相关对象或依赖关系

视图 VIEW#

1. 视图的作用？
   简化复杂查询；
   为不同用户提供不同数据视图；
   隐藏敏感属性，提高安全性；
   阻止不符合条件的数据加入/修改成不符合条件的数据。
2. 定义视图 AS

   1
   CREATE VIEW S_G(Sno,Gavg) -- 可选：列名
   2
   AS
   3
    SELECT Sno,AVG(Grade)
   4
    FROM SC
   5
    GROUP BY Sno;
   

3. 查询视图
4. 更新视图
5. 删除视图

   1
   DROP VIEW S_G [RESTRICT | CASCADE]
   

4. 字符串匹配
   • ⭐字符匹配 LIKE/=

   1
   SELECT* FROM Student WHERE Sno LIKE '24%'  -- 通配符，查询学号以24开头的学生的详细信息
   2
   SELECT* FROM Student WHERE Sno = '24%' -- 查询学号就是'24%'学生的详细信息
   

   • ⭐字符串中的通配符

   1
   SELECT Sname, Sno, Ssex
   2
   FROM Student
   3
   WHERE Sname LIKE '刘%';  -- 查询所有姓刘学生的姓名、学号和性别
   4
   
   5
   SELECT Sname
   6
   FROM Student
   7
   WHERE Sname LIKE '欧阳_'; -- 查询姓"欧阳"且全名为三个汉字的学生的姓名
   8
   
   9
   SELECT Sname, Sno
   10
   FROM Student
   11
   WHERE Sname LIKE '_阳%'; -- 查询名字中第2个字为"阳"字的学生的姓名和学号
   12
   
   13
   SELECT Sname, Sno, Ssex
   14
   FROM Student
   15
   WHERE Sname NOT LIKE '刘%'; -- 查询所有不姓刘的学生姓名、学号和性别
   

5. 多表连接 JOIN

   1
   SELECT DISTINCT s.sid, s.sname
   2
   FROM Student s
   3
   JOIN Enroll e ON s.sid = e.sid
   4
   JOIN Course c ON e.cid = c.cid
   5
   WHERE c.cname = '数据管理基础'
   6
   AND c.dept = '智软学院';
   

   使用 DISTINCT 是为了消除同一学生由于多条匹配记录产生的重复
   • DISTINCT 的位置：SELECT 子句；聚集函数内部。

     1
     SELECT DISTINCT dept
     2
     FROM Student;
     

     1
     COUNT(DISTINCT sid)
     2
     AVG(DISTINCT score)
     3
     SUM(DISTINCT amount)
     

6. 分组与 HAVING先Group 分组后 HAVING
   
7. EXISTS 子查询有结果，返回TRUE；子查询无结果，返回FALSE

   1
   SELECT Sname
   2
   FROM Student S
   3
   WHERE EXISTS (
   4
       SELECT *
   5
       FROM SC
   6
       WHERE SC.Sno = S.Sno
   7
       AND Cno = '1' -- 从 Student 表里，找出那些‘存在’选修 1 号课程记录的学生
   8
   ); -- 查询选修了 1 号课程的学生
   9
   
   10
   SELECT Sname
   11
   FROM Student S
   12
   WHERE NOT EXISTS (
   13
       SELECT *
   14
       FROM SC
   15
       WHERE SC.Sno = S.Sno
   16
       AND Cno = '1'
   17
   ); -- 查询没选1号课程的学生
   

   • 查询选修全部课程的学生

     1
     SELECT Sname
     2
     FROM Student
     3
     WHERE NOT EXISTS ( -- ③ 除去没有选择课程的学生
     4
         SELECT *
     5
         FROM Course -- ② 有NOT EXISTS学生选择的课程中的：学生没有选择的课程
     6
         WHERE NOT EXISTS (
     7
             SELECT *
     8
             FROM SC
     9
             WHERE SC.Sno = Student.Sno
     10
             AND SC.Cno = Course.Cno -- ① 对SC中的每门课程，去Student、Course中寻找有无记录
     11
         ) -- 学生选择的课程
     12
     );
     13
     ✅ 理解：不存在一门课是这个学生没选的
     

   • 例
   

   
   

   

⭐例题#

1. 所有课程都及格：HAVING MIN(grade)>=60

   

2. 修完本院所有必修课

   

3. 每组最大值

   

4. 只讲授本院课程

5. SQL（Structured Query Language）结构化查询语言，是关系数据库的标准语言

6. SQL与关系数据库的三级模式

7. 核心功能：

• 数据定义：CREATE，DROP，ALTER - 数据查询：SELECT - 数据操作：INSERT，UPDATE，DELETE - 数据控制：GRANT，REVOKE

SQL数据定义#

1
CREATE SCHEMA TEST AUTHORIZATION ZHANG
2
    CREATE TABLE TAB1 (
3
        COL1 SMALLINT,
4
        COL2 INT,
5
        COL3 CHAR(20),
6
        COL4 NUMERIC(10,3),
7
        COL5 DECIMAL(5,2) );

1
CREATE UNIQUE INDEX Coucno ON Course(Cno); -- Course表按课程号
2
CREATE UNIQUE INDEX SCno ON SC(Sno ASC,Cno DESC); -- 升序建唯一索引，SC表按学号升序和课程号降序建唯一索引

1
ALTER INDEX SCno RENAME TO SCSno; -- 将SC表的SCno索引名改为SCSno

1
 DROP INDEX Stusname; -- 删除Student表的Stusname索引

SQL数据查询#

聚集函数/统计函数#

1. COUNT 统计元组个数
2. SUM 计算一列的总和/AVG 计算一列的平均值
3. MIN 一列值中的最大值/MIN 一列值中的最小值

---

数据更新#

1. 插入#

   1. 格式

      1
      INSERT
      2
      INTO <表名> (属性名1，属性名2,…)
      3
      VALUES (expr1|NULL , expr2|NULL ,…)
      

      
   2. 插入子查询结果

      1
      INSERT
      2
      INTO <表名>(属性1，属性2，…)
      3
      子查询
      

      

2. 修改#

   1. 格式

      1
      UPDATE <表名>
      2
      SET <属性1>=<expr1>，<属性2>=<expr2>
      3
      WHERE <条件>
      

      

3. 删除#

   1. 格式

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

      

事务#

1. 定义 是用户定义的一个数据库操作序列，这些操作要么全做，要么全不做
2. 例
3. 事务的特性（ACID） 原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)、持久性(Durability)
4. 定义事务
   • 事物的启动：隐式方式（由数据库管理系统来决定，何时为何用户启动一个新的事务）
   • 事物的结束：用户调用commit或rollback命令，显式地结束当前事务；由数据库管理系统强行结束一个用户的当前正在运行中的事务

   1. 事务启动方式#

      • 数据定义命令 DDL
        只要你执行 CREATE、ALTER 等定义语句，系统自动把这一条语句当成一个独立事务。
        执行前，如果你正在做其他操作，系统会自动先提交之前的事务。
      • 将系统设为自动提交方式（打开自动提交标志）
        这是最常见的默认状态。每一条 SELECT、UPDATE 等操作，做完后系统自动帮你 COMMIT，立即生效。
      • 数据操纵命令(DML)
        如果你没有开启自动提交，当你执行 INSERT/UPDATE/DELETE 时，系统检测到你没有事务，就自动帮你开启一个新事务，等着你手动提交或回滚。

   2. 事务结束方式#

      • 事务提交：COMMIT
        事务正常结束，确认所有操作生效
      • 事务回滚：ROLLBACK
        事务异常终止，撤销所有操作。保存点 savepoint，支持部分回滚。只撤销保存点后的操作
5. 相关的SQL语句
   • 事务结束命令 事务提交命令：COMMIT/事务放弃命令：ROLLBACK
   • 事务设置命令
     • 设置事务的自动提交命令：SET AUTOCOMMIT ON | OFF
     • 设置事务的类型：SET TRANSACTION READONLY | READWRITE
   • 在MySQL数据库中，设置事务自动提交功能的SQL命令格式是
     • SET AUTOCOMMIT = 0; /* 关闭事务自动提交功能 */
     • SET AUTOCOMMIT = 1; /* 打开事务自动提交功能 */
   • 只读/读写型事务 SET TRANSACTION READONLY | READWRITE ;
   • 事隔离级别——解决相互干扰
     1. READ UNCOMMITTED 读未提交
     2. READ COMMITTED 读已提交
     3. REPEATABLE READ 可重复读
     4. SERIALIZABLE 串行化
   • 基于封锁的并发控制（Locking）——具体执行策略
     1. 读锁（共享锁/S锁） 事务 T 持有读锁时，只允许别人读，不允许写。作用：保证大家看到的是同一时刻的数据。
     2. 写锁（排他锁/X锁） 事务 T 持有写锁时，别人既不能读，也不能写。作用：保证修改操作不被打断，原子性执行。
     3. 互斥原则 一个数据对象上，同一时刻只能有一种锁（要么一堆读锁，要么一个写锁），不能混着来。

SQL中的空值#

注意取值

视图#

1. 格式

   1
   CREATE VIEW <视图名> [(<列名> [,<列名>]…)]
   2
   AS <子查询>
   3
   [WITH CHECK OPTION];
   

2. 例-行列子集视图 若一个视图是从单个基本表导出的，并且只是去掉了基本表的某些行和某些列，但保留了主码，我们称这类视图为行列子集视图。
3. WITH CHECK OPTION 阻止不符合条件的数据加入/修改成不符合条件的数据…/而基本表没有限制
4. 建立视图
5. 删除视图 DROP VIEW <视图名> [CASCADE]
6. 查询视图
7. 更新视图

数据安全性概述#

1. 数据库安全性：保护数据库防止非法使用造成的数据泄露、更改或破坏，是数据库系统核心性能指标之一

2. 数据库不安全因素 & 安全措施#

   • 非授权用户对数据库的恶意存取和破坏：用户身份鉴别、存取控制、视图
   • 数据库中重要或敏感的数据被泄露：强制存取控制、数据加密存储、加密传输
   • 安全环境的脆弱性：建立可信计算机系统
   • 故障/误操作/并发访问

3. 安全标准#

   • 美国国防部颁布TCSEC(DoD85) → 不同国家在其基础上建立的评估准则ITSEC/CTCPEC/FC → CC (国际通用标准)
   • TCSEC/TDI安全级别划分（从低到高）/ CC评估保证级(EAL)划分

   

   

   - 中国标准：GB 17859-1999 

4. 计算机系统的安全模型

   

5. 数据库管理系统安全性控制模型 数据库安全性控制的常用方法:用户标识和鉴定、存取控制、视图、审计、数据加密

数据库安全性控制#

数据库安全性控制的常用方法：用户标识和鉴定、存取控制、视图、审计、数据加密

数据库设计的基本方法#

数据库设计基本步骤（新奥尔良法）#

2. 概念结构设计#

1. 概念结构设计：把需求抽象成概念模型

2. 描述概念模型的工具：E-R 模型 / EER 模型

3. 概念结构设计方法#

   自顶向下、自下向上、二者混合策略、逐步扩张（学生的核心需求和概念结构 → 再扩展教师等其他需求和概念结构）

3. 逻辑结构设计#

• 逻辑结构设计：把 E-R 图翻译成关系表

• 实体集 & 属性 → 表#

  1. 规则1：实体集转换为关系模式
     • E-R图中有实体：科室、医生、病人、病例
       → 科室表、医生表、病人表、病例表
     • E-R图中有实体：医生，有属性：医生号，姓名，职称
       → 医生（医生号，姓名，职称）
  2. 规则2：组合属性拆成子属性
     • 不写emp_address，而是子属性（staddress、city、state、zipcode 要么全为空，要么全不为空）
  3. 规则3：多值属性拆成新表
     • 对于学号S1307，姓名王承志，选修课程（Database, Operating System, Computer Network）
     • 方法一：纵向展开；方法二：分解出去，单独成表

• 联系转换 & 全参与/非全参与#

  • 补充：实体集与联系的全参与/非全参与

  1. 1:1联系转换

     • 1:1 联系可以单独建表，也可以合并到任意一端（要考虑是否全参与）

     • 规则

       参与情况	建表方式	例子
       两边非全参与（0..1/0..1）	联系单独建表	电单车—电池
       一边全参与(1..1)，一边非全参与(0..1)	依赖存在方+依赖的主码	人—驾驶证
       两边全参与(1..1/1..1)	联系和实体合并成一个表	学生—校园卡

     • 例：非全参与、非全参与（分开单独建表）

       

     • 例：全参与、非全参与（依赖存在的全参与端驾驶证要加上依赖存在的人的主码）

       

     • 例：全参与、全参与（合并）

       

  2. 1联系转换

     • 1 联系通常把 1 端的主码加入到 n 端作为外码
     • 多端全参与：多方加上外码
     • 多端非全参与：3张表

  3. m联系转换

  • 建3张表
  • 例：病人去医生那就诊（一个医生有多个病人，一个病人也有多个医生）

  1
  病人（病人号，姓名）
  2
  医生（医生号，姓名）
  3
  就诊（病人号，医生号，就诊时间，就诊记录）
  

  3. 多元联系转换

     • 实体建表之外，多元联系也建一个表
     • 例：供应商向项目供应零件（E-R图中E-R 图：供应商 —— 供应 —— 项目 —— 零件）
       某供应商给某项目供应某零件，供应量是多少。

     1
     供应商(供应商号, 供应商名)
     2
     项目(项目号, 项目名)
     3
     零件(零件号, 零件名)
     4
     
     5
     供应(供应商号, 项目号, 零件号, 供应量)
     

  4. 单个实体集内部的联系转换

     • 例：职工之间存在领导关系
       这里“领导职工号”也是职工号，表示该职工的上级（1：1）

     1
     职工(职工号, 姓名, 领导职工号)
     

     • 如果是 m 的内部关系，需要新建关系

     1
      职工(职工号, 姓名)
     2
      合作(职工号1, 职工号2, 项目名)
     

     • 例：一个职工可以管理多个下级职工、一个职工只能有一个上级职工

  5. ISA联系转换（父子类）

     • 方法一：建3个表（不相交 + 部分特化）
       建学生（），教师（），职工（），硕导（），博导（）
     • 方法二：只建2个子表（不相交 + 完全特化II）
     • 方法三：建1个表

• 具有相同码的关系模式合并

  1. 如果两个关系模式具有相同主码，且语义上属于同一对象，可以考虑合并
  2. 例：

  1
  学生基本信息(学号, 姓名, 性别)
  2
  学生学籍信息(学号, 专业, 入学年份)
  

  可以合并为：学生(学号, 姓名, 性别, 专业, 入学年份)

数据模型优化#

1. 数据模型优化通常用规范化理论作指导
2. 目标：
   • 减少数据冗余（拆成多个表）
   • 避免更新异常
   • 避免插入异常
   • 避免删除异常
   • 提高数据一致性
3. 分解方法：水平分解（分行：分成23级学生、24级学生）、垂直分解（分列：分成学生基本信息+联系方式）
4. 设计用户子模式（视图）
   • 视图作用：
     简化用户操作；
     满足不同用户需求；
     隐藏敏感数据，保证安全性；
     封装复杂查询。

物理结构设计#

1. 物理结构设计：为逻辑数据结构选择最适合应用环境的物理结构
2. 常见的存取方法

   方法	适合场景
   B+ 树索引	范围查询/连接/排序/最值
   Hash 索引	等值查询/连接
   聚簇存取	经常按某属性集中访问的数据

数据库实施#

嵌入式SQL#

1. 嵌入式SQL是将SQL语句嵌入程序设计语言中，被嵌入的程序设计语言称为 (宿)主语言。如Java、C++

2. 格式#

   前缀 EXEC SQL  后缀 ;   into获取结果元组值   前缀 : 来区分主变量

   1
   EXEC SQL select Sno, Sname, Sage
   2
       into :hsno, :hsname, :hsage -- 输出主变量（由SQL语句对其赋值/设置状态信息，返回给应用程序）
   3
       from Student
   4
       where Sno = :givensno ; -- 输入主变量（由应用程序对其赋值，在SQL语句引用）
   5
       -- 根据程序传入的学号，从 Student 表查询单个学生的学号、姓名、年龄，并把结果存入主变量
   

3. SQL通信区（SQLCA）#

   SQL语句执行后，数据库系统需要反馈给应用程序的信息：描述系统当前工作状态、描述运行环境
   这些信息将送到并保存在SQL通信区中、应用程序从SQL通信区中获取这些状态信息。

   • 定义SQLCA
     用 EXEC SQL INCLUDE SQLCA 定义
   • 使用SQLCA
     SQLCA中有一个用于存放每次执行SQL语句后返回当前SQL语句执行状态的代码变量SQLCODE；
     每执行完一条SQL语句之后都测试：执行成功返回SUCCESS（0），否则表示出错或异常。 SQLCODE的值，以了解该SQL语句执行情况并做相应处理。

4. 主变量#

   嵌入式SQL语句中可以使用的主语言程序变量

   • 指示变量
     是一个整型变量，是一种特殊的主变量，用来“指示”相关主变量的值是否为 空值
     指示变量的用途：指示输入主变量是否为空值；检测输出变量是否为空值，值是否被截断

     指示变量取值	含义
     0	主变量正常接收到了非空的数据库值
     > 0	数据库字符串被截断，只存了部分到主变量
     -1	数据库值是NULL，主变量里的值无意义

   • 例：查询返回的Grade属性值是否为NULL/根据课程号把该课程的成绩都置为空。

     1
     EXEC SQL SELECT Sno, Cno, Grade
     2
         INTO :Hsno, :Hcno, :Hgrade :Gradeid
     3
         -- 多一个:Gradeid。:Hgrade 是接收成绩的主变量，:Gradeid 就是它的指示变量
     4
         FROM SC
     5
         WHERE Sno = :givensno AND Cno = :givencno;
     6
     -- 如果数据库里 Grade 不是空值 → Gradeid = 0，可以直接用 Hgrade 的值
     7
     -- 如果数据库里 Grade 是 NULL → Gradeid = -1，说明 Hgrade 里的值是无效的，不能直接用
     

     1
     gra_ind = -1 ;  -- 要把数据库里的值改成 NULL
     2
     EXEC SQL UPDATE SC
     3
         SET Grade = :Hgrade INDICATOR :gra_ind
     4
         -- 把 Grade 字段的值设置成 :Hgrade，但要首先参考指示变量 gra_ind
     5
         -- 数据库会忽略 :Hgrade 里的数字，直接把 Grade 设为 NULL
     6
         WHERE cno = :givencno; -- 根据学号查找
     

   • 为了使用主语言变量，必须首先在DECLARE SECTION部分声明这些变量
     Why? 编译时类型检查、预先申请内存空间。只有在declare section中定义的主语言变量才能被用在ESQL中

     1
     exec sql begin declare section; -- 主变量声明开始
     2
     char hsno[10]，givensno[10], hsname[21];
     3
     int hsage;
     4
     exec sql end declare section; -- 主变量生声明结束
     5
     
     6
     EXEC SQL select Sno, Sname, Sage
     7
     into :hsno, :hsname, :hsage
     8
     from Student
     9
     where Sno = :givensno ; --根据学号查询学号、姓名、年龄
     

5. ⭐游标 Cursor#

   游标是系统为用户开设的一个数据缓冲区，用于存放SQL查询的执行结果。解决SQL批量返回数据、主语言单条处理之间的矛盾

   • 声明游标 EXEC SQL DECLARE 游标名 CURSOR FOR 查询语句;
   • 打开游标 EXEC SQL OPEN 游标名;
   • 提取数据 EXEC SQL FETCH 游标名 INTO 主变量;
   • 关闭游标 EXEC SQL CLOSE 游标名;

6. 过程#

• 建立/关闭数据库连接

  1
  EXEC SQL CONNECT TO target [AS 连接名] [USER 用户名]; -- 建立连接
  2
  EXEC SQL SET CONNECTION 连接名 | DEFAULT; -- 切换连接
  3
  EXEC SQL DISCONNECT 连接名 | DEFAULT; -- 关闭连接
  

• 例 依次检查某个系的学生记录，交互式更新某些学生年龄。

  1
  EXEC SQL BEGIN DECLARE SECTION; /*主变量声明开始*/
  2
      char Deptname[21];
  3
      char Hsno[10];
  4
      char Hsname[21];
  5
      char Hssex[3];
  6
      int HSage;
  7
      int NEWAGE;
  8
  EXEC SQL END DECLARE SECTION; /*主变量声明结束*/
  9
  
  10
  long SQLCODE;
  11
  EXEC SQL INCLUDE SQLCA; /*定义SQL通信区*/
  12
  
  13
  #include <sqlca.h>
  14
  
  15
  int main(void) /*C语言主程序开始*/
  16
  {
  17
      int count = 0;
  18
      char yn; /*变量yn代表yes或no*/
  19
      printf("Please choose the department name(CS/MA/IS): ");
  20
      scanf("%s",deptname); /*为主变量deptname赋值*/
  21
      EXEC SQL CONNECT TO TEST@localhost:54321 USER "SYSTEM"/"MANAGER"; /*连接数据库TEST*/
  22
      -- 我现在要登录连接到数据库，数据库地址是本机，端口是 54321，数据库名字叫 TEST，登录账号是 SYSTEM，密码是 MANAGER
  23
      EXEC SQL DECLARE SX CURSOR FOR /*定义游标SX*/
  24
          SELECT Sno,Sname,Ssex,Sage /*游标SX对应的查询语句*/
  25
          FROM Student
  26
          WHERE SDept = :deptname;
  27
      EXEC SQL OPEN SX;
  28
  /*打开游标SX，执行游标对应的查询，并指向查询结果的第一行*/
  29
  
  30
      for (;;) -- 循环读取游标里的每一条数据，直到游标读完 / 出错时退出
  31
      {
  32
          /* 推进游标，将当前数据放入主变量 */
  33
          EXEC SQL FETCH SX INTO :Hsno, :Hsname, :Hssex, :HSage;
  34
          if (sqlca.sqlcode != 0) { -- 操作不成功时退出
  35
              break;
  36
          }
  37
  
  38
          /* 是第一行的话，打印表头和学生信息 */
  39
          if (count++ == 0) {
  40
              printf("\n%-10s %-20s %-10s %-10s\n", "Sno", "Sname", "Ssex", "Sage");
  41
          }
  42
  
  43
          /* 打印当前学生信息 */
  44
          printf("%-10s %-20s %-10s %-10d\n", Hsno, Hsname, Hssex, HSage);
  45
  
  46
          /* 询问用户是否要更新该学生的年龄 */
  47
          printf("UPDATE AGE(y/n)? ");
  48
          do {
  49
              scanf("%c", &yn);
  50
              -- getchar();  --吃掉多余的换行符
  51
          } while (yn != 'N' && yn != 'n' && yn != 'Y' && yn != 'y');
  52
          -- 强制让用户必须输入 Y/y/ N/n 才往下走，输错就一直重新输
  53
  
  54
          /* 如果选择更新操作 */
  55
          if (yn == 'y' || yn == 'Y') {
  56
              printf("INPUT NEW AGE: ");
  57
              scanf("%d", &NEWAGE);
  58
              getchar();
  59
  
  60
              /* 嵌入式SQL更新语句*/
  61
              EXEC SQL UPDATE Student
  62
                  SET Sage = :NEWAGE  -- Sage更新为读入的NWEAGE
  63
                  WHERE CURRENT OF SX;
  64
          } /* 对当前游标指向的学生年龄进行更新 */
  65
      }
  66
  
  67
      /* 收尾 */
  68
      EXEC SQL CLOSE SX; -- 关闭游标SX，不再和查询结果对应
  69
      EXEC SQL COMMIT WORK; -- 提交更新
  70
      EXEC SQL DISCONNECT TEST; -- 断开数据库连接
  71
  } -- 只在 EXEC SQL FETCH SX INTO 语句之后检查了状态码 SQLCA.SQLCODE
  72
  -- 在其他嵌入式SQL语句之后，都没有检查SQL语句的执行状态码！
  73
  -- Solution：添加异常处理👇
  

1
EXEC SQL DECLARE SX CURSOR FOR
2
    SELECT Sno,Sname,Ssex,Sage
3
    FROM Student
4
    WHERE Sdept=:deptname;
5
EXEC SQL OPEN SX;
6
for(;;){
7
    EXEC SQL FETCH SX INTO :HSno,:Hname,:HSsex,:HSage;
8
    if (SQLCA.SQLCODE != 0) break; -- 操作不成功，则退出循环
9
    ……
10
    EXEC SQL UPDATE Student
11
        SET Sage=:NEWAGE
12
        WHERE CURRENT OF SX;  -- 只对当前游标指向的一行记录
13
    ……
14
}
15
EXEC SQL CLOSE SX;

过程化SQL#

1. what？写在数据库中的带编程逻辑的SQL语言，可用于定义触发器、存储过程、存储函数对应的SQL程序块。
2. 过程化SQL vs 嵌入式SQL
   可独立编程，不再需要区分 主变量 与 SQL变量
   不需要经历从预编译到编译的处理
   在数据库服务器内部实现数据交换与处理
3. 常用的两种过程化SQL：Oracle：PL/SQL；SQL Server：T-SQL