单表查询
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(':', 字段, 字段)
ELSE
CONCAT_WS(':', 字段, 字段)
END
) AS 别名
FROM 表名;
范例
mysql> select (
-> case
-> when sex='male' then emp_name
-> else
-> concat_ws(':',emp_name,'女')
-> end)
-> from employee;
+------------------------------------------------------------------------------+
| (
case
when sex='male' then emp_name
else
concat_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 BY mysql> 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)