项目数据库基本管理及数据操作和查询

任务2-1：系统数据库管理

任务2-2：用户数据库的创建、修改和删除

任务2-3：数据表的创建、修改和删除

任务2-4：数据表记录的添加、修改和删除

任务2-5：约束管理

任务2-6：单表查询（T-SQL）

任务2-7：多表查询（T-SQL）

任务2-8：嵌套查询（T-SQL）

SQLServer2005将数据库映射为一组操作系统文件，数据和日志信息分别存储在不同的文件中。数据文件数据库文件事务日志文件用于存储数据库中的所有对象，如表、视图、存储过程等用以记录所有事务及每个事务对数据库所做的修改数据文件：数据文件用于存储数据库中的所有对象，如表、视图、存储过程等。主要数据文件次要数据文件包含数据库的启动信息和数据库中其他文件的指针。每个数据库有且仅有一个主要数据文件，主要数据文件的建议文件扩展名为.mdf。次要数据文件是可选的，由用户定义并存储主要数据文件未存储的其他数据和对象，建议文件扩展名为.ndf。【注意】次要数据文件不是必须的，如果主要数据文件足够大，能够容纳数据库中的所有数据，则该数据库不需要次要数据文件；但有些数据库可能非常大，超过了单个Windows文件的最大值(4G)，可以使用多个次要数据文件，这样数据库就能继续增长。【说明】SQLServer2005不强制使用.mdf、.ndf和.ldf文件扩展名，但使用它们有助于标识文件的各种类型和用户。SQLServer2005的每个数据库文件都有一个逻辑文件名和一个物理文件名。逻辑文件名只在Transact-SQL语句中使用，是实际磁盘文件名的代号。物理文件名是操作系统文件的实际名字，包括文件所在的路径。事务日志文件：用以记录所有事务及每个事务对数据库所做的修改。每个SQLServer2005数据库至少拥有一个事务日志文件，也可以拥有多个事务日志文件。事务日志文件的大小最少是1MB。事务日志是数据库的重要组件，当系统出现故障或数据库遭到破坏时，就需要使用事务日志恢复数据库内容。

日志文件的建议文件扩展名为.ldf。2、数据库文件组

多个数据文件集合起来形成的一个整体就是文件组。对文件进行

分组的目的是便于进行管理和进行数据的分配。

每个文件组有一个组名。一个数据文件不能存在于两个或两个以上的文件组里，日志文件不属于任何文件组。主文件组：包含了所有的系统表。当建立数据库时，主文件组包括主要数据文件和所有没有被包含在其他文件组里的次要数据文件。用户自定义文件组：用户自定义文件组包含所有在使用CREATEDATABASE或ALTERDATABASE命令时使用FILEGROUP关键字来进行指定文件组的文件。默认文件组：默认文件组包含所有在创建时没有指定文件组的表、索引等数据库对象。在每个数据库中，每次只能有一个文件组是默认文件组。可以在用户自定义文件组中指定一个默认文件组；如果没有指定默认文件组，则主文件组为默认文件组。3、数据库的物理存储结构页面和盘区SQLServer2005中数据存储的基本单位是页。为数据库中的数据文件（.mdf或.ndf）分配的磁盘空间可以从逻辑上划分成页。在SQLServer2005中，页的大小是8KB，SQLServer2005数据库每兆字节有128页。由8个连续页面（8×8KB=64KB）组成的数据结构称为一个盘区，SQLServer2005数据库每兆字节有16个盘区。

简单地说，一个数据库是由文件组成的，文件是由盘区组成，而盘区是由页面组成的。【注意】数据行存放在数据页中，但数据页只能包含除text、ntext和image数据外的所有数据，text、ntext和image数据存储在单独的页中。行不能跨页存储，而每数据页是8KB，严格地说是8060B，因此页中每一行最多包含的数据量是8060B。日志文件是由一系列日志记录组成，而不是页面。4、SQLServer2005数据库的分类1

系统数据库

2示例数据库

3用户数据库3）系统数据库

系统数据库是在SQLServer2005的每个实例中都存在的标准数据库，用于存储有关SQLServer的信息，SQLServer使用系统数据库来管理系统。1）示例数据库

AdventureWorks和AdventureWorksDW是SQLServer2005中的示例数据库，是系统为了让用户学习和理解SQLServer2005而设计的。2）用户数据库

用户数据库是用户根据事务管理需求创建的数据库，例如，图书信息管理数据库、sales数据库等。master数据库：SQLServer2005中的总控数据库，是最重要的系统数据库。系统是根据master数据库中的信息来管理系统和其他数据库。如果master数据库信息被破坏，整个SQLServer系统将受到影响，用户数据库将不能被使用。model数据库：用户建立新数据库提供模板和原型，它包含了将复制到每个新建数据库中的系统表。msdb数据库：支持SQLServer代理。当代理程序调度作业、记录操作时，系统要用到或实时产生很多相关信息，这些信息一般存储在msdb数据库中。tempdb数据库：临时数据库，保存所有的临时表、临时数据以及临时创建的存储过程。resource数据库：只读和隐藏的数据库，包含SQLServer2005所有的系统对象，我们无法使用可以列出所有数据库的一般SQL命令来看到它。5、数据库对象的结构架构是一种允许我们对数据库对象进行分组的容器对象，是形成单个命名空间的数据库对象的集合。命名空间是一个集合，其中每个元素的名称都是唯一的。在SQLServer2005中，一个数据库对象通过由4个命名部分组成的结构来引用，即：

[[[server_name.][database_name].][schema_name].]object_name对象所在的服务器名称对象所在的数据库名称对象的架构名称对象名如果应用程序引用了一个没有限定架构的数据库对象，那么SQLServer2005将尝试在用户的默认架构（通常为dbo）中找出这个对象。

