sql宝典
来源：互联网 发布：java实现远程监控 编辑：程序博客网 时间：2024/04/29 04:06
1、说明：创建数据库
CREATE DATABASE database-name
2、说明：删除数据库
drop database dbname
3、说明：备份sql server
--- 创建 备份数据的 device
USE master
EXEC sp_addumpdevice 'disk', 'testBack', 'c:/mssql7backup/MyNwind_1.dat'
--- 开始 备份
BACKUP DATABASE pubs TO testBack
4、说明：创建新表
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)
根据已有的表创建新表：
A：create table tab_new like tab_old (使用旧表创建新表)
B：create table tab_new as select col1,col2… from tab_old definition only
5、说明：删除新表
drop table tabname
6、说明：增加一个列
Alter table tabname add column col type
注：列增加后将不能删除。DB2中列加上后数据类型也不能改变，唯一能改变的是增加varchar类型的长度。
7、说明：添加主键： Alter table tabname add primary key(col)
说明：删除主键： Alter table tabname drop primary key(col)
8、说明：创建索引：create [unique] index idxname on tabname(col….)
删除索引：drop index idxname
注：索引是不可更改的，想更改必须删除重新建。
9、说明：创建视图：create view viewname as select statement
删除视图：drop view viewname
10、说明：几个简单的基本的sql语句
选择：select * from table1 where 范围
插入：insert into table1(field1,field2) values(value1,value2)
删除：delete from table1 where 范围
更新：update table1 set field1=value1 where 范围
查找：select * from table1 where field1 like ’%value1%’ ---like的语法很精妙，查资料!
排序：select * from table1 order by field1,field2 [desc]
总数：select count as totalcount from table1
求和：select sum(field1) as sumvalue from table1
平均：select avg(field1) as avgvalue from table1
最大：select max(field1) as maxvalue from table1
最小：select min(field1) as minvalue from table1
11、说明：几个高级查询运算词
A： UNION 运算符
UNION 运算符通过组合其他两个结果表（例如 TABLE1 和 TABLE2）并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时（即 UNION ALL），不消除重复行。两种情况下，派生表的每一行不是来自 TABLE1 就是来自 TABLE2。
B： EXCEPT 运算符
EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL)，不消除重复行。
C： INTERSECT 运算符
INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL)，不消除重复行。
注：使用运算词的几个查询结果行必须是一致的。
12、说明：使用外连接
A、left outer join：
左外连接（左连接）：结果集几包括连接表的匹配行，也包括左连接表的所有行。
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
B：right outer join:
右外连接(右连接)：结果集既包括连接表的匹配连接行，也包括右连接表的所有行。
C：full outer join：
全外连接：不仅包括符号连接表的匹配行，还包括两个连接表中的所有记录。
二、提升
1、说明：复制表(只复制结构,源表名：a 新表名：b) (Access可用)
法一：select * into b from a where 1<>1
法二：select top 0 * into b from a
2、说明：拷贝表(拷贝数据,源表名：a 目标表名：b) (Access可用)
insert into b(a, b, c) select d,e,f from b;
3、说明：跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用)
insert into b(a, b, c) select d,e,f from b in ‘具体数据库’ where 条件
例子：..from b in '"&Server.MapPath(".")&"/data.mdb" &"' where..
4、说明：子查询(表名1：a 表名2：b)
select a,b,c from a where a IN (select d from b ) 或者: select a,b,c from a where a IN (1,2,3)
5、说明：显示文章、提交人和最后回复时间
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b
6、说明：外连接查询(表名1：a 表名2：b)
select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c
7、说明：在线视图查询(表名1：a )
select * from (SELECT a,b,c FROM a) T where t.a > 1;
8、说明：between的用法,between限制查询数据范围时包括了边界值,not between不包括
select * from table1 where time between time1 and time2
select a,b,c, from table1 where a not between 数值1 and 数值2
9、说明：in 的使用方法
select * from table1 where a [not] in (‘值1’,’值2’,’值4’,’值6’)
10、说明：两张关联表，删除主表中已经在副表中没有的信息
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 )
11、说明：四表联查问题：
select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where .....
12、说明：日程安排提前五分钟提醒
SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5
13、说明：一条sql 语句搞定数据库分页
select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段
14、说明：前10条记录
select top 10 * form table1 where 范围
15、说明：选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.)
select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b)
16、说明：包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表
(select a from tableA ) except (select a from tableB) except (select a from tableC)
17、说明：随机取出10条数据
select top 10 * from tablename order by newid()
18、说明：随机选择记录
select newid()
19、说明：删除重复记录
Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...)
20、说明：列出数据库里所有的表名
select name from sysobjects where type='U'
21、说明：列出表里的所有的
select name from syscolumns where id=object_id('TableName')
22、说明：列示type、vender、pcs字段，以type字段排列，case可以方便地实现多重选择，类似select 中的case。
select type,sum(case vender when 'A' then pcs else 0 end),sum(case vender when 'C' then pcs else 0 end),sum(case vender when 'B' then pcs else 0 end) FROM tablename group by type
显示结果：
type vender pcs
电脑 A 1
电脑 A 1
光盘 B 2
光盘 A 2
手机 B 3
手机 C 3
23、说明：初始化表table1
TRUNCATE TABLE table1
24、说明：选择从10到15的记录
select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc
三、技巧
1、1=1，1=2的使用，在SQL语句组合时用的较多
“where 1=1” 是表示选择全部 “where 1=2”全部不选，
如：
if @strWhere !=''
begin
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where ' + @strWhere
end
else
begin
set @strSQL = 'select count(*) as Total from [' + @tblName + ']'
end
我们可以直接写成
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where 1=1 安定 '+ @strWhere
2、收缩数据库
--重建索引
DBCC REINDEX
DBCC INDEXDEFRAG
--收缩数据和日志
DBCC SHRINKDB
DBCC SHRINKFILE
3、压缩数据库
dbcc shrinkdatabase(dbname)
4、转移数据库给新用户以已存在用户权限
exec sp_change_users_login 'update_one','newname','oldname'
go
5、检查备份集
RESTORE VERIFYONLY from disk='E:/dvbbs.bak'
6、修复数据库
ALTER DATABASE [dvbbs] SET SINGLE_USER
GO
DBCC CHECKDB('dvbbs',repair_allow_data_loss) WITH TABLOCK
GO
ALTER DATABASE [dvbbs] SET MULTI_USER
GO
7、日志清除
SET NOCOUNT ON
DECLARE @LogicalFileName sysname,
@MaxMinutes INT,
@NewSize INT
USE tablename -- 要操作的数据库名
SELECT @LogicalFileName = 'tablename_log', -- 日志文件名
@MaxMinutes = 10, -- Limit on time allowed to wrap log.
@NewSize = 1 -- 你想设定的日志文件的大小(M)
-- Setup / initialize
DECLARE @OriginalSize int
SELECT @OriginalSize = size
FROM sysfiles
WHERE name = @LogicalFileName
SELECT 'Original Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @LogicalFileName
CREATE TABLE DummyTrans
(DummyColumn char (8000) not null)
DECLARE @Counter INT,
@StartTime DATETIME,
@TruncLog VARCHAR(255)
SELECT @StartTime = GETDATE(),
@TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY'
DBCC SHRINKFILE (@LogicalFileName, @NewSize)
EXEC (@TruncLog)
-- Wrap the log if necessary.
WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired
AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName)
AND (@OriginalSize * 8 /1024) > @NewSize
BEGIN -- Outer loop.
SELECT @Counter = 0
WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000))
BEGIN -- update
INSERT DummyTrans VALUES ('Fill Log')
DELETE DummyTrans
SELECT @Counter = @Counter + 1
END
EXEC (@TruncLog)
END
SELECT 'Final Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),size) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @LogicalFileName
DROP TABLE DummyTrans
SET NOCOUNT OFF
8、说明：更改某个表
exec sp_changeobjectowner 'tablename','dbo'
9、存储更改全部表
CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch
@OldOwner as NVARCHAR(128),
@NewOwner as NVARCHAR(128)
AS
DECLARE @Name as NVARCHAR(128)
DECLARE @Owner as NVARCHAR(128)
DECLARE @OwnerName as NVARCHAR(128)
DECLARE curObject CURSOR FOR
select 'Name' = name,
'Owner' = user_name(uid)
from sysobjects
where user_name(uid)=@OldOwner
order by name
OPEN curObject
FETCH NEXT FROM curObject INTO @Name, @Owner
WHILE(@@FETCH_STATUS=0)
BEGIN
if @Owner=@OldOwner
begin
set @OwnerName = @OldOwner + '.' + rtrim(@Name)
exec sp_changeobjectowner @OwnerName, @NewOwner
end
-- select @name,@NewOwner,@OldOwner
FETCH NEXT FROM curObject INTO @Name, @Owner
END
close curObject
deallocate curObject
GO
10、SQL SERVER中直接循环写入数据
declare @i int
set @i=1
while @i<30
begin
insert into test (userid) values(@i)
set @i=@i+1
end
下列语句部分是MsSql语句，不可以在access中使用。
SQL分类：
DDL—数据定义语言(CREATE，ALTER，DROP，DECLARE)
DML—数据操纵语言(SELECT，DELETE，UPDATE，INSERT)
DCL—数据控制语言(GRANT，REVOKE，COMMIT，ROLLBACK)
首先,简要介绍基础语句：
1、说明：创建数据库
CREATE DATABASE database-name
2、说明：删除数据库
drop database dbname
3、说明：备份sql server
--- 创建 备份数据的 device
USE master
EXEC sp_addumpdevice 'disk', 'testBack', 'c:/mssql7backup/MyNwind_1.dat'
--- 开始 备份
BACKUP DATABASE pubs TO testBack
4、说明：创建新表
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)
根据已有的表创建新表：
A：create table tab_new like tab_old (使用旧表创建新表)
B：create table tab_new as select col1,col2… from tab_old definition only
5、说明：删除新表
drop table tabname
6、说明：增加一个列
Alter table tabname add column col type
注：列增加后将不能删除。DB2中列加上后数据类型也不能改变，唯一能改变的是增加varchar类型的长度。
7、说明：添加主键： Alter table tabname add primary key(col)
说明：删除主键： Alter table tabname drop primary key(col)
8、说明：创建索引：create [unique] index idxname on tabname(col….)
删除索引：drop index idxname
注：索引是不可更改的，想更改必须删除重新建。
9、说明：创建视图：create view viewname as select statement
删除视图：drop view viewname
10、说明：几个简单的基本的sql语句
选择：select * from table1 where 范围
插入：insert into table1(field1,field2) values(value1,value2)
删除：delete from table1 where 范围
更新：update table1 set field1=value1 where 范围
查找：select * from table1 where field1 like ’%value1%’ ---like的语法很精妙，查资料!
排序：select * from table1 order by field1,field2 [desc]
总数：select count * as totalcount from table1
求和：select sum(field1) as sumvalue from table1
平均：select avg(field1) as avgvalue from table1
最大：select max(field1) as maxvalue from table1
最小：select min(field1) as minvalue from table1
11、说明：几个高级查询运算词
A： UNION 运算符
UNION 运算符通过组合其他两个结果表（例如 TABLE1 和 TABLE2）并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时（即 UNION ALL），不消除重复行。两种情况下，派生表的每一行不是来自 TABLE1 就是来自 TABLE2。
B： EXCEPT 运算符
EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL)，不消除重复行。
C： INTERSECT 运算符
INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL)，不消除重复行。
注：使用运算词的几个查询结果行必须是一致的。
12、说明：使用外连接
A、left outer join：
左外连接（左连接）：结果集几包括连接表的匹配行，也包括左连接表的所有行。
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
B：right outer join:
右外连接(右连接)：结果集既包括连接表的匹配连接行，也包括右连接表的所有行。
C：full outer join：
全外连接：不仅包括符号连接表的匹配行，还包括两个连接表中的所有记录。
其次，大家来看一些不错的sql语句
1、说明：复制表(只复制结构,源表名：a 新表名：b) (Access可用)
法一：select * into b from a where 1<>1
法二：select top 0 * into b from a
2、说明：拷贝表(拷贝数据,源表名：a 目标表名：b) (Access可用)
insert into b(a, b, c) select d,e,f from b;
3、说明：跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用)
insert into b(a, b, c) select d,e,f from b in ‘具体数据库’ where 条件
例子：..from b in '"&Server.MapPath(".")&"/data.mdb" &"' where..
4、说明：子查询(表名1：a 表名2：b)
select a,b,c from a where a IN (select d from b ) 或者: select a,b,c from a where a IN (1,2,3)
5、说明：显示文章、提交人和最后回复时间
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b
6、说明：外连接查询(表名1：a 表名2：b)
select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c
7、说明：在线视图查询(表名1：a )
select * from (SELECT a,b,c FROM a) T where t.a > 1;
8、说明：between的用法,between限制查询数据范围时包括了边界值,not between不包括
select * from table1 where time between time1 and time2
select a,b,c, from table1 where a not between 数值1 and 数值2
9、说明：in 的使用方法
select * from table1 where a [not] in (‘值1’,’值2’,’值4’,’值6’)
10、说明：两张关联表，删除主表中已经在副表中没有的信息
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 )
11、说明：四表联查问题：
select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where .....
12、说明：日程安排提前五分钟提醒
SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5
13、说明：一条sql 语句搞定数据库分页
select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段
14、说明：前10条记录
select top 10 * form table1 where 范围
15、说明：选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.)
select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b)
16、说明：包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表
(select a from tableA ) except (select a from tableB) except (select a from tableC)
17、说明：随机取出10条数据
select top 10 * from tablename order by newid()
18、说明：随机选择记录
select newid()
19、说明：删除重复记录
Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...)
20、说明：列出数据库里所有的表名
select name from sysobjects where type='U'
21、说明：列出表里的所有的
select name from syscolumns where id=object_id('TableName')
22、说明：列示type、vender、pcs字段，以type字段排列，case可以方便地实现多重选择，类似select 中的case。
select type,sum(case vender when 'A' then pcs else 0 end),sum(case vender when 'C' then pcs else 0 end),sum(case vender when 'B' then pcs else 0 end) FROM tablename group by type
显示结果：
type vender pcs
电脑 A 1
电脑 A 1
光盘 B 2
光盘 A 2
手机 B 3
手机 C 3
23、说明：初始化表table1
TRUNCATE TABLE table1
24、说明：选择从10到15的记录
select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc
随机选择数据库记录的方法（使用Randomize函数，通过SQL语句实现）
对存储在数据库中的数据来说，随机数特性能给出上面的效果，但它们可能太慢了些。你不能要求ASP“找个随机数”然后打印出来。实际上常见的解决方案是建立如下所示的循环：
Randomize
RNumber = Int(Rnd*499) +1
While Not objRec.EOF
If objRec("ID") = RNumber THEN
... 这里是执行脚本 ...
end if
objRec.MoveNext
Wend
这很容易理解。首先，你取出1到500范围之内的一个随机数（假设500就是数据库内记录的总数）。然后，你遍历每一记录来测试ID 的值、检查其是否匹配RNumber。满足条件的话就执行由THEN 关键字开始的那一块代码。假如你的RNumber 等于495，那么要循环一遍数据库花的时间可就长了。虽然500这个数字看起来大了些，但相比更为稳固的企业解决方案这还是个小型数据库了，后者通常在一 个数据库内就包含了成千上万条记录。这时候不就死定了？
采用SQL，你就可以很快地找出准确的记录并且打开一个只包含该记录的recordset，如下所示：
Randomize
RNumber = Int(Rnd*499) + 1
SQL = "SELECT * FROM Customers WHERE ID = " & RNumber
set objRec = ObjConn.Execute(SQL)
Response.WriteRNumber & " = " & objRec("ID") & " " & objRec("c_email")
不必写出RNumber 和ID，你只需要检查匹配情况即可。只要你对以上代码的工作满意，你自可按需操作“随机”记录。Recordset没有包含其他内容，因此你很快就能找到你需要的记录这样就大大降低了处理时间。
再谈随机数
现在你下定决心要榨干Random 函数的最后一滴油，那么你可能会一次取出多条随机记录或者想采用一定随机范围内的记录。把上面的标准Random 示例扩展一下就可以用SQL应对上面两种情况了。
为了取出几条随机选择的记录并存放在同一recordset内，你可以存储三个随机数，然后查询数据库获得匹配这些数字的记录：
SQL = "SELECT * FROM Customers WHERE ID = " & RNumber & " OR ID = " & RNumber2 & " OR ID = " & RNumber3
假如你想选出10条记录（也许是每次页面装载时的10条链接的列表），你可以用BETWEEN 或者数学等式选出第一条记录和适当数量的递增记录。这一操作可以通过好几种方式来完成，但是 SELECT 语句只显示一种可能（这里的ID 是自动生成的号码）：
SQL = "SELECT * FROM Customers WHERE ID BETWEEN " & RNumber & " AND " & RNumber & "+ 9"
注意：以上代码的执行目的不是检查数据库内是否有9条并发记录。
随机读取若干条记录，测试过
Access语法：SELECT top 10 * From 表名 ORDER BY Rnd(id)
Sql server语法：select top n * from 表名 order by newid()
MySql语法：Select * From 表名 Order By rand() Limit n
Access左连接语法(最近开发要用左连接,Access帮助什么都没有,网上没有Access的SQL说明,只有自己测试, 现在记下以备后查)
语法：Select table1.fd1,table1,fd2,table2.fd2 From table1 left join table2 on table1.fd1,table2.fd1 where ...
使用SQL语句 用...代替过长的字符串显示
语法：
SQL数据库：select case when len(field)>10 then left(field,10)+'...' else field end as news_name,news_id from tablename
Access数据库：SELECT iif(len(field)>2,left(field,2)+'...',field) FROM tablename;
Conn.Execute说明
Execute方法
该方法用于执行SQL语句。根据SQL语句执行后是否返回记录集，该方法的使用格式分为以下两种：
1．执行SQL查询语句时，将返回查询得到的记录集。用法为：
Set 对象变量名=连接对象.Execute("SQL 查询语言")
Execute方法调用后，会自动创建记录集对象，并将查询结果存储在该记录对象中，通过Set方法，将记录集赋给指定的对象保存，以后对象变量就代表了该记录集对象。
2．执行SQL的操作性语言时，没有记录集的返回。此时用法为：
连接对象.Execute "SQL 操作性语句" [, RecordAffected][, Option]
·RecordAffected 为可选项，此出可放置一个变量，SQL语句执行后，所生效的记录数会自动保存到该变量中。通过访问该变量，就可知道SQL语句队多少条记录进行了操作。
·Option 可选项，该参数的取值通常为adCMDText，它用于告诉ADO，应该将Execute方法之后的第一个字符解释为命令文本。通过指定该参数，可使执行更高效。
·BeginTrans、RollbackTrans、CommitTrans方法
这三个方法是连接对象提供的用于事务处理的方法。BeginTrans用于开始一个事物；RollbackTrans用于回滚事务；CommitTrans用于提交所有的事务处理结果，即确认事务的处理。
事务处理可以将一组操作视为一个整体，只有全部语句都成功执行后，事务处理才算成功；若其中有一个语句执行失败，则整个处理就算失败，并恢复到处里前的状态。
BeginTrans和CommitTrans用于标记事务的开始和结束，在这两个之间的语句，就是作为事务处理的语句。判断事务处理是否成功，可通过 连接对象的Error集合来实现，若Error集合的成员个数不为0，则说明有错误发生，事务处理失败。Error集合中的每一个Error对象，代表一 个错误信息。
1、说明：复制表(只复制结构,源表名：a 新表名：b) (Access可用)
法一：select * into b from a where 1<>1
法二：select top 0 * into b from a
2、说明：拷贝表(拷贝数据,源表名：a 目标表名：b) (Access可用)
insert into b(a, b, c) select d,e,f from b;
3、说明：跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用)
insert into b(a, b, c) select d,e,f from b in ‘具体数据库’ where 条件
例子：..from b in '"&Server.MapPath(".")&"/data.mdb" &"' where..
4、说明：子查询(表名1：a 表名2：b)
select a,b,c from a where a IN (select d from b ) 或者: select a,b,c from a where a IN (1,2,3)
5、说明：显示文章、提交人和最后回复时间
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b
6、说明：外连接查询(表名1：a 表名2：b)
select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c
7、说明：在线视图查询(表名1：a )
select * from (SELECT a,b,c FROM a) T where t.a > 1;
8、说明：between的用法,between限制查询数据范围时包括了边界值,not between不包括
select * from table1 where time between time1 and time2
select a,b,c, from table1 where a not between 数值1 and 数值2
9、说明：in 的使用方法
select * from table1 where a [not] in (‘值1’,’值2’,’值4’,’值6’)
10、说明：两张关联表，删除主表中已经在副表中没有的信息
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 )
11、说明：四表联查问题：
select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where .....
12、说明：日程安排提前五分钟提醒
SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5
13、说明：一条sql 语句搞定数据库分页
select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段
14、说明：前10条记录
select top 10 * form table1 where 范围
15、说明：选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.)
select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b)
16、说明：包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表
(select a from tableA ) except (select a from tableB) except (select a from tableC)
17、说明：随机取出10条数据
select top 10 * from tablename order by newid()
18、说明：随机选择记录
select newid()
19、说明：删除重复记录
Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...)
20、说明：列出数据库里所有的表名
select name from sysobjects where type='U'
21、说明：列出表里的所有的
select name from syscolumns where id=object_id('TableName')
22、说明：列示type、vender、pcs字段，以type字段排列，case可以方便地实现多重选择，类似select 中的case。
select type,sum(case vender when 'A' then pcs else 0 end),sum(case vender when 'C' then pcs else 0 end),sum(case vender when 'B' then pcs else 0 end) FROM tablename group by type
显示结果：
type vender pcs
电脑 A 1
电脑 A 1
光盘 B 2
光盘 A 2
手机 B 3
手机 C 3
23、说明：初始化表table1
TRUNCATE TABLE table1
24、说明：选择从10到15的记录
select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc
1、说明：复制表(只复制结构,源表名：a 新表名：b) (Access可用)
法一：select * into b from a where 1<>1
法二：select top 0 * into b from a
2、说明：拷贝表(拷贝数据,源表名：a 目标表名：b) (Access可用)
insert into b(a, b, c) select d,e,f from b;
3、说明：跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用)
insert into b(a, b, c) select d,e,f from b in ‘具体数据库’ where 条件
例子：..from b in '"&Server.MapPath(".")&"/data.mdb" &"' where..
4、说明：子查询(表名1：a 表名2：b)
select a,b,c from a where a IN (select d from b ) 或者: select a,b,c from a where a IN (1,2,3)
5、说明：显示文章、提交人和最后回复时间
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b
6、说明：外连接查询(表名1：a 表名2：b)
select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c
7、说明：在线视图查询(表名1：a )
select * from (SELECT a,b,c FROM a) T where t.a > 1;
8、说明：between的用法,between限制查询数据范围时包括了边界值,not between不包括
select * from table1 where time between time1 and time2
select a,b,c, from table1 where a not between 数值1 and 数值2
9、说明：in 的使用方法
select * from table1 where a [not] in (‘值1’,’值2’,’值4’,’值6’)
10、说明：两张关联表，删除主表中已经在副表中没有的信息
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 )
11、说明：四表联查问题：
select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where .....
12、说明：日程安排提前五分钟提醒
SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5
13、说明：一条sql 语句搞定数据库分页
select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段
14、说明：前10条记录
select top 10 * form table1 where 范围
15、说明：选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.)
select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b)
16、说明：包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表
(select a from tableA ) except (select a from tableB) except (select a from tableC)
17、说明：随机取出10条数据
select top 10 * from tablename order by newid()
18、说明：随机选择记录
select newid()
19、说明：删除重复记录
Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...)
20、说明：列出数据库里所有的表名
select name from sysobjects where type='U'
--修改用户数据库的排序规则
ater database dbname collate SQL_Latin1_General_CP1_CI_AS
--修改字段的排序规则
alter table a alter column c2 varchar(50) collate SQL_Latin1_General_CP1_CI_AS
--按姓氏笔画排序
select * from 表名 order by 列名 Collate Chinese_PRC_Stroke_ci_as
--按拼音首字母排序
select * from 表名 order by 列名 Collate Chinese_PRC_CS_AS_KS_WS
--查找一个Server下含有某个表名的数据库名字
EXEC sp_msforeachdb '
USE [?]
IF OBJECT_ID(N''表名'') IS NOT NULL
PRINT N''?'''
--查询所有表名、字段名及字段描述
select
表名=object_name(id),
列名=col_name(object_id(object_name(id)),smallid),
描述=value
from sysproperties
--查询所有表的记录数明细
--方法(1)
select
a.name,b.rows
from
sysobjects a, sysindexes b
where
a.name=b.name and a.type='u'
--方法(2)
sp_msforeachtable 'select ''?'' as ''表名'',(select sum(1) from ?) as ''记录数'''
--复制表(表结构)
--方法(1)
select * into Employee_bak from Employee where 1<>1
--方法(2)
select top 0 * into Employee_bak from Employee
--方法(3)
--企业管理器中的右键表-->复制,然后在查询分析器中粘贴查看SQL语句
--判断两字符是否完全(区分大小写)相等(如何判断字符的大小写)
declare @v nvarchar(10)
declare @p nvarchar(10)
set @v='NIPSAN'
if cast(@v as varbinary)= cast(@p as varbinary)
print N'相等'
else
print N'不等'
--得到数据库中所有表的空间/记录情况
exec sp_MSForEachTable
@precommand=N'
create table ##(
id int identity,
表名 sysname,
字段数 int,
记录数 int,
保留空间 Nvarchar(10),
使用空间 varchar(10),
索引使用空间 varchar(10),
未用空间 varchar(10))',
@command1=N'insert ##(表名,记录数,保留空间,使用空间,索引使用空间,未用空间) exec sp_spaceused ''?''
update ## set 字段数=(select count(*) from syscolumns where id=object_id(''?''))
where id=scope_identity()', @postcommand=N'select * from ## order by id drop table ##'
--查看硬盘分区:
EXEC master..xp_fixeddrives
--Order By的一个小技巧
/*
Order By可以指定列序而不用指定列名,在下面的例子里说明它的用处(注意,第三列未指定别名)
Select a.ID,a.Name,(Select Count(*) From TableB b Where a.ID=b.PID) From TableA a Order By 3
*/
SELECT
表名=case when a.colorder=1 then d.name else '' end,
表说明=case when a.colorder=1 then isnull(f.value,'') else '' end,
字段序号=a.colorder,
字段名=a.name,
标识=case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end,
主键=case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in (
SELECT name FROM sysindexes WHERE indid in(
SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid
))) then '√' else '' end,
类型=b.name,
占用字节数=a.length,
长度=COLUMNPROPERTY(a.id,a.name,'PRECISION'),
小数位数=isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),
允许空=case when a.isnullable=1 then '√'else '' end,
默认值=isnull(e.text,''),
字段说明=isnull(g.[value],''),
索引名称=isnull(h.索引名称,''),
索引顺序=isnull(h.排序,'')
FROM syscolumns a
left join systypes b on a.xtype=b.xusertype
inner join sysobjects d on a.id=d.id and d.xtype='U' and d.status>=0
left join syscomments e on a.cdefault=e.id
left join sysproperties g on a.id=g.id and a.colid=g.smallid
left join sysproperties f on d.id=f.id and f.smallid=0
left join(--这部分是索引信息,如果要显示索引与表及字段的对应关系,可以只要此部分
select 索引名称=a.name,c.id,d.colid
,排序=case indexkey_property(c.id,b.indid,b.keyno,'isdescending')
when 1 then '降序' when 0 then '升序' end
from sysindexes a
join sysindexkeys b on a.id=b.id and a.indid=b.indid
join (--这里的作用是有多个索引时,取索引号最小的那个
select id,colid,indid=min(indid) from sysindexkeys
group by id,colid) b1 on b.id=b1.id and b.colid=b1.colid and b.indid=b1.indid
join sysobjects c on b.id=c.id and c.xtype='U' and c.status>=0
join syscolumns d on b.id=d.id and b.colid=d.colid
where a.indid not in(0,255)
) h on a.id=h.id and a.colid=h.colid
--where d.name='要查询的表' --如果只查询指定表,加上此条件
order by a.id,a.colorder
--索引信息
--索引信息
select 索引名称=case when b.keyno=1 then a.name else '' end
,表名=case when b.keyno=1 then c.name else '' end
,列名=d.name
,排序=case indexkey_property(c.id,b.indid,b.keyno,'isdescending')
when 1 then '降序' when 0 then '升序' end
,聚集=INDEXPROPERTY(c.id,a.name,'IsClustered')
,唯一=case INDEXPROPERTY(c.id,a.name,'IsUnique')
when 0 then '非唯一'
when 1 then case when e.id is null then '唯一索引' else '唯一约束' end
end
,e.name
,填充因子=a.OrigFillFactor
from sysindexes a
join sysindexkeys b on a.id=b.id and a.indid=b.indid
--join (--这里的作用是有多个索引时,取索引号最小的那个
--select id,colid,indid=min(indid) from sysindexkeys
--group by id,colid) b1 on b.id=b1.id and b.colid=b1.colid and b.indid=b1.indid
join sysobjects c on b.id=c.id and c.xtype='U' and c.status>=0
join syscolumns d on b.id=d.id and b.colid=d.colid
left join sysobjects e on b.indid=e.id and e.xtype='UQ'
where a.indid not in(0,255)
order by c.name,a.name
n久前整理过的一些最基本的,随意看看.
1.Insert (添加数据)
.Inert Into tb1(field1,field2) Values(11,'aa')
.Inert tb1 Values(11,"aa")
.Inert tb1(field1,field2) Select field3,'s'+field4 From tb2 Where field3<22
.Inert tb1 Select * From tb2 Where field3<22
2.Update (修改数据)
.Update tb1 Set field1=field1+1,field2='zz' Where field1<1
.Update tb1 Set field1=field1+1,field2='zz' From (Select * From tb2 Where field3>1) As a Where a.field3=table1.field2
.Update tb1 Set field1=field1+1,field2='zz' From table2 As a,table1 as b Where a.field4=table1.field2
3.Delete (删除数据)
.Delete table1 Where field2 Like 'a%'
.Delete table1 From (Select * From table2 Where field3<1) As a Where a.field4=table1.field2
.Delete table1 As a,table2 As b Where a.field2=b.field4 And b.field3<1
4.Creat (在本机上新建msde数据库.如需安装在始动路径,改绝对路径为Application.StartupPath)
.if object_id('msde1') is null CREATE DATABASE msde1 ON (Name=msde1_dat,filename='C://msde1.mdf',size=10,maxsize=50,filegrowth=10%) log on (name=msde1_log,filename='c://msde1.ldf',size=3,maxsize=20,filegrowth=1)
5.Drop (删除表)
.Drop table1
.if object_id('table1') is not null DROP TABLE table1 (删除之前可先判断一下.)
.Drop table1 Truncate Table table2 (如table1 参照 table2,需要先删除 table1)
6.If,Else,Begin
If Exists(Select field2 From table1 Where field2='aa')
Begin
Delect table1 Where field2='aa'
Print 'aa is deleted.'
End
Else
Print 'aa is not found.'
7.Declare,GOTO
Declare @s int,@time int
Select @s=1,@time=1
label1:
Select @s=@s*@time
Select @time=@time+1
If @time<=10
GOTO label1
Print '10 n='+str(@s)
8.While,Break,Continue
While Boolean //Boolean: 1=1
{SQL}
Break
{SQL}
Continue
{SQL}
9.WaitFor
Begin
WaitFor Time '10:00:00' //(系统时间)
Select * From Table1
End
Begin
WaitFor Delay '1:00:00' //(1小时后)
Select * From Table1
End
10.Return
Create Procedure check_contact @para varchare(40)
AS
If (Select contract From authors Where au_lname=@para)=1
Return 1
Else
Return 2
11.Case
Select Name=Convert(varchar(15),au_lname),
Contract=Case contract
When 0 Then '0'
When 1 Then '1'
Else '2'
End
From authors
Select Name=Convert(varchar(15),au_lname),
Contract=Case
When contract=0 Then '0'
When contract<>0 Then '1'
End
From authors
12.DataLength //返回表达式中数据的实际长度(如为字段名,则返回字段中数据的长度,非字段定义长度.)
Select Name=field1,DataLength=DataLength(field1) From table1
13.Current_TimeStamp //返回系统的当前日期和时间
Use pubs
GO
Creat Table books
{
book_id char(3) Not NULL,
pub_date datetime Not NULL Default Current_TimeStamp,
price money
}
GO
Insert books(book_id,price)
Values('A01',$55.0)
14.FormatMessage//格式化指定错误号的错误信息
Declare @Err123 nvarchar(64)
Set @Err123=FormatMessage(123,50)
Print @Err123
15.GetAnsiNULL//返回数据库的默认空值设置
Select GetAnsiNULL('pubs')//return 1:notNull; return 0: NULL
16.IsNULL //替换空值项(注意替换项和替换内容的类型需一致)
Select title=field2,num=IsNULL(field1,0) From table1
Select title=field2,num=IsNULL(str(field1),'Nothing') From table1
17.Coalescs//返回参数中的第一个非空(NULL)表达式
Select title=field2,numMark=Coalescs(field1,field2,'Nothing') From table1
////The Result: If field1 is null And field2 is not null Then return field2's value.
If field1 is null And field2 is null Then return 'Nothing'.
18.Permissions//返回用户的语句和对象权限.
Declare @state_p int,@object_p int
Set @state_p=Permissions()
If (@sate_p & 2)=2
Print '你可以在当前数据库中创建表'
If (@sate_p & 4)=4
Print '你可以在当前数据库中创建存储过程'
If (@sate_p & 8)=8
Print '你可以在当前数据库中创建视图'
Set @object_p=Permissions(Object_id('pubs..jobs'))
If (@object_p & 1)=1
Print '你可以检索jobs表中的数据'
If (@object_p & 2)=2
Print '你可以修改jobs表中的数据'
If (@object_p & 8)=8
Print '你可以向jobs表中添加数据'
If (@object_p & 16)=16
Print '你可以删除jobs表中的数据'
19.Convert//转换函数(从一种系统数据类型转换为另外一种)
Select thetime1=Convert(char,Current_TimeStamp,120),thetime2=Convert(char,Current_TimeStamp,108)
//////The Result: thetime1:2006-03-13 15:50:00; thetime2:03:50:00
Select s0=Convert(char,$123456789.9876,0),s1=Convert(char,$123456789.9876,1),s2=Convert(char,$123456789.9876,2)
//////The Result: s0:123456789.99; s1:123,456,789.99; s2:123456789.9876
Select n0=Convert(char,123456789.9876,0),n1=Convert(char,$123456789.9876,1),n2=Convert(char,$123456789.9876,2)
//////The Result: n0:123457; n1:1.2345679e+005; n2:1.234567890000000e+005
20.Cast//转换函数(转换格式控制没有Convert函数灵活)
Select title_id,theDate=Cast(pubdate As Char(11)) From titles Where title_id Like 'b%'
//////The Result: title_id: BU1032; theDate: Jun 12 1991
****************************
一些日期元素的取值范围:yy(1753~9999),qq(1~4),mm(1~12),dy(1~366),dd(1~31),wk(0~51),hh(0~23),mi(1~59),ss(1~59),ms(0~999)
****************************
21.DateAdd//返回指定时间间隔后的日期.
Select NowDate=GetDate(),LateDate=DateAdd(dy,60,GetDate())
22.DateDiff //返回时间间隔.
Select TheDate=pubdate,NowDate=GetDate(),NY=DateDiff(yy,pubdate,GetDate()),NM=DateDiff(mm,pubdate,GetDate()) From titles Where type='business'
23.DateName,DatePart//返回日期中指定部分对应的字符串,整数值
Select YN=DateName(yy,GetDate()) The Result:'2006'
Select YI=DatePart(yy,GetDate()) The Result:2006
24.Char,Ascii
Print 'A==>ASCII value is:'+Char(13)+Str(Ascii('A'),2,0)//Enter key's ASCII is 13.
//////The Result:A==>ASCII value is:
65
25.Stuff //删除字符串中指定位置的字符串,然后在删除位置插入字符串.
Print Stuff('abcdefghijk',3,5,'ABCD')
//////The Result:abABCDhijk
26.Str
Print Str(10.1234,10,6)
//////The Result:10.123400
27.Replicate,Replace//按指定次数复制字符串,替换字符串中的内容.
Select Replace(Replicate('ABC',2),'BC','bc')
//////The Result:AbcAbc
28.sin,cos,tan,ctan...,Radians
Select sin30du=Sin(Radians(30.0)),ctan30du=cot(Radians(30.0))
29.Round(近似值)
Select r1=Round(918.2736,3),r2=Round(918.2736,-2),r3=Round(918.2736,3,1),r4=Round(918.2736,-2,1)
//////The Result: r1:918.2740; r2:900.0000; r3:918.2730; r4:900.0000;
经验分享交流：常用SQL语句技法
下列语句部分是Mssql语句，不可以在access中使用。
SQL分类：
DDL—数据定义语言(CREATE，ALTER，DROP，DECLARE)
DML—数据操纵语言(SELECT，DELETE，UPDATE，INSERT)
DCL—数据控制语言(GRANT，REVOKE，COMMIT，ROLLBACK)
首先,简要介绍基础语句：
1、说明：创建数据库
CREATE DATABASE database-name
2、说明：删除数据库
drop database dbname
3、说明：备份sql server
--- 创建 备份数据的 device
USE master
EXEC sp_addumpdevice 'disk', 'testBack', 'c:/mssql7backup/MyNwind_1.dat'
--- 开始 备份
BACKUP DATABASE pubs TO testBack
4、说明：创建新表
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)
根据已有的表创建新表：
A：create table tab_new like tab_old (使用旧表创建新表)
B：create table tab_new as select col1,col2… from tab_old definition only
5、说明：
删除新表：drop table tabname
6、说明：
增加一个列：Alter table tabname add column col type
注：列增加后将不能删除。DB2中列加上后数据类型也不能改变，唯一能改变的是增加varchar类型的长度。
7、说明：
添加主键：Alter table tabname add primary key(col)
说明：
删除主键：Alter table tabname drop primary key(col)
8、说明：
创建索引：create [unique] index idxname on tabname(col….)
删除索引：drop index idxname
注：索引是不可更改的，想更改必须删除重新建。
9、说明：
创建视图：create view viewname as select statement
删除视图：drop view viewname
10、说明：几个简单的基本的sql语句
选择：select * from table1 where 范围
插入：insert into table1(field1,field2) values(value1,value2)
删除：delete from table1 where 范围
更新：update table1 set field1=value1 where 范围
查找：select * from table1 where field1 like ’%value1%’ ---like的语法很精妙，查资料!
排序：select * from table1 order by field1,field2 [desc]
总数：select count * as totalcount from table1
求和：select sum(field1) as sumvalue from table1
平均：select avg(field1) as avgvalue from table1
最大：select max(field1) as maxvalue from table1
最小：select min(field1) as minvalue from table1
11、说明：几个高级查询运算词
A： UNION 运算符
UNION 运算符通过组合其他两个结果表（例如 TABLE1 和 TABLE2）并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时（即 UNION ALL），不消除重复行。两种情况下，派生表的每一行不是来自 TABLE1 就是来自 TABLE2。
B： EXCEPT 运算符
EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL)，不消除重复行。
C： INTERSECT 运算符
INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL)，不消除重复行。
注：使用运算词的几个查询结果行必须是一致的。
12、说明：使用外连接
A、left outer join：
左外连接（左连接）：结果集几包括连接表的匹配行，也包括左连接表的所有行。
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
B：right outer join:
右外连接(右连接)：结果集既包括连接表的匹配连接行，也包括右连接表的所有行。
C：full outer join：
全外连接：不仅包括符号连接表的匹配行，还包括两个连接表中的所有记录。
其次，大家来看一些不错的sql语句
1、说明：复制表(只复制结构,源表名：a 新表名：b) (Access可用)
法一：select * into b from a where 1<>1
法二：select top 0 * into b from a
2、说明：拷贝表(拷贝数据,源表名：a 目标表名：b) (Access可用)
insert into b(a, b, c) select d,e,f from b;
3、说明：跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用)
insert into b(a, b, c) select d,e,f from b in ‘具体数据库’ where 条件
例子：..from b in '"&Server.MapPath(".")&"/data.mdb" &"' where..
4、说明：子查询(表名1：a 表名2：b)
select a,b,c from a where a IN (select d from b ) 或者: select a,b,c from a where a IN (1,2,3)
5、说明：显示文章、提交人和最后回复时间
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b
6、说明：外连接查询(表名1：a 表名2：b)
select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c
7、说明：在线视图查询(表名1：a )
select * from (SELECT a,b,c FROM a) T where t.a > 1;
8、说明：between的用法,between限制查询数据范围时包括了边界值,not between不包括
select * from table1 where time between time1 and time2
select a,b,c, from table1 where a not between 数值1 and 数值2
9、说明：in 的使用方法
select * from table1 where a [not] in (‘值1’,’值2’,’值4’,’值6’)
10、说明：两张关联表，删除主表中已经在副表中没有的信息
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 )
11、说明：四表联查问题：
select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where .....
2、说明：日程安排提前五分钟提醒
SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5
13、说明：一条sql 语句搞定数据库分页
select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段
14、说明：前10条记录
select top 10 * form table1 where 范围
15、说明：选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.)
select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b)
16、说明：包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表
(select a from tableA ) except (select a from tableB) except (select a from tableC)
17、说明：随机取出10条数据
select top 10 * from tablename order by newid()
18、说明：随机选择记录
select newid()
19、说明：删除重复记录
Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...)
20、说明：列出数据库里所有的表名
select name from sysobjects where type='U'
21、说明：列出表里的所有的
select name from syscolumns where id=object_id('TableName')
22、说明：列示type、vender、pcs字段，以type字段排列，case可以方便地实现多重选择，类似select 中的case。
select type,sum(case vender when 'A' then pcs else 0 end),sum(case vender when 'C' then pcs else 0 end),sum(case vender when 'B' then pcs else 0 end) FROM tablename group by type
显示结果：
type vender pcs
电脑 A 1
电脑 A 1
光盘 B 2
光盘 A 2
手机 B 3
手机 C 3
23、说明：初始化表table1
TRUNCATE TABLE table1
24、说明：选择从10到15的记录
select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc
随机选择数据库记录的方法（使用Randomize函数，通过SQL语句实现）
对存储在数据库中的数据来说，随机数特性能给出上面的效果，但它们可能太慢了些。你不能要求ASP“找个随机数”然后打印出来。实际上常见的解决方案是建立如下所示的循环：
Randomize
RNumber = Int(Rnd*499) +1
While Not objRec.EOF
If objRec("ID") = RNumber THEN
... 这里是执行脚本 ...
end if
objRec.MoveNext
Wend
这很容易理解。首先，你取出1到500范围之内的一个随机数（假设500就是数据库内记录的总数）。然后，你遍历每一记录来测试ID 的值、检查其是否匹配RNumber。满足条件的话就执行由THEN 关键字开始的那一块代码。假如你的RNumber 等于495，那么要循环一遍数据库花的时间可就长了。虽然500这个数字看起来大了些，但相比更为稳固的企业解决方案这还是个小型数据库了，后者通常在一个数据库内就包含了成千上万条记录。这时候不就死定了？
采用SQL，你就可以很快地找出准确的记录并且打开一个只包含该记录的recordset，如下所示：
Randomize
RNumber = Int(Rnd*499) + 1
SQL = "SELECT * FROM Customers WHERE ID = " & RNumber
set objRec = ObjConn.Execute(SQL)
Response.WriteRNumber & " = " & objRec("ID") & " " & objRec("c_email")
不必写出RNumber 和ID，你只需要检查匹配情况即可。只要你对以上代码的工作满意，你自可按需操作“随机”记录。Recordset没有包含其他内容，因此你很快就能找到你需要的记录这样就大大降低了处理时间。
再谈随机数
现在你下定决心要榨干Random 函数的最后一滴油，那么你可能会一次取出多条随机记录或者想采用一定随机范围内的记录。把上面的标准Random 示例扩展一下就可以用SQL应对上面两种情况了。
为了取出几条随机选择的记录并存放在同一recordset内，你可以存储三个随机数，然后查询数据库获得匹配这些数字的记录：
SQL = "SELECT * FROM Customers WHERE ID = " & RNumber & " OR ID = " & RNumber2 & " OR ID = " & RNumber3
假如你想选出10条记录（也许是每次页面装载时的10条链接的列表），你可以用BETWEEN 或者数学等式选出第一条记录和适当数量的递增记录。这一操作可以通过好几种方式来完成，但是 SELECT 语句只显示一种可能（这里的ID 是自动生成的号码）：
SQL = "SELECT * FROM Customers WHERE ID BETWEEN " & RNumber & " AND " & RNumber & "+ 9"
注意：以上代码的执行目的不是检查数据库内是否有9条并发记录。
随机读取若干条记录，测试过
Access语法：SELECT top 10 * From 表名 ORDER BY Rnd(id)
Sql server:select top n * from 表名 order by newid()
mysql select * From 表名 Order By rand() Limit n
Access左连接语法(最近开发要用左连接,Access帮助什么都没有,网上没有Access的SQL说明,只有自己测试, 现在记下以备后查)
语法 select table1.fd1,table1,fd2,table2.fd2 From table1 left join table2 on table1.fd1,table2.fd1 where ...
使用SQL语句 用...代替过长的字符串显示
语法：
SQL数据库：select case when len(field)>10 then left(field,10)+'...' else field end as news_name,news_id from tablename
Access数据库：SELECT iif(len(field)>2,left(field,2)+'...',field) FROM tablename;
Conn.Execute说明
Execute方法
该方法用于执行SQL语句。根据SQL语句执行后是否返回记录集，该方法的使用格式分为以下两种：
1．执行SQL查询语句时，将返回查询得到的记录集。用法为：
Set 对象变量名=连接对象.Execute("SQL 查询语言")
Execute方法调用后，会自动创建记录集对象，并将查询结果存储在该记录对象中，通过Set方法，将记录集赋给指定的对象保存，以后对象变量就代表了该记录集对象。
2．执行SQL的操作性语言时，没有记录集的返回。此时用法为：
连接对象.Execute "SQL 操作性语句" [, RecordAffected][, Option]
·RecordAffected 为可选项，此出可放置一个变量，SQL语句执行后，所生效的记录数会自动保存到该变量中。通过访问该变量，就可知道SQL语句队多少条记录进行了操作。
·Option 可选项，该参数的取值通常为adCMDText，它用于告诉ADO，应该将Execute方法之后的第一个字符解释为命令文本。通过指定该参数，可使执行更高效。
·BeginTrans、RollbackTrans、CommitTrans方法
这三个方法是连接对象提供的用于事务处理的方法。BeginTrans用于开始一个事物；RollbackTrans用于回滚事务；CommitTrans用于提交所有的事务处理结果，即确认事务的处理。
事务处理可以将一组操作视为一个整体，只有全部语句都成功执行后，事务处理才算成功；若其中有一个语句执行失败，则整个处理就算失败，并恢复到处里前的状态。
BeginTrans和CommitTrans用于标记事务的开始和结束，在这两个之间的语句，就是作为事务处理的语句。判断事务处理是否成功，可通过连接对象的Error集合来实现，若Error集合的成员个数不为0，则说明有错误发生，事务处理失败。Error集合中的每一个Error对象，代表一个错误信息。
超详细的SQL语句语法 一.数据控制语句 (DML) 部分
1.INSERT (往数据表里插入记录的语句)
INSERT INTO 表名(字段名1, 字段名2, ……) VALUES ( 值1, 值2, ……);
INSERT INTO 表名(字段名1, 字段名2, ……) SELECT 字段名1, 字段名2, …… FROM 另外的表名;
字符串类型的字段值必须用单引号括起来, 例如: ’GOOD DAY’
如果字段值里包含单引号’ 需要进行字符串转换, 我们把它替换成两个单引号''.
字符串类型的字段值超过定义的长度会出错, 最好在插入前进行长度校验.
日期字段的字段值可以用当前数据库的系统时间SYSDATE, 精确到秒
或者用字符串转换成日期型函数TO_DATE(‘2001-08-01’,’YYYY-MM-DD’)
TO_DATE()还有很多种日期格式, 可以参看ORACLE DOC.
年-月-日 小时:分钟:秒 的格式YYYY-MM-DD HH24:MI:SS
INSERT时最大可操作的字符串长度小于等于4000个单字节, 如果要插入更长的字符串, 请考虑字段用CLOB类型,
方法借用ORACLE里自带的DBMS_LOB程序包.
INSERT时如果要用到从1开始自动增长的序列号, 应该先建立一个序列号
CREATE SEQUENCE 序列号的名称 (最好是表名+序列号标记) INCREMENT BY 1 START WITH 1
MAXVALUE 99999 CYCLE NOCACHE;
其中最大的值按字段的长度来定, 如果定义的自动增长的序列号 NUMBER(6) , 最大值为999999
INSERT 语句插入这个字段值为: 序列号的名称.NEXTVAL
2.DELETE (删除数据表里记录的语句)
DELETE FROM表名 WHERE 条件;
注意：删除记录并不能释放ORACLE里被占用的数据块表空间. 它只把那些被删除的数据块标成unused.
如果确实要删除一个大表里的全部记录, 可以用 TRUNCATE 命令, 它可以释放占用的数据块表空间
TRUNCATE TABLE 表名;
此操作不可回退.
3.UPDATE (修改数据表里记录的语句)
UPDATE表名 SET 字段名1=值1, 字段名2=值2, …… WHERE 条件;
如果修改的值N没有赋值或定义时, 将把原来的记录内容清为NULL, 最好在修改前进行非空校验;
值N超过定义的长度会出错, 最好在插入前进行长度校验..
注意事项:
A. 以上SQL语句对表都加上了行级锁,
确认完成后, 必须加上事物处理结束的命令 COMMIT 才能正式生效,
否则改变不一定写入数据库里.
如果想撤回这些操作, 可以用命令 ROLLBACK 复原.
B. 在运行INSERT, DELETE 和 UPDATE 语句前最好估算一下可能操作的记录范围,
应该把它限定在较小 (一万条记录) 范围内,. 否则ORACLE处理这个事物用到很大的回退段.
程序响应慢甚至失去响应. 如果记录数上十万以上这些操作, 可以把这些SQL语句分段分次完成,
其间加上COMMIT 确认事物处理.
二.数据定义 (DDL) 部分
1.CREATE (创建表, 索引, 视图, 同义词, 过程, 函数, 数据库链接等)
ORACLE常用的字段类型有
CHAR 固定长度的字符串
VARCHAR2 可变长度的字符串
NUMBER(M,N) 数字型M是位数总长度, N是小数的长度
DATE 日期类型
创建表时要把较小的不为空的字段放在前面, 可能为空的字段放在后面
创建表时可以用中文的字段名, 但最好还是用英文的字段名
创建表时可以给字段加上默认值, 例如 DEFAULT SYSDATE
这样每次插入和修改时, 不用程序操作这个字段都能得到动作的时间
创建表时可以给字段加上约束条件
例如 不允许重复 UNIQUE, 关键字 PRIMARY KEY
2.ALTER (改变表, 索引, 视图等)
改变表的名称
ALTER TABLE 表名1 TO 表名2;
在表的后面增加一个字段
ALTER TABLE表名 ADD 字段名 字段名描述;
修改表里字段的定义描述
ALTER TABLE表名 MODIFY字段名 字段名描述;
给表里的字段加上约束条件
ALTER TABLE 表名 ADD CONSTRAINT 约束名 PRIMARY KEY (字段名);
ALTER TABLE 表名 ADD CONSTRAINT 约束名 UNIQUE (字段名);
把表放在或取出数据库的内存区
ALTER TABLE 表名 CACHE;
ALTER TABLE 表名 NOCACHE;
3.DROP (删除表, 索引, 视图, 同义词, 过程, 函数, 数据库链接等)
删除表和它所有的约束条件
DROP TABLE 表名 CASCADE CONSTRAINTS;
4.TRUNCATE (清空表里的所有记录, 保留表的结构)
TRUNCATE 表名;
三.查询语句 (SELECT) 部分
SELECT字段名1, 字段名2, …… FROM 表名1, [表名2, ……] WHERE 条件;
字段名可以带入函数
例如: COUNT(*), MIN(字段名), MAX(字段名), AVG(字段名), DISTINCT(字段名),
TO_CHAR(DATE字段名,'YYYY-MM-DD HH24:MI:SS')
NVL(EXPR1, EXPR2)函数
解释:
IF EXPR1=NULL
RETURN EXPR2
ELSE
RETURN EXPR1
DECODE(AA﹐V1﹐R1﹐V2﹐R2....)函数
解释:
IF AA=V1 THEN RETURN R1
IF AA=V2 THEN RETURN R2
..…
ELSE
RETURN NULL
LPAD(char1,n,char2)函数
解释:
字符char1按制定的位数n显示，不足的位数用char2字符串替换左边的空位
字段名之间可以进行算术运算
例如: (字段名1*字段名1)/3
查询语句可以嵌套
例如: SELECT …… FROM
(SELECT …… FROM表名1, [表名2, ……] WHERE 条件) WHERE 条件2;
两个查询语句的结果可以做集合操作
例如: 并集UNION(去掉重复记录), 并集UNION ALL(不去掉重复记录), 差集MINUS, 交集INTERSECT
分组查询
SELECT字段名1, 字段名2, …… FROM 表名1, [表名2, ……] GROUP BY字段名1
[HAVING 条件] ;
两个以上表之间的连接查询
SELECT字段名1, 字段名2, …… FROM 表名1, [表名2, ……] WHERE
表名1.字段名 = 表名2. 字段名 [ AND ……] ;
SELECT字段名1, 字段名2, …… FROM 表名1, [表名2, ……] WHERE
表名1.字段名 = 表名2. 字段名(+) [ AND ……] ;
有(+)号的字段位置自动补空值
查询结果集的排序操作, 默认的排序是升序ASC, 降序是DESC
SELECT字段名1, 字段名2, …… FROM 表名1, [表名2, ……]
ORDER BY字段名1, 字段名2 DESC;
字符串模糊比较的方法
INSTR(字段名, ‘字符串’)>0
字段名 LIKE ‘字符串%’ [‘%字符串%’]
每个表都有一个隐含的字段ROWID, 它标记着记录的唯一性.
四.ORACLE里常用的数据对象 (SCHEMA)
1.索引 (INDEX)
CREATE INDEX 索引名ON 表名 ( 字段1, [字段2, ……] );
ALTER INDEX 索引名 REBUILD;
一个表的索引最好不要超过三个 (特殊的大表除外), 最好用单字段索引, 结合SQL语句的分析执行情况,
也可以建立多字段的组合索引和基于函数的索引
ORACLE8.1.7字符串可以索引的最大长度为1578 单字节
ORACLE8.0.6字符串可以索引的最大长度为758 单字节
ORACLE DOC上说字符串最大可以建索引的长度约是:数据块的大小(db_block_size)*40%
2.视图 (VIEW)
CREATE VIEW 视图名AS SELECT …. FROM …..;
ALTER VIEW视图名 COMPILE;
视图仅是一个SQL查询语句, 它可以把表之间复杂的关系简洁化.
3.同义词 (SYNONMY)
CREATE SYNONYM同义词名FOR 表名;
CREATE SYNONYM同义词名FOR 表名@数据库链接名;
4.数据库链接 (DATABASE LINK)
CREATE DATABASE LINK数据库链接名CONNECT TO 用户名 IDENTIFIED BY 密码 USING ‘数据库连接字符串’;
数据库连接字符串可以用NET8 EASY CONFIG或者直接修改TNSNAMES.ORA里定义.
数据库参数global_name=true时要求数据库链接名称跟远端数据库名称一样
数据库全局名称可以用以下命令查出
SELECT * FROM GLOBAL_NAME;
查询远端数据库里的表
SELECT …… FROM 表名@数据库链接名;
五.权限管理 (DCL) 语句
1.GRANT 赋于权限
常用的系统权限集合有以下三个:
CONNECT(基本的连接), RESOURCE(程序开发), DBA(数据库管理)
常用的数据对象权限有以下五个:
ALL ON 数据对象名, SELECT ON 数据对象名, UPDATE ON 数据对象名,
DELETE ON 数据对象名, INSERT ON 数据对象名, ALTER ON 数据对象名
GRANT CONNECT, RESOURCE TO 用户名;
GRANT SELECT ON 表名 TO 用户名;
GRANT SELECT, INSERT, DELETE ON表名 TO 用户名1, 用户名2;
2.REVOKE 回收权限
REVOKE CONNECT, RESOURCE FROM 用户名;
REVOKE SELECT ON 表名 FROM 用户名;
REVOKE SELECT, INSERT, DELETE ON表名 FROM 用户名1, 用户名2;
SQL分类：
DDL—数据定义语言(CREATE，ALTER，DROP，DECLARE)
DML—数据操纵语言(SELECT，DELETE，UPDATE，INSERT)
DCL—数据控制语言(GRANT，REVOKE，COMMIT，ROLLBACK)
首先,简要介绍基础语句：
1、说明：创建数据库
CREATE DATABASE database-name
2、说明：删除数据库
drop database dbname
3、说明：备份sql server
--- 创建 备份数据的 device
USE master
EXEC sp_addumpdevice 'disk', 'testBack',
'c:/mssql7backup/MyNwind_1.dat'
--- 开始 备份
BACKUP DATABASE pubs TO testBack
4、说明：创建新表
create table tabname(col1 type1 [not null] [primary key],col2
type2 [not null],..)
根据已有的表创建新表：
A：create table tab_new like tab_old (使用旧表创建新表)
B：create table tab_new as select col1,col2… from tab_old
definition only
5、说明：删除新表drop table tabname
6、说明：增加一个列
Alter table tabname add column col type
注：列增加后将不能删除。DB2中列加上后数据类型也不能改变，唯一能改
变的是增加varchar类型的长度。
7、说明：添加主键： Alter table tabname add primary key(col)
说明：删除主键： Alter table tabname drop primary key(col)
8、说明：创建索引：create [unique] index idxname on tabname(col
….)
删除索引：drop index idxname
注：索引是不可更改的，想更改必须删除重新建。
9、说明：创建视图：create view viewname as select statement
删除视图：drop view viewname
10、说明：几个简单的基本的sql语句
选择：select * from table1 where 范围
插入：insert into table1(field1,field2) values(value1,value2)
删除：delete from table1 where 范围
更新：update table1 set field1=value1 where 范围
查找：select * from table1 where field1 like ’%value1%’ --
-like的语法很精妙，查资料!
排序：select * from table1 order by field1,field2 [desc]
总数：select count * as totalcount from table1
求和：select sum(field1) as sumvalue from table1
平均：select avg(field1) as avgvalue from table1
最大：select max(field1) as maxvalue from table1
最小：select min(field1) as minvalue from table1
11、说明：几个高级查询运算词
A： UNION 运算符
UNION 运算符通过组合其他两个结果表（例如 TABLE1 和 TABLE2）并消去
表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时（即
UNION ALL），不消除重复行。两种情况下，派生表的每一行不是来自
TABLE1 就是来自 TABLE2。
B： EXCEPT 运算符
EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所
有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT
ALL)，不消除重复行。
C： INTERSECT 运算符
INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有
重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时
(INTERSECT ALL)，不消除重复行。
注：使用运算词的几个查询结果行必须是一致的。
12、说明：使用外连接
A、left outer join：
左外连接（左连接）：结果集几包括连接表的匹配行，也包括左连接表的所
有行。
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
B：right outer join:
右外连接(右连接)：结果集既包括连接表的匹配连接行，也包括右连接表的
所有行。
C：full outer join：
全外连接：不仅包括符号连接表的匹配行，还包括两个连接表中的所有记录
。
其次，大家来看一些不错的sql语句
1、说明：复制表(只复制结构,源表名：a 新表名：b) (Access可用)
法一：select * into b from a where 1<>1
法二：select top 0 * into b from a
2、说明：拷贝表(拷贝数据,源表名：a 目标表名：b) (Access可用)
insert into b(a, b, c) select d,e,f from b;
3、说明：跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用)
insert into b(a, b, c) select d,e,f from b in ‘具体数据库’
where 条件
例子：..from b in '"&Server.MapPath(".")&"/data.mdb" &"'
where..
4、说明：子查询(表名1：a 表名2：b)
select a,b,c from a where a IN (select d from b ) 或者:
select a,b,c from a where a IN (1,2,3)
5、说明：显示文章、提交人和最后回复时间
select a.title,a.username,b.adddate from table a,(select
max(adddate) adddate from table where table.title=a.title) b
6、说明：外连接查询(表名1：a 表名2：b)
select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON
a.a = b.c
7、说明：在线视图查询(表名1：a )
select * from (SELECT a,b,c FROM a) T where t.a > 1;
8、说明：between的用法,between限制查询数据范围时包括了边界值,not
between不包括
select * from table1 where time between time1 and time2
select a,b,c, from table1 where a not between 数值1 and 数值2
9、说明：in 的使用方法
select * from table1 where a [not] in (‘值1’,’值2’,’值4’,
’值6’)
10、说明：两张关联表，删除主表中已经在副表中没有的信息
delete from table1 where not exists ( select * from table2
where table1.field1=table2.field1 )
11、说明：四表联查问题：
select * from a left inner join b on a.a=b.b right inner join
c on a.a=c.c inner join d on a.a=d.d where .....
12、说明：日程安排提前五分钟提醒
SQL: select * from 日程安排 where datediff('minute',f开始时
间,getdate())>5
13、说明：一条sql 语句搞定数据库分页
select top 10 b.* from (select top 20 主键字段,排序字段 from 表
名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字
段 order by a.排序字段
14、说明：前10条记录
select top 10 * form table1 where 范围
15、说明：选择在每一组b值相同的数据中对应的a最大的记录的所有信息(
类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排
名,等等.)
select a,b,c from tablename ta where a=(select max(a) from
tablename tb where tb.b=ta.b)
16、说明：包括所有在 TableA 中但不在 TableB和TableC 中的行并消除
所有重复行而派生出一个结果表
(select a from tableA ) except (select a from tableB) except
(select a from tableC)
17、说明：随机取出10条数据
select top 10 * from tablename order by newid()
18、说明：随机选择记录
select newid()
19、说明：删除重复记录
Delete from tablename where id not in (select max(id) from
tablename group by col1,col2,...)
20、说明：列出数据库里所有的表名
select name from sysobjects where type='U'
21、说明：列出表里的所有的
select name from syscolumns where id=object_id('TableName')
22、说明：列示type、vender、pcs字段，以type字段排列，case可以方便
地实现多重选择，类似select 中的case。
select type,sum(case vender when 'A' then pcs else 0
end),sum(case vender when 'C' then pcs else 0 end),sum(case
vender when 'B' then pcs else 0 end) FROM tablename group by
type
显示结果：
type vender pcs
电脑 A 1
电脑 A 1
光盘 B 2
光盘 A 2
手机 B 3
手机 C 3
23、说明：初始化表table1
TRUNCATE TABLE table1
24、说明：选择从10到15的记录
select top 5 * from (select top 15 * from table order by id
asc) table_别名 order by id desc
随机选择数据库记录的方法（使用Randomize函数，通过SQL语句实现）
对存储在数据库中的数据来说，随机数特性能给出上面的效果，但它们
可能太慢了些。你不能要求ASP“找个随机数”然后打印出来。实际上常见
的解决方案是建立如下所示的循环：
Randomize
RNumber = Int(Rnd*499) +1
While Not objRec.EOF
If objRec("ID") = RNumber THEN
... 这里是执行脚本 ...
end if
objRec.MoveNext
Wend
这很容易理解。首先，你取出1到500范围之内的一个随机数（假设500
就是数据库内记录的总数）。然后，你遍历每一记录来测试ID 的值、检查
其是否匹配RNumber。满足条件的话就执行由THEN 关键字开始的那一块代码
。假如你的RNumber 等于495，那么要循环一遍数据库花的时间可就长了。
虽然500这个数字看起来大了些，但相比更为稳固的企业解决方案这还是个
小型数据库了，后者通常在一个数据库内就包含了成千上万条记录。这时候
不就死定了？
采用SQL，你就可以很快地找出准确的记录并且打开一个只包含该记录
的recordset，如下所示：
Randomize
RNumber = Int(Rnd*499) + 1
SQL = "SELECT * FROM Customers WHERE ID = " & RNumber
set objRec = ObjConn.Execute(SQL)
Response.WriteRNumber & " = " & objRec("ID") & " " &
objRec("c_email")
不必写出RNumber 和ID，你只需要检查匹配情况即可。只要你对以上代
码的工作满意，你自可按需操作“随机”记录。Recordset没有包含其他内
容，因此你很快就能找到你需要的记录这样就大大降低了处理时间。
再谈随机数
现在你下定决心要榨干Random 函数的最后一滴油，那么你可能会一次
取出多条随机记录或者想采用一定随机范围内的记录。把上面的标准Random
示例扩展一下就可以用SQL应对上面两种情况了。
为了取出几条随机选择的记录并存放在同一recordset内，你可以存储
三个随机数，然后查询数据库获得匹配这些数字的记录：
SQL = "SELECT * FROM Customers WHERE ID = " & RNumber & " OR
ID = " & RNumber2 & " OR ID = " & RNumber3
假如你想选出10条记录（也许是每次页面装载时的10条链接的列表），
你可以用BETWEEN 或者数学等式选出第一条记录和适当数量的递增记录。这
一操作可以通过好几种方式来完成，但是 SELECT 语句只显示一种可能（这
里的ID 是自动生成的号码）：
SQL = "SELECT * FROM Customers WHERE ID BETWEEN " & RNumber &
" AND " & RNumber & "+ 9"
注意：以上代码的执行目的不是检查数据库内是否有9条并发记录。
随机读取若干条记录，测试过
Access语法：SELECT top 10 * From 表名 ORDER BY Rnd(id)
Sql server:select top n * from 表名 order by newid()
mysqlelect * From 表名 Order By rand() Limit n
Access左连接语法(最近开发要用左连接,Access帮助什么都没有,网上没有
Access的SQL说明,只有自己测试, 现在记下以备后查)
语法elect table1.fd1,table1,fd2,table2.fd2 From table1 left
join table2 on table1.fd1,table2.fd1 where ...
使用SQL语句 用...代替过长的字符串显示
语法：
SQL数据库：select case when len(field)>10 then left(field,10)
+'...' else field end as news_name,news_id from tablename
Access数据库：SELECT iif(len(field)>2,left(field,2)
+'...',field) FROM tablename;
Conn.Execute说明
Execute方法
该方法用于执行SQL语句。根据SQL语句执行后是否返回记录集，该方法
的使用格式分为以下两种：
1．执行SQL查询语句时，将返回查询得到的记录集。用法为：
Set 对象变量名=连接对象.Execute("SQL 查询语言")
Execute方法调用后，会自动创建记录集对象，并将查询结果存储在
该记录对象中，通过Set方法，将记录集赋给指定的对象保存，以后对象变
量就代表了该记录集对象。
2．执行SQL的操作性语言时，没有记录集的返回。此时用法为：
连接对象.Execute "SQL 操作性语句" [, RecordAffected][,
Option]
·RecordAffected 为可选项，此出可放置一个变量，SQL语
句执行后，所生效的记录数会自动保存到该变量中。通过访问该变量，就可
知道SQL语句队多少条记录进行了操作。
·Option 可选项，该参数的取值通常为adCMDText，它用于
告诉ADO，应该将Execute方法之后的第一个字符解释为命令文本。通过指定
该参数，可使执行更高效。
·BeginTrans、RollbackTrans、CommitTrans方法
这三个方法是连接对象提供的用于事务处理的方法。BeginTrans用于开
始一个事物；RollbackTrans用于回滚事务；CommitTrans用于提交所有的
事务处理结果，即确认事务的处理。
事务处理可以将一组操作视为一个整体，只有全部语句都成功执行后，
事务处理才算成功；若其中有一个语句执行失败，则整个处理就算失败，并
恢复到处里前的状态。
BeginTrans和CommitTrans用于标记事务的开始和结束，在这两个之间
的语句，就是作为事务处理的语句。判断事务处理是否成功，可通过连接对
象的Error集合来实现，若Error集合的成员个数不为0，则说明有错误发生
，事务处理失败。Error集合中的每一个Error对象，代表一个错误信息。
SQL分类：
DDL—数据定义语言(CREATE，ALTER，DROP，DECLARE)
DML—数据操纵语言(SELECT，DELETE，UPDATE，INSERT)
DCL—数据控制语言(GRANT，REVOKE，COMMIT，ROLLBACK)
首先,简要介绍基础语句：
1、说明：创建数据库
CREATE DATABASE database-name
2、说明：删除数据库
drop database dbname
3、说明：备份sql server
--- 创建 备份数据的 device
USE master
EXEC sp_addumpdevice 'disk', 'testBack',
'c:/mssql7backup/MyNwind_1.dat'
--- 开始 备份
BACKUP DATABASE pubs TO testBack
4、说明：创建新表
create table tabname(col1 type1 [not null] [primary key],col2
type2 [not null],..)
根据已有的表创建新表：
A：create table tab_new like tab_old (使用旧表创建新表)
B：create table tab_new as select col1,col2… from tab_old
definition only
5、说明：删除新表drop table tabname
6、说明：增加一个列
Alter table tabname add column col type
注：列增加后将不能删除。DB2中列加上后数据类型也不能改变，唯一能改
变的是增加varchar类型的长度。
7、说明：添加主键： Alter table tabname add primary key(col)
说明：删除主键： Alter table tabname drop primary key(col)
8、说明：创建索引：create [unique] index idxname on tabname(col
….)
删除索引：drop index idxname
注：索引是不可更改的，想更改必须删除重新建。
9、说明：创建视图：create view viewname as select statement
删除视图：drop view viewname
10、说明：几个简单的基本的sql语句
选择：select * from table1 where 范围
插入：insert into table1(field1,field2) values(value1,value2)
删除：delete from table1 where 范围
更新：update table1 set field1=value1 where 范围
查找：select * from table1 where field1 like ’%value1%’ --
-like的语法很精妙，查资料!
排序：select * from table1 order by field1,field2 [desc]
总数：select count * as totalcount from table1
求和：select sum(field1) as sumvalue from table1
平均：select avg(field1) as avgvalue from table1
最大：select max(field1) as maxvalue from table1
最小：select min(field1) as minvalue from table1
11、说明：几个高级查询运算词
A： UNION 运算符
UNION 运算符通过组合其他两个结果表（例如 TABLE1 和 TABLE2）并消去
表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时（即
UNION ALL），不消除重复行。两种情况下，派生表的每一行不是来自
TABLE1 就是来自 TABLE2。
B： EXCEPT 运算符
EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所
有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT
ALL)，不消除重复行。
C： INTERSECT 运算符
INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有
重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时
(INTERSECT ALL)，不消除重复行。
注：使用运算词的几个查询结果行必须是一致的。
12、说明：使用外连接
A、left outer join：
左外连接（左连接）：结果集几包括连接表的匹配行，也包括左连接表的所
有行。
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
B：right outer join:
右外连接(右连接)：结果集既包括连接表的匹配连接行，也包括右连接表的
所有行。
C：full outer join：
全外连接：不仅包括符号连接表的匹配行，还包括两个连接表中的所有记录
。
其次，大家来看一些不错的sql语句
1、说明：复制表(只复制结构,源表名：a 新表名：b) (Access可用)
法一：select * into b from a where 1<>1
法二：select top 0 * into b from a
2、说明：拷贝表(拷贝数据,源表名：a 目标表名：b) (Access可用)
insert into b(a, b, c) select d,e,f from b;
3、说明：跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用)
insert into b(a, b, c) select d,e,f from b in ‘具体数据库’
where 条件
例子：..from b in '"&Server.MapPath(".")&"/data.mdb" &"'
where..
4、说明：子查询(表名1：a 表名2：b)
select a,b,c from a where a IN (select d from b ) 或者:
select a,b,c from a where a IN (1,2,3)
5、说明：显示文章、提交人和最后回复时间
select a.title,a.username,b.adddate from table a,(select
max(adddate) adddate from table where table.title=a.title) b
6、说明：外连接查询(表名1：a 表名2：b)
select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON
a.a = b.c
7、说明：在线视图查询(表名1：a )
select * from (SELECT a,b,c FROM a) T where t.a > 1;
8、说明：between的用法,between限制查询数据范围时包括了边界值,not
between不包括
select * from table1 where time between time1 and time2
select a,b,c, from table1 where a not between 数值1 and 数值2
9、说明：in 的使用方法
select * from table1 where a [not] in (‘值1’,’值2’,’值4’,
’值6’)
10、说明：两张关联表，删除主表中已经在副表中没有的信息
delete from table1 where not exists ( select * from table2
where table1.field1=table2.field1 )
11、说明：四表联查问题：
select * from a left inner join b on a.a=b.b right inner join
c on a.a=c.c inner join d on a.a=d.d where .....
12、说明：日程安排提前五分钟提醒
SQL: select * from 日程安排 where datediff('minute',f开始时
间,getdate())>5
13、说明：一条sql 语句搞定数据库分页
select top 10 b.* from (select top 20 主键字段,排序字段 from 表
名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字
段 order by a.排序字段
14、说明：前10条记录
select top 10 * form table1 where 范围
15、说明：选择在每一组b值相同的数据中对应的a最大的记录的所有信息(
类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排
名,等等.)
select a,b,c from tablename ta where a=(select max(a) from
tablename tb where tb.b=ta.b)
16、说明：包括所有在 TableA 中但不在 TableB和TableC 中的行并消除
所有重复行而派生出一个结果表
(select a from tableA ) except (select a from tableB) except
(select a from tableC)
17、说明：随机取出10条数据
select top 10 * from tablename order by newid()
18、说明：随机选择记录
select newid()
19、说明：删除重复记录
Delete from tablename where id not in (select max(id) from
tablename group by col1,col2,...)
20、说明：列出数据库里所有的表名
select name from sysobjects where type='U'
21、说明：列出表里的所有的
select name from syscolumns where id=object_id('TableName')
22、说明：列示type、vender、pcs字段，以type字段排列，case可以方便
地实现多重选择，类似select 中的case。
select type,sum(case vender when 'A' then pcs else 0
end),sum(case vender when 'C' then pcs else 0 end),sum(case
vender when 'B' then pcs else 0 end) FROM tablename group by
type
显示结果：
type vender pcs
电脑 A 1
电脑 A 1
光盘 B 2
光盘 A 2
手机 B 3
手机 C 3
23、说明：初始化表table1
TRUNCATE TABLE table1
24、说明：选择从10到15的记录
select top 5 * from (select top 15 * from table order by id
asc) table_别名 order by id desc
随机选择数据库记录的方法（使用Randomize函数，通过SQL语句实现）
对存储在数据库中的数据来说，随机数特性能给出上面的效果，但它们
可能太慢了些。你不能要求ASP“找个随机数”然后打印出来。实际上常见
的解决方案是建立如下所示的循环：
Randomize
RNumber = Int(Rnd*499) +1
While Not objRec.EOF
If objRec("ID") = RNumber THEN
... 这里是执行脚本 ...
end if
objRec.MoveNext
Wend
这很容易理解。首先，你取出1到500范围之内的一个随机数（假设500
就是数据库内记录的总数）。然后，你遍历每一记录来测试ID 的值、检查
其是否匹配RNumber。满足条件的话就执行由THEN 关键字开始的那一块代码
。假如你的RNumber 等于495，那么要循环一遍数据库花的时间可就长了。
虽然500这个数字看起来大了些，但相比更为稳固的企业解决方案这还是个
小型数据库了，后者通常在一个数据库内就包含了成千上万条记录。这时候
不就死定了？
采用SQL，你就可以很快地找出准确的记录并且打开一个只包含该记录
的recordset，如下所示：
Randomize
RNumber = Int(Rnd*499) + 1
SQL = "SELECT * FROM Customers WHERE ID = " & RNumber
set objRec = ObjConn.Execute(SQL)
Response.WriteRNumber & " = " & objRec("ID") & " " &
objRec("c_email")
不必写出RNumber 和ID，你只需要检查匹配情况即可。只要你对以上代
码的工作满意，你自可按需操作“随机”记录。Recordset没有包含其他内
容，因此你很快就能找到你需要的记录这样就大大降低了处理时间。
再谈随机数
现在你下定决心要榨干Random 函数的最后一滴油，那么你可能会一次
取出多条随机记录或者想采用一定随机范围内的记录。把上面的标准Random
示例扩展一下就可以用SQL应对上面两种情况了。
为了取出几条随机选择的记录并存放在同一recordset内，你可以存储
三个随机数，然后查询数据库获得匹配这些数字的记录：
SQL = "SELECT * FROM Customers WHERE ID = " & RNumber & " OR
ID = " & RNumber2 & " OR ID = " & RNumber3
假如你想选出10条记录（也许是每次页面装载时的10条链接的列表），
你可以用BETWEEN 或者数学等式选出第一条记录和适当数量的递增记录。这
一操作可以通过好几种方式来完成，但是 SELECT 语句只显示一种可能（这
里的ID 是自动生成的号码）：
SQL = "SELECT * FROM Customers WHERE ID BETWEEN " & RNumber &
" AND " & RNumber & "+ 9"
注意：以上代码的执行目的不是检查数据库内是否有9条并发记录。
随机读取若干条记录，测试过
Access语法：SELECT top 10 * From 表名 ORDER BY Rnd(id)
Sql server:select top n * from 表名 order by newid()
mysqlelect * From 表名 Order By rand() Limit n
Access左连接语法(最近开发要用左连接,Access帮助什么都没有,网上没有
Access的SQL说明,只有自己测试, 现在记下以备后查)
语法elect table1.fd1,table1,fd2,table2.fd2 From table1 left
join table2 on table1.fd1,table2.fd1 where ...
使用SQL语句 用...代替过长的字符串显示
语法：
SQL数据库：select case when len(field)>10 then left(field,10)
+'...' else field end as news_name,news_id from tablename
Access数据库：SELECT iif(len(field)>2,left(field,2)
+'...',field) FROM tablename;
Conn.Execute说明
Execute方法
该方法用于执行SQL语句。根据SQL语句执行后是否返回记录集，该方法
的使用格式分为以下两种：
1．执行SQL查询语句时，将返回查询得到的记录集。用法为：
Set 对象变量名=连接对象.Execute("SQL 查询语言")
Execute方法调用后，会自动创建记录集对象，并将查询结果存储在
该记录对象中，通过Set方法，将记录集赋给指定的对象保存，以后对象变
量就代表了该记录集对象。
2．执行SQL的操作性语言时，没有记录集的返回。此时用法为：
连接对象.Execute "SQL 操作性语句" [, RecordAffected][,
Option]
·RecordAffected 为可选项，此出可放置一个变量，SQL语
句执行后，所生效的记录数会自动保存到该变量中。通过访问该变量，就可
知道SQL语句队多少条记录进行了操作。
·Option 可选项，该参数的取值通常为adCMDText，它用于
告诉ADO，应该将Execute方法之后的第一个字符解释为命令文本。通过指定
该参数，可使执行更高效。
·BeginTrans、RollbackTrans、CommitTrans方法
这三个方法是连接对象提供的用于事务处理的方法。BeginTrans用于开
始一个事物；RollbackTrans用于回滚事务；CommitTrans用于提交所有的
事务处理结果，即确认事务的处理。
事务处理可以将一组操作视为一个整体，只有全部语句都成功执行后，
事务处理才算成功；若其中有一个语句执行失败，则整个处理就算失败，并
恢复到处里前的状态。
BeginTrans和CommitTrans用于标记事务的开始和结束，在这两个之间
的语句，就是作为事务处理的语句。判断事务处理是否成功，可通过连接对
象的Error集合来实现，若Error集合的成员个数不为0，则说明有错误发生
，事务处理失败。Error集合中的每一个Error对象，代表一个错误信息。
一、数据库的基本概念和设计方法
所谓数据库实际上是关于某一特定主题或目标的信息集合。它把大量数据按一定的结构进行存储，集中管理和统一使用这些数据，实现数据共享。在Access中，数据库不仅包含用于存放加工过的信息的表，还包含以表中所存放的信息为操作对象的查询、窗体、报表、页等数据库对象。
一般地，数据库的设计应遵循以下几个步骤：
1、确定创建数据库所要完成的目的。
2、确定创建数据库中所需要的表。
3、确定表中所需要的字段。
4、明确有唯一值的主关键字段。
5、确定表之间的关系。
6、优化设计。
7、输入数据并创建其他数据库对象。
二、创建数据库
在Access中既可以使用人工的方法按照自己的要求来建立数据库，也可以使用软件为用户提供的各种数据库向导，前者更为自由而后者则显然要方便一些。
A、自行创建数据库
使用人工的方法按照自己的要求来建立数据库，首先应创建一个空数据库，然后再将对象加入到数据库中。创建空数据库的步骤为：
1、在启动Access后的Microsoft Access对话框中选择“空Access数据库”选项。
2、在弹出的“文件新建数据库”对话框中选择数据库存放的位置、输入数据库的名称并单击“创建”按钮，进入Access数据库窗口，即完成了空数据库的创建。
B、使用数据库向导创建数据库
Access为用户提供了多种数据库向导，使用数据库向导可以方便地完成数据库的创建工作。具体步骤如下：
1、在启动Access后的Microsoft Access对话框中选择“Access数据库向导、数据页和项目”选项。
2、在弹出“新建”对话框后单击“数据库”选项卡，选择一种数据库。
3、在弹出的“文件新建数据库”对话框中，选择数据库存放的位置，输入数据库的名称并单击“创建”按钮。
4、依次在数据库向导窗口中为数据库中的各个表选择字段、选择显示样式、确定打印报表所用的样式、输入所建数据库的标题、确定是否在所有报表上加一幅图片，最后启动该数据库，至此就完成了使用向导创建数据库的工作。
三、表的基本概念
表是数据库中存储数据的最基本的对象，常称为“基础表”，是构成数据库的一个重要组成部分。表由若干记录组成，每一行称为一个记录，对应着一个真实的对象；每一列称为一个字段，对应着对象的一个属性信息。每个表的关键字（关键字可以为一个字段或多个字段）使表中的记录唯一。在表内还可以定义索引，当表内存放大量数据时可以加速数据的查找。
Access中的所有数据都存放在数据表中。表是一个数据库系统的基础，只有建立表后，才可以建立查询、窗体和报表等其他项目，逐步完善数据库。
四、创建表
Access提供了三种创建新表的方法：
1、使用数据库向导，在一个操作中创建整个数据库所需的全部表、窗体及报表。
2、使用表向导来选择表的字段，这些字段可以从其他已定义好的表中选择。
3、使用设计视图创建表。
不管使用哪一种方法来创建表，随时都可以使用表设计试图来进一步自定义表。由于第一种方法已包含在使用数据库向导创建数据库的内容之中，这里着重说明后两种方法的实现。
A、利用表向导创建表
利用表向导创建新表，一般情况下能满足数据库开发者的要求，至于多余的字段可以用其他的方法将其删除。利用表向导创建表的步骤如下：
1、在数据库窗口中，单击“对象”列表中的“表”，双击“使用向导创建表”。
2、在弹出的“表向导”对话框中单击“表向导”并单击“确定”按钮。
3、在弹出的对话框中选择要使用的表并添加所需要的字段（可以重命名）后单击“下一步”按钮。
4、为表指定名称并确认主键，单击“下一步”按钮。
5、选择创建完表后的动作，然后单击“完成”按钮。
B、利用设计视图创建表
使用设计器建立表的方法有两种：一种方法是直接使用设计器来创建新表，另一种方法是使用表向导来构造一个结构相近的表，然后在设计器中打开修改。下面介绍使用设计器来创建新表的步骤：
1、在数据库窗口中，单击对象列表中的“表”，双击“使用设计器创建表”的按钮，进入“新建表”对话框。
2、在各栏中输入字段名称、选择数据类型，然后设置字段属性。
3、接着选中要设置为关键字的字段，单击“编辑”菜单中“主键”命令，设置“主关键字”，此时在所选字段左边行选定器上出现钥匙标记。
4、保存所设计的表。
五、关于主关键字与建立表间关系
A、设置主关键字
为了提高Access在查询、窗体和报表操作中的快速查找能力和组合保存在各个不同表中信息的性能，必须为建立的表指定一个主关键字。主关键字可以包含一个或多个字段，以保证每条记录都有唯一的值。设定主关键字的目的就在于保证表中的所有记录都能够被唯一识别。如果表中没有可以用作唯一识别表中记录的字段，则可以使用多个字段来组合成主关键字。其设置步骤如下：
1、在表设计器中，单击字段名称左边的字段选择按钮，选择要作为主关键字的字段。单击字段选择按钮的同时按住Ctrl键可以同时选择多个字段。
2、单击“编辑”菜单中的“主键”命令，则在该字段的左边显示钥匙标记。
B、建立表间关系
在表中定义主关键字除了可以保证每条记录可以被唯一识别外，更重要的作用在于多个表间的连接。当数据库中包含多个表时，需要通过主关键字的连接来建立表间的关系，使各表协同工作。
要在两个表间建立关系，必须在这两个表中拥有相同数据类型的字段。其设置步骤如下：
1、打开表所在的数据库窗口。
2、单击“工具”菜单中的“关系”命令，弹出“显示表”对话框。
3、选择要建立关系的表，然后单击“添加”按钮，依次添加完所需要的表后，单击“关闭”按钮。
4、在关系对话框中选择其中一表中的主关键字，拖曳到另一表中相同的主关键字，释放鼠表键后，弹出“编辑关系”对话框。
5、若在“编辑关系”对话框中选中“实施参照完整性”和“级联更新相关字段”复选框，则使在更新主表中记录的同时更新关系表中的相关记录。
6、若在“编辑关系”对话框中选中“实施参照完整性”和“级联删除相关字段”复选框，则使在删除主表中记录的同时删除关系表中的相关记录。
7、接着单击“联接类型”按钮，弹出“联接属性”对话框，在此选择联接的方式。
8、在“编辑关系”对话框中单击“创建”按钮，即在创建关系的表之间有一条线将其连接起来，表示已创建好表之间的关系。
9、关闭关系对话框，按需要选择是否保存关系的设定。
编辑或修改关联性的操作是直接用鼠标在这一条线上双击，然后在弹出的“编辑关系”对话框中进行修改。删除关联性的操作是先用鼠标在这一条线上单击，然后再按Delete键删除。
六、记录的基本操作
Access只允许每次操作一个记录，正在操作的记录在行选定器上显示一个“三角图标”用于标记当前记录。当改变当前记录的数据但又没有保存时，行选定器上显示一个“笔型图标”。
记录的基本操作包括添加记录、修改记录和删除记录。
A、添加记录
打开表的数据视图画面时，表的最末端有一条空白的记录，在记录的行选定器上显示一个星花图标，标示可以从这里开始增加新的记录。单击“插入”菜单中的“新记录”或直接选定该行即可添加记录。输入完数据后，移到另一个记录时会自动保存该记录。
B、修改记录
可用Tab键或直接用鼠标移到要修改的字段进行修改。
C、删除记录
选择一条或多条记录后按Delete键删除所有选中的记录。
七、总结
读过本文之后，相信您应该可以轻松地创建一个小型的数据库了，然而相比于Access强大而繁多的功能，这篇文章涉及的仅仅是最最简单和基本的一些操作，关于查询、窗体、报表、页以及宏与模块的内容将比表的基础操作更为复杂，有兴趣的读者可以查询其它的资料，以便顺利地设计和制作出高质量的应用系统。
select * from txt1
select * from txt2
select * from txt1 where name in (select name from txt2 where chk = 0)
select * from txt1 where name not in (select name from txt2 where chk = 0)
select * from txt1 inner join txt2 on txt1.name = txt2.name
select * from txt1 left join txt2 on txt1.name = txt2.name
select * from txt1 right join txt2 on txt1.name = txt2.name
select * from txt1,txt2 where txt1.name = txt2.name Order By txt2.ID Desc
select Txt1.name,Sum(Txt1.Sumint) from txt1,txt2 where txt1.name =txt2.name Group by txt1.name
select count(Txt1.name),sum(sumint) from txt1,txt2 where txt1.name =txt2.name having sum(sumint)<10
select count(Txt1.name),sum(sumint) from txt1,txt2 where txt1.name =txt2.name having Sum(sumint) >50
select * from txt1,txt2 where txt1.name <>txt2.name Order by Txt1.Name compute Sum(sumint) by txt1.name
insert into txt3 select name,Sumint from txt1
In:等值连接，用来查找多表相同字段的记录
Not In:非等值连接，用来查找不存在的记录
Inner join:内连接，主要用来查找都符合条件的记录
Left join:左连接，主要用来查找左边有，右边没有的用空值表达
Right join:右连接，主要用来查找右边有，左边没有的用空值表达
Order By:升序 Asc 降序 Desc
Group By:分组排序 按字段分组(如按班级分),使用的都要有集合函数存在
Having :对分组条件进行判断,使用都要确保每个字段都要用集合函数
COMPUTE BY：子句使您得以用同一 SELECT 语句既查看明细行，又查看汇总行。可以计算子组的汇总值，也可以计算整个结果集的汇总值。
行聚合函数名称；例如，SUM、AVG、MIN、MAX 或 COUNT
insert into:主要把查询的语句插入到新表。
txt1表的数据
ID Name SumInt
1 张三 55
2 李四 85
3 王五 85
4 赵六 855
txt2表的数据
ID Name Chk
1 张三 0
2 李四1 1
3 王五 0
4 赵六 0
在Oracle PL-SQL中，左连接和右连接以如下方式来实现
查看如下语句：
SELECT emp_name, dept_name FORM Employee, DepartmentWHERE Employee.emp_deptid(+) = Department.deptid反之：
SELECT emp_name, dept_name FORM Employee, DepartmentWHERE Employee.emp_deptid = Department.deptid(+)DAYNAME 返回一个大小写混合的字符串，对于参数的日部分，用星期表示这一天的名称（例如，Friday）。
DAYOFWEEK 返回参数中的星期几，用范围在 1-7 的整数值表示，其中 1 代表星期日。
DAYOFWEEK_ISO 返回参数中的星期几，用范围在 1-7 的整数值表示，其中 1 代表星期一。
DAYOFYEAR 返回参数中一年中的第几天，用范围在 1-366 的整数值表示。
DAYS 返回日期的整数表示。
JULIAN_DAY 返回从公元前 4712 年 1 月 1 日（儒略日历的开始日期）到参数中指定日期值之间的天数，用整数值表示。
MIDNIGHT_SECONDS 返回午夜和参数中指定的时间值之间的秒数，用范围在 0 到 86400 之间的整数值表示。
MONTHNAME 对于参数的月部分的月份，返回一个大小写混合的字符串（例如，January）。
TIMESTAMP_ISO 根据日期、时间或时间戳记参数而返回一个时间戳记值。
TIMESTAMP_FORMAT 从已使用字符模板解释的字符串返回时间戳记。
TIMESTAMPDIFF 根据两个时间戳记之间的时差，返回由第一个参数定义的类型表示的估计时差。
TO_CHAR 返回已用字符模板进行格式化的时间戳记的字符表示。TO_CHAR 是 VARCHAR_FORMAT 的同义词。
TO_DATE 从已使用字符模板解释过的字符串返回时间戳记。TO_DATE 是 TIMESTAMP_FORMAT 的同义词。
WEEK 返回参数中一年的第几周，用范围在 1-54 的整数值表示。以星期日作为一周的开始。
WEEK_ISO 返回参数中一年的第几周，用范围在 1-53 的整数值表示。
要使当前时间或当前时间戳记调整到 GMT/CUT，则把当前的时间或时间戳记减去当前时区寄存器：
current time - current timezone
current timestamp - current timezone
给定了日期、时间或时间戳记，则使用适当的函数可以单独抽取出（如果适用的话）年、月、日、时、分、秒及微秒各部分：
YEAR (current timestamp)
MONTH (current timestamp)
DAY (current timestamp)
HOUR (current timestamp)
MINUTE (current timestamp)
SECOND (current timestamp)
MICROSECOND (current timestamp)
因为没有更好的术语，所以您还可以使用英语来执行日期和时间计算：
current date + 1 YEAR
current date + 3 YEARS + 2 MONTHS + 15 DAYS
current time + 5 HOURS - 3 MINUTES + 10 SECONDS
从时间戳记单独抽取出日期和时间也非常简单：
DATE (current timestamp)
TIME (current timestamp)
而以下示例描述了如何获得微秒部分归零的当前时间戳记：
CURRENT TIMESTAMP - MICROSECOND (current timestamp) MICROSECONDS
如果想将日期或时间值与其它文本相衔接，那么需要先将该值转换成字符串。为此，只要使用 CHAR() 函数：
char(current date)
char(current time)
char(current date + 12 hours)
要将字符串转换成日期或时间值，可以使用：
TIMESTAMP ('2002-10-20-12.00.00.000000')
TIMESTAMP ('2002-10-20 12:00:00')
DATE ('2002-10-20')
DATE ('10/20/2002')
TIME ('12:00:00')
TIME ('12.00.00')
TIMESTAMP()、DATE() 和 TIME() 函数接受更多种格式。上面几种格式只是示例，我将把它作为一个练习，让读者自己去发现其它格式。
有时，您需要知道两个时间戳记之间的时差。为此，DB2 提供了一个名为 TIMESTAMPDIFF() 的内置函数。但该函数返回的是近似值，因为它不考虑闰年，而且假设每个月只有 30 天。以下示例描述了如何得到两个日期的近似时差：
timestampdiff (<n>, char(
timestamp('2002-11-30-00.00.00')-
timestamp('2002-11-08-00.00.00')))
对于 <n>，可以使用以下各值来替代，以指出结果的时间单位：
1 = 秒的小数部分
2 = 秒
4 = 分
8 = 时
16 = 天
32 = 周
64 = 月
128 = 季度
256 = 年
当日期很接近时使用 timestampdiff() 比日期相差很大时精确。如果需要进行更精确的计算，可以使用以下方法来确定时差（按秒计）：
(DAYS(t1) - DAYS(t2)) * 86400 +
(MIDNIGHT_SECONDS(t1) - MIDNIGHT_SECONDS(t2))
为方便起见，还可以对上面的方法创建 SQL 用户定义的函数：
CREATE FUNCTION secondsdiff(t1 TIMESTAMP, t2 TIMESTAMP)
RETURNS INT
RETURN (
(DAYS(t1) - DAYS(t2)) * 86400 +
(MIDNIGHT_SECONDS(t1) - MIDNIGHT_SECONDS(t2))
)
@
如果需要确定给定年份是否是闰年，以下是一个很有用的 SQL 函数，您可以创建它来确定给定年份的天数：
CREATE FUNCTION daysinyear(yr INT)
RETURNS INT
RETURN (CASE (mod(yr, 400)) WHEN 0 THEN 366 ELSE
CASE (mod(yr, 4)) WHEN 0 THEN
CASE (mod(yr, 100)) WHEN 0 THEN 365 ELSE 366 END
ELSE 365 END
END)@
最后，以下是一张用于日期操作的内置函数表。它旨在帮助您快速确定可能满足您要求的函数，但未提供完整的参考。有关这些函数的更多信息，请参考 SQL 参考大全。
SQL 日期和时间函数
DAYNAME 返回一个大小写混合的字符串，对于参数的日部分，用星期表示这一天的名称（例如，Friday）。
DAYOFWEEK 返回参数中的星期几，用范围在 1-7 的整数值表示，其中 1 代表星期日。
DAYOFWEEK_ISO 返回参数中的星期几，用范围在 1-7 的整数值表示，其中 1 代表星期一。
DAYOFYEAR 返回参数中一年中的第几天，用范围在 1-366 的整数值表示。
DAYS 返回日期的整数表示。
JULIAN_DAY 返回从公元前 4712 年 1 月 1 日（儒略日历的开始日期）到参数中指定日期值之间的天数，用整数值表示。
MIDNIGHT_SECONDS 返回午夜和参数中指定的时间值之间的秒数，用范围在 0 到 86400 之间的整数值表示。
MONTHNAME 对于参数的月部分的月份，返回一个大小写混合的字符串（例如，January）。
TIMESTAMP_ISO 根据日期、时间或时间戳记参数而返回一个时间戳记值。
TIMESTAMP_FORMAT 从已使用字符模板解释的字符串返回时间戳记。
TIMESTAMPDIFF 根据两个时间戳记之间的时差，返回由第一个参数定义的类型表示的估计时差。
TO_CHAR 返回已用字符模板进行格式化的时间戳记的字符表示。TO_CHAR 是 VARCHAR_FORMAT 的同义词。
TO_DATE 从已使用字符模板解释过的字符串返回时间戳记。TO_DATE 是 TIMESTAMP_FORMAT 的同义词。
WEEK 返回参数中一年的第几周，用范围在 1-54 的整数值表示。以星期日作为一周的开始。
×××××××××××××××××××××××××××××××××××××××××××××××
DB2常用标量函数
函数名 说明
AVG() 返回一组数值的平均值
VAR(), 返回一组数值的方差
ASCII() 返回整数参数最左边的字符的ASCII码
BIGINT() 返回整型常量中的数字或字符串的64位整数表示
CHAR() 返回日期时间型,字符串,整数,十进制或双精度浮点数的字符串表示
CHR() 返回具有由参数指定的ASCII码的字符
CONCAT(str1,str2) 返回两个字符串的连接
YEAR() 返回数值的年部分
UCASE() 返回字符串的大写
TIME() 返回一个数值中的时间
SUBSTR(EXP1,EXP2) 返回EXP1串自EXP2处开始的子串
SECOND() 返回一个数值的秒部分
RTRIM() 删除字符串尾部的空格
ROUND(EXP1,EXP2) 返回EXP1小数点右边的第EXP2位置处开始的四舍五入值
REPLACE(EXP1,EXP2,EXP3) 用EXP3替代EXP1中所有的EXP2
REAL() 返回一个数值的单精度浮点数表示
RAND() 返回0和1之间的随机浮点数
POSSTR(EXP1,EXP2) 返回EXP2在EXP1中的位置
MONTH() 返回一个数值的月部分
MOD(EXP1,EXP2) 返回EXP1除以EXP2的余数
MINUTE() 返回一个数值的分钟部分
LTRIM() 删除字符串前面的空格
HOUR() 返回一个数值的小时部分
××××××××××××××××××××××××××××××××××××××××××××××××××××××
DB2中的限制之三字符串的限制
在現實的DB2應用中大家可能經常會遇到一些錯誤,錯誤的原因是對DB2的
限制不太了解而引起的,在此我簡單地總結一下,歡迎大家繼續﹐以便和大家共享﹐
共同探討﹐共同近步﹗(以下主要以DB2 7.X為例)。
1. CHAR 的最大長度(字節數)<=254
2. VARCHAR 的最大長度(字節數)<=32672
3. LONG VARCHAR 的最大長度(字節數)<=32700
4. CLOB 的最大長度(字節數)<=2147483647
5. GRAPHICCHAR 的最大長度(字節數)<=127
6. VARGRAPHIC 的最大長度(字節數)<=16336
7. LONG VARGRAPHIC 的最大長度(字節數)<=16350
8. DBCLOB 的最大長度(字節數)<=1073741823
9. BLOB 的最大長度(字節數)<=2147483647
10. 字符常量的最大長度(字節數)<=32672
11. 圖形常量的最大長度(字節數)<=16336
12. 串聯字符串的最大長度(字節數)<=2147483647
13. 串聯圖形串的最大長度(字節數)<=1073741823
14. 串聯二進制串的最大長度(字節數)<=2147483647
15. 十六進制常量數字的最大長度(字節數)<=16336
16. 分類注釋的最大長度(字節數)<=254
17. 咝袝r結构類型列對象的最大長度(字節數)<=1
××××××××××××××××××××××××××××××××××××××××××××
IBM DB2 日常维护汇总
1.DB2 产品的级别有那些 ?
企业版的 NTERPRISEEDITION
工作组版 WORKGROUPEDITION
企业扩展版 ENTERPRISEEXTENDEDEDITION
个人版的 PERSONALEDITION
卫星版的 SATELLITEEDITION
微型版的 EVERYPLACE
2. 可以连接到 DB2 数据库的产品有哪些 ?
DB2 客户端
DB2CONNECT
DB2DATAPROPAGATOR
DB2NET.DATA
DB2DATAJOINER
DB2RELATIONALCONNECT
WEBSPHERE 应用服务器
等
3.DB2 支持的通讯协议有哪些 ?
TCP/IP
NETBIOS
APPG
IPX/SPX
NAMEPIPE
等
4.DB2 客户端产品有哪些 ?
DB2 运行时间客户端 DB2RUNTIMECLIENT
DB2 管理客户端 DB2ADMINISTRATIONCLIENT
DB2 应用程序开发客户端 DB2APPLICATIONDEVELOPMENTCLIENT
DB2 瘦客户端 DB2THINCLIENT
5. 一个数据库是否可以安装在多个数据库服务器上 ?
可以
6. 从哪个版本后存储过程可以用 SQL 语句来创建 ?
7.1 版后
7.DB2 提供哪些关系扩展器 ?
文本扩展器 TEXTEXTENDER
图象扩展器 IMAGEEXTENDER
音频扩展器 AUDIOEXTENDER
视频扩展器 VIDEOEXTENDER
空间数据扩展器 SPATIALEXTENDER
XML 扩展器 XML EXTENDER
网络搜索扩展器 NET.SEARCHEXTENDER
8.WINDOWS 和 OS/2 环境下的 DB2 安装目录结构 ?
用 SETUP.EXE 来安装
SQLLIB 安装的根目录 , 包括 README 文件
SQLLIBADSM 包含 ADSTAR 分布式存储管理器文件
SQLLIBBIN 包含 DB2 工具的可执行文件
SQLLIBBND 包含 DB2 工具的绑定文件
SQLLIBCC 包含运行控制中心所需的文件
SQLLIBCFG 包含默认的系统配置文件
SQLLIBCONV 包含代码页转换表文件
SQLLIBDB2 默认的实例目录
SQLLIBDB2DAS00 缺省的 DB2 管理服务器目录
SQLLIBDOC 包含 DB2 联机手册
SQLLIBFUNCTION 默认的用户自定义函数目录
SQLLIBFUNCTIONUNFENCED 默认的非隔离用户自定义函授目录
SQLLIBHELP 联机帮助文件
SQLLIBJAVADB2 所需的 JAVA 类库
JAVA12 包含 JDK1.2 的支持程序
SQLLIBMISC 包含 HTML 搜索服务器文件
SQLLIBMSGPRIME 包含信息文件
SQLLIBQP 包含 QUERYPATROLLER 的客户端文件
SQLLIBSAMPLES 包含样例程序和样例脚本
SQLLIBSPMLOG 包含 DB2 同步点管理器日志文件
SQLLIBTHNSETUP 包含瘦客户端安装文件
9.UNIX 和 LINUX 环境下的 DB2 安装目录结构 ?
用 DB2SETUP.EXE 来安装
安装的根目录下还将创建以下目录 :
README 安装的根目录 , 包括 README 文件
ADM 包含系统管理工具文件
ADSM 包含 ADSTAR 分布式存储管理器文件
BIN 包含 DB2 工具的二进制可执行文件
BND 包含 DB2 工具的绑定文件
CC 包含运行控制中心所需的文件
CFG 包含默认的系统配置文件
CONV 包含代码页转换表文件
DOC 包含 DB2 联机手册
FUNCTION 默认的用户自定义函数目录
FUNCTIONUNFENCED 默认的非隔离用户自定义函授目录
INSTALL 包含安装程序
INSTANCE 包含实例脚本
JAVADB2 所需的 JAVA 类库
LIBDB2 库文件
MAP 包含 DB2CONNECT 使用的映射文件
MISC 包含 HTML 搜索服务器文件
SAMPLES 包含样例程序和样例脚本
MSG 包含 DB2 信息文件
10.AIX 下用哪个命令来安装 DB2?
INSTALLP 命令
11. 同一操作系统下可以安装多个 DB2 数据库 ?
可以的
12. 如何停止实例 ?
DB2STOP
13. 如何启动实例 ?
DB2START
14. 如何修改注册项的值 ?
DB2SET 可以修改
如 :
设定当前实例的一个参数
DB2SETPARAMETER=VALUE
设定一个全局级的参数
DB2SETPARAMETER=VALUE-G( 小写 )
查看能在配置文件注册表中设置的所有变量的列表
DB2SET-LR( 小写 )
15. 如何在 CLP 执行操作系统的命令 ?
在命令前加 "!" 作为前缀
DB2=>!DIRC:
16. 在 CLP 中命令过长怎么办 ?
用 "" 作为续行符号
17. 如何获得 DB2 的命令的语法相关信息 ?
DB2? 显示所有 DB2 命令
DB2?COMMAND 显示命令信息
DB2?SQLnnnn 显示这个 SQLCODE 的解释信息
DB2?DB2nnnn 显示这个 DB2 错误的解释信息
18. 如何查看当前 CLP 的设置 ?
DB2=>LISTCOMANDOPTIONS
19. 如何更新当前 CLP 会话的特定项设置 ?
DB2UPDATECOMMANDOPTIONSUSINGOPTIONS...
20.COMMANDWINDOWS 可以通过哪个命令调用 ?
DB2CMD 命令
21. 管理服务器的默认名为 ?
UNIX 下为 DB2AS
WINDOWS 下为 DB2DAS00
22. 常用管理 DB2 服务器实例的命令 ?
DB2ADMINSTART 启动 DB2 管理服务器实例
DB2ADMINSTOP 停止 DB2 管理服务器实例
DASICRTUNIX 下创建 DB2 管理服务器实例
DASIDROPUNIX 下删除 DB2 管理服务器实例
DB2ADMINCREATEWINDOWSOROS/2 下创建 DB2 管理服务器实例
DB2ADMINDROPWINDOWSOROS/2 下删除 DB2 管理服务器实例
DB2GETADMINCFG 显示 DB2 管理服务器的配置参数
DB2UPDATEADMINCFG 修改 DB2 管理服务器的配置参数
DB2RESETADMINCFG 将 DB2 管理服务器的配置参数设为默认值
23.DB2 目录分为哪几种 ?
系统数据库目录
本地数据库目录
节点目录
DCS 目录
管理节点目录
24. 如何查看系统数据库目录 ?
LISTDBDIRECTORY
25. 如何查看数据库服务器目录 ?
LISTNODEDIRECTORY
26.DB2 实例的服务器的默认端口是 ?
50000
服务器名称为 DB2CDB2
27.DB2UDB 服务器端的认证类型有 ?
SERVER
SERVER_ENCRYPT
CLIENT
DCE
DCE_SERVER_ENCRYPT
KERBEROS
KRB_SERVER_ENCRYPT
28.DB2 客户端的认证类型有 ?
SERVER
SERVER_ENCRYPT
DCS
DCS_ENCRYPT
CLIENT
DCE
29.DB2 中有哪几种类型的权限 ?
SYSADM 系统管理权限
SYSCTRL 系统控制权限
SYSMAINT 系统维护权限
DBADM 数据库管理权限
LOAD 对表进行 LOAD 操作的权限
30. 不能通过 GRANT 授权的权限有哪种 ?
SYSAM
SYSCTRL
SYSMAINT
要更该述权限必须修改数据库管理器配置参数
31. 表的类型有哪些 ?
永久表 ( 基表 )
临时表 ( 说明表 )
临时表 ( 派生表 )
32. 如何知道一个用户有多少表 ?
SELECT*FROMSYSIBM.SYSTABLESWHERECREATOR='USER'
33. 如何知道用户下的函数 ?
select*fromIWH.USERFUNCTION
select*fromsysibm.SYSFUNCTIONS
34. 如何知道用户下的 VIEW 数 ?
select*fromsysibm.sysviewsWHERECREATOR='USER'
35. 如何知道当前 DB2 的版本 ?
select*fromsysibm.sysvERSIONS
36. 如何知道用户下的 TRIGGER 数 ?
select*fromsysibm.SYSTRIGGERSWHERESCHEMA='USER'
37. 如何知道 TABLESPACE 的状况 ?
select*fromsysibm.SYSTABLESPACES
38. 如何知道 SEQUENCE 的状况 ?
select*fromsysibm.SYSSEQUENCES
39. 如何知道 SCHEMA 的状况 ?
select*fromsysibm.SYSSCHEMATA
40. 如何知道 INDEX 的状况 ?
select*fromsysibm.SYSINDEXES
41. 如何知道表的字段的状况 ?
select*fromsysibm.SYSCOLUMNSWHERETBNAME='AAAA'
42. 如何知道 DB2 的数据类型 ?
select*fromsysibm.SYSDATATYPES
43. 如何知道 BUFFERPOOLS 状况 ?
select*fromsysibm.SYSBUFFERPOOLS
44.DB2 表的字段的修改限制 ?
只能修改 VARCHAR2 类型的并且只能增加不能减少 .
45. 如何查看表的结构 ?
DESCRIBLETABLETABLE_NAME
OR
DESCRIBLESELECT*FROMSCHEMA.TABLE_NAME
46. 如何快速清除一个大表 ?
ALTERTABLETABLE_NAMEACTIVENOTLOGGEDINITALLYWITHEMPTYTABLE
ALTER TABLETABLE_NAME ACTIVE NOT LOGGED INITALLY WITH EMPTYTABLE
两个 commit 之间的 delete 动作是不记日志的
commit
alter table ab activate not logged intially
delete ab where id >1000
commit
（创建的表 ab 不记录日志： create table ab (id int) not logged initially ）
47. 如何查看数据库的包 ?
select*fromsysCAT.PACKAGES
48. 如何查看数据库的存储过程 ?
SELECT*FROMSYSCAT.PROCEDURES
49. 如何查看表的约束 ?
SELECT*FROMSYSCAT.CHECKSWHERETABNAME='AAAA'
50. 如何查看表的引用完整约束 ?
SELECT*FROMSYSCAT.REFERENCESWHERETABNAME='AAAA'
51. 安装 DB2 默认的事例名称是 ?
在 WINDOWS 或 OS/2 中默认的是 DB2
在 LINUX 或 UNIX 环境下默认的是 DB2INST1
52. 安装后的默认帐户是 ?
在 WINDOWS 或 OS/2 中默认的是 DB2ADMIN
在 LINUX 或 UNIX 环境下默认的是 DB2AS
53. 事例的类型有哪些 ?
CLIENT( 客户 )
STANDALONE( 独立的 )
SATELLITE( 卫星 )
EEDB2
EEE
54. 如何创建事例 ?
DB2ICRTINSTNAME<...PARAMETERS>
55. 如何列出系统上的所有可用事例 ?
DB2ILIST
56. 如何知道当前对话用的是哪个事例 ?
GETINSTANCE
57. 如何更新事例的配置 ?
DB2IUPDT
58. 如何删除事例 ?
DB2IDROPINSTANCE_NAME
具体步骤如下 :
停止事例上所有应用程序
在所有打开的命令行上执行 DB2TERMINATE
运行 DB2STOP
备份 DB2INSTPROF 注册变量指出的事例目录
退出事例所有登陆者
使用 DB2IDROP
也可以删除 ID
59. 如何列出本地系统上有许可信息的所有产品 ?
DB2LICM-L
60. 如何增加一产品许可 ?
DB2LICM-AFILENAME
61. 如何删除一个产品的许可 ?
DB2LICM-RPRODUCTPASSWORD
62. 如何更新已购买的许可数量 ?
DB2LICM-U
63. 如何强制只使用已经购买的数量 ?
DB2LICM-EHARD
64. 如何更新系统上所使用的许可政策类型 ?
DB2LICM-PREGISTEREDCONCURRENT
65. 如何更新系统上的处理器的数量 ?
DB2LICM-N
66. 如何查询许可文件记录的版本信息 ?
DB2LICM-V
67. 如何查询 DB2LICM 的帮助信息 ?
DB2LICM-H
68. 一个数据库至少包括哪些表空间 ?
一个目录表空间
一个或多个用户表空间
一个或多个临时表空间
69. 根据数据与存储空间之间移动的控制方式不同 , 分哪两种表空间 ?
系统管理的空间 (SMS)
数据库管理的空间 (DMS)
70. 如何列出系统数据库目录的内容 ?
LISTDATABASEDIRECTORY
71.CREATEDATABASE 是 SQL 命令吗 ?
不是 , 是系统命令
72. 如何查看数据库 ABC 的配置文件的内容 ?
GETDATABASECONFIGURATIONFORABC
73. 如何将数据库 ABC 的参数设置为默认数值 ?
RESETDATABASECONFIGURATIONFORABC
74. 如何修改数据库 ABC 配置参数数值 ?
UPDATEDATABASECONFIGURATIONFORABC
USING
75. 如何重新启动数据库 ?
RESTARTDATABASEDATABASE_NAME
76. 如何激活数据库 ?
ACTIVATEDATABASEDATABASE_NAME
77. 如何停止数据库 ?
DEACTIVATEDATABASEDATABASE_NAME
78. 如何删除数据库 ?
DROPDATABASEDATABASE_NAME
79. 如何建立模式 ?
CREATESCHEMASCHEMA_NAME
80. 如何设置模式 ?
SETSCHEMA=SCHEMA_NAME
81. 表的类型有哪些 ?
基本表
结果表
概要表
类型表
子类型
子表
声明的临时表
系统临时表
82. 如何定义序列 ?
CREATESEQUENCEORDERSEQSTARTWITH1INCREMENTBY1NOMAXVALUENOCYCLECACHE24
83. 如何将表置于检查挂起状态 ?
SETINTEGRITYTABLE_NAMEOFF
84. 如何获得表的排斥锁 ?
LOCKTABLETABLE_NAMEINEXCLUSIVEMODE
85. 如何把表调整为没有检查数据的在线状态 ?
SETINTEGRITYFORTABLE_NAMEALLIMMEDIATEUNCHECKED
86. 如何解除表的锁定 ?
COMMIT
87. 如何关闭表的日志 ?
ALTERTABLETABLE_NAMEACTIVENOTLOGGEDINIALLY
88. 如何删除表 ?
DROPSCHEMA.TABLE_NAME
89. 如何重命名表 ?
RENAMETABLE_OLDTOTABLE_NEW
90. 如何取当前时间 ?
SELECTCURRENTTIMESTAMPFROMSYSIBM.SYSDUMMY1
91. 如何创建 DB2 的概要表 ?
DB2 的概要表功能类似于 ORACLE 的实体化视图 !
语法为 :
CREATESUMMARYTABLETABLE_NAMEAS(FULLSELECT)...
例如 :
定义一个可以刷新的概要表 :
CREATESUMMARYTABLETABLE_NAMEAS(SELECT*FROMTABLE_NAME1WHERECOL1='AAA')
DATAINITIALLYDEFERREDREFRESHDEFERRED
其中 DATAINITIALLYDEFERRED 规定不能将数据作为 CREATETABLE 语句的一部分插入表中 .
REFRESHDEFERRED 规定表中的数据可以在任何使用了 REFRESHTABLE 语句的时候获得刷新 !
92. 如何刷新概要表 ?
REFRESHTABLESUM_TABLE
其中 SUM_TABLE 为概要表 .
93. 如何修改概要表 ?
ALTERTABLESUM_TABLE...
94. 如何创建临时表 ?
语法 :
DECLAREGLOBALTEMPORARYTABLETABLE_NAME
AS(FULLSELECT)DEFINITIONONLY
EXCLUDINGIDENTITYCOLUMNATTRIBUTES
ONCOMMITDELETEROWS
NOTLOGGED
第一行规定临时表的名称 .
第二行规定临时表的列的定义 .
第三行规定不是从源结果表定义中复制的恒等列 .
第四行规定如果没有打开 WITHGOLD 光标 , 将会删除表的所有行 .
第五行规定不对表的改变进行记录 .
例如 :
DECLAREGLOBALTEMPORARYTABLEDEC_BSEMPMS
AS(SELECT*FROMBSEMPMS)DEFINITIONONLY
EXCLUDINGIDENTITYCOLUMNATTRIBUTES
ONCOMMITDELETEROWS
NOTLOGGED
95. 视图的管理 ?
如何创建视图 :
CREATEVIEWVIEW_NAMEASSELECT*FROMTABLE_NAMEWHERE...
删除视图 :
DROPVIEWVIEW_NAME
96. 如何知道视图定义的内容 ?
SELECT*FROMSYSCAT.VIEWS 中的 TEXT 列中 .
97. 如何创建别名 ?
CREATEALIASALIAS_NAMEFORPRO_NAME
后面的 PRO_NAME 可以是 TABLE,VIEW,ALIAS,NICKNAME 等 .
98. 如何创建序列 ?
例如 :
CREATESEQUENCESEQUENCE_NAME
STARTWITHSTART_NUMBER
INCREMENTBYVALUE1
NOMAXVALUE
NOCYCLE
CACHEMAXIMUMNUMBEROFSEQUENCEVALUES
第一行规定序列的名称 .
第二行规定序列的开始数值 .
第三行规定每次新增的幅度 .
第四行规定没有最大数值限制 .
第五行规定最大数值限制 .
99. 如何更改序列 ?
ALTERSEQUENCESEQUENCE_NAME...
可以修改的参数
STARTWITH 的 START_NUMBER
INCREMENT 的 VALUE1
NOMAXVALUE 的数值
NOCYCLE 属性
MAXIMUMNUMBEROFSEQUENCEVALUES 最大数值
100. 如何删除序列 ?
DROPSEQUENCESEQUENCE_NAME
101.DB2 支持导入 (IMPORT) 的文件格式有 ?
有
EL,ASC,IXF,WSF 等102.DB2 支持导出 (EXPORT) 的文件格式有 ?
有
EL,IXF,WSF 等 .不支持 ASC 格式 .
103.DB2 支持载入 (LOAD) 的文件格式有 ?
有
EL,ASC,IXF 等 .不支持 WSF 格式 .
104.DB2 支持 DB2MOVE 的文件格式有 ?
有 :IXF 等 .
不支持 ASC,DEL,WSF 格式 .
105.DB2 数据库监控的两个组成部分 ?
快照监控 (SNAPSHOTMONITOR) 可返回特定时间点的数据库活动的快照 .
事件监控 (EVENTMONITOR) 记录事件发生的数据 .
106. 系统监控的数据元素类型 ?
计数器 (COUNTER) 记录活动发生的次数 .
测量 (GAUGE) 测量条目的当前值 .
水线 (WATERMARK) 从监控来元素达到的最大或最小数值 .
信息 (INFORMATION) 监控活动的参照类型细节 .
时间点 (TIMESTAMP) 活动发生的日期时间 .
时间 (TIME) 返回一个活动花费的时间 .
107. 如何知道监控堆所需的页的数量 ?
(NUMBEROFMONITORINGAPPLICATIONS+1)*(NUMBEROFDATABASES*(800+(NUMBEROFTABLES
ACCESSED*20)+((NUMBEROFAPPLICATIONSCONNECTED+1)*(200+(NUMBEROFTABLE
SPACES*100)))))/4096
其大小受参数 MON_HEAD_SZ 控制 .
108. 如何建立事件监控器 ?
CREATEEVENTMONITORTABLEMONFORTABLESWRITETOFILE'D:TEMP'
109. 如何激活事件监控器 ?
SETEVENTMONITORTABLEMONSTATE1
110. 如何停止事件监控器 ?
SETEVENTMONITORTABLEMONSTATE0
111. 如何查询监控器的状态 ?
SELECTEVMONNAME,EVENT_MON_STATE(EVMONNAME)FROMSYSCAT.EVENTMONITORS
112. 如何删除事件监控器 ?
DROPEVENTMONITORTABLEMON
113.UNIX 和 WINDOWS 上创建管道事件监控器 (PIPE 意 EVNT 见 MONITOR) 的不同 ?
第一步 : 定义事件监控器
UNIX:
CONNECTTOSAMPLE
CREATEEVENTMONITORSTMB2FORSTATEMENTSWRITETOPIPE'/TMP/EVPIPE1'
WINDOWS:
CONNECTTOSAMPLE
CREATEEVENTMONITORSTMB2FORSTATEMENTSWRITETOPIPE'/.TMPEVPIPE1'
第二步 : 建立命名管道
UNIX:
可以使用 MKFIFO() 函数或者 MKFIFO 命令 .
WINDOWS:
可以使用 CREATENAMEDPIPE() 函数 , 管道名称与 CREATEEVENTMONITOR 规定名称相同 .
第三步 : 打开命名管道
UNIX:
使用 OPEN() 函数 .
WINDOWS:
使用 CONNECTNAMEDPIPE() 函数 .
也可以用 DB2EVMON 命令 , 如 :
DB2EVMON-DBSAMPLE-EVMSTMB2
第四步 : 激活命名管道事件监控器
除非自动激活命名管道事件监控器 , 否则
SETEVENTMONITORSTMB2STATE1
第五步 : 从命名管道读取数据
UNIX:
可以使用 READ() 函数 .
WINDOWS:
可以使用 READFILE() 函数 .
第六步 : 停止事件监控器
SETEVENTMONITORSTMB2STATE0
第七步 : 关闭命名管道
UNIX:
可以使用 CLOSE() 函数 .
WINDOWS:
可以使用 DISCONNECTNAMEDPIPE() 函数 .
第八步 : 删除命名管道
UNIX:
可以使用 UNLINK() 函数 .
WINDOWS:
可以使用 CLOSEHANDLE() 函数 .
114.DB2 的 SQL 语句的类别
DCL: 数据控制语言 , 提供对数据库对象的访问权限 .
DDL: 数据定义语言 , 创建 , 修改 , 删除数据库对象的 .
DML: 数据操纵语言 , 用来插入 , 更新 , 删除数据的 .
115.DCL 的权限有哪些 ?
CONTROL 权限 : 如果用户创建一个对象 , 则改用户能完全访问该对象 .
GRANT 语句将权限授予给用户 .
REVOKE 语句撤销一个用户的权限 .
116.DDL 有哪些 ?
CREATE
DECLARE
ALTER
DROP
等
117.DML 有哪些 ?
INSERT
SELECT
UPDATE
DELETE
等
118.DB2 有没有布尔类型 ?
没有
119. 如何查询 DB2 的内置函数 ?
自带文档 ADMINISTION-->SQLREFERENCE-->FUNCTIONS 内
120. 如何执行 DB2 的脚本文件 ?
DB2-VTFFILENAME
121.DB2 中象 ORACLE 的 ROWNUM() 是 ?
ROW_NUMBER()OVER()
122.DB2 如何得到错误代码的说明 ?
DB2?SQLCODE
123.DB2 中的 VARCHAR 转换为 INTEGER 的函数为 ?
CAST()
124.DB2 中的 INTEGER 转换为 VARCHAR 的函数为 ?
CHAR()
125.DB2 中的 VARCHAR 转换为 DATE 的
函数为 ?
DATE()
126.DB2 中的 DATE 转换为 VARCHAR 的函数为 ?
CHAR()
127.DB2 中的 TRIGGER 能否修改 ?
不能 , 只能删除重建
128.WINDOWS 下如何知道 DB2 的端口号 ?
WINNTSYSTEM32DRIVERSETCSERVICES
129.DB2 如何执行存储过程 ?
可以 DB2CALLPROCEDURE_NAME
130. 如何进入 DB2 的 DOS 命令方式 ?
DB2CMD
131. 如何得到 DB2 的进程号 ?
DB2LISTAPPLICATIONS
132. 如何杀 DB2 的进程 ?
FORCEAPPLICATION(ID) DB2 函数
135.AVG()
返回一组数值的平均值 .
SELECTAVG(SALARY)FROMBSEMPMS;
136.CORR(),CORRELATION()
返回一对数值的关系系数 .
SELECTCORRELATION(SALARY,BONUS)FROMBSEMPMS;
137.COUNT()
返回一组行或值的个数 .
SELECTCOUNT(*)FROMBSEMPMS;
138.COVAR(),COVARIANCE()
返回一对数值的协方差 .
SELECTCOVAR(SALARY,BONUS)FROMBSEMPMS;
139.MAX()
返回一组数值中的最大值 .
SELECTMAX(SALARY)FROMBSEMPMS;
140.MIN()
返回一组数值中的最小值 .
SELECTMIN(SALARY)FROMBSEMPMS;
141.STDDEV()
返回一组数值的标准偏差 .
SELECTSTDDEV(SALARY)FROMBSEMPMS;
142.SUM()
返回一组数据的和 .
SELECTSUM(SALARY)FROMBSEMPMS;
143.VAR(),VARIANCE()
返回一组数值的方差 .
SELECTVARIANCE(SALARY)FROMBSEMPMS;
144.ABS(),ABSVAL()
返回参数的绝对值 .
SELECTABS(-3.4)FROMBSEMPMS;
145.ACOS()
返回参数的反余弦值 .
SELECTACOS(0.9)FROMBSEMPMS;
146.ASCII()
返回整数参数最左边的字符的 ASCII 码 .
SELECTASCII('R')FROMBSEMPMS;
147.ASIN()
返回用弧度表示的角度的参数的反正弦函数 .
SELECTASIN(0.9)FROMBSEMPMS;
148.ATAN()
返回参数的反正切值 , 该参数用弧度表示的角度的参数 .
SELECTATAN(0.9)FROMBSEMPMS;
149.ATAN2()
返回用弧度表示的角度的 X 和 Y 坐标的反正切值 .
SELECTATAN2(0.5,0.9)FROMBSEMPMS;
150.BIGINT()
返回整型常量中的数字或字符串的 64 位整数表示 .
SELECTBIGINT(EMP_NO)FROMBSEMPMS;
151.CEILING()ORCEIL()
返回比参数大或等于参数的最小的整数值 .
SELECTCEILING(3.56)FROMBSEMPMS;
SELECTCEIL(4.67)FROMBSEMPMS;
152.CHAR()
返回日期时间型 , 字符串 , 整数 , 十进制或双精度浮点数的字符串表示 .
SELECTCHAR(SALARY,',')FROMBSEMPMS;
153.CHR()
返回具有由参数指定的 ASCII 码的字符 .
SELECTCHAR(167)FROMBSEMPMS;
133.A 用户安装 DB2 后 , 如何用 B 用户启动 DATABASE?
在 B 用户下的 .PROFILE 中加上
./HOME/DB2INST/SQLLIB/DB2PROFILE
134.DB2 中类似 ORACLE 的快照是 ?
SUMMARYTABLE
154.CONCAT()
返回两个字符串的连接 .
SELECTCONCAT(EMP_NO,EMP_NAM)FROMBSEMPMS;
155.YEAR()
返回数值的年部分 .
SELECTYEAR('2003/01/02')FROMBSEMPMS;
156.VARCHAR()
返回字符串 , 日期型 , 图形串的可变长度的字符串表示 .
SELECTVARCHAR(EMP_NAM,50)FROMBSEMPMS;
157.UCASE()ORUPPER()
返回字符串的大写 .
SELECTUCASE(EMP_NAM)FROMBSEMPMS;
SELECTUPPER(EMP_NO)FROMBSEMPMS;
158.TRUNCATE()ORTRUNC()
从表达式小数点右边的位置开始截断并返回该数值 .
SELECTTRUNCATE(345.6789,2)FROMBSEMPMS;
159.TIME()
返回一个数值中的时间 .
SELECTTIME('2001-03-19.12.30.123456')FROMBSEMPMS;
160.SUBSTR(EXP1,EXP2)
返回 EXP1 串自 EXP2 处开始的子串 .
SELECTSUBSTR('CDNJFDJFJD',5)FROMBSEMPMS;
SELECTSUBSTR('CDNJFDJFJD',5,2)FROMBSEMPMS;
161.SQRT()
返回该参数的平方根 .
SELECTSQRT(36)FROMBSEMPMS;
162.SPACE()
返回由参数指定的长度 , 包含空格在内的字符串 .
SELECTSPACE(10)FROMBSEMPMS;
163.SECOND()
返回一个数值的秒部分 .
SELECTSECOND('18:34:32')FROMBSEMPMS;
164.RTRIM()
删除字符串尾部的空格 .
SELECTRTRIM('COMMENT')FROMBSEMPMS;
165.ROUND(EXP1,EXP2)
返回 EXP1 小数点右边的第 EXP2 位置处开始的四舍五入值 .
SELECTROUND(2345.6789,2)FROMBSEMPMS;
166.REPLACE(EXP1,EXP2,EXP3)
用 EXP3 替代 EXP1 中所有的 EXP2
SELECTCHAR(REPLACE('ROMANDD','NDD','CCB'),10)FROMBSEMPMS;
167.REPEAT(EXP1,EXP2)
返回 EXP1 重复 EXP2 次后的字符串 .
SELECTCHAR(REPEAT('REPEAT',3),21)FROMBSEMPMS;
168.REAL()
返回一个数值的单精度浮点数表示 .
SELECTREAL(10)FROMBSEMPMS;
169.RAND()
返回 0 和 1 之间的随机浮点数 .
SELECTRAND()FROMBSEMPMS;
170.POWER(EXP1,EXP2)
返回 EXP1 的 EXP2 次幂 .
SELECTPOWER(2,5)FROMBSEMPMS;
171.POSSTR(EXP1,EXP2)
返回 EXP2 在 EXP1 中的位置 .
SELECT('ABCDEFGH','D')FROMBSEMPMS;
172.NULLIF(EXP1,EXP2)
如果 EXP1=EXP2, 则为 NULL, 否则为 EXP1
173.NODENUMBER()
返回行的分区号 .
SELECTNODENUMBER(EMP_NO)FROMBSEMPMS;
174.MONTH()
返回一个数值的月部分 .
SELECTMONTH('2003/10/20')FROMBSEMPMS;
175.MOD(EXP1,EXP2)
返回 EXP1 除以 EXP2 的余数 .
SELECTMOD(20,8)FROMBSEMPMS;
176.MINUTE()
返回一个数值的分钟部分 .
SELECTMINUTE('18:34:23')FROMBSEMPMS;
177.LTRIM()
删除字符串前面的空格 .
SELECTLTRIM('CDDD')FROMBSEMPMS;
178.HOUR()
返回一个数值的小时部分 .
SELECTHOUR('18:34:23')FROMBSEMPMS;
179.DOUBLE()
如果参数是一个数字表达式 , 返回与其相对应的浮点数 , 如果参数是字符串表达式 , 则返回该数的字符串表达式 .
SELECTDOUBLE('5678')FROMBSEMPMS;
180.EXP()
返回参数的指数函数 .
SELECTEXP(2)FROMBSEMPMS;
181.FLOAT()
返回一个数的浮点表示 .
SELECTFLOAT(789)FROMBSEMPMS;
182.FLOOR()
返回小于或等于参数的最大整数 .
SLECTFLOOR(88.93)FROMBSEMPMS;
183.HEX()
返回一个表示为字符串的值的 16 进制表示 .
SELECTHEX(16)FROMBSEMPMS;
sql培训内容（精简）
作者:vlife
SQL定义：SQL是一种面向数据库的通用数据处理语言规范，能完成以下几类功能：提取查询数据，插入修改删除数据，生成修改和删除数据库对象，数据库安全控制，数据库完整性及数据保护控制。
SQL分类：
DDL—数据定义语言（CREATE，ALTER，DROP，DECLARE）
DML—数据操纵语言（SELECT，DELETE，UPDATE，INSERT）
DCL—数据控制语言（GRANT，REVOKE，COMMIT，ROLLBACK）
db2数据类型
CHAR()：定长字符串 最大长度为 254
VARCHAR()：变长字符 最大长度为 4000
SMALLINT：短整型数字 长度为 2 字节
INTEGER：整型数字 长度为 4 字节
REAL：单精度浮点 32 位近似值
DOUBLE：双精度浮点 64 位近似值
DECIMAL(m,n)：数字 精度为m小数位为n
DATE：日期时间
TIME：日期时间
TIMESTAMP：日期时间
DDL—数据库定义语言：直接提交的。
CREATE：用于创建数据库对象。
DECLARE：除了是创建只在过程中使用的临时表外，DECLARE语句和CREATE语句非常相似。唯一可以被声明的对象是表。并且必须放入用户临时表空间。
DROP：可以删除任何用CREATE（数据库对象）和DECLARE（表）创建的对象。
ALTER：允许修改某些数据库对象的信息。不能修改索引。
下面主要基于对象介绍基本的语法：
1、数据库：
创建数据库：CREATE DATABASE database-name [USING CODESET codeset TERRITORY territory]
注：代码页的问题。
删除数据库：drop database dbname
2、表：
创建新表：
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)
根据已有的表创建新表：
A：create table tab_new like tab_old
B：create table tab_new as select col1,col2… from tab_old definition only
修改表：
增加一个列:
Alter table tabname add column col type
注：列增加后将不能删除。DB2中列加上后数据类型也不能改变，唯一能改变的是增加varchar类型的长度。添加主键：
Alter table tabname add primary key(col)
删除主键：
Alter table tabname drop primary key(col)
删除表：drop table tabname
3、表空间：
创建表空间：create tablespace tbsname pagesize 4k managed by database using (file ‘file’ size)
表空间加入容器：alter tablespace tablespace_name add(file 'filename' size)
注：该操作是不可逆的，加入容器后将不能将其删除，因此在加入的时候注意。
删除表空间：drop tablespace tbsname
4、索引：
创建索引：create [unique] index idxname on tabname(col….)
删除索引：drop index idxname
注：索引是不可更改的，想更改必须删除重新建。
5、视图：
创建视图：create view viewname as select statement
删除视图：drop view viewname
注：视图唯一能修改的是引用类型列，改变列的范围。其他定义好了都不能修改。当视图基于的基表drop后，视图变为无效。
DML—数据库操纵语言，它不隐式地提交当前事务 ，是否提交视环境设定而定。
SELECT：从表中查询符合数据
注：条件中连接的问题，避免出现笛卡儿乘积
DELETE：删除已有表的数据
UPDATE：更新已有表的数据
INSERT：向已有表中插入数据
注：DELETE，UPDATE和INSERT是否直接提交取决与执行语句所在的环境。
在执行时注意事务日志满的情况。
1、select时，注意索引谓词和非索引谓词，尽量在有索引的列上使用索引谓词。
谓词类型 可索引 注 释
Col∝con Y ∝代表>;,>;=,=,<=,<,但是<>;不是可索引的。
Col between con1 and con2 Y 在匹配系列中必须是最后的。
Col in list Y 仅对一个匹配列
Col is null Y
Col like ‘xyz%’ Y 模糊匹配%在后面。
Col like ‘%xyz’ N 模糊匹配%在前面。
Col1∝Col2 N Col1和col2来自同一个表
Col∝Expression N 例如：c1（c1+1）/2
Pred1 and Pred2 Y Pred1和Pred2都是可索引的，指相同索引的列
Pred1 or Pred2 N 除了（c1=a or c1=b）外，他可以被认为是c1 in（a，b）
Not Pred1 N 或者任何的等价形式：Not between,Not in,Not like等等。
使用索引的例子介绍：
<1>;、单个表上索引查询的介绍 ：
A：select * from t1 where c1 = 10；
在c1列上无索引，如何检索
在c1列上有索引，如何检索
B：select * from t1 where c1 =10 and c2 between 5 and 10 and c3 like ‘A%’
仅在c1列上有索引
在c1,c2和c3列上有单独的索引
在c1，c2和c3列上有联合索引
此处可以填加索引匹配规则和高级规则，比较难理解！！
<2>;、两个或多个表上索引查询的介绍：
(1)、使用循环嵌套法执行查询
A：select t1.c1,t1.c2,t2.c3,t2.c4 from t1,t2 where t1.c1=10 and t1.c2 =t2.c3
介绍查询的方法，看看哪列上最需要索引。
B：select t1.c1,t1.c2,t2.c3,t2.c4 from t1,t2 where t1.c1=10 and t2.c4 = 10 and t1.c2 =t2.c3
(2)、使用归并连接执行连接查询
例：select t1.c1,t1.c2,t2.c3,t2.c4 from t1,t2 where t1.c1=10 and t2.c4 = 10 and t1.c2 =t2.c3
(3)、三个以上的表进行连接查询采取的规则或者方法
（2）、GROUP BY：实现简单分组的功能，当用group by时，查询选择列中除了在group by中出现的和常量外，其他的列上要用分组函数。可以使用一些分组函数实现一些列不在group by中出现，min，max等。
（3）、HAVING的使用，对一些分组列进行条件判断。
（4）、ORDER BY子句使得SQL在显示查询结果时将各返回行按顺序排列，返回行的排列顺序由ORDER BY 子句指定的表达式的值确定。
2、DELETE：从表中删除记录
语法格式：
DELETE FROM tablename WHERE (conditions)
3、INSERT：向表中插入记录
语法格式：
INSERT INTO tablename (col1,col2,…) VALUES (value1,value2,…);
INSERT INTO tablename (col1,col2,…) VALUES (value1, value2,…), (value1, value2,…),……
Insert不会等待任何程序，不会导致锁定。
4、UPDATE：
语法格式：
UPDATE tabname SET (col1=values1,col2=values2,…) WHERE (conditions);
注：update的速度比较慢，要在相应列上建立索引。
DCL—数据控制语言
GRANT—授予用户权限
REVOKE—撤消用户权限
COMMIT—提交事务，可以使数据库的修改永久化
ROLLBACK—回滚事务，消除上一个COMMIT命令后的所做的全部修改，使得数据库的内容恢复到上一个COMMIT执行后的状态.。
1、GRANT：所有着或者管理员把访问权限赋给其他用户
语法格式：
grant [all privileges|privileges,….] on tabname | viewname to [public|user,….]
2、REVOKE:取消某一用户的某中访问权限
语法格式：
Revoke [all privileges|privileges,….] on tabname | viewname from [public|user,….]
注：不能取消实例级别的用户的任何权限，他们不是通过grant授权的，是通过组实现的权限。
3、COMMIT:把事务中所做的修改永久化记录到数据库。
语法格式：
commit [work]
4、ROLLBACK:将上次提交以来所做的更改全部撤消。
语法格式：
Rollback [work]
高级sql简单介绍
一、查询间使用运算词
A： UNION 运算符
UNION 运算符通过组合其他两个结果表（例如 TABLE1 和 TABLE2）并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时（即 UNION ALL），不消除重复行。两种情况下，派生表的每一行不是来自 TABLE1 就是来自 TABLE2。
B： EXCEPT 运算符
EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL)，不消除重复行。
C： INTERSECT 运算符
INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL)，不消除重复行。
注：使用运算词的几个查询结果行必须是一致的。
二、外连接
A、left outer join：
左外连接（左连接）：结果集几包括连接表的匹配行，也包括左连接表的所有行。
B：right outer join:
右外连接(右连接)：结果集既包括连接表的匹配连接行，也包括右连接表的所有行。
C：full outer join：
全外连接：不仅包括符号连接表的匹配行，还包括两个连接表中的所有记录。
注：复合外连接按照从左到右的顺序执行连接，左边连接的结果集和右边连接
三、超级分组和移动函数
A：grouping sets：用来在单个sql中形成多级分组。
例：select company_id,node_id,count(customer_id) from customer group by grouping sets(company_id,node_id)
B：rollup：可以在单个数据库操作中形成多个分组。
例：select company_id,node_id,count(customer_id) from customer group by rollup(company_id,node_id)
注：rollup操作不是可交换的操作，指定用户组的顺序是很重要的。
C：cube： 生成分组表中分组的所有组合。
例：select company_id,node_id,count(customer_id) from customer group by cube(company_id,node_id)
D：over：移动函数可以帮助实现移动的数据分析
Select date,avg(qty) over(order by date rows between 1 preceding and 1 following) as values from sale
附录：常用函数介绍
1、类型转化函数：
转化为数字类型的：
decimal, double, Integer, smallint,real
Hex(arg):转化为参数的16进制表示。
转化为字符串类型的：
char, varchar
Digits(arg):返回arg的字符串表示法，arg必须为decimal。
转化为日期时间的：
date, time,timestamp
2、时间日期：
year, quarter, month, week, day, hour, minute ,second
dayofyear(arg):返回arg在年内的天值
Dayofweek(arg):返回arg在周内的天值
days(arg):返回日期的整数表示法，从0001-01-01来的天数。
midnight_seconds(arg):午夜和arg之间的秒数。
Monthname(arg):返回arg的月份名。
Dayname(arg):返回arg的星期。
3、字符串函数：
length,lcase, ucase, ltrim, rtrim
Coalesce(arg1,arg2….):返回参数集中第一个非null参数。
Concat (arg1,arg2):连接两个字符串arg1和arg2。
insert(arg1,pos,size,arg2):返回一个，将arg1从pos处删除size个字符，将arg2插入该位置。
left(arg,length):返回arg最左边的length个字符串。
locate(arg1,arg2,<pos>白眼:在arg2中查找arg1第一次出现的位置，指定pos，则从arg2的pos处开始找arg1第一次出现的位置。
posstr(arg1,arg2):返回arg2第一次在arg1中出现的位置。
repeat(arg1 ,num_times):返回arg1被重复num_times次的字符串。
replace(arg1,arg2,arg3):将在arg1中的所有arg2替换成arg3。
right(arg,length):返回一个有arg左边length个字节组成的字符串。
space(arg):返回一个包含arg个空格的字符串。
substr(arg1,pos,<length>白眼:返回arg1中pos位置开始的length个字符，如果没指定length，则返回剩余的字符。
4、数学函数：
Abs, count, max, min, sum
Ceil(arg):返回大于或等于arg的最小整数。
Floor(arg):返回小于或等于参数的最小整数。
Mod(arg1,arg2):返回arg1除以arg2的余数，符号与arg1相同。
Rand():返回1到1之间的随机数。
Power(arg1,arg2):返回arg1的arg2次方。
Round(arg1,arg2):四舍五入截断处理，arg2是位数，如果arg2为负，则对小数点前的数做四舍五入处理。
Sigh(arg):返回arg的符号指示符。-1,0,1表示。
truncate(arg1,arg2):截断arg1，arg2是位数，如果arg2是负数，则保留arg1小数点前的arg2位。
5、其他：
nullif(arg1,arg2)：如果2个参数相等，则返回null，否则，返回参数1
为了大家更容易理解我举出的SQL语句，本文假定已经建立了一个学生成绩管理数据库，全文均以学生成绩的管理为例来描述。
1.在查询结果中显示列名：
a.用as关键字：select name as '姓名' from students order by age
b.直接表示：select name '姓名' from students order by age
2.精确查找:
a.用in限定范围：select * from students where native in ('湖南', '四川')
b.between...and：select * from students where age between 20 and 30
c.“=”：select * from students where name = '李山'
d.like:select * from students where name like '李%' (注意查询条件中有“%”，则说明是部分匹配，而且还有先后信息在里面，即查找以“李”开头的匹配项。所以若查询有“李”的所有对象，应该命令：'%李%';若是第二个字为李，则应为'_李%'或'_李'或'_李_'。)
e.[]匹配检查符：select * from courses where cno like '[AC]%' (表示或的关系，与"in(...)"类似，而且"[]"可以表示范围，如：select * from courses where cno like '[A-C]%')
3.对于时间类型变量的处理
a.smalldatetime：直接按照字符串处理的方式进行处理，例如：
select * from students where birth > = '1980-1-1' and birth <= '1980-12-31'
4.集函数
a.count()求和，如：select count(*) from students (求学生总人数)
b.avg(列)求平均，如：select avg(mark) from grades where cno=’B2’
c.max(列)和min(列)，求最大与最小
5.分组group
常用于统计时，如分组查总数：
select gender,count(sno)
from students
group by gender
(查看男女学生各有多少)
注意：从哪种角度分组就从哪列"group by"
对于多重分组，只需将分组规则罗列。比如查询各届各专业的男女同学人数 ，那么分组规则有：届别(grade)、专业(mno)和性别(gender)，所以有"group by grade, mno, gender"
select grade, mno, gender, count(*)
from students
group by grade, mno, gender
通常group还和having联用，比如查询1门课以上不及格的学生，则按学号(sno)分类有：
select sno,count(*) from grades
where mark<60
group by sno
having count(*)>1
6.UNION联合
合并查询结果，如：
SELECT * FROM students
WHERE name like ‘张%’
UNION [ALL]
SELECT * FROM students
WHERE name like ‘李%’
7.多表查询
a.内连接
select g.sno,s.name,c.coursename
from grades g JOIN students s ON g.sno=s.sno
JOIN courses c ON g.cno=c.cno
(注意可以引用别名)
b.外连接
b1.左连接
select courses.cno,max(coursename),count(sno)
from courses LEFT JOIN grades ON courses.cno=grades.cno
group by courses.cno
左连接特点：显示全部左边表中的所有项目，即使其中有些项中的数据未填写完全。
左外连接返回那些存在于左表而右表中却没有的行，再加上内连接的行。
b2.右连接
与左连接类似
b3.全连接
select sno,name,major
from students FULL JOIN majors ON students.mno=majors.mno
两边表中的内容全部显示
c.自身连接
select c1.cno,c1.coursename,c1.pno,c2.coursename
from courses c1,courses c2 where c1.pno=c2.cno
采用别名解决问题。
d.交叉连接
select lastname+firstname from lastname CROSS JOIN firstanme
相当于做笛卡儿积
8.嵌套查询
a.用关键字IN,如查询李山的同乡：
select * from students
where native in (select native from students where name=’ 李山’)
b.使用关键字EXIST,比如，下面两句是等价的：
select * from students
where sno in (select sno from grades where cno=’B2’)
select * from students where exists
(select * from grades where
grades.sno=students.sno AND cno=’B2’)
9.关于排序order
a.对于排序order，有两种方法：asc升序和desc降序
b.对于排序order,可以按照查询条件中的某项排列，而且这项可用数字表示，如：
select sno,count(*) ,avg(mark) from grades
group by sno
having avg(mark)>85
order by 3
10.其他
a.对于有空格的识别名称，应该用"[]"括住。
b.对于某列中没有数据的特定查询可以用null判断，如select sno,courseno from grades where mark IS NULL
c.注意区分在嵌套查询中使用的any与all的区别，any相当于逻辑运算“||”而all则相当于逻辑运算“&&”
d.注意在做否定意义的查询是小心进入陷阱：
如，没有选修‘B2’课程的学生 ：
select students.*
from students, grades
where students.sno=grades.sno
AND grades.cno <> ’B2’
上面的查询方式是错误的，正确方式见下方：
select * from students
where not exists (select * from grades
where grades.sno=students.sno AND cno='B2')
11.关于有难度多重嵌套查询的解决思想：
如，选修了全部课程的学生：
select *
from students
where not exists ( select *
from courses
where NOT EXISTS
(select *
from grades
where sno=students.sno
AND cno=courses.cno))
最外一重：从学生表中选，排除那些有课没选的。用not exist。由于讨论对象是课程，所以第二重查询从course表中找，排除那些选了课的即可。
- sql宝典
- SQL宝典
- SQL宝典
- sql宝典
- SQL宝典
- SQL Server宝典(1)
- SQL Server宝典(2)
- SQL Server 宝典
- SQL Server 宝典
- SQL Server宝典(S1)
- SQL Server宝典(S2)
- SQL 之面试宝典
- Oracle宝典--常用SQL
- Oracle sql 宝典
- 徐氏SQL宝典 之三
- Microsoft SQL Server 2000宝典
- sql 宝典笔记（1）
- 新手宝典：SQL Server 几个安全问题
- 一个拨号方案的例子
- 做一个CFrameWnd.(用到的相关类可在前几篇内找到)
- ASP.NET常用代码
- Some tricks for working with WPF’s RichTextBox
- 几种常用的代码
- sql宝典
- 感悟
- 从 ASP.NET 服务器控件插入客户端脚本
- Asp.net页面的生命周期
- GNU gcc 使用手册
- 我的JS TreeView树（一）
- JAVA环境变量配置介绍
- RMS的遍历
- 关于整数部分为0的小数在web上的显示问题
