MySQL学习——SQL查询语句（一）

查询数据是指从数据库中获取所需要的数据，查询数据是数据库操作中最常用也是最重要的操作，用户可以根据对数据的需求，使用不同的查询方式，通过不同的查询方式可以获得不同的数据，在MySQL中使用select语句进行查询，本篇内容主要包括：

1.查询语句的基本语法；

2.在单表上查询数据；

3.使用聚合函数查询数据；

4.多表联合查询；

5.子查询；

6.合并查询结果；

7.为表和字段取别名；

8.使用正则表达式查询；

1.基本查询语句

　　select语句基本语法格式：

select 属性列表
         from  表名和视图列表
         where 条件表达式1
         group by  属性1   【HAVING  条件表达式2】
         order by  属性2  【ASC |  DESC】


注：属性列表参数表示需要查询的字段名，
      表名和视图列表 参数表示从哪查，可以有多个
      属性1 表示按该字段中的数据进行分组
      属性2 参数该字段中的数据进行排序（ASC 表示升序，DESC表示降序，默认升序）


例：

mysql> select num,d_id,name,age,sex,homeaddr from examplee;
+------+------+--------+------+------+----------+
| num | d_id | name | age | sex | homeaddr |
+------+------+--------+------+------+----------+
| 1 | 1001 | aa | 24 | w | abc |
| 3 | 1003 | cxsa | 25 | w | abc |
| 2 | 1002 | cxsa | 21 | m | abc |
| 4 | 1004 | ccdxsa | 27 | w | abc |
+------+------+--------+------+------+----------+
4 rows in set (0.00 sec)

mysql> select num,d_id,name,age,sex,homeaddr from examplee
-> where age<26
-> order by d_id DESC;
+------+------+------+------+------+----------+
| num | d_id | name | age | sex | homeaddr |
+------+------+------+------+------+----------+
| 3 | 1003 | cxsa | 25 | w | abc |
| 2 | 1002 | cxsa | 21 | m | abc |
| 1 | 1001 | aa | 24 | w | abc |
+------+------+------+------+------+----------+
3 rows in set (0.04 sec)

mysql>

2.单表查询

2.1 查询说有字段

1.列出表的所有字段
2.使用 * 查询所有字段


mysql> select * from examplee;
+------+------+--------+------+------+----------+
| num  | d_id | name   | age  | sex  | homeaddr |
+------+------+--------+------+------+----------+
|    1 | 1001 | aa     |   24 | w    | abc      |
|    3 | 1003 | cxsa   |   25 | w    | abc      |
|    2 | 1002 | cxsa   |   21 | m    | abc      |
|    4 | 1004 | ccdxsa |   27 | w    | abc      |
+------+------+--------+------+------+----------+
4 rows in set (0.00 sec)

mysql>

2.2 查询指定字段

mysql> select num from examplee;
+------+
| num  |
+------+
|    1 |
|    3 |
|    2 |
|    4 |
+------+
4 rows in set (0.00 sec)

mysql>

2.3 查询指定记录

#查询id=1001的数据
mysql> select * from examplee where d_id=1001;
+------+------+------+------+------+----------+
| num  | d_id | name | age  | sex  | homeaddr |
+------+------+------+------+------+----------+
|    1 | 1001 | aa   |   24 | w    | abc      |
+------+------+------+------+------+----------+
1 row in set (0.00 sec)

mysql>

注：where字句常用的查询条件

 

 2.4 带IN关键字的查询

　　IN关键字可以判断某个字段的值是否在指定的集合中，如果满足查询条件该记录将被查出来，如果不满足则查询失败，查询无果

　　格式：【NOT】 IN （元素1，元素2……元素n）

mysql> select * from examplee where d_id in(1001,1004);
+------+------+--------+------+------+----------+
| num  | d_id | name   | age  | sex  | homeaddr |
+------+------+--------+------+------+----------+
|    1 | 1001 | aa     |   24 | w    | abc      |
|    4 | 1004 | ccdxsa |   27 | w    | abc      |
+------+------+--------+------+------+----------+
2 rows in set (0.00 sec)

mysql>

mysql> select * from examplee where name not in('aa','cxsa');
+------+------+--------+------+------+----------+
| num  | d_id | name   | age  | sex  | homeaddr |
+------+------+--------+------+------+----------+
|    4 | 1004 | ccdxsa |   27 | w    | abc      |
+------+------+--------+------+------+----------+
1 row in set (0.00 sec)

