SQL 常用的命令

--修改表名
--格式:SP_RENAME TABLENAME,NEWTABLENAME
SP_RENAME TABLENAME,NEWTABLENAME
--只能对表,不能对临时表 --修改字段名
--格式:SP_RENAME 'TABLENAME.COLNAME',NEWCOLNAME,'COLUMN'
SP_RENAME 'TABLENAME.COLNAME',NEWCOLNAME,'COLUMN' --添加字段
--格式:ALTER TABLE TABLE_NAME ADD NEW_COLUMN DATA_TYPE [INTERALITY_CODITION]
示例1
ALTER TABLE STUDENT ADD NATIONALITY VARCHAR(20)
ALTER TABLE STUDENT ADD ID IDENTITY INT(1,1) --示例2 添加INT类型的列,默认值为 0
ALTER TABLE STUDENT ADD STUDENTNAME INT DEFAULT 0 --示例3 添加INT类型的列,默认值为0,主键
ALTER TABLE STUDENT ADD STUDENTID INT PRIMARY KEY DEFAULT 0 --示例4 判断STUDENT中是否存在NAME字段且删除字段
IF EXISTS(SELECT * FROM SYSCOLUMNS WHERE ID=OBJECT_ID('STUDENT') AND NAME='NAME') BEGIN
ALTER TABLE STUDENT DROP COLUMN NAME
END
更改字段
格式:ALTER TABLE TABLE_NAME ALTER COLUMN COLUMN_NAME
ALTER TABLE STUDENT ALTER COLUMN NAME VARCHAR(200)
删除字段
格式:ALTER TABLE TABLE_NAME DROP COLUMN COLUMN_NAME
ALTER TABLE STUDENT DROP COLUMN NATIONALITY;
查看字段约束
格式: SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE TABLE_NAME = TABLE_NAME
SELECT TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
WHERE TABLE_NAME = 'STUDENT'
查看字段缺省约束表达式 (即默认值等)
格式:SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = TABLE_NAME
SELECT TABLE_NAME, COLUMN_NAME, COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='STUDENT'
查看字段缺省约束名
格式:SELECT NAME FROM SYSOBJECTS WHERE OBJECT_ID(TABLE_NAME)=PARENT_OBJ AND XTYPE=’D’
SELECT NAME FROM SYSOBJECTS
WHERE OBJECT_ID('表?名?')=PARENT_OBJ AND XTYPE='D'
删除字段约束
格式:ALTER TABLE TABLENAME DROP CONSTRAINT CONSTRAINTNAME
ALTER TABLE STUDENT DROP CONSTRAINT PK__STUDENT__2F36BC5B772B9A0B
添加字段约束
格式:ALTER TABLE TABLENAME ADD CONSTRAINT CONSTRAINTNAME PRIMARY KEY (COLUMN_NAME)
--示例1
ALTER TABLE STUINFO ADD CONSTRAINT PK_STUNO PRIMARY KEY (STUNO) --示例2 添加主键约束(PRIMARY KEY)
-- 存在主键约束PK_STUNO,则删除
IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME='PK_STUNO' AND XTYPE='PK')
ALTER TABLE STUINFO
DROP CONSTRAINT PK_STUNO
GO
-- 重新添加主键约束PK_STUNO
ALTER TABLE STUINFO ADD CONSTRAINT PK_STUNO PRIMARY KEY (STUNO)
GO --示例3 添加 唯一UQ约束(UNIQUE CONSTRAINT)
-- 存在唯一约束UQ_STUNO,则删除
IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME='UQ_STUID' AND XTYPE='UQ')
ALTER TABLE STUINFO
DROP CONSTRAINT UQ_STUID
GO
-- 重新添加唯一约束UQ_STUID
ALTER TABLE STUINFO ADD CONSTRAINT UQ_STUID UNIQUE (STUID) --示例4 添加默认DF约束(DEFAULT CONSTRAINT)
-- 存在默认约束UQ_STUNO,则删除
IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME='DF_STUADDRESS' AND XTYPE='D')
ALTER TABLE STUINFO DROP CONSTRAINT DF_STUADDRESS
GO
-- 重新添加默认约束DF_STUADDRESS
ALTER TABLE STUINFO ADD CONSTRAINT DF_STUADDRESS DEFAULT ('地址不详') FOR STUADDRESS --示例5 检查CK约束(CHECK CONSTRAINT)
-- 存在检查约束UQ_STUNO,则删除
IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME='CK_STUAGE' AND XTYPE='C')
ALTER TABLE STUINFO DROP CONS

  

