教学课件：《SQL-Server-2008数据库管理与开发》

SQLServer2008数据库管理与开发项目一创建和维护数据库数据库表是存储数据的基本单元，在创建数据库表之前，必须做出有关表结构的决策，包括表要包含的内容，反应到表结构上是指表将要包含哪些字段以及这些字段的数据类型。目录

任务一创建数据库表

任务二修改和删除数据库表

任务三认识主键和外键创建数据库表任务一：创建数据库表准备知识1.数据库相关概念2.数据类型3.空值4.添加Identity列1.数据库相关概念1.数据库相关概念（1）数据数据(Data)实际上就是描述事物的符号记录。计算机中的数据一般分为两部分，其中一部分与程序仅有短时问的交互关系，随着程序的结束而消亡，它们称为临时性(Transient)数据，这类数据一般存放于计算机内存中；而另一部分数据则对系统起着长期持久的作用，它们称为持久性(Persistent)数据。数据库系统中处理的就是这种持久性数据。近10多年来，数据在软件系统中的地位产生了变化，在数据库系统及数据库应用系统中数据已占有主体地位，而程序已退居附属地位。在数据库系统中需要对数据进行集中、统一的管理，以达到数据被多个应用程序共享的目标。小提示：1.数据库相关概念1.数据库相关概念（2）数据库数据库(Database，简称DB)是数据的集合，它具有统一的结构形式并存放于统一的存储介质内，是多种应用数据的集成，并可被各个应用程序所共享。数据库存放数据是按数据所提供的数据模式存放的，它能构造复杂的数据结构以建立数据问内在联系与复杂的关系，从而构成数据的全局结构模式。数据库中的数据具有“集成”、“共享”之特点，亦即是数据库集中了各种应用的数据，进行统一的构造与存储，而使它们可被不同应用程序所使用。1.数据库相关概念1.数据库相关概念（3）数据库管理系统数据库管理系统(DatabaseManagementSystem，简称DBMS)是数据库的机构，它是一种系统软件，负责数据库中的数据组织、数据操纵、数据维护、控制及保护和数据服务等。数据库中的数据是具有海量级的数据，并且其结构复杂，因此需要提供管理工具。数据库管理系统是数据库系统的核心。注意啦！数据库中的数据具有共享性，而数据共享可能会引发数据的非法使用，因此必须要对数据正确使用作出必要的规定，并在使用时做检查，这就是数据的安全性。1.数据库相关概念1.数据库相关概念（4）数据库管理员由于数据库的共享性，因此对数据库的规划、设计、维护、监视等需要有专人管理，称他们为数据库管理员(DatabaseAdministrator简称DBA)。其主要工作如下：1)数据库设计(DatabaseDesign)。2)数据库维护。3)改善系统性能，提高系统效率。知识链接当效率下降时，DBA需采取适当的措施，如进行数据库的重组、重构等。1.数据库相关概念1.数据库相关概念（5）数据库系统数据库系统(DatabaseSystem简称DBS)由如下几部分组成：数据库(数据)、数据库管理系统(软件)、数据库管理员(人员)、系统平台之一——硬件平台(硬件)、系统平台之二——软件平台(软件)。这五个部分构成了一个以数据库为核心的完整的运行实体，称为数据库系统。1.数据库相关概念1.数据库相关概念在数据库系统中，硬件平台包括：计算机：它是系统中硬件的基础平台，目前常用的有微型机、小型机、中型机、大型机及巨型机。网络：过去数据库系统一般建立在单机上，但是近年来它较多的建立在网络上，从目前形势看，数据库系统今后将以建立在网络上为主，而其结构形式又以客户／服务器(C／S)方式与浏览器／服务器(B／S)方式为主。1.数据库相关概念1.数据库相关概念在数据库系统中，软件平台包括：操作系统：它是系统的基础软件平台，目前常用的有各种UNIX(包括LINUX)与WINDOWS两种。数据库系统开发工具：为开发数据库应用程序所提供的工具。接口软件：在网络环境下数据库系统中数据库与应用程序，数据库与网络间存在着多种接口，它们需要用接口软件进行联接，否则数据库系统整体就无法运作。1.数据库相关概念1.数据库相关概念（6）数据库应用系统(DatabaseApplicationSystem简称DBAS)利用数据库系统进行应用开发可构成一个数据库应用系统，数据库应用系统是数据库系统再加上应用软件及应用界面这三者所组成，具体包括：数据库、数据库管理系统、数据库管理员，硬件平台、软件平台、应用软件、应用界面。其中应用软件是由数据库系统所提供的数据库管理系统(软件)及数据库系统开发工具所书写而成，而应用界面大多由相关的可视化工具开发而成。1.数据库相关概念1.数据库相关概念数据库应用系统的7个部分以一定的逻辑层次结构方式组成一个有机的整体。如果不计数据库管理员(人员)并将应用软件与应用界面记成应用系统，则数据库应用系统的结构如图所示。数据库系统的软硬件层次结构图1.数据库相关概念1.数据库相关概念下面以一个用户读取某数据记录为例，展示在数据库系统中访问数据的具体执行过程。数据库访问数据的步骤如果用户是更新一个记录内容，则执行过程类似。首先读出目标记录，并在用户工作区中进行修改，然后向DBMS发出“写回修改数据”的数据库指令即可。拓展提高：2.数据类型2.数据类型在创建数据库表时，你必须为每一表列指定数据库类型。SQLServer提供了许多内建系统数据库类型，这些类型大致可以分成以下几类：数值类型。如Int，Numeric等。字符类型。如Char，Varchar等。文本和图像。如Text和Image等。时间日期类型。如Datetime，Smalldatetime等。其他特殊数据类型。如Table，Sysname和Uniqueidentifier等。2.数据类型2.数据类型首先看看数值类型。下表列出了所有的数值类型的数据类型以及它们的存储容量：数据类型描述存储大小Bit值为1、0或Null的整型数据类型，Bit列不抱括索引。1字节tinyInt介于0～255之间的整数数据1字节smallInt介于-2^15(-32768)与2^15-1(32767)之间的整数数据2字节Int介于-2^31(-2147483648)与2^31-1（-2147483647）之间的整数数据4字节BigInt一个8字节的整数数据8字节Binary[n]n字节的固定长度的二进制数据，n是介于1～8000之间的一个值。当一列的数据输入差别很小时，请使用Binary。N+2字节Varbinary[n]n字节可变长度的二进制数据，n是介于1～8000之间的一个值。当一列的数据输入差别很大时，请使用Varbinary。输入数据的实际长度+2字节。实际长度可以是0字节Varbinary(max)这是SQLServer2005新增的类型，max最大可以到达2^31-1字节，即。当长度超过8000字节时，请使用Varbianry(max)输入数据的实际长度+2字节。实际长度可以是0字节2.数据类型2.数据类型Decimal[p,[s]]固定精度和刻度的数字。精度(p)指被保存数字的总位数，刻度(s)指定数字的小数点后面的位数。根据精度不同情况，其大小可以在5~17字节之间Numeric[p,[s]]Numeric是Decimal的同义词。在功能上等价于Decimal数据类型根据精度不同情况，其大小可以在5~17字节之间Real范围在-3.40E+38到3.40E+38之间的浮点精度数字数据。4字节Float[(n)]-1.79E+308至

