MYSQL学习路线（2）

数据查询是数据库的核心操作。SQL提供了SELECT语句进行数据查询，该语句具有灵活的使用方式和丰富的功能。其一般格式为：

SELECT [ALL|DISTINCT]<目标列表达式>[,<目标列表达式>]......
FROM <表名或视图名>[,<表名或视图名>...]|(<SELECT语句>[AS]<别名>)
[WHERE <条件表达式>]
[GROUP BY <列名1>[HAVING <条件表达式>]]
[ORDER BY <列名2>[ASC|DESC]];

整个SELECT语句的含义是，根据WHERE子句的条件表达式从FROM子句指定的基本表、视图或派生表中找出满足条件的元组，再按SELECT子句中的目标列表表达式选出元组中的属性值形成结果表。如果有GROUP BY子句，则将结果按<列名1>的值进行分组，该属性列值相等的元组为一个组。通常会在每组中作用聚集函数。如果GROUP BY子句带HAVING短语，则只有满足指定条件的组才予以输出。

如果有ORDER BY子句，则结果表还要按<列名2>的值的升序或降序排序。

1、单表查询

案列(1)：

/*查询全体学生的学号与姓名*/
SELECT Sno,Sname FROM Student;

/*查询全体学生的姓名及其出生年份*/
/*2014-Sage是一个算数表达式*/
SELECT Sname,2014-Sage FROM Student;

/*查询全体学生的姓名、出生日期和所在的院系，要求用小写字母表示系名*/
/*“Year of Birth：”是一个字符常量，所在列值都为Year of Birth：*/
SELECT Sname "Year of Birth:" LOWER(Sdept) FROM Student;

/*设置别名，列名后加空格*/
SELECT Sname NAME,'Year of Birth:' BIRTH,2014-Sage BIRTHDAY FROM Student;

案例(2):

/*消除取值重复的行*/
SELECT DISTINCT Sno FROM SC;

/*查找计算机系全体学生名单*/
SELECT Sname FROM Student WHERE Sdept='CS';

/*查找年龄在20~30岁之间的学生名字、系别和年龄*/
SELECT Sname,Sdept,Sage FROM Student WHERE Sage BETWEEN 20 AND 23；

/*查找计算机科学系、数学系、信息系学生的姓名和性别*/
SELECT Sname,Ssex FROM Student WHERE Sdept IN ('CS','MA','IS');

/*查询刘姓的学生的姓名、学号和性别*/
/*%代表任意长度的字符串*/
SELECT Sname,Sno,Ssex FROM Student WHERE Sname LIKE '刘%';

/*查找姓刘且全名只有两个汉字的学生姓名*/
/*_代表任意单个字符*/
SELECT Sname FROM Student WHERE Sname LIKE '刘_';

/*查找DB_Design课程的课程号和学分*/
/*ESCAPE'\'表示'\'为换码字符*/
SELECT Cno,Ccredit FROM Course WHERE Cname LIKE 'DB\_Design' ESCAPE'\';

/*查所有成绩的学生学号和课程号IS不能用=替代 */
SELECT Sno,Cno FROM SC WHERE Grade IS NOT NULL;

常用的查询条件

查询条件	谓词
比较	=，>，<，>=，<=，!=，<>，!>，!<，NOT+
确定范围	BETWEEN AND，NOT BETWEEN AND
确定集合	IN,NOT IN
字符匹配	LIKE,NOT LIKE
空值	IS NULL,IS NOT NULL
多重条件	AND,OR,NOT

案例(3):

/*ORDER BY子句*/
/*查询选修了3号课程的学生的学号及成绩，查询结果按分数的降序排列*/
SELECT Sno,Grade FROM SC WHERE Cno='3' ORDER BY Grade DESC;

/*聚集函数*/
/*查询学生总人数*/
SELECT COUNT(*) FROM Student;

/*计算选修了课程的学生平均成绩*/
SELECT AVG(Grade) FROM SC WHERE Cno='1';

/*查询学生201215012选修课程的总学分数*/
SELECT SUM(Ccredit) FROM SC,Course WHERE Sno='201215012' AND SC.Sno=Course.Cno;

主要聚集函数

