代码改变世界

9月23日培训日记

2006-09-26 22:11  java ee spring  阅读(148)  评论(0编辑  收藏  举报

今天由袁龙友老师继续讲数据库,我没有去听课,下面是张挺记录的培训日记。

回顾上次作业

根据上次留下的作业,回顾了建两个表,表间互相引用以及主外键约束,其中约束又分为表级约束和列级约束。怎样使一段脚本可以重复执行?需要先判断是否已经存在创建的数据库或者表,如果有,则先删除,但是考虑一种情况,两表间互相存在引用,则删不掉。此时正确的处理方式应该是,先删掉约束,再删表,具体办法见上次课的笔记。

 

T-SQL编程

Sql Server中的系统函数:时间和日期函数是比较重要的,在编程中经常会使用。

(1)       GetDate()

(2)       DateAdd()

(3)       DateName()DatePart()的区别?前者返回字符串,后者返回整数

(4)       DateDiff()

 

一个具体问题:

A是一个不断有新记录增加的表,字段t是记录插入的时间,现在想知道每10秒钟插入到该表的记录总数大于N条的时间和记录条数,并按由大到小的顺序排列,请写出完成该功能的语句。

解决思路:将开始插入的时间定为基准时间,求出每个时间与基准时间相差的秒数比上10的商,用求出的商来分组。

答案:select max(t) as maxT,min(t) as minT,count(*) as Num from dt group by datediff(ss,’2006-9-23’,t)/10 having count(*)>N order by desc

如果用定义变量的方式,可以先定义一个基准时间@Start

declare @start datetime

select @start=min(t) from dt

……

 

作业:

SQL语句求出一个选手的得分(10个评委,去掉一个最高分和一个最低分,再求平均分)

思路1:先定义两个变量最高分和最低分的id,排除这两个id,再求平均值

思路2:不用定义变量,总分减去一个最高分和一个最低分再除8

 

局部变量:储存临时值的变量

 

会话级的变量:通过classpath对比讲解,一个会话中的变量,不影响另外的会话

 

全局变量:全局的变量,影响整个库,如@@RowCount这个系统变量的值,会随着SQL语句的执行而改变

 

Sql Server中不能由用户定义全局变量,标准的SQL中定义了标准的全局变量,但是各个厂家的实现中,决大多数都不是通用的。

 

实现分页的SQL语句:(每页显示3条记录)

select top 3 * from grade where id not in (select top 3 ID from grade)

 

通用形式:

declare @pageIndex int, @pageSize int

set @pageSize=3    set @pageIndex=2

select top(@pageSize) * from grade where id not in(select top(@pageIndex-1)* @pageSize) ID from grade

declare @RecordNum int

select @RecordNum=Count(*) from grade

 

可以只用一条SQL语句实现分页,其策略为:

select * from grade select @@RowCount

(MySQL中分页可以使用limit限制取记录的行号)

使用top 分页,如果记录很多,越到后面分页越慢,性能差

 

跳页时选择性能好的分页

declare @id int

select top 3 @id=id from grade

select top 3 * from grade where id>@id

 

使用会话级变量

declare @id int

set RowCount 6

select @id=id from grade

select top 3 * from grade where id>@id(这样比子查询快很多)

 

会话级变量如果设置为0,则表示无此变量

 

查询数据远比传输数据的速度快,由此引出了面向连接的数据库编程的概念

如果将查询到的结果马上送入ResultSet,则是面向无连接的数据库编程,这样做的缺点是占内存资源

 

写脚本完成学生选课的功能,表结构如下图:

查学生ID使用全局变量@@Identity,该全局变量显示标识列的值,向标识列插入数据,需要将变量Indetity_insert设置为on

例如,set IDENTITY_INSERT grade on

可以设置标识列从某个值开始编号,使用DBCC CHECKIDENT语句,例如,从200开始编号,则dbcc checkident(grade,RESEED,199)

 

全局变量 @@error

每条SQL语句执行后,都要检查@@error变量,增,删,改的SQL语句执行后,系统都会设置@@error的值。

如:

if @@error=0,表示语句正确,则继续进行以后的语句,否则将破坏数据的完整性。

 

SQL语句执行后,要不要输出@@RowCount变量的值,可以设置set nocount on,这样就会关闭回显功能,但是还是会将值赋给@@RowCount变量。

 

SQL SERVER中引入了TRY……CATCH语句块

CATCH中可以用特定的系统函数(见帮助)

 

临时表及表级变量

变量中存单个的值,如果要存集合,可以存到表中,并且可以使用临时表,表名前有字符”#”,临时表保存在tempDB数据库中。会话窗口关闭时,临时表被删除。

临时表分全局临时表和会话级临时表,全局临时表前面有”##”,所有的会话军共享全局临时表。

临时表是有编号的。建临时表不要建索引。

临时表的应用:

应用临时表实现分页处理(百度就是用这种方式)

前面的几种方式每翻一页要查两次数据库,这种方式每增加一页查一次数据库。

create table #PageTable

(

    NewID int identity(1,1),

    ID int

)

insert into #PageTable(ID) select ID from Grade select a.* from Grade a join(select ID from #PageTable where NewID between 3 and 5) b on a.ID=b.ID

前面讲的几种方式要求表有单一主键,如果想按name页排序分页,将不能实现,应用临时表可解决该问题。

临时表也要写磁盘,比写内存要慢,考虑到性能,可以使用表级变量,定义表级变量:

Declare @Table Table

(

    NewID int identity(1,1),

    ID int

)

使用表级变量与临时表相同。

 

系统函数(字符串函数)

(1)       验证身份证号(分18位和15位两种,年龄在18-60岁之间)

使用逻辑表达式,

If(len(cardNo=15 and datediff(yyyy,subString(cardNo,7,6),getdate())>18 and datediff(yyyy,subString(cardNo,7,6),getdate())<60) or len(cardNo=18……)

 

图书馆借书的实际案例

一个图书馆借书的规定是:科技类的书可以借30天,文艺类的45天,其它的书20

SQL语句查询出,三天之类即将过期的图书的借阅记录。

Declare @userID int,@bookType varchar(20),@H_Date datetime

If @bookType=’文艺’ select @H_Date=DateAdd(dd,getDate(),45)

Else if ……

 

Case……when语句

Select Name,(case when sex=1 then ‘’ else ‘’end) as sex from student]