多关系数据查询

多关系查询结构

表的连接方法有以下两种

表之间满足一定条件的进行连接时，FROM子句指明进行连接的表名，WHERE子句指明连接的列名及其连接条件。语法格式为

SELECT [ALL|DISTINCT] [TOP N [PERCENT] [WITH TIES]] <字段名> [AS 别名1] [{,<字段名> [AS 别名2]}]
FROM <表名1> [[AS] 表1别名] [{,<表名2> [[AS] 表2别名,……]}]
[WHERE <检索条件>]
[GROUP BY <列名1> [HAVING <条件表达式>]]
[ORDER BY <列名2> [ASC|DESC]];

利用关键字JOIN进行连接，格式语法如下

1
2
3

SELECT [ALL|DISTINCT] [TOP N [PERCENT] [WITH TIES]] 字段名1 [AS 别名1] [,字段名2 [AS 别名2]……]
FROM 表名1 [[AS] 表1别名] [INNER|[LEFT|RIGHT|FULL|[OUTER]]|CROSS] JOIN 表名2 [[AS] 表2别名]
ON 条件;

内连接查询

实例讲解，现有s和sc两个表，s表中包含学生基本信息(学号(sno)，姓名(sname)，性别(sex)，年龄(age)，专业(major)……)，sc表中包含学生各科成绩(学号(sno)，姓名(sname)，数学(math)，语文(chinese)……)，现要查询学号为114514的学生的学号，姓名，性别，年龄，数学成绩，语文成绩

方法一

1
2
3

SELECT s.sno,s.sname,sex,age,math,chinese
FROM s,sc
WHERE s.sno="114514" AND s.sno=sc.sno

方法二

1
2
3

SELECT s.sno,s.sname,sex,age,math,chinese
FROM s INNER JOIN sc
ON s.sno="114514" AND s.sno=sc.sno;

上述语句的学号sno和姓名sname需要加上表名作为前缀(因为两个表中都有，如果不加，学号和姓名会出现两次)，其他字段名由于只出现一次，可以不用添加前缀,s.sno=sc.sno则是将s表中的sno和sc表中的sno进行等值连接

外连接查询

在内连接查询中，不满足连接条件的元组不能作为查询结果输出，而在外连接查询中，参与连接的表有主从之分，以主表的每行数据去匹配从表的数据列，符合连接条件的数据将直接返回到结果中，对于那些不符合连接条件的列，被填上NULL值后，在返回到结果中
外连接查询分为左外连接查询和右外连接查询两种，以主表所在的方向区分外连接查询，主表在左边，则称为左外连接查询，主表在右边，则称其为右外连接查询

例:
查询所有学生的学号，姓名，性别，年龄，数学成绩，语文成绩，并按学号排序
SELECT s.sno,s.sname,sex,age,math,chinese
FROM s LEFT OUTER JOIN sc ON s.sno=sc.sno
ORDER BY sno;

交叉连接查询

交叉连接查询为连接查询没有特殊的要求，任何表都可以进行交叉查询操作

例:
对s表和sc表进行交叉查询
SELECT *
FROM s CROSS JOIN sc;

自连接查询

当一个表与其自身进行连接查询操作时，称为表的自连接查询
比如，查询学号比114514大的学生的学号，姓名，性别

方法一

1
2
3

SELECT x.sno AS 学号, x.sname AS 姓名,x.sex AS 性别
FROM s AS x,s AS y
WHERE x.sno>y.sno AND y.sno="114514";

方法二
1
2
3
SELECT x.sno AS 学号, x.sname AS 姓名,x.sex AS 性别
FROM s AS x INNER JOIN s AS y
On x.sno>y.sno AND y.sno="114514";
子查询
普通子查询
普通子查询的执行顺序：首先执行子查询，然后把子查询的结果带入父查询的查询条件中，普通子查询只执行一次，而父查询所涉及的所有记录行都与其查询结果进行比较以确保查询结果集合
返回一个值的普通子查询
当子查询的返回值只有一个时，可以使用比较运算符将父查询和子查询连接起来
比如，查询比张三年龄大的学生的学号，姓名，年龄
1
2
3
4
5
SELECT sno,sname,age
FROM s
WHERE age>(SELECT age
FROM s
WHERE sname="张三")
返回一组值的普通子查询
如果子查询的返回值不止一个，而是一个集合，则不能直接使用比较运算符，可以在比较运算符和子查询之间插入ANY和ALL，其中等值关系可以用IN，不等值关系则可使用NOT IN
ANY可以与=、>、>=、<、<=、<>结合起来使⽤，分别表示等于、⼤于、⼤于等于、⼩于、⼩于等于、不等于其中的任何⼀个数据。也就是说只要有任意一个满足就是true，并且显示满足条件的数据。
ALL可以与=、>、>=、<、<=、<>结合是来使⽤，分别表示等于、⼤于、⼤于等于、⼩于、⼩于等于、不等于其中的所有数据。当所有数据都满足才是true，会返回满足所有条件的数据。

现添加c表和stuc表，c表中包含课程基本信息(课程号(cno)，课程名(cname)，任教教师(cteacher)，课时(ctime)……)，stuc表中包含学生选课基本信息(学号(sno)，姓名(sname)，课程号(cno)，课程名(cname)……)，现要查询姓名为张三的学生所选课程的课程号，课程名，任教教师
1
2
3
4
5
SELECT cno,cname,cteacher
FROM c
WHERE cno=ANY(SELECT cno
FROM stuc
WHERE sname="张三")
用于数据操纵的普通查询
比如，将sc表中数学平均成绩进行赋分(平均成绩*80%+20)
1
2
3
UPDATE sc
SET math=(SELECT 0.8*AVG(math)+20
FROM sc);

集合运算查询

集合运算查询是使用UNION关键字将来自不同查询的数据组合起来，形成一个具有综合信息的查询结果，使用UNION时，系统会自动将重复的数据行剔除

参加集合运算查询的各子查询，其查询结果的结果应该是相同的，即各子查询结果中数据的数目和对应的数据类型都必须相同

比如，查询主修为计算机专业的学生，再查询主修为数学的学生

SELECT *
FROM s
WHERE major="计算机"
UNION
SELECT *
FROM s
WHERE major="数学";

MySQL语法使用(四)

多关系数据查询

多关系查询结构

内连接查询

外连接查询

交叉连接查询

自连接查询

子查询

普通子查询

返回一个值的普通子查询

返回一组值的普通子查询

用于数据操纵的普通查询

相关子查询

集合运算查询