1. 语法：（以SQL199语法为标准）
select 查询列表
from 表
【 join type join 表2
on 连接条件
where 筛选条件
group by 分组字段
having 分组后的筛选
order by 排序字段 】
limit offset ,size;
#offset 要显示条目的起始索引（起始索引从 0 开始）
注意⚠️：（1)limit语句放在查询语句的最后
(2)公式：要显示的页数page ，每页的条目数size limit（page -1）*size ，size；
√ 2. 练习
案例一：查询前5条员工信息
mysql> select * from employees limit 0 ,5;
+-------------+------------+-----------+----------+--------------+---------+----------+----------------+------------+---------------+---------------------+
| employee_id | first_name | last_name | email | phone_number | job_id | salary | commission_pct | manager_id | department_id | hiredate |
+-------------+------------+-----------+----------+--------------+---------+----------+----------------+------------+---------------+---------------------+
| 100 | Steven | K_ing | SKING | 515.123.4567 | AD_PRES | 24000.00 | NULL | NULL | 90 | 1992-04-03 00:00:00 |
| 101 | Neena | Kochhar | NKOCHHAR | 515.123.4568 | AD_VP | 17000.00 | NULL | 100 | 90 | 1992-04-03 00:00:00 |
| 102 | Lex | De Haan | LDEHAAN | 515.123.4569 | AD_VP | 17000.00 | NULL | 100 | 90 | 1992-04-03 00:00:00 |
| 103 | Alexander | Hunold | AHUNOLD | 590.423.4567 | IT_PROG | 9000.00 | NULL | 102 | 60 | 1992-04-03 00:00:00 |
| 104 | Bruce | Ernst | BERNST | 590.423.4568 | IT_PROG | 6000.00 | NULL | 103 | 60 | 1992-04-03 00:00:00 |
+-------------+------------+-----------+----------+--------------+---------+----------+----------------+------------+---------------+---------------------+
5 rows in set (0.00 sec)
#当是从索引‘0’ 开始，可以省略。
mysql> select * from employees limit 5;
+-------------+------------+-----------+----------+--------------+---------+----------+----------------+------------+---------------+---------------------+
| employee_id | first_name | last_name | email | phone_number | job_id | salary | commission_pct | manager_id | department_id | hiredate |
+-------------+------------+-----------+----------+--------------+---------+----------+----------------+------------+---------------+---------------------+
| 100 | Steven | K_ing | SKING | 515.123.4567 | AD_PRES | 24000.00 | NULL | NULL | 90 | 1992-04-03 00:00:00 |
| 101 | Neena | Kochhar | NKOCHHAR | 515.123.4568 | AD_VP | 17000.00 | NULL | 100 | 90 | 1992-04-03 00:00:00 |
| 102 | Lex | De Haan | LDEHAAN | 515.123.4569 | AD_VP | 17000.00 | NULL | 100 | 90 | 1992-04-03 00:00:00 |
| 103 | Alexander | Hunold | AHUNOLD | 590.423.4567 | IT_PROG | 9000.00 | NULL | 102 | 60 | 1992-04-03 00:00:00 |
| 104 | Bruce | Ernst | BERNST | 590.423.4568 | IT_PROG | 6000.00 | NULL | 103 | 60 | 1992-04-03 00:00:00 |
+-------------+------------+-----------+----------+--------------+---------+----------+----------------+------------+---------------+---------------------+
5 rows in set (0.00 sec)
案例二：查询第11 至25条员工信息。
mysql> #查询第11 至25条员工信息。
mysql> select * from employees limit 10,15;
+-------------+-------------+------------+----------+--------------+------------+----------+----------------+------------+---------------+---------------------+
| employee_id | first_name | last_name | email | phone_number | job_id | salary | commission_pct | manager_id | department_id | hiredate |
+-------------+-------------+------------+----------+--------------+------------+----------+----------------+------------+---------------+---------------------+
| 110 | John | Chen | JCHEN | 515.124.4269 | FI_ACCOUNT | 8200.00 | NULL | 108 | 100 | 2000-09-09 00:00:00 |
| 111 | Ismael | Sciarra | ISCIARRA | 515.124.4369 | FI_ACCOUNT | 7700.00 | NULL | 108 | 100 | 2000-09-09 00:00:00 |
| 112 | Jose Manuel | Urman | JMURMAN | 515.124.4469 | FI_ACCOUNT | 7800.00 | NULL | 108 | 100 | 2000-09-09 00:00:00 |
| 113 | Luis | Popp | LPOPP | 515.124.4567 | FI_ACCOUNT | 6900.00 | NULL | 108 | 100 | 2000-09-09 00:00:00 |
| 114 | Den | Raphaely | DRAPHEAL | 515.127.4561 | PU_MAN | 11000.00 | NULL | 100 | 30 | 2000-09-09 00:00:00 |
| 115 | Alexander | Khoo | AKHOO | 515.127.4562 | PU_CLERK | 3100.00 | NULL | 114 | 30 | 2000-09-09 00:00:00 |
| 116 | Shelli | Baida | SBAIDA | 515.127.4563 | PU_CLERK | 2900.00 | NULL | 114 | 30 | 2000-09-09 00:00:00 |
| 117 | Sigal | Tobias | STOBIAS | 515.127.4564 | PU_CLERK | 2800.00 | NULL | 114 | 30 | 2000-09-09 00:00:00 |
| 118 | Guy | Himuro | GHIMURO | 515.127.4565 | PU_CLERK | 2600.00 | NULL | 114 | 30 | 2000-09-09 00:00:00 |
| 119 | Karen | Colmenares | KCOLMENA | 515.127.4566 | PU_CLERK | 2500.00 | NULL | 114 | 30 | 2000-09-09 00:00:00 |
| 120 | Matthew | Weiss | MWEISS | 650.123.1234 | ST_MAN | 8000.00 | NULL | 100 | 50 | 2004-02-06 00:00:00 |
| 121 | Adam | Fripp | AFRIPP | 650.123.2234 | ST_MAN | 8200.00 | NULL | 100 | 50 | 2004-02-06 00:00:00 |
| 122 | Payam | Kaufling | PKAUFLIN | 650.123.3234 | ST_MAN | 7900.00 | NULL | 100 | 50 | 2004-02-06 00:00:00 |
| 123 | Shanta | Vollman | SVOLLMAN | 650.123.4234 | ST_MAN | 6500.00 | NULL | 100 | 50 | 2004-02-06 00:00:00 |
| 124 | Kevin | Mourgos | KMOURGOS | 650.123.5234 | ST_MAN | 5800.00 | NULL | 100 | 50 | 2004-02-06 00:00:00 |
+-------------+-------------+------------+----------+--------------+------------+----------+----------------+------------+---------------+---------------------+
15 rows in set (0.00 sec)
案例三：查询有奖金的员工信息，并且将工资较高的前10名显示出来
#查询有奖金的员工信息，并且将工资较高的前10名显示出来
mysql> select * from employees where commission_pct is not null order by salary desc limit 10;
+-------------+------------+-----------+----------+--------------------+--------+----------+----------------+------------+---------------+---------------------+
| employee_id | first_name | last_name | email | phone_number | job_id | salary | commission_pct | manager_id | department_id | hiredate |
+-------------+------------+-----------+----------+--------------------+--------+----------+----------------+------------+---------------+---------------------+
| 145 | John | Russell | JRUSSEL | 011.44.1344.429268 | SA_MAN | 14000.00 | 0.40 | 100 | 80 | 2002-12-23 00:00:00 |
| 146 | Karen | Partners | KPARTNER | 011.44.1344.467268 | SA_MAN | 13500.00 | 0.30 | 100 | 80 | 2002-12-23 00:00:00 |
| 147 | Alberto | Errazuriz | AERRAZUR | 011.44.1344.429278 | SA_MAN | 12000.00 | 0.30 | 100 | 80 | 2002-12-23 00:00:00 |
| 168 | Lisa | Ozer | LOZER | 011.44.1343.929268 | SA_REP | 11500.00 | 0.25 | 148 | 80 | 2014-03-05 00:00:00 |
| 148 | Gerald | Cambrault | GCAMBRAU | 011.44.1344.619268 | SA_MAN | 11000.00 | 0.30 | 100 | 80 | 2002-12-23 00:00:00 |
| 174 | Ellen | Abel | EABEL | 011.44.1644.429267 | SA_REP | 11000.00 | 0.30 | 149 | 80 | 2014-03-05 00:00:00 |
| 149 | Eleni | Zlotkey | EZLOTKEY | 011.44.1344.429018 | SA_MAN | 10500.00 | 0.20 | 100 | 80 | 2002-12-23 00:00:00 |
| 162 | Clara | Vishney | CVISHNEY | 011.44.1346.129268 | SA_REP | 10500.00 | 0.25 | 147 | 80 | 2014-03-05 00:00:00 |
| 150 | Peter | Tucker | PTUCKER | 011.44.1344.129268 | SA_REP | 10000.00 | 0.30 | 145 | 80 | 2014-03-05 00:00:00 |
| 156 | Janette | K_ing | JKING | 011.44.1345.429268 | SA_REP | 10000.00 | 0.35 | 146 | 80 | 2014-03-05 00:00:00 |
+-------------+------------+-----------+----------+--------------------+--------+----------+----------------+------------+---------------+---------------------+
10 rows in set (0.06 sec)
注：这是本人的学习笔记及练习，如果有错误的地方望指出一起讨论，谢谢！
网友评论