COUNT(*)	统计元组个数
COUNT([DISTINCT | ALL]<列名>)	统计一列中值的个数
SUM([DISTINCT | ALL<列名>])	统计一列值的总和（此列必须是数值类型）
AVG([DISTINCT | ALL<列名>])	计算一列值的平均值（此列必须是数值类型）
MAX([DISTINCT | ALL<列名>])	求一列值中的最大值
MIN([DISTINCT | ALL<列名>])	求一列值中的最小值

如果指定DISTINCT短语，则表示在计算时要取消指定列中的重复值。如果不指定DISTINCT短语或者指定ALL短语（ALL为默认值），则表示不取消重复值。

当聚集函数遇到空值时，除COUNT(*)外，都跳过空值而只处理非空值。COUNT(*)是对元组进行计算、某个元组的一个或部分列取空值不影响COUNT的统计。

WHERE子句中不能用聚集函数作为条件表达式。聚集函数只能用于SELECT 子句和GROUP BY中的HAVING子句。

案例4：GROUP BY

/*查询选修了三门以上课程的学生学号*/
SELECT Sno FROM SC GROUP BY Sno HAVING COUNT(*)>3;

这里先用GROUP BY子句按Sno进行分组，再用聚集函数COUNT对每一组计算。

WHERE子句与HAVING 短语的区别在于作用对象不同。WHERE 子句作用于基本表或视图，从中选择满足条件的元组。HAVING短语作用于组，从中选择满足条件的组。

2、连接查询

若一个查询同时涉及两个以上的表，则称之为连接查询。连接查询是关系数据库中最主要的查询，包括等值连接查询、自然连接查询、非等值连接查询、自身连接查询、外连接查询和复合条件连接查询等。

等值与非等值连接查询：

/*查询每个学生及其选修课程的情况*/
SELECT Student.*,SC.*
FROM Student,SC
WHERE Student.Sno=SC.Sno;/*将Student与SC中同一学生的元组连接起来*/

自然连接查询：

/*紧跟上个例子*/
/*除去Student与CS表中重复的属性*/

SELECT Student.Sno,Sname,Ssex,Sage,Cno,Grade
FROM Student,SC
WHERE Student.Sno=SC.Sno;

自身连接查询：

连接查询不仅可以在两个表之间进行，也可以是一个表与其自己进行连接，称为表的自身连接。

案例：首先要为Course表取两个别名，一个是FIRST，另一个是SECOND。

/*查询每一门课的间接先修课*/
SELECT FIRST.Cno,SECOND.Cpno
FROM Course FIRST,Course SECOND
WHERE FIRST.Cpno=SECOND.Cno;

外连接查询：

有时想以Student表为主体列出每个学生的基本情况及其选课情况。若某个学生没有选课，仍把Student的悬浮元组保存在结果关系中，而在SC表的属性上填空值NULL，这时就需要使用外连接。

SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROM Student LEFT OUTER JOIN SC ON (Student.Sno=SC.Sno);
/*也可以使用USING来去掉结果的重复值：
    FROM Student LEFT OUTER JOIN SC USING(Sno)*/

左外连接列出左边关系中所有的元组（本例Student），右外连接列出右边关系中所有的元组。

多表连接：

/*三个表之间的查询*/
SELECT Student.Sno,Sname,Cname,Grade
FROM Student,SC,Sourse
WHERE Student.Sno=SC.Sno AND SC.Cno=Course.Cno;

3、嵌套查询

在SQL语言中，一个SELECT-FROM-WHERE语句称为一个查询块，将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询。

案例：

SELECT Sname /*外层查询或父查询*/
FROM Student
WHERE Sno IN
    (SELECT Sno /*内层查询或子查询*/
     FROM SC
     WHERE Cno='2');

1、带有IN谓词的子查询

案例：

问题：查询与“刘晨”在同一个系学习的学生。

解决方案：1、确定“刘晨”所在系名。2、查找所有在CS系学习的学生

/*查找在该系的所有的学生*/
SELECT Sno,Sname,Sdept
FROM Student
WHERE Sdept IN
    (  /*查询“刘晨”所在系名*/
     SELECT Sdept
     FROM Student
     WHERE Sname="刘晨")；

本例中，子查询的查询条件不依赖于父查询，称为不相关子查询。一种解决方法是由里向外处理，即先执行子查询，子查询结果用于建立其父查询的查找条件。

2、带有比较运算符的子查询

如果子查询的查询条件依赖于父查询，这类子查询称为相关子查询，整个查询语句称为相关嵌套查询。

