关系数据库标准语言 SQL

关系数据库标准语言SQL

要点 • SQL语言简介 • SQL语言及其使用方式 • 独立式SQL • 嵌入式SQL

3.1 SQL概述 • Structured Query Language, 1974年提出 • 关系数据库的国际标准语言：大多数数据库均用SQL作为共同的数据存取语言和标准接口，实现不同数据库系统之间的互操作 • 目前仍被不断扩充 • 介于关系代数和关系演算之间，三者可相互转换

SQL的特点 • 综合统一： • 集DDL、DML、DCL功能于一体，可独立完成数据库生命周期中的全部活动，语言风格统一 • 高度非过程化： • 用户只需提出“做什么”，而无需指明“怎么做” • 面向集合的操作方式： • 操作对象、查询结果、更新数据均可以是元组的集合 • 提供两种使用方式： • 自含式、嵌入式 • 语言简捷，易学易用

SQL 外模式视图2 视图1 模式基本表2 基本表3 基本表4 基本表1 存储文件1 存储文件2 内模式 SQL语言的基本概念 • SQL支持关系数据库的三级模式结构 • 存储文件：组成关系数据库的内模式，对用户透明 • 基表(Base Table)：组成关系数据库的模式，一个关系对应一个基表，一或多个基表对应一个存储文件 • 视图(View)：组成关系数据库的外模式，从一个或多个基表中导出，不独立存储在数据库中

3.2 数据定义 • SQL的数据定义语言(DDL)

CREATE TABLE <表名> ( <列名><数据类型>[列级完整性约束] [, <列名><数据类型>[列级完整性约束]]… [, <表级完整性约束>] ); 定义基表 • 数据类型： • 不同的数据库系统有自己的数据类型规定，但一般都包括INTEGER, FLOAT, CHAR(n), VARCHAR(n)等 • 完整性约束条件 • 列级完整性约束条件：涉及表的某一列 • 如对数据类型的约束，对数据格式的约束，对取值范围或集合的约束，对空值NULL(空值，不知道或不能用的值)的约束，对取值的唯一性UNIQUE约束，对列的排序说明等 • 表级完整性约束条件：涉及表的一个或多个列 • 如订货关系中规定发货量不得超过订货量

SQL支持的数据类型 • SMALLINT 半字长的整数 • INT 全字长的整数 • FLOAT浮点数 • CHAR(n)长度为n的定长字符串 • VARCHAR(n) 最大长度为n的变长字符串 • DEC(p,q) 十进制数，共p位，小数点后有q位 • DATE 日期型, 格式YYYY-MM-DD • TIME 时间型，格式为HH.MM.SS • TIMESTAMP日-时戳（日期加时间）

完整性约束 • CREATE TABLE的完整性约束 • NOT NULL 属性值禁止为空 • UNIQUE 取值唯一 • PRIMARY KEY (A1, ..., An) 主码--若干属性列 • CHECK(P) P为条件表达式

例：建立学生管理的相关基表 CREATE TABLE Student (Sno CHAR(5) NOT NULL UNIQUE, Sname CHAR(20) UNIQUE, Ssex CHAR(1), Sage INTEGER, Sdept CHAR(15)); CREATE TABLE Course (Cno CHAR(8) NOT NULL UNIQUE, Cname CHAR(20), Cpno INTEGER, Ccredit INTEGER); CREATE TABLE SC (Sno CHAR(5) NOT NULL UNIQUE, Cno CHAR(8) NOT NULL UNIQUE, Grade INTEGER);

例：建立图书管理的相关基表 CREATE TABLE Borrows (CardNo INTEGER NOT NULL UNIQUE, Name CHAR(10), Dept CHAR(20)); CREATE TABLE Books (BookNo INTEGER NOT NULL UNIQUE, SortNo CHAR(10), Title CHAR(30), Author CHAR(12), Price FLOAT, LoanNo INTEGER); CREATE TABLE Loans (CardNo INTEGER NOT NULL UNIQUE, BookNo INTEGER NOT NULL UNIQUE, Title CHAR(30), Date CHAR(10));

