asp.net中ADO SQL數(shù)據(jù)庫 筆記匯總 持續(xù)更新中

connectionStrings>
addname="SQLconnectionStr"connectionString="Data Source=.;Initial Catalog=NetShopDB;Integrated Security=True"providerName="System.Data.SqlClient"/>
/connectionStrings>

public static string getConnectionStr()
{
return ConfigurationManager.ConnectionStrings["SQLconnectionStr"].ToString();
}

string conStr= SqlConnection.getConnectionStr();

public Liststudent> getData1(string myid, string myname)
{
//這里用using靈活方便,用完con不需手動(dòng)關(guān)閉,會(huì)自動(dòng)關(guān)閉當(dāng)前連接
using(SqlConnection con=new SqlConnection(conStr) )
{
//打開連接
con.Open();
string cmdStr = "select * from ns_user where userID=@myid and userName=@myname";
SqlCommand cmd = new SqlCommand(cmdStr,con);
//這里用參數(shù)序列化防止注入式攻擊
cmd.Parameters.Add(new SqlParameter("@myid", myid));
cmd.Parameters.Add(new SqlParameter("@myname", myname));
//執(zhí)行查詢，并返回查詢所返回的結(jié)果集中第一行的第一列。忽略其他列或行
//object myResult = cmd.ExecuteScalar();
// 對(duì)連接執(zhí)行 Transact-SQL 語句并返回受影響的行數(shù)。(負(fù)責(zé)執(zhí)行語句, 例如增insert,刪delete,改update)
//int ResultRowCount = cmd.ExecuteNonQuery();
SqlDataReader sdr = cmd.ExecuteReader();
Liststudent> ls = new Liststudent>();
while (sdr.Read())
{
student s = new student();
s.Sid = sdr["sid"].ToString();
s.Sname = sdr["sname"].ToString();
ls.Add(s);
}
return ls;
}
}
Dataset(記得)
#region 獲取教師的詳細(xì)信息
public DataSet GetTeacherInfo()
{
using (SqlConnection conn = new SqlConnection(dbapp))
{
SqlDataAdapter sda = new SqlDataAdapter("select * from table1 ", conn);
DataSet ds = new DataSet(); //定義一個(gè)表的集合
sda.Fill(ds, "teacher");
return ds;
}

}
#endregion

#region
public int UserCheck(string userID,string userName)
{
using(SqlConnection con=new SqlConnection (conStr))
{
con.Open();
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "sp_MYLogin"; //sp_MYLogin是存儲(chǔ)過程名稱
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = con;

//為存儲(chǔ)過程賦值
//賦值方式一(賦第一個(gè)值括號(hào)里面的 "@name"必須和存儲(chǔ)過程里的"@name"一模一樣)
cmd.Parameters.Add(new SqlParameter("@userID", userID));

//賦值方式二(賦第二個(gè)值)
SqlParameter pwd = new SqlParameter("@pwd", SqlDbType.NVarChar, 50);
pwd.Value = userName;
pwd.Direction = ParameterDirection.Input;
cmd.Parameters.Add(pwd);
//定義一個(gè)變量來接受存儲(chǔ)過程的返回值
SqlParameter result = new SqlParameter("@return", SqlDbType.Int);
result.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(result);

cmd.ExecuteNonQuery(); //執(zhí)行存儲(chǔ)過程

//取得SQL變量@result中存放的存儲(chǔ)過程的返回值
int num = Convert.ToInt32(cmd.Parameters["@return"].Value); //Parameters是一個(gè)集合["@return"]就是他的索引
return num;
}
}
#endregion

catch (Exception ex)
{ //錯(cuò)誤日志
error.ErrorWrite("UserInsert", ex.Message, DateTime.Now.ToString());
return null;
}

create database student --創(chuàng)建的數(shù)據(jù)庫名稱
on primary ---指定數(shù)據(jù)文件的各個(gè)參數(shù)
(
name='studnet3_data', --所有的字符串都以' 間隔
filename='E:\lx\student4_data.mdf', --文件名包含路徑和名字.擴(kuò)展名
size=3MB, ----默認(rèn)大小，如果不寫大小，則默認(rèn)是MB
maxsize=100MB, ----最大容量
filegrowth=1MB ---自動(dòng)增長量/擴(kuò)容,如果為，則不自動(dòng)擴(kuò)容
)

log on -----日志文件的各個(gè)參數(shù)
(
name='student5_log',
filename='E:\lx\student4_data.ldf',
size=1MB,
maxsize=10MB,
filegrowth=10% --10%是最大容量的%)
)

