SQL/MySQL/Select Clause/Limits — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
|
(нет различий)
| |
Версия 16:46, 26 мая 2010
Содержание
Another ORDER BY and limit
<source lang="sql">
/* 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;
</source>
Limits and order
<source lang="sql">
/* 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;
</source>
Limit the query results: Get 3 Most Expensive Products
<source lang="sql">
/* 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;
</source>
ORDER and limit
<source lang="sql">
/* 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;
</source>
Retrieving the Top Five Students
<source lang="sql">
/* 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;
</source>
Simple LIMIT
<source lang="sql">
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;
</source>
Use LIMIT
<source lang="sql">
/* 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;
</source>
Use LIMIT in SELECT
<source lang="sql">
/* 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;
</source>
