电脑知识|欧美黑人一区二区三区|软件|欧美黑人一级爽快片淫片高清|系统|欧美黑人狂野猛交老妇|数据库|服务器|编程开发|网络运营|知识问答|技术教程文章 - 好吧啦网

您的位置:首頁技術文章
文章詳情頁

Mysql排序和分頁(order by&limit)及存在的坑

瀏覽:91日期:2023-10-11 10:44:46

排序查詢(order by)

電商中:我們想查看今天所有成交的訂單,按照交易額從高到低排序,此時我們可以使用數據庫中的排序功能來完成。

排序語法:

select 字段名 from 表名 order by 字段1 [asc|desc],字段2 [asc|desc]; 需要排序的字段跟在order by之后; asc|desc表示排序的規則,asc:升序,desc:降序,默認為asc; 支持多個字段進行排序,多字段排序之間用逗號隔開。

單字段排序

mysql> create table test2(a int,b varchar(10));Query OK, 0 rows affected (0.01 sec)mysql> insert into test2 values (10,’jack’),(8,’tom’),(5,’ready’),(100,’javacode’);Query OK, 4 rows affected (0.00 sec)Records: 4 Duplicates: 0 Warnings: 0mysql> select * from test2;+------+----------+| a | b |+------+----------+| 10 | jack || 8 | tom || 5 | ready || 100 | javacode |+------+----------+4 rows in set (0.00 sec)mysql> select * from test2 order by a asc;+------+----------+| a | b |+------+----------+| 5 | ready || 8 | tom || 10 | jack || 100 | javacode |+------+----------+4 rows in set (0.00 sec)mysql> select * from test2 order by a desc;+------+----------+| a | b |+------+----------+| 100 | javacode || 10 | jack || 8 | tom || 5 | ready |+------+----------+4 rows in set (0.00 sec)mysql> select * from test2 order by a;+------+----------+| a | b |+------+----------+| 5 | ready || 8 | tom || 10 | jack || 100 | javacode |+------+----------+4 rows in set (0.00 sec)

多字段排序

比如學生表,先按學生年齡降序,年齡相同時,再按學號升序,如下:

mysql> create table stu(id int not null comment ’學號’ primary key,age tinyint not null comment ’年齡’,name varchar(16) comment ’姓名’);Query OK, 0 rows affected (0.01 sec)mysql> insert into stu (id,age,name) values (1001,18,’路人甲Java’),(1005,20,’劉德華’),(1003,18,’張學友’),(1004,20,’張國榮’),(1010,19,’梁朝偉’);Query OK, 5 rows affected (0.00 sec)Records: 5 Duplicates: 0 Warnings: 0mysql> select * from stu;+------+-----+---------------+| id | age | name |+------+-----+---------------+| 1001 | 18 | 路人甲Java || 1003 | 18 | 張學友 || 1004 | 20 | 張國榮 || 1005 | 20 | 劉德華 || 1010 | 19 | 梁朝偉 |+------+-----+---------------+5 rows in set (0.00 sec)mysql> select * from stu order by age desc,id asc;+------+-----+---------------+| id | age | name |+------+-----+---------------+| 1004 | 20 | 張國榮 || 1005 | 20 | 劉德華 || 1010 | 19 | 梁朝偉 || 1001 | 18 | 路人甲Java || 1003 | 18 | 張學友 |+------+-----+---------------+5 rows in set (0.00 sec)

按別名排序

mysql> select * from stu;+------+-----+---------------+| id | age | name |+------+-----+---------------+| 1001 | 18 | 路人甲Java || 1003 | 18 | 張學友 || 1004 | 20 | 張國榮 || 1005 | 20 | 劉德華 || 1010 | 19 | 梁朝偉 |+------+-----+---------------+5 rows in set (0.00 sec)mysql> select age ’年齡’,id as ’學號’ from stu order by 年齡 asc,學號 desc;+--------+--------+| 年齡 | 學號 |+--------+--------+| 18 | 1003 || 18 | 1001 || 19 | 1010 || 20 | 1005 || 20 | 1004 |+--------+--------+

按函數排序

