SQL Server 应用开发 --- SQL SERVER 2000 数据查询综合实例

SQL SERVER 2000 数据查询综合实例
实例1：更新用户卡信息
  1、描述：
         某公司印了一批充值卡，卡的密码是随机生成的，现在出现这个问题：卡里的"O"和"0"，"i"和"1"，用户反映说看不清楚，公司决定，把存储在数据库中的密码中所有的"O"都改成"0"，把所有的"i"都改成"1"。
  2、实现：
     declare @Card table ([password] varchar(8)) -- 创建数据表
     insert into @Card
     select 'abcdefgh' union
     select 'ijklmnop' union
     select 'qrstuvwx'
     select * from @Card
     update @Card set [password] = replace(replace([password],'o','0'),'i','1')
     select * from @Card
     go

实例2：特殊排序
  1、描述：
         在数据库表中有以下字符数据，如： 13-1、13-2、13-3、13-4、13-100、13-108、13-18、13-11、13-15、14-1、14-2, 现在希望通过SQL语句进行排序，并道德要按前半部份数字进行排序，然后再按后半部分的数字进行排序，输出要排成这样： 13-1、13-2、13-3、13-10、13-11、13-15、13-18、13-100、13-108、14-1、14-2
  2、实现：
     declare @SellRecord table (listNumber varchar(10)) -- 创建数据表
     insert into @SellRecord
     select '13-1' union
     select '13-2' union
     select '13-3' union
     select '13-4' union
     select '13-100' union
     select '13-108' union
     select '13-18' union
     select '13-11' union
     select '13-15' union
     select '14-1' union
     select '14-2'
     select * from @SellRecord
     select * from @SellRecord order by convert(int,left(listNumber,charindex('-',listNumber)-1)),convert(int,stuff(listNumber,1,charindex('-',listNumber),' '))

实例3：查询一张表中的奇数行和偶数行
  1、描述：
         某单位中要根据奇数行和偶数行的数据来汇总，并在这个汇总的基础上再得到一个数值
  2、实现：
     -- 创建数据表
     use pubs
     go
     if exists (select * from sysobjects where name = 'tbl') drop table tbl
     go
     create table tbl
     (
      idKey int identity(1,1) not null,
      a int
     )
     go
     insert into tbl (a) values (1)
     insert into tbl (a) values (2)
     insert into tbl (a) values (3)
     insert into tbl (a) values (4)
     insert into tbl (a) values (5)
     delete from tbl where idKey = 2
     go
     select * from tbl
     go
     -- 进行查询
     select identity(int,1,1) as [id], a
     into tempTbl
     from tbl
     go
     select * from tempTbl
     select sum(a) from tempTbl where [id]%2 != 0
     select sum(a) from tempTbl where [id]%2 = 0
     go

实例4：银行卡恢复
  1、描述：          一家银行发行了新的信用卡，刚开始的时候推广很好。但是逐渐地废卡越来越多，卡上的余额少于2元，并且用户长时间不使用该卡，因此银行在二月份把这些少于2元的卡的用户信息备份后就都从数据库表中删除了，但是很快问题就来了，用户发现他的卡再也不能使用而投拆，因此只能再把这些卡恢复。
  2、实现：
     use pubs
     go
     if exists (select * from sysobjects where name = 'S') drop table S
     go
     if exists (select * from sysobjects where name = 'M') drop table M
     go
     create table M
     (
       CardID int primary key not null,
       UserName varchar(20) not null
     )
     go
     create table S
     (
       CountID int identity(1,1) primary key, -- 帐户ID
       CardID int foreign key references M (CardID), -- 卡号
       Score float -- 余额
     )
     go
     insert into M (CardID,UserName) values (16,'张三')
     insert into M (CardID,UserName) values (23,'李四')
     insert into M (CardID,UserName) values (25,'王五')
     insert into M (CardID,UserName) values (29,'刘六')
     insert into M (CardID,UserName) values (30,'杨七')
     insert into S (CardID,Score) values (16,34.5)
     insert into S (CardID,Score) values (25,300)
     insert into S (CardID,Score) values (29,1.5)
     go
     select * from M
     select * from S
     go
     -- 恢复
     insert into S (CardID,Score) select M.CardID,2 from M left join S on M.CardID = S.CardID where S.CardID is null
     go
     select * from S
     go

实例5:
  1、描述：
         有如下二个表，将其中的数据进行合并，并按照学号进行分组，求出总分与平均分
  2、实现：
     -- 创建数据表
     use pubs
     go
     if exists (select * from sysobjects where name = 'A') drop table A
     go
     if exists (select * from sysobjects where name = 'B') drop table B
     go
     create table A -- 数学成绩表
     (
       [id] int primary key,
       score int
     )
     go
     create table B -- 语言成绩表
     (
       [id] int primary key,
       score int
     )
     go
     insert into A values (16,66)
     insert into A values (23,56)
     insert into A values (25,67)
     insert into A values (29,45)
     insert into B values (23,80)
     insert into B values (25,90)
     insert into B values (29,59)
     insert into B values (30,84)
     go
     select * from A
     select * from B
     go
     -- 建立一个临时表并数据进行合并,并进行相关操作
     if exists (select * from sysobjects where name = 'C') drop table C
     go
     create table C -- 数学成绩表
     (
       [id] int,
       score int
     )
     go
     insert into C (id,score)
     select A.id, A.score from A union
     select B.id, B.score from B
     go
     select id as 学号, sum(score) as 总分, avg(score) as 平均分 from C group by id
     go

