mysql查询

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)