mysql带有谓词的查询_MySql数据库之数据查询（单表查询、连接查询、嵌套查询、集合查询、派生查询）...

目录

一、本篇作为例子的三个表

二、单表查询

1、选择表中的的若干列

(1) 查询指定列

(2)查询全部列

(3)查询经过计算的值

2、选择表中的若干元组

(1)消除取值重复的行

(2)查询满足条件的元组

(3)ORDER BY 子句

(4)聚集函数(自带函数)

COUNT(*)：统计元组个数

COUNT(列名)：统计某列中元组的个数

SUM(列名)：计算一列值的总和

AVG(列名)：计算一列值的平均值

MAX(列名)：一列值中的最大值

MIN(列名)：一列值中的最小值

(5)GROUP BY 子句

三、连接查询

1、等值与非等值连接查询

2、自身连接

3、外连接

4、多表连接

四、嵌套查询

1、带有IN谓词的子查询

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

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

4、带有EXISTS谓词的子查询

五、集合查询

六、基于派生表的查询

注：有关MySql数据库的其他操作请参见MySql目录进行查找。

一、本篇作为例子的三个表

Student

学号(Sno)

姓名(Sname)

性别(Ssex)

年龄(Sage)

所在系(Sdept)

201215121

李勇

男

20

CS

201215122

刘晨

女

19

CS

201215123

王敏

女

18

MA

201215125

张立

男

19

IS

Course

课程号(Cno)

课程名(Cname)

先行课(Cpno)

学分(Ccredit)

1

数据库

5

4

2

数学

2

3

信息系统

1

4

4

操作系统

6

3

5

数据结构

7

4

6

数据处理

2

7

PASCAL语言

6

4

SC

学号(Sno)

课程号(Cno)

成绩(Grade)

201215121

1

92

201215121

2

85

201215121

3

88

201215122

2

90

201215122

3

80

二、单表查询

1、选择表中的的若干列

(1) 查询指定列

格式：select 属性名，属性名，... from 表名；

例子：select Sno, Sname from Student；

(2)查询全部列

select * from Student；

(3)查询经过计算的值

属性名可以替换为算术表达式、字符串常量、函数等。

例1：select Sname, 2019-Sage from Student；(算术表达式)

例2：select Sname, 'Year of Birth: ', 2019-Sage, lower(Sdept) from Student；

用户还可以通过指定别名来改变查询结果的列标题。

2、选择表中的若干元组

(1)消除取值重复的行

因为查询某些列后可能会有元组拥有相同的取值，因此可以通过增加单词distinct来消除重复的行。

例：select distinct Sno from SC；

(2)查询满足条件的元组

1) 比较大小(=,>,=,<=,!=,!>,!

例：select Sname, Sage from Student where Sage<20；

2) 确定范围

例：select Sname, Sdept, Sage from Student where Sage [not] between 20 and 23；

3) 确定集合

例：select Sname, Ssex from Student where Sdept [not] in ('CS', 'MA', 'IS')；

4) 字符匹配

可以使用like来进行字符串的匹配。其中有两种通配符：%和_。另外若在通配符前加上\则不再具有通配符的含义。

%：表示任意长度的字符串。

_：表示任意单个字符。

例1：select * from Student where Sno like '201215121';

例2：select Sname, Sno, Ssex from Student where Sname like 'liu%';

例3：select Sname from Student where Sname like '_iuchen';

5) 涉及空值的查询

例：select Sno, Cno from SC where Grade is NULL;

6) 多重条件查询

例：select Sname from Student where Sdept='CS' and Sage<20;

(3)ORDER BY 子句

用户可以用order by子句对查询结果按照一个或多个属性列的升序(ASC)或者降序(DESC)排列，默认值为升序。

例1：SELECT Sno, Grade FROM SC WHERE Cno='3' ORDER BY Grade DESC;

解释：查询结果按分数降序排序。

例2：SELECT * FROM Student ORDER BY Sdept, Sage DESC;

解释：查询结果按所在系升序排序，同一系中的学生按年龄降序排列。

(4)聚集函数(自带函数)

聚集函数主要又下列几种：

COUNT(*)：统计元组个数

例：SELECT COUNT(*) FROM Student;

COUNT(列名)：统计某列中元组的个数

例：SELECT COUNT(DISTINCT Sno) FROM SC;

SUM(列名)：计算一列值的总和

例：SELECT AVG(Grade) FROM SC WHERE Cno='1';

AVG(列名)：计算一列值的平均值

例：SELECT AVG(Grade) FROM SC WHERE Cno='1';

MAX(列名)：一列值中的最大值

例：SELECT MAX(Grade) FROM SC WHERE Cno='1';

MIN(列名)：一列值中的最小值

例：SELECT MIN(Grade) FROM SC WHERE Cno='1';

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