sp_helpdb student ---查詢數(shù)據(jù)庫名稱
sp_renamedb student,stu --重命名數(shù)據(jù)庫
drop database student --刪除數(shù)據(jù)庫

drop table person --刪除表
create table person --創(chuàng)建表
(
---注意：下面是屬性(字段名稱)在前，數(shù)據(jù)類型在后
ID int primary key identity(2,1) not null, -- primary key是設(shè)置主鍵，保證該列值唯一且不為空，identity(2,1)起始值是，步長為
Name nvarchar(10) not null, ---not null是指不能為空
Sex bit not null, --bit是bool類型
age int default 18 , -- default 18是指自動(dòng)取默認(rèn)值
scroe decimal(4,1) check(score=100) --4指小數(shù)點(diǎn)前后加起來總共位，代表小數(shù)點(diǎn)后位數(shù) check是檢查限制約束
cardid int unique --unique 指唯一鍵，在表中有多列數(shù)據(jù)需要保證唯一時(shí)除了主鍵以外的列需要設(shè)置為唯一列
)

select * from tableName --查找顯示整個(gè)表(所有列)
select列名1,列名2 from tableName --顯示部分列
select列名1='編號(hào)',列名2='品牌' from Product1 --把其中一列的信息統(tǒng)一修改

--修改顯示時(shí)列標(biāo)題(方法一)
select '編號(hào)'=ProCode,'品牌'=ProTypeName from Product1
--修改顯示時(shí)列標(biāo)題(方法二)
Select 列名1 '編號(hào)',列名2 '品牌' from Product1

--顯示時(shí)對(duì)列進(jìn)行算數(shù)運(yùn)算, 顯示時(shí)添加列
select列名1,列名2,列名3*0.5 '打折后',Number from Product1

select distinct 列名1 from tableName --顯示時(shí)刪除該列的重復(fù)行
select top 3 * from Product1 --顯示前三列
select top 50 percent * from Product1 --顯示總行數(shù)的前%行
--and是并且, or是或者 , 條件非: not 列名='值'
select * from tableName where Number=15 and not age=12 and Name='aa' or sex=1
--查找score范圍為0到100的數(shù)據(jù)
select * from Product1 where score100 and score >=1
select * from Product1 where score between 1 and 100
--in，not in (包含不包含) 以下都是查找number為，，的數(shù)據(jù)
select * from Product1 where Number=10 or Number=15 or Number=20
select * from Product1 where Number in(10,15,20)
select * from Product1 where not Number in(10,15,20)
--1>like模式匹配符%可以代替任意個(gè)數(shù)量的字符2> _可以代替一個(gè)字符 3>[]是一個(gè)查找范圍
select * from Product1 where ProCode like 'D%' --查找首字母為D的ProCode的數(shù)據(jù)
select * from Product1 where ProCode like '_S%' --查找第二個(gè)字符是S的procode數(shù)據(jù)
select * from Product1 where 列名1 like '1%' --即使是float型也要加''
select * from Product1 where 列名1 like '_4_' --查找第二個(gè)字符是且字符個(gè)數(shù)為的3數(shù)據(jù)
select * from Product1 where 列名1 like '_[5-9]%' --查找第二個(gè)字符是5到9的列名1數(shù)據(jù)
--查找為空,不為空的數(shù)據(jù)
select *from Product1 where proValue is not null
select *from Product1 where proValue is null
go --排序( desc降序 asc升序 什么都不寫就默認(rèn)升序 )
select * from Product1 order by proValue desc --將provalue按降序排列
select * from Product1 order by proValue asc --將provalue按升序排列
select * from Product1 order by Number --將Number按默認(rèn)(升序)排列
select * from Product1 order by Number desc,proValue asc --第二個(gè)條件在第一個(gè)條件相同時(shí)才執(zhí)行
go --聚合函數(shù)
select MAX(proValue) from Product1 --查找proValue中的最大值
select min(proValue) from Product1 --查找proValue中的最小值
select sum(proValue) from Product1 --查找proValue中數(shù)據(jù)的和
select avg(proValue) from Product1 --查找proValue中的平均值
select count(*) from Product1 --查找表中的行數(shù)*也可以用列名代替

--group by分組（where在group by 之前，having分組之后的篩選條件，where和having都是篩選條件）
--分組:可以顯示分組列的信息和分組后的信息分別進(jìn)行統(tǒng)計(jì)
select列名1,max(列名2),min(列名3) from tableName where proTypeName='電視機(jī)' group by 列名1
select proTypeName,max(proValue),min(proValue) from Product1 group by proTypeName having count(proValue)>1

