SQL/MySQL/Select Clause/Limits

Содержание

1 Another ORDER BY and limit
2 Limits and order
3 Limit the query results: Get 3 Most Expensive Products
4 ORDER and limit
5 Retrieving the Top Five Students
6 Simple LIMIT
7 Use LIMIT
8 Use LIMIT in SELECT

Another ORDER BY and limit

/*
mysql> select * from employee;
+----+-----------+----------+----------------------------+------+---------------+--------+-------+---------------------+
| id | firstname | lastname | title                      | age  | yearofservice| salary | perks | email               |
+----+-----------+----------+----------------------------+------+---------------+--------+-------+---------------------+
|  1 | John      | Chen     | Senior Programmer          |   31 |             3| 120000 | 25000 | j@hotmail.ru       |
|  2 | Jan       | Pillai   | Senior Programmer          |   32 |             4| 110000 | 20000 | g@yahoo.ru         |
|  3 | Ane       | Pandit   | Web Designer               |   24 |             3|  90000 | 15000 | a@gmail.ru         |
|  4 | Mary      | Anchor   | Web Designer               |   27 |             2|  85000 | 15000 | m@mail.ru          |
|  5 | Fred      | King     | Programmer                 |   32 |             3|  75000 | 15000 | f@net.ru           |
|  6 | John      | Mac      | Programmer                 |   32 |             4|  80000 | 16000 | j@hotmail.ru       |
|  7 | Arthur    | Sam      | Programmer                 |   28 |             2|  75000 | 14000 | e@yahoo.ru         |
|  8 | Alok      | Nanda    | Programmer                 |   32 |             3|  70000 | 10000 | a@yahoo.ru         |
|  9 | Susan     | Ra       | Multimedia Programmer      |   32 |             4|  90000 | 15000 | h@gmail.ru         |
| 10 | Paul      | Simon    | Multimedia Programmer      |   23 |             1|  85000 | 12000 | ps@gmail.ru        |
| 11 | Edward    | Parhar   | Multimedia Programmer      |   30 |             2|  75000 | 15000 | a@hotmail.ru       |
| 12 | Kim       | Hunter   | Senior Web Designer        |   32 |             4| 110000 | 20000 | kim@coolmail.ru    |
| 13 | Roger     | Lewis    | System Administrator       |   32 |             3| 100000 | 13000 | roger@mail.ru      |
| 14 | Danny     | Gibson   | System Administrator       |   31 |             2|  90000 | 12000 | danny@hotmail.ru   |
| 15 | Mike      | Harper   | Senior Marketing Executive |   36 |             1| 120000 | 28000 | m@gmail.ru         |
| 16 | Mary      | Sunday   | Marketing Executive        |   31 |             5|  90000 | 25000 | monica@bigmail.ru  |
| 17 | Jack      | Sim      | Marketing Executive        |   27 |             1|  70000 | 18000 | hal@gmail.ru       |
| 18 | Joe       | Irvine   | Marketing Executive        |   27 |             1|  72000 | 18000 | joseph@hotmail.ru  |
| 19 | Henry     | Ali      | Customer Service Manager   |   32 |             3|  70000 |  9000 | shahida@hotmail.ru |
| 20 | Peter     | Champion | Finance Manager            |   32 |             2| 120000 | 25000 | peter@yahoo.ru     |
+----+-----------+----------+----------------------------+------+---------------+--------+-------+---------------------+
20 rows in set (0.00 sec)
mysql> SELECT firstname, lastName from
    -> employee LIMIT 5;
+-----------+----------+
| firstname | lastName |
+-----------+----------+
| John      | Chen     |
| Jan       | Pillai   |
| Ane       | Pandit   |
| Mary      | Anchor   |
| Fred      | King     |
+-----------+----------+
5 rows in set (0.00 sec)

*/
Drop table employee;
CREATE TABLE employee (
    id int unsigned not null auto_increment primary key,
    firstname varchar(20),
    lastname varchar(20),
    title varchar(30),
    age int,
    yearofservice int,
    salary int,
    perks int,
    email varchar(60)
); 

INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("John", "Chen", "Senior Programmer", 31, 3, 120000, 25000, "j@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Jan", "Pillai", "Senior Programmer", 32, 4, 110000, 20000, "g@yahoo.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Ane", "Pandit", "Web Designer", 24, 3, 90000, 15000, "a@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mary", "Anchor", "Web Designer", 27, 2, 85000, 15000, "m@mail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Fred", "King", "Programmer", 32, 3, 75000, 15000, "f@net.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("John", "Mac", "Programmer", 32, 4, 80000, 16000, "j@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Arthur", "Sam", "Programmer", 28, 2, 75000, 14000, "e@yahoo.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Alok", "Nanda", "Programmer", 32, 3, 70000, 10000, "a@yahoo.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Susan", "Ra", "Multimedia Programmer", 32, 4, 90000, 15000, "h@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Paul", "Simon", "Multimedia Programmer", 23, 1, 85000, 12000, "ps@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Edward", "Parhar", "Multimedia Programmer", 30, 2, 75000, 15000, "a@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Kim", "Hunter", "Senior Web Designer", 32, 4, 110000, 20000, "kim@coolmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Roger", "Lewis", "System Administrator", 32, 3, 100000, 13000, "roger@mail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Danny", "Gibson", "System Administrator", 31, 2, 90000, 12000, "danny@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mike", "Harper", "Senior Marketing Executive", 36, 1, 120000, 28000, "m@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mary", "Sunday", "Marketing Executive", 31, 5, 90000, 25000, "monica@bigmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Jack", "Sim", "Marketing Executive", 27, 1, 70000, 18000, "hal@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Joe", "Irvine", "Marketing Executive", 27, 1, 72000, 18000, "joseph@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Henry", "Ali", "Customer Service Manager", 32, 3, 70000, 9000, "shahida@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Peter", "Champion", "Finance Manager", 32, 2, 120000, 25000, "peter@yahoo.ru");
select * from employee;
SELECT firstname, lastName, age from
employee ORDER BY age DESC
LIMIT 4;

Limits and 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");
select * from Item;
SELECT ID, Name, InStock
FROM Item
WHERE Department="Classical"
ORDER BY ID DESC
LIMIT 3,4;

Limit the query results: Get 3 Most Expensive Products

/*
mysql> select * from Product;
+-----------+------------+----------------------+-------+
| ProductID | Name       | Description          | Price |
+-----------+------------+----------------------+-------+
|         1 | Java       | Java Demo Code       |     6 |
|         2 | Swing      | Swing Demo Code      |     7 |
|         3 | C#         | C# Demo Code         |     4 |
|         4 | JavaScript | JavaScript Demo Code |     3 |
|         5 | SQL        | SQL Demo Code        |     8 |
|         6 | Struts     | Struts Demo Code     |     9 |
+-----------+------------+----------------------+-------+
6 rows in set (0.00 sec)
mysql> /* Get 3 Most Expensive Products*/
mysql> SELECT Name, Price
    -> FROM Product
    -> ORDER BY Price DESC, Name ASC
    -> LIMIT 0,3;
+--------+-------+
| Name   | Price |
+--------+-------+
| Struts |     9 |
| SQL    |     8 |
| Swing  |     7 |
+--------+-------+
3 rows in set (0.00 sec)

*/
/* Create Database */  
Drop TABLE Product;

CREATE TABLE Product (
    ProductID INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
    Name VARCHAR(50) NOT NULL,
    Description VARCHAR(255) NOT NULL,
    Price DECIMAL NULL 
) Type=InnoDB;
  
  
/* Insert Data */
INSERT INTO Product (Name, Description, Price) VALUES ("Java", 
                                                       "Java Demo Code",
                                                       5.99 );
INSERT INTO Product (Name, Description, Price) VALUES ("Swing", 
                                                       "Swing Demo Code",
                                                       6.99 );
INSERT INTO Product (Name, Description, Price) VALUES ("C#", 
                                                       "C# Demo Code",
                                                       3.99 );
INSERT INTO Product (Name, Description, Price) VALUES ("JavaScript", 
                                                       "JavaScript Demo Code",
                                                       2.99 );
INSERT INTO Product (Name, Description, Price) VALUES ("SQL", 
                                                       "SQL Demo Code",
                                                       7.99 );
INSERT INTO Product (Name, Description, Price) VALUES ("Struts", 
                                                       "Struts Demo Code",
                                                       8.99 );
select * from Product;
/* Get 3 Most Expensive Products*/
SELECT Name, Price 
FROM Product 
ORDER BY Price DESC, Name ASC
LIMIT 0,3;

ORDER and limit

/*
mysql> Drop table Item;
Query OK, 0 rows affected (0.00 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:49 |
|  2 | Most      |      10 |       5 |        2 | Classical  | Opera    | 2005-10-09 09:19:49 |
|  3 | Jazz      |      17 |       4 |        3 | Popular    | Jazz     | 2005-10-09 09:19:49 |
|  4 | Class     |       9 |       4 |        4 | Classical  | Dance    | 2005-10-09 09:19:49 |
|  5 | Violin    |      24 |       2 |        5 | Classical  | General  | 2005-10-09 09:19:49 |
|  6 | Cha Cha   |      16 |       6 |        6 | Classical  | Vocal    | 2005-10-09 09:19:49 |
|  7 | Blues     |       2 |      25 |        7 | Popular    | Blues    | 2005-10-09 09:19:49 |
|  8 | Pure      |      32 |       3 |       18 | Popular    | Jazz     | 2005-10-09 09:19:49 |
|  9 | Mud       |      12 |      15 |       19 | Popular    | Country  | 2005-10-09 09:19:49 |
| 10 | The       |       5 |      20 |       11 | Popular    | New Age  | 2005-10-09 09:19:49 |
| 11 | Embrace   |      24 |      11 |       12 | Popular    | New Age  | 2005-10-09 09:19:49 |
| 12 | Magic     |      42 |      17 |       13 | Classical  | General  | 2005-10-09 09:19:49 |
| 13 | Lake      |      25 |      44 |       24 | Classical  | Dance    | 2005-10-09 09:19:49 |
| 14 | LaLala    |      20 |      10 |        5 | Classical  | Opera    | 2005-10-09 09:19:49 |
| 15 | Soul      |      15 |      30 |       16 | Popular    | Blues    | 2005-10-09 09:19:49 |
| 16 | Stages    |      42 |       0 |        7 | Popular    | Blues    | 2005-10-09 09:19:49 |
| 17 | Six       |      16 |       8 |        6 | Classical  | General  | 2005-10-09 09:19:49 |
+----+-----------+---------+---------+----------+------------+----------+---------------------+
17 rows in set (0.00 sec)
mysql> SELECT ID, Name, InStock
    -> FROM Item
    -> WHERE Department="Classical"
    -> ORDER BY ID DESC
    -> LIMIT 4;
+----+--------+---------+
| ID | Name   | InStock |
+----+--------+---------+
| 17 | Six    |      16 |
| 14 | LaLala |      20 |
| 13 | Lake   |      25 |
| 12 | Magic  |      42 |
+----+--------+---------+
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");
select * from Item;
SELECT ID, Name, InStock
FROM Item
WHERE Department="Classical"
ORDER BY ID DESC
LIMIT 4;

Retrieving the Top Five Students

/*
mysql> Select * from StudentExam;
+-----------+------+------------+
| StudentID | Mark | Comments   |
+-----------+------+------------+
|        10 |   46 | Java       |
|        10 |   65 | C#         |
|        10 |   79 | JavaScript |
|        11 |   66 | Java       |
|        11 |   85 | C#         |
|        11 |   99 | JavaScript |
+-----------+------+------------+
6 rows in set (0.01 sec)
mysql> /* Real command */
mysql> SELECT StudentID, AVG(Mark) AS AverageMark
    -> FROM StudentExam
    -> GROUP BY StudentID
    -> ORDER BY AverageMark DESC
    -> LIMIT 0, 5;
+-----------+-------------+
| StudentID | AverageMark |
+-----------+-------------+
|        11 |     83.3333 |
|        10 |     63.3333 |
+-----------+-------------+
2 rows in set (0.02 sec)

*/
/* Create table */
Drop TABLE StudentExam;
CREATE TABLE StudentExam (
   StudentID  INT NOT NULL,
   Mark       INT,
   Comments   VARCHAR(255)
   
)TYPE = InnoDB;
/* Insert data */
INSERT INTO StudentExam (StudentID,Mark,Comments) VALUES (10,46,"Java");
INSERT INTO StudentExam (StudentID,Mark,Comments) VALUES (10,65,"C#");
INSERT INTO StudentExam (StudentID,Mark,Comments) VALUES (10,79,"JavaScript");
INSERT INTO StudentExam (StudentID,Mark,Comments) VALUES (11,66,"Java");
INSERT INTO StudentExam (StudentID,Mark,Comments) VALUES (11,85,"C#");
INSERT INTO StudentExam (StudentID,Mark,Comments) VALUES (11,99,"JavaScript");
Select * from StudentExam;
/* Real command */
 
SELECT StudentID, AVG(Mark) AS AverageMark
FROM StudentExam
GROUP BY StudentID
ORDER BY AverageMark DESC
LIMIT 0, 5;

Simple LIMIT

Drop table employee;
CREATE TABLE employee (
    id int unsigned not null auto_increment primary key,
    firstname varchar(20),
    lastname varchar(20),
    title varchar(30),
    age int,
    yearofservice int,
    salary int,
    perks int,
    email varchar(60)
); 

INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("John", "Chen", "Senior Programmer", 31, 3, 120000, 25000, "j@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Jan", "Pillai", "Senior Programmer", 32, 4, 110000, 20000, "g@yahoo.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Ane", "Pandit", "Web Designer", 24, 3, 90000, 15000, "a@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mary", "Anchor", "Web Designer", 27, 2, 85000, 15000, "m@mail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Fred", "King", "Programmer", 32, 3, 75000, 15000, "f@net.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("John", "Mac", "Programmer", 32, 4, 80000, 16000, "j@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Arthur", "Sam", "Programmer", 28, 2, 75000, 14000, "e@yahoo.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Alok", "Nanda", "Programmer", 32, 3, 70000, 10000, "a@yahoo.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Susan", "Ra", "Multimedia Programmer", 32, 4, 90000, 15000, "h@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Paul", "Simon", "Multimedia Programmer", 23, 1, 85000, 12000, "ps@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Edward", "Parhar", "Multimedia Programmer", 30, 2, 75000, 15000, "a@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Kim", "Hunter", "Senior Web Designer", 32, 4, 110000, 20000, "kim@coolmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Roger", "Lewis", "System Administrator", 32, 3, 100000, 13000, "roger@mail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Danny", "Gibson", "System Administrator", 31, 2, 90000, 12000, "danny@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mike", "Harper", "Senior Marketing Executive", 36, 1, 120000, 28000, "m@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mary", "Sunday", "Marketing Executive", 31, 5, 90000, 25000, "monica@bigmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Jack", "Sim", "Marketing Executive", 27, 1, 70000, 18000, "hal@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Joe", "Irvine", "Marketing Executive", 27, 1, 72000, 18000, "joseph@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Henry", "Ali", "Customer Service Manager", 32, 3, 70000, 9000, "shahida@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Peter", "Champion", "Finance Manager", 32, 2, 120000, 25000, "peter@yahoo.ru");
select * from employee;
SELECT firstname, lastName from
employee LIMIT 5;

Use LIMIT

/*
mysql> select * from employee;
+----+-----------+----------+----------------------------+------+---------------+--------+-------+---------------------+
| id | firstname | lastname | title                      | age  | yearofservice| salary | perks | email               |
+----+-----------+----------+----------------------------+------+---------------+--------+-------+---------------------+
|  1 | John      | Chen     | Senior Programmer          |   31 |             3| 120000 | 25000 | j@hotmail.ru       |
|  2 | Jan       | Pillai   | Senior Programmer          |   32 |             4| 110000 | 20000 | g@yahoo.ru         |
|  3 | Ane       | Pandit   | Web Designer               |   24 |             3|  90000 | 15000 | a@gmail.ru         |
|  4 | Mary      | Anchor   | Web Designer               |   27 |             2|  85000 | 15000 | m@mail.ru          |
|  5 | Fred      | King     | Programmer                 |   32 |             3|  75000 | 15000 | f@net.ru           |
|  6 | John      | Mac      | Programmer                 |   32 |             4|  80000 | 16000 | j@hotmail.ru       |
|  7 | Arthur    | Sam      | Programmer                 |   28 |             2|  75000 | 14000 | e@yahoo.ru         |
|  8 | Alok      | Nanda    | Programmer                 |   32 |             3|  70000 | 10000 | a@yahoo.ru         |
|  9 | Susan     | Ra       | Multimedia Programmer      |   32 |             4|  90000 | 15000 | h@gmail.ru         |
| 10 | Paul      | Simon    | Multimedia Programmer      |   23 |             1|  85000 | 12000 | ps@gmail.ru        |
| 11 | Edward    | Parhar   | Multimedia Programmer      |   30 |             2|  75000 | 15000 | a@hotmail.ru       |
| 12 | Kim       | Hunter   | Senior Web Designer        |   32 |             4| 110000 | 20000 | kim@coolmail.ru    |
| 13 | Roger     | Lewis    | System Administrator       |   32 |             3| 100000 | 13000 | roger@mail.ru      |
| 14 | Danny     | Gibson   | System Administrator       |   31 |             2|  90000 | 12000 | danny@hotmail.ru   |
| 15 | Mike      | Harper   | Senior Marketing Executive |   36 |             1| 120000 | 28000 | m@gmail.ru         |
| 16 | Mary      | Sunday   | Marketing Executive        |   31 |             5|  90000 | 25000 | monica@bigmail.ru  |
| 17 | Jack      | Sim      | Marketing Executive        |   27 |             1|  70000 | 18000 | hal@gmail.ru       |
| 18 | Joe       | Irvine   | Marketing Executive        |   27 |             1|  72000 | 18000 | joseph@hotmail.ru  |
| 19 | Henry     | Ali      | Customer Service Manager   |   32 |             3|  70000 |  9000 | shahida@hotmail.ru |
| 20 | Peter     | Champion | Finance Manager            |   32 |             2| 120000 | 25000 | peter@yahoo.ru     |
+----+-----------+----------+----------------------------+------+---------------+--------+-------+---------------------+
20 rows in set (0.00 sec)
mysql> SELECT firstname, lastName from
    -> employee LIMIT 6,3;
+-----------+----------+
| firstname | lastName |
+-----------+----------+
| Arthur    | Sam      |
| Alok      | Nanda    |
| Susan     | Ra       |
+-----------+----------+
3 rows in set (0.00 sec)
*/
Drop table employee;
CREATE TABLE employee (
    id int unsigned not null auto_increment primary key,
    firstname varchar(20),
    lastname varchar(20),
    title varchar(30),
    age int,
    yearofservice int,
    salary int,
    perks int,
    email varchar(60)
); 

INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("John", "Chen", "Senior Programmer", 31, 3, 120000, 25000, "j@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Jan", "Pillai", "Senior Programmer", 32, 4, 110000, 20000, "g@yahoo.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Ane", "Pandit", "Web Designer", 24, 3, 90000, 15000, "a@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mary", "Anchor", "Web Designer", 27, 2, 85000, 15000, "m@mail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Fred", "King", "Programmer", 32, 3, 75000, 15000, "f@net.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("John", "Mac", "Programmer", 32, 4, 80000, 16000, "j@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Arthur", "Sam", "Programmer", 28, 2, 75000, 14000, "e@yahoo.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Alok", "Nanda", "Programmer", 32, 3, 70000, 10000, "a@yahoo.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Susan", "Ra", "Multimedia Programmer", 32, 4, 90000, 15000, "h@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Paul", "Simon", "Multimedia Programmer", 23, 1, 85000, 12000, "ps@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Edward", "Parhar", "Multimedia Programmer", 30, 2, 75000, 15000, "a@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Kim", "Hunter", "Senior Web Designer", 32, 4, 110000, 20000, "kim@coolmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Roger", "Lewis", "System Administrator", 32, 3, 100000, 13000, "roger@mail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Danny", "Gibson", "System Administrator", 31, 2, 90000, 12000, "danny@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mike", "Harper", "Senior Marketing Executive", 36, 1, 120000, 28000, "m@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mary", "Sunday", "Marketing Executive", 31, 5, 90000, 25000, "monica@bigmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Jack", "Sim", "Marketing Executive", 27, 1, 70000, 18000, "hal@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Joe", "Irvine", "Marketing Executive", 27, 1, 72000, 18000, "joseph@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Henry", "Ali", "Customer Service Manager", 32, 3, 70000, 9000, "shahida@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Peter", "Champion", "Finance Manager", 32, 2, 120000, 25000, "peter@yahoo.ru");

select * from employee;
SELECT firstname, lastName from
employee LIMIT 6,3;

Use LIMIT in SELECT

/*
mysql> select * from Product;
+-----------+------------+----------------------+-------+
| ProductID | Name       | Description          | Price |
+-----------+------------+----------------------+-------+
|         1 | Java       | Java Demo Code       |     6 |
|         2 | Swing      | Swing Demo Code      |     7 |
|         3 | C#         | C# Demo Code         |     4 |
|         4 | JavaScript | JavaScript Demo Code |     3 |
|         5 | SQL        | SQL Demo Code        |     8 |
|         6 | Struts     | Struts Demo Code     |     9 |
+-----------+------------+----------------------+-------+
6 rows in set (0.00 sec)
mysql> /* Browse Products */
mysql> SELECT ProductID, Name
    -> FROM Product
    -> ORDER BY ProductID
    -> LIMIT 3,4;
+-----------+------------+
| ProductID | Name       |
+-----------+------------+
|         4 | JavaScript |
|         5 | SQL        |
|         6 | Struts     |
+-----------+------------+
3 rows in set (0.00 sec)

*/
/* Create Database */  
Drop TABLE Product;

CREATE TABLE Product (
    ProductID INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
    Name VARCHAR(50) NOT NULL,
    Description VARCHAR(255) NOT NULL,
    Price DECIMAL NULL 
) Type=InnoDB;
  
  
/* Insert Data */
INSERT INTO Product (Name, Description, Price) VALUES ("Java", 
                                                       "Java Demo Code",
                                                       5.99 );
INSERT INTO Product (Name, Description, Price) VALUES ("Swing", 
                                                       "Swing Demo Code",
                                                       6.99 );
INSERT INTO Product (Name, Description, Price) VALUES ("C#", 
                                                       "C# Demo Code",
                                                       3.99 );
INSERT INTO Product (Name, Description, Price) VALUES ("JavaScript", 
                                                       "JavaScript Demo Code",
                                                       2.99 );
INSERT INTO Product (Name, Description, Price) VALUES ("SQL", 
                                                       "SQL Demo Code",
                                                       7.99 );
INSERT INTO Product (Name, Description, Price) VALUES ("Struts", 
                                                       "Struts Demo Code",
                                                       8.99 );
select * from Product;
/* Browse Products */
SELECT ProductID, Name
FROM Product 
ORDER BY ProductID
LIMIT 3,4;

SQL/MySQL/Select Clause/Limits

Содержание

Another ORDER BY and limit

Limits and order

Limit the query results: Get 3 Most Expensive Products

ORDER and limit

Retrieving the Top Five Students

Simple LIMIT

Use LIMIT

Use LIMIT in SELECT

Навигация

Персональные инструменты

Пространства имён

Варианты

Просмотры

Ещё

Поиск

Разделы

Навигация

Инструменты