黑马程序员-ADO.net-SQL语句进阶练习

                                                ------- Windows Phone 7手机开发、.Net培训、期待与您交流！ -------

终于学完了SQL呀，其实学完了以后才知道原来不难，之前刚开始接触的时候感觉特别的难，尤其是装SQL Server的时候，装不上，搞得换了好几次系统，悲催呀

最后终于还是被我征服了，我so高兴咧，嘻嘻。

 1.练习2 有一张表T_scores 记录比赛成绩

  Date      Name   Score   要求格式输出如下： Name  胜   负

 2008-8-8   拜仁   胜             拜仁  1    2

 2008-8-9   奇才   胜      湖人  1    0

 2008-8-9   湖人   胜      奇才  2    0

 2008-8-10  拜仁   负             注意：中文字符串前加N,比如N‘胜’

 2008-8-8   拜仁   负

 2008-8-12  奇才   胜

 第一步

 select Name

 {

 case Score

 when N'胜' then 1

 else 0

 end

 } as 胜

 {

 case Score

 when N‘败' then 1

 else 0

 end

 } as 负

 第二步

 select Name

 sum(

 case Score

 when N'胜' then 1

 else 0

 end

 )as 胜

 sum(

 case Score

 when N‘败' then 1

 else 0

 end

 ) as 负

 from person

 group by Name

 

 )

 

 

 

 

 

 3.练习3

   单号     金额

  Rk1  10

  Rk2  20

  Rk3  -30

  Rk4  -10

 将上面的表输出为如下的格式

  单号     金额  支出

  Rk1  10   0

  Rk2  20   0

  Rk3  0        30

  Rk4  0   10

 直接写SQL语句来加 

 select FNumber

 {

 case

 when AMount>0 then FAmount

 else 0

 end

 } as 收入

 {

 case

 when FAmount<0 then ABS(FAmount)

 else 0

 end

 } as 支出

 from person

 

 4.练习

 创建一张表，记录电话呼叫员的工作流水，记录呼叫员编号，对方号码，通话开始时间，通话结束时间

 建立表插入表都使用SQL语句（自己写）

 要求：

  输出所有数据中通话时间最长的5条记录。order by  datediff

  输出所有数据中拨打长途号码（对方号码以0开头的）的总时长 like,sum

  输出本月通话总时长最多的前三个呼叫员的编号datediff(month..)sum,order by

  输出本月拨打电话次数最多的前三个呼叫员的编号 group by,count(*),

  输出所有数据的拨号流水，并且在最后一行添加宗呼叫次数 

  呼叫员编号，对方号码，通话时长

  。。。。

  汇总（市内号码总时长）（长途号码总时长）

 (1)

 select datediff(second,startdatatime,enddatatime) from T_callrecords1  (计算通话时长)

 

 select top 5 * from T-callrecordes1

 order by datediff(second,startdatetime,enddatatime) Desc

 

 (2)select * from T_callrecordes1

 where telNum like '0%'

 

 select sum(datediff(second,startdatetime,enddatetime)) from t_callrecords1

 where TelNum like '0%'

 

 (3) select datediff(month,convert(datetime,'2010-06-01'),convert(datetime,'2010-07-02'))

 select datediff(month,convert(datetime,'2010-06-15'),convert(datetime,'2010-07-02'))

 

 select callerNumber ,TelNum,datediff(month,startdatetime,getdate()) from T_callrecords1

 select * from T_callrecords1 

 where datediff(month,startdatetime,getdate())=0

 

 select top 3 callerNumber from T_callrecords1

 where datediff(month,stardatetime,getdate())=0

 group by callerNumber

 order by sum(datediff(second,startdatetime,enddatetime)) desc

 

 (4)输出本月拨打电话次数最多的前三个呼叫员的编号

 select callerNumber,count(*)

 from T_callrecords1

 where daatediff(month,stardatetime,getdate())=0

 group by callerNumber

 oreder by count (*) desc

 

 select callerNumber,TelNum,datediff(second,stardatetime,enddatetime) from T_callrecords1

 union all

 select  '汇总'

 convert（varchar(50),

 sum({

 case

 when TelNum not like '0%' then datediff(second,startdatetime,enddatetime)

 else 0

 end

 })

 ） as 市内通话

 sum({

 case

 when TelNum  like '0%' then datediff(second,startdatetime,enddatetime)

 else 0

 end

 }) as 长途通话

 from T_callrecords1

 union all

 select callreNumber(second,stardatetime,enddatetime)

 

 5.索引（提高查询效率，经常要查询的where中经常用的）

  全表扫描：对数据进行检索（select）效率最差的全表扫描就是一条一条的找

  创建索引的方式，在表设计器中右击字段，索引键，添加，索引一个名字（改名字），

  列为某个字段建立索引，查询的时候该怎么查就怎么查，没有任何区别

 缺点：占据空间，插入更新删除时速度会变慢

 

 

 6.表连接jion

  有客户表和订单表，客户表字段为ID，Name，Age，订单表字段为 Id，BillNo,CustomerId

  订单表通过customerid关联客户表。

 

 

  select * from T_orders

 给两个表取别名，然后表示两个表的关系，最后再查询

  select o.billNo,c.Name,c.age

  from T_order as o join T_customers as c on o.cunstomerId=c.Id

 要求显示所有年龄大于15岁的顾客购买的订单号，客户姓名，客户年龄。

  select o.billNo,c.Name,c.age

  from T_order as o join T_customers as c on o.cunstomerId=c.Id

  where c.age>15

 要求显示年龄大于平均年龄的顾客购买的订单

 select o.billNo,c.Name,c.age

  from T_order as o join T_customers as c on o.cunstomerId=c.Id

  where c.age>(select avg(age) from T_customers) 子查询

 建立外键，表间关系（SQL中手工操作）

 

 

 7.子查询

  将一个查询语句做为一个结果集供其他SQL语句使用

 

  子查询要有一个别名

  select * from

  {

  select * from T_orders

  } as o1

 单值做为子查询：

  select 1 as f1,2,(select min(FYearPublished) from t_book),(select max(FYearpublished) from T_book) as f4)

 

 多行单列的子查询，

  select * from T_reader

  where FYearofjion in(2001,2003)  取值为2001,2003的数据

  select * from T_reader

  where FYearofjoin in

 {

 select FYearpublished from T_book

 }读者加入的年份刚好有书出版

 

 求结果在3到5行之间的，可以不是用top

 row_NUMER()当行号     不能出现在where中

 select row_NUMER() OVER(ORDER BY FSalary DESC) as rownum,

 FNumber,FName,FSalary,FAge From t_Employee

 where row_NUMER() OVER(ORDER BY FSalary DESC)>=3 and row_NUMER() OVER(ORDER BY FSalary DESC)<=5(这是错误的)

 网页数据使用的分页技术就是使用此函数子查询

 

 select * from

 {

 select row_NUMER() OVER(ORDER BY FSalary DESC) as rownum,

 FNumber,FName,FSalary,FAge from T_EMployee

 } as e1

 where e1.rownum>=3 and e1.rownum<=5