-3.23E-308、0以及3.23E-308至

1.79E+308取决于

n的值Money货币数据。介于-2^63(-922,337,203,685,477.5808)~2^63-1(922,337,203,685,477.5807)之间，精确到货币单位的万分之一8字节Smallmoney货币数据。介于-214,748.3648到

214,748.36474字节数值类型注意啦！所有标有[n]、[(n)]或[p,[s]]表示要指定的字段数据长度，如果未在数据定义或变量声明语句中指定n，则默认长度为1。如果在使用CAST和CONVERT函数时未指定n，则默认长度为30。2.数据类型2.数据类型字符类型下表列出了SQLServer字符类型及其存储容量。数据类型描述存储大小Char[(n)]固定长度，非

Unicode字符数据，长度为

n个字节。n的取值范围为

1至

8,000

n个字节Varchar[(n)]可变长度，非

Unicode字符数据。n的取值范围为

1至

8,000。输入数据的实际长度加

2个字节Varchar[(max)]可变长度，非

Unicode字符数据,max指示最大存储大小是

2^31-1个字节。输入数据的实际长度加

2个字节Nchar[(n)]n个字符的固定长度的

Unicode字符数据。n值必须在

1到

4,000之间2×n字节

+2字节Nvarchar[(n)]可变长度

Unicode字符数据。n值在

1到

4,000之间2×n字节

+2字节Nvarchar[(max)]可变长度

Unicode字符数据，max指示最大存储大小为

2^31-1字节。2×n字节

+2字节2.数据类型2.数据类型文本和图像类型下表列出了文本和图像(image)类型及其存储容量。数据类型描述存储大小Text服务器代码页中长度可变的非

Unicode数据，最大长度为

2^31-1(2,147,483,647)个字符。当服务器代码页使用双字节字符时，存储仍是

2,147,483,647字节。根据字符串，存储大小可能小于

2,147,483,647字节。2^31-1字节Ntext长度可变的

Unicode数据，最大长度为

2^30-1(1,073,741,823)个字符。输入字符个数的两倍（以字节为单位）2^30–1字节Image长度可变的二进制数据，从

0到

2^31-1(2,147,483,647)个字节。2^31-1字节在开发新程序时，尽量避免使用表3-3中列出的数据类型，这些类型在微软SQLServer未来的版本中将被删除。改用Varbinary(max)，Varchar(max)，Nvarchar(max)数据类型。小提示：2.数据类型2.数据类型日期时间类型下表列出了日期时间类型。数据类型描述存储大小Smalldatetime月1日到月6日，精度为1分钟。两个

2字节的整数Dateteime日期和时间数据，从

月

1日到

31日，准确度为三百分之一秒或

3.33毫秒。值被圆整到

.000、.003或

.007毫秒增量。两个4字节Timestamp公开数据库中自动生成的唯一一个二进制数字的数据类型。Timestamp通常用作给表行加版本戳的机制。一个表只能有一个Timestamp类型字段。8字节2.数据类型2.数据类型其他特殊数据类型下表列出了其他特殊数据类型。数据类型描述存储大小Sysname由特殊系统提供的、SQLServer用户定义的数据类型。SQLServer将Sysname类型定义为Nvarchar(128)，可以存储128个Unicode字符(或256字节)256字节Xml这是SQLServer2005新增的数据类型，用来存储

Xml文档和片段Xml数据类型实例的存储表示形式不能超过2GBUniqueidentifier全局唯一标识符

(GUID)。可以通过NEWID()函数产生。16字节Sql_Variable用于存储

SQLServer2005支持的各种数据类型（不包括

Text、Ntext、Image、Timestamp和

Sql_Variant）的值。最大长度可以是

8016个字节Table类似于使用临时表，此类型包括列表和数据类型，可以用来定义本地变量或用于用户定义的函数的返回值。随着表定义的变化而变化3.空值3.空值Null值是一个未知值，将该值引用为Null。列值的为空性是指该列接受或拒绝Null值的能力。一列中的Null值通常表明对于一个特殊的数据行，该列中没有输入项，因为该值既不为空也不为0；其真实的值是未知的——因此，没有相等的两个值。一般来说，都该避免使用Null值。因为对这些值的更新或查询，以及对有这些值的列的一些选项的设置都会更加复杂，例如主键和Identity属性都不能在为空性列中使用。知识链接如果用户需要的信息仍然不可用，则可能需要为空列，例如，客户信息的初始化，有些信息在初始化时可能未知，需要以后补充，这些需要以后补充的信息字段可以设为Null值。4.添加Identity列4.添加Identity列在创建表时，可以通过向列定义中添加Identity属性来将某一列指定为标识列。创建带有Identity属性的列时，SQLServer将根据种子值和增量值自动为该列生成一个行值，默认情况下不允许手工为Identity列插入行值，除非将标识列的IDENTITY_INSERT属性设置为ON。种子(seed)值是插入到表中的第一行的标识值。增量值（Increment）是SQLServer为了连续插入而递增标识值的数量。当每次插入行时，SQLServer都将为该行的标识列指派一个当前的标识值。被插入的下一行收到这个标识值，它是一个比当前最大标识值更大的增量。这样，每个被插入的行都将收到一个唯一的标识符。为了地唯一标识某一行，通常都将标识列用作表中的主键约束。

