工作项目总结(一)之数据库操作

 以下数据库操作大部分为项目研发中遇见所需要解决的，现贴出如下。部分为他人原创，如有侵权，请联系本人，予以删除，谢谢！

1、

<span style="font-family:SimSun;font-size:16px;">--SQL code 说明：列出数据库里所有的表名 select name from sysobjects where type='U' --说明：列出表里的所有的列 列名select name from syscolumns where id=object_id('GWSPMD_ChartParameter')</span>

2、

<span style="font-family:SimSun;">--时间函数--sql对时间类型的操作的常用函数--getdate() --返回当前系统日期和时间。--DateAdd 在向指定日期加上一段时间的基础上，返回新的datetime 值--mm 月dd 天数yy年hh小时ss分钟--DATEADD ( datepart , number, date ) --例：向当天的时间增加天--select dateadd(dd,5,getdate())（红色部分修改为相应的月、日、年等）--convert(datetime, Time) DTTime（转换时间格式）--分钟Convert(varchar(16),DTTime,120) --小时Convert(varchar(13),Emidtime, 120)-- 年月日Convert(varchar(100),DTTime, 23)--月Convert(varchar(7),DTTime, 23)</span> 

3、

--更新语句Update table  set A=a,B=b where C=c

4、 

--将一个数据表数据无重复导入另一个数据表 insert into GWSPMD_EMIselect *from GWSPMD_TemporaryEMI  as A where Not Exists(Select *  From GWSPMD_EMI where GWSPMD_EMI.Emidtime=A.TETime )

5、

--创建自增长数据表if exists (select * from sysobjects where name='GWSPMD_ChartCharacter')drop table GWSPMD_ChartCharacter create table GWSPMD_ChartCharacter(ChartCharacter_Id int primary key identity(1,1),ChartCharacter_name varchar(30) not null unique,Del_flg char(1) default '0' )----自增长数据表插入 ，不需要管自增长的列insert into GWSPMD_ChartCharactervalues('时发电量','0')

 

6、

--一个项目完成后数据库中会有很多无用的测试数据，可以使用delete * 将数据全部删除，但自增长列（一般是主键）基数不会归零，使用TRUNCATE函数可以将表中数据全部删除，并且将自增长列基数归零。一定要注意，表中的数据全部删除了。它的语法如下：TRUNCATE TABLE tableName --其中tableName中所要操作的数据原文来自：雨枫技术教程网 http://www.fengfly.com原文网址：http://67.220.81.123/plus/view-172949-1.html

7、

--SQL函数 AVG求平均数/*********************************************/---向GWSPMD_users表插入数据--INSERT INTO table_name (列, 列,...) VALUES (值, 值,....)INSERT INTO GWSPMD_users (User_id,User_name,User_password,User_role,Del_flg ) VALUES ('a', 'a','a',0,0)

8、

--先查找是否存在，不存在则插入  注意where中的主键位置--Exists的查询结果集的记录数大于0,就执行 

SetInitialDayPower='"+DayPowerSetValue+"',SetInitialMonthPower='"+MonthPowerSetValue+"',"            //+ "SetInitialYearPower='" + YearPowerSetValue + "',Operator_id='" + userId + "'         "if Exists(Select * from GWSPMD_StationParameter where Station_id='"+s_id+"') "            + " Update GWSPMD_StationParameter set SetInitialDayPower='"+DayPowerSetValue+"',SetInitialMonthPower="            +"'"+MonthPowerSetValue+"',SetInitialYearPower='" + YearPowerSetValue + "',Operator_id='" + userId + "' where Station_id='" + s_id + "'"            + " else INSERT into GWSPMD_StationParameter(Station_id,SetInitialDayPower,SetInitialMonthPower,SetInitialYearPower,Operator_id) "            +" values('" + s_id + "','" + DayPowerSetValue + "','" + MonthPowerSetValue + "','" + YearPowerSetValue + "','" + userId + "')" ;

 

9、

--在向指定日期加上一段时间的基础上，返回新的 datetime 值。DATEADD ( datepart , number, date )--只输出年月日：CONVERT(varchar(100),Emidtime, 23) as '时间' 输出格式：'2010-3-8 '--时间增加如：天数加1Convert(varchar(100),DATEADD ( day , 1, '2010-3-8'), 23)