mysql>

2.5 带 BETWEEN AND的范围查询

　　between and关键字可以判断某个字段的值是否在指定的范围内，

　　格式：【NOT】 BETWWEN 取值1 AND 取值2

mysql> select * from examplee where age between 15 and 25;
+------+------+------+------+------+----------+
| num  | d_id | name | age  | sex  | homeaddr |
+------+------+------+------+------+----------+
|    1 | 1001 | aa   |   24 | w    | abc      |
|    3 | 1003 | cxsa |   25 | w    | abc      |
|    2 | 1002 | cxsa |   21 | m    | abc      |
+------+------+------+------+------+----------+
3 rows in set (0.00 sec)

mysql>


mysql> select * from examplee where age not between 15 and 25;
+------+------+--------+------+------+----------+
| num  | d_id | name   | age  | sex  | homeaddr |
+------+------+--------+------+------+----------+
|    4 | 1004 | ccdxsa |   27 | w    | abc      |
+------+------+--------+------+------+----------+
1 row in set (0.00 sec)

mysql>

2.6 带LIKE的字符匹配查询

　　LIKE关键字可以匹配字符串是否相等，意思就是长得像……

　　格式： 【NOT】 LIKE ‘字符串’

mysql> select * from examplee where name like 'a%';
+------+------+------+------+------+----------+
| num  | d_id | name | age  | sex  | homeaddr |
+------+------+------+------+------+----------+
|    1 | 1001 | aa   |   24 | w    | abc      |
+------+------+------+------+------+----------+
1 row in set (0.00 sec)

mysql>



注：‘%’可以代表任意长度的字符串，长度可以为0
    ‘—’只能表示单个字符

2.7 查询空值

 IS NULL 关键字可以用来判断字段的值是否为空

格式：IS 【NOT】 NULL

mysql> select * from examplee where homeaddr is null;
Empty set (0.00 sec)

mysql>

2.8 带AND的多条件查询

mysql> select * from examplee where d_id=1001 and sex like 'w';
+------+------+------+------+------+----------+
| num  | d_id | name | age  | sex  | homeaddr |
+------+------+------+------+------+----------+
|    1 | 1001 | aa   |   24 | w    | abc      |
+------+------+------+------+------+----------+
1 row in set (0.00 sec)

mysql>


mysql>
mysql> select * from examplee where d_id<1004 and age>24 and sex='w';
+------+------+------+------+------+----------+
| num  | d_id | name | age  | sex  | homeaddr |
+------+------+------+------+------+----------+
|    3 | 1003 | cxsa |   25 | w    | abc      |
+------+------+------+------+------+----------+
1 row in set (0.00 sec)

mysql>

　　

2.9 带OR的多条件查询

mysql> select * from examplee where d_id<1002 or sex like 'w';
+------+------+--------+------+------+----------+
| num  | d_id | name   | age  | sex  | homeaddr |
+------+------+--------+------+------+----------+
|    1 | 1001 | aa     |   24 | w    | abc      |
|    3 | 1003 | cxsa   |   25 | w    | abc      |
|    4 | 1004 | ccdxsa |   27 | w    | abc      |
+------+------+--------+------+------+----------+
3 rows in set (0.00 sec)

mysql>


mysql> select * from examplee where num in(1,2,3) or age between 20 and 27 or homeaddr like 'a%';
+------+------+--------+------+------+----------+
| num  | d_id | name   | age  | sex  | homeaddr |
+------+------+--------+------+------+----------+
|    1 | 1001 | aa     |   24 | w    | abc      |
|    3 | 1003 | cxsa   |   25 | w    | abc      |
|    2 | 1002 | cxsa   |   21 | m    | abc      |
|    4 | 1004 | ccdxsa |   27 | w    | abc      |
+------+------+--------+------+------+----------+
4 rows in set (0.00 sec)

mysql>

2.10 查询结果不重复

　　如果表中的某些字段上没有唯一性约束，这些字段可能存在重复的值

　　select 语句中使用 DISTINCT关键字来消除重复的记录

　　格式： select DISTINCT 属性名

mysql>
mysql> select DISTINCT d_id from examplee;
+------+
| d_id |
+------+
| 1001 |
| 1003 |
| 1002 |
| 1004 |
+------+
4 rows in set (0.00 sec)

