随笔分类 - SQL
摘要:1.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。 2.应尽量避免在 where 子句中使用!=或<>操作符,否则将引擎放弃使用索引而进行全表扫描。 3.应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引
阅读全文
摘要:Oracle Mssql
阅读全文
摘要:1:Integrated Security参数 当设置Integrated Security为 True 的时候,连接语句前面的 UserID, PWD 是不起作用的,即采用windows身份验证模式。 只有设置为 False 或省略该项的时候,才按照 UserID, PWD 来连接。...
阅读全文
摘要:SELECT nvarcharsname,intsage, CASE charSsex WHEN 'm' THEN '男' ELSE '女' END AS sex FROM student
阅读全文
摘要: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 ...
阅读全文
摘要:SELECT ROW_NUMBER() OVER (ORDER BY intid ASC) AS rowid,buyerinfo,buyername,address FROM tblbuyermsg
阅读全文
摘要: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)
阅读全文
摘要:在几千条记录里,存在着些相同的记录,如何能用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...
阅读全文
摘要: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...
阅读全文