当前位置:Gxlcms > 数据库问题 > 数据库的增删改、单表查询

数据库的增删改、单表查询

时间:2021-07-01 10:21:17 帮助过:6人阅读

  插入数据 INSERT

  更新数据 UPDATE

  删除数据 DELETE

  1. 插入数据 <span style="color: #0000ff">INSERT</span>
  2. <span style="color: #800000; font-weight: bold">1</span><span style="color: #000000">.插入完整数据(顺序插入)
  3. 语法一:
  4. </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);
  5. 语法二:
  6. </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);
  7. </span><span style="color: #800000; font-weight: bold">2</span><span style="color: #000000">. 指定字段插入数据
  8. 语法:
  9. </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…);
  10. </span><span style="color: #800000; font-weight: bold">3</span><span style="color: #000000">. 插入多条记录
  11. 语法:
  12. </span><span style="color: #0000ff">INSERT</span> <span style="color: #0000ff">INTO</span> 表名 <span style="color: #0000ff">VALUES</span><span style="color: #000000">
  13. (值1,值2,值3…值n),
  14. (值1,值2,值3…值n),
  15. (值1,值2,值3…值n);
  16. </span><span style="color: #800000; font-weight: bold">4</span><span style="color: #000000">. 插入查询结果
  17. 语法:
  18. </span><span style="color: #0000ff">INSERT</span> <span style="color: #0000ff">INTO</span><span style="color: #000000"> 表名(字段1,字段2,字段3…字段n)
  19. </span><span style="color: #0000ff">SELECT</span> (字段1,字段2,字段3…字段n) <span style="color: #0000ff">FROM</span><span style="color: #000000"> 表2
  20. </span><span style="color: #0000ff">WHERE</span><span style="color: #000000"> …;
  21. 三、更新数据UPDATE
  22. 语法:
  23. </span><span style="color: #0000ff">UPDATE</span> 表名 <span style="color: #0000ff">SET</span><span style="color: #000000">
  24. 字段1</span><span style="color: #808080">=</span><span style="color: #000000">值1,
  25. 字段2</span><span style="color: #808080">=</span><span style="color: #000000">值2,
  26. </span><span style="color: #0000ff">WHERE</span><span style="color: #000000"> CONDITION;
  27. 示例:
  28. </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">’)
  29. </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’;
  30. 四、删除数据DELETE
  31. 语法:
  32. </span><span style="color: #0000ff">DELETE</span> <span style="color: #0000ff">FROM</span><span style="color: #000000"> 表名
  33. </span><span style="color: #0000ff">WHERE</span><span style="color: #000000"> CONITION;
  34. 示例:
  35. </span><span style="color: #0000ff">DELETE</span> <span style="color: #0000ff">FROM</span> mysql.<span style="color: #ff00ff">user</span>
  36. <span style="color: #0000ff">WHERE</span> pas<span style="color: #000000">sword=’’;</span>

 

 

   单表查询                                

 

语法:

  1. 一、单表查询的语法
  2. SELECT 字段1,字段2... FROM 表名
  3. WHERE 条件
  4. GROUP BY field
  5. HAVING 筛选
  6. ORDER BY field
  7. LIMIT 限制条数
  8. 二、关键字的执行优先级(重点)
  9. <strong>重点中的重点:关键字的执行优先级
  10. </strong>from
  11. where
  12. group by
  13. having
  14. select
  15. distinct
  16. order by
  17. limit

1.找到表:from

2.拿着where指定的约束条件,去文件/表中取出一条条记录

3.将取出的一条条记录进行分组group by,如果没有group by,则整体作为一组

4.将分组的结果进行having过滤

5.执行select

6.去重

7.将结果按条件排序:order by

8.限制结果的显示条数

 

创建公司员工表,表的字段和数据类型

  1. <span style="color: #000000">company.employee
  2. 员工id id </span><span style="color: #0000ff">int</span><span style="color: #000000">
  3. 姓名 name </span><span style="color: #0000ff">varchar</span><span style="color: #000000">
  4. 性别 sex enum
  5. 年龄 age </span><span style="color: #0000ff">int</span><span style="color: #000000">
  6. 入职日期 hire_date date
  7. 岗位 post </span><span style="color: #0000ff">varchar</span><span style="color: #000000">
  8. 职位描述 post_comment     </span><span style="color: #0000ff">varchar</span><span style="color: #000000">
  9. 薪水 salary     </span><span style="color: #0000ff">double</span><span style="color: #000000">
  10. 办公室 office </span><span style="color: #0000ff">int</span><span style="color: #000000">
  11. 部门编号 depart_id </span><span style="color: #0000ff">int</span>

 

