时间:2021-07-01 10:21:17 帮助过:6人阅读
插入数据 INSERT
更新数据 UPDATE
删除数据 DELETE
- 插入数据 <span style="color: #0000ff">INSERT</span>
- <span style="color: #800000; font-weight: bold">1</span><span style="color: #000000">.插入完整数据(顺序插入)
- 语法一:
- </span><span style="color: #0000ff">INSERT</span> <span style="color: #0000ff">INTO</span> 表名(字段1,字段2,字段3...字段n)<span style="color: #0000ff">VALUES</span><span style="color: #000000">(值1,值2,值3...值n);
- 语法二:
- </span><span style="color: #0000ff">INSERT</span> <span style="color: #0000ff">INTO</span> 表名 <span style="color: #0000ff">VALUES</span><span style="color: #000000"> (值1,值2,值3…值n);
- </span><span style="color: #800000; font-weight: bold">2</span><span style="color: #000000">. 指定字段插入数据
- 语法:
- </span><span style="color: #0000ff">INSERT</span> <span style="color: #0000ff">INTO</span> 表名(字段1,字段2,字段3…) <span style="color: #0000ff">VALUES</span><span style="color: #000000"> (值1,值2,值3…);
- </span><span style="color: #800000; font-weight: bold">3</span><span style="color: #000000">. 插入多条记录
- 语法:
- </span><span style="color: #0000ff">INSERT</span> <span style="color: #0000ff">INTO</span> 表名 <span style="color: #0000ff">VALUES</span><span style="color: #000000">
- (值1,值2,值3…值n),
- (值1,值2,值3…值n),
- (值1,值2,值3…值n);
- </span><span style="color: #800000; font-weight: bold">4</span><span style="color: #000000">. 插入查询结果
- 语法:
- </span><span style="color: #0000ff">INSERT</span> <span style="color: #0000ff">INTO</span><span style="color: #000000"> 表名(字段1,字段2,字段3…字段n)
- </span><span style="color: #0000ff">SELECT</span> (字段1,字段2,字段3…字段n) <span style="color: #0000ff">FROM</span><span style="color: #000000"> 表2
- </span><span style="color: #0000ff">WHERE</span><span style="color: #000000"> …;
- 三、更新数据UPDATE
- 语法:
- </span><span style="color: #0000ff">UPDATE</span> 表名 <span style="color: #0000ff">SET</span><span style="color: #000000">
- 字段1</span><span style="color: #808080">=</span><span style="color: #000000">值1,
- 字段2</span><span style="color: #808080">=</span><span style="color: #000000">值2,
- </span><span style="color: #0000ff">WHERE</span><span style="color: #000000"> CONDITION;
- 示例:
- </span><span style="color: #0000ff">UPDATE</span> mysql.<span style="color: #ff00ff">user</span> <span style="color: #0000ff">SET</span> password<span style="color: #808080">=</span>password(‘<span style="color: #800000; font-weight: bold">123</span><span style="color: #000000">’)
- </span><span style="color: #0000ff">where</span> <span style="color: #ff00ff">user</span><span style="color: #808080">=</span>’root’ <span style="color: #808080">and</span> host<span style="color: #808080">=</span><span style="color: #000000">’localhost’;
- 四、删除数据DELETE
- 语法:
- </span><span style="color: #0000ff">DELETE</span> <span style="color: #0000ff">FROM</span><span style="color: #000000"> 表名
- </span><span style="color: #0000ff">WHERE</span><span style="color: #000000"> CONITION;
- 示例:
- </span><span style="color: #0000ff">DELETE</span> <span style="color: #0000ff">FROM</span> mysql.<span style="color: #ff00ff">user</span>
- <span style="color: #0000ff">WHERE</span> pas<span style="color: #000000">sword=’’;</span>
语法:
- 一、单表查询的语法
- SELECT 字段1,字段2... FROM 表名
- WHERE 条件
- GROUP BY field
- HAVING 筛选
- ORDER BY field
- LIMIT 限制条数
- 二、关键字的执行优先级(重点)
- <strong>重点中的重点:关键字的执行优先级
- </strong>from
- where
- group by
- having
- select
- distinct
- order by
- limit
1.找到表:from
2.拿着where指定的约束条件,去文件/表中取出一条条记录
3.将取出的一条条记录进行分组group by,如果没有group by,则整体作为一组
4.将分组的结果进行having过滤
5.执行select
6.去重
7.将结果按条件排序:order by
8.限制结果的显示条数
创建公司员工表,表的字段和数据类型
- <span style="color: #000000">company.employee
- 员工id id </span><span style="color: #0000ff">int</span><span style="color: #000000">
- 姓名 name </span><span style="color: #0000ff">varchar</span><span style="color: #000000">
- 性别 sex enum
- 年龄 age </span><span style="color: #0000ff">int</span><span style="color: #000000">
- 入职日期 hire_date date
- 岗位 post </span><span style="color: #0000ff">varchar</span><span style="color: #000000">
- 职位描述 post_comment </span><span style="color: #0000ff">varchar</span><span style="color: #000000">
- 薪水 salary </span><span style="color: #0000ff">double</span><span style="color: #000000">
- 办公室 office </span><span style="color: #0000ff">int</span><span style="color: #000000">
- 部门编号 depart_id </span><span style="color: #0000ff">int</span>
创建员工表,并插入记录
- <span style="color: #000000">#创建表,设置字段的约束条件
- </span><span style="color: #0000ff">create</span> <span style="color: #0000ff">table</span><span style="color: #000000"> employee(
- id </span><span style="color: #0000ff">int</span> <span style="color: #0000ff">primary</span> <span style="color: #0000ff">key</span><span style="color: #000000"> auto_increment,
- name </span><span style="color: #0000ff">varchar</span>(<span style="color: #800000; font-weight: bold">20</span>) <span style="color: #808080">not</span> <span style="color: #0000ff">null</span><span style="color: #000000">,
- sex enum(</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">male</span><span style="color: #ff0000">‘</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">female</span><span style="color: #ff0000">‘</span>) <span style="color: #808080">not</span> <span style="color: #0000ff">null</span> <span style="color: #0000ff">default</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">male</span><span style="color: #ff0000">‘</span><span style="color: #000000">, #大部分是男的
- age </span><span style="color: #0000ff">int</span>(<span style="color: #800000; font-weight: bold">3</span>) unsigned <span style="color: #808080">not</span> <span style="color: #0000ff">null</span> <span style="color: #0000ff">default</span> <span style="color: #800000; font-weight: bold">28</span><span style="color: #000000">,
- hire_date date </span><span style="color: #808080">not</span> <span style="color: #0000ff">null</span><span style="color: #000000">,
- post </span><span style="color: #0000ff">varchar</span>(<span style="color: #800000; font-weight: bold">50</span><span style="color: #000000">),
- post_comment </span><span style="color: #0000ff">varchar</span>(<span style="color: #800000; font-weight: bold">100</span><span style="color: #000000">),
- salary </span><span style="color: #0000ff">double</span>(<span style="color: #800000; font-weight: bold">15</span>,<span style="color: #800000; font-weight: bold">2</span><span style="color: #000000">),
- office </span><span style="color: #0000ff">int</span><span style="color: #000000">,#一个部门一个屋
- depart_id </span><span style="color: #0000ff">int</span><span style="color: #000000">
- );
- # 查看表结构
- mysql</span><span style="color: #808080">></span> <span style="color: #0000ff">desc</span><span style="color: #000000"> employee;
- </span><span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">------------+-----------------------+------+-----+---------+----------------+</span>
- <span style="color: #808080">|</span> Field <span style="color: #808080">|</span> Type <span style="color: #808080">|</span> <span style="color: #0000ff">Null</span> <span style="color: #808080">|</span> <span style="color: #0000ff">Key</span> <span style="color: #808080">|</span> <span style="color: #0000ff">Default</span> <span style="color: #808080">|</span> Extra <span style="color: #808080">|</span>
- <span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">------------+-----------------------+------+-----+---------+----------------+</span>
- <span style="color: #808080">|</span> id <span style="color: #808080">|</span> <span style="color: #0000ff">int</span>(<span style="color: #800000; font-weight: bold">11</span>) <span style="color: #808080">|</span> NO <span style="color: #808080">|</span> PRI <span style="color: #808080">|</span> <span style="color: #0000ff">NULL</span> <span style="color: #808080">|</span> auto_increment <span style="color: #808080">|</span>
- <span style="color: #808080">|</span> emp_name <span style="color: #808080">|</span> <span style="color: #0000ff">varchar</span>(<span style="color: #800000; font-weight: bold">20</span>) <span style="color: #808080">|</span> NO <span style="color: #808080">|</span> <span style="color: #808080">|</span> <span style="color: #0000ff">NULL</span> <span style="color: #808080">|</span> <span style="color: #808080">|</span>
- <span style="color: #808080">|</span> sex <span style="color: #808080">|</span> enum(<span style="color: #ff0000">‘</span><span style="color: #ff0000">male</span><span style="color: #ff0000">‘</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">female</span><span style="color: #ff0000">‘</span>) <span style="color: #808080">|</span> NO <span style="color: #808080">|</span> <span style="color: #808080">|</span> male <span style="color: #808080">|</span> <span style="color: #808080">|</span>
- <span style="color: #808080">|</span> age <span style="color: #808080">|</span> <span style="color: #0000ff">int</span>(<span style="color: #800000; font-weight: bold">3</span>) unsigned <span style="color: #808080">|</span> NO <span style="color: #808080">|</span> <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">28</span> <span style="color: #808080">|</span> <span style="color: #808080">|</span>
- <span style="color: #808080">|</span> hire_date <span style="color: #808080">|</span> date <span style="color: #808080">|</span> NO <span style="color: #808080">|</span> <span style="color: #808080">|</span> <span style="color: #0000ff">NULL</span> <span style="color: #808080">|</span> <span style="color: #808080">|</span>
- <span style="color: #808080">|</span> post <span style="color: #808080">|</span> <span style="color: #0000ff">varchar</span>(<span style="color: #800000; font-weight: bold">50</span>) <span style="color: #808080">|</span> YES <span style="color: #808080">|</span> <span style="color: #808080">|</span> <span style="color: #0000ff">NULL</span> <span style="color: #808080">|</span> <span style="color: #808080">|</span>
- <span style="color: #808080">|</span> post_comment <span style="color: #808080">|</span> <span style="color: #0000ff">varchar</span>(<span style="color: #800000; font-weight: bold">100</span>) <span style="color: #808080">|</span> YES <span style="color: #808080">|</span> <span style="color: #808080">|</span> <span style="color: #0000ff">NULL</span> <span style="color: #808080">|</span> <span style="color: #808080">|</span>
- <span style="color: #808080">|</span> salart <span style="color: #808080">|</span> <span style="color: #0000ff">double</span>(<span style="color: #800000; font-weight: bold">15</span>,<span style="color: #800000; font-weight: bold">2</span>) <span style="color: #808080">|</span> YES <span style="color: #808080">|</span> <span style="color: #808080">|</span> <span style="color: #0000ff">NULL</span> <span style="color: #808080">|</span> <span style="color: #808080">|</span>
- <span style="color: #808080">|</span> office <span style="color: #808080">|</span> <span style="color: #0000ff">int</span>(<span style="color: #800000; font-weight: bold">11</span>) <span style="color: #808080">|</span> YES <span style="color: #808080">|</span> <span style="color: #808080">|</span> <span style="color: #0000ff">NULL</span> <span style="color: #808080">|</span> <span style="color: #808080">|</span>
- <span style="color: #808080">|</span> depart_id <span style="color: #808080">|</span> <span style="color: #0000ff">int</span>(<span style="color: #800000; font-weight: bold">11</span>) <span style="color: #808080">|</span> YES <span style="color: #808080">|</span> <span style="color: #808080">|</span> <span style="color: #0000ff">NULL</span> <span style="color: #808080">|</span> <span style="color: #808080">|</span>
- <span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">------------+-----------------------+------+-----+---------+----------------+</span>
- <span style="color: #800000; font-weight: bold">10</span> rows <span style="color: #808080">in</span> <span style="color: #0000ff">set</span> (<span style="color: #800000; font-weight: bold">0.08</span><span style="color: #000000"> sec)
- #插入记录
- #三个部门:教学,销售,运营
- </span><span style="color: #0000ff">insert</span> <span style="color: #0000ff">into</span> employee(name ,sex,age,hire_date,post,salary,office,depart_id) <span style="color: #0000ff">values</span><span style="color: #000000">
- (</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">egon</span><span style="color: #ff0000">‘</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">male</span><span style="color: #ff0000">‘</span>,<span style="color: #800000; font-weight: bold">18</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">20170301</span><span style="color: #ff0000">‘</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">老男孩驻沙河办事处外交大使</span><span style="color: #ff0000">‘</span>,<span style="color: #800000; font-weight: bold">7300.33</span>,<span style="color: #800000; font-weight: bold">401</span>,<span style="color: #800000; font-weight: bold">1</span><span style="color: #000000">), #以下是教学部
- (</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">alex</span><span style="color: #ff0000">‘</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">male</span><span style="color: #ff0000">‘</span>,<span style="color: #800000; font-weight: bold">78</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">20150302</span><span style="color: #ff0000">‘</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">teacher</span><span style="color: #ff0000">‘</span>,<span style="color: #800000; font-weight: bold">1000000.31</span>,<span style="color: #800000; font-weight: bold">401</span>,<span style="color: #800000; font-weight: bold">1</span><span style="color: #000000">),
- (</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">wupeiqi</span><span style="color: #ff0000">‘</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">male</span><span style="color: #ff0000">‘</span>,<span style="color: #800000; font-weight: bold">81</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">20130305</span><span style="color: #ff0000">‘</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">teacher</span><span style="color: #ff0000">‘</span>,<span style="color: #800000; font-weight: bold">8300</span>,<span style="color: #800000; font-weight: bold">401</span>,<span style="color: #800000; font-weight: bold">1</span><span style="color: #000000">),
- (</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">yuanhao</span><span style="color: #ff0000">‘</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">male</span><span style="color: #ff0000">‘</span>,<span style="color: #800000; font-weight: bold">73</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">20140701</span><span style="color: #ff0000">‘</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">teacher</span><span style="color: #ff0000">‘</span>,<span style="color: #800000; font-weight: bold">3500</span>,<span style="color: #800000; font-weight: bold">401</span>,<span style="color: #800000; font-weight: bold">1</span><span style="color: #000000">),
- (</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">liwenzhou</span><span style="color: #ff0000">‘</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">male</span><span style="color: #ff0000">‘</span>,<span style="color: #800000; font-weight: bold">28</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">20121101</span><span style="color: #ff0000">‘</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">teacher</span><span style="color: #ff0000">‘</span>,<span style="color: #800000; font-weight: bold">2100</span>,<span style="color: #800000; font-weight: bold">401</span>,<span style="color: #800000; font-weight: bold">1</span><span style="color: #000000">),
- (</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">jingliyang</span><span style="color: #ff0000">‘</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">female</span><span style="color: #ff0000">‘</span>,<span style="color: #800000; font-weight: bold">18</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">20110211</span><span style="color: #ff0000">‘</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">teacher</span><span style="color: #ff0000">‘</span>,<span style="color: #800000; font-weight: bold">9000</span>,<span style="color: #800000; font-weight: bold">401</span>,<span style="color: #800000; font-weight: bold">1</span><span style="color: #000000">),
- (</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">jinxin</span><span style="color: #ff0000">‘</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">male</span><span style="color: #ff0000">‘</span>,<span style="color: #800000; font-weight: bold">18</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">19000301</span><span style="color: #ff0000">‘</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">teacher</span><span style="color: #ff0000">‘</span>,<span style="color: #800000; font-weight: bold">30000</span>,<span style="color: #800000; font-weight: bold">401</span>,<span style="color: #800000; font-weight: bold">1</span><span style="color: #000000">),
- (</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">xiaomage</span><span style="color: #ff0000">‘</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">male</span><span style="color: #ff0000">‘</span>,<span style="color: #800000; font-weight: bold">48</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">20101111</span><span style="color: #ff0000">‘</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">teacher</span><span style="color: #ff0000">‘</span>,<span style="color: #800000; font-weight: bold">10000</span>,<span style="color: #800000; font-weight: bold">401</span>,<span style="color: #800000; font-weight: bold">1</span><span style="color: #000000">),
- (</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">歪歪</span><span style="color: #ff0000">‘</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">female</span><span style="color: #ff0000">‘</span>,<span style="color: #800000; font-weight: bold">48</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">20150311</span><span style="color: #ff0000">‘</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">sale</span><span style="color: #ff0000">‘</span>,<span style="color: #800000; font-weight: bold">3000.13</span>,<span style="color: #800000; font-weight: bold">402</span>,<span style="color: #800000; font-weight: bold">2</span><span style="color: #000000">),#以下是销售部门
- (</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">丫丫</span><span style="color: #ff0000">‘</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">female</span><span style="color: #ff0000">‘</span>,<span style="color: #800000; font-weight: bold">38</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">20101101</span><span style="color: #ff0000">‘</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">sale</span><span style="color: #ff0000">‘</span>,<span style="color: #800000; font-weight: bold">2000.35</span>,<span style="color: #800000; font-weight: bold">402</span>,<span style="color: #800000; font-weight: bold">2</span><span style="color: #000000">),
- (</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">丁丁</span><span style="color: #ff0000">‘</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">female</span><span style="color: #ff0000">‘</span>,<span style="color: #800000; font-weight: bold">18</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">20110312</span><span style="color: #ff0000">‘</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">sale</span><span style="color: #ff0000">‘</span>,<span style="color: #800000; font-weight: bold">1000.37</span>,<span style="color: #800000; font-weight: bold">402</span>,<span style="color: #800000; font-weight: bold">2</span><span style="color: #000000">),
- (</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">星星</span><span style="color: #ff0000">‘</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">female</span><span style="color: #ff0000">‘</span>,<span style="color: #800000; font-weight: bold">18</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">20160513</span><span style="color: #ff0000">‘</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">sale</span><span style="color: #ff0000">‘</span>,<span style="color: #800000; font-weight: bold">3000.29</span>,<span style="color: #800000; font-weight: bold">402</span>,<span style="color: #800000; font-weight: bold">2</span><span style="color: #000000">),
- (</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">格格</span><span style="color: #ff0000">‘</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">female</span><span style="color: #ff0000">‘</span>,<span style="color: #800000; font-weight: bold">28</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">20170127</span><span style="color: #ff0000">‘</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">sale</span><span style="color: #ff0000">‘</span>,<span style="color: #800000; font-weight: bold">4000.33</span>,<span style="color: #800000; font-weight: bold">402</span>,<span style="color: #800000; font-weight: bold">2</span><span style="color: #000000">),
- (</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">张野</span><span style="color: #ff0000">‘</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">male</span><span style="color: #ff0000">‘</span>,<span style="color: #800000; font-weight: bold">28</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">20160311</span><span style="color: #ff0000">‘</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">operation</span><span style="color: #ff0000">‘</span>,<span style="color: #800000; font-weight: bold">10000.13</span>,<span style="color: #800000; font-weight: bold">403</span>,<span style="color: #800000; font-weight: bold">3</span><span style="color: #000000">), #以下是运营部门
- (</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">程咬金</span><span style="color: #ff0000">‘</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">male</span><span style="color: #ff0000">‘</span>,<span style="color: #800000; font-weight: bold">18</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">19970312</span><span style="color: #ff0000">‘</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">operation</span><span style="color: #ff0000">‘</span>,<span style="color: #800000; font-weight: bold">20000</span>,<span style="color: #800000; font-weight: bold">403</span>,<span style="color: #800000; font-weight: bold">3</span><span style="color: #000000">),
- (</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">程咬银</span><span style="color: #ff0000">‘</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">female</span><span style="color: #ff0000">‘</span>,<span style="color: #800000; font-weight: bold">18</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">20130311</span><span style="color: #ff0000">‘</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">operation</span><span style="color: #ff0000">‘</span>,<span style="color: #800000; font-weight: bold">19000</span>,<span style="color: #800000; font-weight: bold">403</span>,<span style="color: #800000; font-weight: bold">3</span><span style="color: #000000">),
- (</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">程咬铜</span><span style="color: #ff0000">‘</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">male</span><span style="color: #ff0000">‘</span>,<span style="color: #800000; font-weight: bold">18</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">20150411</span><span style="color: #ff0000">‘</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">operation</span><span style="color: #ff0000">‘</span>,<span style="color: #800000; font-weight: bold">18000</span>,<span style="color: #800000; font-weight: bold">403</span>,<span style="color: #800000; font-weight: bold">3</span><span style="color: #000000">),
- (</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">程咬铁</span><span style="color: #ff0000">‘</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">female</span><span style="color: #ff0000">‘</span>,<span style="color: #800000; font-weight: bold">18</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">20140512</span><span style="color: #ff0000">‘</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">operation</span><span style="color: #ff0000">‘</span>,<span style="color: #800000; font-weight: bold">17000</span>,<span style="color: #800000; font-weight: bold">403</span>,<span style="color: #800000; font-weight: bold">3</span><span style="color: #000000">)
- ;</span>
(1)where 约束
- <span style="color: #000000">where子句中可以使用
- </span><span style="color: #800000; font-weight: bold">1</span>.比较运算符:<span style="color: #808080">></span>、<span style="color: #808080"><</span>、<span style="color: #808080">>=</span>、<span style="color: #808080"><=</span>、<span style="color: #808080"><></span>、<span style="color: #808080">!=</span>
- <span style="color: #800000; font-weight: bold">2</span>.<span style="color: #808080">between</span> <span style="color: #800000; font-weight: bold">80</span> <span style="color: #808080">and</span> <span style="color: #800000; font-weight: bold">100</span><span style="color: #000000"> :值在80到100之间
- </span><span style="color: #800000; font-weight: bold">3</span>.<span style="color: #808080">in</span>(<span style="color: #800000; font-weight: bold">80</span>,<span style="color: #800000; font-weight: bold">90</span>,<span style="color: #800000; font-weight: bold">100</span><span style="color: #000000">)值是10或20或30
- </span><span style="color: #800000; font-weight: bold">4</span>.<span style="color: #808080">like</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">xiaomagepattern</span><span style="color: #ff0000">‘</span>: pattern可以是<span style="color: #808080">%</span>或者_。<span style="color: #808080">%</span><span style="color: #000000">小时任意多字符,_表示一个字符
- </span><span style="color: #800000; font-weight: bold">5</span>.逻辑运算符:在多个条件直接可以使用逻辑运算符 <span style="color: #808080">and</span> <span style="color: #808080">or</span> <span style="color: #808080">not</span>
验证结果:
where约束
- #<span style="color: #800000; font-weight: bold">1</span><span style="color: #000000"> :单条件查询
- mysql</span><span style="color: #808080">></span> <span style="color: #0000ff">select</span> id,emp_name <span style="color: #0000ff">from</span> employee <span style="color: #0000ff">where</span> id <span style="color: #808080">></span> <span style="color: #800000; font-weight: bold">5</span><span style="color: #000000">;
- </span><span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">--+------------+</span>
- <span style="color: #808080">|</span> id <span style="color: #808080">|</span> emp_name <span style="color: #808080">|</span>
- <span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">--+------------+</span>
- <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">6</span> <span style="color: #808080">|</span> jingliyang <span style="color: #808080">|</span>
- <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">7</span> <span style="color: #808080">|</span> jinxin <span style="color: #808080">|</span>
- <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">8</span> <span style="color: #808080">|</span> xiaomage <span style="color: #808080">|</span>
- <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">9</span> <span style="color: #808080">|</span> 歪歪 <span style="color: #808080">|</span>
- <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">10</span> <span style="color: #808080">|</span> 丫丫 <span style="color: #808080">|</span>
- <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">11</span> <span style="color: #808080">|</span> 丁丁 <span style="color: #808080">|</span>
- <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">12</span> <span style="color: #808080">|</span> 星星 <span style="color: #808080">|</span>
- <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">13</span> <span style="color: #808080">|</span> 格格 <span style="color: #808080">|</span>
- <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">14</span> <span style="color: #808080">|</span> 张野 <span style="color: #808080">|</span>
- <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">15</span> <span style="color: #808080">|</span> 程咬金 <span style="color: #808080">|</span>
- <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">16</span> <span style="color: #808080">|</span> 程咬银 <span style="color: #808080">|</span>
- <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">17</span> <span style="color: #808080">|</span> 程咬铜 <span style="color: #808080">|</span>
- <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">18</span> <span style="color: #808080">|</span> 程咬铁 <span style="color: #808080">|</span><span style="color: #000000">
- #</span><span style="color: #800000; font-weight: bold">2</span><span style="color: #000000"> 多条件查询
- mysql</span><span style="color: #808080">></span> <span style="color: #0000ff">select</span> emp_name <span style="color: #0000ff">from</span> employee <span style="color: #0000ff">where</span> post<span style="color: #808080">=</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">teacher</span><span style="color: #ff0000">‘</span> <span style="color: #808080">and</span> salary<span style="color: #808080">></span><span style="color: #800000; font-weight: bold">10000</span><span style="color: #000000">;
- </span><span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">--------+</span>
- <span style="color: #808080">|</span> emp_name <span style="color: #808080">|</span>
- <span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">--------+</span>
- <span style="color: #808080">|</span> alex <span style="color: #808080">|</span>
- <span style="color: #808080">|</span> jinxin <span style="color: #808080">|</span>
- <span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">--------+</span>
- <span style="color: #000000">
- #</span><span style="color: #800000; font-weight: bold">3</span>.关键字BETWEEN <span style="color: #808080">AND</span>
- <span style="color: #0000ff">SELECT</span> name,salary <span style="color: #0000ff">FROM</span><span style="color: #000000"> employee
- </span><span style="color: #0000ff">WHERE</span> salary <span style="color: #808080">BETWEEN</span> <span style="color: #800000; font-weight: bold">10000</span> <span style="color: #808080">AND</span> <span style="color: #800000; font-weight: bold">20000</span><span style="color: #000000">;
- </span><span style="color: #0000ff">SELECT</span> name,salary <span style="color: #0000ff">FROM</span><span style="color: #000000"> employee
- </span><span style="color: #0000ff">WHERE</span> salary <span style="color: #808080">NOT</span> <span style="color: #808080">BETWEEN</span> <span style="color: #800000; font-weight: bold">10000</span> <span style="color: #808080">AND</span> <span style="color: #800000; font-weight: bold">20000</span><span style="color: #000000">;
- #注意</span><span style="color: #ff0000">‘‘</span><span style="color: #000000">是空字符串,不是null
- </span><span style="color: #0000ff">SELECT</span> name,post_comment <span style="color: #0000ff">FROM</span> employee <span style="color: #0000ff">WHERE</span> post_comment<span style="color: #808080">=</span><span style="color: #ff0000">‘‘</span><span style="color: #000000">;
- ps:
- 执行
- </span><span style="color: #0000ff">update</span> employee <span style="color: #0000ff">set</span> post_comment<span style="color: #808080">=</span><span style="color: #ff0000">‘‘</span> <span style="color: #0000ff">where</span> id<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">2</span><span style="color: #000000">;
- 再用上条查看,就会有结果了
- #</span><span style="color: #800000; font-weight: bold">5</span><span style="color: #000000">:关键字IN集合查询
- mysql</span><span style="color: #808080">></span> <span style="color: #0000ff">SELECT</span> name,salary <span style="color: #0000ff">FROM</span> employee <span style="color: #0000ff">WHERE</span> salary<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">3000</span> <span style="color: #808080">OR</span> salary<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">3500</span> <span style="color: #808080">OR</span> salary<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">4000</span> <span style="color: #808080">OR</span> salary<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">9000</span><span style="color: #000000"> ;
- </span><span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">----------+---------+</span>
- <span style="color: #808080">|</span> name <span style="color: #808080">|</span> salary <span style="color: #808080">|</span>
- <span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">----------+---------+</span>
- <span style="color: #808080">|</span> yuanhao <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">3500.00</span> <span style="color: #808080">|</span>
- <span style="color: #808080">|</span> jingliyang <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">9000.00</span> <span style="color: #808080">|</span>
- <span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">----------+---------+</span>
- <span style="color: #800000; font-weight: bold">2</span> rows <span style="color: #808080">in</span> <span style="color: #0000ff">set</span> (<span style="color: #800000; font-weight: bold">0.00</span><span style="color: #000000"> sec)
- mysql</span><span style="color: #808080">></span> <span style="color: #0000ff">SELECT</span> name,salary <span style="color: #0000ff">FROM</span> employee <span style="color: #0000ff">WHERE</span> salary <span style="color: #808080">IN</span> (<span style="color: #800000; font-weight: bold">3000</span>,<span style="color: #800000; font-weight: bold">3500</span>,<span style="color: #800000; font-weight: bold">4000</span>,<span style="color: #800000; font-weight: bold">9000</span><span style="color: #000000">) ;
- </span><span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">----------+---------+</span>
- <span style="color: #808080">|</span> name <span style="color: #808080">|</span> salary <span style="color: #808080">|</span>
- <span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">----------+---------+</span>
- <span style="color: #808080">|</span> yuanhao <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">3500.00</span> <span style="color: #808080">|</span>
- <span style="color: #808080">|</span> jingliyang <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">9000.00</span> <span style="color: #808080">|</span>
- <span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">----------+---------+</span>
- mysql<span style="color: #808080">></span> <span style="color: #0000ff">SELECT</span> name,salary <span style="color: #0000ff">FROM</span> employee <span style="color: #0000ff">WHERE</span> salary <span style="color: #808080">NOT</span> <span style="color: #808080">IN</span> (<span style="color: #800000; font-weight: bold">3000</span>,<span style="color: #800000; font-weight: bold">3500</span>,<span style="color: #800000; font-weight: bold">4000</span>,<span style="color: #800000; font-weight: bold">9000</span><span style="color: #000000">) ;
- </span><span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">---------+------------+</span>
- <span style="color: #808080">|</span> name <span style="color: #808080">|</span> salary <span style="color: #808080">|</span>
- <span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">---------+------------+</span>
- <span style="color: #808080">|</span> egon <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">7300.33</span> <span style="color: #808080">|</span>
- <span style="color: #808080">|</span> alex <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">1000000.31</span> <span style="color: #808080">|</span>
- <span style="color: #808080">|</span> wupeiqi <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">8300.00</span> <span style="color: #808080">|</span>
- <span style="color: #808080">|</span> liwenzhou <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">2100.00</span> <span style="color: #808080">|</span>
- <span style="color: #808080">|</span> jinxin <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">30000.00</span> <span style="color: #808080">|</span>
- <span style="color: #808080">|</span> xiaomage <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">10000.00</span> <span style="color: #808080">|</span>
- <span style="color: #808080">|</span> 歪歪 <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">3000.13</span> <span style="color: #808080">|</span>
- <span style="color: #808080">|</span> 丫丫 <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">2000.35</span> <span style="color: #808080">|</span>
- <span style="color: #808080">|</span> 丁丁 <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">1000.37</span> <span style="color: #808080">|</span>
- <span style="color: #808080">|</span> 星星 <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">3000.29</span> <span style="color: #808080">|</span>
- <span style="color: #808080">|</span> 格格 <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">4000.33</span> <span style="color: #808080">|</span>
- <span style="color: #808080">|</span> 张野 <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">10000.13</span> <span style="color: #808080">|</span>
- <span style="color: #808080">|</span> 程咬金 <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">20000.00</span> <span style="color: #808080">|</span>
- <span style="color: #808080">|</span> 程咬银 <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">19000.00</span> <span style="color: #808080">|</span>
- <span style="color: #808080">|</span> 程咬铜 <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">18000.00</span> <span style="color: #808080">|</span>
- <span style="color: #808080">|</span> 程咬铁 <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">17000.00</span> <span style="color: #808080">|</span>
- <span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">---------+------------+</span>
- <span style="color: #800000; font-weight: bold">16</span> rows <span style="color: #808080">in</span> <span style="color: #0000ff">set</span> (<span style="color: #800000; font-weight: bold">0.00</span><span style="color: #000000"> sec)
- #</span><span style="color: #800000; font-weight: bold">6</span><span style="color: #000000">:关键字LIKE模糊查询
- 通配符’</span><span style="color: #808080">%</span><span style="color: #000000">’
- mysql</span><span style="color: #808080">></span> <span style="color: #0000ff">SELECT</span> <span style="color: #808080">*</span> <span style="color: #0000ff">FROM</span> employee <span style="color: #0000ff">WHERE</span> name <span style="color: #808080">LIKE</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">jin%</span><span style="color: #ff0000">‘</span><span style="color: #000000">;
- </span><span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">--+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+</span>
- <span style="color: #808080">|</span> id <span style="color: #808080">|</span> name <span style="color: #808080">|</span> sex <span style="color: #808080">|</span> age <span style="color: #808080">|</span> hire_date <span style="color: #808080">|</span> post <span style="color: #808080">|</span> post_comment <span style="color: #808080">|</span> salary <span style="color: #808080">|</span> office <span style="color: #808080">|</span> depart_id <span style="color: #808080">|</span>
- <span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">--+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+</span>
- <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">6</span> <span style="color: #808080">|</span> jingliyang <span style="color: #808080">|</span> female <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">18</span> <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">2011</span><span style="color: #808080">-</span><span style="color: #800000; font-weight: bold">02</span><span style="color: #808080">-</span><span style="color: #800000; font-weight: bold">11</span> <span style="color: #808080">|</span> teacher <span style="color: #808080">|</span> <span style="color: #0000ff">NULL</span> <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">9000.00</span> <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">401</span> <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">1</span> <span style="color: #808080">|</span>
- <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">7</span> <span style="color: #808080">|</span> jinxin <span style="color: #808080">|</span> male <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">18</span> <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">1900</span><span style="color: #808080">-</span><span style="color: #800000; font-weight: bold">03</span><span style="color: #808080">-</span><span style="color: #800000; font-weight: bold">01</span> <span style="color: #808080">|</span> teacher <span style="color: #808080">|</span> <span style="color: #0000ff">NULL</span> <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">30000.00</span> <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">401</span> <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">1</span> <span style="color: #808080">|</span>
- <span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">--+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+</span>
- <span style="color: #800000; font-weight: bold">2</span> rows <span style="color: #808080">in</span> <span style="color: #0000ff">set</span> (<span style="color: #800000; font-weight: bold">0.00</span><span style="color: #000000"> sec)
- 通配符</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">_</span><span style="color: #ff0000">‘</span><span style="color: #000000">
- mysql</span><span style="color: #808080">></span> <span style="color: #0000ff">SELECT</span> age <span style="color: #0000ff">FROM</span> employee <span style="color: #0000ff">WHERE</span> name <span style="color: #808080">LIKE</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">ale_</span><span style="color: #ff0000">‘</span><span style="color: #000000">;
- </span><span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">---+</span>
- <span style="color: #808080">|</span> age <span style="color: #808080">|</span>
- <span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">---+</span>
- <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">78</span> <span style="color: #808080">|</span>
- <span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">---+</span>
- <span style="color: #800000; font-weight: bold">1</span> row <span style="color: #808080">in</span> <span style="color: #0000ff">set</span> (<span style="color: #800000; font-weight: bold">0.00</span><span style="color: #000000"> sec)
- 练习:
- </span><span style="color: #800000; font-weight: bold">1</span><span style="color: #000000">. 查看岗位是teacher的员工姓名、年龄
- </span><span style="color: #800000; font-weight: bold">2</span><span style="color: #000000">. 查看岗位是teacher且年龄大于30岁的员工姓名、年龄
- </span><span style="color: #800000; font-weight: bold">3</span>. 查看岗位是teacher且薪资在9000<span style="color: #808080">-</span><span style="color: #000000">1000范围内的员工姓名、年龄、薪资
- </span><span style="color: #800000; font-weight: bold">4</span><span style="color: #000000">. 查看岗位描述不为NULL的员工信息
- </span><span style="color: #800000; font-weight: bold">5</span><span style="color: #000000">. 查看岗位是teacher且薪资是10000或9000或30000的员工姓名、年龄、薪资
- </span><span style="color: #800000; font-weight: bold">6</span><span style="color: #000000">. 查看岗位是teacher且薪资不是10000或9000或30000的员工姓名、年龄、薪资
- </span><span style="color: #800000; font-weight: bold">7</span><span style="color: #000000">. 查看岗位是teacher且名字是jin开头的员工姓名、年薪
- #对应的sql语句
- </span><span style="color: #0000ff">select</span> name,age <span style="color: #0000ff">from</span> employee <span style="color: #0000ff">where</span> post <span style="color: #808080">=</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">teacher</span><span style="color: #ff0000">‘</span><span style="color: #000000">;
- </span><span style="color: #0000ff">select</span> name,age <span style="color: #0000ff">from</span> employee <span style="color: #0000ff">where</span> post<span style="color: #808080">=</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">teacher</span><span style="color: #ff0000">‘</span> <span style="color: #808080">and</span> age <span style="color: #808080">></span> <span style="color: #800000; font-weight: bold">30</span><span style="color: #000000">;
- </span><span style="color: #0000ff">select</span> name,age,salary <span style="color: #0000ff">from</span> employee <span style="color: #0000ff">where</span> post<span style="color: #808080">=</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">teacher</span><span style="color: #ff0000">‘</span> <span style="color: #808080">and</span> salary <span style="color: #808080">between</span> <span style="color: #800000; font-weight: bold">9000</span> <span style="color: #808080">and</span> <span style="color: #800000; font-weight: bold">10000</span><span style="color: #000000">;
- </span><span style="color: #0000ff">select</span> <span style="color: #808080">*</span> <span style="color: #0000ff">from</span> employee <span style="color: #0000ff">where</span> post_comment <span style="color: #0000ff">is</span> <span style="color: #808080">not</span> <span style="color: #0000ff">null</span><span style="color: #000000">;
- </span><span style="color: #0000ff">select</span> name,age,salary <span style="color: #0000ff">from</span> employee <span style="color: #0000ff">where</span> post<span style="color: #808080">=</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">teacher</span><span style="color: #ff0000">‘</span> <span style="color: #808080">and</span> salary <span style="color: #808080">in</span> (<span style="color: #800000; font-weight: bold">10000</span>,<span style="color: #800000; font-weight: bold">9000</span>,<span style="color: #800000; font-weight: bold">30000</span><span style="color: #000000">);
- </span><span style="color: #0000ff">select</span> name,age,salary <span style="color: #0000ff">from</span> employee <span style="color: #0000ff">where</span> post<span style="color: #808080">=</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">teacher</span><span style="color: #ff0000">‘</span> <span style="color: #808080">and</span> salary <span style="color: #808080">not</span> <span style="color: #808080">in</span> (<span style="color: #800000; font-weight: bold">10000</span>,<span style="color: #800000; font-weight: bold">9000</span>,<span style="color: #800000; font-weight: bold">30000</span><span style="color: #000000">);
- </span><span style="color: #0000ff">select</span> name,salary<span style="color: #808080">*</span><span style="color: #800000; font-weight: bold">12</span> <span style="color: #0000ff">from</span> employee <span style="color: #0000ff">where</span> post<span style="color: #808080">=</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">teacher</span><span style="color: #ff0000">‘</span> <span style="color: #808080">and</span> name <span style="color: #808080">like</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">jin%</span><span style="color: #ff0000">‘</span>;
(2)group by 分组查询
- #1、首先明确一点:分组发生在where之后,即分组是基于where之后得到的记录而进行的
- #2、分组指的是:将所有记录按照某个相同字段进行归类,比如针对员工信息表的职位分组,或者按照性别进行分组等
- #3、为何要分组呢?
- 取每个部门的最高工资
- 取每个部门的员工数
- 取男人数和女人数
- 小窍门:‘每’这个字后面的字段,就是我们分组的依据
- #4、大前提:
- 可以按照任意字段分组,但是分组完毕后,比如group by post,只能查看post字段,如果想查看组内信息,需要借助于聚合函数
当执行以下sql语句的时候,是以post字段查询了组中的第一条数据,没有任何意义,因为我们现在想查出当前组的多条记录。
- mysql> select * from employee 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 | | 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,于是也可以有结果,默认都是组内的第一条记录,但其实这是没有意义的
- 如果想分组,则必须要设置全局的sql的模式为ONLY_FULL_GROUP_BY
- mysql> set global sql_mode=‘ONLY_FULL_GROUP_BY‘;
- Query OK, 0 rows affected (0.00 sec)
- #查看MySQL 5.7默认的sql_mode如下:
- mysql> select @@global.sql_mode;
- +--------------------+
- | @@global.sql_mode |
- +--------------------+
- | ONLY_FULL_GROUP_BY |
- +--------------------+
- 1 row in set (0.00 sec)
- mysql> exit;#设置成功后,一定要退出,然后重新登录方可生效
- Bye
继续验证通过group by分组之后,只能查看当前字段,如果想查看组内信息,需要借助于聚合函数
- mysql> select * from emp group by post;# 报错
- ERROR 1054 (42S22): Unknown column ‘post‘ in ‘group statement‘
- mysql> select post from employee group by post;
- +-----------------------------------------+
- | post |
- +-----------------------------------------+
- | operation |
- | sale |
- | teacher |
- | 老男孩驻沙河办事处外交大使 |
- +-----------------------------------------+
- 4 rows in set (0.00 sec)
(3)聚合函数
- max()求最大值
- min()求最小值
- avg()求平均值
- sum() 求和
- count() 求总个数
- #强调:聚合函数聚合的是组的内容,若是没有分组,则默认一组
- # 每个部门有多少个员工
- select post,count(id) from employee group by post;
- # 每个部门的最高薪水
- select post,max(salary) from employee group by post;
- # 每个部门的最低薪水
- select post,min(salary) from employee group by post;
- # 每个部门的平均薪水
- select post,avg(salary) from employee group by post;
- # 每个部门的所有薪水
- select post,sum(age) from employee group by post;
(4)HAVING过滤
- HAVING与WHERE不一样的地方在于
- #!!!执行优先级从高到低:where > group by > having
- #1. Where 发生在分组group by之前,因而Where中可以有任意字段,但是绝对不能使用聚合函数。
- #2. Having发生在分组group by之后,因而Having中可以使用分组的字段,无法直接取到其他字段,可以使用聚合函数
验证:
- <span style="color: #000000">验证:
- mysql</span><span style="color: #808080">></span> <span style="color: #0000ff">select</span> <span style="color: #808080">*</span> <span style="color: #0000ff">from</span> employee <span style="color: #0000ff">where</span> salary<span style="color: #808080">></span><span style="color: #800000; font-weight: bold">1000000</span><span style="color: #000000">;
- </span><span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">--+------+------+-----+------------+---------+--------------+------------+--------+-----------+</span>
- <span style="color: #808080">|</span> id <span style="color: #808080">|</span> name <span style="color: #808080">|</span> sex <span style="color: #808080">|</span> age <span style="color: #808080">|</span> hire_date <span style="color: #808080">|</span> post <span style="color: #808080">|</span> post_comment <span style="color: #808080">|</span> salary <span style="color: #808080">|</span> office <span style="color: #808080">|</span> depart_id <span style="color: #808080">|</span>
- <span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">--+------+------+-----+------------+---------+--------------+------------+--------+-----------+</span>
- <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">2</span> <span style="color: #808080">|</span> alex <span style="color: #808080">|</span> male <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">78</span> <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">2015</span><span style="color: #808080">-</span><span style="color: #800000; font-weight: bold">03</span><span style="color: #808080">-</span><span style="color: #800000; font-weight: bold">02</span> <span style="color: #808080">|</span> teacher <span style="color: #808080">|</span> <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">1000000.31</span> <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">401</span> <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">1</span> <span style="color: #808080">|</span>
- <span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">--+------+------+-----+------------+---------+--------------+------------+--------+-----------+</span>
- <span style="color: #800000; font-weight: bold">1</span> row <span style="color: #808080">in</span> <span style="color: #0000ff">set</span> (<span style="color: #800000; font-weight: bold">0.00</span><span style="color: #000000"> sec)
- mysql</span><span style="color: #808080">></span> <span style="color: #0000ff">select</span> <span style="color: #808080">*</span> <span style="color: #0000ff">from</span> employee <span style="color: #0000ff">having</span> salary<span style="color: #808080">></span><span style="color: #800000; font-weight: bold">1000000</span><span style="color: #000000">;
- ERROR </span><span style="color: #800000; font-weight: bold">1463</span> (<span style="color: #800000; font-weight: bold">42000</span>): Non<span style="color: #808080">-</span><span style="color: #ff00ff">grouping</span> field <span style="color: #ff0000">‘</span><span style="color: #ff0000">salary</span><span style="color: #ff0000">‘</span> <span style="color: #0000ff">is</span> used <span style="color: #808080">in</span> <span style="color: #0000ff">HAVING</span><span style="color: #000000"> clause
- # 必须使用group by才能使用group_concat()函数,将所有的name值连接
- mysql</span><span style="color: #808080">></span> <span style="color: #0000ff">select</span> post,group_concat(name) <span style="color: #0000ff">from</span> emp <span style="color: #0000ff">group</span> <span style="color: #0000ff">by</span> post <span style="color: #0000ff">having</span> salary <span style="color: #808080">></span> <span style="color: # </div>
- <div class=" "="">
- <ul class="m-news-opt fix">
- <li class="opt-item">
- <a href="/sql_question-407642.html" target="_blank"><p>< 上一篇</p><p class="ellipsis">安装mysql 8 及 修复登录错误日志</p></a>
- </li>
- <li class="opt-item ta-r">
- <a href="/sql_question-407644.html" target="_blank"><p>下一篇 ></p><p class="ellipsis">MySQL 数据的增删改查</p></a>
- </li>
- </ul>
- </span>