当前位置：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.    
  4. CONCAT_WS() 第一个参数为分隔符
  5. 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.         
  5. </span><span style="color: #008000">#</span><span style="color: #008000">2:多条件查询</span>
  6. <span style="color: #000000">    SELECT name,salary FROM employee
  7.         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">;
  8. </span><span style="color: #008000">#</span><span style="color: #008000">3:关键字BETWEEN AND</span>
  9. <span style="color: #000000">    SELECT name,salary FROM employee 
  10.         WHERE salary BETWEEN </span>10000 AND 20000<span style="color: #000000">;
  11.     SELECT name,salary FROM employee 
  12.         WHERE salary NOT BETWEEN </span>10000 AND 20000<span style="color: #000000">;
  13.     
  14. </span><span style="color: #008000">#</span><span style="color: #008000">4:关键字IS NULL(判断某个字段是否为NULL不能用等号，需要用IS)</span>
  15. <span style="color: #000000">    SELECT name,post_comment FROM employee 
  16.         WHERE post_comment IS NULL;
  17.     SELECT name,post_comment FROM employee 
  18.         WHERE post_comment IS NOT NULL;
  19.         
  20.     SELECT name,post_comment FROM employee 
  21.         WHERE post_comment</span>=<span style="color: #800000">‘‘</span>; 注意<span style="color: #800000">‘‘</span><span style="color: #000000">是空字符串，不是null
  22.     ps：
  23.         执行
  24.         update employee set post_comment</span>=<span style="color: #800000">‘‘</span> where id=2<span style="color: #000000">;
  25.         再用上条查看，就会有结果了
  26. </span><span style="color: #008000">#</span><span style="color: #008000">5:关键字IN集合查询</span>
  27. <span style="color: #000000">    SELECT name,salary FROM employee 
  28.         WHERE salary</span>=3000 OR salary=3500 OR salary=4000 OR salary=9000<span style="color: #000000"> ;
  29.     
  30.     SELECT name,salary FROM employee 
  31.         WHERE salary IN (</span>3000,3500,4000,9000<span style="color: #000000">) ;
  32.     SELECT name,salary FROM employee 
  33.         WHERE salary NOT IN (</span>3000,3500,4000,9000<span style="color: #000000">) ;
  34. </span><span style="color: #008000">#</span><span style="color: #008000">6:关键字LIKE模糊查询</span>
  35.     通配符’%<span style="color: #000000">’
  36.     SELECT </span>*<span style="color: #000000"> FROM employee 
  37.             WHERE name LIKE </span><span style="color: #800000">‘</span><span style="color: #800000">eg%</span><span style="color: #800000">‘</span><span style="color: #000000">;
  38.     通配符’_’
  39.     SELECT </span>*<span style="color: #000000"> FROM employee 
  40.             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   语义   

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！