SQL开发实用小教程

浏览:347 发布时间:2019/5/28 10:07:21 返回


作者:保持这种优郁先生


概述

本教程主要收集整理分享与数据库相关的小实例,主要以微个的SQL Server和.NET为实例。以快速上手用于实践为目标,对于更深层的原理,需要查阅更多的相关书籍和文档。

 

记录收集的内容主要是基础常用的内容。上班之余纯手工敲出,这波操作也是对作者自己的提升,在写作过程中也查阅了很多文档,执行了一些小代码段,也说明个人平时经常用到的东西,也不一定能十分肯定就是这样的,还是需要验证,调整,可以有误之出望凉解指正。

 

希望对在学习数据库开发新手有所帮助,对于十分熟悉的老手有提示查阅复习之用。

 

一部分,增删改查,CRUD常用代码

增 Create 

新增,增加,创建,保存,写入,插入,Add,Save,Insert,Create

 

MSSQL:

insert into [tb](col1,col2) values (@p1,@p2)

 

insert into [tb] select @p1,@p2

insert into [tb](col1,col2) select @p1,@p2

 

insert into [tb] select col1,col2 from [tb2] --从另一个表查询数据插入

insert into [tb](col1,col2) select col1,col2 from [tb2]

 

insert into [tb] (col1,col2) values (@p11,@p12),(@p21,@p22),… --插入多行 sql2012以上

 

EF Core:

db.MyModel.Add(m); //m MyModel对象,db为数据对应的entities/context对象

db.Set<MyModel>.Add(m); //同上

db.SaveChanges(); //也提供异步的版本 db.SaveChangesAsync();

 

db.Database.ExecuteSqlCommand(sql,par); //通过执行sql语句,sql:sql语句,par:参数

 

ADO.NET:

cmd.ExecuteNoneQuery(); //用执行sql语句插入或存储过程,cmd里指定sql语句,参数,连接对象,参考SqlHelper


删 Delete


删除,移除,清空,Delete,Del,Remove,Clear

 

MSSQL:

delete from [tb] where id=@id -- 平时操作时记得带上条件哦,不要把整表删除了

delete [tb] where id=@id

 

delete a from [tb] a join [tb2] b on a.xid=b.xid where b.col=@col -- 关联的方式删除数据

delete from [tb] a where a.xid in (select xid from [tb2] b where b.col=@col) --同上,用最简单的写法也可以完成,但是update则不同于这个,后面有update有时候必须安上面种写法才能搞定

 

truncate table [tb] --如果是清空整表,这个更合适

 

drop table [tb] --有时候删除表数据,结构,重新创建也是一种方式

 

EF Core:

db.MyModel.Remove(m);//删除一个

db.Set<MyModel>.Remove(m);

db.Remove(m);

 

db.MyModel.RemoveRange(list);//删除列表

 

db.MyModel.Clear();//全部清除

db.SaveChanges();

 

ADO.NET:

cmd.ExecuteNoneQuery(); //同样执行sql语句即可


改 Update


修改,替换,Update

 

MSSQL:

update [tb] set col1=@p1,col2=@p2 where id=@id

 

update a set a.col1=b.col1 from [tb] a join [tb2] b on a.xid=b.xid where b.col=@col  --用一个表去更新另一个表的数据时,需要这样连接才能完成,如果多表各于复杂可以考虑把tb2cte来代替

 

EF Core:

var m=db.MyModel.Find(id);

m.Col="Col";

db.SaveChanges(); //少数字段

 

db.Entry(m).State=EntityState.Modified; //传入模型更新,需要id对应

db.SaveChages();

 

ADO.NET:

cmd.ExecuteNoneQuery(); //同样执行sql语句即可


查 Selete/Read

查询,查询,搜索,筛选,读取,获取,Select,Search,Read,Get

 

MSSQL:

select col1,col2 from [tb] where id=@id --最基础的查询

 

select * from [tb] where col like ''''+@p+'''%' order by id desc  -- 模糊查询+排序

 

select a.* ,b.col1 from [tb] a join [tb] b on a.xid=b.xid --获取多表数据,联连

 

;with cte as (

select *,rn=row_number() over(order by id desc) from [tb]

)

