第 3 章 SQL 语言

第3章 SQL语言

第3章关系数据库语言SQL • 3.1 SQL简介 • 3.2 SQL的数据定义 • 3.3 SQL的数据查询 • 3.4 SQL数据查询中的限制和规定 • 3.5 数据更新 • 3.6 视图 • 3.7 嵌入式SQL 东华大学数学02 《数据库系统原理》

3.1 SQL简介 • 3.1.1 SQL的发展史 • 3.1.2 SQL数据库的体系结构 • 3.1.3 SQL的组成返回东华大学数学02 《数据库系统原理》

3.1.1 SQL的发展史 • 1970年，IBM研究中心提出关系模型。 • 1972年，IBM配制的查询语言称为SQUARE (Specifying Queries As Relational Expres sion ) 语言。 • 1974年，Boyce和Chamberlin把SQUARE修改为SEQUEL (Structured English QUEry Language)语言。后来SEQUEL简称为SQL (Structured Query Language)，即“结构式查询语言”，SQL的发音仍为“sequel”。现在SQL已经成为一个标准。东华大学数学02 《数据库系统原理》

SQL用户 用户1 用户2 用户3 用户4 View 视图1 视图2 Base table 基本表1 基本表2 基本表3 基本表4 存储文件1 存储文件2 存储文件3 存储文件4 Stored file 图3.1 SQL数据库的体系结构 3.1.2 SQL数据库的体系结构 (1) 东华大学数学02 《数据库系统原理》

3.1.2 SQL数据库的体系结构(2) • SQL的体系结构要点： 1、一个SQL模式是已命名的数据组，由表、授权、规则、约束等组成； 2、一个SQL表由行集构成，一行是列的序列，每列对应一个数据项； 3、表有3类：基本表、视图、导出表。基本表是实际存储在数据库中的表，视图是由若干基本表或其他视图构成的表的定义，而导出表是执行了查询时产生的表；东华大学数学02 《数据库系统原理》

3.1.2 SQL数据库的体系结构(3) 4、一个基本表可以跨一个或多个存储文件，一个存储文件也可以存放一个或多个基本表。每个存储文件与外部存储器上的一个物理文件对应； 5、用户可以用SQL语句对基本表和视图进行查询等操作。在用户看来是一样的，都是表； 6、SQL用户可以是应用程序，也可以是终端用户。SQL语句可嵌在主语言的程序中使用，也能作为独立的用户接口。东华大学数学02 《数据库系统原理》

3.1.3 SQL的组成(1) • 核心SQL主要有四个部分: (1) 数据定义语言，即SQL DDL，用于定义SQL模式、基本表、视图、索引等结构。 (2) 数据操纵语言，即SQL DML。数据操纵分成数据查询和数据更新两类。其中数据更新又分成插入、删除和修改三种操作。返回东华大学数学02 《数据库系统原理》

3.1.3 SQL的组成(2) (3) 嵌入式SQL语言的使用规定。这一部分内容涉及到SQL语句嵌入在宿主语言程序中的规则。 (4) 数据控制语言，即SQL DCL，这一部分包括对基本表和视图的授权、完整性规则的描述、事务控制等内容。东华大学数学02 《数据库系统原理》

3.2 SQL的数据定义 • 3.2.1 SQL模式的创建和撤消 • 3.2.2 基本数据类型 • 3.2.3 基本表的创建、修改和撤消 • 3.2.4 索引的创建和撤消返回东华大学数学02 《数据库系统原理》

3.2.1 SQL模式的创建和撤消(1) • 在SQL中，一个SQL模式定义为基本表的集合。 • 一个SQL模式由模式名和模式拥有者的用户名或帐号来确定，并包含模式中每一个元素(基本表、视图、索引等)的定义。 • 创建SQL模式，就是定义了一个存储空间。东华大学数学02 《数据库系统原理》

3.2.1 SQL模式的创建和撤消(2) • SQL模式的创建： CREATE SCHEMA<模式名>AUTHORIZATION<用户名> 如：CREATE SCHEMA ST_CO AUTHORIZATION WHQ • SQL模式的撤销： DROP SCHEMA <模式名>［CASCADE|RESTRICT］如：drop schema st_to cascade • CASCADE(级联式)方式：把SQL模式及其下属的基本表、视图等所有元素全部撤销； RESTRICT(约束式)方式：只有当SQL模式中没有任何下属元素时，才能撤销SQL模式，否则拒绝执行DROP语句。东华大学数学02 《数据库系统原理》