例：完整性约束 CREATE TABLE branch(branch-name char(15) not null,branch-city char(30),assets integer)； CREATE TABLE branch(branch-name char(15),branch-city char(30),assets Integer,PRIMARY KEY (branch-name),CHECK (assets >= 0))；

修改基表 ALTER TABLE <表名> [ADD <列名> <数据类型> [列级完整性约束]] [DROP <完整性约束名>] [MODIFY <列名> <数据类型>]; • 例 ALTER TABLE Loans ADD XX INT; ALTER TABLE Loans MODIFY Cardno SMALLINT; ALTER TABLE Loans DROP UNIQUE(BookNo);

删除基本表 DROP TABLE <表名> ; • 例 DROP TABLE Loans;

建立索引 CREATE [UNIQUE] [CLUSTER] INDEX <索引名> ON <表名> (<列名> [<ASC|DESC>] [, <列名> [<ASC|DESC>]]…); • 加快检索速度 • UNIQUE表示索引的每一个索引值只对应唯一的数据记录 • CLUSTER：建聚簇索引，即索引项顺序与表中记录的物理顺序一致，一个基表只能建一个聚簇索引 • ASC(升序，缺省)、DESC(降序) • 索引建立后由系统使用和维护，不需用户干预例 CREATE UNIQUE INDEX IB ON Borrows(CardNo); CREATE UNIQUE INDEX IS ON Student(Sno); CREATE UNIQUE INDEX SCno ON SC(Sno ASC, Cno DESC);

删除索引 DROP INDEX <索引名> ; • 例 DROP INDEX IB; DROP INDEX SCno; 提示：在Access中用DROP INDEX SCno ON SC;

3.3 查询 • 数据库查询是数据库的核心操作，SQL 提供了基于集合和关系的查询操作，具有丰富的功能和灵活的使用方式 • 一个 SQL 查询的结果是一个关系 • 查询可分为 • 单表查询：查询只涉及一个表 • 连接查询：查询同时涉及两个以上的表 • 嵌套查询：一个查询块嵌套在另一个查询块中 • 视图查询：在视图基础上的查询

SQL查询语句的格式 SELECT [ALL|DISTINCT] <目标列表达式> [,<目标列表达式>]… FROM <表名或视图名>[, <表名或视图名>]… [WHERE <条件表达式>] [GROUP BY <列名1> [HAVING <条件表达式>]] [ORDER BY <列名2> [ASC|DESC]]; • 查询语句的典型格式 • 这个查询与下面的关系代数表达式等价： A1, A2, ..., An(P (r1 x r2 x ... x rm)) select A1, A2, ..., Anfrom r1, r2, ..., rmwhere P 在做每个SQL查询时都试着写出其关系代数表达式

3.3.1 单表查询 一、选择表中若干列 • 查询指定列：在<目标列表达式>中指定预查属性 • 查询全部列：在<目标列表达式>中使用* • 查询经计算的值：在<目标列表达式>中可使用常量、表达式、函数等 SELECT Sno, Sname FROM Student; SELECT * FROM Student; SELECT Sname, ‘Year of Birth is’, 2002-Sage FROM Student;

查询实例 SELECT Sname, ‘Year of Birth is’, 2002-Sage, ISLOWER(Sdept) FROM Student; 结果为： Sname ‘year of Birth:’ 2002-Sage ISLOWER(Sdept) ———————————————————————— 李勇Year of Birht: 1982 cs 刘晨Year of Birht: 1983 is 王敏Year of Birht: 1984 ma 张立Year of Birht: 1983 is

