【SQL Server】数据库指令

一、连接数据库

string connStr = @"server=" + Server + ";database=" + DataBase + ";user=" + User_ID + ";password=" + PassWord;

二、表格操作

1、新建表

string cmdStr = "CREATE TABLE " + tableName + " (" + colStr + ")";

eg：CREATE TABLE tableName (colName1 integer NOT Null PRIMARY KEY,colName2 integer NOT Null)

2、删除表格

DROP TABLE tableName

3、修改表名

eg：EXEC sp_rename 'tableName' , 'tableName1';

三、列操作

1、往表格新增列

string cmdStr = "ALTER TABLE " + tableName + " ADD " + colName + " " + colType;
if (!allowNull)
{
   cmdStr += " NOT Null";
}
if (isKey)
{
   cmdStr += " PRIMARY KEY";
}

eg：ALTER TABLE tableName ADD colName integer NOT Null PRIMARY KEY

2、删除表格指定列

string cmdStr = "ALTER TABLE " + tableName + " DROP COLUMN " + colName;

eg：ALTER TABLE tableName DROP COLUMN colName

 

3、修改字段属性

注意：主键不能为其他类型，不能设置为允许空

eg：alter table tableName alter column colName2 nvarchar(100) null;

4、重命名字段

eg：EXEC sp_rename 'tableName.colName2' , 'colName3';

四、数据操作

1、插入数据

string cmdStr = string.Format("insert into {0}({1}) values({2}); select @@IDENTITY as 'Identity'", tableName, colStr, valueStr);

insert into T_Users (username,password,creatdatetime,powerlevel) values('mm','ttt','2021/12/9 13:51:51',1)

返回自增ID：
insert into T_Users (username,password,creatdatetime,powerlevel) values('mm','ttt','2021/12/9 13:51:51',1); select @@IDENTITY as 'Identity'

2、删除数据

string cmdStr = "delete from " + tableName + " where " + mainKeyName + " = '" + mainKeyValue + "'";

eg：delete from T_Users where username = 'mm'

3、修改数据

string cmdStr = string.Format("update {0} set {1} where {2}" + " = " + "{3};", tableName, colStr, mainKeyName, keyValue);

eg：update T_Users set powerlevel = 2,creatoruid=26 where username = 'b'

4、查询数据

string cmdStr = string.Format("select {0} from {1} {2};", columns, tableName, conditionStr);

eg：select powerlevel from T_Users where powerlevel = 2
eg：select * from T_Users where powerlevel = 2