例如，引用服务器“HBSI”上的数据库“sales”中的销售员表“Seller”时，完整的引用为“HBSI.sales.dbo.Seller”。在实际引用时，在能够区分对象的前提下，前三个部分是可以根据情况省略的。1、创建数据库创建数据库使用SQLServerManagementStudio创建数据库。使用Transact-SQL语句创建数据库优点：简单直观优点：可以将创建数据库的脚本保存下来，在其他计算机上运行以创建相同的数据库

1）使用SQLServerManagementStudio创建数据库CREATEDATABASEdatabase_name

[ON[

<filespec>[,...n]]

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

[LOGON<filespec>[,...n]]<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]2）使用Transact-SQL语句创建数据库指明主要数据文件、次要数据文件和文件组的明确定义

事务日志文件的明确定义

数据库的名称附录：Transact-SQL语法约定参考说明

大写

Transact-SQL关键字。粗体

数据库名、表名、列名、索引名、存储过程、实用工具、数据类型名以及按所显示的原样必须键入的文本。[]（方括号）

可选语法项。不要键入方括号。<>（<标签>）

说明。[,...n]

此项可以重复n次，各项之间以逗号分隔。[....n]

指示前面的项可以重复n次。每一项由空格分隔。<label>::=

语法块的名称。此约定用于对可在语句中的多个位置使用的过长语法段或语法单元进行分组和标记。可使用语法块的每个位置由括在尖括号内的标签指示：<标签>。{}（大括号）

必选语法项。不要键入大括号。|（竖线）

大括号中的语法项。只能使用其中一项。;

Transact-SQL语句终止符。虽然在此版本的SQLServer中大部分语句不需要分号，但将来的版本需要分号。斜体

用户提供的Transact-SQL语法的参数。下划线

指示当语句中省略了包含带下划线的值的子句时应用的默认值。

CREATEDATABASEsampleONPRIMARY(NAME=sample_dat,FILENAME='d:\data\sample_data.mdf',SIZE=5,MAXSIZE=50,FILEGROWTH=10)该语句需在“查询

编辑器”中输入并执行例创建一个数据库sample。主文件为sample_dat。【说明】由于没有为主要数据文件指定容量单位，系统默认为MB。由于在创建时没有指定日志文件，系统将自动创建一个初始容量为2MB的日志文件并且没有最大容量限制。如果在查询语句编辑区域选定了语句，则只执行选定的语句，否则执行所有语句。其他示例:教材P35~P36。2、查看数据库查看数据库的属性使用SQLServerManagementStudio用Transact-SQL语句对于已有的数据库1）使用SQLServerManagementStudio查看数据库属性

教材P36~P37。2）使用Transact-SQL语句查看数据库属性

系统存储过程sp_helpdb查看数据库的属性。

例：查看数据库sales的属性。语句格式：sp_helpdbsales

例：查看所有数据库的属性。语句格式：sp_helpdb

3、修改数据库1）使用SQLServerManagementStudio修改数据库属性 教材P38。2）使用Transact-SQL语句修改数据库属性ALTERDATABASEdatabase_name{ADDFILE<filespec>[,...n][TOFILEGROUPfilegroup_name]|ADDLOGFILE<filespec>[,...n]|REMOVEFILElogical_file_name

|ADDFILEGROUPfilegroup_name

|REMOVEFILEGROUPfilegroup_name|MODIFYFILE<filespec>|MODIFYNAME=new_dbname|MODIFYFILEGROUPfilegroup_name{filegroup_property|NAME=new_filegroup_name}|SETAUTO_SHRINKon/off}为数据库sample添加数据文件sample_dat2和日志文件sample_log2。ALTERDATABASEsampleADDFILE(NAME=sample_dat2,FILENAME='d:\data\sample_dat2.ndf',SIZE=4,MAXSIZE=10,FILEGROWTH=1)ALTERDATABASEsampleADDLOGFILE(NAME=sample_log2,FILENAME='d:\data\sample_log2.ldf',SIZE=4,MAXSIZE=10,FILEGROWTH=1)例其他示例： 教材P40。4、重命名数据库使用系统存储过程sp_renamedb更改某个数据库的名字，其语法格式为：sp_renamedb‘old_name’,’new_name’例如，sp_renamedb'business','company‘【注意】后续版本的SQLServer将删除该功能，最好使用ALTERDATABASEMODIFYNAME进行重命名数据库。正在使用的数据库是不能进行重命名的。5、数据库的收缩 教材P41~P42。6、删除数据库当一个数据库不再需要时可以将其删除，以释放该数据库所占有的磁盘空间。但是应该注意的是，如果某个数据库正在被使用时，则无法对其进行删除操作。1）使用SQLServerManagementStudio删除数据库教材P43。2）使用Transact-SQL语句删除数据库语法格式：DROPDATABASE

database_name例：删除数据库student

DROPDATABASEstudent例：删除sample和student数据库。

DROPDATABASEsample,student【注意】4个系统数据库master、tempdb、model、msdb不能删除。正在使用的数据库不能删除。数据库被删除之后，文件及其数据都从服务器上的磁盘中被删除。一旦删除数据库，它即被永久删除，所以删除数据库时一定要谨慎。课堂练习教材P44： 【2.7实验】中的实验内容（1、2、3、4、6）。1、表的概念

