sql server 数据定义常用sql

目录

创建表
更新表
删除表
查询表结构
创建视图

创建表

创建表TestSql

CREATE TABLE TestSql(
Id bigint identity(1,1) not null PRIMARY key
)

参考资料

SQL CREATE TABLE 语句
SQL PRIMARY KEY 约束

更新表

创建字段

创建普通字段

--ALTER TABLE table_name ADD column_name datatype
ALTER TABLE TestSql ADD Name varchar(10)
--创建不为空的字段
ALTER TABLE TestSql ADD Sex int not null

参考资料

SQL 撤销索引、表以及数据库

修改字段

修改字段的数据类型+是否为空

--ALTER TABLE table_name ALTER COLUMN column_name datatype
--修改数据类型（eg.varchar(10)->varchar(20)），成功
--如果Name字段不为空，后面需跟上'not null'
ALTER TABLE TestSql ALTER COLUMN Name varchar(20)
--修改数据类型（eg.varchar->int），有索引，则修改失败
ALTER TABLE TestSql ALTER COLUMN Name int

--sql server常用数据类型
ALTER TABLE TestSql ALTER COLUMN Name bigint
ALTER TABLE TestSql ALTER COLUMN Name char(100)
ALTER TABLE TestSql ALTER COLUMN Name date
ALTER TABLE TestSql ALTER COLUMN Name datetime
ALTER TABLE TestSql ALTER COLUMN Name datetime2
--一共7位，没有小数位
ALTER TABLE TestSql ALTER COLUMN Name decimal(7)
--一共7位，2位小数
ALTER TABLE TestSql ALTER COLUMN Name decimal(7,2)
ALTER TABLE TestSql ALTER COLUMN Name float
ALTER TABLE TestSql ALTER COLUMN Name geography
ALTER TABLE TestSql ALTER COLUMN Name geometry
ALTER TABLE TestSql ALTER COLUMN Name int
ALTER TABLE TestSql ALTER COLUMN Name nchar(10)
ALTER TABLE TestSql ALTER COLUMN Name nvarchar(10)
ALTER TABLE TestSql ALTER COLUMN Name smallint
ALTER TABLE TestSql ALTER COLUMN Name text
--默认7位
ALTER TABLE TestSql ALTER COLUMN Name time(4)
ALTER TABLE TestSql ALTER COLUMN Name tinyint
ALTER TABLE TestSql ALTER COLUMN Name varchar(4)
ALTER TABLE TestSql ALTER COLUMN Name nvarchar(100)
--仅1位（其他字符类型类比）
ALTER TABLE TestSql ALTER COLUMN Name nvarchar
ALTER TABLE TestSql ALTER COLUMN Name nvarchar(MAX)

关于MAX的问题，查看nchar 和 nvarchar (Transact-SQL)
max 指示最大存储大小是 2^30-1 个字符 (2 GB) 。
参考资料:
数字类型
日期和时间类型
字符串和二进制类型

修改字段名称

--例1 把表TABLE的列tid改为id
EXEC sp_rename @objname = 'TABLE.tid', @newname = 'id', @objtype = 'column'
 
--例2（简写）把表TABLE的列tid改为id
EXEC sp_rename 'TABLE.tid', @newname = 'id', @objtype = 'column'

--注意: 更改对象名的任一部分都可能会破坏脚本和存储过程。
EXEC sp_rename 'TestSql.Name', @newname = 'NewName', @objtype = 'column'

修改表名

--例1 把表TABLE1改为TABLE2
EXEC sp_rename @objname = 'TABLE1', @newname = 'TABLE2'

--例2（简写）把表TABLE1改为TABLE2
EXEC sp_rename 'TABLE1','TABLE2'

--注意: 更改对象名的任一部分都可能会破坏脚本和存储过程。
EXEC sp_rename 'TestSql','TestSql2'

参考资料

SQL Server修改表名，字段名，索引名

外键

添加一个TestSqlChild表，TestSqlChild表有字段ParentId依赖TestSql的主键Id
创建外键

--ALTER TABLE target_table_name
--ADD CONSTRAINT FOREIGN_KEY_Name
--FOREIGN KEY (target_table_column)
--REFERENCES table_name(table_name_column)

ALTER TABLE TestSqlChild
ADD CONSTRAINT fk_ParentId
FOREIGN KEY (ParentId)
REFERENCES TestSql(Id)

删除外键

--ALTER TABLE target_table_name
--DROP CONSTRAINT FOREIGN_KEY_Name

ALTER TABLE TestSqlChild
DROP CONSTRAINT fk_ParentId

参考资料

SQL FOREIGN KEY 约束

索引

创建索引

--CREATE INDEX index_name ON table_name (column_name)
--创建普通索引
CREATE INDEX NameIndex ON TestSql (Name)
--创建唯一索引
CREATE UNIQUE INDEX NameIndex ON TestSql (Name)

参考资料

SQL CREATE INDEX 语句
删除索引

--DROP INDEX table_name.index_name
DROP INDEX TestSql.NameIndex

查询目标字段的索引
使用存储过程查询

--use database_name EXEC Sp_helpindex table_name
--或者
--EXEC Sp_helpindex table_name
EXEC sp_helpindex 'TestSql'

结果如下：

删除字段

--ALTER TABLE table_name DROP COLUMN column_name
--如果字段有索引，需要先删除索引，才能删除字段（外键类似）
ALTER TABLE TestSql DROP COLUMN Sex

删除表

删除表TestSql

DROP TABLE TestSql

查询表结构

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'tableName'
ORDER BY ORDINAL_POSITION

或者

sp_Columns 'tableName'

包含注释信息

SELECT A.name AS table_name,B.name AS column_name,D.IS_NULLABLE,D.DATA_TYPE,D.CHARACTER_MAXIMUM_LENGTH,C.value AS column_description

FROM sys.tables A INNER JOIN sys.columns B

ON B.object_id = A.object_id LEFT JOIN sys.extended_properties C

ON C.major_id = B.object_id AND C.minor_id = B.column_id

INNER join INFORMATION_SCHEMA.COLUMNS D on D.COLUMN_NAME=B.name and D.table_name=A.name

WHERE A.name ='tableName'

参考资料

Get Table information (Column Name, Data Type) in SQL Server
SQL Server 的所有表查询和注释说明查询

创建视图

--CREATE VIEW view_name AS
--SELECT column_name(s)
--FROM table_name
--WHERE condition

CREATE VIEW QueryTestSql AS
SELECT *
FROM TestSql
WHERE 1=1

--使用
select * from QueryTestSql

参考资料

SQL VIEW（视图）