SQL语句基本知识.ppt

第4章关系数据库语言SQL 教学内容SQL概述 SQL的数据定义 SQL的数据查询 SQL的数据更新 嵌入式SQL教学要求了解SQL语言特点和基本概念 重点掌握SQL语言基本功能 会用SQL语言对数据库进行查询 更新 会用SQL语言创建视图 表和其他高级功能 教学重点掌握SQL语言基本功能 教学难点SQL的数据查询 4 1SQL语言概述 4 1 1SQL的产生和发展 1970年 提出关系模型 1972年 SQUARE语言 1974年 SEQUEL 1986年 美国提出了 数据库SQL语言 1987年 国际化标准化组织采纳为国际标准 称为 SQL86 1989年 SQL89 1992年 SQL2 1999年 SQL3 4 1 2SQL的特点 一体化高度非过程化两种使用方式用户性能好功能强大是所有数据库管理系统的公共语言 4 1 3SQL语言的基本组成 SQL语言支持关系数据库的三级模式结构 如图6 1所示 其中 1 外模式对应于视图和部分基本表 2 模式对应于基本表 内模式对应于存储文件 3 在SQL中 表被分为两种 一种叫基表 basetable 其数据显式地存储在数据库中 另一种叫视图 view 它是一种虚表 具有逻辑定义 是从一个或几个基表 或视图 导出的表 视图本身并不作为一个表显式地存储在数据库中 每个基本表对应一个存储文件 一个表可以带有若干索引 索引也存放在存储文件中 存储文件及索引组成了关系数据库的内模式 主要由以下三个部分组成 1 数据定义语言 DataDefinitionLanguage简称DDL 用于定义 撤销和修改数据模式 2 数据操纵语言 DataManipulationLanguage简称DML 主要包括两部分 1 数据查询语言 QueryLanguage简称QL 用于进行数据查询 2 数据更新语言 用于增加 删除和修改数据 3 数据控制语言 DataControlLanguage简称DCL 用于对数据访问权限的控制完整性描述事务控制等 4 1 4SQL语言的基本数据类型 4 2数据定义 4 2 1基本表的操作 1 定义基本表 定义基本表的语句格式如下 CREATETABLE 列级完整性的约束 列级完整性的约束 说明 1 是所要定义的基本表的名字 它可以由一个或多个属性 列 组成 2 列级完整性的约束主要有 NOTNULL 就是规定一个属性的值不允许为空值 省略则表示可以允许该属性的值为空值 3 完整性约束主要有以下三种子句 主键子句 PRIMARYKEY 检查子句 CHECK 外键子句 FOREIGNKEY 例4 1 建立一个 图书 表 它由图书编号B 书名Title 作者Author 图书分类号T 出版社编号P 单价Price 复本数量CopyNumber 库存量Stocks 日罚金Fine 是否新书IsNew十个属性组成 其中图书编号 出版社编号不能为空 且图书编号的值是唯一的 CREATETABLEBook B CHAR 6 NOTNULL TitleCHAR 60 AuthorCHAR 30 BookTypeCHAR 2 P CHAR 2 NOTNULL PriceNUMERIC 9 CopyNumberSMALLINT StocksSMALLINT FineNUMERIC 9 IsnewBIT PRIMARYKEY B 2 修改基本表 修改基本表的语句格式如下 ALTERTABLE ADD 完整性约束 DROP MODIFY 说明 1 是要修改的基本表 2 ADD子句用于增加新列和新的完整性约束条件 3 DROP子句用于删除指定的完整性约束条件 4 MODIFY子句用于修改原有的列定义 包括修改列名和数据类型 例4 2 将图书表Book的 复本数量 列的数据类型由短整型改为长整型 ALTERTABLEBookMODIFY ALTERCOLUMN CopyNumberINTCopyNumberINT注 要用ALTERCOLUMN 3 删除基本表 删除基本表的语句格式如下 DROPTABLE 说明 一个基本表一旦被删除后 这个表的所有数据都将被删除掉 因此 执行删除基本表的操作一定要格外小心 例4 3 删除图书表Book DROPTABLEBook 4 2 2索引的建立与删除 1 建立索引 CREATE UNIQUE CLUSTER INDEXON 次序2 说明 1 是要建索引的基本表的名字 2 索引可以建立在该表的一列或多列上 各列名之间用逗号隔开 3 规定了索引值的排列次序 可为升序 ASC 或降序 DESC 缺省值为ASC 4 UNIQUE表示要建立的有一个索引值只对应唯一的数据记录 所以 如选择此项 则相应的列一定是主关键字 5 CLUSTER表示要建立的索引是聚簇索引 例4 4 为图书表Book 借阅人表Reader建立索引 其中Book表按图书编号升序建唯一索引 Reader表按读者编号降序建唯一索引 CREATEUNIQUEINDEXBook IndexONBook B CREATEUNIQUEINDEXReader IndexONReader R DESC 2 删除索引删除索引的语句格式如下 DROPINDEX 例4 5 删除Book表的Book Index索引 DROPINDEXBook Index 4 3数据查询 4 3 1SELECT语句的一般格式 SELECT DISTINCT FROM 基本表名2或视图名2 WHERE GROUPBY HAVING ORDERBY ASC DESC 说明 1 SELECT子句指定了查询的列 可以是 1 2 表中的列名 3 表达式 2 FROM子句指定了被查询的基本表或视图 3 WHERE子句说明查询的条件 可使用下列操作符 1 算术比较运算符 2 逻辑运算符 AND OR NOT 3 集合运算符 UNION 并 INTERSECT 交 EXCEPT 差 4 集合成员资格运算符 IN NOTIN 5 谓词 EXISTS 存在量词 ALL ANY UNIQUE 6 集合函数 COUNT 计数 SUM 求和 AVG 平均值 MAX 最大值 MIN 最小值 7 还可以是另外一个SELECT语句 即SELECT语句可以嵌套 4 SELECT和FROM子句是每个SQL语句所必需的 其它子句是任选的 整个语句的含义是 根据WHERE子句的条件表达式 从FROM子句指定的基本表或视图中找出满足条件的元组 再按SELECT子句中的目标列表达式 选出元组中的属性值形成结果表 5 若有GROUP子句 则结果按指定的分组产生结果表中的一个元组 通常在每组中还可以通过HAVING作用库函数 当查询结果分组输出时 只输出满足条件的组 6 若有ORDER子句 则结果要根据指定的按升序或降序排序 下面以图书 借阅人数据库为例来说明SELECT语句的各种用法 图书 借阅人数据库包括六个表 1 图书表Book B Title Author BookType P Price CopyNumber Stocks Fine IsNew Book由图书编号 B 书名 Title 作者 Author 图书分类 BookType 出版社 P 单价 元 Price 复本数量 CopyNumber 库存量 Stocks 日罚金 元 Fine 是否新书 IsNew 十个属性组成 其中B 为主键 P 为外键 2 借阅卡表Card C R Card由借阅卡编号 C 读者编号 R 两个属性组成 其中C 为主键 R 为外键 3 借阅人表Reader R Name IsStudent Password Borrowed Email PhoneNumber Reader由学生编号 R 姓名 Name 是否学生 IsStudent 密码 Password 已借数量 Borrowed Email地址 Email 电话号码 PhoneNumber 七个属性组成 其中R 为主键 4 借阅表Lending L R B BorrowingDate IsRenewed RenewingDate IsReturned ReturningDate Lending由流水号 L 借阅人编号 R 图书编号 B 借阅日期 BorrowingDate 是否续借 IsRenewed 续借日期 RenewingDate 是否 已归还 IsReturned 归还日期 ReturningDate 八个属性组成 其中L R B 为联合主键 5 出版社表Publisher P Publisher Publisher由编号 P 出版社名 Publisher 两个属性组成 其中P 为主键 6 图书分类表Type T Type Type由编号 T 分类号 Type 两个属性组成 其中T 为主键 4 3 2单表查询 1 查询全部列把表中的所有属性列都选出来 可以有两种方法 1 就在SELECT关键字后面列出所有属性列名 2 可以在SELECT关键字后面用 此时属性列名的显示顺序与其在基本表中的顺序相同 例4 6 查询图书馆中所有图书的详细记录 SELECT FROMBook 等价于 SELECTB Title Author BookType P Price CopyNumber Stocks Fine IsNewFROMBook 2 查询部分列可以通过在SELECT关键后面列出用户要查询的属性列后 属性列名的显示顺序可以根据用户需要来指定 例4 7 查询图书馆中所有图书的书号与书名 SELECTB TitleFROMBook 3 查询某些列的不同元组两个本来不完全相同的元组 投影到指定的某些列上后 可能变成相同的行 为了去掉结果表中相同的行 可以在SELECT关键字后加DISTINCT关键字 例4 8 查询借了图书的借阅者编号 SELECTR FROMLending 假设查询的结果为 该查询结果中包含了很多重复的行 用下面的查询语句可以去掉重复的行 SELECTDISTINCTR FROMLending 4 查询满足条件的元组 可以通过WHERE子句实现 WHERE子句常用的查询条件如右表所示 1 比较大小常用的比较运算符有 大于 大于等于 等于 不等于 例4 9 查询图书馆中出版社编号为01出版的所有图书的书名 SELECTTitleFROMBookWHEREP 01 2 确定集合用IN NOTIN 来查找属性值属于 不属于 指定集合的元组 例4 11 查询图书馆中出版社编号为01 02 03出版的图书书名和单价 SELECTTitle PriceFROMBookWHEREP IN 01 02 03 3 确定范围用BETWEEN AND NOTBETWEEN AND 来查找属性值在 不在 指定范围内的元组 例4 12 查询图书馆中单价在24 30元 包括24元和30元 之间的图书的书名和单价 SELECTTitle PriceFROMBookWHEREPriceBETWEEN24AND30 4 字符匹配用LIKE来实现字符的匹配 其语句格式为 NOT LIKE 说明 可以包含有通配符 和 其中 百分号 代表任意长度 可以为零 的字符串 下横线 代表任意单个字符 或汉字 例4 13 查询图书馆中所有以 数据库系统 五个字开头的图书书名和编号 SELECTTitle B FROMBookWHERETitleLIKE 数据库系统 例4 14 查询图书馆中所有以 数据库系统 开头且全名为7个汉字的图书书名 SELECTTitleFROMBookWHERETitleLIKE 数据库系统 5 空值的查询用NULL来判断例4 15 查询缺少成绩的学生学号及课程号 SELECTS C FROMSCWHEREGradeISNULL 6 组合条件的查询用AND和OR来连接多个查询条件 其中AND的优先级高于OR 不过 用户可以用括号来改变优先级 例4 16 查询图书馆中出版社编号为02出版的且单价在25元以下的图书书名及编号 SELECTTitle B FROMBookWHEREP 02 andPrice 25 5 分组查询用GROUPBY子句实现 将查询结果表按某一列或多列值分组 值相等的为一组 例4 17 统计图书馆中各个出版社出版的图书数量 SELECTP COUNT B FROMBookGROUPBYP 6 排序查询用ORDERBY子句实现 将查询结果按照一个或多个属性列的升序 ASC 或降序 DESC 排列 缺省则为升序 例4 18 查询图书馆中图书的基本情况 查询结果按单价降序排列 SELECT FROMBookORDERBYPriceDESC 7 属性列运算查询可以用几种基本的算术运算符来查询数据 常用的有 加 减 乘 除 例4 19 查询图书馆中所有图书书名 编号及借出的数量 SELECTTitle B CopyNumber StocksFROMBook 8 别名查询 1 属性在查询输出的列名与基本表中列名不一致时 可以根据用户的需要 用 旧属性名AS新名 形式来完成该操作 2 关系有时 一个表在查询语句中被多次调用时 为了区别不同的调用 应用 表名AS新表名 形式给每次使用标上不同的名字 例4 20 查询借了图书编号为0001和0002的借阅者编号 SELECTX R FROMLENDINGASX LENDINGASYWHEREX R Y R ANDX B 0001 ANDY B 0002 SELECTX R FROMLendingXINNERJOINLendingYONX R Y R WHERE X B 0001 AND Y B 0002 4 3 3多表查询1 无条件多表查询无条件多表查询是将各表的记录以 笛卡尔积 的方式组合起来 2 等值多表查询当连接两个表或两个以上的条件为比较运算符的 时 称为等值连接 例4 21 查询图书馆中每本图书及其出版社的情况 SELECTBook Publisher FROMBook PublisherWHEREBook P Publisher P 3 非等值多表查询当连接两个表或两个表以上的条件为比较运算符 称为非等值连接 4 组合条件多表查询在连接查询中 WHERE子句中有多个连接条件时 称为组合条件多表查询 例4 22 查询中南大学出版社出版的且单价在25元以下的所有图书 SELECTBook FROMBookINNERJOINPublisherONBook P Publisher P WHERE Publisher PUBLISHER 中南大学出版社 AND Book Price 25 4 3 4嵌套查询1 带IN的嵌套查询IN运算符可以测试表达式的值是否与子查询返回集中的某一个相等 例4 23 查询图书馆中中南大学出版社出版的图书编号 书名及单价 SELECTB Title PriceFROMBookWHERE P IN SELECTP FROMPublisherWHEREPublisher 中南大学出版社 2 带ANY的嵌套查询将表达式的值与查询返回集中的任一值相比较 只要其中有一个满足条件就返回TRUE 例4 24 查询图书馆中比出版社编号为01某一本书单价低的书号 书名及单价 SELECTB Title PriceFROMBookWHERE Price ANY SELECTPriceFROMBookWHEREP 01 3 带ALL的嵌套查询将表达式的值与查询返回集中的所有值相比较 只有所有值都满足条件才返回TRUE 例4 25 查询图书馆中比出版社编号为01的所有书的单价低的书号 书名及单价 SELECTB Title PriceFROMBookWHEREPrice ALL SELECTPriceFROMBookWHEREP 01 4 带比较运算符的嵌套查询例4 26 查询图书馆中中南大学出版社出版的图书编号 书名及单价 SELECTB Title PriceFROMBookWHEREP SELECTP FROMPublisherWHEREPublisher 中南大学出版社 5 带EXISTS的嵌套查询EXISTS表示一个子查询至少返回一行时条件成立 例4 27 查询图书馆中中南大学出版社出版的图书编号 书名及单价 SELECTB Title PriceFROMBookWHEREEXISTS SELECT FROMPublisherWHEREP Book P ANDPublisher 中南大学出版社 4 3 5集合查询集合操作主要包括并 交 差等操作 标准SQL中只提供了并操作 没有直接提供交 差操作 但可以用其它来办法来实现 1 并操作的嵌套查询例4 28 查询图书馆中出版社编号为01出版及单价不大于25元的图书 SELECT FROMBookWHEREP 01 UNIONSELECT FROMBookWHEREPrice 25 2 交操作的嵌套查询例4 29 查询图书馆中出版社编号为01出版与单价不大于25元的图书的交集 等价于 查询出版社编号为01出版且单价不大于25元的图书 SELECT FROMBookWHERE P 01 AND Price 25 3 差操作的嵌套查询例4 30 查询图书馆中出版社编号为01出版与单价不大于25元的图书的差集 等价于 查询出版社编号为01出版与单价大于25元的图书 SELECT FROMBookWHEREP 01 ANDPrice 25 4 3 6函数查询 1 COUNT函数用来统计一个表中有多少条记录例4 31 查询图书馆中图书总数 SELECTCOUNT FROMBook 2 SUM函数用来计算字段值的和例4 32 计算所有图书的总金额 SELECTSUM Price FROMBook 3 AVG函数用来求某个字段所有值的平均值例4 33 计算图书馆中所有图书的单价的平均值 SELECTAVG Price FROMBook 4 MAX函数用来求某一列中值的最大值 例4 34 查询图书馆中单价最贵的图书 SELECTMAX Price FROMBook 5 MIN函数用来求某一列中值的最小值 例4 35 查询图书馆中单价最低的图书 SELECTMIN Price FROMBook 4 4数据更新4 4 1插入数据 1 插入单个元组插入单个元组的INSERT语句的格式为 INSERTINTO VALUES 说明 1 其功能是将新元组插入指定表中 2 其中新记录属性列1的值为常量1 属性列2的值为常量2 INTO子句中没有出现的属性列 新记录在这些列上将取空值 3 在表定义时说明了NOTNULL的属性列不能取空值 否则会出错 4 如果INTO子句中没有指明任何列名 则新插入的记录必须在每个属性列上均有值 例4 36 将一个新借阅人记录 读者编号 0006 姓名 李冬 是否学生 否 密码 111111 已借阅数量 0 Email ld PhoneNumber 07332622200 插入到Reader表中 INSERTINTOReaderVALUES 0006 李冬 否 111111 0 ld 07332622200 2 插入子查询结果子查询不仅可以嵌套在SELECT语句中 用以构造父查询的条件 也可以嵌套在INSERT语句中 用以生成要插入的批量数据 插入子查询结果的INSERT语句的格式为 INSERTINTO 子查询 例4 37 求图书馆中每个出版社的图书总数 并把结果存入数据库 CREATETABLEBookNumber P CHAR 2 Book sumINT 然后对出版社分组求图书总数 再把出版社编号和图书总数存入新表中 INSERTINTOBookNumber P Book Sum SELECTP COUNT B FROMBookGROUPBYP 4 4 2删除数据删除数据的语句格式如下 DELETEFROM WHERE 说明 1 DELETE语句的功能是从指定表中删除满足WHERE子句条件的所有元组 2 如果省略WHERE子句 表示删除表中全部元组 但表的定义仍在字典中 3 DELETE语句删除的是表中的数据 而不是关于表的定义 例4 38 删除图书编号为0003的图书记录 DELETEFROMBookWHEREB 0003 4 4 3修改数据修改数据的语句格式如下 UPDATESET WHERE 说明 1 其功能是修改指定表中满足WHERE子句条件的元组 2 SET子句给出的值用于取代相应的属性列值 3 如果省略WHERE子句 则表示要修改表中的所有元组 例4 40 将图书馆中图书编号为0002的单价改为30元 UPDATEBookSETPrice 30WHEREB 0002 例4 41 将图书馆中所有图书的复本数量增加1 UPDATEBookSETCopyNumber CopyNumber 1 4 5视图管理4 5 1视图的基本概念 1 视图的简介 视图是由基表或其他视图导出的虚表 只在数据字典中保留其逻辑定义 而基表是实际存储在数据库中的 视图由用户定义 并为用户所使用 视图一经定义 对它的操作就如同基表一样 用户可在基表上进行操作 也可在视图上进行操作 在视图上进行的操作交由DBMS转化为对相应基表的操作 视图既是表 但又不同于基表 视图与表的最大区别是表包含实际的数据 并消耗物理存储 而视图不包含数据 且除了需要存储提供视图定义的查询语句外 不需要其他存储 2 视图的作用 1 视图提供了逻辑数据独立性 2 简化了用户观点 3 数据的安全保护功能 4 5 2视图的基本操作 1 定义视图定义视图的语句格式如下 CREATEVIEW AS子查询 WITHCHECKOPTION 说明 1 是要建的视图名称 2 子查询可以是任意复杂的SELECT语句 但通常不允许含有ORDERBY子句和DISTINCT短语 3 WITHCHECKOPTION表示对视图进行UPDATE INSERT和DELETE操作时要保证修改 插入和删除的记录满足视图定义中的子查询中的条件表达式 例4 42 建立出版社编号为02的图书的视图 CREATEVIEWHEP BookASSELECTB Title PriceFROMBookWHEREP 02 2 删除视图删除视图的语句格式如下 DROPVIEW 说明 视图被删除后 其定义将从数据字典中删除 例4 43 删除视图HEP Book DROPVIEWHEP Book 3 查询视图在SQL中 视图一经定义后 用户对视图的查询与对基本表的查询是一样的 例4 44 在高等教育出版社的视图中找出单价小于30元的图书 SELECTB TitleFROMHEP BookWHEREPrice 30 4 更新视图视图的更新主要包括 INSERT UPDATE和DELETE三类操作 执行上述操作时 必须遵循以下三条规则 1 如果一个视图是从多个关系使用联接连接操作导出的 那么不允许对这个视图执行更新操作 2 如果在导出视图的过程中 使用了分组和聚合操作 那么不允许对这个视图执行更新操作 3 如果视图是从单个关系使用选择 投影操作导出的 并且包含了关系的主键或多个候选键 那么这样的视图称为 行列子集视图 并且可执行更新操作 例4 45 将高等教育出版社视图HEP Book中书名为数据库系统教程的单价改为28元 UPDATEHEP BookSETPrice 28WHERETitle 数据库系统教程 4 6数据控制4 6 1授予权限 授予权限的语句格式如下 GRANT ON TO WITHGRANTOPTION 说明 1 其功能是将对指定操作对象的指定操作权限授予指定的用户 2 对于不同类型的操作对象有不同的操作权限 常见的操作权限如下 1 对属性列和视图的操作权限有 查询 SELECT 插入 INSERT 修改 UPDATE 删除 DELETE 以及这四种权限的总和 ALLPRIVILEGES 2 对基本表的操作权限有 查询 SELECT 插入 INSERT 修改 UPDATE 删除 DELETE 修改表 ALTER 和建立索引 INDEX 以及这六种权限的总和 ALLPRIVILEGES 3 对数据库可以有建立表 CREATETAB 的权限 该权限属于DBA 可由DBA授予普通用户 普通用户拥有此权限后可以建立基本表 基本表的属主 Owner 拥有对该表的一切操作权限 3 接受权限的用户可以是一个或多个具体用户 也可以是PUBLIC 即全体用户 4 如果指定了WITHGRANTOPTION子句 则获得某种权限的用户还可以把这种权限再授予其他的用户 如果没有指定WITHGRANTOPTION子句 则获得某种权限的用户只能使用该权限 但不能传播该权限 例4 46 将对Book表的查询权限授给用户User1 GRANTSELECTONBookTOUser1例4 47 将对Book表的所有权限授给User2和User3 GRANTALLPRIVILEGESONBookTOUser2 User3 4 6 2回收权限 收回权限的语句格式如下 REVOKE O