【MySQL】_联合查询基础表
联合查询也称为多表查询，是将多个表联合到一起进行查询；
笛卡尔积是联合查询的基础，笛卡尔积其实就是一种排列组合，把两张表的记录尽可能地排列组合出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)在该数据库中四张表，三个实体：学生、班级、课程；
其中学生和班级是一对多关系，学生和课程是多对多关系（成绩表是关联表），班级和课程之间没有直接的关联关系；