有學生表(id:編號,birth:出生日期,name:姓名),如下:

mysql> drop table if exists student;Query OK, 0 rows affected (0.01 sec)mysql> CREATE TABLE student ( -> id int(11) NOT NULL COMMENT ’學號’, -> birth date NOT NULL COMMENT ’出生日期’, -> name varchar(16) DEFAULT NULL COMMENT ’姓名’, -> PRIMARY KEY (id) -> );Query OK, 0 rows affected (0.01 sec)mysql> insert into student (id,birth,name) values (1001,’1990-10-10’,’路人甲Java’),(1005,’1960-03-01’,’劉德華’),(1003,’1960-08-16’,’張學友’),(1004,’1968-07-01’,’張國榮’),(1010,’1962-05-16’,’梁朝偉’);Query OK, 5 rows affected (0.00 sec)Records: 5 Duplicates: 0 Warnings: 0mysql>mysql> SELECT * FROM student;+------+------------+---------------+| id | birth | name |+------+------------+---------------+| 1001 | 1990-10-10 | 路人甲Java || 1003 | 1960-08-16 | 張學友 || 1004 | 1968-07-01 | 張國榮 || 1005 | 1960-03-01 | 劉德華 || 1010 | 1962-05-16 | 梁朝偉 |+------+------------+---------------+5 rows in set (0.00 sec)

需求:按照出生年份升序、編號升序,查詢出編號、出生日期、出生年份、姓名,2種寫法如下:

mysql> SELECT id 編號,birth 出生日期,year(birth) 出生年份,name 姓名 from student ORDER BY year(birth) asc,id asc;+--------+--------------+--------------+---------------+| 編號 | 出生日期 | 出生年份 | 姓名 |+--------+--------------+--------------+---------------+| 1003 | 1960-08-16 | 1960 | 張學友 || 1005 | 1960-03-01 | 1960 | 劉德華 || 1010 | 1962-05-16 | 1962 | 梁朝偉 || 1004 | 1968-07-01 | 1968 | 張國榮 || 1001 | 1990-10-10 | 1990 | 路人甲Java |+--------+--------------+--------------+---------------+5 rows in set (0.00 sec)mysql> SELECT id 編號,birth 出生日期,year(birth) 出生年份,name 姓名 from student ORDER BY 出生年份 asc,id asc;+--------+--------------+--------------+---------------+| 編號 | 出生日期 | 出生年份 | 姓名 |+--------+--------------+--------------+---------------+| 1003 | 1960-08-16 | 1960 | 張學友 || 1005 | 1960-03-01 | 1960 | 劉德華 || 1010 | 1962-05-16 | 1962 | 梁朝偉 || 1004 | 1968-07-01 | 1968 | 張國榮 || 1001 | 1990-10-10 | 1990 | 路人甲Java |+--------+--------------+--------------+---------------+5 rows in set (0.00 sec)

說明:year函數:屬于日期函數,可以獲取對應日期中的年份。上面使用了2種方式排序,第一種是在order by中使用了函數,第二種是使用了別名排序。

where之后進行排序

有訂單數據如下:

mysql> drop table if exists t_order;Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> create table t_order( -> id int not null auto_increment comment ’訂單編號’, -> price decimal(10,2) not null default 0 comment ’訂單金額’, -> primary key(id) -> )comment ’訂單表’;Query OK, 0 rows affected (0.01 sec)mysql> insert into t_order (price) values (88.95),(100.68),(500),(300),(20.88),(200.5);Query OK, 6 rows affected (0.00 sec)Records: 6 Duplicates: 0 Warnings: 0mysql> select * from t_order;+----+--------+| id | price |+----+--------+| 1 | 88.95 || 2 | 100.68 || 3 | 500.00 || 4 | 300.00 || 5 | 20.88 || 6 | 200.50 |+----+--------+6 rows in set (0.00 sec)

需求:查詢訂單金額>=100的,按照訂單金額降序排序,顯示2列數據,列頭:訂單編號、訂單金額,如下:

