SQL Server 命令备忘录

配置 SQL Server 数据库

EXEC  sp_dboption 'pubs', 'read only', 'True'
此选项把数据库 "pubs" 设置为只读。

EXEC sp_dboption 'pubs' autoshrink true
此选项把符合条件的"pubs" 数据库文件设置为自动周期性收缩。

EXEC sp_dboption 'pubs' single user
此命令每次只允许一个用户访问数据库。

DBCC SHRINKDATABASE(PUBS, 10)
会减小"pubs"数据库中文件的大小，并允许有 10% 的可用空间。

使用 T-SQL 创建表
 create table MyTable
 (stdID int,stdName varchar(50))

自定义数据类型
EXEC sp_addtype City, 'nvarchar(15)', NULL
EXEC sp_addtype PostCode, 'nvarchar(6)', NULL
EXEC sp_addtype NCode, 'nvarchar(18)', NULL

删除自定义数据类型
EXEC  sp_droptype city

使用 T-SQL 创建表
CREATE TABLE Categories
(CategoryID int IDENTITY(1,1),
 CategoryName nvarchar(15) NOT NULL,
 Description ntext NULL,
 Picture image NULL)

实体完整性实现

主键约束
CREATE TABLE jobs(
Job_id smallint PRIMARY KEY,
job_desc varchar(50) NOT NULL)

唯一约束
CREATE TABLE jobs(
Job_id smallint UNIQUE)

标识列
CREATE TABLE jobs(
Job_id smallint IDENTIRY（2,1） PRIMARY KEY)

Uniqueidentifier数据类型和NEWID函数
CREATE TABLE Customer
(CustID uniqueidentifier NOT NULL DEFAULT NEWID(),
 CustName char(30) NOT NULL)

INSERT Customer VALUES (NEWID(),'ASB')

引用完整性实现
ALTER TABLE dbo.Orders
ADD CONSTRAINT FK_Orders_Customers
FOREIGN KEY (CustomerID) 
REFERENCES dbo.Customers(CustomerID)

更新表结构
ALTER TABLE MyTable ALTER COLUMN NullCOl NVARCHAR(20) NOT NULL
ALTER TABLE jobs ADD HIRE_DATE DATETIME
ALTER TABLE Doc_ED DROP COLUMN Column_B
ALTER TABLE Doc_ED WITH NOCHECK ADD CONSTRAINT Exd_Check CHECK (Column_a > 1)

在已有字段中增加标识约束时，必须先删除该字段，再新建字段。
alter table mytable drop column userid
alter table mytable add userid int identity(1,1)

删除表
DROP TABLE Airlines_Master

DEFAULT约束
USE Northwind
CREATE TABLE ABC
(ASD int Default 8, ASDE varchar(20) Default 'UNKNOWN')

USE Northwind
ALTER TABLE dbo.Customers ADD CONSTRAINT DF_contactname DEFAULT 'UNKNOWN' FOR ContactName

CHECK约束
CREATE TABLE ABCD
(ASD int CHECK(ASD <100) ,
 ASDE varchar(80) Default 'UNKNOWN')

USE Northwind

ALTER TABLE Employees ADD CONSTRAINT CK_birthdate
CHECK (BirthDate > '01-01-1900' AND BirthDate < '01-01-2010')

PRIMARY KEY约束
CREATE TABLE AWC
(ASD int CHECK(ASD <100) PRIMARY KEY,
 ASDE varchar(80) Default 'UNKNOWN')

USE Northwind
ALTER TABLE Customers ADD CONSTRAINT PK_Customers PRIMARY KEY (CustomerID)

UNIQUE约束
CREATE TABLE AAC
(ASD int CHECK(ASD <100) PRIMARY KEY,
 ASDE int UNIQUE)

USE Northwind
ALTER TABLE Suppliers ADD CONSTRAINT  U_CompanyName UNIQUE (CompanyName)

FOREIGN KEY约束
CREATE TABLE ACC
(ASD int CHECK(ASD <100) PRIMARY KEY,
 ASDE int FOREIGN KEY REFERENCES AAC(ASD))

USE Northwind
ALTER TABLE Orders WITH NOCHECK ADD CONSTRAINT FK_Or_Cu
FOREIGN KEY (CustomerID) 
REFERENCES Customers(CustomerID)

向表中插入数据
语法：INSERT [INTO] 表名 （字段列表）VALUES （值列表）

例：
1.  插入指定值的一条记录
INSERT INTO MyTable (PriKey, Description) 
VALUES (123, 'A description of part 123.')

2. 从查询中获得数据（多条）
INSERT INTO MyTable  (PriKey, Description)
SELECT ForeignKey, Description FROM SomeView

更新数据行
语法：UPDATE 表名 SET 字段名 = 新值，… WHERE 条件
 
例： UPDATE TITLES SET Price = Price + 0.25*Price Where Title_ID = 'TC777'

联接：根据表之间的逻辑关系搜索多表中的数据
1． 内联接
SELECT …  FROM 表1  INNER JOIN  表2  ON  表1.字段1=表2.字段1  WHERE 条件

例：UPDATE TITLES SET Price = Price + 10 From Titles INNER JOIN TitleAuthor
ON Titles.Title_IS = TitleAuthor.Title_ID WHERE Titles.Title = 'SuShi,AnyOne?'

2． 外联接
左联接：左表与右表相联接的数据和左表的其他数据：
SELECT …  FROM 表1  LEFT  JOIN  表2  ON  表1.字段1=表2.字段1 WHERE 条件

例：UPDATE TITLES SET Price = Price + 10 From Titles LEFT OUTER JOIN TitleAuthor
ON Titles.Title_IS = TitleAuthor.Title_ID WHERE Titles.Title = 'SuShi,AnyOne?'

右联接：右表与左表相联接的数据和右表的其他数据：
SELECT …  FROM 表1  RIGHT  JOIN  表2  ON  表1.字段1=表2.字段1 WHERE 条件

例：UPDATE TITLES SET Price = Price + 10 From Titles RIGHT OUTER JOIN TitleAuthor
ON Titles.Title_IS = TitleAuthor.Title_ID WHERE Titles.Title = 'SuShi,AnyOne?'

全联接：右表与左表相联接的数据及右表和左表的其他数据：
SELECT …  FROM 表1  FULL  JOIN  表2  ON  表1.字段1=表2.字段1WHERE 条件

3． 自联接：表与其自身联接
SELECT …  FROM 表1  as  A  JOIN  表1  as  B  ON  A.字段1=B.字段2 WHERE 条件

删除数据
DELETE  FROM  表  WHERE  条件 (速度慢)
TRUNCATE  TABLE  表   (删除表中所有的记录，但不记日志,速度快)

查询数据
1. 数据库和所有者限定
SELECT  *  FROM  Northwind.dbo.Shippers

2. 查询中使用常量和运算符
SELECT  title_id +':'+ title + '->' + type  As  MyTitle  FROM  titles

3. 聚合函数
SELECT productid ,SUM(quantity)  AS total_quantity FROM  orderhist GROUP BY productid

显示所有type的值，并按type分组求平均值
SELECT type, AVG(price) FROM titles WHERE royalty = 10 GROUP BY ALL type

条件中带有聚合函数的，要用Having子句
SELECT productid, SUM(quantity)  AS total_quantity  FROM orderhist
 GROUP BY productid  HAVING  SUM(quantity)>=30

4. 模糊查询
WHERE companyname LIKE '%Restaurant%'

WHERE fax IS NULL

WHERE country IN ('Japan', 'Italy')

WHERE unitprice BETWEEN 10 AND 20