常用sql语句

 

// 利用pSQL改数据
select  * from eosoperator o where o . userid = 'admincz'  for  update
 wqNXrEJQJ2bfy1uGWiiX0w ==
 Q3qtuxGsI7B5u60nFMu + qA ==
 commit
 dCz7O + t2DE1ox6sKPqNp / w ==   5185024  shengzhonhxin  
 wqNXrEJQJ2bfy1uGWiiX0w ==
 -------------------查询
 
  900207 ------
 
  // 按时间格式化查询
  select  to_char ( OPER_TIME , 'YYYY-MM-DD HH24 :MI:SS ' )  as OPER_TIME , OPER_MANNAME , PROCESSINSTID , ORGNAME , OPER_NAME , OPER_DETAIL  ,  ACTIVITYINSTNAME from pub_busilog_td where processinstid = ' 47930 ' order by OPER_TIME
 
 
  // 多表查询--自连接
  select  a . * , b . orgName from
 ioms_interior_office_record_td a  , eosorg_t_organization b  
  where b . orgid  =  a . record_org_id and a . officedata_id  =  ' 320 '
  
  //  给出每个地区人口最大的国家：
SELECT  region ,  name ,  population FROM bbc x
  WHERE population  >=  ALL
     ( SELECT  population FROM bbc y
        WHERE y . region = x . region )
  
  
  
 yz / 0OSL2YlJUIUl6pi61YA ==  
  
  
-------------------修改：

UPDATE IOMS_INTERIOR_OFFICEDATA_TD   SET  IOMS_INTERIOR_OFFICEDATA_TD . BUSI_ID =    52
COMMIT


// 删除环节
select  * from  tssa . pub_activity_td where LOCAL_ID = ' 0 'and BUSI_ID = ' 41 '
 
DELETE FROM pub_activity_td WHERE  LOCAL_ID = ' 0 'and BUSI_ID = ' 41 '


rollback


// 删除所有没有流程模版的流程实例

select  * from wfprocessinst where processdefid  not  in  ( select  processdefid from wfprocessdefine )


// 查询环节参与人


select  * from wfworkitem where processinstid = ' 200070 ' ;
select  * from wfwiparticipant where workitemid = ' 2054854 '
select  * from wfprocessinst where processinstid = ' 200070 ' ;
select  * from eosorg_t_employee where orgid = 2196


-----------------------------------------------------------------------------------------------------------------
常用标准SQL语句创建表：
creat table 表名  ( id ,  int ,  admin ,  char ( 10 ))

插入纪录：insert into 表名  ( id ,  admin )  values ( 1 ,  '名称' )

删除纪录：delete 表名 where admin = '名称'

修改纪录：update 表名  set  admin = '名称' where id = 1

删除表：drop table 表名
-------------------------------------------------------------------------------------------------------------------
create table IOMS_INTERIOR_ALLOCATE_TD 
(
             ALLOC_ID       NUMBER ( 38 ),         / * 调拨实例ID * /
             ALLOC_TYPE     varchar2 ( 10 ),           / * 调拨原因 0业务需求 1网络优化 2其它 * /
             ALLOC_PURPUSE  varchar2 ( 1000 ),     / * 调拨原因、用途 * /
               DEPT_EXPORT   varchar2 ( 100 ),      / * 调出单位 * /
               DEPT_IMPORT   varchar2 ( 100 ),      / * 调入单位 * /
                 ASSET_NAME     varchar2 ( 100 ),      / * 资产名称 * /
                 ASSET_SPEC     varchar2 ( 100 ),      / * 资产规格 * /
                 ASSET_UNIT     varchar2 ( 100 ),      / * 资产单位 * /
                 ASSET_AMOUNT   NUMBER ( 38 , 0 ),       / * 资产数量 * /
             ASSET_FIXED    varchar2 ( 10 ),           / * 是否固定资产  0  不是 1是  * /
             DEVICE_TYPE    varchar2 ( 500 ),      / * 资产专业类别 * /
                 ORGINAL_WORTH  varchar2 ( 100 ),      / * 资产原值 * /
                 CURRENT_WORTH  varchar2 ( 100 ),      / * 资产现值 * /
                EVAL_WORTH     varchar2 ( 100 ),      / * 资产评估价值 * /
                EVAL_BY        varchar2 ( 1000 ),     / * 资产评估方法 * /
              NOTE         varchar2 ( 1000 ),     / * 备注 * /
                    INTERIOR_ID    NUMBER ( 18 , 0 )   NOT  NULL ,        / * 内部任务单号 * /
                    primary key  ( ALLOC_ID )           
);

-----------------------------------------------------------------------------------------------------------------------
添加、删除、修改使用db . Execute ( Sql ) 命令执行操作 
╔--------------------╗ 
☆ 数据记录筛选 ☆ 
╚--------------------╝ 
注意：单双引号的用法可能有误 ( 没有测式 )  

Sql  =   " Select Distinct 字段名 From 数据表 "  
Distinct函数，查询数据库存表内不重复的记录 

Sql  =   " Select Count(*) From 数据表 where 字段名1>#18:0:0# and 字段名1< #19:00#  "  
count函数 , 查询数库表内有多少条记录，“字段名1”是指同一字段 
例： 
set  rs = conn . execute ( " select count(id) as idnum from news " )  
response . write rs ( " idnum " )  

sql = " select * from 数据表 where 字段名 between 值1 and 值2 "  
Sql = " select * from 数据表 where 字段名 between #2003-8-10# and #2003-8-12# "  
在日期类数值为2003- 8 - 10   19 : 55 : 08  的字段里查找2003- 8 -10至2003- 8 -12的所有记录，而不管是几点几分。 

select  * from tb_name where datetime between  # 2003 - 8 - 10 #  and  # 2003 - 8 - 12 #  
字段里面的数据格式为： 2003 - 8 - 10   19 : 55 : 08 ，通过sql查出2003- 8 -10至2003- 8 -12的所有纪录，而不管是几点几分。 

Sql = " select * from 数据表 where 字段名=字段值 order by 字段名 [desc] "  

Sql = " select * from 数据表 where 字段名 like '%字段值%' order by 字段名 [desc] "  
模糊查询 

Sql = " select top 10 * from 数据表 where 字段名 order by 字段名 [desc] "  
查找数据库中前10记录 

Sql = " select top n * form 数据表 order by newid() "  
随机取出数据库中的若干条记录的方法 
top n，n就是要取出的记录数 

Sql = " select * from 数据表 where 字段名 in ('值1','值2','值3') "  

╔--------------------╗ 
☆ 添加数据记录 ☆ 
╚--------------------╝ 
sql = " insert into 数据表 (字段1,字段2,字段3 …) valuess (值1,值2,值3 …) "  

sql = " insert into 数据表 valuess (值1,值2,值3 …) "  
不指定具体字段名表示将按照数据表中字段的顺序，依次添加 

sql = " insert into 目标数据表 select * from 源数据表 "  
把源数据表的记录添加到目标数据表 

╔--------------------╗ 
☆ 更新数据记录 ☆ 
╚--------------------╝ 
Sql = " update 数据表 set 字段名=字段值 where 条件表达式 "  

Sql = " update 数据表 set 字段1=值1,字段2=值2 …… 字段n=值n where 条件表达式 "  

Sql = " update 数据表 set 字段1=值1,字段2=值2 …… 字段n=值n  "  
没有条件则更新整个数据表中的指定字段值 

╔--------------------╗ 
☆ 删除数据记录 ☆ 
╚--------------------╝ 
Sql = " delete from 数据表 where 条件表达式 "  

Sql = " delete from 数据表 "  
没有条件将删除数据表中所有记录 )  

╔--------------------------╗ 
☆ 数据记录统计函数 ☆ 
╚--------------------------╝ 
AVG ( 字段名 )  得出一个表格栏平均值 
COUNT ( *|字段名 )  对数据行数的统计或对某一栏有值的数据行数统计 
MAX ( 字段名 )  取得一个表格栏最大的值 
MIN ( 字段名 )  取得一个表格栏最小的值 
SUM ( 字段名 )  把数据栏的值相加 

引用以上函数的方法： 
sql = " select sum(字段名) as 别名 from 数据表 where 条件表达式 "  
set  rs = conn . excute ( sql )  
用 rs ( " 别名 " )  获取统的计值，其它函数运用同上。 

╔-----------------------------╗ 
☆ 数据表的建立和删除 ☆ 
╚-----------------------------╝ 
CREATE TABLE 数据表名称 ( 字段1 类型1 ( 长度 ), 字段2 类型2 ( 长度 )  ……  )  
例：CREATE TABLE tab01 ( name varchar ( 50 ), datetime default now ())  
DROP TABLE 数据表名称  ( 永久性删除一个数据表 )  
------------------------------------------------------------------------------------------------------------------------------------------
telnet  192.168 . 20.18  
oracle   devoracle2007  
exp  icdmain / icd @ahfwbz
file = exp_icdmain_csd_yyyymmdd . dmp




失去了跟主机的连接。

