关系数据库标准语言SQL-1

第3章关系数据库

标准语言SQLDatabasePrinciplesandApplications第3章关系数据库标准语言SQL3.1SQL概述3.2数据定义3.3数据查询3.4数据更新3.5索引3.6视图3.1SQL概述SQL（StructuredQueryLanguage）结构化查询语言，是关系数据库的标准语言SQL是一个通用的、功能极强的关系数据库语言DatabasePrinciplesandApplicationsSQL概述SQL的起源与发展1986年10月美国国家标准局ANSI批准SQL作为关系数据库语言的美国标准。称为SQL86。1989年10月颁布增强完整性特征的SQL89标准1992年ISO对标准又进行了修订，称为SQL921999年ISO颁布了SQL99。SQL标准经历三次修订，分别为SQL2003、SQL2006和SQL2008DatabasePrinciplesandApplicationsSQL概述SQL的起源与发展

标准大致页数发布日期SQL/861986.10SQL/89(FIPS127-1)120页1989年SQL/92622页1992年SQL991700页1999年SQL20033600页2003年DatabasePrinciplesandApplicationsSQL概述SQL组成数据定义语言（DDL）

对模式、基本表、视图、索引、域等的创建、修改和删除操作。数据操纵语言（DML）

查询和更新数据库中的数据。数据控制语言（DCL）

对基本表和视图的授权、完整性规则的描述、事务控制等。嵌入式SQL语言（ESQL）

规定SQL语句在高级程序设计语言中使用的规范与标准DatabasePrinciplesandApplicationsSQL概述SQL特点1.综合统一集数据定义语言（DDL），数据操纵语言（DML），数据控制语言（DCL）功能于一体。可以独立完成数据库生命周期中的全部活动：定义关系模式，插入数据，建立数据库；对数据库中的数据进行查询和更新；数据库重构和维护数据库安全性、完整性控制等用户数据库投入运行后，可根据需要随时逐步修改模式，不影响数据的运行。数据操作符统一DatabasePrinciplesandApplicationsSQL概述SQL特点（续）2.高度非过程化非关系数据模型的数据操纵语言“面向过程”，必须制定存取路径SQL只要提出“做什么”，无须了解存取路径存取路径的选择以及SQL的操作过程由系统自动完成。DatabasePrinciplesandApplicationsSQL概述SQL特点（续）3.面向集合的操作方式非关系数据模型采用面向记录的操作方式，操作对象是一条记录SQL采用集合操作方式操作对象、查找结果可以是元组的集合一次插入、删除、更新操作的对象可以是元组的集合DatabasePrinciplesandApplicationsSQL概述SQL特点（续）4.语言简洁，易学易用 SQL功能极强，完成核心功能只用了9个动词。DatabasePrinciplesandApplicationsSQL概述SQL特点（续）5.以同一种语法结构提供多种使用方式SQL是独立的语言能够独立地用于联机交互的使用方式SQL又是嵌入式语言

SQL能够嵌入到高级语言（例如C，C++，Java）程序中，供程序员设计程序时使用DatabasePrinciplesandApplicationsSQL概述SQL特点（续）6.支持三级模式结构DatabasePrinciplesandApplicationsSQL概述SQL视图2视图1基本表2基本表1基本表3基本表4存储文件2存储文件1外模式模式内模式SQL特点（续）基本表本身独立存在的表SQL中一个关系就对应一个基本表一个(或多个)基本表对应一个存储文件一个表可以带若干索引存储文件逻辑结构组成了关系数据库的内模式物理结构是任意的，对用户透明视图从一个或几个基本表导出的表数据库中只存放视图的定义而不存放视图对应的数据视图是一个虚表用户可以在视图上再定义视图DatabasePrinciplesandApplicationsSQL概述示例数据库DatabasePrinciplesandApplicationsSQL概述nonamegenderagespecialty14001王建立男18CP14002张玲女19FR14003王军男20IF14004李建国男19CP14005赵文娟女18MAisbnnameauthorpublisherpricecategory04-012955-7积分变换张元林高等教育出版社8.00理04-023909-6线性代数贾兰香高等教育出版社16.00理302-00860-4C程序设计谭浩强清华大学出版社20.00工302-02368-5数据结构_C语言版严蔚敏清华大学出版社22.00工302-03314-5数据结构题集严蔚敏清华大学出版社16.00工5613-3644-6读者文摘精粹版周宏陕西师范大学出版社38.00文81080-159-1大学英语李荫华上海外语教育出版社35.00文noisbnstartdateenddatefine14001302-00860-42013/8/6

0.0014001302-02368-52013/3/102013/6/152.0014001302-03314-52013/3/102013/7/123.001400304-012955-72013/10/72013/12/203.5014004302-00860-42013/1/92013/3/90.0014004302-03314-52013/5/9

0.0014005302-00860-42013/6/182013/7/100.00reader表book表borrow表数据类型数据库中的所有数据都存放在按行与列格式组织的基本表中，数据类型是数据的一种属性，决定数据存储的空间、数据的长度以及数据能参与的运算。SQL提供的数据类型可以归纳为：数值类型、字符类型、日期时间类型、货币类型和其他数据类型。