<pre class="sql" name="code">--GetDate() 用于sql server :select GetDate()--DateDiff('s','2005-07-20','2005-7-25 22:56:32')返回值为 514592 秒--DateDiff('d','2005-07-20','2005-7-25 22:56:32')返回值为 5 天--DatePart('w','2005-7-25 22:56:32')返回值为 2 即星期一(周日为1，周六为7)DatePart('d','2005-7-25 22:56:32')返回值为 25即25号DatePart('y','2005-7-25 22:56:32')返回值为 206即这一年中第206天DatePart('yyyy','2005-7-25 22:56:32')返回值为 2005即2005年

10、

--sql时间格式转换大全使用 CONVERT： select convert(datetime ,'20080531',120)－－－字符转换成日期CONVERT (data_type[(length)], expression [, style])select CONVERT(varchar, getdate(), 120 ) 2004-09-12 11:06:08select replace(replace(replace(CONVERT(varchar, getdate(), 120 ),\'-\',\'\'),\' \',\'\'),\':\',\'\') 20040912110608select CONVERT(varchar(12) , getdate(), 111 ) 2004/09/12select CONVERT(varchar(12) , getdate(), 112 ) 20040912select CONVERT(varchar(12) , getdate(), 102 ) 2004.09.12select CONVERT(varchar(12) , getdate(), 101 ) 09/12/2004select CONVERT(varchar(12) , getdate(), 103 ) 12/09/2004select CONVERT(varchar(12) , getdate(), 104 ) 12.09.2004select CONVERT(varchar(12) , getdate(), 105 ) 12-09-2004select CONVERT(varchar(12) , getdate(), 106 ) 12 09 2004select CONVERT(varchar(12) , getdate(), 107 ) 09 12, 2004select CONVERT(varchar(12) , getdate(), 108 ) 11:06:08select CONVERT(varchar(12) , getdate(), 109 ) 09 12 2004 1select CONVERT(varchar(12) , getdate(), 110 ) 09-12-2004select CONVERT(varchar(12) , getdate(), 113 ) 12 09 2004 1select CONVERT(varchar(12) , getdate(), 114 ) 11:06:08.177 sql语句得到一天，一周，一月 时间间隔//当天(OperateTime >=CONVERT(varchar(10),getDate(),120)+' 00:00:00' and OperateTime <=CONVERT(varchar(10),getDate(),120)+' 23:59:59')//近三天DateDiff(day,OperateTime,getdate()) <=3//本周sql数据库 Datepart(year,OperateTime)=DatePart(year,Getdate()) and DatePart(week,OperateTime)=DatePart(week,GetDate())Access 数据库Datepart('yyyy',OperateTime)=DatePart('yyyy',date()) and DatePart('ww',OperateTime)=DatePart('ww',Date())//本月Datepart(year,OperateTime)=DatePart(year,Getdate()) and DatePart(month,OperateTime)=DatePart(month,GetDate())OperateTime是表中日期字段

<pre class="sql" name="code">select   *   from   t       where   time   between   to_date('2004-05','yyyy-mm')   and   to_date('2004-09','yyyy-mm');  --返回日期中的日期部分 select DATEPART (day, '2008-08-08') --返回日期是周几 select DATEPART (weekday, '2008-08-08')SetVariable("%开始时间%",format([%开始时间%],"0000-00-00 00:00:00")) And SetVariable("%结束时间%",format([%结束时间%],"0000-00-00 00:00:00")) And FilterFor("时间",">=",[%开始时间%],"时间","<=",[%结束时间%])

11、

--随机取数据库前三条3条数据select top 3 Temp from  GWS_Test2    order by  Temp 

12、

--创建一个表，并分时段汇总，最后删除CREATE TABLE #tb(列名varchar(12),时间datetime)INSERT INTO #tbSELECT '03174190188','2009-11-01 07:17:39.217' UNION ALLSELECT '015224486575','2009-11-01 08:01:17.153' UNION ALLSELECT '013593006926','2009-11-12 08:04:46.560' UNION ALLSELECT '013599584239','2009-11-22 08:53:27.763' UNION ALLSELECT '013911693526','2009-11-23 08:53:51.683' UNION ALLSELECT '013846472440','2009-11-23 08:54:57.233' UNION ALLSELECT '013990353697','2009-11-24 08:55:25.077' UNION ALLSELECT '013990353697','2009-11-25 08:56:01.327' UNION ALLSELECT '013945594843','2009-11-26 08:57:02.233' UNION ALLSELECT '013990353697','2009-11-27 08:57:29.700' UNION ALLSELECT '013916597421','2009-11-28 08:59:49.390' UNION ALLSELECT '03916995857','2009-11-29 09:11:05.607' UNION ALLSELECT '015097712001','2009-11-30 09:13:50.293'--SQL查询如下:DECLARE @minDate datetime,@maxDate datetime;SELECT @minDate = '2009-11-1',@maxDate = '2009-12-01';DECLARE @sql varchar(8000);SET @sql = '';SELECT @sql=@sql+',SUM(CASE WHEN DATEDIFF(day,B.时间,'''                      +CONVERT(varchar(10),DATEADD(day,number,@minDate),120)                      +''')=0 THEN 1 ELSE 0 END) AS ['                        +CONVERT(varchar(10),DATEADD(day,number,@minDate),120)+']'FROM master.dbo.spt_values WHERE type = 'P' AND DATEADD(day,number,@minDate)<=@maxDate;DECLARE @cmd nvarchar(4000);SET @cmd = N'SELECT ISNULL(A.时段,''合计'') AS 时段'+@sql+',    COUNT(列名) AS 合计FROM(    SELECT 时段=RIGHT(100+number,2)+'':00~''+RIGHT(100+number+1,2)+'':00'',        MinDate = RIGHT(100+number,2)+'':00:00'',        MaxDate = RIGHT(100+number+1,2)+'':00:00''    FROM master.dbo.spt_values    WHERE type = ''P'' AND number < 24) AS A    LEFT JOIN (SELECT * FROM #tb                 WHERE 时间BETWEEN @minDate AND @maxDate) AS BON CONVERT(varchar(8),B.时间,108) >= A.MinDate    AND CONVERT(varchar(8),B.时间,108) < A.MaxDateGROUP BY A.时段WITH ROLLUP;'EXEC sp_executesql @cmd,N'@minDate datetime,@maxDate datetime',@minDate,@maxDate;DROP TABLE #tb;

13、

--SQL  --会删除原有表，新建一个表select distinct * into #Tmp from tableNamedrop table tableNameselect * into tableName from #Tmpdrop table #Tmp--删除相同项select distinct * into #DTTime from GWS_Inverterdrop table GWS_Inverterselect * into GWS_Inverter from #DTTimedrop table #DTTime

14、

--Access数据库时间查询 （where nDate>=#2010-05-05#）string sqlStr = "Select nDate,EnvirTemp, EnvirHumid, InsGlobalRadiation, tolGlobalRadiation, InsDirectRadiation,tolDirectRadiation FROM MeteTable where nDate>=#2010-05-05# ";sql="Select * FROM [new2010year] where 1=1 and Format([b_levetime],'YYYY-MM-DD')='2010-01-06' order by id desc"

15、

InvoiceDate between DateValue('2007-6-22') and DateValue('2007-06-24')--时间变量不能直接使用，必须用DateValue（）函数转换一下，才能被access正确识别出来------------------------Access时间查询SELECT MeteTable.nDate, MeteTable.EnvirTemp, MeteTable.EnvirHumid, MeteTable.InsGlobalRadiation, MeteTable.InsDirectRadiation, MeteTable.tolGlobalRadiation, MeteTable.tolDirectRadiationFROM MeteTableWHERE nDate between #2010-5-5 08:00:00# and #2010-5-5 09:00:00#;

16、

--写入变量 Access时间如beginDateT = BeginDate.Text;endDateT = BeginDate.Text + " 23:59:59";string sqlStr = "Select nDate,EnvirTemp, EnvirHumid, InsGlobalRadiation, tolGlobalRadiation, InsDirectRadiation,tolDirectRadiation FROM MeteTable" + " where nDate>= #"+beginDateT+"# and nDate<=#"+endDateT+"#";

17、

--从Access查询数据Select  Format(nDate,"yyyy-mm-dd"),EnvirTemp, EnvirHumid, InsGlobalRadiation, tolGlobalRadiation, InsDirectRadiation,tolDirectRadiation FROM MeteTable     where  Format(nDate,"yyyy-mm-dd");Select  Format(nDate,"yyyy-mm-dd"),Max(EnvirTemp),Min(EnvirTemp),AVG(EnvirTemp),Max(InsGlobalRadiation),AVG(InsGlobalRadiation),Sum(tolGlobalRadiation),Max(InsDirectRadiation),AVG(InsDirectRadiation),Sum(tolDirectRadiation)  FROM MeteTablewhere  Format(nDate,"yyyy-mm-dd")order by  Format(nDate,"yyyy-mm-dd");SELECT Format(nDate,"yyyy-mm-dd") AS 表达式1, Max(MeteTable.EnvirTemp) AS EnvirTemp之最大值, Min(MeteTable.EnvirTemp) AS EnvirTemp之最小值, Avg(MeteTable.EnvirTemp) AS EnvirTemp之平均值, Max(MeteTable.InsGlobalRadiation) AS InsGlobalRadiation之最大值, Avg(MeteTable.InsGlobalRadiation) AS InsGlobalRadiation之平均值, Sum(MeteTable.tolGlobalRadiation) AS tolGlobalRadiation之总计, Max(MeteTable.InsDirectRadiation) AS InsDirectRadiation之最大值, Avg(MeteTable.InsDirectRadiation) AS InsDirectRadiation之平均值, Sum(MeteTable.tolDirectRadiation) AS tolDirectRadiation之总计FROM MeteTableWHERE (((Format([nDate],"yyyy-mm-dd"))<>False))GROUP BY Format(nDate,"yyyy-mm-dd");

18、

--SQL里面的convert函数，在Access中用什么--SQL中convert(char(10), Operation_DateTime,120)>='" + DateTime.Parse(Q_FirstDate.EditValue.ToString()).ToString("yyyy-MM-dd") + "' and convert(char(10), Operation_DateTime,120) <='" + DateTime.Parse(Q_EndDate.EdITValue.ToString()).ToString("yyyy-MM-dd") + "'--C# code  Access中format(Operation_DateTime,'yyyy-mm-dd')>='"     + DateTime.Parse(Q_FirstDate.EditValue.ToString()).ToString("yyyy-MM-dd")     + "' and format(Operation_DateTime,'yyyy-mm-dd') <='"     + DateTime.Parse(Q_EndDate.EdITValue.ToString()).ToString("yyyy-MM-dd") + "'

19、

在Access中先自己定义一个显示函数，然后使用函数转换Function ConvertDateToNumeric(pDate As Date) As Long    Dim LYear As Integer    Dim LMth As Integer    Dim LDay As Integer    'Extract the year, month, and day values from the date parameter called pDate    LYear = DatePart("yyyy", pDate)    LMth = DatePart("m", pDate)    LDay = DatePart("d", pDate)    'Format new number as a ddmmyyyy value    ConvertDateToNumeric = Right("00" & CStr(LDay), 2) & Right("00" & CStr(LMth), 2) & CLng(CStr(LYear))End Functionformat(10.666666667, "##.00 ") VBA 函数Transact-SQL标量函数Ccur(x)CONVERT(money,x)Cdbl(x)CONVERT(float,x)Cint(x)CONVERT(smallint,x)Clng(x)CONVERT(int,x)Csng(x)CONVERT(real,x)Cstr(x)CONVERT(varchar,x)Cvdate(x)CONVERT(datetime,x)

 

 

 

</pre><p align="left"> </p><p> </p><p><span style="font-family:Courier New;font-size:18px;BACKGROUND-COLOR: #f0f0f0"></span> </p><pre class="sql" name="code"><span style="font-size:18px;"></span>