数据库中包含一个或多个表。表是数据的集合，是用来存储数据和操作数据的逻辑结构。数据在表中是按照行和列的格式来组织排列的，每一行代表一条唯一的记录，每一列代表记录的一个属性。例如，一个包含销售员基本信息的数据表，表中每一行代表一名销售员，每列分别代表该销售员的信息，如编号、姓名、性别等。2、系统数据类型二进制数据类型（binary、varbinary、image）整数数据类型（bit、int、bigint、smallint、tinyint）浮点数据类型（float、real）精确小数数据类型（decimal、numeric）货币数据类型（money、smallmoney)日期/时间数据类型（datetime、smalldatetime）字符数据类型（char、varchar、text）unicode数据类型（nchar、nvarchar、ntext）特殊数据类型3、表的创建1）使用SQLServerManagementStudio创建数据表

教材P50~P51。2）使用Transact-SQL语句创建数据表CREATETABLE[database_name.[schema_name].|schema_name.]table_name({column_name1data_type}[DEFAULTconstant_expression][IDENTITY(SEED,INCREMENT)][NULL|NOTNULL][,…n])[ON{filegroup|DEFAULT}]说明：P51。例：为sales数据库创建一个销售人员表Seller，它包含销售员编号（SaleID）、姓名（SaleName）、性别（Sex）、出生日期（Birthady）、雇用日期（HireDate）、地址（Address）、电话（Telephone）和备注（Note）字段，其中SaleID、SaleName这两列不允许为空。CREATETABLESeller(SaleIDchar(3)NOTNULL,SaleNamechar(8)NOTNULL,Sexchar(2),Birthdaydatetime,HireDatedatetime,Addresschar(60),Telephonechar(13),Notechar(200))例：为sales数据库创建订单表Orders，包括OrderID、CustomerID、SaleID和OrderDate字段，其中OrderID为标识列，起始值为10248，增量为1；CustomerID和SaleID字段不允许为空值；OrderDate字段的默认值为当前日期。CREATETABLEOrders(OrderIDintIDENTITY(10248,1),CustomerIDchar(3)NOTNULL,SaleIDchar(3)NOTNULL,OrderDatedatetimeDEFAULTgetdate())4、修改表结构1）使用SQLServerManagementStudio修改表结构 教材P52~P53。2）使用Transact-SQL语句修改表结构ALTERTABLEtable_name{ADDcolumn_namedate_type[DEFAULTcontant_expression][IDENTITY(SEED,INCREMENT)][NULL|NOTNULL][,…n]|DROPCOLUMNcolumn_name[,…n]|ALTERCOLUMNcolumn_namenew_datetype[NULL|NOTNULL]}例：sales数据库中的Customer表包含CustomerID、ConpanyName和ConnectName三个字段，现为该表添加地址（Address）、邮政编码（ZipCode）和电话号码（Telephone）字段。

ALTERTABLECustomer

ADDAddresschar(40),ZipCodechar(6),Telephonechar(20)例：将表Seller中的Sex列删除

ALTERTABLESellerDROPCOLUMNSex例：将Seller表中的Address字段的长度改为30，且不能为空

ALTERTABLESeller

ALTERCOLUMNAddressvarchar(30)NOTNULL5、删除数据表1）使用SQLServerManagementStudio删除数据表

教材P53~P54。2）使用Transact-SQL语句删除数据表使用DROPTABLE命令删除表格，其语法格式为：

DROPTABLEtable_name1[,…n]例：将Customer表从sales数据库中删除。

DROPTABLECustomer课堂练习

教材P68： 【3.9实验】中的实验内容（1、2）。

注：建表时约束暂不创建。1、使用Transact-SQL语句向表中插入数据INSERT[INTO]table_name[(column_name[,…n]

)]

VALUES

(expression|NULL|DEFAULT

[,…n]

)

其中：table_name：要插入数据的表名。column_name：要插入数据的列名。expression：与column_name相对应的字段的值，字符型和日期型值插入时要加单引号。例：向Category表中添加三行数据。

INSERTINTOCategory(CategoryID,CategoryName,Description)

VALUES(1,'饮料','软饮料、咖啡、茶、啤酒和淡啤酒')

INSERTINTOCategory(CategoryID,CategoryName,Description)

VALUES(2,'调味品','香甜可口的果酱、调料、酱汁和调味品')

INSERTINTOCategory(CategoryID,CategoryName,Description)

VALUES(3,'点心','甜点、糖和面包')例：向Seller表中插入一行数据，其中Sex字段使用默认值为‘男’，HireDate等字段均取空值。

INSERTINTOSeller(SaleID,SaleName,Sex,Birthday,HireDate,Address,Telephone,Notes)VALUES('s11','赵宇飞',DEFAULT,'1974-07-5',NULL,NULL,NULL,NULL)或

INSERTINTOSeller(SaleID,SaleName,Birthday)

VALUES('s11','赵宇飞','1974-07-25')例：对表中所有列插入数据，则可省略列名。

INSERTINTOCategory

VALUES(1,'饮料','软饮料、咖啡、茶、啤酒和淡啤酒')

INSERTINTOCategory

VALUES('调味品',2,'香甜可口的果酱、调料、酱汁和调味品')

INSERTINTOCategory

VALUES(2,'点心')【注意】在插入数据时，对允许为空的列可使用NULL插入空值；对具有默认值的列可使用DEFAULT插入默认值。当向表中所有列都插入新数据时，可以省略列名表，但必须保证VALUES后的各数据项位置同表定义时的顺序一致，否则系统会报错。错误错误例：创建数据表Orders，然后再插入一行数据。CREATETABLEOrders(OrderIDintIDENTITY(10248,1),CustomerIDchar(3)NOTNULL,SaleIDchar(3)NOTNULL,OrderDatedatetimeDEFAULTgetdate())INSERTINTOOrders(CustomerID,SaleID)Values('c01','s11')【注意】具有IDENTITY属性的列，其值由系统给出，用户不必向表中插入数据。2、使用Transact-SQL语句修改表中数据UPDATEtable_name

