SQL在使用中的特别注意项（数据库基础）

一、为什么SQL Server不允许在视图定义使用ORDER BY子句

 

答： SQL Server之所以不允许在视图定义中使用ORDER BY子句是为了遵守ANSI SQL-92标准。因为对该标准的原理分析需要对结构化查询语言（SQL）的底层结构和它所基于的数学理论进行讨论，我们不能在这里对它进行充分的解释。但是，如果你需要在视图中指定ORDER BY子句，可以考虑使用以下方法：

USE pubs
GO

CREATE VIEW AuthorsByName
AS
SELECT TOP 100 PERCENT *
FROM authors
ORDER BY au_lname, au_fname
GO


Microsoft在SQL Server 7.0中引入的TOP结构在同ORDER BY子句结合使用时是非常有用的。只有在同TOP关键词结合使用时，SQL Server才支持在视图中使用ORDER BY子句。

注意：TOP关键词是SQL Server对ANSI SQL-92标准的扩展。

 

二、SQL中，单引号与&等特殊符号的处理

 

单引号' 可以用chr(39)表示，& 可以用chr(38)表示。
　　
　　例子：
　　
　　select 'test:' || chr(38) || chr(39) from dual;
　　结果是：test:&'
　　
　　
　　注：chr()的反函数是ascii(), 比如：
　　select ascii('a') from dual;
　　结果是:97
　　
　　另外，单引号也可以这样处理：
　　
　　select 'I''m chinese' from dual
　　结果是：I'm chinese.

三、快速掌握一些异常精妙的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 性别,生源地,高考总成绩

 

四、比较少用的数据类型

 

定长字符串

　　
1. CHAR(x)是定长字符串。长度属性 x 必须在 1 和 254 之间，并包括 1 和 254。

　　
定长图形字符串

　　
2. GRAPHIC(x)是定长字符串。长度属性 x 必须在 1 和 127 之间，并包括 1 和 127。
　　
3. INTEGER（大型整数）是精度为 10 位的四字节整数。

4. REAL（单精度浮点数）是实数的 32 位近似值。

 

5. DECIMAL 是一个十进制数。小数点的位置由数字的 精度(p)和小数位(s) 确定。精度是数字的总位数，必须小于 32。小数位是小数部分数字的位数且总是小于或等于精度值。如果未指定精度和小数位，则十进制值的缺省精度为 5，缺省小数位为 0。

6. 日期时间值是日期、时间以及时间戳记的表示。日期时间值可以用于某些算术运算和字符串运算并且与某些字符串是相容的，然而它们既非字符串也非数字。

 

7.时间戳记为指定日期和时间的值，分为七个部分（年、月、日、小时、分钟、秒以及微秒）。 空值是一个区别于所有非空值的特殊值。它意味着行中的那一列无任何其他值。所有数据类型都存在空值。

8. Ms Sql用约束和触发器实施商业规则

 

在商界，我们的确通常需要确保始终实施某些规则。例如，参与项目的雇员必须被雇用。或者想要某些事件有计划地发生。例如，如果销售员售出一批商品，则应增加其佣金。

DB2 通用数据库为此提供了一套有用的方法。 唯一约束是禁止在表的一列或多列中出现重复值的规则。 参考完整性约束确保在整个指定的表中数据一致性。 表检查约束是一些条件，它们定义为表定义的一部分，限制一列或多列中使用的值。触发器允许您定义一组操作，这些操作通过对指定的表进行删除、插入或更新操作来执行或触发。触发器可用于写入其他表、修改输入值以及发布警报信息。

第一节提供关键字的概念性概述。接着，通过示例和图表进一步探讨参考完整性、约束以及触发器。


1、关键字
关键字是可用来标识或存取特定行的一组列。

由不止一列组成的关键字称为组合关键字。在具有组合关键字的表中，组合关键字中各列的排序不受这些列在表中排序的约束。


