熊伟的个人博客

巩固数据库基础之SQL

2017/10/27 datebase

巩固数据库基础之SQL

最近在工作中写sql语句时，遇到了以前在学校学习sql过程中没见过的知识，在此记录。

with as 用法

以前写sql，在遇到查询条件复杂的情况，更多用到的是嵌套查询，尽管也能完成查询任务，但相比我最近新学到的with as就要麻烦很多了。

先来看看两种写法的对比：

/*嵌套的写法*/
select *, enddates-startdates as day
from (
	SELECT date(to_char(ty.a,'9999') || '-03-01')-1  AS enddates, date(to_char(ty.a,'9999') || '-02-01') AS startdates, ty.a as year
FROM (
        SELECT s.a
        FROM generate_series(1980, 2010) as s(a)
     ) as ty
) as adate ;

/*with-as的写法*/
with ty
as
(
    SELECT s.a
    FROM generate_series(1980, 2010) as s(a)
),
adate
as
(
    SELECT date(to_char(ty.a,'9999') || '-03-01')-1  AS enddates, date(to_char(ty.a,'9999') || '-02-01') AS startdates, ty.a as year
    FROM ty
)
select *, enddates-startdates as day
from adate;

从上面两种写法一眼就能看出with as写法相比嵌套写法的优点：

  1. 比嵌套写法更简单（更符合我个人的思维模式），相比来说，如果嵌套的层数过多，那就太复杂了；简单还体现在，如果底层的结果被用在了不止一个地方，那还会造成代码冗余！
  2. 方便阅读，代码通常还得给其他人看，而嵌套的写法最大的缺点就是不易阅读和理解
  3. 便于维护，这其实是方便阅读带来的好处，但这一点也是非常重要的。
  4. with as写法相比嵌套写法的查询效率要高，上面的例子中with as就比嵌套快0.001s（用的PostgreSQL数据库查询，嵌套语句耗时0.003s，with as语句0.002s）！！
  5. 以上4点是我硬凑起来的……无他，我就觉得with as写法比嵌套写法好太多了~！只恨到现在才知道……

写法

  • with as写法的格式： 
    with tmp_table_name1
as
(
  /*第一个查询---实际使用时不要在一句sql的中间写注释*/
  ……
),
tmp_table_name2
as
(
   /*第二个查询---实际使用时不要在一句sql的中间写注释*/
  ……
),
tmp_table_name3
as
(
   /*第三个查询---实际使用时不要在一句sql的中间写注释*/
  ……
)
select *
from tmp_table_name3;

写法实际上在前面的例子中已经体现了，这里只提醒几个注意点：

  • 最后一句（也就是嵌套写法最外面的查询）的前面不要有”, tmp_table_name as“,尤其是那个逗号，千瓦不能有，我才不会告诉你我跳下去这个坑几次……
  • 与嵌套写法类似，with as中的最后一句除了可以是查询外，还可以是插入（insert）、删除（delete）、修改（update）。

（10.30更新）

今天刚好又用到了with as与insert结合的方式，顺便附在这里：

-- with as 与insert结合使用
with
sdata as
(
		select stationid from china_stations limit(723)
),
adata as
(
		select * from sixtysum, sdata where stationid = station
)
insert into sixtysum_723
(
		station, time, rain
)
select station, time, rain from adata;

row_number() over()用法

row_number() over()实际上是两个函数row_number()和over()组合起来用的，它们又叫分析函数（开窗函数？）,下面这篇文章解释的很好，我就偷个懒了，嘿嘿……

引用自SQL 关于row_number() over() 注：引用文章只做学习用，如有侵权，请留言，我会尽快删除。

先建表（dbo.PeopleInfo）：

CREATE TABLE [dbo].[PeopleInfo](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [name] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL,
    [Gender] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL,
    [numb] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL,
    [phone] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL,
    [FenShu] [int] NULL
) ON [PRIMARY]

向表中插入数据：

insert into peopleinfo([name],Gender,numb,phone,fenshu) values ('李欢','男','3223','1365255',80)
insert into peopleinfo([name],Gender,numb,phone,fenshu) values ('李欢','男','322123','1',90)
insert into peopleinfo([name],Gender,numb,phone,fenshu) values ('李名','男','3213112352','13152',56)
insert into peopleinfo([name],Gender,numb,phone,fenshu) values ('李名','女','32132312','13342563',60)
insert into peopleinfo([name],Gender,numb,phone,fenshu) values ('王华','女','3223','1365255',80)

查询出所有插入的数据：

select * from  dbo.PeopleInfo

结果如图：

例子一：只用order by 不用 partition by 的sql语句如下：

--不用partition by
select [name],gender,fenshu, row_number() over(order by fenshu desc) as num from dbo.PeopleInfo

结果如图：

例子二：用order by 也用 partition by 的sql语句如下：

select [name],gender,fenshu, row_number() over(partition by Gender order by fenshu desc) as num from dbo.PeopleInfo

结果如图：

比较例子一和例子二的结果图很容易就明白partition by的用处了，以例子二为例就是先用partition by把性别Gender分成两个区一个男一个女，然后再用order by 把每个区里的分数fenshu从大到小排序。

函数的使用：

第一节的例子里我用到了一个函数generate_series()，这个函数使用来生成一段连续的数字序列的，在实际工作中很有用处。

还有一个函数:regexp_split_to_table(),这个函数用来将一个字符串按给定的字符分割为一个查询集合，与java中的String.split()函数功能相似。

例子：

select a
from regexp_split_to_table('2005, 2006, 2007', E'(, *)+') as s(a);

结果：

上面两个函数在PostgreSQL数据库中有，MySQL中就没有。 所以，以后多注意一些数据库自带的函数，往往能极大提升工作效率

其他

在查询中不要过多的依赖”in()“,如果in()中的参数过多，会导致查询效率非常低。

总结

要学习的还有很多啊……

Search

    Table of Contents