几种重要sql用法

转：以前遇见别人问的几个数据库的问题，我没有回答好，最近看些数据库的书籍把他们总结如下，以备后人之鉴。

1 、 用 select 语句，查询重复记录。（一般我们用的是 distinct 找不同的记录，问到重复的反而有点发愣）
假设，表名为 T1 子段为 A,B,C
 select count(*) ,A,B,C from T1
   group by A,B,C having count(*) > 1
测试数据：
    A100          B100          C100     
    A101          B101          C101     
    A102          B102          C102     
    A102          B102          C100     
    A102          B102          C102     
    A102          B102          C102     
    A100          B100          C100     
运行结果：
2    A100          B100          C100     
3    A102          B102          C102   

 

2 、 如果查询的结果某个字段是 Null ，用默认的值代替。

1 、） Sql server 中：

select   case   when   字段名 1   is   null  
                              then   替代值  
                            else   字段名 1    
           end   +  
           case   when   字段名 2   is   null  
                              then   替代值  
                          else   字段名 2    
            end   as   显示字段名    
          from   表名

注：字段名是表中的列。

经常如果某个字段是“”时（空格），也可以用某个字段代替，例如 customer 表：

 

执行下列语句：

select   custid,shipline1,shipline2, name ,          

  case when   Status =' ' then '0' else Status end as Status  from  customer

  得到 ( 如果 status 为 ’ ’, 则让它视图显示“ 0 ” ) ：

 

执行进一步更复杂的要求，如果 name 为 ’my’ 显示‘ a ’ , 为 ’yo’ 显示‘ b ’ , 其他显示 ’c’,sql 语句如下：

 

select

  custid,shipline1,shipline2,  

  case   when   name='my'  then   'a'    

        when   name='yo'   then   'b'

        else 'c'   end  as name ,          

  case when   Status ='' then '0' else Status end

  as Status  from  customer order by custid asc

 

注：（附建表语句）

create table AppDta.dbo.customer(

            custid int Not Null check(custid>0),

            name char(30) Not Null check(name<>''),

           shipline1 varchar(100) Not Null Default '',

           shipline2 varchar(100) Not Null Default '',

            Status  char(1) Not Null Default '',

           CreditLimit Money Not Null check((CreditLimit Is NUll) or (CreditLimit>=0)));

 

2 、） oracle 中：

下面用一个常见的数据显示来说明 decode 函数的用法。就是成绩单的显示。我想做开发的人员都遇到过这个，而且在大学期间也是常常接触成绩单，显示的是：姓名、语文、数学等。 实现脚本如下（ cjd.sql ）：