--修改表名
--格式:SP_RENAME TABLENAME,NEWTABLENAME
SP_RENAME TABLENAME,NEWTABLENAME
--只能对表,不能对临时表

--修改字段名
--格式:SP_RENAME 'TABLENAME.COLNAME',NEWCOLNAME,'COLUMN'
SP_RENAME 'TABLENAME.COLNAME',NEWCOLNAME,'COLUMN'

--添加字段
--格式:ALTER TABLE TABLE_NAME ADD NEW_COLUMN DATA_TYPE [INTERALITY_CODITION]
示例1
ALTER TABLE STUDENT ADD NATIONALITY VARCHAR(20)
ALTER TABLE STUDENT ADD ID IDENTITY INT(1,1)

--示例2 添加INT类型的列,默认值为 0
ALTER TABLE STUDENT ADD STUDENTNAME INT DEFAULT 0

--示例3 添加INT类型的列,默认值为0,主键
ALTER TABLE STUDENT ADD STUDENTID INT PRIMARY KEY DEFAULT 0

--示例4 判断STUDENT中是否存在NAME字段且删除字段
IF EXISTS(SELECT * FROM SYSCOLUMNS WHERE ID=OBJECT_ID('STUDENT') AND NAME='NAME') BEGIN
ALTER TABLE STUDENT DROP COLUMN NAME
END
更改字段
格式:ALTER TABLE TABLE_NAME ALTER COLUMN COLUMN_NAME
ALTER TABLE STUDENT ALTER COLUMN NAME VARCHAR(200)
删除字段
格式:ALTER TABLE TABLE_NAME DROP COLUMN COLUMN_NAME
ALTER TABLE STUDENT DROP COLUMN NATIONALITY;
查看字段约束
格式: SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE TABLE_NAME = TABLE_NAME
SELECT TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
WHERE TABLE_NAME = 'STUDENT'
查看字段缺省约束表达式 (即默认值等)
格式:SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = TABLE_NAME
SELECT TABLE_NAME, COLUMN_NAME, COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='STUDENT'
查看字段缺省约束名
格式:SELECT NAME FROM SYSOBJECTS WHERE OBJECT_ID(TABLE_NAME)=PARENT_OBJ AND XTYPE=’D’
SELECT NAME FROM SYSOBJECTS
WHERE OBJECT_ID('表?名?')=PARENT_OBJ AND XTYPE='D'
删除字段约束
格式:ALTER TABLE TABLENAME DROP CONSTRAINT CONSTRAINTNAME
ALTER TABLE STUDENT DROP CONSTRAINT PK__STUDENT__2F36BC5B772B9A0B
添加字段约束
格式:ALTER TABLE TABLENAME ADD CONSTRAINT CONSTRAINTNAME PRIMARY KEY (COLUMN_NAME)
--示例1
ALTER TABLE STUINFO ADD CONSTRAINT PK_STUNO PRIMARY KEY (STUNO)

--示例2 添加主键约束(PRIMARY KEY)
-- 存在主键约束PK_STUNO,则删除
IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME='PK_STUNO' AND XTYPE='PK')
ALTER TABLE STUINFO
DROP CONSTRAINT PK_STUNO
GO
-- 重新添加主键约束PK_STUNO
ALTER TABLE STUINFO ADD CONSTRAINT PK_STUNO PRIMARY KEY (STUNO)
GO

--示例3 添加 唯一UQ约束(UNIQUE CONSTRAINT)
-- 存在唯一约束UQ_STUNO,则删除
IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME='UQ_STUID' AND XTYPE='UQ')
ALTER TABLE STUINFO
DROP CONSTRAINT UQ_STUID
GO
-- 重新添加唯一约束UQ_STUID
ALTER TABLE STUINFO ADD CONSTRAINT UQ_STUID UNIQUE (STUID)

--示例4 添加默认DF约束(DEFAULT CONSTRAINT)
-- 存在默认约束UQ_STUNO,则删除
IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME='DF_STUADDRESS' AND XTYPE='D')
ALTER TABLE STUINFO DROP CONSTRAINT DF_STUADDRESS
GO
-- 重新添加默认约束DF_STUADDRESS
ALTER TABLE STUINFO ADD CONSTRAINT DF_STUADDRESS DEFAULT ('地址不详') FOR STUADDRESS

--示例5 检查CK约束(CHECK CONSTRAINT)
-- 存在检查约束UQ_STUNO,则删除
IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME='CK_STUAGE' AND XTYPE='C')
ALTER TABLE STUINFO DROP CONS

上一篇:codeforces #262 DIV2 C称号Present(二分法+贪婪)


下一篇:leetcode reverse integer&&Palindrome Number