数据库入门整理-SQL SERVER篇

•  建表语句

create table STUDENT (
[ID] decimal(8) identity not null,
constraint [UQ_STUDENT_ID] unique([ID]),
[XH] nvarchar(20) not null,
[XM] nvarchar(30) not null,
constraint [UQ_STUDENT_XM] unique([XM]),
[SEX] decimal(1) not null,
[MOBILE] decimal(11),
[SFZX] TINYINT,
[RXSJ] date
);
-- 添加主键
alter table STUDENT add primary key clustered([ID],[XH]);
-- 添加字段备注
EXEC sp_addextendedproperty 'MS_Description', N'自增编号', N'SCHEMA', N'dbo',N'TABLE', N'STUDENT', N'COLUMN', N'ID';
EXEC sp_addextendedproperty 'MS_Description', N'学号', N'SCHEMA', N'dbo',N'TABLE', N'STUDENT', N'COLUMN', N'XH';
EXEC sp_addextendedproperty 'MS_Description', N'姓名', N'SCHEMA', N'dbo',N'TABLE', N'STUDENT', N'COLUMN', N'XM';
EXEC sp_addextendedproperty 'MS_Description', N'性别(1:男, 2:女)', N'SCHEMA', N'dbo',N'TABLE', N'STUDENT', N'COLUMN', N'SEX';
EXEC sp_addextendedproperty 'MS_Description', N'手机号', N'SCHEMA', N'dbo',N'TABLE', N'STUDENT', N'COLUMN', N'MOBILE';
EXEC sp_addextendedproperty 'MS_Description', N'是否在校(0:离校,1:在校)', N'SCHEMA', N'dbo',N'TABLE', N'STUDENT', N'COLUMN', N'SFZX';
EXEC sp_addextendedproperty 'MS_Description', N'入学时间', N'SCHEMA', N'dbo',N'TABLE', N'STUDENT', N'COLUMN', N'RXSJ';
-- 添加表备注
EXEC sp_addextendedproperty 'MS_Description', N'学生表', N'SCHEMA', N'dbo',N'TABLE', N'STUDENT';

•  更新语句

-- 表重命名
EXEC sp_rename 'STUDENT', 'STUDENT2', 'OBJECT';

-- 删除主键
alter table STUDENT2 drop constraint [主键名xxx];

-- 添加主键
alter table STUDENT2 add primary key clustered([ID]);

-- 添加唯一索引、指定索引名
alter table STUDENT2 add constraint [UQ_STUDENT2_XH] unique([XH]);

-- 删除唯一索引
alter table STUDENT2 drop constraint UQ_STUDENT_XM;

-- 修改字段名
EXEC sp_rename 'STUDENT2.SEX', 'XB', 'column';

-- 添加表备注
EXEC sp_addextendedproperty 'MS_Description', N'学生表2', N'SCHEMA', N'dbo',N'TABLE', N'STUDENT2';

-- 修改表备注
EXEC sp_updateextendedproperty 'MS_Description', N'学生表2', N'SCHEMA', N'dbo',N'TABLE', N'STUDENT2';

-- 添加字段备注
EXEC sp_addextendedproperty 'MS_Description', N'是否在校(0:离校, 1:在校)', N'SCHEMA', N'dbo',N'TABLE', N'STUDENT2', N'COLUMN', N'SFZX';

-- 修改字段备注
EXEC sp_updateextendedproperty 'MS_Description', N'是否在校(0:离校, 1:在校)', N'SCHEMA', N'dbo',N'TABLE', N'STUDENT2', N'COLUMN', N'SFZX';

• 查询当前库表操作

-- 获取当前库存在哪些表
SELECT A.[NAME] TABLE_NAME, B.[VALUE] COMMENTS
FROM sysobjects A
LEFT JOIN sys.extended_properties B ON A.id=B.major_id AND B.minor_id=0
WHERE A.[TYPE]='U'
ORDER BY A.[NAME]

-- 查询当前库是否存在表
SELECT COUNT(*) hasTable
FROM sys.tables
WHERE [TYPE]='u' AND object_id = OBJECT_ID(#{tableName})

• 查询指定表字段信息 

SELECT C.[NAME] AS COLUMN_NAME, COLUMNPROPERTY(C.id,C.name,'PRECISION') AS DATA_LENGTH,
isnull(ETP.value,'') AS COLUMN_COMMENTS, A.[NAME] TABLE_NAME, B.[VALUE] COMMENTS,
C.PREC AS DATA_PRECISION, C.SCALE AS DATA_SCALE,
(CASE WHEN C.ISNULLABLE=0 THEN '0' ELSE '' END) AS NULLABLE,
-- 判断是否自增
(CASE WHEN COLUMNPROPERTY(C.id,C.name,'IsIdentity')=1 THEN 'GENERATED' ELSE T.[NAME] END) AS DATA_TYPE,
-- 查询主键
(SELECT cc.constraint_name
FROM information_schema.table_constraints tc
INNER JOIN information_schema.constraint_column_usage cc
ON tc.constraint_name = cc.constraint_name
WHERE tc.constraint_type='PRIMARY KEY' AND tc.table_name=A.[NAME] AND cc.column_name=C.[NAME]) PRIMARY_KEY,
-- 查询唯一
(SELECT TOP 1 cc.constraint_name
FROM information_schema.table_constraints tc
INNER JOIN information_schema.constraint_column_usage cc
ON tc.constraint_name = cc.constraint_name
WHERE tc.constraint_type='UNIQUE' AND tc.table_name=A.[NAME] AND cc.column_name=C.[NAME]) UNIQUE_KEY

FROM syscolumns C
INNER JOIN sysobjects A ON C.id = A.id
LEFT JOIN sys.extended_properties B ON A.id=B.major_id AND B.minor_id=0
INNER JOIN systypes T ON C.xusertype = T.xusertype
LEFT JOIN sys.extended_properties ETP ON ETP.major_id = c.id AND ETP.minor_id = C.colid AND ETP.name
='MS_Description'
LEFT JOIN syscomments CM ON C.cdefault=CM.id
WHERE A.[NAME]=#{tableName}
--ORDER BY A.[NAME]

•  获取表详细信息

SELECT 　　
t.NAME AS TableName,
t.CREATE_DATE AS CREATE_TIME,
p.ROWS AS TABLE_COUNT,
CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 4) AS NUMERIC(36, 4)) AS TABLE_SIZE
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
WHERE t.is_ms_shipped = 0 AND i.OBJECT_ID > 0 AND t.NAME=#{tableName}
GROUP BY t.NAME,t.CREATE_DATE,p.ROWS