简单记录几个有用的sql查询

下面示例中,查询的数据表参考这一篇的Person表。

一、限制返回的行数

1、Sql Server

 
1SELECT TOP 10 Id,FirstName, LastName
2FROM Person

2、Oracle

 
1SELECT Id,FirstName, LastName
2FROM Person
3WHERE ROWNUM<=10

3、DB2

 
1SELECT Id,FirstName, LastName
2FROM Person FETCH FIRST 10 ROWS ONLY

4、MySql

 
1SELECT Id,FirstName, LastName
2FROM Person LIMIT 10

5、PostgreSQL

 
1SELECT Id,FirstName, LastName
2FROM Person LIMIT 10

小结:查询语句都很基础,MySql和PostgreSQL的写法是相同的,可以看到各个DBMS的sql书写可读性都不错,用户(开发者)使用体验很重要。

 

二、按特定格式查询日期(Sql Server版)

实际开发中通常都直接查询结果,然后用编程语言进行日期格式输出(如C#、Java等),但是数据库同样也提供了这种转换处理能力,下面整理一下Sql Server的常用时间格式查询和对应输出结果:

 
01SELECT CONVERT(VARCHAR(100), GETDATE(), 0)-- 10 17 2010  4:51PM
02SELECT CONVERT(VARCHAR(100), GETDATE(), 1)-- 10/17/10 
03SELECT CONVERT(VARCHAR(100), GETDATE(), 2)-- 10.10.17 
04SELECT CONVERT(VARCHAR(100), GETDATE(), 3)-- 17/10/10 
05SELECT CONVERT(VARCHAR(100), GETDATE(), 4)-- 17.10.10
06SELECT CONVERT(VARCHAR(100), GETDATE(), 5)-- 17-10-10 
07SELECT CONVERT(VARCHAR(100), GETDATE(), 6)-- 17 10 10 
08SELECT CONVERT(VARCHAR(100), GETDATE(), 7)-- 10 17, 10 
09SELECT CONVERT(VARCHAR(100), GETDATE(), 8)-- 16:52:13
10SELECT CONVERT(VARCHAR(100), GETDATE(), 9)-- 10 17 2010  4:52:13:960PM
11SELECT CONVERT(VARCHAR(100), GETDATE(), 10)-- 10-17-10 
12SELECT CONVERT(VARCHAR(100), GETDATE(), 11)-- 10/10/17
13SELECT CONVERT(VARCHAR(100), GETDATE(), 12)-- 101017
14SELECT CONVERT(VARCHAR(100), GETDATE(), 13)-- 17 10 2010 16:53:39:403 
15SELECT CONVERT(VARCHAR(100), GETDATE(), 14)-- 16:53:39:403 
16SELECT CONVERT(VARCHAR(100), GETDATE(), 20)-- 2010-10-17 16:53:39
17SELECT CONVERT(VARCHAR(100), GETDATE(), 21)-- 2010-10-17 16:54:55.100 
18SELECT CONVERT(VARCHAR(100), GETDATE(), 22)-- 10/17/10  4:54:55 PM 
19SELECT CONVERT(VARCHAR(100), GETDATE(), 24)-- 16:54:55 
20SELECT CONVERT(VARCHAR(100), GETDATE(), 25)-- 2010-10-17 16:54:55.100 
21SELECT CONVERT(VARCHAR(100), GETDATE(), 100)-- 10 17 2010  4:54PM 
22SELECT CONVERT(VARCHAR(100), GETDATE(), 101)-- 10/17/2010
23SELECT CONVERT(VARCHAR(100), GETDATE(), 102)-- 2010.10.17 
24SELECT CONVERT(VARCHAR(100), GETDATE(), 103)-- 17/10/2010 
25SELECT CONVERT(VARCHAR(100), GETDATE(), 104)-- 17.10.2010 
26SELECT CONVERT(VARCHAR(100), GETDATE(), 105)-- 17-10-2010 
27SELECT CONVERT(VARCHAR(100), GETDATE(), 106)-- 17 10 2010 
28SELECT CONVERT(VARCHAR(100), GETDATE(), 107)-- 10 17, 2010 
29SELECT CONVERT(VARCHAR(100), GETDATE(), 108)-- 16:56:36 
30SELECT CONVERT(VARCHAR(100), GETDATE(), 109)-- 10 17 2010  4:56:36:370PM 
31SELECT CONVERT(VARCHAR(100), GETDATE(), 110)-- 10-17-2010
32SELECT CONVERT(VARCHAR(100), GETDATE(), 111)-- 2010/10/17 
33SELECT CONVERT(VARCHAR(100), GETDATE(), 112)-- 20101017 
34SELECT CONVERT(VARCHAR(100), GETDATE(), 113)-- 17 10 2010 16:57:51:713 
35SELECT CONVERT(VARCHAR(100), GETDATE(), 114)-- 16:59:19:640
36SELECT CONVERT(VARCHAR(100), GETDATE(), 120)-- 2010-10-17 16:59:19
37SELECT CONVERT(VARCHAR(100), GETDATE(), 121)-- 2010-10-17 16:59:19.640
38SELECT CONVERT(VARCHAR(100), GETDATE(), 126)-- 2010-10-17T16:59:19.640
39SELECT CONVERT(VARCHAR(100), GETDATE(), 130)-- 10 ?? ?????? 1431  4:59:19:640PM

小结:ms真够贴心的,还有哪种格式它没有帮我们实现呢?


三、从表中随机返回n条记录

1、Sql Server

 
1SELECT TOP 10 Id, FirstName, LastName
2FROM Person (NOLOCK)
3ORDER BY NEWID()

2、Oracle

 
1SELECT Id, FirstName, LastName FROM(
2    SELECT Id, FirstName, LastName FROM Person
3    ORDER BY DBMS_RANDOM.VALUE()
4)
5WHERE ROWNUM<=10

3、DB2

 
1SELECT Id, FirstName, LastName FROM Person
2ORDER BY RAND() FETCH  FIRST 10 ROWS ONLY

4、MySql

 
1SELECT Id, FirstName, LastName FROM Person
2ORDER BY RAND() LIMIT 10

5、PostgreSQL

 
1SELECT Id, FirstName, LastName FROM Person
2ORDER BY RANDOM() LIMIT 10

小结:

(1)、对比各数据库产品(DBMS)的sql查询书写方式,可以发现它们的相似之处都需要使用ORDER BY 子句对行进行随机排序,而随机函数都使用各自的内置函数。好玩的地方在于,虽然各个DBMS的随机函数有相似或相同的地方,但是没有一个查询是相同的。

(2)、Oracle的查询方式相比其他稍显啰嗦,可读性稍差,但是可以看出它的思路,理解它的实现原理,对开发者是很有益的。

 

四、将空值转换为实际值

通常情况下,我们通过case when可以实现空值到实际值的转换:

 
1SELECT TOP 10
2Id,
3FirstName,
4CASE 
5WHEN LastName IS NULL THEN ''
6ELSE LastName
7END AS LastName
8FROM
9Person (NOLOCK)

但是,更简洁的写法是使用COALESCE函数:

 
1SELECT TOP 10
2    Id,
3    FirstName,
4    COALESCE(LastName,'') AS LastName
5FROM
6    Person (NOLOCK)

最后,在开发中写出简洁高效的sql语句一直是我孜孜追求的目标,每次碰到动辄上百乃至上千行的sql语句或者存储过程需要维护就头疼不已,尤其是那些业务逻辑比较复杂的,不知道大家在开发中有没有过这种经历。如何规避复杂的sql语句和存储过程开发易维护的系统,不知道您有什么看法或者好的解决方案?欢迎讨论。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/16436858/viewspace-676265/,如需转载,请注明出处,否则将追究法律责任。

转载于:http://blog.itpub.net/16436858/viewspace-676265/

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值