数据库系统（二）SQL语言

第三章：SQL语言

第一节：SQL概述

一、SQL语言：概念

1.SQL语言：概念

（1）SQL语言是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理RDBS。

RDBS：关系型数据库系统=>二维表存数据。

（2）SQL语言（结构化查询语言）是“非过程化”编程语言，允许用户在高层数据结构上工作。不要求用户指定于了解具体数据存放方法，所以不同底层结构的DBS可以使用相同的SQL语言作为数据输入与管理的接口。

“非过程化”编程语言：不需要路径与存取方法，即路径透明。

（3）SQL语言语句可以嵌套，使得灵活性大、功能强大。

2.语言的特点

（1）综合统一。

（2）高度非过程化：存取路径与SQL语言操作过程由系统自动完成，提高数据独立性。

（3）面向集合的操作方式。操作对象、查找结果可以说元组的集合；一次插入、删除、更新操作的对象可以是元组的集合。

（4）同一种语法结构提供两种使用方式，语言简洁、易学易用。

·自含式语言：能够独立地用于联机交互的使用方式。

·嵌入式语言：能够嵌入到高级语言如c#中，供程序猿设计程序时使用。

（5）SQL语言支持RDBS的三级模式结构（索引、表、视图），用户用SQL语言对基本表和视图进行操作。

二、SQL语言：分类

1.数据定义语言（DDL）

其语句包括动词CREATE、ALTER和DROP。在DB中创建新表、修改表和删除表；为表加入索引等。

2.数据操作语言（DML）

·数据查询语言：SELECT语句

·数据修改语句：INSERT、UPDATE和DELETE。

3.数据控制语言（DCL）

·事务处理语言：BEGIN TRANSACTION、COMMIT和ROLLBACK。用于处理并发。

·授权语句：通过GRANT（授权）/REVOKE（撤权）获得许可，确定用户、角色对数据对象的访问权限。用于处理安全。

三、SQL语言：处理过程

1.DDL语句处理过程

（1）语言处理层首先要对DDL语句进行语法检查、语义检查和用户权限检查。

·语法检查：检查SQL语言是否正确。

·语义检查和权限检查：DBMS读取数据字典，检查是否存在与该语句中的表、视图、索引等要创建的对象相同的对象名，检查该用户是否具有创建数据库对象的权限。

（2）把DDL语句翻译成内部表示，将其存储在数据字典中。

内部表：关系名、建立者、属性个数、记录长度等。

2.DML语句处理过程

（1）先对DML语句进行词法分析和语法分析，并将外部关系（表）名、属性名转换为内部名，通过词法分析和语法分析后生成语法分析树。=>提高查询效率。

（2）根据数据字典中的内容进行查询检查，包括审核用户的存取权限和完整性检查。

（3）对查询进行优化，包括代数优化和存取路径优化。

（4）把选中的查询执行方案描述出来。

第二节：数据定义

一、表语句：定义

1.定义模式

（1）定义表（模式）：创建表、删除表、修改表定义。

（2）定义视图（外模式）：创建视图、删除视图、间接修改视图定义（删除+创建）。

（3）定义索引（内模式）：创建索引、删除索引、间接修改索引定义（删除+创建）。

2.表定义语法

语法格式

CREATE TABLE<表名>
(<列名><数据类型>[<列级完整性约束>]
[,<列名><数据类型>[<列级完整性约束>]]...
[,<表级完整性约束>]);

3.定义表时添加约束

（1）建表的同时还可以定义相关完整性约束，存于系统数据字典中，当user操作表中data是，RDBMS自动检查该操作是否违背这些完整性约束条件。

（2）如果完整性约束条件涉及多个属性列，则约束须建立表级约束，否则既可定义表级，也可定义列级。

例如：主键为双属性时：PRIMARY KEY（SNO，CNO）

（3）常用完整性约束

·实体完整性（主键）约束:PRIMARY KEY
·参照完整性（外键）约束:FOREIGN KEY
·唯一性约束:UNIQUE
·非空约束:NOT NULL
·取值约束:CHECK

二、表语句：修改