SETcolumn_name=expression[,…n]

[WHEREsearch_conditions]

其中：table_name：要更新数据的表名。column_name：要更新数据的列名。expression：更新后的数据值。search_conditions：更新条件，只对表中满足该条件的记录进行更新。例：将Product表中‘啤酒’的价格改为4元。

UPDATEProduct

SETPrice=4

WHEREProductName='啤酒'例：将Seller表中SaleID为s11的地址改为‘东直门外大街108号’，电话改为‘（010）60486658’。

UPDATESeller

SETAddress='东直门外大街108号',Telephone='(010)60486658'

WHERESaleID='s11'例：将Product表中CategoryID为2的所有产品的价格下调10%。

UPDATEProduct

SETPrice=Price*(1-0.1)

WHERECategoryID=23、使用Transact-SQL语句删除表中数据DELETE

[FROM]table_name

[

WHEREsearch_conditions]

说明：删除表中符合search_conditions的数据；缺省WHERE子句时，表示删除该表中的所有数据。例：将SaleID为s11的数据从Seller表中删除。

DELETEFROMSellerWHERESaleID='s11'4、使用SQLServerManagementStudio插入、修改、删除表中

数据教材P57~P58。课堂练习1、根据以下数据表中的内容，使用T-SQL语句分别创建出表结构，并完成数据的插入。（1）表名：Employee（2）表名：Product2、修改“ProductID”为“Y19”的产品，使其“Stocks”为“1000”、“Price”为“200”。3、删除“EmpNo”为“J0015”的职员记录。

任务2-5：约束管理约束可以在两个层次上实施：列级：用户定义的约束只对表中的一列起作用。表级：用户定义的约束对表中的多列起作用。约束定义了必须遵循的用于维护数据一致性和正确性的规则，是强制实现数据完整性的主要途径。约束有5种类型，包括：主键约束、唯一性约束、检查约束、默认约束、外键约束（参照约束）。1、主键（PRIMARYKEY）约束主键用于唯一地标识表中每一条记录。我们可以定义表中的一列或多列为主键，则主键列上没有任何两行具有相同值（即重复值），该列也不能为空值。为了有效实现数据的管理，每张表都应该有自己的主键，且只能有一个主键。

CREATETABLEStudent(sidintPRIMARY

KEY,snamechar(20)NOTNULL,sageint,scitychar(10))在SQLServerManagementStudio中创建主键约束教材P60。2、唯一性（UNIQUE）约束 用来限制表中任意两行在指定列上都不允许有相同的值。一个表上可以放置多个UNIQUE约束。

唯一性约束和主键约束的区别：唯一性约束允许在该列上存在NULL值，而主键约束限制更为严格，不但不允许有重复，而且也不允许有空值。CREATETABLEDepartment (dep_idintPRIMARYKEY, dep_namechar(20)NOTNULLUNIQUE, dep_headchar(5))在SQLServerManagementStudio中创建唯一性约束教材P61。3、检查（CHECK）约束

CREATETABLEStudent (sidintPRIMARYKEY, snamechar(20)NOTNULL, sageintCHECK(sageBETWEEN18AND30), scitychar(10))用来指定某列的可取值的范围。它通过限制输入到列中的值来强制域的完整性。我们可以在单列上定义多个CHECK约束，以它们定义的顺序来求值。在SQLServerManagementStudio中创建检查约束教材P62~P63。4、默认（DEFAULT）约束

CREATETABLEStudent (sidintPRIMARYKEY, snamechar(20)NOTNULL, sageintdefault20, scitychar(10)) 用于给表中指定列赋予一个常量值（默认值），当向该表插入数据时，如果用户没有明确给出该列的值，SQLServer会自动为该列输入默认值。每列只能有一个DEFAULT约束。在SQLServerManagementStudio中创建默认约束教材P63。5、外键（FOREIGNKEY）约束 用于与其他表（称为参照表）中的列（称为参照列）建立连接。通过将参照表中的主键所在列或具有唯一性约束的列包含在另一个表中，这些列就构成了另一个表的外键。当参照表中的参照列更新后，外键列也会自动更新，从而保证两个表之间的一致性关系。在SQLServerManagementStudio中创建外键约束 教材P65~P66。CREATETABLEScore(snochar(10)REFERENCESStudent(sno),Cnochar(10)REFERENCESCourse(cno),gradeint,PRIMARY

KEY(sno,cno))6、实现数据完整性 数据完整性是指数据的正确性、一致性和安全性，它是衡量数据库中数据质量好坏的重要标准。当用户用INSERT、DELETE或UPDATE语句修改数据库内容时，数据的完整性就可能会遭到破坏。实体完整性（EntityIntegrity）域完整性（DomainIntegrity）参照完整性（ReferentialIntegrity）用户定义完整性1）实体完整性 实体完整性指的是表中的每一行都能由称为主键的属性列来唯一标识，且不存在重复的数据行。作为唯一标识符的主键可能是一列，也可能是几列的组合，并且主键不可为空。

例如，在Seller表中可能由两个或多个销售员都叫“张芳”，因此SaleName字段不能设为主键。我们给每一个销售员赋予唯一编码SaleID来标识他们，SaleID字段为主键。2）域完整性指的是限制向表中输入的值的范围，保证给定列的输入有效性。它可以通过限制数据类型、值域或数据格式来实现。