(5)GROUP BY 子句

GROUP BY子句将查询结果按某一列或多列的值分组，值相等的为一组。

对查询结果分组的目的是为了细化聚集函数的作用对象。分组后聚集函数将作用于每组。

例1：SELECT Cno, COUNT(Sno) FROM SC GROUP BY Cno;

解释：语句对查询结果按Cno的值分组，所有具有相同Cno的值的元组为一组，然后对每一组作用聚集函数COUNT进行计算，求得该组学生人数。

GROUPBY 还可以使用HAVING短语按一定条件对这些组进行筛选最终输出满足指定条件的组。

例2：SELECT Sno FROM SC GROUP BY Sno HAVING COUNT(*) > 3;

例3：SELECT Sno, AVG(Grade) FROM SC GROUP BY Sno HAVING AVG(Grade) >= 90;

注意：这里的聚集函数使用在HAVING 短语中而非用在WHERE的条件表达式中。

三、连接查询

1、等值与非等值连接查询

当连接运算符为=时，称为等值连接。使用其他运算符称为非等值连接。

等值连接例1：SELECT Student.*, SC.* FROM Student, SC WHERE Student.Sno=SC.Sno;

非等值连接例2：SELECT Student.Sno, Sname FROM Student, SC WHERE Student.Sno=SC.Sno AND SC.Cno='2' AND SC.Grade>90;

注意：若选择的属性列在两个表中是唯一的，则可以省去表名前缀；若不唯一，则引用时必须加上表名前缀。

2、自身连接

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

例：查询一门课的间接先修课。

SELECT FIRST.Cno, SECOND.Cpno FROM Course FIRST, Course SECOND WHERE FIRST.Cpno=SECOND.Cno;

注意：为了区分自身的两个表而给这两个表分别取了别名FIRST和SECOND。

3、外连接

两个表进行连接时，若其中一个表没有相关的元组信息，则按普通的两表连接则会省去某些元组，若想保留则可以使用外连接。

例：SELECT Student.Sno, Sname, Ssex, Sage, Sdept, Cno, Grade FROM Student LEFT OUTER JOIN SC ON (Student.Sno=SC.Sno);

4、多表连接

还可以进行两个表以上的连接，称为多表连接。

例：SELECT Student.Sno, Sname, Cname, Grade FROM Student, SC, Course WHERE Student.Sno=SC.Sno AND SC.Cno=Course.Cno;

四、嵌套查询

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

1、带有IN谓词的子查询

例：SELECT Sno, Sname, Sdept FROM Student WHERE Sdept IN (SELECT Sdept FROM Student WHERE Sname='liuchen');

解释：查询与“liuchen”在同一个系的学生。

注意：有些查询可以用连接查询替代，有些是不能替代的。

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

例：SELECT Sno, Cno FROM SC x WHERE Grade >= (SELECT AVG(Grade) FROM SC y WHERE y.Sno=x.Sno);

解释：查询每个学生超过他自己选修课程平均成绩的课程号。

注意：这个子查询与父查询相关，因此这类查询称为相关子查询。

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

子查询返回多值时需要使用ANY和ALL谓词修饰。其中：

ANY：表示某个           ALL：表示所有

例1：SELECT Sname, Sage FROM Student WHERE Sage'CS';

解释：查询非计算机系中比计算机科学系某个学生年龄小的学生姓名和年龄。

例2：SELECT Sname, Sage FROM Student WHERE Sage'CS';

解释：查询非计算机系中比计算机科学系所有学生年龄小的学生姓名和年龄。

4、带有EXISTS谓词的子查询

带有EXISTS谓词的子查询不返回任何数据，只产生逻辑结果真值“true”或逻辑假值“false”。

例1：SELECT Sname FROM Student WHERE EXISTS (SELECT * FROM SC WHERE Sno=Student.Sno AND Cno='1');

解释：查询所有选修了1号课程的学生姓名。

例2：SELECT Sname FROM Student WHERE NOT EXISTS (SELECT * FROM SC WHERE Sno=Student.Sno AND Cno='1');

解释：查询没有选修了1号课程的学生姓名。

注：EXISTS还可以用来进行量词转化后进行查询。

五、集合查询

集合操作包括：并操作(UNION)、交操作(INTERSECT)、差操作(EXCEPT)

例1：SELECT * FROM Student WHERE Sdept='CS' UNION SELECT * FROM Student WHERE Sage<=19;

解释：查询计算机科学系的学生及年龄不大于19岁的学生。

另外MySql并没有提供对交和差操作的支持，这里就不再举例。

六、基于派生表的查询

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

例：SELECT Sname FROM Student, (SELECT Sno FROM SC WHERE Cno='1') AS SC1 WHERE Student.Sno=SC1.Sno;

注：有关MySql数据库的其他操作请参见MySql目录进行查找。