mysql查询中Limit、order的简单示例
本节内容：
mysql查询中Limit、order的用法
例子：
/*
mysql> Drop table Item;
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE TABLE Item
-> (
-> ID SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> Name VARCHAR(50) NOT NULL,
-> InStock SMALLINT UNSIGNED NOT NULL,
-> OnOrder SMALLINT UNSIGNED NOT NULL,
-> Reserved SMALLINT UNSIGNED NOT NULL,
-> Department ENUM('Classical', 'Popular') NOT NULL,
-> Category VARCHAR(20) NOT NULL,
-> RowUpdate TIMESTAMP NOT NULL
-> );
Query OK, 0 rows affected (0.05 sec)
mysql> INSERT INTO Item (Name, InStock, OnOrder, Reserved, Department, Category)
-> VALUES ('Bloodshot', 10, 5, 1, 'Popular',
'Rock'),
-> ('Most', 10, 5, 2, 'Classical',
'Opera'),
-> ('Jazz', 17, 4, 3, 'Popular',
'Jazz'),
-> ('Class', 9, 4, 4, 'Classical',
'Dance'),
-> ('Violin', 24, 2, 5, 'Classical',
'General'),
-> ('Cha Cha', 16, 6, 6, 'Classical',
'Vocal'),
-> ('Blues', 2, 25, 7, 'Popular',
'Blues'),
-> ('Pure', 32, 3, 18, 'Popular',
'Jazz'),
-> ('Mud', 12, 15, 19, 'Popular',
'Country'),
-> ('The', 5, 20, 11, 'Popular',
'New Age'),
-> ('Embrace', 24, 11, 12, 'Popular',
'New Age'),
-> ('Magic', 42, 17, 13, 'Classical',
'General'),
-> ('Lake', 25, 44, 24, 'Classical',
'Dance'),
-> ('LaLala', 20, 10, 5, 'Classical',
'Opera'),
-> ('Soul', 15, 30, 16, 'Popular',
'Blues'),
-> ('Stages', 42, 0, 7, 'Popular',
'Blues'),
-> ('Six', 16, 8, 6, 'Classical',
'General');
Query OK, 17 rows affected (0.00 sec)
Records: 17 Duplicates: 0 Warnings: 0
mysql> select * from Item;
+----+-----------+---------+---------+----------+------------+----------+---------------------+
| ID | Name | InStock | OnOrder | Reserved | Department | Category | RowUpdate |
+----+-----------+---------+---------+----------+------------+----------+---------------------+
| 1 | Bloodshot | 10 | 5 | 1 | Popular | Rock | 2005-10-09 09:19:50 |
| 2 | Most | 10 | 5 | 2 | Classical | Opera | 2005-10-09 09:19:50 |
| 3 | Jazz | 17 | 4 | 3 | Popular | Jazz | 2005-10-09 09:19:50 |
| 4 | Class | 9 | 4 | 4 | Classical | Dance | 2005-10-09 09:19:50 |
| 5 | Violin | 24 | 2 | 5 | Classical | General | 2005-10-09 09:19:50 |
| 6 | Cha Cha | 16 | 6 | 6 | Classical | Vocal | 2005-10-09 09:19:50 |
| 7 | Blues | 2 | 25 | 7 | Popular | Blues | 2005-10-09 09:19:50 |
| 8 | Pure | 32 | 3 | 18 | Popular | Jazz | 2005-10-09 09:19:50 |
| 9 | Mud | 12 | 15 | 19 | Popular | Country | 2005-10-09 09:19:50 |
| 10 | The | 5 | 20 | 11 | Popular | New Age | 2005-10-09 09:19:50 |
| 11 | Embrace | 24 | 11 | 12 | Popular | New Age | 2005-10-09 09:19:50 |
| 12 | Magic | 42 | 17 | 13 | Classical | General | 2005-10-09 09:19:50 |
| 13 | Lake | 25 | 44 | 24 | Classical | Dance | 2005-10-09 09:19:50 |
| 14 | LaLala | 20 | 10 | 5 | Classical | Opera | 2005-10-09 09:19:50 |
| 15 | Soul | 15 | 30 | 16 | Popular | Blues | 2005-10-09 09:19:50 |
| 16 | Stages | 42 | 0 | 7 | Popular | Blues | 2005-10-09 09:19:50 |
| 17 | Six | 16 | 8 | 6 | Classical | General | 2005-10-09 09:19:50 |
+----+-----------+---------+---------+----------+------------+----------+---------------------+
17 rows in set (0.00 sec)
mysql> SELECT ID, Name, InStock
-> FROM Item
-> WHERE Department='Classical'
-> ORDER BY ID DESC
-> LIMIT 3,4;
+----+---------+---------+
| ID | Name | InStock |
+----+---------+---------+
| 12 | Magic | 42 |
| 6 | Cha Cha | 16 |
| 5 | Violin | 24 |
| 4 | Class | 9 |
+----+---------+---------+
4 rows in set (0.00 sec)
*/
Drop table Item;
CREATE TABLE Item
(
ID SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
Name VARCHAR(50) NOT NULL,
InStock SMALLINT UNSIGNED NOT NULL,
OnOrder SMALLINT UNSIGNED NOT NULL,
Reserved SMALLINT UNSIGNED NOT NULL,
Department ENUM('Classical', 'Popular') NOT NULL,
Category VARCHAR(20) NOT NULL,
RowUpdate TIMESTAMP NOT NULL
);
INSERT INTO Item (Name, InStock, OnOrder, Reserved, Department, Category)
VALUES ('Bloodshot', 10, 5, 1, 'Popular', 'Rock'),
('Most', 10, 5, 2, 'Classical', 'Opera'),
('Jazz', 17, 4, 3, 'Popular', 'Jazz'),
('Class', 9, 4, 4, 'Classical', 'Dance'),
('Violin', 24, 2, 5, 'Classical', 'General'),
('Cha Cha', 16, 6, 6, 'Classical', 'Vocal'),
('Blues', 2, 25, 7, 'Popular', 'Blues'),
('Pure', 32, 3, 18, 'Popular', 'Jazz'),
('Mud', 12, 15, 19, 'Popular', 'Country'),
('The', 5, 20, 11, 'Popular', 'New Age'),
('Embrace', 24, 11, 12, 'Popular', 'New Age'),
('Magic', 42, 17, 13, 'Classical', 'General'),
('Lake', 25, 44, 24, 'Classical', 'Dance'),
('LaLala', 20, 10, 5, 'Classical', 'Opera'),
('Soul', 15, 30, 16, 'Popular', 'Blues'),
('Stages', 42, 0, 7, 'Popular', 'Blues'),
('Six', 16, 8, 6, 'Classical', 'General');
使用Limit、order的例子：
select * from Item;
SELECT ID, Name, InStock
FROM Item
WHERE Department='Classical'
ORDER BY ID DESC
LIMIT 3,4;
您可能感兴趣的文章：
mysql中limit、order by和group by的用法实例解析
mysql limit,offset 区别实例分析
php实现数据库链式查询的简单例子
实例详解mysql limit大数据量分页优化的方法
Mysql中limit的用法实例详解
学习MySQL数据分页查询(limit用法)
mysql分页limit优化方法
优化mysql的limit offset的例子
mysql分页查询 oracle分页查询
MySQL limit查询优化的实例详解