SQL Server（二）—— 数据表操作

一、数据表创建

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),　　--设置主键，自增（标识种子和标识增量均为 1），
	name COLLATE Chinese_PRC_CIAS NOT NULL UNIQUE,　　--不允许为 NULL 的列,列排序规则设为 Chinese_PRC_CIAS,并添加 Unique 约束
	age INT 18 NOT NULL 
		DEFAULT 18 
		CONSTRAINT CK_age CHECK(age > 0 AND age < 120),　　-- 带约束的列，且默认值为 18
	gender NCHAR(1) NOT NULL DEFAULT ('男'),
	deptId INT 
		CONSTRAINT FK_table_employee_dept
		FOREIGN KEY --外键约束
		REFERENCES dept(id) -- 关联 dept 表的 id 字段	
		ON UPDATE CASCADE -- 外键约束更新规则设为“层叠”
		ON DELETE SET NULL -- 外键约束删除规则设为“置 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) -- 计算列
)

二、修改数据表

1. 简单字段操作

ALTER TABLE table_dept ALTER COLUMN name NVARCHAR(25) COLLATE Chinese_PRC_CI_AS NOT NULL -- 修改字段(DEFAULT约束，NOT NUL 约束)
ALTER TABLE table_dept ADD remark NVARCHAR(50) -- 添加字段 remark
ALTER TABLE table_dept DROP COLUMN remark -- 删除字段

2. CHECK 约束操作

ALTER TABLE table_employee ADD CONSTRAINT CK_gender (gender ='男' OR gender ='女') -- 添加 CHECK 约束
ALTER TABLE table_employee
  WITH NOCHECK ADD -- 不对已有数据检查，CHECK 默认会对原有数据检查
  CONSTRAINT CK_gender (gender ='男' OR gender ='女') -- 添加 CHECK 约束
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 -- 删除约束

3. 主键约束操作

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 -- 删除主键约束

4. 外键约束操作

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

5. UNIQUE 约束

ALTER TABLE employee ADD CONSTRAINT Unique_EmpNo UNIQUE(EmpNo)

6. DEFAULT 约束

ALTER TABLE employee ADD CONSTRAINT DF_emp_gender N'男' FOR gender;

7. 修改对象名称

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' -- 修改主键名，同时该方法可以用于修改其他约束，数据表、视图、存储过程、触发器的名称（注意：命名不要使用"."否则可能将导致对象查找时出错。）

8. 代理键 IDENTITY

DBCC CHECKIDENT('table_dept',NORESEED);--检查当前的IDENTITY值
DBCC CHECKIDENT('table_dept',RESEED,50);--重置种子

三、删除数据表

DROP TABLE table_dept -- 删除表，包括数据和结构
DELETE TABLE table_dept WHERE Id > 1000 --删除表数据，操作可回退
TRUNCATE TABLE table_dept -- 删除表数据连同事务日志，因此操作无法回退