select

关键词：select，as

返回一个二元表。后面跟字符类型或运算类型，字符类型。运算类型运算式做字段（表名），运算结果为一条数据。字符类型字段和数据同名。

as可以为给字段起别名（可省略）。省略和和不省略注意引号问题。

  1. mysql>select 2*7;
  2. +-----+
  3. | 2*7 |
  4. +-----+
  5. |  14 |
  6. mysql>select '青柠檬的';
  7. +----------+
  8. | 青柠檬的 |
  9. +----------+
  10. | 青柠檬的 |
  11. +----------+
  12. mysql>select '青柠檬的' as 奶茶店;    #引号对别名不影响
  13. +----------+
  14. | 奶茶店   |
  15. +----------+
  16. | 青柠檬的 |
  17. +----------+
  18. msyql>select '青柠檬的' 奶茶店;        #注意下面两条语句的结果
  19. +----------+
  20. | 奶茶店   |
  21. +----------+
  22. | 青柠檬的 |
  23. +----------+
  24. mysql>select '青柠檬的' '奶茶店';
  25. +----------------+
  26. | 青柠檬的       |
  27. +----------------+
  28. | 青柠檬的奶茶店 |

from

关键字：from，select

后面接要查的表（可以接多个表名），与select配合，查询单表时返回整张表的数据，查询多表时返回的为多表的笛卡尔积。

  1. mysql> select * from t1;
  2. +------+-------+
  3. | id   | name  |
  4. +------+-------+
  5. |    1 | frank |
  6. |    2 | pony  |
  7. +------+-------+
  8. mysql> select * from t2;
  9. +------+---------+
  10. | math | english |
  11. +------+---------+
  12. |   87 |      99 |
  13. |   67 |      89 |
  14. +------+---------+
  15. mysql> select * from t1,t2;
  16. +------+-------+------+---------+
  17. | id   | name  | math | english |
  18. +------+-------+------+---------+
  19. |    1 | frank |   87 |      99 |
  20. |    2 | pony  |   87 |      99 |
  21. |    1 | frank |   67 |      89 |
  22. |    2 | pony  |   67 |      89 |
  23. +------+-------+------+---------+

dual

关键词：select，from，dual

跟在from后面的关键字，为mysql中的虚拟表。select…from…后面其实时省略了as duanl。

  1. mysql>select 2*7 as res;
  2. +-----+
  3. | res |
  4. +-----+
  5. |  14 |
  6. +-----+
  7. mysql>select 2*7 as res from dual;
  8. +-----+
  9. | res |
  10. +-----+
  11. |  14 |
  12. +-----+

where

后面筛选条件

in

和 or（not in）结果是等效的。但是两者查询效率上有差别。

between and

查询区间为左闭右闭。[ ]

is null

  1. select * from students where id is null;

查询值是否为空

聚合函数

MySQL函数

like模糊查询

  1. select * from student where name like '张%'    #%代表多个值
  2. select * from student where name like '张_'    #_代表一个值

order by

  1. select * from score order by chinese asc;    #升序
  2. select * from score order by chinese desc;    #降序

group by

查询的字段必须为聚合函数和分组字段。

  1. select avg(age) as '年龄',gender as '性别' from info group by gender;    #按性别去分组，group by后面跟分组条件
  3. select avg(age) as '年龄'，address as '地区' from info group by address；    #按地区分组

group_concat

  1. select group_concat(name),gender from from student group by gender;

having

对已经查询过后的结果在进行查询。

  1. select chinese from score where chinese>95
  2. select avg(age) as 'age', address as '地区' from info group by address 
  3. having age>24;

limit

限制

  1. select * from limit 0,2;    -- [起始量，偏移量)

distinct

去除重复的信息

  1. select distinct address from info;
  1. select count(distinct address) from info;
  3. select all address from info == select address from info;