修改基本表语法

语法格式

ALTER TABLE<表名>
[ADD<新列名><数据类型>[完整性约束]]
[DROP<完整性约束名>]
[MODIFY<列名><数据类型>];

·ADD子句：用于增加新的列、新的列级完整性约束条件、新的表级完整性约束条件。

ALTER TABLE STUDENT ADD SCOME DATE;

·DROP子句：用于删除表中列、列级完整性约束。

ALTER TABLE STUDENT DROP UNIQUE(SNAME);

·MODIFY子句：用于修改原有的列定义，包括修改列名和数据类型。

ALTER TABLE STUDENT MODIFY SAGE SMALLINT;

三、表语句：删除

删除基本语法

DROP TABLE <表名>;

（1）基本表定义一旦被删除，表上的数据、索引、触发器等对象也会一并删除。

（2）表上的视图仍然保留，但无法引用。

（3）如删除的表被其他表引用，则这些表也可能被删除。

四、数据类型

不同DBS支持的数据类型不完全相同

1.IBM DB2 SQL支持的数据类型

·SMALLINT:半字长二进制整数
·INTEGER或INT:全字长二进制整数
·DECIMAL(p[,q]):压缩十进制数，共p位，其中小数点后有q位
·FLOAT:双字长浮点数
·CHARTER(n)或CHAR(n):长度为n的定长字符串
·VARCHAR(n):最大长度为n的变长字符串
·GRAPHIC(n):长度为n的定长图形字符串
·VARGRAPHIC(n):最大长度为n的变长图形字符串
·DATE:日期型，格式为YYYY-MM-DD
·TIME:时间型，格式为HH.MM.SS
·TIMESTAMP:日期加时间

2.ORACLE SQL支持的数据类型

·NUMBER(p[,q])
·CHAR(n)
·VARCHAR2(n)
·LONG
·RAW或LONGRAW
·DATE

五、索引：创建和删除

1.创建索引语法

语句格式

CREATE [UNIQUE] [CLUSTER] INDEX<索引名>
ON <表名>(<列名>[<次序>][,<列名>[<次序>]]...);

·<表名>：指定要建索引的基本表的名字，索引库建立在该表的一列或多列上，各列名之间用逗号分隔。

·<次序>：指定索引值的排列次序，升序使用ASC关键字，降序使用DESC关键字。缺省值为ASC关键字。

·UNIQUE：表明此索引的每一个索引值只对应唯一的数据记录。

·CLUSTER：表示要建立的索引是聚簇索引。

2.删除索引语法

DROP INDEX <索引名>;

六、数据字典

（1）是RDBMS内部的一组系统表，它记录了数据库中所有的定义信息，包括关系模式定义、视图定义、索引定义、完整性约束定义、各类user对数据库操作权限、统计信息等。

（2）DBMS在执行DDL是，实际上就是在更新数据字典。

（3）在进行查询优化和查询处理时，数据字典中的信息是重要依据。

（4）DBMS中的数据字典，应包含三级模式、两级映象、数据安全性、数据完整性等方面的信息。

第三节：单表查询

一、查询语法

结果在内存表（虚表）中。

1.查询语句格式

SELECT [ALL|DISTINCT]<目标列表达式>[<别名>] [,<目标列表达式>[<别名>]]...
FROM <表名或视图名>[<别名>][,<表名或视图名>[<别名>]]...
[WHERE <条件表达式>]
[GROUP BY <列名>[,<列名>]...
[HAVING <条件表达式>]]
[ORDER BY <列名>[,<列名>]...[ASC|DESC]];

·SELECT子句：指定要显示的属性列。

·FROM子句：指定查询对象（表或视图）。

·WHERE子句：指定查询条件。

·GROUP BY子句：对查询结果按指定的列分组，该属性列值相等的元组为一个组。通常会在每组中使用统计函数。

·HAVING短语：筛选出满足指定条件的组。

·ORDER BY 子句：对查询结果按指定列值升序或降序。

2.语句作用

用来选择表中的若干列、若干行，对查询结果排序、使用统计函数、对查询结果分组。

