SELECT 语句的一般格式:
- SELECT [ALL|DISTINCT] <目标列表达式>[别名][,<目标列表达式> [别名]] ...
- FROM <表名/视图名> [别名] [, <表名/视图名> [别名]]... | (<select 语句>) [AS] <别名>
- [WHERE <条件表达式>]
- [GROUP BY <列名1> [HAVING <条件表达式>]]
- [ORDER BY <列名2> [ASC | DESC]];
执行顺序:
from.....where.....group by......habing.....select........order
1. 目标列表达式的可选格式
(1) *
(2)<表名.>*
(3)COUNT([DISTINCT|ALL] *)
(4) [<表名.>]<属性列名表达式>[,[<表名.>]<属性列名表达式>]...
Tips: <属性列名表达式> 可以是由属性列、作用于属性列的聚集函数、属性和常量的任意算术运算(+、-、*、/)组成的运算公式。
eg. 表达式
- mysql> SELECT Sname,2016-Sage FROM Student;
- +--------+-----------+
- | Sname | 2016-Sage |
- +--------+-----------+
- | Jason | 1995 |
- | Taylor | 1995 |
- | dsf | 1995 |
- +--------+-----------+
- 3 rows in set (0.03 sec)
eg. 字符串常量、函数
- mysql> SELECT Sname,'Year of Birth:',2016-Sage,LOWER(Sdept) FROM Student; // 小写字母表示系名
- +--------+----------------+-----------+--------------+
- | Sname | Year of Birth: | 2016-Sage | LOWER(Sdept) |
- +--------+----------------+-----------+--------------+
- | Jason | Year of Birth: | 1995 | ele |
- | Taylor | Year of Birth: | 1995 | ele |
- | dsf | Year of Birth: | 1995 | ele |
- +--------+----------------+-----------+--------------+
- 3 rows in set (0.05 sec)
eg. 查询时可以指定别名,这对于算术表达式、常量、函数名的目标列表达尤为有用.
- mysql> SELECT Sname NAME,'Year of Birth:' BIRTH,2016-Sage BIRTHDAY,LOWER(Sdept) DEPARTMANT FROM Student;
- +--------+----------------+----------+------------+
- | NAME | BIRTH | BIRTHDAY | DEPARTMANT | // 指定别名,界面友好性。
- +--------+----------------+----------+------------+
- | Jason | Year of Birth: | 1995 | ele |
- | Taylor | Year of Birth: | 1995 | ele |
- | dsf | Year of Birth: | 1995 | ele |
- +--------+----------------+----------+------------+
- 3 rows in set (0.00 sec)
2.聚集函数的一般格式
COUNT(*) 统计元组的个数
COUNT([DISTINCT|ALL] <列名>) 统计一列中值的个数
SUM([DISTINCT|ALL] <列名>) 计算一列值的总和(此列必须是数值型)
AVG([DISTINCT|ALL] <列名>) 计算一列值的平均值(此列必须是数值型)
MAX([DISTINCT|ALL] <列名>) 求一列值中的最大值
MIN([DISTINCT|ALL] <列名>) 求一列值中的最小值
DISTINC 短语,指明在计算时要取消指定列中的重复值。默认为ALL,表示不取消重复值。
当聚集函数遇到空值时,除COUNT(*)外,都跳过空值而只处理非空值。 COUNT(*)对元组进行计数时,包含空值所在的元组。
注意: WHERE 子句中是不能用聚集函数作为条件表达式的。 聚集函数只能用于 SELECT子句 和 GROUP BY 中的 HAVING 子句。
- SELECT COUNT(*) FROM Student; // 查询学生总人数
- mysql> SELECT COUNT(*) FROM Student;+----------+| COUNT(*) |+----------+| 3 |+----------+1 row in set (0.04 sec)
- SELECT COUNT(DISTINCT Sno) FROM SC; // 查询选修了课程的学生人数
- mysql> SELECT COUNT(DISTINCT Sno) FROM Sc;+---------------------+| COUNT(DISTINCT Sno) |+---------------------+| 5 |+---------------------+1 row in set (0.00 sec)
- //学生每选一门课在SC表中都有一条相应的记录. 一个学生选多门课,SC表会记录多次,COUNT函数必须用DISTINCT短语去掉重复的记录
3. WHERE 子句的条件表达式的可选格式
(1) 比较大小
常见运算符: =(等于),>(大于),<(小于),>=,<=, !=或<>,!>(不大于),!<(不小于).
(2)确定范围
[NOT] BETWEEN 下限 AND 上限
eg.
查询年龄在20~23岁之间的学生的姓名、系名和年龄
- SELECT Sname,Sdept,Sage FROM WHERE Sage BETWEEN 20 AND 24;
(3)确定集合
IN 用来查找属性值属于指定集合的元组
NOT IN 属性不属于任何指定集合的元组
eg.
查找 CS系,ELE系,IS系 中的学生的姓名、性别
- SELECT Sname,Ssex FROM Student WHERE Sdept IN ('CS','ELE',IS);
(4) 字符匹配
LIKE 用来进行字符串的匹配。 查找指定的属性列值与<匹配字符串>相匹配的元组
语法格式:
[NOT] LIKE <'匹配字符串> [ESCAPE <'换码字符'>]
% 代表任意长度(>=0个)的字符串
_ 下划线代表任意单个字符. eg. a_b 表示以a开头,以b结尾的长度为3的任意字符串.
eg.
查找学号为3120开头的学生的信息
- mysql> SELECT * FROM Student WHERE Sno LIKE '3120%';
- +--------+--------+------+------+-------+
- | Sno | Sname | Ssex | Sage | Sdept |
- +--------+--------+------+------+-------+
- | 312001 | Jason | fe | 21 | ELE |
- | 312028 | Taylor | fe | 21 | ELE |
- +--------+--------+------+------+-------+
- 2 rows in set (0.00 sec)
如果用户要查询的字符串本身就含有通配符 % 或 _ ,这时就要用 ESCAPE '<换码字符>' 短语对通配符进行转义了
eg. 以 & 作为转义字符。mysql不支持'\'
- mysql> select Cno,Cname,Ccredit from Course where Cname LIKE 'DB&_Design' ESCAPE '&';
- +-----+-----------+---------+
- | Cno | Cname | Ccredit |
- +-----+-----------+---------+
- | 004 | DB_Design | 6 |
- +-----+-----------+---------+
- 1 row in set (0.00 sec)
(5) 涉及空值的查询
IS NULL // 为空
IS NOT NULL // 不为空
eg.
- mysql> select Sno,Cno from Sc where Grade is not null;
- +--------+-----+
- | Sno | Cno |
- +--------+-----+
- | 20123 | 004 |
- | 20132 | 001 |
- | 312001 | 001 |
- | 312028 | 003 |
- | 312029 | 006 |
- +--------+-----+
- 5 rows in set (0.00 sec)
(6) 多重条件查询
AND 和 OR 用来连接多个查询条件. AND 的优先级高于 OR,用括号可以改变优先级
- mysql> SELECT Sname FROM Student WHERE Sdept='ELE' AND Sage<22;
- +--------+
- | Sname |
- +--------+
- | Jason |
- | Taylor |
- | dsf |
- +--------+
- 3 rows in set (0.00 sec)
4. ORDER BY 子句 : 对查询结果按照一个或多个属性列的升序(ASC)或降序(DESC)排列,默认升序.
- mysql> SELECT * FROM Student ORDER BY Sage DESC; // DESC降序
- +--------+--------+------+------+-------+
- | Sno | Sname | Ssex | Sage | Sdept |
- +--------+--------+------+------+-------+
- | 31223 | dsf | ma | 23 | IS |
- | 312028 | Taylor | fe | 21 | ELE |
- | 312001 | Jason | fe | 19 | CS |
- +--------+--------+------+------+-------+
- 3 rows in set (0.00 sec)
5. GROUP BY 子句 : 将查询结果按某一列或多列的值分组,属性值相等的为一组。 对查询结果进行分组是为了细化聚集函数的作用对象,分组后,聚集函数将作用于每一个分组,即每一组都有一个函数值。
- mysql> SELECT Cno,COUNT(Sno) FROM Sc GROUP BY Cno; // 按照属性 Cno 进行分组,再对每一个分组计算 Sno的个数
- +-----+------------+
- | Cno | COUNT(Sno) |
- +-----+------------+
- | 001 | 2 |
- | 003 | 1 |
- | 004 | 1 |
- | 006 | 1 |
- +-----+------------+
如果分组后还要求按一定的条件对这些分组进行筛选,最终只输出满足条件的组,则使用 HAVING 短语指定筛选条件。
- mysql> select * from Sc;
- +--------+------+-------+
- | Sno | Cno | Grade |
- +--------+------+-------+
- | 20123 | 004 | 96 |
- | 20132 | 001 | 88 |
- | 312001 | 001 | 98 |
- | 312028 | 003 | 99 |
- | 312028 | 004 | 100 |
- | 312028 | 006 | 100 |
- | 312028 | 009 | 100 |
- | 312028 | 010] | 100 |
- | 312029 | 006 | 100 |
- +--------+------+-------+
- 9 rows in set (0.00 sec)
- mysql> SELECT Sno FROM Sc GROUP BY Sno HAVING COUNT(*) > 3; // 先用 GROUP BY 子句按 Sno 进行分组,再用聚集函数对每一个分组计数,HAVING短语给出选择组的条件,满足条件(元组个数>3)的组才会被选出来。
- +--------+
- | Sno |
- +--------+
- | 312028 |
- +--------+
- 1 row in set (0.00 sec)
注意: WHERE 子句与 HAVING 短语的区别在于 作用对象不同,WHERE 子句作用于 基本表或者视图,从中选择满足条件的元组。
HAVING 短语作用于分组,从中选出满足条件的组。
eg. 查询平均成绩大于等于90分的学生学号和平均成绩,
SELECT Sno,AVG(Grade) FROM Sc GROUP BY Sno HAVING AVG(Grade)>=90;
// 先按学号进行分组,得出每一个学生课程组,再计算平均值,根据 HAVING 条件,输出平均分>=90分的分组.
- mysql> SELECT Sno,AVG(Grade) FROM Sc GROUP BY Sno HAVING AVG(Grade)>=90;
- +--------+------------+
- | Sno | AVG(Grade) |
- +--------+------------+
- | 20123 | 96.0000 |
- | 312001 | 98.0000 |
- | 312028 | 99.8000 |
- | 312029 | 100.0000 |
- +--------+------------+
6. 嵌套查询
在 SQL 语言中, 一个 SELECT-FROM-WHERE 语句成为一个查询块。 将一个查询块嵌套在另一个查询块的 WHERE 子句或 HAVING短语的条件中 的查询称为嵌套查询(nested query)。
- select Sname from Student where Sno IN // 外层查询或父查询
- (select Sno from Sc where Cno='006'); // 内层查询或子查询
SQL 允许多层嵌套查询,即一个子查询中还可以嵌套其他子查询。
注意: 子查询的 SELECT 语句中不能使用 ORDER BY 子句, ORDER BY 子句只能对最终查询结果排序。
嵌套查询使用户可以用多个简单的查询构成复杂的查询,从而增强SQL的查询能力。 以层层嵌套的方式来构造程序正是 SQL 中 “结构化” 的含义所在。
(1)带有 IN 谓词的子查询
在嵌套查询中,子查询的结果往往是 元组的集合,所以 IN 在嵌套查询中最经常使用。
eg.
- mysql> SELECT Sno,Sname,Sdept FROM Student WHERE Sdept IN (SELECT Sdept FROM Student WHERE Sname='huangxin'); // 与 huangxin 在同一个系的学生信息
- +--------+---------------+-------+
- | Sno | Sname | Sdept |
- +--------+---------------+-------+
- | 312019 | Linus Travlds | Linux |
- | 312022 | huangxin | Linux |
- +--------+---------------+-------+
- 2 rows in set (0.00 sec)
这种 子查询的条件不依赖于父查询,称为 不相关子查询。( 执行过程: 由里向外处理,即先执行子查询,子查询的结果用于建立父查询的查找条件).
如果子查询的查询条件依赖于父查询,这类子查询称为 相关子查询. 整个查询语句称为 相关嵌套查询(correlated nested query).
(1)带有 比较运算符的 子查询
指的是 父查询与子查询之间用 比较运算符进行连接。当用户能确切知道内层查询返回的是单个值时,可以用(>< >= <= !=或<> )等比较运算符。
eg.
相关子查询, 子查询的 查询条件 x.Sno 依赖于父查询传进来的元组 x,是与父查询相关的,
> 用于连接父查询 、子查询
- mysql> SELECT Sno,Cno FROM Sc x WHERE Grade > (SELECT AVG(Grade) FROM Sc y WHERE y.Sno = x.Sno); // x,y 是表的别名
- +--------+-----+
- | Sno | Cno |
- +--------+-----+
- | 312028 | 004 |
- | 312028 | 006 |
- | 312028 | 009 |
- +--------+-----+
- 3 rows in set (0.00 sec)
(3)带有 ANY(SOME) 或 ALL 谓词的子查询
子查询返回单值时,可以用比较运算符,但返回 多个值时 要用ANY、ALL来进行连接。 而是用ANY/ALL 时,必须同时使用 比较运算符。
eg. 查询 ELE 系 中任意一个年龄小于ELE系学生的信息
- mysql> SELECT Sname,Sage,Sdept FROM Student WHERE Sage<ANY(SELECT Sage FROM Student WHERE Sdept='ELE') AND Sdept != 'ELE';
- +-------+------+-------+
- | Sname | Sage | Sdept |
- +-------+------+-------+
- | Jason | 19 | CS |
- +-------+------+-------+
- 1 row in set (0.00 sec)
也可以用聚集函数实现:
- mysql> SELECT Sname,Sage,Sdept FROM Student WHERE Sage < (SELECT MAX(Sage) FROM Student WHERE Sdept='ELE') AND Sdept != 'ELE';
- +-------+------+-------+
- | Sname | Sage | Sdept |
- +-------+------+-------+
- | Jason | 19 | CS |
- +-------+------+-------+
- 1 row in set (0.00 sec)
小结: 使用聚集函数实现子查询通常比 直接用 ANY/ALL 查询效率要高!
- =ANY 等价于 IN
- <ANY 等价于 <MAX
- !=ALL 等价于 NOT IN
- <ALL 等价于 <MIN
(4) 带有 EXISTS 的子查询
这种子查询不返回任何数据,只产生 逻辑真值‘true’ 或逻辑假值 ‘false’.
eg. 相关子查询,子查询依赖于父查询的 Student.Sno 进行条件判断,若成功,则 WHERE子句 返回 '真',对“父”的每一个元组,重复完成这一过程。
- mysql> SELECT Sname FROM Student WHERE EXISTS (SELECT * FROM Sc WHERE Sno=Student.Sno AND Cno='004');
- +--------+ // WHERE 返回真值后,外层查询从元组中提取 Sname 列
- | Sname |
- +--------+
- | Taylor |
- +--------+
- 1 row in set (0.00 sec)
NOT EXIST : 内层查询结果为空时,则外层的 WHERE 子句返回真值。
- mysql> SELECT Sname FROM Student WHERE NOT EXISTS (SELECT * FROM Sc WHERE Sno=Student.Sno AND Cno='004');
- +---------------+
- | Sname |
- +---------------+
- | dsf |
- | huangxin |
- | Jason |
- | Linus Travlds |
- +---------------+
- 4 rows in set (0.00 sec)
7. 集合查询
SELECT 语句查询的结果是元组的集合,所以多个SELECT 语句的结果可以进行 集合操作。
主要包括: UNION并操作、 INTERSECT交操作、 EXCEPT差操作
注意: 参加集合操作的各查询结果的列数必须相同,对应的数据类型也必须相同。
(1) UINON 会自动去掉重复元组, 如果要保留重复元组则使用 UNION ALL 操作符。
- mysql> select * from Student where Sdept='CS' UNION select * from Student where Sage <=19;
- +--------+-------+------+------+-------+
- | Sno | Sname | Ssex | Sage | Sdept |
- +--------+-------+------+------+-------+
- | 312001 | Jason | fe | 19 | CS |
- +--------+-------+------+------+-------+
- 1 row in set (0.13 sec)
MySQL 不支持集合的 INTERSECT 、EXCEPT 操作。
可以用 WHERE 的 条件句实现:
- mysql> select * from Student where Sage <= 22 AND Sdept='CS'; // 年龄小于22并且是CS系
8. 基于派生表的查询
子查询出现在 FROM 子句中, 这时子查询生成的临时派生表(derived table)成为主查询的查询对象。
eg. mysql 不能指定派生表的属性列,子查询SELECT后面的列名为其默认属性。 可以在子查询指定别名
- mysql> SELECT Sname FROM Student,(SELECT Sno FROM Sc WHERE Cno='001') AS Sc1 WHERE Student.Sno=Sc1.Sno;
- // 派生表,
- +-------+
- | Sname |
- +-------+
- | Jason |
- +-------+
- 1 row in set (0.00 sec)
AS 关键字可以省略,但是必须为 派生关系指定一个别名。
- mysql> SELECT Sname FROM Student,(SELECT Sno www FROM Sc WHERE Cno='001') Sc1 WHERE Student.Sno=Sc1.www;
- +-------+
- | Sname |
- +-------+
- | Jason |
- +-------+
- 1 row in set (0.04 sec)