C 與sqlserver數(shù)據(jù)庫操作_附實(shí)例說明及sql語句大全

1、C#數(shù)據(jù)庫連接操作大全+sql語句大全下面是c#與數(shù)據(jù)庫的連接及增刪改除的各種操作，全部經(jīng)過上機(jī)驗(yàn)證。學(xué)習(xí)軟件的過程中，數(shù)據(jù)庫起著至關(guān)重要的作用。軟件行業(yè)里面有句老話，不會數(shù)據(jù)庫就沒有入門。軟件思想可以慢慢培養(yǎng)，但是數(shù)據(jù)庫的鏈接是一定要學(xué)會的。增刪改查各種都不能少。創(chuàng)立數(shù)據(jù)庫 創(chuàng)立之前判斷該數(shù)據(jù)庫是否存在 if exists (select * from sysdatabases where name='databaseName') drop database 'databaseName' go Create DATABASE database-name刪除數(shù)據(jù)

2、庫 drop database dbname備份sql server - 創(chuàng)立 備份數(shù)據(jù)的 device USE master EXEC sp_addumpdevice 'disk', 'testBack', 'c:mssql7backupMyNwind_1.dat' - 開始 備份 BACKUP DATABASE pubs TO testBack創(chuàng)立新表 create table tabname(col1 type1 not null primary key,col2 type2 not null,.) 根據(jù)已有的表創(chuàng)立新表： A：create

3、 table tab_new like tab_old (使用舊表創(chuàng)立新表) B：create table tab_new as select col1,col2 from tab_old definition only刪除新表 drop table tabname增加一個(gè)列 Alter table tabname add column col type 注：列增加后將不能刪除。DB2中列加上后數(shù)據(jù)類型也不能改變，唯一能改變的是增加varchar類型的長度。添加主鍵 Alter table tabname add primary key(col) 說明：刪除主鍵： Alter table ta

4、bname drop primary key(col)創(chuàng)立索引 create unique index idxname on tabname(col.) 刪除索引：drop index idxname on tabname 注：索引是不可更改的，想更改必須刪除重新建。創(chuàng)立視圖 create view viewname as select statement 刪除視圖：drop view viewname幾個(gè)簡單的根本的sql語句 選擇：select * from table1 where 范圍 插入：insert into table1(field1,field2) values(value1

5、,value2) 刪除：delete from table1 where 范圍 更新：update table1 set field1=value1 where 范圍 查找：select * from table1 where field1 like %value1% 所有包含value1這個(gè)模式的字符串-like的語法很精妙，查資料! 排序：select * from table1 order by field1,field2 desc 總數(shù)：select count(*) as totalcount from table1 求和：select sum(field1) as sumvalue

6、 from table1 平均：select avg(field1) as avgvalue from table1 最大：select max(field1) as maxvalue from table1 最?。簊elect min(field1) as minvalue from table1separator幾個(gè)高級查詢運(yùn)算詞 A： UNION 運(yùn)算符 UNION 運(yùn)算符通過組合其他兩個(gè)結(jié)果表例如 TABLE1 和 TABLE2并消去表中任何重復(fù)行而派生出一個(gè)結(jié)果表。當(dāng) ALL 隨 UNION 一起使用時(shí)即 UNION ALL，不消除重復(fù)行。兩種情況下，派生表的每一行不是來自 TABL

7、E1 就是來自 TABLE2。 B： EXCEPT 運(yùn)算符 EXCEPT 運(yùn)算符通過包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重復(fù)行而派生出一個(gè)結(jié)果表。當(dāng) ALL 隨 EXCEPT 一起使用時(shí) (EXCEPT ALL)，不消除重復(fù)行。 C： INTERSECT 運(yùn)算符 INTERSECT 運(yùn)算符通過只包括 TABLE1 和 TABLE2 中都有的行并消除所有重復(fù)行而派生出一個(gè)結(jié)果表。當(dāng) ALL 隨 INTERSECT 一起使用時(shí) (INTERSECT ALL)，不消除重復(fù)行。 注：使用運(yùn)算詞的幾個(gè)查詢結(jié)果行必須是一致的。使用外連接 A、left outer join：

