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

eApxLmlq

[笔记]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也是一个实行最小限度记录的操作，因此使用它也会影响数据库的恢复。它经常被用于创建临时表。
好好学习,天天向上

mqqpmsw

学习   学习