随笔分类 -  SQL

摘要:1.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。 2.应尽量避免在 where 子句中使用!=或<>操作符,否则将引擎放弃使用索引而进行全表扫描。 3.应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引 阅读全文
posted @ 2019-09-16 20:03 JasonGu0 阅读(257) 评论(0) 推荐(0)
摘要:Oracle Mssql 阅读全文
posted @ 2017-03-28 14:10 JasonGu0 阅读(229) 评论(0) 推荐(0)
摘要:1:Integrated Security参数 当设置Integrated Security为 True 的时候,连接语句前面的 UserID, PWD 是不起作用的,即采用windows身份验证模式。 只有设置为 False 或省略该项的时候,才按照 UserID, PWD 来连接。... 阅读全文
posted @ 2015-05-26 20:23 JasonGu0 阅读(465) 评论(0) 推荐(0)
摘要:SELECT nvarcharsname,intsage, CASE charSsex WHEN 'm' THEN '男' ELSE '女' END AS sex FROM student 阅读全文
posted @ 2014-01-12 15:27 JasonGu0 阅读(820) 评论(0) 推荐(0)
摘要:Create function [dbo].[split](@SourceSql varchar(max),@StrSeprate varchar(10))returns @temp table(line varchar(max))asbegin declare @i int set @SourceSql = rtrim(ltrim(@SourceSql)) set @i = charindex(@StrSeprate,@SourceSql) while @i >= 1 begin if len(left(@SourceSql,@i-1))>0 ... 阅读全文
posted @ 2014-01-03 11:14 JasonGu0 阅读(180) 评论(0) 推荐(0)
摘要:SELECT ROW_NUMBER() OVER (ORDER BY intid ASC) AS rowid,buyerinfo,buyername,address FROM tblbuyermsg 阅读全文
posted @ 2013-11-28 13:58 JasonGu0 阅读(1271) 评论(0) 推荐(0)
摘要:delete from tblBuyerMsg where (buyerinfo) in (select buyerinfo from tblBuyerMsg group by buyerinfo having count(*) > 1)AND buyername in (select buyername from tblBuyerMsg group by buyername having count(*)>1)AnD address in (select address from tblBuyerMsg group by address having count(*)>1) 阅读全文
posted @ 2013-11-27 10:35 JasonGu0 阅读(575) 评论(1) 推荐(0)
摘要:在几千条记录里,存在着些相同的记录,如何能用SQL语句,删除掉重复的呢?1、查找表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断select * from peoplewhere peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)2、删除表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断,只留有rowid最小的记录delete from people where peopleId in (select peopleI... 阅读全文
posted @ 2013-07-31 12:11 JasonGu0 阅读(260) 评论(0) 推荐(0)
摘要:T_sql语句:create proc [dbo].[getdatas]@tid nvarchar(100),@name nvarchar(100) output as beginselect @name=Buyer_info from boby_info where tid=@tidendC# ADO.NET 后台调用 static void hh() { SqlConnection conn = new SqlConnection(); conn.ConnectionString = "server=.;database=Manag... 阅读全文
posted @ 2013-07-20 17:07 JasonGu0 阅读(392) 评论(0) 推荐(0)