数据库知识点滴（3）---非常实用的查询

上一章就搞定了把多对多关系的表拆分成一对多和多对一，现在呢就针对项目中可能用到的多表查询进行一下总结
（1）我想知道ClassID=1的所有商品的描述（描述？这基本不可能，原本是想商品名字的，但是在建立表的时候忘记加这个字段了啊，所以就用描述好了，反正思路是一样的）
       代码如下
        select  p.Description from  Product  p  inner  join  ClassProduct  cp
        on  p.ProductID=cp.ProductID
        where  cp.ClassID=1
        如果我知道ProductID=1,那么如何返回这个这支笔属于分类的名字 ，同样的道理。
（2）我想知道在Class表中 ClassName='blue'的所有商品的描述。
       代码如下
        select p.Description from
        --中间表
        ClassProduct cp
        inner join Class c on cp.ClassID=c.ClassID
        inner join Product  p on  p.ProductID=cp.ProductID
        where c.ClassName='blue'
        同样我想知道Class表中ClassName='blue' ,并且价格小于5的所有商品。那么我们只需要增加一个条件，where c.ClassName='blue' and p.Price<5.
        后面我们会用到这个。
（3）现在假如我们往Product数据表中增加了一个记录，那么ClassProduct怎么与这个Product表同步呢，因为这个时候Product中已经多了一个记录，那么关于这个记录的ProductID在ClassProduct表中就找不到，那么我们怎么保持同步，就是让ClassProduct表中也有这个ProductID呢？解决问题的关键在于怎么获得加入的商品的ID，也就是这个ProductID。这里我们通过读取@@identity的值来获取生成的ID，然后将这个ProductID赋予其所属的分类。
       我们看个例子：
            Create  procedure  AddNewProductsWithClassProduct
            as
             declare  @ProductID   int
            insert   into  Product  (Description,Number,Price) values ('This is the funny  pen',26,2.5)
            --好现在我们把记录插进去了，现在可以而且必须现在把这个值读出来
            select  @ProductID=@@Identity
            -- ProductID 都准备好了，那么 插入ClassProduct 表就不成问题了
            insert   into  ClassProduct   (ClassID,ProductID)  values  (2,@productID)
  (4)   现在我们想返回跟商品（笔）ProductID=10的类别不同的类名。 我们假设这里Product的类别为红色和黑色（这里只是做个假设，跟我们以前做的实例是否一致都无所谓），那么我们就是要返回蓝色。
      ---这里distinct 是为了消除相同的行，你可以删除这个看看结果再加上去
         select    distinct   c.ClassName  from  ClassProduct  cp
         inner  join  Class   c   on   c.ClassID=cp.ClassID
          where    cp.ClassID   not   in 
        --这是我们首先要解决的问题，找出符合条件的ClassID
         (select  cp.ClassID  from  ClassProduct cp
           inner   join  Product   p  on  p.ProductID=cp.ProductID
           where  p.ProductID=10 
          )
          其实这里有一个比较简单的情况就是，我想查找出与ProductID不同类别的商品的信息。如果上面这个都会，那么这个是肯定会的。
         --注意这里我们是没有使用distinct的，不过使用了在这里也没有影响的。
          select  p.ProductID, p.Description,p.Price  from Product  p
           inner  join ClassProduct  cp  on p.ProductID=cp.ProductID
          where  cp.ClassID   not  in
           (select  cp.ClassID  from  ClassProduct  cp
            inner   join  Product  p   on p.ProductID=cp.ProductID
            where  p.ProductID=10)
（5） 我现在想把某个商品从某个类中移除，那么如果这个商品只属于一个类的话，就直接把这个商品删除，但是如果这个商品属于两个或者更多的类的话，那么只是把商品从这个类中移除。比如删除属于类别1的商品12号。
           if (select   count(*) from  ClassProduct   cp  where  ProductID=1)>1
              delete from  ClassProduct   where  ClassProduct.ProductID=1  and  ClassProduct.ClassID=1
          else
              begin
                 delete from  ClassProduct   where  ClassProduct.ProductID=1  and  ClassProduct.ClassID=1
                 delete from  Product   where  ProductID=12
          end
   (6)我现在想往商品中添加新的商品，假如已经存在这个商品，那么我们只在数量上加1（Number+1) ,但是假如我们没有这个商品那么我们就往表中增加这个商品
         哎呀，没有ProductName字段真晕，这里还是先把它加上去好了。
        -- 添加ProductName字段到Product表
        alter   table  Product
        add  ProductName  nvarchar(50)
       --添加数据
        update  table  Product
        set  ProductName='p1'   where  ProductID=1
        同样的道理，依次命名为p2,p3,....p11.这里我是一个一个地update不知道有没有更好的办法，谁看到了麻烦告诉一下，这里先感谢了
        好了现在我们试图往里面添加一个商品了，但是我也不知道这个商品的名字是什么，所以只能通过判断了，跟购物车类似。使用存储过程吧！
        create  procedure  AddproducttoProduct
        (@ProductName   nvarchar(50))
        as
        if   exists (select  ProductName  from  Product  where  ProductName=@ProductName)
             update  Product  set  number=number+1  where ProductName=@ProductName
       else  
             insert  into  Product  (ProductName,Price) values (@ProductName,2.5) 
       end
    (7) 为了模仿购物车，还是再加一个字段吧，就是添加商品的时间，好晕，开始的时候没有规划好。
          alter table  Product 
          add   ProTime  smalldatetime
          --再为以前的记录随便加些时间。
         我想知道哪些商品最近十天都没有修改或者添加。
          --这里因为聚合函数不能放在where的前面，所以就利用分组的办法咯   datediff是一个系统函数
          select  ProductName  from  Product  p
          group  by ProductName
          having  min(datediff(dd,p.Protime,getdate()))>=10
       把这个问题加点难度，但是技术是一样的，我想知道属于类1中有哪些商品最近10天没有变动.并且把ID查出来
               select  p.ProductID,p.ProductName from  Product  p  inner join ClassProduct   cp  on  p.ProductID=cp.ProductID
               where cp.ClassID=1
              group  by p.ProductName,p.ProductID
              having  min(datediff(dd,p.Protime,getdate()))>=10  
       查询超级慢，是不是有问题啊,
      没事估计是昨天晚上我的机子出了问题，卡了。因为开始我对group by 的认识也不多，所以去查了下资料，发现有两个网站写的很好
      详情点这里  百度查询这个我已经转到我的文章来了  ，还 有一个http://www.w3school.com.cn/sql/sql_having.asp
        假如我想删除符合条件的商品，那么现在做起来就非常简单了
        delete    from   Product   p
         where  p.ProdcutID  in
         ( select ProductID  from  Product
           group  by  ProductID
           having  min(datediff(dd,Protime,getdate()))>=10
          )
          想查询符合条件的商品的数量也不难
          select   count(ProductID)
          from  Product
          where  p.ProdcutID  in
         ( select ProductID  from  Product
           group  by  ProductID
           having  min(datediff(dd,Protime,getdate()))>=10
          )  
