1 一一一一、、、、基础基础基础基础 1111、、、、说明说明说明说明：：：：创建数据库创建数据库创建数据库创建数据库 CREATE DATABASE databaseCREATE DATABASE databaseCREATE DATABASE databaseCREATE DATABASE database----namenamenamename 2222、、、、说明说明说明说明：：：：删除数据库删除数据库删除数据库删除数据库 drop database dbnamedrop database dbnamedrop database dbnamedrop database dbname 3333、、、、说明说明说明说明：：：：备份备份备份备份 sql serversql serversql serversql server ------------ 创建创建创建创建 备份数据的备份数据的备份数据的备份数据的 device device device device USE masterUSE masterUSE masterUSE master EXEC sp_addumpdevice 'disk', 'testBack', 'c:EXEC sp_addumpdevice 'disk', 'testBack', 'c:EXEC sp_addumpdevice 'disk', 'testBack', 'c:EXEC sp_addumpdevice 'disk', 'testBack', 'c:\\\\mssql7backupmssql7backupmssql7backupmssql7backup\\\\MyNwind_1.MyNwind_1.MyNwind_1.MyNwind_1. dat'dat'dat'dat' ------------ 开始开始开始开始 备份备份备份备份 BACKUP DATABASE pubs TO testBackBACKUP DATABASE pubs TO testBackBACKUP DATABASE pubs TO testBackBACKUP DATABASE pubs TO testBack 4444、、、、说明说明说明说明：：：：创建新表创建新表创建新表创建新表 ccccreate table tabname(col1 type1 [not null] [primary key],col2 type2 reate table tabname(col1 type1 [not null] [primary key],col2 type2 reate table tabname(col1 type1 [not null] [primary key],col2 type2 reate table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..[not null],..[not null],..[not null],.. )))) 根据已有的表创建新表根据已有的表创建新表根据已有的表创建新表根据已有的表创建新表：：：： AAAA：：：：create table tab_new like tab_old (create table tab_new like tab_old (create table tab_new like tab_old (create table tab_new like tab_old (使用旧表创建新表使用旧表创建新表使用旧表创建新表使用旧表创建新表)))) BBBB：：：：create table tab_new as select col1,col2… from tab_old definitioncreate table tab_new as select col1,col2… from tab_old definitioncreate table tab_new as select col1,col2… from tab_old definitioncreate table tab_new as select col1,col2… from tab_old definition only only only only 5555、、、、说明说明说明说明：：：：删除新表删除新表删除新表删除新表 drop table tabnamedrop table tabnamedrop table tabnamedrop table tabname 6666、、、、说明说明说明说明：：：：增加一个列增加一个列增加一个列增加一个列 Alter table tabname add column col typeAlter table tabname add column col typeAlter table tabname add column col typeAlter table tabname add column col type 注注注注：列增加后将不能删除。DB2 中列加上后数据类型也不能改变，唯一能改变的 是增加 varchar 类型的长度。 7777、、、、说明说明说明说明：：：：添加主键添加主键添加主键添加主键： Alter table tabname add primary key(col) Alter table tabname add primary key(col) Alter table tabname add primary key(col) Alter table tabname add primary key(col) 说明：删除主键： Alter table tabname drop primary key(col) Alter table tabname drop primary key(col) Alter table tabname drop primary key(col) Alter table tabname drop primary key(col) 8888、、、、说明说明说明说明：：：：创建索引创建索引创建索引创建索引：create [unique] index idxname on tabname(col….) create [unique] index idxname on tabname(col….) create [unique] index idxname on tabname(col….) create [unique] index idxname on tabname(col….) 删除索引：drop index idxnamedrop index idxnamedrop index idxnamedrop index idxname 注：索引是不可更改的，想更改必须删除重新建。 9999、、、、说明说明说明说明：：：：创建视图创建视图创建视图创建视图：create view viewname as select statement 删除视图删除视图删除视图删除视图：drop view viewname 10101010、、、、说明说明说明说明：：：：几个简单的基本的几个简单的基本的几个简单的基本的几个简单的基本的 sqlsqlsqlsql 语句语句语句语句 选择选择选择选择：：：：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 2 求和求和求和求和：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 11111111、、、、说明说明说明说明：：：：几个高级查询运算词几个高级查询运算词几个高级查询运算词几个高级查询运算词 AAAA：：：： UNION UNION UNION UNION 运算符运算符运算符运算符 UNION 运算符通过组合其他两个结果表（例如 TABLE1 和 TABLE2）并消去表中 任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时（即 UNION AL L），不消除重复行。两种情况下，派生表的每一行不是来自 TABLE1 就是来自 TABLE2 。 BBBB：：：： EXCEPT EXCEPT EXCEPT EXCEPT 运算符运算符运算符运算符 EXCEPTEXCEPTEXCEPTEXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重 复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL)，不消 除重复行。 CCCC：：：： INTERSECT INTERSECT INTERSECT INTERSECT 运算符运算符运算符运算符 INTERSECTINTERSECTINTERSECTINTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复 行而派生出一个结果表。当 ALLALLALLALL 随 INTERSECT 一起使用时 (INTERSECT ALL)， 不消除重复行。 注注注注：：：：使用运算词的几个查询结果行必须是一致的。 12121212、、、、说明说明说明说明：：：：使用外连接使用外连接使用外连接使用外连接 A、left left left left （（（（outerouterouterouter）））） join join join 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 BBBB：：：：right right right right （（（（outerouterouterouter）））） join: join: join: join: 右外连接(右连接)：结果集既包括连接表的匹配连接行，也包括右连接表的所有 行。 CCCC：：：：fullfullfullfull/cross/cross/cross/cross （（（（outerouterouterouter）））） join join join join： 全外连接：不仅包括符号连接表的匹配行，还包括两个连接表中的所有记录。 12121212、、、、分组分组分组分组:Group by::Group by::Group by::Group by: 一张表，一旦分组 完成后，查询后只能得到组相关的信息。 组相关的信息组相关的信息组相关的信息组相关的信息：：：：（统计信息） count,sum,max,min,avg 分组的标准分组的标准分组的标准分组的标准)))) 在在在在 SQLServerSQLServerSQLServerSQLServer 中分组时中分组时中分组时中分组时：：：：不能以不能以不能以不能以 texttexttexttext ,,,,ntextntextntextntext ,,,,imageimageimageimage 类型的字段作为分组依类型的字段作为分组依类型的字段作为分组依类型的字段作为分组依 据据据据 在在在在 selecteselecteselecteselecte 统计函数统计函数统计函数统计函数中的字段中的字段中的字段中的字段，，，，不能和不能和不能和不能和普通普通普通普通的字段放在一起的字段放在一起的字段放在一起的字段放在一起；；；； 11113333、、、、对数据库进行操作对数据库进行操作对数据库进行操作对数据库进行操作：：：： 分离数据库分离数据库分离数据库分离数据库： sp_detach_db; sp_detach_db; sp_detach_db; sp_detach_db; 附加数据库附加数据库附加数据库附加数据库：sp_attach_db sp_attach_db sp_attach_db sp_attach_db 后接表明，附加 需要完整的路径名 14141414....如何修改数据库的名称如何修改数据库的名称如何修改数据库的名称如何修改数据库的名称:::: sp_renamedb 'old_name', 'new_name' 3 二二二二、、、、提升提升提升提升 1111、、、、说明说明说明说明：：：：复制表复制表复制表复制表((((只复制结构只复制结构只复制结构只复制结构,,,,源表名源表名源表名源表名：：：：a a a a 新表名新表名新表名新表名：：：：b) (Accessb) (Accessb) (Accessb) (Access 可用可用可用可用)))) 法一法一法一法一：：：：select * into b from a where 1<>1（仅用于 SQlServer ） 法二法二法二法二：：：：select top 0 * into b from a 2222、、、、说明说明说明说明：：：：拷贝表拷贝表拷贝表拷贝表((((拷贝数据拷贝数据拷贝数据拷贝数据,,,,源表名源表名源表名源表名：：：：a a a a 目标表名目标表名目标表名目标表名：：：：b) (Accessb) (Accessb) (Accessb) (Access 可用可用可用可用)))) insert into b(a, b, c) select d,e,f from b; 3333、、、、说明说明说明说明：：：：跨数据库之间表的拷贝跨数据库之间表的拷贝跨数据库之间表的拷贝跨数据库之间表的拷贝((((具体数据使用绝对路径具体数据使用绝对路径具体数据使用绝对路径具体数据使用绝对路径) (Access) (Access) (Access) (Access 可用可用可用可用)))) insert into b(a, b, c) select d,e,f from b in ‘具体数据库’ where 条 件 例子：..from b in '"&Server.MapPath(".")&"\data.mdb" &"' where.. 4444、、、、说明说明说明说明：：：：子查子查子查子查询询询询((((表名表名表名表名 1111：：：：a a a a 表名表名表名表名 2222：：：：b)b)b)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) 5555、、、、说明说明说明说明：：：：显示文章显示文章显示文章显示文章、、、、提交人和最后回复时间提交人和最后回复时间提交人和最后回复时间提交人和最后回复时间 select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b 6666、、、、说明说明说明说明：：：：外连接查外连接查外连接查外连接查询询询询((((表名表名表名表名 1111：：：：a a a a 表名表名表名表名 2222：：：：b)b)b)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 7777、、、、说明说明说明说明：：：：在线视图查询在线视图查询在线视图查询在线视图查询((((表名表名表名表名 1111：：：：a )a )a )a ) select * from (SELECT a,b,c FROM a) T where t.a > 1; 8888、、、、说明说明说明说明：：：：betweenbetweenbetweenbetween 的用法的用法的用法的用法,between,between,between,between 限制查询数据范围时包括了边界值限制查询数据范围时包括了边界值限制查询数据范围时包括了边界值限制查询数据范围时包括了边界值,not bet,not bet,not bet,not bet weenweenweenween 不包括不包括不包括不包括 select * from table1 where time between time1 and time2 select a,b,c, from table1 where a not between 数值 1 and 数值 2 9999、、、、说明说明说明说明：：：：in in in in 的使用方法的使用方法的使用方法的使用方法 select * from table1 where a [not] in (‘值 1’,’值 2’,’值 4’,’值 6’) 10101010、、、、说明说明说明说明：：：：两张关联表两张关联表两张关联表两张关联表，，，，删除主表中已经在副表中没有的信息删除主表中已经在副表中没有的信息删除主表中已经在副表中没有的信息删除主表中已经在副表中没有的信息 delete from table1 where not exists ( select * from table2 where tabl e1.field1=table2.field1 ) 11111111、、、、说明说明说明说明：：：：四表联查问题四表联查问题四表联查问题四表联查问题：：：： 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 ..... 4 12121212、、、、说明说明说明说明：：：：日程安排提前五分钟提醒日程安排提前五分钟提醒日程安排提前五分钟提醒日程安排提前五分钟提醒 SQL: select * from 日程安排 where datediff('minute',f 开始时间,getdat e())>5 13 、、、、说明说明说明说明：：：：一条一条一条一条 sql 语句搞定数据库分页语句搞定数据库分页语句搞定数据库分页语句搞定数据库分页 select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序 字段 desc) a, 表名 b where b. 主键字段 = a. 主键字段 order by a. 排序字段 具体实现具体实现具体实现具体实现：：：： 关于数据库分页： declare @start int,@end int @sql nvarchar(600) set @sql= ’select top’+str (@end-@start+1)+ ’+from T where rid not in(select top’+str (@str-1)+ ’Rid from T where Rid>-1)’ exec sp_executesql @sql 注意注意注意注意：：：：在在在在 toptoptoptop 后不能直接跟一个变量后不能直接跟一个变量后不能直接跟一个变量后不能直接跟一个变量，，，，所以在实际应用中只有这样的进行特殊所以在实际应用中只有这样的进行特殊所以在实际应用中只有这样的进行特殊所以在实际应用中只有这样的进行特殊 的处理的处理的处理的处理。。。。RidRidRidRid 为一个标识列为一个标识列为一个标识列为一个标识列，，，，如果如果如果如果 toptoptoptop 后还有具体的字段后还有具体的字段后还有具体的字段后还有具体的字段，，，，这样做是非常有好处这样做是非常有好处这样做是非常有好处这样做是非常有好处 的的的的。。。。因为这样可以避免因为这样可以避免因为这样可以避免因为这样可以避免 top top top top 的字段如果是逻辑索引的的字段如果是逻辑索引的的字段如果是逻辑索引的的字段如果是逻辑索引的，，，，查询的结果后实际表中查询的结果后实际表中查询的结果后实际表中查询的结果后实际表中 的不一致的不一致的不一致的不一致（（（（逻辑索引中的数据有可能和数据表中的不一致逻辑索引中的数据有可能和数据表中的不一致逻辑索引中的数据有可能和数据表中的不一致逻辑索引中的数据有可能和数据表中的不一致，，，，而查询时如果处在而查询时如果处在而查询时如果处在而查询时如果处在 索引则首先查询索引索引则首先查询索引索引则首先查询索引索引则首先查询索引）））） 14141414、、、、说明说明说明说明：：：：前前前前 10101010 条记录条记录条记录条记录 select top 10 * form table1 where 范围 15151515、、、、说明说明说明说明：：：：选择在每一组选择在每一组选择在每一组选择在每一组 bbbb 值相同的数据中对应的值相同的数据中对应的值相同的数据中对应的值相同的数据中对应的 aaaa 最大的记录的所有信息最大的记录的所有信息最大的记录的所有信息最大的记录的所有信息((((类类类类 似这样的用法可以用于论坛每月排行榜似这样的用法可以用于论坛每月排行榜似这样的用法可以用于论坛每月排行榜似这样的用法可以用于论坛每月排行榜,,,,每月热销产品分析每月热销产品分析每月热销产品分析每月热销产品分析,,,,按科目成绩排名按科目成绩排名按科目成绩排名按科目成绩排名,,,, 等等等等等等等等.).).).) select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b) 16161616、、、、说明说明说明说明：：：：包括所有在包括所有在包括所有在包括所有在 TableATableATableATableA 中但不在中但不在中但不在中但不在 TableBTableBTableBTableB 和和和和 TableCTableCTableCTableC 中的行并消除所有中的行并消除所有中的行并消除所有中的行并消除所有 重复行而派生出一个结果表重复行而派生出一个结果表重复行而派生出一个结果表重复行而派生出一个结果表 (select a from tableA ) except (select a from tableB) except (select a from tableC) 17171717、、、、说明说明说明说明：：：：随机取出随机取出随机取出随机取出 10101010 条数据条数据条数据条数据 select top 10 * from tablename order by newid()newid()newid()newid() 18181818、、、、说明说明说明说明：：：：随机选择记录随机选择记录随机选择记录随机选择记录 select newid() 19191919、、、、说明说明说明说明：：：：删除重复记录删除重复记录删除重复记录删除重复记录 1),1),1),1), delete from tablename where id not in (select max(id) from tablena me group by col1,col2,...) 2)2)2)2) ,select distinct * into temp from tablename 5 delete from tablename insert into tablename select * from temp 评价评价评价评价：：：： 这种操作牵连大量的数据的移动这种操作牵连大量的数据的移动这种操作牵连大量的数据的移动这种操作牵连大量的数据的移动，，，，这种做法不适合大容量但数据操作这种做法不适合大容量但数据操作这种做法不适合大容量但数据操作这种做法不适合大容量但数据操作 3),3),3),3),例如例如例如例如：：：：在一个外部表中导入数据在一个外部表中导入数据在一个外部表中导入数据在一个外部表中导入数据，，，，由于某些原因第一次只导入了一部分由于某些原因第一次只导入了一部分由于某些原因第一次只导入了一部分由于某些原因第一次只导入了一部分，，，，但但但但 很难判断具体位置很难判断具体位置很难判断具体位置很难判断具体位置，，，，这样只有在下一次全部导入这样只有在下一次全部导入这样只有在下一次全部导入这样只有在下一次全部导入，，，，这样也就产生好多重复的字这样也就产生好多重复的字这样也就产生好多重复的字这样也就产生好多重复的字 段段段段，，，，怎样删除重复字段怎样删除重复字段怎样删除重复字段怎样删除重复字段 alter table tablename --添加一个自增列 add column_b int identity(1,1) delete from tablename where column_b not in( select max(column_b ) from tablename group by column1,column2,... ) alter table tablename drop column column_b 20202020、、、、说明说明说明说明：：：：列出数据库里所有的表名列出数据库里所有的表名列出数据库里所有的表名列出数据库里所有的表名 select name from sysobjects where type='U' // U 代表用户 21212121、、、、说明说明说明说明：：：：列出表里的所有的列出表里的所有的列出表里的所有的列出表里的所有的列名列名列名列名 select name from syscolumns where id=object_id('TableName') 22222222、、、、说明说明说明说明：：：：列示列示列示列示 typetypetypetype、、、、vendervendervendervender、、、、pcspcspcspcs 字段字段字段字段，，，，以以以以 typetypetypetype 字段排列字段排列字段排列字段排列，，，，casecasecasecase 可以方便地可以方便地可以方便地可以方便地 实现多重选择实现多重选择实现多重选择实现多重选择，，，，类似类似类似类似 select select select select 中的中的中的中的 casecasecasecase。。。。 select type,sum(case vender when 'A' then pcs else 0 end),sum(case ve nder when 'C' then pcs else 0 end),sum(case vender when 'B' then pcs else 0 end) FROM tablename group by type 显示结果显示结果显示结果显示结果：：：： type vender pcstype vender pcstype vender pcstype vender pcs 电脑 A 1 电脑 A 1 光盘 B 2 光盘 A 2 手机 B 3 手机 C 3 23232323、、、、说明说明说明说明：：：：初始化表初始化表初始化表初始化表 table1table1table1table1 TRUNCATE TABLE table1 24242424、、、、说明说明说明说明：：：：选择从选择从选择从选择从 10101010 到到到到 15151515 的记录的记录的记录的记录 select top 5 * from (select top 15 * from table order by id asc) tabl e_别名 order by id desc 三三三三、、、、技巧技巧技巧技巧 6 1111、、、、1=11=11=11=1，，，，1=21=21=21=2 的使用的使用的使用的使用，，，，在在在在 SQLSQLSQLSQL 语句组合时用的较多语句组合时用的较多语句组合时用的较多语句组合时用的较多 “where 1=1” “where 1=1” “where 1=1” “where 1=1” 是表示选择全部是表示选择全部是表示选择全部是表示选择全部 “where 1=2” “where 1=2” “where 1=2” “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 + '] wherset @strSQL = 'select count(*) as Total from [' + @tblName + '] wherset @strSQL = 'select count(*) as Total from [' + @tblName + '] wherset @strSQL = 'select count(*) as Total from [' + @tblName + '] wher e 1=1 e 1=1 e 1=1 e 1=1 安定安定安定安定 '+ @strWhere '+ @strWhere '+ @strWhere '+ @strWhere 2222、、、、收缩数据库收缩数据库收缩数据库收缩数据库 --重建索引 DBCC REINDEX DBCC INDEXDEFRAG --收缩数据和日志 DBCC SHRINKDB DBCC SHRINKFILE 3333、、、、压缩数据库压缩数据库压缩数据库压缩数据库 dbcc shrinkdatabase(dbname) 4444、、、、转移数据库给新用户以已存在用户权限转移数据库给新用户以已存在用户权限转移数据库给新用户以已存在用户权限转移数据库给新用户以已存在用户权限 exec sp_change_users_login 'update_one','newname','oldname' go 5555、、、、检查备份集检查备份集检查备份集检查备份集 RESTORE VERIFYONLY from disk='E:\dvbbs.bak' 6666、、、、修复数据库修复数据库修复数据库修复数据库 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 7777、、、、日志清除日志清除日志清除日志清除 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 = @Logical FileName) 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 8 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 8888、、、、说明说明说明说明：：：：更改某个表更改某个表更改某个表更改某个表 exec sp_changeobjectowner 'tablename','dbo' 9999、、、、存储更改全部表存储更改全部表存储更改全部表存储更改全部表 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 9 FETCH NEXT FROM curObject INTO @Name, @Owner END close curObject deallocate curObject GO 10101010、、、、SQL SERVERSQL SERVERSQL SERVERSQL SERVER 中直接循环写入数据中直接循环写入数据中直接循环写入数据中直接循环写入数据 declare @i int set @i=1 while @i<30 begin insert into test (userid) values(@i) set @i=@i+1 end 案例案例案例案例：：：： 有如下表有如下表有如下表有如下表，，，，要求就裱中所有沒有及格的成績要求就裱中所有沒有及格的成績要求就裱中所有沒有及格的成績要求就裱中所有沒有及格的成績，，，，在每次增長在每次增長在每次增長在每次增長 0.10.10.10.1 的基礎上的基礎上的基礎上的基礎上，，，，使他們剛好及格使他們剛好及格使他們剛好及格使他們剛好及格:::: Name score Zhangshan 80 Lishi 59 Wangwu 50 Songquan 69 while((select while((select while((select while((select minminminmin (score) from tb_table)<(score) from tb_table)<(score) from tb_table)<(score) from tb_table)< 60606060 )))) beginbeginbeginbegin update tb_table set score update tb_table set score update tb_table set score update tb_table set score ====score*score*score*score* 1.011.011.011.01 where scorewhere scorewhere scorewhere score <60<60<60<60 ifififif (select (select (select (select minminminmin (score) from tb_table)(score) from tb_table)(score) from tb_table)(score) from tb_table) >60>60>60>60 break break break break else else else else continue continue continue continue endendendend 数据开发数据开发数据开发数据开发----经典经典经典经典 10 1.1.1.1.按姓氏笔画排序按姓氏笔画排序按姓氏笔画排序按姓氏笔画排序:::: Select * From TableName Order By CustomerName Collate Chinese_PRC_Str oke_ci_as //从少到多 2.2.2.2.数据库加密数据库加密数据库加密数据库加密:::: select encrypt('原始密码') select pwdencrypt('原始密码') select pwdcompare('原始密码','加密后密码') = 1--相同；否则不相同 encr ypt('原始密码') select pwdencrypt('原始密码') select pwdcompare('原始密码','加密后密码') = 1--相同；否则不相同 3.3.3.3.取回表中字段取回表中字段取回表中字段取回表中字段:::: declare @list varchar(1000), @sql nvarchar(1000) select @list=@list+ ',' +b.name from sysobjects a,syscolumns b where a. id=b.id and a.name= '表 A' set @sql= 'select ' +right(@list,len(@list)-1)+ ' from 表 A' exec (@sql) 4.4.4.4.查看硬盘分区查看硬盘分区查看硬盘分区查看硬盘分区:::: EXEC master..xp_fixeddrivesEXEC master..xp_fixeddrivesEXEC master..xp_fixeddrivesEXEC master..xp_fixeddrives 5.5.5.5.比较比较比较比较 A,BA,BA,BA,B 表是否相等表是否相等表是否相等表是否相等:::: if (select checksum_agg(binary_checksum(*)) from A) = (select checksum_agg(binary_checksum(*)) from B) print '相等' else print '不相等' 6.6.6.6.杀掉所有的事件探察器进程杀掉所有的事件探察器进程杀掉所有的事件探察器进程杀掉所有的事件探察器进程:::: DECLARE hcforeach CURSOR GLOBAL FOR SELECT 'kill '+RTRIM(spid) FROM m aster.dbo.sysprocesses WHERE program_name IN('SQL profiler',N'SQL 事件探查器') EXEC sp_msforeach_worker '? ' 7. 记录搜索记录搜索记录搜索记录搜索: 开头到开头到开头到开头到 N 条记录条记录条记录条记录 Select Top N * From 表 ------------------------------- N 到到到到 M 条记录条记录条记录条记录(要有主索引要有主索引要有主索引要有主索引 ID) Select Top M-N * From 表 Where ID in (Select Top M ID From 表) Order by ID Desc 11 ---------------------------------- N 到结尾记录到结尾记录到结尾记录到结尾记录 Select Top N * From 表 Order by ID Desc 案例案例案例案例 例如例如例如例如 1111：：：：一张表有一万多条记录一张表有一万多条记录一张表有一万多条记录一张表有一万多条记录，，，，表的第一个字段表的第一个字段表的第一个字段表的第一个字段 RecID RecID RecID RecID 是自增长字段是自增长字段是自增长字段是自增长字段，，，， 写一个写一个写一个写一个 SQLSQLSQLSQL 语句语句语句语句，，，， 找出表的第找出表的第找出表的第找出表的第 31313131 到到到到第第第第 40404040 个记录个记录个记录个记录。。。。 select top 10 recid from A where recid not in(select top 30 recid from A) 分析：如果这样写会产生某些问题，如果 recid 在表中存在逻辑索引。 select top 10 recid from A where……是从索引中查找，而后面的 select top 30 recid from A 则在数据表中查找，这样由于索引中的顺序有可能和数据表中的不一致，这样就导 致查询到的不是本来的欲得到的数据。 解决方案解决方案解决方案解决方案 1111，，，， 用用用用 orderorderorderorder by by by by select top 30 recid from A order by ricid 如果如果如果如果该字段不是自该字段不是自该字段不是自该字段不是自 增长增长增长增长，，，，就会出现问题就会出现问题就会出现问题就会出现问题 2，，，， 在那个子查询中也加条件在那个子查询中也加条件在那个子查询中也加条件在那个子查询中也加条件：：：：select top 30 recid from A where recid>-1 例例例例 2：：：：查询表中的最后以条记录查询表中的最后以条记录查询表中的最后以条记录查询表中的最后以条记录，，，，并不知道这个表共有多少数据并不知道这个表共有多少数据并不知道这个表共有多少数据并不知道这个表共有多少数据,以及表结构以及表结构以及表结构以及表结构。。。。 set @s = 'select top 1 * from T where pid not in (select top ' + str (@count-1) + ' pid from T)' print @s exec sp_executesql @s 9999：：：：获取当前数据库中的所有用户表获取当前数据库中的所有用户表获取当前数据库中的所有用户表获取当前数据库中的所有用户表 select Name from sysobjects where xtype='u' and status>=0 10101010：：：：获取某一个表的所有字段获取某一个表的所有字段获取某一个表的所有字段获取某一个表的所有字段 select name from syscolumns where id= object_id ('表名') select name from syscolumns where id in (select id from sysobjects wh ere type = 'u' and name = '表名') 两种方式的效果相同 11111111：：：：查看与某一个表相关的视图查看与某一个表相关的视图查看与某一个表相关的视图查看与某一个表相关的视图、、、、存储过程存储过程存储过程存储过程、、、、函数函数函数函数 select a.* from sysobjects a, syscomments b where a.id = b.id and b.t ext like '%表名%' 12121212：：：：查看当前数据库中所有存储过程查看当前数据库中所有存储过程查看当前数据库中所有存储过程查看当前数据库中所有存储过程 select name as 存储过程名称 from sysobjects where xtype='P' 13131313：：：：查询用户创建的所有数据库查询用户创建的所有数据库查询用户创建的所有数据库查询用户创建的所有数据库 select * from master.. sysdatabases D where sid not in(select sid from master.. syslogins where name='sa') 或者 select dbid, name AS DB_NAME from master..s ysdatabases where sid <> 0 x01 12 14141414：：：：查询某一个表的字段和数据类型查询某一个表的字段和数据类型查询某一个表的字段和数据类型查询某一个表的字段和数据类型 select column_name,data_type from information_schema.columns where table_name = '表名' 15151515：：：：不同服务器数据库之间的数据操作不同服务器数据库之间的数据操作不同服务器数据库之间的数据操作不同服务器数据库之间的数据操作 --------创建链接服务器创建链接服务器创建链接服务器创建链接服务器 exec sp_addlinkedserver 'ITSV ', ' ', 'SQLOLEDB ', '远程服务器名或 ip 地址 ' exec sp_addlinkedsrvlogin 'ITSV ', 'false ',null, '用户名 ', '密码 ' --查询示例 select * from ITSV.数据库名.dbo.表名 --导入示例 select * into 表 from ITSV.数据库名.dbo.表名 --------以后不再使用时删除链接服务器以后不再使用时删除链接服务器以后不再使用时删除链接服务器以后不再使用时删除链接服务器 exec sp_dropserver 'ITSV ', 'droplogins ' --------连接远程连接远程连接远程连接远程////局域网数据局域网数据局域网数据局域网数据(openrowset/openquery/opendatasource) --1、openrowset --查询示例 select * from openrowset( 'SQLOLEDB ', 'sql 服务器名 '; '用户名 '; '密码 ',数据 库名.dbo.表名) --生成本地表 select * into 表 from openrowset( 'SQLOLEDB ', 'sql 服务器名 '; '用户名 '; '密 码 ',数据库名.dbo.表名) --把本地表导入远程表 insert openrowset( 'SQLOLEDB ', 'sql 服务器名 '; '用户名 '; '密码 ',数据库名.db o.表名) 13 select *from 本地表 --更新本地表 update b set b.列 A=a.列 A from openrowset( 'SQLOLEDB ', 'sql 服务器名 '; '用户名 '; '密码 ',数据库名.dbo. 表名)as a inner join 本地表 b on a.column1=b.column1 --openquery 用法需要创建一个连接 --首先创建一个连接创建链接服务器 exec sp_addlinkedserver 'ITSV ', ' ', 'SQLOLEDB ', '远程服务器名或 ip 地址 ' --查询 select * FROM openquery(ITSV, 'SELECT * FROM 数据库.dbo.表名 ') --把本地表导入远程表 insert openquery(ITSV, 'SELECT * FROM 数据库.dbo.表名 ') select * from 本地表 --更新本地表 update b set b.列 B=a.列 B FROM openquery(ITSV, 'SELECT * FROM 数据库.dbo.表名 ') as a inner join 本地表 b on a.列 A=b.列 A --3、opendatasource/openrowset SELECT * 14 FROM opendatasource( 'SQLOLEDB ', 'Data Source=ip/ServerName;User ID=登陆名; Password=密码 ' ).test.dbo.roy_ta --把本地表导入远程表 insert opendatasource( 'SQLOLEDB ', 'Data Source=ip/ServerName;User ID=登陆名; Password=密码 ').数据库.dbo.表名 select * from 本地表 SQL Server 基本函数 SQL Server 基本函数 1.1.1.1.字符串字符串字符串字符串函数函数函数函数 长度与分析用 1,1,1,1,datalengthdatalengthdatalengthdatalength (Char_expr) 返回字符串包含字符数,但不包含后面的空格 2,2,2,2,substringsubstringsubstringsubstring (expression,start,length) 取子串，字符串的下标是从“1”， start 为起始位置，length 为字符串长度，实际应用中以 len(expression) 取得 其长度 3,3,3,3,rightrightrightright (char_expr,int_expr) 返回字符串右边第 int_expr 个字符，还用 lelelele ftftftft 于之相反 4,4,4,4,isnullisnullisnullisnull (((( check ____expression ,,,, replacement_value ))))如果 check_expressio n 為空，則返回 replacement_value 的值，不為空，就返回 check_expression 字符操作类 5,5,5,5,SSSSp_addtypep_addtypep_addtypep_addtype 自定義數據類型 例如：EXEC sp_addtype birthday, datetime, 'NULL' 6,6,6,6,set nocount {set nocount {set nocount {set nocount {on|offon|offon|offon|off}}}} 使返回的结果中不包含有关受 Transact-SQL 语句影响的行数的信息。如果存储 过程中包含的一些语句并不返回许多实际的数据，则该设置由于大量减少了网络 流量，因此可显著提高性能。SET NOCOUNT 设置是在执行或运行时设置，而不是 在分析时设置。 SET NOCOUNT 为 ON 时，不返回计数（表示受 Transact-SQL 语句影响的行数）。 SET NOCOUNT 为 OFF 时，返回计数 常识常识常识常识 15 在在在在 SQLSQLSQLSQL 查询中查询中查询中查询中：：：：fromfromfromfrom 后最多可以跟多少张表或视图：256256256256 在在在在 SQLSQLSQLSQL 语句中出现语句中出现语句中出现语句中出现 Order byOrder byOrder byOrder by ,,,,查询时查询时查询时查询时，，，，先排序先排序先排序先排序，，，，后取后取后取后取 在在在在 SQLSQLSQLSQL 中中中中，，，，一个字段的最大容量是一个字段的最大容量是一个字段的最大容量是一个字段的最大容量是 8000800080008000 ，，，，而对于而对于而对于而对于 nvarchar(4000)nvarchar(4000)nvarchar(4000)nvarchar(4000) ,,,,由于由于由于由于 nvarcharnvarcharnvarcharnvarchar 是是是是 UnicodeUnicodeUnicodeUnicode 码码码码。。。。 SQLSerSQLSerSQLSerSQLServer2000ver2000ver2000ver2000 同步复制技术实现步骤同步复制技术实现步骤同步复制技术实现步骤同步复制技术实现步骤 一、 预备工作 1.发布服务器,订阅服务器都创建一个同名的 windows 用户,并设置相同的密码,做为发布快 照文件夹的有效访问用户 --管理工具 --计算机管理 --用户和组 --右键用户 --新建用户 --建立一个隶属于 administrator 组的登陆 windows 的用户（SynUser） 2.在发布服务器上,新建一个共享目录,做为发布的快照文件的存放目录,操作: 我的电脑--D:\ 新建一个目录,名为: PUB --右键这个新建的目录 --属性--共享 --选择"共享该文件夹" --通过"权限"按纽来设置具体的用户权限,保证第一步中创建的用户(SynUser) 具有对该文 件夹的所有权限 --确定 3.设置 SQL 代理(SQLSERVERAGENT)服务的启动用户(发布/订阅服务器均做此设置) 开始--程序--管理工具--服务 --右键 SQLSERVERAGENT --属性--登陆--选择"此账户" --输入或者选择第一步中创建的 windows 登录用户名（SynUser） --"密码"中输入该用户的密码 4.设置 SQL Server 身份验证模式,解决连接时的权限问题(发布/订阅服务器均做此设置) 企业管理器 --右键 SQL 实例--属性 --安全性--身份验证 --选择"SQL Server 和 Windows" --确定 5.在发布服务器和订阅服务器上互相注册 企业管理器 --右键 SQL Server 组 --新建 SQL Server 注册... --下一步--可用的服务器中,输入你要注册的远程服务器名 --添加 --下一步--连接使用,选择第二个"SQL Server 身份验证" 16 --下一步--输入用户名和密码（SynUser） --下一步--选择 SQL Server 组,也可以创建一个新组 --下一步--完成 6.对于只能用 IP,不能用计算机名的,为其注册服务器别名（此步在实施中没用到） (在连接端配置,比如,在订阅服务器上配置的话,服务器名称中输入的是发布服务器的 IP) 开始--程序--Microsoft SQL Server--客户端网络实用工具 --别名--添加 --网络库选择"tcp/ip"--服务器别名输入 SQL 服务器名 --连接参数--服务器名称中输入 SQL 服务器 ip 地址 --如果你修改了 SQL 的端口,取消选择"动态决定端口",并输入对应的端口号 二、 正式配置 1、配置发布服务器 打开企业管理器，在发布服务器（B、C、D）上执行以下步骤: (1) 从[工具]下拉菜单的[复制]子菜单中选择[配置发布、订阅服务器和分发]出现配置发布 和分发向导 (2) [下一步] 选择分发服务器 可以选择把发布服务器自己作为分发服务器或者其他 sql 的服务器（选择自己） (3) [下一步] 设置快照文件夹 采用默认\\servername\Pub (4) [下一步] 自定义配置 可以选择:是,让我设置分发数据库属性启用发布服务器或设置发布设置 否,使用下列默认设置（推荐） (5) [下一步] 设置分发数据库名称和位置 采用默认值 (6) [下一步] 启用发布服务器 选择作为发布的服务器 (7) [下一步] 选择需要发布的数据库和发布类型 (8) [下一步] 选择注册订阅服务器 (9) [下一步] 完成配置 2、创建出版物 发布服务器 B、C、D 上 (1)从[工具]菜单的[复制]子菜单中选择[创建和管理发布]命令 (2)选择要创建出版物的数据库，然后单击[创建发布] (3)在[创建发布向导]的提示对话框中单击[下一步]系统就会弹出一个对话框。对话框上的 内容是复制的三个类型。我们现在选第一个也就是默认的快照发布(其他两个大家可以去看 看帮助) (4)单击[下一步]系统要求指定可以订阅该发布的数据库服务器类型, SQLSERVER 允许在不同的数据库如 orACLE 或 ACCESS 之间进行数据复制。 但是在这里我们选择运行"SQL SERVER 2000"的数据库服务器 (5)单击[下一步]系统就弹出一个定义文章的对话框也就是选择要出版的表 注意: 如果前面选择了事务发布 则再这一步中只能选择带有主键的表 (6)选择发布名称和描述 (7)自定义发布属性 向导提供的选择: 是 我将自定义数据筛选,启用匿名订阅和或其他自定义属性 否 根据指定方式创建发布 （建议采用自定义的方式） (8)[下一步] 选择筛选发布的方式 17 (9)[下一步] 可以选择是否允许匿名订阅 1)如果选择署名订阅,则需要在发布服务器上添加订阅服务器 方法: [工具]->[复制]->[配置发布、订阅服务器和分发的属性]->[订阅服务器] 中添加 否则在订阅服务器上请求订阅时会出现的提示:改发布不允许匿名订阅 如果仍然需要匿名订阅则用以下解决办法 [企业管理器]->[复制]->[发布内容]->[属性]->[订阅选项] 选择允许匿名请求订阅 2)如果选择匿名订阅,则配置订阅服务器时不会出现以上提示 (10)[下一步] 设置快照 代理程序调度 (11)[下一步] 完成配置 当完成出版物的创建后创建出版物的数据库也就变成了一个共享数据库 有数据 srv1.库名..author 有字段:id,name,phone, srv2.库名..author 有字段:id,name,telphone,adress 要求： srv1.库名..author 增加记录则 srv1.库名..author 记录增加 srv1.库名..author 的 phone 字段更新，则 srv1.库名..author 对应字段 telphone 更新 --*/ --大致的处理步骤 --1.在 srv1 上创建连接服务器,以便在 srv1 中操作 srv2,实现同步 exec sp_addlinkedserver 'srv2','','SQLOLEDB','srv2 的 sql 实例名或 ip' exec sp_addlinkedsrvlogin 'srv2','false',null,'用户名','密码' go --2.在 srv1 和 srv2 这两台电脑中,启动 msdtc(分布式事务处理服务),并且设置为自动 启动 。我的电脑--控制面板--管理工具--服务--右键 Distributed Transaction Coordinator-- 属性--启动--并将启动类型设置为自动启动 go --然后创建一个作业定时调用上面的同步处理存储过程就行了 企业管理器 --管理 --SQL Server 代理 --右键作业 --新建作业 --"常规"项中输入作业名称 --"步骤"项 --新建 --"步骤名"中输入步骤名 --"类型"中选择"Transact-SQL 脚本(TSQL)" --"数据库"选择执行命令的数据库 18 --"命令"中输入要执行的语句: exec p_process --确定 --"调度"项 --新建调度 --"名称"中输入调度名称 --"调度类型"中选择你的作业执行安排 --如果选择"反复出现" --点"更改"来设置你的时间安排 然后将 SQL Agent 服务启动,并设置为自动启动,否则你的作业不会被执行 设置方法: 我的电脑--控制面板--管理工具--服务--右键 SQLSERVERAGENT--属性--启动类型--选择" 自动启动"--确定. --3.实现同步处理的方法 2,定时同步 --在 srv1 中创建如下的同步处理存储过程 create proc p_process as --更新修改过的数据 update b set name=i.name,telphone=i.telphone from srv2.库名.dbo.author b,author i where b.id=i.id and (b.name <> i.name or b.telphone <> i.telphone) --插入新增的数据 insert srv2.库名.dbo.author(id,name,telphone) select id,name,telphone from author i where not exists( select * from srv2.库名.dbo.author where id=i.id) --删除已经删除的数据(如果需要的话) delete b from srv2.库名.dbo.author b where not exists( select * from author where id=b.id) go 19 SQL 基本语句基本语句基本语句基本语句 来自：SQL 编程技巧 掌握 SQL 四条最基本的数据操作语句：Insert ，Select ，Update 和 Delete 。 练掌握 SQL 是数据库用户的宝贵财 富。在本文中，我们将引导你掌握四条最基本的数据操作语句 —SQL 的核心功能—来依次介绍比较操作符、选择断言以及三值逻辑。当你完成这些学习后，显然你已经 开始算是精通 SQL 了。 在我们开始之前，先使用 CREATE TABLE 语句来创建一个表（如图 1 所示）。DDL 语句对数据库对 象如表、列和视进行定义。它们并不对表中的行进行处理，这是因为 DDL 语句并不处理数据库中实际的数 据。这些工作由另一类 SQL 语句—数据操作语言（DML）语句进行处理。 SQL 中有四种基本的 DML 操作：INSERT，SELECT，UPDATE 和 DELETE。由于这是大多数 SQL 用户经常用到的，我们有必要在此对它们进行一一说明。在图 1 中我们给出了一个名为 EMPLOYEES 的表。 其中的每一行对应一个特定的雇员记录。请熟悉这张表，我们在后面的例子中将要用到它。 INSERT 语句 用户可以用 INSERT 语句将一行记录插入到指定的一个表中。例如，要将雇员 John Smith 的记录插入 到本例的表中，可以使用如下语句： INSERT INTO EMPLOYEES VALUES ('Smith','John','1980-06-10', 'Los Angles',16,45000); 通过这样的 INSERT 语句，系统将试着将这些值填入到相应的列中。这些列按照我们创建表时定义的 顺序排列。在本例中，第一个值“Smith” 将填到第一个列 LAST_NAME 中；第二个值“John” 将填到第二列 FIRST_NAME 中……以此类推。 我们说过系统会“试着”将值填入，除了执行规则之外它还要进行类型检查。如果类型不符（如将一个 字符串填入到类型为数字的列中），系统将拒绝这一次操作并返回一个错误信息。 如果 SQL 拒绝了你所填入的一列值，语句中其他各列的值也不会填入。这是因为 SQL 提供对事务的 支持。一次事务将数据库从一种一致性转移到另一种一致性。如果事务的某一部分失败，则整个事务都会 失败，系统将会被恢复（或称之为回退）到此事务之前的状态。 回到原来的 INSERT 的例子，请注意所有的整形十进制数都不需要用单引号引起来，而字符串和日期 类型的值都要用单引号来区别。为了增加可读性而在数字间插入逗号将会引起错误。记住，在 SQL 中逗号 是元素的分隔符。 20 同样要注意输入文字值时要使用单引号。双引号用来封装限界标识符。 对于日期类型，我们必须使用 SQL 标准日期格式（yyyy-mm-dd ），但是在系统中可以进行定义，以接 受其他的格式。当然，2000 年临近，请你最好还是使用四位来表示年份。 既然你已经理解了 INSERT 语句是怎样工作的了，让我们转到 EMPLOYEES 表中的其他部分： INSERT INTO EMPLOYEES VALUES ('Bunyan','Paul','1970-07-04', 'Boston',12,70000); INSERT INTO EMPLOYEES VALUES ('John','Adams','1992-01-21', 'Boston',20,100000); INSERT INTO EMPLOYEES VALUES ('Smith','Pocahontas','1976-04-06', 'Los Angles',12,100000); INSERT INTO EMPLOYEES VALUES ('Smith','Bessie','1940-05-02', 'Boston',5,200000); INSERT INTO EMPLOYEES VALUES ('Jones','Davy','1970-10-10', 'Boston',8,45000); INSERT INTO EMPLOYEES VALUES ('Jones','Indiana','1992-02-01', 'Chicago',NULL,NULL); 在最后一项中，我们不知道 Jones 先生的工薪级别和年薪，所以我们输入 NULL（不要引号）。NULL 21 是 SQL 中的一种特殊情况，我们以后将进行详细的讨论。现在我们只需认为 NULL 表示一种未知的值。 有时，像我们刚才所讨论的情况，我们可能希望对某一些而不是全部的列进行赋值。除了对要省略的 列输入 NULL 外，还可以采用另外一种 INSERT 语句，如下： INSERT INTO EMPLOYEES( FIRST_NAME, LAST_NAME, HIRE_DATE, BRANCH_OFFICE) VALUE( 'Indiana','Jones', '1992-02-01','Indianapolis'); 这样，我们先在表名之后列出一系列列名。未列出的列中将自动填入缺省值，如果没有设置缺省值则 填入 NULL。请注意我们改变了列的顺序，而值的顺序要对应新的列的顺序。如果该语句中省略了 FIRST_NAME 和 LAST_NAME 项（这两项规定不能为空），SQL 操作将失败。 让我们来看一看上述 INSERT 语句的语法图： INSERT INTO table [(column { ,column})] VALUES (columnvalue [{,columnvalue}]); 和前一篇文章中一样，我们用方括号来表示可选项，大括号表示可以重复任意次数的项（不能在实际 的 SQL 语句中使用这些特殊字符）。VALUE 子句和可选的列名列表中必须使用圆括号。 SELECT 语句 SELECT 语句可以从一个或多个表中选取特定的行和列。因为查询和检索数据是数据库管理中最重要 的功能，所以 SELECT 语句在 SQL 中是工作量最大的部分。实际上，仅仅是访问数据库来分析数据并生成 报表的人可以对其他 SQL 语句一窍不通。 SELECT 语句的结果通常是生成另外一个表。在执行过程中系统根据用户的标准从数据库中选出匹配 的行和列，并将结果放到临时的表中。在直接 SQL（direct SQL ）中，它将结果显示在终端的显示屏上，或 者将结果送到打印机或文件中。也可以结合其他 SQL 语句来将结果放到一个已知名称的表中。 22 SELECT 语句功能强大。虽然表面上看来它只用来完成本文第一部分中提到的关系代数运算“选择”（或 称“限制”），但实际上它也可以完成其他两种关系运算—“投影”和“连接”，SELECT 语句还可以完成聚合计 算并对数据进行排序。 SELECT 语句最简单的语法如下： SELECT columns FROM tables; 当我们以这种形式执行一条 SELECT 语句时，系统返回由所选择的列以及用户选择的表中所有指定的 行组成的一个结果表。这就是实现关系投影运算的一个形式。 让我们看一下使用图 1 中 EMPLOYEES 表的一些例子（这个表是我们以后所有 SELECT 语句实例都要 使用的。而我们在图 2 和图 3 中给出了查询的实际结果。我们将在其他的例子中使用这些结果）。 假设你想查看雇员工作部门的列表。那下面就是你所需要编写的 SQL 查询： SELECT BRANCH_OFFICE FROM EMPLOYEES; 以上 SELECT 语句的执行将产生如图 2 中表 2 所示的结果。 由于我们在 SELECT 语句中只指定了一个列，所以我们的结果表中也只有一个列。注意结果表中具有 重复的行，这是因为有多个雇员在同一部门工作（记住 SQL 从所选的所有行中将值返回）。要消除结果中 的重复行，只要在 SELECT 语句中加上 DISTINCT 子句： SELECT DISTINCT BRANCH_OFFICE FROM EMPLOYEES; 这次查询的结果如表 3 所示。 现在已经消除了重复的行，但结果并不是按照顺序排列的。如果你希望以字母表顺序将结果列出又该 怎么做呢？只要使用 ORDER BY 子句就可以按照升序或降序来排列结果： SELECT DISTINCT BRANCH_OFFICE FROM EMPLOYEES ORDER BY BRANCH_OFFICE ASC; 这一查询的结果如表 4 所示。请注意在 ORDER BY 之后是如何放置列名 BRANCH _OFFICE 的，这就 是我们想要对其进行排序的列。为什么即使是结果表中只有一个列时我们也必须指出列名呢？这是因为我 们还能够按照表中其他列进行排序，即使它们并不显示出来。列名 BRANCH_ OFFICE 之后的关键字 ASC 表示按照升序排列。如果你希望以降序排列，那么可以用关键字 DESC。 23 同样我们应该指出 ORDER BY 子句只将临时表中的结果进行排序；并不影响原来的表。 假设我们希望得到按部门排序并从工资最高的雇员到工资最低的雇员排列的列表。除了工资括号中的 内容，我们还希望看到按照聘用时间从最近聘用的雇员开始列出的列表。以下是你将要用到的语句： SELECT BRANCH_OFFICE,FIRST_NAME, LAST_NAME,SALARY,HIRE_DATE FROM EMPLOYEES ORDER BY SALARY DESC, HIRE_DATE DESC; 这里我们进行了多列的选择和排序。排序的优先级由语句中的列名顺序所决定。SQL 将先对列出的第 一个列进行排序。如果在第一个列中出现了重复的行时，这些行将被按照第二列进行排序，如果在第二列 中又出现了重复的行时，这些行又将被按照第三列进行排序……如此类推。这次查询的结果如表 5 所示。 将一个很长的表中的所有列名写出来是一件相当麻烦的事，所以 SQL 允许在选择表中所有的列时使用 *号： SELECT * FROM EMPLOYEES; 这次查询返回整个 EMPLOYEES 表，如表 1 所示。 下面我们对开始时给出的 SELECT 语句的语法进行一下更新（竖直线表示一个可选项，允许在其中 选择一项。）： SELECT [DISTINCT] (column [{, columns}])| * FROM table [ {, table}] [ORDER BY column [ASC] | DESC [ {, column [ASC] | DESC }]]; 定义选择标准 在我们目前所介绍的 SELECT 语句中，我们对结果表中的列作出了选择但返回的是表中所有的行。让 我们看一下如何对 SELECT 语句进行限制使得它只返回希望得到的行： 24 SELECT columns FROM tables [WHERE predicates]; WHERE 子句对条件进行了设置，只有满足条件的行才被包括到结果表中。这些条件由断言（predicate ） 进行指定（断言指出了关于某件事情的一种可能的事实）。如果该断言对于某个给定的行成立，该行将被 包括到结果表中，否则该行被忽略。在 SQL 语句中断言通常通过比较来表示。例如，假如你需要查询所有 姓为 Jones 的职员，则可以使用以下 SELECT 语句： SELECT * FROM EMPLOYEES WHERE LAST_NAME = 'Jones'; LAST_NAME = 'Jones' 部分就是断言。在执行该语句时，SQL 将每一行的 LAST_NAME 列与“Jones” 进 行比较。如果某一职员的姓为“Jones” ，即断言成立，该职员的信息将被包括到结果表中（见表 6）。 使用最多的六种比较 我们上例中的断言包括一种基于“等值”的比较（LAST_NAME = 'Jones' ），但是 SQL 断言还可以包含 其他几种类型的比较。其中最常用的为： 等于 = 不等于 <> 小于 < 大于 > 小于或等于 <= 大于或等于 >= 下面给出了不是基于等值比较的一个例子： SELECT * FROM EMPLOYEES WHERE SALARY > 50000; 这一查询将返回年薪高于$50,000.00 的职员（参见表 7）。 逻辑连接符 有时我们需要定义一条不止一种断言的 SELECT 语句。举例来说，如果你仅仅想查看 Davy Jones 的信 息的话，表 6 中的结果将是不正确的。为了进一步定义一个 WHERE 子句，用户可以使用逻辑连接符 AND， OR 和 NOT。为了只得到职员 Davy Jones 的记录，用户可以输入如下语句： 25 SELECT * FROM EMPLOYEES WHERE LAST_NAME = 'Jones' AND FIRST_NAME = 'Davy'; 在本例中，我们通过逻辑连接符 AND 将两个断言连接起来。只有两个断言都满足时整个表达式才会 满足。如果用户需要定义一个 SELECT 语句来使得当其中任何一项成立就满足条件时，可以使用 OR 连接 符： SELECT * FROM EMPLOYEES WHERE LAST_NAME = 'Jones' OR LAST_NAME = 'Smith'; 有时定义一个断言的最好方法是通过相反的描述来说明。如果你想要查看除了 Boston 办事处的职员以 外的其他所有职员的信息时，你可以进行如下的查询： SELECT * FROM EMPLOYEES WHERE NOT(BRANCH_OFFICE = 'Boston'); 关键字 NOT 后面跟着用圆括号括起来的比较表达式。其结果是对结果取否定。如果某一职员所在部门 的办事处在 Boston ，括号内的表达式返回 true ，但是 NOT 操作符将该值取反，所以该行将不被选中。 断言可以与其他的断言嵌套使用。为了保证它们以正确的顺序进行求值，可以用括号将它们括起来： SELECT * FROM EMPLOYEES WHERE (LAST_NAME = 'Jones' AND FIRST_NAME = 'Indiana') OR (LAST_NAME = 'Smith' AND FIRST_NAME = 'Bessie'); SQL 沿用数学上标准的表达式求值的约定—圆括号内的表达式将最先进行求值，其他表达式将从左到 右进行求值。 以上对逻辑连接符进行了说明，在对下面的内容进行说明之前，我们再一次对 SELECT 语句的语法进 行更新： SELECT [DISTINCT] (column [{, column } ] )| * 26 FROM table [ { , table} ] [ORDER BY column [ASC] | [DESC [{ , column [ASC] | [DESC } ] ] WHERE predicate [ { logical-connector predicate } ]; NULL 和三值逻辑 在 SQL 中 NULL 是一个复杂的话题，关于 NULL 的详细描述更适合于在 SQL 的高级教程而不是现在 的入门教程中进行介绍。但由于 NULL 需要进行特殊处理，并且你也很可能会遇到它，所以我们还是简略 地进行一下说明。 首先，在断言中进行 NULL 判断时需要特殊的语法。例如，如果用户需要显示所有年薪未知的职员的 全部信息，用户可以使用如下 SELECT 语句： SELECT * FROM EMPLOYEES WHERE SALARY IS NULL; 相反，如果用户需要所有已知年薪数据的职员的信息，你可以使用以下语句： SELECT * FROM EMPLOYEES WHERE SALARY IS NOT NULL; 请注意我们在列名之后使用了关键字 IS NULL 或 IS NOT NULL ，而不是标准的比较形式：COLUMN = NULL、COLUMN <> NULL 或是逻辑操作符 NOT（NULL）。 这种形式相当简单。但当你不明确地测试 NULL（而它们确实存在）时，事情会变得很混乱。 例如，回过头来看我们图 1 中的 EM-PLOYEES 表，可以看到 Indiana Jones 的工薪等级或年薪值都是 未知的。这两个列都包含 NULL。可以想象运行如下的查询： SELECT * FROM EMPLOYEES WHERE GRADE <= SALARY; 此时，Indiana Jones 应该出现在结果表中。因为 NULL 都是相等的，所以可以想象它们是能够通过 GRADE 小于等于 SALARY 的检查的。这其实是一个毫无疑义的查询，但是并没有关系。SQL 允许进行这 样的比较，只要两个列都是数字类型的。然而，Indiana Jones 并没有出现在查询的结果中，为什么？ 27 正如我们早先提到过的，NULL 表示未知的值（而不是象某些人所想象的那样表示一个为 NULL 的值）。 对于 SQL 来说意味着这个值是未知的，而只要这个值为未知，就不能将其与其他值比较（即使其他值也是 NULL）。所以 SQL 允许除了在 true 和 false 之外还有第三种类型的真值，称之为“非确定”（unknown ）值。 如果比较的两边都是 NULL，整个断言就被认为是非确定的。将一个非确定断言取反或使用 AND 或 OR 与其他断言进行合并之后，其结果仍是非确定的。由于结果表中只包括断言值为“真”的行，所以 NULL 不可能满足该检查。从而需要使用特殊的操作符 IS NULL 和 IS NOT NULL 。 UPDATE 语句 UPDATE 语句允许用户在已知的表中对现有的行进行修改。 例如，我们刚刚发现 Indiana Jones 的等级为 16 ，工资为$40,000.00 ，我们可以通过下面的 SQL 语句对 数据库进行更新（并清除那些烦人的 NULL）。 UPDATE EMPLOYEES SET GRADE = 16, SALARY = 40000 WHERE FIRST_NAME = 'Indiana' AND LAST_NAME = 'Jones'; 上面的例子说明了一个单行更新，但是 UPDATE 语句可以对多行进行操作。满足 WHERE 条件的所有 行都将被更新。如果，你想让 Boston 办事处中的所有职员搬到 New York ，你可以使用如下语句： UPDATE EMPLOYEES SET BRANCH_OFFICE = 'New York' WHERE BRANCH_OFFICE = 'Boston'; 如果忽略 WHERE 子句,表中所有行中的部门值都将被更新为'New York' 。 UPDATE 语句的语法流图如下面所示： UPDATE table SET column = value [{, column = value}] [ WHERE predicate [ { logical-connector predicate}]]; DELETE 语句 28 DELETE 语句用来删除已知表中的行。如同 UPDATE 语句中一样，所有满足 WHERE 子句中条件的行 都将被删除。由于 SQL 中没有 UNDO 语句或是“你确认删除吗？”之类的警告，在执行这条语句时千万要 小心。如果决定取消 Los Angeles 办事处并解雇办事处的所有职员，这一卑鄙的工作可以由以下这条语句来 实现： DELETE FROM EMPLOYEES WHERE BRANCH_OFFICE = 'Los Angeles'; 如同 UPDATE 语句中一样，省略 WHERE 子句将使得操作施加到表中所有的行。 DELETE 语句的语法流图如下面所示： DELETE FROM table [WHERE predicate [ { logical-connector predicate} ] ]; 现在我们完成了数据操作语言（DML）的主要语句的介绍。我们并没有对 SQL 能完成的所有功能进行 说明。SQL 还提供了许多的功能，如求平均值、求和以及其他对表中数据的计算，此外 SQL 还能完成从多 个表中进行查询（多表查询，或称之为连接）的工作。这种语言还允许你使用 GRANT 和 REVOKE 命令控 制使用者的数据访问权限。