定义别名 • 用户可以通过指定别名来改变查询结果的列标题，这对于含算术表达式、常量、函数名的目标列表达式尤为有用。例如对于上例，可以定义如下列别名： SELECT Sname NAME, ‘Year of Birth:’ BIRTH, 1996-Sage YEAR, ISLOWER(Sdept) DEPARTMENT FROM Student; 结果为： NAME BIRTH YEAR DEPARTMENT ——————————————————-----—————— 李勇 Year of Birth: 1982 cs 刘晨Year of Birth: 1983 if 王敏Year of Birth: 1984 ma 张立Year of Birth: 1983 if

ACCESS数据库中SQL例 别名表示：字段 as 别名注：ISLOWER( )函数在access中无法识别 SELECT Sname AS NAME, 'Year of Birth:' AS BIRTH, 1996-Sage AS YEAR, Sdept AS DEPARTMENT FROM Student;

二、选择表中若干元组 • 取消取值重复的列：指定DISTINCT短语 • 查询满足条件的元组：在WHERE子句中指定条件 • 对查询结果排序：使用ORDER BY • 使用集函数：COUNT, SUM, AVG, MAX, MIN • 对查询结果分组：使用GROUP BY，HAVING

DISTINCT短语 • SQL 允许重复的元组/行存在，如果需要去掉重复的元组/行，必须指定DISTINCT 短语，缺省为ALL • 例:查询选修了课程的学生学号结果为：结果为： Sno Sno —————— 95001 95001 95001 95002 95001 95002 95002 SELECT Sno FROM SC; 或 SELECT ALL Sno FROM SC; SELECT DISTINCT Sno FROM SC;

WHERE子句 • 查询满足条件的元组：在WHERE子句中指定条件 • WHERE子句常用的查询条件:

Where子句－比较大小 例查询计算机系全体学生的名单 SELECT Sname FROM Student WHERE Sdept = ‘CS’; 例查询考试成绩有不及格的学生的学号 SELECT DISTINCT Sno FROM SC WHERE Grade < 60; 例查询所有年龄在20岁以下的学生姓名及其年龄 SELECT Sname, Sage SELECT Sname, Sage FROM Student FROM Student WHERE Sage<20; WHERE NOT Sage>=20;

Where子句－确定范围 例查询年龄在20~40岁之间的学生姓名、系别和年龄 SELECT Sname, Sdept, Sage FROM Student WHERE Sage BETWEEN 20 AND 40; 例查询年龄不在20~23岁之间的学生姓名、系别和年龄 SELECT Sname, Sdept, Sage FROM Student WHERE Sage NOT BETWEEN 20 AND 23;

Where子句－确定集合 例查询信息系(IS)、数学系(MA)和计算机系(CS)学生的姓名和性别 SELECT Sname, Ssex FROM Student WHERE Sdept IN (‘IS’, ‘MA’, ‘CS’); 例查询除信息系(IS)、数学系(MA)和计算机系(CS)以外其它系学生的姓名和性别 SELECT Sname, Ssex FROM Student WHERE Sdept NOT IN (‘IS’,’MA’,’CS’)

SELECT * FROM Student WHERE Sno LIKE ‘95001’; SELECT * FROM Student WHERE Sno = ‘95001’; Where子句－字符串匹配 • 谓词LIKE可以用来进行字符串的匹配 • 格式：[NOT] LIKE ’<匹配串>’[ESCAPE’<换码字符>’] • 匹配串中%代表任意长度的字符串，如a%b：acb, addgb, ab • 匹配串中_ 代表任意单个字符，如a_b：acb afb 提示：Access中的匹配符分别使用*和？。例查询学号为95001的学生的详细情况：相当于

通配符例子 例查询所有姓刘的学生的详细信息 SELECT * FROM Student WHERE Sname LIKE ‘刘%’; 例查询所有全名为三个字且中间汉字为“明”的学生的详细信息 SELECT * FROM Student WHERE Sname LIKE ‘_ _明_ _’;