3.2.1 SQL模式的创建和撤消(3) • SQL模式即“数据库(DATABASE)” • 创建数据库： create database dbtest • 删除数据库： drop database dbtest 东华大学数学02 《数据库系统原理》

3.2.2 基本数据类型(1) • SQL提供的主要数据类型(也称为“域类型”)有： (1) 数值型 INTEGER 长整型(或INT) SMALLINT 短整型 REAL 浮点型 DOUBLE PRECISION 双精度浮点数 FLOAT(n) 浮点数，精度为n个数字 NUMERIC(p,d) 定点数[或DECIMAL(p,d)或 DEC(p,d)];有p位数,其中小数点后面有d位数字东华大学数学02 《数据库系统原理》

3.2.2 基本数据类型(2) (2) 字符串型 CHAR(n) 长度为n的长字符串 VARCHAR(N) 具有最大长度为n的变长字符串 (3) 位串型 BIT(n) 长度为n的二进制位串 BIT VARYING(n) 最大长度为n的变长位串 (4) 时间型 DATE 日期，包含年、月、日，形为YYYY-MM-DD TIME 时间，包含一日的时、分、秒，形为HH:MM:SS 东华大学数学02 《数据库系统原理》

3.2.2 基本数据类型(3) 东华大学数学02 《数据库系统原理》

3.2.3基本表的创建 (1) • 基本表的创建 CREATE TABLE <基本表名> ( <列名1 类型>， <列名2 类型>， …… <完整性约束1>， <完整性约束2>， …… ) 东华大学数学02 《数据库系统原理》

例3.1 基本表的创建 例3.1 基本表S（S#，SNAME，AGE，SEX）CREATE TABLE S ( S# CHAR(4), -- NOT NULL, SNAME CHAR(8) NOT NULL, AGE SMALLINT, SEX CHAR(1), --PRIMARY KEY(S#), CONSTRAINT PK_S#PRIMARY KEY(S#)) 定义约束名称，便于修改或删除东华大学数学02 《数据库系统原理》

