常用SQL备份--仅为留作备份(downmoon)
来源:互联网 发布:淘宝基本知识 编辑:程序博客网 时间:2024/06/01 09:57
一、数据表结构
[M_ID] [int] IDENTITY (1, 1) NOT NULL ,
[P_ID] [int] NULL ,
[P_C_ID] [int] NULL ,
[P_Name] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[P_Key] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[P_SingleIntro] [nvarchar] (1000) COLLATE Chinese_PRC_CI_AS NULL ,
[P_Intro] [ntext] COLLATE Chinese_PRC_CI_AS NULL ,
[P_Order] [float] NULL ,
[P_Cate] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[P_SysCate] [nvarchar] (500) COLLATE Chinese_PRC_CI_AS NULL ,
[P_C_Name] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[P_C_Order] [int] NULL ,
[P_C_Contact] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[P_C_Address] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[P_C_Tel] [nvarchar] (80) COLLATE Chinese_PRC_CI_AS NULL ,
[P_C_Email] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[P_C_Fax] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[P_C_Web] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[P_TopTime] [smalldatetime] NULL ,
[P_InsertTime] [smalldatetime] NULL ,
[P_ModifyTime] [smalldatetime] NULL ,
[P_BigImage] [nvarchar] (150) COLLATE Chinese_PRC_CI_AS NULL ,
[P_SmallImage] [nvarchar] (150) COLLATE Chinese_PRC_CI_AS NULL ,
[P_User] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[P_CheckState] [int] NULL ,
[P_CheckInfo] [nvarchar] (80) COLLATE Chinese_PRC_CI_AS NULL ,
[P_Price] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[P_OtherState] [int] NULL ,
[M_ZipCode] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[M_PrintImage] [nvarchar] (300) COLLATE Chinese_PRC_CI_AS NULL ,
[M_Year] [int] NULL ,
[M_Month] [int] NULL ,
[M_Term] [int] NULL ,
[M_Name] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[M_SubmitDate] [smalldatetime] NOT NULL CONSTRAINT [DF__Magzine__M_Submi__284DF453] DEFAULT (getdate()),
[IsUse] [smallint] NOT NULL CONSTRAINT [DF__Magzine__IsUse__2942188C] DEFAULT (0),
[F1] [int] NULL ,
[F2] [int] NULL ,
[F3] [nvarchar] (300) COLLATE Chinese_PRC_CI_AS NULL ,
[F4] [nvarchar] (300) COLLATE Chinese_PRC_CI_AS NULL ,
[C_TelCode] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
CONSTRAINT [PK_magzine] PRIMARY KEY CLUSTERED
(
[M_ID]
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
二、数据如下
INSERT INTO dbo.Magzine(
M_ID,P_ID,P_C_ID,P_Name,
P_Key,P_SingleIntro,P_Intro,P_Order,
P_Cate,P_SysCate,P_C_Name,P_C_Order,
P_C_Contact,P_C_Address,P_C_Tel,P_C_Email,
P_C_Fax,P_C_Web,P_TopTime,P_InsertTime,
P_ModifyTime,P_BigImage,P_SmallImage,P_User,
P_CheckState,P_CheckInfo,P_Price,P_OtherState,
M_ZipCode,M_PrintImage,M_Year,M_Month,
M_Term,M_Name,M_SubmitDate,IsUse,
F1,F2,F3,F4,
C_TelCode)
VALUES (
1,1,119,
'长白山老人参119','人参','简介:长白山的百年人参119','详细介绍119',
120,'','种子、种苗','长白山第119人参种植厂',
100,'胡119涛','中南海办公楼1号','010-51888888',
'hu1tao@cn.com','010-88888888','http://www.china.com','2006-07-14 14:54:00.000',
'2006-08-13 14:54:00.000','2006-08-03 14:54:00.000','http://www.vegnet.com.cn/images/ploy_d.jpg','http://www.vegnet.com.cn/images/ploy.jpg',
'欢迎与邀月交流,net技术与软件架构',1,'正确','981¥/克',
1,'313200','http://www.vegnet.com.cn/images/ploy_d.jpg',0,
1,32,'蔬菜网杂志0年1月','2006-10-11 14:54:00.000',
1,0,0,NULL,
NULL,'010')
GO
INSERT INTO dbo.Magzine(
M_ID,P_ID,P_C_ID,P_Name,
P_Key,P_SingleIntro,P_Intro,P_Order,
P_Cate,P_SysCate,P_C_Name,P_C_Order,
P_C_Contact,P_C_Address,P_C_Tel,P_C_Email,
P_C_Fax,P_C_Web,P_TopTime,P_InsertTime,
P_ModifyTime,P_BigImage,P_SmallImage,P_User,
P_CheckState,P_CheckInfo,P_Price,P_OtherState,
M_ZipCode,M_PrintImage,M_Year,M_Month,
M_Term,M_Name,M_SubmitDate,IsUse,
F1,F2,F3,F4,
C_TelCode)
VALUES (
2,2,127,
'长白山老人参127','人参','简介:长白山的百年人参127','详细介绍127',
120,'','肥料农药','长白山第127人参种植厂',
100,'胡127涛','中南海办公楼1号','010-51888888',
'hu2tao@cn.com','010-88888888','http://www.china.com','2006-07-06 14:54:00.000',
'2006-08-05 14:54:00.000','2006-07-26 14:54:00.000','http://www.vegnet.com.cn/images/ploy_d.jpg','http://www.vegnet.com.cn/images/ploy.jpg',
'欢迎与邀月交流,net技术与软件架构',1,'正确','973¥/克',
1,'313200','http://www.vegnet.com.cn/images/ploy_d.jpg',0,
1,32,'蔬菜网杂志0年1月','2006-10-11 14:54:00.000',
1,0,0,NULL,
NULL,'010')
GO
INSERT INTO dbo.Magzine(
M_ID,P_ID,P_C_ID,P_Name,
P_Key,P_SingleIntro,P_Intro,P_Order,
P_Cate,P_SysCate,P_C_Name,P_C_Order,
P_C_Contact,P_C_Address,P_C_Tel,P_C_Email,
P_C_Fax,P_C_Web,P_TopTime,P_InsertTime,
P_ModifyTime,P_BigImage,P_SmallImage,P_User,
P_CheckState,P_CheckInfo,P_Price,P_OtherState,
M_ZipCode,M_PrintImage,M_Year,M_Month,
M_Term,M_Name,M_SubmitDate,IsUse,
F1,F2,F3,F4,
C_TelCode)
VALUES (
3,3,116,
'长白山老人参116','人参','简介:长白山的百年人参116','详细介绍116',
120,'','蔬菜基地','长白山第116人参种植厂',
100,'胡116涛','中南海办公楼1号','010-51888888',
'hu3tao@cn.com','010-88888888','http://www.china.com','2006-07-17 14:54:00.000',
'2006-08-16 14:54:00.000','2006-08-06 14:54:00.000','http://www.vegnet.com.cn/images/ploy_d.jpg','http://www.vegnet.com.cn/images/ploy.jpg',
'test333',1,'正确','984¥/克',
1,'313200','http://www.vegnet.com.cn/images/ploy_d.jpg',0,
1,32,'蔬菜网杂志0年1月','2006-10-11 14:54:00.000',
1,0,0,NULL,
NULL,'010')
GO
INSERT INTO dbo.Magzine(
M_ID,P_ID,P_C_ID,P_Name,
P_Key,P_SingleIntro,P_Intro,P_Order,
P_Cate,P_SysCate,P_C_Name,P_C_Order,
P_C_Contact,P_C_Address,P_C_Tel,P_C_Email,
P_C_Fax,P_C_Web,P_TopTime,P_InsertTime,
P_ModifyTime,P_BigImage,P_SmallImage,P_User,
P_CheckState,P_CheckInfo,P_Price,P_OtherState,
M_ZipCode,M_PrintImage,M_Year,M_Month,
M_Term,M_Name,M_SubmitDate,IsUse,
F1,F2,F3,F4,
C_TelCode)
VALUES (
5,5,122,
'长白山老人参122','人参','简介:长白山的百年人参122','详细介绍122',
120,'','蔬菜基地','长白山第122人参种植厂',
100,'胡122涛','中南海办公楼1号','010-51888888',
'hu5tao@cn.com','010-88888888','http://www.china.com','2006-07-11 14:54:00.000',
'2006-08-10 14:54:00.000','2006-07-31 14:54:00.000','http://www.vegnet.com.cn/images/ploy_d.jpg','http://www.vegnet.com.cn/images/ploy.jpg',
'test333',1,'正确','978¥/克',
1,'313200','http://www.vegnet.com.cn/images/ploy_d.jpg',0,
1,32,'蔬菜网杂志0年1月','2006-10-11 14:54:00.000',
1,0,0,NULL,
NULL,'010')
GO
INSERT INTO dbo.Magzine(
M_ID,P_ID,P_C_ID,P_Name,
P_Key,P_SingleIntro,P_Intro,P_Order,
P_Cate,P_SysCate,P_C_Name,P_C_Order,
P_C_Contact,P_C_Address,P_C_Tel,P_C_Email,
P_C_Fax,P_C_Web,P_TopTime,P_InsertTime,
P_ModifyTime,P_BigImage,P_SmallImage,P_User,
P_CheckState,P_CheckInfo,P_Price,P_OtherState,
M_ZipCode,M_PrintImage,M_Year,M_Month,
M_Term,M_Name,M_SubmitDate,IsUse,
F1,F2,F3,F4,
C_TelCode)
VALUES (
6,6,116,
'长白山老人参116','人参','简介:长白山的百年人参116','详细介绍116',
120,'','蔬菜基地','长白山第116人参种植厂',
100,'胡116涛','中南海办公楼1号','010-51888888',
'hu6tao@cn.com','010-88888888','http://www.china.com','2006-07-17 14:54:00.000',
'2006-08-16 14:54:00.000','2006-08-06 14:54:00.000','http://www.vegnet.com.cn/images/ploy_d.jpg','http://www.vegnet.com.cn/images/ploy.jpg',
'欢迎与邀月交流,net技术与软件架构',1,'正确','984¥/克',
1,'313200','http://www.vegnet.com.cn/images/ploy_d.jpg',0,
1,32,'蔬菜网杂志0年1月','2006-10-11 14:54:00.000',
1,0,0,NULL,
NULL,'010')
GO
INSERT INTO dbo.Magzine(
M_ID,P_ID,P_C_ID,P_Name,
P_Key,P_SingleIntro,P_Intro,P_Order,
P_Cate,P_SysCate,P_C_Name,P_C_Order,
P_C_Contact,P_C_Address,P_C_Tel,P_C_Email,
P_C_Fax,P_C_Web,P_TopTime,P_InsertTime,
P_ModifyTime,P_BigImage,P_SmallImage,P_User,
P_CheckState,P_CheckInfo,P_Price,P_OtherState,
M_ZipCode,M_PrintImage,M_Year,M_Month,
M_Term,M_Name,M_SubmitDate,IsUse,
F1,F2,F3,F4,
C_TelCode)
VALUES (
7,7,125,
'长白山老人参125','人参','简介:长白山的百年人参125','详细介绍125',
120,'','种子、种苗','长白山第125人参种植厂',
100,'胡125涛','中南海办公楼1号','010-51888888',
'hu7tao@cn.com','010-88888888','http://www.china.com','2006-07-08 14:54:00.000',
'2006-08-07 14:54:00.000','2006-07-28 14:54:00.000','http://www.vegnet.com.cn/images/ploy_d.jpg','http://www.vegnet.com.cn/images/ploy.jpg',
'欢迎与邀月交流,net技术与软件架构',1,'正确','975¥/克',
1,'313200','http://www.vegnet.com.cn/images/ploy_d.jpg',0,
1,32,'蔬菜网杂志0年1月','2006-10-11 14:54:00.000',
1,0,0,NULL,
NULL,'010')
GO
INSERT INTO dbo.Magzine(
M_ID,P_ID,P_C_ID,P_Name,
P_Key,P_SingleIntro,P_Intro,P_Order,
P_Cate,P_SysCate,P_C_Name,P_C_Order,
P_C_Contact,P_C_Address,P_C_Tel,P_C_Email,
P_C_Fax,P_C_Web,P_TopTime,P_InsertTime,
P_ModifyTime,P_BigImage,P_SmallImage,P_User,
P_CheckState,P_CheckInfo,P_Price,P_OtherState,
M_ZipCode,M_PrintImage,M_Year,M_Month,
M_Term,M_Name,M_SubmitDate,IsUse,
F1,F2,F3,F4,
C_TelCode)
VALUES (
9,9,120,
'长白山老人参120','人参','简介:长白山的百年人参120','详细介绍120',
120,'','肥料农药','长白山第120人参种植厂',
100,'胡120涛','中南海办公楼1号','010-51888888',
'hu9tao@cn.com','010-88888888','http://www.china.com','2006-07-13 14:54:00.000',
'2006-08-12 14:54:00.000','2006-08-02 14:54:00.000','http://www.vegnet.com.cn/images/ploy_d.jpg','http://www.vegnet.com.cn/images/ploy.jpg',
'欢迎与邀月交流,net技术与软件架构',1,'正确','980¥/克',
1,'313200','http://www.vegnet.com.cn/images/ploy_d.jpg',0,
1,32,'蔬菜网杂志0年1月','2006-10-11 14:54:00.000',
1,0,0,NULL,
NULL,'010')
GO
INSERT INTO dbo.Magzine(
M_ID,P_ID,P_C_ID,P_Name,
P_Key,P_SingleIntro,P_Intro,P_Order,
P_Cate,P_SysCate,P_C_Name,P_C_Order,
P_C_Contact,P_C_Address,P_C_Tel,P_C_Email,
P_C_Fax,P_C_Web,P_TopTime,P_InsertTime,
P_ModifyTime,P_BigImage,P_SmallImage,P_User,
P_CheckState,P_CheckInfo,P_Price,P_OtherState,
M_ZipCode,M_PrintImage,M_Year,M_Month,
M_Term,M_Name,M_SubmitDate,IsUse,
F1,F2,F3,F4,
C_TelCode)
VALUES (
20,10,112,
'长白山老人参112','人参','简介:长白山的百年人参112','详细介绍112',
120,'','种子、种苗','长白山第112人参种植厂',
100,'胡112涛','中南海办公楼1号','010-51888888',
'hu10tao@cn.com','010-88888888','http://www.china.com','2006-07-21 14:54:00.000',
'2006-08-20 14:54:00.000','2006-08-10 14:54:00.000','http://www.vegnet.com.cn/images/ploy_d.jpg','http://www.vegnet.com.cn/images/ploy.jpg',
'欢迎与邀月交流,net技术与软件架构',1,'正确','988¥/克',
1,'313200','http://www.vegnet.com.cn/images/ploy_d.jpg',0,
1,32,'蔬菜网杂志0年1月','2006-10-11 14:54:00.000',
1,0,0,NULL,
NULL,'010')
GO
INSERT INTO dbo.Magzine(
M_ID,P_ID,P_C_ID,P_Name,
P_Key,P_SingleIntro,P_Intro,P_Order,
P_Cate,P_SysCate,P_C_Name,P_C_Order,
P_C_Contact,P_C_Address,P_C_Tel,P_C_Email,
P_C_Fax,P_C_Web,P_TopTime,P_InsertTime,
P_ModifyTime,P_BigImage,P_SmallImage,P_User,
P_CheckState,P_CheckInfo,P_Price,P_OtherState,
M_ZipCode,M_PrintImage,M_Year,M_Month,
M_Term,M_Name,M_SubmitDate,IsUse,
F1,F2,F3,F4,
C_TelCode)
VALUES (
95,10731,90571,
'辣椒种子-皱皮辣','','单株多年定向选择的优良品种,抗病丰产,株高70厘米,开展度40厘米,果实浅白绿色,老熟后红色,果形四棱灯笼形,果皮不规则皱缩,平均单果重30-40克,味辣,早熟,产量高。','',
0,'','蔬菜基地','昆明春喜农业科技开发有限公司',
0,'韩成杰','云南昆明市省种子交易市场四幢6-12号(小板桥)','0871-7361111,7362883,7363333,7364444',
'chunxiseed@163.com','0871-7362222,7364185','www.chunxiseed.com.cn',NULL,
NULL,NULL,'2005971025538635.jpg','200597102553586.jpg',
'chunxiseed',1,'','',
0,'','',2007,
5,32,'蔬菜网杂志2007年5月','2007-03-10 15:01:00.000',
1,0,0,'',
'','')
GO
INSERT INTO dbo.Magzine(
M_ID,P_ID,P_C_ID,P_Name,
P_Key,P_SingleIntro,P_Intro,P_Order,
P_Cate,P_SysCate,P_C_Name,P_C_Order,
P_C_Contact,P_C_Address,P_C_Tel,P_C_Email,
P_C_Fax,P_C_Web,P_TopTime,P_InsertTime,
P_ModifyTime,P_BigImage,P_SmallImage,P_User,
P_CheckState,P_CheckInfo,P_Price,P_OtherState,
M_ZipCode,M_PrintImage,M_Year,M_Month,
M_Term,M_Name,M_SubmitDate,IsUse,
F1,F2,F3,F4,
C_TelCode)
VALUES (
126,10852,90571,
'热王A1','','热王A1,一代杂交种子,早熟、耐热,生长期约50天,株形较披张,外叶深绿,叶面稍皱,刺毛极少,叶柄较凹,白绿色,叶长卵圆形,球顶圆,叠抱,软叶率58%。','',
0,'','蔬菜基地','昆明春喜农业科技开发有限公司',
0,'韩成杰','云南昆明市省种子交易市场四幢6-12号(小板桥)','7361111',
'chunxiseed@163.com','7362222','www.chunxiseed.com.cn',NULL,
NULL,NULL,'2005971655584053.jpg','20070327-131703-671-987.jpg',
'chunxiseed',1,'','',
0,'313200','20070327-131703-656-128282.jpg',2007,
6,32,'蔬菜网杂志2007年6月','2007-03-27 13:17:00.000',
1,0,0,'127.0.0.1',
'','0571')
GO
INSERT INTO dbo.Magzine(
M_ID,P_ID,P_C_ID,P_Name,
P_Key,P_SingleIntro,P_Intro,P_Order,
P_Cate,P_SysCate,P_C_Name,P_C_Order,
P_C_Contact,P_C_Address,P_C_Tel,P_C_Email,
P_C_Fax,P_C_Web,P_TopTime,P_InsertTime,
P_ModifyTime,P_BigImage,P_SmallImage,P_User,
P_CheckState,P_CheckInfo,P_Price,P_OtherState,
M_ZipCode,M_PrintImage,M_Year,M_Month,
M_Term,M_Name,M_SubmitDate,IsUse,
F1,F2,F3,F4,
C_TelCode)
VALUES (
127,10709,90571,
'春喜翠宝','','西葫芦种子,极早熟一代小果型西葫芦杂交种。','',
0,'葱蒜类','种子、种苗~葱蒜类','昆明春喜农业科技开发有限公司',
0,'五功','云南昆明市省种子交易市场四幢6-12号(小板桥)','7361111',
'chunxiseed@163.com','7362222','www.chunxiseed.com.cn',NULL,
NULL,NULL,'200597918378286.jpg','200597918373826.jpg',
'chunxiseed',1,'','',
0,'310012','000___20061114-175345-828-679.JPG',2007,
6,32,'蔬菜网杂志2007年6月','2007-04-09 09:02:00.000',
1,0,0,'10.103.33.6',
'','0871')
GO
Set Identity_Insert dbo.Magzine OFF
三、现欲统计表中每期产品推荐中的每个类别的产品数、企业数
方法一:用表变量和游标:
-- CPP_Vegnet_GetCountOfMagzine 32
Create procedure CPP_Vegnet_GetCountOfMagzine
(@M_Term int)
as
SET NOCOUNT ON
declare @d2 datetime
set @d2=getdate()
DECLARE @M_Cate nvarchar(100)
DECLARE @TmpID int
DECLARE @TmpID2 int
DECLARE @M_Count int
DECLARE @C_Count int
DECLARE @M_Cate2 nvarchar(100)
Declare @TableVar Table (PKID int IDENTITY (1, 1) Primary Key ,M_cate nvarchar(100),M_Count int,C_Count int)
Insert Into @TableVar (M_cate,M_Count)
select P_SysCate as M_cate,count(M_ID) as C_Count from Magzine
where M_Term=@M_Term and IsUse>=1
group by P_SysCate
order by P_SysCate
--Select * From @TableVar order by M_cate
DECLARE Magzine_Cursor CURSOR FOR
Select PKID,M_cate From @TableVar order by M_cate
OPEN Magzine_Cursor
FETCH NEXT FROM Magzine_Cursor
INTO @TmpID,@M_Cate2
WHILE @@FETCH_STATUS = 0
BEGIN
print @TmpID
print @M_Cate2
set @TmpID2=0
--修改记录
set @TmpID2=( select count(Distinct P_C_Name) from Magzine
where (M_Term=@M_Term) and (P_SysCate=@M_Cate2) and (IsUse>=1)
)
print @TmpID2
update @TableVar set C_Count=@TmpID2 where pkID=@TmpID and M_Cate=@M_Cate2
--修改结束
FETCH NEXT FROM Magzine_Cursor into @TmpID,@M_Cate2
END
CLOSE Magzine_Cursor
DEALLOCATE Magzine_Cursor
Select * From @TableVar order by M_cate
select [语句执行花费时间(毫秒)]=datediff(ms,@d2,getdate())
方法二:用SQL语句
where M_Term=32 and IsUse>=1
group by P_SysCate
order by P_SysCate
- 常用SQL备份--仅为留作备份(downmoon)
- 常用SQL备份--仅为留作备份
- MySQL常用操作(留作备份)
- Markdown 指引 留作备份
- 仅复制备份 (SQL Server)
- 常用SQL语句备份
- smarty 截取字符串 自己留作 备份
- 在mac 机器上常用的命令(留作备份)持续更新~
- SQl 数据库常用语句备份
- 不常用sql语句备份
- SAP HANA常用sql备份
- 一个在线测试Html、脚本代码的网站,留作备份
- 备份sql
- sql备份
- SQL备份
- SQL备份
- SQL备份
- 常用sql语句与mysql备份恢复
- Zee的LR脚本练习四: 关于函数lr_eval_string
- 只修改input file组件的浏览按钮样式
- 动态显示文本框中输入的字符数字
- 文章复制打印代码
- 收藏的做网页的一些精华asp代码
- 常用SQL备份--仅为留作备份(downmoon)
- asp连接oracle数据库读写
- javascript 操作xml数据流
- 抓取文章
- JCalendar 日历控件
- H.323桌面会议视频系统
- 静态变量
- chr码的资料
- .NET2.0抓取网页全部链接