一、数据表创建
CREATE TABLE table_dept(
id INT NOT NULL PRIMARY KEY IDENTITY(10000,1),
name NVARCHAR(20) NOT NULL
)
CREATE TABLE table_dept(
id INT NOT NULLIDENTITY(10000,1),
name NVARCHAR(20) NOT NULL,
CONSTRAINT [PK_dept_id] PRIMARY KEY CLUSTERED (id ASC)
)
CREATE TABLE table_employee(
id INT NOT NULL PRIMARY KEY IDENTITY(10000,1),
name COLLATE Chinese_PRC_CIAS NOT NULL UNIQUE,
age INT 18 NOT NULL
DEFAULT 18
CONSTRAINT CK_age CHECK(age > 0 AND age < 120),
gender NCHAR(1) NOT NULL DEFAULT ('男'),
deptId INT
CONSTRAINT FK_table_employee_dept
FOREIGN KEY
REFERENCES dept(id)
ON UPDATE CASCADE
ON DELETE SET NULL
)
CREATE TABLE table_order(
id INT IDENTITY(1,1) PRIMARY KEY,
price decimal(17,2) NOT NULL,
num INT NOT NULL,
total AS (price * num)
)
二、修改数据表
- 简单字段操作
ALTER TABLE table_dept ALTER COLUMN name NVARCHAR(25) COLLATE Chinese_PRC_CI_AS NOT NULL
ALTER TABLE table_dept ADD remark NVARCHAR(50)
ALTER TABLE table_dept DROP COLUMN remark
- CHECK 约束操作
ALTER TABLE table_employee ADD CONSTRAINT CK_gender (gender ='男' OR gender ='女')
ALTER TABLE table_employee
WITH NOCHECK ADD
CONSTRAINT CK_gender (gender ='男' OR gender ='女')
ALTER TABLE table_employee NOCHECK CONSTRAINT CK_gender,CK_age
ALTER TABLE table_employee NOCHECK CONSTRAINT ALL
ALTER TABLE table_employee CHECK CONSTRAINT CK_gender,CK_age
ALTER TABLE table_employee CHECK CONSTRAINT ALL
ALTER TABLE table_employee DROP CK_gender
- 主键约束操作
ALTER TABLE table_dept ALTER COLUMN id INT NOT NULL
ALTER TABLE table_dept ADD CONSTRAINT PK_id PRIMARY KEY(id)
ALTER TABLE table_dept DROP PK_id
- 外键约束操作
ALTER TABLE employee ADD CONSTRAINT FK_employee_dept FOREIGN KEY(dept_id) REFERENCES table_dept(id)
ALTER TABLE employee ADD CONSTRAINT FK_employee_dept FOREIGN KEY(dept_id) REFERENCES table_dept(id) ON DELETE SET NULL
- UNIQUE 约束
ALTER TABLE employee ADD CONSTRAINT Unique_EmpNo UNIQUE(EmpNo)
- DEFAULT 约束
ALTER TABLE employee ADD CONSTRAINT DF_emp_gender N'男' FOR gender;
- 修改对象名称
exec sp_rename 'table_dept','tb_dept'
exec sp_rename 'table_dept.age','年龄','COLUMN'
exec sp_rename 'test','db_test','DATABASE'
exec sp_rename 'table_dept.IX_name','IX_dept_name'
exec sp_rename 'table_dept.PK_id','PK_id2'
- 代理键 IDENTITY
DBCC CHECKIDENT('table_dept',NORESEED);
DBCC CHECKIDENT('table_dept',RESEED,50);
三、删除数据表
DROP TABLE table_dept
DELETE TABLE table_dept WHERE Id > 1000
TRUNCATE TABLE table_dept