一、单表查询的语法
SELECT 字段1,字段2... FROM 表名
WHERE 条件
GROUP BY field
HAVING 筛选
ORDER BY field
LIMIT 限制条数
二、关键字的执行优先级（重点）
1、from 库.表——找到表 2、where 条件——按照where指定的约束条件，去表中取出一条条记录 3、group by 分组条件——对取出的一条条记录分组，如果没有group by，整体作为一组 4、having 过滤——将分组的结果进行过滤 5、select——从虚拟表选择出需要的内容 6、distinct——去重，如果在查询中指定了DISTINCT子句，则会创建一张内存临时表（如果内存放不下，就需要存放在硬盘了）。这张临时表的表结构和上一步产生的虚拟表VT7是一样的，不同的是对进行DISTINCT操作的列增加了一个唯一索引，以此来除重复数据。 7、order by 排序字段——对虚拟表中的内容按照指定的列进行排序，然后返回一个新的虚拟表 8、limit n;——限制结果的显示条数，LIMIT子句从上一步得到的VT8虚拟表中选出从指定位置开始的指定行数据。对于没有应用ORDER BY的LIMIT子句，得到的结果同样是无序的，所以，很多时候，我们都会看到LIMIT子句会和ORDER BY子句一起使用
详细见：http://www.cnblogs.com/linhaifeng/articles/7372774.html
三、简单查询
company.employee 员工id id int 姓名 emp_name varchar 性别 sex enum 年龄 age int 入职日期 hire_date date 岗位 post varchar 职位描述 post_comment varchar 薪水 salary double 办公室 office int 部门编号 depart_id int #创建表 create table employee( id int not null unique auto_increment, 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 ); #查看表结构 mysql> desc employee; +--------------+-----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+-----------------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(20) | NO | | NULL | | | sex | enum('male','female') | NO | | male | | | age | int(3) unsigned | NO | | 28 | | | hire_date | date | NO | | NULL | | | post | varchar(50) | YES | | NULL | | | post_comment | varchar(100) | YES | | NULL | | | salary | double(15,2) | YES | | NULL | | | office | int(11) | YES | | NULL | | | depart_id | int(11) | YES | | NULL | | +--------------+-----------------------+------+-----+---------+----------------+ #插入记录 #三个部门：教学，销售，运营 insert into employee(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) ; #ps：如果在windows系统中，插入中文字符，select的结果为空白，可以将所有字符编码统一设置成gbk
#简单查询 SELECT id,name,sex,age,hire_date,post,post_comment,salary,office,depart_id FROM employee; SELECT * FROM employee; SELECT name,salary FROM employee; #避免重复DISTINCT SELECT DISTINCT post FROM employee; #通过四则运算查询 SELECT name, salary*12 FROM employee; SELECT name, salary*12 AS Annual_salary FROM employee; SELECT name, salary*12 Annual_salary FROM employee; #定义显示格式 CONCAT() 函数用于连接字符串 SELECT CONCAT('姓名: ',name,' 年薪: ', salary*12) AS Annual_salary FROM employee; CONCAT_WS() 第一个参数为分隔符 SELECT CONCAT_WS(':',name,salary*12) AS Annual_salary FROM employee; 结合CASE语句： SELECT ( CASE WHEN NAME = 'egon' THEN NAME WHEN NAME = 'alex' THEN CONCAT(name,'_BIGSB') ELSE concat(NAME, 'SB') END ) as new_name FROM emp;
mysql> select * from employee; +----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+ | id | 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) mysql> select id,name,salary from employee; +----+------------+------------+ | id | name | salary | +----+------------+------------+ | 1 | egon | 7300.33 | | 2 | alex | 1000000.31 | | 3 | wupeiqi | 8300.00 | | 4 | yuanhao | 3500.00 | | 5 | liwenzhou | 2100.00 | | 6 | jingliyang | 9000.00 | | 7 | jinxin | 30000.00 | | 8 | 成龙 | 10000.00 | | 9 | 歪歪 | 3000.13 | | 10 | 丫丫 | 2000.35 | | 11 | 丁丁 | 1000.37 | | 12 | 星星 | 3000.29 | | 13 | 格格 | 4000.33 | | 14 | 张野 | 10000.13 | | 15 | 程咬金 | 20000.00 | | 16 | 程咬银 | 19000.00 | | 17 | 程咬铜 | 18000.00 | | 18 | 程咬铁 | 17000.00 | +----+------------+------------+ 18 rows in set (0.00 sec)
在需要去重查询时，可以利用distinct去除重复信息。
# distinct去除重复的职位信息 mysql> select distinct post from employee; +-----------------------------------------+ | post | +-----------------------------------------+ | 老男孩驻沙河办事处外交大使 | | teacher | | sale | | operation | +-----------------------------------------+ 4 rows in set (0.00 sec)
利用四则运算查询（加减乘除）得到需要的查询结果。针对四则运算的字段还可以取别名，以优化显示结果。
# 查看一个人的年薪 mysql> select name, salary*12 from employee; +------------+-------------+ | name | salary*12 | +------------+-------------+ | egon | 87603.96 | | alex | 12000003.72 | | wupeiqi | 99600.00 | | yuanhao | 42000.00 | | liwenzhou | 25200.00 | | jingliyang | 108000.00 | | jinxin | 360000.00 | | 成龙 | 120000.00 | | 歪歪 | 36001.56 | | 丫丫 | 24004.20 | | 丁丁 | 12004.44 | | 星星 | 36003.48 | | 格格 | 48003.96 | | 张野 | 120001.56 | | 程咬金 | 240000.00 | | 程咬银 | 228000.00 | | 程咬铜 | 216000.00 | | 程咬铁 | 204000.00 | +------------+-------------+ 18 rows in set (0.00 sec) # 给年薪起一个别名 mysql> select name, salary*12 as Annual_salary from employee; +------------+---------------+ | name | Annual_salary | +------------+---------------+ | egon | 87603.96 | | alex | 12000003.72 | | wupeiqi | 99600.00 | | yuanhao | 42000.00 | | liwenzhou | 25200.00 | | jingliyang | 108000.00 | | jinxin | 360000.00 | | 成龙 | 120000.00 | | 歪歪 | 36001.56 | | 丫丫 | 24004.20 | | 丁丁 | 12004.44 | | 星星 | 36003.48 | | 格格 | 48003.96 | | 张野 | 120001.56 | | 程咬金 | 240000.00 | | 程咬银 | 228000.00 | | 程咬铜 | 216000.00 | | 程咬铁 | 204000.00 | +------------+---------------+ 18 rows in set (0.00 sec) # 起别名不加as的方法 mysql> select name, salary*12 Annual_salary from employee; +------------+---------------+ | name | Annual_salary | +------------+---------------+ | egon | 87603.96 | | alex | 12000003.72 | | wupeiqi | 99600.00 | | yuanhao | 42000.00 | | liwenzhou | 25200.00 | | jingliyang | 108000.00 | | jinxin | 360000.00 | | 成龙 | 120000.00 | | 歪歪 | 36001.56 | | 丫丫 | 24004.20 | | 丁丁 | 12004.44 | | 星星 | 36003.48 | | 格格 | 48003.96 | | 张野 | 120001.56 | | 程咬金 | 240000.00 | | 程咬银 | 228000.00 | | 程咬铜 | 216000.00 | | 程咬铁 | 204000.00 | +------------+---------------+ 18 rows in set (0.00 sec)
利用concat和逗号自由定义显示格式
# concat()函数用于连接字符串 mysql> select concat('姓名:',name) from employee; +------------------------+ | concat('姓名:',name) | +------------------------+ | 姓名:egon | | 姓名:alex | | 姓名:wupeiqi | | 姓名:yuanhao | | 姓名:liwenzhou | | 姓名:jingliyang | | 姓名:jinxin | | 姓名:成龙 | | 姓名:歪歪 | | 姓名:丫丫 | | 姓名:丁丁 | | 姓名:星星 | | 姓名:格格 | | 姓名:张野 | | 姓名:程咬金 | | 姓名:程咬银 | | 姓名:程咬铜 | | 姓名:程咬铁 | +------------------------+ 18 rows in set (0.00 sec) mysql> select concat('姓名:',name,'性别:',sex,'年薪:',salary*12) as emp_info_salary from employee; # as设置查询结果标题 +------------------------------------------------+ | emp_info_salary | +------------------------------------------------+ | 姓名:egon性别:male年薪:87603.96 | | 姓名:alex性别:male年薪:12000003.72 | | 姓名:wupeiqi性别:male年薪:99600.00 | | 姓名:yuanhao性别:male年薪:42000.00 | | 姓名:liwenzhou性别:male年薪:25200.00 | | 姓名:jingliyang性别:female年薪:108000.00 | | 姓名:jinxin性别:male年薪:360000.00 | | 姓名:成龙性别:male年薪:120000.00 | | 姓名:歪歪性别:female年薪:36001.56 | | 姓名:丫丫性别:female年薪:24004.20 | | 姓名:丁丁性别:female年薪:12004.44 | | 姓名:星星性别:female年薪:36003.48 | | 姓名:格格性别:female年薪:48003.96 | | 姓名:张野性别:male年薪:120001.56 | | 姓名:程咬金性别:male年薪:240000.00 | | 姓名:程咬银性别:female年薪:228000.00 | | 姓名:程咬铜性别:male年薪:216000.00 | | 姓名:程咬铁性别:female年薪:204000.00 | +------------------------------------------------+ 18 rows in set (0.00 sec) # 想把薪资单独分出来，用concat和逗号拼接出想要的格式 mysql> select concat('姓名:',name,'性别:',sex) as info, concat('年薪:', salary*12) as annual_salary from employee; +--------------------------------+--------------------+ | info | annual_salary | +--------------------------------+--------------------+ | 姓名:egon性别:male | 年薪:87603.96 | | 姓名:alex性别:male | 年薪:12000003.72 | | 姓名:wupeiqi性别:male | 年薪:99600.00 | | 姓名:yuanhao性别:male | 年薪:42000.00 | | 姓名:liwenzhou性别:male | 年薪:25200.00 | | 姓名:jingliyang性别:female | 年薪:108000.00 | | 姓名:jinxin性别:male | 年薪:360000.00 | | 姓名:成龙性别:male | 年薪:120000.00 | | 姓名:歪歪性别:female | 年薪:36001.56 | | 姓名:丫丫性别:female | 年薪:24004.20 | | 姓名:丁丁性别:female | 年薪:12004.44 | | 姓名:星星性别:female | 年薪:36003.48 | | 姓名:格格性别:female | 年薪:48003.96 | | 姓名:张野性别:male | 年薪:120001.56 | | 姓名:程咬金性别:male | 年薪:240000.00 | | 姓名:程咬银性别:female | 年薪:228000.00 | | 姓名:程咬铜性别:male | 年薪:216000.00 | | 姓名:程咬铁性别:female | 年薪:204000.00 | +--------------------------------+--------------------+ 18 rows in set (0.00 sec)
# concat_ws() 第一个参数为分隔符，处理多条记录+分隔符更方便 mysql> select concat_ws(':', name, sex, salary) as emp_info from employee; +---------------------------+ | emp_info | +---------------------------+ | egon:male:7300.33 | | alex:male:1000000.31 | | wupeiqi:male:8300.00 | | yuanhao:male:3500.00 | | liwenzhou:male:2100.00 | | jingliyang:female:9000.00 | | jinxin:male:30000.00 | | 成龙:male:10000.00 | | 歪歪:female:3000.13 | | 丫丫:female:2000.35 | | 丁丁:female:1000.37 | | 星星:female:3000.29 | | 格格:female:4000.33 | | 张野:male:10000.13 | | 程咬金:male:20000.00 | | 程咬银:female:19000.00 | | 程咬铜:male:18000.00 | | 程咬铁:female:17000.00 | +---------------------------+ 18 rows in set (0.00 sec)
结合case语句，自定义复杂的显示格式。
# 结合CASE语句 mysql> SELECT -> ( -> CASE -> WHEN NAME = 'egon' THEN -> NAME -> WHEN NAME = 'alex' THEN -> CONCAT(name,'_BIGSB') -> ELSE -> concat(NAME, 'SB') -> END -> ) as new_name -> FROM -> employee; +--------------+ | new_name | +--------------+ | egon | | alex_BIGSB | | wupeiqiSB | | yuanhaoSB | | liwenzhouSB | | jingliyangSB | | jinxinSB | | 成龙SB | | 歪歪SB | | 丫丫SB | | 丁丁SB | | 星星SB | | 格格SB | | 张野SB | | 程咬金SB | | 程咬银SB | | 程咬铜SB | | 程咬铁SB | +--------------+ 18 rows in set (0.00 sec)
小练习：
1、查出所有员工的名字，薪资，格式为 <名字:egon> <薪资:3000>
2、查出所有岗位(去掉重复)
3、查出所有员工名字，以及他们的年薪，年薪字段名为annual_year
mysql> select concat("<名字:",name,">") as NAME, concat("<薪资:",salary,">") as SALARY from employee; +---------------------+---------------------+ | NAME | SALARY | +---------------------+---------------------+ | <名字:egon> | <薪资:7300.33> | | <名字:alex> | <薪资:1000000.31> | | <名字:wupeiqi> | <薪资:8300.00> | | <名字:yuanhao> | <薪资:3500.00> | | <名字:liwenzhou> | <薪资:2100.00> | | <名字:jingliyang> | <薪资:9000.00> | | <名字:jinxin> | <薪资:30000.00> | | <名字:成龙> | <薪资:10000.00> | | <名字:歪歪> | <薪资:3000.13> | | <名字:丫丫> | <薪资:2000.35> | | <名字:丁丁> | <薪资:1000.37> | | <名字:星星> | <薪资:3000.29> | | <名字:格格> | <薪资:4000.33> | | <名字:张野> | <薪资:10000.13> | | <名字:程咬金> | <薪资:20000.00> | | <名字:程咬银> | <薪资:19000.00> | | <名字:程咬铜> | <薪资:18000.00> | | <名字:程咬铁> | <薪资:17000.00> | +---------------------+---------------------+ 18 rows in set (0.00 sec) mysql> select distinct post from employee; +-----------------------------------------+ | post | +-----------------------------------------+ | 老男孩驻沙河办事处外交大使 | | teacher | | sale | | operation | +-----------------------------------------+ 4 rows in set (0.00 sec) mysql> select name, salary*12 as annual_year from employee; +------------+-------------+ | name | annual_year | +------------+-------------+ | egon | 87603.96 | | alex | 12000003.72 | | wupeiqi | 99600.00 | | yuanhao | 42000.00 | | liwenzhou | 25200.00 | | jingliyang | 108000.00 | | jinxin | 360000.00 | | 成龙 | 120000.00 | | 歪歪 | 36001.56 | | 丫丫 | 24004.20 | | 丁丁 | 12004.44 | | 星星 | 36003.48 | | 格格 | 48003.96 | | 张野 | 120001.56 | | 程咬金 | 240000.00 | | 程咬银 | 228000.00 | | 程咬铜 | 216000.00 | | 程咬铁 | 204000.00 | +------------+-------------+ 18 rows in set (0.00 sec)
四、WHERE约束
where字句可以使用：
1、比较运算符：> < >= <= ; <> != (这两个不等于符号，一般采用!=)
2、between 80 and 100 指的就是大于等于80，小于等于100
3、in(80,90,100) 满足在这个范围内，值是80或90或100
4、like 'egon%' 这种是模糊匹配
pattern可以是%或_，
%表示任意多字符；_表示一个字符
5、逻辑运算符：在多个条件直接可以使用逻辑运算符 and(与) or（或） not(非)
#1:单条件查询 SELECT name FROM employee WHERE post='sale'; #2:多条件查询 (AND串联条件) SELECT name,salary FROM employee WHERE post='teacher' AND salary>10000; #3:关键字BETWEEN AND SELECT name,salary FROM employee WHERE salary BETWEEN 10000 AND 20000; SELECT name,salary FROM employee WHERE salary NOT BETWEEN 10000 AND 20000; #4:关键字IS NULL(判断某个字段是否为NULL不能用等号，需要用IS) SELECT name,post_comment FROM employee WHERE post_comment IS NULL; SELECT name,post_comment FROM employee WHERE post_comment IS NOT NULL; SELECT name,post_comment FROM employee WHERE post_comment=''; 注意''是空字符串，不是null ps： 执行 update employee set post_comment='' where id=2; 再用上条查看，就会有结果了 #5:关键字IN集合查询 SELECT name,salary FROM employee WHERE salary=3000 OR salary=3500 OR salary=4000 OR salary=9000 ; SELECT name,salary FROM employee WHERE salary IN (3000,3500,4000,9000) ; SELECT name,salary FROM employee WHERE salary NOT IN (3000,3500,4000,9000) ; #6:关键字LIKE模糊查询 通配符’%’ SELECT * FROM employee WHERE name LIKE 'eg%'; 通配符’_’ SELECT * FROM employee WHERE name LIKE 'al__';
# 1、单条件查询 mysql> select id,name,age from employee where id > 7; +----+-----------+-----+ | id | name | age | +----+-----------+-----+ | 8 | 成龙 | 48 | | 9 | 歪歪 | 48 | | 10 | 丫丫 | 38 | | 11 | 丁丁 | 18 | | 12 | 星星 | 18 | | 13 | 格格 | 28 | | 14 | 张野 | 28 | | 15 | 程咬金 | 18 | | 16 | 程咬银 | 18 | | 17 | 程咬铜 | 18 | | 18 | 程咬铁 | 18 | +----+-----------+-----+ 11 rows in set (0.00 sec) mysql> select name from employee where post='sale'; +--------+ | name | +--------+ | 歪歪 | | 丫丫 | | 丁丁 | | 星星 | | 格格 | +--------+ 5 rows in set (0.00 sec) # 2、多条件查询(AND串联) mysql> select name,salary from employee -> where post='teacher' and salary>10000; +--------+------------+ | name | salary | +--------+------------+ | alex | 1000000.31 | | jinxin | 30000.00 | +--------+------------+ 2 rows in set (0.00 sec) mysql> select * from employee where post ='teacher' and salary>8000; +----+------------+--------+-----+------------+---------+--------------+------------+--------+-----------+ | id | 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 | | 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 | +----+------------+--------+-----+------------+---------+--------------+------------+--------+-----------+ 5 rows in set (0.00 sec) # 3、关键字BETWEEN x AND y ————大于等于x，小于等于y mysql> select name,salary from employee -> where salary between 10000 and 20000; +-----------+----------+ | name | salary | +-----------+----------+ | 成龙 | 10000.00 | | 张野 | 10000.13 | | 程咬金 | 20000.00 | | 程咬银 | 19000.00 | | 程咬铜 | 18000.00 | | 程咬铁 | 17000.00 | +-----------+----------+ 6 rows in set (0.01 sec) mysql> select name,salary from employee -> where salary NOT BETWEEN 10000 AND 20000; # 10000以下，20000以上 +------------+------------+ | name | salary | +------------+------------+ | egon | 7300.33 | | alex | 1000000.31 | | wupeiqi | 8300.00 | | yuanhao | 3500.00 | | liwenzhou | 2100.00 | | jingliyang | 9000.00 | | jinxin | 30000.00 | | 歪歪 | 3000.13 | | 丫丫 | 2000.35 | | 丁丁 | 1000.37 | | 星星 | 3000.29 | | 格格 | 4000.33 | +------------+------------+ 12 rows in set (0.00 sec) #4:关键字IN集合查询 mysql> select * from employee where salary=3000 or salary=3500 or salary=4000 or salary=9000; +----+------------+--------+-----+------------+---------+--------------+---------+--------+-----------+ | id | 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 | | 6 | jingliyang | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 | +----+------------+--------+-----+------------+---------+--------------+---------+--------+-----------+ 2 rows in set (0.00 sec) mysql> select * from employee -> where salary in (3000,3500,4000,9000); +----+------------+--------+-----+------------+---------+--------------+---------+--------+-----------+ | id | 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 | | 6 | jingliyang | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 | +----+------------+--------+-----+------------+---------+--------------+---------+--------+-----------+ 2 rows in set (0.01 sec) mysql> select * from employee where salary not in (3000,3500,4000,9000); +----+-----------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+ | id | 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 | | 5 | liwenzhou | male | 28 | 2012-11-01 | teacher | NULL | 2100.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 | +----+-----------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+ 16 rows in set (0.00 sec) #5:关键字IS NULL(判断某个字段是否为NULL不能用等号，需要用IS) # 注意NULL和''空字符串是不同的 mysql> select * from employee -> where post_comment is null; +----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+ | id | 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) # is not NULL取反 mysql> select * from employee where post_comment is not null; Empty set (0.00 sec) mysql> select * from employee where post_comment=''; # 注意‘’空字符串不是null Empty set (0.00 sec) #6：关键字LIKE模糊匹配 mysql> select * from employee -> where name like 'eg%'; # 任意个数任意字符 +----+------+------+-----+------------+-----------------------------------------+--------------+---------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+------+------+-----+------------+-----------------------------------------+--------------+---------+--------+-----------+ | 1 | egon | male | 18 | 2017-03-01 | 老男孩驻沙河办事处外交大使 | NULL | 7300.33 | 401 | 1 | +----+------+------+-----+------------+-----------------------------------------+--------------+---------+--------+-----------+ 1 row in set (0.01 sec) mysql> select * from employee -> where name like 'al__'; # 一个'_'对应一个任意字符 +----+------+------+-----+------------+---------+--------------+------------+--------+-----------+ | id | 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)
五、分组查询：GROUP BY
1、什么是分组？为什么要分组？
1、分组发生在where之后，即分组是基于where之后得到的记录而进行的
2、分组指的是：将所有记录按照某个相同字段进行归类，比如针对员工信息表的职位分组，或者按照性别进行分组等。
3、为什么要分组：分门别类地进行处理。
每个部门员工、男人与女人数等，“每”这个字后面的字段，就是我们的分组依据。
4、大前提：可以按照任意字段分组，但是分组完毕后，比如group by post，只能查看post字段，如果想查看组内信息，需要借助于聚合函数。
2、ONLY_FULL_GROUP_BY
#查看MySQL 5.7默认的sql_mode如下： mysql> select @@global.sql_mode; ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION #！！！注意 ONLY_FULL_GROUP_BY的语义就是确定select target list中的所有列的值都是明确语义，简单的说来，在ONLY_FULL_GROUP_BY模式下，
target list中的值要么是来自于聚集函数的结果，要么是来自于group by list中的表达式的值。 #设置sql_mole如下操作(我们可以去掉ONLY_FULL_GROUP_BY模式)： mysql> set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
在没有设置ONLY_FULL_GROUP_BY时，在分组的情况下查看到其他字段的信息，也可以有结果，默认都是组内的第一条记录。但是这个的意义不大。
mysql> select @@global.sql_mode; +-------------------+ | @@global.sql_mode | +-------------------+ | | +-------------------+ 1 row in set (0.00 sec) mysql> select * from emp group by post; +----+------+--------+-----+------------+----------------------------+--------------+------------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+------+--------+-----+------------+----------------------------+--------------+------------+--------+-----------+ | 14 | 张野 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 | | 9 | 歪歪 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 | | 2 | alex | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 | | 1 | egon | male | 18 | 2017-03-01 | 老男孩驻沙河办事处外交大使 | NULL | 7300.33 | 401 | 1 | +----+------+--------+-----+------------+----------------------------+--------------+------------+--------+-----------+ 4 rows in set (0.00 sec) #由于没有设置ONLY_FULL_GROUP_BY,于是也可以有结果，默认都是组内的第一条记录，但其实这是没有意义的 mysql> set global sql_mode='ONLY_FULL_GROUP_BY'; Query OK, 0 rows affected (0.00 sec) mysql> quit #设置成功后，一定要退出，然后重新登录方可生效 Bye mysql> use db1; Database changed mysql> select * from emp group by post; #报错 ERROR 1055 (42000): 'db1.emp.id' isn't in GROUP BY mysql> select post,count(id) from emp group by post; #只能查看分组依据和使用聚合函数 +----------------------------+-----------+ | post | count(id) | +----------------------------+-----------+ | operation | 5 | | sale | 5 | | teacher | 7 | | 老男孩驻沙河办事处外交大使 | 1 | +----------------------------+-----------+ 4 rows in set (0.00 sec)
3、GROUP BY
（1）单独使用GROUP BY关键字分组：
SELECT post FROM employee GROUP BY post;
注意：我们按照post字段分组，那么select查询的字段只能是post，想要获取组内的其他相关信息，需要借助函数。
mysql> select post from employee GROUP BY post; +-----------------------------------------+ | post | +-----------------------------------------+ | operation | | sale | | teacher | | 老男孩驻沙河办事处外交大使 | +-----------------------------------------+ 4 rows in set (0.01 sec) mysql> select * from employee GROUP BY post; ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'db2.employee.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
（2）GROUP BY关键字和GROUP_CONCAT()函数一起使用
GROUP_CONCAT()函数将对组里的对应记录进行拼接。
#按照岗位分组，并查看组内成员名 mysql> SELECT post,GROUP_CONCAT(name) FROM employee GROUP BY post; +-----------------------------------------+---------------------------------------------------------+ | post | GROUP_CONCAT(name) | +-----------------------------------------+---------------------------------------------------------+ | operation | 张野,程咬金,程咬银,程咬铜,程咬铁 | | sale | 歪歪,丫丫,丁丁,星星,格格 | | teacher | alex,wupeiqi,yuanhao,liwenzhou,jingliyang,jinxin,成龙 | | 老男孩驻沙河办事处外交大使 | egon | +-----------------------------------------+---------------------------------------------------------+ 4 rows in set (0.00 sec) mysql> SELECT post,GROUP_CONCAT(name) as emp_members FROM employee GROUP BY post; +-----------------------------------------+---------------------------------------------------------+ | post | emp_members | +-----------------------------------------+---------------------------------------------------------+ | operation | 张野,程咬金,程咬银,程咬铜,程咬铁 | | sale | 歪歪,丫丫,丁丁,星星,格格 | | teacher | alex,wupeiqi,yuanhao,liwenzhou,jingliyang,jinxin,成龙 | | 老男孩驻沙河办事处外交大使 | egon | +-----------------------------------------+---------------------------------------------------------+ 4 rows in set (0.00 sec)
（3）GROUP BY与聚合函数一起使用
mysql> select post,count(id) as count from employee group by post; # 按照岗位分组，并查看每个组有多少人 +-----------------------------------------+-------+ | post | count | +-----------------------------------------+-------+ | operation | 5 | | sale | 5 | | teacher | 7 | | 老男孩驻沙河办事处外交大使 | 1 | +-----------------------------------------+-------+ 4 rows in set (0.00 sec)
强调：如果我们用unique的字段作为分组的依据，则每一条记录自成一组，这种分组没有意义。
多条记录之间的某个字段值相同，该字段通常用来作为分组的依据。
分组之后，只能取分组的字段，以及每个组聚合函数的结果。
4、聚合函数
注意：聚合函数聚合的是组的内容，若没有分组，则默认分为一组，因此没有分组也可以使用聚合函数。常用聚合函数如下：
SELECT COUNT(*) FROM employee; SELECT COUNT(*) FROM employee WHERE depart_id=1; SELECT MAX(salary) FROM employee; SELECT MIN(salary) FROM employee; SELECT AVG(salary) FROM employee; SELECT SUM(salary) FROM employee; SELECT SUM(salary) FROM employee WHERE depart_id=3;
# 利用聚合函数查看分组情况 # 统计每个部门员工人数 mysql> select count(id) from employee group by post; +-----------+ | count(id) | +-----------+ | 5 | | 5 | | 7 | | 1 | +-----------+ 4 rows in set (0.00 sec) # 统计公司总人数 mysql> select count(*) from employee; +----------+ | count(*) | +----------+ | 18 | +----------+ 1 row in set (0.00 sec) # 每个职位有多少个员工？count() mysql> select post, count(id) from employee group by post; +-----------------------------------------+-----------+ | post | count(id) | +-----------------------------------------+-----------+ | operation | 5 | | sale | 5 | | teacher | 7 | | 老男孩驻沙河办事处外交大使 | 1 | +-----------------------------------------+-----------+ 4 rows in set (0.00 sec) # 为员工统计取别名 count() mysql> select post,count(id) as emp_count from employee group by post; +-----------------------------------------+-----------+ | post | emp_count | +-----------------------------------------+-----------+ | operation | 5 | | sale | 5 | | teacher | 7 | | 老男孩驻沙河办事处外交大使 | 1 | +-----------------------------------------+-----------+ 4 rows in set (0.00 sec) # 取每个部门的最大工资sum() mysql> select post,max(salary) as max_salary from employee group by post; +-----------------------------------------+------------+ | post | max_salary | +-----------------------------------------+------------+ | operation | 20000.00 | | sale | 4000.33 | | teacher | 1000000.31 | | 老男孩驻沙河办事处外交大使 | 7300.33 | +-----------------------------------------+------------+ 4 rows in set (0.00 sec) # 取每个部门最小工资，min() mysql> select post,min(salary) as min_salary from employee group by post; +-----------------------------------------+------------+ | post | min_salary | +-----------------------------------------+------------+ | operation | 10000.13 | | sale | 1000.37 | | teacher | 2100.00 | | 老男孩驻沙河办事处外交大使 | 7300.33 | +-----------------------------------------+------------+ 4 rows in set (0.00 sec) # 取每个部门的平均工资，avg() mysql> select post,avg(salary) as avg_salary from employee group by post; +-----------------------------------------+---------------+ | post | avg_salary | +-----------------------------------------+---------------+ | operation | 16800.026000 | | sale | 2600.294000 | | teacher | 151842.901429 | | 老男孩驻沙河办事处外交大使 | 7300.330000 | +-----------------------------------------+---------------+ 4 rows in set (0.00 sec) # 取每个部门员工工资总和，sum() mysql> select post,sum(salary) as sum_salary from employee group by post; +-----------------------------------------+------------+ | post | sum_salary | +-----------------------------------------+------------+ | operation | 84000.13 | | sale | 13001.47 | | teacher | 1062900.31 | | 老男孩驻沙河办事处外交大使 | 7300.33 | +-----------------------------------------+------------+ 4 rows in set (0.00 sec) # 取id为3的部门员工年龄总和 mysql> select post,sum(age) from employee where depart_id =3 group by post; +-----------+----------+ | post | sum(age) | +-----------+----------+ | operation | 100 | +-----------+----------+ 1 row in set (0.00 sec) mysql> select post,sum(age) from employee where depart_id =3 group by post;
5、小练习
1. 查询岗位名以及岗位包含的所有员工名字 2. 查询岗位名以及各岗位内包含的员工个数 3. 查询公司内男员工和女员工的个数 4. 查询岗位名以及各岗位的平均薪资 5. 查询岗位名以及各岗位的最高薪资 6. 查询岗位名以及各岗位的最低薪资 7. 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资
mysql> select post,GROUP_CONCAT(name) as dev_members from employee group by post; +-----------------------------------------+---------------------------------------------------------+ | post | dev_members | +-----------------------------------------+---------------------------------------------------------+ | operation | 张野,程咬金,程咬银,程咬铜,程咬铁 | | sale | 歪歪,丫丫,丁丁,星星,格格 | | teacher | alex,wupeiqi,yuanhao,liwenzhou,jingliyang,jinxin,成龙 | | 老男孩驻沙河办事处外交大使 | egon | +-----------------------------------------+---------------------------------------------------------+ 4 rows in set (0.00 sec) mysql> select post,count(id) from employee group by post; +-----------------------------------------+-----------+ | post | count(id) | +-----------------------------------------+-----------+ | operation | 5 | | sale | 5 | | teacher | 7 | | 老男孩驻沙河办事处外交大使 | 1 | +-----------------------------------------+-----------+ 4 rows in set (0.00 sec) mysql> select sex,count(id) from employee group by sex; +--------+-----------+ | sex | count(id) | +--------+-----------+ | male | 10 | | female | 8 | +--------+-----------+ 2 rows in set (0.00 sec) mysql> select post,avg(salary) as avg_salary from employee 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,max(salary) as max_salary from employee group by post; +-----------------------------------------+------------+ | post | max_salary | +-----------------------------------------+------------+ | operation | 20000.00 | | sale | 4000.33 | | teacher | 1000000.31 | | 老男孩驻沙河办事处外交大使 | 7300.33 | +-----------------------------------------+------------+ 4 rows in set (0.00 sec) mysql> select post,min(salary) as min_salary from employee 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 sex,avg(salary) as avg_sex_salary from employee group by sex; +--------+----------------+ | sex | avg_sex_salary | +--------+----------------+ | male | 110920.077000 | | female | 7250.183750 | +--------+----------------+ 2 rows in set (0.00 sec)
六、HAVING过滤
HAVING与WHERE不一样的地方？
执行优先级从高到低：where > group by > having
1、where发生在分组group by之前，因此where中可以有任意字段，但是绝对不能使用聚合函数。
2、having发生分组group by之后，因此having中可以使用分组的字段，无法直接取到其他字段，可以使用聚合函数。
mysql> select @@sql_mode; +-------------------------------------------------------------------------------------------------------------------------------------------+ | @@sql_mode | +-------------------------------------------------------------------------------------------------------------------------------------------+ | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | +-------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> select * from employee where salary > 10000; +----+-----------+--------+-----+------------+-----------+--------------+------------+--------+-----------+ | id | 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 | | 7 | jinxin | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 | | 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 | +----+-----------+--------+-----+------------+-----------+--------------+------------+--------+-----------+ 7 rows in set (0.00 sec) mysql> select * from employee having salary > 10000; +----+-----------+--------+-----+------------+-----------+--------------+------------+--------+-----------+ | id | 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 | | 7 | jinxin | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 | | 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 | +----+-----------+--------+-----+------------+-----------+--------------+------------+--------+-----------+ 7 rows in set (0.00 sec) mysql> select post,group_concat(name) from employee group by post having salary > 10000; # 错误，分组后无法直接取到salary字段 ERROR 1054 (42S22): Unknown column 'salary' in 'having clause'
# 无法直接取到其他字段，可以使用聚合函数 mysql> select post,group_concat(name) from employee group by post having avg(salary) > 10000; +-----------+---------------------------------------------------------+ | post | group_concat(name) | +-----------+---------------------------------------------------------+ | operation | 张野,程咬金,程咬银,程咬铜,程咬铁 | | teacher | alex,wupeiqi,yuanhao,liwenzhou,jingliyang,jinxin,成龙 | +-----------+---------------------------------------------------------+ 2 rows in set (0.00 sec)
小练习：
1. 查询各岗位内包含的员工个数小于2的岗位名、岗位内包含员工名字、个数 2. 查询各岗位平均薪资大于10000的岗位名、平均工资 3. 查询各岗位平均薪资大于10000且小于20000的岗位名、平均工资
# 1. 查询各岗位内包含的员工个数小于2的岗位名、岗位内包含员工名字、个数 # 员工个数过滤前 mysql> select post, group_concat(name), count(id) from employee group by post; +-----------------------------------------+---------------------------------------------------------+-----------+ | post | group_concat(name) | count(id) | +-----------------------------------------+---------------------------------------------------------+-----------+ | operation | 张野,程咬金,程咬银,程咬铜,程咬铁 | 5 | | sale | 歪歪,丫丫,丁丁,星星,格格 | 5 | | teacher | alex,wupeiqi,yuanhao,liwenzhou,jingliyang,jinxin,成龙 | 7 | | 老男孩驻沙河办事处外交大使 | egon | 1 | +-----------------------------------------+---------------------------------------------------------+-----------+ 4 rows in set (0.00 sec) # 员工个数过滤后 mysql> select post, group_concat(name), count(id) from employee group by post having count(id) < 2; +-----------------------------------------+--------------------+-----------+ | post | group_concat(name) | count(id) | +-----------------------------------------+--------------------+-----------+ | 老男孩驻沙河办事处外交大使 | egon | 1 | +-----------------------------------------+--------------------+-----------+ 1 row in set (0.00 sec) # 2.查询各岗位平均薪资大于10000的岗位名、平均工资 mysql> select post, avg(salary) as avg_salary from employee group by post having avg_salary > 10000; +-----------+---------------+ | post | avg_salary | +-----------+---------------+ | operation | 16800.026000 | | teacher | 151842.901429 | +-----------+---------------+ 2 rows in set (0.00 sec) # 3.查询各岗位平均薪资大于10000且小于20000的岗位名、平均工资 mysql> select post, avg(salary) as avg_salary from employee group by post having avg_salary between 10000 and 20000; +-----------+--------------+ | post | avg_salary | +-----------+--------------+ | operation | 16800.026000 | +-----------+--------------+ 1 row in set (0.00 sec)
七、查询排序：ORDER BY
按单列排序：
ORDER BY...： 默认是升序排列
ORDER BY ... ASC：升序排列
ORDER BY ... DESC：降序排列
mysql> select * from employee ORDER BY salary; +----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+ | 11 | 丁丁 | female | 18 | 2011-03-12 | sale | NULL | 1000.37 | 402 | 2 | | 10 | 丫丫 | female | 38 | 2010-11-01 | sale | NULL | 2000.35 | 402 | 2 | | 5 | liwenzhou | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 | | 9 | 歪歪 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 | | 12 | 星星 | female | 18 | 2016-05-13 | sale | NULL | 3000.29 | 402 | 2 | | 4 | yuanhao | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 | | 13 | 格格 | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | 402 | 2 | | 1 | egon | male | 18 | 2017-03-01 | 老男孩驻沙河办事处外交大使 | NULL | 7300.33 | 401 | 1 | | 3 | wupeiqi | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 | | 6 | jingliyang | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 | | 8 | 成龙 | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 401 | 1 | | 14 | 张野 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 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 | | 16 | 程咬银 | female | 18 | 2013-03-11 | operation | NULL | 19000.00 | 403 | 3 | | 15 | 程咬金 | male | 18 | 1997-03-12 | operation | NULL | 20000.00 | 403 | 3 | | 7 | jinxin | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 | | 2 | alex | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 | +----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+ 18 rows in set (0.00 sec) mysql> select * from employee ORDER BY salary ASC; +----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+ | 11 | 丁丁 | female | 18 | 2011-03-12 | sale | NULL | 1000.37 | 402 | 2 | | 10 | 丫丫 | female | 38 | 2010-11-01 | sale | NULL | 2000.35 | 402 | 2 | | 5 | liwenzhou | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 | | 9 | 歪歪 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 | | 12 | 星星 | female | 18 | 2016-05-13 | sale | NULL | 3000.29 | 402 | 2 | | 4 | yuanhao | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 | | 13 | 格格 | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | 402 | 2 | | 1 | egon | male | 18 | 2017-03-01 | 老男孩驻沙河办事处外交大使 | NULL | 7300.33 | 401 | 1 | | 3 | wupeiqi | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 | | 6 | jingliyang | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 | | 8 | 成龙 | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 401 | 1 | | 14 | 张野 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 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 | | 16 | 程咬银 | female | 18 | 2013-03-11 | operation | NULL | 19000.00 | 403 | 3 | | 15 | 程咬金 | male | 18 | 1997-03-12 | operation | NULL | 20000.00 | 403 | 3 | | 7 | jinxin | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 | | 2 | alex | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 | +----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+ 18 rows in set (0.00 sec) mysql> select * from employee ORDER BY salary DESC; +----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+ | id | 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 | | 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 | | 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 | | 14 | 张野 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 | | 8 | 成龙 | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 401 | 1 | | 6 | jingliyang | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 | | 3 | wupeiqi | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 | | 1 | egon | male | 18 | 2017-03-01 | 老男孩驻沙河办事处外交大使 | NULL | 7300.33 | 401 | 1 | | 13 | 格格 | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | 402 | 2 | | 4 | yuanhao | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 | | 12 | 星星 | female | 18 | 2016-05-13 | sale | NULL | 3000.29 | 402 | 2 | | 9 | 歪歪 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 | | 5 | liwenzhou | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 | | 10 | 丫丫 | female | 38 | 2010-11-01 | sale | NULL | 2000.35 | 402 | 2 | | 11 | 丁丁 | female | 18 | 2011-03-12 | sale | NULL | 1000.37 | 402 | 2 | +----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+ 18 rows in set (0.00 sec)
按多列排序：
上面是按工资排，如果按age排序，有很多人年纪是相同的，这时就需要采用多列排序了。
例如：先按照age升序排，如果age相同按照id降序排
mysql> select * from employee -> ORDER BY AGE, -> id DESC; +----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+ | 18 | 程咬铁 | female | 18 | 2014-05-12 | operation | NULL | 17000.00 | 403 | 3 | | 17 | 程咬铜 | male | 18 | 2015-04-11 | operation | NULL | 18000.00 | 403 | 3 | | 16 | 程咬银 | female | 18 | 2013-03-11 | operation | NULL | 19000.00 | 403 | 3 | | 15 | 程咬金 | male | 18 | 1997-03-12 | operation | NULL | 20000.00 | 403 | 3 | | 12 | 星星 | female | 18 | 2016-05-13 | sale | NULL | 3000.29 | 402 | 2 | | 11 | 丁丁 | female | 18 | 2011-03-12 | sale | NULL | 1000.37 | 402 | 2 | | 7 | jinxin | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 | | 6 | jingliyang | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 | | 1 | egon | male | 18 | 2017-03-01 | 老男孩驻沙河办事处外交大使 | NULL | 7300.33 | 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 | | 5 | liwenzhou | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 | | 10 | 丫丫 | female | 38 | 2010-11-01 | sale | NULL | 2000.35 | 402 | 2 | | 9 | 歪歪 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 | | 8 | 成龙 | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 401 | 1 | | 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 | +----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+ 18 rows in set (0.00 sec)
有order by的sql语句，执行顺序（注意与语法顺序的异同）：
先执行from找到表；再按照where约束条件过滤数据；再交给group by进行分组；接着交给having进行过滤；
过滤之后运行distinct进行去重（没有distinct就不去重）；去重之后再执行order by进行排序；orderby 运行完之后运行limit。
最需要注意的一点：where这一步的时候还没有分组，因此where不能用聚合函数。体会下面这个用例
mysql> select * from employee where max(salary) > 1000; ERROR 1111 (HY000): Invalid use of group function mysql> select max(salary) from employee where salary > 1000; +-------------+ | max(salary) | +-------------+ | 1000000.31 | +-------------+ 1 row in set (0.00 sec)
另一个关注的重点：distinct语句执行是在having后面。体会下面这个用例：(用例实际执行和教材不同，需关注)
having不能使用distinct定义的别名，order by 则可以使用。
mysql> select post, count(id) as emp_count from employee -> where salary > 1000 -> group by post -> having emp_count > 5; # 此处引用不了emp_count别名，因为distinct还没有执行 +---------+-----------+ | post | emp_count | +---------+-----------+ | teacher | 7 | +---------+-----------+ 1 row in set (0.00 sec) mysql> select post, count(id) as emp_count from employee where salary > 1000 group by post having count(id) > 5; +---------+-----------+ | post | emp_count | +---------+-----------+ | teacher | 7 | +---------+-----------+ 1 row in set (0.00 sec)
小练习：
1. 查询所有员工信息，先按照age升序排序，如果age相同则按照hire_date降序排序
2. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资升序排列
3. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资降序排列# 1. 查询各岗位内包含的员工个数小于2的岗位名、岗位内包含员工名字、个数 mysql> select * from employee ORDER BY age ASC, hire_date DESC; +----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+ | 1 | egon | male | 18 | 2017-03-01 | 老男孩驻沙河办事处外交大使 | NULL | 7300.33 | 401 | 1 | | 12 | 星星 | female | 18 | 2016-05-13 | sale | NULL | 3000.29 | 402 | 2 | | 17 | 程咬铜 | male | 18 | 2015-04-11 | operation | NULL | 18000.00 | 403 | 3 | | 18 | 程咬铁 | female | 18 | 2014-05-12 | operation | NULL | 17000.00 | 403 | 3 | | 16 | 程咬银 | female | 18 | 2013-03-11 | operation | NULL | 19000.00 | 403 | 3 | | 11 | 丁丁 | female | 18 | 2011-03-12 | sale | NULL | 1000.37 | 402 | 2 | | 6 | jingliyang | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 | | 15 | 程咬金 | male | 18 | 1997-03-12 | operation | NULL | 20000.00 | 403 | 3 | | 7 | jinxin | male | 18 | 1900-03-01 | teacher | NULL | 30000.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 | | 5 | liwenzhou | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 | | 10 | 丫丫 | female | 38 | 2010-11-01 | sale | NULL | 2000.35 | 402 | 2 | | 9 | 歪歪 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 | | 8 | 成龙 | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 401 | 1 | | 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 | +----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+ 18 rows in set (0.00 sec) # 分组之后，distinct这里只能查被分组的字段和聚合函数 # 2.查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资升序排列 mysql> select post, avg(salary),group_concat(name) from employee GROUP BY post having avg(salary) > 10000 order by avg(salary) ASC; +-----------+---------------+---------------------------------------------------------+ | post | avg(salary) | group_concat(name) | +-----------+---------------+---------------------------------------------------------+ | operation | 16800.026000 | 张野,程咬金,程咬银,程咬铜,程咬铁 | | teacher | 151842.901429 | alex,wupeiqi,yuanhao,liwenzhou,jingliyang,jinxin,成龙 | +-----------+---------------+---------------------------------------------------------+ 2 rows in set (0.00 sec) # 3. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资降序排列 mysql> select post, avg(salary),group_concat(name) from employee GROUP BY post having avg(salary) > 10000 order by avg(salary) DESC; +-----------+---------------+---------------------------------------------------------+ | post | avg(salary) | group_concat(name) | +-----------+---------------+---------------------------------------------------------+ | teacher | 151842.901429 | alex,wupeiqi,yuanhao,liwenzhou,jingliyang,jinxin,成龙 | | operation | 16800.026000 | 张野,程咬金,程咬银,程咬铜,程咬铁 | +-----------+---------------+---------------------------------------------------------+ 2 rows in set (0.00 sec)
八、限制查询的记录数：LIMIT
LIMIT语法上是写在最后，执行时也是最后一个执行。
限制最终打印在屏幕上的条数。
mysql> select * from employee order by salary desc -> limit 3; +----+-----------+------+-----+------------+-----------+--------------+------------+--------+-----------+ | id | 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 | | 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 | +----+-----------+------+-----+------------+-----------+--------------+------------+--------+-----------+ 3 rows in set (0.00 sec) # 找出工资最高人的详细信息 mysql> select * from employee order by salary desc limit 1; +----+------+------+-----+------------+---------+--------------+------------+--------+-----------+ | id | 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)
LIMIT还能够实现分页功能（但是使用LIMIT来分页效率不高，一般还是需要一些缓存机制来帮忙实现分页）
# limit做分页功能 mysql> select * from employee order by salary DESC -> limit 0,5; +----+-----------+--------+-----+------------+-----------+--------------+------------+--------+-----------+ | id | 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 | | 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 | | 16 | 程咬银 | female | 18 | 2013-03-11 | operation | NULL | 19000.00 | 403 | 3 | | 17 | 程咬铜 | male | 18 | 2015-04-11 | operation | NULL | 18000.00 | 403 | 3 | +----+-----------+--------+-----+------------+-----------+--------------+------------+--------+-----------+ 5 rows in set (0.01 sec) mysql> select * from employee order by salary desc -> limit 5,5; # 从第5开始，即先查询出第6条，然后包含这一条在内往后查5条 +----+------------+--------+-----+------------+-----------+--------------+----------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+------------+--------+-----+------------+-----------+--------------+----------+--------+-----------+ | 18 | 程咬铁 | female | 18 | 2014-05-12 | operation | NULL | 17000.00 | 403 | 3 | | 14 | 张野 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 | | 8 | 成龙 | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 401 | 1 | | 6 | jingliyang | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 | | 3 | wupeiqi | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 | +----+------------+--------+-----+------------+-----------+--------------+----------+--------+-----------+ 5 rows in set (0.00 sec)
九、正则查询
更加强大的模糊匹配——正则表达式。
调用正则表达式查询的关键字——REGEXP
# 名字是'jin'开头的记录 mysql> select * from employee where name regexp '^jin'; +----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+ | id | 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.01 sec) # 名字是'jin'开头，且以g或n结尾的记录 mysql> select * from employee where name regexp '^jin.*(g|n)$'; +----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+ | id | 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) # 名字以on结尾的记录 mysql> select * from employee where name regexp 'on$'; +----+------+------+-----+------------+-----------------------------------------+--------------+---------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+------+------+-----+------------+-----------------------------------------+--------------+---------+--------+-----------+ | 1 | egon | male | 18 | 2017-03-01 | 老男孩驻沙河办事处外交大使 | NULL | 7300.33 | 401 | 1 | +----+------+------+-----+ # 匹配m字符两次'mm' mysql> select * from employee where name regexp 'm{2}'; Empty set (0.00 sec) mysql> select * from employee where name regexp 'g{1}'; +----+------------+--------+-----+------------+-----------------------------------------+--------------+---------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+------------+--------+-----+------------+-----------------------------------------+--------------+---------+--------+-----------+ | 1 | egon | male | 18 | 2017-03-01 | 老男孩驻沙河办事处外交大使 | NULL | 7300.33 | 401 | 1 | | 6 | jingliyang | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 | +----+------------+--------+-----+------------+-----------------------------------------+--------------+---------+--------+-----------+ 2 rows in set (0.00 sec)
小结：对字符串匹配的方式 WHERE name = 'egon'; WHERE name LIKE 'yua%'; WHERE name REGEXP 'on$';
十、单表查询的语法顺序和执行顺序总结
语法顺序：
select distinct 字段1,字段2,字段3 from 库.表 where 条件 group by 分组条件 having 过滤 order by 排序字段 limit n;
执行顺序：
select执行了一个打印，各个关键字关系用伪代码表示如下：
def from(db, table): f = open(r'%s\%s' %(db, table)) return f def where(condition, f): for line in f: if condition: yield line def group(lines): pass def having(group_res): pass def distinct(having_res): pass def order(distinct_res): pass def limit(order_res): pass def select(): from('db1', 't1') lines=where('id>3',f) group_res=group(lines) having_res=having(group_res) distinct_res=distinct(having_res) order_res=order(distinct_res) res=limit(order_res) print(res) return res