三、关系数据库标准查询语言SQL

一、SQL概述

(一)、SQL语言的组成

1.数据定义（DDL）

——定义数据库的逻辑结构，包括基本表、视图、索引等

2.数据操纵(DML)

——包括查询和更新，更新又包含插入、删除和修改

3.数据控制(DCL)

——授权、完整性规则描述、事务控制等

4.嵌入式SQL(ESQL)

——在宿主语言中使用SQL的规则

(二)、SQL语言的特点

——综合统一、面向集合的操作方式、高度非过程化、统一的语法结构提供两种使用方式(自含式、嵌入式)、语言简洁

-DDL: create drop alter

-DML: select insert delete update

-DCL: grant revoke commit rollback

二、数据定义语言（DDL）

(一)、定义、删除与修改基本表

1.定义基本表语法

create table <表名> (<列名><数据类型>(列级约束条件)，<列名><数据类型>(列级约束条件)，...，<表集完整性约束条件>);

2.修改表语法

alter table <表名>    ADD<新列名><数据类型>(列级约束条件);
alter table <表名>    MODIFY<列名><数据类型>;
alter table <表名>    DROP<完整性约束条件>;

3.删除表语法

drop table <表名>;

(二)、建立和删除索引

1.索引的建立语法

create unique index <索引名> on <表名>(<列名1><次序>，...)
    <次序>可以是ASC和DESC

2.索引的删除语法


drop index [<表名>]<索引名>

三、SQL的数据查询（DML）

    SELECT <目标表达式1> ,<目标表达式2> ... ...
    FROM <表名或视图名1> ，<表名或视图名2>... ...
    WHERE <条件表达式>
    GROUP BY <列名表达式1>，<列名表达式2> HAVING <条件表达式>
    ORDER BY <列名表达式1> ASC|DESC, <列名表达式2> ASC|DESC

—先按WHERE子句条件从FROM子句指定的表/视图中找出满足条件的元组(选择)

—如有GROUP子句，则将结果按<列名表达式>的值分组，该<列名表达式>值相等的元组为一个组，通常会在每组中使用聚合函数。

—如果GROUP子句带HAVING子句，则对组过虑，将满足条件的组输出

—再按SELECT子句中的目标表达式选择出元组中的属性，形成结果表(投影)

—如果ORDER子句，则将结果按<列名表达式1>的值升序或降序排列

(一)、单表查询

——消除取值重复行

 SELECT DISTINCT SD FROM S

——查询满足条件的元组

比较大小：<、<= 、>、>=、=、<>

 SELECT SN,SA FROM S WHERE SD=’CS’
 SELECT * FROM S WHERE SA<20

确定范围：BETWEEN... AND

 SELECT * FROM S WHERE SA BETWEEN 20 AND 21

2. 确定集合：IN

 SELECT * FROM S WHERE SD IN (‘CS’,’IS’,’MA’)

3. 字符匹配：LIKE，转义字符’\’

 SELECT * FROM S WHERE S# LIKE ‘TB%’
 SELECT * FROM S WHERE SN LIKE ‘刘_’

4.涉及空值的查询：IS NULL

SELECT * FROM SC WHERE GR IS NULL

5.多重条件查询：

 SELECT * FROM S WHERE SD=’CS’ AND SA<20

——查询结构排序

ORDER BY <字段表达式> ASC|DESC 
SELECT * FROM SC WHERE C#=’3’ ORDER BY GR DESC

——使用集(聚合)函数

