mysql 笛卡尔积的应用

1.查询student表

mysql> select * from student;
+--------+------+------+
| stu_id | name | c_id |
+--------+------+------+
| 001    | jim  | 1    |
| 002    | tom  | 2    |
+--------+------+------+
2 rows in set (0.00 sec)

2.查询class表

mysql> select * from grade;
+------+--------+
| g_id | gname  |
+------+--------+
| 1    | grade1 |
| 2    | grade2 |
+------+--------+
2 rows in set (0.00 sec)

3.查询grade表

mysql> select * from class;
+------+--------+------+
| c_id | cname  | g_id |
+------+--------+------+
| 1    | class1 | 1    |
| 2    | class2 | 2    |
+------+--------+------+
2 rows in set (0.00 sec)

4.笛卡尔积

mysql> select * from student,class,grade;
+--------+------+------+------+--------+------+------+--------+
| stu_id | name | c_id | c_id | cname  | g_id | g_id | gname  |
+--------+------+------+------+--------+------+------+--------+
| 001    | jim  | 1    | 1    | class1 | 1    | 1    | grade1 |
| 002    | tom  | 2    | 1    | class1 | 1    | 1    | grade1 |
| 001    | jim  | 1    | 2    | class2 | 2    | 1    | grade1 |
| 002    | tom  | 2    | 2    | class2 | 2    | 1    | grade1 |
| 001    | jim  | 1    | 1    | class1 | 1    | 2    | grade2 |
| 002    | tom  | 2    | 1    | class1 | 1    | 2    | grade2 |
| 001    | jim  | 1    | 2    | class2 | 2    | 2    | grade2 |
| 002    | tom  | 2    | 2    | class2 | 2    | 2    | grade2 |
+--------+------+------+------+--------+------+------+--------+
8 rows in set (0.00 sec)

5.先用笛卡尔积查所有数据  再筛选正确数据

mysql> select * from student s,class c,grade g where s.c_id=c.c_id and c.g_id =
g.g_id;
+--------+------+------+------+--------+------+------+--------+
| stu_id | name | c_id | c_id | cname  | g_id | g_id | gname  |
+--------+------+------+------+--------+------+------+--------+
| 001    | jim  | 1    | 1    | class1 | 1    | 1    | grade1 |
| 002    | tom  | 2    | 2    | class2 | 2    | 2    | grade2 |
+--------+------+------+------+--------+------+------+--------+
2 rows in set (0.00 sec)