1 / 91

3. SQL 概述

3. SQL 概述. SQL 概述 SQL 数据定义功能 SQL 数据查询功能 SQL 数据修改功能 SQL 数据控制功能 嵌入式 SQL. 3.1 SQL 概述. 历史 1974 年，由 Boyce 和 Chamberlin 提出。 1975-1979 年，在 System R 上实现，由 IBM 的 San Jose 研究室研制，称为 Sequel ，现在称为 SQL (Struceured Query Languang) 。 标准化 有关组织 ANSI(American Natural Standard Institute)

heba
Download Presentation

3. 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. 《数据库系统概论》讲义 3. SQL概述 • SQL概述 • SQL数据定义功能 • SQL数据查询功能 • SQL数据修改功能 • SQL数据控制功能 • 嵌入式SQL

  2. 《数据库系统概论》讲义 3.1 SQL概述 • 历史 • 1974年，由Boyce和Chamberlin提出。 • 1975-1979年，在System R上实现，由IBM的San Jose研究室研制，称为Sequel，现在称为SQL (Struceured Query Languang)。 • 标准化 • 有关组织 • ANSI(American Natural Standard Institute) • ISO(International Organization for Standardization) • 有关标准 • SQL-86：“数据库语言SQL”

  3. 《数据库系统概论》讲义 3.1 SQL概述 • SQL-89：“具有完整性增强的数据库语言SQL”，增加了对完整性约束的支持。 • SQL-92：“数据库语言SQL”，是SQL-89的超集，增加了许多新特性，如新的数据类型，更丰富的数据操作，更强的完整性、安全性支持等。 • SQL-3：正在讨论中的新的标准，将增加对面向对象模型的支持。 • 特点 • 一体化 集DDL，DML，DCL于一体。 单一的结构----关系，带来了数据操作符的统一。 • 面向集合的操作方式 一次一集合。

  4. 《数据库系统概论》讲义 3.1 SQL概述 • 高度非过程化 用户只需提出“做什么”，无须告诉“怎么做”，不必了解存取路径。 • 两种使用方式，统一的语法结构 SQL既是自含式语言（用户使用），又是嵌入式语言（程序员使用）。 • 语言简洁，易学易用

  5. 《数据库系统概论》讲义 SQL示例 DEPT(D# , DNAME , DEAN) S(S# , SNAME , SEX , AGE , D#) COURSE(C# , CN , PC# , CREDIT) SC(S# , C# , SCORE) PROF(P# , PNAME, AGE, D# , SAL) PC(P# , C#)

  6. 《数据库系统概论》讲义 SQL示例

  7. 《数据库系统概论》讲义 3.2 SQL数据定义 • 域定义 • 基本表的定义 • 索引的定义 • 数据库的建立与撤消 • SQL数据定义特点

  8. 《数据库系统概论》讲义 域定义 • 域类型（SQL-92） • char（n）：固定长度的字符串。 • varchar（n）：最大长度为n的可变长字符串。 • int：长整型，全字长。 • smallint：短整型，半字长。 • decimal（p[，q]）：定长数，共p位，其中小数点后边q位。 • real：浮点数。 • double precision：双精度浮点数。 • date：日期（年、月、日）。 • time：时间（小时、分、秒）。

  9. 《数据库系统概论》讲义 SQL提供的数据类型

  10. 《数据库系统概论》讲义 域定义 • 域定义 • 格式 create domain域名 数据类型 • 示例 create domainperson-name char（20） 类似C语言中： typedefADDRESS_LIST{ char name[10]; char telephone[20]; char location[20] char email[20]}; ADDRESS_LIST tom;

  11. 《数据库系统概论》讲义 创建表 • 定义（CREATE） create table表名（ 列名 数据类型 [default 缺省值] [not null] [，列名 数据类型 [default 缺省值] [not null]] …… [，primary key（列名 [，列名] …）] [，foreign key（列名 [，列名] …） references表名 （列名 [，列名] …）] [，check（条件）]）

  12. 《数据库系统概论》讲义 创建表 • 示例 create domain person_name char(20) create table PROF ( PNO char[10], person_name PNAME not null, SAL int, AGE int, DNO char[10], primary key (PNO), foreign key (DNO) references DEPT(DNO), check (SAL > 0))

  13. 《数据库系统概论》讲义 修改表 • 修改基本表定义（ALTER） • 格式： alter table表名 [add子句] 增加新列 [drop 子句] 删除列 [modify子句] 修改列定义 • 示例 alter table PROF add LOCATION char[30]

  14. 《数据库系统概论》讲义 撤消表 • 撤消基本表定义（drop） • 格式 drop table表名 • 示例 drop tableDEPT • DANGER 撤消基本表后，基本表的定义、表中数据、索引、以及由此表导出的视图的定义都被删除。 • WHAT’S THE MEAN OFCASCADE?

  15. 《数据库系统概论》讲义 创建索引 • 索引的定义 create [unique/distinct] [cluster] index索引名 on表名 (列名 [asc/desc] [ , 列名[asc/desc]]…) unique（distinct）：唯一性索引，不允许表中不同的行在索引列上取相同值。若已有相同值存在，则系统给出相关信息，不建此索引。系统并拒绝违背唯一性的插入、更新。 cluster：聚集索引，表中元组按索引项的值排序并物理地聚集在一起。一个基本表上只能建一个聚集索引。 asc/desc：索引表中索引值的排序次序，缺省为asc。

  16. 《数据库系统概论》讲义 创建索引 • 示例： DEPT(D# , DNAME , DEAN) S(S# , SNAME , SEX , AGE , D#) COURSE(C# , CN , PC# , CREDIT) SC(S# , C# , SCORE) PROF(P# , PNAME, AGE, D# , SAL) PC(P# , C#) create unique index s-index on S（S#） create unique index sc-index on SC （S# asc, C# desc） create cluster index c-index on course（C#）

  17. 《数据库系统概论》讲义 索引撤消 • 索引的撤消 • 格式： drop index 索引名 • 索引的有关说明 • 可以动态地定义索引，即可以随时建立和删除索引。 • 不允许用户在数据操作中引用索引。索引如何使用完全由系统决定，这支持了数据的物理独立性。 • 应该在使用频率高的、经常用于连接的列上建索引。 • 一个表上可建多个索引。索引可以提高查询效率，但索引过多耗费空间，且降低了插入、删除、更新的效率。

  18. 《数据库系统概论》讲义 创建库 有的数据库系统支持多库。 • 建立一个新数据库 create database数据库名 • 撤消一个数据库 drop database数据库名 • 指定当前数据库 database数据库名 • 指定当前数据库 close database数据库名

  19. 《数据库系统概论》讲义 3.2 SQL数据定义特点 • SQL中，任何时候都可以执行一个数据定义语句，随时修改数据库结构。 • 在非关系型的数据库系统中，必须在数据库的装入和使用前全部完成数据库的定义。若要修改已投入运行的数据库，则需停下一切数据库活动，把数据库卸出，修改数据库定义并重新编译，再按修改过的数据库结构重新装入数据。 • 数据库定义不断增长(不必一开始就定义完整)。 • 数据库定义随时修改(不必一开始就完全合理)。 • 可进行增加索引、撤消索引的实验，检验其对效率的影响。

  20. 《数据库系统概论》讲义 3.3 SQL数据查询 • SQL数据查询基本结构 • select子句 • 重复元组的处理 • from子句 • where子句 • 更名运算 • 字符串操作 • 元组显示顺序 • 集合操作

  21. 《数据库系统概论》讲义 3.3 SQL数据查询 • 分组和聚集函数 • 空值 • 嵌套子查询 • 派生关系 • 视图 • 关系的连接

  22. 《数据库系统概论》讲义 数据查询基本结构 • 基本结构 selectA1 , A2 , … , An fromr1 ,r2 , …, rm whereP  ∏A1 , A2 , … , An(p(r1  r2  …  rm)) • 示例 给出所有老师的姓名。 select PNAME from PROF

  23. 《数据库系统概论》讲义 数据查询目标列 • 目标列形式 可以为列名，* ，算术表达式，聚集函数。 • “*”：表示“所有的属性”。 给出所有老师的信息。 select * from PROF • 带，， ， 的算术表达式 给出所有老师的姓名及税后工资额。 select PNAME，SAL  0.95 from PROF

  24. 《数据库系统概论》讲义 重复元组处理 • 语法约束 缺省为保留重复元组，也可用关键字all显式指明。若要去掉重复元组，可用关键字distinct或unique指明。 • 示例 找出所有选修课程的学生。 selectdistinct SNO from SC

  25. 《数据库系统概论》讲义 From子句 • 说明 from子句列出查询的对象表。当目标列取自多个表时，在不混淆的情况下可以不用显式指明来自哪个关系。 • 示例 • 例：找出工资低于500的职工的姓名、工资、系别。 select PNAME , SAL , DNAME from PROF , DEPT where SAL < 500 and PROF.DNO = DEPT.DNO

  26. 《数据库系统概论》讲义 From子句 • 例：列出教授“哲学”课程的老师的教工号及姓名。 select PROF. PNO , PNAME from PROF , PC , COURSE where PROF.PNO = PC.PNO and PC.CNO = COURSE.CNO and COURSE.CNAME = “哲学”

  27. 《数据库系统概论》讲义 Where子句 • 语法成分 • 比较运算符：、 、、、=、  。 • 逻辑运算符：and，or，not。 • between条件：判断表达式的值是否在某范围内。 • in：确定集合，判断表达式的值是否在某集合内。 • 示例 • 列出工资在500~800之间的老师姓名。 select PNAME from PROF where SAL between 500 and 800

  28. 《数据库系统概论》讲义 更名运算 • 格式 old_name as new_name 为关系和属性重新命名，可出现在select和from子句中。 • 示例 • 属性更名 例：给出所有老师的姓名、所纳税额及税后工资额。 select PNAME，SAL0.05 as taxi， SAL*0.95 as incoming from PROF

  29. 《数据库系统概论》讲义 更名运算 • 关系更名 找出工资比所在系主任工资高的老师姓名及工资。 select P1.PNAME，P1.SAL from PROF as P1，PROF as P2，DEPT where P1.DNO = DEPT.DNO and DEPT.DEAN = P2.PNO and P1.SAL > P2.SAL 注：as可选。

  30. 《数据库系统概论》讲义 字符串操作 • 命令格式 • like：找出满足给定匹配条件的字符串。 格式：列名 [not] like “字符串” • 匹配规则： “%” ：匹配零个或多个字符。 “＿”：匹配任意单个字符。 escape：定义转义字符，以去掉特殊字符的特定含义，使其被作为普通字符看待。如escape “\”，是定义了 \ 作为转义字符，则可用\%去匹配%，用\＿去匹配＿，用\ \去匹配 \ 。

  31. 《数据库系统概论》讲义 字符串操作 • 示例 • 列出姓名以“张”打头的教师的所有信息。 select * from PROF where PNAME like “张%” • 列出名称中含有4个字符以上，且倒数第3个字符是d，倒数第2个字符是_的系的所有信息。 select * from PROF where PNAME like “% ＿d \＿＿”

  32. 《数据库系统概论》讲义 元组显示顺序 • 命令 order by列名 [asc | desc] • 示例 • 按系名升序列出老师姓名，所在系名，同一系中老师按姓名降序排列。 select DNAME，PNAME from PROF，DEPT where PROF.DNO = DEPT.DNO order by DNAME asc，PNAME desc

  33. 《数据库系统概论》讲义 集合操作 • 命令 集合并：union 集合交：intersect 集合差：except • 示例 • 求选修了001或002号课程的学生号。 （select SNO from SC where CNO = 001） union all （select SNO from SC where CNO = 002）

  34. 《数据库系统概论》讲义 集合操作 • 求选修了001或002号而没有选003号课程的学生号。 （select SNO from SC where CNO = 001 or CNO = 002 ） except （select SNO from SC where CNO = 003） • 提示 集合操作自动去除重复元组，如果要保留重复元组的话，必须用all关键词指明。

  35. 《数据库系统概论》讲义 分组和聚集函数 • 分组命令 group by列名 [having条件表达式] group by将表中的元组按指定列上的值相等的原则分组，然后在每一分组上使用聚集函数，得到单一值。having则对分组进行选择，只将聚集函数作用到满足条件的分组上。 • 聚集函数 • 平均值：avg • 最小值：min • 最大值：max • 总和：sum • 记数：count

  36. 《数据库系统概论》讲义 分组和聚集函数 • 示例 • 列出各系的老师的最高、最低、平均工资。 select DNO，max(SAL)，min(SAL)，avg(SAL) from PROF group by DNO • 列出及格的学生的平均成绩。 select SNO，avg(SCORE) from SC group by SNO havingmin(SCORE) >= 60

  37. 《数据库系统概论》讲义 分组和聚集函数 • 火眼金睛 ① selectcount (SNO ) from SC ② select PNAME，max(SAL) from PROF ③ select DNO，avg(SAL) from PROF group by DNO where AGE < 60

  38. 《数据库系统概论》讲义 空值操作 • 空值测试 is [not] null 测试指定列的值是否为空值。 • 示例 找出年龄值为空的老师姓名。 select PNAME from PROF where AGE is null 不可写为where AGE = null

  39. 《数据库系统概论》讲义 空值操作 • 注意事项 • 除is [not] null之外，空值不满足任何查找条件。 • 如果null参与算术运算，则该算术表达式的值为null。 • 如果null参与比较运算，则结果可视为false。在SQL-92中可看成unknown。 • 如果null参与聚集运算，则除count(*)之外其它聚集函数都忽略null。 例：selectsum(SAL) from PROF 例：selectcount(*) from PROF

  40. 《数据库系统概论》讲义 嵌套子查询 • 集合成员资格 • 集合之间的比较 • 集合基数的测试 • 测试集合是否为空 • 测试集合是否存在重复元组

  41. 《数据库系统概论》讲义 集合成员资格 • in 子查询 表达式 [not] in（子查询） 判断表达式的值是否在子查询的结果中。 • 示例 • 选修了001号课程的学生的学号及姓名。 select SNO，SNAME from S where SNO in (select SNO from SC where CNO = 001）

  42. 《数据库系统概论》讲义 集合成员资格 • 列出选修了001号和002号课程的学生的学号。 select SNO from SC where SC.CNO = 001 and SNO in （select SNO from SC where CNO = 002） • 列出张军和王红同学的所有信息。 select * from S where SNAME in （“张军”，“王红”）

  43. 《数据库系统概论》讲义 集合之间比较 • some/all子查询 • 表达式 比较运算符some（子查询） 表达式的值至少与子查询结果中的一个值相比满足比较运算符 。 • 表达式 比较运算符 all（子查询） 表达式的值与子查询结果中的所有的值相比都满足比较运算符。

  44. 《数据库系统概论》讲义 集合之间比较 • 示例 • 找出平均成绩最高的学生号。 select SNO from SC group by SNO havingavg(SCORE) >= all （selectavg(SCORE) from SC group by SNO）

  45. 《数据库系统概论》讲义 集合基数测试 • 测试集合是否为空 [not] exists（子查询） 判断子查询的结果集合中是否有任何元组存在。 • 列出选修了01号课程的学生的学号及姓名。 select SNO，SNAME from S where exists （select * from SC where CNO = 01 and SC.SNO = S.SNO）

  46. 《数据库系统概论》讲义 集合基数测试 • 列出选修了001号和002号课程的学生的学号。 select SNO from SC SC1 where SC1.CNO = 001 andexists （select SNO from SC SC2 where SC2. CNO = 002 and SC2.SNO = SC1.SNO） • 注：in后的子查询与外层查询无关，每个子查询执行一次，而exists后的子查询与外层查询有关，需要执行多次，称之为相关子查询。

  47. 《数据库系统概论》讲义 集合基数测试 任意课程，001号学生选之，所求学生选之。  不存在任何一门课程，001号学生选之，所求学生没有选之。 • 列出至少选修了001号学生选修的所有课程的学生名。 select SNAME from S wherenot exists （select CNO from COURSE where exists （select * from SC where SC.CNO = COURSE.CNO and SC.SNO = 001） and not exists （select * from SC where SC.CNO = COURSE.CNO and SC.SNO = S.SNO））

  48. 《数据库系统概论》讲义 集合基数测试 • 测试集合是否存在重复元组 unique (子查询） 如果子查询结果中没有重复元组，则返回true。 • 示例 • 找出所有只教授一门课程的老师姓名。 select PNAME from PROF whereunique （select PNO from PC where PC.PNO = PROF.PNO)

  49. 《数据库系统概论》讲义 集合基数测试 • 找出至少选修了两门课程的学生姓名。 select SNAME from S wherenotunique （select SNO from SC where SC.SNO = S.SNO) • 思考 to TRUE or not to TRUE , that is the question. unique { (a , b , null) , (a , b , null) } ?

  50. 《数据库系统概论》讲义 派生关系 • 命令 （子查询） as关系名（列名，列名，…） SQL-92中，允许在from子句中使用子查询表达式，这时可将该子查询的结果命名为一个临时关系加以引用。 • 示例 • 找出平均成绩及格的学生。 先求出每个学生的平均成绩，再从中找出及格的学生 select SNAME ， avg（SCORE） from S，SC where SC.SNO = S.SNO group by SC .SNO

More Related