mysql ・並び替え　条件の制限

SQL mysql

・並び替え（小さい順）　order by

select * from tossy order by score;

+----+--------+------------------+--------+-------+---------------------+
| id | name | email | team | score | created |
+----+--------+------------------+--------+-------+---------------------+
| 5 | tok | toko@gmail.jp | red | 1.8 | 2016-01-04 19:00:00 |
| 4 | hana | ha@gmail.com | yellow | 2.2 | 2012-01-03 10:00:00 |
| 3 | hanako | hanako@gmail.com | blue | 3.3 | 2016-01-02 16:00:00 |
| 1 | toko | toko@gmail.com | red | 8.8 | 2016-01-05 19:00:00 |
| 6 | han | ha@gmail.jp | yellow | 9.2 | 2012-01-09 10:00:00 |
| 2 | to | to@gmail.com | blue | 9.9 | 2016-01-01 15:00:00 |
+----+--------+------------------+--------+-------+---------------------+


・並び替え（大きい順） desc

select * from tossy order by score desc;

+----+--------+------------------+--------+-------+---------------------+
| id | name | email | team | score | created |
+----+--------+------------------+--------+-------+---------------------+
| 2 | to | to@gmail.com | blue | 9.9 | 2016-01-01 15:00:00 |
| 6 | han | ha@gmail.jp | yellow | 9.2 | 2012-01-09 10:00:00 |
| 1 | toko | toko@gmail.com | red | 8.8 | 2016-01-05 19:00:00 |
| 3 | hanako | hanako@gmail.com | blue | 3.3 | 2016-01-02 16:00:00 |
| 4 | hana | ha@gmail.com | yellow | 2.2 | 2012-01-03 10:00:00 |
| 5 | tok | toko@gmail.jp | red | 1.8 | 2016-01-04 19:00:00 |
+----+--------+------------------+--------+-------+---------------------+


・件数の制限　limit

select * from tossy score limit 5;

+----+--------+------------------+--------+-------+---------------------+
| id | name | email | team | score | created |
+----+--------+------------------+--------+-------+---------------------+
| 1 | toko | toko@gmail.com | red | 8.8 | 2016-01-05 19:00:00 |
| 2 | to | to@gmail.com | blue | 9.9 | 2016-01-01 15:00:00 |
| 3 | hanako | hanako@gmail.com | blue | 3.3 | 2016-01-02 16:00:00 |
| 4 | hana | ha@gmail.com | yellow | 2.2 | 2012-01-03 10:00:00 |
| 5 | tok | toko@gmail.jp | red | 1.8 | 2016-01-04 19:00:00 |
+----+--------+------------------+--------+-------+---------------------+


・開始位置　件数指定

select * from tossy limit 3, 2;

+----+------+---------------+--------+-------+---------------------+
| id | name | email | team | score | created |
+----+------+---------------+--------+-------+---------------------+
| 4 | hana | ha@gmail.com | yellow | 2.2 | 2012-01-03 10:00:00 |
| 5 | tok | toko@gmail.jp | red | 1.8 | 2016-01-04 19:00:00 |
+----+------+---------------+--------+-------+---------------------+


・組み合わせ　上位　３名のハイスコアのユーザー

select * from tossy order by score desc limit 3;

+----+------+----------------+--------+-------+---------------------+
| id | name | email | team | score | created |
+----+------+----------------+--------+-------+---------------------+
| 2 | to | to@gmail.com | blue | 9.9 | 2016-01-01 15:00:00 |
| 6 | han | ha@gmail.jp | yellow | 9.2 | 2012-01-09 10:00:00 |
| 1 | toko | toko@gmail.com | red | 8.8 | 2016-01-05 19:00:00 |
+----+------+----------------+--------+-------+---------------------+