创建数据库表任务实施小王是某学校的计算机老师，目前接到校方通知，为了加强对学生的管理，小王需要统计本校在校学生人数及学生的相关信息。任务：创建数据库表任务分析：理解牢记！因为SQLServer中，读者可以通过创建数据库表来进行数据的统计与整理，并有不同的数据库类型可供选择。因此，小王决定通过建立数据库表来进行统计。任务：创建数据库表创建数据库表任务实施

1）打开SQLServerManagementStudio并连接到数据库服务器，在“对象资源管理器”中展开“数据库”，接着展开“数据库”/“DataCollationSys”节点，右键单击表文件夹。创建表2）在弹出式菜单中执行”新建表(N)…”命令，将出现下图所示的界面。添加表列3）添加完表所需的所有字段后，单击”保存”按钮，将弹出下图所示的保存表界面，在“输入表名称”编辑框中输入表的名称后单击”确定”按钮即可。保存表4）在创建表的时候，可以为表指定主键。设置主键5）在步骤二的界面中，在“列名”列中输入AreaID，“数据类型”列中选择int数据类型，去掉“允许空”列中“√”，在“列属性”中，展开“标识规范”，设置“（是标识）”属性值为“是”，“标识增量”属性为“1”（缺省值为1），标识种子属性为“1”（缺省值为1）。同样地，增加列Area，分别指定列名“Area”，数据类型varchar(20)，允许空。将字段AreaID设置为主键，完成后保存表名为Area_Tbl。表Area_Tbl的结构如图所示。保存表6）可以采用编码的方式，使用T-SQL来创建表。创建表的语法为：CREATETABLE

[database_name.[owner].|owner.]table_name