mysql> select a.id 訂單編號,a.price 訂單金額 from t_order a where a.price>=100 order by a.price desc;+--------------+--------------+| 訂單編號 | 訂單金額 |+--------------+--------------+| 3 | 500.00 || 4 | 300.00 || 6 | 200.50 || 2 | 100.68 |+--------------+--------------+4 rows in set (0.00 sec)

limit介紹

limit用來限制select查詢返回的行數,常用于分頁等操作。

語法:

select 列 from 表 limit [offset,] count;

說明:

offset:表示偏移量,通俗點講就是跳過多少行,offset可以省略,默認為0,表示跳過0行;范圍:[0,+∞)。 count:跳過offset行之后開始取數據,取count行記錄;范圍:[0,+∞)。 limit中offset和count的值不能用表達式。

下面我們列一些常用的示例來加深理解。

獲取前n行記錄

select 列 from 表 limit 0,n;或者select 列 from 表 limit n;

示例,獲取訂單的前2條記錄,如下:

mysql> create table t_order( -> id int not null auto_increment comment ’訂單編號’, -> price decimal(10,2) not null default 0 comment ’訂單金額’, -> primary key(id) -> )comment ’訂單表’;Query OK, 0 rows affected (0.01 sec)mysql> insert into t_order (price) values (88.95),(100.68),(500),(300),(20.88),(200.5);Query OK, 6 rows affected (0.01 sec)Records: 6 Duplicates: 0 Warnings: 0mysql> select * from t_order;+----+--------+| id | price |+----+--------+| 1 | 88.95 || 2 | 100.68 || 3 | 500.00 || 4 | 300.00 || 5 | 20.88 || 6 | 200.50 |+----+--------+6 rows in set (0.00 sec)mysql> select a.id 訂單編號,a.price 訂單金額 from t_order a limit 2;+--------------+--------------+| 訂單編號 | 訂單金額 |+--------------+--------------+| 1 | 88.95 || 2 | 100.68 |+--------------+--------------+2 rows in set (0.00 sec)mysql> select a.id 訂單編號,a.price 訂單金額 from t_order a limit 0,2;+--------------+--------------+| 訂單編號 | 訂單金額 |+--------------+--------------+| 1 | 88.95 || 2 | 100.68 |+--------------+--------------+2 rows in set (0.00 sec)

獲取最大的一條記錄

我們需要獲取訂單金額最大的一條記錄,可以這么做:先按照金額降序,然后取第一條記錄,如下:

mysql> select a.id 訂單編號,a.price 訂單金額 from t_order a order by a.price desc;+--------------+--------------+| 訂單編號 | 訂單金額 |+--------------+--------------+| 3 | 500.00 || 4 | 300.00 || 6 | 200.50 || 2 | 100.68 || 1 | 88.95 || 5 | 20.88 |+--------------+--------------+6 rows in set (0.00 sec)mysql> select a.id 訂單編號,a.price 訂單金額 from t_order a order by a.price desc limit 1;+--------------+--------------+| 訂單編號 | 訂單金額 |+--------------+--------------+| 3 | 500.00 |+--------------+--------------+1 row in set (0.00 sec)mysql> select a.id 訂單編號,a.price 訂單金額 from t_order a order by a.price desc limit 0,1;+--------------+--------------+| 訂單編號 | 訂單金額 |+--------------+--------------+| 3 | 500.00 |+--------------+--------------+1 row in set (0.00 sec)

獲取排名第n到m的記錄

我們需要先跳過n-1條記錄,然后取m-n+1條記錄,如下:

select 列 from 表 limit n-1,m-n+1;

如:我們想獲取訂單金額最高的3到5名的記錄,我們需要跳過2條,然后獲取3條記錄,如下:

mysql> select a.id 訂單編號,a.price 訂單金額 from t_order a order by a.price desc;+--------------+--------------+| 訂單編號 | 訂單金額 |+--------------+--------------+| 3 | 500.00 || 4 | 300.00 || 6 | 200.50 || 2 | 100.68 || 1 | 88.95 || 5 | 20.88 |+--------------+--------------+6 rows in set (0.00 sec)mysql> select a.id 訂單編號,a.price 訂單金額 from t_order a order by a.price desc limit 2,3;+--------------+--------------+| 訂單編號 | 訂單金額 |+--------------+--------------+| 6 | 200.50 || 2 | 100.68 || 1 | 88.95 |+--------------+--------------+3 rows in set (0.00 sec)