--內(nèi)連接inner join 查詢兩表共有的信息
--from查詢列名1.Name=列名2.Name 是關(guān)聯(lián)條件(關(guān)聯(lián)條件中列的內(nèi)容要一致)
select * from tableName inner join Product2 on列名1.Name=列名2.Name
--顯示查詢后的部分列，p1.*意思是顯示p1的所有列
select p1.*,proArea,proBrand from Product1 as p1 inner join Product2 on p1.ProCode=Product2.ProCode
--Product1 as p1 意思是給Product1起了個(gè)別名p1 ，as 可以省略
select * from Product1 as p1 inner join Product2 as p2 on p1.ProCode=p2.ProCode
--where查詢，省略了as 格式:select * from 表,表where 關(guān)聯(lián)條件>
select * from Product1 p1,Product2 p2 where p1.ProCode=p2.ProCode

--外連接 --先顯示兩表關(guān)聯(lián)到的數(shù)據(jù)，再顯示關(guān)聯(lián)不到的數(shù)據(jù)
go --格式:select * from 表left\right\full outer join 表on 關(guān)聯(lián)條件>
select * from Product1 p1 left outer join Product2 p2 on p1.ProCode=p2.ProCode --左外連接
select * from Product1 p1 right outer join Product2 p2 on p1.ProCode=p2.ProCode --右外連接
select * from Product1 p1 full outer join Product2 p2 on p1.ProCode=p2.ProCode --全外連接

--交叉連接(又叫笛卡爾連接:瘋狂連接，n對(duì)n連接,沒有關(guān)聯(lián)條件)
--格式:select * from 表cross join 表
select * from Product1 cross join Product2
--自連接(自查詢:是把一張表分解成兩張表來用)
select c1.*,c2.Name from ST_class c1,ST_class c2 where c1.ID=c2.Department and c1.Name='計(jì)算機(jī)學(xué)院'
--嵌套查詢
--子查詢返回多個(gè)值
select * from product1 where ProCode in(select ProCode from Product2 where proArea='北京')
select * from product1 where ProCode not in(select ProCode from Product2 where proArea='北京')
--子查詢返回一個(gè)值
select* from Product1 where proValue=(select MAX(provalue) from Product1)

--子查詢返回多個(gè)值時(shí)可以用any（返回結(jié)果中的任何一個(gè)【最小的一個(gè)】）all（返回結(jié)果中的所有【最大值】）

--聯(lián)合查詢 union all (用union all連接兩個(gè)單獨(dú)的子查詢)
select SNAME,SSEX,SBIRTHDAY from STUDENT union all select TNAME,TSEX,TBIRTHDAY from TEACHER

--創(chuàng)建(create)/修改(alter)一個(gè)存儲(chǔ)過程
alter proc sp_SMGetCity ---sp_SMGetCity 是存儲(chǔ)過程的名字
(
@code nvarchar(50), --數(shù)據(jù)類型要和比較的字符相同
@name nvarchar(50) output ,
@grade int=1
--'這里要注意:存儲(chǔ)過程賦初值,只能在排在最后的參量中'
---一個(gè)@ 符號(hào)是局部變量
---兩個(gè)@ 符號(hào)是全局變量
)
as
begin
select @name=Name from TBName where Code like @code+'%' and Grade=@grade --begin和end 中間是要執(zhí)行的SQL語句
print @name --帶輸出參數(shù)
end

declare @outname nvarchar(50) -- 定義一個(gè)變量
exec sp_SMGetCity'11',@outname output --@aa把變量賦值給輸出參數(shù)，用來接收返回值
select @outname

sp_help sp_SMGetCity --查看名為sp_studentmanager的存儲(chǔ)過程的創(chuàng)建信息
sp_helptext sp_SMGetCity --查看名為sp_studentmanager的存儲(chǔ)過程的創(chuàng)建代碼
drop proc sp_SMGetCity --刪除存儲(chǔ)過程

--return只能返回整型數(shù)據(jù)
--刪除存儲(chǔ)過程drop proc sp_Name
-- exec(@aa)--執(zhí)行@aa(SQL語句)，所以要加括號(hào),執(zhí)行字符串中的SQL語句

存儲(chǔ)過程調(diào)用存儲(chǔ)過程
as
begin
declare @return int
exec @return=sp_checkUser@id,@name --存儲(chǔ)過程調(diào)用存儲(chǔ)過程
if @return=0
print '沒有重復(fù),return只能返回整型'
else
print '用戶已注冊(cè)'
end

