Archive for July, 2005

SQL Server数据库性能优化技术

Friday, July 22nd, 2005
设计1个应用系统似乎并不难,但是要想使系统达到最优化的性能并不是一件容易的事。在开发工具、数据库设计、应用程序的结构、查询设计、接口选择等方面有多种选择,这取决于特定的应用需求以及开发队伍的技能。本文以SQL Server为例,从后台数据库的角度讨论应用程序性能优化技巧,并且给出了一些有益的建议。

1 数据库设计
  要在良好的SQL Server方案中实现最优的性能,最关键的是要有1个很好的数据库设计方案。在实际工作中,许多SQL Server方案往往是由于数据库设计得不好导致性能很差。所以,要实现良好的数据库设计就必须考虑这些问题。
1.1 逻辑库规范化问题
  一般来说,逻辑数据库设计会满足规范化的前3级标准:
  1.第1规范:没有重复的组或多值的列。
  2.第2规范:每个非关键字段必须依赖于主关键字,不能依赖于1个组合式主关键字的某些组成部分。
  3.第3规范:1个非关键字段不能依赖于另1个非关键字段。
  遵守这些规则的设计会产生较少的列和更多的表,因而也就减少了数据冗余,也减少了用于存储数据的页。但表关系也许需要通过复杂的合并来处理,这样会降低系统的性能。某种程度上的非规范化可以改善系统的性能,非规范化过程可以根据性能方面不同的考虑用多种不同的方法进行,但以下方法经实践验证往往能提高性能。
  1.如果规范化设计产生了许多4路或更多路合并关系,就可以考虑在数据库实体(表)中加入重复属性(列)。
  2.常用的计算字段(如总计、最大值等)可以考虑存储到数据库实体中。
  比如某一个项目的计划管理系统中有计划表,其字段为:项目编号、年初计划、二次计划、调整计划、补列计划…,而计划总数(年初计划+二次计划+调整计划+补列计划)是用户经常需要在查询和报表中用到的,在表的记录量很大时,有必要把计划总数作为1个独立的字段加入到表中。这里可以采用触发器以在客户端保持数据的一致性。
  3.重新定义实体以减少外部属性数据或行数据的开支。相应的非规范化类型是:
  (1)把1个实体(表)分割成2个表(把所有的属性分成2组)。这样就把频繁被访问的数据同较少被访问的数据分开了。这种方法要求在每个表中复制首要关键字。这样产生的设计有利于并行处理,并将产生列数较少的表。
  (2)把1个实体(表)分割成2个表(把所有的行分成2组)。这种方法适用于那些将包含大量数据的实体(表)。在应用中常要保留历史记录,但是历史记录很少用到。因此可以把频繁被访问的数据同较少被访问的历史数据分开。而且如果数据行是作为子集被逻辑工作组(部门、销售分区、地理区域等)访问的,那么这种方法也是很有好处的。
1.2 生成物理数据库
  要想正确选择基本物理实现策略,必须懂得数据库访问格式和硬件资源的操作特点,主要是内存和磁盘子系统I/O。这是一个范围广泛的话题,但以下的准则可能会有所帮助。
  1.与每个表列相关的数据类型应该反映数据所需的最小存储空间,特别是对于被索引的列更是如此。比如能使用smallint类型就不要用integer类型,这样索引字段可以被更快地读取,而且可以在1个数据页上放置更多的数据行,因而也就减少了I/O操作。
  2.把1个表放在某个物理设备上,再通过SQL Server段把它的不分簇索引放在1个不同的物理设备上,这样能提高性能。尤其是系统采用了多个智能型磁盘控制器和数据分离技术的情况下,这样做的好处更加明显。
  3.用SQL Server段把一个频繁使用的大表分割开,并放在2个单独的智能型磁盘控制器的数据库设备上,这样也可以提高性能。因为有多个磁头在查找,所以数据分离也能提高性能。
  4.用SQL Server段把文本或图像列的数据存放在1个单独的物理设备上可以提高性能。1个专用的智能型的控制器能进一步提高性能。

2 与SQL Server相关的硬件系统
  与SQL Server有关的硬件设计包括系统处理器、内存、磁盘子系统和网络,这4个部分基本上构成了硬件平台,Windows NT和SQL Server运行于其上。