分頁查詢

開發過程中,分頁我們經常使用,分頁一般有2個參數:page:表示第幾頁,從1開始,范圍[1,+∞)pageSize:每頁顯示多少條記錄,范圍[1,+∞)如:page = 2,pageSize = 10,表示獲取第2頁10條數據。我們使用limit實現分頁,語法如下:

select 列 from 表名 limit (page - 1) * pageSize,pageSize;

需求:我們按照訂單金額降序,每頁顯示2條,依次獲取所有訂單數據、第1頁、第2頁、第3頁數據,如下:

mysql> select a.id 訂單編號,a.price 訂單金額 from t_order a order by a.price desc;+--------------+--------------+| 訂單編號 | 訂單金額 |+--------------+--------------+| 3 | 500.00 || 4 | 300.00 || 6 | 200.50 || 2 | 100.68 || 1 | 88.95 || 5 | 20.88 |+--------------+--------------+6 rows in set (0.00 sec)mysql> select a.id 訂單編號,a.price 訂單金額 from t_order a order by a.price desc limit 0,2;+--------------+--------------+| 訂單編號 | 訂單金額 |+--------------+--------------+| 3 | 500.00 || 4 | 300.00 |+--------------+--------------+2 rows in set (0.00 sec)mysql> select a.id 訂單編號,a.price 訂單金額 from t_order a order by a.price desc limit 2,2;+--------------+--------------+| 訂單編號 | 訂單金額 |+--------------+--------------+| 6 | 200.50 || 2 | 100.68 |+--------------+--------------+2 rows in set (0.00 sec)mysql> select a.id 訂單編號,a.price 訂單金額 from t_order a order by a.price desc limit 4,2;+--------------+--------------+| 訂單編號 | 訂單金額 |+--------------+--------------+| 1 | 88.95 || 5 | 20.88 |+--------------+--------------+2 rows in set (0.00 sec)

避免踩坑

limit中不能使用表達式

mysql> select * from t_order where limit 1,4+1;ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ’limit 1,4+1’ at line 1mysql> select * from t_order where limit 1+0;ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ’limit 1+0’ at line 1mysql>

結論:limit后面只能夠跟明確的數字。

limit后面的2個數字不能為負數

mysql> select * from t_order where limit -1;ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ’limit -1’ at line 1mysql> select * from t_order where limit 0,-1;ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ’limit 0,-1’ at line 1mysql> select * from t_order where limit -1,-1;ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ’limit -1,-1’ at line 1

排序分頁存在的坑

準備數據:

mysql> insert into test1 (b) values (1),(2),(3),(4),(2),(2),(2),(2);Query OK, 8 rows affected (0.01 sec)Records: 8 Duplicates: 0 Warnings: 0mysql> select * from test1;+---+---+| a | b |+---+---+| 1 | 1 || 2 | 2 || 3 | 3 || 4 | 4 || 5 | 2 || 6 | 2 || 7 | 2 || 8 | 2 |+---+---+8 rows in set (0.00 sec)mysql> select * from test1 order by b asc;+---+---+| a | b |+---+---+| 1 | 1 || 2 | 2 || 5 | 2 || 6 | 2 || 7 | 2 || 8 | 2 || 3 | 3 || 4 | 4 |+---+---+8 rows in set (0.00 sec)

下面我們按照b升序,每頁2條數據,來獲取數據。

下面的sql依次為第1頁、第2頁、第3頁、第4頁、第5頁的數據,如下:

mysql> select * from test1 order by b asc limit 0,2;+---+---+| a | b |+---+---+| 1 | 1 || 2 | 2 |+---+---+2 rows in set (0.00 sec)mysql> select * from test1 order by b asc limit 2,2;+---+---+| a | b |+---+---+| 8 | 2 || 6 | 2 |+---+---+2 rows in set (0.00 sec)mysql> select * from test1 order by b asc limit 4,2;+---+---+| a | b |+---+---+| 6 | 2 || 7 | 2 |+---+---+2 rows in set (0.00 sec)mysql> select * from test1 order by b asc limit 6,2;+---+---+| a | b |+---+---+| 3 | 3 || 4 | 4 |+---+---+2 rows in set (0.00 sec)mysql> select * from test1 order by b asc limit 7,2;+---+---+| a | b |+---+---+| 4 | 4 |+---+---+1 row in set (0.00 sec)