技术分享图片
  1. <span style="color: #000000">#创建表,设置字段的约束条件
  2. </span><span style="color: #0000ff">create</span> <span style="color: #0000ff">table</span><span style="color: #000000"> employee(
  3. 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,
  4. 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">,
  5. 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">, #大部分是男的
  6. 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">,
  7. hire_date date </span><span style="color: #808080">not</span> <span style="color: #0000ff">null</span><span style="color: #000000">,
  8. post </span><span style="color: #0000ff">varchar</span>(<span style="color: #800000; font-weight: bold">50</span><span style="color: #000000">),
  9. post_comment </span><span style="color: #0000ff">varchar</span>(<span style="color: #800000; font-weight: bold">100</span><span style="color: #000000">),
  10. 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">),
  11. office </span><span style="color: #0000ff">int</span><span style="color: #000000">,#一个部门一个屋
  12. depart_id </span><span style="color: #0000ff">int</span><span style="color: #000000">
  13. );
  14. # 查看表结构
  15. mysql</span><span style="color: #808080">></span> <span style="color: #0000ff">desc</span><span style="color: #000000"> employee;
  16. </span><span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">------------+-----------------------+------+-----+---------+----------------+</span>
  17. <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>
  18. <span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">------------+-----------------------+------+-----+---------+----------------+</span>
  19. <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>
  20. <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>
  21. <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>
  22. <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>
  23. <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>
  24. <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>
  25. <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>
  26. <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>
  27. <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>
  28. <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>
  29. <span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">------------+-----------------------+------+-----+---------+----------------+</span>
  30. <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)
  31. #插入记录
  32. #三个部门:教学,销售,运营
  33. </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">
  34. (</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">), #以下是教学部
  35. (</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">),
  36. (</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">),
  37. (</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">),
  38. (</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">),
  39. (</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">),
  40. (</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">),
  41. (</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">),
  42. (</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">),#以下是销售部门
  43. (</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">),
  44. (</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">),
  45. (</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">),
  46. (</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">),
  47. (</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">), #以下是运营部门
  48. (</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">),
  49. (</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">),
  50. (</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">),
  51. (</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">)
  52. ;</span>
创建员工表,并插入记录

 

 

(1)where 约束 

  1. <span style="color: #000000">where子句中可以使用
  2. </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>
  3. <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之间
  4. </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
  5. </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">小时任意多字符,_表示一个字符
  6. </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>

 

 

验证结果:

技术分享图片
  1. #<span style="color: #800000; font-weight: bold">1</span><span style="color: #000000"> :单条件查询
  2. 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">;
  3. </span><span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">--+------------+</span>
  4. <span style="color: #808080">|</span> id <span style="color: #808080">|</span> emp_name <span style="color: #808080">|</span>
  5. <span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">--+------------+</span>
  6. <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>
  7. <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>
  8. <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>
  9. <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">9</span> <span style="color: #808080">|</span> 歪歪 <span style="color: #808080">|</span>
  10. <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">10</span> <span style="color: #808080">|</span> 丫丫 <span style="color: #808080">|</span>
  11. <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">11</span> <span style="color: #808080">|</span> 丁丁 <span style="color: #808080">|</span>
  12. <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">12</span> <span style="color: #808080">|</span> 星星 <span style="color: #808080">|</span>
  13. <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">13</span> <span style="color: #808080">|</span> 格格 <span style="color: #808080">|</span>
  14. <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">14</span> <span style="color: #808080">|</span> 张野 <span style="color: #808080">|</span>
  15. <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">15</span> <span style="color: #808080">|</span> 程咬金 <span style="color: #808080">|</span>
  16. <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">16</span> <span style="color: #808080">|</span> 程咬银 <span style="color: #808080">|</span>
  17. <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">17</span> <span style="color: #808080">|</span> 程咬铜 <span style="color: #808080">|</span>
  18. <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">
  19. #</span><span style="color: #800000; font-weight: bold">2</span><span style="color: #000000"> 多条件查询
  20. 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">;
  21. </span><span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">--------+</span>
  22. <span style="color: #808080">|</span> emp_name <span style="color: #808080">|</span>
  23. <span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">--------+</span>
  24. <span style="color: #808080">|</span> alex <span style="color: #808080">|</span>
  25. <span style="color: #808080">|</span> jinxin <span style="color: #808080">|</span>
  26. <span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">--------+</span>
  27. <span style="color: #000000">
  28. #</span><span style="color: #800000; font-weight: bold">3</span>.关键字BETWEEN <span style="color: #808080">AND</span>
  29. <span style="color: #0000ff">SELECT</span> name,salary <span style="color: #0000ff">FROM</span><span style="color: #000000"> employee
  30. </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">;
  31. </span><span style="color: #0000ff">SELECT</span> name,salary <span style="color: #0000ff">FROM</span><span style="color: #000000"> employee
  32. </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">;
  33. #注意</span><span style="color: #ff0000">‘‘</span><span style="color: #000000">是空字符串,不是null
  34. </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">;
  35. ps:
  36. 执行
  37. </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">;
  38. 再用上条查看,就会有结果了
  39. #</span><span style="color: #800000; font-weight: bold">5</span><span style="color: #000000">:关键字IN集合查询
  40. 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"> ;
  41. </span><span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">----------+---------+</span>
  42. <span style="color: #808080">|</span> name <span style="color: #808080">|</span> salary <span style="color: #808080">|</span>
  43. <span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">----------+---------+</span>
  44. <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>
  45. <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>
  46. <span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">----------+---------+</span>
  47. <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)
  48. 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">) ;
  49. </span><span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">----------+---------+</span>
  50. <span style="color: #808080">|</span> name <span style="color: #808080">|</span> salary <span style="color: #808080">|</span>
  51. <span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">----------+---------+</span>
  52. <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>
  53. <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>
  54. <span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">----------+---------+</span>
  55. 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">) ;
  56. </span><span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">---------+------------+</span>
  57. <span style="color: #808080">|</span> name <span style="color: #808080">|</span> salary <span style="color: #808080">|</span>
  58. <span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">---------+------------+</span>
  59. <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>
  60. <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>
  61. <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>
  62. <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>
  63. <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>
  64. <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>
  65. <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>
  66. <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>
  67. <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>
  68. <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>
  69. <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>
  70. <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>
  71. <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>
  72. <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>
  73. <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>
  74. <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>
  75. <span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">---------+------------+</span>
  76. <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)
  77. #</span><span style="color: #800000; font-weight: bold">6</span><span style="color: #000000">:关键字LIKE模糊查询
  78. 通配符’</span><span style="color: #808080">%</span><span style="color: #000000">’
  79. 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">;
  80. </span><span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">--+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+</span>
  81. <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>
  82. <span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">--+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+</span>
  83. <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>
  84. <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>
  85. <span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">--+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+</span>
  86. <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)
  87. 通配符</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">_</span><span style="color: #ff0000">‘</span><span style="color: #000000">
  88. 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">;
  89. </span><span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">---+</span>
  90. <span style="color: #808080">|</span> age <span style="color: #808080">|</span>
  91. <span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">---+</span>
  92. <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">78</span> <span style="color: #808080">|</span>
  93. <span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">---+</span>
  94. <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)
  95. 练习:
  96. </span><span style="color: #800000; font-weight: bold">1</span><span style="color: #000000">. 查看岗位是teacher的员工姓名、年龄
  97. </span><span style="color: #800000; font-weight: bold">2</span><span style="color: #000000">. 查看岗位是teacher且年龄大于30岁的员工姓名、年龄
  98. </span><span style="color: #800000; font-weight: bold">3</span>. 查看岗位是teacher且薪资在9000<span style="color: #808080">-</span><span style="color: #000000">1000范围内的员工姓名、年龄、薪资
  99. </span><span style="color: #800000; font-weight: bold">4</span><span style="color: #000000">. 查看岗位描述不为NULL的员工信息
  100. </span><span style="color: #800000; font-weight: bold">5</span><span style="color: #000000">. 查看岗位是teacher且薪资是10000或9000或30000的员工姓名、年龄、薪资
  101. </span><span style="color: #800000; font-weight: bold">6</span><span style="color: #000000">. 查看岗位是teacher且薪资不是10000或9000或30000的员工姓名、年龄、薪资
  102. </span><span style="color: #800000; font-weight: bold">7</span><span style="color: #000000">. 查看岗位是teacher且名字是jin开头的员工姓名、年薪
  103. #对应的sql语句
  104. </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">;
  105. </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">;
  106. </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">;
  107. </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">;
  108. </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">);
  109. </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">);
  110. </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>;
where约束

 

(2)group by 分组查询

  1. #1、首先明确一点:分组发生在where之后,即分组是基于where之后得到的记录而进行的
  2. #2、分组指的是:将所有记录按照某个相同字段进行归类,比如针对员工信息表的职位分组,或者按照性别进行分组等
  3. #3、为何要分组呢?
  4. 取每个部门的最高工资
  5. 取每个部门的员工数
  6. 取男人数和女人数
  7. 小窍门:‘每’这个字后面的字段,就是我们分组的依据
  8. #4、大前提:
  9. 可以按照任意字段分组,但是分组完毕后,比如group by post,只能查看post字段,如果想查看组内信息,需要借助于聚合函数

 

当执行以下sql语句的时候,是以post字段查询了组中的第一条数据,没有任何意义,因为我们现在想查出当前组的多条记录。

  1. mysql> select * from employee group by post;
  2. +----+--------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
  3. | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
  4. +----+--------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
  5. | 14 | 张野 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 |
  6. | 9 | 歪歪 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 |
  7. | 2 | alex | male | 78 | 2015-03-02 | teacher | | 1000000.31 | 401 | 1 |
  8. | 1 | egon | male | 18 | 2017-03-01 | 老男孩驻沙河办事处外交大使 | NULL | 7300.33 | 401 | 1 |
  9. +----+--------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
  10. 4 rows in set (0.00 sec)
  11. #由于没有设置ONLY_FULL_GROUP_BY,于是也可以有结果,默认都是组内的第一条记录,但其实这是没有意义的
  12. 如果想分组,则必须要设置全局的sql的模式为ONLY_FULL_GROUP_BY
  13. mysql> set global sql_mode=‘ONLY_FULL_GROUP_BY‘;
  14. Query OK, 0 rows affected (0.00 sec)
  15. #查看MySQL 5.7默认的sql_mode如下:
  16. mysql> select @@global.sql_mode;
  17. +--------------------+
  18. | @@global.sql_mode |
  19. +--------------------+
  20. | ONLY_FULL_GROUP_BY |
  21. +--------------------+
  22. 1 row in set (0.00 sec)
  23. mysql> exit;#设置成功后,一定要退出,然后重新登录方可生效
  24. Bye

 

继续验证通过group by分组之后,只能查看当前字段,如果想查看组内信息,需要借助于聚合函数

  1. mysql> select * from emp group by post;# 报错
  2. ERROR 1054 (42S22): Unknown column ‘post‘ in ‘group statement‘
  3. mysql> select post from employee group by post;
  4. +-----------------------------------------+
  5. | post |
  6. +-----------------------------------------+
  7. | operation |
  8. | sale |
  9. | teacher |
  10. | 老男孩驻沙河办事处外交大使 |
  11. +-----------------------------------------+
  12. 4 rows in set (0.00 sec)

 

 

(3)聚合函数

  1. max()求最大值
  2. min()求最小值
  3. avg()求平均值
  4. sum() 求和
  5. count() 求总个数
  6. #强调:聚合函数聚合的是组的内容,若是没有分组,则默认一组
  7. # 每个部门有多少个员工
  8. select post,count(id) from employee group by post;
  9. # 每个部门的最高薪水
  10. select post,max(salary) from employee group by post;
  11. # 每个部门的最低薪水
  12. select post,min(salary) from employee group by post;
  13. # 每个部门的平均薪水
  14. select post,avg(salary) from employee group by post;
  15. # 每个部门的所有薪水
  16. select post,sum(age) from employee group by post;

 

 

(4)HAVING过滤

  

  1. HAVING与WHERE不一样的地方在于
  2. #!!!执行优先级从高到低:where > group by > having
  3. #1. Where 发生在分组group by之前,因而Where中可以有任意字段,但是绝对不能使用聚合函数。
  4. #2. Having发生在分组group by之后,因而Having中可以使用分组的字段,无法直接取到其他字段,可以使用聚合函数

 

验证:

技术分享图片
  1. <span style="color: #000000">验证:
  2. 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">;
  3. </span><span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">--+------+------+-----+------------+---------+--------------+------------+--------+-----------+</span>
  4. <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>
  5. <span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">--+------+------+-----+------------+---------+--------------+------------+--------+-----------+</span>
  6. <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>
  7. <span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">--+------+------+-----+------------+---------+--------------+------------+--------+-----------+</span>
  8. <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)
  9. 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">;
  10. 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
  11. # 必须使用group by才能使用group_concat()函数,将所有的name值连接
  12. 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>
  13. <div class=" "="">
  14. <ul class="m-news-opt fix">
  15. <li class="opt-item">
  16. <a href="/sql_question-407642.html" target="_blank"><p>< 上一篇</p><p class="ellipsis">安装mysql 8 及 修复登录错误日志</p></a>
  17. </li>
  18. <li class="opt-item ta-r">
  19. <a href="/sql_question-407644.html" target="_blank"><p>下一篇 ></p><p class="ellipsis">MySQL 数据的增删改查</p></a>
  20. </li>
  21. </ul>
  22. </span>

人气教程排行