SQL常用语句

创建表

CREATE TABLE [表名] ( 字段名 数据类型 [NOT NULL] [PRIMARY KEY], 字段名 数据类型 [NOT NULL] [PRIMARY KEY] )

添加字段默认值

ALTER TABLE [table_global_system_basic_module_yogurtcry_20201007] ADD DEFAULT (-1) FOR dataState

添加字段说明

EXEC sp_addextendedproperty 'MS_Description', '需要添加的说明', 'user', dbo, 'table', [表名], 'column', [列名]

避免重复写入数据

INSERT INTO [表名] ( 字段名1, 字段名2, 字段名3, 字段名4 ) SELECT '要插入的数据1', '要插入的数据2', '要插入的数据3', '要插入的数据4' WHERE NOT EXISTS ( SELECT 字段名1, 字段名2, 字段名3, 字段名4 FROM [表名] WHERE [表名].要比对的字段名 = '要比对的数据' );

创建唯一值约束(非主键)

ALTER TABLE [表名] ADD CONSTRAINT [约束名] UNIQUE (字段名)

删除唯一值约束

ALTER TABLE [表名] DROP CONSTRAINT [约束名]

查询数据库中表内总行数

SELECT table_tableNameList.name AS 表名, table_tableRowCount.rows AS 表内行数 FROM ( SELECT name, id, xtype FROM sysobjects WHERE sysobjects.xtype = 'u' ) AS table_tableNameList LEFT JOIN ( SELECT id, rows FROM sysindexes WHERE indid IN (0, 1) ) AS table_tableRowCount ON table_tableNameList.id = table_tableRowCount.id ORDER BY 表名 ASC, 表内行数 DESC

查询数据库中表内总行数及字段名

SELECT table_tableRowCountInfo.name AS 表名, table_columnName.COLUMN_NAME AS 字段名, table_tableRowCountInfo.rows AS 表内行数 FROM ( SELECT table_tableNameList.name, table_tableRowCount.rows FROM ( SELECT name, id, xtype FROM sysobjects WHERE sysobjects.xtype = 'u' ) AS table_tableNameList LEFT JOIN ( SELECT id, rows FROM sysindexes WHERE indid IN (0, 1) ) AS table_tableRowCount ON table_tableNameList.id = table_tableRowCount.id ) AS table_tableRowCountInfo LEFT JOIN ( SELECT * FROM INFORMATION_SCHEMA.COLUMNS ) AS table_columnName ON table_columnName.TABLE_NAME = table_tableRowCountInfo.name