上面有2個問題:

問題1:看一下第2個sql和第3個sql,分別是第2頁和第3頁的數據,結果出現了相同的數據,是不是懵逼了。

問題2:整個表只有8條記錄,怎么會出現第5頁的數據呢,又懵逼了。

我們來分析一下上面的原因:主要是b字段存在相同的值,當排序過程中存在相同的值時,沒有其他排序規則時,mysql懵逼了,不知道怎么排序了。

就像我們上學站隊一樣,按照身高排序,那身高一樣的時候如何排序呢?身高一樣的就亂排了。

建議:排序中存在相同的值時,需要再指定一個排序規則,通過這種排序規則不存在二義性,比如上面可以再加上a降序,如下:

mysql> select * from test1 order by b asc,a desc;+---+---+| a | b |+---+---+| 1 | 1 || 8 | 2 || 7 | 2 || 6 | 2 || 5 | 2 || 2 | 2 || 3 | 3 || 4 | 4 |+---+---+8 rows in set (0.00 sec)mysql> select * from test1 order by b asc,a desc limit 0,2;+---+---+| a | b |+---+---+| 1 | 1 || 8 | 2 |+---+---+2 rows in set (0.00 sec)mysql> select * from test1 order by b asc,a desc limit 2,2;+---+---+| a | b |+---+---+| 7 | 2 || 6 | 2 |+---+---+2 rows in set (0.00 sec)mysql> select * from test1 order by b asc,a desc limit 4,2;+---+---+| a | b |+---+---+| 5 | 2 || 2 | 2 |+---+---+2 rows in set (0.00 sec)mysql> select * from test1 order by b asc,a desc limit 6,2;+---+---+| a | b |+---+---+| 3 | 3 || 4 | 4 |+---+---+2 rows in set (0.00 sec)mysql> select * from test1 order by b asc,a desc limit 8,2;Empty set (0.00 sec)

看上面的結果,分頁數據都正常了,第5頁也沒有數據了。

總結

order by … [asc|desc]用于對查詢結果排序,asc:升序,desc:降序,asc|desc可以省略,默認為asc limit用來限制查詢結果返回的行數,有2個參數(offset,count),offset:表示跳過多少行,count:表示跳過offset行之后取count行 limit中offset可以省略,默認值為0 limit中offset 和 count都必須大于等于0 limit中offset和count的值不能用表達式 分頁排序時,排序不要有二義性,二義性情況下可能會導致分頁結果亂序,可以在后面追加一個主鍵排序

到此這篇關于Mysql排序和分頁(order by&limit)及存在的坑的文章就介紹到這了,更多相關Mysql排序和分頁內容請搜索好吧啦網以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持好吧啦網!

