Sql基本操作

1. 语句创建表

经常用语句操作数据库，比如创建表，在此写下常用的语句，备忘。

在语句中创建主键，并自增长1：

Create table ObjectInfo
(
ID INT not null primary key identity(1,1),
ObjName varchar(100),
ObjDesc varchar(100),
OStatus varchar(5),
ActionDate date,
OrderID int ,
OType varchar(10)

)

View Code

2. 创建方法：标量值函数

CREATE FUNCTION [dbo].[fGetAbnormalCount](@staffId INT, @startDate date, @endDate date, @type int)
RETURNS INT
BEGIN
DECLARE @COUNT INT
IF @type = 1
BEGIN 
  SET @COUNT = (SELECT COUNT(1) from  HMP_STAFF_WORKTIME where ID IN (
SELECT ID FROM HMP_STAFF_WORKTIME WHERE StaffID = @staffId and WorkDate BETWEEN @startDate AND @endDate
 and CheckTime in(select MIN(CheckTime) from HMP_STAFF_WORKTIME group by WorkDate ))
 AND CheckTime > (SELECT StartTimeAM FROM HMP_WORKTIME WHERE WorkDate BETWEEN EnableDate AND DisAbleDate)
      AND dbo.fJudgeWorkDay(@staffId, WorkDate) = 1 )
  +(SELECT COUNT(1) from  HMP_STAFF_WORKTIME where ID IN (
SELECT ID FROM HMP_STAFF_WORKTIME WHERE StaffID = @staffId and WorkDate BETWEEN @startDate AND @endDate
 and CheckTime in(select MIN(CheckTime) from HMP_STAFF_WORKTIME group by WorkDate ) )
 AND CheckTime > (SELECT StartTimeAM FROM HMP_WORKTIME WHERE WorkDate BETWEEN EnableDate AND DisAbleDate)
      AND dbo.fJudgeWorkDay(@staffId, WorkDate) = 2)
      + (SELECT COUNT(1) from  HMP_STAFF_WORKTIME where ID IN (
SELECT ID FROM HMP_STAFF_WORKTIME WHERE StaffID = @staffId and WorkDate BETWEEN @startDate AND @endDate
 and CheckTime in(select MIN(CheckTime) from HMP_STAFF_WORKTIME group by WorkDate ) )
 AND CheckTime > (SELECT StartTimePM FROM HMP_WORKTIME WHERE WorkDate BETWEEN EnableDate AND DisAbleDate)
      AND dbo.fJudgeWorkDay(@staffId, WorkDate)= 3)
END
ELSE IF @type = 2 --早退
BEGIN
  SET @COUNT = (SELECT COUNT(1) from  HMP_STAFF_WORKTIME where ID IN (
SELECT ID FROM HMP_STAFF_WORKTIME WHERE StaffID = @staffId and WorkDate BETWEEN @startDate AND @endDate
 and CheckTime in(select MAX(CheckTime) from HMP_STAFF_WORKTIME group by WorkDate ) )
 AND CheckTime < (SELECT EndTimeAM FROM HMP_WORKTIME WHERE WorkDate BETWEEN EnableDate AND DisAbleDate)
      AND dbo.fJudgeWorkDay(@staffId, WorkDate) = 2)
      +(SELECT COUNT(1) from  HMP_STAFF_WORKTIME where ID IN (
SELECT ID FROM HMP_STAFF_WORKTIME WHERE StaffID = @staffId and WorkDate BETWEEN @startDate AND @endDate
 and CheckTime in(select MAX(CheckTime) from HMP_STAFF_WORKTIME group by WorkDate ) )
 AND CheckTime < (SELECT EndTimePM FROM HMP_WORKTIME WHERE WorkDate BETWEEN EnableDate AND DisAbleDate)
  AND dbo.fJudgeWorkDay(@staffId, WorkDate) = 1)
      +
        (SELECT COUNT(1) from  HMP_STAFF_WORKTIME where ID IN (
SELECT ID FROM HMP_STAFF_WORKTIME WHERE StaffID = @staffId and WorkDate BETWEEN @startDate AND @endDate
 and CheckTime in(select MAX(CheckTime) from HMP_STAFF_WORKTIME group by WorkDate ) )
 AND CheckTime < (SELECT EndTimePM FROM HMP_WORKTIME WHERE WorkDate BETWEEN EnableDate AND DisAbleDate)
 AND dbo.fJudgeWorkDay(@staffId, WorkDate) = 3)