({<column_definition>

|column_nameAScomputed_column_expression

|<table_constraint>::=[CONSTRAINTconstraint_name]}

|[{PRIMARYKEY|UNIQUE}[,...n]

)

[ON{filegroup|DEFAULT}]<column_definition>::={column_name

data_type}

[COLLATE<collation_name>]

[[DEFAULTconstant_expression]

|[IDENTITY[(seed,

increment)[NOTFORREPLICATION]]]

]

[ROWGUIDCOL]

[<column_constraint>][...n]修改和删除数据库表任务二：修改和删除数据库表准备知识修改、删除数据库表修改、删除数据库表修改、删除数据库表修改数据库表涉及的内容包括修改表名、字段名、增加或删除字段以及修改表字段的属性等。修改、删除数据库表任务实施小李是某酒店的大堂经理，为了能够统一规划酒店所承办的各种宴会以及宴会的类型、时间、准备工作等事项，小李需要确认酒店的数据库表。任务：修改、删除数据库表任务分析：理解牢记！因为在创建了数据库表以后，有可能根据业务系统和存储数据内容的变化，对表做相应的修改。因此，小李决定根据实际情况对数据库表作相应的修改或删除。任务：修改、删除数据库表修改、删除数据库表任务实施

1）修改表名首先来看看通过SQLServerManagementStudio修改表名。打开SQLServerManagementStudio并连接到数据库服务器，在“对象资源管理器”中展开“数据库”节点，接着展开数据库“DataCollationSys”节点，展开“表”文件夹，右键单击要更改名字的表，单击弹出菜单中的”重命名”菜单，如图1-8所示。然后输入新的表名，这样就完成了表名的修改。修改表名知识链接修改表名将引起使用该表的视图、存储过程或函数产生“找不到对象”的错误，因此在更改表名前，必须确定是否有视图、存储过程或函数引用了该表。如果有，而且一定要更改该表名，要记得更改引用该表的视图、存储过程或函数中该表表名为对应的新表的表名。2）设置字段属性打开SQLServerManagementStudio并连接到数据库服务器，在“对象资源管理器”中展开“数据库”文件夹，接着展开“数据库”/“DataCollationSys”节点，展开“表”文件夹，右键单击要更改名字的表Area_Tbl，执行”修改”命令。修改表字段知识链接同修改表名一样，修改表字段类型也会影响使用该字段的视图、存储过程或函数。因此在修改了字段名后，别忘了将引用该字段的视图、存储过程或函数中相应的字段名改成新的字段名。3）添加表列可以在SQLServerManagementStudio中连接数据库服务后，依次展开“数据库”文件夹、你要添加列的表所属的数据库（这里是DataCollationSys数据库）、“表”文件夹、你要修改的表（这里是Area_Tbl表）、“列”文件夹，右键单击“列”文件夹，在弹出菜单中执行”新建列”命令，在该界面中进行添加表列。新建列4）删除表列打开SQLServerManagementStudio，连接数据库服务，依次展开“数据库”文件夹、所要删除列的表所属的数据库（这里是DataCollationSys数据库）、“表”文件夹、所要修改的表（这里是Area_Tbl表）、“列”文件夹，右键单击要删除的字段，在弹出菜单中执行”删除”命令，在接着出现的界面中单击”确认”按钮删除表列。删除表列也可以在下图所示的界面中，选中要删除的列，右键单击该列，在弹出的菜单中执行”删除列”命令删除表列。在表设计界面中删除列需要注意的是删除列后，列中的数据内容也随之一起被删除。另外，象修改表字段一样，不是所有的字段都可以删除。拓展提高：创建或删除主键和外键任务三：创建或删除主键和外键准备知识认识主键和外键认识主键和外键认识主键和外键主键是表中唯一标识一行的约束，主键通常定义在一列中，有时也定义在几列中，通过几列组合在一起唯一标识一行，通过主键可强制表的实体完整性，即确保数据库中所代表的任何事物都不存在重复的数据，每一个表只能有一个主键约束，而且主键不接受空值。知识链接由于主键约束确保唯一数据，所以经常用来定义标识列。所谓标识列，指表中已指派了标识属性的列。标识属性生成唯一数字。认识主键和外键认识主键和外键外键是用于建立和加强两个表数据之间的链接的一列或多列。外键一般建立在一列上，有时也通过几列组合起来建立外键，通过将一个表中的主键值的一列或多列添加到另一个表，可以创建两个表之间的链接，这个列就成为第二个表的外键。外键约束并不仅仅只可以与另一个表的主键约束相链接，它还可以定义为引用另一个表的唯一约束。外键约束不允许空值，但是，如果任何组合外键的列包含空值，则跳过外键约束的校验。认识主键和外键认识主键和外键下图所示的表Area_Tbl和Province_Tbl。表Province_Tbl中AreaID是表Area_Tbl的主键，把AreaID字段加入表Province_Tbl中，那么在表Province_Tbl中AreaId字段就是外键，表Area_Tbl和Province_Tbl通过AreaId字段关联。主键和外键关联示例尽管外键的主要目的是控制存储在外键表中的数据，但它还可以控制对主表数据的修改。拓展提高：创建或删除主键和外键任务实施小张是某学校高二年级的年级主任，期中考试过后，小张创建了数据库表来统计学生的考试成绩。小张想要根据学生成绩的数据库表来对全校学生进行排名。任务：创建或删除主键和外键任务分析：理解牢记！由于在SQLServer的关系数据库中，表与表间通过关系进行关联，最重要的关系就是主键和外键。因此，小张决定通过创建或删除主键和外键来完成这一任务。任务：创建或删除主键和外键创建或删除主键和外键任务实施

（1）创建表主键和外键

1）打开SQLServerManagementStudio，建立数据库链接。依次展开“数据库”文件夹、你要添加主键和外键的表所属的数据库、“表”文件夹，如果在新建表的同时创建主键和外键，右键执行”新建表”命令，在出现的界面中，右键单击选择作为主键或外键的列，在弹出菜单中执行”设置主键”命令。创建表主键2）创建外键的过程稍微要复杂一些，在上图所示的界面中，执行”关系”命令，在出现的界面中单击”添加”按钮添加关系。外键关系3）在上图中，选择新添加的关系，在右边的关系属性中，单击“表和列规范”编辑框旁的

按钮，出现下图所示界面。表和列规范在“主键表”下拉框中选择主键所在的表，例如Area_Tbl，当鼠标光标移到下面的编辑框中时，将出现一个下拉列表框，列出所选主键表中的所有字段，选择主键，例如AreaID，然后将鼠标光标移动到右边“外键表”下面的编辑框中，在出现的下拉列表框中选择相应的字段，例如AreaID，单击”确定”按钮，这样，外键表中的选择字段就成为外键，外键就这样被创建。4）通过修改表来设置表的主键，操作方式和创建表时创建主键是一样。打开SQLServerManagementStudio，建立数据库链接。依次展开“数据库”文件夹、你要创建主键和外键的表所属的数据库（这里是DataCollationSys数据库）、“表”文件夹。（2）删除主键和外键1）在SQLServerManagementStudio中连到数据库服务器，在“对象资源管理器”中展开“数据库”文件夹，展开”数据库”/”DataCollationSys”/”表”文件夹，右键单击要删除主键或外键的表Area_Tbl，执行”修改”命令。修改表

2）在出现的界面中用右键单击主键所在的字段，在弹出式菜单中执行”移除主键”命令，然后保存对表的修改，这样主键就从表中删除了。删除主键

3）在SQLServerManagementStudio中删除外键约束再下图所示界面中选中要删除的外键约束后，单击”删除”按钮删除外键约束。删除外键约束重点难点项目一创建和维护数据库重点难点创建数据库创建、删除主键和外键重点难点

项目二了解Transact-SQL程序设计基础本项目介绍Transact-SQL常用语句，讲解如何使用Transaction-SQL(T-SQL)SELECT语句检索数据，使用UPDATE语句更新数据，使用INSERT语句插入数据，以及Pivot、UNPivot、Row_Number、DENSE_Rank、Rank、等SQLServer2008中新增的T-SQL语句的用途和用法。目录

任务利用SQL语句进行数据查询利用SQL语句进行数据查询任务：利用SQL语句进行数据查询准备知识常用的SQLServer语句常用的SQL语句介绍使用SQLServer2008中新增函数和语句常用的SQL语句1.常用的SQLServer语句

SQL是结构化查询语言的缩写，是访问数据库的标准语言。标准化的数据库系统都对SQL语言提供支持，这给数据库应用程序开发人员带来了很大的方便，无论后台数据库是SQLServer、Oracle，还是IBMDB2，甚至是小型数据库Access，都可以使用标准的SQL语句对它进行操作。（1）Select语句SELECT语句主要用于从数据库中检索数据，同时它也可以用于向局部变量赋值或者调用一个函数。SELECT语句既可以简单，也可以复杂，但最好使用简单的语句，也就是说能达到同样目的，选择同样数据时，尽量使SELECT语句简单，在检索你需要的结果时，要尽量简化你的SELECT语句。在使用SELECT语句检索数据时，你可以使用过滤条件过滤掉你不需要的数据，同时，在检索数据时，也应该考虑检索数据的速度，特别是从数据量很大的表中检索数据。一般可以在WHERE子句中尽量使用有索引的列以加速数据检索的速度。小提示：常用的SQL语句1.常用的SQLServer语句SELECT语句的语法包含几个子句，大多数子句都是可选的。一个SELECT语句至少要包括一个SELECT语句和一个FROM子句。这两个子句识别检索哪些列或者列中的数据，以及分别从哪些表中检索数据。SELECT语句的语法如下：SELECTselect_list

[INTOnew_table]

FROMtable_source

[WHEREsearch_condition]

[GROUPBYgroup_by_expression]

[HAVINGsearch_condition]

[ORDERBYorder_expression[ASC|DESC]]常用的SQL语句1.常用的SQLServer语句1）Where语句2）DISTINCT语句3）ORDERBY语句对结果排序4）使用GROUPBY语句对查询结果分组5）使用HAVING语句对分组结果进行过滤6）使用JOIN实现多表查询7）使用UNION合并多个结果集常用的SQL语句1.常用的SQLServer语句（2）INSERT语句INSERT语句的功能是向表中插入记录，它的基本语法结构如下：INSERTINTO表名

(字段列表)VALUES(字段值列表)字段列表和字段值列表都使用逗号分隔，而且两个列表中值的数量和数据类型必须对应。如果在INSERT语句中指定了表中所有记录的值，则字段列表可以省略。例如，执行下面的语句可以在表Users中插入一个用户名为tester的记录。INSERTINTOUsers(UserName,UserPwd)VALUES('tester','111111')常用的SQL语句1.常用的SQLServer语句运行后再查看表Users的内容，结果如图所示。查看新插入的数据常用的SQL语句1.常用的SQLServer语句（3）UPDATE语句UPDATE语句的功能是更新表中指定记录的指定字段值，它的基本语法结构如下：UPDATE表名

SET字段名1=值1,字段名2=值2WHERE更新条件知识链接在一个UPDATE语句中可以同时更新多个字段的值，但是字段名不能重复。为了指定更新记录的条件，UPDATE语句中通常使用WHERE子句。常用的SQL语句1.常用的SQLServer语句例如，执行下面的语句可以在表Users中将用户tester的密码更新为123456。INSERTINTOUsers(UserName,UserPwd)VALUES('tester','111111')运行后再查看表Users的内容，结果如图所示。查看新插入的数据2.常用的SQL语句介绍2.常用的SQL语句介绍更新数据包括修改、删除和插入新的数据，其中修改和删除数据既可以整表更新或删除，也可以带条件进行更新或删除指定条件下的数据。更新数据的T-SQL基本语法如下：UPDATEtable_nameSETcolumn_name=expression

贵一张[,…n][FROMtable_name][WHEREsearch_condition]2.常用的SQL语句介绍2.常用的SQL语句介绍其中相关的参数说明如下：UPDATE：该关键字表示要更新数据；

table_name：是要更新数据的表。SET：该关键字指定要更新哪些字段的数据；coloumn_name：需要更新数据的列。Expression：是用于更新coloumn_name指定列值的表达式，expression既可以是常量、变量也可以是来自其它由FROMtable_name子句中指定的table_name表中的字段，甚至可以是一个计算表达式。WHERE：该子句指定要更新哪些行的数据，这些行必须符合search_condition中指定的条件。2.常用的SQL语句介绍2.常用的SQL语句介绍下面介绍各种数据更新的方法。（1）在指定条件下更新指定字段的值通过在WHERE子句中指定搜索条件，可以更新指定字段的值。例如我们要更新表Sales_Tbl中店2-23305T在2006-07-12这天的销量Quantity。在更新数据前，先用下面的查询语句浏览一下2006-07-12这天店2-23305T的销量：SELECTQuantityFROMSales_TblWHEREStoreId='2-23305T' ANDSoldDate>='2006-07-12' ANDSoldDate<'2006-07-13'运行结果如下：Quantity

12.常用的SQL语句介绍2.常用的SQL语句介绍（2）带子查询的修改语句子查询可以嵌套在

UPDATE、DELETE和

INSERT语句以及

SELECT语句中。这一节看看子查询如何应用在UPDATE、DELETE和

INSERT这些修改数据的修改语句中。（3）PIVOT和UNPIVOT的用法微软SQLServer2008引入了新的SQL操作语句PIVOT和UNPIVOT。在以前的SQLServer版本中，如果要产生交叉报表(CrossReport)，我们采用CASE语句和聚合函数，但是在SQLServer2008中，要到达同样的效果，只需使用PIVOT和UNPIVOT操作语句即可。2.常用的SQL语句介绍2.常用的SQL语句介绍（4）Top语句的用法Top语句最基本的用法是限制返回到结果集中的行数。其语法如下：TOPn[PERCENT]其中n指定返回的行数。如果未指定

PERCENT，n就是返回的行数。如果指定了

PERCENT，n就是返回的结果集行的百分比。注意啦！如果一个SELECT语句既包含TOP又包含ORDERBY子句，那么返回的行将会从排序后的结果集中选择。整个结果集按照指定的顺序建立并返回排好序的结果集的前n行。3.使用新增函数和语句3.使用SQLServer2008中的新增函数和语句（1）使用新增函数1）Row_Number函数在先前版本的SQLServer中，要产生行号，需要借助于临时表或子查询，

SQLServer2008提供了这样的产生行号的函数，这就是Row_Number函数。Row_Number函数返回结果集分区内行的序列号，每个分区的第一行从

1开始。其语法如下：ROW_NUMBER()

OVER([<partition_by_clause>]<order_by_clause>)3.使用新增函数和语句3.使用SQLServer2008中的新增函数和语句2）RNNK函数RANK函数返回结果集的分区内指定字段的值的排名。指定字段的值的排名是相关行之前的排名数加一。其语法如下：RANK()

OVER([<partition_by_clause>]<order_by_clause>)3.使用新增函数和语句3.使用SQLServer2008中的新增函数和语句3）DENSE_RANK函数DENSE_RANK()函数也是一个排名统计函数，它返回结果集分区中行的排名，在排名中没有任何间断。知识链接如果有两个或多个行受同一个分区中排名的约束，则每个约束行将接收相同的排名。3.使用新增函数和语句3.使用SQLServer2008中的新增函数和语句4）NTILE函数NTILE函数用于计算数据分布的函数，该函数将有序分区中的行分发到指定数目的组中。各个组有编号，编号从1开始。对于每一行，NTILE将返回此行所属的组的编号。NTILE语法如下：NTILE(integer_expression)

OVER([<partition_by_clause>]<order_by_clause>)3.使用新增函数和语句3.使用SQLServer2008中的新增函数和语句5）OpenRowSet函数在SQLServer2008中，OpenRowSet函数也得到了加强，本节主要介绍OpenRowSet函数批量操作的功能。在介绍增强功能之前，简单介绍一下OpenRowSet函数的功能和用法。OpenRowSet的功能等同于使用链接服务器，并且OpenRowSet是使用OLEDB连接并访问远程数据的一次性的临时方法。可以在查询的

FROM子句中像引用表名那样引用

OPENROWSET函数。依据

OLEDB访问接口的功能，还可以将

OPENROWSET函数引用为

INSERT、UPDATE或

DELETE语句的目标表。知识链接尽管查询可能返回多个结果集，但OPENROWSET只返回第一个结果集。3.使用新增函数和语句3.使用SQLServer2008中的新增函数和语句（2）使用新增语句1）OUTPUT语句在SQLServer2000中，在往表中插入数据后，若要获得新增的标识值，则需要使用系统变量@@IDENTITY或函数SCOPE_IDENTITY和

IDENT_CURRENT，在SQLServer2008中，新增了OUTPUT语句，用来在向表中插入数据、更新数据或删除数据后立即返回真实的数据。3.使用新增函数和语句3.使用SQLServer2008中的新增函数和语句应用程序需要返回新增的标识列值、计算列值或由缺省约束产生的缺省值时，可以采用OUTPUT语句方便地实现。OUTPUT语法如下：OUTPUT{column_name|scalar_expression}INTO{@table_variable|output_table}[(column_list)]或：OUTPUT{column_name|scalar_expression}其中column_name可以是下面几种值：{DELETED|INSERTED|from_table_name}.{*|column_name}如果要将OUTPUT返回的值保存到表变量或输出表中，则使用INTO关键字，如果不需要保存这些返回值，则不需要关键字INTO关键字。3.使用新增函数和语句3.使用SQLServer2008中的新增函数和语句

2）ExecuteAS语句SQLServer2008新增了ExecuteAS语句，用于设置会话的执行上下文。在默认情况下，会话在用户登录时开始，在用户注销时结束。会话过程中的所有操作都受限于对该用户进行的权限检查。当运行

ExecuteAS语句时，会话的执行上下文将切换到指定的登录名或用户名。切换上下文之后，SQLServer2008数据库系统将根据登录名和用户安全令牌检查该帐户（而非调用

ExecuteAS语句的用户）的权限。3.使用新增函数和语句3.使用SQLServer2008中的新增函数和语句实际上，SQLServer2008数据库系统在会话或模块的执行期间模拟了用户或登录帐户，或显式恢复了上下文切换。ExecuteAS语句的语法如下：{Exec|Execute}AS{Login|User}=‘Name’[WITH{NOREVERT|COOKIEINTO@varbinary_variable}]|Caller其中Login指定要模拟执行上下文的是一个登录，模拟范围处于服务器级别。User指定要模拟执行的上下文是当前数据库中的用户，模拟范围只限于当前数据库。Name为有效的用户或登录名，Name必须是Sysadmin固定服务器角色成员，或者分别作为sys.database_principals或sys.server_Principals中的主体而存在。Name可以被指定为局部变量。Name必须是单独帐户，而不能是组、角色、证书、密钥或内置帐户。拓展提高：在开发新程序时，尽量避免使用表3-3中列出的数据类型，这些类型在微软SQLServer未来的版本中将被删除。改用Varbinary(max)，Varchar(max)，Nvarchar(max)数据类型。小提示：利用SQL语句进行数据查询任务实施小张是某大型购物超市的销售经理，为了了解超市各物品的销售情况，及时对超市物品进行上新、价格调整等，小张需要统计超市物品的销售情况。任务：利用SQL语句进行数据查询任务分析：理解牢记！由于SQLServer语句具有数据查询的功能，读者可以根据SQLServer语句进行数据的查询与整理，因此，小张决定使用SQLServer语句的相关知识来完成超市销售情况的统计工作。任务：利用SQL语句进行数据查询利用SQL语句进行数据查询任务实施

假设现有下表所示的销售数据，共有11条记录。Sales_Tbl样例数据要从Sales_Tbl表检索StoreId，ProductModel和Quantity字段的值，SQL语句如下：SELECTStoreId,ProductModel,QuantityFROMSales_Tbl在查询分析器中执行该SQL语句，结果如下表所示。选择指定列查询结果StpreIdProductModelQuantity2-23305Tg1030cn12-23305Tlj102012-O3B-4983u1560cn42-O3B-5372u1560cn152-O3B-4283Photosmart783822-O3B-4562Officejet425512-O3B-4562LJ5100se22-1XQDXCg1030cs12-1XQDXCpsc140612-O3B-7304U1560cn32-1UDSCF245printer34重点难点项目二了解Transact-SQL程序设计基础重点难点掌握SQL语句熟练使用SQL语句重点难点

项目三设计收视分析问卷调查系统本项目主要讲解了如何利用SQLServer2008的相关知识来建立收视分析问卷调查系统，并利用SQLServer的相关知识对该系统的准确性进行验证。目录

任务设计并验证收视分析问卷调查系统设计并验证问卷调查系统任务：设计并验证收视分析问卷调查系统准备知识课程设计的目的和意义2.系统分析与设计1.课程设计的目的和意义1.课程设计的目的和意义今天走在大街小巷几乎都可以听到“收视率”这个名称。某某节目的收视率比较高，某某频道的收视率比较高等。什么是收视率呢？它是传媒行业的一个术语，表示某个媒介在受众中的接受程度。对于我们这里要讲的内容来说，收视率就是反应大家喜欢看哪个电视节目，爱看哪个电视节目。收视率是一种能用量化来反应这种喜欢看、爱看程度的高低的统计数据。统计哪个节目有多少人看，看了多长时间，对于现在收视分析公司有很多手段。小提示：2.系统分析与设计2.系统分析与设计收视分析问卷调查就是采用科学的抽样方法，对受众进行采访，记录受众每天都看了哪些频道，看了多长时间。（1）功能描述本系统共分为新建电视台、新建频道、登记调查问卷和登记节目单四大模块，各模块分别实现了相应的功能，详细的功能设计如下：1）新建电视台模块实现了用户登记电视台的功能，新用户使用本系统的时候没有电视台，需要他首先新建需要的电视台，然后才可以顺利的进行其他功能的使用。2.系统分析与设计2.系统分析与设计2）新建频道模块实现了用户登记频道的功能，用户新建了电视台以后，要给所建的电视台新建几个频道，一个电视台可能有多个频道。用户可以依次进行创建。3）登记调查问卷实现了用户调查问卷的录入登记功能，用户每天都要对调查问卷进行登记工作，页面记录了从早上6点到第二天的凌晨2点，每15分钟一个间隔，并且提供了选择频道的下拉列表供用户进行选择。4）登记节目单实现了用户登记录入节目单的功能，用户每天要进行每个频道节目单的录入登记工作，页面提供了选择频道的下拉菜单、开始时间、结束时间和节目名称输入框组。知识链接在通过采集每个频道的节目情况进行收费分析，计算出每个频道，每个节目的收视率。我们这里不设计到收视率的计算，而是设计如何进行受众采访和信息搜集的一套系统。2.系统分析与设计2.系统分析与设计（2）功能模块设计根据功能描述，收视率调查问卷登记系统可以分为4个大模块，每一个模块都提供了相应的功能。收视率调查问卷登记系统详细的功能模块图如图所示。系统功能详细设计图设计并验证问卷调查系统任务实施小张是某电视台的节目主持人，目前接到上级任务，小张需要针对本台热播的电视剧在网上做一个问卷调查，了解电视剧的收视情况。任务：设计并验证收视分析问卷调查系统任务分析：理解牢记！由于SQLServer数据系统中，用户可以进行系统程序的设计进行问卷调查，方便简单，因此，小张决定设计一个与收视情况相关的调查系统，来提高工作效率。任务：设计并验证收视分析问卷调查系统设计并验证问卷调查系统任务实施

1.设计数据库数据库设计是构建任何数据库驱动的

Web站点的第一步。一个成功的管理系统是由：50%的业务+50%的软件所组成，而

50%的成功软件又有25%的数据库

+25%的程序所组成，数据库设计的好坏是一个关键。如果把企业的数据比做生命所必需的血液，那么数据库的设计就是信息系统开发和建设中最重要的一部分。（1）数据库需求设计通过对系统功能的分析可知，收视率调查问卷登记系统需要包含以下数据库信息：1）电视台信息，包括电视台编号和电视台名称等信息。2）频道信息，包括频道编号、频道名称、对应的电视台编号等信息。3）调查记录信息，包括调查记录ID、开始时间、结束时间、频道编号、被调查者编号等信息。4）节目单信息，包括节目单ID、频道编号、开始时间、结束时间和节目名称等信息。5）被调查者信息，包括被调查者ID、性别、年龄和学历等信息。（2）数据库表的设计1）电视台信息表（T_TVstation）电视台信息表包含电视台的基本信息，在以后的编码中新增频道模块用到这个表，表的结构如下。字段名称数据类型数据类型大小可否为空TVstationIdint4不能为空TVstationNameVarchar30不能为空电视台信息表2）频道信息表（T_Channel）频道信息表包含了产品和顾客的相关信息，在处理销售的相关功能模块时都需要用到此表，此表结构如下。频道信息表字段名称数据类型数据类型大小可否为空ChannelIdint4不能为空ChannelNameVarchar30不能为空TVstationIdint4不能为空3）调查记录信息表（T_Diagnoses）调查记录信息主要是用于身份的验证，使合法用户对数据库进行操作。表的结构如表下。调查记录信息表字段名称数据类型数据类型大小可否为空DiagnosesIdint4不能为空BeginTimedatetime8不能为空EndTimedatetime8不能为空ChannelIdint4不能为空InformantIdint4可为空4）节目单信息表（T_Playbill）节目单信息表主要是描述产品的相关参数，与其它表一起运用到销售功能模块。表的结构如下。节目单信息表字段数据类型大小可否为空idint4不能为空ChannelIdint4不能为空BeginTimedatetime8不能为空EndTimedatetime8不能为空ProgramNameVarchar30不能为空5）被调查者信息表（T_Informant）被调查者信息表主要是记录供应商的基本信息，便于在交易过程的进行联系和识别产品，表的结构如下。被调查者信息表字段名称数据类型数据类型大小可否为空InformantIdint4不能为空SexVarchar2可为空AgeVarchar2可为空DiplomaVarchar20可为空（3）数据库表的实现创建以上数据表的SQL语句如下：--创建电视台信息信息表CREATETABLE[dbo].[T_TVstation]( [TVstationId][int]IDENTITY(1,1)NOTNULL, [TVstationName][varchar](30)COLLATEChinese_PRC_CI_ASNOTNULL)ON[PRIMARY]GO--创建频道信息表CREATETABLE[dbo].[T_Channel]( [ChannelId][int]IDENTITY(1,1)NOTNULL, [TVstationId][int]NOTNULL, [ChannelName][varchar](30)COLLATEChinese_PRC_CI_ASNOTNULL)ON[PRIMARY]GO

--创建调查记录信息表CREATETABLE[dbo].[T_Diagnoses]( [DiagnosesId][int]IDENTITY(1,1)NOTNULL, [BeginTime][datetime]NOTNULL, [EndTime][datetime]NOTNULL, [ChannelId][int]NOTNULL, [InformantId][int]NULL)ON[PRIMARY]GO

--创建节目单信息表CREATETABLE[dbo].[T_Playbill]( [id][int]IDENTITY(1,1)NOTNULL, [ChannelId][int]NOTNULL, [BeginTime][datetime]NOTNULL, [EndTime][datetime]NOTNULL, [ProgramName][varchar](30)COLLATEChinese_PRC_CI_ASNOTNULL)ON[PRIMARY]GO--创建被调查者信息表CREATETABLE[dbo].[T_Informant]( [InformantId][int]IDENTITY(1,1)NOTNULL, [Sex][varchar](2)COLLATEChinese_PRC_CI_ASNULL, [Age][varchar](2)COLLATEChinese_PRC_CI_ASNULL, [Diploma][varchar](20)COLLATEChinese_PRC_CI_ASNULL)ON[PRIMARY]GO运行查询分析器，进入“连接到SQLserver登录”页面，登录成功后，在页面的编辑区域输入上述SQL语句，单击F5键，即可创建这些数据表，也可以使用本书已有的备份数据库。小提示：设计系统界面（1）创建应用程序

1）主框架的设计设计步骤如下：①运行Dreamweaver，单击“文件”｜“新建”，然后出现下面的界面。创建系统主框架②选择“动态页”，选择“JSP”，单击“创建”按钮，这样一个jsp页面就创建完成了。然后在Dreamweaver的快捷面板上选择“布局”，然后选择下图所示的选项。设置系统框架结构2）左侧菜单的设计菜单的功能主要是将对相关操作集中到一起给用户选择，在网页中的菜单要给用户美观大方又实用的感觉。本系统的菜单效果图如下所示：左侧菜单设计3）Topframe区域的设计效果图如下所示。系统主页面Topframe设计（2）创建系统所需要的Java文件1）创建DB.java文件DB.java文件包含连接数据库的方法和调用存储过程（增加、删除、修改、查询）的方法。页面直接通过这个文件来连接数据库和调用存储过程，来完成所需要的操作。整个系统的目录结构如下：项目文件目录结构2）创建Values.java文件（3）创建系统所需要的公共的文件1）创建common.jsp文件这个文件主要实现调用DB和Values文件和jsp页面所需要的的一些java包。本系统的模块都会用到这个文件。新建一个jsp页面，然后将如下代码拷贝到文件中。<%@pagesession="true"%><%@pageimport="com.tv.db.DB"%><%@pageimport="java.sql.*,java.util.*"%><%@pageimport="java.util.Date"%><jsp:useBeanid="db"scope="page"class="com.tv.db.DB"/><jsp:useBeanid="param"scope="page"class="com.tv.db.Values"/>2）创建message.jsp文件这是提示页面，提示新增成功或失败。具体实现方法为：新建一个jsp页面，然后将如下代码拷贝到文件中。3）创建Style.css文件这个文件是页面的样式文件，主要实现jsp页面具有相同的页面风格。本系统的模块都会用到这个文件。实现方法是在Dreamweaver中新建css文件。（4）新增电视台此模块主要功能是实现增加电视台功能。这是最基本的模块，其他模块都在它的基础是才可以正常的使用。页面的效果如图。新增电视台设计（5）新增频道

此模块主要功能是实现增加电视台的频道功能。在下拉列表中选择电视台，然后填写频道的名称，单击提交，就实现了频道的添加，页面的效果图如图。新增频道界面设计知识链接用户在下拉列表中选择电视台，然后填写频道的名称，单击提交，输入的频道就会保存的数据库表中对应的电视台的频道。（6）登记调查问卷此模块主要功能是实现登记收视信息的被调查者信息和调查问卷的内容。收视信息视从早上的6点到第二天的凌晨2点，时间间隔是15分钟，记录被调查者一天的收视信息。填写被调查者基本信息，在下拉列表中选择性别，年龄和学历可意选添。选择被调查者收视信息，选择相应时段被调查者收视信息，都为选添项。选择完毕后，单击提交，就实现了登记被调查者信息和调查问卷内容的功能，页面的效果设计如图所示。登记调查问卷界面设计用户填写被调查者的信息和收视信息后，单击提交，输入的被调查者的信息和收视信息就会分别保存的数据库相应的表中利用插入触发器实现被调查者的Id插入到收视信息表中，实现了两个表的对应关系。知识拓展：（7）登记节目单此模块主要功能是实现登记频道的节目单功能。在下拉列表中选择相应的频道，然后填写开始时间、结束时间和节目名称，单击“提交”按钮，就实现了节目单的添加，页面的效果图如图所示。登记节目单用户在下拉列表中选择频道，然后填写开始时间，结束时间，节目名称，不用把每一行的文本框都填写完，每一行都为选添，单击提交，输入的信息就会保存到数据库表中。要实现此功能，首先要编写一个存储过程，这个存储过程实现向数据库中添加节目单数据。1）实现向数据库中添加节目单数据的存储过程2）创建一个inputplaybill.jsp页面。

3.验证系统的运行结果（1）首先配置好jsp的运行环境，然后在浏览器中输入系统的地址：http://localhost:8080/datainput进入系统，首先进入系统的主界面，如图所示。系统主界面（2）单击左侧的链接“新增电视台”，进入新增电视台页面，在页面的电视台名称文本框中输入电视台名称“河北电视台”，然后单击提交按钮，“河北电视台”就会保存到数据库中。新增电视台界面成功后提示页面如下：新增电视台成功提示界面（3）单击左侧的链接“新增频道”，进入新增频道页面，在页面的选择电视台下拉列表中选择“河北电视台”，频道名称文本框中输入频道名称“河北电视台一套”，然后单击提交按钮，频道“河北电视台一套”就会保存到数据库中。新增频道成功提示界面（4）单击左侧的链接“登记调查问卷”，进入登记调查问卷页面，在页面上填写如图所示的信息后，单击提交按钮，被调查者信息和收视信息都会保存到数据库中。登记调查问卷界面成功后提示页面如图所示。登记调查问卷提示成功界面（5）单击左侧的链接“登记节目单”，进入登记节目单页面，在页面的选择频道下拉列表中选择“中央一”，然后输入节目单的开始时间，结束时间，节目名称，然后单击提交按钮，两条节目单数据就会保存到数据库中。登记节目单界面成功后提示页面如图所示。登记节目单成功提示界面重点难点项目三设计收视分析问卷调查系统重点难点课程设计的目的和意义设计调查系统重点难点

项目四设计学生信息学管理系统本项目主要讲解了如何利用SQLServer的相关知识来设计学生的信息管理系统，帮助读者更好地使用SQLServer数据库的相关知识。目录

任务设计学生信息管理系统并进行验证设计学生信息管理系统任务：设计学生信息管理系统准备知识课程设计的目的和意义2.系统分析与设计1.课程设计的目的和意义1.课程设计的目的和意义由于当前学校规模不断的扩招，学生数量的不断增加，学生信息量也成倍的增长。学生管理工作也成了学校各项管理工作的一个重要的部分。其管理水平的高低将直接影响到人才的培养质量。面对庞大的信息量，如何有效地提高学生管理工作的效率是一个学校急需解决的问题。因此本项目讨论如何开发适合学校需要的学生管理信息系统，通过这样的系统，可以做到信息的规范管理，科学统计和快速查询，并减少管理工作的工作量。学生信息管理系统就是对学生的信息进行管理。学生信息包括学生的基本信息、学生的学籍信息、学生的成绩信息、学生的奖罚信息。小提示：1.课程设计的目的和意义1.课程设计的目的和意义学生的基本信息就是每年新生报到时，对每位新生建立的个人档案，包括学生学号、姓名、性别、出生日期、所属班级、籍贯、家庭信息等。学生的成绩信息包括学生在校期间所有考试的成绩。学生的奖罚信息是对学习出色学生的奖励和对差学生处罚的信息。学生的学籍信息记录学生因每年的学业修完对学籍信息进行变更的信息。学生信息还包括许多其他信息，比如学生在校期间，每年的体检信息；每个月的补助信息等等，在这里不再详细介绍。2.系统分析与设计2.系统分析与设计（1）功能描述1）学生信息管理包括新生入学个人档案的建立和在校学生信息的查询修改。新生档案的建立包括对新生分配的院系信息、班级信息、入学日期、学号、姓名、性别、年龄等信息的登记。学生信息的查询修改是指对学生信息的修改，由于新生登记时信息错误的登记，或者