mysql数据库的SELECT高级应用
五、select高级应用
1、课程大纲
• 开发环境准备 • select语句的基本语法格式 • where子句 • order by子句 • limit子句 • 多表连接查询（join、using） • 集合操作（union） • group by与having字句 • 子查询
2、开发环境
• 导入world.sql • 创建用户，使用sqlyog登录数据库
3、select语法环境
help select;
select
[all | distinct | distinctrow ]
select_expr [, select_expr ...]
[from table_references
[where where_condition]
[group by {col_name | expr | position}
[asc | desc], ... [with rollup]]
[having where_condition]
[order by {col_name | expr | position}
[asc | desc], ...]
[limit {[offset,] row_count | row_count offset offset}]
[for update | lock in share mode]]
4、where子句
• 基本语法
select *|{[distinct] column|select_expr [alias], ...]}
[from [database.]table]
[where conditions];
• where条件又叫做过滤条件，它从from子句的中间结果中去掉所有 条件conditions不为true（而为false或者null）的行
• where子句跟在from子句后面
• 不能在where子句中使用列别名
例1：where字句的基本使用
select * from world.`city` where countrycode='chn'; or select * from world.`city` where countrycode='chn';
注意：
where中出现的字符串和日期字面量必须使用引号括起来
这里，字符串字面量写成大写或小写结果都一样，即不区分大小写进行查询。
这和oracle不同，oracle中where条件中的字面量是区分大小写的
例2：where字句中的逻辑操作符：
select * from world.`city` where countrycode='chn' and district = 'anhui';
• 逻辑操作符介绍：
– and
逻辑与。只有当所有的子条件都为true时，and才返回true。否则返回false或null
– or
逻辑或。只要有一个子条件为true，or就返回true。否则返回false或null
– not
逻辑非。如果子条件为true，则返回false；如果子条件为false，则返回true
– xor
逻辑异或。当一个子条件为true而另一个子条件为false时，其结果为true；
当两个条件都为true或都为false时，结果为false。否则，结果为null
例3 ：where字句中的范围比较：
select * from world.`city` where population between 100000 and 200000 ;
• 例4：where字句中的in
select * from city
where countrycode in ('chn','jpn');
例5：where字句中的like
– 语法：
like ‘匹配模式字符串’
– 实现模式匹配查询或者模糊查询：
测试一个列值是否匹配给出的模式
– 在‘匹配模式字符串’中，可以有两个具有特殊含义的通配字符：
%：表示0个或者任意多个字符
_：只表示一个任意字符
select * from city where countrycode like 'ch%';
5、order by子句
• order by子句用来排序行
• 如果select语句中没有order by子句，那么结果集中行的顺序是
不可预料的
• 语法：
select expr
from table
[where condition(s)]
[order by {column, expr, numeric_position} [asc|desc]];
• 其中：
– asc：执行升序排序。默认值
– desc：执行降序排序
– order by子句一般在select语句的最后面
例1： 基本使用
select * from city order by population;
• 例2：多个排序条件
select * from city order by population,countrycode;
• 例3：以select字句列编号排序
select * from city order by 5; 按照第5列进行排序。
• 例4：desc asc
select * from city order by 5 desc;
• 例5：null值的排序
在mysql中，把null值当做一列值中的最小值对待。因此，升序排序时，它出现在最前面
6、limit子句
mysql特有的子句。
• 它是select语句中的最后一个子句（在order by后面）。
• 它用来表示从结果集中选取最前面或最后面的几行。
• 偏移量offset的最小值为0。
• 语法：
limit <获取的行数> [offset <跳过的行数>]
或者 limit [<跳过的行数>,] <获取的行数>
select * from city order by 5 desc limit 4;
注：先按照人口数量进行降序排序，然后使用limit从中挑出最前面的4行。
如果没有order by子句，返回的4行就是不可预料的。
7、多表连接查询
传统的连接写法（使用where）
select name,ci.countrycode ,cl.language ,ci.population from city ci , countrylanguage cl where ci.`countrycode`=cl.countrycode;
注意：一旦给表定义了别名，那么原始的表名就不能在出现在该语句 的其它子句中了
• natural join子句
• 自动到两张表中查找所有同名同类型的列拿来做连接列，进行相等 连接
select name,countrycode ,language ,population from city natural join countrylanguage where population > 1000000 order by population;
注意：在select子句只能出现一个连接列
使用using子句
select name,countrycode ,language ,population from city join countrylanguage using(countrycode);
8、集合操作
union [distinct] • union all • 语法： select ... union [all | distinct] select ... [union [all | distinct] select ...]
• union用于把两个或者多个select查询的结果集合并成一个
• 进行合并的两个查询，其select列表必须在数量和对应列的数据类型上保持一致
• 默认会去掉两个查询结果集中的重复行
• 默认结果集不排序
• 最终结果集的列名来自于第一个查询的select列表
9、分组操作及分组处理
“group by”从字面意义上理解就是根据“by”指定的规则对数据进行分组， 所谓的分组就是将一个“数据集”划分成若干个“小区域”，然后针对若干个 “小区域”进行数据处理。
• having与where的区别
- where 子句的作用是在对查询结果进行分组前，将不符合where条件的行去掉，即在分
组之前过滤数据，where条件中不能包含聚组函数，使用where条件过滤出特定的行。
- having 子句的作用是筛选满足条件的组，即在分组之后过滤数据，条件中经常包含聚
组函数，使用having 条件过滤出特定的组，也可以使用多个分组标准进行分组。
11、子查询
子查询定义
- 在一个表表达中可以调用另一个表表达式，这个被调用的表表达式叫做子查询（ subquery），我么也称作子选择（subselect）或内嵌选择（inner select）。子查询 的结果传递给调用它的表表达式继续处理。
- 子查询（inner query）先执行，然后执行主查询（outer query）
- 子查询按对返回结果集的调用方法，可分为：where型子查询，from型子查询及exists 型子查询。
• 使用子查询原则
- 一个子查询必须放在圆括号中。
- 将子查询放在比较条件的右边以增加可读性。
子查询不包含 order by 子句。对一个 select 语句只能用一个 order by 子句，并且 如果指定了它就必须放在主 select 语句的最后。
- 在子查询中可以使用两种比较条件：单行运算符(>, =, >=, <, <>, <=) 和多行运算符 (in, any, all)。