Ⅰ where语句 -- 条件语句
where -- 条件判断查询id值大于3,且小于5的记录 -- 字段>3 and 字段< 5;mysql> select * from wot where id > 3 and id < 5;+----+------+------+-----+------------+---------+--------------+---------+--------+-----------+| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |+----+------+------+-----+------------+---------+--------------+---------+--------+-----------+| 4 | T92 | male | 73 | -07-01 | teacher | NULL | 3500.00 | 401 | 1 |+----+------+------+-----+------------+---------+--------------+---------+--------+-----------+1 row in set (0.00 sec)
查询id值大于等于3,且小于等于5的记录 -- 字段>=3 and 字段<=5;
mysql> select * from wot where id>=3 and id <=5;+----+-------------+------+-----+------------+---------+--------------+---------+--------+-----------+| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |+----+-------------+------+-----+------------+---------+--------------+---------+--------+-----------+| 3 | 261工程| male | 81 | -03-05 | teacher | NULL | 8300.00 | 401 | 1 || 4 | T92 | male | 73 | -07-01 | teacher | NULL | 3500.00 | 401 | 1 || 5 | 征服者GC | male | 28 | -11-01 | teacher | NULL | 2100.00 | 401 | 1 |+----+-------------+------+-----+------------+---------+--------------+---------+--------+-----------+3 rows in set (0.00 sec)
查询id值大于等于3,且小于等于5的记录 -- between 3 and5;
mysql> select * from wot where id between 3 and 5;+----+-------------+------+-----+------------+---------+--------------+---------+--------+-----------+| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |+----+-------------+------+-----+------------+---------+--------------+---------+--------+-----------+| 3 | 261工程| male | 81 | -03-05 | teacher | NULL | 8300.00 | 401 | 1 || 4 | T92 | male | 73 | -07-01 | teacher | NULL | 3500.00 | 401 | 1 || 5 | 征服者GC | male | 28 | -11-01 | teacher | NULL | 2100.00 | 401 | 1 |+----+-------------+------+-----+------------+---------+--------------+---------+--------+-----------+3 rows in set (0.00 sec)
查询非id值大于等于3,且小于等于5的记录 -- not between 3 and 5;
mysql> select * from wot where id not between 3 and 5;+----+---------------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+| id | name| sex | age | hire_date | post| post_comment | salary| office | depart_id |+----+---------------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+| 1 | zhu | male | 18 | -03-01 | 坦克世界驻蹲坑懦夫卡办事处 | NULL | 7300.33 | 401 | 1 || 2 | shuai| male | 78 | -03-02 | teacher | NULL | 1000000.31 | 401 | 1 || 6 | VK7201 | female | 18 | -02-11 | teacher | NULL | 9000.00 | 401 | 1 || 7 | FV215b | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 || 8 | 超级征服者| male | 48 | -11-11 | teacher | NULL | 10000.00 | 401 | 1 || 9 | FV4005 | female | 48 | -03-11 | sale| NULL | 3000.13 | 402 | 2 || 10 | E-100突击车 | female | 38 | -11-01 | sale| NULL | 2000.35 | 402 | 2 || 11 | 蟋蟀15 | female | 18 | -03-12 | sale| NULL | 1000.37 | 402 | 2 || 12 | 268Ⅳ工程 | female | 18 | -05-13 | sale| NULL | 3000.29 | 402 | 2 || 13 | FV215b(183) | female | 28 | -01-27 | sale| NULL | 4000.33 | 402 | 2 || 14 | 棘手问题 | male | 28 | -03-11 | operation | NULL | 10000.13 | 403 | 3 || 15 | 晴空| male | 18 | 1997-03-12 | operation | NULL | 20000.00 | 403 | 3 || 16 | 安哈尔特-莱昂 | female | 18 | -03-11 | operation | NULL | 19000.00 | 403 | 3 || 17 | 加拉哈德 | male | 18 | -04-11 | operation | NULL | 18000.00 | 403 | 3 || 18 | 爪哇犀牛 | female | 18 | -05-12 | operation | NULL | 17000.00 | 403 | 3 |+----+---------------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+15 rows in set (0.00 sec)
精准查询id值等于3,5,7的记录 -- 字段=3 or 字段=5 or 字段=7;
mysql> select * from wot where id = 3 or id = 5 or id = 7;+----+-------------+------+-----+------------+---------+--------------+----------+--------+-----------+| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |+----+-------------+------+-----+------------+---------+--------------+----------+--------+-----------+| 3 | 261工程| male | 81 | -03-05 | teacher | NULL | 8300.00 | 401 | 1 || 5 | 征服者GC | male | 28 | -11-01 | teacher | NULL | 2100.00 | 401 | 1 || 7 | FV215b| male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 |+----+-------------+------+-----+------------+---------+--------------+----------+--------+-----------+3 rows in set (0.00 sec)
精准匹配多个 -- in(6,8,13); -- not in(6,8,13);
mysql> select * from wot where id in(6,8,13);+----+-----------------+--------+-----+------------+---------+--------------+----------+--------+-----------+| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |+----+-----------------+--------+-----+------------+---------+--------------+----------+--------+-----------+| 6 | VK7201| female | 18 | -02-11 | teacher | NULL | 9000.00 | 401 | 1 || 8 | 超级征服者| male | 48 | -11-11 | teacher | NULL | 10000.00 | 401 | 1 || 13 | FV215b(183)| female | 28 | -01-27 | sale | NULL | 4000.33 | 402 | 2 |+----+-----------------+--------+-----+------------+---------+--------------+----------+--------+-----------+3 rows in set (0.00 sec)mysql> select * from-> wot where id not in(6,8,13);+----+---------------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+| id | name| sex | age | hire_date | post| post_comment | salary| office | depart_id |+----+---------------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+| 1 | zhu | male | 18 | -03-01 | 坦克世界驻蹲坑懦夫卡办事处 | NULL | 7300.33 | 401 | 1 || 2 | shuai| male | 78 | -03-02 | teacher | NULL | 1000000.31 | 401 | 1 || 3 | 261工程 | male | 81 | -03-05 | teacher | NULL | 8300.00 | 401 | 1 || 4 | T92 | male | 73 | -07-01 | teacher | NULL | 3500.00 | 401 | 1 || 5 | 征服者GC | male | 28 | -11-01 | teacher | NULL | 2100.00 | 401 | 1 || 7 | FV215b | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 || 9 | FV4005 | female | 48 | -03-11 | sale| NULL | 3000.13 | 402 | 2 || 10 | E-100突击车 | female | 38 | -11-01 | sale| NULL | 2000.35 | 402 | 2 || 11 | 蟋蟀15 | female | 18 | -03-12 | sale| NULL | 1000.37 | 402 | 2 || 12 | 268Ⅳ工程 | female | 18 | -05-13 | sale| NULL | 3000.29 | 402 | 2 || 14 | 棘手问题 | male | 28 | -03-11 | operation | NULL | 10000.13 | 403 | 3 || 15 | 晴空| male | 18 | 1997-03-12 | operation | NULL | 20000.00 | 403 | 3 || 16 | 安哈尔特-莱昂 | female | 18 | -03-11 | operation | NULL | 19000.00 | 403 | 3 || 17 | 加拉哈德 | male | 18 | -04-11 | operation | NULL | 18000.00 | 403 | 3 || 18 | 爪哇犀牛 | female | 18 | -05-12 | operation | NULL | 17000.00 | 403 | 3 |+----+---------------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+15 rows in set (0.00 sec)
模糊匹配 -- 字段 like '%车'以车结尾; -- 字段 like '_空'以空结尾,只向前一个字符
mysql> select * from wot where name like '%车';+----+----------------+--------+-----+------------+------+--------------+---------+--------+-----------+| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |+----+----------------+--------+-----+------------+------+--------------+---------+--------+-----------+| 10 | E-100突击车 | female | 38 | -11-01 | sale | NULL | 2000.35 | 402 | 2 |+----+----------------+--------+-----+------------+------+--------------+---------+--------+-----------+1 row in set (0.00 sec)mysql> select * from wot where name like '_空';+----+--------+------+-----+------------+-----------+--------------+----------+--------+-----------+| id | name | sex | age | hire_date | post| post_comment | salary | office | depart_id |+----+--------+------+-----+------------+-----------+--------------+----------+--------+-----------+| 15 | 晴空 | male | 18 | 1997-03-12 | operation | NULL | 20000.00 | 403 | 3 |+----+--------+------+-----+------------+-----------+--------------+----------+--------+-----------+1 row in set (0.00 sec)
正则匹配 -- 字段 regexp '车$',匹配以车结尾; -- 字段 regexp '^z',匹配以z开头
mysql> select * from wot where name regexp '车$';+----+----------------+--------+-----+------------+------+--------------+---------+--------+-----------+| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |+----+----------------+--------+-----+------------+------+--------------+---------+--------+-----------+| 10 | E-100突击车 | female | 38 | -11-01 | sale | NULL | 2000.35 | 402 | 2 |+----+----------------+--------+-----+------------+------+--------------+---------+--------+-----------+1 row in set (0.00 sec)mysql> select * from wot where name regexp '^z';+----+------+------+-----+------------+-----------------------------------------+--------------+---------+--------+-----------+| id | name | sex | age | hire_date | post| post_comment | salary | office | depart_id |+----+------+------+-----+------------+-----------------------------------------+--------------+---------+--------+-----------+| 1 | zhu | male | 18 | -03-01 | 坦克世界驻蹲坑懦夫卡办事处 | NULL | 7300.33 | 401 | 1 |+----+------+------+-----+------------+-----------------------------------------+--------------+---------+--------+-----------+1 row in set (0.00 sec)
判断字段是否为空 -- is null; -- 注意: '' 空字符串不是null
mysql> select * from wot where post_comment is null;+----+---------------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+| id | name| sex | age | hire_date | post| post_comment | salary| office | depart_id |+----+---------------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+| 1 | zhu | male | 18 | -03-01 | 坦克世界驻蹲坑懦夫卡办事处 | NULL | 7300.33 | 401 | 1 || 2 | shuai| male | 78 | -03-02 | teacher | NULL | 1000000.31 | 401 | 1 || 3 | 261工程 | male | 81 | -03-05 | teacher | NULL | 8300.00 | 401 | 1 || 4 | T92 | male | 73 | -07-01 | teacher | NULL | 3500.00 | 401 | 1 || 5 | 征服者GC | male | 28 | -11-01 | teacher | NULL | 2100.00 | 401 | 1 || 6 | VK7201 | female | 18 | -02-11 | teacher | NULL | 9000.00 | 401 | 1 || 7 | FV215b | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 || 8 | 超级征服者| male | 48 | -11-11 | teacher | NULL | 10000.00 | 401 | 1 || 9 | FV4005 | female | 48 | -03-11 | sale| NULL | 3000.13 | 402 | 2 || 10 | E-100突击车 | female | 38 | -11-01 | sale| NULL | 2000.35 | 402 | 2 || 11 | 蟋蟀15 | female | 18 | -03-12 | sale| NULL | 1000.37 | 402 | 2 || 12 | 268Ⅳ工程 | female | 18 | -05-13 | sale| NULL | 3000.29 | 402 | 2 || 13 | FV215b(183) | female | 28 | -01-27 | sale| NULL | 4000.33 | 402 | 2 || 14 | 棘手问题 | male | 28 | -03-11 | operation | NULL | 10000.13 | 403 | 3 || 15 | 晴空| male | 18 | 1997-03-12 | operation | NULL | 20000.00 | 403 | 3 || 16 | 安哈尔特-莱昂 | female | 18 | -03-11 | operation | NULL | 19000.00 | 403 | 3 || 17 | 加拉哈德 | male | 18 | -04-11 | operation | NULL | 18000.00 | 403 | 3 || 18 | 爪哇犀牛 | female | 18 | -05-12 | operation | NULL | 17000.00 | 403 | 3 |+----+---------------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+18 rows in set (0.00 sec)mysql> update wot set post_comment='' where id not in(1,2,4,5,6); -- 将id不为12456的post_comment字段的记录改成'' 空字符串Query OK, 13 rows affected (0.00 sec)Rows matched: 13 Changed: 13 Warnings: 0mysql> select * from wot where post_comment is null; -- 再次查询post_comment字段为空的只有这几个了+----+-------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+| id | name | sex | age | hire_date | post| post_comment | salary| office | depart_id |+----+-------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+| 1 | zhu | male | 18 | -03-01 | 坦克世界驻蹲坑懦夫卡办事处 | NULL | 7300.33 | 401 | 1 || 2 | shuai | male | 78 | -03-02 | teacher | NULL | 1000000.31 | 401 | 1 || 4 | T92 | male | 73 | -07-01 | teacher | NULL | 3500.00 | 401 | 1 || 5 | 征服者GC | male | 28 | -11-01 | teacher | NULL | 2100.00 | 401 | 1 || 6 | VK7201| female | 18 | -02-11 | teacher | NULL | 9000.00 | 401 | 1 |+----+-------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+5 rows in set (0.00 sec)
Ⅱ group by -- 分组
分完组之后只能看到分组后的字段以及聚合的结果max() -- 最大值min() -- 最小值avg() -- 平均值sum() -- 取合值count() -- 取个数group_concat() -- 打印出括号内字段记录以某个字段的记录进行分组,默认只显示每组的第一条记录mysql> select * from wot group by depart_id;+----+--------------+--------+-----+------------+-----------------------------------------+--------------+----------+--------+-----------+| id | name | sex | age | hire_date | post| post_comment | salary | office | depart_id |+----+--------------+--------+-----+------------+-----------------------------------------+--------------+----------+--------+-----------+| 1 | zhu| male | 18 | -03-01 | 坦克世界驻蹲坑懦夫卡办事处 | NULL | 7300.33 | 401 | 1 || 9 | FV4005 | female | 48 | -03-11 | sale| NULL | 3000.13 | 402 | 2 || 14 | 棘手问题| male | 28 | -03-11 | operation | NULL | 10000.13 | 403 | 3 |+----+--------------+--------+-----+------------+-----------------------------------------+--------------+----------+--------+-----------+3 rows in set (0.00 sec)
分组后不应该能查看到分组的记录,应该用聚合函数来进行处理 -- 聚合函数只能分组之后的功能来用设置分组模式 -- 不再显示分组数据,使用聚合函数进行分组数据处理
mysql> set global sql_mode='ONLY_FULL_GROUP_BY';Query OK, 0 rows affected (0.00 sec)mysql> select * from wot group by depart_id; -- 设置完成后不能查看分组的单条记录了ERROR 1055 (42000): 'db1.wot.id' isn't in GROUP BY
每个部门有多少个人
mysql> select post,count(id) from wot group by post;+-----------------------------------------+-----------+| post| count(id) |+-----------------------------------------+-----------+| operation | 5 || sale| 5 || teacher | 7 || 坦克世界驻蹲坑懦夫卡办事处 | 1 |+-----------------------------------------+-----------+4 rows in set (0.00 sec)
每个部门的最大薪资
mysql> select post,max(salary) from wot group by post;+-----------------------------------------+-------------+| post| max(salary) |+-----------------------------------------+-------------+| operation | 20000.00 || sale|4000.33 || teacher | 1000000.31 || 坦克世界驻蹲坑懦夫卡办事处 |7300.33 |+-----------------------------------------+-------------+4 rows in set (0.01 sec)
每个部门的最小薪资,薪资总和,平均薪资
mysql> select post,min(salary) from wot group by post; -- 最小薪资+-----------------------------------------+-------------+| post| min(salary) |+-----------------------------------------+-------------+| operation | 10000.13 || sale|1000.37 || teacher |2100.00 || 坦克世界驻蹲坑懦夫卡办事处 |7300.33 |+-----------------------------------------+-------------+4 rows in set (0.00 sec)mysql> select post,sum(salary) from wot group by post; -- 薪资总和+-----------------------------------------+-------------+| post| sum(salary) |+-----------------------------------------+-------------+| operation | 84000.13 || sale| 13001.47 || teacher | 1062900.31 || 坦克世界驻蹲坑懦夫卡办事处 |7300.33 |+-----------------------------------------+-------------+4 rows in set (0.00 sec)mysql> select post,avg(salary) from wot group by post; -- 平均薪资+-----------------------------------------+---------------+| post| avg(salary) |+-----------------------------------------+---------------+| operation | 16800.026000 || sale| 2600.294000 || teacher | 151842.901429 || 坦克世界驻蹲坑懦夫卡办事处 | 7300.330000 |+-----------------------------------------+---------------+4 rows in set (0.00 sec)
每个职位的男生的平均薪资
mysql> select post,avg(salary) from wot where sex='male' group by post;+-----------------------------------------+---------------+| post| avg(salary) |+-----------------------------------------+---------------+| operation | 16000.043333 || teacher | 175650.051667 || 坦克世界驻蹲坑懦夫卡办事处 | 7300.330000 |+-----------------------------------------+---------------+3 rows in set (0.00 sec)
每个部门所有人的名字及每个部门男生的名字
mysql> select post,group_concat(name) -- 每个部门所有人的名字-> from wot group by post;+-----------------------------------------+-------------------------------------------------------------------+| post| group_concat(name) |+-----------------------------------------+-------------------------------------------------------------------+| operation | 爪哇犀牛,加拉哈德,安哈尔特-莱昂,晴空,棘手问题 || sale| FV215b(183),268Ⅳ工程,蟋蟀15,E-100突击车,FV4005|| teacher | 超级征服者,FV215b,VK7201,征服者GC,T92,261工程,shuai|| 坦克世界驻蹲坑懦夫卡办事处 | zhu |+-----------------------------------------+-------------------------------------------------------------------+4 rows in set (0.00 sec)mysql> select post,group_concat(name) from wot where sex='male' group by post; -- 每个部门的男生的名字+-----------------------------------------+--------------------------------------------------------+| post| group_concat(name) |+-----------------------------------------+--------------------------------------------------------+| operation | 加拉哈德,晴空,棘手问题 || teacher | 超级征服者,FV215b,征服者GC,T92,261工程,shuai || 坦克世界驻蹲坑懦夫卡办事处 | zhu |+-----------------------------------------+--------------------------------------------------------+3 rows in set (0.00 sec)
Ⅲ having -- 过滤条件,在分组之后执行
找出男生平均薪资大于8000的职位,及每个职位的男生名字mysql> select post,group_concat(name) from wot where sex='male' group by post having avg(salary) > 8000;+-----------+--------------------------------------------------------+| post| group_concat(name) |+-----------+--------------------------------------------------------+| operation | 加拉哈德,晴空,棘手问题 || teacher | 超级征服者,FV215b,征服者GC,T92,261工程,shuai |+-----------+--------------------------------------------------------+2 rows in set (0.00 sec)
Ⅳ order by -- 排序(默认升序 asc) 在having之后执行
asc -- 升序排列desc -- 降序排列可以指定多个排序字段mysql> select * from wot order by id desc;+----+---------------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+| id | name| sex | age | hire_date | post| post_comment | salary| office | depart_id |+----+---------------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+| 18 | 爪哇犀牛 | female | 18 | -05-12 | operation | | 17000.00 | 403 | 3 || 17 | 加拉哈德 | male | 18 | -04-11 | operation | | 18000.00 | 403 | 3 || 16 | 安哈尔特-莱昂 | female | 18 | -03-11 | operation | | 19000.00 | 403 | 3 || 15 | 晴空| male | 18 | 1997-03-12 | operation | | 20000.00 | 403 | 3 || 14 | 棘手问题 | male | 28 | -03-11 | operation | | 10000.13 | 403 | 3 || 13 | FV215b(183) | female | 28 | -01-27 | sale| | 4000.33 | 402 | 2 || 12 | 268Ⅳ工程 | female | 18 | -05-13 | sale| | 3000.29 | 402 | 2 || 11 | 蟋蟀15 | female | 18 | -03-12 | sale| | 1000.37 | 402 | 2 || 10 | E-100突击车 | female | 38 | -11-01 | sale| | 2000.35 | 402 | 2 || 9 | FV4005 | female | 48 | -03-11 | sale| | 3000.13 | 402 | 2 || 8 | 超级征服者| male | 48 | -11-11 | teacher | | 10000.00 | 401 | 1 || 7 | FV215b | male | 18 | 1900-03-01 | teacher | | 30000.00 | 401 | 1 || 6 | VK7201 | female | 18 | -02-11 | teacher | NULL | 9000.00 | 401 | 1 || 5 | 征服者GC | male | 28 | -11-01 | teacher | NULL | 2100.00 | 401 | 1 || 4 | T92 | male | 73 | -07-01 | teacher | NULL | 3500.00 | 401 | 1 || 3 | 261工程 | male | 81 | -03-05 | teacher | | 8300.00 | 401 | 1 || 2 | shuai| male | 78 | -03-02 | teacher | NULL | 1000000.31 | 401 | 1 || 1 | zhu | male | 18 | -03-01 | 坦克世界驻蹲坑懦夫卡办事处 | NULL | 7300.33 | 401 | 1 |+----+---------------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+18 rows in set (0.00 sec)mysql> select * from wot order by age,id desc; -- 首先以age进行默认升序排列,然后以id进行降序排列+----+---------------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+| id | name| sex | age | hire_date | post| post_comment | salary| office | depart_id |+----+---------------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+| 18 | 爪哇犀牛 | female | 18 | -05-12 | operation | | 17000.00 | 403 | 3 || 17 | 加拉哈德 | male | 18 | -04-11 | operation | | 18000.00 | 403 | 3 || 16 | 安哈尔特-莱昂 | female | 18 | -03-11 | operation | | 19000.00 | 403 | 3 || 15 | 晴空| male | 18 | 1997-03-12 | operation | | 20000.00 | 403 | 3 || 12 | 268Ⅳ工程 | female | 18 | -05-13 | sale| | 3000.29 | 402 | 2 || 11 | 蟋蟀15 | female | 18 | -03-12 | sale| | 1000.37 | 402 | 2 || 7 | FV215b | male | 18 | 1900-03-01 | teacher | | 30000.00 | 401 | 1 || 6 | VK7201 | female | 18 | -02-11 | teacher | NULL | 9000.00 | 401 | 1 || 1 | zhu | male | 18 | -03-01 | 坦克世界驻蹲坑懦夫卡办事处 | NULL | 7300.33 | 401 | 1 || 14 | 棘手问题 | male | 28 | -03-11 | operation | | 10000.13 | 403 | 3 || 13 | FV215b(183) | female | 28 | -01-27 | sale| | 4000.33 | 402 | 2 || 5 | 征服者GC | male | 28 | -11-01 | teacher | NULL | 2100.00 | 401 | 1 || 10 | E-100突击车 | female | 38 | -11-01 | sale| | 2000.35 | 402 | 2 || 9 | FV4005 | female | 48 | -03-11 | sale| | 3000.13 | 402 | 2 || 8 | 超级征服者| male | 48 | -11-11 | teacher | | 10000.00 | 401 | 1 || 4 | T92 | male | 73 | -07-01 | teacher | NULL | 3500.00 | 401 | 1 || 2 | shuai| male | 78 | -03-02 | teacher | NULL | 1000000.31 | 401 | 1 || 3 | 261工程 | male | 81 | -03-05 | teacher | | 8300.00 | 401 | 1 |+----+---------------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+18 rows in set (0.00 sec)
Ⅴ limit 数字 -- 控制显示几个记录
limit 开始数字,显示几条记录; -- 如果开始不为0,那么实际显示的记录是开始数字加1的那条记录,如limit 5,5; -- 则表示从第6条开始显示,显示5条mysql> select * from wot limit 0,5; -- 从0开始显示5条数据+----+-------------+------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+| id | name | sex | age | hire_date | post| post_comment | salary| office | depart_id |+----+-------------+------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+| 1 | zhu | male | 18 | -03-01 | 坦克世界驻蹲坑懦夫卡办事处 | NULL | 7300.33 | 401 | 1 || 2 | shuai | male | 78 | -03-02 | teacher | NULL | 1000000.31 | 401 | 1 || 3 | 261工程| male | 81 | -03-05 | teacher | | 8300.00 | 401 | 1 || 4 | T92 | male | 73 | -07-01 | teacher | NULL | 3500.00 | 401 | 1 || 5 | 征服者GC | male | 28 | -11-01 | teacher | NULL | 2100.00 | 401 | 1 |+----+-------------+------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+5 rows in set (0.00 sec)mysql> select * from wot limit 5,5; -- 当开始序列不为0时,会从开始序列的下一条记录开始显示+----+-----------------+--------+-----+------------+---------+--------------+----------+--------+-----------+| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |+----+-----------------+--------+-----+------------+---------+--------------+----------+--------+-----------+| 6 | VK7201| female | 18 | -02-11 | teacher | NULL | 9000.00 | 401 | 1 || 7 | FV215b| male | 18 | 1900-03-01 | teacher | | 30000.00 | 401 | 1 || 8 | 超级征服者| male | 48 | -11-11 | teacher | | 10000.00 | 401 | 1 || 9 | FV4005| female | 48 | -03-11 | sale | | 3000.13 | 402 | 2 || 10 | E-100突击车| female | 38 | -11-01 | sale | | 2000.35 | 402 | 2 |+----+-----------------+--------+-----+------------+---------+--------------+----------+--------+-----------+5 rows in set (0.00 sec)
