本专栏关于联合查询已建好相应库与表,链接如下:
基于以上库与表,本篇介绍内连接;
内连接表示语法有两种:
第一种:
select [列名],[列名]... form [表1],[表2] where 条件;
第二种:
select [列名],[列名] from [表1] join [表2] on 条件;
2.1 示例1:查询许仙同学的成绩
(“许仙”是名字在student表中,“成绩”在score表中,位于不同的表中,需要进行联合查询)
将student表与score表进行笛卡尔积——>删去无效数据——>按照许仙名字来筛选——>必要时对结果进行精简;
(1)计算student表和score表的笛卡尔积:
mysql> select* from student,score;
(2)根据两个表的关联列是否对应,删去无效数据:
mysql> select* from student, score where id = student_id;
注:当联合查询的关联列名重名时,可以使用表名.列名进行指定,即上文SQL指令也可以写为:
mysql> select* from student, score where student.id = score.student_id;
在实际开发中,更建议采用这种写法,避免当表多列多的情况下产生混淆;
(3)根据名字,筛选出许仙同学的成绩:
mysql> select* from student, score where student.id=score.student_id and student.name = "许仙";
此时查询结果为:
+----+-------+------+---------------+------------+-------+------------+-----------+
| id | sn | name | qq_mail | classes_id | score | student_id | course_id |
+----+-------+------+---------------+------------+-------+------------+-----------+
| 4 | 00031 | 许仙 | xuxian@qq.com | 1 | 67.0 | 4 | 1 |
| 4 | 00031 | 许仙 | xuxian@qq.com | 1 | 23.0 | 4 | 3 |
| 4 | 00031 | 许仙 | xuxian@qq.com | 1 | 56.0 | 4 | 5 |
| 4 | 00031 | 许仙 | xuxian@qq.com | 1 | 72.0 | 4 | 6 |
+----+-------+------+---------------+------------+-------+------------+-----------+
(4)对查询结果进行精简,查询指令与最终查询结果为:
mysql> select student.name, score.course_id, score.score from student, score
-> where student.id = score.student_id and student.name = "许仙";
+------+-----------+-------+
| name | course_id | score |
+------+-----------+-------+
| 许仙 | 1 | 67.0 |
| 许仙 | 3 | 23.0 |
| 许仙 | 5 | 56.0 |
| 许仙 | 6 | 72.0 |
+------+-----------+-------+
4 rows in set (0.00 sec)
注:计算笛卡尔积即其筛选方式有两种:
第一种:
select* from [表名],[表名] where [条件];
第二种:
select* from [表名] join [表名] on [条件];
故而上文的SQL语句也可以写为:
mysql> select student.name, score.course_id, score.score from
-> student join score
-> on student.id = score.student_id and student.name="许仙";
2.2 示例2: 查询所有同学的总成绩,及同学的个人信息
(查询信息关系到学生表与成绩表)
将student表和score表进行笛卡尔积计算——>根据联合列学生id删去无效信息——>根据学生name或id进行分组并根据分组情况对score进行求和
(1)将student表和score表进行笛卡尔积计算并对无效信息进行删除:
mysql> select* from student,score where student.id = score.student_id;
(2)根据学生id进行分组,根据分组使用聚合函数sum进行聚合计算总成绩:
mysql> select student.name, sum(score.score)from student,score where student.id = score.student_id group by id;
+------------+------------------+
| name | sum(score.score) |
+------------+------------------+
| 黑旋风李逵 | 300.0 |
| 菩提老祖 | 119.5 |
| 白素贞 | 200.0 |
| 许仙 | 218.0 |
| 不想毕业 | 118.0 |
| 好好说话 | 178.0 |
| tellme | 172.0 |
+------------+------------------+
7 rows in set (0.00 sec)
2.3 示例3:查询所有同学的科目及各科成绩,及同学的个人信息
(同学姓名在student表中,科目信息在course表中,各科成绩在score表中)
mysql> select student.name, course.name, score.score
-> from student, course, score
-> where student.id = score.student_id
-> and score.course_id = course.id;
+------------+--------------+-------+
| name | name | score |
+------------+--------------+-------+
| 黑旋风李逵 | Java | 70.5 |
| 黑旋风李逵 | 计算机原理 | 98.5 |
| 黑旋风李逵 | 高阶数学 | 33.0 |
| 黑旋风李逵 | 英文 | 98.0 |
| 菩提老祖 | Java | 60.0 |
| 菩提老祖 | 高阶数学 | 59.5 |
| 白素贞 | Java | 33.0 |
| 白素贞 | 计算机原理 | 68.0 |
| 白素贞 | 高阶数学 | 99.0 |
| 许仙 | Java | 67.0 |
| 许仙 | 计算机原理 | 23.0 |
| 许仙 | 高阶数学 | 56.0 |
| 许仙 | 英文 | 72.0 |
| 不想毕业 | Java | 81.0 |
| 不想毕业 | 高阶数学 | 37.0 |
| 好好说话 | 中国传统文化 | 56.0 |
| 好好说话 | 语文 | 43.0 |
| 好好说话 | 英文 | 79.0 |
| tellme | 中国传统文化 | 80.0 |
| tellme | 英文 | 92.0 |
+------------+--------------+-------+
20 rows in set (0.00 sec)