说明：不同的SQL实现定义的数据类型以及使用的关键字有所不同。以下所述数据类型适用SQLServer，其他SQL实现请参照其说明。DatabasePrinciplesandApplicationsSQL基本知识数据类型（续）1.数值类型数值类型根据其所存储数据的精确与否，分为精确数值类型和近似数值类型。(1)精确数值类型精确数值类型用来存储没有小数位的整数或定点小数DatabasePrinciplesandApplicationsSQL基本知识类别数据类型字节数取值范围Integerbigint8-263～263-1int4-231～231-1smallint2-32768～32767tinyint10～255Exactnumricdecimal(p[,s])5～17-1038+1～1038-1numeric(p[,s])5～17-1038+1～1038-1数据类型（续）decimal和numeric数据类型需要提供两个参数，第1个参数说明数据类型的精度，即数字的个数，第2个参数说明数据类型的小数位数，例如decimal(12,4)，定义一个数据类型，其中可以存放12位数字，其中小数点后面有4位数字。在SQL

Server中decimal和numeric两者唯一的区别在于decimal不能用于带有identity关键字的列。数据类型bigint,int,smallint和tinyint数据类型只能用于存储整数。decimal和numeric数据类型既可以存放小数数值，也可以存放整数，当参数s的值取0时，表示这两种数据类型中存放的是整数DatabasePrinciplesandApplicationsSQL基本知识数据类型（续）(2)近似数值类型近似数字数据类型可以存储十进制值，用于表示浮点数据。此类型的数据不一定有精确的表示。SQLServer存储数据时对小数点右边的数进行四舍五入。只有在精确数据类型不够大，不能存储数值时，才考虑使用float。float[(n)]中n的取值范围为1~53。当1≤n≤24时，占用4字节，精度为7；当25≤n≤53时，占用8字节，精度为15。DatabasePrinciplesandApplicationsSQL基本知识数据类型字节数取值范围float[(n)]4或8-2.23E308～2.23E308real4-3.4E38～3.4E38数据类型（续）2.字符类型字符类型是用于存储字符型数据的。该数据类型可以使用ASCII编码或Unicode编码。ASCII编码要求用8个二进制位来表示字母的范围。Unicode标准使用2个字节来表示每个字符。在Unicode标准中，包括了以各种字符集定义的全部字符。字符类型常量应该用单引号括起来。DatabasePrinciplesandApplicationsSQL基本知识数据类型（续）DatabasePrinciplesandApplicationsSQL基本知识数据类型字节数字符数作用char(n)1～8000最多8000个字符固定宽度的ASCII数据类型varchar(n)1～8000最多8000个字符可变宽度的ASCII数据类型varchar(max)最大2G最多1073741824个字符可变宽度的ASCII数据类型text最大2G最多1073741824个字符可变宽度的ASCII数据类型nchar(n)2～8000最多4000个字符固定宽度的Unicode数据类型nvarchar(n)2～8000最多4000个字符可变宽度的Unicode数据类型nvarchar(max)最大2G最多536870912个字符固定宽度的Unicode数据类型ntext最大2G最多536870912个字符固定宽度的Unicode数据类型数据类型（续）3.日期时间类型日期时间类型数据，可具体分为datetime与smalldatetime两种类型。datetime和smalldatetime数据类型在计算机内部是作为整数存储的。datetime类型存储为1对4字节整数，前4字节存储日期值，后4字节存储时间值。它们一起表示自1753年1月1日午夜12点钟经过的毫秒数。smalldatetime类型存储为一对2字节整数，前2字节存储日期值，后2字节存储时间值，它们一起表示自1900年1月1日午夜12点钟经过的分钟SQL基本知识DatabasePrinciplesandApplications数据类型（续）日期时间数据类型由有效的日期和时间组成。如有下列日期和时间数据“12/05/9812:26:00:00:00PM”和“2:38:29:15:01AM6/15/98”。前一个数据是日期在前，时间在后。SQLServer中常用的日期和时间表示格式如下：分隔符可用'/'、'-'或'.'，例如'4/15/2005'、'4-15-05'或'4.15.2005'；字母日期格式：'April15,2005'；不用分隔符：'20050501'；时:分:秒:毫秒：'08:05:25:28'时:分AM|PM：'05:08AM'、'08:05PM'SQL基本知识DatabasePrinciplesandApplications数据类型（续）SQL基本知识DatabasePrinciplesandApplications日期类型字节数取值范围datetime8从1753-1-1到9999-12-31，精度为3.33毫秒smalldatetime4从1900-1-1到2079-12-31，精度为1分钟数据类型（续）4.货币类型货币数据类型可用于存储精确到小数点后4个小数位的货币值。货币数据类型的特点是小数点后存储4位小数，所以在金融应用程序中很少使用这种数据类型。通常使用decimal数据类型代替货币数据类型，因为使用decimal数据类型可以更灵活地指定小数点后多位数。货币类型常量应以货币单位符号做前缀，默认$SQL基本知识DatabasePrinciplesandApplications数据类型

字节数

取值范围money8-922

337

203

685

477.5808～922

337

203

685

477.5807smallmoney4-214

748.3648～214