8、左外連接左連接：結(jié)果集既包括連接表的匹配行，也包括左連接表的所有行。 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: 右外連接(右連接)：結(jié)果集既包括連接表的匹配連接行，也包括右連接表的所有行。 C：full outer join： 全外連接：不僅包括符號連接表的匹配行，還包括兩個(gè)連接表中的所有記錄。編輯本段提升復(fù)制表 (只復(fù)制結(jié)構(gòu),源表名：a 新表名：b) (Access可用) 法一：select * into b from a where 1&l

9、t;>1 法二：select top 0 * into b from a拷貝表 (拷貝數(shù)據(jù),源表名：a 目標(biāo)表名：b) (Access可用) insert into b(a, b, c) select d,e,f from b;跨數(shù)據(jù)庫之間表的拷貝 (具體數(shù)據(jù)使用絕對路徑) (Access可用) insert into b(a, b, c) select d,e,f from b in 具體數(shù)據(jù)庫 where 條件 例子：.from b in '"&Server.MapPath("."&"data.mdb" &am

10、p;"' where.子查詢 (表名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)顯示文章、提交人和最后回復(fù)時(shí)間 select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b外連接查詢在線視圖查詢 (表名1：a select * from (Selec

11、t a,b,c FROM a) T where t.a > 1;between的用法 between限制查詢數(shù)據(jù)范圍時(shí)包括了邊界值,not between不包括 select * from table1 where time between time1 and time2 select a,b,c, from table1 where a not between 數(shù)值1 and 數(shù)值2in 的使用方法 select * from table1 where a not in (值1,值2,值4,值6)刪除主表中已經(jīng)在副表中沒有的信息四表聯(lián)查問題 select * from a left i