C: Documents and Settings Administrator >
C: Documents and Settings Administrator >
C: Documents and Settings Administrator > ftp  192.168 . 20.18
Connected to  192.168 . 20.18 .
220   ( vsFTPd  1.2 . 0 )
User  ( 192.168 . 20.18 : ( none )) : oracle
331  Please specify the password .
Password:
230  Login successful .
ftp >  ls
200  PORT  command  successful .  Consider using PASV .
150  Here comes the directory listing .
070213 . dmp
jdhuang
mstrback_20070308 . dmp
mstrold_20070308 . dmp
oradata
startdb
sxeoms070212_1354 . dmp
sxtsioms . 2007 - 01 - 28 -14_46 . dmp
sxtsioms . 2007 - 02 - 09 -17_24 . dmp
sxtsioms . 2007 - 04 - 25 -01_00 . dmp
sxtsioms070208_1303 . dmp
test . dmp
wwang
yding
226  Directory send OK .
ftp: 收到  249  字节，用时  0 . 00Seconds  249000 . 00Kbytes / sec .
ftp >  get test . dmp d: / test . dmp
200  PORT  command  successful .  Consider using PASV .
150  Opening BINARY  mode  data connection  for  test . dmp  ( 469868544  byte
226  File send OK .
ftp: 收到  469868544  字节，用时  573 . 11Seconds  819 . 86Kbytes / sec .
ftp >  ls
200  PORT  command  successful .  Consider using PASV .
150  Here comes the directory listing .
070213 . dmp
jdhuang
mstrback_20070308 . dmp
mstrold_20070308 . dmp
oradata
startdb
sxeoms070212_1354 . dmp
sxtsioms . 2007 - 01 - 28 -14_46 . dmp
sxtsioms . 2007 - 02 - 09 -17_24 . dmp
sxtsioms . 2007 - 04 - 25 -01_00 . dmp
sxtsioms070208_1303 . dmp
test . dmp
wwang
yding
226  Directory send OK .
ftp: 收到  249  字节，用时  0 . 02Seconds  15 . 56Kbytes / sec .
ftp >

----------------------------------------------------------------------------------------------------------
// 用sql查出树型结构
SELECT  busi_name , LPAD ( concat ( '┣' , busi_name ),   2 *LENGTH ( busi_name )+( LEVEL* 2 ) - ( length ( LPAD ( busi_name ,   2 *LENGTH ( busi_name ), '*' )) -length ( busi_name )), ' ' ), length ( busi_name )
FROM pub_busitype_td
START  WITH busi_id = 0
CONNECT BY PRIOR busi_id = pbusi_id

----------------------------------------------------------------------------------------------------------------------------------------------------
CREATE OR  REPLACE  PROCEDURE  " IOMS_INTERIOR_TO_HISINTERIOR "   / *
  内部任务统一归档存储过程，输入
   运维单号，根据运维单号查询业务类型
   和流程实例号，若有相关错误则抛出
   异常，返回ret
   ret = 1         失败
   ret = 0         成功
   ret is null  没有找到相应的业务类型
   create by gsli* /
  (
 interiorId in          number ,
 ret         out         VARCHAR2
)
IS
varprocessinstId number ;
countNum number ;
busiId VARCHAR2 ( 10 );
reterror EXCEPTION ;
begin
/ *当要移除的运维单数超过一条时触发异常* /
select  count ( processinst_id )  into countNum from ioms_interior_td where interior_id  =  interiorId ;
if  countNum! = 1   then
  raise reterror ;
end   if ;
/ *当查出的业务类型为空是触发异常* /
select  busi_id into busiId from ioms_interior_td where interior_id  =  interiorId ;
if  busiId is null   then
  raise reterror ;
end   if ;
/ *当流程实例号为空时触发异常* /
select  processinst_id into varprocessinstId from ioms_interior_td where interior_id  =  interiorId ;
if  varprocessinstId is null  then
  raise reterror ;
end   if ;
busiId : =   REPLACE ( busiId , ' ' , '' );
/ *工作联系单的数据归档* /
if  busiId  =  ' 81 '  then
  insert into hisioms_interior_td  ( select  * from ioms_interior_td where interior_id = interiorId );
  insert into hispub_busilog_td  ( select  * from pub_busilog_td where processinstid = varprocessinstId );
  delete from ioms_interior_td where interior_id = interiorId ;
  delete from pub_busilog_td where processinstid = varprocessinstId ;
  ret: = 0 ;
  commit ;
end   if ;

/ *生产任务单的数据归档* /
if  busiId  =  ' 91 '  then
  insert into hisioms_interior_td  ( select  * from ioms_interior_td where interior_id = interiorId );
  insert into hispub_busilog_td  ( select  * from pub_busilog_td where processinstid = varprocessinstId );
  delete from ioms_interior_td where interior_id = interiorId ;
  delete from pub_busilog_td where processinstid = varprocessinstId ;
  ret: = 0 ;
  commit ;
end   if ;

/ *重要通知数据归档* /
if  busiId  =  ' 92 '  then
  insert into hisioms_interior_td  ( select  * from ioms_interior_td where interior_id = interiorId );
  insert into hispub_busilog_td  ( select  * from pub_busilog_td where processinstid = varprocessinstId );
  delete from ioms_interior_td where interior_id = interiorId ;
  delete from pub_busilog_td where processinstid = varprocessinstId ;
  ret: = 0 ;
  commit ;
end   if ;

/ *省中心局数据制作数据归档* /
if  busiId  =  ' 52 '  then
  insert into hisioms_interior_td  ( select  * from ioms_interior_td where interior_id = interiorId );
  insert into hisioms_interior_off_rec_td  ( select  * from ioms_interior_office_record_td where officedata_id in  ( select  officedata_id from ioms_interior_officedata_td where interior_id = interiorId ));
  insert into hisioms_interior_officedata_td  ( select  * from ioms_interior_officedata_td where interior_id = interiorId );
  insert into hispub_busilog_td  ( select  * from pub_busilog_td where processinstid = varprocessinstId );
  delete from ioms_interior_office_record_td where officedata_id in ( select  officedata_id from ioms_interior_officedata_td where interior_id = interiorId );
  delete from ioms_interior_officedata_td where interior_id = interiorId ;
  delete from ioms_interior_td where interior_id = interiorId ;
  delete from pub_busilog_td where processinstid = varprocessinstId ;
  ret: = 0 ;
  commit ;
end   if ;
/ *大区的局数据制作数据和本地网外挂小流程归档* /
if  busiId  =  ' 112 '  then  
      insert into hisioms_interior_td  ( select  * from ioms_interior_td where interior_id = interiorId );
      insert into hisioms_interior_local_td  ( select  * from ioms_interior_localoffidata_td where interior_id = interiorId );
      insert into hispub_busilog_td  ( select  * from pub_busilog_td where processinstid = varprocessinstId );
      delete from ioms_interior_localoffidata_td where interior_id = interiorId ;
      delete from ioms_interior_td where interior_id = interiorId ;
      delete from pub_busilog_td where processinstid = varprocessinstId ;
      ret: = 0 ;
      commit ;
end   if ;
/ *传输软件 ( 二干和本地网接入层 ) 升级流程归档* /
if  busiId  =  ' 309 '  then
        insert into hisioms_interior_td  ( select  * from ioms_interior_td where interior_id = interiorId );
    insert into hisioms_interior_upgrade_td  ( select  * from ioms_interior_upgrade_td where interior_id = interiorId );
        insert into hisioms_interior_up_rec_td  ( select  * from ioms_interior_upgrade_rec_td where upgrade_id in  ( select  upgrade_id from ioms_interior_upgrade_td where interior_id  =  interiorId ));
        insert into hisioms_interior_softver_td  ( select  * from ioms_interior_softver_td where upgrade_id in  ( select  upgrade_id from ioms_interior_upgrade_td where interior_id  =  interiorId ));
    insert into hisioms_interior_cssoftinfo_td  ( select  * from ioms_interior_cssoftinfo_td where upgrade_id in  ( select  upgrade_id from ioms_interior_upgrade_td where interior_id = interiorId ));         
        insert into hispub_busilog_td  ( select  * from pub_busilog_td where processinstid = varprocessinstId );
        delete from ioms_interior_upgrade_rec_td where upgrade_id in  ( select  upgrade_id from ioms_interior_upgrade_td where interior_id  =  interiorId );
        delete from ioms_interior_softver_td where upgrade_id in  ( select  upgrade_id from ioms_interior_upgrade_td where interior_id  =  interiorId );
    delete from ioms_interior_cssoftinfo_td where upgrade_id in  ( select  upgrade_id from ioms_interior_upgrade_td where interior_id  =  interiorId );
        delete from ioms_interior_upgrade_td where interior_id = interiorId ;
        delete from ioms_interior_td where interior_id = interiorId ;
        delete from pub_busilog_td where processinstid = varprocessinstId ;
        ret: = 0 ;
      commit ;
end   if ;
/ *传输软件本地网接入型流程归档* /
if  busiId  =  ' 310 '  then
        insert into hisioms_interior_td  ( select  * from ioms_interior_td where interior_id = interiorId );
    insert into hisioms_interior_upgrade_td  ( select  * from ioms_interior_upgrade_td where interior_id = interiorId );
        insert into hisioms_interior_up_rec_td  ( select  * from ioms_interior_upgrade_rec_td where upgrade_id in  ( select  upgrade_id from ioms_interior_upgrade_td where interior_id  =  interiorId ));
        insert into hisioms_interior_softver_td  ( select  * from ioms_interior_softver_td where upgrade_id in  ( select  upgrade_id from ioms_interior_upgrade_td where interior_id  =  interiorId ));
    insert into hisioms_interior_cssoftinfo_td  ( select  * from ioms_interior_cssoftinfo_td where upgrade_id in  ( select  upgrade_id from ioms_interior_upgrade_td where interior_id = interiorId ));         
        insert into hispub_busilog_td  ( select  * from pub_busilog_td where processinstid = varprocessinstId );
        delete from ioms_interior_upgrade_rec_td where upgrade_id in  ( select  upgrade_id from ioms_interior_upgrade_td where interior_id  =  interiorId );
        delete from ioms_interior_softver_td where upgrade_id in  ( select  upgrade_id from ioms_interior_upgrade_td where interior_id  =  interiorId );
    delete from ioms_interior_cssoftinfo_td where upgrade_id in  ( select  upgrade_id from ioms_interior_upgrade_td where interior_id  =  interiorId );
        delete from ioms_interior_upgrade_td where interior_id = interiorId ;
        delete from ioms_interior_td where interior_id = interiorId ;
        delete from pub_busilog_td where processinstid = varprocessinstId ;
        ret: = 0 ;
      commit ;
end   if ;


  / *
 调拨流程归档（busiId = 317 ）
  * /  
  
   if  busiId  =  ' 317 '  then
  insert into hisioms_interior_td  ( select  * from ioms_interior_td where interior_id = interiorId );
  insert into hisioms_interior_allocate_td ( select  * from ioms_interior_allocate_td where interior_id = interiorId );
  insert into hispub_busilog_td  ( select  * from pub_busilog_td where processinstid = varprocessinstId );
  insert into HISIOMS_INTERIOR_ALLOCATE_TJ ( select  * from ioms_interior_allocatetype_tj  where alloc_id =( select  alloc_id from ioms_interior_allocate_td  where interior_id = interiorId ));
  delete from ioms_interior_allocate_td where interior_id = interiorId ;
  delete from ioms_interior_td where interior_id = interiorId ;
  delete from  HISIOMS_INTERIOR_ALLOCATE_TJ where alloc_id =( select  alloc_id from ioms_interior_allocate_td  where interior_id = interiorId );  
  delete from pub_busilog_td where processinstid = varprocessinstId ;
  ret: = 0 ;
  commit ;
end   if ;


/ *异常处理* /
EXCEPTION
   WHEN  reterror  THEN
    rollback ;
    ret: = 1 ;
END ;


----------------------------------------------------------------------------------------------------------------------
CREATE OR  REPLACE  PROCEDURE  " DELETE_IOMS_INTERIOR "   / *内部任务统一归档存储过程，输入
   运维单号，根据运维单号查询业务类型
   和流程实例号，若有相关错误则抛出
   异常，返回ret
   ret = 1         失败
   ret = 0         成功
   ret is null  没有找到相应的业务类型
   create by gsli* /
(
interiorId in          number ,
state in         VARCHAR2 ,
ret         out         VARCHAR2
)
IS
varprocessinstId number ;
countNum number ;
busiId VARCHAR2 ( 10 );
reterror EXCEPTION ;
begin
/ *未归档的工单的删除* /
if  state  =  ' 0 '  then  
   / *当要移除的运维单数超过一条时触发异常* /
   select  count ( processinst_id )  into countNum from ioms_interior_td where interior_id  =  interiorId ;
   if  countNum! = 1   then
    raise reterror ;
   end   if ;
   / *当查出的业务类型为空是触发异常* /
   select  busi_id into busiId from ioms_interior_td where interior_id  =  interiorId ;
   if  busiId is null   then
    raise reterror ;
   end   if ;
   / *当流程实例号为空时触发异常* /
   select  processinst_id into varprocessinstId from ioms_interior_td where interior_id  =  interiorId ;
   if  varprocessinstId is null  then
    raise reterror ;
   end   if ;
  busiId : =   REPLACE ( busiId , ' ' , '' );
     / *工作联系单的数据删除* /
     if  busiId  =  ' 81 '  then  
      delete from ioms_interior_td where interior_id = interiorId ;
      delete from pub_busilog_td where processinstid = varprocessinstId ;
      ret: = 0 ;
      commit ;
     end   if ;
    
     / *生产任务单的数据删除* /
     if  busiId  =  ' 91 '  then  
      delete from ioms_interior_td where interior_id = interiorId ;
      delete from pub_busilog_td where processinstid = varprocessinstId ;
      ret: = 0 ;
      commit ;
     end   if ;
    
     / *重要通知数据删除* /
     if  busiId  =  ' 92 '  then  
      delete from ioms_interior_td where interior_id = interiorId ;
      delete from pub_busilog_td where processinstid = varprocessinstId ;
      ret: = 0 ;
      commit ;
     end   if ;
    
     / *省中心局数据制作数据归档* /
     if  busiId  =  ' 52 '  then  
      delete from ioms_interior_office_record_td where officedata_id in ( select  officedata_id from ioms_interior_officedata_td where interior_id = interiorId );
      delete from ioms_interior_officedata_td where interior_id = interiorId ;
      delete from ioms_interior_td where interior_id = interiorId ;
      delete from pub_busilog_td where processinstid = varprocessinstId ;
      ret: = 0 ;
      commit ;
     end   if ;
     / *大区的局数据制作数据和本地网外挂小流程归档* /
     if  busiId  =  ' 112 '  then  
          delete from ioms_interior_localoffidata_td where interior_id = interiorId ;
          delete from ioms_interior_td where interior_id = interiorId ;
          delete from pub_busilog_td where processinstid = varprocessinstId ;
          ret: = 0 ;
          commit ;
     end   if ;
     / *软件升级流程 ( 二干和本地网骨干层 ) 删除* /
   if  busiId  =  ' 309 '  then
    delete from ioms_interior_upgrade_rec_td where upgrade_id in  ( select  upgrade_id from ioms_interior_upgrade_td where interior_id  =  interiorId );
      delete from ioms_interior_softver_td where upgrade_id in  ( select  upgrade_id from ioms_interior_upgrade_td where interior_id  =  interiorId );
    delete from ioms_interior_cssoftinfo_td where upgrade_id in  ( select  upgrade_id from ioms_interior_upgrade_td where interior_id  =  interiorId );
      delete from ioms_interior_upgrade_td where interior_id = interiorId ;
      delete from ioms_interior_td where interior_id = interiorId ;
      delete from pub_busilog_td where processinstid = varprocessinstId ;
    ret: = 0 ;
      commit ;
   end   if ;
   / *软件升级流程 ( 本地网接入层 ) 删除* /
   if  busiId  =  ' 310 '  then
    delete from ioms_interior_upgrade_rec_td where upgrade_id in  ( select  upgrade_id from ioms_interior_upgrade_td where interior_id  =  interiorId );
      delete from ioms_interior_softver_td where upgrade_id in  ( select  upgrade_id from ioms_interior_upgrade_td where interior_id  =  interiorId );
    delete from ioms_interior_cssoftinfo_td where upgrade_id in  ( select  upgrade_id from ioms_interior_upgrade_td where interior_id  =  interiorId );
      delete from ioms_interior_upgrade_td where interior_id = interiorId ;
      delete from ioms_interior_td where interior_id = interiorId ;
      delete from pub_busilog_td where processinstid = varprocessinstId ;
    ret: = 0 ;
      commit ;
   end   if ;
    / *调拨流程删除* /
    if  busiId  =  ' 317 '  then
   
   delete from IOMS_INTERIOR_ALLOCATETYPE_TJ where alloc_id =( select  alloc_id from ioms_interior_allocate_td  where interior_id = interiorId );  
   delete from ioms_interior_allocate_td where interior_id = interiorId ;
   delete from ioms_interior_td where interior_id = interiorId ;
   delete from pub_busilog_td where processinstid = varprocessinstId ;
    ret: = 0 ;
      commit ;
   end   if ;
end   if ;
/ *已归档的工单的删除* /
if  state  =  ' 1 '  then  
   / *当要移除的运维单数超过一条时触发异常* /
   select  count ( processinst_id )  into countNum from hisioms_interior_td where interior_id  =  interiorId ;
   if  countNum! = 1   then
    raise reterror ;
   end   if ;
   / *当查出的业务类型为空是触发异常* /
   select  busi_id into busiId from hisioms_interior_td where interior_id  =  interiorId ;
   if  busiId is null   then
    raise reterror ;
   end   if ;
   / *当流程实例号为空时触发异常* /
   select  processinst_id into varprocessinstId from hisioms_interior_td where interior_id  =  interiorId ;
   if  varprocessinstId is null  then
    raise reterror ;
   end   if ;
  busiId : =   REPLACE ( busiId , ' ' , '' );
     / *工作联系单的数据删除* /
     if  busiId  =  ' 81 '  then  
      delete from hisioms_interior_td where interior_id = interiorId ;
      delete from hispub_busilog_td where processinstid = varprocessinstId ;
      ret: = 0 ;
      commit ;
     end   if ;
    
     / *生产任务单的数据删除* /
     if  busiId  =  ' 91 '  then  
      delete from hisioms_interior_td where interior_id = interiorId ;
      delete from hispub_busilog_td where processinstid = varprocessinstId ;
      ret: = 0 ;
      commit ;
     end   if ;
    
     / *重要通知数据删除* /
     if  busiId  =  ' 92 '  then  
      delete from hisioms_interior_td where interior_id = interiorId ;
      delete from hispub_busilog_td where processinstid = varprocessinstId ;
      ret: = 0 ;
      commit ;
     end   if ;
    
     / *省中心局数据制作数据归档* /
     if  busiId  =  ' 52 '  then  
      delete from hisioms_interior_off_rec_td where officedata_id in ( select  officedata_id from ioms_interior_officedata_td where interior_id = interiorId );
      delete from hisioms_interior_officedata_td where interior_id = interiorId ;
      delete from hisioms_interior_td where interior_id = interiorId ;
      delete from hispub_busilog_td where processinstid = varprocessinstId ;
      ret: = 0 ;
      commit ;
     end   if ;
     / *大区的局数据制作数据和本地网外挂小流程归档* /
     if  busiId  =  ' 112 '  then  
          delete from hisioms_interior_local_td where interior_id = interiorId ;
          delete from hisioms_interior_td where interior_id = interiorId ;
          delete from hispub_busilog_td where processinstid = varprocessinstId ;
          ret: = 0 ;
          commit ;
     end   if ;
     / *软件升级流程 ( 二干和本地网骨干层 ) 删除* /
   if  busiId  =  ' 309 '  then
    delete from hisioms_interior_up_rec_td where upgrade_id in  ( select  upgrade_id from hisioms_interior_upgrade_td where interior_id  =  interiorId );
      delete from hisioms_interior_softver_td where upgrade_id in  ( select  upgrade_id from hisioms_interior_upgrade_td where interior_id  =  interiorId );
    delete from hisioms_interior_cssoftinfo_td where upgrade_id in  ( select  upgrade_id from hisioms_interior_upgrade_td where interior_id  =  interiorId );
      delete from hisioms_interior_upgrade_td where interior_id = interiorId ;
      delete from hisioms_interior_td where interior_id = interiorId ;
      delete from hispub_busilog_td where processinstid = varprocessinstId ;
    ret: = 0 ;
      commit ;
   end   if ;
   / *软件升级流程 ( 本地网接入层 ) 删除* /
   if  busiId  =  ' 310 '  then
    delete from hisioms_interior_up_rec_td where upgrade_id in  ( select  upgrade_id from hisioms_interior_upgrade_td where interior_id  =  interiorId );
      delete from hisioms_interior_softver_td where upgrade_id in  ( select  upgrade_id from hisioms_interior_upgrade_td where interior_id  =  interiorId );
    delete from hisioms_interior_cssoftinfo_td where upgrade_id in  ( select  upgrade_id from hisioms_interior_upgrade_td where interior_id  =  interiorId );
      delete from hisioms_interior_upgrade_td where interior_id = interiorId ;
      delete from hisioms_interior_td where interior_id = interiorId ;
      delete from hispub_busilog_td where processinstid = varprocessinstId ;
    ret: = 0 ;
      commit ;
   end   if ;
end   if ;
/ *异常处理* /
EXCEPTION
   WHEN  reterror  THEN
    rollback ;
    ret: = 1 ;
END ;
------------------------------------------------------------------------------------
运维成本：
select  * from ioms_cost_budget_td  b where b . budget_name like '%信息中心2007年7月份终端维护费%'
select  * from IOMS_COST_PROJECT_TD c where c . bud_project_id = ' 8642 '
select  * from ioms_cost_apply_td
select  * from ioms_cost_budget_td  b where b . budget_id = ' 11091 '
select  * from IOMS_COST_PROJECT_TD c where c . bud_project_id = ' 8642 '
delete from ioms_cost_budget_td where budget_id = ' 11091 '
delete from  IOMS_COST_PROJECT_TD  where bud_project_id = ' 8642 '
select   * from  tssa . bndict_t_dictionary  a where  a . busintypeid = 'IOMS_PROJECT_TYPE'

select  b . budget_type from  IOMS_COST_BUDGET_TD b where b . budget_id = ' 12012 '
select  * from  ioms_cost_project_td c where c . bud_project_id = 9642



-------------------------------------------------------------------------------------------------
oracle 系统工具文件
------------------------------------------------------------------------------------------------------------
sqlplusw  / nolog-登录

SQL >  conn tssa / tssa2006@ahfwbz_192 . 168.20 . 18
已连接。
SQL >  show sga ;

Total System Global Area   114365800  bytes
Fixed Size                    451944  bytes
Variable Size               79691776  bytes
Database  Buffers             33554432  bytes
Redo  Buffers                   667648  bytes
SQL >  SQL >  conn 

--------------------------------------------------------------------------------------------------
sql基础知识 
1 . 数据库查询：Select用法 
Select  [Top ( 数值 ) ] 字段列表 From 数据表 [Where 条件] [Order by 字段] [asc或desc] 

2 . 添加数据：Insert Into用法 
Insert Into 数据表 ( 字段1 , 字段2 , 字段3 , … )  Values ( 字段1的值 , 字段2的值 , 字段3的值 , … )  

3 . 删除数据：Delete用法 
Delete From 数据表 [Where 条件] 

4 . 更新数据：Update用法 
Update 数据表  Set  字段1 = 字段值1 ,  字段2 = 字段值2 ,  … [Where 条件] 

5 . 建数据表：Create Table用法 
Create Table 数据表名 ( 字段1名称 , 字段1类型 , 字段2名称 字段2类型 ,  … )  

Access数据表常用数据类型 :Text , Char ( Number ), Memo , Number , Int , Date / Time , Logical , OLEObject 

自动编号字段添加例子 :create  table aaa ( id int identity  ( 1 ,   1 )   not  null , abc varchar ( 25 )  null )  

6 . 改数据表：Alter Table用法 
添加字段：Alter Table 数据表名 Add Column 字段名 字段类型 

删除字段：Alter Table 数据表名 Drop Column 字段名 

7 . 删数据表：Drop Table用法 

---------------------------------------------------------------------------------------------------------


select  a . interior_id , a . interior_flow_id , a . interior_title , a . interior_detail , a . interior_senduser_org_id , a . busi_id , g . orgname , b . busi_name ,  
( select  h . OPERATORNAME from EOSOPERATOR h where 
h . userid = a . interior_senduser_id or to_char ( h . operatorid )= a . interior_senduser_id )  as  OPERATORNAME ,



to_char ( a . interior_notify_time , 'YYYY-MM-DD HH24 :MI ' )  as INTERIOR_NOTIFY_TIME ,
to_char ( a . interior_finish_time , 'YYYY-MM-DD HH24 :MI ' )  as INTERIOR_FINISH_TIME  



from IOMS_INTERIOR_TD a , PUB_BUSITYPE_TD b , eosorg_t_organization g 
where b . busi_id = a . busi_id and g . orgid = a . interior_senduser_org_id  
and a . interior_senduser_org_id in  ( select  orgid from eosorg_t_organization 
where orgseq like ' %9999 . 2 . %' )  and a . INTERIOR_NOTIFY_TIME >= to_date ( '  2007 - 08 - 01   13 : 16 ' , 'YYYY-MM-DD HH24 :MI ' )  
and a . INTERIOR_NOTIFY_TIME <= to_date ( ' 2007 - 08 - 31   13 : 16 ' , 'YYYY-MM-DD HH24 :MI ' )  and a . BUSI_ID = ' 91 '
order by a . INTERIOR_NOTIFY_TIME DESC ;  

-------------------------------------------------------------------------------------------------------------------
删除某个工单的关联记录
---------------------------------------------------------------------------------------------------------------------

 DECLARE
   v_interiorid   ioms_interior_allocate_td . interior_id% TYPE ;
      CURSOR my_cursor IS
        select   interior_id from  ioms_interior_allocate_td   a where a . orginal_worth = ' 5555555 ' ;
   BEGIN
     OPEN my_cursor ;
     LOOP
       FETCH my_cursor INTO v_interiorid ;
      delete ioms_interior_allocate_tj where alloc_id =( select  alloc_id from  ioms_interior_allocate_td where interior_id = v_interiorid );
 delete ioms_interior_allocate_td where interior_id = v_interiorid ;
   delete pub_busilog_td where processinstid =( select  processinstid from ioms_interior_td  where interior_id = v_interiorid );
   commit ;
  delete ioms_interior_td where interior_id = v_interiorid ;
   commit ;
  dbms_output . put_line ( v_interiorid );
   EXIT   WHEN  my_cursor%NOTFOUND ;
     END  LOOP ;
    CLOSE my_cursor ;
  END ;
   /
--------------------------------------------------------------------------------------------------------------------
oracle 日志分析步骤 . txt
--------------------------------------------------------------------------------------------------------------------
1 . 确保utl_file_dir目录已经存在
通过show parameter utl_file_dir 这个命令查看
如果不存在，先在init . ora中设置utl_file_dir = '具体的一个目录路径 , 后来分析的时候用到'
修改后重启数据库使他生效，主要在启动的时候要保证数据库使用的是init . ora这个文件
2调用日志分析程序包在改路径下创建用于分析数据的字典文件如：
execute dbms_logmnr_d . build ( 'ahfwbzdict . ora' , ' / opt / oracle / admin / ahfwbz / cdump' );
3 . 将所要分析的文件加到改分析文件中
execute dbms_logmnr . add_logfile ( ' / opt / oracle / product / 9.2 / dbs / arch1_275 . dbf' , dbms_logmnr . new );
4 . 执行分析
execute dbms_logmnr . start_logmnr ( dictfilename => ' / opt / oracle / admin / ahfwbz / cdump / ahfwbzdict . ora' );
5 . 查看分析结果
SELECT  TIMESTAMP , SEG_TYPE_NAME , TABLE_SPACE , USERNAME , SESSION_INFO , OPERATION , SQL_REDO , SQL_UNDO
  from v $ logmnr_contents  where seg_type_name = 'TABLE' and table_space = 'TSSADATA' and operation = 'DELETE' ;
 
 
 
 
查询魔个时间段 
 ----------------------------------------------------------------------------------------------------------------
   select   count ( * )  from  hisioms_interior_td b  where b . interior_notify_time >= to_date ( ' 2007 - 09 - 01   00 : 00 : 00 ' , 'yyyy-mm-dd hh24 :mi:ss ' )  and b . interior_finish_time <= to_date ( ' 2007 - 09 - 30   00 : 00 : 00 ' , 'yyyy-mm-dd hh24 :mi:ss ' )
and b . busi_id = 327
----------------------------------------------------------------------------------------------------------------------


重启数据库
---------------------------------------------------------------------------------------------------------


雨过天晴  09 : 06 : 11
数据库的启动：
  在root下关数据库   
   1 .# su   -   oracle                  // 转到oracle用户   
   2 .$ lsnrctl   stop                  // 关监听   
   3 .$ shutdown immediate
                            // 关数据库     
  
   / home / oracle / product / 9.2 / bin 在里面起服务！
   ./ sqlplus  / nolog
  
  在root开数据库   
   1 .# su   -   oracle                  // 转到oracle用户   
   2 .$ sqlplus  / onlog
   3 .$ connect  / as sysdba
   4 .$ startup
  切换到oracle用户下                              // 开数据库     
   $ lsnrctl    start                   // 开监听  
  
  
  
  
  
  ----------------------------------------------------------------------------------------------------------------------------------
  Red Hat Enterprise Linux AS release  3   ( Taroon )
Kernel  2.4 . 21 - 4 . EL  on  an i686
login: oracle
Password :devoracle2007
Last login: Thu Oct  25   16 : 28 : 01  from  192.168 . 20.152
[oracle@kdhx oracle] $  ls
070213 . dmp        jdhuang                sxtsioms070208_1303 . dmp
abb . dmp           kxu816data . dmp         sxtsioms . 2007 - 01 - 28 -14_46 . dmp
allall . tar        kxuData0807 . dmp        sxtsioms . 2007 - 02 - 09 -17_24 . dmp
AllData . dmp       kxuSql . sql             sxtsioms . 2007 - 04 - 25 -01_00 . dmp
busiTypedata . dmp  mstrback_20070308 . dmp  test
daymng . dmp        mstrold_20070308 . dmp   test1 . sh
daymng . log        oradata                test . sh
expdat . dmp        startdb                users . dmp
flow . dmp          sxeoms070212_1354 . dmp  wwang
flow . log          sxtsioms0213 . epj       yding
[oracle@kdhx oracle] $  env
REMOTEHOST = 192.168 . 20.109
HOSTNAME = kdhx
SHELL =/ bin / bash
TERM = ansi
HISTSIZE = 1000
ORACLE_OWNER = oracle
USER = oracle
LD_LIBRARY_PATH =/ opt / oracle / product / 9.2 / lib :LD_LIBRARY_PATH
LS_COLORS = no = 00 :fi = 00 :di = 01 ; 34 :ln = 01 ; 36 :pi = 40 ; 33 :so = 01 ; 35 :bd = 40 ; 33 ; 01 :cd = 40 ; 33 ; 0
1 :or = 01 ; 05 ; 37 ; 41 :mi = 01 ; 05 ; 37 ; 41 :ex = 01 ; 32 :* . cmd = 01 ; 32 :* . exe = 01 ; 32 :* . com = 01 ; 32 :* . b
tm = 01 ; 32 :* . bat = 01 ; 32 :* . sh = 01 ; 32 :* . csh = 01 ; 32 :* . tar = 01 ; 31 :* . tgz = 01 ; 31 :* . arj = 01 ; 31 :
* . taz = 01 ; 31 :* . lzh = 01 ; 31 :* . zip = 01 ; 31 :* . z = 01 ; 31 :* . Z = 01 ; 31 :* . gz = 01 ; 31 :* . bz2 = 01 ; 31 :*
. bz = 01 ; 31 :* . tz = 01 ; 31 :* . rpm = 01 ; 31 :* . cpio = 01 ; 31 :* . jpg = 01 ; 35 :* . gif = 01 ; 35 :* . bmp = 01 ; 3
5 :* . xbm = 01 ; 35 :* . xpm = 01 ; 35 :* . png = 01 ; 35 :* . tif = 01 ; 35 :
ORACLE_SID = sxeoms
ORACLE_BASE =/ opt / oracle
MAIL =/ var / spool / mail / oracle
PATH =/ opt / oracle / product / 9.2 / bin: / opt / oracle / product / 9.2 / bin: / opt / oracle / product
/ 9.2 / Apache / Apache / bin: / usr / kerberos / bin: / usr / local / bin: / bin: / usr / bin: / usr / X11R6
/ bin: / home / oracle / bin
INPUTRC =/ etc / inputrc
PWD =/ home / oracle
THREADS_FLAG = native
LANG = en_US
ORACLE_TERM = vt100
SSH_ASKPASS =/ usr / libexec / openssh / gnome-ssh-askpass
SHLVL = 1
HOME =/ home / oracle
LANGUAGE = zh_CN . GB18030 :zh_CN . GB2312 :zh_CN
LD_ASSUME_KERNEL = 2.4 . 21
LOGNAME = oracle
LESSOPEN = | / usr / bin / lesspipe . sh %s
ORACLE_HOME =/ opt / oracle / product / 9.2
G_BROKEN_FILENAMES = 1
_ =/ bin / env
[oracle@kdhx oracle] $  export oracle_sid = ahfwbz          
[oracle@kdhx oracle] $  ls
070213 . dmp        jdhuang                sxtsioms070208_1303 . dmp
abb . dmp           kxu816data . dmp         sxtsioms . 2007 - 01 - 28 -14_46 . dmp
allall . tar        kxuData0807 . dmp        sxtsioms . 2007 - 02 - 09 -17_24 . dmp
AllData . dmp       kxuSql . sql             sxtsioms . 2007 - 04 - 25 -01_00 . dmp
busiTypedata . dmp  mstrback_20070308 . dmp  test
daymng . dmp        mstrold_20070308 . dmp   test1 . sh
daymng . log        oradata                test . sh
expdat . dmp        startdb                users . dmp
flow . dmp          sxeoms070212_1354 . dmp  wwang
flow . log          sxtsioms0213 . epj       yding
[oracle@kdhx oracle] $  conn test / test as sysdba
-bash: conn:  command   not  found
[oracle@kdhx oracle] $  conn test / test as sysdba
-bash: conn:  command   not  found
[oracle@kdhx oracle] $  conn test / test@ahfwbz as sysdba
-bash: conn:  command   not  found
[oracle@kdhx oracle] $  conn test / test@ahfwbz_192 . 168.20 . 18  as sysdba
-bash: conn:  command   not  found
[oracle@kdhx oracle] $

忘了监听
-----------------------------------------------------------------------------------
路由：
   Network Address          Netmask  Gateway Address  Metric
         134.0 . 0.0          255.0 . 0.0       192.168 . 20.5         1
   
   route delete  134.0 . 0.0  
   
   
   C: Documents and Settings Administrator > route add  134.0 . 0.0  mask  255.0 . 0.0   192.168 . 20.5  
   
   
   
   
   
   
   删除环节：
select  *  from  pub_activity_td   where local_id = 42  and busi_id = 112  and activityid = 'A5510000000000003912'


delete from  pub_activity_td   where local_id = 42  and busi_id = 112  and activityid = 'A5510000000000003910'


------------------------------------------------------------------------------------------------------------------------


-----------------------------------------------------------------------------------------------------------------------
        
1 ，设置savapoint A ;
   回到断点：rollback to savapoint A ;
 --------------------------------------  
数据定义语言：DDl 如表，索引，视图，序列，命令如：create drop alter；
数据操纵语言 :DML   如：selete insert update delete；
在一个数据库中建立的全部对象都保存在数据字典中；
在varchar中，数据可变插入，而char只能定长插入；
pk和unique都具有唯一性  区别：pk不允许有null，而unique允许null；
truncate 可以删除约束的表 并隐含提交commit；
 ---------------------------------------------  
2 ,  先where 后select 
   先where 再group 再having 后select 
   先where 再group 再having 再select 后order 
   先join 再where 后select
   
 -----------------------------------------------  
3 ，查找和删除重复记录
            1 、查找表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断
           select  * from people
         where peopleId in  ( select  peopleId from people group by peopleId having count ( peopleId )   >   1 )
        
           2 、删除表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断，只留有rowid最小的记录
        delete from people 
        where peopleId in  ( select  peopleId from people group by peopleId having count ( peopleId )   >   1 )
        and rowid  not  in  ( select  min ( rowid )  from people group by peopleId having count ( peopleId  )> 1 )
        
           3 、查找表中多余的重复记录（多个字段） 
           select  * from vitae a
          where  ( a . peopleId , a . seq )  in  ( select  peopleId , seq from vitae group by peopleId , seq having count ( * )   >   1 )
        
         4 、删除表中多余的重复记录（多个字段），只留有rowid最小的记录
        delete from vitae a
        where  ( a . peopleId , a . seq )  in  ( select  peopleId , seq from vitae group by peopleId , seq having count ( * )   >   1 )
        and rowid  not  in  ( select  min ( rowid )  from vitae group by peopleId , seq having count ( * )> 1 )
        
        
         5 、查找表中多余的重复记录（多个字段），不包含rowid最小的记录
         select  * from vitae a
        where  ( a . peopleId , a . seq )  in  ( select  peopleId , seq from vitae group by peopleId , seq having count ( * )   >   1 )
        and rowid  not  in  ( select  min ( rowid )  from vitae group by peopleId , seq having count ( * )> 1 )
        
         ( 二 )
        比方说
        在A表中存在一个字段“name”，
        而且不同记录之间的“name”值有可能会相同，
        现在就是需要查询出在该表中的各记录之间，“name”值存在重复的项；
         Select  Name , Count ( * )  From A Group By Name Having Count ( * )   >   1
        
        如果还查性别也相同大则如下:
         Select  Name , sex , Count ( * )  From A Group By Name , sex Having Count ( * )   >   1
        
        
         ( 三 )
        方法一
        
        declare @max integer , @id integer
        
        declare cur_rows cursor local  for   select  主字段 , count ( * )  from 表名 group by 主字段 having count ( * )   > ；  1
        
        open cur_rows
        
        fetch cur_rows into @id , @max
        
        while @@fetch_status = 0
        
        begin
        
         select  @max  =  @max - 1
        
         set  rowcount @max
        
        delete from 表名 where 主字段  =  @id
        
        fetch cur_rows into @id , @max
        
         end
        
        close cur_rows
        
         set  rowcount  0
        
        方法二
        
        ＂重复记录＂有两个意义上的重复记录，一是完全重复的记录，也即所有字段均重复的记录，二是部分关键字段重复的记录，比如Name字段重复，而其他字段不一定重复或都重复可以忽略。
        
        　　 1 、对于第一种重复，比较容易解决，使用
        
         select  distinct * from tableName
        
        　　就可以得到无重复记录的结果集。
        
        　　如果该表需要删除重复的记录（重复记录保留1条），可以按以下方法删除
        
         select  distinct * into  # Tmp from tableName
        
        drop table tableName
        
         select  * into tableName from  # Tmp
        
        drop table  # Tmp
        
        　　发生这种重复的原因是表设计不周产生的，增加唯一索引列即可解决。
        
        　　 2 、这类重复问题通常要求保留重复记录中的第一条记录，操作方法如下
        
        　　假设有重复的字段为Name , Address，要求得到这两个字段唯一的结果集
        
         select  identity ( int , 1 , 1 )  as autoID ,  * into  # Tmp from tableName
        
         select  min ( autoID )  as autoID into  # Tmp2 from  # Tmp group by Name , autoID
        
         select  * from  # Tmp where autoID in ( select  autoID from  # tmp2 )
        
        　　最后一个select即得到了Name，Address不重复的结果集（但多了一个autoID字段，实际写时可以写在select子句中省去此列）
        
         ( 四 )
        查询重复
        
         select  * from tablename where id in  (
        
         select  id from tablename 
        
        group by id 
        
        having count ( id )   >   1
        
         )  
----------------------------------------------------------------------------------------------
创建表  并限定其如入值：
create table（
......
zip_code varchar ( 20 )  check ( zip_code in ( ' 001 ' , ' 005 ' ))
.....
）

create table（
......
zip_code varchar ( 20 )  check ( zip_code between  10  and  50 ))
.....
）