二、查询列

查询操作属于投影运算，不消除重复行，变化方式主要表现在SELECT子句的<目标表达式>。可以查询指定列、全部列（目标表达式为*）、经过计算的值。

1.选择若干列

·方法是在SELECT子句的<目标表达式>中指定要查询的属性。

·<目标表达式>中各列先后顺序可不同于表中逻辑顺序。

2.查询全部列

·在SELECT后面列出所有列名。

·当顺序与表中顺序一致时，可以用*。

3.查询经过计算的值

SELECT子句的<目标表达式>为表达式，可以是算术表达式、字符串常量、函数列、别名等。

三、查询元组

1.选择表中若干元组，并取消重复行

在SELECT子句中使用DISTINCT短语，查询会取消重复行，查询满足条件的元组。DISTINCT短语的作用范围是所有目标列。

2.查询满足条件的元组

通过WHERE子句实现。

查询条件	谓词
比较	=,>,<,>=,<=,!=,<>,!>,!<;NOT+上述运算符
确定范围	BETWEEN AND,NOT BETWEEN AND
确定集合	IN,NOT IN
字符匹配	LIKE,NOT LIKE
空值	IS NULL,IS NOT NULL
多重条件	AND,OR

（1）比较查询：

SELECT SNAME,SAGE FROM STUDENT WHERE NOT SAGE<20;

（2）范围查询 ：

SELECT SNAME,SAGE FROM STUDENT WHERE SAGE BETWEEN 20 AND 23;

 （3）确定集合查询：

SELECT SNAME,SAGE FROM STUDENT WHERE SDEPT IN ('IS','MA','CS');

（4）模糊查询：

·<匹配串>用来指定匹配模版，匹配模版指固定字符串或含通配符字符串。

·当匹配模版为固定字符串是，用=取代LIKE，用!=或<>取代NOT LIKE。

·通配符：%：代表任意长度（可为0）字符串

                _：代表任意单个字符

SELECT * FROM STUDENT WHERE SNO LIKE '95001';
SELECT SNAME FROM STUDENT WHERE SNAME LIKE '刘%';
SELECT SNO FROM STUDENT WHERE SNAME LIKE '王_';

（5）空值查询：

IS NULL不能用= NULL代替

SELECT SNO,CNO FROM SC WHERE GRADE IS NULL;

（6）多重条件查询：

用逻辑运算符AND或OR来连接多个查询条件，AND的优先级高于OR，可以用括号改变优先级。

SELECT SNAME,SAGE FROM STUDENT WHERE SDEPT='IS' AND SAGE<23;

四、查询排序

对查询结果排序

（1）使用ORDER BY子句，可以按一个或多个列排序。

（2）升序用ASC，降序用DESC，缺省值为ASC。

（3）当排序列含空值时，若使用ASC，则空值最先显示；同理，DESC空值最后。

示例：查询全体学生情况，查询结果按系号升序排列，同系学生按年龄降序排列。

SELECT * FROM STUDENT ORDER BY SDEPT,SAGE DESC;

五、统计查询

统计函数：

（1）计数:COUNT([DISTINCT|ALL]*);COUNT([DISTINCT|ALL]<列名>)
（2）计算总和:SUM([DISTINCT|ALL]<列名>)
（3）计算平均值:AVG([DISTINCT|ALL]<列名>)
（4）求最大值:MAX([DISTINCT|ALL]<列名>)
（5）求最小值:MIN([DISTINCT|ALL]<列名>)

·DISTINCT短语是在计算是取消列中重复值。

·ALL短语是不取消重复值，ALL为缺省值。

·统计函数单列不统计空值，故用*不用具体列防止因空值少统计。例如GRADE列有十个值，其中三个为NULL值，COUNT(GRADE)结果为7，COUNT(*)结果为10。

六、分组查询

1.分组查询的用途

细化统计函数的作用对象，若对查询结果未分组，统计函数将作用于整个查询结果；若对查询结果分组后，统计结果将分别作用于每个组。

2.分组查询方法