標簽: MySQL 數據庫
相關文章:
主站蜘蛛池模板: 医疗仪器模块 健康一体机 多参数监护仪 智慧医疗仪器方案定制 血氧监护 心电监护 -朗锐慧康 | 【官网】博莱特空压机,永磁变频空压机,螺杆空压机-欧能优 | 自动部分收集器,进口无油隔膜真空泵,SPME固相微萃取头-上海楚定分析仪器有限公司 | 工业rfid读写器_RFID工业读写器_工业rfid设备厂商-ANDEAWELL | 机房监控|动环监控|动力环境监控系统方案产品定制厂家 - 迈世OMARA | 桐城新闻网—桐城市融媒体中心主办 | 数码听觉统合训练系统-儿童感觉-早期言语评估与训练系统-北京鑫泰盛世科技发展有限公司 | 纳米二氧化硅,白炭黑,阴离子乳化剂-臻丽拾科技 | 免费网站网址收录网_海企优网站推荐平台 | 江苏农村商业银行招聘网_2024江苏农商行考试指南_江苏农商行校园招聘 | MVE振动电机_MVE震动电机_MVE卧式振打电机-河南新乡德诚生产厂家 | 南京雕塑制作厂家-不锈钢雕塑制作-玻璃钢雕塑制作-先登雕塑厂 | 分光色差仪,测色仪,反透射灯箱,爱色丽分光光度仪,美能达色差仪维修_苏州欣美和仪器有限公司 | 低合金板|安阳低合金板|河南低合金板|高强度板|桥梁板_安阳润兴 北京租车牌|京牌指标租赁|小客车指标出租 | 江西自考网 | 防伪溯源|防窜货|微信二维码营销|兆信_行业内领先的防伪防窜货数字化营销解决方案供应商 | 万家财经_财经新闻_在线财经资讯网 | 【直乐】河北石家庄脊柱侧弯医院_治疗椎间盘突出哪家医院好_骨科脊柱外科专业医院_治疗抽动症/关节病骨伤权威医院|排行-直乐矫形中医医院 | 必胜高考网_全国高考备考和志愿填报信息平台 | 宁波普瑞思邻苯二甲酸盐检测仪,ROHS2.0检测设备,ROHS2.0测试仪厂家 | 不锈钢丸厂家,铝丸,铸钢丸-淄博智源铸造材料有限公司 | 辐射色度计-字符亮度测试-反射式膜厚仪-苏州瑞格谱光电科技有限公司 | 欧美日韩国产一区二区三区不_久久久久国产精品无码不卡_亚洲欧洲美洲无码精品AV_精品一区美女视频_日韩黄色性爱一级视频_日本五十路人妻斩_国产99视频免费精品是看4_亚洲中文字幕无码一二三四区_国产小萍萍挤奶喷奶水_亚洲另类精品无码在线一区 | 精密钢管,冷拔精密无缝钢管,精密钢管厂,精密钢管制造厂家,精密钢管生产厂家,山东精密钢管厂家 | 滚筒烘干机_转筒烘干机_滚筒干燥机_转筒干燥机_回转烘干机_回转干燥机-设备生产厂家 | 纸塑分离机-纸塑分离清洗机设备-压力筛-碎浆机厂家金双联环保 | lcd条形屏-液晶长条屏-户外广告屏-条形智能显示屏-深圳市条形智能电子有限公司 | 机制砂选粉机_砂石选粉机厂家-盐城市助成粉磨科技有限公司 | 欧必特空气能-商用空气能热水工程,空气能热水器,超低温空气源热泵生产厂家-湖南欧必特空气能公司 | 电加热导热油炉-空气加热器-导热油加热器-翅片电加热管-科安达机械 | 衬氟止回阀_衬氟闸阀_衬氟三通球阀_衬四氟阀门_衬氟阀门厂-浙江利尔多阀门有限公司 | 阻垢剂,反渗透阻垢剂,缓蚀阻垢剂-山东普尼奥水处理科技有限公司 真空粉体取样阀,电动楔式闸阀,电动针型阀-耐苛尔(上海)自动化仪表有限公司 | 安平县鑫川金属丝网制品有限公司,防风抑尘网,单峰防风抑尘,不锈钢防风抑尘网,铝板防风抑尘网,镀铝锌防风抑尘网 | 带式过滤机厂家_价格_型号规格参数-江西核威环保科技有限公司 | 车间除尘设备,VOCs废气处理,工业涂装流水线,伸缩式喷漆房,自动喷砂房,沸石转轮浓缩吸附,机器人喷粉线-山东创杰智慧 | 郑州水质检测中心_井水检测_河南废气检测_河南中环嘉创检测 | 百方网-百方电气网,电工电气行业专业的B2B电子商务平台 | loft装修,上海嘉定酒店式公寓装修公司—曼城装饰 | 常州律师事务所_常州律所_常州律师-江苏乐天律师事务所 | 顶空进样器-吹扫捕集仪-热脱附仪-二次热解吸仪-北京华盛谱信仪器 | WF2户外三防照明配电箱-BXD8050防爆防腐配电箱-浙江沃川防爆电气有限公司 |