唯一关键字
唯一关键字被定义为它的任何值都不相同。唯一关键字的列不能包含空值。在执行 INSERT 和 UPDATE 语句期间，数据库管理程序强制执行该约束。一个表可以有多个唯一关键字。唯一关键字是可选的，并且可在 CREATE TABLE 或 ALTER TABLE 语句中定义。


主关键字
主关键字是一种唯一关键字，表定义的一部分。一个表不能有多个主关键字，并且主关键字的列不能包含空值。主关键字是可选的，并且可在 CREATE TABLE 或 ALTER TABLE 语句中定义。


外部关键字
外部关键字在参考约束的定义中指定。一个表可以有零个或多个外部关键字。如果组合外部关键字的值的任何部分为空，则该值为空。外部关键字是可选的，并且可在 CREATE TABLE 语句或 ALTER TABLE 语句中定义。


2、唯一约束
唯一约束确保关键字的值在表中是唯一的。唯一约束是可选的，并且可以通过使用指定 PRIMARY KEY 或 UNIQUE 子句的 CREATE TABLE 或 ALTER TABLE 语句来定义唯一约束。例如，可在一个表的雇员编号列上定义一个唯一约束，以确保每个雇员有唯一的编号。


3、参考完整性约束
通过定义唯一约束和外部关键字，可以定义表与表之间的关系，从而实施某些商业规则。唯一关键和外部关键字约束的组合通常称为参考完整性约束。外部关键字所引用的唯一约束称为父关键字。外部关键字表示特定的父关键字，或与特定的父关键字相关。例如，某规则可能规定每个雇员（EMPLOYEE 表）必须属于某现存的部门（DEPARTMENT 表）。因此，将 EMPLOYEE 表中的“部门号”定义为外部关键字，而将 DEPARTMENT 表中的“部门号”定义为主关键字。下列图表提供参考完整性约束的直观说明。



4、表检查约束
表检查约束指定对于表的每行都要进行判定的条件。可对个别列指定检查约束。可使用 CREATE 或 ALTER TABLE 语句添加检查约束。

下列语句创建具有下列约束的表：

部门编号的值必须在范围 10 至 100 内
雇员的职务只能为下列之一： "Sales"、"Mgr"或"Clerk"
1986 年之前雇用的每个雇员的工资必须超过 $40,500。


CREATE TABLE EMP
(ID　SMALLINT NOT NULL,
NAME　VARCHAR(9),
DEPT　SMALLINT CHECK (DEPT BETWEEN 10 AND 100),
JOB　CHAR(5)　CHECK (JOB IN ('Sales', 'Mgr', 'Clerk')),
HIREDATE　DATE,
SALARY　DECIMAL(7,2),
COMM　DECIMAL(7,2),
PRIMARY KEY (ID),
CONSTRAINT YEARSAL CHECK (YEAR(HIREDATE) >= 1986 OR SALARY > 40500) )

仅当条件判定为假时才会违反约束。例如，如果插入行的 DEPT 为空值，则插入继续进行而不出错，尽管 DEPT 的值应该象约束中定义的那样在 10 和 100 之间。

下列语句将一个约束添加至名为 COMP 的 EMPLOYEE 表中，该约束为雇员的总报酬必须超过 $15,000：


ALTER TABLE EMP
ADD CONSTRAINT COMP CHECK (SALARY + COMM > 15000)

将检查表中现存的行以确保这些行不违反新约束。可通过使用如下的 SET CONSTRAINTS 语句将此检查延期：

SET CONSTRAINTS FOR EMP OFF
ALTER TABLE EMP ADD CONSTRAINT COMP CHECK (SALARY + COMM > 15000)
SET CONSTRAINTS FOR EMP IMMEDIATE CHECKED


首先使用 SET CONSTRAINTS 语句以延期对表的约束检查。然后可将一个或多个约束添加至表而不检查这些约束。接着再次发出 SET CONSTRAINTS 语句，反过来将约束检查打开并执行任何延期的约束检查。


5、触发器
一个触发器定义一组操作，这组操作通过修改指定基表中数据的操作来激活。