2.1 系统处理器(CPU)
  根据自己的具体需要确定CPU结构的过程就是估计在硬件平台上占用CPU的工作量的过程。从以往的经验看,CPU配置最少应是1个80586/100处理器。如果只有2~3个用户,这就足够了,但如果打算支持更多的用户和关键应用,推荐采用Pentium Pro或PⅡ级CPU。

2.2 内存(RAM)
  为SQL Server方案确定合适的内存设置对于实现良好的性能是至关重要的。SQL Server用内存做过程缓存、数据和索引项缓存、静态服务器开支和设置开支。SQL Server最多能利用2GB虚拟内存,这也是最大的设置值。还有一点必须考虑的是Windows NT和它的所有相关的服务也要占用内存。
  Windows NT为每个WIN32应用程序提供了4GB的虚拟地址空间。这个虚拟地址空间由Windows NT虚拟内存管理器(VMM)映射到物理内存上,在某些硬件平台上可以达到4GB。SQL Server应用程序只知道虚拟地址,所以不能直接访问物理内存,这个访问是由VMM控制的。Windows NT允许产生超出可用的物理内存的虚拟地址空间,这样当给SQL Server分配的虚拟内存多于可用的物理内存时,会降低SQL Server的性能。
  这些地址空间是专门为SQL Server系统设置的,所以如果在同一硬件平台上还有其它软件(如文件和打印共享,应用程序服务等)在运行,那么应该考虑到它们也占用一部分内存。一般来说硬件平台至少要配置32MB的内存,其中,Windows NT至少要占用16MB。1个简单的法则是,给每一个并发的用户增加100KB的内存。例如,如果有100个并发的用户,则至少需要32MB+100用户*100KB=42MB内存,实际的使用数量还需要根据运行的实际情况调整。可以说,提高内存是提高系统性能的最经济的途径。
2.3 磁盘子系统
  设计1个好的磁盘I/O系统是实现良好的SQL Server方案的一个很重要的方面。这里讨论的磁盘子系统至少有1个磁盘控制设备和1个或多个硬盘单元,还有对磁盘设置和文件系统的考虑。智能型SCSI-2磁盘控制器或磁盘组控制器是不错的选择,其特点如下:
  (1)控制器高速缓存。
  (2)总线主板上有处理器,可以减少对系统CPU的中断。
  (3)异步读写支持。
  (4)32位RAID支持。
  (5)快速SCSI—2驱动。
  (6)超前读高速缓存(至少1个磁道)。
3 检索策略
  在精心选择了硬件平台,又实现了1个良好的数据库方案,并且具备了用户需求和应用方面的知识后,现在应该设计查询和索引了。有2个方面对于在SQL Server上取得良好的查询和索引性能是十分重要的,第1是根据SQL Server优化器方面的知识生成查询和索引;第2是利用SQL Server的性能特点,加强数据访问操作。
3.1 SQL Server优化器
  Microsoft SQL Server数据库内核用1个基于费用的查询优化器自动优化向SQL提交的数据查询操作。数据操作查询是指支持SQL关键字WHERE或HAVING的查询,如SELECT、DELETE和UPDATE。基于费用的查询优化器根据统计信息产生