-------------------------------------------------------------------------------------

 

 

 

 

 

 

 

 

 

 

 

 

第三部分  操作数据：SQL


          本部分内容
            SQL基础
            中级SQL
          高级SQL 
 

   
第10章  SQL基础


                 本章内容
           
                 SQL介绍
                 使用SELECT 语句从表中取数据
                 创建新表
                 字段属性 
                 向表中添加数据
                 删除和修改表       
         

    为了建立交互站点，你需要使用数据库来存储来自访问者的信息。例如，你要建立一个职业介绍服务的站点，你就需要存储诸如个人简历，所感兴趣的工作等等这样的信息。创建动态网叶也需要使用数据库，如果你想显示符合来访者要求的最好的工作，你就需要从数据库中取出这份工作的信息。你将会发现，在许多情况下需要使用数据库。
    在这一章里，你将学会怎样使用“结构化查询语言”（SQL〕来操作数据库。SQL语言是数据库的标准语言。在Active Sever Pages 中，无论何时你要访问一个数据库，你就要使用SQL语言。因此，掌握好SQL对ASP编程是非常重要的。
     
    注意：
    你可以把“SQL”读作“sequel”，也可以按单个字母的读音读作S－Q－L。                        两种发音都是正确的，每种发音各有大量的支持者。在本书里，认为“SQL”读作“sequel”。

    通过这一章的学习，你将理解怎样用SQL实现数据库查询，你将学会怎样使用这种查询从数据表中取出信息，最后，你将学会怎样设计和建立自己的数据库。
            
    注意：
    通过下面几章对SQL的介绍，你将对SQL有足够的了解，从而可以有效地使用Active Sever Pages。但是，SQL是一种复杂的语言，本书不可能包括它的全部细节。要全面掌握SQL语言，你需要学习在Microsoft SQL Sever 中使用SQL。你可以到附近的书店去买一本Microsoft SQL Sever  6.5 。

SQL介绍：
    本书假设你是在SQL操作Microsoft SQL Sever 的数据库。你也可以用SQL操作许多其它类型的数据库。SQL是操作数据库的标准语言。（事实上，关于SQL语言有一个专门的ANSI标准〕

    注意：
    不要在你的站点上试图用Microsoft Access代替Microsoft SQL Sever。SQL Sever可以同时服务于许多用户，如果你希望你的站点有较高的访问率，MS Access是不能胜任的。

    在学习SQL的细节之前，你需要理解它的两大特点。一个特点容易掌握，另一个掌握起来有点困难。
    第一个特点是所有SQL数据库中的数据都存储在表中。一个表由行和列组成。例如，下面这个简单的表包括name 和e-mail address：
    Name                  Email Address
     ................................................................
    Bill Gates               billg@microsoft . com
    president Clinton         president@whitehouse . com
    Stephen Walther         swalther@somewhere . com
    这个表有两列（列也称为字段，域〕：Name和Email Address。有三行，每一行包含一组数据。一行中的数据组合在一起称为一条记录。
    无论何时你向表中添加新数据，你就添加了一条新记录。一个数据表可以有几十个记录，也可以有几千甚至几十亿个记录。虽然你也许永远不需要存储十亿个Email地址，但知道你能这样做总是好的，也许有一天你会有这样的需要。
    你的数据库很有可能包含几十个表，所有存储在你数据库中的信息都被存储在这些表中。当你考虑怎样把信息存储在数据库中时，你应该考虑怎样把它们存储在表中。
    SQL的第二个特点有些难于掌握。这种语言被设计为不允许你按照某种特定的顺序来取出记录，因为这样做会降低SQL Sever取记录的效率。使用SQL，你只能按查询条件来读取记录。
    当考虑如何从表中取出记录时，自然会想到按记录的位置读取它们。例如，也许你会尝试通过一个循环，逐个记录地扫描，来选出特定的记录。在使用SQL时，你必须训练自己，不要有这种思路。
    假如你想选出所有的名字是“Bill Gates”的记录，如果使用传统的编程语言，你也许会构造一个循环，逐个查看表中的记录，看名字域是否是“Bill Gates”。
    这种选择记录的方法是可行的，但是效率不高。使用SQL，你只要说，“选择所有名字域等于Bill Gates的记录”，SQL就会为你选出所有符合条件的记录。SQL会确定实现查询的最佳方法。
    建设你想取出表中的前十个记录。使用传统的编程语言，你可以做一个循环，取出前十个记录后结束循环。但使用标准的SQL查询，这是不可能实现的。从SQL的角度来说，在一个表中不存在前十个记录这种概念。
    开始时，当你知道你不能用SQL实现某些你感觉应该能实现的功能，你会受到挫折。你也许会以头撞墙甚至想写恶毒的信件给SQL的设计者们。但后来你会认识到，SQL的这个特点不仅不是个限制，反而是其长处。因为SQL不根据位置来读取记录，它读取记录可以很快。
    综上所述，SQL有两个特点：所有数据存储在表中，从SQL的角度来说，表中的记录没有顺序。在下一节，你将学会怎样用SQL从表中选择特殊的记录。

使用SQL从表中取记录。
    SQL的主要功能之一是实现数据库查询。如果你熟悉Internet 引擎，那么你已经熟悉查询了。你使用查询来取得满足特定条件的信息。例如，如果你想找到有ASP信息的全部站点，你可以连接到 Yahoo!并执行一个对Active Sever Pages的搜索。在你输入这个查询后，你会收到一个列表，表中包括所有其描述中包含搜索表达式的站点。
    多数Internet 引擎允许逻辑查询。在逻辑查询中，你可以包括特殊的运算符如AND、OR和NOT，你使用这些运算符来选择特定的记录。例如，你可以用AND来限制查询结果。如果你执行一个对Active Sever Pages  AND  SQL的搜索。你将得到其描述中同时包含Active Sever Pages 和SQL的记录。当你需要限制查询结果时，你可以使用AND。
    如果你需要扩展查询的结果，你可以使用逻辑操作符OR。例如，如果你执行一个搜索，搜索所有的其描述中包含Active Sever Pages  OR  SQL的站点，你收到的列表中将包括所有其描述中同时包含两个表达式或其中任何一个表达式的站点。
    如果你想从搜索结果中排除特定的站点，你可以使用NOT。例如，查询“Active Sever Pages  ”AND  NOT  “SQL”将返回一个列表，列表中的站点包含Active  Sever Pages，但不包含SQL。当必须排除特定的记录时，你可以使用NOT。
    用SQL执行的查询与用Internet搜索引擎执行的搜索非常相似。 当你执行一个SQL查询时，通过使用包括逻辑运算符的查询条件，你可以得到一个记录列表。此时查询结果是来自一个或多个表。
    SQL查询的句法非常简单。假设有一个名为email_table 的表，包含名字和地址两个字段，要得到Bill Gates 的e_mail地址 , 你可以使用下面的查询：

     SELECT  email from email_table WHERE name = " Bill Gates "

    当这个查询执行时，就从名为email_table的表中读取Bill Gates的e_mail 地址。这个简单的语句包括三部分：
    ■  SELECT语句的第一部分指名要选取的列。在此例中，只有email列被选取。当执行 时，只显示email列的值 billg@microsoft . com。
    ■  SELECTT语句的第二部份指明要从哪个（些）表中查询数据。在此例中，要查询的表名为email_table 。
    ■  最后，SELECT语句的WHERE子句指明要选择满足什么条件的记录。在此例中，查询条件为只有name列的值为Bill Gates 的记录才被选取。
    Bill Gates很有可能拥有不止一个email地址。如果表中包含Bill Gates的多个email地址。用上述的SELECT语句可以读取他所有的email地址。SELECT语句从表中取出所有name字段值为Bill Gates 的记录的email 字段的值。
    前面说过，查询可以在查询条件中包含逻辑运算符。假如你想读取Bill Gates 或Clinton总统的所有email地址，你可以使用下面的查询语句：

     SELECT   email  FROM  email_table  WHERE  name = " Bill Gates "  OR 
                                                   name = " president  Clinton "

    此例中的查询条件比前一个复杂了一点。这个语句从表email_table中选出所有name列为Bill Gates或president Clinton的记录。如果表中含有Bill Gates或president Clinton的多个地址，所有的地址都被读取。
    SELECT语句的结构看起来很直观。如果你请一个朋友从一个表中为你选择一组记录，你也许以非常相似的方式提出你的要求。在SQL SELECT语句中，你“SELECT特定的列FROM一个表WHERE某些列满足一个特定的条件”。
    下一节将介绍怎样执行SQL查询来选取记录。这将帮助你熟悉用SELECT语句从表中取数据的各种不同方法。

使用ISQL执行SELECT查询
    当你安装SQL Sever时，你同时安装了一个叫作ISQL / w的应用程序。ISQL / w允许你执行交互的SQL查询。在把查询包括到你的ASP网页中之前，用ISQL / w对其进行测试是非常有用的。

    注意：
    在这本书的第一部份，你学习了怎样安装和配置Microsoft SQL Sever 。如果没有安装SQL Sever或者SQL Sever不能运行，请参阅第三章“安装和使用SQL Sever”。

    选择任务上SQL Sever程序组中的ISQL_w以启动该程序。程序启动时，首先会出现一个对话框，要求输入服务器信息和登录信息（见图10 . 1 ）。在Sever框中，输入你的SQL服务器的名字。如果服务器正运行在本地计算机上，服务器名字就是你计算机的名字。在登录信息框中，输入一个登录帐号和密码或选择使用“可信连接”，然后单击Connect按钮。 

      图10。 1



    注意：
    如果你将SQL Sever配置为使用完整安全或混合安全，那么你可以使用可信连接。如果你使用标准安全，你则需要提供用户帐号和密码。要了解更多信息，参见第三章。

    如果一切正常，在你单击连接按钮后会出现一个查询窗口，如图10 . 2所示。（如果有异常，请参考第三章） 


        图10 . 2


    在执行查询之前，你需要选择数据库。安装 SQL Sever时你已为自己创建了一个数据库，SQL Sever还有许多系统数据库，如master，model，msdb，和tempdb。
    方便的是，SQL Sever带有一个特殊的名为pubs的例子数据库。库 pubs中包含供一个虚拟的出版商使用的各个表。文档中所有的例子程序都是针对这个库来设计的。本书中的许多例子也使用这个数据库。
    在查询窗口顶部的DB下拉框中选择数据库pubs，这样你就选择了数据库。你所有的查询都将针对这个库中的各个表来执行。现在你可以执行你的第一个查询了。这真让人兴奋！
    你的第一个查询将针对一个名为autrors的表，表中包含所有为某个虚拟出版商工作的作者的相关数据。单击查询窗口并输入以下的语句：

     SELECT   phone  FROM authors WHERE au_name = " Ringer "

    输入完成后，单击执行查询按钮（一个绿色三角形，看起来像VCR播放键）。单击此按钮后，任何出现在查询窗口中的语句均会被执行。查询窗口会自动变成结果显示窗口，你可以看到查询的结果（见图10 . 3 ）。
    你看到的查询结果也许与图10 . 3所示的不同。在SQL Sever的不同版本中，库pubs中的数据会有所不同。对SQL Sever  6 . 5来说，将会找到两条记录。结果显示窗口中应显示如下内容：
    
    phone
……………… .
801  826_0752
801  826_0752
     ( 2  row ( s )   affected )

  
 
  图10 . 3



    你所执行的SELECT语句从表authors中取出所有名字为Ringer的作者的电话号码。你通过在WHERE子句中使用特殊的选择条件来限制查询的结果。你也可以忽略选择条件，从表中取出所有作者的电话号码。要做到这一点，单击Query标签，返回到查询窗口，输入以下的SELECT语句：

SELECT  Phone  FROM authors
    这个查询执行后，会取出表authors中的所有电话号码（没有特定的顺序）。如果表authors中包含一百个电话号码，会有一百个记录被取出，如果表中有十亿个电话号码，这十亿条记录都会被取出（这也许需要一些时间）。
    表authrs的字段包括姓，名字，电话号码，地址，城市，州和邮政编码。通过在SELECT语句的第一部份指定它们，你可以从表中取出任何一个字段。你可以在一个SELECT语句中一次取出多个字段，比如：

     SELECT  au_fname  , au_lname ,  phone FROM authors

    这个SELECT语句执行后，将取出这三个列的所有值。下面是这个查询的结果的一个示例（为了节省纸张，只显示查询结果的一部分，其余记录用省略号代替）：

    au_fname          au_lname                 phone 
    ………………………………………………………………………… .
    Johnson            White                     408   496_7223
    Marjorie            Green                     415   986_7020
    Cheryl              Carson                   415   548_7723
    Michael             O’Leary                  408   286_2428
    … 
     ( 23  row ( s )   affected )

    在SELECT语句中，你需要列出多少个字段，你就可以列出多少。不要忘了把字段名用逗号隔开。你也可以用星号（*）从一个表中取出所有的字段。这里有一个使用星号的例子：

     SELECT   *  FROM  authors

    这个SELECT语句执行后，表中的所有字段的值都被取出。你会发现你将在SQL查询中频繁使用星号。

    技巧：
    你可以使用星号来查看一个表的所有列的名字。要做到这一点，只需要在执行完SELECT语句后看一下查询结果的列标题。

操作多个表
    到现在为止，你只尝试了用一句SQL查询从一个表中取出数据。你也可以用一个SELECT语句同时从多个表中取出数据，只需在SELECT语句的FROM从句中列出要从中取出数据的表名称即可：
        
     SELECT  au_lname  , title  FROM  authors ,  titles   

    这个SELECT语句执行时，同时从表authors和表titles中取出数据。从表authors中取出所有的作者名字，从表titles中取出所有的书名。在ISQL / w程序中执行这个查询，看一下查询结果。你会发现一些奇怪的出乎意料的情况：作者的名字并没有和它们所著的书相匹配，而是出现了作者名字和书名的所有可能的组合，这也许不是你所希望见到的。
    出了什么差错？问题在于你没有指明这两个表之间的关系。你没有通过任何方式告诉SQL如何把表和表关联在一起。由于不知道如何关联两个表，服务器只能简单地返回取自两个表中的记录的所有可能组合。
    要从两个表中选出有意义的记录组合，你需要通过建立两表中字段的关系来关联两个表。要做到这一点的途径之一是创建第三个表，专门用来描述另外两个表的字段之间的关系。
    表authors有一个名为au_id的字段，包含有每个作者的唯一标识。表titles有一个名为title_id的字段，包含每个书名的唯一标识。如果你能在字段au_id和字段title_id 之间建立一个关系，你就可以关联这两个表。数据库pubs中有一个名为titleauthor的表，正是用来完成这个工作。表中的每个记录包括两个字段，用来把表titles和表authors关联在一起。下面的SELECT语句使用了这三个表以得到正确的结果：

     SELECT   au_name , title FROM authors , titles , titleauthor  
                            WHERE  authors . au_id = titleauthor . au_id
                             AND    titles . title_id = titleauthor . title_id    

    当这个SELECT语句执行时，每个作者都将与正确的书名相匹配。表titleauthor指明了表authors和表titles的关系，它通过包含分别来自两个表的各一个字段实现这一点。第三个表的唯一目的是在另外两个表的字段之间建立关系。它本身不包含任何附加数据。
    注意在这个例子中字段名是如何书写的。为了区别表authors和表titles中相同的字段名au_id，每个字段名前面都加上了表名前缀和一个句号。名为author . au_id 的字段属于表authors，名为titleauthor . au_id的字段属于表titleauthor，两者不会混淆。
    通过使用第三个表，你可以在两个表的字段之间建立各种类型的关系。例如，一个作者也许写了许多不同的书，或者一本书也许由许多不同的作者共同完成。当两个表的字段之间有这种“多对多”的关系时，你需要使用第三个表来指明这种关系。
    但是，在许多情况下，两个表之间的关系并不复杂。比如你需要指明表titles和表publishers之间的关系。因为一个书名不可能与多个出版商相匹配，你不需要通过第三个表来指明这两个表之间的关系。要指明表titles和表publishers之间的关系，你只要让这两个表有一个公共的字段就可以了。在数据库pubs中，表titles和表publishers都有一个名为pub_id的字段。如果你想得到书名及其出版商的一个列表，你可以使用如下的语句：
     SELECT   title , pub_name  FROM  titles , publishers
                                WHERE titles . pub_id = publishers . pub_id

    当然，如果一本书是由两个出版商联合出版的，那么你需要第三个表来代表这种关系。
    通常，当你予先知道两个表的字段间存在“多对多”关系时，就使用第三个表来关联这两个表。反之，如果两个表的字段间只有“一对一”或“一对多”关系，你可以使用公共字段来关联它门。

操作字段
    通常，当你从一个表中取出字段值时，该值与创建该表时所定义的字段名联系在一起。如果你从表authors中选择所有的作者名字，所有的值将会与字段名au_lname相联系。但是在某些情况下，你需要对字段名进行操作。在SELECT语句中，你可以在缺省字段名后面仅跟一个新名字来取代它。例如，可以用一个更直观易读的名字Author Last Name来代替字段名au_lname：

     SELECT  au_lname  " Author Last Name "  FROM authors

    当这个SELECT语句执行时，来自字段au_lname的值会与“Author Last Name”相联系。查询结果可能是这样：
         
      Author Last Name 
      …………………………………………………………………… ..   
      White
      Green
      Carson
      O’Leary
      Straight 
      …
       ( 23  row ( s )  affected )
 
注意字段标题不再是au_lname，而是被Author Last Name所取代。
    你也可以通过执行运算，来操作从一个表返回的字段值。例如，如果你想把表titles中的所有书的价格加倍，你可以使用下面的SELECT语句：
    
     SELECT   price* 2  FROM titles       

    当这个查询执行时，每本书的价格从表中取出时都会加倍。但是，通过这种途径操作字段不会改变存储在表中的书价。对字段的运算只会影响SELECT语句的输出，而不会影响表中的数据。为了同时显示书的原始价格和涨价后的新价格，你可以使用下面的查询：

SELECT   price   " Original  price " ,  price* 2    " New price "  FROM  titles

    当数据从表titles中取出时，原始价格显示在标题Original price下面，加倍后的价格显示在标题New price下面。结果可能是这样：
          
      original  price         new  price
      ……………………………………………………………… .
19.99      39.98
11.95                   23.90  
2.99      5.98
19.99      39.98
…
( 18  row ( s )   affected )
        
    你可以使用大多数标准的数学运算符来操作字段值，如加（ + ），减（-），乘（*）和除（ / ）。你也可以一次对多个字段进行运算，例如：

      SELECT   price*ytd_sales  " total revenue "  FROM titles

    在这个例子中，通过把价格与销售量相乘，计算出了每种书的总销售额。这个SELECT语句的结果将是这样的：
       
      total  revenue
      …………………………………………… ..
       81 , 859 , 05
       46 , 318 , 20
       55 , 978 , 78
       81 , 859 , 05
       40 , 619 , 68
      …
       ( 18  row ( s )   affected )
  
    最后，你还可以使用连接运算符（它看起来像个加号）来连接两个字符型字段：

       SELECT   au_fname + "   " + au_lname    " author name "  FROM authors

    在这个例子中，你把字段au_fname和字段au_lname粘贴在一起，中间用一个逗号 隔开，并把查询结果的标题指定为author name。这个语句的执行结果将是这样的：

       author  names
       …………………………………………………………
       Johnson White
       Marjorie Green
       Cheryl  Carson
      Michael O’Leary
      Dean Straight
      …
       ( 23  row ( s )   affected )
 
    可以看到，SQL为你提供了对查询结果的许多控制。你应该在ASP编程过程中充分利用这些优点。使用SQL来操作查询结果几乎总是比使用有同样作用的脚本效率更高。

