Mysql学习mysql查询中Limit、order的简单示例

/*
mysql> Drop table Item;
Query OK, 0 rows affected (0.01 sec)MYSQL利用

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利用

mysql> INSERT INTO Item (Name, InStock, OnOrder, Reserved, Department, Category)MYSQL利用

    ->           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: 0MYSQL利用

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利用

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)MYSQL利用

*/MYSQL利用

Drop table Item;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
);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');MYSQL利用

select * from Item;MYSQL利用

SELECT ID, Name, InStock
FROM Item
WHERE Department='Classical'
ORDER BY ID DESC
LIMIT 3,4;MYSQL利用