12、nner 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 .日程安排提前五分鐘提醒 SQL: select * from 日程安排 where datediff('minute',f開始時(shí)間,getdate()>5一條sql 語句搞定數(shù)據(jù)庫分頁前10條記錄 select top 10 * form table1 where 范圍選擇排名 選擇在每一組b值相同的數(shù)據(jù)中對應(yīng)的a最大的記錄的所有信息(類似這樣的用法可以用于論壇每月排行榜,每月熱銷產(chǎn)品分析,按科目成績排名,

13、等等.) select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b)派生結(jié)果表 包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重復(fù)行而派生出一個(gè)結(jié)果表 (select a from tableA except (select a from tableB) except (select a from tableC)隨機(jī)取出10條數(shù)據(jù) select top 10 * from tablename order by newid()隨機(jī)選擇記錄 sel

14、ect newid()刪除重復(fù)記錄 Delete from tablename where id not in (select max(id) from tablename group by col1,col2,.)列出數(shù)據(jù)庫里所有的表名 select name from sysobjects where type='U'列出表里的所有的 select name from syscolumns where id=object_id('TableName')列示排列 列示type、vender、pcs字段，以type字段排列，case可以方便地實(shí)現(xiàn)多重選擇，類似s

15、elect 中的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 顯示結(jié)果： type vender pcs 電腦 A 1 電腦 A 1 光盤 B 2 光盤 A 2 B 3 C 3初始化表table1 TRUNCATE TABLE

16、table1選擇從10到15的記錄 select top 5 * from (select top 15 * from table order by id asc) table_別名 order by id desc數(shù)據(jù)類型轉(zhuǎn)換 declare numid int declare id varchar(50) set numid=2005 set id=convert(varchar,numid) 通過上述語句完成數(shù)據(jù)類型Int轉(zhuǎn)換成varchar，其他轉(zhuǎn)換類似，可參看convert函數(shù)編輯本段技巧1=1，1=2的使用 在SQL語句組合時(shí)用的較多 “where 1=1 是表示選擇全部 “whe

17、re 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 ' + tbl

18、Name + ' where 1=1 安定 '+ strWhere收縮數(shù)據(jù)庫 -重建索引 DBCC REINDEX DBCC INDEXDEFRAG -收縮數(shù)據(jù)和日志 DBCC SHRINKDB DBCC SHRINKFILE壓縮數(shù)據(jù)庫 dbcc shrinkdatabase(dbname) 轉(zhuǎn)移數(shù)據(jù)庫給新用戶以已存在用戶權(quán)限 exec sp_change_users_login 'update_one','newname','oldname' go檢查備份集 RESTORE VERIFYONLY from disk='E

19、:dvbbs.bak'修復(fù)數(shù)據(jù)庫 Alter DATABASE dvbbs SET SINGLE_USER GO DBCC CHECKDB('dvbbs',repair_allow_data_loss) WITH TABLOCK GO Alter DATABASE dvbbs SET MULTI_USER GO日志去除 SET NOCOUNT ON DECLARE LogicalFileName sysname, MaxMinutes INT, NewSize INT USE tablename - 要操作的數(shù)據(jù)庫名 Select LogicalFileName = &

20、#39;tablename_log', - 日志文件名 MaxMinutes = 10, - Limit on time allowed to wrap log. NewSize = 1 - 你想設(shè)定的日志文件的大小(M) - Setup / initialize DECLARE OriginalSize int Select OriginalSize = size FROM sysfiles Where name = LogicalFileName Select 'Original Size of ' + db_name() + ' LOG is '

21、+ 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 StartTi

22、me = 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 sy

23、sfiles 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 (

24、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更改某個(gè)表 exec sp_changeobjectowner '

25、;tablename','dbo'存儲更改全部表 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, 

26、9;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 e

27、nd - select name,NewOwner,OldOwner FETCH NEXT FROM curObject INTO Name, Owner END close curObject deallocate curObject GOSQL SERVER中直接循環(huán)寫入數(shù)據(jù) declare i int set i=1 while i<30 begin insert into test (userid) values(i) set i=i+1 end一：數(shù)據(jù)庫連接代碼：SqlConnection objSqlConnection = new SqlConnection ("

28、server = ;uid = sa; pwd =;database =test");objSqlConnection.Open();二：數(shù)據(jù)庫的添加記錄代碼：            int i = 0;          string s1 = "", s2 = ""   

29、60;      i = Convert.ToInt16(textBox1.Text);              s1 = textBox2.Text;          s2 = textBox3.Text;       &#

30、160;    SqlConnection objSqlConnection = new SqlConnection("server = ;uid = sa; pwd =;database =test");            objSqlConnection.Open();          &#

31、160;             MessageBox.Show("數(shù)據(jù)庫連接成功", "好");               try             

32、                    SqlCommand sqlcom = new SqlCommand("insert into info(id,name,sex) values( " + i + ",'" + s1 + "','" + s2 + "')", objSqlConnection

33、);                  sqlcom.ExecuteNonQuery();                   MessageBox.Show("添加成功！", "啊"); 

34、                            catch (Exception a)                    

35、60;             MessageBox.Show(a.ToString();                           MessageBox.Show("添加成功！", &qu

36、ot;啊");三：數(shù)據(jù)庫的修改代碼：            int i = 0;            string s1 = "", s2 = ""            s1 = tex

37、tBox2.Text;            s2 = textBox3.Text;            if (textBox1.Text.Length = 0)                i

38、 = 0;            else                i = Convert.ToInt32(textBox1.Text);            SqlConnection ob

39、jSqlConnection = new SqlConnection("server = ;uid = sa; pwd =;database =test");            objSqlConnection.Open();            MessageBox.Show("數(shù)據(jù)庫連接成功"

40、;, "好");            try                            SqlCommand sqlcom = new SqlCommand(&quo

41、t;update info set name='"+s1+"',sex='"+s2+"'"+"where id=" + i, objSqlConnection);                sqlcom.ExecuteNonQuery();        

42、;        MessageBox.Show("修改成功！", "啊");                objSqlConnection.Close();             &#

43、160;          catch (Exception a)                            MessageBox.Show(a.ToString();   &#

44、160;        四：數(shù)據(jù)庫的刪除代碼：            int i = 0;            string s1 = "", s2 = ""      

45、0;     s1 = textBox2.Text;            s2 = textBox3.Text;            if(textBox1.Text.Length=0)          

46、      i=0;            else                 i = Convert.ToInt16(textBox1.Text);        

47、0;   SqlConnection objSqlConnection = new SqlConnection("server = ;uid = sa; pwd =;database =test");                        objSqlConnection.Open(

48、);            MessageBox.Show("數(shù)據(jù)庫連接成功", "好");            try                

49、60;           SqlCommand sqlcom = new SqlCommand("delete from info where id="+i, objSqlConnection);                sqlcom.ExecuteNonQuery();  

50、60;             MessageBox.Show("刪除成功！", "啊");                objSqlConnection.Close();        

51、                catch (Exception a)                            MessageBox.Show(a

52、.ToString();            五：數(shù)據(jù)庫的查詢代碼：            1.類開始：                  DataTable dt1 = new

53、 DataTable();                  SqlDataAdapter da1 = new SqlDataAdapter();            2.按鈕代碼：         

54、0;  int i = 0,n=0;            string s1 = "", s2 = ""            s1 = textBox2.Text;           

55、 s2 = textBox3.Text;           if (textBox1.Text.Length = 0)                i = 0;            else &

56、#160;              i = Convert.ToInt32(textBox1.Text);            SqlConnection objSqlConnection = new SqlConnection("server = ;uid = sa; pwd =;database =te

57、st");            objSqlConnection.Open();            MessageBox.Show("數(shù)據(jù)庫連接成功", "好");           

58、0;string query = "SELECT * from info where id="+i;            DataSet objDataSet = new DataSet();            SqlDataAdapter obj = new SqlDataAdapter();   &

59、#160;                  obj.SelectCommand = new SqlCommand(query, objSqlConnection);            obj.Fill(objDataSet, "info");  

60、0;                 SqlCommand objSqlCommand = new SqlCommand(query, objSqlConnection);            SqlDataReader objSqlReader = objSqlCommand.ExecuteReader(

61、);             while (objSqlReader.Read()                                 &

62、#160;  n += 1;                    MessageBox.Show("編號: " + objSqlReader.Getvalue(0) + "  姓名:" + objSqlReader.Getvalue(1) + "  性別" + objSqlRe

63、ader.Getvalue(2);                                if (n = 0)             

64、60;      MessageBox.Show("數(shù)據(jù)庫中沒有這樣的記錄！");六：數(shù)據(jù)庫的查詢代碼：       int i = 0;           / int n = 0;            string s1 = &quo

65、t;", s2 = ""            string sql;            s1 = textBox2.Text;            s2 = textBox3.Text; &#

66、160;          if (textBox1.Text.Length = 0)                            i = 0;     

67、60;                                  else               

68、; i = Convert.ToInt32(textBox1.Text);           SqlConnection objSqlConnection = new SqlConnection("server = ;uid = sa; pwd =;database =test");              

69、;        objSqlConnection.Open();            MessageBox.Show("數(shù)據(jù)庫連接成功", "好");            string query = "SELECT * f

70、rom info where id="+i;            if(i=0)                  sql = "select * from info "        &

71、#160;   else                  sql = "select * from info where id=" + i;            da1 = new SqlDataAdapter(sql, objSqlConnec

72、tion);            dt1.Clear();            da1.Fill(dt1);            dataGridView1.DataSource = dt1;數(shù)據(jù)庫的封裝類代碼：class DBCla

73、ss            public void dbclass(string  sql)                    try          &

74、#160;                 SqlConnection sqlcon = new SqlConnection("server = ;uid = sa; pwd =;database =test");               

75、; sqlcon.Open();                            SqlTransaction objt = sqlcon.BeginTransaction();  /事物開始                              SqlCommand sqlcom = new SqlCommand(sql, sqlcon);