MSSQL扫盲系列(1)-CREATE，ALTER，DROP

简单的数据定义(CREATE，ALTER，DROP,DECLARE)

表，列，视图，索引，触发器

   创建表，并附带各种约束，默认值

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)

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'--该回来

  列

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
--删除列,由于有默认值约束，需要先删掉

 约束（默认值也是一种约束）

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

视图

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;
--删除视图

索引

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
--删除索引

触发器

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
--删除触发器

删除表

DROP TABLE USERS

附加部分关于数据库操作的（一般都是使用MSSMS）

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'
--数据还原，注意---中间是双引号