SQL Server2008宝典 全书代码
时间：2022-03-14 02:35
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 Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Datasample_database_1.mdf>‘,
SIZE = 10MB,
MAXSIZE = 50MB,
FILEGROWTH = 10%),
( NAME = <logical_filename2, , sample_database_file2>,
FILENAME = N‘<data_filename2, , C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Datasample_database_2.ndf>‘,
SIZE = 10MB,
MAXSIZE = 50MB,
FILEGROWTH = 10%),
FILEGROUP <filegroup_1, , sample_database_filegroup1>
( NAME = <logical_filename3, , sample_database_file3>,
FILENAME = N‘<data_filename3, , C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Datasample_database_3.ndf>‘,
SIZE = 10MB,
MAXSIZE = 50MB,
FILEGROWTH = 10%),
( NAME = <logical_filename4, , sample_database_file4>,
FILENAME = N‘<data_filename4, , C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Datasample_database_4.ndf>‘,
SIZE = 10MB,
MAXSIZE = 50MB,
FILEGROWTH = 10%),
FILEGROUP <filegroup2, , sample_database_group_2>
( NAME = <logical_filename5, , sample_database_file5>,
FILENAME = N‘<data_filename5, , C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Datasample_database_5.ndf>‘,
SIZE = 10MB,
MAXSIZE = 50MB,
FILEGROWTH = 10%)
LOG ON
( NAME = <logical_log_filename1, , sample_database_log_file1>,
FILENAME = N‘<log_filename1, , C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Datasample_database_1.ldf>‘,
SIZE = 10MB,
MAXSIZE = 50MB,
FILEGROWTH = 10%),
( NAME = <logical_log_filename2, , sample_database_log_file2>,
FILENAME = N‘<log_filename2, , C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Datasample_database_2.ldf>‘,
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB)
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:\test\例三数据库.mdf‘),
(FILENAME=‘D:\test\例三数据库_log.LDF‘)
FOR ATTACH
use master
exec sp_attach_db 例三数据库,‘D:\test\例三数据库.mdf‘
use master
exec sp_attach_db 例三数据库,
‘D:\test\例三数据库.mdf‘,
‘D:\test\例三数据库_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