-- 建表
create table stud
(
 sid  varchar2(10),
 kcbm  varchar2(10),
 cj  int
);
-- 插入测试数据
insert into stud values(''''1'''','''' 语文 '''',80);
insert into stud values(''''2'''','''' 数学 '''',90);
insert into stud values(''''3'''','''' 英语 '''',100);
commit;
-- 创建视图， decode 用法
create or replace view cjd as
 select sid,
 decode(kcbm,'''' 语文 '''',cj,0) 语文 ,
 decode(kcbm,'''' 数学 '''',cj,0) 数学 ,
 decode(kcbm,'''' 英语 '''',cj,0) 英语
 from stud
 order by sid;
-- 显示数据
select * from cjd;

执行过程如下：

SQL> create table stud(sid varchar2(10),
  2  kcbm varchar2(10),
  3  cj int);

表已创建。

WS$R= 业 @ 网 VgoX 育 Yb 网 IlU

SQL> insert into stud values(''''1'''','''' 语文 '''',80);

已创建 1 行。

SQL> insert into stud values(''''2'''','''' 数学 '''',90);

已创建 1 行。

SQL> insert into stud values(''''3'''','''' 英语 '''',100);

已创建 1 行。

SQL> commit;

提交完成。

SQL> create or replace view cjd as
  2  select sid,
  3  decode(kcbm,'''' 语文 '''',cj,0) 语文 ,
  4  decode(kcbm,'''' 数学 '''',cj,0) 数学 ,
  5  decode(kcbm,'''' 英语 '''',cj,0) 英语
  6  from stud
  7  order by sid;

视图已建立。

SQL> select * from cjd;

SID              语文        数学        英语                                     
---------- ---------- ---------- ----------                                    
1                  80          0          0                                    
2                   0         90          0                                    
3                   0          0        100 

3 、 存储过程和触发器的区别。

1 、）什么是存储过程呢？

定义：

      将常用的或很复杂的工作，预先用 SQL 语句写好并用一个指定的名称存储起来 , 那么以后要叫数据库提供与已定义好的存储过程的功能相同的服务时 , 只需调用 execute, 即可自动完成命令。

讲到这里 , 可能有人要问：这么说存储过程就是一堆 SQL 语句而已啊？

                                       Microsoft 公司为什么还要添加这个技术呢 ?

那么存储过程与一般的 SQL 语句有什么区别呢 ?

存储过程的优点：

                       1. 存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般 SQL 语句每执行一次就编译一次 , 所以使用存储过程可提高数据库执行速度。

                        2. 当对数据库进行复杂操作时 ( 如对多个表进行 Update,Insert,Query,Delete 时），可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。

                       3. 存储过程可以重复使用 , 可减少数据库开发人员的工作量

                       4. 安全性高 , 可设定只有某此用户才具有对指定存储过程的使用权

存储过程的种类：

    1. 系统存储过程：以 sp_ 开头 , 用来进行系统的各项设定 . 取得信息 . 相关管理工作 ,

                               如 sp_help 就是取得指定对象的相关信息

   2. 扩展存储过程    以 XP_ 开头 , 用来调用操作系统提供的功能

                              exec master..xp_cmdshell 'ping 10.8.16.1'

   3. 用户自定义的存储过程 , 这是我们所指的存储过程

   常用格式

   Create procedure procedue_name

   [@parameter data_type][output]

   [with]{recompile|encryption}

   as

        sql_statement

解释 : 

output ：表示此参数是可传回的

with {recompile|encryption}

recompile: 表示每次执行此存储过程时都重新编译一次

encryption: 所创建的存储过程的内容会被加密

如 :

   表 book 的内容如下

   编号     书名                            价格

   001      C 语言入门                    $30

   002      PowerBuilder 报表开发   $52

  实例 1: 查询表 Book 的内容的存储过程

   create proc query_book

      as

      select * from book

   go

   exec query_book

  实例 2: 加入一笔记录到表 book, 并查询此表中所有书籍的总金额

   Create proc insert_book

   @param1 char(10),@param2 varchar(20),@param3 money,@param4 money output

   with encryption  --------- 加密

   as

   insert book( 编号 , 书名，价格） Values(@param1,@param2,@param3)
   select @param4=sum( 价格 ) from book
  go

  执行例子 :
  declare @total_price money
  exec insert_book '003','Delphi 控件开发指南 ',$100,@total_price
  print ' 总金额为 '+convert(varchar,@total_price)
  go
存储过程的 3 种传回值 :
   1. 以 Return 传回整数
   2. 以 output 格式传回参数
   3.Recordset
传回值的区别 :
       output 和 return 都可在批次程式中用变量接收 , 而 recordset 则传回到执行批次的客户端中  

实例 3 ：设有两个表为 Product,Order, 其表内容如下：
      Product
           产品编号        产品名称     客户订数     
            001             钢笔          30        
            002             毛笔          50        
            003             铅笔          100       
      Order 
           产品编号          客户名      客户订金
            001              南山区       $30
            002              罗湖区       $50
            003              宝安区       $4
请实现按编号为连接条件 , 将两个表连接成一个临时表 , 该表只含编号 . 产品名 . 客户名 . 订金 . 总金额 ,
总金额 = 订金 * 订数 , 临时表放在存储过程中

代码如下 :
     Create proc temp_sale
     as
       select a. 产品编号 ,a. 产品名称 ,b. 客户名 ,b. 客户订金 ,a. 客户订数 * b. 客户订金 as 总金额
       into #temptable from Product a inner join Order b on a. 产品编号 =b. 产品编号
    if  @@error=0
       print 'Good'
    else
       print 'Fail'
     go

2 ）什么是触发器

定义：触发器是一种特殊的存储过程，当 insert,update,delete 语句修改表中的一个或多个行时，执行触发器。