可使用触发器来执行对输入数据的验证；自动生成新插入行的值；为了交叉引用而读取其他表；为了审查跟踪而写入其他表；或通过电子邮件信息支持警报。使用触发器将导致应用程序开发及商业规则的全面实施更快速并且应用程序和数据的维护更容易。

DB2 通用数据库支持几种类型的触发器。可定义触发器在 DELETE、INSERT 或 UPDATE 操作之前或之后激活。每个触发器包括一组称为触发操作的 SQL 语句，这组语句可包括一个可选的搜索条件。

可进一步定义后触发器以对每一行都执行触发操作，或对语句执行一次触发操作，而前触发器总是对每一行都执行触发操作。

在 INSERT、UPDATE 或 DELETE 语句之前使用触发器，以便在执行触发操作之前检查某些条件，或在将输入值存储在表中之前更改输入值。使用后触发器，以便在必要时传播值或执行其他任务，如发送信息等，这些任务可能是触发器操作所要求的。

下列示例说明了前触发器和后触发器的使用。考虑一个记录并跟踪股票价格波动的应用程序。该数据库包含两个表，CURRENTQUOTE 和 QUOTEHISTORY，定义如下：


CREATE TABLE CURRENTQUOTE
(SYMBOL VARCHAR(10),
QUOTE DECIMAL(5,2),
STATUS VARCHAR(9))

CREATE TABLE　QUOTEHISTORY
(SYMBOL VARCHAR(10),
QUOTE DECIMAL(5,2),
TIMESTAMP TIMESTAMP)

当使用如下语句更新 CURRENTQUOTE 的 QUOTE 列时：


UPDATE CURRENTQUOTE
SET QUOTE = 68.5
WHERE SYMBOL = 'IBM'

应更新 CURRENTQUOTE 的 STATUS 列以反映股票是否：

在升值
处于本年度的新高
在下跌
处于本年度的新低
价位稳定
这通过使用下列前触发器来实现：

(1)

CREATE TRIGGER STOCK_STATUS
NO CASCADE BEFORE UPDATE OF QUOTE ON CURRENTQUOTE
REFERENCING NEW AS NEWQUOTE OLD AS OLDQUOTE
FOR EACH ROW MODE DB2SQL　

(2)

SET NEWQUOTE.STATUS =

(3)

CASE

(4)

WHEN NEWQUOTE.QUOTE >=
(SELECT MAX(QUOTE)
FROM QUOTEHISTORY　
WHERE SYMBOL = NEWQUOTE.SYMBOL　
AND YEAR(TIMESTAMP) = YEAR(CURRENT DATE) )　
THEN 'High'

(5)

WHEN NEWQUOTE.QUOTE <=　
(SELECT MIN(QUOTE)
FROM QUOTEHISTORY
WHERE SYMBOL = NEWQUOTE.SYMBOL　
AND YEAR(TIMESTAMP) = YEAR(CURRENT DATE) )　
THEN 'Low'

(6)

WHEN NEWQUOTE.QUOTE > OLDQUOTE.QUOTE
THEN 'Rising'
WHEN NEWQUOTE.QUOTE < OLDQUOTE.QUOTE　
THEN 'Dropping'
WHEN NEWQUOTE.QUOTE = OLDQUOTE.QUOTE
THEN 'Steady'
END　



(1)
此代码块将名为 STOCK_STATUS 的触发器定义为一个应该在更新 CURRENTQUOTE 表的 QUOTE 列之前激活的触发器。第二行指定，在将 CURRENTQUOTE 表的实际更新所引起的任何更改应用于数据库之前，要应用触发操作。第二行也意味着触发操作将不会激活任何其他触发器。第三行指定一些名称，必须将这些名称作为列名的限定符用于新值 (NEWQUOTE) 和旧值 (OLDQUOTE)。用这些相关名（NEWQUOTE 和 OLDQUOTE）限定的列名称为转换变量。第四行表示应对每一行都执行触发操作。