赞
踩
联合查询也称为多表查询,是将多个表联合到一起进行查询;
笛卡尔积是联合查询的基础,笛卡尔积其实就是一种排列组合,把两张表的记录尽可能地排列组合出n种情况:
以两张表:班级表与学生表为例,计算这两个表的笛卡尔积:
笛卡尔积就是得到了一张更大的表,其列数为两个表列数之和,行数为两个表列数之积;
试在testdemo1数据库下创建以下表:
- mysql> show tables;
- +---------------------+
- | Tables_in_testdemo1 |
- +---------------------+
- | classes |
- | course |
- | score |
- | student |
- +---------------------+
- 4 rows in set (0.00 sec)
表的结构与内容分别为:
(1)student表:
- mysql> desc student;
- +------------+-------------+------+-----+---------+----------------+
- | Field | Type | Null | Key | Default | Extra |
- +------------+-------------+------+-----+---------+----------------+
- | id | int(11) | NO | PRI | NULL | auto_increment |
- | sn | varchar(20) | YES | | NULL | |
- | name | varchar(20) | YES | | NULL | |
- | qq_mail | varchar(20) | YES | | NULL | |
- | classes_id | int(11) | YES | | NULL | |
- +------------+-------------+------+-----+---------+----------------+
- 5 rows in set (0.00 sec)
-
- mysql> select* from student;
- +----+-------+------------+------------------+------------+
- | id | sn | name | qq_mail | classes_id |
- +----+-------+------------+------------------+------------+
- | 1 | 09982 | 黑旋风李逵 | xuanfeng@qq.com | 1 |
- | 2 | 00835 | 菩提老祖 | NULL | 1 |
- | 3 | 00391 | 白素贞 | NULL | 1 |
- | 4 | 00031 | 许仙 | xuxian@qq.com | 1 |
- | 5 | 00054 | 不想毕业 | NULL | 1 |
- | 6 | 51234 | 好好说话 | say@qq.com | 2 |
- | 7 | 83223 | tellme | NULL | 2 |
- | 8 | 09527 | 老外学中文 | foreigner@qq.com | 2 |
- +----+-------+------------+------------------+------------+
- 8 rows in set (0.00 sec)
(2)classes表:
- mysql> desc classes;
- +-------+--------------+------+-----+---------+----------------+
- | Field | Type | Null | Key | Default | Extra |
- +-------+--------------+------+-----+---------+----------------+
- | id | int(11) | NO | PRI | NULL | auto_increment |
- | name | varchar(20) | YES | | NULL | |
- | desc | varchar(100) | YES | | NULL | |
- +-------+--------------+------+-----+---------+----------------+
- 3 rows in set (0.00 sec)
-
- mysql> select* from classes;
- +----+-------------------+-----------------------------------------------+
- | id | name | desc |
- +----+-------------------+-----------------------------------------------+
- | 1 | 计算机系2019级1班 | 学习了计算机原理、C和Java语言、数据结构和算法 |
- | 2 | 中文系2019级3班 | 学习了中国传统文学 |
- | 3 | 自动化2019级5班 | 学习了机械自动化 |
- +----+-------------------+-----------------------------------------------+
- 3 rows in set (0.00 sec)
(3)course表:
- mysql> desc course;
- +-------+-------------+------+-----+---------+----------------+
- | Field | Type | Null | Key | Default | Extra |
- +-------+-------------+------+-----+---------+----------------+
- | id | int(11) | NO | PRI | NULL | auto_increment |
- | name | varchar(20) | YES | | NULL | |
- +-------+-------------+------+-----+---------+----------------+
- 2 rows in set (0.00 sec)
-
- mysql> select* from course;
- +----+--------------+
- | id | name |
- +----+--------------+
- | 1 | Java |
- | 2 | 中国传统文化 |
- | 3 | 计算机原理 |
- | 4 | 语文 |
- | 5 | 高阶数学 |
- | 6 | 英文 |
- +----+--------------+
- 6 rows in set (0.00 sec)
(4)score表:
- mysql> desc score;
- +------------+--------------+------+-----+---------+-------+
- | Field | Type | Null | Key | Default | Extra |
- +------------+--------------+------+-----+---------+-------+
- | score | decimal(3,1) | YES | | NULL | |
- | student_id | int(11) | YES | | NULL | |
- | course_id | int(11) | YES | | NULL | |
- +------------+--------------+------+-----+---------+-------+
- 3 rows in set (0.00 sec)
-
- mysql> select* from score;
- +-------+------------+-----------+
- | score | student_id | course_id |
- +-------+------------+-----------+
- | 70.5 | 1 | 1 |
- | 98.5 | 1 | 3 |
- | 33.0 | 1 | 5 |
- | 98.0 | 1 | 6 |
- | 60.0 | 2 | 1 |
- | 59.5 | 2 | 5 |
- | 33.0 | 3 | 1 |
- | 68.0 | 3 | 3 |
- | 99.0 | 3 | 5 |
- | 67.0 | 4 | 1 |
- | 23.0 | 4 | 3 |
- | 56.0 | 4 | 5 |
- | 72.0 | 4 | 6 |
- | 81.0 | 5 | 1 |
- | 37.0 | 5 | 5 |
- | 56.0 | 6 | 2 |
- | 43.0 | 6 | 4 |
- | 79.0 | 6 | 6 |
- | 80.0 | 7 | 2 |
- | 92.0 | 7 | 6 |
- +-------+------------+-----------+
- 20 rows in set (0.00 sec)
在该数据库中四张表,三个实体:学生、班级、课程;
其中学生和班级是一对多关系,学生和课程是多对多关系(成绩表是关联表),班级和课程之间没有直接的关联关系;
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。