第3章 SQL基础8h130p V21

1、DATABASEUESTC学以致用学以致用 用以促学用以促学数据库原理及应用数据库原理及应用第第3章章 SQL基础基础电子科技大学电子科技大学 数学科学学院数学科学学院李毅超李毅超 * *3-1（8h）DATABASEUESTC学以致用学以致用 用以促学用以促学Click to add TitleClick to add Title1 1 SQL SQL简介简介1 1Click to add TitleClick to add Title2 2 数据库的操作数据库的操作2 2Click to add TitleClick to add Title2 2 数据表的操作数据表的操作3 3本章内容C

2、lick to add TitleClick to add Title1 1 表中数据的操作表中数据的操作4 4Click to add TitleClick to add Title1 1 视图视图5 5Click to add TitleClick to add Title1 1 索引索引6 63-2DATABASEUESTC学以致用学以致用 用以促学用以促学 n SQL SQL（StructuredStructured Query Query LanguageLanguage）l结构化查询语言结构化查询语言l是关系数据库的标准语言是关系数据库的标准语言n SQL SQL是一个通用的、功能

3、极强的关系数据库语言是一个通用的、功能极强的关系数据库语言lSQLSQL的名字并不贴切。的名字并不贴切。SQLSQL决不仅仅是一个查询工具，还决不仅仅是一个查询工具，还用于控制用于控制DBMSDBMS为用户提供的所有功能，包括：为用户提供的所有功能，包括：SQL简介3-3DATABASEUESTC学以致用学以致用 用以促学用以促学 l数据定义数据定义(Data definition)：SQL可用于定义被存放数据的可用于定义被存放数据的结构和组织，以及数据项之间的关系。结构和组织，以及数据项之间的关系。 l数据检索数据检索(Data retrieval)：SQL能使用户或应用程序从数据能使用户或

4、应用程序从数据库中检索数据并使用这些数据。库中检索数据并使用这些数据。 l数据操纵数据操纵(Data manipulation)：用户或应用程序通过：用户或应用程序通过SQL更改数据库，如增加新数据，删除旧数据，修改已存入的数更改数据库，如增加新数据，删除旧数据，修改已存入的数据等。据等。 l存取控制存取控制(Access control)：SQL可用来限制用户检索，增可用来限制用户检索，增加和修改数据的权限，保护所存储的数据不被非法存取。加和修改数据的权限，保护所存储的数据不被非法存取。 l数据共享数据共享(Data sharing)：SQL可用于调整数据让并发用户可用于调整数据让并发用户共

5、享，以保证用户之间彼此不受影响。共享，以保证用户之间彼此不受影响。 l数据完整性数据完整性(Data integrity)：SQL能对数据库的完整性条件能对数据库的完整性条件作出规定，以使其不会因为修改紊乱或系统出错而被破坏。作出规定，以使其不会因为修改紊乱或系统出错而被破坏。 SQL简介3-4DATABASEUESTC学以致用学以致用 用以促学用以促学 SQL简介3-5目前，没有一个数据库系统能够支持目前，没有一个数据库系统能够支持SQL标准的所有概念和特性。标准的所有概念和特性。不同厂商对不同厂商对SQL基本命令集进行了修改和扩充。有人把确立基本命令集进行了修改和扩充。有人把确立SQL为关

6、系数据库语言标准及其后的发展称为一场革命。为关系数据库语言标准及其后的发展称为一场革命。n SQL标准的进展过程标准的进展过程DATABASEUESTC学以致用学以致用 用以促学用以促学 n SQLSQL的工作机理的工作机理SQL简介DATABASEUESTC学以致用学以致用 用以促学用以促学 n 支持三级模式支持三级模式SQL简介SQL视图视图1视图视图2基本表基本表1基本表基本表2基本表基本表3基本表基本表4存储文件存储文件1存储文件存储文件2外模式外模式模式模式内模式内模式DATABASEUESTC学以致用学以致用 用以促学用以促学 n 基本表基本表l本身独立存在的表本身独立存在的表lS

7、QL中一个关系就对应一个基本表中一个关系就对应一个基本表l一个一个（或多个或多个）基本表对应一个存储文件基本表对应一个存储文件l一个表可以带若干索引一个表可以带若干索引n 存储文件存储文件l逻辑结构组成了关系数据库的内模式逻辑结构组成了关系数据库的内模式l物理结构对用户是隐蔽的物理结构对用户是隐蔽的n 视图视图l从一个或几个基本表导出的表从一个或几个基本表导出的表l数据库中只存放视图的定义而不存放视图对应的数据数据库中只存放视图的定义而不存放视图对应的数据l视图是一个虚表视图是一个虚表l用户可以在视图上再定义视图用户可以在视图上再定义视图SQL简介DATABASEUESTC学以致用学以致用 用

