数据库学习之五：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)。