SQL学习（时间，存储过程，触发器）

SQL学习

  1. 几个操作时间的函数 
     1 --datapart 获取时间中的年月日时分秒等部分
 2 select DATEPART(year,current_timestamp);
 3 select DATEPART(DAY,current_timestamp);
 4 select DATEPART(MONTH,current_timestamp);
 5 
 6 --dateadd 在相应时间上加上年月日时分秒等
 7 select CURRENT_TIMESTAMP,DATEADD(DAY,10,CURRENT_TIMESTAMP);
 8 select DATEADD(month,11,'2001-2-28 12:00:00') as 上帝时刻;
 9 
10 --datediff 获取两时间段的差值并换算为时分秒年月日等
11 select DATEDIFF(month,'2014-8-3','2015-9-10');
12 
13 --转换函数 转换类型
14 --cast   convert
15 
16 select '123'+456;
17 select '123'+cast(456 as varchar);
18 select '123'+CONVERT(varchar,456);
19 
20 --convert时间类型转换 后面的数字即不同地区的时间表示方式
21 select CURRENT_TIMESTAMP,
22 CONVERT(VARCHAR,CURRENT_TIMESTAMP,111);--中国时间表示
23 
24 select CURRENT_TIMESTAMP,
25 CONVERT(VARCHAR,CURRENT_TIMESTAMP,110);--美国时间表示

     

  2. 简单练习 
     1 --练习题
 2 use TextDB
 3 create table TB_CallRecord
 4 (
 5 Id int not null identity(1,1),
 6 CallNumber nvarchar(50),
 7 TelNum varchar(50),
 8 StartDateTime datetime null,
 9 EndDateTime datetime null
10 )
11 --主键约束
12 alter table TB_CallRecord
13 add constraint PK_CallRecord primary key (Id);
14 --检查约束
15 alter table TB_CallRecord
16 add constraint CK_CallRecords check(CallNumber like '[0-9][0-9][0-9]')
17 
18 alter table TB_CallRecord
19 add constraint CK_CallRecords_EndDateTime check(EndDateTime > StartDateTime)
20 
21 --默认约束
22 alter table TB_CallRecord
23 add constraint DF_CallRecords default(getdate()) for EndDateTime
24 
25 
26 
27 INSERT TB_CallRecord ([CallNumber], [TelNum], [StartDateTime], [EndDateTime]) VALUES ('001', '0208888888', CAST(0x00009DAF00A4CB80 AS DateTime), CAST(0x00009DAF00A62E94 AS DateTime));
28 INSERT TB_CallRecord ([CallNumber], [TelNum], [StartDateTime], [EndDateTime]) VALUES ('001', '0208888888', CAST(0x00009DB000D63BC0 AS DateTime), CAST(0x00009DB000D68DC8 AS DateTime));
29 INSERT TB_CallRecord ([CallNumber], [TelNum], [StartDateTime], [EndDateTime]) VALUES ('001', '89898989', CAST(0x00009DB000E85C60 AS DateTime), CAST(0x00009DB000E92F50 AS DateTime));
30 INSERT TB_CallRecord ([CallNumber], [TelNum], [StartDateTime], [EndDateTime]) VALUES ('002', '98987676', CAST(0x00009DB2015BB7A0 AS DateTime), CAST(0x00009DB2015C4DA0 AS DateTime));
31 INSERT TB_CallRecord ([CallNumber], [TelNum], [StartDateTime], [EndDateTime]) VALUES ('002', '02188839389', CAST(0x00009DA4014C9C70 AS DateTime), CAST(0x00009DA4014E0308 AS DateTime));
32 INSERT TB_CallRecord ([CallNumber], [TelNum], [StartDateTime], [EndDateTime]) VALUES ('001', '767676766', CAST(0x00009DB400DAA0C0 AS DateTime), CAST(0x00009DB400DD5FE0 AS DateTime));
33 INSERT TB_CallRecord ([CallNumber], [TelNum], [StartDateTime], [EndDateTime]) VALUES ('003', '0227864656', CAST(0x00009DB200B9AB40 AS DateTime), CAST(0x00009DB200B9FC1C AS DateTime));
34 INSERT TB_CallRecord ([CallNumber], [TelNum], [StartDateTime], [EndDateTime]) VALUES ('003', '676765777', CAST(0x00009DB8014042B8 AS DateTime), CAST(0x00009DB80141804C AS DateTime));
35 INSERT TB_CallRecord ([CallNumber], [TelNum], [StartDateTime], [EndDateTime]) VALUES ('001', '89977653', CAST(0x00009D9A00FB9898 AS DateTime), CAST(0x00009D9A00FE6118 AS DateTime));
36 INSERT TB_CallRecord ([CallNumber], [TelNum], [StartDateTime], [EndDateTime]) VALUES ('004', '400400400', CAST(0x00009D9A00FB9898 AS DateTime), CAST(0x00009D9A00FE6118 AS DateTime));
37 
38 --查看全表
39 select * from TB_CallRecord;
40 
41 --输出通话时间最长的五条记录
42 select top 5 *,DATEDIFF(SECOND,StartDateTime,EndDateTime) as 通话时长 from TB_CallRecord order by DATEDIFF(SECOND,StartDateTime,EndDateTime) DESC; 
43 
44 --输出所有数据中拨打长途号码（对方号码以0开头）的总时长。like、sum
45 SELECT SUM(DATEDIFF(SECOND,StartDateTime,EndDateTime)) from TB_CallRecord WHERE TelNum like '0%';
46 
47 --输出通话总时长最多的前三个呼叫员的编号。
48 select top 3 Id,DATEDIFF(SECOND,StartDateTime,EndDateTime) as 通话时长 from TB_CallRecord order by DATEDIFF(SECOND,StartDateTime,EndDateTime) DESC; 
49 
50 --输出本月拨打电话次数最多的前三个呼叫员的编号.group by,count(*)
51 select top 3 CallNumber,Id,StartDateTime From TB_CallRecord where DATEPART(MONTH,StartDateTime) = 7 order by CallNumber DESC;
52 
53 
54 --表序列化row_number()over(order by 字段)  就是将不连续的表依据某一列值排序
55 select ROW_NUMBER()over(order by Id) as 序列化 ,*from TB_CallRecord;

     

  3. 事务 
     1 --事务：SQL中每一条语句都是一个事务，任何错误都会导致整个事务失败
    　　--语法：
       /*
    　　begin transaction
    　　  代码
       end
       */
    
 2 begin transaction
 3 declare @myError int;
 4 update TextDB..TB_CallRecord set CallNumber = 0127897789 where CallNumber = '004';
 5 set @myError = (select @@ERROR);
 6 update TextDB..TB_CallRecord set CallNumber = '005'  where Id = 10;
 7 set @myError += (select @@ERROR);
 8 if(@myError!=0)
 9 begin