mysql>

2.11 对查询结果进行排序

从表中查询出来的数据可能是无序的，或者其排序顺序不是用户所期望的顺序，为了使查询结果满足用户的需求，可以使用 order by关键字记录进行排序；

　　格式：ORDER BY 属性名 【ASC（升序） | DESC（降序）】

mysql>
mysql> select * from examplee;
+------+------+--------+------+------+----------+
| num  | d_id | name   | age  | sex  | homeaddr |
+------+------+--------+------+------+----------+
|    1 | 1001 | aa     |   24 | w    | abc      |
|    3 | 1003 | cxsa   |   25 | w    | abc      |
|    2 | 1002 | cxsa   |   21 | m    | abc      |
|    4 | 1004 | ccdxsa |   27 | w    | abc      |
+------+------+--------+------+------+----------+
4 rows in set (0.00 sec)

mysql> select * from examplee order by age ASC;
+------+------+--------+------+------+----------+
| num  | d_id | name   | age  | sex  | homeaddr |
+------+------+--------+------+------+----------+
|    2 | 1002 | cxsa   |   21 | m    | abc      |
|    1 | 1001 | aa     |   24 | w    | abc      |
|    3 | 1003 | cxsa   |   25 | w    | abc      |
|    4 | 1004 | ccdxsa |   27 | w    | abc      |
+------+------+--------+------+------+----------+
4 rows in set (0.00 sec)

mysql>

mysql>
mysql> select * from examplee order by d_id ASC,age DESC;
+------+------+--------+------+------+----------+
| num | d_id | name | age | sex | homeaddr |
+------+------+--------+------+------+----------+
| 1 | 1001 | aa | 24 | w | abc |
| 2 | 1002 | cxsa | 21 | m | abc |
| 3 | 1003 | cxsa | 25 | w | abc |
| 4 | 1004 | ccdxsa | 27 | w | abc |
+------+------+--------+------+------+----------+
4 rows in set (0.00 sec)

mysql> update examplee set d_id = 1001 where num = 2;
Query OK, 1 row affected (0.09 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from examplee order by d_id ASC,age DESC;
+------+------+--------+------+------+----------+
| num | d_id | name | age | sex | homeaddr |
+------+------+--------+------+------+----------+
| 1 | 1001 | aa | 24 | w | abc |
| 2 | 1001 | cxsa | 21 | m | abc |
| 3 | 1003 | cxsa | 25 | w | abc |
| 4 | 1004 | ccdxsa | 27 | w | abc |
+------+------+--------+------+------+----------+
4 rows in set (0.00 sec)

mysql>

 

2.12 分组查询

group by 关键字可以将查询结果按某个字段或者多个字段进行分组，字段相等的为一组

格式： GROUP BY 属性名 【HAVING 条件表达式】【WITH ROLLUP】

mysql> select sex,count(*) from examplee group by sex;
+------+----------+
| sex  | count(*) |
+------+----------+
| m    |        1 |
| w    |        3 |
+------+----------+
2 rows in set (0.00 sec)

mysql>

 

2.13 用limit限制查询结果的数量（俩中方式）

　查询数据时，可能会查出很多的记录，而用户需要的记录可能只是很少一部分，这就需要来限制查询结果的数量。

1.不指定初始位置
查询examplee表的所有记录，但只显示前俩条；

mysql> select * from examplee limit 2;
+------+------+------+------+------+----------+
| num  | d_id | name | age  | sex  | homeaddr |
+------+------+------+------+------+----------+
|    1 | 1001 | aa   |   24 | w    | abc      |
|    3 | 1003 | cxsa |   25 | w    | abc      |
+------+------+------+------+------+----------+
2 rows in set (0.00 sec)

mysql>


2.指定初始位置：
limit  关键字可以指定从哪条记录开始显示，并且可以指定显示多少条记录；
  格式：limit  初始位置，记录数




mysql> select * from examplee limit 1,2;
+------+------+------+------+------+----------+
| num  | d_id | name | age  | sex  | homeaddr |
+------+------+------+------+------+----------+
|    3 | 1003 | cxsa |   25 | w    | abc      |
|    2 | 1001 | cxsa |   21 | m    | abc      |
+------+------+------+------+------+----------+
2 rows in set (0.00 sec)

mysql>