COUNT 、SUM、AVG、MAX、MIN
SELECT COUNT(*) FROM S
SELECT COUNT(DISTINCT S#) FROM SC
SELECT AVG(GR) FROM SC WHERE S#=’95001’
SELECT MAX(GR) FROM SC WHERE C#=’1’

——查询分组：GROUP BY

SELECT C#,COUNT(*) FROM SC GROUP BY C# 
SELECT S# FROM SC GROUP BY S# HAVING COUNT(*) >3

(二)、连接查询

——等值与非等值链接查询自然连接

SELECT S.*,SC.* FROM S,SC WHERE S.S# = SC.S#

——自身连接

SELECT f.C#, s.CP FROM C f,C s WHERE f.CP=s.C#

——外连接

SELECT S#,SN,SS,SA,SD,C#,GR 
FROM S LEFT OUTER JOIN SC ON S.S#=SC.S#

——复合条件连接

#检索选修课程号‘2’且成绩在90分以上的所有学生 
SELECT S.S# ,SN FROM S,SC 
WHERE S.S# = SC.S# AND SC.C#=’2’ AND SC.GR>=90

(三)、嵌套查询

——带IN谓词的子查询

检索与“刘晨”同在一系的学生信息

SELECT S#,SN,SD FROM S WHERE SD IN
(SELECT SD FROM S WHERE SN＝‘刘晨’)


本例可以通过自连接来实现

SELECT s1.S#, s1.SN, s1.SD FROM S s1, S s2
WHERE s1.SD = s2.SD AND s2.SN=’刘晨’

——带比较运算的子查询

当确定子查询的返回值是唯一时，可以使用比较运算
符(注意子查询在比较符后)

SELECT S#,SN FROM S WHERE SD=
(SELECT SD FROM S WHERE CN=’刘晨’)

——带ANY和ALL的子查询(子查询返回多值时用)

检索其他系中比IS系任一学生年龄小的学生名单

SELECT S＃,SN FROM S WHERE SA < ANY
(SELECT SA FROM S WHERE SD＝‘IS’)
AND SD<>‘IS’
ORDER BY SA DESC
等价于
SELECT S＃，SN FROM S WHERE SA < 
(SELECT MAX（SA） FROM S WHERE SD＝‘IS’)
AND SD <> ‘IS’
ORDER BY SA DESC

——带EXISTS的子查询(不返回任何数据，只返回True和False)

检索所有选修了课程号为‘1’的学生姓名

SELECT SN FROM S WHERE EXISTS
(SELECT * FROM SC WHERE S# = S.S# AND C# = ‘1’)

注意：此例中子查询的查询条件依赖于外层父查询，称此类查询为相关子查询(corelated subquery)。 

等价连接实现：

SELECT SN FROM S,SC WHERE S.S# = SC.S# AND C# = ‘1’

注：SQL中没有(任取x)p，故须转换为¬(存在x(¬p))，p->q应被等价为¬p∨q

(四)、集合查询

INTERSECT、UNION、MINUS

检索选修了课程号为C01或C02的学生学号

SELECT S# FROM SC WHERE C＃＝‘C01’
UNION  SELECT S# FROM SC WHERE C＃＝‘C02’
等价于：
SELECT S# FROM SC WHERE C# IN (‘C01’，‘C02’)

四、SQL的数据更新(DML)

(一)、数据插入

——插入单个元组

insert into <表名> <列名1>,... values(<'常量1'>，<'常量2'>);

——插入子查询结果

insert into <表名> <列名1>,... <子查询>

(二)、数据修改

update <表名> set <列名> = <表达式> where<条件>

——修改某一个元组的值

UPDATE S SET SA=22 WHERE S# =’S001’

——修改多个元组的值

UPDATE S SET SA＝SA＋1

——带子查询的修改语句

将计算机科学系所有的学生成绩置零

UPDATE SC SET GR=0
WHERE ‘CS’ = (SELECT SD FROM S WHERE S# = SC.S#)

(三)、数据删除

delete from <表名> where <条件>

——删除某一个元组的值

delete from S where S# = 'S001

——删除多个元组的值

delete from SC

——带子查询的删除语句

删除计算机科学系所有学生的选课记录

DELETE FROM SC WHERE ‘CS’=(
SELECT SD FROM S WHERE S#=SC.S#) (相关子查询) 
DELETE from SC where S# in 
(SELECT S# from S where SD=’CS’) (非相关子查询)

五、视图

(一)、定义视图

1.建立视图

create view <视图名> (<列名1>,...)
as <子查询> with check option
with check option 表示对视图更新时自动验证子查询条件

建立一个反映学生出生年月的视图

    CREATE VIEW BT_S（S＃，SN，SB）as
    SELECT S#, SN,2003－SA FROM S

建立一个学生学号和平均成绩的视图

    CREATE VIEW S_G（S＃，AVG_GR）AS 
    SELECT S#, AVG(GR) FROM SC GROUP BY S#

2.删除视图

drop view <视图名>

(二)、查询视图

——对视图的查询转化为对基本表的查询成为视图的消解

SELECT S＃，SA FROM IS_S WHERE SA <20

消解为:

SELECT S# ,SA FROM S WHERE SD=’IS’ AND SA <20

SELECT * FROM S_G WHERE AVG_GR>90

消解为:

SELECT S#, AVG(GR) FROM SC WHERE AVG(GR)>90 GROUP BY S#  (错误)
SELECT S#, AVG(GR) FROM SC GROUP BY S# HAVING AVG(GR)>90  （正确）

(三)、更新视图

——视图的修改

将信息系学生视图中学号为S001的学生姓名改为‘刘辰’

UPDATE IS_S SET SN=‘刘辰’ WHERE S#=‘S001’

视图消解为：

UPDATE S SET SN=‘刘辰’ WHERE S#=‘S001’AND SD=‘IS’

——视图的插入

INSERT INTO IS_S VALUES (‘S001’,‘刘辰’,20)

视图消解：

INSERT INTO S VALUES (‘S001’,‘刘辰’,NULL,20,‘IS’)

——视图的删除

在信息系学生视图中删除记录

DELETE FROM IS_S WHERE S#=‘S001’

视图消解 :

DELETE FROM S WHERE S#=‘S001’AND SD=‘IS’

注：不允许更新的视图规则

由两个以上基本表导出
视图的字段来自常数或表达式，只允许delete
视图的字段来自集函数
视图中含有group by
视图中含义distinct
视图定义由嵌套查询，且内层查询涉及到导出本视图的基本表
不允许更新视图上定义的视图

(四)、视图的用途

简化用户的操作
使用户多角度看待同一数据
对重构数据库提供了一定的逻辑独立性
对数据提供安全保护

六、数据控制语言(DCL)

(一)、授权

语法

    GRANT {ALL PRIVILEGES <权限>}
    [on <对象类型><对象名>]
    TO {PUBLIC <用户>,...}
    WITH GRANT OPTION

例子

    GRANT SELECT ON TABLE S To USER1
    GRANT ALL Privileges ON TABLE S,C TO U2,U3
    GRANT INSERT ON TABLE SC TO U5 WITH GRANT OPTION

(二)、收回权限

语法


 REVOKE {ALL PRIVILEFES <权限>}
 ON <对象类型><对象名>
 FROM {PUBLIC<用户>，..}

示例

REVOKE SELECT ON TABLE SC FROM PUBLIC;
REVOKE UPDATE(SD),SELECT ON TABLE S FROM U4;
REVOKE INSERT ON TABLE SC FROM U5;

七、嵌入式SQL语言

SQL语言是非过程的，而应用大多是过程化的，故通过高级语言来弥补SQL过程控制的不足。
将SQL嵌入高级语言来执行，称嵌入式SQL语言

(一)、嵌入式SQL的一般形式

对于ESQL的处理，DBMS一般有两种处理方式：预编译 和 修改和扩产宿主语言以处理SQL
ESQL一般形式：EXEC SQL <SQL 语句>
ESQL根据其作用不同分为两类：
- 可执行语句
- 说明性语句

(二)、嵌入式SQL语句与主语言之间的通信

数据库工作单元和主语言工作单元之间的通信有
- 向主语言传递SQL语句的执行状态
- 主语言向SQL语句提供参数
- 将SQL语句查询数据库结果交主语言进一步处理
相应的通过SQLCA、主变量、游标来实现
SQL通信区
- SQLCA是一个数据结构，定义语句
```
EXEC SQL INCLUDE SQLCA
```
- SQLCODE反映每次执行SQL语句的结果
主变量
- 主要功能：ESQL可以使用主语言的变量来输入和输出数据
- 分类：输入、输出主变量、指示变量
- 使用方法
  - 所有主变量在定义区定义
  - 可以在SQL中任意表达式的地方出现
  - 在SQL语句中，主变量前加';'，在诸语言中不必加
  - 指示变量用于为输入变量赋空值或指示输出变量是否空值
游标
- 使用原因:SQL语句是面向集合的，而主语言是面向记录的
- 主语言和SQL语言的分工
  - SQL语言负责直接和数据库打交道
  - 主语言用来控制程序流程以及对SQl的执行结构进一步处理
  - SQL语言用主变量从主语言接受执行参数操作数据库->SQL语言的执行状态由DBMS送至SQLCA->主语言从SQLCA取出状态信息，据此决定下一步操作
  - SQL的执行结果通过主变量或游标传给主语言处理

(三)、不使用游标的SQL语句

说明性语句

EXEC SQL INCLUDE SQLCA;
EXEC SQL BEGIN DECALRE SECTION;
EXeC SQL END DECALRe SECTION;

数据定义语句

EXEC SQL CREATE TABLE S(
    S# char(10),
    SN char(10),
    SS char(2),
    SA int,
    SD char(5)));
    EXEC SQL DROP TABLE;
    EXEC SQL DROP TABLE :tablename;(错误，不允许使用主变量)
)

数据控制语句

授权：EXEC SQL GRANT SELECT ON TABLE S TO U1;
连接数据库：EXEC SQL CONNECT :user IDENTIFIED BY :pass USING :tnasname;
出错处理：EXEC SQL WHEREVER SQLERROR do sql_error()

查询结构为单条记录SELECT语句
- 语法
```
EXEC SQL SELECT <目标表达式>,.. INTO <主变量1><指示变量1>,.. FROM <表名或视图名>,... WHERE/GROUP BY/  HAVING/ ORDER BY..
```
- 例子
```
EXEC SQL SELECT S#,SN INTO :sno,:sn FROM S WHERE S# =: GibenSno
```
- 注意
  - into,where和having语句字句中均可以使用主变量，需要事先申明。
  - 返回值某列为NULL时，系统会将指示变量赋值为-1，主变量不变
  - 查询结果满足条件的记录，则DBMS置sqlcode值为100，正常有结果为0
  - 如结果不止单条，程序出错，SQLCA中包含发挥信息

非CURRENT形式的UPDATE语句

EXEC SQL UPDATE SC SET GR = GR+:Raise WHERE C# = 'C01';
EXEC SQL UPDATE SC SET GR=:newgr WHERE S# = 'S001';
Grid = -1;
EXEC SQL UPDATE SC SET GR =:newgr :grid WHERE S# IN (SELECT S# FROM S WHERE SD = 'CS')

非CURRENT形式的DELETE语句

   EXEC SQL DELETE FROM SC WHERE S# IN (SELECT S# FROm S WHERE SN=:name)

INSERT语句

grid = -1;
EXEC SQL INSERT INTO SC VALUES(:sno, :cno, :gr, :grid);

(四)、使用游标的SQL语句

查询结果为多条记录的SELECT语句
- 游标在SELECT语句的集合和主语言的一次只能处理一条记录之间架起桥梁
- 游标步骤：
  - 说明游标：EXEC SQL DECLARE <游标名> CURSOR FOR <SElECT 语句>
  - 打开游标：执行相应的查询，把结果放进缓冲区，并把指针指向第一条记录EXEC SQL OPEN<游标名>
  - 读取当前记录并推进游标指针EXEC SQL FETCH <游标名> INTO <主变量><指示变量>
  - 关闭游标：EXEC SQL CLOSE <游标名>
CURRENT 形式的UPDATE和DELETE语句
- 操作步骤
- 说明游标：EXEC SQL DECLARE <游标名> CURSOR FOR <SELECT 查询> FOR UPDATE OF<列名>
- OPEN游标
- FETCH游标
- 检查是否要修改或删除
- 处理完毕CLOSE游标

(五)、动态SQL语句

在预编译时无法获得如下信息的必须使用动态SQL技术，未知信息可能包括：
- SQL语句正文
- 主变量个数
- 主变量数据类型
- SQL语句引用的数据对象

八、存储过程

语法

create procedure [owned.]procedure_name as<SQL_statements>

语言要素
- 语句块
```
begin <statement block> end
```
- 变量
  - 以@开始的为用户变量，以@@开始的为全局变量。定义变量：declare
- 条件控制
  - if else
- 循环控制
  - while break continue
- 顺序控制
  - label: goto label
- 返回值
  - return
- 打印信息
  - print
- 执行
  - excute
例子

CREATE PROCEDURE get_gr @sno varchar(10), @GR int OUTPUT 
AS
DECLARE @cno varchar(5)
BEGIN
   SELECT top 1 @cno=C#,@GR=GR FROM SC WHERE S# = @sno
   IF (@cno =’C01’)
      select @GR=@GR+1
   ELSE
     select @GR=@GR+2
END

执行：

    declare @gr int
    execute get_gr ‘s001’,@gr  output 
    select @gr 或print @gr

数据库基础-03