【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 tableName3、修改表名
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
/*******相与枕藉乎舟中,不知东方之既白*******/