辟邪剑谱之SQL
1、expre.AppendFormat(" and DateDiff(year,[DateTime],'{0}')=0", year);
2、update tablename set IsDel=1 where ID in('1','3','5');
3、子查询:
(1) select * from (select top 10 * from dbname order by date_field desc) order date_field
(2) select pub_name from publishers where pub_id in (select pub_id from titles where type='business')
(3) select au_lname,au_fname,city from authors where city in (select city from authors where au_fname='Livia'or au_lname='Karsen')
(4) select pub_name from publishers where pub_id not in(select pub_id from titles where type='business')
(5) update titles set priceprice=price*2 where pub_id in (select pub_id from publishers where pub_name='new moon books')
4、联接表示 Microsoft? SQL Server? 2000 应如何使用一个表中的数据来选择另一个表中的行。
5、string.Format("SELECT COUNT(ID) FROM {0} WHERE FamilyId={1} AND IsDel<>1", leaveWordTN, familyId);
6、学习http://www.blogjava.net/Ben/
7、declare 定义变量 @变量名 变量类型
8、set 设置变量的值
9、select 有时候不显示数据
declare @objid int,@objname char(40)
set @objname='users'
select @objid=id from sysobjects where id=object_id(@objname)
10、select 有时候显示数据
declare @objid int,@objname char(40)
set @objname = 'users'
select @objid = id from sysobjects where id = object_id(@objname)
select 'Column_name' = name from syscolumns where id = @objid order by colid
11、sql = "select top " + counts + " [id],[name] " + "FROM [life_unite_type] where type=1 and isdeleted<>1 order by id desc ";
12、sql = "select top " + number + " [id],substring([name],1,22)as name " + "FROM [life_unite_active] where flag<>1 order by id desc ";
13、"select count(*) as num from life_unite_product where isShow <> 4 and id not in (select unite_productid from life_unite_active_product where unite_activeid=" + this.lbl_belongto.Text + " ) "
14、SELECT A.*,B.rolename,b.rules FROM life_manager A,life_role B
WHERE A.roleid=B.id AND A.userName=@UserName AND A.password=@Password
15、SELECT DISTINCT top 20 sort_name,sort_value,sum(sort_count) as sort_count from bbs_sort WHERE (sort_name = '今日发帖榜') group by sort_value,sort_name order by sort_count desc
16、select 公司名称,在库存吨位=(select sum(库存吨位) from 仓库 where 库存情况='在库' and 公司名称=t.公司名称),出库存吨位=(select sum(库存吨位) from 仓库 where 库存情况='出库' and 公司名称=t.公司名称) from 仓库 t group by 公司名称
17、select productid,productCount=(select sum(productCount) from life_unite_order_product where productid=t.productid )from life_unite_order_product as t group by productid order by productid desc
18、SELECT DISTINCT top 20 sort_name,sort_value,sum(sort_count) as sort_count from bbs_sort WHERE (sort_name = '今日发帖榜') group by sort_value,sort_name order by sort_count desc
19、SELECT DISTINCT name,sum(id) as idsum,commid from Table_1 as T WHERE (name = T.name and commid=T.commid) group by name,commid order by idsum desc
20、多表相关取数据 Select life_unite_order_product.*,projname
From life_unite_order,life_unite_order_product
Where life_unite_orderlife_unite_order_product.orderid=life_unite_order.id
21、多表相关取数据 Select A.id,B.name,C.projname
From life_unite_order AS C,life_unite_product AS B,life_unite_order_product AS A
Where A.productid=B.id and A.orderid=C.id
22、Select count (distinct 分类名) as 分类种数
23、
商品表1
商品代号 分类名 单价 数量
DBX-134 电冰箱 1456 8
DSJ-120 电视机 1865 15
DSJ-180 电视机 2073 10
DSJ-340 电视机 3726 5
KTQ-12 空调器 2800 12
WBL-6 微波炉 640 10
XYJ-13 洗衣机 468 20
XYJ-20 洗衣机 873 12
Select distinct 分类名 from 商品表1
Select 商品代号,单价*数量 as 价值
From 商品表1
Select count (distinct 分类名) as 分类种数 From 商品表1
Select max (数量）as 最大数量，min (数量）as 最小数量，avg (数量）as 平均数量， sum (数量）as 总和
From 商品表1 结果：
最大数量 最小数量 平均数量 总和
20 5 11 92
Select count(*) as 种数，max (单价）as 最高价，min (单价）as 最低价，avg (单价）as 平均价
From 商品表1
结果：
种数 最高价 最低价 平均价
3 3726.0 1865.0 2554.6666666665
Select max (单价*数量）as 最高价值，min (单价*数量）as 最低价值，sum (单价*数量）as 总价值
From 商品表1
24、SQL中CONVERT转化函数的用法
SELECT CONVERT(varchar(10), DateTime,120) AS DateTime FROM
25、"select sign,projName from bbs_proj where sign in(select sign from bbs_addressbook where username=" + "'" + userclient.UserName + "')"
26、sql语句;select @@identity;"; 太好了执行完sql(insert)后 返回此条ID
27、TOP n PERCENT表示返回的前面的n%行
28、Like 'AB0[1-5] '
29、select @@version 版本
30、变量 declare @a int
set @a=1
set @a=(select count(*) from address)
print @a
31、select * from address where id between 3 and 12
32、查看sql语句的执行时间 在sql语句前加:"set statistics time on" 然后在消息里查看执行时间
33、sp_fulltext_database全文索引
34、
下面的示例查找所有书的平均价格，用值 .00 替换 titles 表的 price 列中的所有 NULL 条目。如price不为空 就返回price
SELECT AVG(ISNULL(price, .00)) FROM titles
update table set hits=isnull(hits,0)+1 where userid='123'
35、select distinct 排重
36、如何撤销udate误操作
BEGIN TRANSACTION --启动一个事务
update ShopInfo set telphone2='5678'
select * from ShopInfo --查看结果
--ROLLBACK TRANSACTION 单独运行恢复数据
37、修改字段 alter table member alter column member_memo [ntext] COLLATE Chinese_PRC_CI_AS NOT NULL
ALTER TABLE [dbo].[member] add CONSTRAINT [DF_member_member_memo] default '' for [member_memo]
alter table tempbaseinfo alter column HGainMeg varchar(600)
38、添加字段 ALTER TABLE dbo.member add member_websname [nvarchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL CONSTRAINT [DF_member_member_websname] DEFAULT ('')
alter table A add TEL_number char(10)
alter table NewHouseNewCodeCount_2016_01 add DianPingCount int
加说明--execute sp_addextendedproperty 'MS_Description', '点评', 'user', 'dbo', 'table', 'NewHouseNewCodeCount_2016_01', 'column', 'DianPingCount'
允许为空指定默认值--ALTER TABLE NewHouseNewCodeCount_2016_02 ADD DianPingCount int NULL DEFAULT 0
execute sp_addextendedproperty 'MS_Description', '点评', 'user', 'dbo', 'table', 'NewHouseNewCodeCount_2016_02', 'column', 'DianPingCount'
不允许为空指定默认值--ALTER TABLE NewHouseNewCodeCount_2016_03 ADD DianPingCount int NOT NULL DEFAULT 0
execute sp_addextendedproperty 'MS_Description', '点评', 'user', 'dbo', 'table', 'NewHouseNewCodeCount_2016_03', 'column', 'DianPingCount'
39、定义存储过程：ALTER procedure [dbo].[ProcRegtempUserByPhone]
执行存储过程：EXECUTE demo_proc DEFAULT,7
exec insert_book '003','Delphi 控件开发指南',$100,@total_price
删除存储过程 drop proc GetEmployeeCount
比如：创建表student，表中有字段stuId，数据类型int,代表“学生学号”，字段stuName，类型为char(20),代表“学生姓名”。
EXEC sp_addextendedproperty N'MS_Description', N'学生学号', N'user', N'dbo', N'table', N'student', N'column', N'stuid'
40、SQL复制表
select * into 目的数据库名.dbo.目的表名 from 原表名
41、复制表结构
如果新表不想要数据，可以这样写：select * into 表2 from 表1 where 1=2
42、exec(@Sql)是好东西
set @TempStr='select top '+cast(@i as varchar(4000))+'* from mt_10'
exec(@TempStr)
declare @i int
declare @TempStr nvarchar(4000)
declare @Trc varchar(1000)
declare @moTable varchar(20)
select @moTable = 'MT_10'
set @i=3
set @TempStr='select top 1 @Trc=userid from mt_10 where id in ('+ 'select top '+cast(@i as varchar(4000))+'id from mt_10 order by id)'+'order by id desc'
exec sp_executesql @TempStr,N'@Trc varchar(1000) out' ,@Trc out
print @Trc
43、getDate()获取当前时间
44、更新消费券
update ShoppingCouponInfo set AvailMount=Amount-(select count(id)
from ShoppingCouponPrintlog
where ShoppingCouponPrintlog.projid=ShoppingCouponInfo.id)
45、将表aaa的数据插入到表aaa_1的对应字段
insert aaa_1 (name,contact,intro) select name,contact,intro from aaa
DBCC CHECKIDENT（TABLE, [RESEED|NORESEED], [1]) ID 重置为1
46、初始化表table1
TRUNCATE TABLE PfczListUrl
47、临时表的创建
select MAX(id) as idz, cid INTO #afc from PlatinumtempUserIntention where UserState=0 or UserState=1 group by cid
DROP TABLE TABLENAME
48、多字段 排重
select max(RegTime1) as regtime2, NewCode1,CID1 from #phoned group by NewCode1,CID1
49、内链接
---白金卡决策阶段用户状态已改变，需转换为咨询阶段 ----2(1,InputUserTime)
select a.CID INTO #PHandle from InputUserTime as a inner join #PlatinumNotJueCe as b
on a.CID=b.cid WHERE Source=5 AND A.CID IN (select id from #JueCeUser)
inner join 只显示重叠的部分 a.CID=b.cid
select a.CID,b.CID as CIDB,a.UID,b.id as UIDB,b.Phone,a.City,a.NewCode,a.NewCode,a.ProjName,a.RegTime from groupPhoneUserProjName as a inner join groupPhoneUser as b
on a.UID=b.id where a.CID=0 and a.City!='' and b.PhoneType=0 and b.CID=0 and b.CreatTime>='2010-09-17 09:51:00.940' and b.CreatTime<='2011-03-16 02:35:34.850' order by RegTime asc
以groupPhoneUserProjName为主9047条记录
(1).内连接：利用内连接可获取两表的公共部分的记录，即图3的记录集C 语句如下：Select * from A JOIN B ON A.Aid=B.Bnameid 运行结果如下图4所示：其实select * from A,B where A.Aid=B.Bnameid与Select * from A JOIN B ON A.Aid=B.Bnameid的运行结果是一样的。
(2)左连接数据
说明：在语句中，A在B的左边，并且是Left Join，所以其运算方式为：A左连接B的记录=图3公共部分记录集C＋表A记录集A1
(3)右连接（Right JOIN）：即图3公共部分记录集C＋表B记录集B1。
语句如下：select * from A Right JOIN B ON A.Aid=B.Bnameid
聚簇索引能提高多行检索的速度（开始时间 结束时间查询快），而非聚簇索引对于单行的检索很快(id 查询 快)。
每个表只能有一个聚集索引
非聚集索引最适于根据特定的键值，从大型 SQL Server 表中提取少数几个具有良好选择性的行。
50、简单理解非聚集索引
非聚集索引会创建一个类似于表的东西，如只是单列的索引，第一列则放置索引列，第二列就是实际数值的指针
查询时，通过这个类似于表的东西查询，实际来说也就类似于聚集索引的查询方式了，只是多了一个指针定位步骤
如是联合索引的话，前面数列就放置所有的可能集合【笛卡尔积】，后面放置指针
51、
显示有关由Transact-SQL 语句生成的磁盘活动量的信息
SET STATISTICS IO ON
--关闭有关由Transact-SQL 语句生成的磁盘活动量的信息
SET STATISTICS IO OFF
--显示[返回有关语句执行情况的详细信息，并估计语句对资源的需求]
SET SHOWPLAN_ALL ON
--关闭[返回有关语句执行情况的详细信息，并估计语句对资源的需求]
SET SHOWPLAN_ALL OFF
记录行号（按某排序）
select ROW_NUMBER() over(order by ID) as rowindex,ID from ActivityPayrecorde where ACID=65
52、sql进行MD5加密
select hashbytes('md5','123456')
SUBSTRING(sys.fn_sqlvarbasetostr(HASHBYTES('MD5', CAST(b.Phone AS VARCHAR(50)))), 3, 32) AS Phone
53、如何将txt文件导入到数据库
Bulk insert #temp from 'e:\test.txt' with (FieldterMinator =',', RowterMinator= '\n')
54.返回有关语句预计所需资源的详细信息
例子:
GO
SET SHOWPLAN_ALL ON
GO
select top 100 * from tempContrlPhoneBatchSend order by id desc
GO
SET SHOWPLAN_ALL OFF
GO
EstimateIO 该运算符的预计 I/O 成本。仅限于 PLAN_ROWS 类型的行。
EstimateCPU 该运算符的预计 CPU 成本。仅限于 PLAN_ROWS 类型的行。
55、查询sql语句执行时间
实例1：
SET STATISTICS PROFILE ON：显示分析、编译和执行查询所需的时间（以毫秒为单位）。
SET STATISTICS IO ON：报告与语句内引用的每个表的扫描数、逻辑读取数（在高速缓存中访问的页数）和物理读取数（访问磁盘的次数）有关的信息。
SET STATISTICS TIME ON：显示每个查询执行后的结果集，代表查询执行的配置文件。
SET STATISTICS PROFILE ON
SET STATISTICS IO ON
SET STATISTICS TIME ON
GO /*--你的SQL脚本开始*/
SELECT [TestCase] FROM [TestCaseSelect]
GO /*--你的SQL脚本结束*/
SET STATISTICS PROFILE OFF
SET STATISTICS IO OFF
SET STATISTICS TIME OFF
实例2：
declare @d datetime
set @d=getdate()
/*你的SQL脚本开始*/
SELECT [TestCase] FROM [TestCaseSelect]
/*你的SQL脚本结束*/
select [语句执行花费时间(毫秒)]=datediff(ms,@d,getdate())
56 导出source转换汉字
select top 100 Cid, RealName, Phone, PlatinumtempCode, City, PurPose, Aprice, PurPoseCountry, PurPoseCity,
CASE
WHEN Source=1 THEN '400来电会员'
WHEN Source=2 THEN '楼盘详情页报名'
WHEN Source=3 THEN '会员报名管理'
WHEN Source=4 THEN '电子商务旗舰店'
WHEN Source=5 THEN '注册页面主动申请'
WHEN Source=6 THEN '用户勾选海外置业同步'
WHEN Source=7 THEN '其他'
WHEN Source=8 THEN '后台 置业会员添加'
WHEN Source=9 THEN '海外二手房留言'
WHEN Source=10 THEN '国际会员后台添加'
WHEN Source=11 THEN '国际站注册'
WHEN Source=12 THEN '置业心愿单'
WHEN Source=13 THEN '首页委托'
WHEN Source=14 THEN 'IM报名'
WHEN Source=15 THEN '商广项目'
WHEN Source=16 THEN '自售项目'
WHEN Source=17 THEN '渠道活动'
WHEN Source=18 THEN '线下活动'
WHEN Source=19 THEN '团'
WHEN Source=20 THEN '营销产品'
WHEN Source=21 THEN '内容组推广'
WHEN Source=22 THEN '微信报名'
ELSE '其他'
END as IsOver
from WorldtempUser where CreateTime>'2016-07-23' and CreateTime<'2016-07-26' order by id desc
57、连表创建视图
SELECT a.*,
(SELECT count(*)
FROM tempUserSystemCallMissionBatchDistributeRecord_0 WITH (nolock)
WHERE mid = a.mid AND mbid = a.mbid AND Editor = a.Editor AND CallTime IS NOT NULL) AS CallCount
FROM tempUserSystemCallMissionBatchDistribute AS a WITH (nolock)
WHERE RIGHT(CONVERT(nvarchar(20), mid), 1) = '0'
UNION ALL
SELECT a.*,
(SELECT count(*)
FROM tempUserSystemCallMissionBatchDistributeRecord_1 WITH (nolock)
WHERE mid = a.mid AND mbid = a.mbid AND Editor = a.Editor AND CallTime IS NOT NULL) AS CallCount
FROM tempUserSystemCallMissionBatchDistribute AS a WITH (nolock)
WHERE RIGHT(CONVERT(nvarchar(20), mid), 1) = '1'
58、导出某个表的的 字段名 字段类型 字段长度 字段说明
select distinct sc.colorder,sc.name as 字段名,st.name as '字段类型',sc.length as 字段长度,g.[value] AS 字段说明 from syscolumns sc
LEFT OUTER JOIN sys.extended_properties g
on sc.id=g.major_id AND sc.colid = g.minor_id
LEFT OUTER JOIN systypes st
on sc.xtype=st.xtype
where
sc.id in(
select id from sysobjects where xtype='U' and name='tempInfo_0')
and st.name!='sysname'
order by sc.colorder
59、日期转换
只显示年月,不显示日:select datepart(year,getdate())*100+datepart(month,getdate())
Select CONVERT(varchar(100), GETDATE(), 23): 2006-05-16
select datepart(year,getdate()),'_',datepart(month,getdate())
60、实用with动态表
WITH t_LookHouse AS
(
select min(id) as id,orderid from FangALLOrder_lookhouse with (nolock) where orderid>136 group by orderid
)
SELECT top 1000 * FROM FangALLOrder_lookhouse a WITH(NOLOCK)
INNER JOIN t_LookHouse b WITH(NOLOCK) ON a.id=b.Id WHERE a.orderid>136
61、添加主键SQL语句
ALTER TABLE Skills ADD PRIMARY KEY (SkillID);
62、C#读取数据库时间字段 毫秒信息
SELECT TOP 1000 CONVERT(varchar(40) , CreateTime, 21 ),* FROM FangCollectionlog WITH(NOLOCK) WHERE type=0 and CreateTime>'2016-12-23 10:52:07.927' order by CreateTime asc
63、验证手机并发 2011-12-30 start
SET @TempStr = 'INSERT INTO OnlyPhone(Phone) VALUES (''' + @Phone + ''')';
EXEC @TempStr;
SET @TempRC = @@ROWCOUNT;
if @TempRC is null or @TempRC='' or cast(@TempRC as int) <1
begin
set @IsValid = 0;
set @RState = 5;--表示该手机号已存在
end
--验证手机并发 2011-12-30 end
64、
select nolock 运行出现脏读 都可以使用nolock来防止并发锁定------此类项目应用最好是非谨慎项目
脏读解决办法 修改时加排他锁 读取时加共享锁 （这样事务1读取数据过程中，其他事务就不会修改该数据；事务1之后有更新操作，那么会转换为排他锁，其他事务更无权参读和写；当事务1读取数据过程中，有可能其他事务也读取了该数据，读取完毕后共享锁释放）
65、左连接
select top 1000 a.*,a.Editor,b.UserName,b.city from tempContrlPhoneBatchSendCmd as a left outer join temp_USER_BackLimit as b on a.Editor=b.UserName where a.SendTime>'2011-12-23' and a.SendTime<'2012-01-23' order by SendTime asc
update OrientationEmailBatchSendCmd set CmdExpre=REPLACE(CmdExpre,'tempbaseinfo where','where') where id=34
left where
select *, cast(left(reserved,len(reserved)-2) as int)/1024 as a from temp_tablespaceinfo where left(nameinfo,len(nameinfo)-3)='tempMessagesSended' order by cast(left(reserved,len(reserved)-2) as int) desc
66、手机截字整理
select top 100 id, LEFT(phone,LEN(phone)-2) as ab from View_tempInfoAll where city='上海' and regtime>'2012-03-08' and regtime<'2012-03-09' and phone!='' and LEFT(phone,LEN(phone)-2)='138185350'
67、admin赋予存储过程 给写权限
grant exec on ProcUpdatetempInfo to group_temp_w
grant exec on ProcGettempBaseInfo to group_temp_w
68、group by 分组统计 count大于1的记录
select count(id) as Num,phone
from tempInfo_0
where Phone is not null or Phone !=''
group by phone having
count(id)>1
69、时间差 后参数-前参数
SELECT datediff( day, '2010-01-01 00:00', '2010-01-03 23:59')结果为2
70、数据库当前时间减去5秒
select DateAdd("s", -5, getdate())
71、
substring(expression,start,length)
expression:要截取的字符串
start：为1代表从第一个字符开始(包括第一个字符 这里没有0的概念)
length:要截取多长
72、
declare @CDetaiTb varchar(20)
declare @tempID varchar(20)
set @CDetaiTb='tempMARKACTLOG_';
set @tempID='512348';
select len(@tempID);
select @CDetaiTb+substring(@tempID,len(@tempID)-1,2);
结果:tempMARKACTLOG_48
虚拟表的创建于插入
CREATE TABLE #tblTmp
(
[Phone] [varchar](20)
)
Insert into #tblTmp (phone) values
throw new Exception代表异常可以
catch (Exception ex)
{
string message ="获取北方接口经济公司请求接盘记录信息报错url:" + url + "错误:" + ex.ToString();
throw new Exception(message);
}
73、使用符合条件的记录集做为一个表 进行二次查询
select * from (select top 100 * from Agents where AgentsCity='北京')c
select * from (select top 100 * from Agents where AgentsCity='北京')c where c.ID=9
74、两表并表 查询
select count(*) from
(SELECT * FROM dbo.tempBaseInfo_new_2013q where tempstate=0 UNION ALL
SELECT * FROM dbo.tempBaseInfo_new_2013h where tempstate=0) as tabname
75、如何查找作业
对象资源管理器中---SQL Server 代理
exec dbo.tempUserStageRollBack
76、WHERE 条件应该写到内查询中。先过滤，再链接，这样链接时数据量就小很多。如果先链接，则是两个表全数据匹配，然后再过滤。
select count(*) from mallrecorde_change where (a.FeeConfimTime>='2015/1/29 0:00:00' and a.FeeConfimTime<'2015/1/30 0:00:00' ) as a with(nolock) left join mallProjInfo (b.isDeleted=0 and b.activityType=0 ) as b with(nolock) on a.aid=b.id
77、按日分组统计
SELECT CONVERT(VARCHAR(10),RegTime,120),COUNT(*)
FROM View_tempInfoNewHouse with (nolock) where Source!=53 and RegTime>'2015-03-01' and RegTime<'2015-04-08'
GROUP BY CONVERT(VARCHAR(10),RegTime,120) order by CONVERT(VARCHAR(10),RegTime,120) asc
78、快速导入txt到sql server
Bulk insert #temp from 'e:\test.txt' with (FieldterMinator =',', RowterMinator= '\n')
79、sql 休息 休眠5秒
declare @rv rowversion
select @rv=b from tmp where a='abc'
waitfor delay '00:00:05' --休息5秒
update tmp set a='xyz' where b=@rv
go
80、
说明：随机取出10条数据
select top 10 * from tablename order by newid()
说明：随机选择记录
select newid()
说明：删除重复记录
Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...)
说明：列出数据库里所有的表名
select name from sysobjects where type='U'
说明：列出表里的所有的字段
select name from syscolumns where id=object_id('TableName')
81、变量赋值
DECLARE @TableName varchar(100);--城市拆表名
select @TableName=TbName from UserBehavior_TbName where City=@City
DECLARE @count_Behavior INT ;--用户行为
select @count_Behavior=COUNT(*) from UserBehaviorType
DECLARE @CmdExpre varchar(5000);--语句表达式
set @CmdExpre='Insert into #tblTmp select cid,BehaviorID,CreateTime
from '+@TableName;
exec (@CmdExpre)
82、游标
使用游标的顺序： 声名游标、打开游标、读取数据、关闭游标、删除游标。
1.3.1声明游标
最简单游标声明：DECLARE <游标名>CURSOR FOR<SELECT语句>；
其中select语句可以是简单查询，也可以是复杂的接连查询和嵌套查询
例子：[已表2 AddSalary为例子]
Declare mycursor cursor for select * from AddSalary
这样我就对表AddSalary申明了一个游标mycursor
实例训练游标
USE [grouptemp_test]
GO
/****** Object: StoredProcedure [dbo].[Test_Temp_Pk] Script Date: 2015/7/8 18:07:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Test_Temp_Pk]
AS
--声明6个变量t
declare @O_id nvarchar(30)
declare @mid nvarchar(30)
declare @mbid nvarchar(30)
declare @Editor nvarchar(40)
declare @CallCount nvarchar(30)
declare @tabname nvarchar(100)
--声明一个游标mycursor，select语句中参数的个数必须要和从游标取出的变量名相同
declare mycursor cursor for select top 50 id as O_id,mid,mbid,Editor from tempUserSystemCallMissionBatchDistribute with (nolock)
--打开游标
open mycursor
--从游标里取出数据赋值到我们刚才声明的2个变量中
fetch next from mycursor into @O_id,@mid,@mbid,@Editor
--判断游标的状态
--0 fetch语句成功
---1 fetch语句失败或此行不在结果集中
---2被提取的行不存在
while (@@fetch_status=0)
begin
--显示出我们每次用游标取出的值
--print '游标成功取出一条数据'
--DECLARE @count_Behavior INT ;--用户行为
--select @count_Behavior=COUNT(*) from UserBehaviorType
--DECLARE @CmdExpre varchar(5000);--语句表达式
--set @CmdExpre='Insert into #tblTmp select cid,BehaviorID,CreateTime from '+@TableName;
--exec (@CmdExpre)
set @tabname='tempUserSystemCallMissionBatchDistributeRecord_'+RIGHT(CONVERT(nvarchar(20), @mid), 1)
--select @CallCount=count(*) FROM tempUserSystemCallMissionBatchDistributeRecord_0 WHERE mid = @mid AND mbid = @mbid AND Editor = @Editor AND CallTime IS NOT NULL
DECLARE @CmdExpre nvarchar(4000);--语句表达式
set @CallCount=''
set @CmdExpre='select @Trc=count(*) FROM '+@tabname+' with (nolock) WHERE mid ='+ @mid+' AND mbid = '+@mbid +' AND Editor = '''+@Editor +''' AND CallTime IS NOT NULL'
print @CmdExpre
--exec (@CmdExpre)
execute sp_executesql @CmdExpre,N'@Trc varchar(20) output',@CallCount output
print '@O_ID:'+@O_id+'@mid:'+@mid+'@mbid:'+@mbid+'@Editor:'+@Editor+'@tabname:'+@tabname+'@CallCount:'+@CallCount
--用游标去取下一条记录
fetch next from mycursor into @O_id,@mid,@mbid,@Editor
end
--关闭游标
close mycursor
--撤销游标
deallocate mycursor
1、expre.AppendFormat(" and DateDiff(year,[DateTime],'{0}')=0", year);
2、update tablename set IsDel=1 where ID in('1','3','5');
3、子查询:
(1) select * from (select top 10 * from dbname order by date_field desc) order date_field
(2) select pub_name from publishers where pub_id in (select pub_id from titles where type='business')
(3) select au_lname,au_fname,city from authors where city in (select city from authors where au_fname='Livia'or au_lname='Karsen')
(4) select pub_name from publishers where pub_id not in(select pub_id from titles where type='business')
(5) update titles set priceprice=price*2 where pub_id in (select pub_id from publishers where pub_name='new moon books')
4、联接表示 Microsoft? SQL Server? 2000 应如何使用一个表中的数据来选择另一个表中的行。
5、string.Format("SELECT COUNT(ID) FROM {0} WHERE FamilyId={1} AND IsDel<>1", leaveWordTN, familyId);
6、学习http://www.blogjava.net/Ben/
7、declare 定义变量 @变量名 变量类型
8、set 设置变量的值
9、select 有时候不显示数据
declare @objid int,@objname char(40)
set @objname='users'
select @objid=id from sysobjects where id=object_id(@objname)
10、select 有时候显示数据
declare @objid int,@objname char(40)
set @objname = 'users'
select @objid = id from sysobjects where id = object_id(@objname)
select 'Column_name' = name from syscolumns where id = @objid order by colid
11、sql = "select top " + counts + " [id],[name] " + "FROM [life_unite_type] where type=1 and isdeleted<>1 order by id desc ";
12、sql = "select top " + number + " [id],substring([name],1,22)as name " + "FROM [life_unite_active] where flag<>1 order by id desc ";
13、"select count(*) as num from life_unite_product where isShow <> 4 and id not in (select unite_productid from life_unite_active_product where unite_activeid=" + this.lbl_belongto.Text + " ) "
14、SELECT A.*,B.rolename,b.rules FROM life_manager A,life_role B
WHERE A.roleid=B.id AND A.userName=@UserName AND A.password=@Password
15、SELECT DISTINCT top 20 sort_name,sort_value,sum(sort_count) as sort_count from bbs_sort WHERE (sort_name = '今日发帖榜') group by sort_value,sort_name order by sort_count desc
16、select 公司名称,在库存吨位=(select sum(库存吨位) from 仓库 where 库存情况='在库' and 公司名称=t.公司名称),出库存吨位=(select sum(库存吨位) from 仓库 where 库存情况='出库' and 公司名称=t.公司名称) from 仓库 t group by 公司名称
17、select productid,productCount=(select sum(productCount) from life_unite_order_product where productid=t.productid )from life_unite_order_product as t group by productid order by productid desc
18、SELECT DISTINCT top 20 sort_name,sort_value,sum(sort_count) as sort_count from bbs_sort WHERE (sort_name = '今日发帖榜') group by sort_value,sort_name order by sort_count desc
19、SELECT DISTINCT name,sum(id) as idsum,commid from Table_1 as T WHERE (name = T.name and commid=T.commid) group by name,commid order by idsum desc
20、多表相关取数据 Select life_unite_order_product.*,projname
From life_unite_order,life_unite_order_product
Where life_unite_orderlife_unite_order_product.orderid=life_unite_order.id
21、多表相关取数据 Select A.id,B.name,C.projname
From life_unite_order AS C,life_unite_product AS B,life_unite_order_product AS A
Where A.productid=B.id and A.orderid=C.id
22、Select count (distinct 分类名) as 分类种数
23、
商品表1
商品代号 分类名 单价 数量
DBX-134 电冰箱 1456 8
DSJ-120 电视机 1865 15
DSJ-180 电视机 2073 10
DSJ-340 电视机 3726 5
KTQ-12 空调器 2800 12
WBL-6 微波炉 640 10
XYJ-13 洗衣机 468 20
XYJ-20 洗衣机 873 12
Select distinct 分类名 from 商品表1
Select 商品代号,单价*数量 as 价值
From 商品表1
Select count (distinct 分类名) as 分类种数 From 商品表1
Select max (数量）as 最大数量，min (数量）as 最小数量，avg (数量）as 平均数量， sum (数量）as 总和
From 商品表1 结果：
最大数量 最小数量 平均数量 总和
20 5 11 92
Select count(*) as 种数，max (单价）as 最高价，min (单价）as 最低价，avg (单价）as 平均价
From 商品表1
结果：
种数 最高价 最低价 平均价
3 3726.0 1865.0 2554.6666666665
Select max (单价*数量）as 最高价值，min (单价*数量）as 最低价值，sum (单价*数量）as 总价值
From 商品表1
24、SQL中CONVERT转化函数的用法
SELECT CONVERT(varchar(10), DateTime,120) AS DateTime FROM
25、"select sign,projName from bbs_proj where sign in(select sign from bbs_addressbook where username=" + "'" + userclient.UserName + "')"
26、sql语句;select @@identity;"; 太好了执行完sql(insert)后 返回此条ID
27、TOP n PERCENT表示返回的前面的n%行
28、Like 'AB0[1-5] '
29、select @@version 版本
30、变量 declare @a int
set @a=1
set @a=(select count(*) from address)
print @a
31、select * from address where id between 3 and 12
32、查看sql语句的执行时间 在sql语句前加:"set statistics time on" 然后在消息里查看执行时间
33、sp_fulltext_database全文索引
34、
下面的示例查找所有书的平均价格，用值 .00 替换 titles 表的 price 列中的所有 NULL 条目。如price不为空 就返回price
SELECT AVG(ISNULL(price, .00)) FROM titles
update table set hits=isnull(hits,0)+1 where userid='123'
35、select distinct 排重
36、如何撤销udate误操作
BEGIN TRANSACTION --启动一个事务
update ShopInfo set telphone2='5678'
select * from ShopInfo --查看结果
--ROLLBACK TRANSACTION 单独运行恢复数据
37、修改字段 alter table member alter column member_memo [ntext] COLLATE Chinese_PRC_CI_AS NOT NULL
ALTER TABLE [dbo].[member] add CONSTRAINT [DF_member_member_memo] default '' for [member_memo]
alter table tempbaseinfo alter column HGainMeg varchar(600)
38、添加字段 ALTER TABLE dbo.member add member_websname [nvarchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL CONSTRAINT [DF_member_member_websname] DEFAULT ('')
alter table A add TEL_number char(10)
alter table NewHouseNewCodeCount_2016_01 add DianPingCount int
加说明--execute sp_addextendedproperty 'MS_Description', '点评', 'user', 'dbo', 'table', 'NewHouseNewCodeCount_2016_01', 'column', 'DianPingCount'
允许为空指定默认值--ALTER TABLE NewHouseNewCodeCount_2016_02 ADD DianPingCount int NULL DEFAULT 0
execute sp_addextendedproperty 'MS_Description', '点评', 'user', 'dbo', 'table', 'NewHouseNewCodeCount_2016_02', 'column', 'DianPingCount'
不允许为空指定默认值--ALTER TABLE NewHouseNewCodeCount_2016_03 ADD DianPingCount int NOT NULL DEFAULT 0
execute sp_addextendedproperty 'MS_Description', '点评', 'user', 'dbo', 'table', 'NewHouseNewCodeCount_2016_03', 'column', 'DianPingCount'
39、定义存储过程：ALTER procedure [dbo].[ProcRegtempUserByPhone]
执行存储过程：EXECUTE demo_proc DEFAULT,7
exec insert_book '003','Delphi 控件开发指南',$100,@total_price
删除存储过程 drop proc GetEmployeeCount
比如：创建表student，表中有字段stuId，数据类型int,代表“学生学号”，字段stuName，类型为char(20),代表“学生姓名”。
EXEC sp_addextendedproperty N'MS_Description', N'学生学号', N'user', N'dbo', N'table', N'student', N'column', N'stuid'
40、SQL复制表
select * into 目的数据库名.dbo.目的表名 from 原表名
41、复制表结构
如果新表不想要数据，可以这样写：select * into 表2 from 表1 where 1=2
42、exec(@Sql)是好东西
set @TempStr='select top '+cast(@i as varchar(4000))+'* from mt_10'
exec(@TempStr)
declare @i int
declare @TempStr nvarchar(4000)
declare @Trc varchar(1000)
declare @moTable varchar(20)
select @moTable = 'MT_10'
set @i=3
set @TempStr='select top 1 @Trc=userid from mt_10 where id in ('+ 'select top '+cast(@i as varchar(4000))+'id from mt_10 order by id)'+'order by id desc'
exec sp_executesql @TempStr,N'@Trc varchar(1000) out' ,@Trc out
print @Trc
43、getDate()获取当前时间
44、更新消费券
update ShoppingCouponInfo set AvailMount=Amount-(select count(id)
from ShoppingCouponPrintlog
where ShoppingCouponPrintlog.projid=ShoppingCouponInfo.id)
45、将表aaa的数据插入到表aaa_1的对应字段
insert aaa_1 (name,contact,intro) select name,contact,intro from aaa
DBCC CHECKIDENT（TABLE, [RESEED|NORESEED], [1]) ID 重置为1
46、初始化表table1
TRUNCATE TABLE PfczListUrl
47、临时表的创建
select MAX(id) as idz, cid INTO #afc from PlatinumtempUserIntention where UserState=0 or UserState=1 group by cid
DROP TABLE TABLENAME
48、多字段 排重
select max(RegTime1) as regtime2, NewCode1,CID1 from #phoned group by NewCode1,CID1
49、内链接
---白金卡决策阶段用户状态已改变，需转换为咨询阶段 ----2(1,InputUserTime)
select a.CID INTO #PHandle from InputUserTime as a inner join #PlatinumNotJueCe as b
on a.CID=b.cid WHERE Source=5 AND A.CID IN (select id from #JueCeUser)
inner join 只显示重叠的部分 a.CID=b.cid
select a.CID,b.CID as CIDB,a.UID,b.id as UIDB,b.Phone,a.City,a.NewCode,a.NewCode,a.ProjName,a.RegTime from groupPhoneUserProjName as a inner join groupPhoneUser as b
on a.UID=b.id where a.CID=0 and a.City!='' and b.PhoneType=0 and b.CID=0 and b.CreatTime>='2010-09-17 09:51:00.940' and b.CreatTime<='2011-03-16 02:35:34.850' order by RegTime asc
以groupPhoneUserProjName为主9047条记录
(1).内连接：利用内连接可获取两表的公共部分的记录，即图3的记录集C 语句如下：Select * from A JOIN B ON A.Aid=B.Bnameid 运行结果如下图4所示：其实select * from A,B where A.Aid=B.Bnameid与Select * from A JOIN B ON A.Aid=B.Bnameid的运行结果是一样的。
(2)左连接数据
说明：在语句中，A在B的左边，并且是Left Join，所以其运算方式为：A左连接B的记录=图3公共部分记录集C＋表A记录集A1
(3)右连接（Right JOIN）：即图3公共部分记录集C＋表B记录集B1。
语句如下：select * from A Right JOIN B ON A.Aid=B.Bnameid
聚簇索引能提高多行检索的速度（开始时间 结束时间查询快），而非聚簇索引对于单行的检索很快(id 查询 快)。
每个表只能有一个聚集索引
非聚集索引最适于根据特定的键值，从大型 SQL Server 表中提取少数几个具有良好选择性的行。
50、简单理解非聚集索引
非聚集索引会创建一个类似于表的东西，如只是单列的索引，第一列则放置索引列，第二列就是实际数值的指针
查询时，通过这个类似于表的东西查询，实际来说也就类似于聚集索引的查询方式了，只是多了一个指针定位步骤
如是联合索引的话，前面数列就放置所有的可能集合【笛卡尔积】，后面放置指针
51、
显示有关由Transact-SQL 语句生成的磁盘活动量的信息
SET STATISTICS IO ON
--关闭有关由Transact-SQL 语句生成的磁盘活动量的信息
SET STATISTICS IO OFF
--显示[返回有关语句执行情况的详细信息，并估计语句对资源的需求]
SET SHOWPLAN_ALL ON
--关闭[返回有关语句执行情况的详细信息，并估计语句对资源的需求]
SET SHOWPLAN_ALL OFF
记录行号（按某排序）
select ROW_NUMBER() over(order by ID) as rowindex,ID from ActivityPayrecorde where ACID=65
52、sql进行MD5加密
select hashbytes('md5','123456')
SUBSTRING(sys.fn_sqlvarbasetostr(HASHBYTES('MD5', CAST(b.Phone AS VARCHAR(50)))), 3, 32) AS Phone
53、如何将txt文件导入到数据库
Bulk insert #temp from 'e:\test.txt' with (FieldterMinator =',', RowterMinator= '\n')
54.返回有关语句预计所需资源的详细信息
例子:
GO
SET SHOWPLAN_ALL ON
GO
select top 100 * from tempContrlPhoneBatchSend order by id desc
GO
SET SHOWPLAN_ALL OFF
GO
EstimateIO 该运算符的预计 I/O 成本。仅限于 PLAN_ROWS 类型的行。
EstimateCPU 该运算符的预计 CPU 成本。仅限于 PLAN_ROWS 类型的行。
55、查询sql语句执行时间
实例1：
SET STATISTICS PROFILE ON：显示分析、编译和执行查询所需的时间（以毫秒为单位）。
SET STATISTICS IO ON：报告与语句内引用的每个表的扫描数、逻辑读取数（在高速缓存中访问的页数）和物理读取数（访问磁盘的次数）有关的信息。
SET STATISTICS TIME ON：显示每个查询执行后的结果集，代表查询执行的配置文件。
SET STATISTICS PROFILE ON
SET STATISTICS IO ON
SET STATISTICS TIME ON
GO /*--你的SQL脚本开始*/
SELECT [TestCase] FROM [TestCaseSelect]
GO /*--你的SQL脚本结束*/
SET STATISTICS PROFILE OFF
SET STATISTICS IO OFF
SET STATISTICS TIME OFF
实例2：
declare @d datetime
set @d=getdate()
/*你的SQL脚本开始*/
SELECT [TestCase] FROM [TestCaseSelect]
/*你的SQL脚本结束*/
select [语句执行花费时间(毫秒)]=datediff(ms,@d,getdate())
56 导出source转换汉字
select top 100 Cid, RealName, Phone, PlatinumtempCode, City, PurPose, Aprice, PurPoseCountry, PurPoseCity,
CASE
WHEN Source=1 THEN '400来电会员'
WHEN Source=2 THEN '楼盘详情页报名'
WHEN Source=3 THEN '会员报名管理'
WHEN Source=4 THEN '电子商务旗舰店'
WHEN Source=5 THEN '注册页面主动申请'
WHEN Source=6 THEN '用户勾选海外置业同步'
WHEN Source=7 THEN '其他'
WHEN Source=8 THEN '后台 置业会员添加'
WHEN Source=9 THEN '海外二手房留言'
WHEN Source=10 THEN '国际会员后台添加'
WHEN Source=11 THEN '国际站注册'
WHEN Source=12 THEN '置业心愿单'
WHEN Source=13 THEN '首页委托'
WHEN Source=14 THEN 'IM报名'
WHEN Source=15 THEN '商广项目'
WHEN Source=16 THEN '自售项目'
WHEN Source=17 THEN '渠道活动'
WHEN Source=18 THEN '线下活动'
WHEN Source=19 THEN '团'
WHEN Source=20 THEN '营销产品'
WHEN Source=21 THEN '内容组推广'
WHEN Source=22 THEN '微信报名'
ELSE '其他'
END as IsOver
from WorldtempUser where CreateTime>'2016-07-23' and CreateTime<'2016-07-26' order by id desc
57、连表创建视图
SELECT a.*,
(SELECT count(*)
FROM tempUserSystemCallMissionBatchDistributeRecord_0 WITH (nolock)
WHERE mid = a.mid AND mbid = a.mbid AND Editor = a.Editor AND CallTime IS NOT NULL) AS CallCount
FROM tempUserSystemCallMissionBatchDistribute AS a WITH (nolock)
WHERE RIGHT(CONVERT(nvarchar(20), mid), 1) = '0'
UNION ALL
SELECT a.*,
(SELECT count(*)
FROM tempUserSystemCallMissionBatchDistributeRecord_1 WITH (nolock)
WHERE mid = a.mid AND mbid = a.mbid AND Editor = a.Editor AND CallTime IS NOT NULL) AS CallCount
FROM tempUserSystemCallMissionBatchDistribute AS a WITH (nolock)
WHERE RIGHT(CONVERT(nvarchar(20), mid), 1) = '1'
58、导出某个表的的 字段名 字段类型 字段长度 字段说明
select distinct sc.colorder,sc.name as 字段名,st.name as '字段类型',sc.length as 字段长度,g.[value] AS 字段说明 from syscolumns sc
LEFT OUTER JOIN sys.extended_properties g
on sc.id=g.major_id AND sc.colid = g.minor_id
LEFT OUTER JOIN systypes st
on sc.xtype=st.xtype
where
sc.id in(
select id from sysobjects where xtype='U' and name='tempInfo_0')
and st.name!='sysname'
order by sc.colorder
59、日期转换
只显示年月,不显示日:select datepart(year,getdate())*100+datepart(month,getdate())
Select CONVERT(varchar(100), GETDATE(), 23): 2006-05-16
select datepart(year,getdate()),'_',datepart(month,getdate())
60、实用with动态表
WITH t_LookHouse AS
(
select min(id) as id,orderid from FangALLOrder_lookhouse with (nolock) where orderid>136 group by orderid
)
SELECT top 1000 * FROM FangALLOrder_lookhouse a WITH(NOLOCK)
INNER JOIN t_LookHouse b WITH(NOLOCK) ON a.id=b.Id WHERE a.orderid>136
61、添加主键SQL语句
ALTER TABLE Skills ADD PRIMARY KEY (SkillID);
62、C#读取数据库时间字段 毫秒信息
SELECT TOP 1000 CONVERT(varchar(40) , CreateTime, 21 ),* FROM FangCollectionlog WITH(NOLOCK) WHERE type=0 and CreateTime>'2016-12-23 10:52:07.927' order by CreateTime asc
63、验证手机并发 2011-12-30 start
SET @TempStr = 'INSERT INTO OnlyPhone(Phone) VALUES (''' + @Phone + ''')';
EXEC @TempStr;
SET @TempRC = @@ROWCOUNT;
if @TempRC is null or @TempRC='' or cast(@TempRC as int) <1
begin
set @IsValid = 0;
set @RState = 5;--表示该手机号已存在
end
--验证手机并发 2011-12-30 end
64、
select nolock 运行出现脏读 都可以使用nolock来防止并发锁定------此类项目应用最好是非谨慎项目
脏读解决办法 修改时加排他锁 读取时加共享锁 （这样事务1读取数据过程中，其他事务就不会修改该数据；事务1之后有更新操作，那么会转换为排他锁，其他事务更无权参读和写；当事务1读取数据过程中，有可能其他事务也读取了该数据，读取完毕后共享锁释放）
65、左连接
select top 1000 a.*,a.Editor,b.UserName,b.city from tempContrlPhoneBatchSendCmd as a left outer join temp_USER_BackLimit as b on a.Editor=b.UserName where a.SendTime>'2011-12-23' and a.SendTime<'2012-01-23' order by SendTime asc
update OrientationEmailBatchSendCmd set CmdExpre=REPLACE(CmdExpre,'tempbaseinfo where','where') where id=34
left where
select *, cast(left(reserved,len(reserved)-2) as int)/1024 as a from temp_tablespaceinfo where left(nameinfo,len(nameinfo)-3)='tempMessagesSended' order by cast(left(reserved,len(reserved)-2) as int) desc
66、手机截字整理
select top 100 id, LEFT(phone,LEN(phone)-2) as ab from View_tempInfoAll where city='上海' and regtime>'2012-03-08' and regtime<'2012-03-09' and phone!='' and LEFT(phone,LEN(phone)-2)='138185350'
67、admin赋予存储过程 给写权限
grant exec on ProcUpdatetempInfo to group_temp_w
grant exec on ProcGettempBaseInfo to group_temp_w
68、group by 分组统计 count大于1的记录
select count(id) as Num,phone
from tempInfo_0
where Phone is not null or Phone !=''
group by phone having
count(id)>1
69、时间差 后参数-前参数
SELECT datediff( day, '2010-01-01 00:00', '2010-01-03 23:59')结果为2
70、数据库当前时间减去5秒
select DateAdd("s", -5, getdate())
71、
substring(expression,start,length)
expression:要截取的字符串
start：为1代表从第一个字符开始(包括第一个字符 这里没有0的概念)
length:要截取多长
72、
declare @CDetaiTb varchar(20)
declare @tempID varchar(20)
set @CDetaiTb='tempMARKACTLOG_';
set @tempID='512348';
select len(@tempID);
select @CDetaiTb+substring(@tempID,len(@tempID)-1,2);
结果:tempMARKACTLOG_48
虚拟表的创建于插入
CREATE TABLE #tblTmp
(
[Phone] [varchar](20)
)
Insert into #tblTmp (phone) values
throw new Exception代表异常可以
catch (Exception ex)
{
string message ="获取北方接口经济公司请求接盘记录信息报错url:" + url + "错误:" + ex.ToString();
throw new Exception(message);
}
73、使用符合条件的记录集做为一个表 进行二次查询
select * from (select top 100 * from Agents where AgentsCity='北京')c
select * from (select top 100 * from Agents where AgentsCity='北京')c where c.ID=9
74、两表并表 查询
select count(*) from
(SELECT * FROM dbo.tempBaseInfo_new_2013q where tempstate=0 UNION ALL
SELECT * FROM dbo.tempBaseInfo_new_2013h where tempstate=0) as tabname
75、如何查找作业
对象资源管理器中---SQL Server 代理
exec dbo.tempUserStageRollBack
76、WHERE 条件应该写到内查询中。先过滤，再链接，这样链接时数据量就小很多。如果先链接，则是两个表全数据匹配，然后再过滤。
select count(*) from mallrecorde_change where (a.FeeConfimTime>='2015/1/29 0:00:00' and a.FeeConfimTime<'2015/1/30 0:00:00' ) as a with(nolock) left join mallProjInfo (b.isDeleted=0 and b.activityType=0 ) as b with(nolock) on a.aid=b.id
77、按日分组统计
SELECT CONVERT(VARCHAR(10),RegTime,120),COUNT(*)
FROM View_tempInfoNewHouse with (nolock) where Source!=53 and RegTime>'2015-03-01' and RegTime<'2015-04-08'
GROUP BY CONVERT(VARCHAR(10),RegTime,120) order by CONVERT(VARCHAR(10),RegTime,120) asc
78、快速导入txt到sql server
Bulk insert #temp from 'e:\test.txt' with (FieldterMinator =',', RowterMinator= '\n')
79、sql 休息 休眠5秒
declare @rv rowversion
select @rv=b from tmp where a='abc'
waitfor delay '00:00:05' --休息5秒
update tmp set a='xyz' where b=@rv
go
80、
说明：随机取出10条数据
select top 10 * from tablename order by newid()
说明：随机选择记录
select newid()
说明：删除重复记录
Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...)
说明：列出数据库里所有的表名
select name from sysobjects where type='U'
说明：列出表里的所有的字段
select name from syscolumns where id=object_id('TableName')
81、变量赋值
DECLARE @TableName varchar(100);--城市拆表名
select @TableName=TbName from UserBehavior_TbName where City=@City
DECLARE @count_Behavior INT ;--用户行为
select @count_Behavior=COUNT(*) from UserBehaviorType
DECLARE @CmdExpre varchar(5000);--语句表达式
set @CmdExpre='Insert into #tblTmp select cid,BehaviorID,CreateTime
from '+@TableName;
exec (@CmdExpre)
82、游标
使用游标的顺序： 声名游标、打开游标、读取数据、关闭游标、删除游标。
1.3.1声明游标
最简单游标声明：DECLARE <游标名>CURSOR FOR<SELECT语句>；
其中select语句可以是简单查询，也可以是复杂的接连查询和嵌套查询
例子：[已表2 AddSalary为例子]
Declare mycursor cursor for select * from AddSalary
这样我就对表AddSalary申明了一个游标mycursor
实例训练游标
USE [grouptemp_test]
GO
/****** Object: StoredProcedure [dbo].[Test_Temp_Pk] Script Date: 2015/7/8 18:07:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Test_Temp_Pk]
AS
--声明6个变量t
declare @O_id nvarchar(30)
declare @mid nvarchar(30)
declare @mbid nvarchar(30)
declare @Editor nvarchar(40)
declare @CallCount nvarchar(30)
declare @tabname nvarchar(100)
--声明一个游标mycursor，select语句中参数的个数必须要和从游标取出的变量名相同
declare mycursor cursor for select top 50 id as O_id,mid,mbid,Editor from tempUserSystemCallMissionBatchDistribute with (nolock)
--打开游标
open mycursor
--从游标里取出数据赋值到我们刚才声明的2个变量中
fetch next from mycursor into @O_id,@mid,@mbid,@Editor
--判断游标的状态
--0 fetch语句成功
---1 fetch语句失败或此行不在结果集中
---2被提取的行不存在
while (@@fetch_status=0)
begin
--显示出我们每次用游标取出的值
--print '游标成功取出一条数据'
--DECLARE @count_Behavior INT ;--用户行为
--select @count_Behavior=COUNT(*) from UserBehaviorType
--DECLARE @CmdExpre varchar(5000);--语句表达式
--set @CmdExpre='Insert into #tblTmp select cid,BehaviorID,CreateTime from '+@TableName;
--exec (@CmdExpre)
set @tabname='tempUserSystemCallMissionBatchDistributeRecord_'+RIGHT(CONVERT(nvarchar(20), @mid), 1)
--select @CallCount=count(*) FROM tempUserSystemCallMissionBatchDistributeRecord_0 WHERE mid = @mid AND mbid = @mbid AND Editor = @Editor AND CallTime IS NOT NULL
DECLARE @CmdExpre nvarchar(4000);--语句表达式
set @CallCount=''
set @CmdExpre='select @Trc=count(*) FROM '+@tabname+' with (nolock) WHERE mid ='+ @mid+' AND mbid = '+@mbid +' AND Editor = '''+@Editor +''' AND CallTime IS NOT NULL'
print @CmdExpre
--exec (@CmdExpre)
execute sp_executesql @CmdExpre,N'@Trc varchar(20) output',@CallCount output
print '@O_ID:'+@O_id+'@mid:'+@mid+'@mbid:'+@mbid+'@Editor:'+@Editor+'@tabname:'+@tabname+'@CallCount:'+@CallCount
--用游标去取下一条记录
fetch next from mycursor into @O_id,@mid,@mbid,@Editor
end
--关闭游标
close mycursor
--撤销游标
deallocate mycursor
1614
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