748.3647数据类型（续）5.二进制数据类型SQLServer提供了3种二进制数据类型，允许在一个表中存储各种数量的二进制数据。SQL基本知识DatabasePrinciplesandApplications数据类型字节数作用binary(n)1～8000存储固定大小的二进制数据varbinary(n)1～8000存储可变大小的二进制数据varbinary(max)最多2G存储可变大小的二进制数据image最多2G存储可变大小的二进制数据数据类型（续）二进制数据类型用于存储SQLServer中的文件，如Word、Excel、PDF、Visio以及图像等文件。image数据类型除了可以存储图像文件以外，也可以存储其他二进制文件。varbinary(max)数据类型是SQLServer2005新增的一种数据类型，它可以存储与image数据类型相同大小的数据，并且所有能够在binary/varbinary数据类型上执行的操作和函数都可以在varbinary(max)数据类型上使用，这是image数据类型不能做到的。二进制类型常量是以0x作前缀的十六进制数字字符串。SQL基本知识DatabasePrinciplesandApplications运算符1.比较运算符 =、<、<=、>、>=、<>（或者!=）2.逻辑运算符 AND、OR和NOT3.算术运算符 +、-、*和/SQL基本知识DatabasePrinciplesandApplications运算符(续)4.谓词BETWEEN…AND…NOTBETWEEN…AND…用于判断属性列值是否在指定范围内。rageBETWEEN18AND20rageNOTBETWEEN18AND20IN和NOTIN用于判断属性列值是否在指定集合内。rageIN(15,17,18)rageNOTIN(15,17,18)SQL基本知识DatabasePrinciplesandApplications运算符(续)LIKE和NOTLIKE用于进行字符串匹配。语法格式：[NOT]LIKE匹配串[ESCAPE转义字符]匹配串由普通字符和/或通配符组成。通配符：%（百分号）：代表任意长度（长度可以为0）的字符串。_（下划线）：代表任意单个字符。SQL基本知识DatabasePrinciplesandApplications运算符(续)通配符SQL基本知识DatabasePrinciplesandApplications通配符说明示例%包含零个或多个字符的任意字符串。LIKE'%cpu%'将查找在任意位置包含单词cpu的所有字符串。_（下划线）任何单个字符。LIKE'_en'将查找以en结尾的所有3个字母的字符串。[]指定范围([a-f])或([abcdef])中的任何单个字符。LIKE'[C-P]ars'将查找以ars结尾并且以介于C与P之间的任何单个字符开始的字符串。[^]不属于指定范围([a-f])或([abcdef])的任何单个字符。LIKE'de[^l]%'将查找以de开始并且其后的字母不为l的所有字符串。运算符(续)如果要查询的字符串本身含有％或_，需要使用ESCAPE‘<换码字符>’短语对通配符进行转义。例：查询课程“Delphi_6.0”的课程号SELECT课程名,课号FROM课程WHERE课程名LIKE‘Delphi/_6.0’ESCAPE‘/’ESCAPE‘/’短语表示“／”是换码字符，这样匹配串中紧跟在／之后的字符“_”不再具有通配符的含义，转意为普通的“_”字符，请注意“／”不参与匹配过程。SQL基本知识DatabasePrinciplesandApplications运算符(续)ISNULL和ISNOTNULL用于判断是否为空。SQL基本知识DatabasePrinciplesandApplications运算符(续)运算符的优先级SQL基本知识DatabasePrinciplesandApplications优先级运算符1（）（圆括号）2+（正）、-（负）、~（位非）3*（乘）、/（除）、%（取模）4+（加）、（+连接）、-（减）、&（位与）5=,>、<、>=、<=、<>、!=、!>、!<（比较运算符）6^（位异或）、|（位或）7NOT8AND9ALL、ANY、BETWEEN、IN、LIKE、OR、SOME10=（赋值）3.2数据定义SQL的数据定义功能:数据库定义、模式定义、表定义、视图和索引的定义。数据定义DatabasePrinciplesandApplications文件和文件组数据库是存储数据的容器，数据库在磁盘上是以文件为单位存储的，SQLServer将数据库映射为一组操作系统文件。每个SQLServer数据库至少具有两个操作系统文件：一个主数据文件和一个日志文件。SQLServer的数据库具有三种类型的文件：(1)主数据文件：主数据文件是数据库的起点，指向数据库中的其他文件。每个数据库都有且只有一个主数据文件。主数据文件的推荐文件扩展名是.mdf。创建数据库DatabasePrinciplesandApplications文件和文件组(2)次要数据文件(辅助数据文件)：次要数据文件可用作存储用户数据和对象。如果主数据文件可以存储数据库中的所有数据，那么数据库就不需要次要数据文件。有些数据库可能比较大，所以需要多个次要数据文件，或使用位于不同磁盘驱动器上的辅助文件将数据扩展到多个磁盘。并不是所有的数据库都需要次要数据文件。次要数据文件的推荐扩展名为.ndf。(3)事务日志文件：日志文件包含着用于恢复数据库的所有日志信息。每个数据库必须至少有一个日志文件，当然也可以有多个。SQLServer2005事务日志采用提前写入的方式，即对数据库的修改先写入事务日志中，然后再写入数据库。日志文件的推荐文件扩展名是.ldf。创建数据库DatabasePrinciplesandApplications文件和文件组数据库文件组SQLServer中的数据库文件组分为主文件组和用户定义文件组。①主文件组：主文件组包含主要数据库文件和任何没有明确指派给其他文件组的其他文件。数据库的系统表都包含在主文件组中。②用户定义文件组：用户定义文件组是在CREATEDATABASE或ALTERDATABASE语句中，使用FILEGROUP关键字指定的文件组。创建数据库DatabasePrinciplesandApplications文件和文件组文件和文件组应用的规则：(1)一个文件只能存在于一个文件组中，一个文件组也只能被一个数据库使用。(2)主文件组中包含了所有的系统表。当建立数据库时，主文件组包括主数据库文件和未指定组的其它文件。(3)在创建数据库对象时如果没有指定将其放在哪一个文件组中，就会将它放在默认文件组中。如果没有指定默认文件组，则主文件组为默认文件组。(4)事务日志文件不属于任何文件组。创建数据库DatabasePrinciplesandApplications创建数据库用户要创建数据库，必须确定数据库的名称、所有者、大小以及存储该数据库的文件和文件组。数据库名称必须遵循为标识符指定的规则：数据库名称长度为1~128个字符。名称首字符必须是一个英文字母或“_”、“#”和“@”中的任意字符(中文版包括汉字)。在中文版SQLServer中，可以直接使用汉字为数据库命名。名称中不能出现空格，不允许使用SQLServer的保留字。创建数据库DatabasePrinciplesandApplications创建数据库在SQLServer中创建用户数据库主要有以下两种形式：在SQLServerManagementStudio中创建数据库。利用Transact-SQL语句创建数据库。创建数据库DatabasePrinciplesandApplications在SQLServerManagementStudio中创建数据库在SQLServerManagementStudio中创建数据库的过程如下：启动SQLServerManagementStudio，在对象资源管理器中，右击“数据库”选项，在弹出的快捷菜单中选择“新建数据库”命令，如图3.1所示，打开“新建数据库”窗口。创建数据库DatabasePrinciplesandApplications图3.1选择“新建数据库”命令在SQLServerManagementStudio中创建数据库创建数据库DatabasePrinciplesandApplications在“新建数据库”窗口中的“常规”选项卡，如图4.3所示，有以下几个可选项。在“数据库名称”文本框中，输入要创建的数据库名称，如“学生选课”。若要通过接受所有的默认值来创建数据库，则单击“确定”按钮；否则，继续后面的可选项目的选择。若要更改所有者名称，单击“所有者”后的按钮选择其他所有者。若要启用数据库的全文搜索，选中“使用全文索引”复选框。在SQLServerManagementStudio中创建数据库创建数据库DatabasePrinciplesandApplications在SQLServerManagementStudio中创建数据库创建数据库DatabasePrinciplesandApplications若要更改主数据文件和事务日志文件的默认值，并输入新值。各项的具体含义如下：逻辑名称：用来输入数据文件或日志文件的逻辑文件名。逻辑文件名是在所有Transact-SQL语句中引用物理文件时所使用的名称。文件类型：数据库文件的类型。对于新添加的数据库文件可以选择“数据”或“日志”。文件组：数据库中的数据文件所属的文件组。日志文件没有文件组的概念。初始大小：确定数据库文件的初始大小。默认的数据文件初始大小为3M，日志文件为1M。自动增长：显示设置的数据文件和日志文件的增长方式增量。单击后面的按钮可以修改。路径：显示数据库物理文件的存放路径。单击后面的按钮可以选择新的存放路径。在SQLServerManagementStudio中创建数据库创建数据库DatabasePrinciplesandApplications切换到“新建数据库”窗口中的“选项”选项卡中，如图3.3所示。其中有以下几个可选项：若要更改数据库的排序规则，从“排序规则”下拉列表框中选择一个排序规则。若要更改恢复模式，从“恢复模式”下拉列表框中选择一个恢复模式。若要更改数据库其他选项，从下面的列表中根据需要修改选项值。切换到“文件组”选项卡进行设置，如图3.4所示。如果要添加文件组，可以单击“添加”按钮，然后输入文件组的名称。所有参数设置完毕后，单击“确定”按钮，新的数据库就创建成功。展开对象资源管理器中的数据库项，可以看到新建的数据库。创建数据库DatabasePrinciplesandApplications图3.3新建数据库“常规”选项对话框创建数据库DatabasePrinciplesandApplications图3.4新建数据库“文件组”选项对话框利用Transact-SQL语句创建数据库CREATEDATABASE语句的基本格式CREATEDATABASEdatabase_name--设置数据库名称

