postgresql中的order by、limit、offset偏移

清疚 2021-07-25 12:07 491阅读 0赞

首先创建users表

create table users(
    	id serial primary key,
    	player varchar(255) not null,
    	score real,
    	team varchar(255)
    )

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzIzMjU2_size_16_color_FFFFFF_t_70][]

插入记录并select

insert into users(player,score,team) values
    	('库里',28.3,'勇士'),
    	('哈登',30.2,'火箭'),
    	('阿杜',25.6,'勇士'),
    	('阿詹',27.8,'骑士'),
    	('神龟',31.3,'雷霆'),
    	('白边',19.8,'热火');

查询所有记录：select \* from users

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzIzMjU2_size_16_color_FFFFFF_t_70 1][]

这就是users中所有的记录

*  order by：排序。asc（升序，默认为asc）,desc（降序）
 *  limit：限制 SELECT 语句中查询的数据的数量。也就是从表中从上到下取出指定的记录数
 *  offset：偏移。从一个特定的偏移开始提取记录

**select \* from users order by score;**//默认为升序

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzIzMjU2_size_16_color_FFFFFF_t_70 2][]

**select \* from users order by score asc;**//升序

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzIzMjU2_size_16_color_FFFFFF_t_70 3][]

**select \* from users order by score desc;**//降序

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzIzMjU2_size_16_color_FFFFFF_t_70 4][]

**select \* from users order by team**;//拼音首字母排序，如果首字母相同，则排拼音第二个字母，以此类推

![20201018141841202.png][]

**select \* from users order by team,score;**//先对team升序排序，再在排序后的表中对score升序排序。如下

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzIzMjU2_size_16_color_FFFFFF_t_70 5][]

**select \* from users order by team,score desc;**//先对team升序排序，再在排序后的表中对score降序排序。如下

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzIzMjU2_size_16_color_FFFFFF_t_70 6][]

**select \* from users order by score desc limit 4**

![20201018142647543.png][]

**select \* from users order by score desc limit 4 offset 1**

![20201018142715553.png][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzIzMjU2_size_16_color_FFFFFF_t_70]: /images/20210725/b99c692aa8384c9b8b9b771c792fcf27.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzIzMjU2_size_16_color_FFFFFF_t_70 1]: /images/20210725/6e9c1da9c6f3449f8a26612d351886df.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzIzMjU2_size_16_color_FFFFFF_t_70 2]: /images/20210725/8c0472360a9d48dda9dada791077c717.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzIzMjU2_size_16_color_FFFFFF_t_70 3]: /images/20210725/3e00bc939e334a05a17acf1247c00929.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzIzMjU2_size_16_color_FFFFFF_t_70 4]: /images/20210725/cd26329e4ce4471a9eadc4c3b6e36e84.png
[20201018141841202.png]: /images/20210725/62e1eeea19c640668bf0b6e29e8f940e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzIzMjU2_size_16_color_FFFFFF_t_70 5]: /images/20210725/5fca07d24efb402a8cdefc28b9be5fa3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzIzMjU2_size_16_color_FFFFFF_t_70 6]: /images/20210725/ce42e7b8ad804b91a20a219d20373d78.png
[20201018142647543.png]: /images/20210725/653a6e4cfff247dea1954c06df6f9cef.png
[20201018142715553.png]: /images/20210725/04ab23e24cd54d729ce474c3380685fa.png