句的费用估算。
  了解优化器数据处理过程的简单方法是检测SHOWPLAN命令的输出结果。如果用基于字符的工具(例如isql),可以通过键入SHOW SHOWPLAN ON来得到SHOWPLAN命令的输出。如果使用图形化查询,比如SQL Enterprise Manager中的查询工具或isql/w,可以设定配置选项来提供这一信息。
  SQL Server的优化通过3个阶段完成:查询分析、索引选择、合并选择。
  1.查询分析
  在查询分析阶段,SQL Server优化器查看每一个由正规查询树代表的子句,并判断它是否能被优化。SQL Server一般会尽量优化那些限制扫描的子句。例如,搜索和/或合并子句。但是不是所有合法的SQL语法都可以分成可优化的子句,如含有SQL不等关系符“<>”的子句。因为“<>”是1个排斥性的操作符,而不是1个包括性的操作符,所在扫描整个表之前无法确定子句的选择范围会有多大。当1个关系型查询中含有不可优化的子句时,执行计划用表扫描来访问查询的这个部分,对于查询树中可优化的SQL Server子句,则由优化器执行索引选择。
  2.索引选择
  对于每个可优化的子句,优化器都查看数据库系统表,以确定是否有相关的索引能用于访问数据。只有当索引中的列的1个前缀与查询子句中的列完全匹配时,这个索引才被认为是有用的。因为索引是根据列的顺序构造的,所以要求匹配是精确的匹配。对于分簇索引,原来的数据也是根据索引列顺序排序的。想用索引的次要列访问数据,就像想在电话本中查找所有姓为某个姓氏的条目一样,排序基本上没有什么用,因为你还是得查看每一行以确定它是否符合条件。如果1个子句有可用的索引,那么优化器就会为它确定选择性。
  所以在设计过程中,要根据查询设计准则仔细检查所有的查询,以查询的优化特点为基础设计索引。
  (1)比较窄的索引具有比较高的效率。对于比较窄的索引来说,每页上能存放较多的索引行,而且索引的级别也较少。所以,缓存中能放置更多的索引页,这样也减少了I/O操作。
  (2)SQL Server优化器能分析大量的索引和合并可能性。所以与较少的宽索引相比,较多的窄索引能向优化器提供更多的选择。但是不要保留不必要的索引,因为它们将增加存储和维护的开支。对于复合索引、组合索引或多列索引,SQL Server优化器只保留最重要的列的分布统计信息,这样,索引的第1列应该有很大的选择性。
  (3)表上的索引过多会影响UPDATE、INSERT和DELETE的性能,因为所有的索引都必须做相应的调整。另外,所有的分页操作都被记录在日志中,这也会增加I/O操作。  

  (4)对1个经常被更新的列建立索引,会严重影响性能。
  (5)由于存储开支和I/O操作方面的原因,较小的自组索引比较大的索引性能更好一些。但它的缺点是要维护自组的列。
  (6)尽量分析出每一个重要查询的使用频度,这样可以找出使用最多的索引,然后可以先对这些索引进行适当的优化。
  (7)查询中的WHERE子句中的任何列都很可能是个索引列,因为优化器重点处理这个子句。
  (8)对小于1个范围的小型表进行索引是不划算的,因为对于小表来说表扫描往往更快而且费用低。
  (9)与“ORDER BY”或“GROUP BY”一起使用的列一般适于做分族索引。如果“ORDER BY”命令中用到的列上有分簇索引,那么就不会再生成1个工作表了,因为行已经排序了。“GROUP BY”命令则一定产生1个工作表。
  (10)分簇索引不应该构造在经常变化的列上,因为这会引起整行的移动。在实现大型交易处理系统时,尤其要注意这一点,因为这些系统中数据往往是频繁变化的。
  3.合并选择
  当索引选择结束,并且所有的子句都有了一个基于它们的访问计划的处理费用时,优化器开始执行合并选择。合并选择被用来找出一个用于合并子句访问计划的有效顺序。为了做到这一点,优化器比较子句的不同排序,然后选出从物理磁盘I/O的角度看处理费用最低的合并计划。因为子句组合的数量会随着查询的复杂度极快地增长,SQL Server查询优化器使用树剪枝技术来尽量减少这些比较所带来的开支。当这个合并选择阶段结束时,SQL Server查询优化器已经生成了1个基于费用的查询执行计划,这个计划充分利用了可用的索引,并以最小的系统开支和良好的执行性能访问原来的数据。
