开这篇贴的目的是让大家就实例来交流SQL中, 那些平常不怎么被人所使用而其实很有用的命令.
希望大家把自己的心得和经验都贡献出来 然后让版主固顶.
我先来抛块砖:
一. 创建表的时候 使用计算列
CREATE TABLE t1
(P int IDENTITY(1,1),
C int NOT NULL,
M int NOT NULL,
T AS (C+ M)
)
insert into t1 values(1,2)
select t from t1
P C M T
----------- ----------- ----------- -----------
1 1 2 3
(1 row(s) affected)
这里T就是一个计算列. Insert Update语句都不能对计算列字段操作,但是select语句可以从该列取得值.
创建计算列有什么用呢? 可以用于视图的触发器中.
一个视图,是多个表的数据结合在一起显示的.
比如我们创建2个表,存放Employee的FirstName和LastName:
create table name1
(
EmployeeID int PRIMARY KEY,
Firstname varchar(100)
)
create table name2
(
EmployeeID int,
Lastname varchar(100)
)
再创建一个显示Employee全名的view
CREATE VIEW vw_Employee_FullName
AS
SELECT a.EmployeeID, FirstName + ';' + LastName AS FullName
FROM Name1 a join Name2 b on a.EmployeeID =b.EmployeeID
这时候设计一个视图的update操作的instead of触发器
CREATE TRIGGER InsteadInsertName on vw_Employee_FullName
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO Name1 (EmployeeID,Firstname)
SELECT EmployeeID,LEFT(FullName,(CHARINDEX(';', FullName) - 1))
FROM inserted
INSERT INTO Name2 (EmployeeID,Lastname)
SELECT EmployeeID,RIGHT(FullName,LEN(FullName)-CHARINDEX(';', FullName))
FROM inserted
END
这样 从前台插入employee新数据的时候,我们只要把向view中插入就可以了,由view的触发器来向2个表中插入数据
insert into vw_Employee_FullName values (1,'George;Bush')
select * from name1
select * from name2
EmployeeID Firstname
----------- ----------------------------------------------------------------------------------------------------
1 George
(1 row(s) affected)
EmployeeID Lastname
----------- ----------------------------------------------------------------------------------------------------
1 Bush
(1 row(s) affected)
二. 外键之间的级联操作
通常情况下,我们使用外键时,在插入和删除数据的时候一定要注意顺序,插入时要先主表再从表, 删除时要先从表再主表,更新时还不能任意更新外键中的列.
外键也是约束,这些限制是为了保证数据库数据的完整性和一致性,写代码的时候往往一不注意就会得到异常.
实际上系统也允许另外一种工作方式: 更改外键的列时候,主动更新外键关联的其它表的相关列.
创建外键时 用 ON DELETE { CASCADE | NO ACTION },ON Update { CASCADE | NO ACTION }声明是否启用级联操作 默认是NO Action.
ON DELETE NO ACTION
指定如果试图删除某行，而该行含有由其它表的现有行中的外键所引用的键，则产生错误并回滚 DELETE。
ON UPDATE NO ACTION
指定如果试图更新某行中的键值，而该行含有由其它表的现有行中的外键所引用的键，则产生错误并回滚 UPDATE。
ON DELETE CASCADE
指定如果试图删除某行，而该行含有由其它表的现有行中的外键所引用的键，则也将删除所有包含那些外键的行。如果在目标表上也定义了级联引用操作，则对从那些表中删除的行同样采取指定的级联操作。
ON UPDATE CASCADE
指定如果试图更新某行中的键值，而该行的键值由其它表的现有行中的外键所引用，则所有外键值也将更新成为该键指定的新值。如果在目标表上也定义了级联引用操作，则对在那些表中更新的键值同样采取指定的级联操作。
仍然以上面的例子,重新创建name2 并建立外键
Drop table name2
CREATE TABLE Name2
(
EmployeeID int primary key FOREIGN KEY REFERENCES Name1(EmployeeID) ON DELETE Cascade ON Update Cascade,
Lastname varchar(100)
)
update name1 set EmployeeID = 3 where EmployeeID = 1
select * from name1
select * from name2
(1 row(s) affected)
EmployeeID Firstname
----------- ----------------------------------------------------------------------------------------------------
3 George
(1 row(s) affected)
EmployeeID Lastname
----------- ----------------------------------------------------------------------------------------------------
3 Bush
(1 row(s) affected)
****注意 不要试图去update name2, 那是不合法的. 仅仅允许更新外键中主表的列, 并且更新自动传递到子表.
delete name1 where EmployeeID = 3
select * from name1
select * from name2
EmployeeID Firstname
----------- ----------------------------------------------------------------------------------------------------
(0 row(s) affected)
EmployeeID Lastname
----------- ----------------------------------------------------------------------------------------------------
(0 row(s) affected)
**** 注意 这里delete name2也是合法的 但是不会影响到主表name1.
级联是允许多级的, 假如还有表name3, 以name2为主表建立了外键:
CREATE TABLE Name3
(
EmployeeID int FOREIGN KEY REFERENCES Name2(EmployeeID) ON DELETE Cascade ON Update Cascade,
MiddleName varchar(100)
)
那么对name1 的EmployeeID做update, 或者删除行的会, name3也会做同样操作
SQL帮助上是这样描述的:
由单个 DELETE 或 UPDATE 触发的一系列级联引用操作必须构成不包含循环引用的树。
在 DELETE 或 UPDATE 所产生的所有级联引用操作的列表中，每个表只能出现一次。
级联引用操作树到任何给定表的路径必须只有一个。
树的任何分支在遇到指定了 NO ACTION 或默认为 NO ACTION 的表时终止。
好啊
