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

SQL SERVER 2000 数据 查询 综合 实例 实例 1:更新用户卡信息 1、描述: 某公司印了一批充值卡,卡的密码是随机生成的,现在出现这个问题:卡里的"O"和"0","i"和"1",用户反映说看不清楚,公司决定,把存储在 数据 库中的密码中所有的"O"都改成"0",把所有

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))

Statement
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
SQL Server使用CROSS APPLY与OUTER APPLY实现连接查询SQL Server使用CROSS APPLY与OUTER APPLY实现连接查询Aug 26, 2022 pm 02:07 PM

本篇文章给大家带来了关于SQL的相关知识,其中主要介绍了SQL Server使用CROSS APPLY与OUTER APPLY实现连接查询的方法,文中通过示例代码介绍的非常详细,下面一起来看一下,希望对大家有帮助。

SQL Server解析/操作Json格式字段数据的方法实例SQL Server解析/操作Json格式字段数据的方法实例Aug 29, 2022 pm 12:00 PM

本篇文章给大家带来了关于SQL server的相关知识,其中主要介绍了SQL SERVER没有自带的解析json函数,需要自建一个函数(表值函数),下面介绍关于SQL Server解析/操作Json格式字段数据的相关资料,希望对大家有帮助。

聊聊优化sql中order By语句的方法聊聊优化sql中order By语句的方法Sep 27, 2022 pm 01:45 PM

如何优化sql中的orderBy语句?下面本篇文章给大家介绍一下优化sql中orderBy语句的方法,具有很好的参考价值,希望对大家有所帮助。

Monaco Editor如何实现SQL和Java代码提示?Monaco Editor如何实现SQL和Java代码提示?May 07, 2023 pm 10:13 PM

monacoeditor创建//创建和设置值if(!this.monacoEditor){this.monacoEditor=monaco.editor.create(this._node,{value:value||code,language:language,...options});this.monacoEditor.onDidChangeModelContent(e=>{constvalue=this.monacoEditor.getValue();//使value和其值保持一致i

一文搞懂SQL中的开窗函数一文搞懂SQL中的开窗函数Sep 02, 2022 pm 04:55 PM

本篇文章给大家带来了关于SQL server的相关知识,开窗函数也叫分析函数有两类,一类是聚合开窗函数,一类是排序开窗函数,下面这篇文章主要给大家介绍了关于SQL中开窗函数的相关资料,文中通过实例代码介绍的非常详细,需要的朋友可以参考下。

Monaco Editor怎么实现SQL和Java代码提示Monaco Editor怎么实现SQL和Java代码提示May 11, 2023 pm 05:31 PM

monacoeditor创建//创建和设置值if(!this.monacoEditor){this.monacoEditor=monaco.editor.create(this._node,{value:value||code,language:language,...options});this.monacoEditor.onDidChangeModelContent(e=>{constvalue=this.monacoEditor.getValue();//使value和其值保持一致i

如何使用exp进行SQL报错注入如何使用exp进行SQL报错注入May 12, 2023 am 10:16 AM

0x01前言概述小编又在MySQL中发现了一个Double型数据溢出。当我们拿到MySQL里的函数时,小编比较感兴趣的是其中的数学函数,它们也应该包含一些数据类型来保存数值。所以小编就跑去测试看哪些函数会出现溢出错误。然后小编发现,当传递一个大于709的值时,函数exp()就会引起一个溢出错误。mysql>selectexp(709);+-----------------------+|exp(709)|+-----------------------+|8.218407461554972

springboot配置mybatis的sql执行超时时间怎么解决springboot配置mybatis的sql执行超时时间怎么解决May 15, 2023 pm 06:10 PM

当某些sql因为不知名原因堵塞时,为了不影响后台服务运行,想要给sql增加执行时间限制,超时后就抛异常,保证后台线程不会因为sql堵塞而堵塞。一、yml全局配置单数据源可以,多数据源时会失效二、java配置类配置成功抛出超时异常。importcom.alibaba.druid.pool.DruidDataSource;importcom.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;importorg.apache.

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
2 weeks agoBy尊渡假赌尊渡假赌尊渡假赌
Repo: How To Revive Teammates
4 weeks agoBy尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island Adventure: How To Get Giant Seeds
4 weeks agoBy尊渡假赌尊渡假赌尊渡假赌

Hot Tools

EditPlus Chinese cracked version

EditPlus Chinese cracked version

Small size, syntax highlighting, does not support code prompt function

Dreamweaver Mac version

Dreamweaver Mac version

Visual web development tools

ZendStudio 13.5.1 Mac

ZendStudio 13.5.1 Mac

Powerful PHP integrated development environment

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

mPDF

mPDF

mPDF is a PHP library that can generate PDF files from UTF-8 encoded HTML. The original author, Ian Back, wrote mPDF to output PDF files "on the fly" from his website and handle different languages. It is slower than original scripts like HTML2FPDF and produces larger files when using Unicode fonts, but supports CSS styles etc. and has a lot of enhancements. Supports almost all languages, including RTL (Arabic and Hebrew) and CJK (Chinese, Japanese and Korean). Supports nested block-level elements (such as P, DIV),