当前位置:Gxlcms > 数据库问题 > mysql 单表查询

mysql 单表查询

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

  1. <span style="color: #000000">SELECT 字段1,字段2... FROM 表名
  2. WHERE 条件
  3. GROUP BY field
  4. HAVING 筛选
  5. ORDER BY field
  6. LIMIT 限制条数</span>

二、关键字的执行优先级

  1. <span style="color: #000000">重点中的重点:关键字的执行优先级
  2. </span>1、<span style="color: #0000ff">from</span><span style="color: #000000"> 先找表
  3. </span>2<span style="color: #000000">、where 拿着约束条件做筛选
  4. </span>3<span style="color: #000000">、group by 将取出的一条条记录进行分组group by,如果没有group by,则整体作为一组
  5. </span>4<span style="color: #000000">、having 将分组结果进行过滤
  6. </span>5<span style="color: #000000">、select 执行select
  7. </span>6<span style="color: #000000">、distinct 去重
  8. </span>7<span style="color: #000000">、order by 将结果排序
  9. </span>8、limit 限制显示条数

三、简单查询

技术分享图片
  1. <span style="color: #000000">company.employee
  2. 员工id id int
  3. 姓名 emp_name varchar
  4. 性别 sex enum
  5. 年龄 age int
  6. 入职日期 hire_date date
  7. 岗位 post varchar
  8. 职位描述 post_comment varchar
  9. 薪水 salary double
  10. 办公室 office int
  11. 部门编号 depart_id int
  12. </span><span style="color: #008000">#</span><span style="color: #008000">创建表</span>
  13. <span style="color: #000000">create table employee(
  14. id int </span><span style="color: #0000ff">not</span><span style="color: #000000"> null unique auto_increment,
  15. name varchar(</span>20) <span style="color: #0000ff">not</span><span style="color: #000000"> null,
  16. sex enum(</span><span style="color: #800000">‘</span><span style="color: #800000">male</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">female</span><span style="color: #800000">‘</span>) <span style="color: #0000ff">not</span> null default <span style="color: #800000">‘</span><span style="color: #800000">male</span><span style="color: #800000">‘</span>, <span style="color: #008000">#</span><span style="color: #008000">大部分是男的</span>
  17. age int(3) unsigned <span style="color: #0000ff">not</span> null default 28<span style="color: #000000">,
  18. hire_date date </span><span style="color: #0000ff">not</span><span style="color: #000000"> null,
  19. post varchar(</span>50<span style="color: #000000">),
  20. post_comment varchar(</span>100<span style="color: #000000">),
  21. salary double(</span>15,2<span style="color: #000000">),
  22. office int, </span><span style="color: #008000">#</span><span style="color: #008000">一个部门一个屋子</span>
  23. <span style="color: #000000">depart_id int
  24. );
  25. </span><span style="color: #008000">#</span><span style="color: #008000">查看表结构</span>
  26. mysql><span style="color: #000000"> desc employee;
  27. </span>+--------------+-----------------------+------+-----+---------+----------------+
  28. | Field | Type | Null | Key | Default | Extra |
  29. +--------------+-----------------------+------+-----+---------+----------------+
  30. | id | int(11) | NO | PRI | NULL | auto_increment |
  31. | name | varchar(20) | NO | | NULL | |
  32. | sex | enum(<span style="color: #800000">‘</span><span style="color: #800000">male</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">female</span><span style="color: #800000">‘</span>) | NO | | male | |
  33. | age | int(3) unsigned | NO | | 28 | |
  34. | hire_date | date | NO | | NULL | |
  35. | post | varchar(50) | YES | | NULL | |
  36. | post_comment | varchar(100) | YES | | NULL | |
  37. | salary | double(15,2) | YES | | NULL | |
  38. | office | int(11) | YES | | NULL | |
  39. | depart_id | int(11) | YES | | NULL | |
  40. +--------------+-----------------------+------+-----+---------+----------------+
  41. <span style="color: #008000">#</span><span style="color: #008000">插入记录</span><span style="color: #008000">
  42. #</span><span style="color: #008000">三个部门:教学,销售,运营</span>
  43. <span style="color: #000000">insert into employee(name,sex,age,hire_date,post,salary,office,depart_id) values
  44. (</span><span style="color: #800000">‘</span><span style="color: #800000">egon</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">male</span><span style="color: #800000">‘</span>,18,<span style="color: #800000">‘</span><span style="color: #800000">20170301</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">老男孩驻沙河办事处外交大使</span><span style="color: #800000">‘</span>,7300.33,401,1), <span style="color: #008000">#</span><span style="color: #008000">以下是教学部</span>
  45. (<span style="color: #800000">‘</span><span style="color: #800000">alex</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">male</span><span style="color: #800000">‘</span>,78,<span style="color: #800000">‘</span><span style="color: #800000">20150302</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">teacher</span><span style="color: #800000">‘</span>,1000000.31,401,1<span style="color: #000000">),
  46. (</span><span style="color: #800000">‘</span><span style="color: #800000">wupeiqi</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">male</span><span style="color: #800000">‘</span>,81,<span style="color: #800000">‘</span><span style="color: #800000">20130305</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">teacher</span><span style="color: #800000">‘</span>,8300,401,1<span style="color: #000000">),
  47. (</span><span style="color: #800000">‘</span><span style="color: #800000">yuanhao</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">male</span><span style="color: #800000">‘</span>,73,<span style="color: #800000">‘</span><span style="color: #800000">20140701</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">teacher</span><span style="color: #800000">‘</span>,3500,401,1<span style="color: #000000">),
  48. (</span><span style="color: #800000">‘</span><span style="color: #800000">liwenzhou</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">male</span><span style="color: #800000">‘</span>,28,<span style="color: #800000">‘</span><span style="color: #800000">20121101</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">teacher</span><span style="color: #800000">‘</span>,2100,401,1<span style="color: #000000">),
  49. (</span><span style="color: #800000">‘</span><span style="color: #800000">jingliyang</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">female</span><span style="color: #800000">‘</span>,18,<span style="color: #800000">‘</span><span style="color: #800000">20110211</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">teacher</span><span style="color: #800000">‘</span>,9000,401,1<span style="color: #000000">),
  50. (</span><span style="color: #800000">‘</span><span style="color: #800000">jinxin</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">male</span><span style="color: #800000">‘</span>,18,<span style="color: #800000">‘</span><span style="color: #800000">19000301</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">teacher</span><span style="color: #800000">‘</span>,30000,401,1<span style="color: #000000">),
  51. (</span><span style="color: #800000">‘</span><span style="color: #800000">成龙</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">male</span><span style="color: #800000">‘</span>,48,<span style="color: #800000">‘</span><span style="color: #800000">20101111</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">teacher</span><span style="color: #800000">‘</span>,10000,401,1<span style="color: #000000">),
  52. (</span><span style="color: #800000">‘</span><span style="color: #800000">歪歪</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">female</span><span style="color: #800000">‘</span>,48,<span style="color: #800000">‘</span><span style="color: #800000">20150311</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">sale</span><span style="color: #800000">‘</span>,3000.13,402,2),<span style="color: #008000">#</span><span style="color: #008000">以下是销售部门</span>
  53. (<span style="color: #800000">‘</span><span style="color: #800000">丫丫</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">female</span><span style="color: #800000">‘</span>,38,<span style="color: #800000">‘</span><span style="color: #800000">20101101</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">sale</span><span style="color: #800000">‘</span>,2000.35,402,2<span style="color: #000000">),
  54. (</span><span style="color: #800000">‘</span><span style="color: #800000">丁丁</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">female</span><span style="color: #800000">‘</span>,18,<span style="color: #800000">‘</span><span style="color: #800000">20110312</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">sale</span><span style="color: #800000">‘</span>,1000.37,402,2<span style="color: #000000">),
  55. (</span><span style="color: #800000">‘</span><span style="color: #800000">星星</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">female</span><span style="color: #800000">‘</span>,18,<span style="color: #800000">‘</span><span style="color: #800000">20160513</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">sale</span><span style="color: #800000">‘</span>,3000.29,402,2<span style="color: #000000">),
  56. (</span><span style="color: #800000">‘</span><span style="color: #800000">格格</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">female</span><span style="color: #800000">‘</span>,28,<span style="color: #800000">‘</span><span style="color: #800000">20170127</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">sale</span><span style="color: #800000">‘</span>,4000.33,402,2<span style="color: #000000">),
  57. (</span><span style="color: #800000">‘</span><span style="color: #800000">张野</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">male</span><span style="color: #800000">‘</span>,28,<span style="color: #800000">‘</span><span style="color: #800000">20160311</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">operation</span><span style="color: #800000">‘</span>,10000.13,403,3), <span style="color: #008000">#</span><span style="color: #008000">以下是运营部门</span>
  58. (<span style="color: #800000">‘</span><span style="color: #800000">程咬金</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">male</span><span style="color: #800000">‘</span>,18,<span style="color: #800000">‘</span><span style="color: #800000">19970312</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">operation</span><span style="color: #800000">‘</span>,20000,403,3<span style="color: #000000">),
  59. (</span><span style="color: #800000">‘</span><span style="color: #800000">程咬银</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">female</span><span style="color: #800000">‘</span>,18,<span style="color: #800000">‘</span><span style="color: #800000">20130311</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">operation</span><span style="color: #800000">‘</span>,19000,403,3<span style="color: #000000">),
  60. (</span><span style="color: #800000">‘</span><span style="color: #800000">程咬铜</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">male</span><span style="color: #800000">‘</span>,18,<span style="color: #800000">‘</span><span style="color: #800000">20150411</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">operation</span><span style="color: #800000">‘</span>,18000,403,3<span style="color: #000000">),
  61. (</span><span style="color: #800000">‘</span><span style="color: #800000">程咬铁</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">female</span><span style="color: #800000">‘</span>,18,<span style="color: #800000">‘</span><span style="color: #800000">20140512</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">operation</span><span style="color: #800000">‘</span>,17000,403,3<span style="color: #000000">)
  62. ;
  63. </span><span style="color: #008000">#</span><span style="color: #008000">ps:如果在windows系统中,插入中文字符,select的结果为空白,可以将所有字符编码统一设置成gbk</span>
准备表

技术分享图片

1、定义显示格式

concat()函数用于连接字符串 用法:数据库迁移(拷贝)
  1. <span style="color: #000000">CONCAT() 函数用于连接字符串
  2. SELECT CONCAT(</span><span style="color: #800000">‘</span><span style="color: #800000">姓名: </span><span style="color: #800000">‘</span>,name,<span style="color: #800000">‘</span><span style="color: #800000"> 年薪: </span><span style="color: #800000">‘</span>, salary*12<span style="color: #000000">) <br>AS Annual_salary FROM employee;
  3. CONCAT_WS() 第一个参数为分隔符
  4. SELECT CONCAT_WS(</span><span style="color: #800000">‘</span><span style="color: #800000">:</span><span style="color: #800000">‘</span>,name,salary*12) AS Annual_salary FROM employee;

技术分享图片

 

 高级版
  1. select concat(<span style="color: #800000">‘</span><span style="color: #800000">naem:</span><span style="color: #800000">‘</span>,name) as name,concat(<span style="color: #800000">‘</span><span style="color: #800000">annual_salary:</span><span style="color: #800000">‘</span>,salary*12)<br> as annual_salary <span style="color: #0000ff">from</span> emp2;
技术分享图片

2、

  1. 普通版: <br>select id,concat(name,‘:‘,sex,‘:‘,salary) as info from emp2;

技术分享图片

  1. <span style="color: #008000">#</span><span style="color: #008000"> 进化版</span>
  2. select id,concat_ws(<span style="color: #800000">‘</span><span style="color: #800000">:</span><span style="color: #800000">‘</span>,name,sex,salary) as info <span style="color: #0000ff">from</span> emp2;

技术分享图片

  技术分享图片

 

2、distinct 去重

技术分享图片

as显示效果的区别:

技术分享图片

3、where约束

  1. <span style="color: #000000">where字句中可以使用:
  2. </span>1. 比较运算符:> < >= <= <> !=
  3. 2. between 80 <span style="color: #0000ff">and</span> 100<span style="color: #000000"> 值在80到100之间,包括80和100
  4. </span>3. <span style="color: #0000ff">in</span>(80,90,100<span style="color: #000000">) 值是80或90或100
  5. </span>4. like <span style="color: #800000">‘</span><span style="color: #800000">egon%</span><span style="color: #800000">‘</span><span style="color: #000000">
  6. pattern可以是</span>%<span style="color: #000000">或_,
  7. </span>%<span style="color: #000000">表示任意多字符
  8. _表示任意一个字符
  9. </span>5. 逻辑运算符:在多个条件直接可以使用逻辑运算符 <span style="color: #0000ff">and</span> <span style="color: #0000ff">or</span> <span style="color: #0000ff">not</span>
  1. <span style="color: #008000">#</span><span style="color: #008000">1:单条件查询</span>
  2. <span style="color: #000000"> SELECT name FROM employee
  3. WHERE post</span>=<span style="color: #800000">‘</span><span style="color: #800000">sale</span><span style="color: #800000">‘</span><span style="color: #000000">;
  4. </span><span style="color: #008000">#</span><span style="color: #008000">2:多条件查询</span>
  5. <span style="color: #000000"> SELECT name,salary FROM employee
  6. WHERE post</span>=<span style="color: #800000">‘</span><span style="color: #800000">teacher</span><span style="color: #800000">‘</span> AND salary>10000<span style="color: #000000">;
  7. </span><span style="color: #008000">#</span><span style="color: #008000">3:关键字BETWEEN AND</span>
  8. <span style="color: #000000"> SELECT name,salary FROM employee
  9. WHERE salary BETWEEN </span>10000 AND 20000<span style="color: #000000">;
  10. SELECT name,salary FROM employee
  11. WHERE salary NOT BETWEEN </span>10000 AND 20000<span style="color: #000000">;
  12. </span><span style="color: #008000">#</span><span style="color: #008000">4:关键字IS NULL(判断某个字段是否为NULL不能用等号,需要用IS)</span>
  13. <span style="color: #000000"> SELECT name,post_comment FROM employee
  14. WHERE post_comment IS NULL;
  15. SELECT name,post_comment FROM employee
  16. WHERE post_comment IS NOT NULL;
  17. SELECT name,post_comment FROM employee
  18. WHERE post_comment</span>=<span style="color: #800000">‘‘</span>; 注意<span style="color: #800000">‘‘</span><span style="color: #000000">是空字符串,不是null
  19. ps:
  20. 执行
  21. update employee set post_comment</span>=<span style="color: #800000">‘‘</span> where id=2<span style="color: #000000">;
  22. 再用上条查看,就会有结果了
  23. </span><span style="color: #008000">#</span><span style="color: #008000">5:关键字IN集合查询</span>
  24. <span style="color: #000000"> SELECT name,salary FROM employee
  25. WHERE salary</span>=3000 OR salary=3500 OR salary=4000 OR salary=9000<span style="color: #000000"> ;
  26. SELECT name,salary FROM employee
  27. WHERE salary IN (</span>3000,3500,4000,9000<span style="color: #000000">) ;
  28. SELECT name,salary FROM employee
  29. WHERE salary NOT IN (</span>3000,3500,4000,9000<span style="color: #000000">) ;
  30. </span><span style="color: #008000">#</span><span style="color: #008000">6:关键字LIKE模糊查询</span>
  31. 通配符’%<span style="color: #000000">’
  32. SELECT </span>*<span style="color: #000000"> FROM employee
  33. WHERE name LIKE </span><span style="color: #800000">‘</span><span style="color: #800000">eg%</span><span style="color: #800000">‘</span><span style="color: #000000">;
  34. 通配符’_’
  35. SELECT </span>*<span style="color: #000000"> FROM employee
  36. WHERE name LIKE </span><span style="color: #800000">‘</span><span style="color: #800000">al__</span><span style="color: #800000">‘</span>;

4、分组 group by

  注意:使用分组只能查看分组的依据和聚合函数,若是查看其它信息,则默认是该组的第一条信息,这其实是没有意义的。如下:

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

二 ONLY_FULL_GROUP_BY

 

  1. <span style="color: #008000">#</span><span style="color: #008000">查看MySQL 5.7默认的sql_mode如下:</span>
  2. mysql> select @@<span style="color: #0000ff">global</span><span style="color: #000000">.sql_mode;
  3. ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
  4. </span><span style="color: #008000">#</span><span style="color: #008000">!!!注意</span>
  5. <span style="color: #000000">ONLY_FULL_GROUP_BY的语义就是确定select target list中的所有列的值都是明确语义,简单的说来,在ONLY_FULL_GROUP_BY模式下,target list中的值要么是来自于聚集函数的结果,<br>要么是来自于group by list中的表达式的值。
  6. </span><span style="color: #008000">#</span><span style="color: #008000">设置sql_mole如下操作(我们可以去掉ONLY_FULL_GROUP_BY模式):</span>
  7. mysql> set <span style="color: #0000ff">global</span> sql_mode=<span style="color: #800000">‘</span><span style="color: #800000">STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,<br>NO_ENGINE_SUBSTITUTION</span><span style="color: #800000">‘</span>;

 

三 GROUP BY

 

  1. <span style="color: #008000">#</span><span style="color: #008000"> 单独使用GROUP BY关键字分组</span>
  2. <span style="color: #000000"> SELECT post FROM employee GROUP BY post;
  3. 注意:我们按照post字段分组,那么select查询的字段只能是post,想要获取组内的其他相关信息,需要借助函数
  4. GROUP BY关键字和GROUP_CONCAT()函数一起使用
  5. SELECT post,GROUP_CONCAT(name) FROM employee GROUP BY post;<br></span><span style="color: #008000">#</span><span style="color: #008000">按照岗位分组,并查看组内成员名</span>
  6. <span style="color: #000000"> SELECT post,GROUP_CONCAT(name) as emp_members FROM employee GROUP BY post;
  7. </span><span style="color: #008000">GROUP BY与聚合函数一起使用</span>
  8. select post,count(id) as count <span style="color: #0000ff">from</span> employee group by post;<br><span style="color: #008000">#</span><span style="color: #008000">按照岗位分组,并查看每个组有多少人</span>

 

强调:

 

  1. <span style="color: #000000">如果我们用unique的字段作为分组的依据,则每一条记录自成一组,这种分组没有意义
  2. 多条记录之间的某个字段值相同,该字段通常用来作为分组的依据</span>

 

四 聚合函数

  1. <span style="color: #008000">#</span><span style="color: #008000">强调:聚合函数聚合的是组的内容,若是没有分组,则默认一组</span>
  2. <span style="color: #000000">示例:
  3. SELECT COUNT(</span>*<span style="color: #000000">) FROM employee;
  4. SELECT COUNT(</span>*) FROM employee WHERE depart_id=1<span style="color: #000000">;
  5. SELECT MAX(salary) FROM employee;
  6. SELECT MIN(salary) FROM employee;
  7. SELECT AVG(salary) FROM employee;
  8. SELECT SUM(salary) FROM employee;
  9. SELECT SUM(salary) FROM employee WHERE depart_id</span>=3;

五、having 过滤

HAVING与WHERE不一样的地方在于!!!!!!

  1. <span style="color: #008000">#</span><span style="color: #008000">!!!执行优先级从高到低:where > group by > having </span><span style="color: #008000">
  2. #</span><span style="color: #008000">1. Where 发生在分组group by之前,因而Where中可以有任意字段,但是绝对不能使用聚合函数。</span>
  3. <span style="color: #008000">#</span><span style="color: #008000">2. Having发生在分组group by之后,因而Having中可以使用分组的字段,<br>无法直接取到其他字段,可以使用聚合函数</span>

 六、查询排序:ORDER BY

  1. <span style="color: #000000">按单列排序
  2. SELECT </span>*<span style="color: #000000"> FROM employee ORDER BY salary;
  3. SELECT </span>*<span style="color: #000000"> FROM employee ORDER BY salary ASC;
  4. SELECT </span>*<span style="color: #000000"> FROM employee ORDER BY salary DESC;
  5. 按多列排序:先按照age排序,如果年纪相同,则按照薪资排序
  6. SELECT </span>* <span style="color: #0000ff">from</span><span style="color: #000000"> employee
  7. ORDER BY age,
  8. salary DESC;</span>

七、限制显示数据:LIMIT

  1. <span style="color: #000000">示例:
  2. SELECT </span>*<span style="color: #000000"> FROM employee ORDER BY salary DESC
  3. LIMIT </span>3; <span style="color: #008000">#</span><span style="color: #008000">默认初始位置为0 </span>
  4. <span style="color: #000000">
  5. SELECT </span>*<span style="color: #000000"> FROM employee ORDER BY salary DESC
  6. LIMIT 0,</span>5; <span style="color: #008000">#</span><span style="color: #008000">从第0开始,即先查询出第一条,然后包含这一条在内往后查5条</span>
  7. <span style="color: #000000">
  8. SELECT </span>*<span style="color: #000000"> FROM employee ORDER BY salary DESC
  9. LIMIT </span>5,5; <span style="color: #008000">#</span><span style="color: #008000">从第5开始,即先查询出第6条,然后包含这一条在内往后查5条</span>

八、使用正则表达式查询

技术分享图片
  1. SELECT * FROM employee WHERE name REGEXP <span style="color: #800000">‘</span><span style="color: #800000">^ale</span><span style="color: #800000">‘</span><span style="color: #000000">;
  2. SELECT </span>* FROM employee WHERE name REGEXP <span style="color: #800000">‘</span><span style="color: #800000">on$</span><span style="color: #800000">‘</span><span style="color: #000000">;
  3. SELECT </span>* FROM employee WHERE name REGEXP <span style="color: #800000">‘</span><span style="color: #800000">m{2}</span><span style="color: #800000">‘</span><span style="color: #000000">;
  4. 小结:对字符串匹配的方式
  5. WHERE name </span>= <span style="color: #800000">‘</span><span style="color: #800000">egon</span><span style="color: #800000">‘</span><span style="color: #000000">;
  6. WHERE name LIKE </span><span style="color: #800000">‘</span><span style="color: #800000">yua%</span><span style="color: #800000">‘</span><span style="color: #000000">;
  7. WHERE name REGEXP </span><span style="color: #800000">‘</span><span style="color: #800000">on$</span><span style="color: #800000">‘</span>;
View Code

 

 

mysql 单表查询

标签:strong   一个   中文   小结   def   显示   date   oba   语义   

人气教程排行