几个数据库案例分析

We will learn about several  function in the first place.

firstly,character string function

 

 

secondly, system function

 

 

thirdly,mathematic(al) function

 

finnally,Date and Time Functions

 

 

案例一

 

• 某公司印了一批充值卡，卡的密码是随机生成的，现在出现这个问题：

    卡里面的“O和0”（哦和零）“i和1”（哎和一），用户反映说看不清楚，公司决定，把存储在数据库中的密码中所有的“哦”都改成“零”，把所有的“i”都改成“1”；

    请编写SQL语句实现以上要求；

    数据库表名：Card；密码字段名：PassWord；

•

分析：

答案一：

Update Card

Set    PassWord = Replace(Replace(PassWord ,'O','0'),'i','1')

答案二：

Update Card Set PassWord = Replace(PassWord ,'O','0')

Update Card Set PassWord = Replace(PassWord ,'i','1')

 

案例二

 

• 在数据库表中有以下字符数据，如：

14-1、14-2、 13-1、13-2、13-3、13-10、13-100、13-108、13- 18、13-11、13-15

现在希望通过SQL语句进行排序，并且首先要按照前半部分的数字进 行排序，然后再按照后半部分的数字进行排需，输出要排成这样：

13-1、13-2、13-3、13-10、13-11、13-15、13-18、13-100、13- 108、14-1、14-2

• 数据库表名：SellRecord；字段名：ListNumber；

• 这是更新语句，需要使用UPDATE语句；

 因为牵涉到字符串的替换，需要使用到SQL Server中的函数Replace；

答案一：

Update Card Set PassWord = Replace(Replace(PassWord ,'O','0'),'i','1')

答案二：

Update Card Set PassWord = Replace(PassWord ,'O','0')

Update Card Set PassWord = Replace(PassWord ,'i','1')

• 这是更新语句，需要使用UPDATE语句；

• 因为牵涉到字符串的替换，需要使用到SQL Server中的函数Replace；

 分析：

• 这是查询语句，需要使用SELECT语句

• 需要使用到ORDER BY进行排序，并且在ORDER BY的排序列中，也需要重新计算出排序的数字来

• 前半部分的数字，可以从先找到“-”符号的位置，然后，取其左半部分，最后再使用Convert函数将其转换为数字：

Convert(int, Left(ListNumber, CharIndex('-', ListNumber)-1))

• 后半部分的数字，可以先找到“-”符号的位置，然后把从第一个位置到该位置的全部字符替换为空格，最后再使用Convert函数将其转换为数字：

Convert(int, Stuff(ListNumber,1, Charindex('-', ListNumber), ''))

 答案：

SELECT ListNumber

FROM    SellRecord

ORDER BY

Convert(int, Left(ListNumber, CharIndex('-', ListNumber)-1)),

Convert(int, Stuff(ListNumber,1, Charindex('-', ListNumber), ''))

案例三

 

学员内部测试成绩查询的每次显示的都是学员的编号信息，

因为该表中只存储了学员的编号；

实际上最好显示学员的姓名，而姓名存储在学员信息表；

如何同时从这两个表中取得数据？

内联结

写法一：

 

SELECT S.SName,C.CourseID,C.Score

From Score AS C

INNER JOIN Students AS S

ON C.StudentID = S.SCode

写法二：

 

SELECT Students.SName, Score.CourseID, Score.Score

FROM Students,Score

WHERE  Students.SCode = Score.StudentID

写法三：

 

SELECT

S.SName AS 姓名, CS.CourseName AS 课程, C.Score AS 成绩

FROM Students AS S

INNER JOIN Score AS C ON (S.SCode = C.StudentID)

INNER JOIN Course AS CS ON (CS.CourseID = C.CourseID)

左外联结：

 

SELECT S.SName,C.CourseID,C.Score

From Students AS S

LEFT JOIN Score AS C

ON C.StudentID = S.SCode

右外联结：

 

SELECT Titles.Title_id, Titles.Title, Publishers.Pub_name

FROM titles

RIGHT OUTER JOIN Publishers

ON Titles.Pub_id = Publishers.Pub_id

案例四

 

• 在数据库表中，数据行位置并不重要，但是一个单位中要根据奇数行和偶数行的数据来汇总，在这个汇总的基础上再得到一个数值，因此，要查询数据库表的奇数行和偶数行的总数

• 数据库表名：TBL

字段名：A

主键字段：IDKEY（标识列，种子：1；增长量：1）

分析：

 

• 只能依靠标识列的值来进行判断和选取

• 因为数据行可能存在增加、修改和删除，因此标识列的数据值并不“完全可靠”，例如标识列值为3，并不一定是第三行，因为如果第二行被删除了，它就是第二行，实际上也是偶数行

• 根据我们前面使用过的SELECT…INTO，可以创建一张新表，顺便创建新的标识列，再在新的标识列上执行奇偶判断

• 奇数判断依据：标识列值%2不等于0；偶数判断依据：标识列值%2等于0

 答案：

SELECT A, IDENTITY(int,1,1) AS ID

INTO TEMPTABLE

FROM TBL

SELECT SUM(A) AS 奇数列汇总

FROM TEMPTABLE

WHERE ID%2 <> 0

SELECT SUM(A) FROM AS 偶数列汇总

FROM TEMPTABLE

WHERE ID%2 = 0

案例五

 

• 一家银行发行了新的信用卡，刚开始的时候推广得很好，但是逐渐废卡也越来越多（卡上的余额少于2元，并且用户长时间不使用该卡），因此银行在二月份把这些少于2元的卡从都数据库表中删除了，但是很快问题就来了，用户发现他的卡再也不能使用而投诉，因此只能再把这些卡恢复。

 

•

分析：

 

• 这种方式的数据插入，不是固定的数据项，而是从其它表中筛选数据再插入，因此要使用到INSERT INTO…SELECT结构

• SELECT的任务就是找出两个表中不同的项

• 在前面的联结查询中，使用INNER JOIN…ON可以找出相同的项，取相反的情况，是否可以找出不同的项呢？编写以下T-SQL：

 SELECT M.CardID,2 FROM M INNER JOIN S ON (S.CardID<>M.CardID)

   此方法两个表之间是进行“交叉”对比的，而不是一一对比，所以会对比出很多“不同”的项来；因此，这种方法不可行

• 考虑我们在上面的左外连接查询，能够查询出左表(From)中存在而相关表(left join)不存在的数据项，并以NULL填充

•

答案：

INSERT INTO S (S.CardID,S.Score)

SELECT M.CardID,2

FROM 信息表 as M

LEFT JOIN 帐号表 as S

ON (S.CardID=M.CardID)

WHERE S.CardID IS NULL

最后，使用WHERE S.CardID IS NULL可以判断数据项是否存在子表中；并且按 M.CardID,2 插入帐号和常量项 2(元)

编写SQL，把CardID为23、30等在子表中没有的记录插入到子表中，插入的数据Score项值为2……