数据库系统技术 项目五 使用T-SQL命令定义数据库和表

项目五使用T-SQL命令定义数据库和表了解T-SQL语言1

T-SQL语言基础2使用T-SQL命令定义数据库3使用T-SQL命令定义表4

了解T-SQL语言初识T-SQL语言T-SQL语言的开发环境T-SQL语言标识符及语法约定SQL语言SQL是StructuredQueryLanguage的缩写，译为结构化查询语言，最早的SQL语言于1979年在IBM公司的关系数据库系统SystemR得到实现。1986年10月美国国家标准化学会（ANSI）采用SQL作为关系数据库管理系统的标准语言，并公布了第一个SQL标准，称为SQL-86。随后国际标准化组织（ISO）也接纳了这一标准，并对其作进一步的完善，这项工作于1989年4月完成，公布后就是我们所说的SQL-89。SQL语言在上面的基础上，ISO和ANSI联手对SQL进行研究和完善，于1992年8月又推出了新的SQL标准—SQL-92（或简称为SQL2）。后来又对SQL-92进行了完善和扩充，于1999年推出了SQL-99（或简称为SQL3），这是最新的SQL版本。现今的SQL语言已经发展成为关系数据库的标准语言，几乎所有的数据库产品都支持SQL语言。当然除了SQL以外，还有其它类似的一些数据库语言，如QBE、Quel、Datalog等，但这些语言仅仅少数人在使用并不是主流的数据库语言。根据功能来划分SQL语言分为四类SQL功能名称SQL功能英文简称和全称SQL语句数据查询DQL（DataQueryLanguage）SELECT数据操纵DML（DataManipulationLanguage）INSERTUPDATEDELETE数据定义DQL（DataDefinitionLanguage）CREATEALTERDROP数据控制DQL（DataControlLanguage）GRANTREVOKET-SQL语言不同的数据库软件厂商一方面采纳SQL语言作为自己的数据库的语言，另一方面又对SQL语言进行了不同程度的扩展。而T-SQL语言正是微软公司在其SQLServer关系数据库系统中的实现。T-SQL语言即事务SQL（Transact-SQL），简称为T-SQL。T-SQL在SQL语言的基础上增加了变量、流程控制、功能函数、系统存储过程等功能，提供了丰富的编程结构。T-SQL是对SQL语言扩充的基础上发展起来的，因此它的核心内容还是SQL语言中的四类语句。T-SQL语言的开发环境SQL语句的执行方式主要有四种，分别是：直接调用执行、嵌入式执行、模块绑定执行和通过调用层接口（CLI）执行。常用的是直接调用执行和通过调用层接口（CLI）执行这两种。演示使用SSMS直接执行T-SQL语句。T-SQL语言中的标识符标识符：在T-SQL语言中，对数据库、表、变量、存储过程、函数等的定义和引用都需要通过其名称（标识符）来完成。我们所说的标识符，实际上就是我们给对象起的名称，本质上是一个字符串。标识符分为常规标识符和分隔标识符两种常规标识符：是不包含空格的标识符，可以不需要使用单引号或方括号将其分隔的标识符。定义常规标识符时要符合以下规则：标识符中首字符必须是英文字母、汉字、数下划线（_）、@和#，首字符后面可以是其它字符，最长不超过128个字符，不能和T-SQL语言中的关键字重复，也不能用@@开头。分隔标识符：是指包含在两个单引号（’’）或者方括号（[]）内的字符串，这些字符串中可以包含空格。T-SQL的语法约定大写字母：代表T-SQL中保留的关键字，如CREATE、SELECT、UPDATE、DELETE等。小写字母：表示表达式、标识符等。竖线“|”：表示参数之间是“或”的关系，用户可以从其中选择使用。大括号“{}”：大括号中的内容为必选参数，其中可以包含多个选项，各个选项之间用竖线分隔，用户必须从选项中选择其中一项。方括号“[]”：方括号内所列出的项为可选项，用户可以根据需要选择使用。省略号“…”：表示重复前面的语法项目。项目五使用T-SQL命令定义数据库和表了解T-SQL语言1

T-SQL语言基础2使用T-SQL命令定义数据库3使用T-SQL命令定义表4

T-SQL语言基础T-SQL中的常量和变量T-SQL中的运算符T-SQL中的函数T-SQL中的批处理T-SQL中的流程控制语句T-SQL中的功能性语句