例如，销售员的性别只能是“男”或“女”，年龄必须在18～60岁之间，产品的价格不可能为负数等。3）参照完整性 参照完整性也叫引用完整性。指的是当一个表引用了另一个表中的某些数据时，要防止非法的数据更新，以保持表格间数据的一致性。课堂练习

教材P68：【3.9实验】中的实验内容（1，3，4）。1、SELECT语句1）SELECT语句的作用2）SELECT语句的基本语法格式【说明】SELECT：关键字，用于从数据库中检索数据。

select_list：描述进入结果集的列，它指定了结果集中要包含的列的名称，是一个逗号分隔的表达式列表。

table_name：用于指定产生查询结果集的源表的表名。

SELECT[ALL|DISTINCT]

[TOPn[PERCENT]select_list

[INTOnew_table]

FROMtable_name

[WHEREsearch_condition]

[GROUPBYgroup_by_expression]

[HAVINGsearch_condition]

[ORDERBYorder_expression[ASC|DESC]]

让数据库服务器根据客户的要求从数据库中搜索出所需要的信息资料，并且可以按规定的格式进行分类、统计、排序，再返回给客户。另外，利用SELECT语句还可以设置和显示系统信息、给局部变量赋值等。2、基本查询1)选择列2)选择行基本查询3)排序4)使用关键字TOP和DISTINCT1）选择列格式：

SELECT

{

search_condition[,…..n]|

[*]

}FROMtable_name【列名】例4.1从sales数据库的产品表Product中查询出产品ID（ProductID）、产品名称（ProductName）和单价（Price）的数据信息。SELECTProductID,ProductName,Price

FROMProduct【注意】在数据查询时，列的显示顺序由SELECT语句的SELECT子句指定，该顺序可以和列定义时顺序不同，这并不影响数据在表中的存储顺序。【*】例4.2显示Orders表中的所有信息。SELECT*FROMOrders【作用】无条件地把Orders表中的全部信息都查询出来。该语句也称全表查询，这是最简单的一种查询。【计算列】例4.3从Product表中检索出产品ID（ProductID）、产品名称（ProductName）、产品单价（Price）、产品库存量（Stocks）及产品的总价值。SELECTProductID,ProductName,Price,Stocks,Price*Stocks

FROMProductSELECTProductID,ProductName,Price,Stocks,‘总价值’=Price*StocksFROMProduct例4.4为例4.3中的计算列指定一个列标题“总价值”。【说明】在SELECT子句中可以使用算术运算符对数值型数据列进行加（+）、减（-）、乘（*）、除（/）和取模（%）运算，构造计算列。产品的总价值是一个计算表达式，是产品单价和产品库存量的乘积。需注意，对表中列的计算只是影响查询结果，并不改变表中的数据。关键字AS可以省略例4.5显示销售员信息，格式如下：姓名性别出生日期地址SELECTSaleNameAS'姓名',SexAS'性别',BirthdayAS'出生日期',AddressAS'地址‘FROMSeller2）选择行在实际工作中，大部分查询并不是针对表中所有数据记录的查询，而是要找出满足某些条件的数据记录。此时我们可以在SELECT语句中使用WHERE子句。格式：

SELECT

{

search_condition[,…..n]|

[*]

}FROMtable_name

WHEREsearch_condition【使用关系运算符】例4.6查询Product表中价格小于5元的产品记录。SELECTProductID,ProductName,PriceFROMproductWHEREPrice<5.0例4.7查询Seller表中男销售人员的信息。SELECTSaleID,SaleName,Address,TelephoneFROMSellerWHERESex='男'【使用逻辑运算符】逻辑运算符包括：逻辑与AND、逻辑或OR、逻辑非NOTSELECTProductID,ProductName,PriceFROMProductWHEREPrice>=5.0ANDPrice<=10.0例4.8查询Product表中价格在5－10元之间的产品记录。【使用字符串模糊匹配】格式：

expression[NOT]LIKE‘string’[ESCAPE‘换码字符’]

‘string’：是匹配字符串。其含义是查找指定的属性列值与匹配字符串相匹配的记录。匹配字符串可以是一个完整的字符串，也可以使用四种匹配符。匹配符描述%包含零个或多个字符的任意字符串_代表一个任意字符[]表示指定范围内的任意单个字符[^]表示不在指定范围内的任意单个字符表达式

描述

LIKE‘RA%’将搜索以字母“RA”开头的所有字符串LIKE‘%ion’将搜索以字母“ion”结尾的所有字符串LIKE‘%ir%’将搜索任意位置中包含字母“ir”的所有字符串LIKE‘_mt’将搜索以字母“mt”结尾的所有三个字母组成的字符串LIKE‘[BC]%’将搜索以字母“B”或“C”开头的所有字符串LIKE‘[B-K]air’将搜索以字母“B”到“K”中任意一个字母开头，以“air”结尾的字符串LIKE‘B[^a]%’将搜索以字母“B”开头，第二个字母不是“a”的所有字符串例4.11从Seller表中检索出所有姓张的销售人员的资料。SELECT*FROMSellerWHERESaleNameLIKE'张%‘例4.12从Seller表中检索出名字的第二个字不是“芳”和“伟”的销售人员的资料。SELECT*FROMSellerWHERESaleNameLIKE'_[^芳伟]%'

格式：column_name[NOT]BETWEENexpression1ANDexpression2SELECT*

FROMProduct

WHEREPriceBETWEEN5AND10NOTBETWEEN…AND…

表示不在某一范围内【使用查询范围】

在WHERE子句中使用BETWEEN关键字可以对表中某一范围内的数据进行查询，系统将逐行检查表中的数据是否在BETWEEN关键字设定的范围内。如果在其设定的范围内，则取出该行，否则不取该行。例4.13从Product表中查询出价格在5-10元之间的产品信息。等价于：NOTIN用于查询属性值

