MySQL 知识量:16 - 40 - 165
在MySQL中,除了列名可以使用别名外,表也可以使用别名,在前面的例子中实际已经使用了表别名。使用表别名有以下好处:
可以简化SQL语句,便于编辑,也不易写错。
可以允许在一条select语句中多次使用相同的表,甚至表自己与自己可以进行联结匹配。
自联结就是表自己跟自己进行联结,这有什么用呢?用一个例子来说明:
课程表(course):
+----+------------+-----------+ | id | id_student | id_lesson | +----+------------+-----------+ | 1 | 1 | 1 | | 2 | 5 | 3 | | 3 | 2 | 1 | | 4 | 4 | 1 | | 5 | 6 | 1 | | 6 | 1 | 2 | | 7 | 3 | 2 | | 8 | 4 | 2 | | 9 | 5 | 2 | | 10 | 2 | 3 | | 11 | 4 | 3 | | 12 | 6 | 3 | +----+------------+-----------+
如果想知道选择了课程id(id_lesson)“3”的学生还选择了什么课程id?按学生id(id_student)排序,其SQL语句如下:
select c1.id_student,c1.id_lesson from course as c1,course as c2 where c1.id_student=c2.id_student and c2.id_lesson=3 and c1.id_lesson!=3 order by c1.id_student;
查询结果为:
+------------+-----------+ | id_student | id_lesson | +------------+-----------+ | 2 | 1 | | 4 | 1 | | 4 | 2 | | 5 | 2 | | 6 | 1 | +------------+-----------+
以上代码中,通过自联结完成了一个复杂的查询,通常情况下,第二种查询方法可以利用子查询来完成,逻辑上应当是先使用子查询找到选择id_lesson为“3”的id_student,然后查询该id_student的所有对应的id_lesson,并排除id_lesson为“3”的行。
以上例子中的查询结果因为全是数字,似乎让人看不明白,可以再内部联结student表和lesson表,使得结果更好阅读:
select c1.id_student, s.name, c1.id_lesson, l.name from course as c1, course as c2, student as s, lesson as l where c1.id_student=c2.id_student and c2.id_lesson=3 and c1.id_lesson!=3 and s.id=c1.id_student and l.id=c1.id_lesson order by c1.id_student;
查询结果为:
+------------+-------+-----------+---------+ | id_student | name | id_lesson | name | +------------+-------+-----------+---------+ | 2 | Jame | 1 | Maths | | 4 | Robot | 1 | Maths | | 4 | Robot | 2 | English | | 5 | Jen | 2 | English | | 6 | Toney | 1 | Maths | +------------+-------+-----------+---------+
表与表联结时至少应当有一个列是两表中都存在的,这个列就是关联的列,否则就会出现笛卡尔积问题。内部联结不能排除两个表中相同的列被同时查询出来(因为有关联关系的列实质上就是相同的列,尽管有时列名不同,但在结果中出现两次也没有什么意义)。
如果想要两个表(或多个表)中的列只在结果中出现一次(没有重复列),就可以使用自然联结。
MySQL实现自然联结的方式很原始,也就是说需要自己手动实现。具体方法是:对某一个表使用通配符(*)查询所有列,对其他表指明非重复列的列名。
自然联结只是一种联结方式,感觉没有什么实际价值,因为在一般的查询中,也只会查询必要的列。
在内部联结中,只有关联成功的行才能被查询出来。但是在某些情况下,查询结果需要包含没有关联成功的那些行。如果一种联结包含了在相关表中没有关联行的行,那么这种类型的联结就是外部联结。
例如:不是所有学生都选修了所有课程,同时也不是所有学生都参加了考试,如果关联课程表和考试成绩表,要求保留所有课程信息,就可以使用外部联结:
select * from course left outer join exam on course.id=exam.id_course;
查询选课及考试成绩。结果为:
+----+------------+-----------+------+-----------+-------+ | id | id_student | id_lesson | id | id_course | score | +----+------------+-----------+------+-----------+-------+ | 1 | 1 | 1 | 1 | 1 | 76 | | 2 | 5 | 3 | 4 | 2 | 88 | | 3 | 2 | 1 | 5 | 3 | 56 | | 4 | 4 | 1 | 2 | 4 | 99 | | 5 | 6 | 1 | 3 | 5 | 74 | | 6 | 1 | 2 | 6 | 6 | 86 | | 7 | 3 | 2 | NULL | NULL | NULL | | 8 | 4 | 2 | 7 | 8 | 91 | | 9 | 5 | 2 | 8 | 9 | 95 | | 10 | 2 | 3 | 9 | 10 | 89 | | 11 | 4 | 3 | 10 | 11 | 66 | | 12 | 6 | 3 | NULL | NULL | NULL | +----+------------+-----------+------+-----------+-------+
外部联结的表之间通过left outer join或right outer join连接,后面跟着on子句指示关联条件。
left outer join 是左外部联结,将以左边的表为基数表进行关联查询,即使关联失败,左边表的行将也全部保留。
right outer join 是右外部联结,将以右边的表为基数表进行关联查询,即使关联失败,右边表的行也将全部保留。
以上示例中,课程id为7和12的课程对应的学生没有参加考试,表exam查询出的信息是NULL,NULL就表示没有匹配的记录。
下面对示例语句进行调整,将同时使用内部联结和外部联结,以便更清晰的显示结果。
select c.id,s.name as studentName,l.name as lessonName,e.score from course as c inner join student as s on c.id_student=s.id inner join lesson as l on c.id_lesson=l.id left outer join exam as e on c.id=e.id_course order by s.name;
查询课程id、学生姓名、课程名称、考试分数情况,按照学生姓名排序。结果为:
+----+-------------+------------+-------+ | id | studentName | lessonName | score | +----+-------------+------------+-------+ | 7 | Bob | English | NULL | | 3 | Jame | Maths | 56 | | 10 | Jame | Natural | 89 | | 2 | Jen | Natural | 88 | | 9 | Jen | English | 95 | | 4 | Robot | Maths | 99 | | 8 | Robot | English | 91 | | 11 | Robot | Natural | 66 | | 1 | Susan | Maths | 76 | | 6 | Susan | English | 86 | | 5 | Toney | Maths | 74 | | 12 | Toney | Natural | NULL | +----+-------------+------------+-------+
在使用联结时可以同时使用聚集函数,以便实现更加复杂的查询。例如:查询学生的姓名和选课的数量,按照学生姓名排序。
select s.name,count(c.id) from student as s inner join course as c on s.id=c.id_student group by s.name order by s.name;
查询结果为:
+-------+-------------+ | name | count(c.id) | +-------+-------------+ | Bob | 1 | | Jame | 2 | | Jen | 2 | | Robot | 3 | | Susan | 2 | | Toney | 2 | +-------+-------------+
联结的功能十分强大,在使用联结时应当注意技巧:
联结类型的选择要根据查询目标来确定,如果要查询有效的记录,一般使用内部联结;如果要查询所有情况,一般使用外部联结。
联结条件一定要设置正确的关联列。
联结条件总是必不可少的。
如果关联了多个表或使用了多种联结,结果出错时,应对每个联结依次进行测试,尽快找到错误。
更好的做法是,在集成多表联结或多类联结前,先测试每个联结的正确性,再进行集成。
Copyright © 2017-Now pnotes.cn. All Rights Reserved.
编程学习笔记 保留所有权利
MARK:3.0.0.20240214.P35
From 2017.2.6