3.2 高效的查询选择
  从以上查询优化的3个阶段不难看出,设计出物理I/O和逻辑I/O最少的方案并掌握好处理器时间和I/O时间的平衡,是高效查询设计的主要目标。也就是说,希望设计出这样的查询:充分利用索引、磁盘读写最少、最高效地利用了内存和CPU资源。
  以下建议是从SQL Server优化器的优化策略中总结出来的,对于设计高效的查询是很有帮助的。
  1.如果有独特的索引,那么带有“=”操作符的WHERE子句性能最好,其次是封闭的区间(范围),再其次是开放的区间。
  2.从数据库访问的角度看,含有不连续连接词(OR和IN)的WHERE子句一般来说性能不会太好。所以,优化器可能会采用R策略,这种策略会生成1个工作表,其中含有每个可能匹配的执行的标识符,优化器把这些行标志符(页号和行号)看做是指向1个表中匹配的行的“动态索引”。优化器只需扫描工作表,取出每一个行标志符,再从数据表中取得相应的行,所以R策略的代价是生成工作表。
  3.包含NOT、<>、或! =的WHERE子句对于优化器的索引选择来说没有什么用处。因为这样的子句是排斥性的,而不是包括性的,所以在扫描整个原来数据表之前无法确定子句的选择性。
  4.限制数据转换和串操作,优化器一般不会根据WHERE子句中的表达式和数据转换式生成索引选择。例如:
  paycheck * 12>36000 or substring(lastname,1,1)=“L”
  如果该表建立了针对paycheck和lastname的索引,就不能利用索引进行优化,可以改写上面的条件表达式为:
  paycheck<36000/12 or lastname like “L%”
  5.WHERE子句中的本地变量被认为是不被优化器知道和考虑的,例外的情况是定义为储备过程输入参数的变量。
  6.如果没有包含合并子句的索引,那么优化器构造1个工作表以存放合并中最小的表中的行。然后再在这个表上构造1个分簇索引以完成一个高效的合并。这种作法的代价是工作表的生成和随后的分族索引的生成,这个过程叫REFORMATTING。所以应该注意RAM中或磁盘上的数据库tempdb的大小(除了SELECT INTO语句)。另外,如果这些类型的操作是很常见的,那么把tempdb放在RAM中对于提高性能是很有好处的。
4 性能优化的其他考虑
  上面列出了影响SQL Server的一些主要因素,实际上远不止这些。操作系统的影响也很大,在Windows NT下,文件系统的选择、网络协议、开启的服务、SQL Server的优先级等选项也不同程度上影响了SQL Server的性能。
  影响性能的因素是如此的多,而应用又各不相同,找出1个通用的优化方案是不现实的,在系统开发和维护的过程中必须针对运行的情况,不断加以调整。事实上,绝大部分的优化和调整工作是在与客户端独立的服务器上进行的,因此也是现实可行
的。

SQL语句集锦

Friday, July 22nd, 2005
–语 句                                功 能
–数据操作
SELECT      –从数据库表中检索数据行和列
INSERT      –向数据库表添加新数据行
DELETE      –从数据库表中删除数据行
UPDATE      –更新数据库表中的数据
–数据定义
CREATE TABLE    –创建一个数据库表
DROP TABLE     –从数据库中删除表
ALTER TABLE     –修改数据库表结构
CREATE VIEW     –创建一个视图
DROP VIEW     –从数据库中删除视图
CREATE INDEX    –为数据库表创建一个索引
DROP INDEX     –从数据库中删除索引
CREATE PROCEDURE   –创建一个存储过程
DROP PROCEDURE    –从数据库中删除存储过程
CREATE TRIGGER    –创建一个触发器
DROP TRIGGER    –从数据库中删除触发器
CREATE SCHEMA    –向数据库添加一个新模式
DROP SCHEMA     –从数据库中删除一个模式
CREATE DOMAIN    –创建一个数据值域
ALTER DOMAIN    –改变域定义
DROP DOMAIN     –从数据库中删除一个域
–数据控制
GRANT      –授予用户访问权限
DENY      –拒绝用户访问
REVOKE      –解除用户访问权限
–事务控制
COMMIT      –结束当前事务
ROLLBACK     –中止当前事务
SET TRANSACTION    –定义当前事务数据访问特征
–程序化SQL
DECLARE      –为查询设定游标
EXPLAN      –为查询描述数据访问计划
OPEN      –检索查询结果打开一个游标
FETCH      –检索一行查询结果
CLOSE      –关闭游标
PREPARE      –为动态执行准备SQL 语句
EXECUTE      –动态地执行SQL 语句
DESCRIBE     –描述准备好的查询

 

—局部变量
declare @id char(10)
–set @id = ‘10010001’
select @id = ‘10010001’

 

—全局变量
—必须以@@开头

 

–IF ELSE
declare @x int @y int @z int
select @x = 1 @y = 2 @z=3
if @x > @y
print ‘x > y’ –打印字符串’x > y’
else if @y > @z
print ‘y > z’
else print ‘z > y’

 

–CASE
use pangu
update employee
set e_wage =
case
  when job_level = ’1’ then e_wage*1.08
  when job_level = ’2’ then e_wage*1.07
  when job_level = ’3’ then e_wage*1.06
  else e_wage*1.05
end

 

–WHILE CONTINUE BREAK
declare @x int @y int @c int
select @x = 1 @y=1
while @x < 3
begin
  print @x –打印变量x 的值
  while @y < 3
   begin
    select @c = 100*@x + @y
    print @c –打印变量c 的值
    select @y = @y + 1
   end
  select @x = @x + 1
  select @y = 1
end

 

–WAITFOR
–例 等待1 小时2 分零3 秒后才执行SELECT 语句
waitfor delay ’01:02:03’
select * from employee
–例 等到晚上11 点零8 分后才执行SELECT 语句
waitfor time ’23:08:00’
select * from employee

 

 

***SELECT***

 

   select *(列名) from table_name(表名) where column_name operator value
   ex:(宿主)
  select * from stock_information where stockid   = str(nid)
     stockname = ‘str_name’
     stockname like ‘% find this %’
     stockname like ‘[a-zA-Z]%’ ——— ([]指定值的范围)
     stockname like ‘[^F-M]%’   ——— (^排除指定范围)
     ——— 只能在使用like关键字的where子句中使用通配符)
     or stockpath = ‘stock_path’
     or stocknumber < 1000
     and stockindex = 24
     not stock*** = ‘man’
     stocknumber between 20 and 100
     stocknumber in(10,20,30)
     order by stockid desc(asc) ——— 排序,desc-降序,asc-升序
     order by 1,2 ——— by列号
     stockname = (select stockname from stock_information  where stockid  = 4)
     ——— 子查询
     ——— 除非能确保内层select只返回一个行的值,
     ——— 否则应在外层where子句中用一个in限定符
  select distinct column_name form table_name ——— distinct指定检索独有的列值,不重复
  select stocknumber ,"stocknumber + 10" = stocknumber + 10 from table_name
  select stockname , "stocknumber" = count(*) from table_name group by stockname
                                      ——— group by 将表按行分组,指定列中有相同的值
          having count(*) = 2  ———  having选定指定的组
       
  select *
  from table1, table2                 
  where table1.id *= table2.id ——– 左外部连接,table1中有的而table2中没有得以null表示
     table1.id =* table2.id ——– 右外部连接

 

  select stockname from table1
  union [all]  —–  union合并查询结果集,all-保留重复行
  select stockname from table2

 

***insert***

 

  insert into table_name (Stock_name,Stock_number) value ("xxx","xxxx")
              value (select Stockname , Stocknumber from Stock_table2)—value为select语句

 

***update***

 

  update table_name set Stockname = "xxx" [where Stockid = 3]
         Stockname = default
         Stockname = null
         Stocknumber = Stockname + 4

 

***delete***

 

  delete from table_name where Stockid = 3
  truncate table_name ———– 删除表中所有行,仍保持表的完整性
  drop table table_name ————— 完全删除表

 

***alter table*** — 修改数据库表结构

 

  alter table database.owner.table_name add column_name char(2) null …..
  sp_help table_name —- 显示表已有特征
  create table table_name (name char(20), age smallint, lname varchar(30))
  insert into table_name select ……… —– 实现删除列的方法(创建新表)
  alter table table_name drop constraint Stockname_default —- 删除Stockname的default约束
   
***function(/*常用函数*/)***

 

—-统计函数—-
AVG    –求平均值
COUNT   –统计数目
MAX    –求最大值
MIN    –求最小值
SUM    –求和

 

–AVG
use pangu
select avg(e_wage) as dept_avgWage
from employee
group by dept_id

 

–MAX
–求工资最高的员工姓名
use pangu
select e_name
from employee
where e_wage =
(select max(e_wage)
  from employee)

 

–STDEV()
–STDEV()函数返回表达式中所有数据的标准差

 

–STDEVP()
–STDEVP()函数返回总体标准差

 

–VAR()
–VAR()函数返回表达式中所有值的统计变异数

 

–VARP()
–VARP()函数返回总体变异数

 

—-算术函数—-

 

/***三角函数***/
SIN(float_expression) –返回以弧度表示的角的正弦
COS(float_expression) –返回以弧度表示的角的余弦
TAN(float_expression) –返回以弧度表示的角的正切
COT(float_expression) –返回以弧度表示的角的余切
/***反三角函数***/
ASIN(float_expression) –返回正弦是FLOAT 值的以弧度表示的角
ACOS(float_expression) –返回余弦是FLOAT 值的以弧度表示的角
ATAN(float_expression) –返回正切是FLOAT 值的以弧度表示的角
ATAN2(float_expression1,float_expression2)
        –返回正切是float_expression1 /float_expres-sion2的以弧度表示的角
DEGREES(numeric_expression)
                       –把弧度转换为角度返回与表达式相同的数据类型可为
        –INTEGER/MONEY/REAL/FLOAT 类型
RADIANS(numeric_expression) –把角度转换为弧度返回与表达式相同的数据类型可为
        –INTEGER/MONEY/REAL/FLOAT 类型
EXP(float_expression)  –返回表达式的指数值
LOG(float_expression)  –返回表达式的自然对数值
LOG10(float_expression)–返回表达式的以10 为底的对数值
SQRT(float_expression) –返回表达式的平方根
/***取近似值函数***/
CEILING(numeric_expression)  –返回>=表达式的最小整数返回的数据类型与表达式相同可为
        –INTEGER/MONEY/REAL/FLOAT 类型
FLOOR(numeric_expression)    –返回<=表达式的最小整数返回的数据类型与表达式相同可为
        –INTEGER/MONEY/REAL/FLOAT 类型
ROUND(numeric_expression)    –返回以integer_expression 为精度的四舍五入值返回的数据
        –类型与表达式相同可为INTEGER/MONEY/REAL/FLOAT 类型
ABS(numeric_expression)      –返回表达式的绝对值返回的数据类型与表达式相同可为
        –INTEGER/MONEY/REAL/FLOAT 类型
SIGN(numeric_expression)     –测试参数的正负号返回0 零值1 正数或-1 负数返回的数据类型
        –与表达式相同可为INTEGER/MONEY/REAL/FLOAT 类型
PI()       –返回值为π 即3.1415926535897936
RAND([integer_expression])   –用任选的[integer_expression]做种子值得出0-1 间的随机浮点数

 

—-字符串函数—-
ASCII()         –函数返回字符表达式最左端字符的ASCII 码值
CHAR()   –函数用于将ASCII 码转换为字符
    –如果没有输入0 ~ 255 之间的ASCII 码值CHAR 函数会返回一个NULL 值
LOWER()   –函数把字符串全部转换为小写
UPPER()   –函数把字符串全部转换为大写
STR()   –函数把数值型数据转换为字符型数据
LTRIM()   –函数把字符串头部的空格去掉
RTRIM()   –函数把字符串尾部的空格去掉
LEFT(),RIGHT(),SUBSTRING()  –函数返回部分字符串
CHARINDEX(),PATINDEX()  –函数返回字符串中某个指定的子串出现的开始位置
SOUNDEX()  –函数返回一个四位字符码
    –SOUNDEX函数可用来查找声音相似的字符串但SOUNDEX函数对数字和汉字均只返回0 值    
DIFFERENCE()    –函数返回由SOUNDEX 函数返回的两个字符表达式的值的差异
    –0 两个SOUNDEX 函数返回值的第一个字符不同
    –1 两个SOUNDEX 函数返回值的第一个字符相同
    –2 两个SOUNDEX 函数返回值的第一二个字符相同
    –3 两个SOUNDEX 函数返回值的第一二三个字符相同
    –4 两个SOUNDEX 函数返回值完全相同
                                      

 

QUOTENAME()  –函数返回被特定字符括起来的字符串
/*select quotename(‘abc’, ‘{’) quotename(‘abc’)
运行结果如下
———————————-{
{abc} [abc]*/

 

REPLICATE()     –函数返回一个重复character_expression 指定次数的字符串
/*select replicate(‘abc’, 3) replicate( ‘abc’, -2)
运行结果如下
———– ———–
abcabcabc NULL*/

 

REVERSE()       –函数将指定的字符串的字符排列顺序颠倒
REPLACE()       –函数返回被替换了指定子串的字符串
/*select replace(‘abc123g’, ‘123’, ‘def’)
运行结果如下
———– ———–
abcdefg*/

 

SPACE()   –函数返回一个有指定长度的空白字符串
STUFF()   –函数用另一子串替换字符串指定位置长度的子串

 

—-数据类型转换函数—-
CAST() 函数语法如下
CAST() (<expression> AS <data_ type>[ length ])
CONVERT() 函数语法如下
CONVERT() (<data_ type>[ length ], <expression> [, style])

 

