基本建表语法
CREATE TABLE [schema_name.]table_name (
column1 datatype [constraints],
column2 datatype [constraints],
...
[table_constraints]
);完整建表示例
-- 创建数据库
CREATE DATABASE SalesDB;
GO
-- 使用数据库
USE SalesDB;
GO
-- 创建客户表
CREATE TABLE dbo.Customers (
CustomerID INT IDENTITY(1,1) PRIMARY KEY,
FirstName NVARCHAR(50) NOT NULL,
LastName NVARCHAR(50) NOT NULL,
Email NVARCHAR(100) UNIQUE,
Phone VARCHAR(20),
Address NVARCHAR(200),
City NVARCHAR(50),
State CHAR(2),
ZipCode VARCHAR(10),
RegistrationDate DATETIME DEFAULT GETDATE(),
CreditLimit DECIMAL(10,2) CHECK (CreditLimit >= 0),
CONSTRAINT CK_Email_Phone CHECK (Email IS NOT NULL OR Phone IS NOT NULL)
);
GO
-- 创建产品表
CREATE TABLE dbo.Products (
ProductID INT IDENTITY(1,1) PRIMARY KEY,
ProductName NVARCHAR(100) NOT NULL,
Description NVARCHAR(500),
CategoryID INT NOT NULL,
UnitPrice DECIMAL(10,2) NOT NULL CHECK (UnitPrice > 0),
UnitsInStock SMALLINT DEFAULT 0 CHECK (UnitsInStock >= 0),
Discontinued BIT DEFAULT 0,
CONSTRAINT FK_Products_Categories FOREIGN KEY (CategoryID)
REFERENCES dbo.Categories(CategoryID)
);
GO
-- 创建订单表
CREATE TABLE dbo.Orders (
OrderID INT IDENTITY(1,1) PRIMARY KEY,
CustomerID INT NOT NULL,
OrderDate DATETIME NOT NULL DEFAULT GETDATE(),
RequiredDate DATETIME,
ShippedDate DATETIME,
Status TINYINT NOT NULL DEFAULT 1,
TotalAmount DECIMAL(12,2) NOT NULL,
CONSTRAINT FK_Orders_Customers FOREIGN KEY (CustomerID)
REFERENCES dbo.Customers(CustomerID),
CONSTRAINT CK_Dates CHECK (
RequiredDate IS NULL OR RequiredDate >= OrderDate
AND ShippedDate IS NULL OR ShippedDate >= OrderDate
)
);
GO
-- 创建订单明细表
CREATE TABLE dbo.OrderDetails (
OrderID INT NOT NULL,
ProductID INT NOT NULL,
UnitPrice DECIMAL(10,2) NOT NULL,
Quantity SMALLINT NOT NULL CHECK (Quantity > 0),
Discount DECIMAL(4,2) DEFAULT 0 CHECK (Discount BETWEEN 0 AND 1),
PRIMARY KEY (OrderID, ProductID),
CONSTRAINT FK_OrderDetails_Orders FOREIGN KEY (OrderID)
REFERENCES dbo.Orders(OrderID),
CONSTRAINT FK_OrderDetails_Products FOREIGN KEY (ProductID)
REFERENCES dbo.Products(ProductID)
);
GO常用数据类型
数据类型 | 描述 | 示例 |
INT | 整数 | CustomerID INT |
DECIMAL(p,s) | 精确数值 | Price DECIMAL(10,2) |
VARCHAR(n) | 可变长度字符串 | Name VARCHAR(50) |
NVARCHAR(n) | Unicode可变字符串 | Name NVARCHAR(50) |
CHAR(n) | 固定长度字符串 | State CHAR(2) |
DATETIME | 日期和时间 | OrderDate DATETIME |
DATE | 仅日期 | BirthDate DATE |
TIME | 仅时间 | StartTime TIME |
BIT | 布尔值 | IsActive BIT |
UNIQUEIDENTIFIER | GUID | RowGUID UNIQUEIDENTIFIER |
显示第 1 条-第 10 条，共 10 条
- 1
常用约束
- 主键约束
-- 列级主键
ProductID INT PRIMARY KEY
-- 表级主键
PRIMARY KEY (OrderID, ProductID)- 外键约束
CONSTRAINT FK_Orders_Customers FOREIGN KEY (CustomerID)
REFERENCES Customers(CustomerID)- 唯一约束
Email NVARCHAR(100) UNIQUE- 检查约束
Quantity SMALLINT CHECK (Quantity > 0)- 默认值
OrderDate DATETIME DEFAULT GETDATE()- 非空约束
ProductName NVARCHAR(100) NOT NULL高级建表选项
- 创建计算列
CREATE TABLE OrderDetails (
OrderID INT,
ProductID INT,
UnitPrice DECIMAL(10,2),
Quantity SMALLINT,
Discount DECIMAL(4,2),
LineTotal AS (UnitPrice * Quantity * (1 - Discount)) PERSISTED
);- 创建分区表
CREATE PARTITION FUNCTION myRangePF1 (INT)
AS RANGE LEFT FOR VALUES (1, 100, 1000);
CREATE PARTITION SCHEME myRangePS1
AS PARTITION myRangePF1
TO (fg1, fg2, fg3, fg4);
CREATE TABLE PartitionTable (
ID INT,
Data VARCHAR(100)
) ON myRangePS1(ID);- 创建临时表
-- 本地临时表
CREATE TABLE #TempTable (ID INT, Name VARCHAR(50));
-- 全局临时表
CREATE TABLE ##GlobalTempTable (ID INT, Name VARCHAR(50));- 创建表变量
DECLARE @TableVar TABLE (
ID INT,
Name VARCHAR(50)
);修改表结构
- 添加列
ALTER TABLE Customers
ADD LastPurchaseDate DATETIME NULL;- 修改列
ALTER TABLE Customers
ALTER COLUMN Phone VARCHAR(30);- 删除列
ALTER TABLE Customers
DROP COLUMN FaxNumber;- 添加约束
ALTER TABLE Products
ADD CONSTRAINT DF_Products_Discontinued DEFAULT 0 FOR Discontinued;- 删除约束
ALTER TABLE Products
DROP CONSTRAINT DF_Products_Discontinued;最佳实践
- 始终指定schema名称（如dbo）
- 为表名和列名使用有意义的名称
- 为所有表定义主键
- 为外键关系添加适当的约束
- 为经常查询的列添加适当的索引
- 考虑使用IDENTITY列作为代理键
- 为所有列选择适当的数据类型和大小
- 为重要的业务规则添加检查约束
- 为常用查询模式设计表结构
- 考虑数据增长和性能需求