T-SQL中的常量和变量常量，是表示一个特定数据值的符号，常量的类型取决于它所表示的值的数据类型。在SQLServer中，有字符串常量、二进制常量、BIT常量、日期和时间常量等。变量是指在程序运行过程中，值可以发生变化的量。在T-SQL中可以使用两种类型变量：一种是局部变量，另外一种是全局变量。局部变量是用户自定义的变量，在T-SQL使用局部变量命名必须以“@”开头，如@xh，@xm。局部变量必须先用DECLARE定义后才可使用，其语法如下：DECLARE@变量名变量类型[，@变量名变量类型]T-SQL中的常量赋值在T-SQL中不能像在高级程序语言中使用

变量名=变量值这样的方法来给变量赋值，必须使用SET命令或SELECT命令来给变量赋值，SET命令一次只能给一个变量赋值，而SELECT命令一次可以给多个变量赋值。其语法如下：SET@局部变量名=变量值SELECT@局部变量名=变量值[，@变量名变量类型]演示教材中的例5-1全局变量含义@@SERVERNAME返回运行SQLServer数据库的地服务器名称@@VERSION返回SQLServer当前安装版本信息@@OPTIONS返回当前SET选项的信息@@TRANCOUNT返回当前连接的活动事务数@@CPU_BUSY返回SQLServer最近一次启动以来CPU工作时间@@ROWCOUNT返回受前一条SQL语句影响的行数@@ERROR返回最后执行的SQL语句的错误代码T-SQL中的全局变量

全局变量是由SQLServer系统定义并使用的变量，用户不能定义全局变量，但可以使用全局变量。全局变量通常存储SQLServer的配置参数和性能统计数据，用户可在程序中用全局变量来测试系统性能或获取T-SQL命令执行后的状态值。使用全局变量必须注意的事项全局变量全部以“@@”为标记全局变量不是由用户定义的，而是由数据库服务器定义的。用户只能使用SQLServer数据库系统预先定义的全局变量。引用全局变量时，必须以标记符“@@”开头。局部变量的名称不能与全局变量的名称相同，否则会在应用中出错。T-SQL中的运算符运算符是一种符号，用来指定在一个或多个表达式中执行的操作。SQLServer提供的运算符有：算术运算符赋值运算符位运算符比较运算符逻辑运算符字符串连接运算符一元运算符等。T-SQL中的运算符重点掌握算术运算符、赋值运算符、比较运算符、逻辑运算符和字符串连接运算符。演示教材中例5-2至例5-5。T-SQL中的运算符SQLServer提供了许多内部函数，可以分为数学函数、字符串函数、日期函数、聚合函数、系统函数及用户自定义函数等。函数给用户提供了强大的功能，使用户不需要编写很多代码就能够完成某些任务和操作。演示教材中例5-6至例5-8。T-SQL中的批处理批处理是指包含一条或多条T-SQL语句的语句组，批处理中的所有T-SQL语句编译成一个执行计划，从应用程序一次性地发送到SQLServer数据库服务器执行。编写批处理时，GO语句是批处理命令的结束标志，当编译器读取到GO语句时，会把GO语句前的所有语句当作一个批处理，并将这些语句打包发送给数据库服务器。GO语句本身不是T-SQL语句的组成部分，只是一个表示批处理结束的前端指令。T-SQL中的流程控制语句流程控制语句用于控制SQL语句、语句块的执行顺序，T-SQL中的流程控制语句以及功能如下表所示。语句功能BEGIN…END定义语句块IF…ELSE判断语句IF…EXISTS检测语句CASE…WHEN多分支判断语句WHILE循环语句BREAK跳出循环语句CONTINUE重新启用循环语句GOTO跳转语句RETUEN返回语句WAITFOR延期执行语句T-SQL中的流程控制语句演示教材中例5-9至例5-11。T-SQL中的注释语句注释是指程序代码中不执行的文本字符串，是对程序的说明，可以提高程序的可读性，使程序代码更易于维护，一般嵌入在程序中并以特殊的标记显示出来。在T-SQL中，注释可以包含在批处理、存储过程、触发器中。T-SQL中有两种类型的注释符：

--：这是ANSI标准的两个连字符组成的注释符，用于单行注释。