例3.1 基本表的创建(2) CREATE TABLE T ( T# CHAR(4) NOT NULL, --PRIMARY KEY TNAME CHAR(8) NOT NULL, TITLE CHAR(10), CONSTRAINT PK_T# PRIMARY KEY(T#)) 东华大学数学02 《数据库系统原理》

例3.1 基本表的创建(3) CREATE TABLE C ( C# CHAR(4) NOT NULL,--PRIMARY KEY CNAME CHAR(10) NOT NULL, T# CHAR(4), CONSTRAINT PK_C# PRIMARY KEY(C#), CONSTRAINT FK_T# FOREIGN KEY(T#) REFERENCES T(T#) ) 东华大学数学02 《数据库系统原理》

例3.1 基本表的创建(4) CREATE TABLE SC ( S# CHAR(4), C# CHAR(4), SCORE SMALLINT, CONSTRAINT PK_SC PRIMARY KEY(S#,C#), CONSTRAINT FK_S# FOREIGN KEY(S#) REFERENCES S(S#), CONSTRAINT FK_C# FOREIGN KEY(C#) REFERENCES C(C#) ) 东华大学数学02 《数据库系统原理》

3.2.3 基本表的修改(1) • 基本表结构的修改 (1)增加新的列： ALTER TABLE<基本表名> ADD <列名><类型> 如：Alter table s add address varchar(20) 查看表结构：sp_help S (2) 删除原有列: ALTER TABLE<表名>DROP COLUMN<列名> 如：alter table s drop COLUMN address 东华大学数学02 《数据库系统原理》

3.2.3 基本表的修改 (2) • 下面的列不能除去，除非先删除约束： • 被复制列。 • 用在索引中的列。 • 用在 CHECK、FOREIGN KEY、UNIQUE 或 PRIMARY KEY 约束中的列。 • 有相关联的默认值(由 DEFAULT 关键字定义)的列，或绑定到默认对象的列。东华大学数学02 《数据库系统原理》

3.2.3 基本表的修改 (3) 如：alter table sc drop constraint fk_s# alter table sc drop constraint pk_sc alter table sc drop column s# (3) 修改原有列: Alter table <表名> modify <列名> <新属性> 如:alter table s alter column age int --sql server alter table s modify age int --oracle 东华大学数学02 《数据库系统原理》

3.2.3 基本表的撤消 • 基本表的撤消 DROP TABLE <基本表名> • DROP TABLE不能用于除去被FOREIGN KEY约束引用的表。必须先除去引用的FOREIGN KEY 约束。如: alter table C drop constraint fk_t# drop table T 东华大学数学02 《数据库系统原理》

3.2.4 索引的创建和撤消(1) • 主键即“唯一索引”，不允许存在主键值或索引值相同的两行。 • 索引的创建 CREATE [UNIQUE] INDEX <索引名> ON <基本表名>(列名表) 在创建唯一索引时，如果数据已存在，系统会检查是否有重复值。如果存在，则 CREATE INDEX失败，并返回错误信息，唯一索引东华大学数学02 《数据库系统原理》

3.2.4 索引的创建和撤消(2) • 索引的撤消 DROP INDEX<索引名> 东华大学数学02 《数据库系统原理》

3.3 SQL的数据查询 • 3.3.1 SELECT查询语句的基本结构 • 3.3.2SELECT语句的使用技术 • 3.3.3聚合函数 • 3.3.4SELECT语句完整的语法返回东华大学数学02 《数据库系统原理》

3.3.1 SELECT语句的基本结构 (1) • SELECT—FROM—WHERE句型针对表达式πA1,…,An(σF(R1×…×Rm))的句型： SELECT A1，…，An FROM R1，…，Rm WHERE F WHERE子句中的条件表达式F要比关系代数中公式更灵活。东华大学数学02 《数据库系统原理》

3.3.1 SELECT语句的基本结构 (2) WHERE子句的条件表达式F中可使用的运算符： • 算术比较运算符：<,<=,>,>=,=,<>或!=; • 逻辑运算符：AND,OR,NOT • 集合成员资格运算符：IN,NOTIN • 谓词：EXISTS,ALL,SOME,UNIQUE; • 聚合函数：AVG(平均值),MIN(最小值), MAX(最大值),SUM(和),COUNT(计数) 东华大学数学02 《数据库系统原理》

3.3.1 SELECT语句的基本结构 (3) • EXISTS: 指定一个子查询，检测行的存在。语法: EXISTS subquery 如果子查询包含行，则返回 TRUE。 • UNIQUE约束确保在非主键列中不输入重复值 • SELECT语句的查询结果之间还可以进行集合的并(UNION)、交(INTERSECT)、差(EXCEPT)操作。 • SELECT语句能表达所有的关系代数表达式。东华大学数学02 《数据库系统原理》

3.3.2 SELECT语句的使用技术 (1) • SELECT句型使用时有3种写法：连接查询、嵌套查询和带存在量词的嵌套查询。例3.8 教学数据中有四个基本表(关系)：教师关系 T(T#，TNAME，TITLE) 课程关系 C(C#，CNAME，T#) 学生关系 S(S#，SNAME，AGE，SEX) 选课关系 SC(S#，C#，SCORE) 东华大学数学02 《数据库系统原理》

例3.8 插入数据 insert into s values('S1','BAO',20,'M') insert into s values('S2', 'GU',21,'M');insert into s values('S3','AN',22,'F'); insert into s values('S4','LI',19,'F') insert into t values('T1', 'LIU', 'B') insert into t values('T2', 'HU', 'A'); insert into t values('T3', 'GAO', 'A') insert into t values('T4', 'JIA', 'C') insert into c values('C1', 'DB', 'T2'); insert into c values('C2', 'OS', 'T2') insert into c values('C3', 'DS', 'T3'); insert into c values('C4', 'MIS', 'T4') insert into sc values('S1','C1', 80); insert into sc values('S1','C2', null) insert into sc values('S1','C3', 96); insert into sc values('S1','C4', 77) insert into sc values('S2','C1', 66); insert into sc values('S2','C2', 89) insert into sc values('S3','C2', 76); insert into sc values('S4','C2', 60) insert into sc values('S4','C4', 90) 东华大学数学02 《数据库系统原理》

例3.8 用SELECT语句表达查询(1) ① 检索学习课程号为C2的学生学号与成绩。 SELECT S#,SCORE FROM SC WHERE C#='C2' ② 检索学习课程号为C2的学生学号与姓名。 select s.s#,sname from sc,s Where sc.s#=s.s# and c#='C2' 东华大学数学02 《数据库系统原理》

例3.8 用SELECT语句表达查询(2) • 嵌套查询： select s#,sname from s where s# in (select s# from sc where c#='c2') • 使用存在量词的查询： select s#,sname from s where exists (select * from sc where sc.s#=s.s# and c#='c2') 东华大学数学02 《数据库系统原理》

例3.8 用SELECT语句表达查询(3) ③ 检索至少选修LIU老师所授课程中的一门课程的学生学号和姓名。 select s.s#,sname from s,c,sc,t where s.s#=sc.s# and c.c#=sc.c# and c.t#=t.t# and t.tname='liu' 东华大学数学02 《数据库系统原理》

例3.8 用SELECT语句表达查询(4) ④ 检索选修课程号为C2或C4的学生学号。 select s# from sc where c# = 'c2' or c# = 'c4' ⑤ 检索至少选修课程号为C2和C4的学生学号。 select a.s# from sc a, sc b where a.s# = b.s# and a.c# = 'c2' and b.c# = 'c4' 东华大学数学02 《数据库系统原理》

例3.8 用SELECT语句表达查询(5) ⑥检索不学C2课程的学生姓名与年龄 select sname,age from s where s# not in ( select s# from sc where c# = 'c2') 东华大学数学02 《数据库系统原理》

例3.8 用SELECT语句表达查询(6) ⑦检索学习全部课程的学生姓名 select sname from s where (select count(distinct c#) from sc where sc.s#=s.s#) = (select count(distinct c#) from c) 东华大学数学02 《数据库系统原理》

例3.8 用SELECT语句表达查询(7) ⑧检索所学课程包含学生S3所学课程的学号设：A={S3所选课程}， B={某Si所选课程} 则：A  B 即：xA  xB 不存在a，使得 aA且aB not exists (aA且aB) 东华大学数学02 《数据库系统原理》

例3.8 用SELECT语句表达查询(8) not exists (aA且aB) select distinct B.s# from sc B where not exists (select * from sc A where A.s#='s3' and A.c# not in (select c# from sc where s#=B.s#)) 用not in实现了减法操作 and not exists (select * from sc where s# = B.s# and c# = A.c#)) 东华大学数学02 《数据库系统原理》

3.3.2 SELECT语句的使用技术(3) • SELECT语句的图示形式例3.8中的第二个查询语句的图示形式如下：东华大学数学02 《数据库系统原理》

3.3.3 聚合函数(1) • COUNT(*) 计算元组的个数 • COUNT(列名) 对一列中的值计算个数 • SUM(列名) 求某一列值的总和(此列的值必须是数值型) • AVG(列名) 求某一列值的平均值(此列的值必须是数值型) • MAX(列名) 求某一列值的最大值 • MIN(列名) 求某一列值的最小值返回东华大学数学02 《数据库系统原理》

3.3.3 聚合函数(2) (1)求男生总人数和平均年龄 select count(s#),avg(age) from s where sex='m' alter table s alter column age float; select count(s#) '男生人数',avg(age) 平均年龄from s where sex='m' 年龄取整计算结果与原字段类型一致指定列名东华大学数学02 《数据库系统原理》

3.3.3 聚合函数(3) (2)统计选修了课程的学生人数 select count(s#) from sc select count(distinct s#) 选课人数 from sc 使参与运算的值不重复东华大学数学02 《数据库系统原理》

3.3.4 SELECT语句完整的句法 (1) • SELECT语句完整的句法如下： SELECT 目标表的列名或列表达式序列 FROM 基本表名和（或）视图序列 [ WHERE 行条件表达式 ] --可选 [ GROUP BY 列名序列 [ HAVING 组条件表达式 ]] [ ORDER BY 列名[ ASC|DESC ]，… ] 东华大学数学02 《数据库系统原理》

3.3.4 SELECT语句完整的句法(2) • 整个语句的执行过程如下： (1)读取FROM子句中基本表、视图的数据，执行笛卡尔积操作。 (2)选取满足WHERE子句中给出的条件表达式的元组。 (3)按GROUP子句中指定列的值分组，同时提取满足HAVING子句中组条件表达式的那些组。东华大学数学02 《数据库系统原理》

3.3.4 SELECT语句完整的句法(3) (4)按SELECT子句中给出的列名或列表达式求值输出。 (5)ORDER子句对输出的目标表进行排序，按附加说明ASC升序排列，或按DESC降序排列。东华大学数学02 《数据库系统原理》

3.3.4 SELECT语句完整的句法 (4) • 例3.11 ①统计每一年龄选修课程的学生人数 SELECT AGE,COUNT(DISTINCT S.S#)人数 FROM S,SC WHERE S.S#=SC.S# GROUP BY AGE; 东华大学数学02 《数据库系统原理》

3.3.4 SELECT语句完整的句法 (6) ②求基本表S中男同学的每一年龄组(超过50人)有多少人？要求查询结果按人数升序排列，人数相同按年龄降序排列。 SELECT AGE,COUNT(S#) 人数 FROM S WHERE SEX='M' GROUP BY AGE HAVING COUNT(*)> 50 ORDER BY 人数, AGE DESC; 东华大学数学02 《数据库系统原理》

第 3 章 SQL 语言