不属于指定集合的记录。【使用查询列表】如果列值的取值范围不是一个连续的区间，而是一些离散的值，此时就应使用SQLServer提供的另一个关键字IN。

格式：column_name[NOT]IN

(value1,value2,…)例4.14查询Seller表中SaleID为s01，s05，s07的销售人员信息。SELECTSaleID,SaleName,Sex,Birthday,HireDate,AddressFROMSellerWHERESaleIDIN('S01','S05','S07')SELECTSaleID,SaleName,Sex,Birthday,HireDate,AddressFROMSellerWHERESaleID='S01'ORSaleID='S05'ORSaleID='S07'在SQLServer中，用NULL表示空值，它仅仅是一个符号，不等于空格，也不等于0，空值判定的语法格式如下：

SELECT*FROMSellerWHEREHireDateISNULL【空值的判断】column_nameIS[NOT]NULL例4.16检索Salers表中雇用日期为空的销售人员的资料。3）排序在通常情况下，SQLServer数据库中的数据记录行在显示时是无序的，它按照数据记录插入数据库时的顺序排列，因此用SELECT语句查询的结果也是无序的。通过ORDERBY子句，可以将查询结果进行排序显示。其语法格式为：SELECT[ALL|DISTINCT]

[TOPn[PERCENT]select_list

FROMtable_nameWHEREsearch_conditionORDERBY

order_expression[ASC|DESC]]

升序，可省略降序排列SELECTSaleID,SaleName,Sex,Birthday,AddressFROMSellerORDERBYBirthdayDESCSELECT*FROMSellerORDERBYSaleNameASC例4.17从Seller表中按姓名顺序检索出所有销售员的信息。例4.18按出生日期列的降序排列Seller表。例4.19按出生日期的升序排列Seller表。SELECTSaleID,SaleName,Birthday,AddressFROMSellerORDERBY

3使用列所处的位置来指定排序列【注意】在默认情况下，ORDERBY子句按升序进行排序，即默认使用的是ASC关键字，如果特别要求按降序进行排列，必须使用DESC关键字。当ORDERBY子句指定了多个排序列时，系统先按照ORDERBY子句中第一列的顺序排列，当该列出现相同值时，再按照第二列的顺序排列，依次类推。例4.20查询Orders表中的数据，先按CustomerID的升序排列，当CustomerID相同时再按照SaleID的降序排列。SELECT*FROMOrdersORDERBYCustomerID,SaleIDDESC4）使用TOP和DISTINCT关键字【TOP关键字】在SELECT子句中利用TOP关键字限制返回到结果集中的行数。其语法格式为：

SELECT

[TOPinteger|TOPintegerPERCENT

]column_nameFROMtable_nameTOPinteger：表示返回表中最前面的几行，用integer表示返回的行数。TOPintegerPERCENT：用百分比表示返回的行数。例4.21分别从Customer表中检索出前5个及表中前20％的顾客信息。SELECTTOP

5*FROMCustomerSELECTTOP

20

PERCENT*FROMCustomer例4.22查询Product表中，价格最高的6种商品。SELECTTOP

6*FROMProductORDERBYPriceDESC

SELECT

[ALL|DISTINCT]column_name1[,column_name2,…]

FROMtable_name

WHEREsearch_condition允许重复数据行的出现，是默认的关键字从结果集中剔除重复的行【DISTINCT关键字】使用DISTINCT关键字可以从返回的结果集中删除重复的行，使结果更简洁。其语法格式为：例4.23查询OrderDetail表，显示订购的产品编号，如果多张订单订购了同一产品，只需显示一次产品编号。SELECTProductIDFROMOrderDetailSELECT

DISTINCTProductIDFROMOrderDetail【注意】DISTINCT关键字的作用范围是整个查询的结果集，而不是单独的一列。如果同时对两列数据进行查询时，使用了DISTINCT关键字，将返回这两列数据的唯一组合。课堂练习1、显示Customer表中的所有信息。2、显示Customer表中的CompanyName（公司名称）、ConnectName（联系人）、Telephone（电话）。3、从Product表中查询所有产品的信息，包括产品的总价值。并以中文名显示标题列。4、查询价格不在10~20元之间的产品信息。5、查询Seller表中女销售人员的信息。6、查询Seller表中在1970年之前出生的销售人员信息。7、在Seller表中查询姓“刘”的销售员信息。8、在Seller表中查询第2个字为“雪”的销售员信息。9、在Seller表中查询姓“张”并且名字为2个字的销售员信息。10、按产品价格降序排列Product表。11、先按性别升序、再按出生日期降序排列Seller表。12、查询Product表中库存最低的6种商品。1.多表查询

2.分组和汇总

高级查询3.嵌套查询

4.合并数据集

5.在查询的基础上创建新表

1、多表查询SQLServer提供了实现多表查询的方法――连接查询。所谓连接查询是将多个表以某个或某些列为条件进行连接，从中检索出关联数据。语法格式：教材P82。

所谓内连接是多个表通过连接条件中共享列的值进行的比较连接。当未指明连接类型时，默认为内连接。内连接值显示两个表中所有匹配数据的行。1）内连接（INNERJOIN）例4.24显示OrderID为‘10249’的产品ID、产品名称、产品数量及价格。

【分析】由于OrderID、ProductID以及Quantity这三列来自于OrderDetail表，而ProductName、Price来自于Product表，因此该查询涉及到多表查询。其中连接条件为两个表中的ProductID列值相等，以此查询出符合条件的数据信息。SELECTOrderID,OrderDetail.ProductID,ProductName,Price,QuantityFROMOrderDetailJOINProductON

