转载地址:
http://www.cnblogs.com/Blog-/archive/2011/03/06.html
Server里面也就是对某一个表的一定的操作，触发某种条件，从而执行的一段程序。触发器是一个特殊的存储过程。
常见的触发器有三种：分别应用于Insert , Update , Delete 事件。
我为什么要使用触发器？比如，这么两个表：
Create Table Student( --学生表
StudentID int
primary key, --学号
....
)
Create Table
BorrowRecord( --学生借书记录表
BorrowRecord int
identity(1,1), --流水号
StudentID int ,
--学号
BorrowDate datetime, --借出时间
ReturnDAte Datetime, --归还时间
...
)
用到的功能有:
1.如果我更改了学生的学号,我希望他的借书记录仍然与这个学生相关(也就是同时更改借书记录表的学号);
2.如果该学生已经毕业，我希望删除他的学号的同时，也删除它的借书记录。
等等。
这时候可以用到触发器。对于1，创建一个Update触发器：
Create Trigger truStudent
On Student --在Student表中创建触发器
for
Update --为什么事件触发
As --事件触发后所要做的事情
if
Update(StudentID)
begin
Update
BorrowRecord
Set StudentID=i.StudentID
From
BorrowRecord br , Deleted d ,Inserted i --Deleted和Inserted临时表
Where br.StudentID=d.StudentID
end
理解触发器里面的两个临时的表：Deleted , Inserted 。注意Deleted
与Inserted分别表示触发事件的表“旧的一条记录”和“新的一条记录”。
一个数据库系统中有两个虚拟表用于存储在表中记录改动的信息，分别是：
虚拟表Inserted 虚拟表Deleted
在表记录新增时 存放新增的记录 不存储记录
修改时 存放用来更新的新记录 存放更新前的记录
删除时
不存储记录 存放被删除的记录
一个Update
的过程可以看作为：生成新的记录到Inserted表，复制旧的记录到Deleted表，然后删除Student记录并写入新纪录。
对于2，创建一个Delete触发器
Create trigger trdStudent
On Student
for Delete
As
Delete BorrowRecord
From BorrowRecord br , Delted d
Where br.StudentID=d.StudentID
从这两个例子我们可以看到了触发器的关键：A.2个临时的表；B.触发机制。
SQL触发器实例2
/*
建立虚拟测试环境，包含：表[卷烟库存表]，表[卷烟销售表]。
请大家注意跟踪这两个表的数据，体会触发器到底执行了什么业务逻辑，对数据有什么影响。
为了能更清晰的表述触发器的作用，表结构存在数据冗余，且不符合第三范式，这里特此说明。
*/
USE Master
GO
IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE XTYPE = ’U’ AND NAME =
’卷烟库存表’)
DROP TABLE 卷烟库存表
GO
IF EXISTS (SELECT NAME FROM SYSOBJECTS
WHERE XTYPE = ’U’ AND NAME = ’卷烟销售表’)
DROP TABLE 卷烟销售表
GO
--业务规则：销售金额 = 销售数量 * 销售单价 业务规则。
CREATE TABLE 卷烟销售表
(
卷烟品牌 VARCHAR(40) PRIMARY KEY NOT NULL,
购货商 VARCHAR(40) NULL,
销售数量
INT NULL,
销售单价 MONEY NULL,
销售金额 MONEY NULL
)
GO
--业务规则：库存金额 = 库存数量 * 库存单价 业务规则。
CREATE TABLE 卷烟库存表
(
卷烟品牌 VARCHAR(40) PRIMARY KEY NOT NULL,
库存数量 INT NULL,
库存单价 MONEY
NULL,
库存金额 MONEY NULL
)
GO
--创建触发器，示例1
/*
创建触发器[T_INSERT_卷烟库存表]，这个触发器较简单。
说明： 每当[卷烟库存表]发生 INSERT 动作，则引发该触发器。
触发器功能： 强制执行业务规则，保证插入的数据中，库存金额 = 库存数量 * 库存单价。
注意：
[INSERTED]、[DELETED]为系统表，不可创建、修改、删除，但可以调用。
重要： 这两个系统表的结构同插入数据的表的结构。
*/
IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE XTYPE = ’TR’ AND NAME =
’T_INSERT_卷烟库存表’)
DROP TRIGGER T_INSERT_卷烟库存表
GO
CREATE TRIGGER
T_INSERT_卷烟库存表
ON 卷烟库存表
FOR INSERT
AS
--提交事务处理
BEGIN
TRANSACTION
--强制执行下列语句，保证业务规则
UPDATE 卷烟库存表
SET 库存金额 = 库存数量 * 库存单价
WHERE 卷烟品牌 IN (SELECT 卷烟品牌 from INSERTED)
COMMIT TRANSACTION
GO
/*
针对[卷烟库存表]，插入测试数据：
注意，第一条数据（红塔山新势力）中的数据符合业务规则，
第二条数据（红塔山人为峰）中，[库存金额]空，不符合业务规则，
第三条数据（云南映像）中，[库存金额]不等于[库存数量]乘以[库存单价]，不符合业务规则。
第四条数据库存数量为0。
请注意在插入数据后，检查[卷烟库存表]中的数据是否 库存金额 = 库存数量 * 库存单价。
*/
INSERT INTO
卷烟库存表(卷烟品牌,库存数量,库存单价,库存金额)
SELECT ’红塔山新势力’,100,12,1200 UNION ALL
SELECT
’红塔山人为峰’,100,22,NULL UNION ALL
SELECT ’云南映像’,100,60,500 UNION ALL
SELECT
’玉溪’,0,30,0
GO
--查询数据
SELECT * FROM 卷烟库存表
GO
/*
结果集
RecordId 卷烟品牌 库存数量 库存单价 库存金额
-------- ------------
-------- ------- ---------
1 红塔山新势力 100 12.0000 1200.0000
2 红塔山人为峰 100
22.0000 2200.0000
3 云南映像 100 60.0000 6000.0000
4 玉溪 0 30.0000 .0000
（所影响的行数为 4 行）
*/
--触发器示例2
/*
创建触发器[T_INSERT_卷烟销售表]，该触发器较复杂。
说明: 每当[卷烟库存表]发生 INSERT 动作，则引发该触发器。
触发器功能： 实现业务规则。
业务规则: 如果销售的卷烟品牌不存在库存或者库存为零，则返回错误。
否则则自动减少[卷烟库存表]中对应品牌卷烟的库存数量和库存金额。
*/
IF EXISTS (SELECT NAME FROM
SYSOBJECTS WHERE XTYPE = ’TR’ AND NAME = ’T_INSERT_卷烟销售表’)
DROP TRIGGER
T_INSERT_卷烟销售表
GO
CREATE TRIGGER T_INSERT_卷烟销售表
ON 卷烟销售表
FOR
INSERT
AS
BEGIN TRANSACTION
--检查数据的合法性：销售的卷烟是否有库存，或者库存是否大于零
IF
NOT EXISTS (
SELECT 库存数量
FROM 卷烟库存表
WHERE 卷烟品牌 IN (SELECT 卷烟品牌 FROM
INSERTED)
)
BEGIN
--返回错误提示
RAISERROR(’错误！该卷烟不存在库存，不能销售。’,16,1)
--回滚事务
ROLLBACK
RETURN
END
IF EXISTS (
SELECT 库存数量
FROM 卷烟库存表
WHERE 卷烟品牌 IN (SELECT 卷烟品牌 FROM INSERTED) AND
库存数量 <=
0
)
BEGIN
--返回错误提示
RAISERROR(’错误！该卷烟库存小于等于0，不能销售。’,16,1)
--回滚事务
ROLLBACK
RETURN
END
--对合法的数据进行处理
--强制执行下列语句，保证业务规则
UPDATE 卷烟销售表
SET 销售金额 = 销售数量 * 销售单价
WHERE
卷烟品牌 IN (SELECT 卷烟品牌 FROM INSERTED)
DECLARE @卷烟品牌 VARCHAR(40)
SET
@卷烟品牌 = (SELECT 卷烟品牌 FROM INSERTED)
DECLARE @销售数量 MONEY
SET @销售数量 =
(SELECT 销售数量 FROM INSERTED)
UPDATE 卷烟库存表
SET 库存数量 = 库存数量 - @销售数量,
库存金额 = (库存数量 - @销售数量)*库存单价
WHERE 卷烟品牌 = @卷烟品牌
COMMIT TRANSACTION
GO
--请大家自行跟踪[卷烟库存表]和[卷烟销售表]的数据变化。
--针对[卷烟销售表]，插入第一条测试数据，该数据是正常的。
INSERT INTO 卷烟销售表(卷烟品牌,购货商,销售数量,销售单价,销售金额)
SELECT
’红塔山新势力’,’某购货商’,10,12,1200
GO
--针对[卷烟销售表]，插入第二条测试数据，该数据 销售金额 不等于
销售单价 * 销售数量。
--触发器将自动更正数据，使 销售金额 等于 销售单价 * 销售数量。
INSERT INTO
卷烟销售表(卷烟品牌,购货商,销售数量,销售单价,销售金额)
SELECT ’红塔山人为峰’,’某购货商’,10,22,2000
GO
--针对[卷烟销售表]，插入第三条测试数据，该数据中的卷烟品牌在 卷烟库存表中找不到对应。
--触发器将报错。
INSERT INTO 卷烟销售表(卷烟品牌,购货商,销售数量,销售单价,销售金额)
SELECT
’红河V8’,’某购货商’,10,60,600
GO
/*
结果集
服务器: 消息 50000，级别 16，状态
1，过程 T_INSERT_卷烟销售表，行 15
错误！该卷烟不存在库存，不能销售。
*/
--针对[卷烟销售表]，插入第三条测试数据，该数据中的卷烟品牌在 卷烟库存表中库存为0。
--触发器将报错。
INSERT INTO 卷烟销售表(卷烟品牌,购货商,销售数量,销售单价,销售金额)
SELECT
’玉溪’,’某购货商’,10,30,300
GO
/*
结果集
服务器: 消息 50000，级别 16，状态 1，过程
T_INSERT_卷烟销售表，行 29
错误！该卷烟库存小于等于0，不能销售。
*/
--查询数据
SELECT * FROM
卷烟库存表
SELECT * FROM 卷烟销售表
GO
/*
补充：
1、本示例主要通过一个简单的业务规则实现来进行触发器使用的说明，具体的要根据需要灵活处理；
2、关于触发器要理解并运用好 INSERTED
，DELETED 两个系统表；
3、本示例创建的触发器都是 FOR INSERT ,具体的语法可参考：
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
Trigger语法
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
CREATE
TRIGGER trigger_name
ON { table | view }
[ WITH ENCRYPTION ] --用于加密触发器
{
{ { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] }
[
WITH APPEND ]
[ NOT FOR REPLICATION ]
AS
[ { IF UPDATE ( column )
[ { AND | OR } UPDATE ( column ) ]
[ ...n ]
| IF ( COLUMNS_UPDATED (
) { bitwise_operator } updated_bitmask )
{ comparison_operator }
column_bitmask [ ...n ]
} ]
sql_statement [ ...n ]
}
}
4、关于触发器，还应该注意
(1)、DELETE 触发器不能捕获 TRUNCATE TABLE 语句。
(2)、触发器中不允许以下 Transact-SQL 语句：
ALTER DATABASE CREATE DATABASE DISK INIT
DISK RESIZE DROP DATABASE LOAD DATABASE
LOAD LOG RECONFIGURE RESTORE
DATABASE
RESTORE LOG
(3)、触发器最多可以嵌套 32 层。
*/
--修改触发器
--实质上，是将 CREATE TRIGGER ... 修改为 ALTER TRIGGER ...即可。
--删除触发器
DROP TRIGGER xxx
GO
--删除测试环境
DROP TABLE 卷烟库存表
GO
DROP TABLE 卷烟销售表
GO
DROP TRIGGER T_INSERT_卷烟库存表
GO
DROP
TRIGGER T_INSERT_卷烟销售表
GO
##################################################################
触发器的基础知识和例子
：create trigger tr_name
on table/view
{for | after |
instead of } [update][,][insert][,][delete]
[with encryption]
as {batch
| if update (col_name) [{and|or} update (col_name)] }
说明：
1 tr_name
：触发器名称
2 on table/view ：触发器所作用的表。一个触发器只能作用于一个表
3 for 和after ：同义
4
after 与instead of :sql 2000新增项目afrer 与 instead of 的区别
After
在触发事件发生以后才被激活,只可以建立在表上
Instead of
代替了相应的触发事件而被执行,既可以建立在表上也可以建立在视图上
5 insert、update、delete：激活触发器的三种操作，可以同时执行，也可选其一
6 if update
(col_name)：表明所作的操作对指定列是否有影响，有影响，则激活触发器。此外，因为delete 操作只对行有影响，
所以如果使用delete操作就不能用这条语句了(虽然使用也不出错，但是不能激活触发器，没意义)。
7
触发器执行时用到的两个特殊表：deleted ,inserted
deleted 和inserted
可以说是一种特殊的临时表，是在进行激活触发器时由系统自动生成的，其结构与触发器作用的表结构是一
样的，只是存放 的数据有差异。
续
下面表格说明deleted 与inserted 数据的差异
deleted 与inserted 数据的差异
Inserted
存放进行insert和update 操作后的数据
Deleted
存放进行delete 和update操作前的数据
注意：update 操作相当于先进行delete 再进行insert ,所以在进行update操作时，修改前的数据拷贝一条到deleted 表中，修改后
的数据在存到触发器作用的表的同时，也同时生成一条拷贝到insered表中
/////////
CREATE TRIGGER [TRIGGER admixture_receive_log] ON dbo.chl_lydj
FOR
UPDATE
AS
begin
declare @djsfxg char(10) declare @wtbh
char(20)
select @wtbh=wtbh from inserted
update ly_tzk set
djsfxg='已修改' where wtbh=@wtbh
end
if
(select data_sfjl from t_logsetup)='是'
begin
declare @oldcjmc char
(100) declare @oldlyrq datetime
declare @oldbzbh char
(60) declare @oldzl char (20)
declare @olddj char (10)
declare @newcjmc char (100) declare @newlyrq datetime
declare
@newbzbh char (60) declare @newzl char (20)
declare @newdj char
(10)
declare @xgr char (20)
select
@oldcjmc=cjmc,@oldlyrq=lyrq,@oldbzbh=bzbh,@oldzl=zl,@olddj=dj from
deleted
select
@newcjmc=cjmc,@newlyrq=lyrq,@newbzbh=bzbh,@newzl=zl,@newdj=dj from
inserted
select @xgr=xgr from t_modifyuser where
@wtbh=wtbh
if
@oldcjmc<>@newcjmc
begin
insert
into t_modifylog (wtbh, mod_time, mod_table, mod_field, ori_value, now_value,
mod_people) values
(@wtbh,getdate(), 'chl_lydj','cjmc',
@oldcjmc, @newcjmc, @xgr)
end
end
//////////修改时，直接把‘create’改为‘alter’即可
/////////////////////////
CREATE TRIGGER [TRIGGER ly_tzk_syf] ON dbo.ly_tzk
FOR insert
AS
begin
declare @clmc char(100) declare @dwbh char(100)
declare @syf char(100) declare @dwgcbh char(100) declare @wtbh char(50)
declare @dj_1 money declare @feiyong_z money declare
@feiyong_xf money declare @feiyong_sy money
declare @dj
char(20)
select @wtbh=wtbh , @clmc=clmc , @dwbh=dwbh ,@syf=syf from
inserted
select @dj=dj from feihao_bz where clmc=@clmc
select @feiyong_z=feiyong_z,
@feiyong_xf=feiyong_xf, @feiyong_sy=feiyong_sy from gongchengxinxi where dwgcbh=@dwbh
set @dj_1=convert(money ,@dj)
if @dj_1 <>0
begin
set @feiyong_xf=@feiyong_xf+@dj_1
set
@feiyong_sy=@feiyong_sy-@dj_1
update ly_tzk set syf=@dj where wtbh=@wtbh
update
gongchengxinxi set feiyong_xf=@feiyong_xf, feiyong_sy=@feiyong_sy where dwgcbh=@dwbh
end
else
update ly_tzk set syf=convert(char , 0.0) where wtbh=@wtbh
end
//////////////////////
CREATE TRIGGER [TRIGGER ly_tzk_syf_shanchu] ON dbo.ly_tzk
FOR delete
AS
begin
declare @clmc char(100) declare @dwbh
char(100) declare @dwgcbh char(100) declare @wtbh char(50)
declare @feiyong_z money declare @feiyong_xf money declare @feiyong_sy
money
declare @syf char(100) declare @syf_1 money
--declare @dj char(20) declare @dj_1 money
select @wtbh=wtbh ,
@clmc=clmc , @dwbh=dwbh ,@syf=syf from inserted
--select @dj=dj
from feihao_bz where clmc=@clmc
select @feiyong_z=feiyong_z, @feiyong_xf=feiyong_xf, @feiyong_sy=feiyong_sy from
gongchengxinxi where dwgcbh=@dwbh
set @syf_1=convert(money ,@syf)
if @syf_1 <>0
begin
set
@feiyong_xf=@feiyong_xf-@syf_1
set
@feiyong_sy=@feiyong_sy+@syf_1
update gongchengxinxi set feiyong_xf=@feiyong_xf, feiyong_sy=@feiyong_sy where dwgcbh=@dwbh
end
end
//////////////////////
posted @ 2011-03-06 19:56 丨灬風塵一丬 阅读(125) 评论(0) 编辑
SQL
Server存储过程入门案例详解
有了存储过程之后，与数据库的交互就没有必要在程序中写一堆的SQL语句，而只需用一条语句调用适当的存储过程来完成就可以了。
【专家网独家】
问题提出
我使用过几次SQL
Server，但所有与数据库的交互都是通过应用程序的编码来实现的。我不知到在哪里使用存储过程，也不了解实现存储过程需要做哪些工作。希望能详细说明。
专家答疑
存储过程是存储于数据库中的一组T-SQL语句。有了存储过程之后，与数据库的交互就没有必要在程序中写一堆的SQL语句，而只需用一条语句调用适当的存储过程来完成就可以了。另外，由于代码是存储在数据库中，我们也可以在不同的应用程序或查询窗口中不断的重复利用那些代码。下面将讲述一些简单的例子，它们将说明如何构造和使用存储过程。
下面的例子将简单的说明如何创建存储过程。以下所有例子均使用AdventureWorks数据库。其它的数据库和应用程序可以依此类推。
例 1 – 简单的存储过程
这个简单的存储过程将实现如下功能：从Person.Contact表中取出第一条记录。
CREATE PROCEDURE uspGetContact |
创建完上面的语句后，使用下面的命令可以执行该存储过程。
EXEC uspGetContact |
查询的结果如下：
例 2 – 带参数的存储过程
这个例子在上个例子的基础上做了一点修改：传入了一个参数，根据传入的参数来查询相应的记录。为了更好地利用上面的例子，这次我们就不用重新再创建一个存储过程了，而是使用ALTER
PROCEDURE(注意：不是CREATE PROCEDURE)来修改例1中已经创建好的存储过程。代码如下：
ALTER PROCEDURE uspGetContact @LastName NVARCHAR(50) |
下面显示了运行存储过程的2种不同方法。第一种方法仅仅传入了我们想要的参数值。而第二种方法包含了参数名和参数值。任意一种方法都可以运行该例中的存储过程。
EXEC uspGetContact 'Alberts' |
查询结果如下：
例 3 – 带输入和输出参数的存储过程
这个例子中既有输入参数也有输出参数。在存储过程中查询后得到的ContactID可以利用输出参数进行回传。回传参数用户查询人员表中的其他字段，如ContactID、FirstName、LastName以及这个人的任何地址记录。
首先，我们按如下方式修改存储过程uspGetContact。其次，运行下面代码执行该存储过程。最后，基于传回的值可以查询人员的姓名和地址等信息。
ALTER PROCEDURE uspGetContact @LastName NVARCHAR(50), @ContactID INT output |
存储过程修改后，运行如下代码将执行该存储过程。如果ContactID有值，就会返回人员和地址信息。
DECLARE @ContactID INT |
查询结果如下：
例 4 – 使用RAISERROR 语句的存储过程
在这个例子中，将把例3中的2个步骤整合到一个存储过程中。存储过程的第一步是取得ContactID，第二步是查询人员的姓名和地址信息。然后添加RAISERROR语句，如果查询不到记录则返回错误信息。
RAISERROR语句可以用来控制应用程序处理无数据或其它错误的方式。将该存储过程执行2次，看看能找到数据和不能找到数据的情况下各返回什么。
ALTER PROCEDURE uspGetContact @LastName NVARCHAR(50) |
查询结果如下：
EXEC uspGetContact @LastName='Job' |
当第一步查询无数据时，整个存储过程的执行结果如下：
例 5 -调用存储过程的存储过程
这个例子中有2个存储过程。第一个存储过程uspFindContact查找第一条带有地址信息的记录并将ContactID返回给调用它的存储过程，然后显示人员和地址信息。
CREATE PROCEDURE uspFindContact @LastName NVARCHAR(50), @ContactID INT output |
下面的代码对uspGetContact做了一点修改：调用uspFindContact并返回结果集。
ALTER PROCEDURE uspGetContact @LastName NVARCHAR(50) |
第一步查询结果如下：
EXEC uspGetContact @LastName='Job' |
第一步查询结果如下：
例 6 -带评论的存储过程
最后这个例子基于存储过程uspGetContact，并增加了评论功能。展示了如何在一个存储过程中使用评论。存储过程中的评论有2种实现方式：1.使用--
2.使用/*来作为评论的开始，以*/作为结束。其余部分和上面的例子一样。
ALTER PROCEDURE uspGetContact @LastName NVARCHAR(50) |
这些都是非常简单的例子，通过这些例子，希望能让大家知道：创建一个SQL
Server的存储过程并不难。如果能够在查询窗口或者应用程序中得心应手的使用查询语句，那么按照上面的例子，也就能够非常容易的掌握存储过程的使用。
IT专家网原创文章，未经许可，严禁转载!
posted @ 2011-03-06 19:50 丨灬風塵一丬 阅读(54) 评论(0) 编辑
【转】SQL学习（二）
1. SQL中的集合函数
名 称 |
参数类型 |
结果类型 |
描述 |
COUNT SUM AVG MAX MIN |
任意（可以是*） 数值型 数值型 字符型或数值型 字符型或数值型 |
数值型 数值型 数值型 同参数的内容一样 同参数的内容一样 |
出现次数 参数的和 参数的平均值 最大值 最小值 |
注： 在WHERE子句的比较操作中不能出现集合函数，除非它们是出现在子查询的选择列表中
例： select cid from customers
where discnt < (select max(discnt) from customers);
2. SQL中的分组
group by ... having ...通用形式
例：当某个代理商所订购的某样品产品的总量超过1000时，打印出所有满足条件的产品和代理商的ID及这个总量
select pid ,aid, sum(qty) as TOTAL from orders
group by pid,aid
having sum(qty)>1000;
执行过程：
- 首先对from子句中的所有表做笛卡尔积
- 接着删除不满足WHERE子句的行
- 然后根据GROUP BY子句对剩余的进行分组
- 最后求出选择列表中表达式的值
- HAVING子句紧跟在GROUP BY之后但先于对选择列表的表达式的计算
注： HAVING一般情况不会单独出现，一般和GROUP BY一起出现，如果省略了GROUP BY子句，那么HAVING子句将把
整个结果当做一个组来使用
1.ODER BY
order by cid desc 对列cid的值从大到小排列，如果省略desc则从小到大排列
2.CAST
cast以一种类型的值为参数并把它显示的转化为另一种类型
cast (substring(cid from 2 for 3) as integer)
cast (o.qty as char(10))
3. CASE
通用形式：case
when search_conditionl then resulet1
when search_condition2 then resulet2
...
else resul(N+1)
end
例： case when city > 'Al1' then city else 'Atlanta' end
4.BETWEEN谓词
expr1 [NOT] BETWEEN expr2 and expr3
其含义（不考虑NOT）实际上就是：expr2<=expr1 and expr1<=expr3
用between谓词比and链接表达式效率高
5.LIKE谓词
colname [NOT] LIKE val [escape val]
第一个val代表模式串，也可以是一个程序变量
下面列出包括通配符在内的所有可用在模式串中的特殊字符：
模式串中的字符 |
含义 |
下划线（_） 百分号(%) 转义字符 所有其他字符 |
任意单个字符的通配符 包含零个或多个字符的任意序列的通配符 用在需要按字面含义引用的字符之前 代表他们自己 |
例：检索cname值以字母"A"打头的顾客的所有信息
select * from customers where cname like 'A%';
如果要在模式串中按字面意思来引用_,%则用转义字符(escape)\
例：检索cname值以“Tip_”打头并且后面跟着任意个字符的顾客的cid值
select * from customers where cname like 'Tip\_%' escape '\';
6. JOIN
1) inter jion
select cname,city,latiude,longitude
from customers c join cities x on c.city =x.cityname;
2) OUTER JOIN
以下2个表S和T
S T
C A A B
c1 a1 a1 b1
c3 a3 a2 b2
c4 a4 a3 b3
做select * from S full outer join t using(A)
结果为：
C A B
c1 a1 b1
c3 a3 b3
c4 a4 null
null a2 b2
也可以用以下形式实现：
select * from S left join using(A) union select * from S right jion T using
(A);
1. INSERT
通用形式：
INSERT INTO tablename [colname {,colname...}]
{VALUES (expr ｜NULL ｜ Subquery)}
例：1. insert into orders (ordno,month)
values (1107.'aug');
2. insert into swcusts
select * from customers
where city in ('dallas','austin');
2. UPDATE
例：1.update agents set percent=1.1*percent where city='New York';
2.update customers set discnt=1.1*discnt where cid in
(select cid from orders group by cid having
sum(dollars)>1000);
3. DELETE
例：1.delete from agents where city ='New York';
2. delete from agents where in
(select aid from orders group by adi having
sum(dollars)<600);
posted @ 2011-03-06 19:42 丨灬風塵一丬 阅读(60) 评论(0) 编辑
【转】SQL学习（一）
一 建表：
CREATE TABLE tablename(colname datatype [NOT NULL]
{,colname datatype [NOT NULL]...}
[,PRIMARY KEY (colname
{,colname...})];
注： ｜,[] ,{}, ...在sql里不会出现，所以用其标记
二 简单的select语句：
select distinct ordno,x.cid,x.aid,x.pid,
.40*(x.qty*p.price)-.01*（c.dicnt+a.ercent）*（x.qty*p.price）as
heji
from orders as x,customers as c,agents as a,products as
p
where c.cid=x.cid and a.aid= x.aid and
p.pid=x.aid;
x,c,a,p分别为orders,customers,agents,products的别名，关键字as可省略
ordno,cid, aid, pid ,heji为列名
heji
前面的as不可省略，他定义.40*(x.qty*p.price)-.01*（c.dicnt+a.ercent）*（x.qty*p.price）显示为heji
distinct确保检索后的每一行是唯一的
找出至少被两个顾客订购的产品的pid值
select distinct x1.pid
from orders x1,orders x2
where x1.pid=x2.pid and x1.pid<x2.pid;,
1. IN谓词IN谓词的通用表达式：expr in (Subquery)｜expr in (val {,val...})
例： 检索由住在Duluth的顾客和住在New York的代理商组成的所有订货记录的ordno值
select ordno from orders
where (cid,aid) in
(select cid,aid from customers c,agents a where
c.city='Duluth' and a.city='New York');
NOT IN谓词为当且仅当求解出的expr值不在子查询返回的集合中[expr NOT IN (Subquery)]
2. 量化比较谓词
通用形式 expr θ{SOME｜ANY｜ALL} (Subquery) where θ is some operator in the
set {<,<=,=,<>,>,>=}
该形式中的SOME与ANY含义相同，θ包含：{<,<=,=,<>,>,>=} <>是不等于
例：找出佣金百分率最小的代理商的aid值
select aid from agents where percent <=all (select percent from
agents);
注：谓词=SOME与谓词IN具有完全相同的效果
谓词<>all与NOT IN等价
谓词<>SOME不与 NOT IN等价
3. 谓词EXISTS
通用形式 [NOT] EXISTS (Subquery) 注：EXISTS (Subquery)
为真当且仅当子查询返回一个非空集合
例：求出订购了产品P01又订购了产品P07的顾客的cid值
select distinct cid from orders x
where pid='p01' and exists
(select * from orders where cid=x.cid and
pid='p07');
NOT EXISTS 查询效果可用 NOT IN和等价谓词<>ALL替代
4. SQL查询会有很多等价形式
例：检索订购了产品p01的顾客所在的city名这一请求的表达式，有四种主要的Select语句表达式
select distinct city from customers where cid in
(select cid from orders where pid='p01');
select distinct city from customers where cid =any
(select cid from orders where pid='p01');
select distinct city from customers c where exists
(select * from oredrs o where c.cid=o.cid and
o.pid='p01');
select distinct city from customers c, orders o
where c.cid=o.cid and o.pid='p01';
1. UNION 运算符
实质就是关系运算中的并
例：建立一个包含了顾客所在的或者代理商所在的或者两者接在的城市的名单
select city from customers
union select city from agents;
当设计到的子查询项目大于或等于3时可用（）
(select city from customers
union select city from agents)
union all select city from products;
2. 除法： SQL "FOR ALL..."条件
如果我们所面临的查询要求被检索的对象集合必须符合某个带有“所有”这类关键词的条件，我们就按照下列步骤进行
1） 为要检索的对象命名并考虑如何用英文来表述要检索的候选对象的一个反例。在该反例中，前面提到的“所有”
对象中有至少一个对象不符合规定的条件。
2） 建立Select语句的搜索条件以选出步骤1所创建的所有反例。（步骤1和2必定会引用来自外部Select语句的对象，
所以我们要在如何用这些外部对象所在的表来引用他们这一问题上表现出一定的灵活性）
3） 建立包含步骤2所创建的语句的搜索条件，说明不存在上面定义的那种反例。这里将涉及到NOT EXISTS谓词
4） 用步骤3的搜索条件来建立最终的Select语句，检索所期望的对象。
上面的步骤序列通常会产生如下所示的嵌套子查询对：
select ... where not exists (select ... where not exists (select
... ));
例：找出具有下列性质的顾客的cid值：如果顾客c006订购了某种产品，那要检索的顾客也订购了该产品。
此题可以改写成：找出订购了所有被顾客c006订购的产品的cid值。
步骤1：我们称c.cid是符合题意的顾客并用英文构造反例
"There is a product ordered by customer c006 that is not
ordered by c.cid."
步骤2：把英文表述成搜索条件，我们将被c006订购的产品命名为p.pid
cond1: p.pid in (select pid from orders x where
x.cid='c006')
and not exists (select * from orders y
where y.pid=p.pid and
y.cid=c.cid )
步骤3：j建立表示这种反例不存在的条件：
cond2: not exists (select p.pid from prducts p
where p.pid in (select pid from orders
x
where x.cid='c006')and
not exists (select * from orders y
where y.pid=p.pid and
y.cid=c.cid))
步骤4：建立最终的Select
select cid from customers c
where not exists (select p.pid from
prducts p
where p.pid in (select pid from orders
x
where x.cid='c006')and
not exists (select * from orders y
where y.pid=p.pid and
y.cid=c.cid));
该select的一个变体是：
select cid from customers c
where not exists (select z.pid from oredrs
z
where z.cid='c006' and
not exists (select * from orders y
where y.pid=z.pid and y.cid =
c.cid));
3.表别名用法
例：检索对同一产品至少订购了两次的所有顾客的名字。
select cname from (select o.cid as spcid from orders o,oreders x where
o.cid=x.cid
and o.pid=x.pid and o.ordno<>x.o.ordon)y, customers
c
where y.spcid=c.cid;
该查询中为为子查询的结果指定了别名y，并为子查询所检索的列提供了别名spcid
posted @ 2011-03-06 19:39 丨灬風塵一丬 阅读(25) 评论(0) 编辑
【转】 HTML JS
运行测试小工具
<html> function runCode(obj) function saveCode(obj) <body> <textarea name="textarea" style="width:96%" rows="33" |