[ON[PRIMARY][<filespec>[,...n]]--设置数据文件

[,<filegroup>[,...n]]--设置文件组

[LOGON{<filespec>[,...n]}]--设置日志文件

][COLLATEcollation_name]--设置排序规则名称

[WITH<external_access_option>]--设置外部访问

][；]创建数据库DatabasePrinciplesandApplications利用Transact-SQL语句创建数据库创建数据库DatabasePrinciplesandApplications说明：在T-SQL语言的命令格式中，用[]括起来的内容表示是可选的；[，…n]表示重复前面的内容；用<>括起来的内容表示在实际编写语句时，用相应的内容替代；用{}括起来的内容表示是必选的；类似A|B的格式，表示A和B只能选择一个，不能同时都选。语句中参数的说明如下：database_name：新建数据库的名称。同一个SQLServer的实例中数据库名称必须唯一，且最长为128个字符。利用Transact-SQL语句创建数据库ON：显式定义用来存储数据库数据部分的磁盘文件(数据文件)。当后面是以逗号分隔的用以定义主文件组的数据文件的<filespec>列表时，需使用ON。<filespec>列表用于定义主文件组的数据文件。主文件组的文件列表后可以包含用逗号分隔的<filegroup>列表，<filegroup>列表用于定义用户文件组及其中的文件。filespec的定义格式：<filespec>::=--<filespec>语法格式{(NAME=logical_file_name,FILENAME='os_file_name'[,SIZE=size[KB|MB|GB|TB]][,MAXSIZE={maxsize[KB|MB|GB|TB]|UNLIMITED}][,FILEGROWTH=growth_increment[KB|MB|GB|TB|%]])[,...n]}创建数据库DatabasePrinciplesandApplications利用Transact-SQL语句创建数据库filegroup的定义格式：<filegroup>::={FILEGROUPfilegroup_name[DEFAULT]

<filespec>[,...n]}PRIMARY：用于指定主文件组中的文件。主文件组不仅包含数据库系统表中的全部内容，而且还包含用户文件组中没有包含的全部对象。一个数据库只能有一个主文件，默认情况下，如果不指定PRIMARY关键字，则在命令中列出的第一个文件将被默认为主文件。LOGON：显式定义用来存储数据库日志的日志文件。创建数据库DatabasePrinciplesandApplications利用Transact-SQL语句创建数据库NAME：指定数据库文件的逻辑名称，这是在SQLServer系统中使用的名称，是数据库文件在SQLServer中的标识符。FILENAME：指定数据库文件的在操作系统中文件名称和路径，该操作系统文件名和NAME的逻辑名称一一对应。SIZE：指定数据库的初始容量大小。如果没有指定主文件的大小，则SQLServer默认其与模板数据库中的主文件大小一致，其他数据库文件和事务日志文件则默认为1MB。指定大小的数字size可以使用KB、MB、GB和TB后缀，默认的后缀为MB。size中不能使用小数，其最小值为512KB，默认值为1MB。主文件的size不能小于模板数据库中的主文件。创建数据库DatabasePrinciplesandApplications利用Transact-SQL语句创建数据库MAXSIZE：指定操作系统文件可以增长到的最大尺寸。如果没有指定，则文件可以不断增大直到充满磁盘。FILEGROWTH：指定文件每次增加容量的大小，当指定数据为0时，表示文件不增长。增加量可以确定为以KB、MB作后缀的字节数或以%作后缀的被增加容量文件的百分比来表示。默认后缀为MB。如果没有指定FILEGROWTH，则默认值为10%，每次扩容的最小值为64KB。COLLATEcollation_name：指定数据库的默认排序规则。排序规则名称既可以是Windows排序规则名称，也可以是SQL排序规则名称。如果没有指定排序规则，则将SQLServer实例的默认排序规则分配为数据库的排序规则。不能对数据库快照指定排序规则名称。WITH<external_access_option>

