3.3.7-- =============================================-- Create database template-- =============================================USE masterGO-- Drop the database if it already existsIF  EXISTS (    SELECT name         FROM sys.databases         WHERE name = N‘test‘)DROP DATABASE testGOCREATE DATABASE testGO3.3.8IF  EXISTS (    SELECT * FROM sys.objects         WHERE object_id = OBJECT_ID(‘<table_name, nvarchar(50), name>‘)            AND type in (‘U‘))    print ‘该数据表存在‘else    print ‘该数据表不存在‘5.3.1CREATE DATABASE database_name                                --设置建立数据库的名称    [ ON                                                      --设置建立数据库的数据文件        [ PRIMARY ] [ <filespec> [ ,...n ]                                          --设置文件        [ , <filegroup> [ ,...n ] ]                                                 --设置文件组    [ LOG ON { <filespec> [ ,...n ] } ]                              --设置建立数据库的日志文件    ]     [ COLLATE collation_name ]                                 --设置数据库的排序规则名称    [ WITH <external_access_option> ]                     --设置外部与数据库之间的双向访问][;]To attach a database                                                   --附加数据库的语法CREATE DATABASE database_name     ON <filespec> [ ,...n ]                                                 --指定附加的文件    FOR { ATTACH [ WITH <service_broker_option> ]          | ATTACH_REBUILD_LOG }                     --指定附加的文件，只限于读/写数据库[;]<filespec> ::=                                                       --<filespec>的语法内容{(    NAME = logical_file_name ,    FILENAME = ‘os_file_name‘         [ , SIZE = size [ KB | MB | GB | TB ] ]         [ , MAXSIZE = { max_size [ KB | MB | GB | TB ] | UNLIMITED } ]         [ , FILEGROWTH = growth_increment [ KB | MB | GB | TB | % ] ]) [ ,...n ]}<filegroup> ::=                                                    --< filegroup>的语法内容{FILEGROUP filegroup_name [ DEFAULT ]    <filespec> [ ,...n ]}<external_access_option> ::=                          --< external_access_option>的语法内容{    DB_CHAINING { ON | OFF }  | TRUSTWORTHY { ON | OFF }}<service_broker_option> ::=                           --< service_broker_option >的语法内容{    ENABLE_BROKER  | NEW_BROKER  | ERROR_BROKER_CONVERSATIONS}5.3.5CREATE DATABASE 测试数据库CREATE DATABASE 例二数据库ON (    NAME=‘例二数据库‘,    FILENAME=‘D:\DBtest\例二数据库.mdf‘)CREATE DATABASE 例三数据库ON (    NAME=‘例三数据库‘,    FILENAME=‘D:\DBtest\例三数据库.mdf‘,    SIZE=5MB,    MAXSIZE=10MB,    FILEGROWTH=5%)CREATE DATABASE 例四数据库ON (    NAME=‘例四数据库数据文件‘,    FILENAME=‘D:\DBtest\例四数据库数据文件.mdf‘,    SIZE=5MB,    MAXSIZE=10MB,    FILEGROWTH=5%)LOG ON(    NAME=‘例四数据库日志文件‘,    FILENAME=‘D:\DBtest\例四数据库日志文件.ldf‘)CREATE DATABASE 例四数据库ON (    NAME=‘例四数据库逻辑数据文件‘,    FILENAME=‘D:\DBtest\例四数据库数据文件.mdf‘,    SIZE=5MB,    MAXSIZE=10MB,    FILEGROWTH=5%)LOG ON(    NAME=‘例四数据库逻辑日志文件‘,    FILENAME=‘D:\DBtest\例四数据库日志文件.ldf‘)CREATE DATABASE 例五数据库ON (    NAME=‘例五数据库数据文件1‘,    FILENAME=‘D:\DBtest\例五数据库数据文件1.mdf‘,    SIZE=5MB,    MAXSIZE=10MB,    FILEGROWTH=5%),(    NAME=‘例五数据库数据文件2‘,    FILENAME=‘D:\DBtest\例五数据库数据文件2.ndf‘,    SIZE=5MB,    MAXSIZE=10MB,    FILEGROWTH=5%),FILEGROUP 例五数据库数据文件组1(    NAME=‘例五数据库数据文件组1的数据文件‘,    FILENAME=‘D:\DBtest\例五数据库数据文件组1的数据文件.ndf‘,    SIZE=5MB,    MAXSIZE=10MB,    FILEGROWTH=5%),FILEGROUP 例五数据库数据文件组2(    NAME=‘例五数据库数据文件组2的数据文件1‘,    FILENAME=‘D:\DBtest\例五数据库数据文件组2的数据文件1.ndf‘,    SIZE=5MB,    MAXSIZE=10MB,    FILEGROWTH=5%),(    NAME=‘例五数据库数据文件组2的数据文件2‘,    FILENAME=‘D:\DBtest\例五数据库数据文件组2的数据文件2.ndf‘,    SIZE=5MB,    MAXSIZE=10MB,    FILEGROWTH=5%)LOG ON(    NAME=‘例五数据库日志‘,    FILENAME=‘D:\DBtest\例五数据库日志文件.ldf‘) CREATE DATABASE 例六数据库ON (    NAME=‘例六数据库‘,    FILENAME=‘D:\DBtest\例六数据库.mdf‘)COLLATE Chinese_PRC_CI_ASselect * from ::fn_helpcollations ()5.3.6-- =============================================-- Create database template-- =============================================USE masterGO-- Drop the database if it already existsIF  EXISTS (    SELECT name         FROM sys.databases         WHERE name = N‘测试用数据库‘)DROP DATABASE 测试用数据库GOCREATE DATABASE 测试用数据库GO-- =============================================-- Create database on mulitple filegroups-- =============================================IF EXISTS (  SELECT *     FROM sys.databases    WHERE name = N‘<database_name, sysname, sample_database>‘)  DROP DATABASE <database_name, sysname, sample_database>GOCREATE DATABASE <database_name, sysname, sample_database>ON PRIMARY    (NAME = <logical_filename1, , sample_database_file1>,      FILENAME = N‘<data_filename1, , C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Datasample_database_1.mdf>‘,          SIZE = 10MB,          MAXSIZE = 50MB,          FILEGROWTH = 10%),        ( NAME = <logical_filename2, , sample_database_file2>,      FILENAME = N‘<data_filename2, , C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Datasample_database_2.ndf>‘,          SIZE = 10MB,          MAXSIZE = 50MB,          FILEGROWTH = 10%),FILEGROUP <filegroup_1, , sample_database_filegroup1>    ( NAME = <logical_filename3, , sample_database_file3>,      FILENAME = N‘<data_filename3, , C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Datasample_database_3.ndf>‘,          SIZE = 10MB,          MAXSIZE = 50MB,          FILEGROWTH = 10%),        ( NAME = <logical_filename4, , sample_database_file4>,      FILENAME = N‘<data_filename4, , C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Datasample_database_4.ndf>‘,          SIZE = 10MB,          MAXSIZE = 50MB,          FILEGROWTH = 10%),FILEGROUP <filegroup2, , sample_database_group_2>    ( NAME = <logical_filename5, , sample_database_file5>,      FILENAME = N‘<data_filename5, , C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Datasample_database_5.ndf>‘,          SIZE = 10MB,          MAXSIZE = 50MB,          FILEGROWTH = 10%)LOG ON    ( NAME = <logical_log_filename1, , sample_database_log_file1>,      FILENAME = N‘<log_filename1, , C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Datasample_database_1.ldf>‘,          SIZE = 10MB,          MAXSIZE = 50MB,          FILEGROWTH = 10%),    ( NAME = <logical_log_filename2, , sample_database_log_file2>,      FILENAME = N‘<log_filename2, , C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Datasample_database_2.ldf>‘,          SIZE = 5MB,          MAXSIZE = 25MB,          FILEGROWTH = 5MB)GO5.5.1ALTER DATABASE database_name                                     --要修改的数据库名{    <add_or_modify_files>                                           --添加或修改数据库文件  | <add_or_modify_filegroups>                                    --添加或修改数据库文件组  | <set_database_options>                                               --设置数据库选项  | MODIFY NAME = new_database_name                                          --重命名  | COLLATE collation_name                                                --修改排序规则}[;]<add_or_modify_files>::={    ADD FILE <filespec> [ ,...n ]         [ TO FILEGROUP { filegroup_name | DEFAULT } ]   | ADD LOG FILE <filespec> [ ,...n ]   | REMOVE FILE logical_file_name   | MODIFY FILE <filespec>}<filespec>::= (    NAME = logical_file_name      [ , NEWNAME = new_logical_name ]     [ , FILENAME = ‘os_file_name‘ ]     [ , SIZE = size [ KB | MB | GB | TB ] ]     [ , MAXSIZE = { max_size [ KB | MB | GB | TB ] | UNLIMITED } ]     [ , FILEGROWTH = growth_increment [ KB | MB | GB | TB| % ] ]     [ , OFFLINE ]) <add_or_modify_filegroups>::={    | ADD FILEGROUP filegroup_name     | REMOVE FILEGROUP filegroup_name     | MODIFY FILEGROUP filegroup_name        { <filegroup_updatability_option>         | DEFAULT        | NAME = new_filegroup_name         }}<filegroup_updatability_option>::={    { READONLY | READWRITE }     | { READ_ONLY | READ_WRITE }}<set_database_options>::=SET {    { <optionspec> [ ,...n ] [ WITH <termination> ] }    | ALLOW_SNAPSHOT_ISOLATION {ON | OFF }    | READ_COMMITTED_SNAPSHOT {ON | OFF } [ WITH <termination> ]}<optionspec>::= {    <db_state_option>  | <db_user_access_option>   | <db_update_option>   | <external_access_option>  | <cursor_option>   | <auto_option>   | <sql_option>   | <recovery_option>   | <database_mirroring_option>  | <supplemental_logging_option>  | <service_broker_option>  | <date_correlation_optimization_option>  | <parameterization_option>}5.5.4ALTER DATABASE 例二数据库    MODIFY NAME = 例七数据库 或exec sp_renamedb ‘例二数据库‘,‘例七数据库‘ALTER DATABASE 例六数据库    ADD FILE (NAME=增加的数据文件,        FILENAME=‘D:\DBtest\例六数据库增加的数据文件.ndf‘)ALTER DATABASE 例六数据库    ADD LOG FILE (NAME=例九增加的日志文件,        FILENAME=‘D:\DBtest\例九增加的日志文件.ldf‘,        SIZE=3MB,        MAXSIZE=50MB,        FILEGROWTH=10%)ALTER DATABASE 例六数据库    MODIFY FILE (NAME=增加的数据文件,    NEWNAME = 例十数据文件,FILENAME = ‘D:\DBtest\例十数据文件.ndf‘)ALTER DATABASE 例六数据库    COLLATE Chinese_PRC_CI_AS_KSALTER DATABASE 例六数据库    REMOVE FILE 例十数据文件ALTER DATABASE 例六数据库    ADD FILEGROUP 例十三文件组ALTER DATABASE 例六数据库    MODIFY FILEGROUP 例十三文件组    NAME = 例十四文件组ALTER DATABASE 例六数据库    ADD FILE (NAME=例十五数据文件,        FILENAME=‘D:\DBtest\例十五数据文件.ndf‘)        TO FILEGROUP 例十四文件组GOALTER DATABASE 例六数据库    MODIFY FILEGROUP 例十四文件组 DEFAULTGOALTER DATABASE 例六数据库    MODIFY FILEGROUP [PRIMARY] DEFAULTGOALTER DATABASE 例六数据库    REMOVE FILE 例十五数据文件GOALTER DATABASE 例六数据库    REMOVE FILEGROUP 例十四文件组GOALTER DATABASE 例六数据库    ADD FILEGROUP 例十七文件组GOALTER DATABASE 例六数据库    ADD FILE (NAME=例十七数据文件,        FILENAME=‘D:\DBtest\例十七数据文件.ndf‘)        TO FILEGROUP 例十七文件组GOALTER DATABASE 例六数据库    MODIFY FILEGROUP 例十七文件组 READ_ONLY GOALTER DATABASE 例六数据库    SET SINGLE_USERALTER DATABASE 例六数据库    SET AUTO_SHRINK ON5.6.1exec sp_detach_db ‘例四数据库‘use masterexec sp_whouse masterkill 52kill 54exec sp_detach_db ‘例四数据库‘5.6.2use masterCREATE DATABASE 例三数据库    ON (FILENAME = ‘D:\DBtest\例三数据库.mdf‘)    FOR ATTACHuse masterCREATE DATABASE 例三数据库    ON (FILENAME = ‘D:\test\例三数据库.mdf‘),    (FILENAME=‘D:\test\例三数据库_log.LDF‘)    FOR ATTACHuse masterexec sp_attach_db 例三数据库,‘D:\test\例三数据库.mdf‘use masterexec sp_attach_db 例三数据库,‘D:\test\例三数据库.mdf‘,‘D:\test\例三数据库_log.LDF‘5.7.1use masterALTER DATABASE 例三数据库    set OFFLINEuse masterALTER DATABASE 例三数据库    set ONLINEuse masterDROP DATABASE 测试数据库use masterDROP DATABASE 测试用数据库,test5.9.1ALTER DATABASE 例六数据库    SET AUTO_SHRINK ON5.9.2DBCC SHRINKDATABASE ( ‘database_name‘ | database_id | 0      [ ,target_percent ]      [ , { NOTRUNCATE | TRUNCATEONLY } ] )[ WITH NO_INFOMSGS ]DBCC SHRINKDATABASE (例六数据库)DBCC SHRINKDATABASE (例六数据库,10)5.9.3DBCC SHRINKFILE (    { ‘file_name‘ | file_id }     { [ , EMPTYFILE ]     | [ [ , target_size ] [ , { NOTRUNCATE | TRUNCATEONLY } ] ]    })[ WITH NO_INFOMSGS ]use 例六数据库goDBCC SHRINKFILE     (例六数据库_log)gouse 例六数据库goDBCC SHRINKFILE     (例六数据库,3)go5.10USE [master]GO/****** 对象:  Database [例六数据库]    脚本日期: 07/14/2006 15:27:58 ******/CREATE DATABASE [例六数据库] ON  PRIMARY ( NAME = N‘例六数据库‘, FILENAME = N‘D:\DBtest\例六数据库.mdf‘ , SIZE = 1408KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),  FILEGROUP [例十七文件组] ( NAME = N‘例十七数据文件‘, FILENAME = N‘D:\DBtest\例十七数据文件.ndf‘ , SIZE = 1024KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),  FILEGROUP [例十四文件组] ( NAME = N‘例十五数据文件‘, FILENAME = N‘D:\DBtest\例十五数据文件.ndf‘ , SIZE = 1024KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) LOG ON ( NAME = N‘例九增加的日志文件‘, FILENAME = N‘D:\DBtest\例九增加的日志文件.ldf‘ , SIZE = 3072KB , MAXSIZE = 51200KB , FILEGROWTH = 10%), ( NAME = N‘例六数据库_log‘, FILENAME = N‘D:\DBtest\例六数据库_log.LDF‘ , SIZE = 560KB , MAXSIZE = 2048GB , FILEGROWTH = 10%) COLLATE Chinese_PRC_CI_AS_KSGOEXEC dbo.sp_dbcmptlevel @dbname=N‘例六数据库‘, @new_cmptlevel=90GOIF (1 = FULLTEXTSERVICEPROPERTY(‘IsFullTextInstalled‘))beginEXEC [例六数据库].[dbo].[sp_fulltext_database] @action = ‘enable‘endGOALTER DATABASE [例六数据库] SET ANSI_NULL_DEFAULT OFF GOALTER DATABASE [例六数据库] SET ANSI_NULLS OFF GOALTER DATABASE [例六数据库] SET ANSI_PADDING OFF GOALTER DATABASE [例六数据库] SET ANSI_WARNINGS OFF GOALTER DATABASE [例六数据库] SET ARITHABORT OFF GOALTER DATABASE [例六数据库] SET AUTO_CLOSE OFF GOALTER DATABASE [例六数据库] SET AUTO_CREATE_STATISTICS ON GOALTER DATABASE [例六数据库] SET AUTO_SHRINK ON GOALTER DATABASE [例六数据库] SET AUTO_UPDATE_STATISTICS ON GOALTER DATABASE [例六数据库] SET CURSOR_CLOSE_ON_COMMIT OFF GOALTER DATABASE [例六数据库] SET CURSOR_DEFAULT  GLOBAL GOALTER DATABASE [例六数据库] SET CONCAT_NULL_YIELDS_NULL OFF GOALTER DATABASE [例六数据库] SET NUMERIC_ROUNDABORT OFF GOALTER DATABASE [例六数据库] SET QUOTED_IDENTIFIER OFF GOALTER DATABASE [例六数据库] SET RECURSIVE_TRIGGERS OFF GOALTER DATABASE [例六数据库] SET  ENABLE_BROKER GOALTER DATABASE [例六数据库] SET AUTO_UPDATE_STATISTICS_ASYNC OFF GOALTER DATABASE [例六数据库] SET DATE_CORRELATION_OPTIMIZATION OFF GOALTER DATABASE [例六数据库] SET TRUSTWORTHY OFF GOALTER DATABASE [例六数据库] SET ALLOW_SNAPSHOT_ISOLATION OFF GOALTER DATABASE [例六数据库] SET PARAMETERIZATION SIMPLE GOALTER DATABASE [例六数据库] SET  READ_WRITE GOALTER DATABASE [例六数据库] SET RECOVERY FULL GOALTER DATABASE [例六数据库] SET  MULTI_USER GOALTER DATABASE [例六数据库] SET PAGE_VERIFY CHECKSUM  GOALTER DATABASE [例六数据库] SET DB_CHAINING OFF6.4.1CREATE TABLE     [ database_name . [ schema_name ] . | schema_name . ] table_name            --设置表名        (         { <column_definition>                                                 --设置列属性        | <computed_column_definition> }                                      --设置计算列        [ <table_constraint> ] [ ,...n ] )                                          --设置表约束    [ ON             { partition_scheme_name ( partition_column_name )             | filegroup         | "default" } ]                                   --指定存放表数据的分区架构或文件组    [ { TEXTIMAGE_ON             --指定存放Text及Image类型字段数据的分区架构或文件组            { filegroup             | "default" } ]6.4.2<column_definition> ::=column_name <data_type>                                                          --列名    [ COLLATE collation_name ]                                              ---列排序规则    [ NULL | NOT NULL ]                                                     --列是否为空    [         [ CONSTRAINT constraint_name ]                                         --列约束DEFAULT constant_expression ]                                           --缺省值      | [ IDENTITY [ ( seed ,increment ) ]                                           --标识列[ NOT FOR REPLICATION ]                                            --不用于复制    ][ ROWGUIDCOL ]                                              --GUID列（全球惟一值）[ <column_constraint> [ ...n ] ]                                                --设置约束<data type> ::= [ type_schema_name . ] type_name                                    --列的数据类型及架构[ ( precision                                                          --数据类型的精度[ , scale ] | max |                                                            --小数位数        [ { CONTENT | DOCUMENT } ] xml_schema_collection ) ]           --指定xml数据类型<column_constraint> ::= [ CONSTRAINT constraint_name ]                                             --设置约束名{     { PRIMARY KEY| UNIQUE }                                 --设置主键或UNIQUE约束        [ CLUSTERED | NONCLUSTERED ]                     --指定聚集索引或非聚集索引        [             WITH FILLFACTOR = fillfactor                                   --指定填充因子          | WITH ( < index_option > [ , ...n ] )                       --指定一个或多个索引选项        ]        [ ON { partition_scheme_name ( partition_column_name )              | filegroup | "default" } ]                                    --指定索引存放的位置  | [ FOREIGN KEY ]                                                       --设置外键约束        REFERENCES [ schema_name . ] referenced_table_name [ ( ref_column ) ] --设置外键所引用的表及字段        [ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]                                                                           --设置删除规则        [ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]                                                                           --设置更新规则        [ NOT FOR REPLICATION ]                                                                           --设置强制复制  | CHECK [ NOT FOR REPLICATION ] ( logical_expression )               --设置CHECK约束}<computed_column_definition> ::=column_name AS computed_column_expression                                --定义计算列[ PERSISTED [ NOT NULL ] ]                                                    --设置更新[     [ CONSTRAINT constraint_name ]                                           --设置约束    { PRIMARY KEY | UNIQUE }                                  --设置主键或UNIQUE约束        [ CLUSTERED | NONCLUSTERED ]                     --指定聚集索引或非聚集索引        [             WITH FILLFACTOR = fillfactor                                   --指定填充因子          | WITH ( <index_option> [ , ...n ] )                          --指定一个或多个索引选项        ]    | [ FOREIGN KEY ]                                                     --设置外键约束        REFERENCES referenced_table_name [ ( ref_column ) ] --设置外键所引用的表及字段        [ ON DELETE { NO ACTION | CASCADE } ]                           --设置删除规则                                                                   [ ON UPDATE { NO ACTION } ]                                      --设置更新规则        [ NOT FOR REPLICATION ]                                         --设置强制复制    | CHECK [ NOT FOR REPLICATION ] ( logical_expression )             --设置CHECK约束    [ ON { partition_scheme_name ( partition_column_name )         | filegroup | "default" } ]                                            --为约束创建索引]<table_constraint> ::=[ CONSTRAINT constraint_name ] {     { PRIMARY KEY | UNIQUE }                                  --设置主键或UNIQUE约束        [ CLUSTERED | NONCLUSTERED ]                     --指定聚集索引或非聚集索引                (column [ ASC | DESC ] [ ,...n ] ) --指定加入到表约束中的一列或多列的排序顺序。        [             WITH FILLFACTOR = fillfactor                                   --指定填充因子           |WITH ( <index_option> [ , ...n ] )                        --指定一个或多个索引选项        ]        [ ON { partition_scheme_name (partition_column_name)            | filegroup | "default" } ]                                    --指定索引存放的位置    | FOREIGN KEY                                                       --设置外键约束                ( column [ ,...n ] )         REFERENCES referenced_table_name [ ( ref_column [ ,...n ] ) ] --设置外键所引用的表及字段        [ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]                                                                           --设置删除规则        [ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]                                                                           --设置更新规则        [ NOT FOR REPLICATION ]                                                                           --设置强制复制    | CHECK [ NOT FOR REPLICATION ] ( logical_expression )             --设置CHECK约束} <index_option> ::={     PAD_INDEX = { ON | OFF }                                             --是否填充索引  | FILLFACTOR = fillfactor                                                 --设置填充因子  | IGNORE_DUP_KEY = { ON | OFF }                                 --重复键错误响应方式  | STATISTICS_NORECOMPUTE = { ON | OFF }                         --重新计算统计数据  | ALLOW_ROW_LOCKS = { ON | OFF}                                       --允许行锁定  | ALLOW_PAGE_LOCKS ={ ON | OFF}                                       --允许页锁定}6.4.3CREATE TABLE Northwind.dbo.例一表(    编号 int,    姓名 nvarchar(50))CREATE TABLE Northwind..例一表(    编号 int,    姓名 nvarchar(50))USE NorthwindCREATE TABLE 例一表(    编号 int,    姓名 nvarchar(50))6.4.4CREATE TABLE 例二表(    编号 int IDENTITY,    姓名 nvarchar(50))CREATE TABLE 例三表(    编号 int IDENTITY(1,2) PRIMARY KEY,    姓名 nvarchar(50))CREATE TABLE 例四表(    编号 int IDENTITY(1,1) PRIMARY KEY,    姓名 nvarchar(50) NOT NULL)6.4.7CREATE TABLE 例五表(    编号 int IDENTITY(1,1) PRIMARY KEY,    姓名 nvarchar(50) NOT NULL,    性别 bit DEFAULT 1)6.4.8CREATE TABLE 例六表(    编号 int IDENTITY(1,1) PRIMARY KEY,    姓名 nvarchar(50) NOT NULL,    性别 bit DEFAULT 1)ON 第二文件组6.4.9CREATE TABLE 例七表(    编号 int IDENTITY(1,1) PRIMARY KEY,    姓名 nvarchar(50) NOT NULL,    性别 bit DEFAULT 1,    年纪 tinyint CONSTRAINT CK_年纪 CHECK (年纪>0 AND 年纪<101))CREATE TABLE 例八表(    编号 int IDENTITY(1,1) PRIMARY KEY,    姓名 nvarchar(50) COLLATE Chinese_PRC_CI_AS Not null,    性别 bit DEFAULT 1,    年纪 tinyint CONSTRAINT CK_例八年纪 CHECK (年纪>0 AND 年纪<101))6.4.11CREATE TABLE 例九_部门表(    部门编号 int IDENTITY(1,1) PRIMARY KEY,    部门名称 nvarchar(50) Not null)GOCREATE TABLE 例九_员工表(    员工编号 int IDENTITY(1,1) PRIMARY KEY,    所属部门 int         CONSTRAINT FK_员工表外键        FOREIGN KEY        REFERENCES 例九_部门表(部门编号),    员工姓名 nvarchar(20) not null)GO6.4.12CREATE TABLE 例十_部门表(    部门编号 int IDENTITY(1,1) PRIMARY KEY,    部门名称 nvarchar(50) Not null)GOCREATE TABLE 例十_员工表(    员工编号 int IDENTITY(1,1) PRIMARY KEY,    所属部门 int         CONSTRAINT FK_例十_员工表外键        FOREIGN KEY        REFERENCES 例十_部门表(部门编号)        ON UPDATE CASCADE         ON DELETE SET NULL,    员工姓名 nvarchar(20) not null)GO6.4.13CREATE TABLE 例十一_部门表(    部门编号 int IDENTITY(1,1) PRIMARY KEY,    部门名称 nvarchar(50) Not null UNIQUE)6.4.14CREATE TABLE 例十二(    编号 int IDENTITY(1,1) PRIMARY KEY,    单价 money Not null,    数量 int Not null          CONSTRAINT CK_例十二 CHECK (数量>0),    合计 as 单价*数量)6.4.15CREATE TABLE 例十三_用户注册表(    编号 int IDENTITY(1,1) PRIMARY KEY,    用户名 nvarchar(50) Not null,    密码 varchar(16) Not null,    电话 varchar(50),    地址 nvarchar(200),    CHECK (电话 is not null or 地址 is not null))6.6.1ALTER TABLE [ database_name . [ schema_name ] . | schema_name . ] table_name {     ALTER COLUMN column_name                                       --要修改的字段名    {         [ type_schema_name. ] type_name [ ( { precision [ , scale ]             | max | xml_schema_collection } ) ]                           --修改后的数据类型        [ NULL | NOT NULL ]                                           --设置是否为NULL        [ COLLATE collation_name ]                                         –设置排序规则    | {ADD | DROP } { ROWGUIDCOL | PERSISTED }         --添加或删除ROWGUIDCOL属性    }     | [ WITH { CHECK | NOCHECK } ] ADD                                       --添加字段    {         <column_definition>                                                --设置字段属性      | <computed_column_definition>                                         --设置计算列      | <table_constraint>                                                    --设置表约束    } [ ,...n ]    | DROP                                                                       --删除    {         [ CONSTRAINT ] constraint_name                                       --删除约束        [ WITH ( <drop_clustered_constraint_option> [ ,...n ] ) ]              --设置聚集约束选项        | COLUMN column_name                                               –删除字段    } [ ,...n ]     | [ WITH { CHECK | NOCHECK } ] { CHECK | NOCHECK } CONSTRAINT   --启用或禁用约束        { ALL | constraint_name [ ,...n ] }     | { ENABLE | DISABLE } TRIGGER                                   --启用或禁用触发器        { ALL | trigger_name [ ,...n ] }    | SWITCH [ PARTITION source_partition_number_expression ]                --切换数据块        TO [ schema_name. ] target_table         [ PARTITION target_partition_number_expression ]}6.6.2<column_definition> ::=column_name [ type_schema_name. ] type_name                                  --数据类型    [                 ( { precision [ , scale ] | max |             [ { CONTENT | DOCUMENT } ] xml_schema_collection } )     ]     [         [ CONSTRAINT constraint_name ] DEFAULT constant_expression               --约束             [ WITH VALUES ]         | IDENTITY [ (seed , increment ) ] [ NOT FOR REPLICATION ]     ]     [ ROWGUIDCOL ]                                             --GUID列（全球惟一值）    [ COLLATE < collation_name > ]                                           --列排序规则    [ <column_constraint> [ ...n ] ]<column_constraint> ::= [ CONSTRAINT constraint_name ]                                             --设置约束名{     [ NULL | NOT NULL ]     { PRIMARY KEY | UNIQUE }                                  --设置主键或UNIQUE约束        [ CLUSTERED | NONCLUSTERED ]                     --指定聚集索引或非聚集索引        [ WITH FILLFACTOR =fillfactor ]                                     --指定填充因子        [ WITH ( index_option [, ...n ] ) ]                            --指定一个或多个索引选项        [ ON { partition_scheme_name (partition_column_name)             | filegroup | "default" } ]                                    --指定索引存放的位置    | [ FOREIGN KEY ]                                                     --设置外键约束        REFERENCES [ schema_name . ] referenced_table_name             [ ( ref_column ) ]                                   --设置外键所引用的表及字段        [ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ] --设置删除规则        [ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ] --设置更新规则        [ NOT FOR REPLICATION ]                                         --设置强制复制    | CHECK [ NOT FOR REPLICATION ]                                 --设置CHECK约束        | DEFAULT constant_expression [ WITH VALUES ]                 ( logical_expression ) }<computed_column_definition> ::=column_name AS computed_column_expression                                --定义计算列[ PERSISTED [ NOT NULL ] ]                                                    --设置更新[     [ CONSTRAINT constraint_name ]                                           --设置约束    { PRIMARY KEY | UNIQUE }                                  --设置主键或UNIQUE约束        [ CLUSTERED | NONCLUSTERED ]                     --指定聚集索引或非聚集索引        [ WITH FILLFACTOR = fillfactor ]                                     --指定填充因子        [ WITH ( <index_option> [, ...n ] ) ]                          --指定一个或多个索引选项        [ ON { partition_scheme_name ( partition_column_name ) | filegroup             | "default" } ]                                                 --为约束创建索引    | [ FOREIGN KEY ]                                                     --设置外键约束         REFERENCES ref_table [ ( ref_column ) ]                --设置外键所引用的表及字段        [ ON DELETE { NO ACTION | CASCADE } ]                           --设置删除规则        [ ON UPDATE { NO ACTION } ]                                      --设置更新规则        [ NOT FOR REPLICATION ]                                         --设置强制复制    | CHECK [ NOT FOR REPLICATION ] ( logical_expression )             --设置CHECK约束]<index_option> ::={     PAD_INDEX = { ON | OFF }                                             --是否填充索引  | FILLFACTOR = fillfactor                                                 --设置填充因子  | IGNORE_DUP_KEY = { ON | OFF }                                 --重复键错误响应方式  | STATISTICS_NORECOMPUTE = { ON | OFF }                         --重新计算统计数据  | ALLOW_ROW_LOCKS = { ON | OFF}                                       --允许行锁定  | ALLOW_PAGE_LOCKS ={ ON | OFF}                                       --允许页锁定  | SORT_IN_TEMPDB = { ON | OFF }                  --指定是否将排序结果存储在tempdb中  | ONLINE = { ON | OFF }                                  --是否可用于查询和数据修改操作  | MAXDOP = max_degree_of_parallelism       --在索引操作期间覆盖“最大并行度”配置选项}<table_constraint> ::=[ CONSTRAINT constraint_name ] {     { PRIMARY KEY | UNIQUE }                                  --设置主键或UNIQUE约束        [ CLUSTERED | NONCLUSTERED ]                     --指定聚集索引或非聚集索引                (column [ ASC | DESC ] [ ,...n ] )  --指定加入到表约束中的一列或多列的排序顺序        [ WITH FILLFACTOR = fillfactor                                      --指定填充因子        [ WITH ( <index_option>[ , ...n ] ) ]                        --指定一个或多个索引选项        [ ON { partition_scheme_name ( partition_column_name ... )          | filegroup | "default" } ]                                      --指定索引存放的位置    | FOREIGN KEY                                                       --设置外键约束                ( column [ ,...n ] )        REFERENCES referenced_table_name [ ( ref_column [ ,...n ] ) ]     --设置外键所引用的表及字段        [ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]                                                                           --设置删除规则        [ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]                                                                           --设置更新规则        [ NOT FOR REPLICATION ]                                         --设置强制复制    | DEFAULT constant_expression FOR column [ WITH VALUES ]         --指定字段的默认值    | CHECK [ NOT FOR REPLICATION ] ( logical_expression )            --设置CHECK约束} <drop_clustered_constraint_option> ::=      {         MAXDOP = max_degree_of_parallelism      | ONLINE = {ON | OFF }      | MOVE TO { partition_scheme_name ( column_name ) | filegroup          | "default"}}6.6.3ALTER TABLE 例一表    ALTER COLUMN 姓名 nvarchar(20) COLLATE Chinese_PRC_CI_AS not nullALTER TABLE 例一表    ADD 性别 nvarchar(2)6.6.6ALTER TABLE 例一表    DROP COLUMN 密码6.6.7ALTER TABLE 例一表    ADD CONSTRAINT CK_性别 CHECK (性别=‘男‘ OR 性别=‘女‘)ALTER TABLE 例一表    WITH NOCHECK ADD         CONSTRAINT CK_性别 CHECK (性别=‘男‘ OR 性别=‘女‘)6.6.8ALTER TABLE 例一表    NOCHECK CONSTRAINT CK_性别ALTER TABLE 例一表    NOCHECK CONSTRAINT CK_1,CK_2,CK_3ALTER TABLE 例一表    NOCHECK CONSTRAINT ALL6.6.9ALTER TABLE 例一表    CHECK CONSTRAINT CK_性别ALTER TABLE 例一表    CHECK CONSTRAINT CK_1,CK_2,CK_3ALTER TABLE 例一表    CHECK CONSTRAINT ALL6.6.10ALTER TABLE 例一表    DROP CK_性别6.6.11ALTER TABLE 例一表    ALTER COLUMN 编号 int not nullGOALTER TABLE 例一表    ADD CONSTRAINT PK_主键 PRIMARY KEY (编号)GO6.6.12exec sp_rename ‘例一表.姓名‘,‘名称‘,‘COLUMN‘exec sp_rename ‘例一表‘,‘例二十三表‘sp_rename ‘原对象名称’ , ’新对象名称’ [ , ’对象类型’]6.8DROP TABLE [ database_name . [ schema_name ] . | schema_name . ] table_name [ ,...n ] [ ; ]DROP TABLE 例二表6.9USE [Northwind]GO/****** 对象:  Table [dbo].[例十_部门表]    脚本日期: 05/19/2009 13:53:12 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[例十_部门表](    [部门编号] [int] IDENTITY(1,1) NOT NULL,    [部门名称] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,PRIMARY KEY CLUSTERED (    [部门编号] ASC)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]7.3.1INSERT     [ TOP ( expression ) [ PERCENT ] ]                               ..插入记录数或百分比数    [ INTO]                                                                   ..可选参数{ <object>                                                             ..数据表或视图| rowset_function_limited                          ..OPENQUERY或OPENROWSET函数      [ WITH ( <Table_Hint_Limited> [ ...n ] ) ]           ..指定目标表所允许的一个或多个表提示    }{    [ ( column_list ) ]                                     ..要在插入数据的一列或多列的列表    [ <OUTPUT Clause> ]                              ..将插入行作为插入操作的一部分返回    { VALUES ( { DEFAULT | NULL | expression } [ ,...n ] )          ..引入要插入的数据值的列表    | derived_table                                                         ..SELECT语句    | execute_statement                                                  ..EXECUTE语句    } }     | DEFAULT VALUES                               ..强制新行包含为每个列定义的默认值[; ]7.3.2<object> ::={ [ server_name .                                                            ..服务器名      database_name .                                                        ..数据库名      schema_name .                                                            ..架构名      | database_name .[ schema_name ] .       | schema_name .     ]        table_or_view_name                                                 ..表或视图名}7.3.3INSERT 订单明细 VALUES  (10248,1,10,2,0.8)7.3.4INSERT INTO 订单明细    (折扣, 数量, 单价, 产品ID, 订单ID)VALUES (0.8, 3, 26, 2, 10248)7.3.5INSERT INTO 类别    (类别名称, 说明)VALUES (N‘图书‘,N‘所有类型的图书‘)7.3.6SET IDENTITY_INSERT 类别 ON;GOINSERT INTO 类别    (类别ID,类别名称)VALUES (100,N‘电器‘)GOSELECT * FROM 类别GO7.3.7CREATE TABLE 雇员通讯录(    雇员ID int PRIMARY KEY,    姓氏 nvarchar(20) NOT NULL,    名字 nvarchar(10) NOT NULL,    邮政编码 nvarchar(10) NULL,    城市 nvarchar(15) NULL,    地址 nvarchar(60) NULL,    家庭电话 nvarchar(24) NULL)GOINSERT INTO 雇员通讯录SELECT  雇员ID, 姓氏, 名字, 邮政编码, 城市, 地址, 家庭电话FROM  雇员GOSELECT * FROM 雇员通讯录GODELETE 雇员通讯录GOINSERT top (5) INTO 雇员通讯录SELECT  雇员ID, 姓氏, 名字, 邮政编码, 城市, 地址, 家庭电话FROM  雇员GO7.3.8DELETE 雇员通讯录GOINSERT 雇员通讯录(雇员ID, 姓氏, 名字, 邮政编码, 地址)SELECT  雇员ID, 姓氏, 名字, 邮政编码, 地址FROM  雇员GO7.3.9DELETE 雇员通讯录GOINSERT 雇员通讯录(雇员ID, 姓氏, 名字, 邮政编码, 地址, 城市)SELECT  雇员ID, 姓氏, 名字, 邮政编码, 地址, ‘北京‘FROM  雇员GO7.3.10CREATE TABLE 用户与进程信息(    编号 int PRIMARY KEY IDENTITY,    进程ID smallint,    定线程上下文ID smallint,    进程状 态nchar(30),    登录名 nchar(128),    主机名 nchar(128),    阻塞进程的系统进程ID nchar(5),    数据库名 nchar(128),        运行命令 nchar(16),    请求ID int,    查询时间 smalldatetime DEFAULT getdate())GOINSERT 用户与进程信息(进程ID,定线程上下文ID,进程状态,登录名,主机名,    阻塞进程的系统进程ID,数据库名,运行命令,请求ID)    EXEC sp_whoSELECT * FROM 用户与进程信息7.3.11INSERT INTO 用户与进程信息DEFAULT VALUES7.5.1UPDATE     [ TOP ( expression ) [ PERCENT ] ]                               ..更新记录数或百分比数{ <object>                                                   ..要更改数据的表或视图的名称| rowset_function_limited                         .. OPENQUERY或OPENROWSET函数     [ WITH ( <Table_Hint_Limited> [ ...n ] ) ]              ..指定目标表允许的一个或多个表提示    }    SET                                                ..指定要更新的列或变量名称的列表        { column_name = { expression | DEFAULT | NULL }             ..指定更改的数据的字段          | { udt_column_name.{ { property_name = expression         ..更改用户定义类型字段                                | field_name = expression }                                | method_name ( argument [ ,...n ] )                               }             }          | column_name { .WRITE ( expression , @Offset , @Length ) } ..指定更改的数据的字段          | @variable = expression                                          ..已声明的变量          | @variable = column = expression [ ,...n ]         } [ ,...n ]     [ <OUTPUT Clause> ]                    ..返回更新后的数据或基于更新后的数据的表达式    [ FROM{ <table_source> } [ ,...n ] ]      ..指定将表、视图或派生表源用于为更新操作提供条件    [ WHERE { <search_condition>                              ..指定条件来限定所更新的行            | { [ CURRENT OF                         ..指定更新在指定游标的当前位置进行                  { { [ GLOBAL ] cursor_name }           ..指定cursor_name涉及到全局游标                      | cursor_variable_name             ..要从中进行提取的开放游标的名称                  }                 ]              }            }     ]     [ OPTION ( <query_hint> [ ,...n ] ) ]   ..指定优化器提示用于自定义数据库引擎处理语句的方式[ ; ]7.5.2<object> ::={     [ server_name . database_name . schema_name .     | database_name .[ schema_name ] .     | schema_name .    ]        table_or_view_name}7.5.3UPDATE    类别SET  说明 = N‘家用电器‘WHERE   (类别名称 = N‘电器‘)7.5.4UPDATE 产品    SET 单价 = 单价*1.5    WHERE 产品名称 = N‘牛奶‘7.5.5UPDATE 订单明细    SET 单价 = 产品.单价    FROM 产品    WHERE (订单明细.产品ID = 产品.产品ID)         AND (产品.产品名称 = N‘牛奶‘)7.5.6UPDATE top (10) PERCENT 订单明细    SET 单价= 产品.单价    FROM 产品    WHERE (订单明细.产品ID = 产品.产品ID)         AND (产品.产品名称= N‘牛奶‘)7.7.1DELETE     [ TOP ( expression ) [ PERCENT ] ]                                       ..要删除的行数    [ FROM ]     { <object> | rowset_function_limited                        ..openquery或openowset函数      [ WITH ( <table_hint_limited> [ ...n ] ) ]                          ..指定一个或多个表提示    }    [ <OUTPUT Clause> ]                                    ..将已删除的行或行表达式返回    [ FROM <table_source> [ ,...n ] ]     [ WHERE { <search_condition>                                          ..删除行的条件            | { [ CURRENT OF                                         ..删除游标的当前行                   { { [ GLOBAL ] cursor_name }                                   ..游标名                       | cursor_variable_name                                ..游标变量名                   }                 ]              }            }     ]     [ OPTION ( <Query Hint> [ ,...n ] ) ]                                     ..指定优化器提示[; ] 7.7.2<object> ::={     [ server_name . database_name . schema_name .     | database_name .[ schema_name ] .     | schema_name .    ]        table_or_view_name}7.7.3DELETE 类别    WHERE 类别名称= N‘电器‘7.7.4DELETE 订单    WHERE 订购日期 < ‘1996.8.1‘ 7.7.5DELETE 订单    FROM 雇员    WHERE (雇员.雇员ID = 订单.雇员ID)        AND (雇员.姓氏 = N‘王‘) AND (雇员.名字 = N‘伟‘)7.8TRUNCATE TABLE     [ { database_name.[ schema_name ]. | schema_name . } ]    table_name[ ; ] TRUNCATE TABLE 订单明细7.9SELECT [ ALL | DISTINCT ]     [TOP expression [PERCENT] [ WITH TIES ] ]     < select_list >     [ INTO new_table ]     [ FROM { <table_source> } [ ,...n ] ]     [ WHERE <search_condition> ]     [ GROUP BY [ ALL ] group_by_expression [ ,...n ]     [ WITH { CUBE | ROLLUP } ]    ][ HAVING < search_condition > ]7.10.1SELECT [ ALL                                                                   ..所有行      | DISTINCT ]                                                             ..唯一行[ TOP expression [ PERCENT ] [ WITH TIES ] ]                          ..返回结果集的头几行<select_list> <select_list> ::=     {       *                                                                         ..所有列      | { table_name | view_name | table_alias }.*                  ..指定列及列所在的表或视图      | { column_name                                                       ..返回的列名        | [ ] expression                                                     ..返回表达式列        | $IDENTITY                                                         ..返回标识列        | $ROWGUID }                                                     ..返回GUID列      | udt_column_name                                                 ..返回CLR列名         [ { . | :: }                                           ..指定CLR的方法、属性或字段            { { property_name                                                  ..公共属性                | field_name }                                              ..公共数据成员            | method_name(argument [,...n] ) } ]                                   ..公共方法      [ [ AS ] column_alias ]       | column_alias = expression                                               ..替换列名    } [ ,...n ]7.10.3SELECT *FROM 类别7.10.4SELECT 类别名称,说明FROM 类别SELECT 类别.类别名称, 类别.说明FROM 类别7.10.5SELECT  订单ID, 产品ID, 单价, 数量, 折扣, 单价 * (1 . 折扣) * 数量 AS 总价FROM  订单明细SELECT  *, 单价 * (1 . 折扣) * 数量 AS 总价FROM  订单明细SELECT  订单ID as 订单编号, 产品ID as 产品编号,     单价, 数量, 折扣, 单价 * (1 . 折扣) * 数量 AS 总价FROM  订单明细7.10.6SELECT  TOP  10  *    FROM  订单明细    ORDER BY 数量 DESCSELECT  top 10  PERCENT  *    FROM  订单明细    ORDER BY 数量 DESCSELECT  TOP  10  WITH TIES  *    FROM  订单明细    ORDER BY 数量 DESC7.10.7SELECT   货主城市    FROM  订单SELECT  ALL  货主城市    FROM  订单SELECT   DISTINCT 货主城市    FROM  订单SELECT   DISTINCT 货主名称,货主城市    FROM  订单7.10.8SELECT  $IDENTITY    FROM  类别SELECT  $ROWGUID    FROM  类别7.11.1[ FROM { <table_source> } [ ,...n ] ]   <table_source> ::= {    table_or_view_name                                                     ..表或视图名     [ [ AS ] table_alias ]                                                    ..表或视图别名        [ WITH ( < table_hint > [ [ , ]...n ] ) ]                                  ..指定查询优化器    | rowset_function [ [ AS ] table_alias ]                                     ..指定行集函数        [ ( bulk_column_alias [ ,...n ] ) ]                                 ..替代结果集内的列名    | user_defined_function [ [ AS ] table_alias ]                                ..指定表值函数    | OPENXML <openxml_clause>                                         ..通过XML查询    | derived_table [ AS ] table_alias [ ( column_alias [ ,...n ] ) ]                         ..子查询    | <joined_table> }<joined_table> ::=                                                          ..多表联合查询{<table_source>         <join_type>                                                           ..联合类型    <table_source>         ON <search_condition>                                                 ..联合条件    | <table_source> CROSS JOIN <table_source>     | left_table_source { CROSS | OUTER } APPLY right_table_source     | [ ( ] <joined_table> [ ) ] }<join_type> ::=     [ { INNER | { { LEFT | RIGHT | FULL } [ OUTER ] } } [ <join_hint> ] ]    JOIN7.11.3SELECT  *FROM    雇员SELECT 类别ID,类别名称    FROM 类别SELECT 产品ID,产品名称,类别ID    FROM 产品SELECT 产品ID,产品名称,类别名称FROM 产品 JOIN 类别    ON 产品.类别ID = 类别.类别IDSELECT 产品ID,产品名称,类别名称,类别IDFROM 产品 JOIN 类别    ON 产品.类别ID = 类别.类别IDSELECT 产品ID,产品名称,类别名称,类别.类别IDFROM 产品 JOIN 类别    ON 产品.类别ID = 类别.类别IDSELECT 产品ID,产品名称,类别名称,类别.类别IDFROM 产品, 类别    WHERE 产品.类别ID = 类别.类别ID7.11.5SELECT  产品.产品名称, 订单明细.单价, 订单明细.数量,     订单明细.折扣, 订单.订购日期FROM   订单明细 JOIN    订单 ON 订单明细.订单ID = 订单.订单ID JOIN    产品 ON 订单明细.产品ID = 产品.产品IDSELECT  产品.产品名称, 订单明细.单价, 订单明细.数量,     订单明细.折扣, 订单.订购日期FROM   (订单明细 JOIN 订单 ON 订单明细.订单ID = 订单.订单ID ) JOIN 产品 ON 订单明细.产品ID = 产品.产品ID7.11.6SELECT  *FROM 库存信息 INNER JOIN    订单信息 ON 库存信息.产品名称 = 订单信息.产品名称SELECT  *FROM 库存信息 LEFT OUTER JOIN    订单信息 ON 库存信息.产品名称 = 订单信息.产品名称SELECT *FROM 库存信息 RIGHT OUTER JOIN    订单信息 ON 库存信息.产品名称 = 订单信息.产品名称SELECT  *FROM 库存信息 FULL OUTER JOIN    订单信息 ON 库存信息.产品名称 = 订单信息.产品名称SELECT  *FROM 库存信息 CROSS  JOIN 订单信息7.11.7SELECT 细.单价, 细.数量, 细.折扣,     细.单价* (1 . 细.折扣) * 细.数量 AS 总价,    订.订购日期FROM 订单 AS 订 INNER JOIN    订单明细 AS 细 ON 订.订单ID = 细.订单ID7.11.8SELECT   雇员.雇员ID, 雇员.姓氏, 雇员.名字, 雇员.职务,     主管.姓氏 AS 主管姓氏, 主管.名字 AS 主管名字,     主管.职务 AS 主管职务FROM  雇员 LEFT OUTER JOIN    雇员 AS 主管 ON 雇员.上级 = 主管.雇员ID7.12.1[ WHERE <search_condition> ]< search_condition > ::=     { [ NOT ] <predicate> | ( <search_condition> ) }     [ { AND | OR } [ NOT ] { <predicate> | ( <search_condition> ) } ] [ ,...n ] <predicate> ::=     { expression { = | < > | ! = | > | > = | ! > | < | < = | ! < } expression     | string_expression [ NOT ] LIKE string_expression   [ ESCAPE ‘escape_character‘ ]     | expression [ NOT ] BETWEEN expression AND expression     | expression IS [ NOT ] NULL     | CONTAINS     ( { column | * } , ‘< contains_search_condition >‘ )     | FREETEXT ( { column | * } , ‘freetext_string‘ )     | expression [ NOT ] IN ( subquery | expression [ ,...n ] )     | expression { = | < > | ! = | > | > = | ! > | < | < = | ! < }   { ALL | SOME | ANY} ( subquery ) | EXISTS ( subquery )     }7.12.3SELECT * FROM 产品    WHERE 库存量 = 0SELECT * FROM 产品    WHERE 库存量 <> 0SELECT * FROM 产品7.12.4SELECT * FROM 产品    WHERE 库存量 = 0 AND 类别ID = 2SELECT 产品.*     FROM 产品 JOIN 类别    ON 产品.类别ID = 类别.类别ID    WHERE 产品.库存量 = 0 AND 类别.类别名称 = N‘调味品‘SELECT 产品.*     FROM 产品 JOIN 类别    ON 产品.类别ID = 类别.类别ID    WHERE 产品.库存量 = 0  AND         (类别.类别名称 = N‘调味品‘  OR 类别.类别名称 = N‘日用品‘)7.12.5SELECT * FROM 雇员    WHERE 雇用日期< CONVERT(DATETIME, ‘1993.1.1‘, 102)SELECT * FROM 雇员    WHERE 雇用日期< ‘1993.1.1‘SELECT *FROM 雇员WHERE Year(Getdate()).Year(雇用日期) >137.12.6SELECT  *FROM 雇员WHERE 雇用日期 BETWEEN  CONVERT(DATETIME, ‘1993.01.01‘, 102)     AND CONVERT(DATETIME, ‘1994.12.31‘, 102)7.12.7SELECT  *    FROM 雇员    WHERE  上级 IS NULL7.12.8SELECT  *    FROM 雇员    WHERE  雇员ID = 1 OR 雇员ID = 3 OR 雇员ID = 4         OR 雇员ID = 7 OR 雇员ID = 9SELECT *     FROM 雇员    WHERE 雇员ID IN (1,3,4,7,9)SELECT *     FROM 雇员    WHERE 雇员ID NOT IN (1,3,4,7,9)SELECT *     FROM 订单明细    WHERE 产品ID IN        (SELECT 产品ID             FROM 产品 JOIN 类别            ON 产品.类别ID  = 类别.类别ID            WHERE 类别.类别名称 = N‘日用品‘)7.12.9SELECT *     FROM 产品    WHERE 产品名称 LIKE ‘%奶%‘SELECT *     FROM 产品    WHERE 产品名称 LIKE ‘%奶酪‘SELECT *     FROM 产品    WHERE 产品名称 LIKE ‘_奶酪‘SELECT *     FROM 产品    WHERE 产品名称 LIKE ‘%油‘GOSELECT *     FROM 产品    WHERE 产品名称 LIKE ‘[麻酱]油‘GOSELECT *     FROM 产品    WHERE 产品名称 LIKE ‘[^麻酱]油‘GO7.12.10SELECT *     FROM 类别    WHERE 说明 LIKE ‘%[_]%‘7.12.11SELECT *     FROM 订单    WHERE EXISTS (    SELECT *    FROM 雇员    WHERE Year(Getdate()).Year(雇用日期) <13        AND 订单.雇员ID = 雇员.雇员ID)SELECT *     FROM 订单 JOIN 雇员        ON 订单.雇员ID  = 雇员.雇员ID    WHERE Year(Getdate()).Year(雇员.雇用日期) <137.12.12SELECT *     FROM 产品    WHERE 类别ID = ANY     (        SELECT 类别ID        FROM 类别        WHERE  类别名称= N‘日用品‘  OR  类别名称= N‘点心‘    )SELECT *     FROM 产品    WHERE 类别ID in     (        SELECT 类别ID        FROM 类别        WHERE  类别名称= N‘日用品‘  OR  类别名称= N‘点心‘    )或者SELECT *     FROM 产品 JOIN 类别        ON 产品.类别ID = 类别.类别ID    WHERE 类别.类别名称= N‘日用品‘  OR  类别.类别名称= N‘点心‘SELECT *     FROM 产品    WHERE 单价> ALL    (        SELECT 单价        FROM 产品 JOIN 类别            ON 产品.类别ID = 类别.类别ID        WHERE  类别名称= N‘日用品‘    )SELECT *     FROM 产品    WHERE 单价>    (        SELECT max(单价)        FROM 产品 JOIN 类别            ON 产品.类别ID = 类别.类别ID        WHERE  类别名称= N‘日用品‘    )7.13.1[ ORDER BY     {    order_by_expression                                                     ..要排序的列  [ COLLATE collation_name ]                                                   ..排序规则  [ ASC | DESC ]                                                            ..升序或降序    } [ ,...n ] ]7.13.3SELECT * FROM 产品    ORDER BY 产品名称SELECT * FROM 产品    ORDER BY 产品名称 DESC7.13.4SELECT * FROM 产品    ORDER BY 供应商ID,产品名称SELECT * FROM 产品    ORDER BY 供应商ID ASC,产品名称 DESC7.14.1[ GROUP BY [ ALL ] group_by_expression [ ,...n ]     [ WITH { CUBE | ROLLUP } ] ]7.14.3SELECT 货主城市,count(订单ID) AS 订单总数    FROM 订单    GROUP BY 货主城市7.14.4SELECT 货主城市,count(订单ID) AS 订单总数,YEAR(订购日期) as 订购年份    FROM 订单    GROUP BY 货主城市,YEAR(订购日期)    ORDER BY 货主城市,YEAR(订购日期)7.14.5SELECT 货主城市,count(订单ID) AS 订单总数    FROM 订单    GROUP BY 货主城市 WITH CUBESELECT 货主城市,YEAR(订购日期) as 订购年份,count(订单ID) AS 订单总数    FROM 订单    GROUP BY 货主城市,YEAR(订购日期)        WITH CUBE7.14.6SELECT 货主城市,YEAR(订购日期) as 订购年份,count(订单ID) AS 订单总数    FROM 订单    GROUP BY 货主城市,YEAR(订购日期)        WITH ROLLUP7.14.7SELECT 货主城市,count(订单ID) AS 订单总数,sum(运货费) AS 运货费总数    FROM 订单    WHERE 订购日期> ‘1998.5.1‘    GROUP BY 货主城市    SELECT 货主城市,count(订单ID) AS 订单总数,sum(运货费) AS 运货费总数    FROM 订单    WHERE 订购日期> ‘1998.5.1‘    GROUP BY ALL 货主城市    7.15.1[ HAVING <search condition> ]< search_condition > ::=     { [ NOT ] <predicate> | ( <search_condition> ) }     [ { AND | OR } [ NOT ] { <predicate> | ( <search_condition> ) } ] [ ,...n ] <predicate> ::=     { expression { = | < > | ! = | > | > = | ! > | < | < = | ! < } expression     | string_expression [ NOT ] LIKE string_expression   [ ESCAPE ‘escape_character‘ ]     | expression [ NOT ] BETWEEN expression AND expression     | expression IS [ NOT ] NULL     | CONTAINS     ( { column | * } , ‘< contains_search_condition >‘ )     | FREETEXT ( { column | * } , ‘freetext_string‘ )     | expression [ NOT ] IN ( subquery | expression [ ,...n ] )     | expression { = | < > | ! = | > | > = | ! > | < | < = | ! < }   { ALL | SOME | ANY} ( subquery )     | EXISTS ( subquery )     }7.15.2SELECT 货主城市,count(订单ID) AS 订单总数,sum(运货费) AS 运货费总数    FROM 订单    WHERE count(订单ID) > 20    GROUP BY 货主城市    SELECT 货主城市,count(订单ID) AS 订单总数,sum(运货费) AS 运货费总数    FROM 订单    GROUP BY 货主城市        HAVING count(订单ID) > 207.16.1[ COMPUTE     { { AVG | COUNT | MAX | MIN | STDEV | STDEVP | VAR | VARP | SUM }     ( expression ) } [ ,...n ]     [ BY expression [ ,...n ] ] ]7.16.3SELECT 订单ID,货主城市,运货费    FROM 订单    WHERE     发货日期 is null    COMPUTE SUM(运货费)7.16.4SELECT 订单ID,货主城市,运货费    FROM 订单    WHERE     发货日期 is null    ORDER BY 货主城市 desc    COMPUTE SUM(运货费)  by 货主城市7.17.1    { <query specification> | ( <query expression> ) }   UNION [ ALL ]   <query specification | ( <query expression> )  [ UNION [ ALL ] <query specification> | ( <query expression> )     [ ...n ] ]7.17.3SELECT 联系人姓名,地址,电话    FROM 供应商UNION ALLSELECT 联系人姓名,地址,电话    FROM 客户7.17.4SELECT 联系人姓名,地址,电话    FROM 供应商UNION SELECT 联系人姓名,地址,电话    FROM 客户7.17.5SELECT 联系人姓名,地址,电话    FROM 供应商UNION SELECT 联系人姓名,地址,电话    FROM 客户UNIONSELECT ‘张三‘,‘北京中医药大学‘,‘010.12345678‘7.17.6SELECT 联系人姓名,地址,电话    FROM 供应商UNION SELECT 联系人姓名,地址,电话    FROM 客户ORDER BY 联系人姓名7.17.7SELECT 地址,COUNT(地址) AS 联系人数FROM (    SELECT 联系人姓名,地址,电话        FROM 供应商    UNION     SELECT 联系人姓名,地址,电话        FROM 客户) AS 临时表GROUP BY 地址7.18.1SELECT < select_list >     [ INTO new_table ]     [ FROM { <table_source> } [ ,...n ] ]     [ WHERE <search_condition> ] 7.18.2SELECT 产品ID,产品名称    INTO 缺货记录    FROM dbo.产品    WHERE 库存量= 07.18.3SELECT 雇员.雇员ID, 雇员.姓氏, 雇员.名字,     产品.产品名称 as 售出产品, 订单明细.单价, 订单明细.数量,     订单明细.折扣, 订单明细.单价*订单明细.数量*(1.订单明细.折扣) as 总价,    客户.公司名称, 客户.联系人姓名, 客户.地址, 客户.邮政编码,     客户.电话INTO 雇员订单信息FROM  订单 INNER JOIN      订单明细 ON 订单.订单ID = 订单明细.订单ID INNER JOIN      雇员 ON 订单.雇员ID = 雇员.雇员ID INNER JOIN      产品 ON 订单明细.产品ID = 产品.产品ID INNER JOIN      客户 ON 订单.客户ID = 客户.客户ID7.18.4SELECT *     INTO 订单历史记录    FROM  订单    WHERE 0=17.19SELECT * FROM Northwind.dbo.雇员    JOIN test.dbo.订单    ON Northwind.dbo.雇员.雇员ID = test.dbo.订单.雇员IDSELECT * FROM Northwind.dbo.雇员    JOIN test.dbo.订单    ON 雇员.雇员ID = 订单.雇员IDSELECT * FROM Northwind.dbo.雇员 as N雇员    JOIN test.dbo.订单 as 例订单    ON N雇员.雇员ID = 例订单.雇员IDuse NorthwindSELECT * FROM 雇员    JOIN test.dbo.订单    ON 雇员.雇员ID = test.dbo.订单.雇员ID7.20.2SELECT * FROM 类别    WHERE 图片 IS NULLSELECT * FROM 类别    WHERE 图片 IS NOT NULL7.20.3ISNULL ( check_expression , replacement_value )SELECT 类别ID,类别名称,isnull(说明,‘暂无说明‘) as 说明    FROM 类别7.21.1WITH 临时表(雇员ID,上级ID,订单数) AS(    SELECT 雇员.雇员ID,雇员.上级,count(订单.订单ID) FROM 订单        JOIN 雇员 ON 订单.雇员ID = 雇员.雇员ID    GROUP BY 雇员.雇员ID,雇员.上级)SELECT 雇员.姓氏,雇员.名字,sum(订单数) as 订单数FROM 临时表    JOIN 雇员 ON 临时表.上级ID = 雇员.雇员ID    GROUP BY 雇员.姓氏,雇员.名字7.21.2DELETE 雇员通讯录GOWITH 临时表(雇员ID,姓氏,名字,邮政编码,城市,地址,家庭电话) AS(    SELECT  雇员ID, 姓氏, 名字, 邮政编码, 城市, 地址, 家庭电话    FROM  雇员    WHERE  雇员ID IN    (            SELECT 雇员ID FROM 订单        GROUP BY 雇员ID        HAVING COUNT(订单ID) >100    ))INSERT INTO  雇员通讯录    SELECT 雇员ID,姓氏,名字,邮政编码,城市,地址,家庭电话    FROM 临时表DELETE 雇员通讯录INSERT top (5) INTO  雇员通讯录    SELECT  雇员ID, 姓氏, 名字, 邮政编码, 城市, 地址, 家庭电话    FROM  雇员?    OUTPUT子句：使用OUTPUT子句可以返回插入到数据表里的记录。DELETE 雇员通讯录GOINSERT top (5) INTO 雇员通讯录    OUTPUT INSERTED.雇员ID, INSERTED.姓氏, INSERTED.名字, INSERTED.邮政编码,        INSERTED.城市, INSERTED.地址, INSERTED.家庭电话    SELECT  雇员ID, 姓氏, 名字, 邮政编码, 城市, 地址, 家庭电话        FROM  雇员7.21.3.WRITE ( expression, @Offset , @Length )ALTER TABLE 雇员通讯录    ALTER COLUMN 地址nvarchar(max)GOSELECT * FROM 雇员通讯录    WHERE 雇员ID = 4UPDATE 雇员通讯录    SET 地址.WRITE (N‘试一下‘,1,1)    WHERE 雇员ID = 4SELECT * FROM 雇员通讯录    WHERE 雇员ID = 48.3.1INSERT 类别(类别名称,说明)     VALUES (‘图书‘,‘各种图书‘)SELECT * FROM 类别    WHERE 类别名称 = N‘图书‘UPDATE 类别    SET 说明 = N‘计算机、时尚生活等图书‘    WHERE 类别名称 = N‘图书‘SELECT * FROM 类别    WHERE 类别名称 = N‘图书‘8.3.2SELECT * FROM 类别    WHERE 类别名称 = N‘图书‘UPDATE 类别    SET 说明 = N‘计算机、时尚生活等图书‘    WHERE 类别名称 = N‘图书‘GOSELECT * FROM 类别    WHERE 类别名称 = N‘图书‘GO8.4.1--先插入一条记录INSERT 类别(类别名称,说明)     VALUES (‘图书‘,‘各种图书‘)--查看插入记录的内容SELECT * FROM 类别    WHERE 类别名称 = N‘图书‘--更新记录内容--将“说明”字段内容改为“计算机、时尚生活等图书”UPDATE 类别    SET 说明 = N‘计算机、时尚生活等图书‘    WHERE 类别名称 = N‘图书‘--查看更新后的记录内容SELECT * FROM 类别    WHERE 类别名称 = N‘图书‘8.4.2/*下面代码可以完成以下操作：1、查看类别表中类别名称为“图书”的记录内容2、将类别表中类别名称为“图书”的记录的说明字段的内容改为“计算机、时尚生活等图书”3、查看修改后的结果*/SELECT * FROM 类别    WHERE 类别名称= N‘图书‘UPDATE 类别    SET 说明= N‘计算机、时尚生活等图书‘    WHERE 类别名称= N‘图书‘SELECT * FROM 类别    WHERE 类别名称= N‘图书‘8.5.1CAST ( expression AS data_type [ (length ) ])SELECT 产品名称+ ‘的单价为：‘ + CAST(单价 AS VARCHAR(10)) + ‘元‘    AS 产品介绍    FROM 产品8.5.2CONVERT ( data_type [ ( length ) ] , expression [ , style ] )SELECT 订单ID,    CONVERT(varchar(20),订购日期,1) AS 订购日期,    CONVERT(varchar(20),到货日期,102) AS 到货日期,    CONVERT(varchar(20),发货日期,103) AS 发货日期    FROM 订单8.5.3SELECT 产品名称,单价*库存量 AS 积压资金    FROM 产品    WHERE 单价*库存量>08.8.11SELECT 产品名称,单价+$10 AS 价格    FROM 产品SELECT *    FROM 产品    WHERE 单价>$10?    为变量赋值，例如：DECLARE @abc intSET @abc = 123UPDATE 类别 SET 说明= N‘精彩图书‘    WHERE 类别名称= N‘图书‘INSERT 类别 (类别名称)  VALUES (N‘手提电脑等‘)PRINT ‘完成操作’IF @@ERROR >0    PRINT N‘出错了’8.9.1DECLARE      { @local_variable [AS] data_type }       [ ,...n]SET @local_variable = valueSELECT @local_variable = valueSELECT @local_variablePRINT @local_variableDECLARE @name varchar(20)DECLARE @age int,@sex bitSET @name = ‘张三‘SET @age = 20SELECT @sex = 1SELECT @nameSELECT @ageSELECT @sexPRINT @namePRINT @agePRINT @sexDECLARE @name varchar(20)DECLARE @birthday datetimeSELECT @name = 姓氏+名字,@birthday=出生日期    FROM 雇员    WHERE 雇员ID = 1PRINT ‘雇员姓名：‘+@namePRINT ‘雇员生日：‘+CONVERT(varchar(50),@birthday,102)DECLARE @name varchar(20)DECLARE @birthday datetimeset @name = ‘未知‘SELECT @name = 姓氏+名字,@birthday=出生日期    FROM 雇员    WHERE 雇员ID = 1000PRINT ‘雇员姓名：‘+@namePRINT ‘雇员生日：‘+CONVERT(varchar(50),@birthday,102)DECLARE @name varchar(20)DECLARE @birthday datetimeSELECT @name = 姓氏+名字,@birthday=出生日期    FROM 雇员    WHERE 雇员ID = 1PRINT ‘雇员姓名：‘+@namePRINT ‘雇员生日：‘+CONVERT(varchar(50),@birthday,102)GOPRINT ‘雇员姓名：‘+@namePRINT ‘雇员生日：‘+CONVERT(varchar(50),@birthday,102)8.9.2SELECT * FROM 雇员PRINT ‘一共查询了‘+CAST(@@ROWCOUNT AS varchar(5))+‘条记录‘SELECT ‘SQL Server 2008启动以来尝试的连接数：‘+    CAST(@@CONNECTIONS AS varchar(10))8.10.1BEGIN     {         sql_statement | statement_block      } ENDUSE NorthwindDECLARE @price moneyDECLARE @productid intDECLARE @count intSELECT @price = 单价, @productid = 产品ID     FROM 产品    WHERE 产品名称= N‘蕃茄酱‘IF @price<$20    BEGIN        PRINT ‘蕃茄酱的单价低于20元‘        SELECT @count = sum(订单明细.数量)            FROM 订单 JOIN 订单明细            ON 订单.订单ID = 订单明细.订单ID            WHERE 订单明细.产品ID = @productid        PRINT ‘其订购量为：‘ + CAST(@count AS varchar(5))    ENDUSE NorthwindDECLARE @price moneyDECLARE @productid intSELECT @price = 单价, @productid = 产品ID     FROM 产品    WHERE 产品名称= N‘蕃茄酱‘IF @price<$20    SELECT sum(订单明细.数量)        FROM 订单 JOIN 订单明细        ON 订单.订单ID = 订单明细.订单ID        WHERE 订单明细.产品ID = @productid8.10.2IF Boolean_expression      { sql_statement | statement_block } [ ELSE      { sql_statement | statement_block } ]USE NorthwindDECLARE @price moneyDECLARE @productid intDECLARE @count intSELECT @price = 单价, @productid = 产品ID     FROM 产品    WHERE 产品名称= N‘蕃茄酱‘IF @price<$20    BEGIN        PRINT ‘蕃茄酱的单价低于20元‘        SELECT @count = sum(订单明细.数量)            FROM 订单 JOIN 订单明细            ON 订单.订单ID = 订单明细.订单ID            WHERE 订单明细.产品ID = @productid        PRINT ‘其订购量为：‘ + CAST(@count AS varchar(5))    ENDELSE    BEGIN        PRINT ‘蕃茄酱的单价高于20元‘        SELECT @count = sum(库存量)            FROM 产品            WHERE 产品ID = @productid        PRINT ‘其库存量为：‘ + CAST(@count AS varchar(5))    ENDUSE NorthwindDECLARE @price moneySELECT @price = 单价     FROM 产品    WHERE 产品名称= N‘蕃茄酱‘IF @price<$20        PRINT ‘蕃茄酱的单价低于20元‘ELSE    BEGIN        IF $20<=@price and @price<=40            PRINT ‘蕃茄酱的单价在20元与40元之间‘        ELSE            PRINT ‘蕃茄酱的单价大于40元‘    END8.10.3WHILE Boolean_expression      { sql_statement | statement_block }      [ BREAK ]      { sql_statement | statement_block }      [ CONTINUE ]      { sql_statement | statement_block } USE NorthwindDECLARE @id intDECLARE @productname varchar(40)SET @id = 1WHILE @id<10    BEGIN        SELECT @productname = 产品名称 FROM 产品            WHERE 产品ID = @id        PRINT @productname        SET @id = @id+1    ENDUSE NorthwindDECLARE @id intDECLARE @productname varchar(40)DECLARE @maxid intSELECT @maxid = MAX(产品ID) FROM dbo.产品             --查看产品表里最大的编号是多少SET @id = 0WHILE @id<100    BEGIN        SET @id = @id+1                                                  --编号自加一        IF @id % 2 = 1            PRINT ‘***********‘                        --如果编号为奇数则准备输出产品名称        ELSE            CONTINUE   --如果编号为偶数则不执行后面的代码，直接跳回while语句进行判断        SELECT @productname = 产品名称            FROM 产品            WHERE 产品ID = @id        IF @@ROWCOUNT = 1                       --判断select查询出来的行数是为为1            PRINT @productname                              --如果为1则输出产品名称        ELSE            BEGIN                 IF @id > @maxid    --如果不为1则判断产品编号是否超过产品表中最大编号                    BREAK                  --如果超过产品表中最大编号则跳出整个循环                ELSE                    PRINT ‘没有产品ID号为“‘+CAST(@id AS varchar(5))+‘”的记录‘            END            END8.10.4USE NorthwindDECLARE @price moneySELECT @price = 单价    FROM 产品    WHERE 产品名称= N‘蕃茄酱‘IF @price<$20        PRINT ‘蕃茄酱的单价低于20元‘ELSE    BEGIN        IF $20<=@price and @price<40            PRINT ‘蕃茄酱的单价在20元与40元之间‘        ELSE            BEGIN                IF $40<=@price and @price<=80                    PRINT ‘蕃茄酱的单价在40元与80元之间‘                ELSE                    PRINT ‘蕃茄酱的单价大于80元‘            END    ENDCASE input_expression      WHEN when_expression THEN result_expression     [ ...n ]      [     ELSE else_result_expression      ] END 搜索的case语法代码：CASE     WHEN Boolean_expression THEN result_expression     [ ...n ]      [     ELSE else_result_expression      ] ENDUSE NorthwindDECLARE @categoryname varchar(15)DECLARE @outstr varchar(100)SELECT @categoryname = 类别名称    FROM 类别    WHERE  类别ID = 2SET @outstr = CASE @categoryname    WHEN ‘饮料‘ THEN ‘类别：饮料‘    WHEN ‘调味品‘ THEN ‘类别：调味品‘    WHEN ‘点心‘ THEN ‘类别：点心‘    WHEN ‘日用品‘ THEN ‘类别：日用品‘    WHEN ‘特制品‘ THEN ‘类别：特制品‘    WHEN ‘海鲜‘ THEN ‘类别：海鲜‘    ELSE ‘其他类别‘ENDPRINT @outstrUSE NorthwindSELECT 产品名称,CASE 类别ID         WHEN 1 THEN ‘饮料‘        WHEN 2 THEN ‘调味品‘        WHEN 3 THEN ‘点心‘        WHEN 4 THEN ‘日用品‘        WHEN 5 THEN ‘谷类/麦片‘        WHEN 6 THEN ‘肉/家禽‘        WHEN 7 THEN ‘特制品‘        WHEN 8 THEN ‘海鲜‘        ELSE ‘其他类‘        END AS 类别    FROM 产品USE NorthwindDECLARE @price moneyDECLARE @returnstr varchar(50)SELECT @price = 单价    FROM 产品    WHERE 产品名称= N‘蕃茄酱‘SET @returnstr = CASE    WHEN @price<$20 THEN ‘蕃茄酱的单价低于20元‘    WHEN $20<=@price and @price<40 THEN ‘蕃茄酱的单价在20元与40元之间‘    WHEN $40<=@price and @price<=80 THEN ‘蕃茄酱的单价在40元与80元之间‘    ELSE ‘蕃茄酱的单价大于80元‘    ENDPRINT @returnstr8.10.5label:GOTO labelUSE NorthwindDECLARE @price moneyDECLARE @returnstr varchar(50)SELECT @price = 单价    FROM 产品    WHERE 产品名称= N‘蕃茄酱‘IF @price<$20    GOTO print20                                                    --跳转到标签print20IF $20<=@price and @price<40    GOTO print40                                                    --跳转到标签print40IF $40<=@price and @price<=80    GOTO print80                                                    --跳转到标签print80GOTO other                                                            --跳转到标签otherprint20:    PRINT ‘蕃茄酱的单价低于20元‘    GOTO theEnd                                                    --跳转到标签theEndprint40:    PRINT ‘蕃茄酱的单价在20元与40元之间‘    GOTO theEnd                                                    --跳转到标签theEndprint80:    PRINT ‘蕃茄酱的单价在40元与80元之间‘    GOTO theEnd                                                    --跳转到标签theEndother:    PRINT ‘蕃茄酱的单价大于80元‘theEnd:8.10.6WAITFOR {    DELAY ‘time_to_pass‘   | TIME ‘time_to_execute‘ }USE NorthwindSELECT  单价 FROM 产品 WHERE 产品名称= N‘蕃茄酱‘GOWAITFOR DELAY ‘00:00:10‘SELECT  单价 FROM 产品 WHERE 产品名称= N‘蕃茄酱‘GOUSE NorthwindSELECT  单价 FROM 产品 WHERE 产品名称= N‘蕃茄酱‘GOWAITFOR TIME ‘15:57:10‘SELECT  单价 FROM 产品 WHERE 产品名称= N‘蕃茄酱‘GO8.10.7RETURN [ integer_expression ]8.10.8BEGIN TRY     { sql_statement | statement_block }END TRYBEGIN CATCH     { sql_statement | statement_block }END CATCH USE NorthwindBEGIN TRY    DELETE 类别 WHERE 类别ID=5END TRYBEGIN CATCH    PRINT ‘出错信息为：‘ + ERROR_MESSAGE()    DELETE 产品 WHERE 类别ID=5    DELETE 类别 WHERE 类别ID=5END CATCH8.10.9[ { EXEC | EXECUTE } ]    {       [ @return_status = ]                                            --存储过程的返回状态      { module_name [ ;number ] | @module_name_var }         [ [ @parameter = ]                                                          --参数        { value                                                                  --参数值                           | @variable [ OUTPUT ]                            --返回型参数                           | [ DEFAULT ]                                         --缺省值                           }        ]      [ ,...n ]      [ WITH RECOMPILE ]                       --执行模块后，强制编译、使用和放弃新计划    }[;]2、运行字符串的语法代码：{ EXEC | EXECUTE }         ( { @string_variable                                                  --字符串变量        | [ N ]‘tsql_string‘ } [ + ...n ] )                                            --字符串常量    [ AS { LOGIN | USER } = ‘ name ‘ ]                                --要模拟的上下文登录名[;]3、向链接服务器发送传递命令的语法代码：{ EXEC | EXECUTE }        ( { @string_variable                                                  --字符串变量        | [ N ] ‘command_string‘ } [ + ...n ]                                       --字符串常量        [ {, { value | @variable [ OUTPUT ] } } [...n] ]        )     [ AS { LOGIN | USER } = ‘ name ‘ ]                                --要模拟的上下文登录名    [ AT linked_server_name ]                                               --链接服务器名[;]例十八、查看当前数据库中所有数据表和视图，其代码如下：sp_tables或EXEC sp_tables或EXECUTE sp_tablesUSE NorthwindDECLARE @execstr varchar(1000)DECLARE @year intSET @year = 2000WHILE @year>1990    BEGIN        set @execstr = ‘SELECT * FROM 订单 WHERE YEAR(订购日期)=‘            +CAST(@year AS varchar(4))                     --将查询语句放在一个变量中        EXEC (@execstr)                                         --执行变量中的查询语句--当该年的订单数不为零时将查询出来的记录插入到一个新表中        IF @@ROWCOUNT >0               --执行括号里的T-SQL语句            EXECUTE (‘SELECT * INTO 订单_‘+@year                +‘ FROM 订单 WHERE YEAR(订购日期)=‘+@year)        SET @year = @year - 1    END9.1SELECT 订单.订单ID, 雇员.姓氏, 雇员.名字, 产品.产品名称,     订单明细.单价, 订单明细.数量, 订单明细.折扣, 运货商.公司名称,     订单.货主名称, 订单.货主地址, 订单.货主城市, 订单.订购日期,     订单.发货日期FROM 订单 INNER JOIN      订单明细 ON 订单.订单ID = 订单明细.订单ID INNER JOIN      雇员 ON 订单.雇员ID = 雇员.雇员ID INNER JOIN      产品 ON 订单明细.产品ID = 产品.产品ID INNER JOIN      运货商 ON 订单.运货商 = 运货商.运货商IDCREATE VIEW 订单详细视图ASSELECT 订单.订单ID, 雇员.姓氏, 雇员.名字, 产品.产品名称,     订单明细.单价, 订单明细.数量, 订单明细.折扣, 运货商.公司名称,     订单.货主名称, 订单.货主地址, 订单.货主城市, 订单.订购日期,     订单.发货日期FROM 订单 INNER JOIN      订单明细 ON 订单.订单ID = 订单明细.订单ID INNER JOIN      雇员 ON 订单.雇员ID = 雇员.雇员ID INNER JOIN      产品 ON 订单明细.产品ID = 产品.产品ID INNER JOIN      运货商 ON 订单.运货商= 运货商.运货商IDSELECT * FROM 订单详细视图SELECT * FROM 订单详细视图    WHERE 订单ID = 102489.2.2.1CREATE VIEW [ schema_name . ] view_name                                --架构名.视图名    [ (column [ ,...n ] ) ]                                                             --列名[ WITH <view_attribute> [ ,...n ] ] AS select_statement [ ; ]                                                        --搜索语句[ WITH CHECK OPTION ]           --强制修改语句都必须符合在select_ statement中设置的条件<view_attribute> ::= {    [ ENCRYPTION ]                                                               --加密    [ SCHEMABINDING ]                                                       --绑定架构    [ VIEW_METADATA ]     }                                 --返回有关视图的元数据信息9.2.2.3--创建视图CREATE VIEW view_例一    AS    SELECT 产品.产品ID,产品.产品名称,类别.类别名称,供应商.公司名称        FROM 产品        JOIN 类别 ON 产品.类别ID = 类别.类别ID        JOIN 供应商 ON 产品.供应商ID = 供应商.供应商IDGO--查看视图SELECT * FROM view_例一GO9.2.2.4CREATE VIEW view_例二(产品编号,产品名称,产品类别,供应商名称)    AS    SELECT 产品.产品ID,产品.产品名称,类别.类别名称,供应商.公司名称        FROM 产品        JOIN 类别 ON 产品.类别ID = 类别.类别ID        JOIN 供应商 ON 产品.供应商ID = 供应商.供应商IDGOSELECT * FROM view_例二GO9.2.2.5CREATE VIEW view_例三    AS    SELECT 产品.产品ID,产品.产品名称,类别.类别名称,供应商.公司名称        FROM 产品        JOIN 类别 ON 产品.类别ID = 类别.类别ID        JOIN 供应商 ON 产品.供应商ID = 供应商.供应商ID    ORDER BY 产品.产品ID DESC                  --不能在select子句里使用order by子句SELECT top 100 * FROM view_例三    ORDER BY 产品ID DESCCREATE VIEW view_例三    AS    SELECT top 100 产品.产品ID,产品.产品名称,类别.类别名称,供应商.公司名称        FROM 产品        JOIN 类别 ON 产品.类别ID = 类别.类别ID        JOIN 供应商 ON 产品.供应商ID = 供应商.供应商ID    ORDER BY 产品.产品ID DESC9.3.3ALTER VIEW [ schema_name . ] view_name [ ( column [ ,...n ] ) ] [ WITH <view_attribute> [ ,...n ] ] AS select_statement [ ; ][ WITH CHECK OPTION ]<view_attribute> ::= {     [ ENCRYPTION ]    [ SCHEMABINDING ]    [ VIEW_METADATA ]     }ALTER VIEW view_例三    AS    SELECT top 50 产品.产品ID,产品.产品名称,类别.类别名称,供应商.公司名称        FROM 产品        JOIN 类别 ON 产品.类别ID = 类别.类别ID        JOIN 供应商 ON 产品.供应商ID = 供应商.供应商ID    ORDER BY 产品.产品ID DESC9.4CREATE VIEW view_例五    WITH ENCRYPTION    AS    SELECT top 50 产品.产品ID,产品.产品名称,类别.类别名称,供应商.公司名称        FROM 产品        JOIN 类别 ON 产品.类别ID = 类别.类别ID        JOIN 供应商 ON 产品.供应商ID = 供应商.供应商ID    ORDER BY 产品.产品ID DESCALTER VIEW view_例五    AS    SELECT top 50 产品.产品ID,产品.产品名称,类别.类别名称,供应商.公司名称        FROM 产品        JOIN 类别 ON 产品.类别ID = 类别.类别ID        JOIN 供应商 ON 产品.供应商ID = 供应商.供应商ID    ORDER BY 产品.产品ID DESC9.5--创建两个数据表CREATE TABLE 例七_1(    id int IDENTITY(1,1) NOT NULL PRIMARY KEY,    username nchar(10) NULL) CREATE TABLE 例七_2(    id int IDENTITY(1,1) NOT NULL PRIMARY KEY,    newname nchar(10) NULL)GO--创建视图CREATE VIEW view_例七    WITH SCHEMABINDING    AS    SELECT 例七_1.username,例七_2.newname        FROM dbo.例七_1 JOIN dbo.例七_2            ON 例七_1.id = 例七_2.idGO--修改数据表PRINT ‘‘ALTER TABLE 例七_1    ALTER COLUMN username nvarchar(100)GOPRINT ‘‘ALTER TABLE 例七_2    ALTER COLUMN newname nvarchar(100)GO--删除数据表PRINT ‘‘DROP TABLE 例七_1GOPRINT ‘‘DROP TABLE 例七_2GO9.6CREATE VIEW view_例八    AS    SELECT 产品ID,产品名称,单价        FROM 产品        WHERE 单价> $20    WITH CHECK OPTIONUPDATE view_例八    SET 单价= $16    WHERE 产品ID = 4UPDATE  产品    SET 单价= $169.7.3UPDATE view_例八    SET 单价= $16    WHERE 产品ID = 4DELETE view_例八    WHERE 产品ID = 49.8.2DROP VIEW [ schema_name . ] view_name [ ...,n ] [ ; ]DROP VIEW view_例一DROP VIEW view_例二,view_例三INSERT view_例八(产品名称,单价)    VALUES (‘白菜‘,$1)    WHERE 产品ID = 49.9exec sp_rename ‘view_例五‘,‘view_例五_1‘10.2.1CREATE { PROC | PROCEDURE }      [schema_name.] procedure_name [ ; number ]               --架构名。存储过程名[；分组]    [ { @parameter [ type_schema_name. ] data_type }               --参数        [ VARYING ] [ = default ] [ [ OUT [ PUT ]                     --作为游标输出参数    ] [ ,...n ] [ WITH <procedure_option> [ ,...n ][ FOR REPLICATION ]                        --不能在订阅服务器上执行为复制创建的存储过程AS { <sql_statement> [;][ ...n ]                     --存储过程语句| <method_specifier> }[;]<procedure_option> ::=     [ ENCRYPTION ]                     --加密    [ RECOMPILE ]                      --不预编译    [ EXECUTE_AS_Clause ]        --执行存储过程的安全上下文<sql_statement> ::= { [ BEGIN ] statements [ END ] }                     --存储过程语句<method_specifier> ::=EXTERNAL NAME assembly_name.class_name.method_name   --指定程序集方法CREATE PROC pr_例一    AS    SELECT * FROM 类别GOEXEC pr_例一10.3.1ALTER { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ]     [ { @parameter [ type_schema_name. ] data_type }     [ VARYING ] [ = default ] [ [ OUT [ PUT ]     ] [ ,...n ] [ WITH <procedure_option> [ ,...n ] ][ FOR REPLICATION ] AS      { <sql_statement> [ ...n ] | <method_specifier> }<procedure_option> ::=     [ ENCRYPTION ]    [ RECOMPILE ]    [ EXECUTE_AS_Clause ]<sql_statement> ::= { [ BEGIN ] statements [ END ] }<method_specifier> ::=EXTERNAL NAME assembly_name.class_name.method_nameALTER PROC pr_例一    AS    SELECT * FROM 类别        ORDER BY 类别名称10.4CREATE PROCEDURE pr_例二    @p1 intASBEGIN    SET NOCOUNT ON;    SELECT * FROM 产品 WHERE 类别ID=@p1END[ { EXEC | EXECUTE } ]    {       [ @return_status = ]                                                        --返回值        [ [ @parameter = ] { value                                               --参数及值                           | @variable [ OUTPUT ]                            --返回型参数                           | [ DEFAULT ]                                        --缺省值                           }        ]      [ ,...n ]      [ WITH RECOMPILE ]                       --执行模块后，强制编译、使用和放弃新计划    }10.5.2CREATE PROCEDURE pr_例六    @类别名称 varchar(15),     @单价 money = $10,    @库存量 smallint,    @订购量 smallint =5ASBEGIN    SELECT * FROM 产品 JOIN 类别        ON 产品.类别ID = 类别.类别ID        WHERE (类别.类别名称 = @类别名称)            AND (产品.单价 > @单价)            AND (产品. 库存量 > @库存量)            AND (产品. 订购量 > @订购量)ENDGOCREATE PROCEDURE pr_例六_2    @类别名称 varchar(15),     @库存量 smallint,    @订购量 smallint =5,    @单价 money = $10ASBEGIN    SELECT * FROM 产品 JOIN 类别        ON 产品.类别ID  = 类别.类别ID        WHERE (类别.类别名称 = @类别名称)            AND (产品.单价 > @单价)            AND (产品. 库存量 > @库存量)            AND (产品. 订购量 > @订购量)ENDGO10.5.3CREATE PROCEDURE pr_例七ASBEGIN    declare @返回值 int    SELECT @返回值 = sum(库存量) FROM 产品    return @返回值ENDGOdeclare @接收值 intexec @接收值 = pr_例七print @接收值CREATE PROCEDURE pr_例七_2@返回值 int outputASBEGIN    SELECT @返回值 = sum(库存量) FROM 产品ENDGO接收output的返回值也必须要用变量，如：declare @接收值 intexec pr_例七_2 @接收值 outputprint @接收值CREATE PROCEDURE pr_例七_3ASBEGIN    declare @库存 int    SELECT @库存= sum(库存量) FROM 产品    return ‘库存量为：‘+CAST(@库存 as varchar(10))ENDGOdeclare @接收值 intexec @接收值 = pr_例七_3print @接收值CREATE PROCEDURE pr_例七_4@返回值 varchar(20) outputASBEGIN    declare @库存 int        SELECT @库存 = sum(库存量) FROM 产品    SET @返回值 = ‘库存量为：‘+CAST(@库存 as varchar(10))ENDGOdeclare @接收值 varchar(20)exec pr_例七_4 @接收值 outputprint @接收值CREATE PROCEDURE pr_例八@类别名称 nvarchar(15)ASBEGIN    SELECT 产品ID,产品名称 FROM 产品        JOIN 类别 ON 产品.类别ID = 类别.类别ID        WHERE 类别.类别名称= @类别名称ENDGOexec pr_例八 N‘饮料‘10.5.4SET NOCOUNT ON10.5.5CREATE PROC #临时存储过程AS     SELECT * FROM 产品GOEXEC #临时存储过程10.5.6CREATE PROC pr_例九_查看订购量最多的产品ID@产品ID int outputAS     SELECT TOP 1 @产品ID = 产品ID FROM 订单明细        GROUP BY 产品ID        ORDER BY MAX(数量) DESC    PRINT ‘存储过程“pr_例九_查看订购量最多的产品ID”的嵌套层次为：第‘        +CAST(@@NESTLEVEL AS VARCHAR(1))+‘层‘GOCREATE PROC pr_例九_查看产品的供应商ID@供应商ID int outputAS     DECLARE @产品编号 int    exec pr_例九_查看订购量最多的产品ID @产品编号 output    SELECT @供应商ID = 供应商ID FROM 产品        WHERE 产品ID = @产品编号    PRINT ‘存储过程“pr_例九_查看产品的供应商ID”的嵌套层次为：第‘        +CAST(@@NESTLEVEL AS VARCHAR(1))+‘层‘GOCREATE PROC pr_例九_供应商信息AS     DECLARE @供应商编号 int    exec pr_例九_查看产品的供应商ID @供应商编号 output    SELECT * FROM 供应商        WHERE 供应商ID = @供应商编号    PRINT ‘存储过程“pr_例九_供应商信息”的嵌套层次为：第‘        +CAST(@@NESTLEVEL AS VARCHAR(1))+‘层‘GOExec pr_例九_供应商信息10.5.8CREATE PROC pr_例十@类别名称 nvarchar(15)WITH ENCRYPTIONAS     SELECT * FROM 类别        WHERE 类别名称= @类别名称GO10.5.9sp_helptext 存储过程名exec sp_helptext pr_例八GOexec sp_helptext pr_例十GO10.5.10CREATE PROC pr_例十一;1    as    select * from 类别GOCREATE PROC pr_例十一;2@类别名称 nvarchar(15)    as    select * from 类别        where 类别名称= @类别名称GOEXEC pr_例十一;1或EXEC pr_例十一exec pr_例十一;2 饮料10.6.1DROP { PROC | PROCEDURE } { [ schema_name. ] procedure } [ ,...n ]DROP PROC pr_例八DROP PROC pr_例十一10.7.1sp_help [ [ @objname = ] ‘name‘ ]10.7.2sp_helpdb [ [ @dbname= ] ‘name‘ ]10.7.3sp_helpfile [ [ @filename = ] ‘name‘ ]10.7.4sp_helpfilegroup [ [ @filegroupname = ] ‘name‘ ]10.7.5sp_helpindex [ @objname = ] ‘name‘10.7.7sp_helpstats[ @objname = ] ‘object_name‘      [ , [ @results = ] ‘value‘ ]10.7.8sp_helptext [ @objname = ] ‘name‘ [ , [ @columnname = ] computed_column_name ]10.7.9sp_helptrigger [ @tabname = ] ‘table‘      [ , [ @triggertype = ] ‘type‘ ]10.7.10sp_lock [[@spid1 = ] ‘spid1‘] [,[@spid2 = ] ‘spid2‘]10.7.12sp_rename [ @objname = ] ‘object_name‘ , [ @newname = ] ‘new_name‘     [ , [ @objtype = ] ‘object_type‘ ]10.7.13sp_renamedb [ @dbname = ] ‘old_name‘ , [ @newname = ] ‘new_name‘10.7.14sp_who [[@login_name =] ‘login‘]10.7.15sp_columns [ @table_name = ] object  [ , [ @table_owner = ] owner ]      [ , [ @column_name = ] column ]      [ , [ @ODBCVer = ] ODBCVer ]10.7.17sp_fkeys [ @pktable_name = ] ‘pktable_name‘      [ , [ @pktable_owner = ] ‘pktable_owner‘ ]      { , [ @fktable_name = ] ‘fktable_name‘ }      [ , [ @fktable_owner = ] ‘fktable_owner‘ ] 10.7.18sp_pkeys [ @table_name = ] ‘name‘  [ , [ @table_owner = ] ‘owner‘ ] 10.7.19sp_server_info [[@attribute_id = ] ‘attribute_id‘]10.7.20sp_tables [ [ @table_name = ] ‘name‘ ]      [ , [ @table_owner = ] ‘owner‘ ]      [ , [ @table_type = ] "type" ]10.7.21sp_stored_procedures [ [ @sp_name = ] ‘name‘ ]     [ , [ @sp_owner = ] ‘schema‘]     [ , [@fUsePattern = ] ‘fUsePattern‘ ]10.8.2exec sp_configure ‘clr‘,‘1‘GORECONFIGUREGO10.8.3using System;using System.Data;using System.Data.SqlClient;using System.Data.SqlTypes;using Microsoft.SqlServer.Server;public partial class StoredProcedures{    [Microsoft.SqlServer.Server.SqlProcedure]    public static void CLR_SP(out string returnstr) //returnstr存储过程返回的参数    {        //创建一个数据连接        using (SqlConnection conn = new SqlConnection("context connection=true"))        {            //打开数据库连接            conn.Open();            //创建一个SqlCommand实例，放入T-SQL语句            SqlCommand mycommand = new SqlCommand("select top 1 类别名称 from 类别", conn);            SqlDataReader myreader = mycommand.ExecuteReader();                        if (myreader.Read())            {                //当myreader里含有记录的话，返回类别名                returnstr = myreader[0].ToString();            }            else            {                //当myreader里不含有记录的话，返回“无记录”字符串                returnstr = "无记录";            }            //关闭数据库连接            conn.Close();        }    }};10.8.4csc /t:library /out:CLR_SP.dll CLR_SP.cs10.8.5CREATE ASSEMBLY CLR_SP     FROM ‘E:\book\SQL Server 2008大全\数据库\第十章\扩展存储过程\SqlServerProject\SqlServerProject\CLR_SP.dll‘GO10.8.6CREATE PROCEDURE CLRSP@outstr nvarchar(200) output    AS EXTERNAL NAME CLR_SP.StoredProcedures.CLR_SPGO10.8.7declare @str nvarchar(200)EXEC CLRSP @str outputprint @str11.5.1CREATE TRIGGER 产品_Insert    ON  产品   AFTER INSERTAS BEGIN    print ‘又添加了一种产品‘ENDGOCREATE TRIGGER 产品_Update    ON  产品   AFTER UPDATEAS BEGIN    print ‘有一种产品更改了‘ENDGOCREATE TRIGGER 产品_Delete   ON  产品   AFTER DELETEAS BEGIN    print ‘又删除了一种产品‘ENDGO11.5.2INSERT INTO 产品(产品名称) VALUES (‘大苹果‘)DELETE FROM 产品 WHERE (产品名称= ‘大苹果‘)11.5.3CREATE TRIGGER <Schema_Name, sysname, Schema_Name>.<Trigger_Name, sysname, Trigger_Name>    ON  <Schema_Name, sysname, Schema_Name>.<Table_Name, sysname, Table_Name>    AFTER <Data_Modification_Statements, , INSERT,DELETE,UPDATE>AS BEGIN    -- SET NOCOUNT ON added to prevent extra result sets from    -- interfering with SELECT statements.    SET NOCOUNT ON;    -- Insert statements for trigger hereENDGOCREATE TRIGGER 触发器名   ON  数据表名或视图名   AFTER INSERT或DELETE或UPDATEAS BEGIN    --这里是要运行的SQL语句ENDGOCREATE TRIGGER 订单_InsertON 订单AFTER INSERT, UPDATE, DELETE AS   EXEC master..xp_sendmail ‘张三‘,       ‘订单有更改，请查询确定‘GOCREATE TRIGGER 订单明细_Insert    ON  订单明细   AFTER INSERTAS BEGIN    if (Select 折扣 from inserted)>0.6    begin        print ‘折扣不能大于0.6‘        Rollback Transaction    endINSERT INTO 订单明细(订单ID,产品ID,单价,数量,折扣)    VALUES (11077,1,18,1,0.7)11.6CREATE TRIGGER 产品_Insert1    ON  产品   AFTER INSERTAS BEGIN    print ‘再一次告诉你，你又添加了一种产品‘ENDGOINSERT INTO 产品(产品名称) VALUES (‘大苹果‘)sp_settriggerorder [ @triggername = ] ‘[ triggerschema. ] triggername‘         , [ @order = ] ‘value‘         , [ @stmttype = ] ‘statement_type‘         [ , [ @namespace = ] { ‘DATABASE‘ | ‘SERVER‘ | NULL } ] Exec sp_settriggerorder     ‘产品_Insert1‘,‘First‘,‘Insert‘goExec sp_settriggerorder     ‘产品_Insert‘,’Last’,‘Insert‘GoExec sp_settriggerorder     ‘产品_Insert1‘,‘First‘,’Update’goENDGO11.7CREATE TABLE 操作记录表(    编号 int IDENTITY(1,1) NOT NULL,    操作表名 varchar(50) NOT NULL,    操作语句 varchar(2000) NOT NULL,    操作内容 varchar(2000) NOT NULL,    操作时间 datetime NOT NULL     CONSTRAINT DF_操作记录表_操作时间 DEFAULT (getdate()), CONSTRAINT PK_操作记录表 PRIMARY KEY CLUSTERED (    编号 ASC)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]GOCREATE TRIGGER 操作记录表_Insert    ON  操作记录表   AFTER INSERTAS BEGIN    print ‘数据库又有记录变动了‘ENDGOCREATE TRIGGER 类别_Insert    ON  类别   AFTER INSERTAS BEGIN    Declare     @类别名称 nvarchar(15),     @说明 nvarchar(max)    set @类别名称= (Select 类别名称 from inserted)    set @说明= (Select 说明 from inserted)    INSERT INTO 操作记录表(操作表名,操作语句,操作内容)     VALUES (‘类别表‘,‘插入记录‘,‘类别名称：‘+@类别名称+‘,说明：‘+@说明)ENDGOINSERT INTO 类别(类别名称,说明) VALUES (‘书籍‘,‘各类图书‘)11.9.2CREATE TRIGGER 触发器名   ON  数据表名或视图名   Instead Of INSERT或DELETE或UPDATEAS BEGIN    --这里是要运行的SQL语句ENDGOCREATE TRIGGER 订单明细_Insert    ON  订单明细   Instead Of INSERTAS BEGIN    SET NOCOUNT ON;    declare     @订单ID int,    @产品ID int,    @单价 money,    @数量 smallint,    @折扣 real    set @订单ID = (select 订单ID from inserted)    set @产品ID = (select 产品ID from inserted)    set @单价 = (select 单价 from inserted)    set @数量 = (select 数量 from inserted)    set @折扣 = (select 折扣 from inserted)    if (@折扣)>0.6            print ‘折扣不能大于0.6‘    else            INSERT INTO 订单明细                (订单ID,产品ID,单价,数量,折扣)            VALUES                 (@订单ID,@产品ID,@单价,@数量,@折扣)    ENDGO11.11ALTER  TRIGGER 触发器名   ON  数据表名或视图名   AFTER INSERT或DELETE或UPDATEAS BEGIN    --这里是要运行的SQL语句ENDGO11.13Alter table 数据表名  Disable或Enable trigger 触发器名或ALL11.15.1CREATE TRIGGER trigger_name ON { ALL SERVER | DATABASE } [ WITH <ddl_trigger_option> [ ,...n ] ]{ FOR | AFTER } { event_type | event_group } [ ,...n ]AS { sql_statement  [ ; ] [ ...n ] | EXTERNAL NAME < method specifier >  [ ; ] }CREATE TRIGGER 触发器名 ON  ALL SERVER或DATABASEFOR 或AFTER 激活DDL触发器的事件AS     要执行的SQL语句sp_rename ‘旧触发器名’,’新触发器名’CREATE TRIGGER 禁止对数据表操作ON DATABASE FOR DROP_TABLE, ALTER_TABLEAS    PRINT ‘对不起，您不能对数据表进行操作‘    ROLLBACK ;（4）单击【执行】按钮，生成触发器。CREATE TRIGGER 不允许删除数据库ON all server  FOR DROP_DATABASEAS    PRINT ‘对不起，您不能删除数据库‘    ROLLBACK ;GOCREATE TABLE 日志记录表(    编号 int IDENTITY(1,1) NOT NULL,    事件 varchar(5000) NULL,    所用语句 varchar(5000) NULL,    操作者 varchar(50) NULL,    发生时间 datetime NULL, CONSTRAINT PK_日志记录表 PRIMARY KEY CLUSTERED (    编号 ASC)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]GOCREATE TRIGGER 记录日志ON DATABASE FOR DDL_DATABASE_LEVEL_EVENTS ASDECLARE @log XMLSET @log = EVENTDATA()INSERT  日志记录表   (事件, 所用语句,操作者, 发生时间)    VALUES    (    @log.value(‘(/EVENT_INSTANCE/EventType)[1]‘, ‘nvarchar(100)‘),    @log.value(‘(/EVENT_INSTANCE/TSQLCommand)[1]‘, ‘nvarchar(2000)‘),   CONVERT(nvarchar(100), CURRENT_USER),   GETDATE()   ) ;GO11.15.2CREATE TABLE 测试表(    编号int IDENTITY(1,1) NOT NULL,    测试内容varchar(50) NOT NULL)GODrop table 测试表GOselect * from 日志记录表GO11.16CREATE TRIGGER (Transact-SQL)DROP TRIGGER (Transact-SQL)ALTER TRIGGER (Transact-SQL)sp_rename (Transact-SQL)DISABLE TRIGGER (Transact-SQL)ENABLE TRIGGER (Transact-SQL)DROP TRIGGER (Transact-SQL)11.17.1CREATE TRIGGER 订单明细删除_test    ON  订单明细   AFTER DELETEAS BEGIN    print ‘您此次删除了‘ + Cast(@@rowcount as varchar) + ‘条记录‘ENDGODelete FROM 订单明细 where 折扣=0.25GODelete FROM 订单明细 where 订单ID=‘123456789‘GO11.17.2ALTER TRIGGER 类别_Insert    ON  类别   AFTER INSERTAS BEGIN    Declare     @类别名称 nvarchar(15),     @说明 nvarchar(max)    set @类别名称 = (Select 类别名称 from inserted)    set @说明 = (Select 说明 from inserted)    INSERT INTO 操作记录表 (操作表名,操作语句,操作内容)     VALUES (‘类别表‘,‘插入记录‘,        ‘插入了ID号为‘+cast(@@IDENTITY as varchar)+‘的记录：类别名称：‘            +@类别名称+‘,说明：‘+@说明)ENDGO11.17.3CREATE TRIGGER 只允许修改折扣   ON   订单明细   Instead Of UPDATEAS BEGIN    SET NOCOUNT ON;    if update(折扣)        begin            declare             @订单ID int,            @产品ID int,            @折扣 real            set @订单ID = (select 订单ID from inserted)            set @产品ID = (select 产品ID from inserted)            set @折扣 = (select 折扣 from inserted)            update 订单明细 set 折扣=@折扣                where 订单ID=@订单ID and 产品ID=@产品ID        end    else    begin        print ‘只能更改折扣字段‘    endENDGOupdate 订单明细 set 折扣=0.2     where 订单ID=10288 and 产品ID=54Goupdate 订单明细 set 订单ID=10288     where 订单ID=10288 and 产品ID=54Go11.17.4set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgoALTER TRIGGER 只允许修改折扣    ON  订单明细   Instead Of UPDATEAS BEGIN    SET NOCOUNT ON;    if update(折扣)        begin            declare             @订单ID int,            @产品ID int,            @折扣 real            set @订单ID = (select 订单ID from inserted)            set @产品ID = (select 产品ID from inserted)            set @折扣 = (select 折扣 from inserted)            update 订单明细set 折扣=@折扣                where 订单ID=@订单ID and 产品ID=@产品ID        end    else        begin            print ‘只能更改折扣字段‘            Raiserror(‘除了折扣字段之外的其他字段信息不能修改‘,16,5)        endEND12.4.2CREATE TABLE 例一(    ID INT NOT NULL IDENTITY(1,2) PRIMARY KEY,    USERNAME VARCHAR(45) UNIQUE,    USERSEXY BIT,    USERADD VARCHAR(200))12.7.1CREATE [ UNIQUE ]                                                           --唯一索引        [ CLUSTERED | NONCLUSTERED ]                            --聚集或非聚集索引    INDEX index_name                                                        --索引名称  ON       [database_name. [schema_name] . |schema_name.]table_or_view_name      --表或视图名  ( column [ ASC | DESC ] [ ,...n ] )                                               --索引字段  [ INCLUDE ( column_name [ ,...n ] ) ]                                        --包含性列字段  [ WITH ( PAD_INDEX  = { ON | OFF }                                          --索引填充      | FILLFACTOR = fillfactor                                             --填充因子大小      | SORT_IN_TEMPDB = { ON | OFF }        --是否在tempdb数据库中存储临时排序的结果      | IGNORE_DUP_KEY = { ON | OFF }                               --是否忽略重复的值      | STATISTICS_NORECOMPUTE = { ON | OFF }               --不自动重新计算统计信息      | DROP_EXISTING = { ON | OFF }                                     --删除现有索引      | ALLOW_ROW_LOCKS = { ON | OFF }                        --在访问索引时使用行锁      | ALLOW_PAGE_LOCKS = { ON | OFF }                       --在访问索引时使用页锁      | MAXDOP = max_degree_of_parallelism                            --设置最大并行度         [ ,...n ] ) ]  [ ON { partition_scheme_name ( column_name )                             --指定分区方案       | filegroup_name                                                      --指定文件组       | default                                                  --将索引放在默认文件组中       }  ]12.7.3CREATE INDEX IX_例二    ON 产品    (产品名称)12.7.4CREATE INDEX IX_例三    ON 雇员    (姓氏,名字)12.7.5CREATE UNIQUE INDEX IX_例四    ON  运货商 (公司名称)12.7.6CREATE TABLE 例五(    编号 int NOT NULL,    姓名 varchar(20),    性别 bit)GOCREATE CLUSTERED INDEX IX_例五    ON 例五    (编号)GO12.7.7CREATE NONCLUSTERED INDEX IX_例六    ON 例五    (姓名 desc)12.7.8CREATE INDEX IX_例七    ON 订单明细 (单价,数量)    INCLUDE  (折扣)12.7.9ALTER DATABASE Northwind    ADD FILEGROUP 例八文件组GOALTER DATABASE Northwind    ADD FILE (NAME=例八文件,        FILENAME=‘D:\DBtest\例八文件.ndf‘)        TO FILEGROUP 例八文件组GOCREATE INDEX IX_例八    ON 产品 (库存量)    ON 例八文件组GO12.7.10CREATE INDEX IX_例九    ON 产品 (订购量)    WITH     (        FILLFACTOR = 70    )12.7.11CREATE INDEX IX_例九    ON 产品 (订购量)    WITH     (        PAD_INDEX = ON ,            FILLFACTOR = 70    )12.7.12CREATE UNIQUE INDEX IX_例十一    ON 订单(订单ID,客户ID desc,雇员ID)    INCLUDE (订购日期,到货日期,发货日期)    WITH    (        PAD_INDEX = ON,        FILLFACTOR = 70,        SORT_IN_TEMPDB = ON,        IGNORE_DUP_KEY = ON,        STATISTICS_NORECOMPUTE = OFF,        MAXDOP =2    )    ON 例八文件组12.8.2sp_helpindex [ @objname = ] ‘name‘12.8.3use Northwindselect * from sys.indexes12.8.4ALTER INDEX { index_name | ALL }                                  --指定索引名或所有索引    ON [database_name.[schema_name].|schema_name.]        table_or_view_name                                            --数据表或视图名        { REBUILD                                                       --重新生成索引            [    [ WITH                     ( PAD_INDEX  = { ON | OFF }                           --索引填充                      | FILLFACTOR = fillfactor                          --填充因子大小                      | SORT_IN_TEMPDB = { ON | OFF }  --是否在tempdb数据库中存储临时排序的结果                      | IGNORE_DUP_KEY = { ON | OFF }            --是否忽略重复的值                      | STATISTICS_NORECOMPUTE = { ON | OFF } --不自动重新计算统计信息                      | ALLOW_ROW_LOCKS = { ON | OFF }      --在访问索引时使用行锁                      | ALLOW_PAGE_LOCKS = { ON | OFF }     --在访问索引时使用页锁                      | MAXDOP = max_degree_of_parallelism          --设置最大并行度                      [ ,...n ] )                 ]                 | [ PARTITION = partition_number                         --指定分区方案                        [ WITH                             ( SORT_IN_TEMPDB = { ON | OFF }--是否在tempdb数据库中存储临时排序的结果                                | MAXDOP = max_degree_of_parallelism--设置最大并行度                                [ ,...n ] )                        ]                     ]            ]            | DISABLE                                                      --禁用索引            | REORGANIZE                                        --重新组织的索引叶级                [ PARTITION = partition_number ]     --重新生成或重新组织索引的一个分区                [ WITH ( LOB_COMPACTION = { ON | OFF } ) ] --压缩包含大型对象数据的页            | SET ( ALLOW_ROW_LOCKS= { ON | OFF }            --在访问索引时使用行锁                      | ALLOW_PAGE_LOCKS = { ON | OFF }     --在访问索引时使用页锁                      | IGNORE_DUP_KEY = { ON | OFF }            --是否忽略重复的值                      | STATISTICS_NORECOMPUTE = { ON | OFF }--不自动重新计算统计信息             [ ,...n ] )     }exec sp_helpindex ‘产品‘ALTER INDEX IX_例三    ON 雇员        REBUILDALTER INDEX IX_例三    ON 雇员    REBUILD     WITH (PAD_INDEX = ON,        FILLFACTOR = 70)12.9.3ALTER INDEX IX_例五    ON 例五    REBUILD12.9.4ALTER INDEX IX_例五    ON 例五    REORGANIZE12.9.6sys.dm_db_index_physical_stats (     { database_id | NULL }    , { object_id | NULL }    , { index_id | NULL | 0 }    , { partition_number | NULL }    , { mode | NULL | DEFAULT })declare @databaseid intdeclare @objectid intset @databaseid = DB_ID(N‘Northwind‘)set @objectid = OBJECT_ID(N‘例一‘)select * from sys.dm_db_index_physical_stats    (@databaseid,@objectid,null,null,null)12.10.2ALTER INDEX PK_类别    ON 类别    DISABLEGOSELECT * FROM 类别GO12.12.3DROP INDEX <table_name>.<index_name>DROP INDEX 类别.IX_类别名称12.13.2CREATE VIEW dbo.雇员订单WITH SCHEMABINDINGAS     SELECT 雇员.姓氏,雇员.名字,订单.订单ID,订单.订购日期        FROM dbo.雇员 JOIN dbo.订单        ON 雇员.雇员ID = 订单.雇员IDGOCREATE UNIQUE CLUSTERED INDEX  IX_雇员订单    ON 雇员订单 (订单ID)GO13.1.1sp_addtype [ @typename = ] type,     [ @phystype = ] system_data_type     [ , [ @nulltype = ] ‘null_type‘ ] ;EXEC sp_addtype 编号,‘int‘,‘not null‘EXEC sp_addtype 姓名,‘varchar(10)‘,‘null‘13.1.2USE NorthwindINSERT 例三(编号,姓名) VALUES (1,‘刘智勇‘)DECLARE @ID 编号DECLARE @name nvarchar(10)SELECT top 1 @ID = 编号, @name = 姓名    FROM 例三    ORDER BY 编号DESCprint ‘编号为：‘ + CAST(@ID AS varchar(2))print ‘姓名为：‘ + @name13.1.3DROP TYPE 用户定义数据类型名sp_droptype用户定义数据类型名DROP TABLE 例三GODROP TYPE 编号GOEXEC SP_DROPTYPE 姓名GO13.2.3CREATE FUNCTION [ schema_name. ] function_name                               --函数名( [ { @parameter_name [ AS ]                                                      --参数名[ type_schema_name. ] parameter_data_type                                  --参数类型    [ = default ] }                                                             --设置默认值    [ ,...n ]  ])RETURNS return_data_type                                             --返回值的数据类型    [ WITH <function_option> [ ,...n ] ]                                          --函数的选项    [ AS ]    BEGIN                 function_body                                                   --函数体        RETURN scalar_expression                                               --返回值    END[ ; ]<function_option>::= {    [ ENCRYPTION ]                                                           --设置加密  | [ SCHEMABINDING ]                                                        --绑定架构  | [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT] --指定函数的OnNullCall属性  | [ EXECUTE_AS_Clause ]                                        --指定执行函数的上下文}CREATE FUNCTION 例五()    RETURNS datetime    begin        return getdate()    endGOPRINT dbo.例五()CREATE FUNCTION 例六(@年份 int)    RETURNS money    BEGIN        DECLARE @销售总值 money        SELECT @销售总值= SUM( 订单明细.单价 *订单明细.数量 * (1-订单明细.折扣))             FROM 订单 JOIN 订单明细            ON 订单.订单ID = 订单明细.订单ID            WHERE YEAR(订购日期) = 1998            GROUP BY YEAR(订购日期)        RETURN @销售总值    ENDGOPRINT ‘1998年的销售总值为‘ + CAST(dbo.例六(1998) as varchar(20)) +‘元‘GOCREATE FUNCTION 例七(@姓氏 varchar(20),@名字 varchar(10))    RETURNS int    BEGIN        DECLARE @订单总数 int        SELECT @订单总数 = count(订单.订单ID)            FROM 订单 JOIN 雇员                ON 订单.雇员ID = 雇员.雇员ID            WHERE 雇员.姓氏 = @姓氏 AND 雇员.名字 =  @名字        RETURN @订单总数    ENDGOSelect dbo.例七(‘王‘,‘伟‘)GO13.2.4CREATE FUNCTION [ schema_name. ] function_name ( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type     [ = default ] }     [ ,...n ]  ])RETURNS TABLE    [ WITH <function_option> [ ,...n ] ]    [ AS ]    RETURN [ ( ] select_stmt [ ) ][ ; ]CREATE FUNCTION 例八(@起始时间 datetime,@结束时间 datetime)    RETURNS TABLE    RETURN select * from 订单 where 订购日期 between @起始时间 AND @结束时间GOSELECT *    FROM 例八(‘1996-7-1‘,‘1996-12-1‘)    ORDER BY 订购日期GO    13.2.5CREATE FUNCTION [ schema_name. ] function_name ( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type     [ = default ] }     [ ,...n ]  ])RETURNS @return_variable TABLE < table_type_definition >    [ WITH <function_option> [ ,...n ] ]    [ AS ]    BEGIN                 function_body         RETURN    END[ ; ]CREATE FUNCTION 例九()    RETURNS @名单 TABLE        (编号 INT IDENTITY(1,1) NOT NULL,        姓名 nvarchar(40) NOT NULL,        地址 nvarchar(60) NULL,        邮编 varchar(10) NULL,        电话 varchar(24) NULL,        类别 varchar(10))    BEGIN        INSERT @名单            SELECT 联系人姓名,地址,邮政编码,电话,‘供应商‘                FROM 供应商        INSERT @名单            SELECT 姓氏+名字,地址,邮政编码,家庭电话,‘雇员‘                FROM 雇员        INSERT @名单            SELECT 联系人姓名,地址,邮政编码,电话,‘客户‘                FROM 客户        INSERT @名单            SELECT 公司名称,NULL,NULL,电话,‘运货商‘                FROM 运货商        RETURN    ENDGOSELECT * FROM 例九()CREATE FUNCTION 例十(@年份 int)    RETURNS @主管信息 TABLE        (            雇员ID  int NOT NULL,            姓氏 nvarchar(20) NOT NULL,            名字 nvarchar(10) NOT NULL,            职务 nvarchar(30) NULL,            尊称 nvarchar(25) NULL,            雇用日期 datetime NULL,            照片 nvarchar(255) NULL,            备注 nvarchar(max) NULL        )    BEGIN        DECLARE @雇员ID  int        DECLARE @上级ID  int        SELECT  TOP 1 @雇员ID  = 雇员ID            FROM 订单            WHERE YEAR(订购日期) = 1996            GROUP BY YEAR(订购日期),雇员ID            ORDER BY COUNT(订单ID) DESC        SELECT @上级ID = 上级FROM 雇员            WHERE 雇员ID = @雇员ID        INSERT @主管信息        SELECT 雇员ID,姓氏,名字,职务,尊称,雇用日期,照片,备注            FROM 雇员            WHERE 雇员ID = @上级ID        RETURN    ENDGOSELECT * FROM 例十(1996)13.2.8--创建一个用户定义函数，用于生成数据表的“编号”字段内容CREATE FUNCTION 例十一_编号()    RETURNS varchar(7)    BEGIN        DECLARE @编号varchar(7)        DECLARE @id int        --找出目前编号最大的记录        SELECT TOP 1 @编号= 编号            FROM tb_例十一            ORDER BY 编号DESC        --如果数据表里没有记录，则将第一条记录的编号设为“TCP-001”        IF @@ROWCOUNT = 0            SET @编号= ‘TCP-001‘        ELSE            BEGIN                --获取最大编号的后三位数，并加一                SET @id = CAST(SUBSTRING(@编号,5,3) AS int) + 1                --REPLICATE函数用于添加                SET @编号= ‘TCP-‘ + REPLICATE(‘0‘,3-LEN(@id)) + CAST(@id as varchar(3))            END        RETURN @编号    ENDGO--创建一个数据表CREATE TABLE tb_例十一(    --将编号的默认值设为“dbo.例十一_编号()”函数    编号varchar(7) DEFAULT dbo.例十一_编号(),    名称nvarchar(10))GO--在数据表中插入记录INSERT tb_例十一(名称) VALUES (‘测试一‘)INSERT tb_例十一(名称) VALUES (‘测试二‘)INSERT tb_例十一(名称) VALUES (‘测试三‘)--查看数据表的内容SELECT * FROM tb_例十一GO13.2.9DROP FUNCTION { [ schema_name. ] function_name } [ ,...n ]DROP FUNCTION 例五,例十14.7.1CONTAINS    ( { column_name | (column_list) | * }                                              --列名        , ‘< contains_search_condition >‘                                        --搜索语句        [ , LANGUAGE language_term ]                            --发出查询时所用的语言    )     < contains_search_condition > ::=     { < simple_term >                                                    --简单词搜索方式    | < prefix_term >                                                     --前缀词搜索方式    | < generation_term >                                                 --派生词搜索方式    | < proximity_term >                                                  --邻近词搜索方式    | < weighted_term >                                                  --权重词搜索方式    }     | { ( < contains_search_condition > )                                          --搜索语句    [ { AND | & | AND NOT | & ! | OR | | } ]                                             --条件    < contains_search_condition > [ ...n ]                                         --搜索语句} < simple_term > ::=                                                 --简单词搜索方式语法块          word | " phrase "< prefix term > ::=                                                  --前缀词搜索方式语法块     { "word * " | "phrase *" }< generation_term > ::=                                             --派生词搜索方式语法块     FORMSOF (           { INFLECTIONAL                                              --指定词干分析器          | THESAURUS } ,                                                --指定同义词库           < simple_term > [ ,...n ] ) < proximity_term > ::=                                               --邻近词搜索方式语法块     { < simple_term > | < prefix_term > }      { { NEAR | ~ }     { < simple_term > | < prefix_term > }      } [ ...n ] < weighted_term > ::=                                               --权重词搜索方式语法块     ISABOUT         ( { {   < simple_term >   | < prefix_term >   | < generation_term >   | < proximity_term >   }    [ WEIGHT ( weight_value ) ]                                                  --指定权重   } [ ,...n ]         )SELECT * FROM 文章    WHERE CONTAINS(标题,‘上海‘)SELECT * FROM 文章    WHERE CONTAINS(内容,‘ "上海" OR "广州"‘)SELECT * FROM 文章    WHERE CONTAINS(内容,‘ 上海‘ OR  ’广州‘)SELECT * FROM 文章    WHERE CONTAINS(内容,‘FORMSOF(INFLECTIONAL,download)‘)SELECT * FROM 文章    WHERE CONTAINS(内容,‘ "do*" ‘)SELECT * FROM 文章    WHERE CONTAINS(内容,        ‘ISABOUT ("download" weight(0.9),        "上海"  weight(0.6),        "山西"  weight(0.5))‘)SELECT * FROM 文章    WHERE CONTAINS(内容,        ‘ISABOUT (download weight(0.9),        上海  weight(0.6),        山西  weight(0.5))‘)SELECT * FROM 文章    WHERE CONTAINS(内容,‘ "教育部" NEAR "表示"‘)14.7.2SELECT * FROM 文章    WHERE FREETEXT(内容,‘教育部‘)SELECT * FROM 文章    WHERE CONTAINS (内容,‘教育部‘)14.7.3CONTAINSTABLE ( table , { column_name | (column_list ) | * } , ‘ < contains_search_condition > ‘      [ , LANGUAGE language_term]   [ ,top_n_by_rank ]    ) < contains_search_condition > ::=     { < simple_term >     | < prefix_term >     | < generation_term >     | < proximity_term >     |  < weighted_term >     }     | { ( < contains_search_condition > )     { { AND | & } | { AND NOT | &! } | { OR | | } }      < contains_search_condition > [ ...n ] }< simple_term > ::=           word | " phrase "< prefix term > ::=      { "word * " | "phrase *" } < generation_term > ::=      FORMSOF ( { INFLECTIONAL | THESAURUS } , < simple_term > [ ,...n ] ) < proximity_term > ::=      { < simple_term > | < prefix_term > }      { { NEAR | ~ } { < simple_term > | < prefix_term > } } [ ...n ] < weighted_term > ::=      ISABOUT        ( { {   < simple_term >   | < prefix_term >   | < generation_term >   | < proximity_term >   }    [ WEIGHT ( weight_value ) ]    } [ ,...n ]         )SELECT * FROM     CONTAINSTABLE(文章,内容,‘教育部‘) as table1SELECT * FROM 文章 JOIN    CONTAINSTABLE(文章,内容,‘ "教育部" NEAR "表示" ‘,10) as table1    ON 文章.编号= table1.[KEY]    ORDER BY table1.RANK DESCSELECT * FROM 文章 JOIN    CONTAINSTABLE(文章,内容,        ‘ISABOUT ("download" weight(0.9),        "上海"  weight(0.6),        "山西"  weight(0.1))‘) AS TABLE1    ON 文章.编号 = TABLE1.[KEY]    ORDER BY TABLE1.RANK DESC14.7.4FREETEXTTABLE (table , { column_name | (column_list) | * }           , ‘freetext_string‘      [ ,LANGUAGE language_term ]      [ ,top_n_by_rank ] )SELECT 文章.内容,TABLE1.* FROM 文章JOIN    FREETEXTTABLE(文章,内容,‘教育部‘,8) AS TABLE1    ON 文章.编号= TABLE1.[KEY]14.7.5SELECT 编号,标题,文件,扩展名 FROM 文章    WHERE CONTAINS(文件,‘数据库‘)14.8.1CREATE FULLTEXT CATALOG catalog_name     [ON FILEGROUP filegroup ]     [IN PATH ‘rootpath‘]     [WITH <catalog_option>]     [AS DEFAULT]     [AUTHORIZATION owner_name ]<catalog_option>::=     ACCENT_SENSITIVITY = {ON|OFF}CREATE FULLTEXT CATALOG TSQL全文目录    ON FILEGROUP [PRIMARY]    IN PATH ‘E:\book\SQL Server 2008大全\数据库\第十四章\运行后数据库‘    AS DEFAULT14.8.2ALTER FULLTEXT CATALOG catalog_name { REBUILD [ WITH ACCENT_SENSITIVITY = { ON | OFF } ]| REORGANIZE| AS DEFAULT }ALTER FULLTEXT CATALOG TSQL全文目录    REBUILD14.8.3CREATE FULLTEXT INDEX ON table_name     [(column_name [TYPE COLUMN type_column_name]           [LANGUAGE language_term] [,...n])]     KEY INDEX index_name          [ON fulltext_catalog_name]     [WITH           {CHANGE_TRACKING {MANUAL | AUTO | OFF [, NO POPULATION]}}     ]CREATE FULLTEXT INDEX     ON 文章(标题,内容,文件 TYPE COLUMN 扩展名)    KEY INDEX PK_文章    ON TSQL全文目录14.8.4ALTER FULLTEXT INDEX ON table_name   { ENABLE    | DISABLE   | SET CHANGE_TRACKING { MANUAL | AUTO | OFF }   | ADD ( column_name      [ TYPE COLUMN type_column_name ]      [ LANGUAGE language_term ] [,...n] )     [ WITH NO POPULATION ]   | DROP ( column_name [,...n] )     [WITH NO POPULATION ]    | START { FULL | INCREMENTAL | UPDATE } POPULATION   | STOP POPULATION   }ALTER FULLTEXT INDEX ON 文章    DISABLEALTER FULLTEXT INDEX ON 文章    ENABLEALTER FULLTEXT INDEX ON 文章    DROP (文件)ALTER FULLTEXT INDEX ON 文章    START FULL POPULATION14.8.5DROP FULLTEXT INDEX ON table_nameDROP FULLTEXT INDEX ON 文章14.8.6DROP FULLTEXT CATALOG catalog_nameDROP FULLTEXT CATALOG TSQL全文目录14.7.1CONTAINS    ( { column_name | (column_list) | * }                                              --列名        , ‘< contains_search_condition >‘                                        --搜索语句        [ , LANGUAGE language_term ]                            --发出查询时所用的语言    )     < contains_search_condition > ::=     { < simple_term >                                                    --简单词搜索方式    | < prefix_term >                                                     --前缀词搜索方式    | < generation_term >                                                 --派生词搜索方式    | < proximity_term >                                                  --邻近词搜索方式    | < weighted_term >                                                  --权重词搜索方式    }     | { ( < contains_search_condition > )                                          --搜索语句    [ { AND | & | AND NOT | & ! | OR | | } ]                                             --条件    < contains_search_condition > [ ...n ]                                         --搜索语句} < simple_term > ::=                                                 --简单词搜索方式语法块          word | " phrase "< prefix term > ::=                                                  --前缀词搜索方式语法块     { "word * " | "phrase *" }< generation_term > ::=                                             --派生词搜索方式语法块     FORMSOF (           { INFLECTIONAL                                              --指定词干分析器          | THESAURUS } ,                                                --指定同义词库           < simple_term > [ ,...n ] ) < proximity_term > ::=                                               --邻近词搜索方式语法块     { < simple_term > | < prefix_term > }      { { NEAR | ~ }     { < simple_term > | < prefix_term > }      } [ ...n ] < weighted_term > ::=                                               --权重词搜索方式语法块     ISABOUT         ( { {   < simple_term >   | < prefix_term >   | < generation_term >   | < proximity_term >   }    [ WEIGHT ( weight_value ) ]                                                  --指定权重   } [ ,...n ]         )SELECT * FROM 文章    WHERE CONTAINS(标题,‘上海‘)SELECT * FROM 文章    WHERE CONTAINS(内容,‘ "上海" OR "广州"‘)SELECT * FROM 文章    WHERE CONTAINS(内容,‘ 上海‘ OR  ’广州‘)SELECT * FROM 文章    WHERE CONTAINS(内容,‘FORMSOF(INFLECTIONAL,download)‘)SELECT * FROM 文章    WHERE CONTAINS(内容,‘ "do*" ‘)SELECT * FROM 文章    WHERE CONTAINS(内容,        ‘ISABOUT ("download" weight(0.9),        "上海"  weight(0.6),        "山西"  weight(0.5))‘)SELECT * FROM 文章    WHERE CONTAINS(内容,        ‘ISABOUT (download weight(0.9),        上海  weight(0.6),        山西  weight(0.5))‘)SELECT * FROM 文章    WHERE CONTAINS(内容,‘ "教育部" NEAR "表示"‘)14.7.2FREETEXT ( { column_name | (column_list) | * }           , ‘freetext_string‘ [ , LANGUAGE language_term ] )SELECT * FROM 文章    WHERE FREETEXT(内容,‘教育部‘)SELECT * FROM 文章    WHERE CONTAINS (内容,‘教育部‘)14.7.3CONTAINSTABLE ( table , { column_name | (column_list ) | * } , ‘ < contains_search_condition > ‘      [ , LANGUAGE language_term]   [ ,top_n_by_rank ]    ) < contains_search_condition > ::=     { < simple_term >     | < prefix_term >     | < generation_term >     | < proximity_term >     |  < weighted_term >     }     | { ( < contains_search_condition > )     { { AND | & } | { AND NOT | &! } | { OR | | } }      < contains_search_condition > [ ...n ] }< simple_term > ::=           word | " phrase "< prefix term > ::=      { "word * " | "phrase *" } < generation_term > ::=      FORMSOF ( { INFLECTIONAL | THESAURUS } , < simple_term > [ ,...n ] ) < proximity_term > ::=      { < simple_term > | < prefix_term > }      { { NEAR | ~ } { < simple_term > | < prefix_term > } } [ ...n ] < weighted_term > ::=      ISABOUT        ( { {   < simple_term >   | < prefix_term >   | < generation_term >   | < proximity_term >   }    [ WEIGHT ( weight_value ) ]    } [ ,...n ]         )SELECT * FROM     CONTAINSTABLE(文章,内容,‘教育部‘) as table1SELECT * FROM 文章 JOIN    CONTAINSTABLE(文章,内容,‘ "教育部" NEAR "表示" ‘,10) as table1    ON 文章.编号= table1.[KEY]    ORDER BY table1.RANK DESCSELECT * FROM 文章 JOIN    CONTAINSTABLE(文章,内容,        ‘ISABOUT ("download" weight(0.9),        "上海"  weight(0.6),        "山西"  weight(0.1))‘) AS TABLE1    ON 文章.编号 = TABLE1.[KEY]    ORDER BY TABLE1.RANK DESC14.7.4FREETEXTTABLE (table , { column_name | (column_list) | * }           , ‘freetext_string‘      [ ,LANGUAGE language_term ]      [ ,top_n_by_rank ] )由以上代码可以看出FREETEXTTABLE函数与FREETEXT谓词的语法代码相似，只是多了table和top_n_by_rank两个参数。SELECT 文章.内容,TABLE1.* FROM 文章JOIN    FREETEXTTABLE(文章,内容,‘教育部‘,8) AS TABLE1    ON 文章.编号= TABLE1.[KEY]14.7.5SELECT 编号,标题,文件,扩展名 FROM 文章    WHERE CONTAINS(文件,‘数据库‘)14.8.1CREATE FULLTEXT CATALOG catalog_name     [ON FILEGROUP filegroup ]     [IN PATH ‘rootpath‘]     [WITH <catalog_option>]     [AS DEFAULT]     [AUTHORIZATION owner_name ]<catalog_option>::=     ACCENT_SENSITIVITY = {ON|OFF}CREATE FULLTEXT CATALOG TSQL全文目录    ON FILEGROUP [PRIMARY]    IN PATH ‘E:\book\SQL Server 2008大全\数据库\第十四章\运行后数据库‘    AS DEFAULT14.8.2ALTER FULLTEXT CATALOG catalog_name { REBUILD [ WITH ACCENT_SENSITIVITY = { ON | OFF } ]| REORGANIZE| AS DEFAULT }ALTER FULLTEXT CATALOG TSQL全文目录    REBUILD14.8.3CREATE FULLTEXT INDEX ON table_name     [(column_name [TYPE COLUMN type_column_name]           [LANGUAGE language_term] [,...n])]     KEY INDEX index_name          [ON fulltext_catalog_name]     [WITH           {CHANGE_TRACKING {MANUAL | AUTO | OFF [, NO POPULATION]}}     ]CREATE FULLTEXT INDEX     ON 文章(标题,内容,文件 TYPE COLUMN 扩展名)    KEY INDEX PK_文章    ON TSQL全文目录14.8.4ALTER FULLTEXT INDEX ON table_name   { ENABLE    | DISABLE   | SET CHANGE_TRACKING { MANUAL | AUTO | OFF }   | ADD ( column_name      [ TYPE COLUMN type_column_name ]      [ LANGUAGE language_term ] [,...n] )     [ WITH NO POPULATION ]   | DROP ( column_name [,...n] )     [WITH NO POPULATION ]    | START { FULL | INCREMENTAL | UPDATE } POPULATION   | STOP POPULATION   }ALTER FULLTEXT INDEX ON 文章    DISABLEALTER FULLTEXT INDEX ON 文章    ENABLEALTER FULLTEXT INDEX ON 文章    DROP (文件)ALTER FULLTEXT INDEX ON 文章    START FULL POPULATION14.8.5DROP FULLTEXT INDEX ON table_nameDROP FULLTEXT INDEX ON 文章14.8.6DROP FULLTEXT CATALOG catalog_nameDROP FULLTEXT CATALOG TSQL全文目录16.1.2--开始事务BEGIN TRANDECLARE @订单ID int--添加一个订单INSERT 订单    (客户ID,雇员ID,订购日期,货主名称,货主地址,货主城市,货主地区,        货主邮政编码,货主国家)    VALUES    (‘VINET‘,2,GETDATE(),‘余小姐‘,‘光明北路124 号‘,‘北京‘,‘华北‘,        ‘111080‘,‘中国‘)IF @@ERROR > 0    GOTO TranRoolBackSET @订单ID = @@IDENTITY--添加两个订单详情INSERT 订单明细(订单ID,产品ID,单价,数量,折扣)    VALUES (@订单ID,14,$200,1,0)IF @@ERROR > 0    GOTO TranRoolBackINSERT 订单明细(订单ID,产品ID,单价,数量,折扣)    VALUES (@订单ID,51,$200,1,0)IF @@ERROR > 0    GOTO TranRoolBackTranRoolBack:IF @@ERROR > 0 OR @@ROWCOUNT<>1    ROLLBACK TRAN  --如果发生错误则回滚事务ELSE    COMMIT TRAN    --如果没有发生错误则提交事务    GO16.2.2--事务开始前查看数据表中原始记录SELECT 类别ID,类别名称FROM 类别ORDER BY 类别ID DESC--事务开始BEGIN TRAN--插入第一条记录INSERT 类别(类别名称) VALUES (‘图书‘)IF @@ERROR >0 OR @@ROWCOUNT <> 1    GOTO TranRollBack--插入第二条记录INSERT 类别(类别名称) VALUES (‘电器‘)IF @@ERROR >0 OR @@ROWCOUNT <> 1    GOTO TranRollBack--查看插入记录后的数据集SELECT 类别ID,类别名称FROM 类别ORDER BY 类别ID DESC--删除最后插入的记录DELETE 类别WHERE 类别ID = (SELECT MAX(类别ID) FROM 类别)IF @@ERROR >0 OR @@ROWCOUNT <> 1BEGIN    TranRollBack:    ROLLBACK TRAN        --回滚事务ENDELSE    COMMIT TRAN            --提交事务--事务结束--事务结束后的数据集SELECT 类别ID,类别名称FROM 类别ORDER BY 类别ID DESC--事务开始前查看数据表中原始记录SELECT 类别ID,类别名称FROM 类别ORDER BY 类别ID DESC--事务开始BEGIN TRAN--插入第一条记录INSERT 类别(类别名称) VALUES (‘海鲜‘)IF @@ERROR >0 OR @@ROWCOUNT <> 1    GOTO TranRollBack--插入第二条记录INSERT 类别(类别名称) VALUES (‘服装‘)IF @@ERROR >0 OR @@ROWCOUNT <> 1    GOTO TranRollBack--查看插入记录后的数据集SELECT 类别ID,类别名称FROM 类别ORDER BY 类别ID DESC--删除类别为“饮料”的记录DELETE 类别WHERE 类别名称= N‘饮料‘IF @@ERROR >0 OR @@ROWCOUNT <> 1BEGIN    TranRollBack:    ROLLBACK TRAN        --回滚事务ENDELSE    COMMIT TRAN            --提交事务--事务结束--事务结束后的数据集SELECT 类别ID,类别名称FROM 类别ORDER BY 类别ID DESC16.3.2--查看事务执行之前的记录SELECT * FROM 产品--开始事务BEGIN TRAN--插入两条记录INSERT 产品(产品名称,类别ID) VALUES (‘西瓜汁‘,1)IF @@ERROR> 0 OR @@ROWCOUNT <>1    GOTO TranRollBackINSERT 产品(产品名称,类别ID) VALUES (‘猕猴桃汁‘,1)IF @@ERROR> 0 OR @@ROWCOUNT <>1    GOTO TranRollBack--查看在事务嵌套之前的记录情况SELECT * FROM 产品    --嵌套开始    BEGIN TRAN        INSERT 产品(产品名称,类别ID) VALUES (‘可乐‘,1)    IF @@ERROR> 0 OR @@ROWCOUNT <>1        ROLLBACK TRAN    ELSE        COMMIT TRAN    --嵌套结束--查看事务嵌套后的记录情况SELECT * FROM 产品IF @@ERROR >0BEGIN    TranRollBack:    ROLLBACK TRANENDELSE    COMMIT TRAN--查看所有事务完成后的情况SELECT * FROM 产品--查看事务执行之前的记录SELECT * FROM 产品--开始事务BEGIN TRAN--插入两条记录INSERT 产品(产品名称,类别ID) VALUES (‘西瓜汁‘,1)IF @@ERROR> 0 OR @@ROWCOUNT <>1    GOTO TranRollBackINSERT 产品(产品名称,类别ID) VALUES (‘猕猴桃汁‘,1)IF @@ERROR> 0 OR @@ROWCOUNT <>1    GOTO TranRollBack--查看在事务嵌套之前的记录情况SELECT * FROM 产品    --嵌套开始    BEGIN TRAN        INSERT 产品(产品名称,类别ID) VALUES (‘牛奶‘,1)    IF @@ERROR> 0 OR @@ROWCOUNT <>1        ROLLBACK TRAN    ELSE        COMMIT TRAN    --嵌套结束--查看事务嵌套后的记录情况SELECT * FROM 产品--在外层事务里再插入一条记录INSERT 产品(产品名称,类别ID) VALUES (‘可乐‘,1)    IF @@ERROR> 0 OR @@ROWCOUNT <>1        ROLLBACK TRAN    ELSE        COMMIT TRAN--查看插入记录后的结果SELECT * FROM 产品IF @@ERROR >0BEGIN    TranRollBack:    ROLLBACK TRANENDELSE    COMMIT TRAN--查看所有事务完成后的情况SELECT * FROM 产品16.3.3BEGIN TRANPRINT ‘当前事务嵌套层次为：‘ + CAST(@@TRANCOUNT AS VARCHAR(2))INSERT 产品(产品名称,类别ID) VALUES (‘西瓜汁‘,1)IF @@ERROR> 0 OR @@ROWCOUNT <>1    GOTO TranRollBackINSERT 产品(产品名称,类别ID) VALUES (‘猕猴桃汁‘,1)IF @@ERROR> 0 OR @@ROWCOUNT <>1    GOTO TranRollBack    --嵌套开始    BEGIN TRAN    PRINT ‘当前事务嵌套层次为：‘ + CAST(@@TRANCOUNT AS VARCHAR(2))        INSERT 产品(产品名称,类别ID) VALUES (‘可乐‘,1)    IF @@ERROR> 0 OR @@ROWCOUNT <>1        ROLLBACK TRAN    ELSE        COMMIT TRAN    --嵌套结束PRINT ‘当前事务嵌套层次为：‘ + CAST(@@TRANCOUNT AS VARCHAR(2))IF @@ERROR >0BEGIN    TranRollBack:    ROLLBACK TRANENDELSE    COMMIT TRAN16.4SAVE { TRAN | TRANSACTION } { savepoint_name | @savepoint_variable }ROLLBACK { TRAN | TRANSACTION } { savepoint_name | @savepoint_variable }SELECT * FROM 产品--开始事务BEGIN TRAN--插入两条记录INSERT 产品(产品名称,类别ID) VALUES (‘西瓜汁‘,1)IF @@ERROR> 0 OR @@ROWCOUNT <>1    GOTO TranRollBackINSERT 产品(产品名称,类别ID) VALUES (‘猕猴桃汁‘,1)IF @@ERROR> 0 OR @@ROWCOUNT <>1    GOTO TranRollBack--查看在事务嵌套之前的记录情况SELECT * FROM 产品SAVE TRAN 嵌套事务    --嵌套开始    BEGIN TRAN        INSERT 产品(产品名称,类别ID) VALUES (‘牛奶‘,1)    IF @@ERROR> 0 OR @@ROWCOUNT <>1        ROLLBACK TRAN 嵌套事务    ELSE        COMMIT TRAN 嵌套事务    --嵌套结束--查看事务嵌套后的记录情况SELECT * FROM 产品--在外层事务里再插入一条记录INSERT 产品(产品名称,类别ID) VALUES (‘可乐‘,1)    IF @@ERROR> 0 OR @@ROWCOUNT <>1        ROLLBACK TRAN    ELSE        COMMIT TRAN--查看插入记录后的结果SELECT * FROM 产品IF @@ERROR >0BEGIN    TranRollBack:    ROLLBACK TRANENDELSE    COMMIT TRAN--查看所有事务完成后的情况SELECT * FROM 产品16.5SET TRANSACTION ISOLATION LEVEL    { READ UNCOMMITTED    | READ COMMITTED    | REPEATABLE READ    | SNAPSHOT    | SERIALIZABLE    }[ ; ]17.1.3CREATE STATISTICS ON < table_name > [ . <index_name> ]    WITH FULLSCAN {, NORECOMPUTE }CREATE STATISTICS 类别名称 ON 类别 (类别名称)17.1.4UPDATE STATISTICS table | view     [         {             { index | statistics_name }          | ( { index |statistics_name } [ ,...n ] )                 }    ]     [    WITH         [             [ FULLSCAN ]             | SAMPLE number { PERCENT | ROWS } ]             | RESAMPLE         ]         [ [ , ] [ ALL | COLUMNS | INDEX ]         [ [ , ] NORECOMPUTE ]     ] ;UPDATE STATISTICS 类别 类别名称17.1.5DROP STATISTICS table.statistics_name | view.statistics_name [ ,...n ]DROP STATISTICS 类别.类别名称17.2.2CREATE SYNONYM [ schema_name_1. ] synonym_name FOR < object >< object > :: ={[server_name.[database_name] . [ schema_name_2 ].| database_name . [ schema_name_2 ].| schema_name_2. ] object_name}CREATE SYNONYM server_类别 FOR WIN-JNZL5E023CZ.test.dbo.类别17.2.3CREATE SYNONYM local_类别FOR 类别GOSELECT * FROM local_类别GOCREATE PROC pr_存储过程    AS    SELECT * FROM 类别GOCREATE SYNONYM local_存储过程 FOR pr_存储过程GOexec local_存储过程SELECT * FROM server_类别SELECT * FROM server_类别17.2.4DROP SYNONYM [ schema. ] synonym_nameDROP SYNONYM local_存储过程18.1sp_addumpdevice [ @devtype = ] ‘device_type‘         , [ @logicalname = ] ‘logical_name‘         , [ @physicalname = ] ‘physical_name‘      ]exec sp_addumpdevice ‘disk‘,‘新备份设备‘,    ‘E:\book\SQL Server 2008大全\数据库\第十八章\运行后数据库\新备份设备.bak‘18.3.1BACKUP DATABASE { database_name | @database_name_var } TO < backup_device > [ ,...n ] [ [ MIRROR TO < backup_device > [ ,...n ] ] [ ...next-mirror ] ] [ WITH      [ BLOCKSIZE = { blocksize | @blocksize_variable } ]      [ [ , ] BUFFERCOUNT = { buffercount | @buffercount_variable } ]      [ [ , ] { CHECKSUM | NO_CHECKSUM } ]     [ [ , ] { STOP_ON_ERROR | CONTINUE_AFTER_ERROR } ]     [ [ , ] DESCRIPTION = { ‘text‘ | @text_variable } ]      [ [ , ] DIFFERENTIAL ]      [ [ , ] EXPIREDATE = { date | @date_var }      | RETAINDAYS = { days | @days_var } ]      [ [ , ] PASSWORD = { password | @password_variable } ]      [ [ , ] { FORMAT | NOFORMAT } ]      [ [ , ] { INIT | NOINIT } ]      [ [ , ] { NOSKIP | SKIP } ]      [ [ , ] MAXTRANSFERSIZE = { maxtransfersize | @maxtransfersize_variable } ]     [ [ , ] MEDIADESCRIPTION = { ‘text‘ | @text_variable } ]      [ [ , ] MEDIANAME = { media_name | @media_name_variable } ]      [ [ , ] MEDIAPASSWORD = { mediapassword | @mediapassword_variable } ]      [ [ , ] NAME = { backup_set_name | @backup_set_name_var } ]      [ [ , ] { REWIND | NOREWIND } ]      [ [ , ] { UNLOAD | NOUNLOAD } ]      [ [ , ] RESTART ]      [ [ , ] STATS [ = percentage ] ]      [ [ , ] COPY_ONLY ]]<backup_device> ::=      {     { logical_backup_device_name | @logical_backup_device_name_var }     |     { DISK | TAPE } = { ‘physical_backup_device_name‘ | @physical_backup_device_name_var }     }BACKUP DATABASE Northwind    TO 新备份设备BACKUP DATABASE Northwind    TO DISK = ‘D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\backup.BAK‘BACKUP DATABASE Northwind    TO 新备份设备18.3.2BACKUP DATABASE { database_name | @database_name_var }      <file_or_filegroup> [ ,...f ]TO < backup_device > [ ,...n ] [ [ MIRROR TO < backup_device > [ ,...n ] ] [ ...next-mirror ] ] [ WITH      [ BLOCKSIZE = { blocksize | @blocksize_variable } ]      [ [ , ] BUFFERCOUNT = { buffercount | @buffercount_variable } ]      [ [ , ] { CHECKSUM | NO_CHECKSUM } ]     [ [ , ] { STOP_ON_ERROR | CONTINUE_AFTER_ERROR } ]     [ [ , ] DESCRIPTION = { ‘text‘ | @text_variable } ]      [ [ , ] DIFFERENTIAL ]      [ [ , ] EXPIREDATE = { date | @date_var }      | RETAINDAYS = { days | @days_var } ]      [ [ , ] PASSWORD = { password | @password_variable } ]      [ [ , ] { FORMAT | NOFORMAT } ]      [ [ , ] { INIT | NOINIT } ]      [ [ , ] { NOSKIP | SKIP } ]      [ [ , ] MAXTRANSFERSIZE = { maxtransfersize | @maxtransfersize_variable } ]     [ [ , ] MEDIADESCRIPTION = { ‘text‘ | @text_variable } ]      [ [ , ] MEDIANAME = { media_name | @media_name_variable } ]      [ [ , ] MEDIAPASSWORD = { mediapassword | @mediapassword_variable } ]      [ [ , ] NAME = { backup_set_name | @backup_set_name_var } ]      [ [ , ] { REWIND | NOREWIND } ]      [ [ , ] { UNLOAD | NOUNLOAD } ]      [ [ , ] RESTART ]      [ [ , ] STATS [ = percentage ] ]      [ [ , ] COPY_ONLY ]]<file_or_filegroup> :: =      {     FILE = { logical_file_name | @logical_file_name_var }     |     FILEGROUP = { logical_filegroup_name | @logical_filegroup_name_var }     | READ_WRITE_FILEGROUPS     }BACKUP DATABASE Northwind    FILE = ‘Northwind_Data‘    TO 新备份设备BACKUP DATABASE Northwind    FILEGROUP = ‘ Northwind文件组‘    TO 新备份设备18.3.3BACKUP LOG { database_name | @database_name_var } {      TO <backup_device> [ ,...n ]  [ [ MIRROR TO <backup_device> [ ,...n ] ] [ ...next-mirror ] ]      [ WITH      [ BLOCKSIZE = { blocksize | @blocksize_variable } ]      [ [ , ] BUFFERCOUNT = { buffercount | @buffercount_variable } ]      [ [ , ] { CHECKSUM | NO_CHECKSUM } ]     [ [ , ] { STOP_ON_ERROR | CONTINUE_AFTER_ERROR } ]     [ [ , ] DESCRIPTION = { ‘text‘ | @text_variable } ]      [ [ , ] EXPIREDATE = { date | @date_var }      | RETAINDAYS = { days | @days_var } ]      [ [ , ] PASSWORD = { password | @password_variable } ]      [ [ , ] { FORMAT | NOFORMAT } ]      [ [ , ] { INIT | NOINIT } ]      [ [ , ] { NOSKIP | SKIP } ]      [ [ , ] MAXTRANSFERSIZE = { maxtransfersize | @maxtransfersize_variable } ]     [ [ , ] MEDIADESCRIPTION = { ‘text‘ | @text_variable } ]      [ [ , ] MEDIANAME = { media_name | @media_name_variable } ]      [ [ , ] MEDIAPASSWORD = { mediapassword | @mediapassword_variable } ]      [ [ , ] NAME = { backup_set_name | @backup_set_name_var } ]      [ [ , ] NO_TRUNCATE ]      [ [ , ] { NORECOVERY | STANDBY = undo_file_name } ]      [ [ , ] { REWIND | NOREWIND } ]      [ [ , ] { UNLOAD | NOUNLOAD } ]      [ [ , ] RESTART ]      [ [ , ] STATS [ = percentage ] ]      [ [ , ] COPY_ONLY ]     ] }BACKUP LOG Northwind    TO 新备份设备18.4RESTORE HEADERONLY FROM <backup_device> [ WITH    [ { CHECKSUM | NO_CHECKSUM } ]   [ [ , ] { CONTINUE_AFTER_ERROR | STOP_ON_ERROR } ]   [ [ , ] FILE = backup_set_file_number ]    [ [ , ] MEDIANAME = { media_name | @media_name_variable } ]                --媒体文件名   [ [ , ] MEDIAPASSWORD = { mediapassword |                                  --媒体密码                    @mediapassword_variable } ]    [ [ , ] PASSWORD = { password | @password_variable } ]    [ [ , ] REWIND ]    [  [ , ] { UNLOAD | NOUNLOAD } ] ] [;]<backup_device> ::={    { logical_backup_device_name |            @logical_backup_device_name_var }   | { DISK | TAPE } = { ‘physical_backup_device_name‘ |              @physical_backup_device_name_var } } RESTORE HEADERONLY    FROM 新备份设备18.4.3RESTORE VERIFYONLYFROM <backup_device> [ ,...n ] [ WITH    [ { CHECKSUM | NO_CHECKSUM } ]   [ [ , ] { CONTINUE_AFTER_ERROR | STOP_ON_ERROR } ]   [ [ , ] FILE =backup_set_file_number ]    [ [ , ] LOADHISTORY ]    [ [ , ] MEDIANAME = { media_name | @media_name_variable } ]    [ [ , ] MEDIAPASSWORD = { mediapassword |                    @mediapassword_variable } ]    [ [ , ] MOVE ‘logical_file_name‘ TO ‘operating_system_file_name‘ ]            [ ,...n ]    [ [ , ] PASSWORD = { password | @password_variable } ]   [ [ , ] { REWIND | NOREWIND } ]    [ [ , ] STATS [ = percentage ] ]    [ [ , ] { UNLOAD | NOUNLOAD } ] ] [;]<backup_device> ::={    { logical_backup_device_name |            @logical_backup_device_name_var }   | { DISK | TAPE } = { ‘physical_backup_device_name‘ |              @physical_backup_device_name_var } } RESTORE VERIFYONLY    FROM 新备份设备    WITH DIFFERENTIALRESTORE VERIFYONLY    FROM 新备份设备    WITH FILE = 2RESTORE VERIFYONLY    FROM DISK=‘D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\backup.bak‘18.6.1RESTORE DATABASE { database_name | @database_name_var }                  --数据库名[ FROM <backup_device> [ ,...n ] ]                                                --备份设备[ WITH    [ { CHECKSUM | NO_CHECKSUM } ]                                        --是否校检和   [ [ , ] { CONTINUE_AFTER_ERROR | STOP_ON_ERROR } ]             --还原失败是否继续   [ [ , ] ENABLE_BROKER ]                                           --启动Service Broker   [ [ , ] ERROR_BROKER_CONVERSATIONS ]                              --对束所有会话   [ [ , ] FILE = { backup_set_file_number | @backup_set_file_number } ]        --用于还原的文件   [ [ , ] KEEP_REPLICATION ]                            --将复制设置为与日志传送一同使用   [ [ , ] MEDIANAME = { media_name | @media_name_variable } ]                    --媒体名   [ [ , ] MEDIAPASSWORD = { mediapassword |                                  --媒体密码                    @mediapassword_variable } ]    [ [ , ] MOVE ‘logical_file_name_in_backup‘ TO ‘operating_system_file_name‘ ]     --数据还原为                [ ,...n ]    [ [ , ] NEW_BROKER ]                                  --创建新的service_broker_guid值   [ [ , ] PASSWORD = { password | @password_variable } ]                     --备份集的密码   [ [ , ] { RECOVERY | NORECOVERY | STANDBY =                              --恢复模式          {standby_file_name | @standby_file_name_var }    } ]    [ [ , ] REPLACE ]                                                      --覆盖现有数据库   [ [ , ] RESTART ]                                            --重新启动被中断的还原操作   [ [ , ] RESTRICTED_USER ]                                      --限制访问还原的数据库   [ [ , ] { REWIND | NOREWIND } ]                                    --是否释放和重绕磁带   [ [ , ] { UNLOAD | NOUNLOAD } ]                                    --是否重绕并卸载磁带   [ [ , ] STATS [ = percentage ] ]                       --还原到其在指定的日期和时间时的状态   [ [ , ] { STOPAT = { date_time | @date_time_var }                  --还原到指定的日期和时间    |  STOPATMARK = { ‘mark_name‘ | ‘lsn:lsn_number‘ }    --恢复为已标记的事务或日志序列号              [ AFTER datetime ]     |  STOPBEFOREMARK = { ‘mark_name‘ | ‘lsn:lsn_number‘ }             [ AFTER datetime ]    } ] ][;]<backup_device> ::={    { logical_backup_device_name |            @logical_backup_device_name_var }   | { DISK | TAPE } = { ‘physical_backup_device_name‘ |              @physical_backup_device_name_var } }USE masterRESTORE DATABASE Northwind    FROM Northwind备份USE masterRESTORE DATABASE Northwind    FROM Northwind备份    WITH FILE = 6USE masterRESTORE DATABASE NorthwindFROM DISK=‘D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQLBackup\backup.bak‘18.6.2USE masterRESTORE DATABASE Northwind    FROM Northwind备份    WITH FILE = 1,NORECOVERYGORESTORE DATABASE Northwind    FROM Northwind备份    WITH FILE = 3GO18.6.3USE masterRESTORE DATABASE Northwind    FROM Northwind备份    WITH FILE = 1,NORECOVERYGORESTORE DATABASE Northwind    FROM Northwind备份    WITH FILE = 2GOUSE masterRESTORE DATABASE Northwind    FROM Northwind备份    WITH FILE = 1,NORECOVERYGORESTORE LOG Northwind    FROM Northwind备份    WITH FILE = 2GO18.6.4USE masterRESTORE DATABASE Northwind    FILEGROUP = ‘PRIMARY‘    FROM Northwind备份GORESTORE LOG Northwind    FROM Northwind备份    WITH FILE = 15GO18.6.5USE masterRESTORE DATABASE Northwind    FROM Northwind备份    WITH FILE = 17,NORECOVERYGORESTORE LOG Northwind    FROM Northwind备份    WITH FILE = 18,STOPAT = ‘2006-9-21 9:14:00‘GO18.6.6USE masterRESTORE DATABASE Northwind_test    FROM Northwind备份    WITH FILE = 17,    MOVE ‘Northwind_Data‘ TO ‘D:\Northwind_Data.MDF‘,    MOVE ‘Northwind_Log‘ TO ‘D:\Northwind_Log.LDF‘,    MOVE ‘Northwind自定义数据文件‘ TO ‘D:\Northwind自定义数据文件.NDF‘,    MOVE ‘Northwind自定义日志文件‘ TO ‘D:\Northwind自定义日志文件.LDF‘GO19.1.3USE modelCREATE TABLE ModelTable(    编号int,    姓名nvarchar(50))GOUSE masterCREATE DATABASE testON (    NAME=‘test‘,    FILENAME=‘D:\test.mdf‘)GOUSE testSELECT * FROM ModelTable19.1.5select * from sys.indexes20.4.9insert 类别 (类别名称) values (‘图书‘)exec sp_setapprole ‘myrole‘,‘123456‘insert 类别 (类别名称) values (‘图书‘)sp_setapprole存储过程的语法代码如下：sp_setapprole [ @rolename = ] ‘role‘,        [ @password = ] { encrypt N‘password‘ }     |        ‘password‘ [ , [ @encrypt = ] { ‘none‘ | ‘odbc‘ } ]        [ , [ @fCreateCookie = ] true | false ]    [ , [ @cookie = ] @cookie OUTPUT ]24.1.2xp_sendmail { [ @recipients= ] ‘recipients [ ;...n ]‘ }      [ ,[ @message= ] ‘message‘ ]      [ ,[ @query= ] ‘query‘ ]      [ ,[ @attachments= ] ‘attachments [ ;...n ]‘ ]      [ ,[ @copy_recipients= ] ‘copy_recipients [ ;...n ]‘     [ ,[ @blind_copy_recipients= ] ‘blind_copy_recipients [ ;...n ]‘     [ ,[ @subject= ] ‘subject‘ ]     [ ,[ @type= ] ‘type‘ ]      [ ,[ @attach_results= ] ‘attach_value‘ ]     [ ,[ @no_output= ] ‘output_value‘ ]      [ ,[ @no_header= ] ‘header_value‘ ]      [ ,[ @width= ] width ]      [ ,[ @separator= ] ‘separator‘ ]      [ ,[ @echo_error= ] ‘echo_value‘ ]      [ ,[ @set_user= ] ‘user‘ ]      [ ,[ @dbuse= ] ‘database‘ ]exec xp_sendmail ‘admin@ibucm.com‘,@query=‘select * from 类别‘,    @subject=‘所有的类别‘,@dbuse = ‘Northwind‘28.2.4USE NorthwindGOSELECT  类别ID,类别名称    FROM 类别FOR XML RAW, XMLDATA28.2.5USE NorthwindGOSELECT  类别ID AS "@类别编号",类别名称 AS "类别/@类别名称", 说明 AS "说明/text()"     FROM  类别FOR XML PATHUSE NorthwindGOSELECT  类别ID AS "@类别编号",类别名称 AS "类别名称",    说明 AS "说明/text()"     FROM  类别FOR XML PATH(‘类别‘)28.2.6USE NorthwindGOSELECT  类别ID AS "@类别编号",类别名称 AS "类别名称",    说明 AS "说明/text()"     FROM  类别FOR XML PATH(‘类别‘) ,ROOT(‘类别表‘)USE NorthwindGOSELECT  类别ID,类别名称    FROM 类别FOR XML RAW, XMLSCHEMA,ROOT(‘类别表‘)28.2.7OPENXML( idoc int [ in] , rowpattern nvarchar [ in ] , [ flags byte [ in ] ] ) [ WITH ( SchemaDeclaration | TableName ) ]--定义存储过程sp_xml_preparedocument使用的句柄DECLARE @handle int--定义xml类型的变量DECLARE @myXML xml--为变量赋值SET @myXML = (SELECT  类别ID AS "@类别编号",类别名称 AS "类别名称",    说明 AS "说明/text()"     FROM  类别FOR XML PATH(‘类别‘) ,ROOT(‘类别表‘))--准备处理XML文件EXEC sp_xml_preparedocument @handle OUTPUT, @myXMLSELECT * FROM OPENXML(@handle, N‘/类别表/类别/说明‘) --删除句柄EXEC sp_xml_removedocument @handle28.3.1CREATE TABLE 文档表(    文档编号int IDENTITY(1,1) PRIMARY KEY,    文档标题nvarchar(50),    文档内容xml)28.3.2DECLARE @myXML xmlSET @myXML = (SELECT  产品.产品ID,类别.类别名称,产品.产品名称,产品.单价,产品.库存量FROM 类别    JOIN  产品  ON 类别.类别ID = 产品.类别IDFOR XML RAW(‘产品‘),ELEMENTS)SELECT @myXMLDECLARE @myXML xmlSET @myXML = ‘ <产品名称>柠檬汁</产品>‘select @myXMLDECLARE @myXML xml28.3.3INSERT 文档表(文档标题,文档内容)    VALUES (‘隐型转换‘,‘<文档内容>该字段以隐形转换方式转为xml数据</文档内容>‘)INSERT 文档表(文档标题,文档内容)    VALUES (‘显式转换‘,        CAST(‘<文档内容>该字段以显式转换方式转为xml数据</文档内容>‘ as xml))INSERT 文档表(文档标题,文档内容)    VALUES (‘显式转换‘,        CONVERT(xml,‘<产品>  <产品ID>76</产品ID>  <类别名称>饮料</类别名称>  <说明>软饮料、咖啡、茶、啤酒和淡啤酒</说明>  <产品名称>柠檬汁</产品名称>  <单价>18.0000</单价>  <库存量>57</库存量></产品>‘))28.4.3CREATE [ PRIMARY ] XML INDEX index_name     ON <object> ( xml_column_name )    [ USING XML INDEX xml_index_name         [ FOR { VALUE | PATH | PROPERTY } ] ]    [ WITH ( <xml_index_option> [ ,...n ] ) ][ ; ]<object> ::={    [ database_name. [ schema_name ] . | schema_name. ]         table_name}<xml_index_option> ::={     PAD_INDEX  = { ON | OFF }  | FILLFACTOR = fillfactor  | SORT_IN_TEMPDB = { ON | OFF }  | STATISTICS_NORECOMPUTE = { ON | OFF }  | DROP_EXISTING = { ON | OFF }  | ALLOW_ROW_LOCKS = { ON | OFF }  | ALLOW_PAGE_LOCKS = { ON | OFF }  | MAXDOP = max_degree_of_parallelism}CREATE PRIMARY XML INDEX XmlPrimaryIndex    ON 文档表(文档内容)CREATE XML INDEX XmlValueIndex    ON 文档表(文档内容)    USING XML INDEX XmlPrimaryIndex FOR VALUE28.5.1DECLARE @myXML xmlSET @myXML = ‘<类别表>  <类别 类别编号="1">    <类别名称>饮料</类别名称>    <说明>软饮料、咖啡、茶、啤酒和淡啤酒</说明>  </类别>  <类别 类别编号="2">    <类别名称>调味品</类别名称>    <说明>香甜可口的果酱、调料、酱汁和调味品</说明>  </类别>  <类别 类别编号="3">    <类别名称>点心</类别名称>    <说明>甜点、糖和甜面包</说明>  </类别>  </类别表>‘select @myXML.query(‘/类别表/类别‘)28.5.2DECLARE @myXML xmlDECLARE @Name varchar(100)SET @myXML = ‘<类别表>  <类别 类别编号="1">    <类别名称>饮料</类别名称>    <说明>软饮料、咖啡、茶、啤酒和淡啤酒</说明>  </类别>  <类别 类别编号="2">    <类别名称>调味品</类别名称>    <说明>香甜可口的果酱、调料、酱汁和调味品</说明>  </类别>  <类别 类别编号="3">    <类别名称>点心</类别名称>    <说明>甜点、糖和甜面包</说明>  </类别>  </类别表>‘--获取第一个/类别表/类别/类别名称的值SET @Name = @myXML.value(‘(/类别表/类别/类别名称)[1]‘,‘nvarchar(100)‘)SELECT @Name--获取第二个/类别表/类别的类别编号值SET @Name = @myXML.value(‘(/类别表/类别/@类别编号)[2]‘,‘nvarchar(100)‘)SELECT @Name28.5.3exist (XQuery)DECLARE @myXML xmlDECLARE @IfExist bitSET @myXML = ‘<类别表>  <类别 类别编号="1">    <类别名称>饮料</类别名称>    <说明>软饮料、咖啡、茶、啤酒和淡啤酒</说明>  </类别>  <类别 类别编号="2">    <类别名称>调味品</类别名称>    <说明>香甜可口的果酱、调料、酱汁和调味品</说明>  </类别>  <类别 类别编号="3">    <类别名称>点心</类别名称>    <说明>甜点、糖和甜面包</说明>  </类别>  </类别表>‘--查看是否存在类别名称为“饮料”的节点SET @IfExist = @myXML.exist(‘/类别表/类别/类别名称[text()="饮料"]‘)SELECT @IfExist--查看是否存在类别编号为“”的节点SET @IfExist = @myXML.exist(‘/类别表/类别/@类别编号=1‘)SELECT @IfExist--查看是否存在类别名称为“图书”的节点SET @IfExist = @myXML.exist(‘/类别表/类别/类别名称[text()="图书"]‘)SELECT @IfExist28.5.4modify (XML_DML)insert       Expression1 (                 {as first | as last} into | after | before                                    Expression2                )DECLARE @myXML xmlSET @myXML = ‘<类别表>  <类别 类别编号="1">    <类别名称>饮料</类别名称>    <说明>软饮料、咖啡、茶、啤酒和淡啤酒</说明>  </类别>  <类别 类别编号="2">    <类别名称>调味品</类别名称>    <说明>香甜可口的果酱、调料、酱汁和调味品</说明>  </类别>  <类别 类别编号="3">    <类别名称>点心</类别名称>    <说明>甜点、糖和甜面包</说明>  </类别>  </类别表>‘SET @myXML.modify(‘insert<类别 类别编号="4">    <类别名称>图书</类别名称></类别> into (/类别表)[1]‘)SET @myXML.modify(‘insert<产品>该类别的产品</产品> into (/类别表/类别)[2]‘)SELECT @myXML28.5.5replace value of       Expression1 with      Expression2DECLARE @myXML xmlSET @myXML = ‘<类别表>  <类别 类别编号="1">    <类别名称>饮料</类别名称>    <说明>软饮料、咖啡、茶、啤酒和淡啤酒</说明>  </类别>  <类别 类别编号="2">    <类别名称>调味品</类别名称>    <说明>香甜可口的果酱、调料、酱汁和调味品</说明>  </类别>  <类别 类别编号="3">    <类别名称>点心</类别名称>    <说明>甜点、糖和甜面包</说明>  </类别>  </类别表>‘SET @myXML.modify(‘replace value of(/类别表/类别/类别名称/text())[1] with "图书"‘)SET @myXML.modify(‘replace value of(/类别表/类别[2]/类别名称/text())[1] with "电器"‘)SET @myXML.modify(‘replace value of(/类别表/类别/@类别编号)[2] with "4"‘)SELECT @myXMLDECLARE @myXML xmlSET @myXML = ‘<类别表>  <类别 类别编号="1">    <类别名称>饮料</类别名称>    <说明>软饮料、咖啡、茶、啤酒和淡啤酒</说明>  </类别>  <类别 类别编号="2">    <类别名称>调味品</类别名称>    <说明>香甜可口的果酱、调料、酱汁和调味品</说明>  </类别>  <类别 类别编号="3">    <类别名称>点心</类别名称>    <说明>甜点、糖和甜面包</说明>  </类别>  </类别表>‘SET @myXML.modify(‘replace value of    (/类别表/类别/类别名称/text())[1] with     (if ((/类别表/类别/@类别编号)[1]>2) then        "编号大于2"    else        "编号小于2"    )‘)SET @myXML.modify(‘replace value of    (/类别表/类别/类别名称/text())[3] with     (if ((/类别表/类别/@类别编号)[3]>2) then        "编号大于2"    else        "编号小于2"    )‘)SELECT @myXML28.5.6DECLARE @myXML xmlSET @myXML = ‘<类别表>  <类别 类别编号="1">    <类别名称>饮料</类别名称>    <说明>软饮料、咖啡、茶、啤酒和淡啤酒</说明>  </类别>  <类别 类别编号="2">    <类别名称>调味品</类别名称>    <说明>香甜可口的果酱、调料、酱汁和调味品</说明>  </类别>  <类别 类别编号="3">    <类别名称>点心</类别名称>    <说明>甜点、糖和甜面包</说明>  </类别>  </类别表>‘SET @myXML.modify(‘delete /类别表/类别[1]/说明‘)SET @myXML.modify(‘delete /类别表/类别[2]/@类别编号‘)SET @myXML.modify(‘delete /类别表/类别[3]‘)SELECT @myXML29.3.1using System;using System.Data;using System.Configuration;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControls;using System.Data.Odbc;public partial class _Default : System.Web.UI.Page {    protected void Page_Load(object sender, EventArgs e)    {        //设置连接ODBC接口        OdbcConnection myConn = new OdbcConnection("DSN=myODBC;UID=sa;PWD=sa");        //打开数据库        myConn.Open();        //在数据库中查询数据，并绑定到GridView        OdbcDataAdapter myAdapter = new OdbcDataAdapter("select * from 类别",myConn);        DataSet ds = new DataSet();        myAdapter.Fill(ds);        this.GridView1.DataSource = ds.Tables[0].DefaultView;        this.GridView1.DataBind();        //关闭数据库        myConn.Close();    }}29.3.2using System;using System.Data;using System.Configuration;using System.Collections;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControls;using System.Data.OleDb;public partial class OleDb : System.Web.UI.Page{    protected void Page_Load(object sender, EventArgs e)    {        //设置连接ODBC接口        OleDbConnection myConn = new OleDbConnection("Provider=SQLOLEDB;Data Source=(local);initial catalog=Northwind;user id=sa;PWD=sa");        //打开数据库        myConn.Open();        //在数据库中查询数据，并绑定到GridView        OleDbDataAdapter myAdapter = new OleDbDataAdapter("select * from 类别", myConn);        DataSet ds = new DataSet();        myAdapter.Fill(ds);        this.GridView1.DataSource = ds.Tables[0].DefaultView;        this.GridView1.DataBind();        //关闭数据库        myConn.Close();    }}30.1.1ALTER DATABASE database_name SET COMPATIBILITY_LEVEL = { 80 | 90 | 100 }      --设置兼容级别，值为80，90，100之一--查询所有数据库的兼容级别select name,compatibility_level from sys.databases30.1.4ALTER DATABASE  SampleDB               --修改数据库SampleDB的兼容级别SET COMPATIBILITY_LEVEL = 90;            --数据库SampleDB的兼容级别设置为90 DECLARE @id int;SELECT @id = id FROM test                   --对变量进行赋值UNION ALLSELECT @id = id FROM TestTable;SELECT @id;                                 --输出@id的值SELECT DATEPART (year, ‘2009/04-30‘)      --获取年份信息，但该日期不是通常意义的有效日期30.1.5ALTER DATABASE  SampleDB               --修改数据库SampleDB的兼容级别SET COMPATIBILITY_LEVEL = 90;            --数据库SampleDB的兼容级别设置为90 30.1.6CREATE TABLE TestTable     --创建表TestTable(id int, age int);SELECT id, age AS id      --查询表TestTable，表中列age的别名设置为id，与id列同名FROM TestTableORDER BY id;             --采用order byALTER DATABASE SampleDB SET COMPATIBILITY_LEVEL = 80;  --修改兼容级别为80SELECT id, age AS id      --查询表TestTable，表中列age的别名设置为id，与id列同名FROM TestTableORDER BY id;             --采用order byALTER DATABASE SampleDB SET COMPATIBILITY_LEVEL = 100;  --修改兼容级别为100SELECT id, age AS ages     --查询表TestTable，表中列age的别名设置为ages，与id列名不同FROM TestTableORDER BY id;             --采用order byALTER DATABASE SampleDB SET COMPATIBILITY_LEVEL = 80  --修改兼容级别为80SELECT id, age AS ages     --查询表TestTable，表中列age的别名设置为ages，与id列名不同FROM TestTableORDER BY id;             --Order by在列别名前使用表前缀 ALTER DATABASE SampleDB SET COMPATIBILITY_LEVEL = 100;  --修改兼容级别为100SELECT id as userid    --查询表TestTable，表中列id的别名设置为useridFROM TestTableORDER BY TestTable.userid;             --采用order byALTER DATABASE SampleDB SET COMPATIBILITY_LEVEL = 80  --修改兼容级别为80SELECT id as userid    --查询表TestTable，表中列id的别名设置为useridFROM TestTableORDER BY TestTable.userid;             --Order by在列别名前使用表前缀ALTER DATABASE SampleDB SET COMPATIBILITY_LEVEL = 100  --修改兼容级别为100SELECT id as userid    --查询表TestTable，表中列id的别名设置为useridFROM TestTableORDER BY userid;             --order by中的列别名的表前缀已经去掉ALTER DATABASE SampleDB SET COMPATIBILITY_LEVEL = 80  --修改兼容级别为80SELECT id as userid    --查询表TestTable，表中列id的别名设置为useridFROM TestTableORDER BY userid;             --order by中的列别名的表前缀已经去掉 30.2.1expression += expressionDECLARE @var int = 1;    --声明变量var,并将其值设置为1SET @var += 2 ;           --对变量var和数值2使用+=运算符30.2.2expression -= expressionDECLARE @var int = 2;    --声明变量var,并将其值设置为2SET @var *= 2 ;           --对变量var和数值2使用*=运算符（变量var的值被设置为4）DECLARE @var int = 4;    --声明变量var,并将其值设置为4SET @var /= 2 ;           --对变量var和数值2使用/=运算符（变量var的值被设置为2）DECLARE @var int = 7;    --声明变量var,并将其值设置为7SET @var += 2 ;           --对变量var和数值2使用%=运算符（变量var的值被设置为1）DECLARE @var int = 9;    --声明变量var,并将其值设置为9SET @var &= 13 ;           --对变量var和数值13使用&=运算符（变量var的值被设置为9）DECLARE @var int = 12;    --声明变量var,并将其值设置为12SET @var ^= 6 ;           --对变量var和数值6使用^=运算符（变量var的值被设置为10）DECLARE @var int = 3;    --声明变量var,并将其值设置为3SET @var |= 2 ;           --对变量var和数值2使用|=运算符（变量var的值被设置为3）30.3.1CONVERT ( data_type [ ( length ) ] , expression [ , style ] )30.3.2--将二进制值转换为ASCIISELECT CONVERT(char(6), 0x53616D706C65, 0) AS ‘转换结果‘  --Style为1时将截断SELECT CONVERT(char(12), 0x4E616d65, 1) AS ‘转换结果‘      --Style为2时将不截断，且转换后的字符值没有前缀0xSELECT CONVERT(char(12), 0x4E616d65, 2) AS ‘转换结果‘ --将字符值转换为二进制值     SELECT CONVERT(binary(6), ‘Sample‘, 0) AS ‘转换结果‘--Style为1时，字符值转换为二进制值时，字符值需要以0x为前缀开始SELECT CONVERT(binary(4), ‘0x4E616D65‘, 1) AS ‘转换结果‘-Style为2时，字符值转换为二进制值时，字符值不能以0x为前缀开始SELECT CONVERT(binary(4), ‘4E616D65‘, 2) AS ‘转换结果‘30.4.3SELECT DATEPART(year, ‘12:00:00.123‘)  --缺了年份数据，所以返回1900    ,DATEPART(month, ‘12:00:00.123‘)    --缺了月份数据，所以返回1    ,DATEPART(day, ‘12:00:00.123‘)      --缺了天的数据，所以返回1    ,DATEPART(dayofyear, ‘12:00:30.123‘) --缺了日期数据，所以返回1    ,DATEPART(weekday, ‘12:00:00.123‘);  --缺了日期数据，所以返回2SELECT DATEPART(hour, ‘2009-04-12‘)   --缺了时间数据，小时的返回值为0    ,DATEPART(minute, ‘2009-04-12‘)     --缺了时间数据，分钟的返回值为0,DATEPART(second, ‘2009-04-12‘);    --缺了时间数据，秒的返回值为030.5.1GROUP BY GROUPING SETS ( <grouping set list> )<grouping set list> ::=                               --分组操作列表    <grouping set> [ ,...n ]<grouping set> ::=                                  --分组操作    <grand total>    | <grouping set item>    | ( <grouping set item list> )<grouping set item> ::=                             --分组操作的具体信息     <simple group by item>    | <rollup spec>    | <cube spec><grouping set item list> ::=<grouping set item> [ ,...n ]<simple group by item> ::=                            <column_expression>                        --针对其执行分组操作的表达式30.5.2GROUP BY col1 (col2,..., coln)            --不正确的用法GROUP BY GROUPING SETS (col1, (col2, ..., coln))?    不能在GROUPING SETS内部使用GROUPING SETS。GROUP BY GROUPING SETS (col1, GROUPING SETS (col2, col3))30.5.3SELECT  id, age,COUNT(id) as count   FROM         Testgroup by GROUPING SETS(id,age)   --使用Grouping Sets进行聚集，一次使用id一次使用age30.6.1[ WITH <common_table_expression> [,...n] ]        --指定在MERGE语句内定义的临时表或视图MERGE         [ TOP ( expression ) [ PERCENT ] ]        --指定源表的行数        [ INTO ] target_table [ WITH ( <merge_hint> ) ] [ [ AS ] table_alias ]  --指定目标表        USING <table_source>                   --指定源表        ON <merge_search_condition>            --指定搜索条件        [ WHEN MATCHED [ AND <clause_search_condition> ]              THEN <merge_matched> ]   --目标表中与源表匹配的行的操作，包括更新和删除        [ WHEN NOT MATCHED [ BY TARGET ] [ AND <clause_search_condition> ]            THEN <merge_not_matched> ]   --将源表中与目标表不匹配的行插入到目标表中，        [ WHEN NOT MATCHED BY SOURCE [ AND <clause_search_condition> ]            THEN <merge_matched> ]  --目标表中与源表匹配的行的操作，包括更新和删除        [ <output_clause> ]   --输出行的信息        [ OPTION ( <query_hint> [ ,...n ] ) ]    --指定使用优化器;<merge_hint>::={    { [ <table_hint_limited> [ ,...n ] ]    [ [ , ] INDEX ( index_val [ ,...n ] ) ] }}<table_source> ::= {        table_or_view_name [ [ AS ] table_alias ] [ <tablesample_clause> ]             [ WITH ( table_hint [ [ , ]...n ] ) ]     | rowset_function [ [ AS ] table_alias ]             [ ( bulk_column_alias [ ,...n ] ) ]     | user_defined_function [ [ AS ] table_alias ]    | OPENXML <openxml_clause>     | derived_table [ AS ] table_alias [ ( column_alias [ ,...n ] ) ]     | <joined_table>     | <pivoted_table>     | <unpivoted_table> }<merge_search_condition> ::=    <search_condition><merge_matched>::=    { UPDATE SET <set_clause> | DELETE }<set_clause>::=SET    { column_name = { expression | DEFAULT | NULL }  | { udt_column_name.{ { property_name = expression                                              | field_name = expression }                                              | method_name ( argument [ ,...n ] ) }     }  | column_name { .WRITE ( expression , @Offset , @Length ) }  | @variable = expression  | @variable = column = expression  | column_name { += | -= | *= | /= | %= | &= | ^= | |= } expression  | @variable { += | -= | *= | /= | %= | &= | ^= | |= } expression  | @variable = column { += | -= | *= | /= | %= | &= | ^= | |= } expression  } [ ,...n ] <merge_not_matched>::={        INSERT [ ( column_list ) ]             { VALUES ( values_list )            | DEFAULT VALUES }}<clause_search_condition> ::=    <search_condition><search condition> ::=    { [ NOT ] <predicate> | ( <search_condition> ) }     [ { AND | OR } [ NOT ] { <predicate> | ( <search_condition> ) } ] [ ,...n ] <predicate> ::=     { expression { = | < > | ! = | > | > = | ! > | < | < = | ! < } expression     | string_expression [ NOT ] LIKE string_expression   [ ESCAPE ‘escape_character‘ ]     | expression [ NOT ] BETWEEN expression AND expression     | expression IS [ NOT ] NULL     | CONTAINS     ( { column | * } , ‘< contains_search_condition >‘ )     | FREETEXT ( { column | * } , ‘freetext_string‘ )     | expression [ NOT ] IN ( subquery | expression [ ,...n ] )     | expression { = | < > | ! = | > | > = | ! > | < | < = | ! < }   { ALL | SOME | ANY} ( subquery )     | EXISTS ( subquery ) } <output_clause>::={    [ OUTPUT <dml_select_list> ]        [ OUTPUT <dml_select_list> INTO { @table_variable | output_table }            [ (column_list) ] ]}<dml_select_list>::=        { <column_name> | scalar_expression }                 [ [AS] column_alias_identifier ] [ ,...n ]<column_name> ::=        { DELETED | INSERTED | from_table_name } . { * | column_name }      | $action30.6.2Merge test   --确定目标表using (select * from testtable where id>3) as source(id,age) on test.id=source.id --确定使用的结果集WHEN MATCHED AND test.age>10   --目标表与结果集匹配的行且该行满足搜索条件，那么该行将被删除 Then delete when not matched                  --当结果集的行在目标表中找不到匹配时将该行插入到表中 then insert values(id,age) output Inserted.id,Inserted.age,Deleted.id as DelId,Deleted.age as DelAge;30.7CREATE VIEW [dbo].[vUser]                                  --创建视图vUserASSELECT     dbo.Users.id, dbo.Users.name, dbo.Test.age      FROM         dbo.Users INNER JOIN                        --该视图依赖于表Users和Test                      dbo.Test ON dbo.Users.id = dbo.Test.idSELECT *FROM sys.sql_expression_dependencies AS sed                 --从视图获取依赖关系信息INNER JOIN sys.objects AS o ON sed.referencing_id = o.object_id  --获取引用实体的详情WHERE referencing_id = OBJECT_ID(N‘vUser‘);                  --获取视图vUser的依赖关系30.8.1CREATE TYPE dbo.TableValue AS TABLE  --创建用户表类型TableValue(    c1 int NOT NULL,                         --该表类型有两个字段，这两个字段的类型为int    c2 int NULL,     PRIMARY KEY (c1))GOdeclare @tv TableValue           --定义表类型变量INSERT INTO @tv(c1,c2)          --向表变量中插入多行数据VALUES (1,2), (2,3), (3,4), (4,5)30.8.2declare @tv TableValue           --声明表类型变量                INSERT INTO @tv(c1,c2)         --为Insert语句准备数据，即向表类型变量中插入数据VALUES (1,2), (2,3), (3,4), (4,5)--将表变量中的数据插入到表Test中insert into Test(id,age) output inserted.id,inserted.age select c1,c2 from @tv  30.8.3CREATE PROCEDURE sp_TableTypePro                   --定义存储过程    @tv TableValue                                  --使用表类型作为参数，但不是READONLYASBEGIN    insert into test(id,age) select c1,c2 from @tv    insert into @tv(c1,c2) values(71,12)         --在表类型为READONLY时不能对表值参数插入数据ENDGOCREATE PROCEDURE sp_TableTypePro    @tv TableValue READONLYASBEGIN        SET NOCOUNT ON;    insert into test(id,age) select c1,c2 from @tvENDGO30.9--将多行数据插入到表中INSERT INTO <objetc> [(column_list)] values  ([column_value])[,(column_value)]参数说明：?    <object>：要插入数据的表明或者视图名。?    column_list：要插入数据的表的列名。?    column_value：要插入到表中的一行或多行数据。INSERT INTO [SampleDB].[dbo].[Test]           ([id]           ,[age]) output INSERTED.id,INSERTED.age      VALUES           (12,34),(56,78),(90,10)   --往表Test中插入的三条数据，该数据的顺序与列的列表一致31.1.1sp_estimate_data_compression_savings       [ @schema_name = ] ‘schema_name‘                  --架构名称     , [ @object_name = ] ‘object_name‘                     --采用数据压缩的对象名    , [@index_id = ] index_id                                --索引ID     , [@partition_number = ] partition_number     , [@data_compression = ] ‘data_compression‘ [;]USE SampleDBGO--执行存储过程，预估表Users采用行压缩后的占用空间EXEC sp_estimate_data_compression_savings NULL, ‘Users‘, NULL, NULL, ‘ROW‘ ;GOCREATE TABLE     [ database_name . [ schema_name ] . | schema_name . ] table_name         ( { <column_definition> | <computed_column_definition>                 | <column_set_definition> }        [ <table_constraint> ] [ ,...n ] )     [ ON { partition_scheme_name ( partition_column_name ) | filegroup         | "default" } ]     [ { TEXTIMAGE_ON { filegroup | "default" } ]     [ FILESTREAM_ON { partition_scheme_name | filegroup         | "default" } ]                                   --创建数据库表    [ WITH ( DATA_COMPRESSION = ROW|PAGE]       --设置数据表使用的压缩方式[ ; ]ALTER TABLE [ database_name . [ schema_name ] . | schema_name . ] table_name REBUILD WITH (DATA_COMPRESSION = ROW|PAGE); --修改现有的表以启用压缩GO--创建使用行压缩的表CREATE TABLE Customer  (id int, name nvarchar(50) )WITH (DATA_COMPRESSION = ROW); --指明创建的表Customer采用行压缩GO--创建使用页压缩的表CREATE TABLE Products(id int, name nvarchar(50) ,Price money)WITH (DATA_COMPRESSION = PAGE); --指明创建的表Products采用页压缩GO对现有的表【Users】启用数据压缩功能，其代码如下:--使User表启用页压缩ALTER TABLE Users REBUILD WITH (DATA_COMPRESSION = PAGE); --修改现有的表Users以启用页压缩GO--创建行数据压缩索引CREATE NONCLUSTERED INDEX IX_INDEX_1     ON Users (Email) WITH ( DATA_COMPRESSION = ROW ) ; GO31.1.2--设置文件流访问级别为已启用完全访问EXEC sp_configure filestream_access_level, 2  RECONFIGURECREATE TABLE Pictures(--Id为ROWGUIDCOL    [Id] [uniqueidentifier] ROWGUIDCOL NOT NULL UNIQUE,     [PictureName] NVARCHAR(50) UNIQUE,--将列Picture设置为使用FILESTREAM    [Picture] VARBINARY(MAX) FILESTREAM NULL)GOINSERT INTO Pictures    VALUES (newid (), ‘测试图片2‘,     -- 向FILESTREAM列插入数据      CAST (‘测试图片2‘ as varbinary(max)));GO-- 向表中插入数据，其中Picture列为NULLINSERT INTO Pictures    VALUES (newid (), ‘测试图片‘, NULL);GO--获取列Picture的文件路径SELECT     TOP (200) Id, Picture.PathName() AS PathNameFROM         Pictures31.1.3CREATE TABLE [User]  ‘--创建表(ID int PRIMARY KEY,Title varchar(200) NOT NULL,FirstName varchar(20) SPARSE NULL, ‘--指定该列为稀疏列LastName smallint SPARSE NULL, ‘--指定该列为稀疏列Sex bit,Telephone varchar(15),Comments varchar(1000) SPARSE NULL ‘--指定该列为稀疏列)GOALTER TABLE [User] ‘--修改表(ID int PRIMARY KEY,Title varchar(200) NOT NULL,FirstName varchar(20) SPARSE NULL, ‘--指定该列为稀疏列LastName smallint SPARSE NULL, default 0 ‘--指定该列有默认值，不能为稀疏列Sex bit,Telephone varchar(15),Comments ntext SPARSE NULL ‘--不能指定该列为稀疏列，数据类型不对)GO‘--插入新的数据--‘insert into [User](ID,Title,Sex,Telephone) values(1002,‘admin‘,1,‘12345678‘)查询时选择表中的所有列，返回的是普通的结果集。‘--查询User表所有的所有列--‘SELECT  * FROM [SampleDB].[dbo].[User]CREATE TABLE[dbo].[Customers] ( [Id]int PRIMARY KEY, [FirstName] varchar(50) NOT NULL,[LastName] varchar(50) NOT NULL, [Gender] bit SPARSE NULL,[Telephone]varchar(15)SPARSE NULL, ‘--使用稀疏列[MonthlyIncome] money SPARSE NULL, ‘--使用稀疏列[Comments]varchar(1000)SPARSE NULL , ‘--使用稀疏列[AllSparseColumns] xml COLUMN_SET FOR ALL_SPARSE_COLUMNS ‘--创建列集)INSERT INTO [SampleDB].[dbo].[Customers]           ([Id]           ,[FirstName]           ,[LastName]           ,[Gender]           ,[Comments]           )     VALUES           (1211           ,‘admin‘           ,‘admin‘           ,1  ‘--对稀疏列Gender插入值1--‘           ,‘大家好‘ ‘--对稀疏列Comments插入值--‘                  )GOINSERT INTO [SampleDB].[dbo].[Customers]           ([Id]           ,[FirstName]           ,[LastName]             ,AllSparseColumns                    )     VALUES           (125           ,‘admin‘           ,‘admin‘‘--使用列集对稀疏列的数据进行操作，形式为XML数据--‘           ,‘<Gender>0</Gender><Comments>OK</Comments>‘                      )GO‘-- 查询列集结果--‘SELECT *  FROM [SampleDB].[dbo].[Customers] UPDATE [SampleDB].[dbo].[Customers]   SET [AllSparseColumns] = ‘<Gender>0</Gender><Comments>Good</Comments>‘ WHERE [Id]=125GOCREATE TABLE Category(--使用HierarchyID数据类型，并使用深度优先索引   CategoryNode hierarchyid PRIMARY KEY CLUSTERED, --使用GetLevel()建立广度优先索引    CategoryNodeLevel AS CategoryNode.GetLevel(),   CategoryName varchar(20) NOT NULL,) ;INSERT INTO [SampleDB].[dbo].[Category]           ([CategoryNode]           ,[CategoryName])     VALUES           (hierarchyid::GetRoot()     --表示插入数据的位置位于层次化结构的根       , ‘数码产品‘           )GOGetDescendant(child1,child2)DECLARE @root hierarchyid SELECT @root = hierarchyid::GetRoot()  --获取顶级分类的hierarchyid值FROM CategoryINSERT INTO [SampleDB].[dbo].[Category]           ([CategoryNode]           ,[CategoryName])     VALUES           (@root .GetDescendant(NULL, NULL),  --获取子分类的hierarchyid值            ‘MP3‘           )GODECLARE @parent hierarchyid , @maxl hierarchyid--设置@parent为根SELECT @parent = [CategoryNode] FROM Category where CategoryNode=‘/‘--获取@parent的最大子级SELECT @maxl = max([CategoryNode]) FROM Category WHERE [CategoryNode].GetAncestor(1) =@parent INSERT INTO [SampleDB].[dbo].[Category]           ([CategoryNode]           ,[CategoryName])     VALUES           (@parent.GetDescendant(maxl,NULL), ‘MP4‘           )GO在代码中的“[CategoryNode].GetAncestor(1) =@parent”表示其父级为@parent的所有节点。该代码使用的函数GetAncestor()。该函数语法如下：Object.GetAncestor（n）31.2.1parent. IsDescendantOf ( child )DECLARE @CurrentCategoryNode hierarchyid --表示当前的分类SELECT @CurrentCategoryNode = [CategoryNode]FROM CategoryWHERE [CategoryName] = ‘MP3‘ ; --设置当前的分类为"MP3"SELECT [CategoryNode].ToString(), --显示层次关系[CategoryNodeLevel],[CategoryName] FROM CategoryWHERE [CategoryNode].IsDescendantOf(@CurrentCategoryNode) = 1 ; --判断是否是当前分类的子级--表示当前的分类DECLARE @CurrentCategoryNode hierarchyid--设置当前的分类为"MP3"SELECT @CurrentCategoryNode = [CategoryNode] FROM Category WHERE [CategoryName] = ‘MP3‘ ;SELECT [CategoryNode].ToString() as CategoryNode,[CategoryNodeLevel],[CategoryName]FROM CategoryWHERE [CategoryNode].GetAncestor(1) = @CurrentCategoryNode ; --返回父级为“MP3”的所有直接子级。DECLARE @CurrentCategoryNode hierarchyidSELECT @CurrentCategoryNode = [CategoryNode]FROM CategoryWHERE [CategoryName] = ‘iPod‘ ;       --设置当前的分类为"iPod"print @CurrentCategoryNode.ToString()SELECT [CategoryNode].ToString() as CategoryNode,[CategoryNodeLevel],[CategoryName]FROM CategoryWHERE @CurrentCategoryNode.IsDescendantOf([CategoryNode]) = 1 ; --判断是否为“iPod”的父级31.2.2SWITCHOFFSET(Datetimeoffset,时区)--将当前系统日期转换为9时区的时间SELECT SWITCHOFFSET(SYSDATETIMEOFFSET(), ‘+09:00‘)DECLARE @date1 dateDECLARE @datetime1 datetimeSELECT @date1 = ‘2009-1-1‘SELECT @datetime1 = @date1    --执行隐式数据转换SELECT @date1 , @datetime1 DECLARE @testdate dateDECLARE @testdate1 dateDECLARE @testsmalldatetime smalldatetimeDECLARE @testsmalldatetime1 smalldatetimeSELECT @testdate = ‘1810-10-25‘SELECT @testdate1 = ‘1900-10-25‘SELECT @testsmalldatetime = @testdate    --执行数据转换SELECT @testsmalldatetime1 = @testdate1  --超出smalldatetime的范围，引发错误。SELECT @testdate ,@testdate1, @testsmalldatetime,@testsmalldatetime1--声明变量DECLARE @testdate dateDECLARE @testdatetimeoffset datetimeoffset(3)DECLARE @testdatetime2 datetime2(3)SELECT @testdate = ‘2009-1-1‘SELECT @testdatetimeoffset = @testdate      --date类型转换为datetimeoffset类型SELECT @testdatetime2 = @testdate          --date类型转换为datetime2类型SELECT @testdate , @testdatetimeoffset ,@testdate, @testdatetime2--声明数据类型DECLARE @timeTo time(3)DECLARE @timeFrom time(4)SELECT @timeFrom = ‘12:00:00.1234‘SELECT @timeTo = @TimeFrom  --将time(4)值转换为time(3)值SELECT @timeTo AS ‘time(3)‘, @timeFrom AS ‘time(4)‘--声明变量DECLARE @time time(4)DECLARE @datetime datetimeSELECT @time = ‘12:00:00.1234‘SELECT @datetime = @time      --执行类型转换，秒的小数部分将被截断为3位SELECT @time AS ‘@time‘, @datetime AS ‘@datetime‘DECLARE @time time(4)DECLARE @smalldatetime smalldatetimeSELECT @time = ‘12:15:12.1234‘SELECT @smalldatetime = @time               --执行类型转换，秒及其小数部分设置为0SELECT @time AS ‘@time‘, @smalldatetime AS ‘@smalldatetime‘DECLARE @time time(4)DECLARE @datetimeoffset datetimeoffset(3)SELECT @time = ‘12:00:00.1234‘SELECT @datetimeoffset = @time          --执行类型转换，时区将设置为0时区SELECT @time AS ‘@time‘, @datetimeoffset AS ‘@datetimeoffset‘31.2.3--使用geometry::STGeomFromText创建点实例geometry::STGeomFromText(‘POINT (X Y Z M)‘, SRID);DECLARE @point geometry;SET @point = geometry::STGeomFromText(‘POINT (1 1)‘, 0);DECLARE @point geometry;SET @point = geometry::STGeomFromText(‘POINT (1 1 1 1)‘,0);DECLARE @point geometry;SET @point = geometry::STGeomFromText(‘POINT (1 1 1 1)‘);SELECT @point.STX;     --点的X值SELECT @point.STY;     --点的Y值SELECT @point.Z;        --点的Z值SELECT @point.M;        --点的M值--使用geometry::STGeomFromText创建点集实例geometry::STGeomFromText(‘MULTIPOINT((X Y Z M), (X Y Z M),...)‘, SRID);SET @multiPoint = geometry::STGeomFromText(‘MULTIPOINT((1 1), (1 1))‘,1);SELECT @geometry.STGeometryN(1).STAsText();     --获取点集中第一个点的属性 DECLARE @linestring geometry;SET @linestring = geometry::STGeomFromText(‘LINESTRING(1 1, 2 4, 5 9)‘,0);geometry::STGeomFromText(‘LINESTRING((X Y Z M,X Y Z M,...), (X Y Z M,X Y Z M,...),...)‘,SRID);DECLARE @multiLine geometry;SET @multiLine = geometry::Parse(‘MULTILINESTRING((0 2, 1 1), (1 0, 1 1))‘);geometry::Parse(‘MULTILINESTRING((X Y Z M,X Y Z M,...), (X Y Z M,X Y Z M,...),...)‘,SRID);CREATE TABLE [dbo].[GeometrySample](    [id] [int] IDENTITY(1,1) NOT NULL,    [GeometryData] [geometry] NULL,  --使用geometry 类型--获取开放地理空间联盟 (OGC) 熟知文本 (WKT) 表示形式    [GeomCol2]  AS ([GeometryData].[STAsText]()) ) ON [PRIMARY]INSERT INTO GeometrySample (GeometryData)--插入线性数据VALUES (geometry::STGeomFromText(‘LINESTRING (1 1, 2 4, 5 10)‘, 0));CREATE TABLE GeographySample    ( id int IDENTITY (1,1),    Geog1 geography, --使用geography类型--获取开放地理空间联盟 (OGC) 熟知文本 (WKT) 表示形式    Geog2 AS Geog1.STAsText() );GOINSERT INTO GeographySample (Geog1)VALUES (geography::STGeomFromText(‘LINESTRING(-122.360 47.656, -122.343 47.656)‘, 4326));INSERT INTO GeographySample (Geog1)VALUES (geography::STGeomFromText(‘POLYGON((-122.358 47.653, -122.348 47.649, -122.348 47.658, -122.358 47.658, -122.358 47.653))‘, 4326));GO31.2.4CREATE TYPE [ schema_name. ] type_name                    --架构名，用户定义表类型名{     FROM base_type           --别名数据类型所基于的数据类型，创建用户定义表类型时省略     [ ( precision [ , scale ] ) ]    [ NULL | NOT NULL ]   | EXTERNAL NAME assembly_name [ .class_name ] --实现用户定义类型的实现的程序集和类名  | AS TABLE ( { <column_definition>  --定义用户定义表类型的列    | <computed_column_definition> }  --将计算列表达式定义为用户定义表类型中的列        [ <table_constraint> ] [ ,...n ] )  --定义用户定义表类型的表约束} [ ; ]<column_definition> ::=column_name <data_type>  --用户定义表类型的列的定义    [ COLLATE collation_name ]     [ NULL | NOT NULL ]    [         DEFAULT constant_expression ]       | [ IDENTITY [ ( seed ,increment ) ]     ]    [ ROWGUIDCOL ] [ <column_constraint> [ ...n ] ] --用户定义表类型的列约束<data type> ::=  --用户定义表类型的列的数据类型[ type_schema_name . ] type_name     [ ( precision [ , scale ] | max |                 [ { CONTENT | DOCUMENT } ] xml_schema_collection ) ] <column_constraint> ::= {     { PRIMARY KEY | UNIQUE }         [ CLUSTERED | NONCLUSTERED ]         [             WITH ( <index_option> [ ,...n ] )         ]  | CHECK ( logical_expression ) } <computed_column_definition> ::= --计算列表达式的定义column_name AS computed_column_expression [ PERSISTED [ NOT NULL ] ][     { PRIMARY KEY | UNIQUE }        [ CLUSTERED | NONCLUSTERED ]        [             WITH ( <index_option> [ ,...n ] )        ]    | CHECK ( logical_expression ) ] <table_constraint> ::=  --用户定义表类型的表约束的定义{     { PRIMARY KEY | UNIQUE }         [ CLUSTERED | NONCLUSTERED ]                               ( column [ ASC | DESC ] [ ,...n ] )         [            WITH ( <index_option> [ ,...n ] )         ]    | CHECK ( logical_expression ) } --指定对唯一聚集索引或唯一非聚集索引执行多行插入操作时出现重复键值的错误响应<index_option> ::= {     IGNORE_DUP_KEY = { ON | OFF } }-- 创建用户定义表类型CREATE TYPE dbo.TypeSample AS TABLE (    c1 int NOT NULL,  --和普通的表一样定义表的字段和主键    c2 char(10) NULL,     c3 datetime NULL,     PRIMARY KEY (c1))GODECLARE @MyTable TypeSample  --定义的表类型TypeSample变量@MyTableINSERT INTO @MyTable(c1,c2,c3)VALUES (1,‘abc‘,‘1/1/2009‘), (2,‘def‘,‘2/1/2009‘), (3,‘ghi‘,‘3/1/2009‘), (4,‘jkl‘,‘4/1/2009‘)   --向@MyTable插入数据SELECT * FROM @MyTableCREATE TABLE [dbo].[UdtSample](    [id] [int] NOT NULL PRIMARY KEY,    [text] [char](10) NOT NULL,    [time] [datetime] NOT NULL,)create PROCEDURE UdtSampleInsert     @table TypeSample readonly --定义用户定义表类型作为参数ASBEGIN    SET NOCOUNT ON;    INSERT INTO [SampleDB].[dbo].[UdtSample]         ([id]         ,[text]         ,[time])              select * from @table --将传进来的用户自定义类型的值插入到表UdtSample相应的字段中ENDGODECLARE @MyTable TypeSample  --定义用户自定义表类型变量作为参数INSERT INTO @MyTable(c1,c2,c3)  --向@MyTable插入数据，准备传递的数据VALUES (1,‘abc‘,‘1/1/2009‘), (2,‘def‘,‘2/1/2009‘), (3,‘ghi‘,‘3/1/2009‘), (4,‘jkl‘,‘4/1/2009‘)   exec UdtSampleInsert @table=@MyTable --执行存储过程UdtSampleInsert，插入数据31.2.5using System;using System.Data;using System.Data.SQLClient;using System.Data.SQLTypes;using Microsoft.SQLServer.Server;[Serializable][Microsoft.SQLServer.Server.SQLUserDefinedType(Format.Native)]public struct Point : INullable{    public int X //横坐标的值    {         get; set;    }    public int Y //纵坐标的值    {        get;set;    }    public override string ToString() //输出字符串    {        if (IsNull)        {            return "NULL";        }        return X+","+Y;    }    public bool IsNull //判断是否为NULL    {        get        {            return m_Null;        }    }    public static Point Null    {        get        {            Point h = new Point();            h.m_Null = true;            return h;        }    }    public static Point Parse(SQLString s) //将字符串转换为Point类型    {        if (s.IsNull)            return Null;        Point u = new Point();        string[] point = s.Value.Split(‘,‘); //从字符串中分析坐标数据，字符串格式为(x,y)        if (point.Length==2)        {            u.X = Int32.Parse(point[0]); //从字符串中获取横坐标的值            u.Y = Int32.Parse(point[1]); //从字符串中获取纵坐标的值        }        else        {            throw new Exception("Point类型的格式不正确");        }        return u;    }            private bool m_Null;}CREATE TABLE PointSample (column1 Point) --column1列的数据类型为PointGoINSERT INTO [SampleDB].[dbo].[PointSample]           ([column1])     VALUES           (‘1,2‘) --列column1的值，将使用Point类的Parse方法自动为Point类型GOINSERT INTO [SampleDB].[dbo].[PointSample]           ([column1])     VALUES           (‘1,e‘) --列column1的值，不是有效值，数据库将阻止插入GO31.2.5.3  select column1 from PointSample --查询Point的数据31.3.4declare @doc xml                         --声明xml类型变量select @doc=‘<Orders>                    --初始化xml数据<Order> <Customer>张三</Customer> <Items> <Item ProductID="1" Price="1" Quantity="2" /> <Item ProductID="2" Price="2" Quantity="2" /> <Item ProductID="3" Price="1" Quantity="3" /> </Items></Order><Order> <Customer>李四</Customer> <Items> <Item ProductID="2" Price="2" Quantity="3"/> </Items></Order></Orders>‘SELECT @doc.query(‘<Orders>   --使用XQuery对@doc进行查询{    for $order in /Orders/Order      --对/Orders/Order节点进行循环    let $count:=count($order/Items/Item)  --使用let语句进行赋值    order by $count                     --使用赋值后的@count进行递增排序    return    <ItemCount>{$count}</ItemCount>    --输出每个@count的值}</Orders>‘) 32.1.1ASYMKEYPROPERTY (Key_ID , ‘algorithm_desc‘ | ‘string_sid‘ | ‘sid‘)SELECT ASYMKEYPROPERTY(258, ‘algorithm_desc‘) AS Algorithm,  --获取非对称密钥的算法说明ASYMKEYPROPERTY(258, ‘string_sid‘) AS String_SID,  --获取密钥的nvarchar格式的SIDASYMKEYPROPERTY(258, ‘sid‘) AS SID ;  --获取密钥的二进制格式的SIDGO32.1.1CRYPT_GEN_RANDOM ( Length[ , seed ] )   --生成随机数SELECT CRYPT_GEN_RANDOM(10) ;32.1.2CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘password‘CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘Sample‘DROP MASTER KEYALTER MASTER KEY <alter_option> --修改数据库主密钥的属性<alter_option> ::=    <regenerate_option> | <encryption_option><regenerate_option> ::=   --重新创建数据库主密钥    [ FORCE ] REGENERATE WITH ENCRYPTION BY PASSWORD = ‘password‘<encryption_option> ::=  --使用服务主密钥对主密钥的副本进行加密    ADD ENCRYPTION BY [ SERVICE MASTER KEY | PASSWORD = ‘password‘ ]| --删除通过服务主密钥对数据库主密钥的加密    DROP ENCRYPTION BY [ SERVICE MASTER KEY | PASSWORD = ‘password‘ ]BACKUP MASTER KEY TO FILE = ‘path_to_file‘     --将主密钥导出到文件中    ENCRYPTION BY PASSWORD = ‘password‘     --用于加密文件中主密钥的密码CREATE CERTIFICATE certificate_name [ AUTHORIZATION user_name ]  --指定证书名称    { FROM <existing_keys> | <generate_new_keys> }  --使用现有的证书    [ ACTIVE FOR BEGIN_DIALOG =  { ON | OFF } ]        <existing_keys> ::=     ASSEMBLY assembly_name              --使用现有的证书    | {         [ EXECUTABLE ] FILE = ‘path_to_file‘        [ WITH PRIVATE KEY ( <private_key_options> ) ]       }        <generate_new_keys> ::=                    --生成新的证书    [ ENCRYPTION BY PASSWORD = ‘password‘]     WITH SUBJECT = ‘certificate_subject_name‘     [ , <date_options> [ ,...n ] ] <private_key_options> ::=                      --对私钥进行加密    FILE = ‘path_to_private_key‘    [ , DECRYPTION BY PASSWORD = ‘password‘ ]    [ , ENCRYPTION BY PASSWORD = ‘password‘ ]  <date_options> ::=    START_DATE = ‘mm/dd/yyyy‘ | EXPIRY_DATE = ‘mm/dd/yyyy‘  --证书的有效期Use masterCREATE CERTIFICATE CertificateSample WITH SUBJECT = ‘CertificateSample‘Go32.1.2DROP CERTIFICATE certificate_name  --删除名为certificate_name的证书BACKUP CERTIFICATE certname TO FILE = ‘path_to_file‘   --保存证书的完整路径    [ WITH PRIVATE KEY       (         FILE = ‘path_to_private_key_file‘ ,   --保存私钥的文件路径        ENCRYPTION BY PASSWORD = ‘encryption_password‘           [ , DECRYPTION BY PASSWORD = ‘decryption_password‘ ]       )     ]--备份证书CertificateSample  BACKUP CERTIFICATE CertificateSample  TO FILE = ‘c:\CertificateSample‘;CREATE DATABASE ENCRYPTION KEY       WITH ALGORITHM = { AES_128 | AES_192 | AES_256 | TRIPLE_DES_3KEY }   ENCRYPTION BY SERVER     {                CERTIFICATE Encryptor_Name |        ASYMMETRIC KEY Encryptor_Name    }[ ; ]CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128  --使用AES_128算法ENCRYPTION BY SERVER CERTIFICATE CertificateSample  --使用证书CertificateSampleALTER DATABASE ENCRYPTION KEY      REGENERATE WITH ALGORITHM = { AES_128 | AES_192 | AES_256 | TRIPLE_DES_3KEY }   |   ENCRYPTION BY SERVER CERTIFICATE Encryptor_NameUse SampleDBALTER DATABASE ENCRYPTION KEYREGENERATE WITH ALGORITHM = AES_256;  --更改加密算法GO32.1.2.7ALTER DATABASE [DataBsseName]   --要启用加密的数据库名称ENCRYPTION {ON | OFF}  --设置为是否使用加密32.2.2insert into Users values(1,‘张三‘)                      --执行数据INSERT操作insert into Users values(2,‘李四‘)                      --执行数据INSERT操作insert into Users values(3,‘王五‘)                      --执行数据INSERT操作select * from Users                                  --执行SELECT操作delete from Users where id=1                         --执行DELETE操作update Users set name=‘张三‘ where id=2              --执行UPDATE操作CREATE SERVER AUDIT audit_name    TO { [ FILE (<file_options> [, ...n]) ] | APPLICATION_LOG | SECURITY_LOG } --审核日志的存放位置    [ WITH ( <audit_options> [, ...n] ) ] }[ ; ]<file_options>::={       FILEPATH = ‘os_file_path‘    --使用二进制文件保存日志文件时审核日志的路径    [, MAXSIZE = { max_size { MB | GB | TB } | UNLIMITED } ]  --审核文件最大大小    [, MAX_ROLLOVER_FILES = integer ]   --保留最大文件数    [, RESERVE_DISK_SPACE = { ON | OFF } ]  --是否预先分配文件大小}  <audit_options>::={    [  QUEUE_DELAY = integer ]    [, ON_FAILURE = { CONTINUE | SHUTDOWN } ]    [, AUDIT_GUID = uniqueidentifier ]}CREATE SERVER AUDIT AuditSample    --创建审核对象TO FILE ( FILEPATH =‘C:\Audit‘ );     --设置文件的保存路径ALTER SERVER AUDIT audit_name      TO { [ FILE ( <file_options> [, ...n] ) ] | APPLICATION_LOG | SECURITY_LOG }    [ WITH ( <audit_options> [, ...n] ) ] }| MODIFY NAME = new_audit_name[ ; ]<file_options>::={       FILEPATH = ‘os_file_path‘    [, MAXSIZE = { max_size { MB | GB | TB } | UNLIMITED } ]    [, MAX_ROLLOVER_FILES = integer ]    [, RESERVE_DISK_SPACE = { ON | OFF } ] }<audit_options>::={    [, STATE = { ON | OFF } ]     --设置审核对象的状态}ALTER SERVER AUDIT AuditSample WITH (STATE=ON); --设置审核对象的状态CREATE DATABASE AUDIT SPECIFICATION audit_specification_name  --审核规范的名称{    [ FOR SERVER AUDIT audit_name ]    --应用此规范的审核对象        [ { ADD ( { <audit_action_specification> | audit_action_group_name } )       } [, ...n] ]    [ WITH ( STATE = { ON | OFF } ) ]  --是否启用该审核规范}[ ; ]<audit_action_specification>::=    --审核中的操作的规范{      action [ ,...n ]ON [ class :: ] securable BY principal [ ,...n ]}Use SampleDB    --将数据库切换为SampleDBCREATE DATABASE AUDIT SPECIFICATION TableAudit  --创建数据库审核规范TableAuditFOR SERVER AUDIT AuditSample                     --该审核规范使用AuditSample审核对象ADD (SELECT , INSERT            --设置需要进行审核的操作列表，这里对查询和插入进行审核     ON Test BY dbo )             --对架构dbo中的表Test进行审核WITH (STATE = ON)               --设置审核的状态为启用CREATE ENDPOINT testEndPoint                 --创建名为testEndPoint的端点STATE=STARTED                               --端点创建时的状态为启动AS HTTP (                                       --端点使用的传输协议为HTTP协议PATH = ‘/SampleEndPoint‘,                         --端点在主机上的路径AUTHENTICATION = (KERBEROS),                --设置身份验证类型为KERBEROSPORTS = (CLEAR),                               --指定传入的请求必须为HTTPSITE = ‘SqlServer2008‘)                           --主机名FOR SOAP (                                      --指定负载类型为SOAPWSDL = DEFAULT,                 --为提交给端点的WSDL查询生成并返回默认WSDL响应DATABASE = SampleDB,                          --执行请求的数据库)33.3.2use SampleDBselect * from SSISSample           --查询表SSISSample中的数据35.1.2SELECT [ProductName]      ,[Categoryname]      ,[SupplierID]            ,[QuantityPerUnit]      ,[UnitPrice]      ,[UnitsInStock]      ,[UnitsOnOrder]      ,[ReorderLevel]      ,[Discontinued]              FROM [Products],[Categories]               --从表[Products],[Categories]中查询  where [Products].CategoryID=[Categories].CategoryID order by [Categoryname]35.1.4--查询订单的详细信息SELECT TOP 1000 [Orders].[OrderID]      ,[CustomerID]      ,[EmployeeID]      ,[OrderDate]          ,[Order Details].Quantity       ,[Products].ProductName      FROM [Orders],[Order Details],[Products]  where [Orders].OrderID=[Order Details].OrderID   and [Order Details].ProductID=[Products].ProductID