alter view vw_Role
as
declare @num int
declare @title nvarchar(100)
declare @ctitle nvarchar(200)
set @ctitle=''
select @num=count(*)from dbo.Operate
while(@num>0)
begin
select @title=name from (select row_number() over(order by id) 'newid' ,name from dbo.Operate )ta where newid =@num
if(@num>1)
set @ctitle+=@title+','
else
set @ctitle+=@title
set @num=@num-1
end
Declare @sql varchar(8000)
set @sql='select * from(select 1 "isture",rolename,modulename,operatename,role_ID,Module_id from vw_userrole group by rolename,modulename,operatename,role_ID,Module_id)a pivot(count(isture)for operatename in('+@ctitle+')) b'
exec(@sql)

alter proc cutpage
(
@tablename nvarchar(100),----分頁的表
@columnname nvarchar(100), ----分頁的列
@ordertype nvarchar(100)='asc', ----排序方式
@pageindex int =1,
@pagecount int =1
)
as
begin
declare @aa nvarchar(max);
set @aa= 'select * from
(select *,ROW_NUMBER() over(order by '+@columnname+' '+@ordertype+') as uprow from '+@tablename+' ) as newtable
where uprow between '+cast((@pageindex-1)*@pagecount+1 as nvarchar(100))+' and '+convert(nvarchar(100), @pageindex*@pagecount)
exec (@aa) --這里@aa必須加括號(hào)（）

end

exec cutpage 'SM_Class', 'classid'

---事務(wù)關(guān)鍵語句講解----
BEGIN TRANSACTION ---事務(wù)關(guān)鍵字transaction

DECLARE @errorSum INT
SET @errorSum=0 --初始化為，即無錯(cuò)誤

update bank SET money=money+1000 where name='張三'
SET @errorSum=@errorSum+@@error
update bank SET money=money-1000 where name='李四'
SET @errorSum=@errorSum+@@error --累計(jì)是否有錯(cuò)誤(@@error是非零)

if @errorSum>0
begin
print '不成功，有錯(cuò)誤,錯(cuò)誤代號(hào)是：'
print @errorsum
rollback transaction
end
else
begin
print '成功'
select * from Bank
commit TRANSACTION
end

--概念：一種特殊的存儲(chǔ)過程,將本表中的數(shù)據(jù)更改(增刪改)，就會(huì)自動(dòng)執(zhí)行實(shí)現(xiàn)定義的語句
--特點(diǎn)：跨越相關(guān)表的級(jí)聯(lián)修改
--關(guān)鍵字：trigger

alter trigger trigger_name_f
on SM_Class
for update --(for是在增刪改之前執(zhí)行after在增刪改之后執(zhí)行，instead of 是所有【增|刪|改】都不執(zhí)行,之前觸發(fā)，但不改值)
as
begin
if update(remark) ---判斷SM_Class表中remark列是否發(fā)生數(shù)據(jù)變化
begin
select * from inserted ---存放修改的新值的表
select * from deleted ----存放修改的舊值的表
print 'remark列發(fā)生更改'
end
else
print '其他列發(fā)生變化'
print '測(cè)試觸發(fā)器，當(dāng)修改表SM_Class時(shí)，顯示這句話,且for時(shí)這句話在前'
end

--游標(biāo)類似于sql dateReader 一行一行的讀取
--一般是在萬不得已的情況下使用, 時(shí)間長,服務(wù)器壓力大,吃更多的內(nèi)存,寬帶,
--游標(biāo)是對(duì)一個(gè)集合的遍歷讀取

declare cur_test cursor
for
select pid,pname from ns_product

open cur_test
declare @id uniqueidentifier;
declare @name nvarchar(50);
--讀取一行(第一次讀取當(dāng)然是第一行了)
fetch next from cur_test into @id,@name

--判斷一下是否讀到數(shù)據(jù)了,狀態(tài)為零說明讀到數(shù)據(jù)了(@@FETCH_STATUS=0)
while @@FETCH_STATUS=0
begin
print @id
print @name
print '----------------------'
--然后讀取下一行
fetch next from cur_test into @id,@name
end
close cur_test
deallocate cur_test

declare @x int ,@y int
set @x=1
set @y =2
if @x>@y
print 'x>y'
else
print 'xy'


select code,name,grade,
case Grade
when '1' then '省'
when '2' then '市'
when '3' then '縣'
end '等級(jí)'
from SM_PostCode

-------------------------------------------------------------
while (select MAX(DEGREE) from SCORE)85
begin
if (select MAX(DEGREE) from SCORE where CNO='3-105')>=100
break
end