OrderDetail.ProductID=Product.ProductIDWHEREOrderID='10249'【注意】缺省了连接类型，默认为内连接。当单个查询引用多个表时，所有列都必须明确。在查询所引用的两个或多个表之间，任何重复的列名都必须用表名限定，如OrderDetail.ProductID，表示引用了OrderDetail表中的ProductID列。如果某个列名在查询用到的两个或多个表中不重复，如ProductName，则对该列的引用不必用表名限定。为了增加可读性，可以使用表的别名。表的别名的命令语法格式为：FROMtable_nametable_alias例4.25修改例4.24，使用表的别名，结果和例4.24一样。SELECTOrderID,O.ProductID,ProductName,Price,QuantityFROMOrderDetailO

INNERJOINProductPON

O.ProductID=P.ProductIDWHEREOrderID='10249'例4.26查询OrderID为‘10248’的顾客ID、顾客姓名、销售员ID、销售员姓名。SELECTOrderID,O.CustomerID,ConnectName,O.SaleID,SaleNameFROMOrdersO

INNERJOINCustomerC

ON

O.CustomerID=C.CustomerIDINNERJOINSellerSON

O.SaleID=S.SaleIDWHEREOrderID='10248'【注意】一旦使用了别名代替某个表，则在连接时必须用表的别名，不能再用表的原名。2）外连接（OUTERJOIN）外连接显示包含来自一个表中所有行和来自另一个表中匹配行的结果集。外连接又分为左外连接、右外连接和完全外连接。

【左外连接（LEFTOUERJOIN）】左外连接返回LEFTOUTERJOIN关键字左侧指定的表（左表）的所有行和右侧指定的表（右表）的匹配的行。对于来自左表中的行，在右表中没有发现匹配的行，那么在来自右表中获得数据的列中将显示NULL值。例4.27显示所有产品的ProductID、ProductName、Price以及被顾客定购的OrderID、Quantity。SELECTP.ProductID,ProductName,Price,OrderID,QuantityFROMProductPLEFTOUTERJOINOrderDetailOONP.ProductID=O.ProductID例4.28显示所有顾客的信息以及他们订购产品的订单ID、销售员ID和订购日期。SELECTC.CustomerID,CompanyName,ConnectName,OrderID,SaleID,OrderDateFROMCustomerCLEFTOUTERJOINOrdersOONC.CustomerID=O.CustomerID【结果】左表Customer中的所有行都显示出来，而不管Orders表中是否订购了这种产品。

【右外连接（RIGHTOUERJOIN）】右外连接即在连接两表时，不管左表中是否有匹配数据，结果将保留右表中的所有行。例4.29修改例4.28使用右外连接。

SELECTOrderID,Quantity,P.ProductID,ProductName,Price

FROMOrderDetailORIGHTOUTERJOINProductPONO.ProductID=P.ProductID【结果】包含了右表中的所有数据行，而不管左表中是否有匹配数据。【完全外连接（FULLOUERJOIN）】完全外连接是左外连接和右外连接的组合。这个连接返回来自两个表的所有匹配和非匹配行。其中，匹配记录仅被显示一次。在非匹配行的情况下，对于数据不可用的列将显示NULL值。

例4.30需要了解所有产品的基本信息和类别信息。

SELECTProductID,ProductName,Price,Stocks,C.CategoryID,CategoryNameFROMProductPFULLOUTERJOINCategoryCON

P.CategoryID=C.CategoryID2、分组与汇总函数名描述AVG([ALL|DISTINCT][expression])返回表达式的平均值MAX(expression)返回表达式中的最大值MIN(expression)返回表达式中的最小值SUM([ALL|DISTINCT][expression])返回表达式中所有值的和COUNT([ALL|DISTINCT][expression])返回表中指定列的数据记录行数。使用DISTINCT关键字删除重复值COUNT(*)返回表中所有数据记录的行数例4.32求Product表中，所有产品的平均价格、最高价、最低价以及总库存。

SELECT

AVG(Price)AS'平均价格'FROMProduct

SELECT

MAX(Price)AS'最高价格'FROMProduct

SELECT

MIN(Price)AS'最低价格'FROMProduct

SELECT

SUM(Stocks)AS'总库存'FROMProduct例4.33统计Product表中，库存量>200的产品数量。

SELECTCOUNT(ProductID)FROMProductWHEREStocks>200例4.34统计Product表中的产品种数。

SELECTCOUNT(*)FROMProduct

【1）使用分组汇总子句】显示分组的汇总数据，必须使用GROUPBY子句。该子句的功能是根据指定的列将表中数据分成多个组后进行汇总。其语法格式为：SELECTcolumn_name1[,…n]FROMtable_nameWHEREsearch_conditionGROUPBY[ALL]colum_name1[,…n][HAVINGsearch_condition]例4.35将Product表中的数据按CategoryID进行分组，然后分别统计每一组产品的平均价格及总库存。SELECTCategoryID,AVG(Price)AS'平均价格',SUM(Stocks)AS'总库存'FROMProduct

GROUPBYCategoryID【注意】使用GROUPBY子句为每一个组产生一个汇总结果，每个组只返回一行，不返回详细信息。SELECT子句中指定的列必须是GROUPBY子句中指定的列，或者是和聚合函数一起使用。如果包含WHERE子句，则只对满足WHERE条件的行进行分组汇总。如果GROUPBY子句使用关键字ALL，则WHERE子句将不起作用。HAVING子句可进一步排除不满足条件的组。当同时存在GROUPBY子句、HAVING子句和WHERE子句时，其执行顺序为：先WHERE子句，后GROUPBY子句，再HAVING子句。即先用WHERE子句过滤不符合条件的数据记录，接着用GROUPBY子句对余下的数据记录按指定列分组、汇总，最后再用HAVING子句排除不符合条件的组。例4.36在上例基础上只显示平均价格低于10元的分组汇总信息。

