最实用的语法: 增:insert into 表名(插入的列名)values(插入的值) 删: delete 表名 where 条件 改: update 表名 列名 set=值 查: select 列名 from 表名 或:select 列名 from 表名 where 条件
增: public static bool AddNoPromptToDatabase(int i) { SqlConnection con = new SqlConnection(_ConString); try { //连接数据库中 SqlCommand cmd = new SqlCommand(); cmd.Connection = con; con.Open(); cmd.CommandText = string.Format("select * from sysobjects where name='NoPrompt'"); SqlDataReader dr = cmd.ExecuteReader(); int a = 0; while (dr.Read()) { a = 1; } dr.Close(); if (a == 0) { cmd.CommandText = string.Format("create table NoPrompt(noprompt int primary key)"); cmd.ExecuteNonQuery(); } cmd.CommandText = string.Format("insert into NoPrompt values('{0}')", i); cmd.ExecuteNonQuery(); return true; } catch (Exception) { return false; } finally { //关闭连接 con.Close(); } } 【如果我的回答对你有所帮助,请到下列地址http://download.csdn.net/source/2247484 下载原创家庭记账软件,并给出宝贵意见!】
using System; using System.Data; using System.Data.SqlClient;//一定要引用这个public class databaseconn { public SqlConnection con; public SqlConnection getTestTDataConnection()//获得连接 { con = new SqlConnection("Server=(local);uid=sa;pwd=123456;database=testT");//这里面要改成你自己的uid:数据库用户名,pwd:数据库密码,database:要增删除改的数据库名 return con; } public void closeDataConnection(SqlConnection con)//关闭连接 { if (con == null) con.Close(); } public int AddUser(User user)//添加用户 { string sql = "insert userInfo values(@username,@password,@email,@sex,@age,@address)"; SqlParameter[] para = new SqlParameter[] { new SqlParameter("@username",SqlDbType.VarChar,32), new SqlParameter("@password",SqlDbType.VarChar,32), new SqlParameter("@email",SqlDbType.VarChar,50), new SqlParameter("@sex",SqlDbType.VarChar,2), new SqlParameter("@age",SqlDbType.Int), new SqlParameter("@address",SqlDbType.VarChar,50) }; para[0].Value = user.UserName; para[1].Value = user.Password; para[2].Value = user.Email; para[3].Value = user.Sex; para[4].Value = user.Age; para[5].Value = user.Address; SqlConnection con = getTestTDataConnection();//调用我上面写的方法.得到数据库连接 SqlCommand commd = new SqlCommand(sql,con); commd.Parameters.AddRange(para); con.Open(); int ret = commd.ExecuteNonQuery(); con.Close(); return ret; } public int deleteUser(string username) //删除用户信息 { string sql = "delete from userInfo where userName = @username"; SqlParameter[] para = new SqlParameter[] { new SqlParameter("@username",SqlDbType.VarChar,32)}; para[0].Value = username; SqlConnection con = getTestTDataConnection(); SqlCommand commd = new SqlCommand(sql, con); commd.Parameters.AddRange(para); con.Open(); int ret = commd.ExecuteNonQuery(); con.Close(); return ret; } public int updateUserInfo(User user) //修改用户信息 { string sql = "update userInfo set password = @password where userName = @username"; SqlParameter[] para = new SqlParameter[] { new SqlParameter("@username",SqlDbType.VarChar,32), new SqlParameter("@password",SqlDbType.VarChar,32) }; para[0].Value = user.UserName; para[1].Value = user.Password; SqlConnection con = getTestTDataConnection(); SqlCommand commd = new SqlCommand(sql, con); commd.Parameters.AddRange(para); con.Open(); int ret = commd.ExecuteNonQuery(); con.Close(); return ret; } }给分吧,我没什么分用了啊兄弟,所以才这么辛苦的为你写了这么些代码啊!
//ExecuteNonQuery: 执行 Transact-SQL INSERT、DELETE、UPDATE 及 SET 语句等命令。//queryString:select...、INSERT...、DELETE...、UPDATE....语句//connectionString:Data Source=.;Initial Catalog=ERPDataBase;Integrated Security=True;User ID=sa; Password=123 // 或 Data Source=.;Initial Catalog=ERPDataBase;Integrated Security=Trueprivate static void CreateCommand(string queryString, string connectionString) { SqlConnection connection = new SqlConnection(connectionString); Connection.Open(); SqlCommand command = new SqlCommand(queryString, connection); command.ExecuteNonQuery(); Connection.Close();
}
先从最基础的ado.net学起走吧.学会了在用以上数据库操作类
一、基础 1、说明:创建数据库 Create DATABASE database-name 2、说明:删除数据库 drop database dbname 3、说明:备份sql server --- 创建 备份数据的 device USE master EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat' --- 开始 备份 BACKUP DATABASE pubs TO testBack 4、说明:创建新表 create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..) 根据已有的表创建新表: A:create table tab_new like tab_old (使用旧表创建新表) B:create table tab_new as select col1,col2… from tab_old definition only 5、说明:删除新表 drop table tabname 6、说明:增加一个列 Alter table tabname add column col type 注:列增加后将不能删除。DB2中列加上后数据类型也不能改变,唯一能改变的是增加varchar类型的长度。 7、说明:添加主键: Alter table tabname add primary key(col) 说明:删除主键: Alter table tabname drop primary key(col) 8、说明:创建索引:create [unique] index idxname on tabname(col….) 删除索引:drop index idxname on tabname 注:索引是不可更改的,想更改必须删除重新建。 9、说明:创建视图:create view viewname as select statement 删除视图:drop view viewname 10、说明:几个简单的基本的sql语句 选择:select * from table1 where 范围 插入:insert into table1(field1,field2) values(value1,value2) 删除:delete from table1 where 范围 更新:update table1 set field1=value1 where 范围 查找:select * from table1 where field1 like ’%value1%’ (所有包含‘value1’这个模式的字符串)---like的语法很精妙,查资料! 排序:select * from table1 order by field1,field2 [desc] 总数:select count as totalcount from table1 求和:select sum(field1) as sumvalue from table1 平均:select avg(field1) as avgvalue from table1 最大:select max(field1) as maxvalue from table1 最小:select min(field1) as minvalue from table1[separator] 11、说明:几个高级查询运算词 A: UNION 运算符 UNION 运算符通过组合其他两个结果表(例如 TABLE1 和 TABLE2)并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时(即 UNION ALL),不消除重复行。两种情况下,派生表的每一行不是来自 TABLE1 就是来自 TABLE2。 B: EXCEPT 运算符 EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL),不消除重复行。 C: INTERSECT 运算符 INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL),不消除重复行。 注:使用运算词的几个查询结果行必须是一致的。 12、说明:使用外连接 A、left outer join: 左外连接(左连接):结果集几包括连接表的匹配行,也包括左连接表的所有行。 SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c B:right outer join: 右外连接(右连接):结果集既包括连接表的匹配连接行,也包括右连接表的所有行。 C:full outer join: 全外连接:不仅包括符号连接表的匹配行,还包括两个连接表中的所有记录。 二、提升 1、说明:复制表(只复制结构,源表名:a 新表名:b) (Access可用) 法一:select * into b from a where 1<>1 法二:select top 0 * into b from a 2、说明:拷贝表(拷贝数据,源表名:a 目标表名:b) (Access可用) insert into b(a, b, c) select d,e,f from b; 3、说明:跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用) insert into b(a, b, c) select d,e,f from b in ‘具体数据库’ where 条件 例子:..from b in '"&Server.MapPath("."&"\data.mdb" &"' where.. 4、说明:子查询(表名1:a 表名2:b) select a,b,c from a where a IN (select d from b 或者: select a,b,c from a where a IN (1,2,3) 5、说明:显示文章、提交人和最后回复时间 select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b 6、说明:外连接查询(表名1:a 表名2:b) select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c 7、说明:在线视图查询(表名1:a select * from (Select a,b,c FROM a) T where t.a > 1; 8、说明:between的用法,between限制查询数据范围时包括了边界值,not between不包括 select * from table1 where time between time1 and time2 select a,b,c, from table1 where a not between 数值1 and 数值2 9、说明:in 的使用方法 select * from table1 where a [not] in (‘值1’,’值2’,’值4’,’值6’) 10、说明:两张关联表,删除主表中已经在副表中没有的信息 delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 11、说明:四表联查问题: select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where ..... 12、说明:日程安排提前五分钟提醒 SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5 13、说明:一条sql 语句搞定数据库分页 select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段 14、说明:前10条记录 select top 10 * form table1 where 范围 15、说明:选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.) select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b) 16、说明:包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表 (select a from tableA except (select a from tableB) except (select a from tableC) 17、说明:随机取出10条数据 select top 10 * from tablename order by newid() 18、说明:随机选择记录 select newid() 19、说明:删除重复记录 Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...) 20、说明:列出数据库里所有的表名 select name from sysobjects where type='U' 21、说明:列出表里的所有的 select name from syscolumns where id=object_id('TableName') 22、说明:列示type、vender、pcs字段,以type字段排列,case可以方便地实现多重选择,类似select 中的case。 select type,sum(case vender when 'A' then pcs else 0 end),sum(case vender when 'C' then pcs else 0 end),sum(case vender when 'B' then pcs else 0 end) FROM tablename group by type 显示结果: type vender pcs 电脑 A 1 电脑 A 1 光盘 B 2 光盘 A 2 手机 B 3 手机 C 3 23、说明:初始化表table1 TRUNCATE TABLE table1 24、说明:选择从10到15的记录 select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc 三、技巧 1、1=1,1=2的使用,在SQL语句组合时用的较多 “where 1=1” 是表示选择全部 “where 1=2”全部不选, 如: if @strWhere !=' begin set @strSQL = 'select count(*) as Total from [' + @tblName + '] where ' + @strWhere end else begin set @strSQL = 'select count(*) as Total from [' + @tblName + ']' end 我们可以直接写成 set @strSQL = 'select count(*) as Total from [' + @tblName + '] where 1=1 安定 '+ @strWhere 2、收缩数据库 --重建索引 DBCC REINDEX DBCC INDEXDEFRAG --收缩数据和日志 DBCC SHRINKDB DBCC SHRINKFILE 3、压缩数据库 dbcc shrinkdatabase(dbname) 4、转移数据库给新用户以已存在用户权限 exec sp_change_users_login 'update_one','newname','oldname' go 5、检查备份集 RESTORE VERIFYONLY from disk='E:\dvbbs.bak' 6、修复数据库 Alter DATABASE [dvbbs] SET SINGLE_USER GO DBCC CHECKDB('dvbbs',repair_allow_data_loss) WITH TABLOCK GO Alter DATABASE [dvbbs] SET MULTI_USER GO 7、日志清除 SET NOCOUNT ON DECLARE @LogicalFileName sysname, @MaxMinutes INT, @NewSize INT USE tablename -- 要操作的数据库名 Select @LogicalFileName = 'tablename_log', -- 日志文件名 @MaxMinutes = 10, -- Limit on time allowed to wrap log. @NewSize = 1 -- 你想设定的日志文件的大小(M) -- Setup / initialize DECLARE @OriginalSize int Select @OriginalSize = size FROM sysfiles Where name = @LogicalFileName Select 'Original Size of ' + db_name() + ' LOG is ' + CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' + CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB' FROM sysfiles Where name = @LogicalFileName Create TABLE DummyTrans (DummyColumn char (8000) not null) DECLARE @Counter INT, @StartTime DATETIME, @TruncLog VARCHAR(255) Select @StartTime = GETDATE(), @TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY' DBCC SHRINKFILE (@LogicalFileName, @NewSize) EXEC (@TruncLog) -- Wrap the log if necessary. WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired AND @OriginalSize = (Select size FROM sysfiles Where name = @LogicalFileName) AND (@OriginalSize * 8 /1024) > @NewSize BEGIN -- Outer loop. Select @Counter = 0 WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000)) BEGIN -- update Insert DummyTrans VALUES ('Fill Log') Delete DummyTrans Select @Counter = @Counter + 1 END EXEC (@TruncLog) END Select 'Final Size of ' + db_name() + ' LOG is ' + CONVERT(VARCHAR(30),size) + ' 8K pages or ' + CONVERT(VARCHAR(30),(size*8/1024)) + 'MB' FROM sysfiles Where name = @LogicalFileName Drop TABLE DummyTrans SET NOCOUNT OFF 8、说明:更改某个表 exec sp_changeobjectowner 'tablename','dbo' 9、存储更改全部表 Create PROCEDURE dbo.User_ChangeObjectOwnerBatch @OldOwner as NVARCHAR(128), @NewOwner as NVARCHAR(128) AS DECLARE @Name as NVARCHAR(128) DECLARE @Owner as NVARCHAR(128) DECLARE @OwnerName as NVARCHAR(128) DECLARE curObject CURSOR FOR select 'Name' = name, 'Owner' = user_name(uid) from sysobjects where user_name(uid)=@OldOwner order by name OPEN curObject FETCH NEXT FROM curObject INTO @Name, @Owner WHILE(@@FETCH_STATUS=0) BEGIN if @Owner=@OldOwner begin set @OwnerName = @OldOwner + '.' + rtrim(@Name) exec sp_changeobjectowner @OwnerName, @NewOwner end -- select @name,@NewOwner,@OldOwner FETCH NEXT FROM curObject INTO @Name, @Owner END close curObject deallocate curObject GO 10、SQL SERVER中直接循环写入数据 declare @i int set @i=1 while @i<30 begin insert into test (userid) values(@i) set @i=@i+1 end
比如:public static DataSet ExecuteDataSet(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{
DataSet TempObj;
DataSet __ds = new DataSet();
SqlCommand cmd = new SqlCommand();
SqlConnection conn = new SqlConnection(connectionString);
SqlDataAdapter sda = new SqlDataAdapter();
try
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
sda.SelectCommand = cmd;
sda.Fill(__ds);
cmd.Parameters.Clear();
TempObj = __ds;
}
catch
{
conn.Close();
throw;
}
return TempObj;
}
//这样调用
DataSet dataSet = DataOp.ExecuteDataSet(DataOp.connBackupPhoneMsg, CommandType.StoredProcedure, "up_getBackupList", para);
//up_getBackupList 这是存储过程名
//DataOp.connBackupPhoneMsg 这是连接字符串
增:insert into 表名(插入的列名)values(插入的值)
删: delete 表名 where 条件
改: update 表名 列名 set=值
查: select 列名 from 表名 或:select 列名 from 表名 where 条件
{
SqlConnection con = new SqlConnection(_ConString); try
{
//连接数据库中
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
con.Open(); cmd.CommandText = string.Format("select * from sysobjects where name='NoPrompt'");
SqlDataReader dr = cmd.ExecuteReader(); int a = 0;
while (dr.Read())
{
a = 1;
}
dr.Close();
if (a == 0)
{
cmd.CommandText = string.Format("create table NoPrompt(noprompt int primary key)");
cmd.ExecuteNonQuery();
}
cmd.CommandText = string.Format("insert into NoPrompt values('{0}')", i);
cmd.ExecuteNonQuery();
return true;
}
catch (Exception)
{
return false;
}
finally
{
//关闭连接
con.Close();
}
}
【如果我的回答对你有所帮助,请到下列地址http://download.csdn.net/source/2247484 下载原创家庭记账软件,并给出宝贵意见!】
using System.Data;
using System.Data.SqlClient;//一定要引用这个public class databaseconn
{
public SqlConnection con;
public SqlConnection getTestTDataConnection()//获得连接
{
con = new SqlConnection("Server=(local);uid=sa;pwd=123456;database=testT");//这里面要改成你自己的uid:数据库用户名,pwd:数据库密码,database:要增删除改的数据库名
return con;
}
public void closeDataConnection(SqlConnection con)//关闭连接
{
if (con == null)
con.Close();
}
public int AddUser(User user)//添加用户
{
string sql = "insert userInfo values(@username,@password,@email,@sex,@age,@address)"; SqlParameter[] para = new SqlParameter[]
{
new SqlParameter("@username",SqlDbType.VarChar,32),
new SqlParameter("@password",SqlDbType.VarChar,32),
new SqlParameter("@email",SqlDbType.VarChar,50),
new SqlParameter("@sex",SqlDbType.VarChar,2),
new SqlParameter("@age",SqlDbType.Int),
new SqlParameter("@address",SqlDbType.VarChar,50)
};
para[0].Value = user.UserName;
para[1].Value = user.Password;
para[2].Value = user.Email;
para[3].Value = user.Sex;
para[4].Value = user.Age;
para[5].Value = user.Address;
SqlConnection con = getTestTDataConnection();//调用我上面写的方法.得到数据库连接
SqlCommand commd = new SqlCommand(sql,con);
commd.Parameters.AddRange(para);
con.Open();
int ret = commd.ExecuteNonQuery();
con.Close();
return ret;
} public int deleteUser(string username) //删除用户信息
{
string sql = "delete from userInfo where userName = @username";
SqlParameter[] para = new SqlParameter[] { new SqlParameter("@username",SqlDbType.VarChar,32)};
para[0].Value = username;
SqlConnection con = getTestTDataConnection();
SqlCommand commd = new SqlCommand(sql, con);
commd.Parameters.AddRange(para);
con.Open();
int ret = commd.ExecuteNonQuery();
con.Close();
return ret;
} public int updateUserInfo(User user) //修改用户信息
{
string sql = "update userInfo set password = @password where userName = @username";
SqlParameter[] para = new SqlParameter[]
{ new SqlParameter("@username",SqlDbType.VarChar,32),
new SqlParameter("@password",SqlDbType.VarChar,32)
};
para[0].Value = user.UserName;
para[1].Value = user.Password;
SqlConnection con = getTestTDataConnection();
SqlCommand commd = new SqlCommand(sql, con);
commd.Parameters.AddRange(para);
con.Open();
int ret = commd.ExecuteNonQuery();
con.Close();
return ret;
}
}给分吧,我没什么分用了啊兄弟,所以才这么辛苦的为你写了这么些代码啊!
说明:个人感觉在Java领域大型开发都离不了ORM的身影,所谓的SSH就是Spring+Struts+Hibernate,除了在学习基础知识的时候被告知可以使用JDBC操作数据库之外,大量的书籍中都是讲述使用Hibernate这个ORM工具来操作数据。在.NET中操作数据库的方式有多种,除了最直接的方式就是使用ADO.NET之外,还可以使用NHibernate这个Hibernate在.NET中的实现ORM,如果你对第三方的ORM 持怀疑态度,你还可以使用来自微软的实现、根正苗红的Linq或者EntityFramework。大部分从早期就开始使用.NET开发的程序员可能对ADO.NET有种迷恋,使用ADO.NET可以充分将我们早期的SQL知识发挥得淋漓尽致,并且出于对性能的考虑,有些人对.NET中的ORM还保持一种观望态度,包括我自己也是这种态度。不过即使在实际开发中不用,并不代表我们不能去了解和比较这些技术,任何事物的出现和消亡总有其原因的,我们可以了解它们的优点和长处。所以本人抽出了几个周末的时间分别用ADO.NET、NHibernate、 Linq和EntityFramework来
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;namespace ADODoNETDemo
{
/// <summary>
/// 针对SQL Server数据库操作的通用类
/// 作者:周公
/// 日期:2009-01-08
/// Version:1.0
/// </summary>
public class SqlDbHelper
{
private string connectionString;
/// <summary>
/// 设置数据库连接字符串
/// </summary>
public string ConnectionString
{
set { connectionString = value; }
}
/// <summary>
/// 构造函数
/// </summary>
public SqlDbHelper()
: this(ConfigurationManager.ConnectionStrings["Conn"].ConnectionString)
{ }
/// <summary>
/// 构造函数
/// </summary>
/// <param name="connectionString">数据库连接字符串</param>
public SqlDbHelper(string connectionString)
{
this.connectionString = connectionString;
}
/// <summary>
/// 执行一个查询,并返回结果集
/// </summary>
/// <param name="sql">要执行的查询SQL文本命令</param>
/// <returns>返回查询结果集</returns>
public DataTable ExecuteDataTable(string sql)
{
return ExecuteDataTable(sql, CommandType.Text, null);
}
/// <summary>
/// 执行一个查询,并返回查询结果
/// </summary>
/// <param name="sql">要执行的SQL语句</param>
/// <param name="commandType">要执行的查询语句的类型,如存储过程或者SQL文本命令</param>
/// <returns>返回查询结果集</returns>
public DataTable ExecuteDataTable(string sql, CommandType commandType)
{
return ExecuteDataTable(sql, commandType, null);
}
/// <summary>
/// 执行一个查询,并返回查询结果
/// </summary>
/// <param name="sql">要执行的SQL语句</param>
/// <param name="commandType">要执行的查询语句的类型,如存储过程或者SQL文本命令</param>
/// <param name="parameters">Transact-SQL 语句或存储过程的参数数组</param>
/// <returns></returns>
public DataTable ExecuteDataTable(string sql, CommandType commandType, SqlParameter[] parameters)
{
DataTable data = new DataTable();//实例化DataTable,用于装载查询结果集
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.CommandType = commandType;//设置command的CommandType为指定的CommandType
//如果同时传入了参数,则添加这些参数
if (parameters != null)
{
foreach (SqlParameter parameter in parameters)
{
command.Parameters.Add(parameter);
}
}
//通过包含查询SQL的SqlCommand实例来实例化SqlDataAdapter
SqlDataAdapter adapter = new SqlDataAdapter(command); adapter.Fill(data);//填充DataTable
}
}
return data;
}
/// <summary>
///
/// </summary>
/// <param name="sql">要执行的查询SQL文本命令</param>
/// <returns></returns>
public SqlDataReader ExecuteReader(string sql)
{
return ExecuteReader(sql, CommandType.Text, null);
}
/// <summary>
///
/// </summary>
/// <param name="sql">要执行的SQL语句</param>
/// <param name="commandType">要执行的查询语句的类型,如存储过程或者SQL文本命令</param>
/// <returns></returns>
public SqlDataReader ExecuteReader(string sql, CommandType commandType)
{
return ExecuteReader(sql, commandType, null);
}
/// <summary>
///
/// </summary>
/// <param name="sql">要执行的SQL语句</param>
/// <param name="commandType">要执行的查询语句的类型,如存储过程或者SQL文本命令</param>
/// <param name="parameters">Transact-SQL 语句或存储过程的参数数组</param>
/// <returns></returns>
public SqlDataReader ExecuteReader(string sql, CommandType commandType, SqlParameter[] parameters)
{
SqlConnection connection = new SqlConnection(connectionString);
SqlCommand command = new SqlCommand(sql, connection);
//如果同时传入了参数,则添加这些参数
if (parameters != null)
{
foreach (SqlParameter parameter in parameters)
{
command.Parameters.Add(parameter);
}
}
connection.Open();
//CommandBehavior.CloseConnection参数指示关闭Reader对象时关闭与其关联的Connection对象
return command.ExecuteReader(CommandBehavior.CloseConnection);
}
/// <summary>
///
/// </summary>
/// <param name="sql">要执行的查询SQL文本命令</param>
/// <returns></returns>
public Object ExecuteScalar(string sql)
{
return ExecuteScalar(sql, CommandType.Text, null);
}
/// <summary>
///
/// </summary>
/// <param name="sql">要执行的SQL语句</param>
/// <param name="commandType">要执行的查询语句的类型,如存储过程或者SQL文本命令</param>
/// <returns></returns>
public Object ExecuteScalar(string sql, CommandType commandType)
{
return ExecuteScalar(sql, commandType, null);
}
/// <summary>
///
/// </summary>
/// <param name="sql">要执行的SQL语句</param>
/// <param name="commandType">要执行的查询语句的类型,如存储过程或者SQL文本命令</param>
/// <param name="parameters">Transact-SQL 语句或存储过程的参数数组</param>
/// <returns></returns>
public Object ExecuteScalar(string sql, CommandType commandType, SqlParameter[] parameters)
{
object result = null;
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.CommandType = commandType;//设置command的CommandType为指定的CommandType
//如果同时传入了参数,则添加这些参数
if (parameters != null)
{
foreach (SqlParameter parameter in parameters)
{
command.Parameters.Add(parameter);
}
}
connection.Open();//打开数据库连接
result = command.ExecuteScalar();
}
}
return result;//返回查询结果的第一行第一列,忽略其它行和列
}
/// <summary>
/// 对数据库执行增删改操作
/// </summary>
/// <param name="sql">要执行的查询SQL文本命令</param>
/// <returns></returns>
public int ExecuteNonQuery(string sql)
{
return ExecuteNonQuery(sql, CommandType.Text, null);
}
/// <summary>
/// 对数据库执行增删改操作
/// </summary>
/// <param name="sql">要执行的SQL语句</param>
/// <param name="commandType">要执行的查询语句的类型,如存储过程或者SQL文本命令</param>
/// <returns></returns>
public int ExecuteNonQuery(string sql, CommandType commandType)
{
return ExecuteNonQuery(sql, commandType, null);
}
/// <summary>
/// 对数据库执行增删改操作
/// </summary>
/// <param name="sql">要执行的SQL语句</param>
/// <param name="commandType">要执行的查询语句的类型,如存储过程或者SQL文本命令</param>
/// <param name="parameters">Transact-SQL 语句或存储过程的参数数组</param>
/// <returns></returns>
public int ExecuteNonQuery(string sql, CommandType commandType, SqlParameter[] parameters)
{
int count = 0;
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.CommandType = commandType;//设置command的CommandType为指定的CommandType
//如果同时传入了参数,则添加这些参数
if (parameters != null)
{
foreach (SqlParameter parameter in parameters)
{
command.Parameters.Add(parameter);
}
}
connection.Open();//打开数据库连接
count = command.ExecuteNonQuery();
}
}
return count;//返回执行增删改操作之后,数据库中受影响的行数
}
/// <summary>
/// 返回当前连接的数据库中所有由用户创建的数据库
/// </summary>
/// <returns></returns>
public DataTable GetTables()
{
DataTable data = null;
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();//打开数据库连接
data = connection.GetSchema("Tables");
}
return data;
} }
}
//ExecuteNonQuery: 执行 Transact-SQL INSERT、DELETE、UPDATE 及 SET 语句等命令。//queryString:select...、INSERT...、DELETE...、UPDATE....语句//connectionString:Data Source=.;Initial Catalog=ERPDataBase;Integrated Security=True;User ID=sa;
Password=123
// 或 Data Source=.;Initial Catalog=ERPDataBase;Integrated Security=Trueprivate static void CreateCommand(string queryString, string connectionString)
{
SqlConnection connection = new SqlConnection(connectionString);
Connection.Open();
SqlCommand command = new SqlCommand(queryString, connection);
command.ExecuteNonQuery();
Connection.Close();
}
Create DATABASE database-name
2、说明:删除数据库
drop database dbname
3、说明:备份sql server
--- 创建 备份数据的 device
USE master
EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat'
--- 开始 备份
BACKUP DATABASE pubs TO testBack
4、说明:创建新表
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)
根据已有的表创建新表:
A:create table tab_new like tab_old (使用旧表创建新表)
B:create table tab_new as select col1,col2… from tab_old definition only
5、说明:删除新表
drop table tabname
6、说明:增加一个列
Alter table tabname add column col type
注:列增加后将不能删除。DB2中列加上后数据类型也不能改变,唯一能改变的是增加varchar类型的长度。
7、说明:添加主键: Alter table tabname add primary key(col)
说明:删除主键: Alter table tabname drop primary key(col)
8、说明:创建索引:create [unique] index idxname on tabname(col….)
删除索引:drop index idxname on tabname
注:索引是不可更改的,想更改必须删除重新建。
9、说明:创建视图:create view viewname as select statement
删除视图:drop view viewname
10、说明:几个简单的基本的sql语句
选择:select * from table1 where 范围
插入:insert into table1(field1,field2) values(value1,value2)
删除:delete from table1 where 范围
更新:update table1 set field1=value1 where 范围
查找:select * from table1 where field1 like ’%value1%’ (所有包含‘value1’这个模式的字符串)---like的语法很精妙,查资料!
排序:select * from table1 order by field1,field2 [desc]
总数:select count as totalcount from table1
求和:select sum(field1) as sumvalue from table1
平均:select avg(field1) as avgvalue from table1
最大:select max(field1) as maxvalue from table1
最小:select min(field1) as minvalue from table1[separator]
11、说明:几个高级查询运算词
A: UNION 运算符
UNION 运算符通过组合其他两个结果表(例如 TABLE1 和 TABLE2)并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时(即 UNION ALL),不消除重复行。两种情况下,派生表的每一行不是来自 TABLE1 就是来自 TABLE2。
B: EXCEPT 运算符
EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL),不消除重复行。
C: INTERSECT 运算符
INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL),不消除重复行。
注:使用运算词的几个查询结果行必须是一致的。
12、说明:使用外连接
A、left outer join:
左外连接(左连接):结果集几包括连接表的匹配行,也包括左连接表的所有行。
SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c
B:right outer join:
右外连接(右连接):结果集既包括连接表的匹配连接行,也包括右连接表的所有行。
C:full outer join:
全外连接:不仅包括符号连接表的匹配行,还包括两个连接表中的所有记录。 二、提升
1、说明:复制表(只复制结构,源表名:a 新表名:b) (Access可用)
法一:select * into b from a where 1<>1
法二:select top 0 * into b from a
2、说明:拷贝表(拷贝数据,源表名:a 目标表名:b) (Access可用)
insert into b(a, b, c) select d,e,f from b;
3、说明:跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用)
insert into b(a, b, c) select d,e,f from b in ‘具体数据库’ where 条件
例子:..from b in '"&Server.MapPath("."&"\data.mdb" &"' where..
4、说明:子查询(表名1:a 表名2:b)
select a,b,c from a where a IN (select d from b 或者: select a,b,c from a where a IN (1,2,3)
5、说明:显示文章、提交人和最后回复时间
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b
6、说明:外连接查询(表名1:a 表名2:b)
select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c
7、说明:在线视图查询(表名1:a
select * from (Select a,b,c FROM a) T where t.a > 1;
8、说明:between的用法,between限制查询数据范围时包括了边界值,not between不包括
select * from table1 where time between time1 and time2
select a,b,c, from table1 where a not between 数值1 and 数值2
9、说明:in 的使用方法
select * from table1 where a [not] in (‘值1’,’值2’,’值4’,’值6’)
10、说明:两张关联表,删除主表中已经在副表中没有的信息
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1
11、说明:四表联查问题:
select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where .....
12、说明:日程安排提前五分钟提醒
SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5
13、说明:一条sql 语句搞定数据库分页
select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段
14、说明:前10条记录
select top 10 * form table1 where 范围
15、说明:选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.)
select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b)
16、说明:包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表
(select a from tableA except (select a from tableB) except (select a from tableC)
17、说明:随机取出10条数据
select top 10 * from tablename order by newid()
18、说明:随机选择记录
select newid()
19、说明:删除重复记录
Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...)
20、说明:列出数据库里所有的表名
select name from sysobjects where type='U'
21、说明:列出表里的所有的
select name from syscolumns where id=object_id('TableName')
22、说明:列示type、vender、pcs字段,以type字段排列,case可以方便地实现多重选择,类似select 中的case。
select type,sum(case vender when 'A' then pcs else 0 end),sum(case vender when 'C' then pcs else 0 end),sum(case vender when 'B' then pcs else 0 end) FROM tablename group by type
显示结果:
type vender pcs
电脑 A 1
电脑 A 1
光盘 B 2
光盘 A 2
手机 B 3
手机 C 3
23、说明:初始化表table1
TRUNCATE TABLE table1
24、说明:选择从10到15的记录
select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc 三、技巧
1、1=1,1=2的使用,在SQL语句组合时用的较多
“where 1=1” 是表示选择全部 “where 1=2”全部不选,
如:
if @strWhere !='
begin
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where ' + @strWhere
end
else
begin
set @strSQL = 'select count(*) as Total from [' + @tblName + ']'
end
我们可以直接写成
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where 1=1 安定 '+ @strWhere
2、收缩数据库
--重建索引
DBCC REINDEX
DBCC INDEXDEFRAG
--收缩数据和日志
DBCC SHRINKDB
DBCC SHRINKFILE
3、压缩数据库
dbcc shrinkdatabase(dbname)
4、转移数据库给新用户以已存在用户权限
exec sp_change_users_login 'update_one','newname','oldname'
go
5、检查备份集
RESTORE VERIFYONLY from disk='E:\dvbbs.bak'
6、修复数据库
Alter DATABASE [dvbbs] SET SINGLE_USER
GO
DBCC CHECKDB('dvbbs',repair_allow_data_loss) WITH TABLOCK
GO
Alter DATABASE [dvbbs] SET MULTI_USER
GO
7、日志清除
SET NOCOUNT ON
DECLARE @LogicalFileName sysname,
@MaxMinutes INT,
@NewSize INT
USE tablename -- 要操作的数据库名
Select @LogicalFileName = 'tablename_log', -- 日志文件名
@MaxMinutes = 10, -- Limit on time allowed to wrap log.
@NewSize = 1 -- 你想设定的日志文件的大小(M)
-- Setup / initialize
DECLARE @OriginalSize int
Select @OriginalSize = size
FROM sysfiles
Where name = @LogicalFileName
Select 'Original Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB'
FROM sysfiles
Where name = @LogicalFileName
Create TABLE DummyTrans
(DummyColumn char (8000) not null)
DECLARE @Counter INT,
@StartTime DATETIME,
@TruncLog VARCHAR(255)
Select @StartTime = GETDATE(),
@TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY'
DBCC SHRINKFILE (@LogicalFileName, @NewSize)
EXEC (@TruncLog)
-- Wrap the log if necessary.
WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired
AND @OriginalSize = (Select size FROM sysfiles Where name = @LogicalFileName)
AND (@OriginalSize * 8 /1024) > @NewSize
BEGIN -- Outer loop.
Select @Counter = 0
WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000))
BEGIN -- update
Insert DummyTrans VALUES ('Fill Log')
Delete DummyTrans
Select @Counter = @Counter + 1
END
EXEC (@TruncLog)
END
Select 'Final Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),size) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'
FROM sysfiles
Where name = @LogicalFileName
Drop TABLE DummyTrans
SET NOCOUNT OFF
8、说明:更改某个表
exec sp_changeobjectowner 'tablename','dbo'
9、存储更改全部表
Create PROCEDURE dbo.User_ChangeObjectOwnerBatch
@OldOwner as NVARCHAR(128),
@NewOwner as NVARCHAR(128)
AS
DECLARE @Name as NVARCHAR(128)
DECLARE @Owner as NVARCHAR(128)
DECLARE @OwnerName as NVARCHAR(128)
DECLARE curObject CURSOR FOR
select 'Name' = name,
'Owner' = user_name(uid)
from sysobjects
where user_name(uid)=@OldOwner
order by name
OPEN curObject
FETCH NEXT FROM curObject INTO @Name, @Owner
WHILE(@@FETCH_STATUS=0)
BEGIN
if @Owner=@OldOwner
begin
set @OwnerName = @OldOwner + '.' + rtrim(@Name)
exec sp_changeobjectowner @OwnerName, @NewOwner
end
-- select @name,@NewOwner,@OldOwner
FETCH NEXT FROM curObject INTO @Name, @Owner
END
close curObject
deallocate curObject
GO
10、SQL SERVER中直接循环写入数据
declare @i int
set @i=1
while @i<30
begin
insert into test (userid) values(@i)
set @i=@i+1
end
学习T_sql 中的 增 删 改 查 就你知道怎么做了!