1 / 135

关系数据库标准语言 SQL

关系数据库标准语言 SQL. 要点. SQL 语言简介 SQL 语言及其使用方式 独立式 SQL 嵌入式 SQL. 3.1 SQL 概述. Structured Query Language, 1974 年提出 关系数据库的国际标准语言：大多数数据库均用 SQL 作为共同的数据存取语言和标准接口，实现不同数据库系统之间的互操作 目前仍被不断扩充 介于关系代数和关系演算之间，三者可相互转换. SQL 的特点. 综合统一： 集 DDL 、 DML 、 DCL 功能于一体，可独立完成数据库生命周期中的全部活动，语言风格统一 高度非过程化：

dane-cote
Télécharger la présentation

关系数据库标准语言 SQL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 关系数据库标准语言SQL

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

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

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

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

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

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

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

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

  10. 例：建立学生管理的相关基表 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);

  11. 例：建立图书管理的相关基表 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));

  12. 例：完整性约束 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))；

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

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

  15. 建立索引 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);

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

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

  18. 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查询时都试着写出其关系代数表达式

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

  20. 查询实例 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

  21. 定义别名 • 用户可以通过指定别名来改变查询结果的列标题，这对于含算术表达式、常量、函数名的目标列表达式尤为有用。例如对于上例，可以定义如下列别名： 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

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

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

  24. 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;

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

  26. 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;

  27. 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;

  28. 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’)

  29. 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的学生的详细情况： 相当于

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

  31. 换码字符 例 查询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 ’\’ ;

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

  33. 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’;

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

  35. 三、对查询结果排序 • 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;

  36. 四、使用集函数

  37. 实例 例 查询学生总人数 例 查询选修了课程的学生人数 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’;

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

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

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

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

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

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

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

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

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

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

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

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

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

More Related