实例6：
  1、描述：
         有表ABC,其中有字段A、B和C，并且都是字符数据，其中A列存储了从A到Z之间的单个字母，查询出A列中字符在A到P之间的所有数据行
  2、实现：
     -- 创建数据表
     use pubs
     go
     if exists (select * from sysobjects where name = 'ABC') drop table ABC
     go
     create table ABC ( id varchar(1) )
     go
     -- 以5个字符为例
     insert into ABC values ('A')
     insert into ABC values ('B')
     insert into ABC values ('C')
     insert into ABC values ('D')
     insert into ABC values ('E')
     go
     select * from ABC where id between 'A' and 'C'
     go

实例7：
  1、描述：
         有学生成绩表，数据如下，查询出每门课都大于80分的学生姓名
  2、实现：
     -- 创建数据表
     use pubs
     go
     if exists (select * from sysobjects where name = 'tb') drop table tb
     go
     create table tb ( stuName varchar(20), course varchar(20), score int )
     go
     insert into tb values ('张千','语文',80)
     insert into tb values ('张千','数学',77)
     insert into tb values ('李万','语文',66)
     insert into tb values ('李万','数学',91)
     insert into tb values ('王亿','语文',84)
     insert into tb values ('王亿','数学',100)
     insert into tb values ('王亿','英语',90)
     insert into tb values ('杨兆','英语',86)
     insert into tb values ('杨兆','数学',93)
     go
     select * from tb
     go
     -- 查询数据
     select stuName from tb group by stuName having min(score) >= 80
     go

实例8：
  1、描述：
         合并用户表，有3个表GameWOW,GameDiablo,GameStarCraft结构如下，将这三个表中的数据合并到新表Game中，新表结构如下，对于新表中存在而源表中不存在的记录，用NULL表示
  2、实现：
     use pubs
     go
     if exists (select * from sysobjects where name = 'GameWOW') drop table GameWOW
     go
     if exists (select * from sysobjects where name = 'GameDiablo') drop table GameDiablo
     go
     if exists (select * from sysobjects where name = 'GameStarCraft') drop table GameStarCraft
     go
     if exists (select * from sysobjects where name = 'Game') drop table Game
     go
     create table GameWOW
     (
       SName varchar(10),
       SPassWord varchar(10),
       SBirthday smalldatetime,
       SAddress varchar(10),
       SEmail varchar(10)
     )
     go
     insert into GameWOW values ('wow','wow',getdate(),'wow','wow')
     go
     create table GameDiablo
     (
       SName varchar(10),
       SPassWord varchar(10),
       SBirthday smalldatetime,
       SSex bit,
       SCardNumber varchar(10)
     )
     go
     insert into GameDiablo values ('diablo','diablo',getdate(),1,'diablo')
     go
     create table GameStarCraft
     (
       SName varchar(10),
       SPassWord varchar(10),
       SBirthday smalldatetime,
       SArea varchar(10),
       SCode int
     )
     go
     insert into GameStarCraft values ('starcraft','starcraft',getdate(),'starcraft',1)
     go
     create table Game
     (
       SName varchar(10),
       SPassWord varchar(10),
       SBirthday smalldatetime,
       SAddress varchar(10),
       SEmail varchar(10),
       SSex bit,
       SCardNumber varchar(10),
        SArea varchar(10),
       SCode int
     )
     go
     -- 合并
     insert into Game (SName,SPassWord,SBirthday,SAddress,SEmail,SSex,SCardNumber,SArea,SCode)
     select SName,SPassWord,SBirthday,SAddress,SEmail,null,null,null,null
     from GameWOW union
     select SName,SPassWord,SBirthday,null,null,SSex,SCardNumber,null,null
     from GameDiablo union
     select SName,SPassWord,SBirthday,null,null,null,null,SArea,SCode
     from GameStarCraft
     go
     select * from Game

实例9：
  1、描述：
         在论坛中采用一定的格式为主帖进行编号，格式为：版块编号_当前日期_四位随机数字
  2、实现：
     select 主贴编号 = '版块编号_' + convert(varchar(4),datepart(yyyy,getdate())) +
convert(varchar(2),datepart(mm,datepart(mm,getdate()))) +
convert(varchar(2),datepart(dd,datepart(dd,getdate()))) +
convert(varchar(4),right(rand(datepart(ms,getdate())*1000),4))