数据的查询-子句,使用 select 查询表数据时,需要思考:

  • 想查询什么;
  • 从哪里查询;

一、 数据的去重-distinct

查询去重 distinct 关键字,放在 select 的后面,所有待查询列名的前面。关键字 distinct 是对查询的所有列整体进行去重,而不是某一列。

例如:

1
select distinct name from student;

是对 name 值相同的数据进行去重;而:

1
select distinct name,age,sex from student;

是对 name、age、sex 三者值都相同的数据进行去重。

二、限制结果行数-limit

select 返回所有匹配的数据,使用 limit 子句可以限制结果的最多可达的行数。该子句有两种格式:

1.仅指定查询数据后,最多返回的行数,默认从第一行开始算:

1
select name,age from student limit 5;

2.指定查询结果的起始行和最多返回的行数:

1
select name,age from student limit 2,5;

表示从查询结果的第三(2+1)行开始算,返回最多五行的数据。

注意: limit 的起始行是从 0 开始计数的,即查询出来的第一行为行 0 而非行 1。

三、数据的排序-order by

查询数据时默认是没有明确的排序规则的,乱序的;(这里的乱序并不是指随机,而是根据表中数据的插入和更新的时间进行排序的,即最终看起来会是乱序的。)

使用 order by 字句进行排序,如对单个列排序:

1
select student_no,name,age from student order by student_no asc;

对多个列进行排序时,先按照写在前面的规则排序;若有数据前面的规则相同,则对这部分数据在原来顺序的基础上再按照下一个规则进行排序。如:

1
select student_no,name,age from student order by age desc,student_no;

先按照 age 进行降序排列;若有数据 age 相同,则对它们在原来的顺序基础上,按照 student_no 进行升序排列。注意:

  • order by 子句可以和 limit 子句结合,得出最值或前几列。order by 子句应位于 from 子句之后,limit 子句之前。
  • order by 子句中使用的列可以为 select 中要查询的,也可以不是;
  • 排序依据的的列名后面加的是排序的方向,如: asc(顺序),desc(倒序);如果不加,则默认为 asc。
  • asc:顺序,升序。数值:递增;字母:自然顺序(a–>z);
  • desc:倒序,降序。数值:递减;字母:自然反序(z–>a);

四、字符校对(排序)规则

注意: 排序时字母的大小写是怎么区分的?对中文汉字又是怎么排序的呢?这时就需要用到数据库(表)的字符校对规则(排序规则)了:

  • collate:字符校对规则,字符序,定义了字符的比较排序规则。它们以其相关的字符集名开始,通常包括一个语言名,并且以 _ci_general_ci_cs 或者 _bin 结束。例如,字符集为 utf8 的话,则对应的校对排序规则分别为 utf8_ciutf8_csutf8_bin
  • _ci_general_ci 结尾的校对排序规则,对字母大小写不敏感;有:a=A > B=b;相应的,规则 _cs 表示大小写敏感。
  • _bin(二元法,二进制编码)结尾的校对排序规则,对字母大小写和汉字敏感;对字母有:A>B>C…>a>b>c;对汉字虽然可以排序,但却是按照二进制编码排序的,人眼不易区分顺序。

五、数据的过滤-where

where 子句应位于 from 子句之后,order by 子句之前。

1.过滤的比较规则

where 子句的条件中,若比较的是字符串,则比较规则依赖于表的字符校对规则 collate。如下面的例子:

1
select student_no,name,age from student where name='zhangsan';

如果 student 表的字符校对规则为 _ci 格式的,则不区分大小写,即 zhangsan 与 ZhangSan 是等价的。

2.between…and…

在 where 子句中,可用 between 操作符与 and 连用过滤某个范围内的值,其效果相当于 n <= x <= m。如:

1
select student_no,name,age from student where age between 17 and 25 order by age asc limit 10;

会过滤所有年龄在 17 岁至 25 岁(包含 17 岁和 25 岁)的学生信息,并按照年龄升序排序,取前 10 条数据返回。

3.and 与 or 的操作次序

在 where 子句的条件中,若同时有 and 和 or 操作,数据库会优先处理 and 操作符,实际查询的结果可能会与我们预期的有所不同。正确的方法是,在 and 和 or 操作符有多个或者混合存在时,应该使用圆括号明确我们预期的比较顺序。