：控制外部与数据库之间的双向访问。创建数据库DatabasePrinciplesandApplications利用Transact-SQL语句创建数据库【例3-1】使用CREATEDATABASE创建一个新的数据库，名称为“STUDENT1”，其他所有参数均取默认值。

CREATEDATABASESTUDENT1【例3-2】创建数据库名为“STUDENT2”的数据库，包含一个主数据文件和一个事务日志文件。主数据文件的逻辑名为“STUDENT2_DATA”，操作系统文件名为“STUDENT2_DATA.MDF”，初始容量大小为5M，最大容量为20M，文件的增长量为20%。事务日志文件的逻辑文件名为“STUDENT2_LOG”，物理文件名为“STUDENT2_LOG.LDF”，初始容量大小为5M，最大容量为10M，文件增长量为2M。数据文件与事务日志文件都放在D:\SQLDATA目录。创建数据库DatabasePrinciplesandApplications利用Transact-SQL语句创建数据库CREATEDATABASESTUDENT2ONPRIMARY(NAME='STUDENT2_DATA',FILENAME='D:\SQLDATA\STUDENT2_DATA.MDF',SIZE=5MB,MAXSIZE=20MB,FILEGROWTH=20%)LOGON(NAME='STUDENT2_LOG',FILENAME='D:\SQLDATA\STUDENT2_LOG.LDF',SIZE=5MB,MAXSIZE=10MB,FILEGROWTH=2MB)GO说明：必须事先在D盘建立好SQLDATA文件夹。创建数据库DatabasePrinciplesandApplications利用Transact-SQL语句创建数据库【例3-3】创建一个指定多个数据文件和日志文件的数据库。该数据库名称为TEACHINGDB，有1个5MB和1个10MB的数据文件和2个5MB的事务日志文件。数据文件逻辑名称为TEACHINGDB1和TEACHINGDB2，物理文件名为TEACHINGDB1.mdf和TEACHINGDB2.ndf。主文件是TEACHINGDB1，由PRIMARY指定，两个数据文件的最大尺寸分别为无限大和100MB，增长速度分别为10%和1MB。事务日志文件的逻辑名为TEACHINGDBLOG1和TEACHINGDBLOG2，物理文件名为TEACHINGDBLOG1.ldf和TEACHINGDBLOG2.ldf，最大尺寸均为50MB，文件增长速度为1MB。要求数据库文件和日志文件的物理文件都存放在D盘的SQLDATA文件夹下。DatabasePrinciplesandApplicationsCREATEDATABASETEACHINGDBONPRIMARY(NAME=TEACHINGDB1,FILENAME='D:\SQLDATA\TEACHINGDB1.mdf',SIZE=5,MAXSIZE=unlimited,FILEGROWTH=10%),(NAME=TEACHINGDB2,FILENAME='D:\SQLDATA\TEACHINGDB2.ndf',SIZE=10,MAXSIZE=100,FILEGROWTH=1)LOGON(NAME=TEACHINGDBLOG1,FILENAME='D:\SQLDATA\TEACHINGDBLOG1.ldf',SIZE=5,MAXSIZE=50,FILEGROWTH=1),(NAME=TEACHINGDBLOG2,FILENAME='D:\SQLDATA\TEACHINGDBLOG2.ldf',SIZE=5,MAXSIZE=50,filegrowth=1)GO创建数据库DatabasePrinciplesandApplications利用Transact-SQL语句创建数据库【例3-4】在【例4-3】创建一个新的文件组，并将逻辑名称为TEACHINGDB2的文件加入此文件组中。CREATEDATABASETEACHINGDBONPRIMARY(NAME=TEACHINGDB1,FILENAME='D:\SQLDATA\TEACHINGDB1.mdf',SIZE=5,MAXSIZE=unlimited,FILEGROWTH=10%),FILEGROUPTEACHINGDB_DATA(NAME=TEACHINGDB12,FILENAME='D:\SQLDATA\TEACHINGDB2.ndf',SIZE=10,MAXSIZE=100,FILEGROWTH=1)创建数据库DatabasePrinciplesandApplications利用Transact-SQL语句创建数据库LOGON(NAME=TEACHINGDBLOG1,FILENAME='D:\SQLDATA\TEACHINGDBLOG1.ldf',SIZE=5,MAXSIZE=50,FILEGROWTH=1),(NAME=TEACHINGDBLOG2,FILENAME='D:\SQLDATA\TEACHINGDBLOG2.ldf',SIZE=5,MAXSIZE=50,filegrowth=1)GO创建数据库DatabasePrinciplesandApplications修改数据库建好数据库后，可以对其进行修改。修改数据库包括增减数据文件和日志文件、修改文件属性（包括更改文件名和文件大小）、修改数据库选项等。可以使用SQLServerManagementStudio与Transact-SQL语句两种方法修改数据库。创建数据库DatabasePrinciplesandApplications使用SQLServerManagementStudio修改数据库启动SQLServerManagementStudio，在对象资源管理器中，右击所需要修改的数据库如students，在快捷菜单中选择“属性”命令打开“数据库属性”窗口，如图3.5所示。在“数据库属性”窗口的“常规”选项卡中，显示的是数据库的基本信息，这些信息不能修改。单击“文件”选项卡，如图3.6所示，可以修改数据库的逻辑名称、初始大小、自动增长等属性，也可以根据需要添加数据文件和日志文件，还可以更改数据库的所有者。创建数据库DatabasePrinciplesandApplications创建数据库DatabasePrinciplesandApplications图3.5数据库属性“文件”选项卡创建数据库DatabasePrinciplesandApplications图3.6数据库属性“文件”选项卡使用SQLServerManagementStudio修改数据库在“文件组”选项卡中，可以修改现有的文件组，也可以指定数据库的默认文件组、添加新文件组。在“选项”选项卡中，修改数据库的排序规则。“数据库属性”窗口包含的各种属性，只要需要，就可以选择相应的选项卡来修改。创建数据库DatabasePrinciplesandApplications利用Transact-SQL语句修改数据库创建数据库DatabasePrinciplesandApplicationsTransact-SQL提供了修改数据库的语句ALTERDATABASE，语句格式如下：