select * from cte where rn between (@pageIndex-1)*@pageSize+1 and @pageIndex*@pageSize --row_number方式分页

 

select top(@ct) * from [tb] order by col1 desc --查询前@ct行,top方式也可以用于分页

select * from (

select top(@pageSize) * from (select top(@pageIndex*@pageSize) * from [tb] order by id desc)

order by  id asc

) order by id desc --top方式分页

 

EF Core:

db.MyModel.Find(id); --单个

db.MyModel.Single(r=>r.id=id);

db.MyModel.FirstOrDefault(r=>r.id==id);

 

db.MyModel.FromSql(sql); --sql语句

 

db.MyModel.Where(r=>r.id==id).ToList(); --条件

 

ADO.NET:

cmd.ExecuteScalar()

cmd.ExecuteReader()

da.Fill(ds); //dataset




二部分,ADO.NET常用代码

常用的ADO方法有ExecuteNonQuery(),ExecuteScalar(),ExecuteReader(),SqlDataAdapter/DataSet

 

ExecuteNonQuery()用于执行无返因结果的语句,如插入insert,更新update,删除delete都可以,该方法返回影响行数。如果在SSMS查询窗口中执行完SQL语句后窗口下面会返回所影响的行数。返回的是一个int类型

 

ExecuteScalar() 用于执行SQL语句或存储过程返回第一行第一列。这个主要用于执行一个查询取一个值,这个值可以是任意类型。如select count(*) from [tb]  计算一个表的记录数

 

ExecuteReader() 执行SQL语句或存储过程返回一个SqlDataReader对象,可以循环读取里面记录,一般我们查询列表建议都用这个

 

SqlDataAdapter/DataSet这个用于提取一个DataSet集体,里面含有多个Table结果,有时候用这个处理多表,或者动态结果比较方面。比如反回一个Table列名不固定,前端用循环处理即可。

 

实例代码

using(SqlConnection conn=new SqlConnection(connStr)){ //connStr数据库连接字符串

conn.Open();

SqlCommand cmd=new SqlCommand();

cmd.Connection=conn;

cmd.CommandText=sql; //sql语句或者存储过程名称

cmd.CommandType=CommandType.Text;//CommandType.StoredProcedure  执行sql语句还是存储过程

cmd.Parameters.Add(p);//循环加入

 

var ret=cmd.ExecuteNonQuery(); //1.

return ret;

 

//

var ret=cmd.ExecuteScalar(); //2

return ret;

 

//

var dr=cmd.ExecuteReader();

using(dr){

while(dr.Read()){ //注意,读取datareader时连接必须保持开打,所以如果在封装时请注意这点

dr["col"].ToString();//取字段

dr.IsDbNull()?0:dr.GetInt32(1); //按位置取数据判断是否为NULL

}

}

 

//

using(var da=new SqlDataAdapter(cmd)){

var ds=new DataSet();

da.Fill(ds);

 

return ds;

}

}

 

C#中的ADO操作基本上就这几种,可以在网上下载SqlHelper来使用。由于SqlHelper是一个公版所以里面相应的方法较多,比如处理事务,比如处理输出参数等等。可以根据自己的情况封装调整。以上几种方式也只是取数,存数,执行的方式,核心是里面的CommandText,这个可以简单到 select * from tb这种语句,也可以复杂到一个大型的存储过程。

 

针对于SQL语句较强的,可以存储过程,或者直接复杂SQL语句完成复杂业务。如果SQL语句弱一些的可能考虑多次操作完成复杂业务,但是也应该考虑性能方面,比如能尽量筛选的先筛出,不要一次性查到内存里面再慢慢处理,这样不太好。对于多个步骤操作的,关键数据需要考虑事务等。

三部分,数据库开发查询常用代码

开发中经常遇到的数据分页,行列转换等等超出简单的增删改查,当然也是属于查询范畴,这里单独提出来是相对于简单的GetById()是复杂一些。

1.数据分页

用中间表表达式,开窗函数来处理分页

;with cte as(

select *,rn=row_number() over(order by id desc) from tb  --添加一个顺序行rn,主要解决id不连续问题

)

