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
Explain the InnoDB Buffer Pool and its importance for performance.Explain the InnoDB Buffer Pool and its importance for performance.Apr 19, 2025 am 12:24 AM

InnoDBBufferPool reduces disk I/O by caching data and indexing pages, improving database performance. Its working principle includes: 1. Data reading: Read data from BufferPool; 2. Data writing: After modifying the data, write to BufferPool and refresh it to disk regularly; 3. Cache management: Use the LRU algorithm to manage cache pages; 4. Reading mechanism: Load adjacent data pages in advance. By sizing the BufferPool and using multiple instances, database performance can be optimized.

MySQL vs. Other Programming Languages: A ComparisonMySQL vs. Other Programming Languages: A ComparisonApr 19, 2025 am 12:22 AM

Compared with other programming languages, MySQL is mainly used to store and manage data, while other languages ​​such as Python, Java, and C are used for logical processing and application development. MySQL is known for its high performance, scalability and cross-platform support, suitable for data management needs, while other languages ​​have advantages in their respective fields such as data analytics, enterprise applications, and system programming.

Learning MySQL: A Step-by-Step Guide for New UsersLearning MySQL: A Step-by-Step Guide for New UsersApr 19, 2025 am 12:19 AM

MySQL is worth learning because it is a powerful open source database management system suitable for data storage, management and analysis. 1) MySQL is a relational database that uses SQL to operate data and is suitable for structured data management. 2) The SQL language is the key to interacting with MySQL and supports CRUD operations. 3) The working principle of MySQL includes client/server architecture, storage engine and query optimizer. 4) Basic usage includes creating databases and tables, and advanced usage involves joining tables using JOIN. 5) Common errors include syntax errors and permission issues, and debugging skills include checking syntax and using EXPLAIN commands. 6) Performance optimization involves the use of indexes, optimization of SQL statements and regular maintenance of databases.

MySQL: Essential Skills for Beginners to MasterMySQL: Essential Skills for Beginners to MasterApr 18, 2025 am 12:24 AM

MySQL is suitable for beginners to learn database skills. 1. Install MySQL server and client tools. 2. Understand basic SQL queries, such as SELECT. 3. Master data operations: create tables, insert, update, and delete data. 4. Learn advanced skills: subquery and window functions. 5. Debugging and optimization: Check syntax, use indexes, avoid SELECT*, and use LIMIT.

MySQL: Structured Data and Relational DatabasesMySQL: Structured Data and Relational DatabasesApr 18, 2025 am 12:22 AM

MySQL efficiently manages structured data through table structure and SQL query, and implements inter-table relationships through foreign keys. 1. Define the data format and type when creating a table. 2. Use foreign keys to establish relationships between tables. 3. Improve performance through indexing and query optimization. 4. Regularly backup and monitor databases to ensure data security and performance optimization.

MySQL: Key Features and Capabilities ExplainedMySQL: Key Features and Capabilities ExplainedApr 18, 2025 am 12:17 AM

MySQL is an open source relational database management system that is widely used in Web development. Its key features include: 1. Supports multiple storage engines, such as InnoDB and MyISAM, suitable for different scenarios; 2. Provides master-slave replication functions to facilitate load balancing and data backup; 3. Improve query efficiency through query optimization and index use.

The Purpose of SQL: Interacting with MySQL DatabasesThe Purpose of SQL: Interacting with MySQL DatabasesApr 18, 2025 am 12:12 AM

SQL is used to interact with MySQL database to realize data addition, deletion, modification, inspection and database design. 1) SQL performs data operations through SELECT, INSERT, UPDATE, DELETE statements; 2) Use CREATE, ALTER, DROP statements for database design and management; 3) Complex queries and data analysis are implemented through SQL to improve business decision-making efficiency.

MySQL for Beginners: Getting Started with Database ManagementMySQL for Beginners: Getting Started with Database ManagementApr 18, 2025 am 12:10 AM

The basic operations of MySQL include creating databases, tables, and using SQL to perform CRUD operations on data. 1. Create a database: CREATEDATABASEmy_first_db; 2. Create a table: CREATETABLEbooks(idINTAUTO_INCREMENTPRIMARYKEY, titleVARCHAR(100)NOTNULL, authorVARCHAR(100)NOTNULL, published_yearINT); 3. Insert data: INSERTINTObooks(title, author, published_year)VA

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

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Tools

DVWA

DVWA

Damn Vulnerable Web App (DVWA) is a PHP/MySQL web application that is very vulnerable. Its main goals are to be an aid for security professionals to test their skills and tools in a legal environment, to help web developers better understand the process of securing web applications, and to help teachers/students teach/learn in a classroom environment Web application security. The goal of DVWA is to practice some of the most common web vulnerabilities through a simple and straightforward interface, with varying degrees of difficulty. Please note that this software

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

Safe Exam Browser

Safe Exam Browser

Safe Exam Browser is a secure browser environment for taking online exams securely. This software turns any computer into a secure workstation. It controls access to any utility and prevents students from using unauthorized resources.

SublimeText3 Linux new version

SublimeText3 Linux new version

SublimeText3 Linux latest version

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