1.查询所有字段
select * from 表名;
2.查询指定记录 where
可以使用 < in between and like 等;
mysql> select num from tmp14 where num<65; +------+ | num | +------+ | 64 | +------+ 1 row in set (0.00 sec)
mysql> select num from tmp14 where num like '6%'; +------+ | num | +------+ | 64 | +------+ 1 row in set (0.01 sec)
like 使用的时候，%可以匹配任意长度的字符。
3.查询空值 IS NULL IS NOT NULL
mysql> select num from tmp14 where num is not null; +------+ | num | +------+ | 64 | +------+ 1 row in set (0.00 sec)
4.带AND 或者 OR 的多条件查询
mysql> select num from tmp14 where num = 64 or num = 14; +------+ | num | +------+ | 64 | +------+ 1 row in set (0.01 sec)
mysql> select num from tmp14 where num in (14,64); +------+ | num | +------+ | 64 | +------+ 1 row in set (0.00 sec)
可以使用 in 的效果与 OR 效果相同，并且in的执行效率更加高于or的执行效率，更重要的是，in可以执行更加复杂的嵌套查询;
5.查询结果不重复，关键字distinct
mysql> select distinct num from tmp14; +------+ | num | +------+ | 64 | +------+ 1 row in set (0.01 sec)
6.对查询结果排序，关键字order by，默认为升序排序，order by DESC为降序排序,order by ASC 为升序排序，并且还可以指定多个排序的字段;
mysql> select num from tmp14 order by num; +------+ | num | +------+ | 14 | | 64 | +------+ 2 rows in set (0.03 sec)
7.分组查询，关键字 group by
group by 关键字通常和函数一起使用，将数据分为多个逻辑组，并对每个进行集合计算；
group by 还可以多分组分段；
mysql> select city,count(*) as total from office group by city; +---------+-------+ | city | total | +---------+-------+ | 4 | 1 | | nanjing | 1 | | wuhan | 2 | +---------+-------+ 3 rows in set (0.05 sec)
8.使用 having 对分组数据过滤
mysql> select city,count(*) as total from office group by city having total>1; +-------+-------+ | city | total | +-------+-------+ | wuhan | 2 | +-------+-------+ 1 row in set (0.00 sec)
9.使用limit限制查询结果
limit [位置偏移行]，行数;
位置偏移行是个可选参数;
如不指定位置偏移行，则显示指定行数内容，如下：只显示一行内容
mysql> select * from office limit 1; +------------+-------+ | officeCode | city | +------------+-------+ | 1 | wuhan | +------------+-------+ 1 row in set (0.00 sec)
指定位置偏移行，从第2行开始，显示2行内容；
mysql> select * from office limit 2,2; +------------+---------+ | officeCode | city | +------------+---------+ | 3 | nanjing | | 4 | 4 | +------------+---------+ 2 rows in set (0.00 sec)
10.常用的集合函数 conut，max，min，sum，avg；
11.内连接查询，关键字inner join on
select suppliers.s_id s_name from fruits,suppliers where fruits.s_id = suppliers.s_id
select suppliers.s_id s_name from fruits inner join suppliers on fruits.s_id = suppliers.s_id
这两句话查询的结果都一样，虽然where语句连接条件简单明了，但是join on语法是ANSI SQL的标准规范，
并且where字句在某些时候回影响查询性能；
12.外连接查询
left join
right join
13.复合条件查询
在连接查询的过程中加入，and ，or等过滤限制条件；
14.带any，some 关键字的子查询
mysql> select num from tmp14 where num > any(select officeCode from office); +------+ | num | +------+ | 64 | | 14 | +------+ 2 rows in set (0.05 sec)
any，some为子查询条件的任一结果都可以；
15.带ALL关键字的子查询
mysql> select num from tmp14 where num > all(select officeCode from office); +------+ | num | +------+ | 64 | | 14 | +------+ 2 rows in set (0.00 sec)
all为子查询条件全部条件都要复合
16.exists关键字的子查询
mysql> select num from tmp14 where exists (select officeCode from office where city = 'wuhan'); +------+ | num | +------+ | 64 | | 14 | +------+ 2 rows in set (0.00 sec)
查询子语句是否存在exists的子查询存在的记录，如果存在则执行前面，不存在则外层查询不执行；
17.带in关键字的子查询
mysql> select num from tmp14 where num in (select officeCode from office where city = 'wuhan'); Empty set (0.04 sec)
查询外层数据在子查询语句中是否存在的值；
18.带比较的运算符子查询 = != >= <=
mysql> select num from tmp14 where num = (select officeCode from office where officeCode=1); +------+ | num | +------+ | 1 | +------+ 1 row in set (0.00 sec)
19.合并查询结果 union union all
mysql> select num from tmp14 where num<100 -> union all -> select num from tmp14 where num in (14,63); +------+ | num | +------+ | 64 | | 14 | | 1 | | 14 | +------+ 4 rows in set (0.01 sec)
union会删除重复的结果，而unionall 会显示全部的结果；
20.使用正则表达式，关键字REGEXP；
mysql> select num from tmp14 where num REGEXP '^6'; +------+ | num | +------+ | 64 | +------+ 1 row in set (0.04 sec)