例如,要查询专业为 IS 或者 CS,年龄大于 19 岁的学生信息。查询语句如下:

1
select * from student where profession='IS' or profession='CS' and age>19;

数据库会先执行条件 profession='CS' and age>19 ,再与条件 profession='IS' 相结合;而不是预期的先执行 profession='IS' or profession='CS' 。正确的查询语句为:

1
select * from student where (profession='IS' or profession='CS') and age>19;

4.操作符 in

可以使用 in 操作符来指定条件范围,范围中的每个条件都可以进行匹配,条件范围放在一对圆括号中,用逗号分隔。如:

1
select * from student where profession in ('IS','CS') and age>19;
  • in 操作符与 or 操作符具有相似的功能,当 or 的条件较多时,可直接用 in 操作符代替,且计算的次序也更容易管理;
  • 操作符 in 一般比 or 执行的更快;
  • in 操作符的条件范围可以是其它 select 语句的返回结果;

使用 not 操作符可对 in、between、exists 子句的结果取反。

5.操作符 like 与通配符

like 操作符后跟 搜索模式(search pattern),搜索模式由字面值、通配符或者两者组合构成。搜索模式的匹配规则也依赖于表的字符校对规则 collate。

通配符可以出现在搜索模式的开识、中间和末尾部分,其分别具有不同的意义。

百分号(%)通配符表示任何字符(包含没有字符)出现任意次数(包含 0 次),下划线(_)通配符只匹配单个字符(只能是 1 个字符,不能是 0 个或多个,一个汉字也是一个字符)。例:

1
2
3
select * from student where name like 'zhang%';

select * from student where name like '李_';

使用通配符搜索会比较低效,应尽量避免使用;在使用通配符的时候,也应尽量避免把通配符放在搜素模式的开始处。

6.正则表达式过滤

在 where 子句中使用 regexp 操作符,进行正则表达式匹配、过滤数据。

例如,查询年龄为 10 至 19 岁的学生信息:

1
select * from student where age regexp '1[0-9]';

六、分组数据

1.创建分组:group by

使用 group by 子句的一些重要规则

  1. 除聚集函数外,select 语句中的每个列都必须在 group by 子句中给出;(注:从 SQL99 标准开始,也可以是 select 子句中的字段直接依赖于 group by 子句中的字段,比如主键)
  2. 如果分组列中具有 null 值,则所有的 null 将分为一组返回;
  3. group by 子句应位于 where 子句之后,order by 子句之前。
  4. 如果要对分组后的数据进行排序返回,应该使用 order by 子句,不应依赖默认的分组排序。

例如:

1
2
3
4
5
6
7
8
9
10
mysql> select cust_email,count(*) from customers group by cust_email;           
+---------------------+----------+
| cust_email | count(*) |
+---------------------+----------+
| NULL | 2 |
| rabbit@wascally.com | 1 |
| sam@yosemite.com | 1 |
| ylee@coyote.com | 1 |
+---------------------+----------+
4 rows in set (0.00 sec)

2.过滤分组:having

having 子句用于对分组后的数据进行过滤;而 where 子句是在数据分组前进行过滤,其排除的行不包括在分组中。having 子句支持所有 where 子句的操作符,且支持 where 子句不支持的聚集函数。

例如:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
mysql> select vend_id,count(*) from products group by vend_id;
+---------+----------+
| vend_id | count(*) |
+---------+----------+
| 1001 | 3 |
| 1002 | 2 |
| 1003 | 7 |
| 1005 | 2 |
+---------+----------+
4 rows in set (0.00 sec)

mysql> select vend_id,count(*) from products group by vend_id having count(*)>2;
+---------+----------+
| vend_id | count(*) |
+---------+----------+
| 1001 | 3 |
| 1003 | 7 |
+---------+----------+
2 rows in set (0.00 sec)

总结:select 子句顺序

下表列出在 select 语句中子句必须遵循的次序:

序号 子句 说明 是否必须使用
1 select 要返回的列或表达式
2 from 从中检索数据的表 仅在从表选择数据时使用
3 where 行级过滤
4 group by 分组说明 仅在按组计算聚集时使用
5 having 组级过滤
6 order by 输出排序顺序
7 limit 要输出检索的行数

(完)