8、以促学用以促学 n综合统一综合统一u集集DDLDDL、DMLDML、DCLDCL的功能于一体的功能于一体u可以在运行后根据需要随时修改模式可以在运行后根据需要随时修改模式u数据操作符统一数据操作符统一n高度非过程化高度非过程化u只需提出只需提出“做什么做什么”，而无需指明，而无需指明“怎么做怎么做”。u无需了解存取路径，存取路径的选择以及无需了解存取路径，存取路径的选择以及SQLSQL语句的语句的操作过程由系统自动完成操作过程由系统自动完成u关系数据库模型的三级模式提供关系数据库模型的三级模式提供n面向集合的操作方式面向集合的操作方式u操作对象和结果均为元组的集合操作对象和结果均为元组的集合S

9、QL简介SQL特点DATABASEUESTC学以致用学以致用 用以促学用以促学 n以同一种语法结构提供两种使用方法以同一种语法结构提供两种使用方法u既是自主式语言：既是自主式语言：SQL语句能够独立地用于联机交互的使用方式u又是嵌入式语言：又是嵌入式语言：SQL语句能够嵌入到高级语言（例如C，C+，Java）程序中，供程序员设计程序时使用u语法完全相同语法完全相同n语言简洁，易学易用，完成核心功能只需语言简洁，易学易用，完成核心功能只需9 9个动词个动词uDDLDDL：CREATECREATE、DROPDROP、ALTERALTERuDMLDML： SELECT SELECT、INSERTIN

10、SERT、UPDATEUPDATE、DELETEDELETEuDCLDCL：GRANTGRANT、REVOKEREVOKESQL简介SQL特点DATABASEUESTC学以致用学以致用 用以促学用以促学Click to add TitleClick to add Title1 1 SQL SQL简介简介1 1Click to add TitleClick to add Title2 2 数据库的操作数据库的操作2 2Click to add TitleClick to add Title2 2 数据表的操作数据表的操作3 3本章内容Click to add TitleClick to add

11、Title1 1 表中数据的操作表中数据的操作4 4Click to add TitleClick to add Title1 1 视图视图5 5Click to add TitleClick to add Title1 1 索引索引6 6DATABASEUESTC学以致用学以致用 用以促学用以促学 n数据库数据库是包含多个对象的集合，包含了相关的基本表、是包含多个对象的集合，包含了相关的基本表、视图、索引、存储过程、与数据库安全性有关的控制机视图、索引、存储过程、与数据库安全性有关的控制机制及其他对象。制及其他对象。nSQL SERVERSQL SERVER使用使用一组操作系统文件映射数据库

12、一组操作系统文件映射数据库。数据库。数据库中的所有数据和对象（如表、存储过程、触发器和视图中的所有数据和对象（如表、存储过程、触发器和视图）都存储在）都存储在3 3种操作系统文件种操作系统文件中：中：u第一类是主文件第一类是主文件，扩展名为，扩展名为mdfmdf，该文件包含数据库的启动信息及数据，该文件包含数据库的启动信息及数据信息，信息，每个数据库都有一个主文件每个数据库都有一个主文件。u第二类是次要文件第二类是次要文件，也称从文件，扩展名为，也称从文件，扩展名为ndfndf，这些文件含有主文件，这些文件含有主文件以外的所有数据，如果主文件可以包含数据库中的所有数据，那么数据以外的所有数据，

13、如果主文件可以包含数据库中的所有数据，那么数据库就不需要次要文件。次要文件的主要用处是，当数据库中的数据量非库就不需要次要文件。次要文件的主要用处是，当数据库中的数据量非常大时，常大时，需要多个次要文件需要多个次要文件来提高数据访问效率，或使用多个次要文件来提高数据访问效率，或使用多个次要文件将数据扩展到多个不同的磁盘驱动器上。将数据扩展到多个不同的磁盘驱动器上。u第三类是事务日志文件第三类是事务日志文件，扩展名为，扩展名为ldfldf，这些文件包含用于恢复数据库，这些文件包含用于恢复数据库的日志信息。的日志信息。每个数据库都必须至少有一个日志文件每个数据库都必须至少有一个日志文件。数据库操作

