关系数据库语言SQL.ppt

第三章第三章 关系数据库语言关系数据库语言SQLSQL z本章提纲： v一. SQL概述 (3.1) v二. SQL的功能 w数据定义功能 (3.2) w数据查询功能 (3.3) w数据修改功能 (3.4) w数据控制功能 (第6, 7章) v三. 视图 (3.5) 目录目录* * z3.1 SQL概述 3.1.1 SQL产生与标准 3.1.2 SQL功能与组成 3.1.3 语句类型与数据类型 z3.2 数据定义 z3.3 数据查询 z3.4 数据修改 z3.5 视图管理 SQLSQL的产生与标准的产生与标准 z什么是SQL？ vStructured Query Language (结构化查询语言) 的缩写 vSQL是一种数据库语言提供了对关系数据库的定义、 操纵 (查询和修改)、控制等功能 vSQL是关系数据库的标准语言 主流的关系数据库管 理系统软件，虽然提供的（用户与数据库交互用的）数据 库语言各有不同，但都要遵循这一标准。不管是Oracle 的PL/SQL，还是SQL Server的Transact-SQL，它们与 标准SQL都是大同小异的。 zSQL的历史 v1974年，由Boyce和Chamber提出 v1975-1979年，在System R上实现，由IBM的San Jose 研究室研制，称为Sequel SQLSQL的产生与标准的产生与标准 zSQL的标准化 v相关组织 wANSI (American National Standard Institute) 美国国家标准协会 wISO (International Organization for Standardization) 国际标准化组织 v相关版本 wSQL-86： l“数据库语言SQL”，确立了SQL（击败其它竞争对手） 成为关系数据库的标准语言。 wSQL-89： l“具有完整性增强的数据库语言SQL”，增加了对完整性 约束的支持 SQLSQL的产生与标准的产生与标准 wSQL-92(SQL-2) l是SQL-89的超集，增加了许多新特性，如新的数据类 型，更丰富的数据操作，更强的完整性、安全性支持等 。 wSQL-99(SQL-3) l增加了对对象模型的支持 wSQL-2003 l标志着传统关系模型到非关系模型（对象模型和XML模 型）的第二次重要扩充 即本章学 习 的SQL SQLSQL的特点的特点 zSQL的特点 v1. 综合统一性 w功能的统一 ：提供数据定义、操纵和控制功能，集 DDL、DML和DCL等语言于一体。 w操作过程的统一：都是对关系进行操作，包括操作对象和 结果 v2. 高度非过程化 w用户只需提出“做什么”，而无须说明“怎么做”，不必介入 具体的操作实现过程（由系统自动处理） v3. 面向集合的操作方式 w一次一集合：每次操作的对象和结果都是关系 (记录集合) w与之相反的是“一次一记录”: 每次操作只能处理一条记录， 要通过循环等手段才能处理一个记录集合 SQLSQL的特点的特点 v4. 两种使用方式，统一的语法结构 w自含式SQL：数据库管理系统自带，供用户与数据库联机 交互使用，所以又称为联机式SQL。 w嵌入式SQL：嵌入到高级语言里面，供程序员用这些高级 语言开发数据库应用程序时使用。 v5. 语言简洁，易学易用 SQLSQL规定的三级模式结构规定的三级模式结构 z数据库的三级模式 v物理模式 w整体数据库的物理结构 w描述所有数据是如何在硬盘等介质上存储的 v逻辑模式 w整体数据库的逻辑结构 w描述所有数据的结构及所有数据间的联系 v子模式 w部分数据库的逻辑结构 w描述某个特定用户能看到的那一部分数据的结构及它们之 间的联系 SQLSQL规定的三级模式结构规定的三级模式结构 zSQL中所规定的数据库结构 (三级模式结构) 关系R2关系R4关系R3 (逻辑)模式 关系R1 SQL用户1SQL用户2SQL用户3SQL用户4 视图V1 视图V2 外模式 存储文件S1存储文件S2存储文件S3内模式 存储物理关 系和索引 SQLSQL的数据类型的数据类型 zSQL-92标准中规定的数据类型 vchar (n)：长度固定为n的字符串 vvarchar (n)：可变长度的字符串，最大长度为n vint：整数 vsmallint：小整数 vnumeric (p, q)：总共不多于p位，小数点右边不多于q位 的定点数 vreal：浮点数 vdouble precision：双精度浮点数 vfloat (n)：精度至少为n位的浮点数 vdate：日期 (年, 月, 日) vtime：时间 (小时, 分, 秒) vinterval：两个date或time类型数据之间的差 SQLSQL的功能的功能 zSQL的功能和相应的操作语句 SQL功能 (子语言) 操作语句备注 数据定义 (DDL) CREATE，ALTER，DROP 可定义的对象包 括数据库，表， 索引，视图等 SQLSQL的功能的功能 zSQL的功能和相应的操作语句 SQL功能 (子语言) 操作语句备注 数据定义 (DDL) CREATE，ALTER，DROP 可定义的对象包 括数据库，表， 索引，视图等 数据操纵 (DML) SELECT又称数据查询 INSERT，UPDATE，DELETE又称数据修改 SQLSQL的功能的功能 zSQL的功能和相应的操作语句 SQL功能 (子语言) 操作语句备注 数据定义 (DDL) CREATE，ALTER，DROP 可定义的对象包 括数据库，表， 索引，视图等 数据操纵 (DML) SELECT又称数据查询 INSERT，UPDATE，DELETE又称数据修改 数据控制 (DC L) GRANT，REVOKE， 包括事务控制， 安全性控制等 目录目录* * z3.1 SQL概述 z3.2 数据定义 3.2.1 SQL模式定义 3.2.2 基本表定义 3.2.3 索引定义 z3.3 数据查询 z3.4 数据修改 z3.5 视图管理 SQLSQL的数据定义功能的数据定义功能 zSQL提供各种对象的定义 v数据库/模式 wSQL中，一个关系数据库(的模式)可以称为SQL模式或模式 。包含为满足应用需要而建立的多个基本表、视图、索引 等。 v关系 v视图 v索引 v用户域(数据类型) SQLSQL的数据定义功能的数据定义功能 创建修改撤销 模式Create SchemaDrop Schema 数据库Create Database Drop Database 关系Create TableAlter TableDrop Table 视图Create ViewDrop View 索引Create IndexDrop Index 域Create DomainDrop Domain 模式的定义模式的定义 z新建模式 v格式 create schema 模式名 authorization 所有者用户名 v示例 create schema Class_schema authorization DBA z撤销模式 v格式 drop schema 模式名 cascade|restrict v示例 drop schema Class_db cascade 数据库的定义数据库的定义 z新建数据库 v格式 create database 数据库名 v示例 create database Class_db z撤销数据库 v格式 drop database 数据库名 v示例 drop database Class_db ( (基本表基本表) )关系的定义关系的定义 z新建(基本表)关系 v命令格式 create table 关系名 ( 属性名 域 default 缺省值 null | not null , 属性名 域 default 缺省值 null | not null , primary key (属性名 , 属性名) , foreign key (属性名 , 属性名) references 被参照关系名 (属性名 , 属性名) , check (条件) ) 格式：A表示A出现0或1次；A表示A出现0或1或多次 关系的属性, 1或多个 关系的主 码, 0或1个 关系的外 部码, 0或 多个 每一个元组（内部属 性值）都必须满足的 约束条件（插入或更 新元组时检查） ( (基本表基本表) )关系的定义关系的定义 v例： create table S ( SNO VARCHAR (4), SNAME PERSON-NAME not null, AGE SMALLINT default 22, SEX VARCHAR (1), primary key (SNO), check ( AGE0 ) ) SNOSnameAGE SEX S ( (基本表基本表) )关系的定义关系的定义 v例： create table C ( CNO VARCHAR(4), CNAME VARCHAR(10) not null, TEACHER VARCHAR (8), primary key (CNO), ) SNO Sname AGE SEX S CNO Cname Teacher C ( (基本表基本表) )关系的定义关系的定义 v例： create table SC ( SNO VARCHAR(4), CNO VARCHAR(4), SCORE INT, primary key (SNO, CNO), foreign key (SNO) references S (SNO), foreign key (CNO) references C (CNO), check ( (SCORE IS NULL) OR (SCORE BETWEEN 0 AND 100) ) ) SNO Sname AGE SEXCNO Cname Teacher SCSC SNO CNO RESULT ( (基本表基本表) )关系的定义关系的定义 z修改关系 (的结构如属性等，而非内容如元组等) v命令格式 alter table 关系名 add 属性名 域 default 缺省值 not null drop 属性名 modify 属性名 新域 default 新缺省值 not null v例 alter table S add DEPT VARCHAR(20) not null alter table SC modify RESULT SMALLINT ( (基本表基本表) )关系的定义关系的定义 z撤销关系 v命令格式 drop table 关系名 v示例 drop table SC vWarning! ：撤销关系的定义后，关系的结构(字段)和内 容(数据记录)都不再存在。相关索引，由它导出的视图也 都可能被连带撤销。 索引的定义索引的定义 z新建索引 v格式 create unique cluster index 索引名 on 表名 (属性名 ,属性名 , ) wunique 表示唯一索引；cluster表示聚簇索引 v示例 create unique index index_on_s_s# on S(S#) z撤销索引 v格式 drop index 索引名 v示例 drop index index_on_s_s# 目录目录* * z3.1 SQL概述 z3.2 数据定义 z3.3 数据查询 z3.4 数据修改 z3.5 视图管理 SQLSQL的数据查询功能的数据查询功能 z统一的查询语句：Select 语句 z基本结构 vSelect 子句 (投影) vFrom 子句 (笛卡儿积) vWhere 子句 (选择) z汇合基本结构 v汇合多个基本结构查询出的结果 (集合运算) z扩展基本结构 v增加 Group By子句 (聚集运算) v增加 Order By子句 (对结果排序) v嵌套子查询 v连接关系 SelectSelect查询的基本结构查询的基本结构 z基本结构： Select A1 , A2 , , An From R1 , R2 , , Rm Where P vA1 , A2 , , An : 属性 vR1 , R2 , , Rm : 关系 vP : 条件(布尔表达式) z近似等价关系运算 vA1 , A2, , An ( p ( R1 R2 Rm ) ) z运算和次序 vFrom (笛卡儿积) Where (选择) Select (投影) Select Select 子句子句 z作用：从(From子句列出的)关系中选出指定属性 v近似于投影运算 z应用示例： vS： 学生关系 v问：所有学生的姓名和年龄? 姓名 性别 年龄 小张男23 小李女22 小陈男22 小刘女22 小孙男22 姓名 年龄 小张23 小李22 小陈22 小刘22 小孙22 Select 姓名, 年龄 From S S 保留指定的属性 Select Select 子句子句 z要点 v属性列表中，可以用表达式来构造的新属性 w相当于广义投影运算 v例 wS: 学生关系 w问: 每个学生的出生年份 姓名 性别 年龄 小张男23 小李女22 小陈男22 小刘女22 小孙男22 S Select 姓名, 2013 年龄 From S 姓名? 小张 1990 小李 1991 小陈 1991 小刘 1991 小孙 1991 Select Select 子句子句 v用表达式构造的新属性无属性名，但可采用以下方式命名 表达式 as 属性名 v原有属性也可用如下方式重新命名 旧属性名 as 新属性名 姓名 性别 年龄 小张男23 小李女22 小陈男22 小刘女22 小孙男22 学生姓名 出生年份 小张1990 小李1991 小陈1991 小刘1991 小孙1991 S Select 姓名 as 学生姓名, 2013 年龄 as 出生年份 From S Select Select 子句子句 z要点 v如果列出的关系有多个，且有同名属性。则指定这些同名 属性时，必须加关系名前缀：关系名.属性名。其它属性 可加可不加 v以下SQL正确么? 如正确，结果关系的属性名是什么? Select 姓名, 性别, 成绩 From R, S Select R.姓名, S.姓名, S.性别 From R, S 姓名 性别 年龄 张军男23 王红女22 刘朝男22 S 姓名 课程 成绩 王红 物理70 王红 数学80 张军 数学90 张军 物理 100 刘朝 数学80 R Select Select 子句子句 z要点 v在属性列表中，*号表示所有关系的所有属性，关系名.*表 示某个关系的所有属性 v思考，下列SQL的结果包含哪些属性? 姓名 性别 年龄 张军男23 王红女22 刘朝男22 S.姓名课程 成绩R.姓名性别年龄 Select * From S, R S 姓名 课程 成绩 王红 物理70 王红 数学80 张军 数学90 张军 物理 100 刘朝 数学80 R 姓名 性别 年龄 成绩 Select S.*, R.成绩 From S, R 思考思考 zselect 子句等价于投影运算么? Select Select 子句子句 z要点 vSelect后面，属性列表前，可加上关键字all或者distinct wall关键字表明结果中不消去重复元组 wdistinct关键字表明结果中消去重复元组 w没说明all或distinct的情况下，默认是all，即不消去重复行 Select all 课程 From R 姓名 课程 成绩 王红 物理70 王红 数学80 张军 数学90 张军 物理 100 刘朝 数学80 R 课程 物理 数学 数学 物理 数学 Select distinct 课程 From R 课程 物理 数学 思考思考 zselect 子句等价于投影运算么? vselect distinct 等价于投影。select 或者 select all都未 消去重复 Where Where 子句子句 z作用：选出满足条件的行 v等价于选择运算 z例： vS ：学生关系 v问：所有男学生的信息? S 筛选满足条件的行 Select From S Where 性别=男 学号 姓名性别年龄 13 小张 男23 02 小李 女22 06 小陈 男21 12 小刘 女22 23 小孙 男21 学号 姓名性别年龄 13 小张 男23 06 小陈 男21 23 小孙 男21 Where Where 子句子句 z在Where子句的条件中可能出现的运算符号 v比较运算符 w、 、=、 v逻辑运算符 wand，or，not v范围运算 w格式1： between 下界 and 上界 (下界b)或0次 (ab)或0次(a数学 姓名 王红 张军 刘朝 集合运算集合运算 z特别要注意 v虽然SQL标准中支持Intersect和Except，许多实际的 DBMS产品如SQL Server 2000及以前的版本，ACCESS 并不提供这两种运算符 v在这些DBMS实现中差运算或者交运算，应使用exist/in 和not exist/not in形式的嵌套子查询 扩展扩展SelectSelect查询语句查询语句 zGroup by 子句 zHaving子句 zOrder by 子句 z嵌套子查询 z连接关系 Group By Group By 子句子句 z格式 z Select A1 , A2 , , An From R1 , R2 , , Rm Where P Group By 属性1, 属性2, zGroup By子句的作用：在Where子句筛选出元组后， 对它们分组 v分组属性: 属性1，属性2， z同时Select 子句的作用发生变化：对分组进行统计 v每个分组在结果中被统计为一个元组 v这时在Select 子句出现的属性只能是： 分组属性 聚集函数(任意属性) 由和组成的表达式 Group By Group By 子句子句 v例： w关系R: 选修成绩 w问: 每门课程的平均和最高成绩? 姓名 课程 成绩 王红 物理70 王红 数学90 张军 数学90 张军 物理 100 刘朝 物理80 R Select 课程, Avg(成绩) as 平均成绩, Max(成绩) as 最高成绩 From R Group By 课程 课程 平均成绩 最高成绩 物理83.3100 数学9090 Group By Group By 子句子句 v例： w关系R: 选修成绩 w问: 平均成绩和最高成绩? 姓名 课程 成绩 王红 物理70 王红 数学90 张军 数学90 张军 物理 100 刘朝 物理80 R Select Avg(成绩) as 平均成绩, Max(成绩) as 最高成绩 From R 平均成绩 最高成绩 86100 Having Having 子句子句 z格式(出现在group by子句后面) z Select A1 , A2 , , An From R1 , R2 , , Rm Where P (元组限定条件) Group By 属性1, 属性2, Having Q (分组限定条件) zHaving子句作用：在分组后，筛选满足条件Q的分组 v在分组限定条件中出现的属性只能是以下形式： 分组属性 聚集函数(任意属性) zHaving子句只能配合Group By子句使用，而不能单独 出现 Having Having 子句子句 zHaving子句中的条件和Where子句中条件的不同 vWhere子句中的条件用于限定元组，施加在单个元组上 w不满足条件的元组将不会参与到下一步的分组运算(Group By子句)或投影运算(Select 子句)等 vHaving子句中的条件用于限定Group By子句产生的各个 分组，施加在整个分组上 w不满足条件的分组，将不会参与下一步的统计运算(Select 子句) Having Having 子句子句 v例： w关系R: 选修成绩 w问: 平均成绩大于85的学生姓名? 姓名 课程 成绩 王红 物理70 王红 数学90 张军 数学90 张军 物理 100 刘朝 物理80 R Select 姓名 From R Group By 姓名 Having Avg(成绩) 85 姓名 张军 Having Having 子句子句 w问: 成绩大于85的学生