3.3.7 -- ============================================= -- Create database template -- ============================================= USE master GO -- Drop the database if it already exists IF EXISTS ( SELECT name FROM sys.databases WHERE name = N'test' ) DROP DATABASE test GO CREATE DATABASE test GO 3.3.8 IF EXISTS ( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('<table_name, nvarchar(50), name>') AND type in ('U')) print '该数据表存在' else print '该数据表不存在' 5.3.1 CREATE 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.5 CREATE 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_AS select * from ::fn_helpcollations () 5.3.6 -- ============================================= -- Create database template -- ============================================= USE master GO -- Drop the database if it already exists IF EXISTS ( SELECT name FROM sys.databases WHERE name = N'测试用数据库' ) DROP DATABASE 测试用数据库 GO CREATE 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> GO CREATE DATABASE <database_name, sysname, sample_database> ON PRIMARY (NAME = <logical_filename1, , sample_database_file1>, FILENAME = N'<data_filename1, , C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataDatasample_database_1.mdf>', SIZE = 10MB, MAXSIZE = 50MB, FILEGROWTH = 10%), ( NAME = <logical_filename2, , sample_database_file2>, FILENAME = N'<data_filename2, , C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataDatasample_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 FilesMicrosoft SQL ServerMSSQL.1MSSQLDataDatasample_database_3.ndf>', SIZE = 10MB, MAXSIZE = 50MB, FILEGROWTH = 10%), ( NAME = <logical_filename4, , sample_database_file4>, FILENAME = N'<data_filename4, , C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataDatasample_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 FilesMicrosoft SQL ServerMSSQL.1MSSQLDataDatasample_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 FilesMicrosoft SQL ServerMSSQL.1MSSQLDataDatasample_database_1.ldf>', SIZE = 10MB, MAXSIZE = 50MB, FILEGROWTH = 10%), ( NAME = <logical_log_filename2, , sample_database_log_file2>, FILENAME = N'<log_filename2, , C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataDatasample_database_2.ldf>', SIZE = 5MB, MAXSIZE = 25MB, FILEGROWTH = 5MB) GO 5.5.1 ALTER 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.4 ALTER 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_KS ALTER DATABASE 例六数据库 REMOVE FILE 例十数据文件 ALTER DATABASE 例六数据库 ADD FILEGROUP 例十三文件组 ALTER DATABASE 例六数据库 MODIFY FILEGROUP 例十三文件组 NAME = 例十四文件组 ALTER DATABASE 例六数据库 ADD FILE (NAME=例十五数据文件, FILENAME='D:DBtest例十五数据文件.ndf') TO FILEGROUP 例十四文件组 GO ALTER DATABASE 例六数据库 MODIFY FILEGROUP 例十四文件组 DEFAULT GO ALTER DATABASE 例六数据库 MODIFY FILEGROUP [PRIMARY] DEFAULT GO ALTER DATABASE 例六数据库 REMOVE FILE 例十五数据文件 GO ALTER DATABASE 例六数据库 REMOVE FILEGROUP 例十四文件组 GO ALTER DATABASE 例六数据库 ADD FILEGROUP 例十七文件组 GO ALTER DATABASE 例六数据库 ADD FILE (NAME=例十七数据文件, FILENAME='D:DBtest例十七数据文件.ndf') TO FILEGROUP 例十七文件组 GO ALTER DATABASE 例六数据库 MODIFY FILEGROUP 例十七文件组 READ_ONLY GO ALTER DATABASE 例六数据库 SET SINGLE_USER ALTER DATABASE 例六数据库 SET AUTO_SHRINK ON 5.6.1 exec sp_detach_db '例四数据库' use master exec sp_who use master kill 52 kill 54 exec sp_detach_db '例四数据库' 5.6.2 use master CREATE DATABASE 例三数据库 ON (FILENAME = 'D:DBtest例三数据库.mdf') FOR ATTACH use master CREATE DATABASE 例三数据库 ON (FILENAME = 'D: est例三数据库.mdf'), (FILENAME='D: est例三数据库_log.LDF') FOR ATTACH use master exec sp_attach_db 例三数据库,'D: est例三数据库.mdf' use master exec sp_attach_db 例三数据库, 'D: est例三数据库.mdf', 'D: est例三数据库_log.LDF' 5.7.1 use master ALTER DATABASE 例三数据库 set OFFLINE use master ALTER DATABASE 例三数据库 set ONLINE use master DROP DATABASE 测试数据库 use master DROP DATABASE 测试用数据库,test 5.9.1 ALTER DATABASE 例六数据库 SET AUTO_SHRINK ON 5.9.2 DBCC SHRINKDATABASE ( 'database_name' | database_id | 0 [ ,target_percent ] [ , { NOTRUNCATE | TRUNCATEONLY } ] ) [ WITH NO_INFOMSGS ] DBCC SHRINKDATABASE (例六数据库) DBCC SHRINKDATABASE (例六数据库,10) 5.9.3 DBCC SHRINKFILE ( { 'file_name' | file_id } { [ , EMPTYFILE ] | [ [ , target_size ] [ , { NOTRUNCATE | TRUNCATEONLY } ] ] } ) [ WITH NO_INFOMSGS ] use 例六数据库 go DBCC SHRINKFILE (例六数据库_log) go use 例六数据库 go DBCC SHRINKFILE (例六数据库,3) go 5.10 USE [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_KS GO EXEC dbo.sp_dbcmptlevel @dbname=N'例六数据库', @new_cmptlevel=90 GO IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled')) begin EXEC [例六数据库].[dbo].[sp_fulltext_database] @action = 'enable' end GO ALTER DATABASE [例六数据库] SET ANSI_NULL_DEFAULT OFF GO ALTER DATABASE [例六数据库] SET ANSI_NULLS OFF GO ALTER DATABASE [例六数据库] SET ANSI_PADDING OFF GO ALTER DATABASE [例六数据库] SET ANSI_WARNINGS OFF GO ALTER DATABASE [例六数据库] SET ARITHABORT OFF GO ALTER DATABASE [例六数据库] SET AUTO_CLOSE OFF GO ALTER DATABASE [例六数据库] SET AUTO_CREATE_STATISTICS ON GO ALTER DATABASE [例六数据库] SET AUTO_SHRINK ON GO ALTER DATABASE [例六数据库] SET AUTO_UPDATE_STATISTICS ON GO ALTER DATABASE [例六数据库] SET CURSOR_CLOSE_ON_COMMIT OFF GO ALTER DATABASE [例六数据库] SET CURSOR_DEFAULT GLOBAL GO ALTER DATABASE [例六数据库] SET CONCAT_NULL_YIELDS_NULL OFF GO ALTER DATABASE [例六数据库] SET NUMERIC_ROUNDABORT OFF GO ALTER DATABASE [例六数据库] SET QUOTED_IDENTIFIER OFF GO ALTER DATABASE [例六数据库] SET RECURSIVE_TRIGGERS OFF GO ALTER DATABASE [例六数据库] SET ENABLE_BROKER GO ALTER DATABASE [例六数据库] SET AUTO_UPDATE_STATISTICS_ASYNC OFF GO ALTER DATABASE [例六数据库] SET DATE_CORRELATION_OPTIMIZATION OFF GO ALTER DATABASE [例六数据库] SET TRUSTWORTHY OFF GO ALTER DATABASE [例六数据库] SET ALLOW_SNAPSHOT_ISOLATION OFF GO ALTER DATABASE [例六数据库] SET PARAMETERIZATION SIMPLE GO ALTER DATABASE [例六数据库] SET READ_WRITE GO ALTER DATABASE [例六数据库] SET RECOVERY FULL GO ALTER DATABASE [例六数据库] SET MULTI_USER GO ALTER DATABASE [例六数据库] SET PAGE_VERIFY CHECKSUM GO ALTER DATABASE [例六数据库] SET DB_CHAINING OFF 6.4.1 CREATE 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.3 CREATE TABLE Northwind.dbo.例一表 ( 编号 int, 姓名 nvarchar(50) ) CREATE TABLE Northwind..例一表 ( 编号 int, 姓名 nvarchar(50) ) USE Northwind CREATE TABLE 例一表 ( 编号 int, 姓名 nvarchar(50) ) 6.4.4 CREATE 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.7 CREATE TABLE 例五表 ( 编号 int IDENTITY(1,1) PRIMARY KEY, 姓名 nvarchar(50) NOT NULL, 性别 bit DEFAULT 1 ) 6.4.8 CREATE TABLE 例六表 ( 编号 int IDENTITY(1,1) PRIMARY KEY, 姓名 nvarchar(50) NOT NULL, 性别 bit DEFAULT 1 ) ON 第二文件组 6.4.9 CREATE 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.11 CREATE TABLE 例九_部门表 ( 部门编号 int IDENTITY(1,1) PRIMARY KEY, 部门名称 nvarchar(50) Not null ) GO CREATE TABLE 例九_员工表 ( 员工编号 int IDENTITY(1,1) PRIMARY KEY, 所属部门 int CONSTRAINT FK_员工表外键 FOREIGN KEY REFERENCES 例九_部门表(部门编号), 员工姓名 nvarchar(20) not null ) GO 6.4.12 CREATE TABLE 例十_部门表 ( 部门编号 int IDENTITY(1,1) PRIMARY KEY, 部门名称 nvarchar(50) Not null ) GO CREATE TABLE 例十_员工表 ( 员工编号 int IDENTITY(1,1) PRIMARY KEY, 所属部门 int CONSTRAINT FK_例十_员工表外键 FOREIGN KEY REFERENCES 例十_部门表(部门编号) ON UPDATE CASCADE ON DELETE SET NULL, 员工姓名 nvarchar(20) not null ) GO 6.4.13 CREATE TABLE 例十一_部门表 ( 部门编号 int IDENTITY(1,1) PRIMARY KEY, 部门名称 nvarchar(50) Not null UNIQUE ) 6.4.14 CREATE TABLE 例十二 ( 编号 int IDENTITY(1,1) PRIMARY KEY, 单价 money Not null, 数量 int Not null CONSTRAINT CK_例十二 CHECK (数量>0), 合计 as 单价*数量 ) 6.4.15 CREATE 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.1 ALTER 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.3 ALTER TABLE 例一表 ALTER COLUMN 姓名 nvarchar(20) COLLATE Chinese_PRC_CI_AS not null ALTER TABLE 例一表 ADD 性别 nvarchar(2) 6.6.6 ALTER TABLE 例一表 DROP COLUMN 密码 6.6.7 ALTER TABLE 例一表 ADD CONSTRAINT CK_性别 CHECK (性别='男' OR 性别='女') ALTER TABLE 例一表 WITH NOCHECK ADD CONSTRAINT CK_性别 CHECK (性别='男' OR 性别='女') 6.6.8 ALTER TABLE 例一表 NOCHECK CONSTRAINT CK_性别 ALTER TABLE 例一表 NOCHECK CONSTRAINT CK_1,CK_2,CK_3 ALTER TABLE 例一表 NOCHECK CONSTRAINT ALL 6.6.9 ALTER TABLE 例一表 CHECK CONSTRAINT CK_性别 ALTER TABLE 例一表 CHECK CONSTRAINT CK_1,CK_2,CK_3 ALTER TABLE 例一表 CHECK CONSTRAINT ALL 6.6.10 ALTER TABLE 例一表 DROP CK_性别 6.6.11 ALTER TABLE 例一表 ALTER COLUMN 编号 int not null GO ALTER TABLE 例一表 ADD CONSTRAINT PK_主键 PRIMARY KEY (编号) GO 6.6.12 exec sp_rename '例一表.姓名','名称','COLUMN' exec sp_rename '例一表','例二十三表' sp_rename ‘原对象名称’ , ’新对象名称’ [ , ’对象类型’] 6.8 DROP TABLE [ database_name . [ schema_name ] . | schema_name . ] table_name [ ,...n ] [ ; ] DROP TABLE 例二表 6.9 USE [Northwind] GO /****** 对象: Table [dbo].[例十_部门表] 脚本日期: 05/19/2009 13:53:12 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE 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.1 INSERT [ 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.3 INSERT 订单明细 VALUES (10248,1,10,2,0.8) 7.3.4 INSERT INTO 订单明细 (折扣, 数量, 单价, 产品ID, 订单ID) VALUES (0.8, 3, 26, 2, 10248) 7.3.5 INSERT INTO 类别 (类别名称, 说明) VALUES (N'图书',N'所有类型的图书') 7.3.6 SET IDENTITY_INSERT 类别 ON; GO INSERT INTO 类别 (类别ID,类别名称) VALUES (100,N'电器') GO SELECT * FROM 类别 GO 7.3.7 CREATE 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 ) GO INSERT INTO 雇员通讯录 SELECT 雇员ID, 姓氏, 名字, 邮政编码, 城市, 地址, 家庭电话 FROM 雇员 GO SELECT * FROM 雇员通讯录 GO DELETE 雇员通讯录 GO INSERT top (5) INTO 雇员通讯录 SELECT 雇员ID, 姓氏, 名字, 邮政编码, 城市, 地址, 家庭电话 FROM 雇员 GO 7.3.8 DELETE 雇员通讯录 GO INSERT 雇员通讯录(雇员ID, 姓氏, 名字, 邮政编码, 地址) SELECT 雇员ID, 姓氏, 名字, 邮政编码, 地址 FROM 雇员 GO 7.3.9 DELETE 雇员通讯录 GO INSERT 雇员通讯录(雇员ID, 姓氏, 名字, 邮政编码, 地址, 城市) SELECT 雇员ID, 姓氏, 名字, 邮政编码, 地址, '北京' FROM 雇员 GO 7.3.10 CREATE 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() ) GO INSERT 用户与进程信息(进程ID,定线程上下文ID,进程状态,登录名,主机名, 阻塞进程的系统进程ID,数据库名,运行命令,请求ID) EXEC sp_who SELECT * FROM 用户与进程信息 7.3.11 INSERT INTO 用户与进程信息 DEFAULT VALUES 7.5.1 UPDATE [ 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.3 UPDATE 类别 SET 说明 = N'家用电器' WHERE (类别名称 = N'电器') 7.5.4 UPDATE 产品 SET 单价 = 单价*1.5 WHERE 产品名称 = N'牛奶' 7.5.5 UPDATE 订单明细 SET 单价 = 产品.单价 FROM 产品 WHERE (订单明细.产品ID = 产品.产品ID) AND (产品.产品名称 = N'牛奶') 7.5.6 UPDATE top (10) PERCENT 订单明细 SET 单价= 产品.单价 FROM 产品 WHERE (订单明细.产品ID = 产品.产品ID) AND (产品.产品名称= N'牛奶') 7.7.1 DELETE [ 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.3 DELETE 类别 WHERE 类别名称= N'电器' 7.7.4 DELETE 订单 WHERE 订购日期 < '1996.8.1' 7.7.5 DELETE 订单 FROM 雇员 WHERE (雇员.雇员ID = 订单.雇员ID) AND (雇员.姓氏 = N'王') AND (雇员.名字 = N'伟') 7.8 TRUNCATE TABLE [ { database_name.[ schema_name ]. | schema_name . } ] table_name [ ; ] TRUNCATE TABLE 订单明细 7.9 SELECT [ 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.1 SELECT [ 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.3 SELECT * FROM 类别 7.10.4 SELECT 类别名称,说明 FROM 类别 SELECT 类别.类别名称, 类别.说明 FROM 类别 7.10.5 SELECT 订单ID, 产品ID, 单价, 数量, 折扣, 单价 * (1 . 折扣) * 数量 AS 总价 FROM 订单明细 SELECT *, 单价 * (1 . 折扣) * 数量 AS 总价 FROM 订单明细 SELECT 订单ID as 订单编号, 产品ID as 产品编号, 单价, 数量, 折扣, 单价 * (1 . 折扣) * 数量 AS 总价 FROM 订单明细 7.10.6 SELECT TOP 10 * FROM 订单明细 ORDER BY 数量 DESC SELECT top 10 PERCENT * FROM 订单明细 ORDER BY 数量 DESC SELECT TOP 10 WITH TIES * FROM 订单明细 ORDER BY 数量 DESC 7.10.7 SELECT 货主城市 FROM 订单 SELECT ALL 货主城市 FROM 订单 SELECT DISTINCT 货主城市 FROM 订单 SELECT DISTINCT 货主名称,货主城市 FROM 订单 7.10.8 SELECT $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> ] ] JOIN 7.11.3 SELECT * FROM 雇员 SELECT 类别ID,类别名称 FROM 类别 SELECT 产品ID,产品名称,类别ID FROM 产品 SELECT 产品ID,产品名称,类别名称 FROM 产品 JOIN 类别 ON 产品.类别ID = 类别.类别ID SELECT 产品ID,产品名称,类别名称,类别ID FROM 产品 JOIN 类别 ON 产品.类别ID = 类别.类别ID SELECT 产品ID,产品名称,类别名称,类别.类别ID FROM 产品 JOIN 类别 ON 产品.类别ID = 类别.类别ID SELECT 产品ID,产品名称,类别名称,类别.类别ID FROM 产品, 类别 WHERE 产品.类别ID = 类别.类别ID 7.11.5 SELECT 产品.产品名称, 订单明细.单价, 订单明细.数量, 订单明细.折扣, 订单.订购日期 FROM 订单明细 JOIN 订单 ON 订单明细.订单ID = 订单.订单ID JOIN 产品 ON 订单明细.产品ID = 产品.产品ID SELECT 产品.产品名称, 订单明细.单价, 订单明细.数量, 订单明细.折扣, 订单.订购日期 FROM (订单明细 JOIN 订单 ON 订单明细.订单ID = 订单.订单ID ) JOIN 产品 ON 订单明细.产品ID = 产品.产品ID 7.11.6 SELECT * 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.7 SELECT 细.单价, 细.数量, 细.折扣, 细.单价* (1 . 细.折扣) * 细.数量 AS 总价, 订.订购日期 FROM 订单 AS 订 INNER JOIN 订单明细 AS 细 ON 订.订单ID = 细.订单ID 7.11.8 SELECT 雇员.雇员ID, 雇员.姓氏, 雇员.名字, 雇员.职务, 主管.姓氏 AS 主管姓氏, 主管.名字 AS 主管名字, 主管.职务 AS 主管职务 FROM 雇员 LEFT OUTER JOIN 雇员 AS 主管 ON 雇员.上级 = 主管.雇员ID 7.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.3 SELECT * FROM 产品 WHERE 库存量 = 0 SELECT * FROM 产品 WHERE 库存量 <> 0 SELECT * FROM 产品 7.12.4 SELECT * FROM 产品 WHERE 库存量 = 0 AND 类别ID = 2 SELECT 产品.* FROM 产品 JOIN 类别 ON 产品.类别ID = 类别.类别ID WHERE 产品.库存量 = 0 AND 类别.类别名称 = N'调味品' SELECT 产品.* FROM 产品 JOIN 类别 ON 产品.类别ID = 类别.类别ID WHERE 产品.库存量 = 0 AND (类别.类别名称 = N'调味品' OR 类别.类别名称 = N'日用品') 7.12.5 SELECT * FROM 雇员 WHERE 雇用日期< CONVERT(DATETIME, '1993.1.1', 102) SELECT * FROM 雇员 WHERE 雇用日期< '1993.1.1' SELECT * FROM 雇员 WHERE Year(Getdate()).Year(雇用日期) >13 7.12.6 SELECT * FROM 雇员 WHERE 雇用日期 BETWEEN CONVERT(DATETIME, '1993.01.01', 102) AND CONVERT(DATETIME, '1994.12.31', 102) 7.12.7 SELECT * FROM 雇员 WHERE 上级 IS NULL 7.12.8 SELECT * FROM 雇员 WHERE 雇员ID = 1 OR 雇员ID = 3 OR 雇员ID = 4 OR 雇员ID = 7 OR 雇员ID = 9 SELECT * 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.9 SELECT * FROM 产品 WHERE 产品名称 LIKE '%奶%' SELECT * FROM 产品 WHERE 产品名称 LIKE '%奶酪' SELECT * FROM 产品 WHERE 产品名称 LIKE '_奶酪' SELECT * FROM 产品 WHERE 产品名称 LIKE '%油' GO SELECT * FROM 产品 WHERE 产品名称 LIKE '[麻酱]油' GO SELECT * FROM 产品 WHERE 产品名称 LIKE '[^麻酱]油' GO 7.12.10 SELECT * FROM 类别 WHERE 说明 LIKE '%[_]%' 7.12.11 SELECT * FROM 订单 WHERE EXISTS ( SELECT * FROM 雇员 WHERE Year(Getdate()).Year(雇用日期) <13 AND 订单.雇员ID = 雇员.雇员ID ) SELECT * FROM 订单 JOIN 雇员 ON 订单.雇员ID = 雇员.雇员ID WHERE Year(Getdate()).Year(雇员.雇用日期) <13 7.12.12 SELECT * 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.3 SELECT * FROM 产品 ORDER BY 产品名称 SELECT * FROM 产品 ORDER BY 产品名称 DESC 7.13.4 SELECT * FROM 产品 ORDER BY 供应商ID,产品名称 SELECT * FROM 产品 ORDER BY 供应商ID ASC,产品名称 DESC 7.14.1 [ GROUP BY [ ALL ] group_by_expression [ ,...n ] [ WITH { CUBE | ROLLUP } ] ] 7.14.3 SELECT 货主城市,count(订单ID) AS 订单总数 FROM 订单 GROUP BY 货主城市 7.14.4 SELECT 货主城市,count(订单ID) AS 订单总数,YEAR(订购日期) as 订购年份 FROM 订单 GROUP BY 货主城市,YEAR(订购日期) ORDER BY 货主城市,YEAR(订购日期) 7.14.5 SELECT 货主城市,count(订单ID) AS 订单总数 FROM 订单 GROUP BY 货主城市 WITH CUBE SELECT 货主城市,YEAR(订购日期) as 订购年份,count(订单ID) AS 订单总数 FROM 订单 GROUP BY 货主城市,YEAR(订购日期) WITH CUBE 7.14.6 SELECT 货主城市,YEAR(订购日期) as 订购年份,count(订单ID) AS 订单总数 FROM 订单 GROUP BY 货主城市,YEAR(订购日期) WITH ROLLUP 7.14.7 SELECT 货主城市,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.2 SELECT 货主城市,count(订单ID) AS 订单总数,sum(运货费) AS 运货费总数 FROM 订单 WHERE count(订单ID) > 20 GROUP BY 货主城市 SELECT 货主城市,count(订单ID) AS 订单总数,sum(运货费) AS 运货费总数 FROM 订单 GROUP BY 货主城市 HAVING count(订单ID) > 20 7.16.1 [ COMPUTE { { AVG | COUNT | MAX | MIN | STDEV | STDEVP | VAR | VARP | SUM } ( expression ) } [ ,...n ] [ BY expression [ ,...n ] ] ] 7.16.3 SELECT 订单ID,货主城市,运货费 FROM 订单 WHERE 发货日期 is null COMPUTE SUM(运货费) 7.16.4 SELECT 订单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.3 SELECT 联系人姓名,地址,电话 FROM 供应商 UNION ALL SELECT 联系人姓名,地址,电话 FROM 客户 7.17.4 SELECT 联系人姓名,地址,电话 FROM 供应商 UNION SELECT 联系人姓名,地址,电话 FROM 客户 7.17.5 SELECT 联系人姓名,地址,电话 FROM 供应商 UNION SELECT 联系人姓名,地址,电话 FROM 客户 UNION SELECT '张三','北京中医药大学','010.12345678' 7.17.6 SELECT 联系人姓名,地址,电话 FROM 供应商 UNION SELECT 联系人姓名,地址,电话 FROM 客户 ORDER BY 联系人姓名 7.17.7 SELECT 地址,COUNT(地址) AS 联系人数 FROM ( SELECT 联系人姓名,地址,电话 FROM 供应商 UNION SELECT 联系人姓名,地址,电话 FROM 客户 ) AS 临时表 GROUP BY 地址 7.18.1 SELECT < select_list > [ INTO new_table ] [ FROM { <table_source> } [ ,...n ] ] [ WHERE <search_condition> ] 7.18.2 SELECT 产品ID,产品名称 INTO 缺货记录 FROM dbo.产品 WHERE 库存量= 0 7.18.3 SELECT 雇员.雇员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 = 客户.客户ID 7.18.4 SELECT * INTO 订单历史记录 FROM 订单 WHERE 0=1 7.19 SELECT * FROM Northwind.dbo.雇员 JOIN test.dbo.订单 ON Northwind.dbo.雇员.雇员ID = test.dbo.订单.雇员ID SELECT * FROM Northwind.dbo.雇员 JOIN test.dbo.订单 ON 雇员.雇员ID = 订单.雇员ID SELECT * FROM Northwind.dbo.雇员 as N雇员 JOIN test.dbo.订单 as 例订单 ON N雇员.雇员ID = 例订单.雇员ID use Northwind SELECT * FROM 雇员 JOIN test.dbo.订单 ON 雇员.雇员ID = test.dbo.订单.雇员ID 7.20.2 SELECT * FROM 类别 WHERE 图片 IS NULL SELECT * FROM 类别 WHERE 图片 IS NOT NULL 7.20.3 ISNULL ( check_expression , replacement_value ) SELECT 类别ID,类别名称,isnull(说明,'暂无说明') as 说明 FROM 类别 7.21.1 WITH 临时表(雇员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.2 DELETE 雇员通讯录 GO WITH 临时表(雇员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 雇员通讯录 GO INSERT 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) GO SELECT * FROM 雇员通讯录 WHERE 雇员ID = 4 UPDATE 雇员通讯录 SET 地址.WRITE (N'试一下',1,1) WHERE 雇员ID = 4 SELECT * FROM 雇员通讯录 WHERE 雇员ID = 4 8.3.1 INSERT 类别(类别名称,说明) VALUES ('图书','各种图书') SELECT * FROM 类别 WHERE 类别名称 = N'图书' UPDATE 类别 SET 说明 = N'计算机、时尚生活等图书' WHERE 类别名称 = N'图书' SELECT * FROM 类别 WHERE 类别名称 = N'图书' 8.3.2 SELECT * FROM 类别 WHERE 类别名称 = N'图书' UPDATE 类别 SET 说明 = N'计算机、时尚生活等图书' WHERE 类别名称 = N'图书' GO SELECT * FROM 类别 WHERE 类别名称 = N'图书' GO 8.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.1 CAST ( expression AS data_type [ (length ) ]) SELECT 产品名称+ '的单价为：' + CAST(单价 AS VARCHAR(10)) + '元' AS 产品介绍 FROM 产品 8.5.2 CONVERT ( 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.3 SELECT 产品名称,单价*库存量 AS 积压资金 FROM 产品 WHERE 单价*库存量>0 8.8.11 SELECT 产品名称,单价+$10 AS 价格 FROM 产品 SELECT * FROM 产品 WHERE 单价>$10 ? 为变量赋值，例如： DECLARE @abc int SET @abc = 123 UPDATE 类别 SET 说明= N'精彩图书' WHERE 类别名称= N'图书' INSERT 类别 (类别名称) VALUES (N'手提电脑等') PRINT ‘完成操作’ IF @@ERROR >0 PRINT N'出错了’ 8.9.1 DECLARE { @local_variable [AS] data_type } [ ,...n] SET @local_variable = value SELECT @local_variable = value SELECT @local_variable PRINT @local_variable DECLARE @name varchar(20) DECLARE @age int,@sex bit SET @name = '张三' SET @age = 20 SELECT @sex = 1 SELECT @name SELECT @age SELECT @sex PRINT @name PRINT @age PRINT @sex DECLARE @name varchar(20) DECLARE @birthday datetime SELECT @name = 姓氏+名字,@birthday=出生日期 FROM 雇员 WHERE 雇员ID = 1 PRINT '雇员姓名：'+@name PRINT '雇员生日：'+CONVERT(varchar(50),@birthday,102) DECLARE @name varchar(20) DECLARE @birthday datetime set @name = '未知' SELECT @name = 姓氏+名字,@birthday=出生日期 FROM 雇员 WHERE 雇员ID = 1000 PRINT '雇员姓名：'+@name PRINT '雇员生日：'+CONVERT(varchar(50),@birthday,102) DECLARE @name varchar(20) DECLARE @birthday datetime SELECT @name = 姓氏+名字,@birthday=出生日期 FROM 雇员 WHERE 雇员ID = 1 PRINT '雇员姓名：'+@name PRINT '雇员生日：'+CONVERT(varchar(50),@birthday,102) GO PRINT '雇员姓名：'+@name PRINT '雇员生日：'+CONVERT(varchar(50),@birthday,102) 8.9.2 SELECT * FROM 雇员 PRINT '一共查询了'+CAST(@@ROWCOUNT AS varchar(5))+'条记录' SELECT 'SQL Server 2008启动以来尝试的连接数：'+ CAST(@@CONNECTIONS AS varchar(10)) 8.10.1 BEGIN { sql_statement | statement_block } END USE Northwind DECLARE @price money DECLARE @productid int DECLARE @count int SELECT @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)) END USE Northwind DECLARE @price money DECLARE @productid int SELECT @price = 单价, @productid = 产品ID FROM 产品 WHERE 产品名称= N'蕃茄酱' IF @price<$20 SELECT sum(订单明细.数量) FROM 订单 JOIN 订单明细 ON 订单.订单ID = 订单明细.订单ID WHERE 订单明细.产品ID = @productid 8.10.2 IF Boolean_expression { sql_statement | statement_block } [ ELSE { sql_statement | statement_block } ] USE Northwind DECLARE @price money DECLARE @productid int DECLARE @count int SELECT @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)) END ELSE BEGIN PRINT '蕃茄酱的单价高于20元' SELECT @count = sum(库存量) FROM 产品 WHERE 产品ID = @productid PRINT '其库存量为：' + CAST(@count AS varchar(5)) END USE Northwind DECLARE @price money SELECT @price = 单价 FROM 产品 WHERE 产品名称= N'蕃茄酱' IF @price<$20 PRINT '蕃茄酱的单价低于20元' ELSE BEGIN IF $20<=@price and @price<=40 PRINT '蕃茄酱的单价在20元与40元之间' ELSE PRINT '蕃茄酱的单价大于40元' END 8.10.3 WHILE Boolean_expression { sql_statement | statement_block } [ BREAK ] { sql_statement | statement_block } [ CONTINUE ] { sql_statement | statement_block } USE Northwind DECLARE @id int DECLARE @productname varchar(40) SET @id = 1 WHILE @id<10 BEGIN SELECT @productname = 产品名称 FROM 产品 WHERE 产品ID = @id PRINT @productname SET @id = @id+1 END USE Northwind DECLARE @id int DECLARE @productname varchar(40) DECLARE @maxid int SELECT @maxid = MAX(产品ID) FROM dbo.产品 --查看产品表里最大的编号是多少 SET @id = 0 WHILE @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 END 8.10.4 USE Northwind DECLARE @price money SELECT @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 END CASE 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 ] END USE Northwind DECLARE @categoryname varchar(15) DECLARE @outstr varchar(100) SELECT @categoryname = 类别名称 FROM 类别 WHERE 类别ID = 2 SET @outstr = CASE @categoryname WHEN '饮料' THEN '类别：饮料' WHEN '调味品' THEN '类别：调味品' WHEN '点心' THEN '类别：点心' WHEN '日用品' THEN '类别：日用品' WHEN '特制品' THEN '类别：特制品' WHEN '海鲜' THEN '类别：海鲜' ELSE '其他类别' END PRINT @outstr USE Northwind SELECT 产品名称,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 Northwind DECLARE @price money DECLARE @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元' END PRINT @returnstr 8.10.5 label: GOTO label USE Northwind DECLARE @price money DECLARE @returnstr varchar(50) SELECT @price = 单价 FROM 产品 WHERE 产品名称= N'蕃茄酱' IF @price<$20 GOTO print20 --跳转到标签print20 IF $20<=@price and @price<40 GOTO print40 --跳转到标签print40 IF $40<=@price and @price<=80 GOTO print80 --跳转到标签print80 GOTO other --跳转到标签other print20: PRINT '蕃茄酱的单价低于20元' GOTO theEnd --跳转到标签theEnd print40: PRINT '蕃茄酱的单价在20元与40元之间' GOTO theEnd --跳转到标签theEnd print80: PRINT '蕃茄酱的单价在40元与80元之间' GOTO theEnd --跳转到标签theEnd other: PRINT '蕃茄酱的单价大于80元' theEnd: 8.10.6 WAITFOR { DELAY 'time_to_pass' | TIME 'time_to_execute' } USE Northwind SELECT 单价 FROM 产品 WHERE 产品名称= N'蕃茄酱' GO WAITFOR DELAY '00:00:10' SELECT 单价 FROM 产品 WHERE 产品名称= N'蕃茄酱' GO USE Northwind SELECT 单价 FROM 产品 WHERE 产品名称= N'蕃茄酱' GO WAITFOR TIME '15:57:10' SELECT 单价 FROM 产品 WHERE 产品名称= N'蕃茄酱' GO 8.10.7 RETURN [ integer_expression ] 8.10.8 BEGIN TRY { sql_statement | statement_block } END TRY BEGIN CATCH { sql_statement | statement_block } END CATCH USE Northwind BEGIN TRY DELETE 类别 WHERE 类别ID=5 END TRY BEGIN CATCH PRINT '出错信息为：' + ERROR_MESSAGE() DELETE 产品 WHERE 类别ID=5 DELETE 类别 WHERE 类别ID=5 END CATCH 8.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_tables USE Northwind DECLARE @execstr varchar(1000) DECLARE @year int SET @year = 2000 WHILE @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 END 9.1 SELECT 订单.订单ID, 雇员.姓氏, 雇员.名字, 产品.产品名称, 订单明细.单价, 订单明细.数量, 订单明细.折扣, 运货商.公司名称, 订单.货主名称, 订单.货主地址, 订单.货主城市, 订单.订购日期, 订单.发货日期 FROM 订单 INNER JOIN 订单明细 ON 订单.订单ID = 订单明细.订单ID INNER JOIN 雇员 ON 订单.雇员ID = 雇员.雇员ID INNER JOIN 产品 ON 订单明细.产品ID = 产品.产品ID INNER JOIN 运货商 ON 订单.运货商 = 运货商.运货商ID CREATE VIEW 订单详细视图 AS SELECT 订单.订单ID, 雇员.姓氏, 雇员.名字, 产品.产品名称, 订单明细.单价, 订单明细.数量, 订单明细.折扣, 运货商.公司名称, 订单.货主名称, 订单.货主地址, 订单.货主城市, 订单.订购日期, 订单.发货日期 FROM 订单 INNER JOIN 订单明细 ON 订单.订单ID = 订单明细.订单ID INNER JOIN 雇员 ON 订单.雇员ID = 雇员.雇员ID INNER JOIN 产品 ON 订单明细.产品ID = 产品.产品ID INNER JOIN 运货商 ON 订单.运货商= 运货商.运货商ID SELECT * FROM 订单详细视图 SELECT * FROM 订单详细视图 WHERE 订单ID = 10248 9.2.2.1 CREATE 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 = 供应商.供应商ID GO --查看视图 SELECT * FROM view_例一 GO 9.2.2.4 CREATE VIEW view_例二(产品编号,产品名称,产品类别,供应商名称) AS SELECT 产品.产品ID,产品.产品名称,类别.类别名称,供应商.公司名称 FROM 产品 JOIN 类别 ON 产品.类别ID = 类别.类别ID JOIN 供应商 ON 产品.供应商ID = 供应商.供应商ID GO SELECT * FROM view_例二 GO 9.2.2.5 CREATE 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 DESC CREATE VIEW view_例三 AS SELECT top 100 产品.产品ID,产品.产品名称,类别.类别名称,供应商.公司名称 FROM 产品 JOIN 类别 ON 产品.类别ID = 类别.类别ID JOIN 供应商 ON 产品.供应商ID = 供应商.供应商ID ORDER BY 产品.产品ID DESC 9.3.3 ALTER 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 DESC 9.4 CREATE VIEW view_例五 WITH ENCRYPTION AS SELECT top 50 产品.产品ID,产品.产品名称,类别.类别名称,供应商.公司名称 FROM 产品 JOIN 类别 ON 产品.类别ID = 类别.类别ID JOIN 供应商 ON 产品.供应商ID = 供应商.供应商ID ORDER BY 产品.产品ID DESC ALTER VIEW view_例五 AS SELECT top 50 产品.产品ID,产品.产品名称,类别.类别名称,供应商.公司名称 FROM 产品 JOIN 类别 ON 产品.类别ID = 类别.类别ID JOIN 供应商 ON 产品.供应商ID = 供应商.供应商ID ORDER BY 产品.产品ID DESC 9.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.id GO --修改数据表 PRINT '' ALTER TABLE 例七_1 ALTER COLUMN username nvarchar(100) GO PRINT '' ALTER TABLE 例七_2 ALTER COLUMN newname nvarchar(100) GO --删除数据表 PRINT '' DROP TABLE 例七_1 GO PRINT '' DROP TABLE 例七_2 GO 9.6 CREATE VIEW view_例八 AS SELECT 产品ID,产品名称,单价 FROM 产品 WHERE 单价> $20 WITH CHECK OPTION UPDATE view_例八 SET 单价= $16 WHERE 产品ID = 4 UPDATE 产品 SET 单价= $16 9.7.3 UPDATE view_例八 SET 单价= $16 WHERE 产品ID = 4 DELETE view_例八 WHERE 产品ID = 4 9.8.2 DROP VIEW [ schema_name . ] view_name [ ...,n ] [ ; ] DROP VIEW view_例一 DROP VIEW view_例二,view_例三 INSERT view_例八(产品名称,单价) VALUES ('白菜',$1) WHERE 产品ID = 4 9.9 exec sp_rename 'view_例五','view_例五_1' 10.2.1 CREATE { 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 类别 GO EXEC pr_例一 10.3.1 ALTER { 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 ALTER PROC pr_例一 AS SELECT * FROM 类别 ORDER BY 类别名称 10.4 CREATE PROCEDURE pr_例二 @p1 int AS BEGIN SET NOCOUNT ON; SELECT * FROM 产品 WHERE 类别ID=@p1 END [ { EXEC | EXECUTE } ] { [ @return_status = ] --返回值 [ [ @parameter = ] { value --参数及值 | @variable [ OUTPUT ] --返回型参数 | [ DEFAULT ] --缺省值 } ] [ ,...n ] [ WITH RECOMPILE ] --执行模块后，强制编译、使用和放弃新计划 } 10.5.2 CREATE PROCEDURE pr_例六 @类别名称 varchar(15), @单价 money = $10, @库存量 smallint, @订购量 smallint =5 AS BEGIN SELECT * FROM 产品 JOIN 类别 ON 产品.类别ID = 类别.类别ID WHERE (类别.类别名称 = @类别名称) AND (产品.单价 > @单价) AND (产品. 库存量 > @库存量) AND (产品. 订购量 > @订购量) END GO CREATE PROCEDURE pr_例六_2 @类别名称 varchar(15), @库存量 smallint, @订购量 smallint =5, @单价 money = $10 AS BEGIN SELECT * FROM 产品 JOIN 类别 ON 产品.类别ID = 类别.类别ID WHERE (类别.类别名称 = @类别名称) AND (产品.单价 > @单价) AND (产品. 库存量 > @库存量) AND (产品. 订购量 > @订购量) END GO 10.5.3 CREATE PROCEDURE pr_例七 AS BEGIN declare @返回值 int SELECT @返回值 = sum(库存量) FROM 产品 return @返回值 END GO declare @接收值 int exec @接收值 = pr_例七 print @接收值 CREATE PROCEDURE pr_例七_2 @返回值 int output AS BEGIN SELECT @返回值 = sum(库存量) FROM 产品 END GO 接收output的返回值也必须要用变量，如： declare @接收值 int exec pr_例七_2 @接收值 output print @接收值 CREATE PROCEDURE pr_例七_3 AS BEGIN declare @库存 int SELECT @库存= sum(库存量) FROM 产品 return '库存量为：'+CAST(@库存 as varchar(10)) END GO declare @接收值 int exec @接收值 = pr_例七_3 print @接收值 CREATE PROCEDURE pr_例七_4 @返回值 varchar(20) output AS BEGIN declare @库存 int SELECT @库存 = sum(库存量) FROM 产品 SET @返回值 = '库存量为：'+CAST(@库存 as varchar(10)) END GO declare @接收值 varchar(20) exec pr_例七_4 @接收值 output print @接收值 CREATE PROCEDURE pr_例八 @类别名称 nvarchar(15) AS BEGIN SELECT 产品ID,产品名称 FROM 产品 JOIN 类别 ON 产品.类别ID = 类别.类别ID WHERE 类别.类别名称= @类别名称 END GO exec pr_例八 N'饮料' 10.5.4 SET NOCOUNT ON 10.5.5 CREATE PROC #临时存储过程 AS SELECT * FROM 产品 GO EXEC #临时存储过程 10.5.6 CREATE PROC pr_例九_查看订购量最多的产品ID @产品ID int output AS SELECT TOP 1 @产品ID = 产品ID FROM 订单明细 GROUP BY 产品ID ORDER BY MAX(数量) DESC PRINT '存储过程“pr_例九_查看订购量最多的产品ID”的嵌套层次为：第' +CAST(@@NESTLEVEL AS VARCHAR(1))+'层' GO CREATE PROC pr_例九_查看产品的供应商ID @供应商ID int output AS DECLARE @产品编号 int exec pr_例九_查看订购量最多的产品ID @产品编号 output SELECT @供应商ID = 供应商ID FROM 产品 WHERE 产品ID = @产品编号 PRINT '存储过程“pr_例九_查看产品的供应商ID”的嵌套层次为：第' +CAST(@@NESTLEVEL AS VARCHAR(1))+'层' GO CREATE PROC pr_例九_供应商信息 AS DECLARE @供应商编号 int exec pr_例九_查看产品的供应商ID @供应商编号 output SELECT * FROM 供应商 WHERE 供应商ID = @供应商编号 PRINT '存储过程“pr_例九_供应商信息”的嵌套层次为：第' +CAST(@@NESTLEVEL AS VARCHAR(1))+'层' GO Exec pr_例九_供应商信息 10.5.8 CREATE PROC pr_例十 @类别名称 nvarchar(15) WITH ENCRYPTION AS SELECT * FROM 类别 WHERE 类别名称= @类别名称 GO 10.5.9 sp_helptext 存储过程名 exec sp_helptext pr_例八 GO exec sp_helptext pr_例十 GO 10.5.10 CREATE PROC pr_例十一;1 as select * from 类别 GO CREATE PROC pr_例十一;2 @类别名称 nvarchar(15) as select * from 类别 where 类别名称= @类别名称 GO EXEC pr_例十一;1 或 EXEC pr_例十一 exec pr_例十一;2 饮料 10.6.1 DROP { PROC | PROCEDURE } { [ schema_name. ] procedure } [ ,...n ] DROP PROC pr_例八 DROP PROC pr_例十一 10.7.1 sp_help [ [ @objname = ] 'name' ] 10.7.2 sp_helpdb [ [ @dbname= ] 'name' ] 10.7.3 sp_helpfile [ [ @filename = ] 'name' ] 10.7.4 sp_helpfilegroup [ [ @filegroupname = ] 'name' ] 10.7.5 sp_helpindex [ @objname = ] 'name' 10.7.7 sp_helpstats[ @objname = ] 'object_name' [ , [ @results = ] 'value' ] 10.7.8 sp_helptext [ @objname = ] 'name' [ , [ @columnname = ] computed_column_name ] 10.7.9 sp_helptrigger [ @tabname = ] 'table' [ , [ @triggertype = ] 'type' ] 10.7.10 sp_lock [[@spid1 = ] 'spid1'] [,[@spid2 = ] 'spid2'] 10.7.12 sp_rename [ @objname = ] 'object_name' , [ @newname = ] 'new_name' [ , [ @objtype = ] 'object_type' ] 10.7.13 sp_renamedb [ @dbname = ] 'old_name' , [ @newname = ] 'new_name' 10.7.14 sp_who [[@login_name =] 'login'] 10.7.15 sp_columns [ @table_name = ] object [ , [ @table_owner = ] owner ] [ , [ @column_name = ] column ] [ , [ @ODBCVer = ] ODBCVer ] 10.7.17 sp_fkeys [ @pktable_name = ] 'pktable_name' [ , [ @pktable_owner = ] 'pktable_owner' ] { , [ @fktable_name = ] 'fktable_name' } [ , [ @fktable_owner = ] 'fktable_owner' ] 10.7.18 sp_pkeys [ @table_name = ] 'name' [ , [ @table_owner = ] 'owner' ] 10.7.19 sp_server_info [[@attribute_id = ] 'attribute_id'] 10.7.20 sp_tables [ [ @table_name = ] 'name' ] [ , [ @table_owner = ] 'owner' ] [ , [ @table_type = ] "type" ] 10.7.21 sp_stored_procedures [ [ @sp_name = ] 'name' ] [ , [ @sp_owner = ] 'schema'] [ , [@fUsePattern = ] 'fUsePattern' ] 10.8.2 exec sp_configure 'clr','1' GO RECONFIGURE GO 10.8.3 using 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.4 csc /t:library /out:CLR_SP.dll CLR_SP.cs 10.8.5 CREATE ASSEMBLY CLR_SP FROM 'E:ookSQL Server 2008大全数据库第十章扩展存储过程SqlServerProjectSqlServerProjectCLR_SP.dll' GO 10.8.6 CREATE PROCEDURE CLRSP @outstr nvarchar(200) output AS EXTERNAL NAME CLR_SP.StoredProcedures.CLR_SP GO 10.8.7 declare @str nvarchar(200) EXEC CLRSP @str output print @str 11.5.1 CREATE TRIGGER 产品_Insert ON 产品 AFTER INSERT AS BEGIN print '又添加了一种产品' END GO CREATE TRIGGER 产品_Update ON 产品 AFTER UPDATE AS BEGIN print '有一种产品更改了' END GO CREATE TRIGGER 产品_Delete ON 产品 AFTER DELETE AS BEGIN print '又删除了一种产品' END GO 11.5.2 INSERT INTO 产品(产品名称) VALUES ('大苹果') DELETE FROM 产品 WHERE (产品名称= '大苹果') 11.5.3 CREATE 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 here END GO CREATE TRIGGER 触发器名 ON 数据表名或视图名 AFTER INSERT或DELETE或UPDATE AS BEGIN --这里是要运行的SQL语句 END GO CREATE TRIGGER 订单_Insert ON 订单 AFTER INSERT, UPDATE, DELETE AS EXEC master..xp_sendmail '张三', '订单有更改，请查询确定' GO CREATE TRIGGER 订单明细_Insert ON 订单明细 AFTER INSERT AS BEGIN if (Select 折扣 from inserted)>0.6 begin print '折扣不能大于0.6' Rollback Transaction end INSERT INTO 订单明细(订单ID,产品ID,单价,数量,折扣) VALUES (11077,1,18,1,0.7) 11.6 CREATE TRIGGER 产品_Insert1 ON 产品 AFTER INSERT AS BEGIN print '再一次告诉你，你又添加了一种产品' END GO INSERT INTO 产品(产品名称) VALUES ('大苹果') sp_settriggerorder [ @triggername = ] '[ triggerschema. ] triggername' , [ @order = ] 'value' , [ @stmttype = ] 'statement_type' [ , [ @namespace = ] { 'DATABASE' | 'SERVER' | NULL } ] Exec sp_settriggerorder '产品_Insert1','First','Insert' go Exec sp_settriggerorder '产品_Insert',’Last’,'Insert' Go Exec sp_settriggerorder '产品_Insert1','First',’Update’ go END GO 11.7 CREATE 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] GO CREATE TRIGGER 操作记录表_Insert ON 操作记录表 AFTER INSERT AS BEGIN print '数据库又有记录变动了' END GO CREATE TRIGGER 类别_Insert ON 类别 AFTER INSERT AS BEGIN Declare @类别名称 nvarchar(15), @说明 nvarchar(max) set @类别名称= (Select 类别名称 from inserted) set @说明= (Select 说明 from inserted) INSERT INTO 操作记录表(操作表名,操作语句,操作内容) VALUES ('类别表','插入记录','类别名称：'+@类别名称+',说明：'+@说明) END GO INSERT INTO 类别(类别名称,说明) VALUES ('书籍','各类图书') 11.9.2 CREATE TRIGGER 触发器名 ON 数据表名或视图名 Instead Of INSERT或DELETE或UPDATE AS BEGIN --这里是要运行的SQL语句 END GO CREATE TRIGGER 订单明细_Insert ON 订单明细 Instead Of INSERT AS 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,@单价,@数量,@折扣) END GO 11.11 ALTER TRIGGER 触发器名 ON 数据表名或视图名 AFTER INSERT或DELETE或UPDATE AS BEGIN --这里是要运行的SQL语句 END GO 11.13 Alter table 数据表名 Disable或Enable trigger 触发器名或ALL 11.15.1 CREATE 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或DATABASE FOR 或AFTER 激活DDL触发器的事件 AS 要执行的SQL语句 sp_rename ‘旧触发器名’,’新触发器名’ CREATE TRIGGER 禁止对数据表操作 ON DATABASE FOR DROP_TABLE, ALTER_TABLE AS PRINT '对不起，您不能对数据表进行操作' ROLLBACK ; （4）单击【执行】按钮，生成触发器。 CREATE TRIGGER 不允许删除数据库 ON all server FOR DROP_DATABASE AS PRINT '对不起，您不能删除数据库' ROLLBACK ; GO CREATE 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] GO CREATE TRIGGER 记录日志 ON DATABASE FOR DDL_DATABASE_LEVEL_EVENTS AS DECLARE @log XML SET @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() ) ; GO 11.15.2 CREATE TABLE 测试表( 编号int IDENTITY(1,1) NOT NULL, 测试内容varchar(50) NOT NULL) GO Drop table 测试表 GO select * from 日志记录表 GO 11.16 CREATE 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.1 CREATE TRIGGER 订单明细删除_test ON 订单明细 AFTER DELETE AS BEGIN print '您此次删除了' + Cast(@@rowcount as varchar) + '条记录' END GO Delete FROM 订单明细 where 折扣=0.25 GO Delete FROM 订单明细 where 订单ID='123456789' GO 11.17.2 ALTER TRIGGER 类别_Insert ON 类别 AFTER INSERT AS BEGIN Declare @类别名称 nvarchar(15), @说明 nvarchar(max) set @类别名称 = (Select 类别名称 from inserted) set @说明 = (Select 说明 from inserted) INSERT INTO 操作记录表 (操作表名,操作语句,操作内容) VALUES ('类别表','插入记录', '插入了ID号为'+cast(@@IDENTITY as varchar)+'的记录：类别名称：' +@类别名称+',说明：'+@说明) END GO 11.17.3 CREATE TRIGGER 只允许修改折扣 ON 订单明细 Instead Of UPDATE AS 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 '只能更改折扣字段' end END GO update 订单明细 set 折扣=0.2 where 订单ID=10288 and 产品ID=54 Go update 订单明细 set 订单ID=10288 where 订单ID=10288 and 产品ID=54 Go 11.17.4 set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER TRIGGER 只允许修改折扣 ON 订单明细 Instead Of UPDATE AS 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) end END 12.4.2 CREATE TABLE 例一 ( ID INT NOT NULL IDENTITY(1,2) PRIMARY KEY, USERNAME VARCHAR(45) UNIQUE, USERSEXY BIT, USERADD VARCHAR(200) ) 12.7.1 CREATE [ 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.3 CREATE INDEX IX_例二 ON 产品 (产品名称) 12.7.4 CREATE INDEX IX_例三 ON 雇员 (姓氏,名字) 12.7.5 CREATE UNIQUE INDEX IX_例四 ON 运货商 (公司名称) 12.7.6 CREATE TABLE 例五 ( 编号 int NOT NULL, 姓名 varchar(20), 性别 bit ) GO CREATE CLUSTERED INDEX IX_例五 ON 例五 (编号) GO 12.7.7 CREATE NONCLUSTERED INDEX IX_例六 ON 例五 (姓名 desc) 12.7.8 CREATE INDEX IX_例七 ON 订单明细 (单价,数量) INCLUDE (折扣) 12.7.9 ALTER DATABASE Northwind ADD FILEGROUP 例八文件组 GO ALTER DATABASE Northwind ADD FILE (NAME=例八文件, FILENAME='D:DBtest例八文件.ndf') TO FILEGROUP 例八文件组 GO CREATE INDEX IX_例八 ON 产品 (库存量) ON 例八文件组 GO 12.7.10 CREATE INDEX IX_例九 ON 产品 (订购量) WITH ( FILLFACTOR = 70 ) 12.7.11 CREATE INDEX IX_例九 ON 产品 (订购量) WITH ( PAD_INDEX = ON , FILLFACTOR = 70 ) 12.7.12 CREATE 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.2 sp_helpindex [ @objname = ] 'name' 12.8.3 use Northwind select * from sys.indexes 12.8.4 ALTER 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 雇员 REBUILD ALTER INDEX IX_例三 ON 雇员 REBUILD WITH (PAD_INDEX = ON, FILLFACTOR = 70) 12.9.3 ALTER INDEX IX_例五 ON 例五 REBUILD 12.9.4 ALTER INDEX IX_例五 ON 例五 REORGANIZE 12.9.6 sys.dm_db_index_physical_stats ( { database_id | NULL } , { object_id | NULL } , { index_id | NULL | 0 } , { partition_number | NULL } , { mode | NULL | DEFAULT } ) declare @databaseid int declare @objectid int set @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.2 ALTER INDEX PK_类别 ON 类别 DISABLE GO SELECT * FROM 类别 GO 12.12.3 DROP INDEX <table_name>.<index_name> DROP INDEX 类别.IX_类别名称 12.13.2 CREATE VIEW dbo.雇员订单 WITH SCHEMABINDING AS SELECT 雇员.姓氏,雇员.名字,订单.订单ID,订单.订购日期 FROM dbo.雇员 JOIN dbo.订单 ON 雇员.雇员ID = 订单.雇员ID GO CREATE UNIQUE CLUSTERED INDEX IX_雇员订单 ON 雇员订单 (订单ID) GO 13.1.1 sp_addtype [ @typename = ] type, [ @phystype = ] system_data_type [ , [ @nulltype = ] 'null_type' ] ; EXEC sp_addtype 编号,'int','not null' EXEC sp_addtype 姓名,'varchar(10)','null' 13.1.2 USE Northwind INSERT 例三(编号,姓名) VALUES (1,'刘智勇') DECLARE @ID 编号 DECLARE @name nvarchar(10) SELECT top 1 @ID = 编号, @name = 姓名 FROM 例三 ORDER BY 编号DESC print '编号为：' + CAST(@ID AS varchar(2)) print '姓名为：' + @name 13.1.3 DROP TYPE 用户定义数据类型名 sp_droptype用户定义数据类型名 DROP TABLE 例三 GO DROP TYPE 编号 GO EXEC SP_DROPTYPE 姓名 GO 13.2.3 CREATE 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() end GO PRINT dbo.例五() CREATE FUNCTION 例六(@年份 int) RETURNS money BEGIN DECLARE @销售总值 money SELECT @销售总值= SUM( 订单明细.单价 *订单明细.数量 * (1-订单明细.折扣)) FROM 订单 JOIN 订单明细 ON 订单.订单ID = 订单明细.订单ID WHERE YEAR(订购日期) = 1998 GROUP BY YEAR(订购日期) RETURN @销售总值 END GO PRINT '1998年的销售总值为' + CAST(dbo.例六(1998) as varchar(20)) +'元' GO CREATE FUNCTION 例七(@姓氏 varchar(20),@名字 varchar(10)) RETURNS int BEGIN DECLARE @订单总数 int SELECT @订单总数 = count(订单.订单ID) FROM 订单 JOIN 雇员 ON 订单.雇员ID = 雇员.雇员ID WHERE 雇员.姓氏 = @姓氏 AND 雇员.名字 = @名字 RETURN @订单总数 END GO Select dbo.例七('王','伟') GO 13.2.4 CREATE 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 @结束时间 GO SELECT * FROM 例八('1996-7-1','1996-12-1') ORDER BY 订购日期 GO 13.2.5 CREATE 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 END GO SELECT * 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 END GO SELECT * 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 @编号 END GO --创建一个数据表 CREATE TABLE tb_例十一 ( --将编号的默认值设为“dbo.例十一_编号()”函数 编号varchar(7) DEFAULT dbo.例十一_编号(), 名称nvarchar(10) ) GO --在数据表中插入记录 INSERT tb_例十一(名称) VALUES ('测试一') INSERT tb_例十一(名称) VALUES ('测试二') INSERT tb_例十一(名称) VALUES ('测试三') --查看数据表的内容 SELECT * FROM tb_例十一 GO 13.2.9 DROP FUNCTION { [ schema_name. ] function_name } [ ,...n ] DROP FUNCTION 例五,例十 14.7.1 CONTAINS ( { 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.2 SELECT * FROM 文章 WHERE FREETEXT(内容,'教育部') SELECT * FROM 文章 WHERE CONTAINS (内容,'教育部') 14.7.3 CONTAINSTABLE ( 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 table1 SELECT * FROM 文章 JOIN CONTAINSTABLE(文章,内容,' "教育部" NEAR "表示" ',10) as table1 ON 文章.编号= table1.[KEY] ORDER BY table1.RANK DESC SELECT * FROM 文章 JOIN CONTAINSTABLE(文章,内容, 'ISABOUT ("download" weight(0.9), "上海" weight(0.6), "山西" weight(0.1))') AS TABLE1 ON 文章.编号 = TABLE1.[KEY] ORDER BY TABLE1.RANK DESC 14.7.4 FREETEXTTABLE (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.5 SELECT 编号,标题,文件,扩展名 FROM 文章 WHERE CONTAINS(文件,'数据库') 14.8.1 CREATE 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:ookSQL Server 2008大全数据库第十四章运行后数据库' AS DEFAULT 14.8.2 ALTER FULLTEXT CATALOG catalog_name { REBUILD [ WITH ACCENT_SENSITIVITY = { ON | OFF } ] | REORGANIZE | AS DEFAULT } ALTER FULLTEXT CATALOG TSQL全文目录 REBUILD 14.8.3 CREATE 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.4 ALTER 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 文章 DISABLE ALTER FULLTEXT INDEX ON 文章 ENABLE ALTER FULLTEXT INDEX ON 文章 DROP (文件) ALTER FULLTEXT INDEX ON 文章 START FULL POPULATION 14.8.5 DROP FULLTEXT INDEX ON table_name DROP FULLTEXT INDEX ON 文章 14.8.6 DROP FULLTEXT CATALOG catalog_name DROP FULLTEXT CATALOG TSQL全文目录 14.7.1 CONTAINS ( { 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.2 FREETEXT ( { column_name | (column_list) | * } , 'freetext_string' [ , LANGUAGE language_term ] ) SELECT * FROM 文章 WHERE FREETEXT(内容,'教育部') SELECT * FROM 文章 WHERE CONTAINS (内容,'教育部') 14.7.3 CONTAINSTABLE ( 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 table1 SELECT * FROM 文章 JOIN CONTAINSTABLE(文章,内容,' "教育部" NEAR "表示" ',10) as table1 ON 文章.编号= table1.[KEY] ORDER BY table1.RANK DESC SELECT * FROM 文章 JOIN CONTAINSTABLE(文章,内容, 'ISABOUT ("download" weight(0.9), "上海" weight(0.6), "山西" weight(0.1))') AS TABLE1 ON 文章.编号 = TABLE1.[KEY] ORDER BY TABLE1.RANK DESC 14.7.4 FREETEXTTABLE (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.5 SELECT 编号,标题,文件,扩展名 FROM 文章 WHERE CONTAINS(文件,'数据库') 14.8.1 CREATE 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:ookSQL Server 2008大全数据库第十四章运行后数据库' AS DEFAULT 14.8.2 ALTER FULLTEXT CATALOG catalog_name { REBUILD [ WITH ACCENT_SENSITIVITY = { ON | OFF } ] | REORGANIZE | AS DEFAULT } ALTER FULLTEXT CATALOG TSQL全文目录 REBUILD 14.8.3 CREATE 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.4 ALTER 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 文章 DISABLE ALTER FULLTEXT INDEX ON 文章 ENABLE ALTER FULLTEXT INDEX ON 文章 DROP (文件) ALTER FULLTEXT INDEX ON 文章 START FULL POPULATION 14.8.5 DROP FULLTEXT INDEX ON table_name DROP FULLTEXT INDEX ON 文章 14.8.6 DROP FULLTEXT CATALOG catalog_name DROP FULLTEXT CATALOG TSQL全文目录 16.1.2 --开始事务 BEGIN TRAN DECLARE @订单ID int --添加一个订单 INSERT 订单 (客户ID,雇员ID,订购日期,货主名称,货主地址,货主城市,货主地区, 货主邮政编码,货主国家) VALUES ('VINET',2,GETDATE(),'余小姐','光明北路124 号','北京','华北', '111080','中国') IF @@ERROR > 0 GOTO TranRoolBack SET @订单ID = @@IDENTITY --添加两个订单详情 INSERT 订单明细(订单ID,产品ID,单价,数量,折扣) VALUES (@订单ID,14,$200,1,0) IF @@ERROR > 0 GOTO TranRoolBack INSERT 订单明细(订单ID,产品ID,单价,数量,折扣) VALUES (@订单ID,51,$200,1,0) IF @@ERROR > 0 GOTO TranRoolBack TranRoolBack: IF @@ERROR > 0 OR @@ROWCOUNT<>1 ROLLBACK TRAN --如果发生错误则回滚事务 ELSE COMMIT TRAN --如果没有发生错误则提交事务 GO 16.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 <> 1 BEGIN TranRollBack: ROLLBACK TRAN --回滚事务 END ELSE 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 <> 1 BEGIN TranRollBack: ROLLBACK TRAN --回滚事务 END ELSE COMMIT TRAN --提交事务 --事务结束 --事务结束后的数据集 SELECT 类别ID,类别名称FROM 类别ORDER BY 类别ID DESC 16.3.2 --查看事务执行之前的记录 SELECT * FROM 产品 --开始事务 BEGIN TRAN --插入两条记录 INSERT 产品(产品名称,类别ID) VALUES ('西瓜汁',1) IF @@ERROR> 0 OR @@ROWCOUNT <>1 GOTO TranRollBack INSERT 产品(产品名称,类别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 >0 BEGIN TranRollBack: ROLLBACK TRAN END ELSE COMMIT TRAN --查看所有事务完成后的情况 SELECT * FROM 产品 --查看事务执行之前的记录 SELECT * FROM 产品 --开始事务 BEGIN TRAN --插入两条记录 INSERT 产品(产品名称,类别ID) VALUES ('西瓜汁',1) IF @@ERROR> 0 OR @@ROWCOUNT <>1 GOTO TranRollBack INSERT 产品(产品名称,类别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 >0 BEGIN TranRollBack: ROLLBACK TRAN END ELSE COMMIT TRAN --查看所有事务完成后的情况 SELECT * FROM 产品 16.3.3 BEGIN TRAN PRINT '当前事务嵌套层次为：' + CAST(@@TRANCOUNT AS VARCHAR(2)) INSERT 产品(产品名称,类别ID) VALUES ('西瓜汁',1) IF @@ERROR> 0 OR @@ROWCOUNT <>1 GOTO TranRollBack INSERT 产品(产品名称,类别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 >0 BEGIN TranRollBack: ROLLBACK TRAN END ELSE COMMIT TRAN 16.4 SAVE { 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 TranRollBack INSERT 产品(产品名称,类别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 >0 BEGIN TranRollBack: ROLLBACK TRAN END ELSE COMMIT TRAN --查看所有事务完成后的情况 SELECT * FROM 产品 16.5 SET TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SNAPSHOT | SERIALIZABLE } [ ; ] 17.1.3 CREATE STATISTICS ON < table_name > [ . <index_name> ] WITH FULLSCAN {, NORECOMPUTE } CREATE STATISTICS 类别名称 ON 类别 (类别名称) 17.1.4 UPDATE 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.5 DROP STATISTICS table.statistics_name | view.statistics_name [ ,...n ] DROP STATISTICS 类别.类别名称 17.2.2 CREATE 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.3 CREATE SYNONYM local_类别FOR 类别 GO SELECT * FROM local_类别 GO CREATE PROC pr_存储过程 AS SELECT * FROM 类别 GO CREATE SYNONYM local_存储过程 FOR pr_存储过程 GO exec local_存储过程 SELECT * FROM server_类别 SELECT * FROM server_类别 17.2.4 DROP SYNONYM [ schema. ] synonym_name DROP SYNONYM local_存储过程 18.1 sp_addumpdevice [ @devtype = ] 'device_type' , [ @logicalname = ] 'logical_name' , [ @physicalname = ] 'physical_name' ] exec sp_addumpdevice 'disk','新备份设备', 'E:ookSQL Server 2008大全数据库第十八章运行后数据库新备份设备.bak' 18.3.1 BACKUP 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 FilesMicrosoft SQL ServerMSSQL.1MSSQLBackupbackup.BAK' BACKUP DATABASE Northwind TO 新备份设备 18.3.2 BACKUP 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.3 BACKUP 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.4 RESTORE 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.3 RESTORE VERIFYONLY FROM <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 DIFFERENTIAL RESTORE VERIFYONLY FROM 新备份设备 WITH FILE = 2 RESTORE VERIFYONLY FROM DISK='D:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackupbackup.bak' 18.6.1 RESTORE 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 master RESTORE DATABASE Northwind FROM Northwind备份 USE master RESTORE DATABASE Northwind FROM Northwind备份 WITH FILE = 6 USE master RESTORE DATABASE Northwind FROM DISK='D:Program FilesMicrosoft SQL ServerMSSQL.1MSSQL Backupbackup.bak' 18.6.2 USE master RESTORE DATABASE Northwind FROM Northwind备份 WITH FILE = 1,NORECOVERY GO RESTORE DATABASE Northwind FROM Northwind备份 WITH FILE = 3 GO 18.6.3 USE master RESTORE DATABASE Northwind FROM Northwind备份 WITH FILE = 1,NORECOVERY GO RESTORE DATABASE Northwind FROM Northwind备份 WITH FILE = 2 GO USE master RESTORE DATABASE Northwind FROM Northwind备份 WITH FILE = 1,NORECOVERY GO RESTORE LOG Northwind FROM Northwind备份 WITH FILE = 2 GO 18.6.4 USE master RESTORE DATABASE Northwind FILEGROUP = 'PRIMARY' FROM Northwind备份 GO RESTORE LOG Northwind FROM Northwind备份 WITH FILE = 15 GO 18.6.5 USE master RESTORE DATABASE Northwind FROM Northwind备份 WITH FILE = 17,NORECOVERY GO RESTORE LOG Northwind FROM Northwind备份 WITH FILE = 18,STOPAT = '2006-9-21 9:14:00' GO 18.6.6 USE master RESTORE 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' GO 19.1.3 USE model CREATE TABLE ModelTable ( 编号int, 姓名nvarchar(50) ) GO USE master CREATE DATABASE test ON ( NAME='test', FILENAME='D: est.mdf' ) GO USE test SELECT * FROM ModelTable 19.1.5 select * from sys.indexes20.4.9 insert 类别 (类别名称) 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.2 xp_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.4 USE Northwind GO SELECT 类别ID,类别名称 FROM 类别 FOR XML RAW, XMLDATA 28.2.5 USE Northwind GO SELECT 类别ID AS "@类别编号",类别名称 AS "类别/@类别名称", 说明 AS "说明/text()" FROM 类别 FOR XML PATH USE Northwind GO SELECT 类别ID AS "@类别编号",类别名称 AS "类别名称", 说明 AS "说明/text()" FROM 类别 FOR XML PATH('类别') 28.2.6 USE Northwind GO SELECT 类别ID AS "@类别编号",类别名称 AS "类别名称", 说明 AS "说明/text()" FROM 类别 FOR XML PATH('类别') ,ROOT('类别表') USE Northwind GO SELECT 类别ID,类别名称 FROM 类别 FOR XML RAW, XMLSCHEMA,ROOT('类别表') 28.2.7 OPENXML( 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, @myXML SELECT * FROM OPENXML(@handle, N'/类别表/类别/说明') --删除句柄 EXEC sp_xml_removedocument @handle 28.3.1 CREATE TABLE 文档表 ( 文档编号int IDENTITY(1,1) PRIMARY KEY, 文档标题nvarchar(50), 文档内容xml ) 28.3.2 DECLARE @myXML xml SET @myXML = (SELECT 产品.产品ID,类别.类别名称,产品.产品名称,产品.单价,产品.库存量FROM 类别 JOIN 产品 ON 类别.类别ID = 产品.类别ID FOR XML RAW('产品'),ELEMENTS ) SELECT @myXML DECLARE @myXML xml SET @myXML = ' <产品名称>柠檬汁</产品>' select @myXML DECLARE @myXML xml 28.3.3 INSERT 文档表(文档标题,文档内容) VALUES ('隐型转换','<文档内容>该字段以隐形转换方式转为xml数据</文档内容>') INSERT 文档表(文档标题,文档内容) VALUES ('显式转换', CAST('<文档内容>该字段以显式转换方式转为xml数据</文档内容>' as xml)) INSERT 文档表(文档标题,文档内容) VALUES ('显式转换', CONVERT(xml,'<产品> <产品ID>76</产品ID> <类别名称>饮料</类别名称> <说明>软饮料、咖啡、茶、啤酒和淡啤酒</说明> <产品名称>柠檬汁</产品名称> <单价>18.0000</单价> <库存量>57</库存量> </产品>')) 28.4.3 CREATE [ 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 VALUE 28.5.1 DECLARE @myXML xml SET @myXML = '<类别表> <类别 类别编号="1"> <类别名称>饮料</类别名称> <说明>软饮料、咖啡、茶、啤酒和淡啤酒</说明> </类别> <类别 类别编号="2"> <类别名称>调味品</类别名称> <说明>香甜可口的果酱、调料、酱汁和调味品</说明> </类别> <类别 类别编号="3"> <类别名称>点心</类别名称> <说明>甜点、糖和甜面包</说明> </类别> </类别表>' select @myXML.query('/类别表/类别') 28.5.2 DECLARE @myXML xml DECLARE @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 @Name 28.5.3 exist (XQuery) DECLARE @myXML xml DECLARE @IfExist bit SET @myXML = '<类别表> <类别 类别编号="1"> <类别名称>饮料</类别名称> <说明>软饮料、咖啡、茶、啤酒和淡啤酒</说明> </类别> <类别 类别编号="2"> <类别名称>调味品</类别名称> <说明>香甜可口的果酱、调料、酱汁和调味品</说明> </类别> <类别 类别编号="3"> <类别名称>点心</类别名称> <说明>甜点、糖和甜面包</说明> </类别> </类别表>' --查看是否存在类别名称为“饮料”的节点 SET @IfExist = @myXML.exist('/类别表/类别/类别名称[text()="饮料"]') SELECT @IfExist --查看是否存在类别编号为“”的节点 SET @IfExist = @myXML.exist('/类别表/类别/@类别编号=1') SELECT @IfExist --查看是否存在类别名称为“图书”的节点 SET @IfExist = @myXML.exist('/类别表/类别/类别名称[text()="图书"]') SELECT @IfExist 28.5.4 modify (XML_DML) insert Expression1 ( {as first | as last} into | after | before Expression2 ) DECLARE @myXML xml SET @myXML = '<类别表> <类别 类别编号="1"> <类别名称>饮料</类别名称> <说明>软饮料、咖啡、茶、啤酒和淡啤酒</说明> </类别> <类别 类别编号="2"> <类别名称>调味品</类别名称> <说明>香甜可口的果酱、调料、酱汁和调味品</说明> </类别> <类别 类别编号="3"> <类别名称>点心</类别名称> <说明>甜点、糖和甜面包</说明> </类别> </类别表>' SET @myXML.modify('insert <类别 类别编号="4"> <类别名称>图书</类别名称> </类别> into (/类别表)[1]') SET @myXML.modify('insert <产品>该类别的产品</产品> into (/类别表/类别)[2]') SELECT @myXML 28.5.5 replace value of Expression1 with Expression2 DECLARE @myXML xml SET @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 @myXML DECLARE @myXML xml SET @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 @myXML 28.5.6 DECLARE @myXML xml SET @myXML = '<类别表> <类别 类别编号="1"> <类别名称>饮料</类别名称> <说明>软饮料、咖啡、茶、啤酒和淡啤酒</说明> </类别> <类别 类别编号="2"> <类别名称>调味品</类别名称> <说明>香甜可口的果酱、调料、酱汁和调味品</说明> </类别> <类别 类别编号="3"> <类别名称>点心</类别名称> <说明>甜点、糖和甜面包</说明> </类别> </类别表>' SET @myXML.modify('delete /类别表/类别[1]/说明') SET @myXML.modify('delete /类别表/类别[2]/@类别编号') SET @myXML.modify('delete /类别表/类别[3]') SELECT @myXML 29.3.1 using 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.2 using 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.1 ALTER DATABASE database_name SET COMPATIBILITY_LEVEL = { 80 | 90 | 100 } --设置兼容级别，值为80，90，100之一 --查询所有数据库的兼容级别 select name,compatibility_level from sys.databases 30.1.4 ALTER DATABASE SampleDB --修改数据库SampleDB的兼容级别 SET COMPATIBILITY_LEVEL = 90; --数据库SampleDB的兼容级别设置为90 DECLARE @id int; SELECT @id = id FROM test --对变量进行赋值 UNION ALL SELECT @id = id FROM TestTable; SELECT @id; --输出@id的值 SELECT DATEPART (year, '2009/04-30') --获取年份信息，但该日期不是通常意义的有效日期 30.1.5 ALTER DATABASE SampleDB --修改数据库SampleDB的兼容级别 SET COMPATIBILITY_LEVEL = 90; --数据库SampleDB的兼容级别设置为90 30.1.6 CREATE TABLE TestTable --创建表TestTable ( id int, age int ); SELECT id, age AS id --查询表TestTable，表中列age的别名设置为id，与id列同名 FROM TestTable ORDER BY id; --采用order by ALTER DATABASE SampleDB SET COMPATIBILITY_LEVEL = 80; --修改兼容级别为80 SELECT id, age AS id --查询表TestTable，表中列age的别名设置为id，与id列同名 FROM TestTable ORDER BY id; --采用order by ALTER DATABASE SampleDB SET COMPATIBILITY_LEVEL = 100; --修改兼容级别为100 SELECT id, age AS ages --查询表TestTable，表中列age的别名设置为ages，与id列名不同 FROM TestTable ORDER BY id; --采用order by ALTER DATABASE SampleDB SET COMPATIBILITY_LEVEL = 80 --修改兼容级别为80 SELECT id, age AS ages --查询表TestTable，表中列age的别名设置为ages，与id列名不同 FROM TestTable ORDER BY id; --Order by在列别名前使用表前缀 ALTER DATABASE SampleDB SET COMPATIBILITY_LEVEL = 100; --修改兼容级别为100 SELECT id as userid --查询表TestTable，表中列id的别名设置为userid FROM TestTable ORDER BY TestTable.userid; --采用order by ALTER DATABASE SampleDB SET COMPATIBILITY_LEVEL = 80 --修改兼容级别为80 SELECT id as userid --查询表TestTable，表中列id的别名设置为userid FROM TestTable ORDER BY TestTable.userid; --Order by在列别名前使用表前缀 ALTER DATABASE SampleDB SET COMPATIBILITY_LEVEL = 100 --修改兼容级别为100 SELECT id as userid --查询表TestTable，表中列id的别名设置为userid FROM TestTable ORDER BY userid; --order by中的列别名的表前缀已经去掉 ALTER DATABASE SampleDB SET COMPATIBILITY_LEVEL = 80 --修改兼容级别为80 SELECT id as userid --查询表TestTable，表中列id的别名设置为userid FROM TestTable ORDER BY userid; --order by中的列别名的表前缀已经去掉 30.2.1 expression += expression DECLARE @var int = 1; --声明变量var,并将其值设置为1 SET @var += 2 ; --对变量var和数值2使用+=运算符 30.2.2 expression -= expression DECLARE @var int = 2; --声明变量var,并将其值设置为2 SET @var *= 2 ; --对变量var和数值2使用*=运算符（变量var的值被设置为4） DECLARE @var int = 4; --声明变量var,并将其值设置为4 SET @var /= 2 ; --对变量var和数值2使用/=运算符（变量var的值被设置为2） DECLARE @var int = 7; --声明变量var,并将其值设置为7 SET @var += 2 ; --对变量var和数值2使用%=运算符（变量var的值被设置为1） DECLARE @var int = 9; --声明变量var,并将其值设置为9 SET @var &= 13 ; --对变量var和数值13使用&=运算符（变量var的值被设置为9） DECLARE @var int = 12; --声明变量var,并将其值设置为12 SET @var ^= 6 ; --对变量var和数值6使用^=运算符（变量var的值被设置为10） DECLARE @var int = 3; --声明变量var,并将其值设置为3 SET @var |= 2 ; --对变量var和数值2使用|=运算符（变量var的值被设置为3） 30.3.1 CONVERT ( data_type [ ( length ) ] , expression [ , style ] ) 30.3.2 --将二进制值转换为ASCII SELECT CONVERT(char(6), 0x53616D706C65, 0) AS '转换结果' --Style为1时将截断 SELECT CONVERT(char(12), 0x4E616d65, 1) AS '转换结果' --Style为2时将不截断，且转换后的字符值没有前缀0x SELECT 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.3 SELECT 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'); --缺了日期数据，所以返回2 SELECT DATEPART(hour, '2009-04-12') --缺了时间数据，小时的返回值为0 ,DATEPART(minute, '2009-04-12') --缺了时间数据，分钟的返回值为0 ,DATEPART(second, '2009-04-12'); --缺了时间数据，秒的返回值为0 30.5.1 GROUP 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.2 GROUP 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.3 SELECT id, age,COUNT(id) as count FROM Test group by GROUPING SETS(id,age) --使用Grouping Sets进行聚集，一次使用id一次使用age 30.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 } | $action 30.6.2 Merge 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.7 CREATE VIEW [dbo].[vUser] --创建视图vUser AS SELECT dbo.Users.id, dbo.Users.name, dbo.Test.age FROM dbo.Users INNER JOIN --该视图依赖于表Users和Test dbo.Test ON dbo.Users.id = dbo.Test.id SELECT * 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.1 CREATE TYPE dbo.TableValue AS TABLE --创建用户表类型TableValue ( c1 int NOT NULL, --该表类型有两个字段，这两个字段的类型为int c2 int NULL, PRIMARY KEY (c1) ) GO declare @tv TableValue --定义表类型变量 INSERT INTO @tv(c1,c2) --向表变量中插入多行数据 VALUES (1,2), (2,3), (3,4), (4,5) 30.8.2 declare @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.3 CREATE PROCEDURE sp_TableTypePro --定义存储过程 @tv TableValue --使用表类型作为参数，但不是READONLY AS BEGIN insert into test(id,age) select c1,c2 from @tv insert into @tv(c1,c2) values(71,12) --在表类型为READONLY时不能对表值参数插入数据 END GO CREATE PROCEDURE sp_TableTypePro @tv TableValue READONLY AS BEGIN SET NOCOUNT ON; insert into test(id,age) select c1,c2 from @tv END GO 30.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.1 sp_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 SampleDB GO --执行存储过程，预估表Users采用行压缩后的占用空间 EXEC sp_estimate_data_compression_savings NULL, 'Users', NULL, NULL, 'ROW' ; GO CREATE 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 ) ; GO 31.1.2 --设置文件流访问级别为已启用完全访问 EXEC sp_configure filestream_access_level, 2 RECONFIGURE CREATE TABLE Pictures ( --Id为ROWGUIDCOL [Id] [uniqueidentifier] ROWGUIDCOL NOT NULL UNIQUE, [PictureName] NVARCHAR(50) UNIQUE, --将列Picture设置为使用FILESTREAM [Picture] VARBINARY(MAX) FILESTREAM NULL ) GO INSERT INTO Pictures VALUES (newid (), '测试图片2', -- 向FILESTREAM列插入数据 CAST ('测试图片2' as varbinary(max))); GO -- 向表中插入数据，其中Picture列为NULL INSERT INTO Pictures VALUES (newid (), '测试图片', NULL); GO --获取列Picture的文件路径 SELECT TOP (200) Id, Picture.PathName() AS PathName FROM Pictures 31.1.3 CREATE 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 '--指定该列为稀疏列 ) GO ALTER 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插入值--' ) GO INSERT 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]=125 GO CREATE 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() --表示插入数据的位置位于层次化结构的根 , '数码产品' ) GO GetDescendant(child1,child2) DECLARE @root hierarchyid SELECT @root = hierarchyid::GetRoot() --获取顶级分类的hierarchyid值 FROM Category INSERT INTO [SampleDB].[dbo].[Category] ([CategoryNode] ,[CategoryName]) VALUES (@root .GetDescendant(NULL, NULL), --获取子分类的hierarchyid值 'MP3' ) GO DECLARE @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.1 parent. IsDescendantOf ( child ) DECLARE @CurrentCategoryNode hierarchyid --表示当前的分类 SELECT @CurrentCategoryNode = [CategoryNode] FROM Category WHERE [CategoryName] = 'MP3' ; --设置当前的分类为"MP3" SELECT [CategoryNode].ToString(), --显示层次关系 [CategoryNodeLevel],[CategoryName] FROM Category WHERE [CategoryNode].IsDescendantOf(@CurrentCategoryNode) = 1 ; --判断是否是当前分类的子级 --表示当前的分类 DECLARE @CurrentCategoryNode hierarchyid --设置当前的分类为"MP3" SELECT @CurrentCategoryNode = [CategoryNode] FROM Category WHERE [CategoryName] = 'MP3' ; SELECT [CategoryNode].ToString() as CategoryNode,[CategoryNodeLevel],[CategoryName] FROM Category WHERE [CategoryNode].GetAncestor(1) = @CurrentCategoryNode ; --返回父级为“MP3”的所有直接子级。 DECLARE @CurrentCategoryNode hierarchyid SELECT @CurrentCategoryNode = [CategoryNode] FROM Category WHERE [CategoryName] = 'iPod' ; --设置当前的分类为"iPod" print @CurrentCategoryNode.ToString() SELECT [CategoryNode].ToString() as CategoryNode,[CategoryNodeLevel],[CategoryName] FROM Category WHERE @CurrentCategoryNode.IsDescendantOf([CategoryNode]) = 1 ; --判断是否为“iPod”的父级 31.2.2 SWITCHOFFSET(Datetimeoffset,时区) --将当前系统日期转换为9时区的时间 SELECT SWITCHOFFSET(SYSDATETIMEOFFSET(), '+09:00') DECLARE @date1 date DECLARE @datetime1 datetime SELECT @date1 = '2009-1-1' SELECT @datetime1 = @date1 --执行隐式数据转换 SELECT @date1 , @datetime1 DECLARE @testdate date DECLARE @testdate1 date DECLARE @testsmalldatetime smalldatetime DECLARE @testsmalldatetime1 smalldatetime SELECT @testdate = '1810-10-25' SELECT @testdate1 = '1900-10-25' SELECT @testsmalldatetime = @testdate --执行数据转换 SELECT @testsmalldatetime1 = @testdate1 --超出smalldatetime的范围，引发错误。 SELECT @testdate ,@testdate1, @testsmalldatetime,@testsmalldatetime1 --声明变量 DECLARE @testdate date DECLARE @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 datetime SELECT @time = '12:00:00.1234' SELECT @datetime = @time --执行类型转换，秒的小数部分将被截断为3位 SELECT @time AS '@time', @datetime AS '@datetime' DECLARE @time time(4) DECLARE @smalldatetime smalldatetime SELECT @time = '12:15:12.1234' SELECT @smalldatetime = @time --执行类型转换，秒及其小数部分设置为0 SELECT @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() ); GO INSERT 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)); GO 31.2.4 CREATE 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) ) GO DECLARE @MyTable TypeSample --定义的表类型TypeSample变量@MyTable INSERT 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 @MyTable CREATE TABLE [dbo].[UdtSample]( [id] [int] NOT NULL PRIMARY KEY, [text] [char](10) NOT NULL, [time] [datetime] NOT NULL, ) create PROCEDURE UdtSampleInsert @table TypeSample readonly --定义用户定义表类型作为参数 AS BEGIN SET NOCOUNT ON; INSERT INTO [SampleDB].[dbo].[UdtSample] ([id] ,[text] ,[time]) select * from @table --将传进来的用户自定义类型的值插入到表UdtSample相应的字段中 END GO DECLARE @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.5 using 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列的数据类型为Point Go INSERT INTO [SampleDB].[dbo].[PointSample] ([column1]) VALUES ('1,2') --列column1的值，将使用Point类的Parse方法自动为Point类型 GO INSERT INTO [SampleDB].[dbo].[PointSample] ([column1]) VALUES ('1,e') --列column1的值，不是有效值，数据库将阻止插入 GO 31.2.5.3 select column1 from PointSample --查询Point的数据 31.3.4 declare @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.1 ASYMKEYPROPERTY (Key_ID , 'algorithm_desc' | 'string_sid' | 'sid') SELECT ASYMKEYPROPERTY(258, 'algorithm_desc') AS Algorithm, --获取非对称密钥的算法说明 ASYMKEYPROPERTY(258, 'string_sid') AS String_SID, --获取密钥的nvarchar格式的SID ASYMKEYPROPERTY(258, 'sid') AS SID ; --获取密钥的二进制格式的SID GO 32.1.1 CRYPT_GEN_RANDOM ( Length[ , seed ] ) --生成随机数 SELECT CRYPT_GEN_RANDOM(10) ; 32.1.2 CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password' CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Sample' DROP MASTER KEY ALTER 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 master CREATE CERTIFICATE CertificateSample WITH SUBJECT = 'CertificateSample' Go 32.1.2 DROP 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 --使用证书CertificateSample ALTER DATABASE ENCRYPTION KEY REGENERATE WITH ALGORITHM = { AES_128 | AES_192 | AES_256 | TRIPLE_DES_3KEY } | ENCRYPTION BY SERVER CERTIFICATE Encryptor_Name Use SampleDB ALTER DATABASE ENCRYPTION KEY REGENERATE WITH ALGORITHM = AES_256; --更改加密算法 GO 32.1.2.7 ALTER DATABASE [DataBsseName] --要启用加密的数据库名称 ENCRYPTION {ON | OFF} --设置为是否使用加密 32.2.2 insert 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 --将数据库切换为SampleDB CREATE DATABASE AUDIT SPECIFICATION TableAudit --创建数据库审核规范TableAudit FOR 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), --设置身份验证类型为KERBEROS PORTS = (CLEAR), --指定传入的请求必须为HTTP SITE = 'SqlServer2008') --主机名 FOR SOAP ( --指定负载类型为SOAP WSDL = DEFAULT, --为提交给端点的WSDL查询生成并返回默认WSDL响应 DATABASE = SampleDB, --执行请求的数据库 ) 33.3.2 use SampleDB select * from SSISSample --查询表SSISSample中的数据 35.1.2 SELECT [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