换码字符 例查询DB_Design课程的课程号和学分 SELECT Cno, Credit FROM Course WHERE Cname LIKE ‘DB\_Design’ ESCAPE ‘\’ ; 注：ESCAPE ‘\’表示‘\’为转义换码字符，紧跟其后的_转义为普通_字符例查询以“DB_”开头，且倒数第3个字符为 i 的课程的详细情况 SELECT * FROM Course WHERE Cname LIKE ‘DB\_%i_ _’ ESCAPE ’\’ ;

Where子句－涉及空值的查询 例：某些学生选修课程后没有参加考试，所以有选修课记录，但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号： SELECT Sno, Cno FROM SC WHERE Grade IS NULL; 注意这里的“IS” 不能用等号代替例查询所有有成绩的学生学号和课程号 SELECT Sno, Cno FROM SC WHERE Grade IS NOT NULL;

Where子句－多重条件查询 例查询计算机系年龄在20岁以下的学生姓名 SELECT Sname FROM Student WHERE Sdept = ‘CS’ AND Sage <20; 例查询信息系(IS)、数学系(MA)和计算机系(CS)学生的姓名和性别 SELECT Sname, Ssex FROM Student WHERE Sdept = ‘IS’ OR Sdept = ‘ MA’ OR Sdept = ‘CS’;