/*找出每个学生超过他自己选修课程平均成绩的课程号*/
SELECT Sno,Cno
FROM SC x
WHERE Grade >= (SELECT AVG(Grade)
                FROM SC y
                WHERE y.Sno=x.Sno);

3、带有ANY或ALL谓词的子查询

>ANY	大于子查询结果中的某个值
>ALL	大于子查询结果中的所有值
<ANY	小于子查询结果中的某个值
<ALL	小于子查询结果中的所有值
>=ANY	大于等于子查询结果中的某个值
>=ALL	大于等于子查询结果中的所有值
<=ANY	小于等于子查询结果中的某个值
<=ALL	小于等于子查询结果中的所有值
=ANY	等于子查询结果中的某个值
=ALL	等于子查询结果中的所有值（通常没有实际意义）
!=(或<>)ANY	不等于子查询结果中的某个值
!=(或<>)ALL	不等于子查询结果中的任何一个值

/*查询非计算机科学专业中比计算机科学系任何一个学生年龄小的学生姓名和年龄*/
SELECT Sname,Sage
FROM Student
WHERE Sage<ANY(SELECT Sage
        FROM Student
        WHERE Sdept='CS')
    AND Sdept<>'CS';

4、带有EXISTS谓词的子查询

EXISTS代表存在量词。带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。NO EXISTS不存在。

/*查询所有选修了1号课程的学生姓名*/
SELECT Sname
FROM Student
WHERE EXISTS
    (SELECT *
     FROM SC
     WHERE Sno=Student.Sno AND Cno='1');

由EXISTS引出的子查询，其目标列表达式通常都用“*”，因为带EXISTS的子查询只返回真值或假值，给出列名无实际意义。

/*查询选修了全部课程的学生姓名*/
SELECT Sname
FROM Student
WHERE NOT EXISTS
    (SELECT *
     FROM Course
     WHERE NOT EXISTS
            (SELECT *
             FROM SC
             WHERE Sno=Student.Sno AND Cno=Course.Cno));

SQL没有全称量词，可以将问题换成等价的存在量词形式：查询没有一门课程是他不选修的。

4、集合查询

SELECT语句的查询结果是元组的集合，所以多个SELECT语句的结果可进行集合操作。集合操作主要包括并操作UNION、交操作INTERSECT和差操作EXCEPT。

注意：参加集合操作的各查询结果的列数必须相同；对应项的数据类型也必须相同

/*查询计算机科学系的学生及年龄不大于19岁的学生的并集*/
SELECT *
FROM Student
WHERE Sdept='CS'
UNION
SELECT *
FROM Student
WHERE Sage<=19;

/*查询计算机科学系的学生与年龄不大于19岁的学生的交集*/
SELECT *
FROM Student
WHERE Sdept='CS'
INTERSECT
SELECT *
FROM Student
WHERE Sage<=19;

/*查询计算机科学系的学生与年龄不大于19岁的学生的差集*/
SELECT Sno,Sname
FROM Student
WHERE Sdept='CS'
EXCEPT
SELECT *
FROM Student
WHERE Sage<=19;

5、基于派生表的查询

子查询不仅可以出现在WHERE子句中，还可以出现在FROM子句中，这时子查询生成的临时派生表成为主查询的查询对象。

/*查询每个学生超过他自选修课程平均成绩的课程号*/

/*第一种方法*/
SELECT Sno,Cno
FROM SC x
WHERE Grade>=(SELECT AVG(Grade)
              FROM SC y
              WHERE y.Sno=x.Sno);

/*第二种方法，派生表*/
SELECT Sno,Cno
FROM SC,(SELECT Sno,AVG(Grade) FROM SC GROUP BY Sno) AS Avg_sc(avg_sno,avg_grade)
WHERE SC.Sno=Avg_sc.avg_sno AND SC.Grade>=Avg_sc.avg_grade;

这里FROM子句中的子查询将生成一个派生表Avg_sc。该表由avg_sno和avg_grade两个属性组成，记录了每个学生的学号及平均成绩。主查询将SC表与Avg_sc按学号相等进行连接，选出修课成绩大于其平均成绩的课程号。

如果子查询中没有聚集函数，派生表可以不指定属性列，子查询SELECT子句后面的列名为其默认属性。

通过FROM子句生成派生表时，AS关键字可以省略，但必须为派生关系指定一个别名。而对于基本表，别名是可选择项。

-------->>>数据更新《MYSQL学习路线（3）》