/*…*/：这是与C语言相同的程序注释符，/*用于注释文字的开头，*/用于注释文字的结尾，可以在程序中标识多行文字为注释语句。T-SQL中的输出语句输出语句PRINT语句用于把消息传递到客户端应用程序，通常是在用户屏幕上显示，消息字符串最长可达8000个字符，超过8000个的任何字符均被截断。PRINT语句只能传输文本型的字符串，或者是单个的字符型变量。PRINT语句也可以传递全局变量，但只能是字符类型的全局变量。选项设置语句（SET）

SQLServer2008数据库系统中设置了一些选项，用以影响服务器处理特定条件的方式，这些选项存在于用户与服务器的连接期间或用户的存储过程和触发器中，可以使用SET语句设置这些参数。语法如下：

SETcondition{on|off|Value}部分选项参数设置选项值含义SETSTATISTICSTIMEON让服务器返回语句的运行时间SETSTATISTICSIOON让服务器返回请示的物理和逻辑页数SETSHOWPLANON让服务器返回当前正在运行的计划中的查询SETPARSONLYON让服务器对查询进行语法检查但并不运行SETROWCOUNTn让服务器只返回查询中的前n行SETNOCOUNTON不必报告查询所返回的行数项目五使用T-SQL命令定义数据库和表了解T-SQL语言1

T-SQL语言基础2使用T-SQL命令定义数据库3使用T-SQL命令定义表4

使用T-SQL命令定义数据库数据库在Windows操作系统中体现为数据库文件，数据库文件包括数据文件和日志文件两大类。我们在这一任务中介绍创建数据库的命令CREATEDATABASE、修改数据库命令ALTERDATABASE和删除数据库命令DROPDATABASE。创建数据库命令CREATEDATABASE最简单的CREATEDATABASE命令格式

CREATEDATABASEdatabase_name指定数据库文件名称和位置的CREATEDATABASE命令格式

CREATEDATABASEdatabase_nameONPRIMARY(--这里的关键字PRIMARY可省略，用于主文件的定义

NAME=logical_file_name,--设置主数据文件的逻辑名称

FILENAME=os_file_name

--设置主数据文件的操作系统名称

)LOGON(--LOGON用于日志文件的定义

NAME=logical_file_name,--设置日志文件的逻辑名称

FILENAME=os_file_name

--设置日志文件的操作系统名称

)创建数据库命令CREATEDATABASE演示教材中例5-14至例5-16创建数据库命令之完整格式CREATEDATABASE数据库名

[ON

[<filespec>[,...n]]

[,<filegroup>[,...n]]

][LOGON{<filespec>[,...n]}][COLLATEcollation_name

][FORLOAD|FORATTACH]ON：指定主数据文件的相关信息；LOGON：指定日志文件的相关信息；指定数据库的默认排序规则。{Windows_collation_name}|{SQL_collation_name}

指定从现有的一组操作系统文件中附加数据库。与早期版本的MicrosoftSQLServer兼容。

<filespec>::=[PRIMARY]

([NAME=logical_file_name

,]

FILENAME='os_file_name'

[,SIZE=size

]

[,MAXSIZE={max_size|UNLIMITED}]

[,FILEGROWTH=growth_increment

])[,...n]<filegroup>::=FILEGROUPfilegroup_name<filespec>[,...n]说明主数据文件或日志文件的逻辑文件名、OS文件名、大小、最大大小和增长率！说明新建文件组和属于组内的文件项！创建数据库命令之完整格式建立数据库实例一CREATEDATABASE设备管理ONPRIMARY

（

NAME='设备管理_data'，--主数据文件的逻辑名

FILENAME=‘D：\data\设备管理_data.mdf’，

--主数据文件的物理名

SIZE=10MB，--初始大小

MAXSIZE=UNLIMTED

）LOGON

（

NAME='设备管理_log'，--日志文件的逻辑名

FILENAME=‘D：\data\设备管理_log.ldf’，

--日志文件的物理名

SIZE=1MB，

MAXSIZE=5MB--最大大小）示例—

创建指定数据文件和事务日志文件的数据库示例创建名为Sales的数据库。因为没有使用关键字PRIMARY，第一个文件(Sales_dat)成为主文件。因为Sales_dat

文件的SIZE参数没有指定MB或KB，因此默认为MB，以兆字节为单位进行分配。Sales_log

文件以兆字节为单位进行分配，因为SIZE参数中显式声明了MB后缀。CREATEDATABASESalesON(NAME=Sales_dat,FILENAME='e:\data\saledat.mdf',SIZE=10,MAXSIZE=50,FILEGROWTH=5)LOGON(NAME='Sales_log',FILENAME='e:\data\salelog.ldf',SIZE=5MB,MAXSIZE=25MB,FILEGROWTH=5MB)