练习 • 关系：图书(书号,书名,作者,出版社,单价） BOOK(Bno, Bname, Author, Press, Price) • 查询“数据库”一书的书号和单价 • 查询单价在20至50元之间的图书信息 • 查询北京某出版社出版的图书信息 • 查询作者是张一，王二，刘三的书的信息 • 查询所有图书的书号，书名和半价信息 • 查询缺少出版社信息的图书的书号和书名

三、对查询结果排序 • ORDER BY 子句可对查询结果按照一个或多个属性列的升序（ASC）或降序（DESC）排列，缺省值为升序例查询选修了3号课程的学生的学号及其成绩，查询结果按分数的降序排列 SELECT Sno, Grade FROM SC WHERE Cno = ‘3’ ORDER BY Grade DESC; 例查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列（见mdb示例） SELECT * FROM Student ORDER BY Sdept, Sage DESC;

四、使用集函数

实例例查询学生总人数例查询选修了课程的学生人数 SELECT COUNT(*) FROM Student; SELECT COUNT(DISTINCT Sno) FROM SC; 例计算选修了1号课程的学生平均成绩 SELECT AVG(Grade) FROM SC WHERE Cno = ‘1’; 例查询选修1号课程的学生最高分数 SELECT MAX(Grade) FROM SC WHERE Cno = ‘1’;

五、对查询结果分组 • GROUP BY 子句将查询结果按某一列或多列分组，值相等的为一组。 • 对查询结果分组的目的是为了细化集函数的作用对象，分组后每个组都有一个函数值。 • 如果分组后还要求按一定的条件对这些组进行筛选，最终只输出满足指定条件的组，则可以是使用HAVING短语指定筛选条件。

分组实例 例求各个课程号及相应的选课人数 SELECT Cno, COUNT(Sno) FROM SC GROUP BY Cno; 例查询选修了3门以上课程的学生学号 SELECT Sno FROM SC GROUP BY Sno HAVING COUNT(*)>3;

练习数据库模式如下： • 仓库(仓库号，负责人) • 货物(货物号，货物名，货物描述) • 入库记录(货物号，仓库号，数量，入库日期，经办人号) 用SQL实现以下查询要求： • 查询仓库中有多少种不同的货物 • 查询每天入库数量的最大值 • 统计2003年3月20日以后每天入库的货物总数量 • 按货物号统计每一种货物在仓库中的总数量 • 查询“A-001”号经办人当日经办的入库记录数 Access中使用Now()函数来获取当前时间，而在SQL Server中用Getdate()函数来获取当前时间

SELECT COUNT(*) FROM 货物；或 SELECT COUNT(DISTINCT 货物号) FROM 入库记录； SELECT入库日期, MAX(数量) FROM 入库记录 GROUP BY 入库日期； SELECT入库日期, SUM(数量) FROM 入库记录 GROUP BY 入库日期 HAVING 入库日期 > “2003/03/20”；

SELECT 货物号，SUM(数量) FROM 入库记录 GROUP BY 货物号; SELECT COUNT(*) FROM 入库记录 WHERE入库日期=Today() GROUP BY 经办人号 HAVING 经办人号 = “A-001”;

3.3.2 连接查询 • 连接(Join)查询 • 查询涉及两个以上的表(在FROM子句中体现) • From 子句对应于关系代数中笛卡儿乘积运算, 它给出待扫描的关系/表(指定多个表)，给出连接条件 • 连接查询的类型 • 等值连接查询 • 非等值连接查询 • 自然连接查询 • 自身连接查询 • 外连接查询 • 符合条件连接查询

一、等值与非等值连接查询 • 连接条件的形式 • [<表名1>.]<列名1> <比较运算符> [<表名2>.]<列名2> • 比较运算符: = (等值连接)、>、<、>=、<=、!= • 连接字段必须是可比的 • [<表名1>.]<列名1> BETWEEN [<表名2>.]<列名2> AND [<表名2>.]<列名3> • 连接查询的执行过程 • 首先在表1中找到第一个元组，然后从头开始扫描表2，逐一查找满足连接条件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中的一个元组。表2全部查找完后，再找表1中第二个元组，然后再从头开始扫描表2。重复上述操作，直至表1中的全部元组都处理完毕为止。

例子 • 例32 查询每个学生及其选修课程的情况学生情况存放Student表中，学生选课情况存放在SC表中，所以本查询实际上涉及Student与SC两个表。通过公共属性Sno实现联系使用表名前缀为避免混淆，若属性名在各表中唯一，则可省略表名前缀 SELECT Student.*, SC.* FROM Student, SC WHERE Student.Sno = SC.Sno; 等值连接连接谓词

自然连接和广义笛卡尔积连接 • 自然连接 • 等值连接的特例 • 在等值连接的基础上将目标列中重复的属性去掉 • 广义笛卡尔积连接 • 不带连接条件的连接 • 结果是两表中元组的交叉乘积

例查询每个学生及其选修课程的情况 SELECT Student.Sno, Sname, Ssex, Sage, Sdept, Cno, Grade FROM Student, SC WHERE Student.Sno = SC.Sno; 自然连接 SELECT Student.*, SC.* FROM Student, SC; 广义笛卡尔积连接

别名二、自身连接 • 一个表与其自己进行连接例查询每一门课的间接先修课 SELECT FIRST.Cno, SECOND.Cpno FROM Course FIRST, Course SECOND WHERE FIRST.Cpno = SECOND.Cno; 见P104表

实例例查询与“刘晨”在同一个系学习的学生 SELECT S1.Sname FROM Student S1, Student S2 WHERE S1.Sdept = S2.Sdept AND S2.Sname = ‘刘晨’；

三、外连接 • 是连接运算的扩充 • 动机 • 除了满足连接条件的元组正常输出外，所有不满足连接条件的元组也进行连接，并用空值NULL来填充并输出 • 外连接分类 • 左外连接（LEFTJOIN）:将LEFT JOIN左边的表名1中的所有记录全部保留，而将右边的表名2中的字段B与表名1.字段A相对应的记录显示出来。 • 右外连接（RIGHTJOIN）：与LEFT JOIN相反。 • 全外连接（FULL JOIN）：综合以上两种。 • 外连接符： • 不同数据库产品采用的外连接形式可能不同； • 课程中，用*（或+）表示，意思当找不到满足连接条件的行时，是带*（或+）表中增加一个万能行（空行）显示。

关系数据库标准语言 SQL

关系数据库标准语言 SQL

Presentation Transcript