ALTERDATABASEdatabase_name--需修改的数据库名

{<add_or_modify_files>--增加或修改数据库文件

|<add_or_modify_filegroups>--增加或修改数据库文件组

|<set_database_options>--设置数据库选项

|MODIFYNAME=new_database_name--数据库重命名

|COLLATEcollation_name--更改排序规则

}[;]利用Transact-SQL语句修改数据库下面说明上述格式的主要参数：(1)database_name:要修改的数据库的名称。(2)<add_or_modify_files>:指定要添加或修改的文件。(3)<add_or_modify_filegroups>:在数据库中添加或删除文件组。(4)<set_database_options>：设置数据库选项。(5)MODIFYNAME=new_database_name:使用指定的名称重命名数据库。(6)COLLATEcollationname:指定数据库的排序规则。<add_or_modify_files>子句的语法：创建数据库DatabasePrinciplesandApplications利用Transact-SQL语句修改数据库

<add_or_modify_files>::=--增加或修改数据库文件语法块

{ADDFILE<filespec>[,...n]--文件属性修改

[TOFILEGROUP{filegroup_name|DEFAULT}] |ADDLOGFILE<filespec>[,...n] |REMOVEFILElogical_file_name |MODIFYFILE<filespec>}下面说明上述格式的主要参数:(1)ADDFILE:将文件添加到数据库。(2)TOFILEGROUP{filegroup_nameIDEFAULT｝：指定要将指定文件添加到的文件组。(3)ADDLOGFILE:添加日志文件。(4)REMOVEFILElogical_file_name:从SQLServer的实例中删除逻辑文件说明并删除物理文件。除非文件为空，否则无法删除文件。MODIFYFILE:指定应修改的文件，一次只能更改一个<filespec>属性。创建数据库DatabasePrinciplesandApplications利用Transact-SQL语句修改数据库<add_or_modify_filegroups>的语法：<add_or_modify_filegroups>::={|ADDFILEGROUPfilegroup_name--增加文件组

|REMOVEFILEGROUPfilegroup_name--删除文件组

|MODIFYFILEGROUPfilegroup_name--修改文件组

{<filegroup_updatability_option>

|DEFAULT

|NAME=new_filegroup_name}}创建数据库DatabasePrinciplesandApplications利用Transact-SQL语句修改数据库【例3-5】为STUDENT2数据库增加容量，原来数据库文件STUDENT2_DATA的初始分配空间为5M，现在将STUDENT2_DATA的分配空间增加至20M。

ALTERDATABASESTUDENT2 MODIFYFILE (NAME=STUDENT2_DATA, SIZE=20MB)创建数据库DatabasePrinciplesandApplications利用Transact-SQL语句修改数据库【例3-6】为数据库STUDENT2添加文件组studentfgrp，再增加数据文件STUDENT2_DATA1，初始大小10M，最大50M，按照5%增长，并将此文件添加到studentfgrp中。ALTERDATABASESTUDENT2ADDFILEGROUPSTUDENTFGRPGOALTERDATABASESTUDENT2ADDFILE(NAME='STUDENT2_DATA1',FILENAME='D:\DATA\STUDENT2_DATA1.NDF',SIZE=10MB,MAXSIZE=50MB,FILEGROWTH=5%)TOFILEGROUPSTUDENTFGRPGO创建数据库DatabasePrinciplesandApplications利用Transact-SQL语句修改数据库【例3-7】将数据库STUDENT2中增加的数据文件STUDENT2_DATA1删除。

ALTERDATABASESTUDENT2

REMOVEFILESTUDENT2_DATA1

指定当前上下文数据库

USEdatabase

【例】将STUDENT2数据库指定为当前缺省使用的数据库

USESTUDENT2创建数据库DatabasePrinciplesandApplications删除数据库可以使用SQLServerManagementStudio与Transact-SQL语句两种方法删除数据库。使用SQLServerManagementStudio删除数据库打开SQLServerManagementStudio，连接到服务器（即实例）。在对象资源管理器中展开“数据库”目录，右击要删除的数据库，在弹出的快捷菜单中选择“删除”命令，在随后出现的“删除对象”对话框中单击“确定”按钮，即可完成对指定数据库的删除操作。创建数据库DatabasePrinciplesandApplications删除数据库使用Transact-SQL语句删除数据库Transact-SQL提供了数据库删除语句DROPDATABASE。具体格式如下：DROPDATABASE{database_name}[,...n][;]其中：database_name为指定要删除的数据库的名称。该语句一次可以删除一个或多个数据库。【例3-8】删除已创建的数据库TEACHINGDB

DROPDATABASETEACHINGDB说明：用户只能根据自己的权限删除用户数据库；不能删除当前正在使用（正打开供用户读写）的数据库；无法删除系统数据库（msdb、model、master、tempdb。创建数据库DatabasePrinciplesandApplications数据库的分离和附加在SQLServer中，除了系统数据库外，其他数据库都可以从服务器的管理中进行分离，以脱离服务器的管理，同时保持数据文件与日志文件的完整性和一致性。而分离出来的数据库可以附加到其他SQLServer服务器上，构成完整的数据库。分离数据库(1)在SQLServerManagementStudio的“对象资源管理器”窗口中展开“数据库”结点，右击需要分离的数据库，如students，从弹出的快捷菜单中选择“任务→分离”命令。(2)在弹出的对话框(如图3.7所示)中设置数据库students的分离参数，单击“确定”按钮，即可完成操作。创建数据库DatabasePrinciplesandApplications创建数据库DatabasePrinciplesandApplications图3.7分离数据库数据库的分离和附加其中的主要参数项含义如下:删除连接：是否断开与指定服务器的连接。更新统计信息：选择在分离数据库之前是否更新过时的优化统计信息。默认情况下，分离操作将在分离数据库时保留过期的优化统计信息。保留全文目录：选择是否保留与数据库相关联的所有全文目录。状态：显示数据库分离前是否“就绪”或“未就绪”。如果状态是“未就绪”，则“消息”列将显示有关数据库的超链接信息。当数据库涉及复制时，“消息”列将显示Databasereplicated。数据库有一个或多个活动连接时，“消息”列将显示<活动连接数>个活动连接；例如，1个活动连接。在可以分离数据列之前，必须选中“删除连接”复选框来断开与所有活动连接的连接。创建数据库DatabasePrinciplesandApplications数据库的分离和附加附加数据库(1)右击“对象资源管理器”窗口中的“数据库”，从弹出的快捷菜单中选择“附加”命令。(2)在弹出的“附加数据库”对话框中，单击“添加”按钮。在弹出的“定位数据库文件”对话框中，选择要添加的数据库的主数据文件(文件后缀为.mdf)(3)单击“确定”按钮，返回“附加数据库”对话框(图3.8所示)。在“附加数据库”对话框中可以修改附加后数据库的名称。(4)单击“确定”按钮。创建数据库DatabasePrinciplesandApplications创建数据库DatabasePrinciplesandApplications图3.8附加数据库3.2.1模式定义对于数据库设计者，模式代表一个完整数据库整体的逻辑设计。对于SQL，模式是基本表、视图、索引等数据库对象一个容器。定义模式相当于定义了一个命名空间，在该空间中可以进一步定义各种数据库对象。模式定义DatabasePrinciplesandApplications创建模式SQL中创建模式的语句为CREATESCHEMA：CREATESCHEMA {

schema_name

|AUTHORIZATIONowner_name

|schema_nameAUTHORIZATIONowner_name}模式定义DatabasePrinciplesandApplications创建模式【例3-9】为数据库STUDENT2创建一个图书借阅模式borrowUSESTUDENT2CREATESCHEMAborrow;默认情况下，模式属于创建它的用户。如果要将模式分配给其它拥有者而不是创建者本身，可以在创建模式时使用AUTHORIZATION子句。模式定义DatabasePrinciplesandApplications创建模式【例3-10】为用户LI创建一个图书借阅模式borrowCREATESCHEMAborrowAUTHORIZATIONLI;或者CREATESCHEMAAUTHORIZATIONLI;该语句没有指定模式名，则其用户名即为隐含的模式名模式定义DatabasePrinciplesandApplications在模式中创建数据库对象在创建模式时，直接创建数据库对象CREATESCHEMA模式名AUTHORIZATION用户名

表定义子句|视图定义子句|授权定义子句【例3-11】为用户LI创建一个图书借阅模式borrow，并且在该模式下定义读者数据表readerCREATESCHEMAborrowAUTHORIZATIONLICREATETABLEreader(

noCHAR(5)PRIMARYKEY, nameCHAR(20),

genderCHAR(2),

ageINT, specialtyCHAR(20));模式定义DatabasePrinciplesandApplications在模式中创建数据库对象在数据库对象前限定模式名【例3-12】在图书借阅模式borrow下定义图书数据表bookCREATETABLEborrow.book(

isbnCHAR(12)PRIMARYKEY, nameCHAR(20),

authorCHAR(10),

publisher

CHAR(30),

pricemoney); 模式定义DatabasePrinciplesandApplications在模式中创建数据库对象使用SETSCHEMA语句指定当前模式

SETSCHEMA模式名其中，模式名即为当前模式。例如， SETSCHEMAborrow;指定当前模式后，就可以直接使用 CREATETABLEreader( ……

);说明：SQLServer中不能使用此语句模式定义DatabasePrinciplesandApplications删除模式在SQL中删除模式的语句为DROPSCHEMA：

DROPSCHEMAschema_name

[CASCADE|RESTRICT]CASCADE（级联）表示在删除模式的同时把该模式中所有的数据库对象一并删除；RESTRICT（限制）表示在删除模式时，如果该模式中已经定义了数据库对象，在拒绝删除该模式。如果在该模式中没有任何数据库对象，则可以删除该模式。模式定义DatabasePrinciplesandApplications删除模式【例3-13】删除图书借阅模式borrowDROPSCHEMAreader_borrowCASCADE;

说明：在SQLServer中没有“级联”和“限制”两个选项。要删除的架构不能包含任何对象。如果架构包含对象，则DROP语句将失败。在SQLServer中SCHEMA称为架构模式定义DatabasePrinciplesandApplications3.2.2基本表定义在数据库中，表是由数据按一定的顺序和格式构成的数据集合，是数据库的主要对象。每一行代表一条记录，每一列代表记录的一个字段。没有记录的表称为空表。在SQLServer2005中，每个数据库最多可包含20亿个表，每个表可包含1,024个字段。每个表通常都有一个主关键字（又称为主码），用于唯一地确定一条记录。在同一个表中不允许有相同名称的字段。基本表定义DatabasePrinciplesandApplications基本表定义对于具体的某一个表，在创建之前，需要确定表的下列特征：表要包含的数据的类型；表中的列数，每一列中数据的类型和长度（如果必要）；哪些列允许空值；是否要使用以及何处使用约束、默认设置和规则；所需索引的类型，哪里需要索引，哪些列是主键，哪些是外键。创建表有两种方法：一种是在SQLServerManagementStudio中创建表，另一种是利用Transact-SQL语句创建表。基本表定义DatabasePrinciplesandApplications利用SQLServerManagementStudio创建表以下表所示的读者信息表结构为例，说明如何利用SSMS在数据库Library中创建reader表。基本表定义DatabasePrinciplesandApplications列名数据类型长度是否为空说明nochar5否主码namechar20否genderchar2否ageint是specialtychar20是telchar15是emailvarchar30是利用SQLServerManagementStudio创建表(1)启动SQLServerManagementStudio，连接到服务器。在“对象资源管理器”中，展开要新建表的数据库Library。(2)右击“表”项，在快捷菜单里选择“新建表”命令。基本表定义DatabasePrinciplesandApplications利用SQLServerManagementStudio创建表(3)弹出如图3.9所示的“表设计器”窗口。在其上半部分对每一列依次输入列名、数据类型(包括列长)及允许空否等基本属性，在表设计器窗口下半部分的列属性，指定列的详细属性。(4)选中要设置为主键的列(如学号)，右击。在弹出的快捷菜单中选择“设置主键”命令，或单击表设计工具栏上的“设置主键”按钮，或执行“表设计器”菜单下的“设置主键”命令，将其设置为主键。(5)定义好表中的所有列后，单击保存按钮或“文件”菜单中的“保存表名”命令，在弹出的“选择名称”对话框中，为该表键入一个名称，单击“确定”按钮。基本表定义DatabasePrinciplesandApplications基本表定义DatabasePrinciplesandApplications图3.9表设计器利用Transact-SQL语句创建表CREATETABLE的语法格式如下：CREATETABLE[数据库名.[架构名].|架构名

.]表名

(列名

数据类型

[列级完整性约束] [,列名

数据类型

[列级完整性约束]

… [,表级完整性约束] [,完整性约束命名子句])基本表定义DatabasePrinciplesandApplications利用Transact-SQL语句创建表【例3-14】在Library数据库中建立“图书”表book，表结构如下表所示。基本表定义DatabasePrinciplesandApplications列名数据类型长度是否为空说明isbnchar11否主码namevarchar50否authorvarchar40否pulisherchar30是pricemoney是categorychar10是利用Transact-SQL语句创建表USELibraryCREATETABLEbook( isbnCHAR(11), /*图书ISBN号*/ nameCHAR(50), /*图书名称*/ authorVARCHAR(40), /*作者*/ publisherVARCHAR(30), /*出版社*/ priceNU