END
ELSE --旷工
BEGIN 
DECLARE @NUM INT, @DATE DATE, @DAYCOUNT INT
SET @DATE = @STARTDATE
SET @NUM = 0
WHILE @DATE <= @ENDDATE
BEGIN
  SET @DAYCOUNT =( SELECT COUNT(1) FROM HMP_STAFF_WORKTIME WHERE @StaffID = @staffId and WorkDate =@DATE
  AND  dbo.fJudgeWorkDay(@staffId, WorkDate) IN(1,2,3))
  IF @DAYCOUNT = 0
  BEGIN  SET @NUM = @NUM +1 END
  
  SET @DATE = DATEADD(DAY, 1,@DATE)
END
SET @COUNT = @NUM
--  SET @COUNT = (SELECT DATEDIFF([DAY], @startDate,@endDate)-
--(select  COUNT( distinct WorkDate) from HMP_STAFF_WORKTIME where StaffID = 
--@staffId and WorkDate between @startDate and @endDate))
END
RETURN @COUNT
END

　调用：

SELECT  [dbo].[fGetAbnormalCount](123, '20130803','20140803' , 1)

　　表值函数：

  1 CREATE FUNCTION [dbo].[fGetAttenDetailByID](@staffID INT, @startDate date, @endDate date)
  2 RETURNS TABLE
  3 RETURN (SELECT ID, StaffNum, StaffName, (SELECT CAMPUSNAME FROM HMP_CAMPUS_INFO WHERE ID = EntryCampus)EntryCampus, (SELECT CAMPUSNAME FROM HMP_CAMPUS_INFO WHERE ID = WORKCAMPUS)WorkCampus, (select DepartName from HMP_DEPARTMENT_INFO where ID = DepartmentID)DepartmentID,
  4 (select ParamValue from HMP_PARAM where ID = Post) Post, (select COUNT(1) from ((SELECT WorkDate FROM HMP_STAFF_WORKTIME WHERE StaffID = HMP_STAFF_INFO.ID AND WORKDATE BETWEEN @startDate AND @enddate GROUP BY WorkDate) )A) 'attendance',
  5  (SELECT COUNT(1) FROM HMP_DUTY_ARRANGE WHERE STAFFID = HMP_STAFF_INFO.ID AND DUTYDATE BETWEEN @startDate AND @enddate) 'onduty',
  6  (SELECT dbo.fGetAbnormalCount(@staffID, @startDate, @ENDDATE, 1)) 'Late',
  7  (SELECT dbo.fGetAbnormalCount(@staffID, @startDate, @ENDDATE, 2)) 'EarlyLeave',
  8  (SELECT dbo.fGetAbnormalCount(@staffID, @startDate, @ENDDATE, 3)) 'WorkAbsence',
  9 (SELECT COUNT(1) FROM HMP_UNREGISTER WHERE ADDER = @STAFFID AND TEMPLATEID = 1 AND UNREGDATE BETWEEN @startDate AND @enddate AND STATUS = 1) 'PublicLackCheck',
 10 (SELECT COUNT(1) FROM HMP_UNREGISTER WHERE ADDER = @STAFFID AND TEMPLATEID = 2 AND UNREGDATE BETWEEN @startDate AND @enddate AND STATUS = 1)  'privateLackCheck',
 11  (select cast(COUNT(1) /2.0 as numeric(5,1)) from HMP_IRREGULAR_DATE where  StaffID = HMP_STAFF_INFO.ID AND ISWORK = 0 AND IrregularDate BETWEEN @startDate AND @enddate) 'Vocation',
 12  (select cast(COUNT(1) /2.0 as numeric(5,1)) from HMP_IRREGULAR_DATE where  StaffID = HMP_STAFF_INFO.ID AND ISWORK = 0 AND IrregularDate BETWEEN @startDate AND @enddate) 'Work' 
 13  FROM HMP_STAFF_INFO WHERE ID = @staffID)
 14 　为表赋值：
 15 
 16 CREATE FUNCTION [dbo].[fGetDtAbnormalList](@staffId INT, @startDate date, @endDate date, @ID INT)
 17 RETURNS @return_table TABLE
 18         (r_date DATE, r_name varchar(50), r_des varchar(10), r_detail varchar)
 19 BEGIN
 20 DECLARE @DateIndex DATE, @TYPE INT, @WorkStatus INT, @TimeSpan INT, @CHECKTIME VARCHAR(50)--, @NORMALTIME VARCHAR, 
 21 DECLARE  @typename varchar(50)
 22 SET @DateIndex = @STARTDATE
 23 SET @TYPE = (SELECT [TYPE] FROM HMP_ABNORMAL_TEMPLATE WHERE ID = @ID)
 24 SET @typename = (SELECT TNAME FROM HMP_ABNORMAL_TEMPLATE WHERE ID = @ID)
 25 WHILE @DateIndex < = @ENDDATE
 26  BEGIN
 27     SET @WORKSTATUS = (SELECT DBO.fJudgeIsAbnormal(@staffId,@DateIndex))
 28     IF ((SELECT COUNT(1) FROM HMP_UNREGISTER WHERE UnregDate = @DateIndex AND STATUS = 1) = 0) --缺卡的不统计在内
 29     BEGIN
 30     IF @TYPE = 1 --迟到
 31     BEGIN
 32         IF @WORKSTATUS = 11 OR @WORKSTATUS = 21 OR
 33            @WORKSTATUS = 14 OR @WORKSTATUS = 24 --全天上班或者上午上班 迟到
 34           BEGIN 
 35           ----迟到时间
 36           --SET @CHECKTIME = (SELECT MIN(CHECKTIME) FROM HMP_STAFF_WORKTIME WHERE StaffID = @staffId AND WorkDate = @ENDDATE)
 37           ----正常打卡时间
 38           --SET @NORMALTIME = (SELECT StartTimeAM FROM HMP_WORKTIME WHERE WorkDate BETWEEN ENABLEDATE AND DISABLEDATE)
 39             SET @TIMESPAN = (select datediff(minute,(SELECT StartTimeAM FROM HMP_WORKTIME WHERE  @DateIndex > ENABLEDATE AND @DateIndex < DISABLEDATE)
 40                         ,
 41                         (SELECT MIN(CHECKTIME) FROM HMP_STAFF_WORKTIME WHERE StaffID = @staffId AND WorkDate = @DateIndex )))
 42             IF @TIMESPAN > (SELECT StartTime FROM HMP_ABNORMAL_TEMPLATE WHERE ID =@ID) AND  @TIMESPAN <= (SELECT ENDTime FROM HMP_ABNORMAL_TEMPLATE WHERE ID =@ID) --大于开始，小于等于结束；临界点属于前一个区间
 43             BEGIN
 44                 SET @CHECKTIME = (SELECT MIN(CHECKTIME) FROM HMP_STAFF_WORKTIME WHERE StaffID = @staffId AND WorkDate = @DateIndex)
 45                 INSERT @return_table(r_date, r_name, r_des, r_detail)VALUES(@DateIndex, @typename, @CHECKTIME, '')
 46             END
 47           END
 48        ELSE IF @WORKSTATUS = 31 OR @WORKSTATUS = 34
 49         BEGIN
 50           SET @TIMESPAN = (select datediff(minute,(SELECT StartTimePM FROM HMP_WORKTIME WHERE @DateIndex > ENABLEDATE AND @DateIndex < DISABLEDATE)
 51                         ,
 52                         (SELECT MIN(CHECKTIME) FROM HMP_STAFF_WORKTIME WHERE StaffID = @staffId AND WorkDate = @DateIndex )))
 53             IF @TIMESPAN > (SELECT StartTime FROM HMP_ABNORMAL_TEMPLATE WHERE ID =@ID) AND  @TIMESPAN <= (SELECT ENDTIME FROM HMP_ABNORMAL_TEMPLATE WHERE ID =@ID) 
 54             BEGIN
 55                 SET @CHECKTIME = (SELECT MIN(CHECKTIME) FROM HMP_STAFF_WORKTIME WHERE StaffID = @staffId AND WorkDate = @DateIndex)
 56                 INSERT @return_table(r_date, r_name, r_des, r_detail)VALUES(@DateIndex, @typename, @CHECKTIME, '')
 57             END
 58         END
 59     END
 60     ELSE IF @TYPE = 2
 61      BEGIN
 62            IF @WORKSTATUS = 12 OR @WORKSTATUS = 32 OR
 63                @WORKSTATUS = 14 OR @WORKSTATUS = 34 --全天上班或者下午上班 早退
 64           BEGIN 
 65             SET @TIMESPAN = (select datediff(minute,(SELECT MAX(CHECKTIME) FROM HMP_STAFF_WORKTIME WHERE StaffID = @staffId AND WorkDate = @DateIndex )
 66                             ,
 67                             (SELECT EndTimePM FROM HMP_WORKTIME WHERE @DateIndex > ENABLEDATE AND @DateIndex < DISABLEDATE)))
 68                 IF @TIMESPAN > (SELECT StartTime FROM HMP_ABNORMAL_TEMPLATE WHERE ID =@ID) AND  @TIMESPAN <= (SELECT ENDTime FROM HMP_ABNORMAL_TEMPLATE WHERE ID =@ID) 
 69                 BEGIN
 70                     SET @CHECKTIME = (SELECT MAX(CHECKTIME) FROM HMP_STAFF_WORKTIME WHERE StaffID = @staffId AND WorkDate = @DateIndex)
 71                     INSERT @return_table(r_date, r_name, r_des, r_detail)VALUES(@DateIndex, @typename, @CHECKTIME, '')
 72                 END
 73               END
 74            ELSE IF @WORKSTATUS = 21 OR @WORKSTATUS = 24
 75             BEGIN
 76               SET @TIMESPAN = (select datediff(minute,(SELECT MAX(CHECKTIME) FROM HMP_STAFF_WORKTIME WHERE StaffID = @staffId AND WorkDate = @DateIndex )
 77                             ,
 78                             (SELECT ENDTimeAM FROM HMP_WORKTIME WHERE @DateIndex BETWEEN ENABLEDATE AND DISABLEDATE)))
 79                 IF @TIMESPAN > (SELECT StartTime FROM HMP_ABNORMAL_TEMPLATE WHERE ID =@ID) AND  @TIMESPAN <= (SELECT ENDTime FROM HMP_ABNORMAL_TEMPLATE WHERE ID =@ID) 
 80                 BEGIN
 81                     SET @CHECKTIME = (SELECT MIN(CHECKTIME) FROM HMP_STAFF_WORKTIME WHERE StaffID = @staffId AND WorkDate = @DateIndex)
 82                     INSERT @return_table(r_date, r_name, r_des, r_detail)VALUES(@DateIndex, @typename, @CHECKTIME, '')
 83                 END
 84             END
 85     END
 86     ELSE IF @TYPE = 3--旷工
 87       BEGIN
 88           IF @WORKSTATUS = 13 OR @WORKSTATUS = 23 OR
 89            @WORKSTATUS = 33
 90           BEGIN 
 91              INSERT @return_table(r_date, r_name, r_des, r_detail)VALUES(@DateIndex, @typename, '', '')          
 92           END
 93       END
 94     ELSE  --没有对应的记录
 95       RETURN
 96         END
 97        SET @DateIndex = (SELECT DATEADD(DAY,1,@DateIndex))
 98  END
 99 RETURN
100 END

View Code

　　3. 添加字段

alter table HISTORY_CLASS_SINGLE_OPERATE
add EFieldName varchar(20)

　　4. 删除字段

alter table HISTORY_CLASS_SINGLE_OPERATE
drop EFieldName varchar(20)

　　5. 修改字段名称

exec sp_rename '[dbo].[HISTORY_CLASS_SINGLE_OPERATE].FieldName', 'CFieldName'