10 rollback transaction  --回滚当前的操作
11 end
12 else
13 begin
14 commit transaction    --执行当前的操作
15 end
16 --事务的特征：如果一个事务满足原子性，持久性，隔离性，一致性，那么这个操作则称为事务。
17 
18 
19 --begin transaction
20 
21 select *from TextDB..TB_CallRecord;

     

  4. 存储过程 
      1 --存储过程
  2 --语法：
  3 /*
  4     create proc[edure] 存储过程名字
  5     参数 as 类型 [默认值|out]
  6     as
  7     begin
  8         代码
  9     end
 10 */
 11 --例如：
 12 go
 13 create proc usp_text
 14 as
 15 begin
 16 begin transaction
 17     declare @myError int;
 18     update TextDB..TB_CallRecord set CallNumber = '008' where CallNumber = '004';
 19     set @myError = (select @@ERROR);
 20     update TextDB..TB_CallRecord set CallNumber = '005'  where Id = 10;
 21     set @myError += (select @@ERROR);
 22     if(@myError!=0)
 23 begin
 24     rollback transaction
 25 end
 26     else
 27 begin
 28     commit transaction
 29 end
 30 
 31 end
 32 --执行存储过程
 33 exec usp_text;
 34 
 35 select * from TextDB..TB_CallRecord;
 36 
 37 
 38 --带参数的存储过程
 39 go
 40 create proc usp_text2
 41 @oldnum as nvarchar(50)
 42 ,@newnum as nvarchar(50) 
 43 as
 44 begin
 45     update TextDB..TB_CallRecord set CallNumber = @newnum where CallNumber = @oldnum;
 46 end
 47 
 48 --调用带参数的存储过程
 49 exec usp_text2 '005','008';
 50 select * from TextDB..TB_CallRecord
 51 
 52 
 53 --带参数和返回值的存储过程
 54 go
 55 create proc usp_text3
 56 @oldnum as nvarchar(50)
 57 ,@newnum as nvarchar(50) 
 58 ,@isSuccess int output   --使用output将函数内参数抛出给外部
 59 as
 60 begin
 61     declare @myError int
 62         update TextDB..TB_CallRecord set CallNumber = @newnum where CallNumber = @oldnum;
 63         set @myError = (select @@ERROR);
 64     if(@myError=0)
 65     begin
 66         commit
 67         set @isSuccess = 1
 68     end
 69     else
 70     begin
 71         rollback
 72         set @isSuccess = 0;
 73     end
 74 end
 75 
 76 --调用带参数和返回值的存储过
 77 declare @result int
 78 exec usp_text3 '008','004',@result output;
 79 select @result; 
 80 
 81 --使用try catch
 82 go
 83 create proc usp_text4
 84 @oldnum as nvarchar(50)
 85 ,@newnum as nvarchar(50)
 86 ,@IsSucess  int output
 87 as
 88 begin
 89     begin transaction
 90     update TextDB..TB_CallRecord 
 91     set CallNumber = @newnum 
 92     where CallNumber = @oldnum
 93     begin try
 94         commit
 95         set @IsSucess = 1;
 96     end try
 97     begin catch
 98         rollback
 99         set @IsSucess = 0;
100     end catch
101 end
102 
103 --调用带有try，catch的存储过程
104 declare @result int
105 exec usp_text4 '004','007',@result output;
106 select @result;

     

  5. 触发器 
     1 --触发器
 2 --语法
 3 /*
 4 create trigger tr_类型触发器名字 on 表名
 5 触发类型：after|instea of
 6 操作类型：inser|delete|update
 7 as 
 8 begin
 9     代码
10 end
11 */
12 
13 --案例
14 --插入数据的同时获得自动增长的Id
15 select * from TextDB..TB_USER;
16 
17 insert INTO TextDB..TB_USER (userID,[password],code,lastTime)
18 OUTPUT inserted.userID
19 VALUES ('aaa','DDD','23','2007-03-12');
20 
21 -----------
22 go 
23 create trigger tr_delete_不会删除的表 on TextDB..TB_USER
24 after
25 delete
26 as
27      insert into TextDB..TB_USER(userID,[password],code,lastTime)
28      select userID,[password],code,lastTime from deleted;
29 go
30 
31 
32 select * from TextDB..TB_USER;
33 delete TB_USER
34 select SUSER_NAME();

    学习于蒋坤老师视频教程

posted @ 2015-07-05 17:24  世纪末の魔术师  阅读(673)  评论(0)  编辑  收藏  举报