(8)如何限制我们select语句的返回的行数呢，我们使用set  rowcount
   例如：create  procedure   OrderRow
              (@Count    int )
             as
             set   Rowcount    @Count
             select *  from  Product    order  by  Protime   Desc
             --重新把Rowcount 设置为0，就是说不 再对返回行数进行限制
             set  rowcount   0
(9),最后是为了实现商品推荐功能的存储过程，用我们已经建立的三个表，我还真想不出怎样的例子映射过来。还是多弄几个表吧。。
    建立表OrderDetail订单详情表，三个字段，OrderID, ProductID，其中OrderID,和ProductID联合主键
  　　  use shoppen
　　go
　　create table  Orderdetail
　　(OrderID  int  ,
　　 ProductID  int  Primary key (OrderID,ProductID)
　　)
　　go
　　insert into Orderdetail  (OrderID,ProductID) values (1,2)
　　insert into Orderdetail  (OrderID,ProductID) values (1,4)
　　insert into Orderdetail  (OrderID,ProductID) values (1,5)
　　insert into Orderdetail  (OrderID,ProductID) values (2,1)
　　insert into Orderdetail  (OrderID,ProductID) values (2,4)
　　insert into Orderdetail  (OrderID,ProductID) values (2,2)
　　insert into Orderdetail  (OrderID,ProductID) values (2,9)
　　insert into Orderdetail  (OrderID,ProductID) values (3,3)
　　insert into Orderdetail  (OrderID,ProductID) values (3,2)
　　insert into Orderdetail  (OrderID,ProductID) values (3,8)
      现在，现在我知道某订单 里面有商品ProductID=4,现在的问题是，如何查询出含有ProductID=4的同一个订单里面的其它商品
      很明显，首先要把含有ProductID=4 的订单找出来。
      select  OrderID from OrderDetail  where  ProductID=4
      好了，再去返回属于这些OrderID的所有商品
      select  ProductID  from  OrderDetail  where  OrderID  in
      (select  OrderID from OrderDetail  where  ProductID=4)
      查询分析器里跑一下，您就会发现，不但ProductID=4的商品ID显示出来了，而且还有挺多重复的ProductID,就是说有相同的商品，现在的目标就是把ProductID=4的商品排除，并且消除重复，前面消除重复我们使用了distinct,这里我们将再次使用它，现在的问题是怎么把ProductID=4的商品排除掉，这个还不简单啊，直接使用where  ProductID!=4  就ok了
               select  distinct ProductID  from  OrderDetail  where  OrderID  in
              (select  OrderID from OrderDetail  where  ProductID=4)  
              and ProductID !=4
              ok,我们这里使用的是子查询，使用子查询的思路非常的清晰，像水一样，直接往低处流。其实这里也可以使用联合查询，建立两个表的实例，然后联合查询
             select     distinct  p1.ProductID  from  OrderDetail  p1 
              inner  join  OrderDetail  p2   on  p1.OrderID=p2.OrderID
              where  p2.ProductID=4   and  p1.ProductID !=4
             这里显然是没有子查询好理解，这里我们可以这样来理解，我查询表p1中的ProductID,但是查的时候呢，因为这个表跟p2关联上了，所以看它的OrderID是不是跟p2的符合条件的OrderID一样。最后再排除重复情况就ok了
             关于性能问题，我是想了也想不出来，所以向来考虑的比较少，留给高手去想，这里我想的问题是:是不是所有的子查询的问题都可以通过转化成联合查询来解决，这个问题，有时间要研究一下，先放这里，希望各位朋友提示提示，发表自己的看法