MSSQL扫盲系列(1)-CREATE,ALTER,DROP
简单的数据定义(CREATE,ALTER,DROP,DECLARE)
表,列,视图,索引,触发器
创建表,并附带各种约束,默认值
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
CREATE TABLE USERS ( ID INT IDENTITY(1,1) PRIMARY KEY,--自增主键
-- ID CHAR(32) PRIMARY KEY UNIQUE,--GUID主键
USERNAME NVARCHAR(20) NOT NULL UNIQUE,--UNIQUE唯一,NOT NULL必填
[PASSWORD] CHAR(32) NOT NULL,--准备使用MD5加密(32bit)
BIRTHDAY DATETIME CHECK(BIRTHDAY<GETDATE() AND BIRTHDAY >CONVERT(DATETIME,'1900-01-01')),
--生日,检查大于1900,小于当前时间
REGTIME DATETIME DEFAULT(GETDATE()), --注册时间,默认是当前时间,
[ADDRESS] INT REFERENCES AREA(ID),--外键,显示用户的地址信息,可空
MARKINFO NVARCHAR(100) --备注信息
)
--创建数据表
CREATE TABLE #(ID INT IDENTITY(1,1) PRIMARY KEY )
--创建本地临时表
CREATE TABLE #1(ID INT IDENTITY(1,1) PRIMARY KEY )
--创建本地临时表
CREATE TABLE ##(ID INT IDENTITY(1,1) PRIMARY KEY )
--创建全局临时表
CREATE TABLE ##1(ID INT IDENTITY(1,1) PRIMARY KEY )
--创建全局临时表
DROP TABLE #1,#,##,##1
--删除临时表
表相关(表变量,临时表,更多CLICK HERE)
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
DECLARE @TB TABLE (ID INT ,NAME NVARCHAR(20))
--创建表变量,@,@@本地或全局
--行数较少的情况下(小于1000行)可以使用表变量
INSERT INTO @TB SELECT TOP 10 ID,USERNAME FROM USERS
--插入数据
SELECT * FROM @TB
--查询
CREATE TABLE # (ID INT ,NAME NVARCHAR(20))
--创建临时表,#,##本地或全局
--行数很多(有几万行),则使用临时表
INSERT INTO # SELECT TOP 10 ID,USERNAME FROM USERS
--插入数据
SELECT * FROM #
--查询
DROP TABLE #
修改表或列名
EXEC SP_RENAME 'USERS', 'MYUSER' --改表名
EXEC SP_RENAME 'MYUSER','USERS'--该回来
EXEC SP_RENAME 'USERS.PASSWORD', 'PSD' ,'COLUMN'--改列名
EXEC SP_RENAME 'USERS.PSD', 'PASSWORD' ,'COLUMN'--该回来
列
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
ALTER TABLE USERS ADD TEMP NVARCHAR(200) DEFAULT('This is a temporary-column! ')--添加列
ALTER TABLE USERS ALTER COLUMN TEMP NVARCHAR(100)--修改列
DECLARE @CONSTRAINT VARCHAR(100)--定义本地临时变量,一个@,两个是全局的
SELECT TOP 1 @CONSTRAINT =NAME FROM SYSOBJECTS WHERE ID=(
SELECT CDEFAULT FROM SYSCOLUMNS WHERE NAME='TEMP' AND ID=(
SELECT ID FROM SYSOBJECTS WHERE NAME='USERS'
)
)--查询约束
IF lEN(@CONSTRAINT)>0
BEGIN
PRINT @CONSTRAINT--打印信息
EXEC( 'USE TOOLS ALTER TABLE USERS DROP CONSTRAINT '+@CONSTRAINT)
--删除约束
END
ALTER TABLE USERS DROP COLUMN TEMP
--删除列,由于有默认值约束,需要先删掉
约束(默认值也是一种约束)
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SELECT NAME FROM SYSOBJECTS WHERE ID in(
SELECT CDEFAULT FROM SYSCOLUMNS WHERE ID=(
SELECT ID FROM SYSOBJECTS WHERE NAME='USERS'
))----查询所有约束
ALTER TABLE USERS ADD CONSTRAINT TEMP_CONSTRAINT CHECK(LEN(PASSWORD)!=32)
--创建约束
ALTER TABLE USERS DROP CONSTRAINT TEMP_CONSTRAINT
--删除约束
视图
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
CREATE VIEW USERS_VIEW AS SELECT USERNAME,[PASSWORD] FROM USERS
--创建视图
CREATE VIEW USERS_VIEW1 AS SELECT USERNAME FROM USERS;
----创建视图
DROP VIEW USERS_VIEW,USERS_VIEW1;
--删除视图
索引
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
CREATE INDEX NAMEINDEX ON USERS(USERNAME)
--创建非聚簇索引,可以有256个
CREATE INDEX NAMEINDEX ON USERS(USERNAME,[PASSWORD])
--创建非聚簇索引,可以有256个
CREATE CLUSTERED INDEX CLUNAMEINDEX ON USERS(USERNAME)
--创建聚簇索引,只能有1个,ONLY ONE
CREATE CLUSTERED INDEX CLUNAMEINDEX ON USERS(USERNAME,[PASSWORD])
--创建聚簇索引,只能有1个,ONLY ONE
CREATE UNIQUE CLUSTERED INDEX CLUNAMEINDEX ON USERS(USERNAME)
--创建唯一聚簇索引
CREATE UNIQUE INDEX CLUNAMEINDEX ON USERS(USERNAME)
--创建唯一索引
DROP INDEX USERS.CLUNAMEINDEX
--删除索引
触发器
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
CREATE TRIGGER TEMP ON USERS INSTEAD OF DELETE AS
INSERT INTO LOGTBALE SELECT GETDATE(),* FROM USERS WHERE USERS.ID=ID
DELETE FROM USERS WHERE ID=ID
--在删除用户时将删除用户的时间写入数据库
--创建触发器
DROP TRIGGER TEMP
--删除触发器
删除表
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
DROP TABLE USERS
附加部分关于数据库操作的(一般都是使用MSSMS)
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
CREATE DATABASE TEMP
--创建名为TEMP的数据库
DROP DATABASE TEMP
--删除
BACKUP DATABASE TEMP TO DISK='E:\T.BAK' WITH NOINIT,NAME='BAK_T_FULL'
--完整备份
BACKUP DATABASE TEMP TO DISK='E:\T.BAK' WITH DIFFERENTIAL,NOINIT,NAME='BAK_T_DIFFER'
--差异备份
RESTORE DATABASE "TEMP" FROM DISK='E:\T.BAK'
--数据还原,注意---中间是双引号