SELECTCategoryID,AVG(Price)AS'平均价格',SUM(Stocks)AS'总库存'

FROMProduct

GROUP

BYCategoryID

HAVING

AVG(Price)<10例4.37对所有价格大于5元且组平均价格大于15元的价格表目，列出所有的产品的类型、平均价格及总库存。SELECTCategoryID,AVG(Price)AS'平均价格',SUM(Stocks)AS'总库存'

FROMProduct

WHEREPrice>5GROUPBYCategoryID

HAVING

AVG(Price)>15注意：如果GROUPBY子句中指定了多个列，则表示要基于这些列的唯一组合来进行分组。在分组过程中，首先按第一列进行分组并按升序排列，然后再按第二列进行分组并按升序排列，依次类推，最后在分好的组中进行汇总。

【2）使用明细汇总子句】使用GROUPBY对查询出来的数据作分类汇总后，只能显示统计结果，看不到详细的数据。使用COMPUTE和COMPUTEBY子句既能浏览详细数据又可看到统计的结果。其语法格式为：COMPUTEaffregate_function(column_name)[,…n][BYcolumn_name[,…n]]其中：affregate_function表示聚合函数。类似于总计类似于小计例4.38使用COMPUTE子句对所有订单订购产品数量进行明细汇总。

SELECTOrderID,ProductID,QuantityFROMOrderDetail

COMPUTE

SUM(Quantity)例4.39用COMPUTEBY子句按订单号汇总出OrderDetail表中每个订单订购产品的数量。

SELECTOrderID,ProductID,QuantityFROMOrderDetail

ORDERBYOrderID

COMPUTESUM(Quantity)BYOrderID【注意】COMPUTE[BY]子句不能与SELECTINTO子句一起使用。COMPUTE子句中的列必须出现在SELECT子句的列表中。COMPUTEBY表示按指定的列进行明细汇总，使用BY关键字时必须同时使用ORDERBY子句，并且COMPUTEBY后出现的列必须具有与ORDERBY后出现的列相同的顺序，且不能跳过其中的列。例如：如果ORDERBY子句按照如下顺序指定排序列：

ORDERBYCategoryID,Price,Stocks则COMPUTEBY后的列表只能是下面任一种形式：

BYCategoryID,Price,StocksBYCategoryID,PriceBYCategoryID例4.40用COMPUTEBY子句按类别ID汇总出Product表中每类产品的平均价格和总库存量。

SELECTCategoryID,ProductID,Price,StocksFROMProduct

ORDERBYCategoryID,PriceCOMPUTE

AVG(Price),SUM(Stocks)BYCategoryID课堂练习1、查询产品类别为“饮料”的所有产品信息。2、显示所有客户的信息，以及其订购产品的OrderID、OrderDate。（使用左外连接）3、显示所有销售员的信息，以及其销售产品的OrderID、OrderDate。（使用右外连接）4、统计产品类别为“饮料”的产品的种类数量、以及总库存。5、统计产品类别为“调味品”的产品的平均价格、最高价、以及最低价。6、统计产品编号为“p01005”所销售总量。7、按照“性别”进行分组，分别统计Seller表中男、女的人数。8、按照“类别编号”进行分组，列出所有库存量大于200且组平均价格大于50元的产品类型编号、平均价格、以及库存总量。9、查询所有产品信息，并使用COMPUTE子句对Product表中的产品种类、以及库存总量进行明细汇总。10、查询产品信息，并使用COMPUTEBY子句按CategoryID汇总出Product表中每种类别产品的平均价格、以及库存总量。1、嵌套查询所谓嵌套查询指的是在一个SELECT查询内再嵌入一个SELECT查询。外层的SELECT语句叫外部查询；内层的SELECT语句叫子查询。使用子查询时需注意：子查询可以嵌套多层。子查询需用圆括号（）括起来。子查询中不能使用COMPUTE[BY]和INTO子句。子查询的SELECT语句中不能使用image、text或ntext数据类型。【1）子查询返回值的类型为单值单列】例4.41查询订单ID为‘10249’的顾客信息。

SELECTCustomerID,CompanyName,ConnectName,Address,ZipCode,TelephoneFROMCustomerWHERECustomerID=

(SELECTCustomerIDFROMOrdersWHEREOrderID='10249')该子查询的执行过程：首先对子查询求值（仅一次），求出OrderID为‘10249’的CustomerID为‘c02’，然后作外部查询，外部查询依赖于子查询的结果。例4.42显示所有价格高于平均价格的产品。

SELECT*FROMProductWHEREPrice>(SELECTavg(Price)FROMProduct)【注意】在例4.41和例4.42中，子查询的结果是用于比较的单值单列数据；如果子查询中返回的是单列多值，则必须在子查询前使用关键字ALL或ANY，否则系统会提示错误信息。关键字含义示例ALL比较子查询的所有值>ALL大于子查询结果中的所有值（大于最大的）<ALL小于子查询结果中的所有值（小于最小的）>=ALL大于等于子查询结果中的所有值<=ALL小于等于子查询结果中的所有值=ALL等于子查询结果中的所有值（通常没有实际意义）<>ALL不等于子查询结果中的任何一个值

ANY比较子查询的任一值>ANY大于子查询结果中的某个值（大于最小）<ANY小于子查询结果中的某个值（小于最大）>=ANY大于等于子查