数据库知识点复习------第三章

关系数据库标准语言---SQL

SQL(Structured Query Language)：结构化查询语言，是关系型数据库的标准语言

SQL是一个通用的，功能极强的关系数据库语言，被国际上绝大多数商品化关系型数据库所采用

3.1 SQL概述

SQL的特点：

1. 综合统一：集DDL，DCL, DML于一体

2.高度非过程化

3.面向集合的操作方式

4.以同一种语法结构提供两种使用方法：可以交互式使用，也可以嵌入式使用

5.以简洁的自然语言作为操作语言

SQL支持数据库三级模式体系结构

3.2 数据定义

SQL的数据定义语句：

关于模式：定义模式实际上是定义了一个命名空间，在这个空间中可以定义该模式包含的数据库对象，例如基本表，视图，索引等。

CREATE SCHEMA <模式名> Authorization <用户名>; //定义模式

1. 创建基本表

常用的一些约束：

创建基本表的例子：

2. 删除基本表

DROP TABLE <表名>;

基本表删除后，表里的数据和表上的索引都会被删除，表上的视图往往会被保留，但无法引用。

删除基本表时，系统会从数据字典中删去有关该基本表及其索引的描述。

3.修改基本表

举例：

向Student表增加”入学时间“列，其数据类型为日期型。

ALTER TABLE Student  ADD Scome DATE;

注：不论基本表中是否已经有数据，新加入的列一律为空值。

将年龄的数据类型改为半字长整数。

ALTER TABLE Student MODIFY Sage SMALLINT;

注：修改原有的列定义可能会损坏已有的数据。

删除学生姓名必须取唯一值的约束。

ALTER TABLE Student DROP UNIQUE(Sname);

3.3 索引

单一索引（Unique Index）：每一个索引值只对应唯一的数据记录。当建立单一索引后，索引项不可以再插入已有值，但可以有多个空值。同样，当建立单一索引时，如果待索引项已经有了相同值则不能建立单一索引。

聚簇索引（Cluster Index）：索引项的顺序与表中数据记录的物理顺序一致。（一个表里面只能有一个聚簇索引）

索引是定义在存储表(TABLE)之上的，有助于无需检索所有记录而快速定位所需记录的一种辅助存储结构，由一系列存储在磁盘上的索引项（index entries）组成，每一个索引项由两部分组成：

1. 索引字段：由TABLE中某些列（通常是一列）中的值串接而成。索引中通常存储了索引字段的每一个值。索引字段类似于词典中的词条。

2. 行指针：指向TABLE中包含索引字段值的记录在磁盘上的存储位置。行指针类似于词条在数据中的页码。

索引字段相当于查字典的时候的首字母，行指针相当于告诉你这个字母开头的单词在哪一页。

存储索引项的文件为索引文件，存储表叫做主文件。

1. 创造索引

例题：

CREATE UNIQUE INDEX index_StuSno ON Student(Sno);

CREATE UNIQUE INDEX index_CCno ON Course(Cno);

CREATE UNIQUE INDEX index_SCno ON SC(Sno ASC, Cno DESC);

CREATE CLUSTER INDEX stus_name ON Student(Sname ASC);

2.删除索引

思考题：（这里都是对表进行操作，所有是属于数据定义的范畴）

3.4 查询

查询基本语法：

示例1：

在投影的定义中，会自动去除重复的行，但是在SQL里面，需要使用DISTINCT才能去除重复行。

DISTINCT的作用范围是所有列，也就是说一个SELECT语句用一个DISTINCT就可以了。

1. WHERE---筛选元组

如果条件是在某个集合，可以用IN:

匹配字符串(LIKE)

如果条件是要匹配某个字符串，可以用LIKE:

例：

注意：一个汉字相当于两个字符，所以使用了两个下划线。

如果要匹配的字符串里面本身就有下划线，就需要使用转义：

空值(NULL)

涉及到了空值的查询需要使用 IS NULL 或者 IS NOT NULL，而不能使用 =NULL 或者 !=NULL.

多个条件的查询用AND或者OR

对查询结果排序输出(ORDER BY):

使用集函数(COUNT, SUM, AVG, MAX, MIN):

不多赘述，看下面几个例子就会了：

分组查询（GROUP BY):

GROUP BY可以细化集函数的作用对象。

未对集函数分组，集函数将作用于整个查询结果；若使用了集函数，集函数将分别作用于每个组。

易错点：