排序查询结果
    本章的介绍中曾强调过，SQL表没有内在的顺序。例如，从一个表中取第二个记录是没有意义的。从SQL的角度看来，没有一个记录在任何其他记录之前。
然而，你可以操纵一个SQL查询结果的顺序。在缺省情况下，当记录从表中取出时，记录不以特定的顺序出现。例如，当从表authors中取出字段au_lname时，查询结果显示成这样：

       au_lname
       …………………………………… .
       White
       Green
       Carson
       O’Leary
       Straight
       …
        ( 23  row ( s )  affected )
   
    看一列没有特定顺序的名字是很不方便的。如果把这些名字按字母顺序排列，读起来就会容易得多。通过使用ORDER BY子句，你可以强制一个查询结果按升序排列，就像这样：

     SELECT  au_lname FROM  authors  ORDER  BY  au_lname

    当这个SELECT语句执行时，作者名字的显示将按字母顺序排列。ORDER BY子句将作者名字按升序排列。
    你也可以同时对多个列使用ORDER BY子句。例如，如果你想同时按升序显示字段au_lname和字段au_fname，你需要对两个字段都进行排序：

     SELECT  au_lname , au_fname FROM authors ORDER BY au_lname  , au_fname

    这个查询首先把结果按au_lname字段进行排序，然后按字段au_fname排序。记录将按如下的顺序取出：

      au_lname                         au_fname
      …………………………………………………………………… .
      Bennet                           Abraham
      Ringer                            Albert
      Ringer                            Anne
      Smith                             Meander
      …
       ( 23  row ( s )  affected )

    注意有两个作者有相同的名字Ringer。名为Albert Ringer的作者出现名为Anne Ringer的作者之前，这是因为姓Albert按字母顺序应排在姓Anne之前。
如果你想把查询结果按相反的顺序排列，你可以使用关键字DESC。关键字DESC把查询结果按降序排列，如下例所示：
     
        SELECT   au_lname , au_fname  FROM authors 
              WHERE au_lname = ”Ringer” ORDER BY  au_lname  , au_fname  DESC

    这个查询从表authors中取出所有名字为Ringer的作者记录。ORDER BY子句根据作者的名字和姓，将查询结果按降序排列。结果是这样的：

      au_lname                          au_fname
      ……………………………………………………………………………………… .
      Ringer                             Anne
      Ringer                             Albert
       ( 2  row ( s )   affectec )

    注意在这个表中，姓Anne出现在姓Albert之前。作者名字按降序显示。
你也可以按数值型字段对一个查询结果进行排序。例如，如果你想按降序取出所有书的价格，你可以使用如下的SQL查询：

        SELECT   price  FROM titles  ORDER BY  price  DESC

    这个SELECT语句从表中取出所有书的价格，显示结果时，价格低的书先显示，价格高的书后显示。

    警告：
    不是特别需要时，不要对查询结果进行排序，因为服务器完成这项工作要费些力气。这意味着带有ORDER BY 子句的SELECT语句执行起来比一般的SELECT语句花的时间长。

取出互不相同的记录
    一个表有可能在同一列中有重复的值。例如，数据库pubs的表authors中有两个作者的名字是Ringer。如果你从这个表中取出所有的名字，名字Ringer将会显示两次。
    在特定情况下，你可能只有兴趣从一个表中取出互不相同的值。如果一个字段有重复的值，你也许希望每个值只被选取一次，你可以使用关键字DISTINCT来做到这一点：

     SELCET  DISTINCT au_lname  FROM  authors   WHERE  au_lname = " Ringer "

    当这个SELECT语句执行时，只返回一个记录。通过在SELECT语句中包含关键字DISTINCT，你可以删除所有重复的值。例如，假设有一个关于新闻组信息发布的表，你想取出所有曾在这个新闻组中发布信息的人的名字，那么你可以使用关键字DISTINCT。每个用户的名字只取一次——尽管有的用户发布了不止一篇信息。
    警告：
    如同ORDER BY子句一样，强制服务器返回互不相同的值也会增加运行开销。福气不得不花费一些时间来完成这项工作。因此，不是必须的时候不要使用关键字DISTINCT。

创建新表
    前面说过，数据库中的所有数据存储在表中。数据表包括行和列。列决定了表中数据的类型。行包含了实际的数据。
    例如，数据库pubs中的表authors有九个字段。其中的一个字段名为为au_lname，这个字段被用来存储作者的名字信息。每次向这个表中添加新作者时，作者名字就被添加到这个字段，产生一条新记录。
    通过定义字段，你可以创建一个新表。每个字段有一个名字和一个特定的数据类型（数据类型在后面的“字段类型”一节中讲述），例如字段au_lname存储的是字符型数据。一个字段也可以存储其它类型的数据。
    使用SQL Sever，创建一个新表的方法是很多的。你可以可执行一个SQL语句或使用SQL事务管理器（SQL Enterprise Manager）来创建一个新表。在下一节里，你将学会如何用SQL语句来创建一个新表。

用SQL创建新表
    注意：
    如果你还没有建立自己的数据库，现在就跳回到第三章创建这个库。你绝不能向master , tempdb或任何其他任何系统数据库中添加数据。

    从SQL Sever程序组（在任务栏中）中启动ISQL / w程序。出现查询窗口后，从窗口顶部的下拉列表中选择你在第三章所创建的数据库。下一步，在查询窗口中键入下面的SQL语句，单击执行查询按钮，执行这个语句：

      CREATE  TABLE  guestbook  ( visitor VARCHAR ( 40 ), comments TEXT , entrydate 
                                         DATETIME )

如果一切正常，你会在结果窗口中看到如下的文字（如果出现异常，请参阅第三章）：

      This  command  dit  not   return  data  , and it did  not   return  any rows       

祝贺你，你已经建立了你的第一个表！
    你所创建的表名为guestbook，你可以使用这个表来存储来字你站点访问者的信息。你是用REEATE TABLE语句创建的这个表，这个语句有两部分：第一部份指定表的名子；第二部份是括在括号中的各字段的名称和属性，相互之间用逗号隔开。
    表guestbook有三个字段：visitor , comments 和entrydate。visitor字段存储访问者的名字，comments字段存储访问者对你站点的意见，entrydate字段存储访问者访问你站点的日期和时间。
    注意每个字段名后面都跟有一个专门的表达式。例如，字段名comments后面跟有表达式TEXT。这个表达式指定了字段的数据类型。数据类型决定了一个字段可以存储什么样的数据。因为字段comments包含文本信息，其数据类型定义为文本型。
    字段有许多不同的数据类型。下一小节讲述SQL所支持的一些重要的数据类型。

字段类型
    不同的字段类型用来存放不同类型的数据。创建和使用表时，更你应该理解五种常用的字段类型：字符型，文本型，数值型，逻辑性和日期型。

字符型数据
    字符型数据非常有用。当你需要存储短的字符串信息时，你总是要用到字符型数据。例如，你可以把从HTML form的文本框中搜集到的信息放在字符型字段中。
    要建立一个字段用来存放可变长度的字符串信息，你可以使用表达式 VARCHAR。考虑你前面创建的表guestbook：

     CREATE  TABLE  guestbook  ( visitor VARCHAR ( 40 ), comments TEXT , entrydate 
                                         DATETIME )

    在这个例子中，字段visitor的数据类型为VARCHAR。注意跟在数据类型后面的括号中的数字。这个数字指定了这个字段所允许存放的字符串的最大长度。在这个例子中，字段visitor能存放的字符串最长为四十个字符。如果名字太长，字符串会被截断，只保留四十个字符。
    VARCHAR类型可以存储的字符串最长为255个字符。要存储更长的字符串数据，可以使用文本型数据（下一节中讲述）。
    另一种字符型数据用来存储固定长度的字符数据。下面是一个使用这种数据类型的例子：

     CREATE TABLE guestbook  ( visitor CHAR ( 40 ), comments TEXT , entrydate 
                                         DATETIME )

    在这个例子中，字段visitor被用来存储四十个字符的固定长度字符串。表达式CHAR指定了这个字段应该是固定长度的字符串。
    VARCHAR型和CHAR型数据的这个差别是细微的，但是非常重要。假如你向一个长度为四十个字符的VARCHAR型字段中输入数据Bill Gates。当你以后从这个字段中取出此数据时，你取出的数据其长度为十个字符——字符串Bill Gates的长度。
    现在假如你把字符串输入一个长度为四十个字符的CHAR型字段中，那么当你取出数据时，所取出的数据长度将是四十个字符。字符串的后面会被附加多余的空格。
    当你建立自己的站点时，你会发现使用VARCHAR型字段要比CHAR型字段方便的多。使用VARCHAR型字段时，你不需要为剪掉你数据中多余的空格而操心。
    VARCHAR型字段的另一个突出的好处是它可以比CHAR型字段占用更少的内存和硬盘空间。当你的数据库很大时，这种内存和磁盘空间的节省会变得非常重要。

文本型数据
    字符型数据限制了字符串的长度不能超过２55个字符。而使用文本型数据，你可以存放超过二十亿个字符的字符串。当你需要存储大串的字符时，应该使用文本型数据。
    这里有一个使用文本型数据的例子：

    CREATE  TABLE  guestbook  ( visitor VARCHAR ( 40 ), comments TEXT , entrydate 
                                         DATETIME )

    在这个例子中，字段comments被用来存放访问者对你站点的意见。注意文本型数据没有长度，而上一节中所讲的字符型数据是有长度的。一个文本型字段中的数据通常要么为空，要么很大。
    当你从HTML form的多行文本编辑框（TEXTAREA）中收集数据时，你应该把收集的信息存储于文本型字段中。但是，无论何时，只要你能避免使用文本型字段，你就应该不适用它。文本型字段既大且慢，滥用文本型字段会使服务器速度变慢。文本型字段还会吃掉大量的磁盘空间。
    警告：
    一旦你向文本型字段中输入了任何数据（甚至是空值），就会有2K的空间被自动分配给该数据。除非删除该记录，否则你无法收回这部分存储空间。

数值型数据
    SQL Sever支持许多种不同的数值型数据。你可以存储整数、小数、和钱数。
    通常，当你需要在表中的存放数字时，你要使用整型（INT）数据。INT型数据的表数范围是从- 2 ， 147 ， 483 ，647到2， 147 ， 483 ，647的整数。下面是一个如何使用INT型数据的例子：

       CREATE  TABLE  visitlog  ( visitor VARCHAR ( 40 ), numvisits  INT )

    这个表可以用来记录你站点被访问的次数。只要没有人访问你的站点超过2， 147 ， 483 ，647次，nubvisits字段就可以存储访问次数。
    为了节省内存空间，你可以使用SMALLINT型数据。SMALLINT 型数据可以存储从-32768到32768的整数。这种数据类型的使用方法与INT型完全相同。
    最后，如果你实在需要节省空间，你可以使用TINYINT型数据。同样，这种类型的使用方法也与INT型相同，不同的是这种类型的字段只能存储从０到255的整数。TINYINT型字段不能用来存储负数。
    通常，为了节省空间，应该尽可能的使用最小的整型数据。一个TINYINT型数据只占用一个字节；一个INT型数据占用四个字节。这看起来似乎差别不大，但是在比较大的表中，字节数的增长是很快的。另一方面，一旦你已经创建了一个字段，要修改它是很困难的。因此，为安全起见，你应该预测以下，一个字段所需要存储的数值最大有可能是多大，然后选择适当的数据类型。
    为了能对字段所存放的数据有更多的控制，你可以使用NUMERIC型数据来同时表示一个数的整数部分和小数部分。NUMERIC型数据使你能表示非常大的数——比INT型数据要大得多。一个NUMERIC型字段可以存储从-1038到1038范围内的数。NUMERIC型数据还使你能表示有小数部分的数。例如，你可以在NUMERIC型字段中存储小数3 . 14 。
    当定义一个NUMERIC型字段时，你需要同时指定整数部分的大小和小数部分的大小。这里有一个使用这种数据类型的例子：
     
       CREATE TABLE  numeric_data  ( bignumber  NUMERIC ( 28 , 0 ),  
                                   fraction    NUMERIC  ( 5 , 4 )   )

    当这个语句执行时，将创建一个名为numeric_data的包含两个字段的表。字段bignumber可以存储直到28位的整数。字段fraction可以存储有五位整数部分和四位小数部分的小数。
    一个NUMERIC型数据的整数部分最大只能有28位，小数部分的位数必须小于或等于整数部分的位数，小数部分可以是零。
    你可以使用INT型或NUMERIC型数据来存储钱数。但是，专门有另外两种数据类型用于此目的。如果你希望你的网点能挣很多钱，你可以使用MONEY型数据。如果你的野心不大，你可以使用SMALLMONEY型数据。MONEY型数据可以存储从- 922 ， 337 ， 203 ， 685 ， 477 . 5808到922， 337 ， 203 ， 685 ， 477 . 5807的钱数。如果你需要存储比这还大的金额，你可以使用NUMERIC型数据。
    SMALLMONEY型数据只能存储从- 214 ， 748 . 3648到214， 748.3647  的钱数。同样，如果可以的话，你应该用SMALLMONEY型来代替MONEY型数据，以节省空间。下面的例子显示了如何使用这两种表示钱的数据类型：

      CREATE TABLE products  ( product VARCHAR ( 40 ), price  MONEY ,
                               Discount_price SMALLMONEY )       

    这个表可以用来存储商品的折扣和普通售价。字段price 的数据类型是MONEY，字段discount_price的数据类型是SMALLMONEY。

存储逻辑值
    如果你使用复选框（CHECKBOX）从网页中搜集信息，你可以把此信息存储在BIT型字段中。BIT型字段只能取两个值：0或1。这里有一个如何使用这种字段的例子：

    CREATE  TABLE  opinion  ( visitor VARCHAR ( 40 ), good BIT )

    这个表可以用来存放对你的网点进行民意调查所得的信息。访问者可以投票表示他们是否喜欢你的网点。如果他们投YES，就在BIT型字段中存入1。反之，如果他们投NO，就在字段中存入0（在下一章里，你将学会如何计算投票）。
    当心，在你创建好一个表之后，你不能向表中添加BIT型字段。如果你打算在一个表中包含BIT型字段，你必须在创建表时完成。

存储日期和时间
    当你建立一个网点时，你也许需要记录在一段时间内的访问者数量。为了能够存储日期和时间，你需要使用DATETIME型数据，如下例所示：

    CREATE  TABL visitorlog (  visitor VARCHAR  ( 40 ),  arrivaltime DATETIME  ,
                               departuretime  DATETIME )

    这个表可以用来记录访问者进入和离开你网点的时间和日期。一个DATETIME型的字段可以存储的日期范围是从１７５３年１月１日第一毫秒到9999年12月31日最后一毫秒。
    如果你不需要覆盖这么大范围的日期和时间，你可以使用SMALLDATETIME型数据。它与DATETIME型数据同样使用，只不过它能表示的日期和时间范围比DATETIME型数据小，而且不如DATETIME型数据精确。一个SMALLDATETIME型的字段能够存储从１９00年１月１日到２０７９年６月６日的日期，它只能精确到秒。
    DATETIME型字段在你输入日期和时间之前并不包含实际的数据，认识这一点是重要的。在下一章，你将学习怎样使用大量的SQL函数来读取和操作日期和时间（参见下面的“缺省值”一节）。你也可以在VBScript和JScript 中使用日期和时间函数来向一个DATETIME型字段中输入日期和时间。

字段属性
    上一节介绍了如何建立包含不同类型字段的表。在这一节中，你将学会如何使用字段的三个属性。这些属性允许你控制空值，缺省值和标识值。

允许和禁止空值
    大多数字段可以接受空值（NULL）。当一个字段接受了空值后，如果你不改变它，它将一直保持空值。空值（NULL）和零是不同的，严格的说，空值表示没有任何值。