select * from cte where rn between (@pageIndex-1)*@pageSize+1 and @pageIndex*@pageSize --取数据则比较简单了

 

--分页一般需要总记录数,那么在最后用union all 可以添加一行

union all

select col1…sum(colx)…coln,count(*) from cte  -- sum(colx) 对某些字段求和,count()取总记录数,如果是单页的合计建议在页面上绑定时处理即可

 

--如果有些情况下不相添加union all来添加行记录,想直接带出有没有办法?

select *,count(1) over() as ct from tb; --这样也可以带出总记录数,只不过是在你查询结果的每一行都带出来了结果

 

用原始的top 拼接sql语句,或者top()函数 来处理分页

select * from (

select top(@pageSize) * from (

select top(@pageSize*@pageIndex) * from [tb] order by id desc --以倒序取前@pageIndex*@pageSize

) order by Id asc --从刚才的结果中顺取@pageSize这么多条 ,这是我们要的数据了

) order by Id desc --把顺序倒回去,同排序规则一致

 

offset方式进行分页,sqlserver 2012,可以比较简单的方式分页了

select * from [tb] order by Id  --这里很自然

offset (@pageIndex-1)*@pageSize rows fetch next @pageSize rows only --接上这句即可,这里注意offset是偏移/跳过,所以这里不像between and 是两转包含,其实如查用 rn>(@pageIndex-1)*@pageSize and rn<=@pageIndex*@pageSize,同这里是一毛一样的,其中rows可以用row替换,next可以用first替换,offset n row fetch first m row only;

-- 如果有场景需要,不分页也可以用offset,如

select * from tb order by id offset 10 rows; --跳过10行记录剩下的数据

 

2.行列转换

在报表里面可以说组常用到行列转换,我们不可能按表里面的结构把数据给道客户,而是按比较方便客户阅读的方式,有时候虽然比较奇葩,但是也得做。我们遇到一个客户就是需要将每道工序放一个列,整个报表下来动态的,几十个字段,没办法按客户要求来完成吧。

 

比较原始的行列转换

select name

,sum(case when course='语文' then score else 0 end) as [语文]

,sum(case when course='数学' then score else 0 end) as [数学]

,sum(case when course='英语' then score else 0 end) as [英语]

from tb group by name --表结构没有写出来,脑补一下,就name,course,score ,姓名,学科,成绩三个字段

-- 这个是一个最简单的静态例子,只有三个学科,如果学科很多数据都是动态的,那么我们就需要动态的来组织了