使用GROUP BY子句分句，分组方法是按指定的一列或多列值分组，值相等的为一组，使用GROUP BY子句后面的SELECT的列表列名中只能出现分组属性和统计函数。

3.筛选输出

只有满足HAVING短语指定条件的组才输出。HAVING子句作用于组，WHERE子句作用于基表或视图。

SELECT SNO, COUNT(*) FROM SC
WHERE GRADE>=90        /*分组前*/
GROUP BY SNO
HAVING COUNT(*)>=3;    /*分组后*/

第四节：连接查询

一、连接查询：内连接

1.连接查询概念

（1）同时涉及多个表的查询称为连接查询，用来连接两个表的条件称为连接条件或连接谓词。连接字段是连接谓词中的列名称。

（2）连接条件中的各连接字段必须是可比，但不必是相同的。

2.常用的连接查询类型

常用的联接查询类型：广义笛卡尔积、等值与非等值连接查询、自连接查询、外连接查询、扶汉条件连接查询。

3.广义笛卡尔积

不带谓词的连接，很少使用。

SELECT STUDENT.*,SC.* FROM STUDENT,SC;

4.等值连接

（1）连接运算符为“=”的连接操作。条件为[<表名1>.]<列名1>=[<表名2>.]<列名2>。

（2）任何子句中引用表1和表2中同名属性时，都必须加表名前缀。引用唯一属性名时可以加也可以省略表名前缀。

SELECT STUDENT.*,SC.* FROM STUDENT,SC
WHERE STUDENT.SNO=SC.SNO;

SELECT STUDENT.*,SC.* FROM STUDENT JOIN SC
ON  STUDENT.SNO=SC.SNO

5.自然连接

等值连接特殊情况，去掉目标中重复属性列。

SELECT STUDENT.SNO,SNAME,SSEX,SAGE,SDEPT,CNO,GRADE
FROM STUDENT,SC
WHERE STUDENT.SNO=SC.SNO;

6.非等值连接

（1）连接运算符不为“=”的连接操作。

（2）条件为[<表名1>.]<列名1><比较运算符>[<表名2>.]<列名2>。其中比较运算符：>、<、>=、<=、!=；或者 [<表名1>.]<列名1> BETWEEN [<表名2>.]<列名2> AND [<表名2>.]<列名3>。

二、连接查询：自连接

一个表与自己进行连接，称为表的自连接。需要给表起别名以示区别。由于所有属性都有同名属性，因此必须使用同名前缀。

SELECT FIRST.CNO,SECOND.CPNO
FROM COURSE FIRST,COURSE SECOND
WHERE FIRST.SNO=SECOND.SNO;

三、连接查询：外连接

1.外连接的概念

（1）普通连接（内连接）操作只输出满足连接条件的元组。外连接操作以指定表为连接主体，将主题表中不满足连接条件的元组一并输出。

（2）外连接操作要在表名后面指定非主体表，非主体表有一“万能”的虚行，全由空值组成。虚行可以与主题表中所有不满足条件的元组连接，由于虚行列全NULL，所以连接结果中，非主体表属性值全NULL。

2.外连接类型

（1）左外连接的外连接符出现在连接条件左边。结果是左边关系中所有元素。LEFT JOIN

（2）右外连接的外连接符出现在连接条件右边。结果是右边关系中所有元素。RIGHT JOIN

（3）全外连接。结果是两个关系中所有元素。

SELECT STUDENT.SNO,SNAME,SSEX,SAGE,SDEPT
FROM STUDENT LEFT (OUTER) JOIN SC
WHERE STUDENT.SNO=SC.SNO;

四、连接查询：复合条件连接

复合条件连接

（1）WHERE子句中含多个连接条件时，称为复合条件连接，类型有：两表按多属性连接、自身按多属性连接、多表连接。

（2）执行多表连接时通常先进行两表连接操作，再将连接结果与第三个表连接。

第五节：子查询

一、子查询：概念

一个SELECT—FROM—WHERE语句称为一个查询块，将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询。上层块称为外层查询或父查询，下层查询块称为内层查询或子查询。

·相关子查询：子查询条件依赖父查询