示例:指定多个数据文件和事务日志文件创建数据库后面的示例使用三个100MB的数据文件和两个100MB的事务日志文件创建了名为Archive的数据库。主文件是列表中的第一个文件，并使用PRIMARY关键字显式指定。事务日志文件在LOGON关键字后指定。注意FILENAME选项中所用的文件扩展名：主要数据文件使用.mdf，次要数据文件使用.ndf，事务日志文件使用.ldf。CREATEDATABASEArchive

ONPRIMARY(NAME=Arch1,FILENAME='c:\programfiles\microsoft

sqlserver\mssql\data\archdat1.mdf',SIZE=100MB,MAXSIZE=200,FILEGROWTH=20),(NAME=Arch2,FILENAME='c:\programfiles\microsoft

sqlserver\mssql\data\archdat2.ndf',SIZE=100MB,MAXSIZE=200,FILEGROWTH=20),(NAME=Arch3,FILENAME='c:\programfiles\microsoft

sqlserver\mssql\data\archdat3.ndf',SIZE=100MB,MAXSIZE=200,FILEGROWTH=20)LOGON(NAME=Archlog1,FILENAME='c:\programfiles\microsoft

sqlserver\mssql\data\archlog1.ldf',SIZE=100MB,MAXSIZE=200,FILEGROWTH=20),(NAME=Archlog2,FILENAME='c:\programfiles\microsoft

sqlserver\mssql\data\archlog2.ldf',SIZE=100MB,MAXSIZE=200,FILEGROWTH=20)使用文件组创建数据库后面的示例使用三个文件组创建名为sales的数据库；主文件组包含文件Spri1_dat和Spri2_dat。指定这些文件的FILEGROWTH增量为15%。

名为SalesGroup1的文件组包含文件SGrp1Fi1和SGrp1Fi2。

名为SalesGroup2的文件组包含文件SGrp2Fi1和SGrp2Fi2。CREATEDATABASESalesONPRIMARY(NAME=SPri1_dat,FILENAME='c:\programfiles\microsoft

sqlserver\mssql\data\SPri1dat.mdf',SIZE=10,MAXSIZE=50,FILEGROWTH=15%),(NAME=SPri2_dat,FILENAME='c:\programfiles\microsoft

sqlserver\mssql\data\SPri2dt.ndf',SIZE=10,MAXSIZE=50,FILEGROWTH=15%),FILEGROUPSalesGroup1(NAME=SGrp1Fi1_dat,FILENAME='c:\programfiles\microsoft

sqlserver\mssql\data\SG1Fi1dt.ndf',SIZE=10,MAXSIZE=50,FILEGROWTH=5),(NAME=SGrp1Fi2_dat,FILENAME='c:\programfiles\microsoft

sqlserver\mssql\data\SG1Fi2dt.ndf',SIZE=10,MAXSIZE=50,FILEGROWTH=5),FILEGROUPSalesGroup2(NAME=SGrp2Fi1_dat,FILENAME='c:\programfiles\microsoft

sqlserver\mssql\data\SG2Fi1dt.ndf',SIZE=10,MAXSIZE=50,FILEGROWTH=5),(NAME=SGrp2Fi2_dat,FILENAME='c:\programfiles\microsoft

sqlserver\mssql\data\SG2Fi2dt.ndf',SIZE=10,MAXSIZE=50,FILEGROWTH=5)LOGON(NAME='Sales_log',FILENAME='c:\programfiles\microsoft

sqlserver\mssql\data\salelog.ldf',SIZE=5MB,MAXSIZE=25MB,FILEGROWTH=5MB)附加数据库假设前面已经创建一个包含物理文件的名为Archive的数据库：CREATEDATABASEArchiveONPRIMARY(FILENAME='c:\programfiles\microsoft

sqlserver\mssql\data\archdat1.mdf')FORATTACH

修改数据库命令ALTERdatabase更改数据库名称往数据库中添加文件（扩大数据库）更改数据库文件从数据库中删除文件（缩小数据库）修改数据库使用ALTERDATABASE语句修改数据库的各属性，包括添加或删除文件或文件组、修改文件或文件组的属性等。ALTERDATABASE<数据库名>ADDFILE<文件格式>[TOFILEGROUP<文件组>]|ADDLOGFILE<文件格式>|REMOVEFILE<逻辑文件名>|REMOVEFILEGROUP<文件组名>|MODIFYFILE<文件格式>|MODIFYFILEGROUP<文件组名><文件组属性>其中“文件格式”的格式为：（NAME=<数据文件的逻辑名>[,FILENAME=<数据文件的物理名称>][,SIZE=<数据文件的初始大小>][,MAXSIZE=<数据文件的最大容量>|UNLIMITED][,FILEGROWTH=<数据文件的增长量>]

）更改数据库名称ALTERDATABASEdatabase_name

--database_name是原数据库名MODIFYNAME=new_database_name;

--new_database_name是新数据库名往数据库中添加文件（扩大数据库）ALTERDATABASEdatabase_name

--database_name是要修改的数据库ADDFILE<filespec>

--filespec为新加的文件参数，如文件的逻辑名、操作系统名和

--大小参数等，与建立数据库时的文件参数相同。从数据库中删除文件（缩小数据库）ALTERDATABASEdatabase_name

--database_name是要修改的数据库REMOVEFILElogical_file_name

--logical_file_name为要删除的文件对应的逻辑文件名更改数据库文件ALTERDATABASEdatabase_name

--database_name是要修改的数据库MODIFYFILE<filespec>

--filespec为更改的文件参数，如文件的逻辑名、

--操作系统名和大小参数等。删除数据库DROPDATABASE<数据库名>例如：DROPDATABASEArchive--删除Archive数据库！修改数据库演示并说明教材中例5-17至5-21项目五使用T-SQL命令定义数据库和表了解T-SQL语言1

T-SQL语言基础2使用T-SQL命令定义数据库3使用T-SQL命令定义表4

使用T-SQL命令创建表创建表时首先要确定在哪一个数据库中创建；然后再确定表的名字，每列的类型、宽度；确定列的约束条件、表主键等相关内容。选择数据库，有两种方法。手动选择：在SSMS的工具栏中使用下拉列表框进行选择。命令选择：USE数据库名创建表的基本命令Createtable表名(<列定义>，…)实例：

createtable教师表

(教师号char(4),

教师姓名char(8),

性别char(2),

年龄smallint,

职称char(20))创建表—每列后加列级完整性约束每个列后面的完整性约束称为列级完整性约束，共有六种。

DEFAULT<常量表达式>：默认值约束；

NULL/NOTNULL：空值/非空值约束；

PRIMARYKEY：主码约束；

UNIQUE：单值约束；

REFERENCE<父表名>(<主码>)：外码约束；

CHECK(<逻辑表达式>)：检查约束。创建表—表级完整性约束“表级完整性约束”在所有列定义后给出，包括四种。

PRIMARYKEY(<列名>,…)：--主码约束；

UNIQUE(<列名>,…)：--单值约束；

FOREIGNKEY(<列名>,…)REFERENCE<父表名>(<主码>)

--外码约束；

CHECK(<逻辑表达式>)：--检查约束。表/列级完整性约束除默认值约束和空值/非空值约束外，对于其他约束，若只涉及到一个列时，则即可以作为列级完整性约束又可以作为表级完整性约束，可取其一使用；若涉及到多个列时，则只能作为表级完整性约束。每个约束前均可加约束名:CONSTRAINT<约束名>…创建表命令—示例2createtable学生(

学生号

char(8)primarykey,

姓名

char(6)notnullunique,

性别

char(2)notnull

check(性别=‘男’or性别=‘女’),

出生日期

datetime

check(出生日期<‘1996-12-31’),

专业

char(10),

年级

int

check(年级>=1and年级<=4))createtable课程(

课程号

char(4)primarykey,

课程名

char(10)notnullunique,

课程学分

int

check(课程学分

>=1and课程学分

<=6))createtable选课(

学生号

char(8)references学生(学生号),

课程号

char(4)references课程(课程号),

成绩

int

check(成绩>=0and成绩<=100),

primarykey(学生号,课程号))建立表—标识列约束标识列约束只能用于数值型数据类型，其基本形式为：IDENTITY(初始值,步长值)实例：

createtable订单

(订单号int

identity(1001,1)primarykey,

货品名称varchar(20)notnull,

客户编号int

数量intnotnullcheck(数量>0),

总金额money,

订货日期datetimedefaultgetdate())建立表—使用uniqueidentifier