ch3-关系数据库语言SQL.ppt

第3章 关系数据库语言SQL 3.1 SQL概述 3.2 SQL的数据定义 3.3 SQL的数据查询 3.4 SQL的数据更新 3.5 视图的定义和对视图的操作 3.6 嵌入式SQL（自学） 小结 1 3.1 SQL概述 nSQL（ Structured Query Language，结构化查询语言 ）是关系数据库的标准语言，是介于关系代数和元组 关系演算之间的一种语言。 只要是关系数据库（如：FoxPro、SQL Server、 Oracle、 DB2、Sybase等）均支持SQL语言。 nSQL标准（ANSI和ISO）发展历程： SQL82SQL86SQL92（SQL2）SQL99（SQL3） nSQL的主要特点： (1) 高度非过程化 (2) 面向集合的操作方式 (3) 简洁易学，灵活易用 2 3.1 SQL概述（续1） nSQL数据库的三级体系结构 n视图（View）对应外模式（用户模式、子模式） n基本表（Base Table）对应模式（逻辑模式、概念模式 ） n存储文件（Stored File）对应内模式（存储模式、物理 模式） 内模式 模式 外模式 存储文件1存储文件2存储文件3存储文件4 基本表1基本表2基本表3基本表4 视图1视图2 用户1用户2用户3用户4 3 3.1 SQL概述（续2） nSQL数据库的三级体系结构 (1) 一个SQL模式由表、授权、规则、约束等组成。 用户面对的是数据库的逻辑模式、而非物理模式，故 SQL模式也称为逻辑数据库。 4 3.1 SQL概述（续3） 特殊的存储过程，当用户表中数据改变时 ，该存储过程被自动执行 触发器 存放在服务器的预先编译好的一组T-SQL 语句 存储过 程 为列提供的默认值默认值 用于对表中的列定义完整性规则约束 为数据快速检索提供支持索引 由表或其他视图导出的虚拟表视图 定义列或变量的数据类型，SQL Server提 供了系统数据类型，并允许用户自定义数 据类型 数据类 型 由行和列构成的集合，用来存储数据表 说明成分 SQL Server 2000数据库的逻辑成分 5 3.1 SQL概述（续4） nSQL数据库的三级体系结构 (1) 一个SQL模式由表、授权、规则、约束等组成。 用户面对的是数据库的逻辑模式、而非物理模式，故 SQL模式也称为逻辑数据库。 (2) 每个SQL表由行（Row）和列（Column）构成， 行对应元组，列对应属性。 表有三种类型：基本表、视图和导出表。基本表 是实际存储在数据库中的表，视图是由若干基本表或 其他视图构成的虚表，导出表是执行查询后产生的表 。 (3) 一个基本表可以对应一个或多个存储文件，一个 存储文件也可以存放一个或多个基本表。 6 3.1 SQL概述（续5） nSQL数据库的三级体系结构 注：在SQL Server中，物理数据库中只有一个存储 文件，称为主数据文件，文件扩展名为.mdf。另外， 还有一个日志文件，扩展名为.ldf。 (4) 用户可以用SQL语句对基本表和视图进行查询等操 作。在用户看来，两者是一样的，都是表。 (5) SQL用户既可以是人，也可以是应用程序。对于应 用程序而言，SQL语句嵌在宿主语言（如C，Java等） 的程序中使用。 nSQL语言的组成 包括四部分：数据定义（DDL）、数据操纵（DML ）、数据控制（DCL）、嵌入式SQL的使用规定。 7 3.1 SQL概述（续6） nSQL语言的组成 grant、revoke数据控制 insert、update、 delete 数据更 新 select数据查 询 数据操 纵 create、drop、alter数据定义 动词（语句）功能 8 3.1 SQL概述（续7） nSQL Server 2000的主要工具 1. 服务管理器：用于启动和停止SQL Server服务器 实例和SQL Server服务。 2. 企业管理器：对数据库服务器、数据库及数据库 对象（表、视图等）进行管理。 3. 查询管理器：用于交互输入T-SQL语句和存储过 程。 9 3.1 SQL概述（续8） nSQL Server 2000自带的数据库 1. master数据库：记录了SQL Server 2000的所有系统信息， 包括所有的其他数据库、登录账号和系统配置，是最重要的 系统数据库。 2. msdb数据库：是SQL Server Agent服务使用的数据库，即 如果要让警报、备份、复制等各项操作能够自动定期执行， 就需要启动SQL Server Agent服务。 3. model数据库：是SQL Server 2000为用户数据库提供的样 板，新的用户数据库都是该数据库的拷贝。 4. tempdb数据库：存储了SQL Server服务器运行期间SQL Server需要的所有临时数据，当服务停止后，tempdb中的数 据不被保存，即被全部清空。 5. pubs和northwind数据库是两个系统自带的供示例用的用 户数据库。 10 3.2 SQL的数据定义 包括对数据库、基本表、视图、索引的创建 和删除（撤销）。 drop indexcreate index索引 drop viewcreate view视图 alter table drop table create table基本表 drop databasecreate database数据库 修 改删 除创 建 操作方式 操作对象 SQL的数据定义语句 11 3.2 SQL的数据定义（续1） 3.2.1 SQL数据库的创建和删除 3.2.2 SQL的基本数据类型 3.2.3 基本表的创建、修改和删除 3.2.4 索引的创建和删除 12 3.2.1 SQL数据库的创建和删除 nSQL数据库（模式）的创建 语句形式： SQL Server：create database 数据库名 【例】create database st_co 注：在SQL Server数据库中，有界面和命令两种操 作方式。 nSQL数据库（模式）的删除 SQL Server：drop database 数据库名 【例】drop database st_co 3.2 SQL的数据定义 13 3.2.2 SQL的基本数据类型 n数值型 ninterger/int：SQL Server中，长度为 4 个字节，数 范围为-231(-2147483648)231-1(2147483647)。 n短整型smallint：SQL Server中，长度为2个字节， 数范围为-215(-32768)215-1(32767)。 n浮点型实数real、float(n)、double precision，用于 对实数的近似表示。 float(n)：n取值153。当n取124之间，长度为4 个字节，数范围为-3.4010383.401038，精度为7位 有效数字，等同于real；当n取2553之间，长度为8 个字节，数范围为-1.79103081.7910308，精度为 15位有效数字，等同于double precision。当省略n时表 示n=53。 14 3.2.2 SQL的基本数据类型（续1） n数值型 n定点型实数numeric(p,d)或decimal(p,d)，用于对实 数的精确表示。 其中p表示数字总位数（不包括符号、小数点），d 表示小数部分位数。 n字符串型 n定长字符串char(n)：n为字符串长度，取值18000 。当实际串长度n时，从串尾截断。当省略n时表示n=1。 n变长字符串varchar(n)：n为字符串的最大长度，取 值18000。字符串的长度为实际长度，不一定为n 。 3.2 SQL的数据定义 15 3.2.2 SQL的基本数据类型（续2） n二进制串型 n定长binary(n)：长度为n个字节的定长二进制数据。 n变长varbinary(n)：最大长度为n个字节的变长二进 制数据。 n时间型 n日期date：包含年、月、日，形式为YYYY-MM-DD 。 n时间time：包含时、分、秒，形式为HH:MM:SS。 3.2 SQL的数据定义 16 n基本表的创建 create table 基本表名 ( 列名1 数据类型, 列名2 数据类型, 列名n 数据类型, 完整性约束, ); 其中完整性约束主要有三种子句：主键子句（primary key）、外键子句（foreign key）和检查子句（check）。 【例】有三个基本表：S(S#, SName, Age, Sex) SC(S#, C#, Grade) C(C#, CName, Teacher) 3.2.3 基本表的创建、修改和删除 3.2 SQL的数据定义 17 【例】有三个基本表：S(S#, SName, Age, Sex) SC(S#, C#, Grade) C(C#, CName, Teacher) 创建表S：create table S( S# char(4) not null, SName char(8) not null, Age smallint, Sex char(1), primary key(S#) ) 注：not null表示该属性的值不能为空。 创建表C：create table C( C# char(4), CName char(10) not null, Teacher char(8), primary key(C#) ) 3.2.3 基本表的创建、修改和删除（续1 ） 3.2 SQL的数据定义 18 【例】有三个基本表：S(S#, SName, Age, Sex) SC(S#, C#, Grade) C(C#, CName, Teacher) 创建表SC： create table SC( S# char(4), C# char(4), Grade smallint, primary key(S#, C#), foreign key(S#) references S(S#), foreign key(C#) references C(C#), check(Grade between 0 and 100) ) 注：新建立的表为空表（没有数据），可利用insert语句 插入数据。 3.2.3 基本表的创建、修改和删除（续2 ） 3.2 SQL的数据定义 19 n基本表结构的修改 可增加新列、删除原有的列或修改数据类型、宽度等。 (1) 增加新列 alter table 表名 add 列名 数据类型 【例】alter table S add Address varchar(30) 注：新增加的列不能定义为not null。 (2) 删除原有列 SQL Server：alter table 表名 drop column 列名 【例】alter table S drop column Age 注：在删除一列之前，须先删除基于该列的所有索引和约束 。 (3) 修改原有列的类型、宽度 SQL Server：alter table 表名 alter column 列名 数据类型 3.2.3 基本表的创建、修改和删除（续3 ） 3.2 SQL的数据定义 20 n基本表结构的修改 【例】alter table S alter column SName char(20) 注：当表中尚无数据时，可以修改表结构，如更改列名 、列的数据类型、长度等属性。但当表中有数据后， 建议不要轻易更改表结构，特别不要改变数据类型， 以免产生错误。 n基本表的删除 drop table 表名 【例】drop table S 3.2.3 基本表的创建、修改和删除（续4 ） 3.2 SQL的数据定义 21 在早期的SQL标准（SQL86、SQL89）中，表没有 主键概念，可以用索引机制弥补。 索引（Index），类似于书的目录索引，是为表中 的给定属性建立物理存取路径的一种方法，缺点：把 数据库的逻辑结构和物理结构混在一起，破坏了数据 库的独立性。 因此，从SQL92标准开始，引入了主键的概念。 目前大多数关系数据库系统仍保留索引机制，但仅 限在查询时起作用。 3.2.4 索引的创建和删除 3.2 SQL的数据定义 22 n索引的创建 create unique index 索引名 on 表名(属性名) 当有unique时，表示作为索引的属性其值不能重复。 【例】create unique index SName_Index on S(SName) n索引的删除 SQL Server：drop index 表名.索引名 【例】drop index S.SName_Index 3.2.4 索引的创建和删除（续1） 3.2 SQL的数据定义 23 3.3 SQL的数据查询 3.3.1 select查询语句的基本形式 3.3.2 select语句完整的句法 3.3.3 select语句中的限定 3.3.4 基本表的连接操作 24 3.3.1 select查询语句的基本形式 n关系代数中最常用的式子是下列表达式： n在SQL中对应的语句（称为select语句）为： select A1, A2, , An from R1, R2, , Rm where F 该语句的执行过程： (1) 读取from子句中的表R1、R2、Rm，并执行笛卡 尔积操作； (2) 选取满足where子句中给出的条件表达式F的元组； (3) 输出select子句中的属性A1、A2、An。 3.3 SQL的数据查询 25 3.3.1 select查询语句的基本形式（续1） select A1, A2, , An from R1, R2, , Rm where F 另外，where子句的条件表达式F可使用如下运算符： n算术比较运算符：、=、=、50 order by 2 asc, Age desc 注：order by子句中的2表示select子句中的第二个属性 count(S#)。 3.3 SQL的数据查询 43 3.3.2 select语句完整的句法（续3） (3) 在基本表SC中检索平均成绩超过80分的学生学号 和平均成绩。 select S#, avg(Grade) from SC group by S# having avg(Grade)80 3.3 SQL的数据查询 44 3.3.3 select语句中的限定 1. select子句中的限定 select子句描述查询输出的列名或列表达式，其形式为： select all | distinct 列名或列表达式 | * distinct表示将重复的列从结果中去除；默认为all，表示 将重复的列保留在结果中。 星号*表示输出from子句中表的所有列。 列表达式中除使用聚合函数avg、min、max、sum、 count外，还可使用+、-、*、/等算术运算符。 【例】在学生S(S#, SName, Age, Sex)中检索每个学生的学号 、姓名和出生年份。 select S#, SName, 2009-Age from S 3.3 SQL的数据查询 45 3.3.3 select语句中的限定（续1） 2. 条件表达式中的算术比较操作 在where子句的行条件表达式和having子句的组条 件表达式中除使用算术比较运算符外，还可使用 “betweenand”比较运算符限定值的范围。 【例】在基本表S中检索1820岁的学生姓名。 select SName from S where age=18 and age、 =、=、some( select grade from SC where S#=S4 ) 【例】在表SC中检索平均成绩最高的学生学号。 select S# from SC group by S# having avg(Grade)=all( select avg(grade) from SC group by S# ) 注：x in R x=some(R)，x not in R x80 3.3 SQL的数据查询 55 3.3.4 基本表的连接操作 SQL语言可以通过from子句直接实现F连接。在SQL Server中实现F连接的from子句形式为： (1) from 表名 F连接类型 表名 on F连接条件 其中F连接类型为：inner join（内连接） left outer join（左外连接） right outer join（右外连接） full outer join（完全外连接） 内连接：从两个表的笛卡儿积中选取满足on所指定的F连 接条件的那些行（元组）。 左外连接：结果中除包括满足F连接条件的那些行外，还 包括左表中原本该舍弃的那些行（在右表的属性上为空值） 。 右外连接：结果中除包括满足F连接条件的那些行外，还 包括右表中原本该舍弃的那些行（在左表的属性上为空值） 。 3.3 SQL的数据查询 56 3.3.4 基本表的连接操作（续1） (1) from 表名 F连接类型 表名 on F连接条件 完全外连接：结果中除包括满足F连接条件的那些行外， 还包括两个表中原本该舍弃的那些行（分别在对方表的属性 上为空值）。 内连接： select * from X inner join Y on X.B=Y.B and X.C=Y.C 左外连接： select * from X left outer join Y on X.B=Y.B and X.C=Y.C 3.3 SQL的数据查询 c 3 b 3 a 3 c 2 b 2 a 2 c 1 b 1 a 1 CBA d 4 c4b 4 d 2 c2b 2 d 1 c1b 1 DCB 【例】表X： 表Y： 57 3.3.4 基本表的连接操作（续2） (1) from 表名 F连接类型 表名 on F连接条件 右外连接： select * from X right outer join Y on X.B=Y.B and X.C=Y.C 完全外连接： select * from X full outer join Y on X.B=Y.B and X.C=Y.C (2) from 表名 cross join 表名 称为交叉连接，实际上是笛卡尔积。交叉连接不能有on 条件，且不能带where子句。 3.3 SQL的数据查询 c 3 b 3 a 3 c 2 b 2 a 2 c 1 b 1 a 1 CBA d 4 c4b 4 d 2 c2b 2 d 1 c1b 1 DCB 【例】表X： 表Y： 58 3.4 SQL的数据更新 数据更新包括数据的插入、修改和删除。 1. 数据插入 (1) 单元组的插入 insert into 基本表名(列名序列) values (元组值) (2) 多元组的插入（SQL Server中不能用） insert into 基本表名(列名序列) values (元组值), (元组值), , (元组值) (3) 查询结果的插入 insert into 基本表名(列名序列) 59 3.4 SQL的数据更新（续1） 1. 数据插入 (4) 表的插入（SQL Server中不能用） insert into 基本表名1(列名序列) table 基本表名2 【例】在基本表SC中插入数据的例子。 (1) 插入一个元组 insert into SC(S#, C#, Grade) values (S4, C2, 90) 注：当插入一个元组的全部属性时，列名部分可省略。 (2) 插入一个元组的部分属性 insert into SC(S#, C#) values (S5, C1) 60 3.4 SQL的数据更新（续2） 【例】在基本表SC中插入数据的例子。 (3) 插入多个元组 insert into SC values (S2, C1, 85), (S2, C2, 90), (S2, C3, 70) (4) 插入查询结果 insert into S_Grade(S#, Grade) select S.S#, Grade from S, SC where S.S#=SC.S# and Sex=男 将男同学的学号和成绩存入一个已建好的表S_Grade中 。 (5) 若某一个班的选课情况已存在表SC4(S#, C#)中，则把 表SC4的数据插入到表SC中，可用下列语句： insert into SC(S#, C#) table SC4 61 3.4 SQL的数据更新（续3） 2. 数据删除 delete from 基本表名 where 条件表达式 表示：从基本表中删除满足条件表达式的元组。若没有 where子句，表示删除表中所有元组。 注：delete语句只能从一个基本表中删除元组。 【例】把课程名为数学的成绩从基本表SC中删除。 delete from SC where C# in ( select C# from C where CName=数学 ) 62 3.4 SQL的数据更新（续4） 3. 数据修改 update 基本表名 set 列名=值, 列名=值, 或 set row=(元组值) where 条件表达式 表示：修改基本表中满足条件表达式的那些元组的列值，需 修改的列值在set子句中指出。set子句的前一种形式表示对 符合条件元组中的个别列值进行修改，后一种形式表示对符 合条件元组中的所有列值进行修改（SQL Server中不能用） 。 注：update语句只能对一个基本表中的数据进行修改。 【例】对基本表SC和C中的值进行修改。 (1) 在表C中，把C4课程的任课教师姓名改为Wu。 update C set Teacher=Wu where C#=C4 63 3.4 SQL的数据更新（续5） 【例】对基本表SC和C中的值进行删除。 (2) 把女同学的成绩提高10%。 update SC set Grade=Grade*1.1 where S# in ( select S# from S where Sex=女 ) (3) 当C4的成绩低于该门课程的平均成绩时，提高5%。 update SC set Grade=Grade*1.05 where C#=C4 and Grade( select avg(Grade) from SC where C#=C4 ) 64 3.5 视图的定义和对视图的操作 1. 视图的创建和撤销 创建视图： create view 视图名(列名序列) as s