14、创建数据库DATABASEUESTC学以致用学以致用 用以促学用以促学 n为了创建数据库，用户必须是系统管理员或者被授权使为了创建数据库，用户必须是系统管理员或者被授权使用用CREATE DATABASECREATE DATABASE语句，语法形式如下：语句，语法形式如下：CREATE DATABASE CREATE DATABASE (Name = (Name = 系统使用的逻辑名系统使用的逻辑名, Filename = Filename = 完全限定的完全限定的NT ServerNT Server文件名文件名, , Size = Size = 文件的初始大小文件的初始大小, MaxSize

15、MaxSize = = 最大的文件尺寸最大的文件尺寸, FileGrowthFileGrowth = = 文件的扩展量文件的扩展量) (Name = (Name = 系统使用的逻辑名系统使用的逻辑名, Filename = Filename = 完全限定的完全限定的NT ServerNT Server文件名文件名, Size = Size = 文件的初始大小文件的初始大小, FileGrowthFileGrowth = = 文件的扩展量文件的扩展量)数据库操作创建数据库DATABASEUESTC学以致用学以致用 用以促学用以促学 n简单示例：简单示例：如果要使用缺省参数创建医院信息系统数据库如

16、果要使用缺省参数创建医院信息系统数据库HISHIS，可以，可以使用如下命令：使用如下命令：Create Database HISCreate Database HIS数据库操作创建数据库DATABASEUESTC学以致用学以致用 用以促学用以促学 n详细示例：为数据或日志指定一个或多个特定文件详细示例：为数据或日志指定一个或多个特定文件CREATE DATABASE HISCREATE DATABASE HISON PrimaryON Primary( NAME = HIS_DATA1,( NAME = HIS_DATA1, FILENAME = FILENAME = d:datad:data

17、 HIS_DATA1.mdf, HIS_DATA1.mdf, SIZE = 10, SIZE = 10, MAXSIZE = 1500, MAXSIZE = 1500, FILEGROWTH = 5 ) FILEGROWTH = 5 )( NAME = HIS_DATA2,( NAME = HIS_DATA2, FILENAME FILENAME d:datad:data HIS_DATA2.ndfHIS_DATA2.ndf, SIZE = 10, SIZE = 10, MAXSIZE = 500, MAXSIZE = 500, FILEGROWTH = 5 ) FILEGROWTH = 5

18、)LOG ONLOG ON( NAME = HIS_LOG,( NAME = HIS_LOG, FILENAME = FILENAME = d:datad:data HIS_LOG.ldfHIS_LOG.ldf, SIZE = 5MB, SIZE = 5MB, MAXSIZE = 500MB, MAXSIZE = 500MB, FILEGROWTH = 5MB ) FILEGROWTH = 5MB )数据库操作创建数据库DATABASEUESTC学以致用学以致用 用以促学用以促学 n可以对数据库原始定义进行更改，更改包括：可以对数据库原始定义进行更改，更改包括：u扩充数据库的数据或事务日志存储

19、空间；扩充数据库的数据或事务日志存储空间；u收缩分配给数据库的数据或事务日志空间；收缩分配给数据库的数据或事务日志空间；u添加或删除数据和事务日志文件；添加或删除数据和事务日志文件；u更改数据库的配置设置；更改数据库的配置设置；u更改数据库名称；更改数据库名称；u更改数据库的所有者等。更改数据库的所有者等。数据库操作修改数据库DATABASEUESTC学以致用学以致用 用以促学用以促学 nAlter DatabaseAlter Database语法如下：语法如下：ALTER DATABASE ALTER DATABASE (Name = (,Filename = Filename = 完全限定

20、的完全限定的NT ServerNT Server文件名文件名, , Size = Size = 文件的初始大小文件的初始大小, MaxSizeMaxSize = = 最大的文件尺寸最大的文件尺寸, FileGrowthFileGrowth = = 文件的扩展量文件的扩展量) (Name = (,Filename = Filename = 完全限定的完全限定的NT ServerNT Server文件名文件名, , Size = Size = 文件的初始大小文件的初始大小, MaxSizeMaxSize = = 最大的文件尺寸最大的文件尺寸, FileGrowthFileGrowth = = 文件

21、的扩展量文件的扩展量) , (Name = (,Filename = Filename = 完全限定的完全限定的NT ServerNT Server文件名文件名, , Size = Size = 文件的初始大小文件的初始大小, MaxSizeMaxSize = = 最大的文件尺寸最大的文件尺寸, FileGrowthFileGrowth = = 文件的扩展量文件的扩展量)数据库操作修改数据库DATABASEUESTC学以致用学以致用 用以促学用以促学 n示例：下面的语句可在医院信息系统数据库示例：下面的语句可在医院信息系统数据库HISHIS中增加一中增加一个新数据库文件，同时要修改原数据库文件

22、个新数据库文件，同时要修改原数据库文件HIS_DATA1HIS_DATA1的的最大文件尺寸为最大文件尺寸为1500MB1500MB，并且要删除医院信息系统数据库，并且要删除医院信息系统数据库HISHIS的次要文件的次要文件HIS_DATA2HIS_DATA2。ALTER DATABASE HISAdd File( NAME = HIS_DATA3,( NAME = HIS_DATA3, FILENAME = d:dataHIS_DATA3. FILENAME = d:dataHIS_DATA3.n ndf,df, SIZE = 10, SIZE = 10, MAXSIZE = 1000, MA

23、XSIZE = 1000, FILEGROWTH = 5 ) FILEGROWTH = 5 )Modify File( NAME = ( NAME = HIS_DATAHIS_DATA1 1, , FILENAME = d:data HIS_DATA1.mdf, FILENAME = d:data HIS_DATA1.mdf, SIZE = 10, SIZE = 10, MAXSIZE = 1500, MAXSIZE = 1500, FILEGROWTH = 5 ) FILEGROWTH = 5 )Remove File HIS_DATAHIS_DATA2 2数据库操作修改数据库DATABAS

24、EUESTC学以致用学以致用 用以促学用以促学 n当不再需要数据库，或者数据库数据被移到其他数当不再需要数据库，或者数据库数据被移到其他数据库或服务器时，即可删除该数据库。据库或服务器时，即可删除该数据库。n数据库删除之后，该数据库的文件及其数据都从服数据库删除之后，该数据库的文件及其数据都从服务器的磁盘删除。当数据库被删除，它即被永久删务器的磁盘删除。当数据库被删除，它即被永久删除，并且不能进行检索，除非使用以前的备份。除，并且不能进行检索，除非使用以前的备份。n不能删除不能删除DBMSDBMS的的系统数据库msdbmsdb，mastermaster，modelmodel和和tempdbte

25、mpdb。 数据库操作删除数据库DATABASEUESTC学以致用学以致用 用以促学用以促学 n 删除数据库的命令格式为：删除数据库的命令格式为： DROP DATABASEDROP DATABASE 数据库名数据库名 CASCADE|RESTRICTCASCADE|RESTRICT CASCADE（级联级联）l删除数据库的同时把该数据库中所有的数据库对象全部删除删除数据库的同时把该数据库中所有的数据库对象全部删除 RESTRICT（限制限制）（缺省为（缺省为RESTRICT）l如果该数据库中定义了下属的数据库对象（如表、视图等），如果该数据库中定义了下属的数据库对象（如表、视图等），则拒绝该

26、删除语句的执行。则拒绝该删除语句的执行。l仅当该数据库中没有任何下属的对象时才能执行。仅当该数据库中没有任何下属的对象时才能执行。n 示例：示例： DROP DATABASE HISDROP DATABASE HIS 数据库操作删除数据库DATABASEUESTC学以致用学以致用 用以促学用以促学 n 课堂练习：课堂练习：n创建创建StudentsStudents数据库。要求：数据库。要求：u2 2个数据文件，初始大小分别为个数据文件，初始大小分别为1MB, 1MB, 最大空间大小分最大空间大小分别为无限制和别为无限制和100MB,100MB,文件增长率分别为文件增长率分别为1M1M和和100

27、MB100MB，名，名称自定。称自定。u2 2个日志文件，起始大小分别为个日志文件，起始大小分别为10MB10MB， 名称自定。名称自定。数据库操作DATABASEUESTC学以致用学以致用 用以促学用以促学Click to add TitleClick to add Title1 1 SQL SQL简介简介1 1Click to add TitleClick to add Title2 2 数据库的操作数据库的操作2 2Click to add TitleClick to add Title2 2 数据表的操作数据表的操作3 3本章内容Click to add TitleClick to a

28、dd Title1 1 表中数据的操作表中数据的操作4 4Click to add TitleClick to add Title1 1 视图视图5 5Click to add TitleClick to add Title1 1 索引索引6 6DATABASEUESTC学以致用学以致用 用以促学用以促学 n SQLSQL Server Server的数据类型：的数据类型：表操作数据类型数 据 类 型功能及特点char(n)固定长度字符串，长度范围是18000，默认值为1。nchar(n)固定长度Unicode字符串，长度范围是14000，默认值为1。varchar(n)变长字符串，长度范围是

29、18000，如省略n，则默认最大长度是1。nvarchar(n)包含n个字符的可变长度Unicode字符数据，n的取值介于1与4000之间；如省略n，则默认长度是1。text变长字符数据，最多达到231-1字节，行中存储指向第一个数据页的指针，实际的文本是以B-树页面存储。DATABASEUESTC学以致用学以致用 用以促学用以促学 n SQL ServerSQL Server的数据类型：的数据类型：表操作数据类型数 据 类 型功能及特点ntext变长Unicode字符数据，最多可达230-1字节，行中存储指向第一个数据页的指针，实际的文本是以B-树页面存储。dec(n,m)decimal(n

30、,m) numeric(n,m)数值型，n是位数，范围是138，m是小数点右边的位数，范围是0n。可用decimal(n)表示decimal(n,0)；如果用不带参数的decimal时，系统默认表示decimal(38,0)，但是，这时SQL Server最大可达到28位。推荐decimal使用明确的n，有助于提高程序的清晰度。int，integer四字节二进制整数，范围是2312311。float(n)浮点数，n是尾数位数，范围是153。如果n为124则指定单精度（4字节），如果n为2553则指定双精度（8字节）；注意：可以使用Float本身表示Float(53)。DATABASEUESTC

31、学以致用学以致用 用以促学用以促学 n SQL ServerSQL Server的数据类型：的数据类型：表操作数据类型数 据 类 型功能及特点real等价于Float(24)，Real列有7位数精度。smalldatetime四字节日期和时间，日期范围是6-6-2079；时间精度是自午夜开始的1分钟之内。Datetime八字节日期和时间，日期范围是12-31-9999，时间精度：3.33毫秒之内。binary(n)定长二进制数据，长度范围是18000字节，如省略n，则默认值是1。varbinary(n)变长二进制数据，长度从18000字节，如省略n，则默认值是1。image变长二进制数据，用于

32、储存图形数据，最长为2311字节，行中存储指向第一个数据页的指针，实际的数字以B-树的页面存储。DATABASEUESTC学以致用学以致用 用以促学用以促学 CREATE TABLE 基表名（基表名（ , - , , | PRIMARY KEY | UNIQUE , -）n 表名形式为：表名形式为： 数据库名数据库名.拥有者拥有者.表名表名n ：涉及相应属性列的完整性约束条件：涉及相应属性列的完整性约束条件n ：涉及一个或多个属性列的完整性约束条件：涉及一个或多个属性列的完整性约束条件n 如果完整性约束条件涉及到该表的多个属性列，则必须定义在表级如果完整性约束条件涉及到该表的多个属性列，则必须

33、定义在表级上，否则既可以定义在列级也可以定义在表级。上，否则既可以定义在列级也可以定义在表级。 表操作创建表DATABASEUESTC学以致用学以致用 用以促学用以促学 n示例：示例：HISHIS数据库中，建立药品基本信息表数据库中，建立药品基本信息表MedicineMedicine如下：如下：CREATE TABLE Medicine ( Mno VARCHAR(10) PRIMARY KEY, -主码约束 Mname VARCHAR(50) NOT NULL, -非空约束 Mprice DECIMAL(18,2) NOT NULL, Munit VARCHAR(10) DEFAULT 克,

34、 -默认约束 Mtype VARCHAR(10)表操作创建表MnoMnameMprice MunitMtype 字符型字符型字符型字符型 数字型数字型字符型字符型长度为长度为10 10 长度为长度为50 50 2位小数位小数长度为长度为1010非空值非空值非空值非空值非空值非空值字符型字符型长度为长度为1010DATABASEUESTC学以致用学以致用 用以促学用以促学 表操作创建表n 示例：示例：建立建立“学生学生”表表StudentStudent。学号是主码，姓名取值唯一。学号是主码，姓名取值唯一。 CREATE TABLE Student (Sno CHAR(9) PRIMARY KEY

35、, /* 列级完整性约束条件列级完整性约束条件,Sno是主码是主码*，不允许，不允许null*/ Sname CHAR(20) UNIQUE, /* Sname取唯一值，可取唯一值，可null*/ Ssex CHAR(2), Sage SMALLINT, Sdept CHAR(20) ); 主码主码UNIQUE约束约束DATABASEUESTC学以致用学以致用 用以促学用以促学 n常用完整性约束u主码约束： PRIMARY KEY （不允许null）u唯一性约束：UNIQUE （允许null）u非空值约束：NOT NULLu参照完整性约束：Foreign KeynPRIMARY KEYPRIM

36、ARY KEY与与 UNIQUEUNIQUE的区别？的区别？表操作创建表DATABASEUESTC学以致用学以致用 用以促学用以促学 n外键u如果存在这样两个表，其中一个表存在主键如果存在这样两个表，其中一个表存在主键（或唯一性约束列），（或唯一性约束列），另一个表存在与该主键另一个表存在与该主键（或唯一性约束列）（或唯一性约束列）相匹配的列，则称该列相匹配的列，则称该列为外键，也称为外码，且称含有主键或者唯一性约束列的表为主为外键，也称为外码，且称含有主键或者唯一性约束列的表为主表（或父表），含有外键的表为从表（或子表）。表（或父表），含有外键的表为从表（或子表）。u外键约束指定外键的列与主

37、表中主键或者唯一性约束列相关联，外键约束指定外键的列与主表中主键或者唯一性约束列相关联，也就是说，外键的每一个值至少能在主表的主键或唯一性约束列也就是说，外键的每一个值至少能在主表的主键或唯一性约束列中找到一个相同的列值。中找到一个相同的列值。u外键是在从表上定义，当修改或删除主表的记录，或者在从表上外键是在从表上定义，当修改或删除主表的记录，或者在从表上修改或插入数据时，由数据库管理系统自动进行检查。修改或插入数据时，由数据库管理系统自动进行检查。表操作创建表DATABASEUESTC学以致用学以致用 用以促学用以促学 n外键示例，实现参照完整性外键示例，实现参照完整性CREATE TABL

38、E RecipeMaster ( Rno VARCHAR(10) PRIMARY KEY, Pno VARCHAR(10) NOT NULL, Dno VARCHAR(10) NOT NULL, DGno VARCHAR(10), Rdatetime DATETIME,)CREATE TABLE RecipeDetail ( Rno varchar (10) , Mno varchar (10) , Mamount decimal(18, 0), CONSTRAINT Rnofk FOREIGN KEY(Rno) REFERENCES RecipeMaster(Rno), CONSTRAINT

39、 mnofk FOREIGN KEY(Mno) REFERENCES medicine(Mno);表操作创建表DATABASEUESTC学以致用学以致用 用以促学用以促学 表操作创建表n 示例：示例：建立一个建立一个“课程课程”表表CourseCREATE TABLE Course ( Cno CHAR(4) PRIMARY KEY, Cname CHAR(40), Cpno CHAR(4), Ccredit SMALLINT， FOREIGN KEY (Cpno) REFERENCES Course(Cno) ); 先修课先修课 Cpno是外码是外码 被参照表是被参照表是Course 被参照

40、列是被参照列是CnoDATABASEUESTC学以致用学以致用 用以促学用以促学 表操作创建表n 示例：示例：建立一个学生选课表建立一个学生选课表SC CREATE TABLE SC (Sno CHAR(9), Cno CHAR(4), Grade SMALLINT， PRIMARY KEY (Sno,Cno), /* 主码由两个属性构成，必须作为表级完整性进行定义主码由两个属性构成，必须作为表级完整性进行定义*/ FOREIGN KEY (Sno) REFERENCES Student(Sno), /* 表级完整性约束条件，表级完整性约束条件，Sno是外码，被参照表是是外码，被参照表是Stu

41、dent */ FOREIGN KEY (Cno)REFERENCES Course(Cno) /* 表级完整性约束条件，表级完整性约束条件， Cno是外码，被参照表是是外码，被参照表是Course*/ ); DATABASEUESTC学以致用学以致用 用以促学用以促学 n语法格式：ALTER TABLE 基表名 ALTER COLUMN , ADD , DROP , DROP ;u：要修改的基本表uADD子句：增加新列和新的完整性约束条件uDROP子句：删除指定的列或完整性约束条件uALTER子句：用于修改列名和数据类型表操作修改表DATABASEUESTC学以致用学以致用 用以促学用以促学

42、 n修改表示例：在医院信息系统的数据库中，如果医院的某些药品价钱随着市场供求在不断调整，不同阶段的处方药品价钱不一样，因此在处方明细表RecipeDetail需要增加一列存储药品单价。ALTER TABLE RecipeDetail ADD Price Decimal ( 5,3 )u注意，使用ALTER TABLE语句在表中增加列，如果新增列定义为NOT NULL列，必须用Default子句指定缺省值，否则，没有指定缺省值，当给表增加新列时，表中原有记录的新增列将自动为NULL，这样就会违背NOT NULL的定义而出错。表操作修改表DATABASEUESTC学以致用学以致用 用以促学用以促学

43、 nDROP TABLE RESTRICT|CASCADE;uRESTRICT：拥有表的对象（Check、Foreign Key、视图、触发器、存储过程、函数等）时禁止删除；uCASCADE：级联删除表的所有对象u不同数据库产品略有执行策略的差别u使用ALTER TABLE语句还可以从表中删除已有的列，但删除列之前，必须删除任何引用该列的约束、缺省表达式、计算列表达式或索引。n删除表示例： 删除RecipeMaster表： DROP TABLE RecipeMaster表操作删除表3-36DATABASEUESTC学以致用学以致用 用以促学用以促学 n用DDL创建以下关系模式：u学生表：Stu

44、dent(Sno,Sname,Ssex,Sage,Sdept)u课程表：Course(Cno,Cname,Cpno,Ccredit)u学生选课表：SC(Sno,Cno,Grade)表操作课堂练习3-37DATABASEUESTC学以致用学以致用 用以促学用以促学Click to add TitleClick to add Title1 1 SQL SQL简介简介1 1Click to add TitleClick to add Title2 2 数据库的操作数据库的操作2 2Click to add TitleClick to add Title2 2 数据表的操作数据表的操作3 3本章内容C

45、lick to add TitleClick to add Title1 1 表中数据的操作表中数据的操作4 4Click to add TitleClick to add Title1 1 视图视图5 5Click to add TitleClick to add Title1 1 索引索引6 63-38DATABASEUESTC学以致用学以致用 用以促学用以促学 n语法格式 SELECT ALL|DISTINCT ， INSERT INTO FROM ， WHERE GROUP BY HAVING ORDER BY ASC|DESC ；uSELECT子句：指定要显示的属性列uFROM子句：

46、指定查询对象(基本表或视图)uWHERE子句：指定查询条件uGROUP BY子句：对查询结果按指定列的值分组，该属性列值相等的元组为一个组。通常会在每组中作用集函数。uHAVING短语：筛选出只有满足指定条件的组uORDER BY子句：对查询结果表按指定列值的升序或降序排序数据操作数据查询3-39数据操作数据查询u */表名表名.属性列属性列/count(*)/属性列表达式属性列表达式属性列属性列表达式：由表达式：由属性列属性列、属性列属性列库函数和常量的任意算术运算组成库函数和常量的任意算术运算组成字段库函数：字段库函数：count|sum|avg|max|min (distinct字段名）

47、字段名）查询列表达式3-40DATABASEUESTC学以致用学以致用 用以促学用以促学 n无条件查询uSELECT子句中的指出要查询哪些列的数据；uFROM子句的指出要查找的数据源；u无条件查询是指查询所有的数据集。n最简单的示例：查询医生基本信息表中所有医生的所有信息。SELECT * FROM Doctor数据操作数据查询DnoDname DsexDage DdeptnoDlevel -140郝亦柯郝亦柯男男28 102医师医师21刘伟刘伟 男男43 103副主任医师副主任医师368罗晓罗晓 女女27 102主治医师主治医师73邓英超邓英超女女43 201主任医师主任医师82杨勋杨勋男男

48、36 101副主任医师副主任医师无条件查询DATABASEUESTC学以致用学以致用 用以促学用以促学 n示例：列别名u查询显示指定的列，列的显示顺序与SELECT子句后指定的列顺序一致，这与关系代数的投影操作功能一致。查询显示各列的标题可以是列名，也可以改变查询显示列的标题。 SELECT Dname 医生姓名, Dlevel 专业职称 FROM Doctor数据操作数据查询医生姓名医生姓名 专业职称专业职称-郝亦柯郝亦柯 医师医师刘伟刘伟 副主任医师副主任医师罗晓罗晓 主治医师主治医师邓英超邓英超 主任医师主任医师杨勋杨勋 副主任医师副主任医师无条件查询DATABASEUESTC学以致用学

49、以致用 用以促学用以促学 n示例：无重复记录u关键字DISTINCT是合并查询结果中的重复记录，当没有DISTINCT时，表示显示所有记录。 SELECT DISTINCT Ddeptno FROM Doctor数据操作数据查询Ddeptno-101102103201 无条件查询DATABASEUESTC学以致用学以致用 用以促学用以促学 n 条件查询u在SELECT命令中使用WHERE子句给出查询条件来实现;uWHERE子句是比较运算、逻辑运算、集合运算和特殊的运算符所构成。数据操作数据查询条件查询运 算 类 别运 算 符具 体 含 义比较运算=等于或!=不等于小于 小于等于大于=或!大于等

50、于逻辑运算NOT逻辑非，用于选择不满足条件的记录行AND逻辑与，用于选择同时满足多个条件的记录行OR逻辑或，用于选择满足任意一个条件的记录行集合运算IN属于集合（或查询返回值构成的集合）NOT IN不属于集合（或查询返回值构成的集合）特殊运算BETWEEN A AND B大于或等于A且小于或等于BLIKE模式匹配，“%”表示匹配任意多个字符，“-”匹配一个字符NOT LIKE为LIKE的否定形式IS NULL为空值IS NOT NULL为非空值DATABASEUESTC学以致用学以致用 用以促学用以促学 n 条件运算符的优先级顺序u纵向由高到低；u横向同行优先级相同，在具体的运算表达式中在左边

51、者优先；u如有括号则括号优先。数据操作数据查询条件查询同行优先级相同优先级由上至下递减=、或!=、=或!IN，NOT IN，BETWEEN A AND B，LIKE，NOT LIKE，IS NULL，IS NOT NULLNOTANDORDATABASEUESTC学以致用学以致用 用以促学用以促学 数据操作数据查询条件查询条件表达式 1) 属性名属性名 属性名属性名|常量常量 2) 属性名属性名not between 属性属性名名|常量常量 and 属性属性名名|常量常量3) 属性名属性名 not like 字符串常数字符串常数4) 属性名属性名 is not null条件表达式数据操作数据查

52、询条件查询6) not exists (select 子句子句) 5) 属性名属性名|常量常量 not in | any|all 值值1，值，值2|(select 子句子句7) 条件表达式条件表达式 and|or 条件表达式条件表达式DATABASEUESTC学以致用学以致用 用以促学用以促学 n条件查询示例：单一条件。u查询医生基本信息表中所有男医生的基本信息。SELECT * FROM Doctor WHERE Dsex=男数据操作数据查询条件查询DnoDname Dsex DageDdeptnoDlevel -140郝亦柯郝亦柯 男男 28102医师医师21刘伟刘伟 男男 43103副主

53、任医师副主任医师82杨勋杨勋 男男 36101副主任医师副主任医师 DATABASEUESTC学以致用学以致用 用以促学用以促学 n% (百分号) 代表任意长度（长度可以为0）的字符串u例：a%b表示以a开头，以b结尾的任意长度的字符串。如acb，addgb，ab 等都满足该匹配串n_ (下横线) 代表任意单个字符u例：a_b表示以a开头，以b结尾的长度为3的任意字符串。如acb，afb等都满足该匹配串n当用户要查询的字符串本身就含有 % 或 _ 时，要使用ESCAPE 短语对通配符进行转义。数据操作数据查询条件查询DATABASEUESTC学以致用学以致用 用以促学用以促学 n条件查询示例：

54、字符串匹配u查询职称为副的医生信息uSelect * From Doctor Where Dlevel Like 副%数据操作数据查询条件查询DnoDname DsexDage DdeptnoDlevel-21刘伟刘伟 男男43103副主任医师副主任医师82杨勋杨勋男男36101副主任医师副主任医师 DATABASEUESTC学以致用学以致用 用以促学用以促学 n条件查询示例：多重条件u查询年龄小于或等于40岁的男医生信息SELECT * FROM Doctor WHERE Dsex=男 and Dage=30数据操作数据查询查询表达式编号编号 药品名药品名 单价单价 调整单价调整单价 单位单

55、位类型类型-314941 肾石通颗粒肾石通颗粒27.1000 31.165000 盒盒 西药西药315189 心胃止痛胶囊心胃止痛胶囊26.9000 30.935000 盒盒 西药西药315722 L-谷氨酰胺胶囊谷氨酰胺胶囊 26.9000 30.935000 盒盒西药西药315977 胃立康片胃立康片 26.5000 30.475000 盒盒西药西药316910 依诺沙星注射液依诺沙星注射液46.0000 52.900000 支支西药西药DATABASEUESTC学以致用学以致用 用以促学用以促学 n带聚集函数的统计查询u在实际应用中，经常要对一个数据集进行统计、求和、求平均等汇总操作，一

56、般数据库系统都提供了聚集函数可以实现这一功能。u聚集函数一般要忽略NULL值，不对NULL值进行操作，除MIN，MAX，COUNT3个函数适合于任何数据类型外，其余的聚集函数一般都要求是数值型。u如果聚集函数加上选项ALL，表示对一组记录值进行计算，重复记录重复计算，但不包括NULL记录。如果加上选项DISTINCT，表示只计算不同记录值，不计算重复记录和空行，但该选项对COUNT（*）不起作用，缺省为ALL。n格式为：（DISTINCT|ALL 表达式）数据操作数据查询统计查询DATABASEUESTC学以致用学以致用 用以促学用以促学 n聚集查询示例u统计医生人数。SELECT COUNT

57、(Dno)人数FROM Doctor数据操作数据查询聚集查询人数人数-5DATABASEUESTC学以致用学以致用 用以促学用以促学 n分组查询：GROUP BY子句uGROUP BY子句主要是完成将查询结果分组u当WHERE子句和GROUP BY子句一起使用时，WHERE子句必须在GROUP BY子句的前面。u执行过程：是先按WHERE子句找出满足条件的数据行，然后按GROUP BY子句指定的列来计算聚集函数的值。n聚集查询示例：分组统计u按部门编号统计不同部门的医生人数SELECT Ddeptno 部门编码， COUNT(Dno)人数FROM DoctorGROP BY Ddeptno数据

58、操作数据查询聚集查询部门编码部门编码 人数人数-101 1102 2103 1201 1DATABASEUESTC学以致用学以致用 用以促学用以促学 n分组查询：HAVING子句u当需要选出符合条件的分组统计值时，在GROUP BY子句之后，可使用HAVING子句实现。 uHAVING子句与WHERE子句区别是：WHERE子句作用在分组之前选择符合条件的记录，而HAVING子句是作用在分组之后选择符合条件的分组结果。uHAVING子句必须在含有GROUP BY子句的查询语句使用，不能单独使用。数据操作数据查询聚集查询DATABASEUESTC学以致用学以致用 用以促学用以促学 n聚集查询示例：

59、条件分组统计u在医生基本信息表中，按部门统计男医生的平均年龄不超过40岁的部门编号，并按平均年龄升序显示。SELECT Ddeptno 部门编号,AVG(Dage) 平均年龄FROM DoctorWHERE Dsex=男GROUP BY DdeptnoHAVING AVG(Dage)=40ORDER BY AVG(Dage) 数据操作数据查询聚集查询部门编号部门编号 平均年龄平均年龄-102 28101 36DnoDnameDsexDageDdeptnoDlevelDsalary140郝亦柯男28102医师180021刘伟男43103副主任医师2800368罗晓女27102主治医师200073

60、邓英超女43201主任医师320082杨勋男36101副主任医师2800DATABASEUESTC学以致用学以致用 用以促学用以促学 n多表间的连接查询多表间的连接查询u多表查询的理论基础是关系运算。u多表间的连接运算遵循笛卡尔规则，但“笛卡尔”查询会产生大量的无意义的数据记录。因此，在进行连接时加上一些限制条件，使产生的数据记录是笛卡尔连接结果集的子集。u进行连接运算的表，必须存在着有某种关系的公共列，连接运算实际是比较各表的公共列值，如果满足条件的连接产生组合输出行。数据操作数据查询连接查询DATABASEUESTC学以致用学以致用 用以促学用以促学 n执行过程：嵌套循环法(NESTED-