为了允许一个字段接受空值，你要在字段定义的后面使用表达式NULL。例如，下面的表中两个字段都允许接受空值：
      
      CREATE TABLE empty  ( empty1 CHAR  ( 40 )  NULL , empty2 INT NULL (
   
    注意：
    BIT型数据不能是空值。一个这种类型的字段必须取0或者１。

    有时你需要禁止一个字段使用空值。例如，假设有一个表存储着信用卡号码和信用卡有效日期，你不会希望有人输入一个信用卡号码但不输入有效日期。为了强制两个字段都输入数据，你可以用下面的方法建立这个表：
      
      CREATE  TABLE creditcards  ( creditcard_number CHAR ( 20 )   NOT  NULL ,
                                 Creditcard_expire  DATETIME   NOT  NULL )
    注意字段定义的后面跟有表达式NOT NULL。通过包含表达式NOT NULL，你可以禁止任何人只在一个字段中插入数据，而不输入另一个字段的数据。
    你将会发现，在你建设自己的网点过程中，这种禁止空值的能力是非常有用的。如果你指定一个字段不能接受空值，那么当你试图输入一个空值时，会有错误警告。这些错误警告可以为程序调试提供有价值的线索。

缺省值
    假设有一个存储地址信息的表，这个表的字段包括街道、城市、州、邮政编码和国家。如果你预计地址的大部分是在美国，你可以把这个值作为country字段的缺省值。
    为了在创建一个表时指定缺省值，你可以使用表达式DEFAULT。请看下面这个在创建表时使用缺省值的例子：
       
      CREATE TABLE addresses  ( street VARCHAR ( 60 )   NULL ,
                               city VARCHAR ( 40 )  NULL ,
                               state VARCHAR ( 20 )  NULL
                               zip  VARCHAR ( 20 )  NULL ,
                                country  VARCHAR ( 30 )  DEFAULT  ‘USA’ )

    在这个例子中，字段country的缺省值被指定为美国。注意单引号的使用，引号指明这是字符型数据。为了给非字符型的字段指定缺省值，不要把该值扩在引号中：
    
      CREATE TABLE orders ( price  MONEY DEFAULT  $ 38.00 ,
                           quantity INT DEFAULT  50 ,
                            entrydate DATETIME DEFAULT GETDATE ())

    在这个CREATE TABLE语句中，每个字段都指定了一个缺省值。注意DATETIME型字段entrydate所指定的缺省值 , 该缺省值是函数Getdate () 的返回值 , 该函数返回当前的日期和时间。

标识字段
    每个表可以有一个也只能有一个标识字段。一个标识字段是唯一标识表中每条记录的特殊字段。例如，数据库pubs中的表jobs包含了一个唯一标识每个工作标识字段： 
    
      job_id   job_desc
      …………………………………………………………… .
1     New Hire Job  not  specified
2     Chief Executive officer
3     Bushness Operations Manager
4     Chief Financial Officier
5     Publisher
 
    字段job_id为每个工作提供了唯一的一个数字。如果你决定增加一个新工作，新增记录的job_id字段会被自动赋给一个新的唯一值。
    为了建立一个标识字段，你只需在字段定义后面加上表达式IDENTITY即可。你只能把NUMERIC型或INT型字段设为标识字段，这里有一个例子：
  
       CREATE TABLE visitorID  ( theID NUBERIC ( 18 )  IDENTITY , name VARCHAR ( 40 ))
   
    这个语句所创建的表包含一个名为theid的标识字段。每当一个新的访问者名字添加到这个表中时，这个字段就被自动赋给一个新值。你可以用这个表为你的站点的每一个用户提供唯一标识。
    技巧：
    建立一个标示字段时，注意使用足够大的数据类型。例如你使用TINYINT型数据，那么你只能向表中添加255个记录。如果你预计一个表可能会变得很大，你应该使用NUMERIC型数据。

    标识字段的存在会使你想尝试许多不可能的事情。例如，你也许想利用标识字段来对记录进行基于它们在表中位置的运算。你应该抛弃这种意图。每个记录的标识字段的值是互不相同的，但是，这并不禁止一个标识字段的标识数字之间存在间隔。例如，你永远不要试图利用一个表的标识字段来取出表中的前十个记录。这种操作会导致失败，比如说６号记录和7号记录根本不存在。

使用SQL事务管理器创建新表
    你可以使用前面几节所讲的方法创建新表。但是，使用事务管理器创建新表会更容易。这一节介绍如何使用这个程序创建新表。
    从任务栏的SQL Sever程序组中选择SQL Enterprise Manager，启动该程序，你会看到如图10 . 4所示的窗口。浏览服务管理器窗口中的树形结构，选择名为Database的文件夹。打开文件夹Database后，选择你在第三章中所建立的数据库。
    
注意：
    如果你还没有创建自己的数据库，回到第三章创建它。你决不要向master , tempdb或任何其它系统数据库中添加数据。
  
    在选择了数据库之后，你会看到一个名为Group / users的文件夹和一个名为objects的文件夹。打开文件夹objects，你会看到许多文件夹，其中一个名为Tables。用右键单击文件夹Tables并选择New table，就会出现如图10 . 5所示的窗口。
    你可以使用Manager  Tables窗口来创建一个新表。Manager Tables窗口有７个列：Key , Column , Name , Datatype , Size , Nulls和Default。Manager Tables窗口中的每一行标明表中一个字段的信息。

        图10 . 4
            10.5

    要建立一个新表，你至少要输入一行信息。在名为Column  Name的列下面键入mycolumn。下一步，选择Datatype列，并从下拉列表中选择CHAR。当你在这两个列中输入信息后，窗口将是如图10 . 6所示的样子。

 图10 . 6

    你已经建立了一个只有一个字段的简单的表。单击保存按扭保存这个新表。当要求你输入新表的名字时，输入mytable并单击OK。现在这个表已经保存到了你的数据库中。
如果你打开服务管理器窗口中的文件夹Tables，你会看到你所建立的新表被列出。你可以双击该表的图表来编辑它，这时Manager Tables窗口会重新出现，你可以增加新的字段并重新保存。
    用SQL事务管理器可以做的工作，你都可以用SQL语句来实现。但是，事务管理器使得建表过程变得更加简单。

向表中添加数据
    下一章将讨论如何使用SQL向一个表中插入数据。但是，如果你需要向一个表中添加许多条记录，使用SQL语句输入数据是很不方便的。幸运的是，Microsoft  SQL Sever带有一个称为Microsoft Query 的客户端应用程序，这个程序使得向表中添加数据变得容易了。
    启动位于任务栏SQL Sever程序组中的Microsoft Query程序。从窗口顶部的菜单中选择File|New Query。这时会显示一个Select Data Source对话框（见图10 . 7 ）。选择你的数据源名字并单击Use。
    
图10。 7

    输入你的登录帐号和密码后，程序要求你选择一个表和一个数据库。选择你在上一节中所建立的表（mytable ），单击按钮Add，然后单击按钮Close关闭该对话框。
    在窗口的左上角会出现一个对话框，框中是取自表mytable的一列字段名。你可以双击任何一个字段，把它添加到主窗口中。如果你双击星号（*）字符，所有的字段都会被添加到主窗口中。
    如果你的表中有记录，它们现在已经出现在主窗口的字段标题下面了。但是，因为你刚刚建立了这个表，表还是空的。要添加新记录，选择Records|Allow Editing，主窗口中就会出现一条新记录。输入一行数据完成这个记录，就向表中添加了一条新记录。
       
                图10。 8

    当你转到下一条新记录时，你向上一条记录中输入的值会自动被保存。如果你需要，你可以用Microsoft Query 向表中输入几百条记录。

删除和修改表
    你应该在建立表之前仔细设计它们，因为你在改变一个已经存在的表时会受到很大的限制。例如，一旦已经建立了一个表，你就不能删除表中的字段或者改变字段的数据类型。在这种情况你所能做的是删除这个表，然后重头开始（参见第十一章“中级SQL”中的“使用SQL创建记录和表”一节）。
    要删除一个表，你可以使用SQL语句DROP TABLE。例如，又从数据库中彻底删除表mytable，你要使用如下的语句：
    
       DROP TABLE mytable

    警告：
    使用DROP TABLE命令时一定要小心。一旦一个表被删除之后，你将无法恢复它。

    当你建设一个站点时，你很可能需要向数据库中输入测试数据。而当你准备向世界提供你的网点时，你会想清空表中的这些测试信息。如果你想清除表中的所有数据但不删除这个表，你可以使用TRUNCATE TABLE语句。例如，下面的这个SQL语句从表mytable中删除所有数据：

        TRUNCATE TABLE mytable

    虽然你不能删除和修改已经存在的字段，但你可以增加新字段。最容易的实现方法是使用SQL事务管理器中的Manager Tables窗口。你也可以使用SQL语句ALTER TABLE。下面是一个如何使用这种语句的例子：

     ALTER TABLE mytable ADD mynewcolumn INT NULL

    这个语句向表mytable中增加了一个新字段mynewcolumn。当你增加新字段时，你必须允许它接受空值，因为表中原来可能已经有了许多记录。

总结
    这一章向你介绍了SQL。使用SQL，你可以操作Microsoft  SQL Sever数据库。你已经学会了使用SELECT语句从数据库中取出数据，你还学会了怎样使用CREATE TABLE语句和SQL事务管理器来创建新表。最后，你学会了如何指明一系列重要的字段属性。
下一章将介绍如何使用索引来增强SQL查询的操作。还将通过许多其它的SQL语句和函数，使你的SQL知识得到进一步扩充。

 

第十一章  中级SQL

               本章内容
               ■创建索引
               ■SQL核心语句
               ■集合函数
■    其它常用的SQL表达式，
  函数，和过程

 

    第十章“SQL基础”向你初步介绍了SQL。你学会了如何用SELECT语句进行查询，你还学会了如何建立自己的表。在这一章里，你将加深你的SQL知识。你将学习如何建立索引来加快查询速度。你还将学会如果用更多的SQL语句和函数来操作表中的数据。

建立索引
    假设你想找到本书中的某一个句子。你可以一页一页地逐页搜索，但这会花很多时间。而通过使用本书的索引，你可以很快地找到你要搜索的主题。
    表的索引与附在一本书后面的索引非常相似。它可以极大地提高查询的速度。对一个较大的表来说，通过加索引，一个通常要花费几个小时来完成的查询只要几分钟就可以完成。因此没有理由对需要频繁查询的表增加索引。
    注意：
    当你的内存容量或硬盘空间不足时，也许你不想给一个表增加索引。对于包含索引的数据库，SQL Sever需要一个可观的额外空间。例如，要建立一个聚簇索引，需要大约１ . ２倍于数据大小的空间。要看一看一个表的索引在数据库中所占的空间大小，你可以使用系统存储过程sp_spaceused，对象名指定为被索引的表名。

聚簇索引和非聚簇索引
    假设你已经通过本书的索引找到了一个句子所在的页码。一旦已经知道了页码后，你很可能漫无目的翻寻这本书，直至找到正确的页码。通过随机的翻寻，你最终可以到达正确的页码。但是，有一种找到页码的更有效的方法。
    首先，把书翻到大概一半的地方，如果要找的页码比半本书处的页码小，就书翻到四分之一处，否则，就把书翻到四分之三的地方。通过这种方法，你可以继续把书分成更小的部分，直至找到正确的页码附近。这是找到书页的非常有效的一种方法。
SQL Sever的表索引以类似的方式工作。一个表索引由一组页组成，这些页构成了一个树形结构。根页通过指向另外两个页，把一个表的记录从逻辑上分成和两个部分。而根页所指向的两个页又分别把记录分割成更小的部分。每个页都把记录分成更小的分割，直至到达叶级页。
    索引有两种类型：聚簇索引和非聚簇索引。在聚簇索引中，索引树的叶级页包含实际的数据：记录的索引顺序与物理顺序相同。在非聚簇索引中，叶级页指向表中的记录：记录的物理顺序与逻辑顺序没有必然的联系。
    聚簇索引非常象目录表，目录表的顺序与实际的页码顺序是一致的。非聚簇索引则更象书的标准索引表，索引表中的顺序通常与实际的页码顺序是不一致的。一本书也许有多个索引。例如，它也许同时有主题索引和作者索引。同样，一个表可以有多个非聚簇索引。
    通常情况下，你使用的是聚簇索引，但是你应该对两种类型索引的优缺点都有所理解。
    每个表只能有一个聚簇索引，因为一个表中的记录只能以一种物理顺序存放。通常你要对一个表按照标识字段建立聚簇索引。但是，你也可以对其它类型的字段建立聚簇索引，如字符型，数值型和日期时间型字段。
    从建立了聚簇索引的表中取出数据要比建立了非聚簇索引的表快。当你需要取出一定范围内的数据时，用聚簇索引也比用非聚簇索引好。例如，假设你用一个表来记录访问者在你网点上的活动。如果你想取出在一定时间段内的登录信息，你应该对这个表的DATETIME型字段建立聚簇索引。
    对聚簇索引的主要限制是每个表只能建立一个聚簇索引。但是，一个表可以有不止一个非聚簇索引。实际上，对每个表你最多可以建立249个非聚簇索引。你也可以对一个表同时建立聚簇索引和非聚簇索引。
    假如你不仅想根据日期，而且想根据用户名从你的网点活动日志中取数据。在这种情况下，同时建立一个聚簇索引和非聚簇索引是有效的。你可以对日期时间字段建立聚簇索引，对用户名字段建立非聚簇索引。如果你发现你需要更多的索引方式，你可以增加更多的非聚簇索引。
    非聚簇索引需要大量的硬盘空间和内存。另外，虽然非聚簇索引可以提高从表中 取数据的速度，它也会降低向表中插入和更新数据的速度。每当你改变了一个建立了非聚簇索引的表中的数据时，必须同时更新索引。因此你对一个表建立非聚簇索引时要慎重考虑。如果你预计一个表需要频繁地更新数据，那么不要对它建立太多非聚簇索引。另外，如果硬盘和内存空间有限，也应该限制使用非聚簇索引的数量。

索引属性
    这两种类型的索引都有两个重要属性：你可以用两者中任一种类型同时对多个字段建立索引（复合索引）；两种类型的索引都可以指定为唯一索引。
    你可以对多个字段建立一个复合索引，甚至是复合的聚簇索引。假如有一个表记录了你的网点访问者的姓和名字。如果你希望根据完整姓名从表中取数据，你需要建立一个同时对姓字段和名字字段进行的索引。这和分别对两个字段建立单独的索引是不同的。当你希望同时对不止一个字段进行查询时，你应该建立一个对多个字段的索引。如果你希望对各个字段进行分别查询，你应该对各字段建立独立的索引。
    两种类型的索引都可以被指定为唯一索引。如果对一个字段建立了唯一索引，你将不能向这个字段输入重复的值。一个标识字段会自动成为唯一值字段，但你也可以对其它类型的字段建立唯一索引。假设你用一个表来保存你的网点的用户密码，你当然不希望两个用户有相同的密码。通过强制一个字段成为唯一值字段，你可以防止这种情况的发生。

用SQL建立索引
    为了给一个表建立索引，启动任务栏SQL Sever程序组中的ISQL / w程序。进入查询窗口后，输入下面的语句：

       CREATE INDEX mycolumn_index  ON  mytable  ( myclumn )

    这个语句建立了一个名为mycolumn_index的索引。你可以给一个索引起任何名字，但你应该在索引名中包含所索引的字段名，这对你将来弄清楚建立该索引的意图是有帮助的。
    注意：
    在本书中你执行任何SQL语句，都会收到如下的信息：
    This   command  did  not   return  data , and it did  not   return  any rows
这说明该语句执行成功了。
索引mycolumn_index对表mytable的mycolumn字段进行。这是个非聚簇索引，也是个非唯一索引。（这是一个索引的缺省属性）
如果你需要改变一个索引的类型，你必须删除原来的索引并重建 一个。建立了一个索引后，你可以用下面的SQL语句删除它：

DROP INDEX mytable . mycolumn_index

注意在DROP INDEX 语句中你要包含表的名字。在这个例子中，你删除的索引是mycolumn_index，它是表mytable的索引。
要建立一个聚簇索引，可以使用关键字CLUSTERED。）记住一个表只能有一个聚簇索引。（这里有一个如何对一个表建立聚簇索引的例子：

CREATE CLUSTERED INDEX mycolumn_clust_index  ON  mytable ( mycolumn )

如果表中有重复的记录，当你试图用这个语句建立索引时，会出现错误。但是有重复记录的表也可以建立索引；你只要使用关键字ALLOW_DUP_ROW把这一点告诉SQL Sever即可：
 
CREATE CLUSTERED INDEX mycolumn_cindex  ON  mytable ( mycolumn )
                     WITH ALLOW_DUP_ROW

这个语句建立了一个允许重复记录的聚簇索引。你应该尽量避免在一个表中出现重复记录，但是，如果已经出现了，你可以使用这种方法。
要对一个表建立唯一索引，可以使用关键字UNIQUE。对聚簇索引和非聚簇索引都可以使用这个关键字。这里有一个例子：

CREATE UNIQUE COUSTERED INDEX myclumn_cindex  ON  mytable ( mycolumn )

这是你将经常使用的索引建立语句。无论何时，只要可以，你应该尽量对一个对一个表建立唯一聚簇索引来增强查询操作。
最后，要建立一个对多个字段的索引──复合索引──在索引建立语句中同时包含多个字段名。下面的例子对firstname和lastname两个字段建立索引：

CREATE INDEX name_index  ON  username ( firstname , lastname )

这个例子对两个字段建立了单个索引。在一个复合索引中，你最多可以对16个字段进行索引。

用事务管理器建立索引
用事务管理器建立索引比用SQL语句容易的多。使用事务管理器，你可以看到已经建立的索引的列表，并可以通过图形界面选择索引选项。
使用事务管理器你可以用两种方式建立索引：使用Manage Tables窗口或使用Manage Indexes窗口。
    要用Manage Tables 窗口建立一个新索引，单击按钮Advanced Options ( 它看起来象一个前面有一加号的表 ) 。这样就打开了Advanced Options对话框。这个对话框有一部分标名为Primary Key（见图11 . 1 ）。
 
图11。 1

要建立一个新索引，从下拉列表中选择你想对之建立索引的字段名。如果你想建立一个对多字段的索引，你可以选择多个字段名。你还可以选择索引是聚簇的还是非聚簇的。在保存表信息后，索引会自动被建立。在Manage Tables窗口中的字段名旁边，会出现一把钥匙。
你已经为你的表建立了“主索引”。主索引必须对不包含空值的字段建立。另外，主索引强制一个字段成为唯一值字段。
要建立没有这些限制的索引，你需要使用Manage Indexes窗口。从菜单中选择Manage|Indexes，打开Manage Indexes 窗口。在Manage Indexes 窗口中，你可以通过下拉框选择表和特定的索引。（见图11 . 2 ）。要建立一个新索引，从Index下拉框中选择New Index . ，然后就可以选择要对之建立索引的字段。单击按钮Add，把字段加人到索引中。

图11。 2

你可以为你的索引选择许多不同的选项。例如，你可以选择该索引是聚簇的还是非聚簇的。你还可以指定该索引为唯一索引。设计好索引后，单击按钮Build，建立该索引。

注意：
唯一索引是指该字段不能有重复的值，而不是只能建立这一个索引。

SQL核心语句
在第十章，你学会了如何用SQL  SELECT  语句从一个表中取数据。但是，到现在为止，还没有讨论如何添加，修改或删除表中的数据。在这一节中，你将学习这些内容。

插入数据
向表中添加一个新记录，你要使用SQL INSERT 语句。这里有一个如何使用这种语句的例子：

     INSERT mytable  ( mycolumn )  VALUES  ( ‘some data’ )

这个语句把字符串’some data’插入表mytable的mycolumn字段中。将要被插入数据的字段的名字在第一个括号中指定，实际的数据在第二个括号中给出。
INSERT 语句的完整句法如下：

INSERT [INTO]  {table_name|view_name} [ ( column_list ) ] {DEFAULT VALUES |
                Values_list | select_statement}

如果一个表有多个字段，通过把字段名和字段值用逗号隔开，你可以向所有的字段中插入数据。假设表mytable有三个字段first_column , second_column , 和third_column。下面的INSERT语句添加了一条三个字段都有值的完整记录：

INSERT mytable  ( first_column , second_column , third_column )
        VALUES  ( ‘some data’ , ’some more data’ , ’yet more data’ )

注意：
你可以使用INSERT语句向文本型字段中插入数据。但是，如果你需要输入很长的字符串，你应该使用WRITETEXT语句。这部分内容对本书来说太高级了，因此不加讨论。要了解更多的信息，请参考Microsoft SQL Sever 的文档。

如果你在INSERT 语句中只指定两个字段和数据会怎么样呢？换句话说，你向一个表中插入一条新记录，但有一个字段没有提供数据。在这种情况下，有下面的四种可能：
■    如果该字段有一个缺省值，该值会被使用。例如，假设你插入新记录时没有给字段third_column提供数据，而这个字段有一个缺省值’some value’。在这种情况下，当新记录建立时会插入值’some value’。
■    如果该字段可以接受空值，而且没有缺省值，则会被插入空值。
■    如果该字段不能接受空值，而且没有缺省值，就会出现错误。你会收到错误信息：
The column in table mytable may  not  be null .
■    最后，如果该字段是一个标识字段，那么它会自动产生一个新值。当你向一个有标识字段的表中插入新记录时，只要忽略该字段，标识字段会给自己赋一个新值。

    注意：
    向一个有标识字段的表中插入新记录后，你可以用SQL变量@@identity来访问新记录
的标识字段的值。考虑如下的SQL语句：

     INSERT mytable  ( first_column )  VALUES ( ‘some value’ )

     INSERT anothertable ( another_first , another_second )
         VALUES ( @@identity , ’some value’ )

    如果表mytable有一个标识字段，该字段的值会被插入表anothertable的another_first字段。这是因为变量@@identity总是保存最后一次插入标识字段的值。
字段another_first应该与字段first_column有相同的数据类型。但是，字段another_first不能是应该标识字段。Another_first字段用来保存字段first_column的值。

删除记录
要从表中删除一个或多个记录，需要使用SQL DELETE语句。你可以给DELETE 语句提供WHERE 子句。WHERE子句用来选择要删除的记录。例如，下面的这个DELETE语句只删除字段first_column的值等于’Delete Me’的记录：
 
DELETE mytable WHERE first_column = ’Deltet Me’

DELETE 语句的完整句法如下：

       DELETE [FROM] {table_name|view_name}  [WHERE clause]

在SQL  SELECT  语句中可以使用的任何条件都可以在DELECT 语句的WHERE子句 中使用。例如，下面的这个DELETE语句只删除那些first_column字段的值为’goodbye’或second_column字段的值为’so long’的记录：

DELETE mytable WHERE first_column = ’goodby’ OR second_column = ’so long’

如果你不给DELETE 语句提供WHERE 子句，表中的所有记录都将被删除。你不应该有这种想法。如果你想删除应该表中的所有记录，应使用第十章所讲的TRUNCATE TABLE语句。

注意：
为什么要用TRUNCATE TABLE 语句代替DELETE语句？当你使用TRUNCATE TABLE语句时，记录的删除是不作记录的。也就是说，这意味着TRUNCATE TABLE 要比DELETE快得多。

更新记录
要修改表中已经存在的一条或多条记录，应使用SQL UPDATE语句。同DELETE语句一样，UPDATE语句可以使用WHERE子句来选择更新特定的记录。请看这个例子：

UPDATE mytable  SET  first_column = ’Updated!’ WHERE second_column = ’Update Me!’

这个UPDATE 语句更新所有second_column字段的值为’Update Me!’的记录。对所有被选中的记录，字段first_column的值被置为’Updated!’。
下面是UPDATE语句的完整句法：

UPDATE {table_name|view_name}  SET  [{table_name|view_name}]
       {column_list|variable_list|variable_and_column_list}
       [ , {column_list2|variable_list2|variable_and_column_list2}…
        [ , {column_listN|variable_listN|variable_and_column_listN}]]
        [WHERE clause]

注意：
你可以对文本型字段使用UPDATE语句。但是，如果你需要更新很长的字符串，应使用UPDATETEXT语句。这部分内容对本书来说太高级了，因此不加讨论。要了解更多的信息，请参考Microsoft SQL Sever 的文档。

    
如果你不提供WHERE子句，表中的所有记录都将被更新。有时这是有用的。例如，如果你想把表titles中的所有书的价格加倍，你可以使用如下的UPDATE 语句：
你也可以同时更新多个字段。例如，下面的UPDATE语句同时更新first_column , second_column , 和third_column这三个字段：

UPDATE mytable   SET   first_column = ’Updated!’
                     Second_column = ’Updated!’
                     Third_column = ’Updated!’
                 WHERE first_column = ’Update Me1’

技巧：
SQL忽略语句中多余的空格。你可以把SQL语句写成任何你最容易读的格式。


用SELECT 创建记录和表
你也许已经注意到，INSERT 语句与DELETE语句和UPDATE语句有一点不同，它一次只操作一个记录。然而，有一个方法可以使INSERT 语句一次添加多个记录。要作到这一点，你需要把INSERT 语句与SELECT 语句结合起来，象这样：

INSERT mytable  ( first_column , second_column )
SELECT  another_first , another_second
FROM anothertable
WHERE another_first = ’ Copy  Me!’

这个语句从anothertable拷贝记录到mytable . 只有表anothertable中字段another_first的值为’ Copy  Me！’的记录才被拷贝。
当为一个表中的记录建立备份时，这种形式的INSERT 语句是非常有用的。在删除一个表中的记录之前，你可以先用这种方法把它们拷贝到另一个表中。
如果你需要拷贝整个表，你可以使用SELECT  INTO 语句。例如，下面的语句创建了一个名为newtable的新表，该表包含表mytable的所有数据：
 
SELECT  * INTO newtable FROM mytable

你也可以指定只有特定的字段被用来创建这个新表。要做到这一点，只需在字段列表中指定你想要拷贝的字段。另外，你可以使用WHERE 子句来限制拷贝到新表中的记录。下面的例子只拷贝字段second_columnd的值等于’ Copy  Me!’的记录的first_column字段。
 
SELECT  first_column INTO newtable
FROM mytable
WHERE second_column = ’ Copy  Me!’

使用SQL修改已经建立的表是很困难的。例如，如果你向一个表中添加了一个字段，没有容易的办法来去除它。另外，如果你不小心把一个字段的数据类型给错了，你将没有办法改变它。但是，使用本节中讲述的SQL语句，你可以绕过这两个问题。
例如，假设你想从一个表中删除一个字段。使用SELECT INTO 语句，你可以创建该表的一个拷贝，但不包含要删除的字段。这使你既删除了该字段，又保留了不想删除的数据。
如果你想改变一个字段的数据类型，你可以创建一个包含正确数据类型字段的新表。创建好该表后，你就可以结合使用UPDATE语句和SELECT 语句，把原来表中的所有数据拷贝到新表中。通过这种方法，你既可以修改表的结构，又能保存原有的数据。

集合函数
到现在为止，你只学习了如何根据特定的条件从表中取出一条或多条记录。但是，假如你想对一个表中的记录进行数据统计。例如，如果你想统计存储在表中的一次民意测验的投票结果。或者你想知道一个访问者在你的站点上平均花费了多少时间。要对表中的任何类型的数据进行统计，都需要使用集合函数。
Microsoft SQL 支持五种类型的集合函数。你可以统计记录数目，平均值，最小值，最大值，或者求和。当你使用一个集合函数时，它只返回一个数，该数值代表这几个统计值之一。

注意：
要在你的ASP网页中使用集合函数的返回值，你需要给该值起一个名字。要作到这一点，你可以在SELECT语句中，在集合函数后面紧跟一个字段名，如下例所示：

SELECT  AVG ( vote )  ‘the_average’ FROM opinion

在这个例子中，vote的平均值被命名为 the_average。现在你可以在你的ASP网页的数据库方法中使用这个名字。

统计字段值的数目
函数COUNT（）也许是最有用的集合函数。你可以用这个函数来统计一个表中有多少条记录。这里有一个例子：

SELECT  COUNT ( au_lname )  FROM authors

这个例子计算表authors中名字（last name）的数目。如果相同的名字出现了不止一次，该名字将会被计算多次。如果你想知道名字为某个特定值的作者有多少个，你可以使用WHERE子句，如下例所示：

SELECT  COUNT ( au_lname )  FROM authors WHERE au_lname = ’Ringer’

这个例子返回名字为’Ringer’的作者的数目。如果这个名字在表authors中出现了两次，则次函数的返回值是2。
假如你想知道有不同名字的作者的数目。你可以通过使用关键字DISTINCT来得到该数目。如下例所示：

SELECT  COUNT ( DISTINCT au_lname )  FROM authors

如果名字’Ringer’出现了不止一次，它将只被计算一次。关键字DISTINCT 决定了只有互不相同的值才被计算。
通常，当你使用COUNT（）时，字段中的空值将被忽略。一般来说，这正是你所希望的。但是，如果你仅仅想知道表中记录的数目，那么你需要计算表中所有的记录─不管它是否包含空值。下面是一个如何做到这一点的例子：

SELECT  COUNT ( * )  FROM authors

注意函数COUNT（）没有指定任何字段。这个语句计算表中所有记录所数目，包括有空值的记录。因此，你不需要指定要被计算的特定字段。
函数COUNT（）在很多不同情况下是有用的。例如，假设有一个表保存了对你站点的质量进行民意调查的结果。这个表有一个名为vote的字段，该字段的值要么是0，要么是1。0表示反对票，1表示赞成票。要确定赞成票的数量，你可以所有下面的SELECT 语句：

SELECT  COUNT ( vote )  FROM opinion_table WHERE vote = 1

计算字段的平均值
使用函数COUNT（），你可以统计一个字段中有多少个值。但有时你需要计算这些值的平均值。使用函数AVG（），你可以返回一个字段中所有值的平均值。
假如你对你的站点进行一次较为复杂的民意调查。访问者可以在1到10之间投票，表示他们喜欢你站点的程度。你把投票结果保存在名为vote的INT型字段中。要计算你的用户投票的平均值，你需要使用函数AVG（）：

SELECT  AVG ( vote )  FROM opinion

这个SELECT语句的返回值代表用户对你站点的平均喜欢程度。函数AVG（）只能对数值型字段使用。这个函数在计算平均值时也忽略空值。

计算字段值的和
假设你的站点被用来出售卡片，已经运行了两个月，是该计算赚了多少钱的时候了。假设有一个名为orders的表用来记录所有访问者的定购信息。要计算所有定购量的总和，你可以使用函数SUM（）：

SELECT  SUM ( purchase_amount )  FROM orders

函数SUM（）的返回值代表字段purchase_amount中所有值的平均值。字段purchase_amount的数据类型也许是MONEY型，但你也可以对其它数值型字段使用函数SUM（）。

返回最大值或最小值
再一次假设你有一个表用来保存对你的站点进行民意调查的结果。访问者可以选择从1到10 的值来表示他们对你站点的评价。如果你想知道访问者对你站点的最高评价，你可以使用如下的语句：

SELECT  MAX ( vote )  FROM opinion

你也许希望有人对你的站点给予了很高的评价。通过函数MAX（），你可以知道一个数值型字段的所有值中的最大值。如果有人对你的站点投了数字10，函数MAX（）将返回该值。
另一方面，假如你想知道访问者对你站点的的最低评价，你可以使用函数MIN（），如下例所示：

SELECT  MIN ( vote )  FROM opinion

函数MIN（）返回一个字段的所有值中的最小值。如果字段是空的，函数MIN（）返回空值。

其它常用的SQL表达式，函数，和过程
这一节将介绍一些其它的SQL技术。你将学习如何从表中取出数据，其某个字段的值处在一定的范围，你还将学习如何把字段值从一种类型转换成另一种类型，如何操作字符串和日期时间数据。最后，你将学会一个发送邮件的简单方法。

通过匹配一定范围的值来取出数据
假设你有一个表用来保存对你的站点进行民意调查的结果。现在你想向所有对你的站点的评价在7到10之间的访问者发送书面的感谢信。要得到这些人的名字，你可以使用如下的SELECT 语句：

SELECT  username FROM opinion WHERE vote > 6   and  vote < 11

这个SELECT 语句会实现你的要求。你使用下面的SELECT 语句也可以得到同样的结果：

SELECT  username FROM opinion WHERE vote BETWEEN  7  AND  10

这个SELECT 语句与上一个语句是等价的。使用哪一种语句是编程风格的问题，但你会发现使用表达式BETWEEN 的语句更易读。
现在假设你只想取出对你的站点投了1或者10的访问者的名字。要从表opinion中取出这些名字，你可以使用如下的SELECT 语句：

SELECT  username FROM opinion WHERE vote = 1  or vote 

   这个SELECT语句会返回正确的结果，没有理由不使用它。但是，存在一种等价的方式。使用如下的SELECT可以得到相同的结果：

SELECT  username FROM opinion WHERE vote IN  ( 1 , 10 )

注意表达式IN 的使用。这个SELECT 语句只取出vote的值等于括号中的值之一的记录。
你也可以使用IN来匹配字符数据。例如，假设你只想取出Bill Gates或President Clinton的投票值。你可以使用如下的SELECT 语句：

SELECT  vote FROM opinion  WHERE  username IN  ( ‘Bill Gates’ , ’President Clinton’ )

最后，你可以在使用BETWEEN或IN的同时使用表达式NOT。例如，要取出那些投票值不在7到10之间的人的名字，你可以使用如下的SELECT 语句：

SELECT  username FROM opinion WHERE vote  NOT  BETWEEN  7  and  10

要选取那些某个字段的值不在一列值之中的记录，你可以同时使用NOT 和IN，如下例所示：

SELECT  vote FROM opinion 
            WHERE username  NOT  IN  ( ‘Bill Gates’ , ’President Clinton’ )

你不是必须在SQL语句中使用BETWEEN或IN，但是，要使你的查询更接近自然语言，这两个表达式是有帮助的。

转换数据
SQL Sever足够强大，可以在需要的时候把大部分数值从一种类型转换为另一种类型。例如，要比较SMALLINT型和INT型数据的大小，你不需要进行显式的类型转换。SQL Sever会为你完成这项工作。但是，当你想在字符型数据和其它类型的数据之间进行转换时，你的确需要自己进行转换操作。例如，假设你想从一个MONEY型字段中取出所有的值，并在结果后面加上字符串“US Dollars”。你需要使用函数CONVERT（），如下例所示：

SELECT  CONVERT ( CHAR ( 8 ), price )+ ’US Dollars’ FROM orders

函数CONVERT（）带有两个变量。第一个变量指定了数据类型和长度。第二个变量指定了要进行转换的字段。在这个例子中，字段price被转换成长度为8个字符的CHAR型字段。字段price要被转换成字符型，才可以在它后面连接上字符串’US Dollars’。
当向BIT型，DATETIME型，INT型，或者NUMERIC型字段添加字符串时，你需要进行同样的转换操作。例如，下面的语句在一个SELECT语句的查询结果中加入字符串’The vote is’，该SELECT语句返回一个BIT型字段的值:

SELECT  ‘The vote is’ + CONVERT ( CHAR ( 1 ), vote )  FROM opinion

下面是这个语句的结果示例：

The vote is  1
The vote is  1
The vote is  0
( 3  row ( s )  affected )

如果你不进行显式的转换，你会收到如下的错误信息：
Implicit conversion from datatype ‘varchar’ to ‘bit’ is  not  allowec .
Use  the CONVERT function to  run  this query .

操作字符串数据
SQL Sever有许多函数和表达式，使你能对字符串进行有趣的操作，包括各种各样的模式匹配和字符转换。在这一节中，你将学习如何使用最重要的字符函数和表达式。

匹配通配符
假设你想建立一个与Yahoo功能相似的Internet目录。你可以建立一个表用来保存一系列的站点名称，统一资源定位器（URL），描述，和类别，并允许访问者通过在HTML form中输入关键字来检索这些内容。
假如有一个访问者想从这个目录中得到其描述中包含关键字trading card的站点的列表。要取出正确的站点列表，你也许试图使用这样的查询：

SELECT  site_name FROM site_directory  WHERE site_desc = ’trading card’

这个查询可以工作。但是，它只能返回那些其描述中只有trading card这个字符串的站点。例如，一个描述为We have the greatest collection of trading cards in the world!的站点不会被返回。
要把一个字符串与另一个字符串的一部分相匹配，你需要使用通配符。你使用通配符和关键字LIKE来实现模式匹配。下面的语句使用通配符和关键字LIKE重写了上面的查询，以返回所有正确站点的名字：
 
SELECT  SITE_name FROM site_directory 
                 WHERE site_desc LIKE ‘%trading cark%’

    在这个例子中，所有其描述中包含表达式trading card的站点都被返回。描述为We have the greatest collection of trading cards in the world!的站点也被返回。当然，如果一个站点的描述中包含I am trading cardboard boxes online ，该站点的名字也被返回。
注意本例中百分号的使用。百分号是通配符的例子之一。它代表0个或多个字符。通过把trading card括在百分号中，所有其中嵌有字符串trading card的字符串都被匹配。
现在，假设你的站点目录变得太大而不能在一页中完全显示。你决定把目录分成两部分。在第一页，你想显示所有首字母在A到M之间的站点。在第二页，你想显示所有首字母在N到Z之间的站点。要得到第一页的站点列表，你可以使用如下的SQL语句：

SELECT  site_name FROM site_directory WHERE site_name LIKE ‘[A-M]%’

在这个例子中使用了表达式[A-M]，只取出那些首字母在A到M之间的站点。中括号（[]）用来匹配处在指定范围内的单个字符。要得到第二页中显示的站点，应使用这个语句：

SELECT  site_name FROM  site_directory
                 WHERE site_name LIKE ‘[N-Z]%’

在这个例子中，括号中的表达式代表任何处在N到Z之间的单个字符。
假设你的站点目录变得更大了，你现在需要把目录分成更多页。如果你想显示那些以A，B或C开头的站点，你可以用下面的查询来实现：
 
SELECT  site_name FROM site_directory WHERE site_name LIKE  ‘[ABC]%’

在这个例子中，括号中的表达式不再指定一个范围，而是给出了一些字符。任何一个其名字以这些字符中的任一个开头的站点都将被返回。
通过在括号内的表达式中同时包含一个范围和一些指定的字符，你可以把这两种方法结合起来。例如，用下面的这个查询，你可以取出那些首字母在C到F之间，或者以字母Y开头的站点：

SELECT  site_name FROM site_directory WHERE site_name LIKE ‘[C-FY]%’

在这个例子中，名字为Collegescape和Yahoo的站点会被选取，而名字为Magicw3的站点则不会被选取。
你也可以使用脱字符（^）来排除特定的字符。例如，要得到那些名字不以Y开头的站点，你可以使用如下的查询：

SELECT  site_name FROM site_directory WHERE site_name LIKE ‘[^Y]%’

对给定的字符或字符范围均可以使用脱字符。
最后，通过使用下划线字符（_），你可以匹配任何单个字符。例如，下面这个查询返回每一个其名字的第二个字符为任何字母的站点：

SELECT  site_name FROM site_directory WHERE site_name LIKE ‘M_crosoft’

这个例子既返回名为Microsoft的站点，也返回名为Macrosoft的站点。但是，名字为Moocrosoft的站点则不被返回。与通配符’％’不同，下划线只代表单个字符。

注意：
如果你想匹配百分号或下划线字符本身，你需要把它们括在方括号中。如果你想匹配连字符 ( - ) ，应把它指定为方括号中的第一个字符。如果你想匹配方括号，应把它们也括在方括号中。例如，下面的语句返回所有其描述中包含百分号的站点：

     SELECT  site_name FROM site_directory WHERE site_desc LIKE ‘%[%]%’
匹配发音
Microsoft SQL 有两个允许你按照发音来匹配字符串的函数。函数SOUNDEX（）给一个字符串分配一个音标码，函数DIFFERENCE（）按照发音比较两个字符串。当你不知道一个名字的确切拼写，但多少知道一点它的发音时，使用这两个函数将有助于你取出该记录。
例如，如果你建立一个Internet目录，你也许想增加一个选项，允许访问者按照站点名的发音来搜索站点，而不是按名字的拼写。考虑如下的语句：

SELECT  site_name FROM site_directory 
                 WHERE DIFFERENCE ( site_name  ,  ‘Microsoft’ > 3

这个语句使用函数DEFFERENCE（）来取得其名字的发音与Microsoft非常相似的站点。函数DIFFERENCE（）返回一个0到4之间的数字。如果该函数返回4，表示发音非常相近；如果该函数返回0，说明这两个字符串的发音相差很大。
例如，上面的语句将返回站点名Microsoft和Macrosoft。这两个名字的发音与Microsoft都很相似。如果你把上一语句中的大于3改为大于2，那么名为Zicrosoft和Megasoft的站点也将被返回。最后，如果你只需要差别等级大于1即可，则名为Picosoft和Minisoft的站点也将被匹配。
要深入了解函数DIFFERENCE（）是如何工作的，你可以用函数SOUNDEX（）来返回函数DIFFERENCE（）所使用的音标码。这里有一个例子：

SELECT  site_name ‘site name’ , SOUNDEX ( site_name )  ‘sounds like’

这个语句选取字段site_name的所有数据及其音标码。下面是这个查询的结果：

site name                     sounds like
……………………………………………………………… .
Yahoo                         Y000
Mahoo                         M000
Microsoft                     M262
Macrosoft                     M262
Minisoft                      M521
Microshoft                    M262
Zicrosoft                     Z262
Zaposoft                      Z121
Millisoft                     M421
Nanosoft                      N521
Megasoft                      M221
Picosoft                      P221
( 12  row ( s )  affected )

如果你仔细看一下音标码，你会注意到音标码的第一个字母与字段值的第一个字母相同。例如，Yahoo和Mahoo的音标码只有第一个字母不同。你还可以发现Microsoft和Macrosoft的音标码完全相同。
函数DIFFERENDE（）比较两个字符串的第一个字母和所有的辅音字母。该函数忽略任何元音字母（包括y），除非一个元音字母是一个字符串的第一个字母。
不幸的是，使用SOUNDEX（）和DIFFERENCE () 有一个欠缺。WHERE子句中包含这两个函数的查询执行起来效果不好。因此，你应该小心使用这两个函数。

删除空格
有两个函数，TTRIM（）和LTRIM（），可以用来从字符串中剪掉空格。函数LTRIM（）去除应该字符串前面的所有空格；函数RTRIM（）去除一个字符串尾部的所有空格。这里有一个任何使用函数RTRIM（）的例子：

SELECT  RTRIM ( site_name )  FROM site_directory

在这个例子中，如果任何一个站点的名字尾部有多余的空格，多余的空格将从查询结果中删去。
你可以嵌套使用这两个函数，把一个字符串前后的空格同时删去：

SELECT  LTRIM ( RTRIM ( site_name )  FROM site_directory

你会发现，在从CHAR型字段中剪掉多余的空格时，这两个函数非常有用。记住，如果你把一个字符串保存在CHAR型字段中，该字符串会被追加多余的空格，以匹配该字段的长度。用这两个函数，你可以去掉无用的空格，从而解决这个问题。

操作日期和时间
日期和时间函数对建立一个站点是非常有用的。站点的主人往往对一个表中的数据何时被更新感兴趣。通过日期和时间函数，你可以在毫秒级跟踪一个表的改变。

返回当前日期和时间
通过函数GETDATE（），你可以获得当前的日期和时间。例如，语句SELECT GETDATE () 返回如下的结果：
…………………………… ..
    NOV  30   1997   3 :29AM
( 1  row ( s )  affected )

显然，如果你将来使用这个函数，你得到的日期将比这个时间晚，或者梗早。
函数GETDATE（）可以用来作为DATEDIME（）型字段的缺省值。这对插入记录时保存当时的时间是有用的。例如，假设有一个表用来保存你站点上的活动日志。每当有一个访问者访问到你的站点时，就在表中添加一条新记录，记下访问者的名字，活动，和进行访问的时间。要建立一个表，其中的记录包含有当前的日期和时间，可以添加一个DATETIME型字段，指定其缺省值为函数GETDATE（）的返回值，就象这样：

CREATE TABLE site_log  (
       username VARCHAR ( 40 ),
       useractivity VARCHAR ( 100 ),
       entrydate DATETIME DEFAULT GETDATE ())

转换日期和时间
你也许已经注意到，在上一节的例子中，函数GETDATE（）的返回值在显示时只显示到秒。实际上，SQL Sever内部时间可以精确到毫秒级（确切地说，可以精确到3 . 33毫秒）。
要得到不同格式的日期和时间，你需要使用函数CONVERT（）。例如，当下面的这个语句执行时，显示的时间将包括毫秒：

SELECT  CONVERT ( VARCHAR ( 30 ), GETDATE (), 9 )

注意例子中数字9的使用。这个数字指明了在显示日期和时间时使用哪种日期和时间格式。当这个语句执行时，将显示如下的日期和时间：
………………………………… ..
Nov  30   1997   3 : 29 : 55 :170AM
( 1  row ( s )  affected )

在函数CONVERT（）中你可以使用许多种不同风格的日期和时间格式。表11 . 1显示了所有的格式。

表11 . 1   日期和时间的类型
类型值            标准             输出
    0                  Default           mon dd yyyy hh :miAM
    1                  USA               mm / dd / yy
    2                   ANSI               yy . mm . dd
    3                  British / French    dd / mm / yy
    4                  German            dd . mm . yy
    5                  Italian           dd-mm-yy
    6                  -                 dd mon yy
    7                  -                 mon dd , yy
    8                  -                 hh :mi:ss
9                         Default  +  milliseconds--mon dd yyyy
hh :mi:ss:mmmAM ( or  )
    10                 USA              mm-dd-yy
    11                 JAPAN            yy / mm / dd
    12                 ISO              yymmdd
    13                 Europe           Default  +  milliseconds--dd mon yyyy
                                       hh :mi:ss:mmm ( 24h )
    14                  -                hh :mi:ss:mmm ( 24h )

   类型0， 9 ，和13总是返回四位的年。对其它类型，要显示世纪，把style值加上100。类型13和14返回24小时时钟的时间。类型0， 7 ，和13返回的月份用三位字符表示（用Nov代表November） .

对表11 . 1中所列的每一种格式，你可以把类型值加上100来显示有世纪的年（例如，00年将显示为2000年）。例如，要按日本标准显示日期，包括世纪，你应使用如下的语句：

SELECT  CONVERT（VARCHAR（ 30 ），GETDATE（）， 111 ）

    在这个例子中，函数CONVERT（）把日期格式进行转换，显示为1997 / 11 / 30

抽取日期和时间
在许多情况下，你也许只想得到日期和时间的一部分，而不是完整的日期和时间。例如，假设你想列出你的站点目录中每个站点被查询的月份。这时你不希望完整的日期和时间把网页弄乱。为了抽取日期的特定部分，你可以使用函数DATEPART（），象这样：

SELECT  site_name ‘Site Name’ ,
DATEPART ( mm , site_entrydate )  ‘Month Posted’ FROM site_directory

函数DATEPART（）的参数是两个变量。第一个变量指定要抽取日期的哪一部分；第二个变量是实际的数据。在这个例子中，函数DATEPART（）抽取月份，因为mm代表月份。下面是这个SELECT 语句的输出结果：

Site Name                         Month Posted
………………………………………………………………
Yahoo                               2
Microsoft                           5
Magicw3                             5
( 3  row ( s )  affected )

   Month Posted列显示了每个站点被查询的月份。函数DATEPART（）的返回值是一个整数。你可以用这个函数抽取日期的各个不同部分，如表11 . 2所示。

表11 . 2   日期的各部分及其简写
日期部分           简写               值
year                yy                 1753 -- 9999
quarter             qq                 1 -- 4
month               mm                 1 -- 12
day of year         dy                 1 -- 366
day                 dd                 1 -- 31
week                wk                 1 -- 53
weekday             dw                 1 -- 7 ( Sunday--Saturday )
hour                hh                 0 -- 23
minute              mi                 0 -- 59
second              ss                 0 -- 59
milisecond          ms                 0 -- 999

当你需要进行日期和时间的比较时，使用函数DATEPART（）返回整数是有用的。但是，上例中的查询结果（ 2 ， 5 ）不是十分易读。要以更易读的格式得到部分的日期和时间，你可以使用函数DATENAME（），如下例所示：
 
SELECT  site_name ‘Site Name’
DATENAME ( mm , site_entrydate )  ‘Month Posted’
FROM site_directory

函数DATENAME（）和函数DATEPART（）接收同样的参数。但是，它的返回值是一个字符串，而不是一个整数。下面是上例该用DATENAME（）得到的结果：

Site Name                           Month Postec
………………………………………………………………… .
Yahoo                               February
Microsoft                           June
Magicw3                             June
( 3  row ( s )  affected )

你也可以用函数DATENAE（）来抽取一个星期中的某一天。下面的这个例子同时抽取一周中的某一天和日期中的月份：

SELECT  site_name ‘Site Name’ ,
DATENAME ( dw , site_entrydate )+  ‘-’  +  DATENAME（mm , site_entrydate）
         ‘Day and Month Posted’ FORM  site_directory

这个例子执行时，将返回如下的结果：

Site Name                       Day and Month Posted
………………………………………………………………………
Yahoo                           Friday - February
Microsoft                       Tuesday - June
Magicw3                         Monday - June
( 3  row ( s )  affected )

返回日期和时间范围
当你分析表中的数据时，你也许希望取出某个特定时间的数据。你也许对特定的某一天中――比如说2000年12月25日――访问者在你站点上的活动感兴趣。要取出这种类型的数据，你也许会试图使用这样的SELECT语句：

SELECT  * FROM weblog WHERE entrydate = ” 12 / 25 / 20000 ”

不要这样做。这个SELECT语句不会返回正确的记录――它将只返回日期和时间是12 / 25 / 2000   12 : 00 : 00 :000AM的记录。换句话说，只有刚好在午夜零点输入的记录才被返回。

注意：
在本节的讨论中，假设字段entrydate是DATETIME型，而不是SMALLDATETIME型。本节的讨论对SMALLDATETIME型字段也是适用的，不过SMALLDATETIME型字段只能精确到秒。

问题是SQL Sever将用完整的日期和时间代替部分日期和时间。例如，当你输入一个日期，但不输入时间时，SQL Sever将加上缺省的时间“ 12 ： 00 ： 00 ：000AM”。当你输入一个时间，但不输入日期时，SQL Sever将加上缺省的日期“Jan  1   1900 ”。
要返回正确的记录，你需要适用日期和时间范围。有不止一种途径可以做到这一点。例如，下面的这个SELECT 语句将能返回正确的记录：

SELECT  * FROM weblog 
WHERE  entrydate >= ” 12 / 25 / 2000 ” AND entrydate < ” 12 / 26 / 2000 ”

这个语句可以完成任务，因为它选取的是表中的日期和时间大于等于12 / 25 / 2000   12 : 00 : 00 :000AM并小于12 / 26 / 2000   12 : 00 : 00 :000AM的记录。换句话说，它将正确地返回2000年圣诞节这一天输入的每一条记录。
另一种方法是，你可以使用LIKE来返回正确的记录。通过在日期表达式中包含通配符“％”，你可以匹配一个特定日期的所有时间。这里有一个例子：

SELECT  * FROM weblog WHERE entrydate LIKE ‘Dec  25   2000 %’

这个语句可以匹配正确的记录。因为通配符“％”代表了任何时间。
使用这两种匹配日期和时间范围的函数，你可以选择某个月，某一天，某一年，某个小时，某一分钟，某一秒，甚至某一毫秒内输入的记录。但是，如果你使用LIKE 来匹配秒或毫秒，你首先需要使用函数CONVERT（）把日期和时间转换为更精确的格式（参见前面“转换日期和时间”一节）。

比较日期和时间
最后，还有两个日期和时间函数对根据日期和时间取出记录是有用的。使用函数DATEADD（）和DATEDIFF（），你可以比较日期的早晚。例如，下面的SELECT语句将显示表中的每一条记录已经输入了多少个小时：

SELECT  entrydate ‘ Time  Entered’
DATEDIFF ( hh , entrydate , GETDATE ())  ‘Hours Ago’ FROM weblog

如果当前时间是2000年11月30号下午6点15分，则会返回如下的结果：

Time  Entered                Hours Ago
………………………………………………… ..
Dec  30   2000    4 :09PM           2
Dec  30   2000    4 :13PM           2
Dec  1   2000    4 :09PM            698
( 3  row ( s )  affected )

函数DADEDIFF（）的参数是三个变量。第个变量指定日期的某一部分。在这个例子中，是按小时对日期进行比较，（要了解日期各部分的详细内容，请参考表11 . 2 ）在日期2000年11月1日和2000年11月30日的指定时间之间有689个小时。另外两个参数是要进行比较的时间。为了返回一个正数，较早的时间应该先给。
函数DATEADD（）把两个日期相加。当你需要计算截止日期这一类的数据时，这个函数是有用处的。例如，假设访问者必须先注册才能使用你的站点。注册以后，他们可以免费使用你的站点一个月。要确定什么时候他们的免费时间会用完，你可以使用如下的SELECT语句：

SELECT  username ‘User Name’ ,
DATEADD ( mm , 1 , firstvisit_date )  ‘Registration Expires’
FROM registration_table

函数DATEADD（）的参数有三个变量。第一个变量代表日期的某一部分（参见表11 . 2 ），这个例子用到了代表月份的mm。第二个变量指定了时间的间隔――在本例中是一个月。最后一个变量是一个日期，在这个例子中，日期是取自DATETIME型字段firstvisit_date . 假设当前日期是June  30 , 2000 ，这个语句将返回如下的内容：

User Name                         Registration Expires
……………………………………………………………………………
Bill Gates                        Jul  30   2000    4 :09PM
President Clinton                 Jul  30   2000    4 :13PM
William Shakespeare               Jul  1   2000    4 :09PM
( 3  row ( s )  affected )

注意：
与你预料的相反，使用函数DATEADD（）把一个日期加上一个月，它并不加上30天。这个函数只简单地把月份值加1。这意味着在11月注册的人将比在2月注册的人多得到2天或3天的时间。要避免这个问题，你可以用函数DATEADD（）直接增加天数，而不是月份。

发送邮件
你可以用SQL Sever发送简单的e_mail信息。要做到这一点，你需要在你的系统中安装邮件服务器，如Microsoft Exchange Sever ( 参见第四章“Exchange Active Sever , Index Sever , 和NetShow” ) 。你还需要配置SQL Sever 以识别邮件服务器。
要让SQL Sever 能识别邮件服务器，启动事务管理器并从菜单中选择Sever|SQL Mail|Configue，这时会出现一个如图11 . 3所示的对话框。输入你在邮件服务器中注册的用户名和口令，然后单击OK。

注意：
如果你使用Microsoft Exchange Sever , 配置SQL Sever的过程将会大大不同。你需要在同一个（域）用户帐号下运行Microsoft SQL Sever和Exchange Sever。你还需要在安装了SQL Sever的机器上安装Exchange Cliect并给这个帐号创建一个一个配置文件。完成这些之后，你就可以在SQL Mail Configuration对话框中输入该配置文件的名字。
 
图11。 3

在发送邮件之前，你要先启动SQL Mail。从菜单中选择Sever|SQL Mail| Start 。如果你的邮件服务器配置正确，并且你输入了正确的用户名和口令，则SQL Mail会成功启动。

注意：
你可以把SQL Sever配置为自动启动邮件服务。要做到这一点，在Set Sever Optons对话框（从菜单中选择Sever|SQL Sever|Configure）中选择Auto  Start  Mail Client即可。

要发送一个邮件，你可以使用名为xp_sendmail的扩展存储过程。这里有一个如何使用这个过程的例子：

master .. xp_sendmail “president@whitehouse . gov” , ”Hello Mr .  President”

这个过程调用向e_mail地址president@whitehouse . gov发送一个简单的email信息：“Hello Mr .  President”。你可以用任何其它的email地址和信息取代上例中相应的内容，但是，你所发送的信息不能超过255个字符长度。
    当你想随时了解你的站点数据库的状态时，存储过程xp_sendmail是有用处的。例如，你可以向一个页面管理程序发送信息。如果你的站点出了什么问题，你马上就可以知道。下一章将讲述更多有关存储过程的内容。

总结
这一章加深了你的SQL知识。你学会了如何建立索引，使你的查询速度更快。你还学会了如何插入，删除和更新一个表中的数据，如何使用集合函数得到一个表中数据的统计信息。最后，你学会了许多有价值的表达式，函数和过程，用来操作字符串，日期和时间及邮件。
下一章将进一步加深你对Microsoft SQL Sever的掌握。你将学习如何用SQL来进行程序设计，如何建立存储过程，触发器和执行计划。更另人兴奋的是，你将学会让SQL Sever自动创建网页的一个简单方法。

 

---

---

---

 

 

 

 

 

经典SQL语句集锦.doc

 

经典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  .....

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对象，代表一个错误信息。

 

 

 

 

 

 

Oracle数据操作和控制语言详解

 

SQL语言共分为四大类：数据查询语言DQL，数据操纵语言DML， 数据定义语言DDL，数据控制语言DCL。其中用于定义数据的结构，比如 创建、修改或者删除数据库；DCL用于定义数据库用户的权限；在这篇文章中我将详细讲述这两种语言在Oracle中的使用方法。 

　　DML语言

　　DML是SQL的一个子集，主要用于修改数据，下表列出了ORACLE支持的DML语句。

语句  用途 
INSERT 向表中添加行 
UPDATE 更新存储在表中的数据 
DELETE  删除行 
SELECT   FOR  UPDATE  禁止其他用户访问DML语句正在处理的行。 
LOCK TABLE  禁止其他用户在表中使用DML语句 

　　插入数据

　　INSERT语句常常用于向表中插入行，行中可以有特殊数据字段，或者可以用子查询从已存在的数据中建立新行。

　　列目录是可选的，缺省的列的目录是所有的列名，包括comlumn_id , comlumn_id可以在数据字典视图ALL_TAB_COLUMNS , USER_TAB_COLUMNS , 或者DBA_TAB_COLUMNS中找到。

　　插入行的数据的数量和数据类型必须和列的数量和数据类型相匹配。不符合列定义的数据类型将对插入值实行隐式数据转换。NULL字符串将一个NULL值插入适当的列中。关键字NULL常常用于表示将某列定义为NULL值。

　　下面的两个例子是等价的。



INSERT INTO customers ( cust_id , state , post_code )
VALUE ( 'Ariel' , NULL , ' 94501 ' );   

　　或

INSERT INTO customers ( cust_id , state , post_code )
VALUE ( 'Ariel' ,, ' 94501 ' );  

　　更新数据

　　UPDATE命令用于修改表中的数据。

UPDATE order_rollup
SET ( qty , price )=( SELECT  SUM ( qty ), SUM ( price )  FROM order_lines WHERE customer_id = 'KOHL'
WHERE cust_id = 'KOHL'
AND order_period = TO_DATE ( ' 01 -Oct- 2000 ' )  

　　删除数据

　　DELETE语句用来从表中删除一行或多行数据，该命令包含两个语句：

　　　 1 、关键字DELETE FROM后跟准备从中删除数据的表名。

　　　 2 、WHERE后跟删除条件

DELETE FROM po_lines
WHERE ship_to_state IN  ( 'TX' , 'NY' , 'IL' )
AND order_date  

　　清空表

　　如果你想删除表中所有数据，清空表，可以考虑使用DDL语言的TRUNCATE语句。TRUNCATE就像没有WHERE子句的DELETE命令一样。TRUNCATE将删除表中所有行。TRUNCATE不是DML语句是DDL语句，他和DELETE右不同的特点。

TRUNCATE TABLE  ( schema ) table DROP ( REUSE )  STORAGE  

　　STORAGE子串是可选的，缺省是DROP STORAGE。当使用DROP STORAGE时将缩短表和表索引，将表收缩到最小范围，并重新设置NEXT参数。REUSE STORAGE不会缩短表或者调整NEXT参数。

　　TRUNCATE和DELETE有以下几点区别

　　 1 、TRUNCATE在各种表上无论是大的还是小的都非常快。如果有ROLLBACK命令DELETE将被撤销，而TRUNCATE则不会被撤销。

　　 2 、TRUNCATE是一个DDL语言，向其他所有的DDL语言一样，他将被隐式提交，不能对TRUNCATE使用ROLLBACK命令。

　　 3 、TRUNCATE将重新设置高水平线和所有的索引。在对整个表和索引进行完全浏览时，经过TRUNCATE操作后的表比DELETE操作后的表要快得多。

　　 4 、TRUNCATE不能触发任何DELETE触发器。

　　 5 、不能授予任何人清空他人的表的权限。

　　 6 、当表被清空后表和表的索引讲重新设置成初始大小，而delete则不能。

　　 7 、不能清空父表。

　　 SELECT   FOR  UPDATE

　　 select   for  update语句用于锁定行，阻止其他用户在该行上修改数据。当该行被锁定后其他用户可以用SELECT语句查询该行的数据，但不能修改或锁定该行。

　　锁定表

　　LOCK语句常常用于锁定整个表。当表被锁定后，大多数DML语言不能在该表上使用。LOCK语法如下：

LOCK schema table IN lock_mode 

　　其中lock_mode有两个选项：

　　　share 共享方式

　　　exclusive 唯一方式

　　例：

LOCK TABLE intentory IN EXCLUSIVE  MODE  

　　死锁

　　当两个事务都被锁定，并且互相都在等待另一个被解锁，这种情况称为死锁。

　　当出现死锁时，ORACLE将检测死锁条件，并返回一个异常。

事务控制

　　事务控制包括协调对相同数据的多个同步的访问。当一个用户改变了另一个用户正在使用的数据时，oracle使用事务控制谁可以操作数据。

　　事务

　　事务表示工作的一个基本单元，是一系列作为一个单元被成功或不成功操作的SQL语句。在SQL和PL / SQL中有很多语句让程序员控制事务。程序员可以：

　　　 1 、显式开始一个事物，选择语句级一致性或事务级一致性

　　　 2 、设置撤销回滚点，并回滚到回滚点
　
　　　 3 、完成事务永远改变数据或者放弃修改。
　　
　　事务控制语句

语句 用途 
Commit  完成事务，数据修改成功并对其他用户开放 
Rollback  撤销事务，撤销所有操作 
rollback to savepoint  撤销在设置的回滚点以后的操作 
set  transaction 响应事务或语句的一致性；特别对于事务使用回滚段 

　　例：

BEGIN
UPDATE checking
SET  balance = balance- 5000
WHERE account = 'Kieesha' ;

INSERT INTO checking_log ( action_date , action , amount )
VALUES  ( SYSDATE , 'Transfer to brokerage' , - 5000 );

UPDATE brokerage
SET  cash_balance = cash_balance + 5000
WHERE account = 'Kiesha' ;

INSERT INTO brokerage_log ( action_date , action , amount )
VALUES  ( SYSDATE , 'Tracfer from checking' , 5000 )

COMMIT

EXCEPTION
WHEN  OTHERS
ROLLBACK

END  

　　Savepoint 和 部分回滚 ( Partial Rollback )

　　在SQL和PL / SQL中Savepoint是在一事务范围内的中间标志。经常用于将一个长的事务划分为小的部分。保留点Savepoint可标志长事务中的任何点，允许可回滚该点之后的操作。在应用程序中经常使用Savepoint；例如一过程包含几个函数，在每个函数前可建立一个保留点，如果函数失败，很容易返回到每一个函数开始的情况。在回滚到一个Savepoint之后，该Savepoint之后所获得的数据封锁被释放。为了实现部分回滚可以用带TO Savepoint子句的ROLLBACK语句将事务回滚到指定的位置。

　　例

BEGIN 

INSERT INTO ATM_LOG ( who , when , what , where )
VALUES  ( 'Kiesha' , SYSDATE , 'Withdrawal of  $ 100 ' , 'ATM54' )
SAVEPOINT ATM_LOGGED ;

UPDATE checking
SET  balance = balance- 100
RETURN  balance INTO new_balance ;

IF  new_balance < 0
THEN
ROLLBACK TO ATM_LOGGED ;
COMMIT
RAISE insufficient_funda ;
END   IF

END  
 

　　关键字SAVEPOINT是可选的，所以下面两个语句是等价的：

ROLLBACK TO ATM_LOGGED ;
ROLLBACK TO SAVEPOINT ATM_LOGGED ;  

　　一致性和事务

　　一致性是事物控制的关键慨念。掌握了oracle 的一致性模型，能使您更好的，更恰当的使用事务控制。oracle通过一致性保证数据只有在事务全部完成后才能被用户看见和使用。这项技术对多用户数据库有巨大的作用。

　　oracle常常使用语句级（state-level ) 一致性，保证数据在语句的生命期之间是可见的但不能被改变。事务由多个语句组成，当使用事务时，事物级（transaction-level ) 一致性在整个事务生命期中保证数据对所有语句都是可见的。

　　oracle通过SCN ( syatem change number ) 实施一致性。一个SCN是一个面向时间的数据库内部键。SCN只会增加不会减少，SCN表示了时间上的一个点，每个数据块都有一个SCN , 通过比较这个点实施操作。

　　事务级一致性

　　 SET  TRANSACTION 的一个作用是确保事务级一致或语句级一致中有一个实施。ORACLE使用这些术语：

　　　ISOLATION LEVEL READ COMMIT 表示语句级一致

　　　ISOLATION LEVEL SERIALIZABLE 表示事务级一致。

　　例：

SET  TRANSACTION ISOLATION LEVEL READ COMMIT ;

SET  TRANSACTION ISOLATION LEVEL READ COMMIT 

　　下面的语句也能确保事务级一致：

SET  TRANSCATION READ ONLY 

　　任何企图在只读（READ ONLY）事务中修改数据的操作都会抛出一个异常。但是，READ ONLY事务只能在下列语句中使用：

SELECT ( 没有FOR UPDATE子句）
LOCK TABLE
SET  ROLE
ALTER SYSTEM
ALTER ALARM 

　　即使没有改变任何数据，READ ONLY事务依然必须使用一个COMMIT或ROLLBACK以结束整个事务。

　　 SET  TRANSCTION的另外一个应用是在回滚时直接使用回滚段（ROLLBACK SEGMENT）。回滚段是ORACLE的一个特殊的数据对象，回滚段的头部包含正在使用该回滚段事务的信息。当用户回滚事务（ROLLBACK）时，ORACLE将会利用回滚段中的数据前影像来将修改的数据恢复到原来的值。oracle用round-robin给事务随机分配回滚段。一个大的事务可以分配任何回滚段，这也许会导致回滚段的大小变得很大。因此要避免让大的事务随机分配回滚段。

　　事务以SET TRANSACTION开始 , 象下面这样：

SET  TRANSACTION  USE  ROLLBACK SEGMENT rb_large ;  

　　rb_large是一个大的回滚段的名称，现在就给一个大的事务分配了一个大的回滚段，其他的小的回滚段将不由动态空间管理，这样就更有效率。

　　下面我们看一个例子 . 我们有一个回滚段表空间大小是2G，在高峰时期需要10个回滚段以满足用户的需要，这些高峰在线用户只有小的事务。一周我们连续运行了4个大的事务，这些事务需要删除和加载数据，每一个撤销需要1G，回滚段的大小如下：

rb_large ( initial 100M minextenta  2 )

rb1  ( initial 1M next minextents  5 )  
rb2  ( initial 1M next minextents  5 )
rb3  ( initial 1M next minextents  5 )
rb4  ( initial 1M next minextents  5 )
rb5  ( initial 1M next minextents  5 )
rb6  ( initial 1M next minextents  5 )
rb7  ( initial 1M next minextents  5 )
rb8  ( initial 1M next minextents  5 )
rb9  ( initial 1M next minextents  5 )
rb10  ( initial 1M next minextents  5 )   

　　所有的都非常恰当的安排在2G的表空间中，如果我们缺省的round-robin给事务分配回滚段，4个大事务将有4个独立的回滚段，每个回滚段的大小将是1G , 如果这样我们的2G表空间就不够，而数据库管理员就不得不在夜晚2点起来工作，每个事务都由以下面的语句开始：

SET  TRANSACTION  USE  ROLLBACK SEGMENT rb_large  

　　现在 4个事务重用相同的表空间，保正4个回滚段的表空间在2G以内。数据库管理员可以睡到天亮。


建立和修改用户

　　CREATE USER 语句将建立一个用户。当一个用户连接到ORACLE数据库时，它必须被验证。ORACLE中验证有三种类型：

　　　Database

　　　external

　　　Global

　　缺省是数据库验证，当用户连接到数据库时，oracle将检测用户是否是数据库的合法用户，并且要提供正确的password . external验证，oracle将只检测用户是否是合法用户，password已经被网络或系统验证了。global验证也是只检测是否是合法用户，password由oraclesecurity server验证。

　　Database验证用户账号

　　数据库验证账号是张好的缺省类型，也是最普通的类型。建立一个账号是piyush , 口令是welcome的账号，只需执行下面的命令：

CREATE  USE  piyush IDENTIFIED BY welcome 

　　piyush可以通过下面的语句将口令改变为saraswatt:

ALTER USER piyush IDENTIFIED BY saraswati ;  

　　外部验证用户账号

　　用户账号进入数据库时可以不提供口令，这种情况下代替数据库识别口令的是客户端操作系统。外部验证账号有时也叫OPS $ 账号，当他们最初在oracle6开始介绍时，oracle账号都有关键字前缀OPS $, 这也就是为什么init . ora 参数os_authent_prefix是OPS $ --默认特征与oracle6保持一致。os_authent_prefix定义的字符串必须被预处理为用于Oracle外部识别账号的操作系统账号名。创建操作系统用户appl的语句是：

CREATE USER ops $ appl IDENTIFIED EATERNALLY 

　　但在通常情况下，os_authent_prefix将被设置为空，像下面这样：

CREATE USER appl IDENTIFIED EATERNALLY 

　　这样效果是一样的 , 关键字IDENTIFIED EXTERNALLY告诉ORACLE这是一个外部识别账号。

　　GLOBAL用户账号

　　GLOBAL类型的用户账号数据库不检测口令，而是由X . 509目录服务器检测口令。创建一个GLOBAL类型的用户账号的方法是：

CREATE USER scott IDENTIFIED GLOBALLY AS  " CN=scott,OU=divisional,O=sybex,C=US "  

　　关键字IDENTIFIED GLOBALLY AS表示建立的是一个GLOBAL类型的用户账号 .

　　创建和更改用户账号

　　CREATE USER 用于建立用户账号和给用户账号的属性赋值。ALTER USER用于更改用户账号和属性。但CREATE USER语句必须包括用户名和口令。

　　有部分属性能用CREATER USER和ALTER USER语句设置，下面对是这些的属性具体描述：

　　给用户分配缺省表空间

　　表空间（tablespace ) 是放置表、索引、丛等用户对象的。如果在create user语句中没有包含表空间，那么缺省的是系统表空间。

CREATE USER piyush IDENTIFIED BY saraswati
DEFAULTE TABLESPACE user_data ;
ALTER USER manoj DEFAULTE TABLESPACE dev1_data ;  

　　给用户分配临时表空间

　　临时表空间，顾名思义是临时存放表、索引等用户对象的临时段。建立方法一样

CREATE USER piyush IDENTIFIED BY saraswati
Temporary TABLESPACE user_data ;
ALTER USER manoj Temporary TABLESPACE dev1_data ;  

　　给用户分配表空间的使用定额

　　使用定额限制用户在表空间中使用磁盘的数量。定额可以按字节、千字节、兆字节或者无限制来制定。

CREATE USER piyush IDENTIFIED BY saraswati
DEFAULT TABLESPACE user_data
QUOTA UNLIMITED  ON  user_data
QUOTA 20M  ON  tools ;
ALTER USER manoj QUOTA 2500K  ON  tools ;  

　　给用户分配一个简表

　　简表可以限制用户在会话时消耗的资源。这些资源包括：连接数据库的时间，空闲时间，每次会话的逻辑读数据的数量等等，缺省的简表对资源无限制。

CREATE USER piyush IDENTIFIED BY saraswati
PROFILE TABLESPACE user_data ;
ALTER USER manoj Temporary TABLESPACE dev1_data ;  

　　为用户响应指定角色

　　这个属性只能由ALTER USER语句设置，试图用CREATE USER语句设置将回返回一个例外。

ALTER USER manoj DEFAULT ROLE ALL EXCEPT salary_adm ;  

　　为用户的password设定到期时间以便在用户下次登录时更改

　　当用户的password到期，在下一次登录时将强迫修改password，oracle提示用户输入旧的password , 然后输入新的password。这项功能常用于新用户，当新用户用缺省的password登录时必须修改立即修改password .

ALTER USER manoj IDENTIFIED BY welcome ;
ALTER USER manoj PASSWORD EXPIRE ;  

　　锁定账号，是用户不能登录

ALTER USER ql AC 
COUNT LOCK  

　　对账号解锁，以便用户能登录数据库

ALTER USER ql ACCOUNT UNLOCK 

　　权限和角色

　　权限允许用户访问属于其它用户的对象或执行程序，ORACLE系统提供三种权限：

　　　Object 对象级

　　　System 系统级

　　　Role 角色级

　　这些权限可以授予给用户、特殊用户public或角色，如果授予一个权限给特殊用户 " Public " （用户public是oracle预定义的，每个用户享有这个用户享有的权限） , 那么就意味作将该权限授予了该数据库的所有用户。

　　对管理权限而言，角色是一个工具，权限能够被授予给一个角色，角色也能被授予给另一个角色或用户。用户可以通过角色继承权限，除了管理权限外角色服务没有其它目的。权限可以被授予，也可以用同样的方式撤销。

　　建立和使用角色

　　如前所诉，角色存在的目的就是为了使权限的管理变得轻松。建立角色使用CREATE ROLE语句，他的语法如下：

CREATE ROLE role_name IDENTIFIED BY password
CREATE ROLE role_name IDENTIFIED EXTERNALLY
CREATE ROLE role_name IDENTIFIED GLOBALLY 

　　缺省情况下建立的角色没有password或者其他的识别。如果使用IDENTIFIED BY 子句建立，那么角色不会自动响应，必须用SET ROLE激活。

SET  ROLE role_name IDENTIFIED BY password 

　　EXTERNALLY和GLOBALLY类型的角色由操作系统和ORACLE Service server验证。通常用户需要权限修改应用程序中使用的表单中的数据，但是只有在应用程序运行时而不是在使用ad hoc工具时，这种上下文敏感安全可以通过有PASSWORD的角色来实现。当用户在应用程序内部连结数据库时，代码将执行SET ROLE命令，通过安全验证。所以用户不需要知道角色的password , 也不需要自己输入SET ROLE命令。

　　对象权限

　　对象权限就是指在表、视图、序列、过程、函数或包等对象上执行特殊动作的权利。有九种不同类型的权限可以授予给用户或角色。如下表：

权限 ALTER DELETE EXECUTE INDEX INSERT READ  REFERENCE  SELECT   UPDATE 
Directory no  no  no  no  no  yes no no  no 
function no no yes no  no no no no no 
procedure no  no yes no no no  no no no 
package no no yes no no no no no no 
DB Object no  no  yes no no  no no no no 
Libary  no no yes  no no no  no  no no 
Operation  no  no  yes no  no  no  no  no  no 
Sequence yes  no  no  no  no  no  no  no  no 
Table  yes  yes  no yes yes  no  yes  yes  yes 
Type  no  no  yes  no  no  no  no  no  no 
View   no  yes  no  no  yes  no  no  yes  yes 

　　对象由不止一个权限，特殊权限ALL可以被授予或撤销。如TABLE的ALL权限就包括：

　　　 SELECT , INSERT , UPDATE和DELETE , 还有INDEX , ALTER , 和REFERENCE。

　　如何看这个表我们以ALTER权限为例进行说明

　　ALTER权限

　　允许执行ALTER TABLE和LOCK TABLE操作，ALTER TABLE可以进行如下操作：

　　　　 .  更改表名

　　　　 .  增加或删除列

　　　　 .  改变列的数据类型或大小

　　　　 .  将表转变为分区表

　　在SEQUENCE上的ALTER权限允许执行ALTER Sequence语句，重新给sequence分配最小值、增量和缓冲区大小。

　　系统权限

　　系统权限需要授予者有进行系统级活动的能力，如连接数据库，更改用户会话、建立表或建立用户等等。你可以在数据字典视图SYSTEM_PRIVILEGE_MAP上获得完整的系统权限。对象权限和系统权限都通过GRANT语句授予用户或角色。需要注意的是在授予对象权限时语句应该是WITH GRANT OPTION子句，但在授予系统权象时语句是WITH ADMIN OPTION，所以在你试图授予系统权限时，使用语句WITH GRANT OPTION系统会报告一个错误：ONLY ADMIN OPTION can be specified。在考试中要特别注意这个语法和错误信息。

　　角色和角色权限

　　角色权限就是将属于用户的权限授予一个角色。任何权限都可以授予给一个角色。授予系统权限给被授予者必须使用WITH_ADMIN_OPTION子句，在会话期间通过SET ROLE语句授予或撤销角色权限。然而，角色权限不能依靠存储在SQL中的权限。如果函数、程序、包、触发器或者方法使用另一个计划拥有的对象，那么就必须直接给对象的拥有者授权，这是因为权限不会在会话之间改变。

　　授予和撤销权限

　　　给用户或者角色授予权限使用GRANT 语句 , GRANT语句的语法如下：

GRANT ROLE（或system privilege） TO user ( role , Public )  WITH ADMIN OPTION（可选） 

　　对象权限被授予 WITH GRANT OPTION，

　　权限和数据字典

　　数据字典是ORACLE存储有关数据库结构信息的地方，数据本身存放在其他地方，数据字典由表和视图组成。在考试中关于数据字典最容易考的内容是：查看那一类权限已经被授予。比如DBA_TAB_PRIV包含了用户授予给另一用户的对象权限和在授予时是否带有WITH GRANT OTPION子串的信息。注意DBA_TAB_PRIV不仅仅包含了对表的权限的关系，他还包括函数、包、队列等等上的权限的关系。下表列出了所有的权限和角色的数据字典视图：

　　表： 权限的数据字典视图

视图  作用 
ALL_COL_PRIVS 表示列上的授权，用户和PUBLIC是被授予者 
ALL_COL_PRIVS_MADE  表示列上的授权，用户是属主和被授予者 
ALL_COL_RECD 表示列上的授权，用户和PUBLIC是被授予者 
ALL_TAB_PRIVS 表示对象上的授权，用户是PUBLIC或被授予者或用户是属主 
ALL_TAB_PRIVS_MADE  表示对象上的权限，用户是属主或授予者 
ALL_TAB_PRIVS_RECD  表示对象上的权限 ,  用户是PUBLIC或被授予者 
DBA_COL_PRIVS  数据库列上的所有授权 
DBA_ROLE_PRIVS  显示已授予用户或其他角色的角色 
DBA_SYS_PRIVS  已授予用户或角色的系统权限 
DBA_TAB_PRIVS  数据库对象上的所有权限 
ROLE_ROLE_PRIVS  显示已授予用户的角色 
ROLE_SYS_PRIVS 显示通过角色授予用户的系统权限 
ROLE_TAB_PRIVS 显示通过角色授予用户的对象权限 
SESSION_PRIVS  显示用户现在可利用的所有系统权限 
USER_COL_PRIVS 显示列上的权限，用户是属主、授予者或被授予者 
USER_COL_PRIVS_MADE 显示列上已授予的权限，用户是属主或授予者 
USER_COL_PRIVS_RECD  显示列上已授予的权限，用户是属主或被授予者 
USER_ROLE_PRIVS  显示已授予给用户的所有角色 
USER_SYS_PRIVS  显示已授予给用户的所有系统权限 
USER_TAB_PRIVS 显示已授予给用户的所有对象权限 
USER_TAB_PRIVS_MADE  显示已授予给其他用户的对象权限，用户是属主 
USER_TAB_PRIVS_RECD 显示已授予给其他用户的对象权限，用户是被授予者 

 

 

 

 

 

 

---

oracle里的常用命令

第一章：日志管理 

1 . forcing log switches 
sql >  alter system switch logfile ;

2 . forcing checkpoints 
sql >  alter system checkpoint ;  

3 . adding online redo log groups 
sql >  alter database add logfile [group  4 ] 
sql >   ( ' / disk3 / log4a . rdo' , ' / disk4 / log4b . rdo' )  size 1m ;  

4 . adding online redo log members 
sql >  alter database add logfile member 
sql >  ' / disk3 / log1b . rdo' to group  1 ,  
sql >  ' / disk4 / log2b . rdo' to group  2 ;  

5 . changes the name of the online redo logfile 
sql >  alter database  rename  file 'c: / oracle / oradata / oradb / redo01 . log' 
sql >  to 'c: / oracle / oradata / redo01 . log' ;  

6 . drop online redo log groups 
sql >  alter database drop logfile group  3 ;  

7 . drop online redo log members 
sql >  alter database drop logfile member 'c: / oracle / oradata / redo01 . log' ;  

8 . clearing online redo log  files  
sql >  alter database clear [unarchived] logfile 'c: / oracle / log2a . rdo' ;  

9 . using logminer analyzing redo logfiles 

a .  in the init . ora specify utl_file_dir  =  ' ' 
b .  sql >  execute dbms_logmnr_d . build ( 'oradb . ora' , 'c: oracle oradb log' );  
c .  sql >  execute dbms_logmnr_add_logfile ( 'c: oracle oradata oradb redo01 . log' ,  
sql >  dbms_logmnr . new );  
d .  sql >  execute dbms_logmnr . add_logfile ( 'c: oracle oradata oradb redo02 . log' ,  
sql >  dbms_logmnr . addfile );  
e .  sql >  execute dbms_logmnr . start_logmnr ( dictfilename => 'c: oracle oradb log oradb . ora' );  
f .  sql >   select  * from v $ logmnr_contents ( v $ logmnr_dictionary , v $ logmnr_parameters 
sql >  v $ logmnr_logs );  
g .  sql >  execute dbms_logmnr . end_logmnr ;  

第二章：表空间管理 
1 . create tablespaces 
sql >  create tablespace tablespace_name datafile 'c: oracle oradata file1 . dbf' size 100m ,  
sql >  'c: oracle oradata file2 . dbf' size 100m minimum extent 550k [logging / nologging] 
sql >  default storage  ( initial 500k next 500k maxextents  500  pctinccease  0 )  
sql >  [online / offline] [permanent / temporary] [extent_management_clause] 

2 . locally managed tablespace 
sql >  create tablespace user_data datafile 'c: oracle oradata user_data01 . dbf' 
sql >  size 500m extent management local uniform size 10m ;  

3 . temporary tablespace 
sql >  create temporary tablespace temp tempfile 'c: oracle oradata temp01 . dbf' 
sql >  size 500m extent management local uniform size 10m ;  

4 . change the storage setting 
sql >  alter tablespace app_data minimum extent 2m ;  
sql >  alter tablespace app_data default storage ( initial 2m next 2m maxextents  999 );  

5 . taking tablespace offline or online 
sql >  alter tablespace app_data offline ;  
sql >  alter tablespace app_data online ;  

6 . read_only tablespace 
sql >  alter tablespace app_data read only|write ;  

7 . droping tablespace 
sql >  drop tablespace app_data including contents ;  

8 . enableing automatic extension of data  files  
sql >  alter tablespace app_data add datafile 'c: oracle oradata app_data01 . dbf' size 200m 
sql >  autoextend  on  next 10m maxsize 500m ;  

9 . change the size fo data  files  manually 
sql >  alter database datafile 'c: oracle oradata app_data . dbf' resize 200m ;  

10 . Moving data  files : alter tablespace 
sql >  alter tablespace app_data  rename  datafile 'c: oracle oradata app_data . dbf' 
sql >  to 'c: oracle app_data . dbf' ;  

11 . moving data  files :alter  database 
sql >  alter database  rename  file 'c: oracle oradata app_data . dbf' 
sql >  to 'c: oracle app_data . dbf' ;  

第三章：表 

1 . create a table 
sql >  create table table_name  ( column datatype , column datatype] ....)  
sql >  tablespace tablespace_name [pctfree integer] [pctused integer] 
sql >  [initrans integer] [maxtrans integer] 
sql >  storage ( initial 200k next 200k pctincrease  0  maxextents  50 )  
sql >  [logging|nologging] [ cache |nocache] 

2 . copy  an existing table 
sql >  create table table_name [logging|nologging] as subquery 

3 . create temporary table 
sql >  create global temporary table xay_temp as  select  * from xay ;  
on  commit preserve rows / on  commit delete rows 

4 . pctfree  =   ( average row size - initial row size )  * 100   / average row size 
pctused  =   100 -pctfree-  ( average row size* 100 / available data space )  

5 . change storage and block utilization parameter 
sql >  alter table table_name pctfree = 30  pctused = 50  storage ( next 500k 
sql >  minextents  2  maxextents  100 );  

6 . manually allocating extents 
sql >  alter table table_name allocate extent ( size 500k datafile 'c: / oracle / data . dbf' );  

7 . move  tablespace 
sql >  alter table employee  move  tablespace users ;  

8 . deallocate of unused space 
sql >  alter table table_name deallocate unused [keep integer] 

9 . truncate a table 
sql >  truncate table table_name ;  

10 . drop a table 
sql >  drop table table_name [cascade constraints] ;  

11 . drop a column 
sql >  alter table table_name drop column comments cascade constraints checkpoint  1000 ;  
alter table table_name drop columns continue ;  

12 . mark a column as unused 
sql >  alter table table_name  set  unused column comments cascade constraints ;  
alter table table_name drop unused columns checkpoint  1000 ;  
alter table orders drop columns continue checkpoint  1000  
data_dictionary : dba_unused_col_tabs 

第四章：索引 

1 . creating function-based indexes 
sql >  create index summit . item_quantity  on  summit . item ( quantity-quantity_shipped );  

2 . create a B- tree  index 
sql >  create [unique] index index_name  on  table_name ( column ,..  asc / desc )  tablespace 
sql >  tablespace_name [pctfree integer] [initrans integer] [maxtrans integer] 
sql >  [logging | nologging] [nosort] storage ( initial 200k next 200k pctincrease  0  
sql >  maxextents  50 );  

3 . pctfree ( index )=( maximum number of rows-initial number of rows ) * 100 / maximum number of rows 

4 . creating reverse key indexes 
sql >  create unique index xay_id  on  xay ( a )  reverse pctfree  30  storage ( initial 200k 
sql >  next 200k pctincrease  0  maxextents  50 )  tablespace indx ;  

5 . create bitmap index 
sql >  create bitmap index xay_id  on  xay ( a )  pctfree  30  storage (  initial 200k next 200k 
sql >  pctincrease  0  maxextents  50 )  tablespace indx ;  

6 . change storage parameter of index 
sql >  alter index xay_id storage  ( next 400k maxextents  100 );  

7 . allocating index space 
sql >  alter index xay_id allocate extent ( size 200k datafile 'c: / oracle / index . dbf' );  

8 . alter index xay_id deallocate unused ;  

第五章：约束 

1 . define constraints as immediate or deferred 
sql >  alter session  set  constraint[s]  =  immediate / deferred / default ;  
set  constraint[s] constraint_name / all immediate / deferred ;  

2 .  sql >  drop table table_name cascade constraints 
sql >  drop tablespace tablespace_name including contents cascade constraints 

3 .  define constraints while create a table 
sql >  create table xay ( id number ( 7 )  constraint xay_id primary key deferrable 
sql >  using index storage ( initial 100k next 100k )  tablespace indx );  
primary key / unique / references table ( column )/ check 

4 . enable constraints 
sql >  alter table xay enable novalidate constraint xay_id ;  

5 . enable constraints 
sql >  alter table xay enable validate constraint xay_id ;  

第六章：LOAD数据 

1 . loading data using direct_load insert 
sql >  insert  / * + append  * /  into emp nologging 
sql >   select  * from emp_old ;  

2 . parallel direct-load insert 
sql >  alter session enable parallel dml ;  
sql >  insert  / * + parallel ( emp , 2 )  * /  into emp nologging 
sql >   select  * from emp_old ;  

3 . using sql*loader 
sql >  sqlldr scott / tiger   
sql >  control  =  ulcase6 . ctl   
sql >  log  =  ulcase6 . log direct = true 

第七章：reorganizing data 

1 . using expoty 
$ exp scott / tiger tables ( dept , emp )  file = c: emp . dmp log = exp . log compress = n direct = y 

2 . using import 
$ imp scott / tiger tables ( dept , emp )  file = emp . dmp log = imp . log ignore = y 

3 . transporting a tablespace 
sql > alter tablespace sales_ts read only ;  
$ exp sys /..  file = xay . dmp transport_tablespace = y tablespace = sales_ts 
triggers = n constraints = n 
$ copy  datafile 
$ imp sys /..  file = xay . dmp transport_tablespace = y datafiles =(/ disk1 / sles01 . dbf ,/ disk2 
/ sles02 . dbf )  
sql >  alter tablespace sales_ts read write ;  

4 . checking transport  set  
sql >  DBMS_tts . transport_set_check ( ts_list  => 'sales_ts'  .., incl_constraints => true );  
在表transport_set_violations 中查看 
sql >  dbms_tts . isselfcontained 为true 是， 表示自包含 

第八章： managing password security and resources 

1 . controlling account lock and password 
sql >  alter user juncky identified by oracle account unlock ;  

2 . user_provided password function 
sql >  function_name ( userid in varchar2 ( 30 ), password in varchar2 ( 30 ),  
old_password in varchar2 ( 30 ))   return  boolean 

3 . create a profile : password setting 
sql >  create profile grace_5 limit failed_login_attempts  3  
sql >  password_lock_time unlimited password_life_time  30  
sql > password_reuse_time  30  password_verify_function verify_function 
sql >  password_grace_time  5 ;  

4 . altering a profile 
sql >  alter profile default failed_login_attempts  3  
sql >  password_life_time  60  password_grace_time  10 ;  

5 . drop a profile 
sql >  drop profile grace_5 [cascade] ;  

6 . create a profile : resource limit 
sql >  create profile developer_prof limit sessions_per_user  2  
sql >  cpu_per_session  10000  idle_time  60  connect_time  480 ;  

7 .   view   =>  resource_cost : alter resource cost 
dba_Users , dba_profiles 

8 .  enable resource limits 
sql >  alter system  set  resource_limit = true ;  

第九章：Managing users 

1 . create a user: database authentication 
sql >  create user juncky identified by oracle default tablespace users 
sql >  temporary tablespace temp quota 10m  on  data password expire 
sql >  [account lock|unlock] [profile profilename|default] ;  

2 . change user quota  on  tablespace 
sql >  alter user juncky quota  0   on  users ;  

3 . drop a user 
sql >  drop user juncky [cascade] ;  

4 .  monitor user 
view : dba_users  ,  dba_ts_quotas

第十章：managing privileges 

1 . system privileges:  view   =>  system_privilege_map  , dba_sys_privs , session_privs 

2 . grant system privilege 
sql >  grant create session , create table to managers ;  
sql >  grant create session to scott with admin option ;  
with admin option can grant or revoke privilege from any user or role ;  

3 . sysdba and sysoper privileges: 
sysoper: startup , shutdown , alter database open|mount , alter database  backup  controlfile ,  
alter tablespace begin / end   backup , recover  database 
alter database archivelog , restricted session 
sysdba: sysoper privileges with admin option , create database , recover  database until 

4 . password file members:  view : =>  v $ pwfile_users 

5 . O7_dictionary_accessibility  = true restriction access to  view  or tables in other schema 

6 . revoke system privilege 
sql >  revoke create table from karen ;  
sql >  revoke create session from scott ;  

7 . grant object privilege 
sql >  grant execute  on  dbms_pipe to public ;  
sql >  grant update ( first_name , salary )   on  employee to karen with grant option ;  

8 . display object privilege :  view   =>  dba_tab_privs ,  dba_col_privs 

9 . revoke object privilege 
sql >  revoke execute  on  dbms_pipe from scott [cascade constraints] ;  

10 . audit record  view  : =>  sys . aud $  

11 .  protecting the audit trail 
sql >  audit delete  on  sys . aud $  by access ;  

12 . statement auditing 
sql >  audit user ;  

13 . privilege auditing 
sql >  audit  select  any table by summit by access ;  

14 . schema object auditing 
sql >  audit lock  on  summit . employee by access whenever successful ;  

15 . view  audit option :  view =>  all_def_audit_opts , dba_stmt_audit_opts , dba_priv_audit_opts , dba_obj_audit_opts 

16 . view  audit result:  view =>  dba_audit_trail , dba_audit_exists , dba_audit_object , dba_audit_session , dba_audit_statement 

第十一章： manager role 

1 . create roles 
sql >  create role sales_clerk ;  
sql >  create role hr_clerk identified by bonus ;  
sql >  create role hr_manager identified externally ;  

2 . modify role 
sql >  alter role sales_clerk identified by commission ;  
sql >  alter role hr_clerk identified externally ;  
sql >  alter role hr_manager  not  identified ;  

3 . assigning roles 
sql >  grant sales_clerk to scott ;  
sql >  grant hr_clerk to hr_manager ;  
sql >  grant hr_manager to scott with admin option ;  

4 . establish default role 
sql >  alter user scott default role hr_clerk , sales_clerk ;  
sql >  alter user scott default role all ;  
sql >  alter user scott default role all except hr_clerk ;  
sql >  alter user scott default role none ;  

5 . enable and disable roles 
sql >   set  role hr_clerk ;  
sql >   set  role sales_clerk identified by commission ;  
sql >   set  role all except sales_clerk ;  
sql >   set  role none ;  

6 . remove role from user 
sql >  revoke sales_clerk from scott ;  
sql >  revoke hr_manager from public ;  

7 . remove role 
sql >  drop role hr_manager ;  

8 . display role information 
view :  => dba_roles , dba_role_privs , role_role_privs , dba_sys_privs , role_sys_privs , role_tab_privs , session_roles 

第十二章:  BACKUP  and RECOVERY 

1 .  v $ sga , v $ instance , v $ process , v $ bgprocess , v $ database , v $ datafile , v $ sgastat 

2 .  Rman need  set  dbwr_IO_slaves or backup_tape_IO_slaves and large_pool_size 

3 .  Monitoring Parallel Rollback 
>  v $ fast_start_servers  ,  v $ fast_start_transactions 

4 . perform a closed database  backup   ( noarchivelog )  
>  shutdown immediate 
>  cp  files   / backup /  
>  startup 

5 . restore  to a different location 
>  connect system / manager as sysdba 
>  startup mount 
>  alter database  rename  file ' / disk1 /../ user . dbf' to ' / disk2 /../ user . dbf' ;  
>  alter database open ;  

6 . recover  syntax 
-- recover  a mounted database 
> recover  database ;  
> recover  datafile ' / disk1 / data / df2 . dbf' ;  
> alter database  recover  database ;  
-- recover  an opened database 
> recover  tablespace user_data ;  
> recover  datafile  2 ;  
> alter database  recover  datafile  2 ;  

7 . how to apply redo log  files  automatically 
> set  autorecovery  on  
> recover  automatic datafile  4 ;  

8 . complete recovery: 
--method  1 ( mounted databae )  
> copy  c: backup user . dbf c: oradata user . dbf 
> startup mount 
> recover  datafile 'c: oradata user . dbf ;  
> alter database open ;  
--method  2 ( opened database , initially opened , not  system or rollback datafile )  
> copy  c: backup user . dbf c: oradata user . dbf  ( alter tablespace offline )  
> recover  datafile 'c: oradata user . dbf' or 
> recover  tablespace user_data ;  
> alter database datafile 'c: oradata user . dbf' online or 
> alter tablespace user_data online ;  
--method  3 ( opened database , initially closed  not  system or rollback datafile )  
> startup mount 
> alter database datafile 'c: oradata user . dbf' offline ;  
> alter database open 
> copy  c: backup user . dbf d: oradata user . dbf 
> alter database  rename  file 'c: oradata user . dbf' to 'd: oradata user . dbf' 
> recover  datafile 'e: oradata user . dbf' or  recover  tablespace user_data ;  
> alter tablespace user_data online ;  
--method  4 ( loss of data file with no  backup  and have all archive log )  
> alter tablespace user_data offline immediate ;  
> alter database create datafile 'd: oradata user . dbf' as 'c: oradata user . dbf'' 
> recover  tablespace user_data ;  
> alter tablespace user_data online 
5 . perform an open database  backup  
>  alter tablespace user_data begin  backup ;  
>   copy   files   / backup /  
>  alter database datafile ' / c: /../ data . dbf'  end   backup ;  
>  alter system switch logfile ;  
6 . backup  a control file 
>  alter database  backup  controlfile to 'control1 . bkp' ;  
>  alter database  backup  controlfile to  trace ;  
7 . recovery  ( noarchivelog  mode )  
>  shutdown abort 
>  cp  files  
>  startup 
8 . recovery of file in  backup   mode  
> alter database datafile  2   end   backup ;  

9 . clearing redo log file 
> alter database clear unarchived logfile group  1 ;  
> alter database clear unarchived logfile group  1  unrecoverable datafile ;  

10 . redo log recovery 
> alter database add logfile group  3  'c: oradata redo03 . log' size 1000k ;  
> alter database drop logfile group  1 ;  
> alter database open ;  
or  > cp c: oradata redo02 . log' c: oradata redo01 . log 
> alter database clear logfile 'c: oradata log01 . log' ;