·不相关子查询：子查询条件不依赖父查询

SELECT SNAME
  FROM STUDENT
  WHERE SNO IN
    (SELECT SNO
       FROM SC
       WHERE CNO='2');

二、子查询：IN子查询

若在嵌套查询中子查询结果是一个集合，可使用谓词IN。

SELECT SNAME
  FROM STUDENT
  WHERE SNO IN
    (SELECT SNO
       FROM SC
       WHERE CNO='2');

三、子查询：带比较运算符的子查询

1.比较运算符的使用场景

能确切知道内层查询返回单值时，可用比较运算符(>、<、=、>=、<=、!=或<>)与ANY或ALL谓词配合使用。

2.常用的比较运算符

>ANY:大于子查询结果中某个值。
>ALL:大于子查询结果中所有值。
<ANY:小于子查询结果中某个值。
<ALL:小于子查询结果中所有值。
>=ANY:大于等于子查询结果中某个值。
>=ALL:大于等于子查询结果中所有值。
<=ANY:小于等于子查询结果中某个值。
<=ALL:小于等于子查询结果中所有值。
=ANY:等于子查询结果中某个值。
=ALL:等于子查询结果中所有值（通常无实际意义）。
!=(或<>)ANY:不等于子查询结果中某个值。
!=(或<>)ALL:不等于子查询结果中任何一个值。

3.示例

SELECT SNO,SNAME,SDEPT
FROM STUDENT
WHERE SDEPT=(
       SELECT SNO
       FROM STUDENT
       WHERE SNAME='张三');

SELECT SNO,SNAME,SDEPT
FROM STUDENT
WHERE SAGE < ANY
      (SELECT SAGE
       FROM STUDENT
       WHERE SDEPT='IS')
AND SDEPT<>'IS';    /*这是父查询块中的条件*/

--用统计函数实现
SELECT SNO,SNAME,SDEPT
FROM STUDENT
WHERE SAGE<
      (SELECT MAX(SAGE)
       FROM STUDENT
       WHERE SDEPT='IS')
AND SDEPT<>'IS';

四、子查询：EXISTS子查询

（1）带有EXISTS谓词的子查询不返回任何数据，只会产生布尔值TRUE或FALSE。若内层查询结果非空返回真值，若空返回假值。

（2）由EXISTS引出的子查询，其目标列表式通常用 * ，因为带EXISTS的子查询返回布尔值，给出列名无实际意义。

SELECT SNAME
FROM STUDENT
WHERE NOT EXISTS
      (SELECT *
       FROM SC
       WHERE SNO=STUDENT.SNO
       AND CNO='1');

第六节：集合查询

一、集合查询

一般商用数据库支持的集合操作：并操作（UNION），交操作（INTERSECT），差操作（EXCEPT）。

参加集合操作的各查询结果列数必须相同（同目），对应项数据类型也必须相同（同域）。

非标准，可移植性差。

二、并操作

基本语法是：<查询块>UNION<查询块>。

SELECT * FROM STUDENT WHERE SDEPT='CS'
UNION
SELECT * FROM STUDENT WHERE SAGE<=19

三、交/差操作

标准SQL中不提供，但可用其他改变逻辑方式间接实现。

SELECT * FROM STUDENT WHERE SDEPT='CS'
INTERSECT/EXCEPT
SELECT * FROM STUDENT WHERE SAGE<=19

第七节：数据更新

一、插入单个元组

有两种插入数据方式：插入单个元组和插入子查询结果，后者可以插入多个元组。

1.语法格式

INSERT INTO <表名>[(<属性列1>)[,<属性列2>...)]
VALUES(<常量1>[，<常量2>]...)

（1）该语句功能是将新元组插入指定表中。

（2）INTO子句：指定要插入的表名及属性列，顺序可与表定义中的顺序不一致，若无属性列，表示插入完整元组，且顺序与表定义中一致；指定部分属性列，插入的元组在其余属性列上面取空值。

（3）VALUES子句：提供值须与INTO子句匹配，包括值的个数与类型。常量除int都加引号。

2.与约束的关系

