在进行查询 *** 作之前，先让我们建一个表，来方便执行后面的查询 *** 作
<span >create <span >table<span > employee(ID <span >int <span >not <span >null <span >unique<span > auto_increment,name <span >varchar(<span >20) <span >not <span >null<span >,sex enum(<span >'<span >male<span >',<span >'<span >female<span >') <span >not <span >null <span >default <span >'<span >male<span >'<span >,#大部分是男的
age <span >int(<span >3) unsigned <span >not <span >null <span >default <span >28<span >,hire_date date <span >not <span >null<span >,post <span >varchar(<span >50<span >),post_comment <span >varchar(<span >100<span >),salary <span >double(<span >15,<span >2<span >),office <span >int<span >,#一个部门一个屋子
depart_ID <span >int<span >
);查看表结构
MysqL<span >> <span >desc<span > employee;
<span >+<span >--<span >------------+-----------------------+------+-----+---------+----------------+
<span >| FIEld <span >| Type <span >| <span >Null <span >| <span >Key <span >| <span >Default <span >| Extra <span >|
<span >+<span >--<span >------------+-----------------------+------+-----+---------+----------------+
<span >| ID <span >| <span >int(<span >11) <span >| NO <span >| PRI <span >| <span >NulL <span >| auto_increment <span >|
<span >| name <span >| <span >varchar(<span >20) <span >| NO <span >| <span >| <span >NulL <span >| <span >|
<span >| sex <span >| enum(<span >'<span >male<span >',<span >'<span >female<span >') <span >| NO <span >| <span >| male <span >| <span >|
<span >| age <span >| <span >int(<span >3) unsigned <span >| NO <span >| <span >| <span >28 <span >| <span >|
<span >| hire_date <span >| date <span >| NO <span >| <span >| <span >NulL <span >| <span >|
<span >| post <span >| <span >varchar(<span >50) <span >| YES <span >| <span >| <span >NulL <span >| <span >|
<span >| post_comment <span >| <span >varchar(<span >100) <span >| YES <span >| <span >| <span >NulL <span >| <span >|
<span >| salary <span >| <span >double(<span >15,<span >2) <span >| YES <span >| <span >| <span >NulL <span >| <span >|
<span >| office <span >| <span >int(<span >11) <span >| YES <span >| <span >| <span >NulL <span >| <span >|
<span >| depart_ID <span >| <span >int(<span >11) <span >| YES <span >| <span >| <span >NulL <span >| <span >|
<span >+<span >--<span >------------+-----------------------+------+-----+---------+----------------+
<span >
<span >insert <span >into employee(name,sex,age,hiredate,post,salary,office,departID) <span >values<span >
(<span >'<span >egon<span >',<span >'<span >male<span >',<span >18,<span >'<span >20170301<span >',<span >'<span >老男孩驻沙河办事处外交大使<span >',<span >7300.33,<span >401,<span >1<span >),#以下是教学部
(<span >'<span >alex<span >',<span >78,<span >'<span >20150302<span >',<span >'<span >teacher<span >',<span >1000000.31,(<span >'<span >wupeiqi<span >',<span >81,<span >'<span >20130305<span >',<span >8300,(<span >'<span >yuanhao<span >',<span >73,<span >'<span >20140701<span >',<span >3500,(<span >'<span >liwenzhou<span >',<span >28,<span >'<span >20121101<span >',<span >2100,(<span >'<span >jingliyang<span >',<span >'<span >female<span >',<span >'<span >20110211<span >',<span >9000,(<span >'<span >jinxin<span >',<span >'<span >19000301<span >',<span >30000,(<span >'<span >成龙<span >',<span >48,<span >'<span >20101111<span >',<span >10000,(<span >'<span >歪歪<span >',<span >'<span >20150311<span >',<span >'<span >sale<span >',<span >3000.13,<span >402,#以下是销售部门
(<span >'<span >丫丫<span >',<span >38,<span >'<span >20101101<span >',<span >2000.35,(<span >'<span >丁丁<span >',<span >'<span >20110312<span >',<span >1000.37,(<span >'<span >星星<span >',<span >'<span >20160513<span >',<span >3000.29,(<span >'<span >格格<span >',<span >'<span >20170127<span >',<span >4000.33,(<span >'<span >张野<span >',<span >'<span >20160311<span >',<span >'<span >operation<span >',<span >10000.13,<span >403,<span >3<span >),#以下是运营部门
(<span >'<span >程咬金<span >',<span >'<span >19970312<span >',<span >20000,(<span >'<span >程咬银<span >',<span >'<span >20130311<span >',<span >19000,(<span >'<span >程咬铜<span >',<span >'<span >20150411<span >',<span >18000,(<span >'<span >程咬铁<span >',<span >'<span >20140512<span >',<span >17000,<span >3<span >)
;
<div >
字段1,字段2... 表名 约束条件 字段 过滤条件 字段 limit 限制条数上面这个查询语句的执行顺序如下：
1：先执行from,从硬盘中读取找到表
2：如果有where,则再通过where后面的约束条件，去文件/表中取出一条条数据（没有where则是取出表中的所有数据）
3：where执行完后，如果有group by，则将取出来的数据按group by后面的字段名分组，如果没有group by则默认将第二步中的数据分为一组
4：按照select后的字段得到一张新的虚拟表，如果有聚合函数，则将组内数据进行聚合，
5：再进一步通过having后面的条件过滤数据（如果有having的话）
6：查出结果 select
7：去重distinct
8.将结果按条件排序：order by
9.限制结果的显示条数
详细过程：请点击此处
三 简单查询</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;">select</span> <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #808080;">*</span> <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;">from</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;"> employee;</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;">select</span> <a href="https://m.jb51.cc/tag/name/" target="_blank" >name</a>,salary <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;">from</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;"> employee;避免重复(去重) distinct</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;">select</span> <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;"><a href="https://www.jb51.cc/tag/dis/" target="_blank" >dis</a>tinct</span> post <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;">from</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;"> employee; </span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;">select</span> <a href="https://m.jb51.cc/tag/name/" target="_blank" >name</a> as 名字,salary<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #808080;">*</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #800000; <a href="https://m.jb51.cc/tag/Font/" target="_blank" >Font</a>-weight: b<a href="https://m.jb51.cc/tag/ol/" target="_blank" >ol</a>d;">12</span> as 年薪 <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;">from</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;"> employee; #在字段名后面as新<a href="https://www.jb51.cc/tag/mingcheng/" target="_blank" >名称</a>，可以重新定义其在表中的名字，也可以省略as直接写新名字</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;">select</span> <a href="https://m.jb51.cc/tag/name/" target="_blank" >name</a>,salary<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #808080;">*</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #800000; <a href="https://m.jb51.cc/tag/Font/" target="_blank" >Font</a>-weight: b<a href="https://m.jb51.cc/tag/ol/" target="_blank" >ol</a>d;">12</span> <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;">as</span> Annual_salary <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;">from</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;"> employee;</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;">select</span> <a href="https://m.jb51.cc/tag/name/" target="_blank" >name</a>,salary<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #808080;">*</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #800000; <a href="https://m.jb51.cc/tag/Font/" target="_blank" >Font</a>-weight: b<a href="https://m.jb51.cc/tag/ol/" target="_blank" >ol</a>d;">12</span> Annual_salary <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;">from</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;"> employee;CONCAT() 函数用于连接字符串
CONCAT_WS() 第一个参数为分隔符
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
</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;">select</span> <a href="https://m.jb51.cc/tag/name/" target="_blank" >name</a>,salary <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;">from</span> employee <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;">where</span> post<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #808080;">=</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #ff0000;">'</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #ff0000;">teacher</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #ff0000;">'</span> <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #808080;">and</span> salary<span >></span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #800000; <a href="https://m.jb51.cc/tag/Font/" target="_blank" >Font</a>-weight: b<a href="https://m.jb51.cc/tag/ol/" target="_blank" >ol</a>d;">10000</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;">;<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;">select</span> <a href="https://m.jb51.cc/tag/name/" target="_blank" >name</a>,salary <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;">from</span> employee <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;">where</span> salary <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #808080;">between</span> <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #800000; <a href="https://m.jb51.cc/tag/Font/" target="_blank" >Font</a>-weight: b<a href="https://m.jb51.cc/tag/ol/" target="_blank" >ol</a>d;">10000</span> <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #808080;">and</span> <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #800000; <a href="https://m.jb51.cc/tag/Font/" target="_blank" >Font</a>-weight: b<a href="https://m.jb51.cc/tag/ol/" target="_blank" >ol</a>d;">20000</span>; #<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #800000; <a href="https://m.jb51.cc/tag/Font/" target="_blank" >Font</a>-weight: b<a href="https://m.jb51.cc/tag/ol/" target="_blank" >ol</a>d;">10000</span>≤结果≤<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #800000; <a href="https://m.jb51.cc/tag/Font/" target="_blank" >Font</a>-weight: b<a href="https://m.jb51.cc/tag/ol/" target="_blank" >ol</a>d;">20000</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;">select</span> <a href="https://m.jb51.cc/tag/name/" target="_blank" >name</a>,salary <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;">from</span> employee <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;">where</span> salary <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #808080;">not</span> <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #808080;">between</span> <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #800000; <a href="https://m.jb51.cc/tag/Font/" target="_blank" >Font</a>-weight: b<a href="https://m.jb51.cc/tag/ol/" target="_blank" >ol</a>d;">10000</span> <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #808080;">and</span> <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #800000; <a href="https://m.jb51.cc/tag/Font/" target="_blank" >Font</a>-weight: b<a href="https://m.jb51.cc/tag/ol/" target="_blank" >ol</a>d;">20000</span>; # 结果≤<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #800000; <a href="https://m.jb51.cc/tag/Font/" target="_blank" >Font</a>-weight: b<a href="https://m.jb51.cc/tag/ol/" target="_blank" >ol</a>d;">10000</span>，结果≥<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #800000; <a href="https://m.jb51.cc/tag/Font/" target="_blank" >Font</a>-weight: b<a href="https://m.jb51.cc/tag/ol/" target="_blank" >ol</a>d;">20000</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;"></span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;">select</span> <a href="https://m.jb51.cc/tag/name/" target="_blank" >name</a>,post_comment <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;">from</span> employee <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;">where</span> post_comment <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;">is</span> <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;">n<a href="https://m.jb51.cc/tag/ul/" target="_blank" >ul</a>l</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;">;</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;">select</span> <a href="https://m.jb51.cc/tag/name/" target="_blank" >name</a>,post_comment <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;">from</span> employee <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;">where</span> post_comment <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;">is</span> <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #808080;">not</span> <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;">n<a href="https://m.jb51.cc/tag/ul/" target="_blank" >ul</a>l</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;">;</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;">select</span> <a href="https://m.jb51.cc/tag/name/" target="_blank" >name</a>,post_comment <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;">from</span> employee <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;">where</span> post_comment<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #808080;">=</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #ff0000;">''</span>; 注意<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #ff0000;">''</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;">是空字符串，不是n<a href="https://m.jb51.cc/tag/ul/" target="_blank" >ul</a>lps： 执行 </span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;">update</span> employee <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;">set</span> post_comment<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #808080;">=</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #ff0000;">''</span> <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;">where</span> <a href="https://m.jb51.cc/tag/ID/" target="_blank" >ID</a><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #808080;">=</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #800000; <a href="https://m.jb51.cc/tag/Font/" target="_blank" >Font</a>-weight: b<a href="https://m.jb51.cc/tag/ol/" target="_blank" >ol</a>d;">2</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;">; 再用上条查看，就会有结果了</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;">select</span> <a href="https://m.jb51.cc/tag/name/" target="_blank" >name</a>,salary <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;">from</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;"> employee </span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;">where</span> salary<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #808080;">=</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #800000; <a href="https://m.jb51.cc/tag/Font/" target="_blank" >Font</a>-weight: b<a href="https://m.jb51.cc/tag/ol/" target="_blank" >ol</a>d;">3000</span> <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #808080;">or</span> salary<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #808080;">=</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #800000; <a href="https://m.jb51.cc/tag/Font/" target="_blank" >Font</a>-weight: b<a href="https://m.jb51.cc/tag/ol/" target="_blank" >ol</a>d;">3500</span> <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #808080;">or</span> salary<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #808080;">=</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #800000; <a href="https://m.jb51.cc/tag/Font/" target="_blank" >Font</a>-weight: b<a href="https://m.jb51.cc/tag/ol/" target="_blank" >ol</a>d;">4000</span> <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #808080;">or</span> salary<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #808080;">=</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #800000; <a href="https://m.jb51.cc/tag/Font/" target="_blank" >Font</a>-weight: b<a href="https://m.jb51.cc/tag/ol/" target="_blank" >ol</a>d;">9000</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;"> ;</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;">select</span> <a href="https://m.jb51.cc/tag/name/" target="_blank" >name</a>,salary <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;">from</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;"> employee </span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;">where</span> salary <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #808080;">in</span> (<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #800000; <a href="https://m.jb51.cc/tag/Font/" target="_blank" >Font</a>-weight: b<a href="https://m.jb51.cc/tag/ol/" target="_blank" >ol</a>d;">3000</span>,<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #800000; <a href="https://m.jb51.cc/tag/Font/" target="_blank" >Font</a>-weight: b<a href="https://m.jb51.cc/tag/ol/" target="_blank" >ol</a>d;">4000</span>,<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #800000; <a href="https://m.jb51.cc/tag/Font/" target="_blank" >Font</a>-weight: b<a href="https://m.jb51.cc/tag/ol/" target="_blank" >ol</a>d;">9000</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;">) ;</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;">select</span> <a href="https://m.jb51.cc/tag/name/" target="_blank" >name</a>,salary <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;">from</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;"> employee </span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;">where</span> salary <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #808080;">not</span> <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #808080;">in</span> (<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #800000; <a href="https://m.jb51.cc/tag/Font/" target="_blank" >Font</a>-weight: b<a href="https://m.jb51.cc/tag/ol/" target="_blank" >ol</a>d;">3000</span>,<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #800000; <a href="https://m.jb51.cc/tag/Font/" target="_blank" >Font</a>-weight: b<a href="https://m.jb51.cc/tag/ol/" target="_blank" >ol</a>d;">9000</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;">) ;<a href="https://m.jb51.cc/tag/tongpeifu/" target="_blank" >通配符</a>’</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #808080;">%</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;">’</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;">select</span> <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #808080;">*</span> <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;">from</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;"> employee </span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;">where</span> <a href="https://m.jb51.cc/tag/name/" target="_blank" >name</a> <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #808080;"><a href="https://m.jb51.cc/tag/li/" target="_blank" >li</a>ke</span> <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #ff0000;">'</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #ff0000;">eg%</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #ff0000;">'</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;">; #匹配<a href="https://m.jb51.cc/tag/name/" target="_blank" >name</a>为eg开头的数据</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;">select</span> <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #808080;">*</span> <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;">from</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;"> employee </span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;">where</span> <a href="https://m.jb51.cc/tag/name/" target="_blank" >name</a> <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #808080;"><a href="https://m.jb51.cc/tag/li/" target="_blank" >li</a>ke</span> <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #ff0000;">'</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #ff0000;">%e%</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #ff0000;">'</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;">; #匹配<a href="https://m.jb51.cc/tag/name/" target="_blank" >name</a>中含有e的数据<a href="https://m.jb51.cc/tag/tongpeifu/" target="_blank" >通配符</a>’_’</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;">select</span> <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #808080;">*</span> <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;">from</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;"> employee </span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;">where</span> <a href="https://m.jb51.cc/tag/name/" target="_blank" >name</a> <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #808080;"><a href="https://m.jb51.cc/tag/li/" target="_blank" >li</a>ke</span> <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #ff0000;">'</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #ff0000;">al__</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #ff0000;">'</span>; #匹配<a href="https://m.jb51.cc/tag/name/" target="_blank" >name</a>为al开头，且为长度为4个字符的数据</pre>post employee <span >group<span > by关键字和GROUP_CONCAT()函数一起使用
<span >select post,GROUP_CONCAT(name) <span >from employee <span >group <span >by<span > post;#按照岗位分组，并查看组内成员名
<span >select post,GROUP_CONCAT(name) <span >as emp_members <span >from employee <span >group <span >by<span > post;
<span >group<span > by与聚合函数一起使用
<span >select post,<span >COUNT(ID) <span >as <span >COUNT <span >from employee <span >group <span >by post;#按照岗位分组，并查看每个组有多少人
强调：
#参考链接：http:www.ywnds.com?p ID, tt (): Expression # List clause nonaggregated which functionally dependent columns clause; this incompatible sql_mode<span >5<span >.7默认的sql_mode如下：MysqL
<span >> <span >select <span >@@global<span >.sql_mode;ONLY_FulL_GROUP_BY,STRICTTRANStableS,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERRORFORdivISION_BYZERO,NOauto_CREATE_USER,NO_ENGINE_SUBSTITUTION去掉ONLY_FulL_GROUP_BY模式，如下 *** 作：
MysqL
<span >> <span >set global sql_mode<span >=<span >'<span >STRICTTRANStableS,NO_ENGINE_SUBSTITUTION<span >'<span >;！！！注意ONLY_FulL_GROUP_BY的语义就是确定select target List中的所有列的值都是明确语义，简单的说来，在ONLY_FulL_GROUP_BY模式下，target List中的值要么是来自于聚集函数的结果，要么是来自于group
<span >by List中的表达式的值。<div onclick="cnblogs_code_show('6b0324a8-e84b-45b5-983f-bdda70d89aa8')">
<div ID="cnblogs_code_open_6b0324a8-e84b-45b5-983f-bdda70d89aa8" >
1234567. 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资MysqL> select post,group_concat(name) +-----------------------------------------+---------------------------------------------------------+| post | group_concat(name) |+-----------------------------------------+---------------------------------------------------------+| operation | 张野,程咬金,程咬银,程咬铜,程咬铁 || sale | 歪歪,丫丫,丁丁,星星,格格 || teacher | alex,wupeiqi,yuanhao,liwenzhou,jingliyang,jinxin,成龙 || 老男孩驻沙河办事处外交大使 | egon |+-----------------------------------------+---------------------------------------------------------+<span >#
<span >题目2：MysqL> select post,count(ID) <span >from<span > employee group by post;
+-----------------------------------------+-----------+
| post | count(ID) |
+-----------------------------------------+-----------+
| operation | 5 |
| sale | 5 |
| teacher | 7 |
| 老男孩驻沙河办事处外交大使 | 1 |
+-----------------------------------------+-----------+
<span >#
<span >题目3：MysqL> select sex,count(ID) <span >from<span > employee group by sex;
+--------+-----------+
| sex | count(ID) |
+--------+-----------+
| male | 10 |
| female | 8 |
+--------+-----------+
<span >#
<span >题目4：MysqL> select post,avg(salary) <span >from<span > employee group by post;
+-----------------------------------------+---------------+
| post | avg(salary) |
+-----------------------------------------+---------------+
| operation | 16800.026000 |
| sale | 2600.294000 |
| teacher | 151842.901429 |
| 老男孩驻沙河办事处外交大使 | 7300.330000 |
+-----------------------------------------+---------------+
<span >#
<span >题目5MysqL> select post,max(salary) <span >from<span > employee group by post;
+-----------------------------------------+-------------+
| post | max(salary) |
+-----------------------------------------+-------------+
| operation | 20000.00 |
| sale | 4000.33 |
| teacher | 1000000.31 |
| 老男孩驻沙河办事处外交大使 | 7300.33 |
+-----------------------------------------+-------------+
<span >#
<span >题目6MysqL> select post,min(salary) <span >from<span > employee group by post;
+-----------------------------------------+-------------+
| post | min(salary) |
+-----------------------------------------+-------------+
| operation | 10000.13 |
| sale | 1000.37 |
| teacher | 2100.00 |
| 老男孩驻沙河办事处外交大使 | 7300.33 |
+-----------------------------------------+-------------+
<span >#
<span >题目七MysqL> select sex,avg(salary) <span >from<span > employee group by sex;
+--------+---------------+
| sex | avg(salary) |
+--------+---------------+
| male | 110920.077000 |
| female | 7250.183750 |
+--------+---------------+六 使用聚合函数查询
先from找到表
再用where的条件约束去表中取出记录
然后进行分组group by，没有分组则默认一组
然后进行聚合
最后select出结果
**) FROM employee WHERE depart_ID=1=3;七 HAVING过滤HAVING与WHERE在语法上是一样的
employee salary employee salary ;group by > 聚合函数 > having select count(ID) employee where salary > 10000; select count(ID) employee having salary > 10000;10000的过滤
<span >#
<span >以上两条sql的顺序是1:找到表employee--->用where过滤---->没有分组则默认一组执行聚合count(ID)---><span >select执行查看组内ID数目
2:找到表employee--->没有分组则默认一组执行聚合count(ID)---->having 基于上一步聚合的结果（此时只有count(ID)字段了）进行salary>10000的过滤，很明显，根本无法获取到salary字段
验证不同之处
#验证之前再次强调：执行优先级从高到低： 聚合函数 (ID) employee salary (ID) employee salary ;#错误，分析：先执行聚合count(ID)，后执行having过滤，无法对ID进行salary<span >1:找到表employee<span >--<span >->用where过滤---->没有分组则默认一组执行聚合count(ID)--->select执行查看组内ID数目<span >2:找到表employee<span >--<span >->没有分组则默认一组执行聚合count(ID)---->having 基于上一步聚合的结果（此时只有count(ID)字段了）进行salary>10000的过滤，很明显，根本无法获取到salary字段
其他需要注意的问题
post,group_concat(name) employee post salary post,group_concat(name) employee post (salary) ;<div onclick="cnblogs_code_show('1b9be7c9-12e4-4a73-910a-bc874b924d32')">
<div ID="cnblogs_code_open_1b9be7c9-12e4-4a73-910a-bc874b924d32" >
. 查询各岗位平均薪资大于10000且小于20000的岗位名、平均工资 post,group_concat(name),(ID) employee post (ID) post group_concat(name) (ID) teacher egon post,(salary) employee post (salary) post (salary) operation teacher post,(salary) employee post (salary) (salary) post (salary) operation 八 查询排序:ORDER BY employee employee salary employee salary 按多列排序:先按照age排序，如果年纪相同，则按照薪资排序<span >SELECT <span >* <span >from<span > employee
<span >ORDER <span >BY<span > age,salary <span >DESC;. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资降序排列 employee age ,hire_date MysqL<span >> <span >select post,<span >avg(salary) <span >from employee <span >group <span >by post <span >having <span >avg(salary) <span >> <span >10000 <span >order <span >by <span >avg(salary) <span >asc<span >;
<span >+<span >--<span >---------+---------------+
<span >| post <span >| <span >avg(salary) <span >|
<span >+<span >--<span >---------+---------------+
<span >| operation <span >| <span >16800.026000 <span >|
<span >| teacher <span >| <span >151842.901429 <span >|
<span >+<span >--<span >---------+---------------+
<span >题目3
MysqL
<span >> <span >select post,<span >avg(salary) <span >from employee <span >group <span >by post <span >having <span >avg(salary) <span >> <span >10000 <span >order <span >by <span >avg(salary) <span >desc<span >;<span >+<span >--<span >---------+---------------+
<span >| post <span >| <span >avg(salary) <span >|
<span >+<span >--<span >---------+---------------+
<span >| teacher <span >| <span >151842.901429 <span >|
<span >| operation <span >| <span >16800.026000 <span >|
<span >+<span >--<span >---------+---------------+九 限制查询的记录数:liMIT employee salary
</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;">SELECT</span> <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #808080;">*</span> <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;">FROM</span> employee <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;">ORDER</span> <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;">BY</span> salary <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;">DESC</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;"> <a href="https://m.jb51.cc/tag/li/" target="_blank" >li</a>MIT </span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #800000; <a href="https://m.jb51.cc/tag/Font/" target="_blank" >Font</a>-weight: b<a href="https://m.jb51.cc/tag/ol/" target="_blank" >ol</a>d;">0</span>,<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #800000; <a href="https://m.jb51.cc/tag/Font/" target="_blank" >Font</a>-weight: b<a href="https://m.jb51.cc/tag/ol/" target="_blank" >ol</a>d;">5</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;">; #从第0开始，即先<a href="https://www.jb51.cc/tag/chaxun/" target="_blank" >查询</a>出第一条，<a href="https://m.jb51.cc/tag/ranhou/" target="_blank" >然后</a>包含这一条在内往后查5条</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;">SELECT</span> <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #808080;">*</span> <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;">FROM</span> employee <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;">ORDER</span> <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;">BY</span> salary <span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #0000ff;">DESC</span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #000000;"> <a href="https://m.jb51.cc/tag/li/" target="_blank" >li</a>MIT </span><span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #800000; <a href="https://m.jb51.cc/tag/Font/" target="_blank" >Font</a>-weight: b<a href="https://m.jb51.cc/tag/ol/" target="_blank" >ol</a>d;">5</span>,<span https://m.jb51.cc/tag/color/" target="_blank" >color</a>: #800000; <a href="https://m.jb51.cc/tag/Font/" target="_blank" >Font</a>-weight: b<a href="https://m.jb51.cc/tag/ol/" target="_blank" >ol</a>d;">5</span>; #从第5开始，即先<a href="https://www.jb51.cc/tag/chaxun/" target="_blank" >查询</a>出第6条，<a href="https://m.jb51.cc/tag/ranhou/" target="_blank" >然后</a>包含这一条在内往后查5条</pre>MysqL<span >> <span >select <span >* <span >from employee limit <span >0,<span >5<span >;<span >+<span >--<span >--+-----------+------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
<span >| ID <span >| name <span >| sex <span >| age <span >| hire_date <span >| post <span >| post_comment <span >| salary <span >| office <span >| depart_ID <span >|
<span >+<span >--<span >--+-----------+------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
<span >| <span >1 <span >| egon <span >| male <span >| <span >18 <span >| <span >2017<span >-<span >03<span >-<span >01 <span >| 老男孩驻沙河办事处外交大使 <span >| <span >NulL <span >| <span >7300.33 <span >| <span >401 <span >| <span >1 <span >|
<span >| <span >2 <span >| alex <span >| male <span >| <span >78 <span >| <span >2015<span >-<span >03<span >-<span >02 <span >| teacher <span >| <span >| <span >1000000.31 <span >| <span >401 <span >| <span >1 <span >|
<span >| <span >3 <span >| wupeiqi <span >| male <span >| <span >81 <span >| <span >2013<span >-<span >03<span >-<span >05 <span >| teacher <span >| <span >NulL <span >| <span >8300.00 <span >| <span >401 <span >| <span >1 <span >|
<span >| <span >4 <span >| yuanhao <span >| male <span >| <span >73 <span >| <span >2014<span >-<span >07<span >-<span >01 <span >| teacher <span >| <span >NulL <span >| <span >3500.00 <span >| <span >401 <span >| <span >1 <span >|
<span >| <span >5 <span >| liwenzhou <span >| male <span >| <span >28 <span >| <span >2012<span >-<span >11<span >-<span >01 <span >| teacher <span >| <span >NulL <span >| <span >2100.00 <span >| <span >401 <span >| <span >1 <span >|
<span >+<span >--<span >--+-----------+------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
<span >5 rows <span >in <span >set (<span >0.00<span > sec)
MysqL<span >> <span >select <span >* <span >from employee limit <span >5,<span >5<span >;
<span >+<span >--<span >--+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+
<span >| ID <span >| name <span >| sex <span >| age <span >| hire_date <span >| post <span >| post_comment <span >| salary <span >| office <span >| depart_ID <span >|
<span >+<span >--<span >--+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+
<span >| <span >6 <span >| jingliyang <span >| female <span >| <span >18 <span >| <span >2011<span >-<span >02<span >-<span >11 <span >| teacher <span >| <span >NulL <span >| <span >9000.00 <span >| <span >401 <span >| <span >1 <span >|
<span >| <span >7 <span >| jinxin <span >| male <span >| <span >18 <span >| <span >1900<span >-<span >03<span >-<span >01 <span >| teacher <span >| <span >NulL <span >| <span >30000.00 <span >| <span >401 <span >| <span >1 <span >|
<span >| <span >8 <span >| 成龙 <span >| male <span >| <span >48 <span >| <span >2010<span >-<span >11<span >-<span >11 <span >| teacher <span >| <span >NulL <span >| <span >10000.00 <span >| <span >401 <span >| <span >1 <span >|
<span >| <span >9 <span >| 歪歪 <span >| female <span >| <span >48 <span >| <span >2015<span >-<span >03<span >-<span >11 <span >| sale <span >| <span >NulL <span >| <span >3000.13 <span >| <span >402 <span >| <span >2 <span >|
<span >| <span >10 <span >| 丫丫 <span >| female <span >| <span >38 <span >| <span >2010<span >-<span >11<span >-<span >01 <span >| sale <span >| <span >NulL <span >| <span >2000.35 <span >| <span >402 <span >| <span >2 <span >|
<span >+<span >--<span >--+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+
<span >5 rows <span >in <span >set (<span >0.00<span > sec)
MysqL<span >> <span >select <span >* <span >from employee limit <span >10,<span >5<span >;
<span >+<span >--<span >--+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
<span >| ID <span >| name <span >| sex <span >| age <span >| hire_date <span >| post <span >| post_comment <span >| salary <span >| office <span >| depart_ID <span >|
<span >+<span >--<span >--+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
<span >| <span >11 <span >| 丁丁 <span >| female <span >| <span >18 <span >| <span >2011<span >-<span >03<span >-<span >12 <span >| sale <span >| <span >NulL <span >| <span >1000.37 <span >| <span >402 <span >| <span >2 <span >|
<span >| <span >12 <span >| 星星 <span >| female <span >| <span >18 <span >| <span >2016<span >-<span >05<span >-<span >13 <span >| sale <span >| <span >NulL <span >| <span >3000.29 <span >| <span >402 <span >| <span >2 <span >|
<span >| <span >13 <span >| 格格 <span >| female <span >| <span >28 <span >| <span >2017<span >-<span >01<span >-<span >27 <span >| sale <span >| <span >NulL <span >| <span >4000.33 <span >| <span >402 <span >| <span >2 <span >|
<span >| <span >14 <span >| 张野 <span >| male <span >| <span >28 <span >| <span >2016<span >-<span >03<span >-<span >11 <span >| operation <span >| <span >NulL <span >| <span >10000.13 <span >| <span >403 <span >| <span >3 <span >|
<span >| <span >15 <span >| 程咬金 <span >| male <span >| <span >18 <span >| <span >1997<span >-<span >03<span >-<span >12 <span >| operation <span >| <span >NulL <span >| <span >20000.00 <span >| <span >403 <span >| <span >3 <span >|
<span >+<span >--<span >--+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
<span >5 rows <span >in <span >set (<span >0.00 sec)
小结：对字符串匹配的方式
<span >WHERE name <span >= <span >'<span >egon<span >'<span >;
<span >WHERE name <span >liKE <span >'<span >yua%<span >'<span >;
<span >WHERE name REGEXP <span >'<span >on$<span >';
<div onclick="cnblogs_code_show('b5f6011c-c2d4-41d7-bd56-bdc1df3d5894')">
<div ID="cnblogs_code_open_b5f6011c-c2d4-41d7-bd56-bdc1df3d5894" >
<span >select <span > <span >from employee <span >where name regexp <span >'<span >^jin.[gn]$<span >';总结
以上是内存溢出为你收集整理的单表查询全部内容，希望文章能够帮你解决单表查询所遇到的程序开发问题。
如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）