set @cols=cast(

select distinct ',sum(case when course='''+course+''' then score else 0 end ) as ['+course+']' from tb for xml path('')

) as nvarchar(max)) -- 这样就组强成了上面的几个sum的代码段,当然这里的sum也可以max 这种聚合函数都可以,因为分组后为该科目的只有一个,所以这里max,sum效果一样,但是要注意了,如果else 0 end这个0变了,或者分数有负数,maxmin就不太实用了。所以还是具体看数据情况来定,总之就是将score0做处理,这里用sum是最好的,这样0的影响是最小的。

 

--case when 这种技术还有很多地方可以应用,比如现在有一个字段A,B,C,D四种字母数据,但是要求以B排第1,后面以字母顺序排。那么我们可以用case when 处理

order by case when col='B' then 1 else col end  --这样可以达到效果,相当于把B改成了1,改成0也可只要是这几个字母以前即可。

 

select name,'语文' as course, [语文]  as score from tb

union all

select name,'数学',[数学] from tb

union all

select name,'英语',[英语] from tb  --将上面的转回来 union all即可

--union union all 的区别,一般我们常用union all,union 是合并去重,如果所有字段一样的则只保留一行,如果我们这里数据标准,也可以用union.

 

使用旋转方式来处理转换 pivot,unpivot

实际是我还是比较推荐上面的写法,因为比较容易理解。而这种写法比较简单一点,但是理解稍微复杂一点。

select name,[语文] as [语文],[数学] as [数学],[英语] as [英语] --这里前面是分类值,后面是显示列名

from tb

pivot ( max(score) for course in ([语文],[数学],[英语]) ) as pvt -- 这里把聚合,分类放在一起了。以course 分,以max(score) 作为聚合数据,当然这里看情况,sum,min都可以

--这个比较坑的一点是,他不像group by 这样多个字段这样灵活。他只分几种字段,一个是分类字段(旋转字段)[course],一个是聚合字段[score],其他的全是分组的字段

 

select name,course,score from tb --这里的tb结构就不是三个字段了,tb(name,语文,数学,英语)

unpivot (score for course in ([语文],[数学],[英语])) as unpvt --看起来也不复杂,其实pivotunpivot就是把以前的写法简化了

 

3.复杂查询小助手CTE

平时我们经常遇到多个表复杂的关系的查询,比如上面的行列转换,但是实例只是针对一个表,实际开发中一般都会配合到有多个表的操作,我们如何将思路理清楚,简化写法,方便阅读。

平时我们复杂的查询会想到,或者用得比较多的是临时表,扩展表(嵌套查询),其中临时表可以简化sql语句,但是会创建新表。扩展表,不用创建新表,但是sql语句就会复杂很多。但是CTE综合了他们两个的优点。

;with cte as( --这里with前面用了一个分号; 一般建议这样用,sql语句里面,如果不是第一句,上一句没有打;,则查询会提示语法错误,所以建议都带上一个分号即可

select * from [tb] a join [tb2] b on a.xid=b.xid

join [tb3] c on b.yid=c.yid --这里面组织一个查询,重点是select 部分出什么结果

),cte2 as (

select * from cte  --这里还可以继续把cte当成一个表,做查询处理

)

select *from cte2 --最后这里出最综结果,当然查询不会这么简单,要不然可以不用这个中间表表达式了

 

4.字符串逗号分割

经常会遇到历史项目中会出现一个字段放id用逗号分割的情况,一般这种设计是不太标准的,但是有时会遇到这种情况。对于这种情况的查找处理。

like,charindex方式

',5,' like '%,'+col+',%' -- 注意这里5前后都用了逗号,存储的字段前后也用来逗号,主要是为了避免 54,53,4 如果这样的数只用5 like 这样的方式会出现问题

charindex(',5,',col)>0 这个同上面like差不多

 

拆分成列值方式

--分割字符串方式

select a.id,substring(a.col,b.number,charindex(',',a.col+',',b.number)-b.number)

from tb a join (

select number from master..spt_values where type='p' --这里只是用一个编号,用临时表也可以

) b on substring(','+a.col,b.number,1)=',' --这条件就可以产生多行,并且把逗号位置后一位定位

 

-- 组织成xml方式分割出来

select a.id,b.col from(

select id,[col]=convert(xml,'<root><v>'+replace([col],',','</v><v>')+'</v></root>') from tb

) a outer apply(

select col=temp.v.value('.','varchar(100)') from a.[col].nodes('/root/v') temp(v)

) b --这样会把逗号分隔的拆开放入列当中

 

--apply在微软的新文档中没有搜索到了,只有2008R2上存在,当然后续版本是支持的,但是新的文档里面没有了,可能是微软不太想推荐 apply,这个确实用得很少。

四部分,常用SQL函数

平时写 SQL查询时,经常会遇到用一些SQL Server提供的函数

 

1,字符串函数

ltrim(col) --left trim 去掉左边空字符

rtrim(col) --去掉右边,有些时候我们都需要一起使用 ltrim(rtrim(col))

 

substring(col,m,n) --取子串 位置从1开始

len(col)  --字符长度

 

left(col,m)

right(col,m) -- 取左右子串,比如right('00000000'+col,8) 8位,把前面补0

 

replace(exp,oldexp,newexp) --exp中用newexp替换oldexp,比如把字符串里空格去掉,replace(col,' ','')

 

charindex(findexp,exp[,m])  --m位置开始 exp中找findexp,返回位置(以1开始)

patindex(patexp,exp) --以模式串来搜

 

upper(exp)

lower(exp) --转大小写

 

str(float[,m,n]) -- 这个函数我个人用得很少,只是偶尔看到有些人写的转换字符串用这个,查询了一下。一般用convert,cast 应该都能解决,看微软的文档,应该这个函数主要针对float类型的数字转换。注意:str()返回的是char类型,所以这个一般用于float比较精确的输出char这个时,我建还是尽量少用。特别是拼接字符串,我就见过好多 'abc'+str(@a)+'xyz',这样结果可能是对的,但是中间会有很多空白字符,不太干净。用cast()也可以搞定

 

还有很多字符串函数,可以查阅微软的官方文档,具体用法可以参考微软的文档。只是常用的我们应该要用得比较熟。

 

2,数学函数

round(exp,m) --取整

 

ceiling(exp) --天花板

floor(exp) --地板 向上,向下取整

 

rand([seed]) --随机函数

 

sign(exp) --符号

 

abs(exp) -- 绝对值

 

3,日期时间

getdate() --获取当前时间

year() month(),day() --获取年月日部分

 

datediff(pt,starttime,endtime) --时间差 datediff(dd,'2019-05-08',getdate()) 58号与今天相关多少天

dateadd(pt,inc,starttime) --时间增量(可以减),dateadd(dd,-1,getdate()) 昨天这个时间

 

datepart(pt,starttime) --获取日期的部分,如 年,月,日,周等等 datepart(wk,getdate()) 第几周

 

eomonth(starttime[,addmonth]) --end of month ,获取这个日期的月底一天,最后一天,这个函数用来解决经常遇到的取月初,月末的情况,eomonth(getdate()) 最后一天,上个月最后一天 eomonth(getdate(),-1),当然取第一天也比较容易了dateadd(dd,1,eomoth(getdate(),-1))这样就可以了,sql2012以后可以用,以前的方式还是比较复杂的。dateadd(dd,-1,dateadd(mm,datediff(mm,0,getdate())+1,0)) 这个结果为emonth(getdate())

 

4,聚合函数

sum() --求和,可能会有NULL存在,有时需要用ISNULL(sum(col),0)

count() --计数,最小0,不会存在NULL

 

max(),min() --最大最小

avg() --平均

 

5,其他常用

cast(exp as datatype) --这个经常转换用到 并接 sql,输出其类型 cast(col as nvarchar(10)) cast(@exp as nvarchar(max))

convert(datatype,exp[,style]) --针对复杂一点的转换可能cast搞定不,不仅仅是简单的两种类型转换,比如时间,常用的时间转换格式,convert(varchar(max),getdate(),112) yyyymmdd;convert(varchar(max),getdate(),120)  yyyy-mm-dd hh:mi:ss 常用的有23112120

 

isnull(col,exp) --如果是NULL,则返回exp,否则还是返回col

 

iif(col,truevalue,falsevalue) --相当于三元表达式 iif(a>b,a,b) 输出大的,如果针对多个项还可以用choose()函数,可以简化以前的case xx when then的方式,如选择第3 choose(3,col1,col2,col3) 这里如是3是变量就可以简化case when方式

五部分,使用频率低的实用代码

平时用得较少而且实用的代码一般是数据库巡检,遇到问题调优等地方用到的。

 

代码结构跟踪

getdate()  --当前时间

db_id() --当前数据id,或者db_id(dbname)可以用于跟踪

object_id() --对象id,表,列,数据库等

object_name() --对象名称,与上面object_id相反的效果

 

--查询包含某个字段名称的所有表

select object_name(object_id),* from sys.all_columns where name like '%userId%'

 

 

性能调优

 

@@version --数据库版本

@@servername,@@servicename --服务器,实例名称

 

sp_helpdb --数据库信息的存储过程

sp_spaceuse --空间使用

 

dbcc dropcleanbuffers

dbcc freeproccache --在测试执行时间比较时会用到,清理缓存

 

set statistics io on  /off

set statistics time on /off --查看io读取次数和执行时间


结束语


这段时间不太忙,一是为了丰富个人网站内容,一是为了自己复习总结之用,在整理过程中参考过自己以前的笔记,微软官方文档,网络资源,实际操作验证。还有很大一部分是徒手敲出来的,难免会有错误的地方,本文不适合复制,粘贴就用上。而是一个提示,概括的作用。

本文后续可能会继续更新修改,也可能把新的知识在更到后来的文章或博客中。希望大家共同进步。


广告

昵称*:
邮箱:
评论*: