找回密码
 注册
搜索
热搜: java php web
查看: 378|回复: 1

[笔记]SQL笔记(1)-T-SQL惯例

[复制链接]
发表于 2009-1-26 06:53:01 | 显示全部楼层 |阅读模式
[笔记]SQL笔记(1)-T-SQL惯例
一、元数据查询
尽管存在许多返回元数据的方法,但通常可采用以下步骤:(后面的"对象创建"部分有具体实例)
(1)在可能的情况下,则使用元数据函数(如OBJECTPROPERTY)
(2)如果不存在返回所需信息的函数,则查询INFORMATION_SCHEMA
(3)如果没有INFORMATION_SCHEMA满足要求,则检查系统存储过程(如sp_tables)
(4)如果上述方法都无效,则直接查询系统表(如sysobjects)


二、对象创建(检查对象的存在性)
1、系统表法
IF EXISTS(SELECT * FROM sysobjects WHERE name = 'authors')
   DROP TABLE dbo.authors
GO
CREATE TABLE dbo.authors
......

注:该代码可以运行,缺陷是:它直接查询系统表sysobjects,如果该表的设计改变,代码就无法正常工作。同时代码不检查对象的拥有者,如果除dbo外的用户创建一个authors对象,则不管dbo.authors是否存在,都可执行至drop table语句。

2、视图法
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'authors' AND TABLE_SCHEMA = 'dbo')
  DROP TABLE dbo.authors
GO
CREATE TABLE dbo.authors
......

注:比前一种方法有些改进,它使用视图确定用户dbo是否拥有authors表。可以运行,但代码较长。

3、习惯方法
IF OBJECT_ID ('dbo.authors')  IS NOT NULL
  DROP TABLE dbo.authors
GO
CREATE TABLE dbo.authors
......

注:代码简单,执行高效。缺陷是:它不检查对象的类型,通过扫描INFORMATION_SCHEMA.TABLES只检查表和视图中是否存在authors对象。

4、元数据函数法
IF (OBJECT_ID ('dbo.authors') IS NOT NULL)  AND  (OBJECTPROPERTY(OBJECT_ID('dbo.authors'),'IsTable') = 1)

  DROP TABLE dbo.authors
GO
CREATE TABLE dbo.authors
......


5、常见用法
IF EXISTS (SELECT  *  FROM dbo.sysobjects WHERE  id = OBJECT_ID('dbo.authors')  AND OBJECTPROPERTY(id, 'IsUserTable') = 1)
  DROP TABLE dbo.authors
GO
CREATE TABLE dbo.authors
......

注:与方法4类似,不能算作新方法,SQL SERVER自动生成的脚本所采用的语句

三、设置数据库上下文
1、改变数据库上下文,然后检查它 (正确的方法)
USE pubs  --如果将pubs输错为其他库名,在运行时,显示RAISERROR中的错误信息
GO

IF DB_NAME() <> 'pubs'
BEGIN
  RAISERROR('Wrong Database!',16,10)
  RETURN
END

注:尽管可在查询分析器中使用组合框或在命令行中使用osql -d改变当前数据库,但习惯的方法是包含一条USE语句。

2、不能达到目的的语句 (称它为错误的方法)
USE pubs  --如果将pubs输错为其他库名,在运行时,不会显示RAISERROR中的错误信息
GO
IF @@ERROR <> 0
BEGIN
  RAISERROR('Wrong Database!',16,10)
  RETURN
END

注:似乎在USE之后只能检查自动变量@@ERROR,以便了解它是否成功,但事实并非如此。USE错误中止当前批处理,因此不可能有任何错误检查。

四、变量赋值
DECLARE @i int,@s int
SELECT @i = 10, @s = 20

SET @i = 10
SET @s = 20

DECLARE @Name varchar(20),@Addr varchar(100)
SELECT @Name = CompanyName,@Addr = Address
FROM   Customers
WHERE  CustomerID='CACTU'

注:尽管SELECT与SET都可用于给变量赋值,但SET较简单,可给游标变量赋值,也可给标量变量赋值。而SELECT可以一次给多个变量赋值,并可在不借助子查询条件下从表或视图中赋值。

五、循环
1、习惯用法
DECLARE @i int
SET @i = 0
WHILE @i < 10
BEGIN
  SET @i = @i + 1
END

2、非习惯用法
DECLARE @i int
SET @i = 10
WHILE @i > 0
BEGIN
  SET @i = @i - 1
END

3、GOTO法
DECLARE @i int
SET @i = 0

cycle:
SET @i = @i + 1
IF (@i < 10) GOTO cycle

注:方法3是可行的,但它不是自然的、惯用的,在没产生任何实际好处的条件下,不必涉及GOTO这种非结构化的概念。一种语言惯例是其解决常见问题的自然方法的集合。T-SQL语言最自然的循环方式是向前迭代,而不是逆向迭代,所以方法2也不是惯用的。

六、最顶层行检索
1、习惯用法
SELECT TOP 10 CompanyName,Address
FROM   Customers
ORDER By CompanyName

2、非习惯用法
SET ROWCOUNT 10

SELECT CompanyName,Address
FROM   Customers
ORDER By CompanyName

SET ROWCOUNT 0   --这步必须要做,否则在当前会话中查询其他表时也返回前面设置的行数

3、游标法(不推荐使用)
DECLARE cur_Rows CURSOR FOR
SELECT CompanyName,Address
FROM   Customers
ORDER By CompanyName
FOR READ ONLY

OPEN cur_Rows

DECLARE @i int
SET @i = 0

FETCH cur_Rows
WHILE (@@FETCH_STATUS = 0) AND (@i < 9)
BEGIN
  SET @i = @i + 1
  FETCH cur_Rows
END

CLOSE cur_Rows
DEALLOCATE cur_Rows

注:方法3是所有方法中最差的。它使用了游标、变量与循环,运行慢且占用更多的内存。

另附:选择N-M(M>N)行数据
SELECT  top (M-N+1) *
FROM (SELECT TOP M * FROM Employees Order by EmployeeID) t order by EmployeeID desc

注:实际使用时M-N+1要用运算后的结果代替(如:M=8,N=3,应写成 top 6,不能写成top (8-3+1)的形式)

七、关于空值
1、习惯用法:
SELECT companyname,region
FROM   customers
WHERE  region is null
      
注:如果在此使用WHERE region = NULL,则不会返回任何结果。但在UPDATE语句中,却可以使用SET region = NULL或WHERE region = NULL。

2、使用ISNULL()法
SELECT companyname,region
FROM   customers
WHERE  ISNULL(region,'') = ''

注:虽能正确运行,但ISNULL()不必要地转换NULL为空字符串,并且未能考虑到某些region值真正为空字符串的可能性。

3、设置ANSI_NULL法
SET ANSI_NULLS OFF

--下面语句可以正确返回NULL值行
SELECT companyname,region
FROM   customers
WHERE  regionfiltered=null
--在IN子句中包含NULL值情况下,正常运行的前提也必须设置SET ANSI_NULLS OFF
SELECT companyname,region
FROM   customers
WHERE  region  IN('BC',NULL)


注:因ANSI SQL标准规定,与NULL比较总是产生NULL,在比较前未能正确地设置ANSI_NULLS(默认为ON)将导致不返回任何行。但无论使用的SQL提供什么特殊语法,都最好写遵循标准的代码,通常这容易被更多的人读懂。


另外:对许多编译器来说,只有是或不是-不存在中间类型。但SQL是基于三值逻辑(TRUE、FALSE、NULL),所以为得到所有行,必须考虑到NULL值。
CREATE TABLE test(t int)
INSERT test SELECT 10
UNION  ALL  SELECT 10
UNION  ALL  SELECT 10
UNION  ALL  SELECT 6
UNION  ALL  SELECT NULL


下面语句执行后只返回前4行记录,不会返回NULL那条记录
SELECT *
FROM   test
WHERE  t=10 or t<>10


下面语句执行后可返回全部结果(这里只是为了说明SQL是基于三值逻辑,所以使用了如下的WHERE语句)
SELECT *
FROM   test
WHERE  t=10 or t<>10 or t is NULL  

附:COUNT(*)与COUNT(列名)区别:
SELECT COUNT(*) FROM test    --返回5
SELECT COUNT(t) FROM test  --返回4


注:当没有NULL时,两者返回相同值,有NULL时,COUNT(*)返回总行数,COUNT(列名)则忽略NULL所在行,只统计其他行。通常使用COUNT(*)比较可取,它可使优化器选择最佳方法来返回行数,而不是强制优化器来计算指定列。

八、清空表
方法1:
DELETE customers


方法2:
TRUNCATE TABLE customers

注:方法1执行时,每个行删除都记录在事务日志中,对删除较大的表不实用。即使对于非常大的表,方法2也很快,因为在删除时,只有它的范围操作被记录在事务日志中。快的同时它也有相应的代价,这意味着使用它会影响到数据库的恢复。同时不能将TRUNCATE用于一个包含外键引用的表(使用前删除相应的外键关系)。


日常应用:删除表中所有记录后,如何使新记录的IDENTITY字段的编号从1开始
方法1: TRUNCATE TABLE 表名
方法2: DELETE 表名
                DBCC CHECKIDENT(表名,reseed,0)

九、复制表
1、复制表结构及内容
SELECT * INTO 目的表 FROM 源表

注:在目的表不存在的情况下,用于创建源表的一个副本或查询结果的一个永久副本,但创建的目的表不包含源表中的主键、约束等,要重新建立。


2、复制表结构
方法1: 设置错误的WHERE条件来实现
SELECT * INTO 目的表 FROM 源表 WHERE 1 = 2


方法2:返回不存在的顶层行来实现
SELECT TOP 0 * INTO 目的表 FROM  源表


注:因为1不等于2,也不存在TOP 0的行,所以没有数据被复制,但会创建空表。如同TRUNCATE TABLE一样,SELECT INTO也是一个实行最小限度记录的操作,因此使用它也会影响数据库的恢复。它经常被用于创建临时表。
好好学习,天天向上
发表于 2009-1-26 08:04:47 | 显示全部楼层
学习   学习
回复

使用道具 举报

您需要登录后才可以回帖 登录 | 注册

本版积分规则

Archiver|手机版|小黑屋|软晨网(RuanChen.com)

GMT+8, 2024-11-13 10:22

Powered by Discuz! X3.5

Copyright © 2001-2023 Tencent Cloud.

快速回复 返回顶部 返回列表