单表查询
1.单表查询语法
SELECT DISTINCT */字段1,字段2... FROM 表名
WHERE 条件
GROUP BY field
HAVING 筛选
ORDER BY field
LIMIT 限制条数
2.关键字执行的优先级
FROM --> WHERE --> GROUP BY --> SELECT --> DISTINCT --> HAVING --> ORDER BY --> LIMIT
说明：
1- 找到需要查询数据的表
2- 根据where条件过滤需要的数据
3- 将取出的数据进行分组，如果没有分组，将整体座位一组
4- 执行select（去重）
5- 将分组的结果进行having过滤
6- 将结果排序，正序、倒序
7- 根据限制显示条数
3.数据准备
# 因为数据有中文字符，所以创建数据库的时候需要指定编码为utf8#创建表create table employee(id int not null unique auto_increment,emp_name varchar(20) not null,sex enum('male','female') not null default 'male', #大部分是男的age int(3) unsigned not null default 28,hire_date date not null,post varchar(50),post_comment varchar(100),salary double(15,2),office int, #一个部门一个屋子depart_id int);#插入记录#三个部门：教学，销售，运营insert into employee(emp_name,sex,age,hire_date,post,salary,office,depart_id) values('egon','male',18,'20170301','老男孩驻沙河办事处外交大使',7300.33,401,1), #以下是教学部('alex','male',78,'20150302','teacher',1000000.31,401,1),('wupeiqi','male',81,'20130305','teacher',8300,401,1),('yuanhao','male',73,'20140701','teacher',3500,401,1),('liwenzhou','male',28,'20121101','teacher',2100,401,1),('jingliyang','female',18,'20110211','teacher',9000,401,1),('jinxin','male',18,'19000301','teacher',30000,401,1),('成龙','male',48,'20101111','teacher',10000,401,1),('歪歪','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门('丫丫','female',38,'20101101','sale',2000.35,402,2),('丁丁','female',18,'20110312','sale',1000.37,402,2),('星星','female',18,'20160513','sale',3000.29,402,2),('格格','female',28,'20170127','sale',4000.33,402,2),('张野','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门('程咬金','male',18,'19970312','operation',20000,403,3),('程咬银','female',18,'20130311','operation',19000,403,3),('程咬铜','male',18,'20150411','operation',18000,403,3),('程咬铁','female',18,'20140512','operation',17000,403,3);自https://www.cnblogs.com/Eva-J/articles/9688313.html 4.简单查询
4.1 简单查询
语法
查询单个列
SELECT 字段 FROM 表名;
查询多个列，以逗号分隔
SELECT 字段1,字段2 FROM 表名;
查询所有列
SELECT * FROM 表名;
范例
mysql> select emp_name from employee;+------------+| emp_name |+------------+| egon || alex || wupeiqi || yuanhao || liwenzhou || jingliyang || jinxin || 成龙 || 歪歪 || 丫丫 || 丁丁 || 星星 || 格格 || 张野 || 程咬金 || 程咬银 || 程咬铜 || 程咬铁 |+------------+18 rows in set (0.00 sec)mysql> select emp_name, age from employee;+------------+-----+| emp_name | age |+------------+-----+| egon | 18 || alex | 78 || wupeiqi | 81 || yuanhao | 73 || liwenzhou | 28 || jingliyang | 18 || jinxin | 18 || 成龙 | 48 || 歪歪 | 48 || 丫丫 | 38 || 丁丁 | 18 || 星星 | 18 || 格格 | 28 || 张野 | 28 || 程咬金 | 18 || 程咬银 | 18 || 程咬铜 | 18 || 程咬铁 | 18 |+------------+-----+18 rows in set (0.00 sec)mysql> select * from employee;+----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+| id | emp_name | sex | age | hire_date | post | post_comment | salary | office | depart_id |+----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+| 1 | egon | male | 18 | 2017-03-01 | 老男孩驻沙河办事处外交大使 | NULL | 7300.33 | 401 | 1 || 2 | alex | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 || 3 | wupeiqi | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 || 4 | yuanhao | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 || 5 | liwenzhou | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 || 6 | jingliyang | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 || 7 | jinxin | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 || 8 | 成龙 | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 401 | 1 || 9 | 歪歪 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 || 10 | 丫丫 | female | 38 | 2010-11-01 | sale | NULL | 2000.35 | 402 | 2 || 11 | 丁丁 | female | 18 | 2011-03-12 | sale | NULL | 1000.37 | 402 | 2 || 12 | 星星 | female | 18 | 2016-05-13 | sale | NULL | 3000.29 | 402 | 2 || 13 | 格格 | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | 402 | 2 || 14 | 张野 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 || 15 | 程咬金 | male | 18 | 1997-03-12 | operation | NULL | 20000.00 | 403 | 3 || 16 | 程咬银 | female | 18 | 2013-03-11 | operation | NULL | 19000.00 | 403 | 3 || 17 | 程咬铜 | male | 18 | 2015-04-11 | operation | NULL | 18000.00 | 403 | 3 || 18 | 程咬铁 | female | 18 | 2014-05-12 | operation | NULL | 17000.00 | 403 | 3 |+----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+18 rows in set (0.00 sec)
4.2 查询不同的行DISTINCT
语法
SELECT DISTINCT 字段 FROM 表名;
范例
mysql> select office from employee;+--------+| office |+--------+| 401 || 401 || 401 || 401 || 401 || 401 || 401 || 401 || 402 || 402 || 402 || 402 || 402 || 403 || 403 || 403 || 403 || 403 |+--------+18 rows in set (0.00 sec)mysql> select distinct office from employee;+--------+| office |+--------+| 401 || 402 || 403 |+--------+3 rows in set (0.00 sec)
4.3 对查询的数据进行四则运算处理
注意查询出的数据需要是数值型的
语法
SELECT 字段(+-*/) FROM 表名;
范例
mysql> select office from employee;+--------+| office |+--------+| 401 || 401 || 401 || 401 || 401 || 401 || 401 || 401 || 402 || 402 || 402 || 402 || 402 || 403 || 403 || 403 || 403 || 403 |+--------+18 rows in set (0.00 sec)mysql> select distinct office from employee;+--------+| office |+--------+| 401 || 402 || 403 |+--------+3 rows in set (0.00 sec)
4.4 别名AS
语法
SELECT 字段 AS 新名字 FROM 表名；
SELECT 字段 新名字 FROM 表名；
范例
mysql> select distinct office as office_num from employee;+------------+| office_num |+------------+| 401 || 402 || 403 |+------------+3 rows in set (0.00 sec)mysql> select distinct office office_num from employee;+------------+| office_num |+------------+| 401 || 402 || 403 |+------------+3 rows in set (0.00 sec)
4.5 拼接字段CONCAT
拼接（concatenate）将值连结到一起构成单个值
语法
SELECT CONCAT(字段1,字段2) FROM 表名;
添加连接符号
SELECT CONCAT(字段1,字段2) FROM 表名;
SELECT CONCAT_WS('分隔符', 字段1,字段2) FROM 表名;
范例
mysql> select concat(emp_name, office) from employee limit 3;+--------------------------+| concat(emp_name, office) |+--------------------------+| egon401 || alex401 || wupeiqi401 |+--------------------------+3 rows in set (0.00 sec)mysql> select concat(emp_name, '(', sex, ')') from employee limit 3;+---------------------------------+| concat(emp_name, '(', sex, ')') |+---------------------------------+| egon(male) || alex(male) || wupeiqi(male) |+---------------------------------+3 rows in set (0.00 sec)mysql> select concat_ws(':', emp_name, sex) from employee limit 3;+-------------------------------+| concat_ws(':', emp_name, sex) |+-------------------------------+| egon:male || alex:male || wupeiqi:male |+-------------------------------+3 rows in set (0.00 sec) 4.6 CASE语句
语法
SELECT
( CASE WHEN 对字段进行判断 THEN 字段 WHEN 对字段进行判断 THEN CONCAT_WS(':', 字段, 字段) ELSECONCAT_WS(':', 字段, 字段) END ) AS 别名FROM 表名;范例
mysql> select ( -> case -> when sex='male' then emp_name -> else -> concat_ws(':',emp_name,'女') -> end) -> from employee;+------------------------------------------------------------------------------+| (casewhen sex='male' then emp_nameelseconcat_ws(':',emp_name,'女')end) |+------------------------------------------------------------------------------+| egon || alex || wupeiqi || yuanhao || liwenzhou || jingliyang:女 || jinxin || 成龙 || 歪歪:女 || 丫丫:女 || 丁丁:女 || 星星:女 || 格格:女 || 张野 || 程咬金 || 程咬银:女 || 程咬铜 || 程咬铁:女 |+------------------------------------------------------------------------------+18 rows in set (0.00 sec) 5.数据过滤WHERE
MySQL使用WHERE子句查询所需要的数据需要指定搜索条件（search criteria），搜索条件也成为过滤条件（filter condition）
常见过滤条件：
1- 比较运算符： > < >= <= <> != is
2- 区间范围：between num1 and num2
3- 范围： in (值1, 值2...)
4- like模糊查询: '%'表示任意多个字符 '_'表示任意一个字符
5- 多个条件：使用and or not相连
5.1 单个条件过滤
语法
SELECT 字段 FROM 表名 WHERE 过滤条件;
范例
mysql> select * from employee where sex='female';+----+------------+--------+-----+------------+-----------+--------------+----------+--------+-----------+| id | emp_name | sex | age | hire_date | post | post_comment | salary | office | depart_id |+----+------------+--------+-----+------------+-----------+--------------+----------+--------+-----------+| 6 | jingliyang | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 || 9 | 歪歪 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 || 10 | 丫丫 | female | 38 | 2010-11-01 | sale | NULL | 2000.35 | 402 | 2 || 11 | 丁丁 | female | 18 | 2011-03-12 | sale | NULL | 1000.37 | 402 | 2 || 12 | 星星 | female | 18 | 2016-05-13 | sale | NULL | 3000.29 | 402 | 2 || 13 | 格格 | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | 402 | 2 || 16 | 程咬银 | female | 18 | 2013-03-11 | operation | NULL | 19000.00 | 403 | 3 || 18 | 程咬铁 | female | 18 | 2014-05-12 | operation | NULL | 17000.00 | 403 | 3 |+----+------------+--------+-----+------------+-----------+--------------+----------+--------+-----------+8 rows in set (0.00 sec)
5.2 查询字段是否为NULL或NOT NULL
NULL无值（no value），它与字段包含0、空字符串或仅仅包含空格不同。
语法
SELECT 字段 FROM 表名 WHERE IS (NOT) NULL;
范例
mysql> select * from employee where post_comment is not null;Empty set (0.00 sec)
5.3 区间范围BETWEEN
语法
SELECT 字段 FROM 表名 WHERE BETWEEN NUM1 AND NUM2;
范例
mysql> select * from employee where age between 20 and 30;+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+| id | emp_name | sex | age | hire_date | post | post_comment | salary | office | depart_id |+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+| 5 | liwenzhou | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 || 13 | 格格 | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | 402 | 2 || 14 | 张野 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 |+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+3 rows in set (0.00 sec)
5.4 范围IN
语法
SELECT 字段 FROM 表名 WHERE IN (值1, 值2);
范例
mysql> select * from employee where age in (28, 78);+----+-----------+--------+-----+------------+-----------+--------------+------------+--------+-----------+| id | emp_name | sex | age | hire_date | post | post_comment | salary | office | depart_id |+----+-----------+--------+-----+------------+-----------+--------------+------------+--------+-----------+| 2 | alex | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 || 5 | liwenzhou | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 || 13 | 格格 | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | 402 | 2 || 14 | 张野 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 |+----+-----------+--------+-----+------------+-----------+--------------+------------+--------+-----------+4 rows in set (0.00 sec)
5.5 模糊匹配LIKE
语法
SELECT 字段 FROM 表名 WHERE 字段 LIKE '标识%';
SELECT 字段 FROM 表名 WHERE 字段 LIKE '标识_';
范例
mysql> select * from employee where emp_name like 'al%';+----+----------+------+-----+------------+---------+--------------+------------+--------+-----------+| id | emp_name | sex | age | hire_date | post | post_comment | salary | office | depart_id |+----+----------+------+-----+------------+---------+--------------+------------+--------+-----------+| 2 | alex | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 |+----+----------+------+-----+------------+---------+--------------+------------+--------+-----------+1 row in set (0.00 sec)mysql> select * from employee where emp_name like 'al_';Empty set (0.00 sec)mysql> select * from employee where emp_name like 'ale_';+----+----------+------+-----+------------+---------+--------------+------------+--------+-----------+| id | emp_name | sex | age | hire_date | post | post_comment | salary | office | depart_id |+----+----------+------+-----+------------+---------+--------------+------------+--------+-----------+| 2 | alex | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 |+----+----------+------+-----+------------+---------+--------------+------------+--------+-----------+1 row in set (0.00 sec)
5.６ 多个条件过滤
语法
SELECT 字段 FROM 表名 WHERE 过滤条件1 AND 过滤条件2;、
类似IN的语法
SELECT 字段 FROM 表名 WHERE 过滤条件1 OR 过滤条件2;
范例
mysql> select * from employee where age=18 and sex='male';+----+-----------+------+-----+------------+-----------------------------------------+--------------+----------+--------+-----------+| id | emp_name | sex | age | hire_date | post | post_comment | salary | office | depart_id |+----+-----------+------+-----+------------+-----------------------------------------+--------------+----------+--------+-----------+| 1 | egon | male | 18 | 2017-03-01 | 老男孩驻沙河办事处外交大使 | NULL | 7300.33 | 401 | 1 || 7 | jinxin | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 || 15 | 程咬金 | male | 18 | 1997-03-12 | operation | NULL | 20000.00 | 403 | 3 || 17 | 程咬铜 | male | 18 | 2015-04-11 | operation | NULL | 18000.00 | 403 | 3 |+----+-----------+------+-----+------------+-----------------------------------------+--------------+----------+--------+-----------+4 rows in set (0.00 sec)mysql> select * from employee where post='teacher' or sex=78;+----+------------+--------+-----+------------+---------+--------------+------------+--------+-----------+| id | emp_name | sex | age | hire_date | post | post_comment | salary | office | depart_id |+----+------------+--------+-----+------------+---------+--------------+------------+--------+-----------+| 2 | alex | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 || 3 | wupeiqi | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 || 4 | yuanhao | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 || 5 | liwenzhou | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 || 6 | jingliyang | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 || 7 | jinxin | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 || 8 | 成龙 | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 401 | 1 |+----+------------+--------+-----+------------+---------+--------------+------------+--------+-----------+7 rows in set (0.00 sec)
６.分组GROUP BY
MySQL使用GROUP BY子句进行分组
语法
单独使用GROUP BY进行分组，分组字段必须是查询字段
SELECT 字段 FROM 表名 GROUP BY 字段;
GROUP BY和GROUP_CONCAT()函数一起使用
SELECT 字段1, GROUP_CONCAT(字段2) FROM 表名 GROUP BY 字段1;
GROUP BY与COUNT函数一起使用
SELECT 字段1, COUNT(字段2) FROM 表名 GROUP BY 字段1;
范例
# 单独使用GROUP BYmysql> select office from employee group by office;+--------+| office |+--------+| 401 || 402 || 403 |+--------+3 rows in set (0.00 sec)# GROUP BY和GROUP_CONCAT配合使用mysql> select office,group_concat(emp_name) from employee group by office;+--------+--------------------------------------------------------------+| office | group_concat(emp_name) |+--------+--------------------------------------------------------------+| 401 | egon,alex,wupeiqi,yuanhao,liwenzhou,jingliyang,jinxin,成龙 || 402 | 歪歪,丫丫,丁丁,星星,格格 || 403 | 张野,程咬金,程咬银,程咬铜,程咬铁 |+--------+--------------------------------------------------------------+3 rows in set (0.00 sec)# GROUP BY和COUNT一同使用mysql> select office, count(emp_name) as number from employee group by office;+--------+--------+| office | number |+--------+--------+| 401 | 8 || 402 | 5 || 403 | 5 |+--------+--------+3 rows in set (0.00 sec)
7.聚合函数
7.1 统计条数COUNT()
语法
SELECT COUNT(字段.../*) FROM 表名;范例mysql> select count(*) from employee;+----------+| count(*) |+----------+| 18 |+----------+1 row in set (0.00 sec)mysql> select count(office) from employee;+---------------+| count(office) |+---------------+| 18 |+---------------+1 row in set (0.00 sec)mysql> select count(office) from employee group by office;+---------------+| count(office) |+---------------+| 8 || 5 || 5 |+---------------+3 rows in set (0.00 sec)
7.2 最大值MAX()
语法
SELECT MAX(字段) FROM 表名;范例mysql> select max(age) from employee;+----------+| max(age) |+----------+| 81 |+----------+1 row in set (0.00 sec)
7.3最小值MIN()
语法
SELECT MIN(字段) FROM 表名;范例mysql> select min(age) from employee;+----------+| min(age) |+----------+| 18 |+----------+1 row in set (0.00 sec)
7.4 平均值AVG()
语法
SELECT AVG(字段) FROM 表名;范例mysql> select avg(age) from employee;+----------+| avg(age) |+----------+| 34.0000 |+----------+1 row in set (0.00 sec)
7.5 求和SUM()
语法
SELECT SUM(字段) FROM 表名;SELECT SUM(字段) FROM 表名 WHERE 条件;范例mysql> select sum(age) from employee;+----------+| sum(age) |+----------+| 612 |+----------+1 row in set (0.00 sec)mysql> select sum(age) from employee where sex='male';+----------+| sum(age) |+----------+| 408 |+----------+1 row in set (0.00 sec)
8.HAVING过滤
HAVING非常类似于WHERE。目前为止所学过的所有类型的WHERE子句都可以用HAVING来代替。唯一的差别是WHERE过滤行，而HAVING过滤分组。
执行优先级从高到低：WHERE > GROUP BY > HAVING
WHERE发生在分组GROUP BY之前，因而WHERE中可以有任意字段，但是绝对不能使用聚合函数。
HAVING发生在分组GROUP BY之后，因而HAVING中可以使用分组的字段，无法直接取到其他字段,可以使用聚合函数
语法
SELECT 字段 FROM 表名 HAVING 过滤条件;
SELECT 字段 FROM 表名 GROUP 字段 HAVING 过滤条件;
范例
mysql> select office, count(emp_name) from employee group by office;+--------+-----------------+| office | count(emp_name) |+--------+-----------------+| 401 | 8 || 402 | 5 || 403 | 5 |+--------+-----------------+3 rows in set (0.30 sec)mysql> select office, count(emp_name) as emp_num from employee group by office having emp_num > 5;+--------+---------+| office | emp_num |+--------+---------+| 401 | 8 |+--------+---------+1 row in set (0.00 sec)
9.排序ORDER BY
语法
升序
SELECT 字段 FROM 表名 ORDER BY 字段 (ASC);
降序
SELECT 字段 FROM 表名 ORDER BY 字段 DESC;
多列排序
SELECT 字段 FROM 表名 ORDER BY 字段1, 字段2 DESC;
范例
mysql> select * from employee order by age, hire_date;+----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+| id | emp_name | sex | age | hire_date | post | post_comment | salary | office | depart_id |+----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+| 7 | jinxin | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 || 15 | 程咬金 | male | 18 | 1997-03-12 | operation | NULL | 20000.00 | 403 | 3 || 6 | jingliyang | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 || 11 | 丁丁 | female | 18 | 2011-03-12 | sale | NULL | 1000.37 | 402 | 2 || 16 | 程咬银 | female | 18 | 2013-03-11 | operation | NULL | 19000.00 | 403 | 3 || 18 | 程咬铁 | female | 18 | 2014-05-12 | operation | NULL | 17000.00 | 403 | 3 || 17 | 程咬铜 | male | 18 | 2015-04-11 | operation | NULL | 18000.00 | 403 | 3 || 12 | 星星 | female | 18 | 2016-05-13 | sale | NULL | 3000.29 | 402 | 2 || 1 | egon | male | 18 | 2017-03-01 | 老男孩驻沙河办事处外交大使 | NULL | 7300.33 | 401 | 1 || 5 | liwenzhou | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 || 14 | 张野 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 || 13 | 格格 | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | 402 | 2 || 10 | 丫丫 | female | 38 | 2010-11-01 | sale | NULL | 2000.35 | 402 | 2 || 8 | 成龙 | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 401 | 1 || 9 | 歪歪 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 || 4 | yuanhao | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 || 2 | alex | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 || 3 | wupeiqi | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 |+----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
10.限制查询结果LIMIT
LIMIT的基准从第0条开始
语法
不超过多少行
SELECT 字段 FROM 表名 LIMIT NUM;
从NUM1开始的NUM2行
SELECT 字段 FROM 表名 LIMIT NUM1,NUM2;
注意：行0 查询出来的第一行为行0而不是行1.因此，LIMIT1，1将查询出第二行而不是第一行。
从NUM1开始的NUM2行
SELECT 字段 FROM 表名 LIMIT NUM2 OFFSET NUM1;
范例
# 查询第0-2条数据mysql> select id from employee limit 3;+----+| id |+----+| 1 || 2 || 3 |+----+3 rows in set (0.00 sec)# 查询第3条数据开始的4条数据mysql> select id from employee limit 3,4;+----+| id |+----+| 4 || 5 || 6 || 7 |+----+4 rows in set (0.00 sec)# 查询从第4条数据开始的3条数据mysql> select id from employee limit 3 offset 4;+----+| id |+----+| 5 || 6 || 7 |+----+3 rows in set (0.00 sec)
11.正则表达式
语法
SELECT 字段 FROM 表名 WHERE 字段 REGEXP '正则';
范例
mysql> select * from employee where age regexp '^2';+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+| id | emp_name | sex | age | hire_date | post | post_comment | salary | office | depart_id |+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+| 5 | liwenzhou | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 || 13 | 格格 | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | 402 | 2 || 14 | 张野 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 |+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+3 rows in set (0.03 sec)mysql> select * from employee where age regexp '3$';+----+----------+------+-----+------------+---------+--------------+---------+--------+-----------+| id | emp_name | sex | age | hire_date | post | post_comment | salary | office | depart_id |+----+----------+------+-----+------------+---------+--------------+---------+--------+-----------+| 4 | yuanhao | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 |+----+----------+------+-----+------------+---------+--------------+---------+--------+-----------+1 row in set (0.00 sec)mysql> select * from employee where emp_name regexp '^j.*[gn]$';+----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+| id | emp_name | sex | age | hire_date | post | post_comment | salary | office | depart_id |+----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+| 6 | jingliyang | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 || 7 | jinxin | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 |+----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+2 rows in set (0.00 sec)