select cast(100+99 as char) convert(varchar(12), getdate())
运行结果如下
—————————— ————
199   Jan 15 2000

 

—-日期函数—-
DAY()   –函数返回date_expression 中的日期值
MONTH()   –函数返回date_expression 中的月份值
YEAR()   –函数返回date_expression 中的年份值
DATEADD(<datepart> ,<number> ,<date>)
    –函数返回指定日期date 加上指定的额外日期间隔number 产生的新日期
DATEDIFF(<datepart> ,<number> ,<date>)
    –函数返回两个指定日期在datepart 方面的不同之处
DATENAME(<datepart> , <date>)  –函数以字符串的形式返回日期的指定部分
DATEPART(<datepart> , <date>)  –函数以整数值的形式返回日期的指定部分
GETDATE()  –函数以DATETIME 的缺省格式返回系统当前的日期和时间

 

—-系统函数—-
APP_NAME()      –函数返回当前执行的应用程序的名称
COALESCE()  –函数返回众多表达式中第一个非NULL 表达式的值
COL_LENGTH(<‘table_name’>, <‘column_name’>) –函数返回表中指定字段的长度值
COL_NAME(<table_id>, <column_id>)   –函数返回表中指定字段的名称即列名
DATALENGTH() –函数返回数据表达式的数据的实际长度
DB_ID([‘database_name’]) –函数返回数据库的编号
DB_NAME(database_id)  –函数返回数据库的名称
HOST_ID()     –函数返回服务器端计算机的名称
HOST_NAME()     –函数返回服务器端计算机的名称
IDENTITY(<data_type>[, seed increment]) [AS column_name])
–IDENTITY() 函数只在SELECT INTO 语句中使用用于插入一个identity column列到新表中
/*select identity(int, 1, 1) as column_name
into newtable
from oldtable*/
ISDATE()  –函数判断所给定的表达式是否为合理日期
ISNULL(<check_expression>, <replacement_value>) –函数将表达式中的NULL 值用指定值替换
ISNUMERIC()  –函数判断所给定的表达式是否为合理的数值
NEWID()   –函数返回一个UNIQUEIDENTIFIER 类型的数值
NULLIF(<expression1>, <expression2>)
–NULLIF 函数在expression1 与expression2 相等时返回NULL 值若不相等时则返回expression1 的值
 

阅读全文(1747) | 回复(16) | 引用(0)  
 

精妙SQL语句
晴天发表评论于2004-10-26 11:16:00 
精妙SQL语句
说明:复制表(只复制结构,源表名:a 新表名:b)
SQL: select * into b from a where 1<>1
说明:拷贝表(拷贝数据,源表名:a 目标表名:b)
SQL: insert into b(a, b, c) select d,e,f from b;
说明:显示文章、提交人和最后回复时间
SQL: select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b
说明:外连接查询(表名1:a 表名2:b)
SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c
说明:日程安排提前五分钟提醒
SQL: select * from 日程安排 where datediff(‘minute’,f开始时间,getdate())>5

说明:两张关联表,删除主表中已经在副表中没有的信息
SQL:
delete from info where not exists ( select * from infobz where info.infid=infobz.infid )
说明:–
SQL:
SELECT A.NUM, A.NAME, B.UPD_DATE, B.PREV_UPD_DATE
FROM TABLE1,
(SELECT X.NUM, X.UPD_DATE, Y.UPD_DATE PREV_UPD_DATE
FROM (SELECT NUM, UPD_DATE, INBOUND_QTY, STOCK_ONHAND
FROM TABLE2
WHERE TO_CHAR(UPD_DATE,’YYYY/MM’) = TO_CHAR(SYSDATE, ‘YYYY/MM’)) X,
(SELECT NUM, UPD_DATE, STOCK_ONHAND
FROM TABLE2
WHERE TO_CHAR(UPD_DATE,’YYYY/MM’) =
TO_CHAR(TO_DATE(TO_CHAR(SYSDATE, ‘YYYY/MM’) || ‘/01′,’YYYY/MM/DD’) – 1, ‘YYYY/MM’) ) Y,
WHERE X.NUM = Y.NUM (+)
AND X.INBOUND_QTY + NVL(Y.STOCK_ONHAND,0) <> X.STOCK_ONHAND ) B
WHERE A.NUM = B.NUM
说明:–
SQL:
select * from studentinfo where not exists(select * from student where studentinfo.id=student.id) and 系名称