DBMS在执行插入语句时汇锦城所插元组是否破坏表上已定义的完整性规则：实体完整性、参照完整性、用户定义完整性。

二、插入子查询

1.语法格式

INSERT INTO <表名>[(<属性列1>)[,<属性列2>...)] 子查询

（1）功能是将子查询插入指定表中。

（2）INTO子句：用来指定要插入数据的表名及属性列，顺序可与表定义中的顺序不一致，若无属性列，表示插入完整元组，且顺序与表定义中一致；指定部分属性列，插入的元组在其余属性列上面取空值。

（3）子查询语句：SELECT子句目标列须与INTO子句匹配，包括值的个数与类型。

2.与约束的关系

DBMS在执行插入语句时汇锦城所插元组是否破坏表上已定义的完整性规则：实体完整性、参照完整性、用户定义完整性。

--第一步：建表
CREATE TABLE DEPTAGE(SDEPT CHAR(15),
                     AVGAGE SMALLINT);

--第二步：插入数据
INSERT INTO DEPTAGE(SDEPT, AVGAGE)
        SELECT SDEPT,AVG(SAGE)
        FROM STUDENT
        GROUP BY SDEPT;

三、复制表查询

1.语法格式

SELECT * INTO 新表 FROM 旧表

2.语句功能

创+导

（1）创建一个新表（执行语句时即创建），若SELECT后面列是*，则新旧表结构相同。

（2）从旧表中选取数据，然后插入新表中。

（3）常用于备份表或存档记录。

SELECT * INTO STUDENT FROM PEOPLE;

SELECT NAME,SEX INTO STUDENT FROM PEOPLE;

四、更新语句

1.语法格式

UPDATE <表名>
SET<列名>=<表达式>[,<列名>=<表达式>]...
[WHERE<条件>]

（1）SET子句：指定修改方式，包括要修改的列、修改后取值。

（2）WHERE子句：指定要修改的元组，缺省值表示修改所有元组。

2.与约束的关系

DBMS在执行插入语句时汇锦城所插元组是否破坏表上已定义的完整性规则：实体完整性、参照完整性、用户定义完整性。

UPDATE STUDENT SET SAGE=SAGE+1

五、删除语句：DELETE

1.语法格式

DELETE FROM <表名>[WHERE<条件>]

（1）删除指定表中满足WHERE子句条件的元组。删行，不要加列名。

（2）WHERE子句指定要删除的元组，缺省值表示删除所有元组。

2.与约束的关系

DBMS在执行插入语句时汇锦城所插元组是否破坏表上已定义的参照完整性。

DELETE FROM SC (WHERE子句)

六、删除语句：TRUNCATE TABLE

1.语法格式

TRUNCATE TABLE 表名

（1）删除表中所有行，但不删除表定义。

（2）执行TRUNCATE语句后，对象的模式和所有索引仍然存在，直到执行DROP语句为止。模式定义和约束保持不变，触发器和权限仍然有效。

2.TRUNCATE TABLE语句和DELETE语句的比较

（1）TRUNCATE TABLE在功能上和不带WHERE子句的DELETE语句相同：二者均删除表中的全部行。但TRUNCATE TABLE比DELETE速度快，且使用的系统和事务日志资源少。

（2）DELETE语句每行删除一次，并在事务日志中为所删除的每行记录一页。TRUNCATE TABLE通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放，行的删除操作不记入事务日志。

（3）TRUNCATE TABLE删除表中所有行，但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值，请改用DELETE。如果要删除表定义及其数据，请使用DROP TABLE语句。

（4）对于由外键约束引用的表，不能使用TRUNCATE TABLE，而应使用不带WHERE子句的DELETE语句。由于TRUNCATE TABLE不记录日志，所以不激活触发器。

（5）TRUNCATE TABLE不能用于参与了索引视图的表。

（6）对于用TRUNCATE TABLE删除数据的白哦上增加数据时，要使用UPDATE STATISTICS来维护索引信息。

（7）如果有ROLLBACK语句，DELETE操作将被撤销，但TRUNCATE TABLE不会撤销。

竖