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

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

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

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

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

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

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

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


Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

AI Hentai Generator
Generate AI Hentai for free.

Hot Article

Hot Tools

EditPlus Chinese cracked version
Small size, syntax highlighting, does not support code prompt function

Dreamweaver Mac version
Visual web development tools

ZendStudio 13.5.1 Mac
Powerful PHP integrated development environment

SublimeText3 Mac version
God-level code editing software (SublimeText3)

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