=’"&strdepartmentname&"’ and 专业名称=’"&strprofessionname&"’ order by 性别,生源地,高考总成绩
说明:
从数据库中去一年的各单位电话费统计(电话费定额贺电化肥清单两个表来源)
SQL:
SELECT a.userper, a.tel, a.standfee, TO_CHAR(a.telfeedate, ‘yyyy’) AS telyear,
SUM(decode(TO_CHAR(a.telfeedate, ‘mm’), ’01’, a.factration)) AS JAN,
SUM(decode(TO_CHAR(a.telfeedate, ‘mm’), ’02’, a.factration)) AS FRI,
SUM(decode(TO_CHAR(a.telfeedate, ‘mm’), ’03’, a.factration)) AS MAR,
SUM(decode(TO_CHAR(a.telfeedate, ‘mm’), ’04’, a.factration)) AS APR,
SUM(decode(TO_CHAR(a.telfeedate, ‘mm’), ’05’, a.factration)) AS MAY,
SUM(decode(TO_CHAR(a.telfeedate, ‘mm’), ’06’, a.factration)) AS JUE,
SUM(decode(TO_CHAR(a.telfeedate, ‘mm’), ’07’, a.factration)) AS JUL,
SUM(decode(TO_CHAR(a.telfeedate, ‘mm’), ’08’, a.factration)) AS AGU,
SUM(decode(TO_CHAR(a.telfeedate, ‘mm’), ’09’, a.factration)) AS SEP,
SUM(decode(TO_CHAR(a.telfeedate, ‘mm’), ’10’, a.factration)) AS OCT,
SUM(decode(TO_CHAR(a.telfeedate, ‘mm’), ’11’, a.factration)) AS NOV,
SUM(decode(TO_CHAR(a.telfeedate, ‘mm’), ’12’, a.factration)) AS DEC
FROM (SELECT a.userper, a.tel, a.standfee, b.telfeedate, b.factration
FROM TELFEESTAND a, TELFEE b
WHERE a.tel = b.telfax) a
GROUP BY a.userper, a.tel, a.standfee, TO_CHAR(a.telfeedate, ‘yyyy’)
说明:四表联查问题:
SQL: select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where …..
说明:得到表中最小的未使用的ID号
SQL:
SELECT (CASE WHEN EXISTS(SELECT * FROM Handle b WHERE b.HandleID = 1) THEN MIN(HandleID) + 1 ELSE 1 END) as HandleID
FROM Handle
WHERE NOT HandleID IN (SELECT a.HandleID – 1 FROM Handle a)

利用google突破各种封锁来下载你要的东西

Friday, July 22nd, 2005

在搜索框上输入: “index of/ ”  inurl:lib
再按搜索你将进入许多图书馆,并且一定能下载自己喜欢的书籍。

在搜索框上输入: “index of /”  cnki
再按搜索你就可以找到许多图书馆的CNKI、VIP、超星等入口!

在搜索框上输入: “index of /”  ppt
再按搜索你就可以突破网站入口下载powerpint作品!

在搜索框上输入: “index of /”  mp3
再按搜索你就可以突破网站入口下载mp3、rm等影视作品!

在搜索框上输入: “index of /”  swf
再按搜索你就可以突破网站入口下载flash作品!

在搜索框上输入: “index of /”  要下载的软件名
再按搜索你就可以突破网站入口下载软件!

注意引号应是英文的!

程序员守则

Tuesday, July 5th, 2005
Fisher原理: 一个系统越适应某一个特定的外部环境,就越不适应其它的环境。程序员永远要在程序的效率和扩充性方面进行抉择。
 
程序员的懒人原则: 不要重新开发轮子,别指望你比别人做得更好。
 
程序员的踢皮球原则: 当发生问题的时候,人的本能会认为错误不在自己这里,那么对于程序员,应该首先证明自己的清白。
 
程序员的自大原则: 程序员永远不爱看别人的代码,但是每个人都想把自己的代码给别人看,所以在写代码的时候要处处为别人着想。
 
程序员的高手原则: 没有代码是效率最高的代码,没是最正确的代码。