上述写法是错误的，集函数要写在HAVING后面。(易混淆的点：where是用于筛选元组的，having是用来筛选group的！）

多表查询（此时需要用到连接）

普通连接

外连接（自然连接）

嵌套查询

不相关子查询

有时，嵌套查询也可以通过连接查询实现

采用不相关子查询的效率要优于连接查询。

带有ANY或者ALL的子查询：

注意不要忘了先决条件 Sdept!='IS'

相关子查询

主要重难点就是查询选修了所有课程的学生。要先会写元组演算表达式，接着再写SQL语言。

3.5 SQL查询实例分析

在这里就不重复ppt上面的内容了，只是说一些踩坑点

1. 在查询不及格人数的时候，一个要注意加DISTINCT，因为SC表里面常常出现一个学生选多门客，然而就算一个学生多门课同时不及格，但他也只是一个人。

2. 集函数没有复合功能，MAX(AVG(grade))这种写法是不对的。

3. 查询的输出只能取自最外层查询所使用的表

4. 基于派生表来进行数据查询（偶尔会用到，就是把查询结果作为一个新的表看待）

3.6 数据更新

1. 插入数据

1.1 插入单个数据

INSERT INTO <表名> VALUES(属性1，属性2);

INSERT INTO <表名>(属性名1，属性名2） VALUES(属性1，属性2);

可以在表名后面指定属性列，也可以不指定（但此时VALUES里面的数据就必须要按照表的属性列的顺序输入）

1.2 插入子查询的结果

INSERT INTO <tableName0> [(<ATTRIBUTE1>, <ATTRIBUTE2>)]

SELECT COL1, COL2 FROM tableName1 WHERE ...;

2. 修改数据

UPDATE <tableName>

SET <列名> = <表达式> [ , <列名> = <表达式>] ...

WHERE <条件> ;

用子查询指定要更改的元组：

相关子查询：

不相关子查询：

3.删除数据

DELETE FROM <tableName> [WHERE <condition>];

使用子查询指定要删除的记录（和UPDATE 类似）

3.7 视图(VIEW)

视图是从一个或几个基本表（或者视图）导出的表，它与基本表不同，是一个虚表。

在数据字典中只存放视图的定义。

基表中的数据发生变化，从视图中查询出来的数据也随之改变。

视图一经定义，就可以像基本表一样被查询或者删除，也可以修改，但是也会随之修改基本表中的对应数据。

视图对应三级模式体系结构中的外模式。（基本表对应的是模式）

1. 建立视图

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

最好加列名的情况：某个目标列是集函数或者列表达式；多表连接选择了同名列作为视图字段；需要在视图中为某个列选取更合适的名字；

WITH CHECK OPTION : 对视图进行更新操作，数据需要满足视图定义的谓词条件（子查询的条件表达式）

注：DBMS执行CREATE VIEW语句时只是把视图的定义存入数据字典，并不会立即执行其中的SELECT语句，当需要对视图进行查询时，再按视图的定义从基本表中将数据查出来。

例:

WITH CHECK OPTION的作用示例：

不允许把Dno从'IS'改为其它(比如'CS')

选取了同名列作为视图字段，最好加上视图中列名:

使用视图来构建视图：

使用了表达式时，也需要指定视图中的列名：

使用了集函数时，需要指定视图的列名：

一个注意点：（最好不使用SELECT *)

2. 删除视图

DROP VIEW <视图名>

PPT看到了406页。

3.8 嵌入式SQL

1. SQL与主语言（例如C语言）交互的工具：

1. SQL通信区(SQLCA, SQL Communication Area)

1). 像主语言传递SQL语句的执行状态信息

2).主语言能够据此控制程序流程

2.主变量

1). 主语言像SQL提供参数

2). 将SQL语句查询数据库的结构交给主语言做进一步处理

3. 游标

SQL的查询结果通常是一个集合，通过游标以此遍历每一条SQL的查询结果。(解决集合性操作语言和过程性操作语言的不匹配）

2. 嵌入式SQL的执行流程

3. 嵌入式SQL实例

4. 嵌入式SQL语句复习

一. 不使用游标：

1. 说明性语句

主变量声明：

EXEC SQL BEGIN DECLARE SECTION;

/* 对主变量的声明 */

EXEC SQL END DECLARE SECTION;

SQLCA声明：

EXEC SQL INCLUDE SQLCA;

2.数据定义语句

在数据定义语句中不能够使用主变量，使用主变量的方式是 :var_name （需要主变量名前面加冒号）

3. 查询语句（结果为单记录）（不常用，更多情况下还是使用游标）

最简单情况：

使用了指示变量：

4. 非游标形式的UPDATE

使用指示变量

4. 非游标形式的DELETE和INSERT

DELETE语句(加入某个学生退学了，现在需要将其记录删掉）:

EXEC SQL DELETE FROM S 

WHERE sno = :givesno;

INSERT 语句(某个学生后面选了一门课，新增一门选课记录)：

gradeid = -1;

EXEC SQL INSERT INTO SC(SNO, CNO, GRADE)

VALUES(:givesno, :givecno, :givegrade:gradeid);

二. 使用游标：

1. 说明游标
EXEC SQL DECLARE <游标名> CURSOR 

FOR <SELECT语句>;

2. 打开游标

EXEC SQL OPEN <游标名>;

3. 移动游标指针，然后取当前记录

EXEC SQL FETCH <游标名> INTO <主变量1> , <主变量2> ;

4. 关闭游标

EXEC SQL CLOSE <游标名>;

实例：

通过CURSOR获取内容的关系语句：

定义CURSOR的语句基本一致，下面简单说明一下如何使用游标进行更新和删除，其实和普通的SQL语句相比，就差了一个 WHERE CURRENT OF <游标名>    

    .

删除操作与之类似：

EXEC SQL DELETE FROM S

WHERE CURRENT OF SX.

关于动态SQL，其实和上面的静态SQL差不多，区别在于具体执行什么操作需要靠分支判断和字符串拼接来实现。

分支判断和字符串拼接过程：

执行最终拼接好的字符串的过程：

sqltext是主变量，也就是上面经过用户输入和if判断后得到的具体的SQL操作语句。