一、表与表之间的关系
1.一对一(一个只对应一个身份证,一个身份证也只对应一个人)
2.一对多(一个班级对应多个学生)
3.多对多
二、多表联合查询操作
1.内联查询
-- 1.隐式内联查询
mysql> select * from t_exam,t_class where t_exam.class_id=t_class.cid;
+----+--------+---------+------+---------+----------+-----+-----------+
| id | name | chinese | math | english | class_id | cid | cname |
+----+--------+---------+------+---------+----------+-----+-----------+
| 1 | 王晶 | 85 | 76 | 70 | 1 | 1 | 初一(2)班 |
| 2 | 周星星 | 70 | 75 | 70 | 1 | 1 | 初一(2)班 |
| 3 | 沈腾 | 90 | 65 | 95 | 1 | 1 | 初一(2)班 |
| 4 | 杨威 | 97 | 50 | 50 | 2 | 2 | 初2(4)班 |
| 5 | 李连杰 | 90 | 89 | 80 | 3 | 3 | 初3(9)班 |
| 6 | 乔布斯 | 90 | 67 | 65 | 3 | 3 | 初3(9)班 |
| 7 | 周润发 | 70 | 80 | 90 | 3 | 3 | 初3(9)班 |
| 8 | 李白 | 70 | 80 | 90 | 6 | 6 | 高二(3)班 |
| 9 | 王晶红 | 70 | 80 | 90 | 5 | 5 | 高一(6)班 |
| 10 | 王晶亮 | 70 | 80 | 90 | 5 | 5 | 高一(6)班 |
| 11 | 王多多 | 70 | 80 | 90 | 5 | 5 | 高一(6)班 |
+----+--------+---------+------+---------+----------+-----+-----------+
11 rows in set (0.00 sec)
-- 2.显式内联查询(inner join ...on ...)
mysql> select * from t_exam e inner join t_class c on e.class_id = c.cid;
+----+--------+---------+------+---------+----------+-----+-----------+
| id | name | chinese | math | english | class_id | cid | cname |
+----+--------+---------+------+---------+----------+-----+-----------+
| 1 | 王晶 | 85 | 76 | 70 | 1 | 1 | 初一(2)班 |
| 2 | 周星星 | 70 | 75 | 70 | 1 | 1 | 初一(2)班 |
| 3 | 沈腾 | 90 | 65 | 95 | 1 | 1 | 初一(2)班 |
| 4 | 杨威 | 97 | 50 | 50 | 2 | 2 | 初2(4)班 |
| 5 | 李连杰 | 90 | 89 | 80 | 3 | 3 | 初3(9)班 |
| 6 | 乔布斯 | 90 | 67 | 65 | 3 | 3 | 初3(9)班 |
| 7 | 周润发 | 70 | 80 | 90 | 3 | 3 | 初3(9)班 |
| 8 | 李白 | 70 | 80 | 90 | 6 | 6 | 高二(3)班 |
| 9 | 王晶红 | 70 | 80 | 90 | 5 | 5 | 高一(6)班 |
| 10 | 王晶亮 | 70 | 80 | 90 | 5 | 5 | 高一(6)班 |
| 11 | 王多多 | 70 | 80 | 90 | 5 | 5 | 高一(6)班 |
+----+--------+---------+------+---------+----------+-----+-----------+
11 rows in set (0.00 sec)
-- 3.区别:显式性能高于隐式
-- 3.1:隐式内联查询,是先执行form后面两张表的查询 ,再通过where条件过滤
-- 3.2:显式内联查询,是带着on后面的条件查询
2.外联查询
-- 1.左外联查询,以左表为主,匹配不到右表的字段用NULL表示
mysql> select * from t_exam ex left join t_class c on ex.class_id = c.cid;
+----+--------+---------+------+---------+----------+------+-----------+
| id | name | chinese | math | english | class_id | cid | cname |
+----+--------+---------+------+---------+----------+------+-----------+
| 1 | 王晶 | 85 | 76 | 70 | 1 | 1 | 初一(2)班 |
| 2 | 周星星 | 70 | 75 | 70 | 1 | 1 | 初一(2)班 |
| 3 | 沈腾 | 90 | 65 | 95 | 1 | 1 | 初一(2)班 |
| 4 | 杨威 | 97 | 50 | 50 | 2 | 2 | 初2(4)班 |
| 5 | 李连杰 | 90 | 89 | 80 | 3 | 3 | 初3(9)班 |
| 6 | 乔布斯 | 90 | 67 | 65 | 3 | 3 | 初3(9)班 |
| 7 | 周润发 | 70 | 80 | 90 | 3 | 3 | 初3(9)班 |
| 8 | 李白 | 70 | 80 | 90 | 6 | 6 | 高二(3)班 |
| 9 | 王晶红 | 70 | 80 | 90 | 5 | 5 | 高一(6)班 |
| 10 | 王晶亮 | 70 | 80 | 90 | 5 | 5 | 高一(6)班 |
| 11 | 王多多 | 70 | 80 | 90 | 5 | 5 | 高一(6)班 |
| 12 | 成龙 | NULL | NULL | NULL | NULL | NULL | NULL |
+----+--------+---------+------+---------+----------+------+-----------+
12 rows in set (0.00 sec)
-- 2.右外联查询,与上面的相反
mysql> select * from t_class c left join t_exam ex on ex.class_id = c.cid;
+-----+-----------+------+--------+---------+------+---------+----------+
| cid | cname | id | name | chinese | math | english | class_id |
+-----+-----------+------+--------+---------+------+---------+----------+
| 1 | 初一(2)班 | 1 | 王晶 | 85 | 76 | 70 | 1 |
| 1 | 初一(2)班 | 2 | 周星星 | 70 | 75 | 70 | 1 |
| 1 | 初一(2)班 | 3 | 沈腾 | 90 | 65 | 95 | 1 |
| 2 | 初2(4)班 | 4 | 杨威 | 97 | 50 | 50 | 2 |
| 3 | 初3(9)班 | 5 | 李连杰 | 90 | 89 | 80 | 3 |
| 3 | 初3(9)班 | 6 | 乔布斯 | 90 | 67 | 65 | 3 |
| 3 | 初3(9)班 | 7 | 周润发 | 70 | 80 | 90 | 3 |
| 6 | 高二(3)班 | 8 | 李白 | 70 | 80 | 90 | 6 |
| 5 | 高一(6)班 | 9 | 王晶红 | 70 | 80 | 90 | 5 |
| 5 | 高一(6)班 | 10 | 王晶亮 | 70 | 80 | 90 | 5 |
| 5 | 高一(6)班 | 11 | 王多多 | 70 | 80 | 90 | 5 |
| 4 | 初1(6)班 | NULL | NULL | NULL | NULL | NULL | NULL |
| 7 | 高二(5)班 | NULL | NULL | NULL | NULL | NULL | NULL |
| 8 | | NULL | NULL | NULL | NULL | NULL | NULL |
+-----+-----------+------+--------+---------+------+---------+----------+
14 rows in set (0.00 sec)
3.全外连接查询
-- 1.全外联 :将两个查询结果合并(可以是不同表),MySQL要求字段数量相同
mysql> select name,math from t_exam union select cid,cname from t_class ;
+--------+-----------+
| name | math |
+--------+-----------+
| 王晶 | 76 |
| 周星星 | 75 |
| 沈腾 | 65 |
| 杨威 | 50 |
| 李连杰 | 89 |
| 乔布斯 | 67 |
| 周润发 | 80 |
| 李白 | 80 |
| 王晶红 | 80 |
| 王晶亮 | 80 |
| 王多多 | 80 |
| 成龙 | NULL |
| 1 | 初一(2)班 |
| 2 | 初2(4)班 |
| 3 | 初3(9)班 |
| 4 | 初1(6)班 |
| 5 | 高一(6)班 |
| 6 | 高二(3)班 |
| 7 | 高二(5)班 |
| 8 | |
+--------+-----------+
20 rows in set (0.00 sec)
三、子表的查询操作
1. where后带子表查询
mysql> select * from t_exam where class_id in (select cid from t_class where cna
me like '初%') ;
+----+--------+---------+------+---------+----------+
| id | name | chinese | math | english | class_id |
+----+--------+---------+------+---------+----------+
| 1 | 王晶 | 85 | 76 | 70 | 1 |
| 2 | 周星星 | 70 | 75 | 70 | 1 |
| 3 | 沈腾 | 90 | 65 | 95 | 1 |
| 4 | 杨威 | 97 | 50 | 50 | 2 |
| 5 | 李连杰 | 90 | 89 | 80 | 3 |
| 6 | 乔布斯 | 90 | 67 | 65 | 3 |
| 7 | 周润发 | 70 | 80 | 90 | 3 |
+----+--------+---------+------+---------+----------+
7 rows in set (0.00 sec)
-- 2.Select后面接子查询
mysql> select * from t_exam,(select distinct math from t_exam where english>80 )
as new_math where t_exam.math=new_math.math;
+----+--------+---------+------+---------+----------+------+
| id | name | chinese | math | english | class_id | math |
+----+--------+---------+------+---------+----------+------+
| 3 | 沈腾 | 90 | 65 | 95 | 1 | 65 |
| 7 | 周润发 | 70 | 80 | 90 | 3 | 80 |
| 8 | 李白 | 70 | 80 | 90 | 6 | 80 |
| 9 | 王晶红 | 70 | 80 | 90 | 5 | 80 |
| 10 | 王晶亮 | 70 | 80 | 90 | 5 | 80 |
| 11 | 王多多 | 70 | 80 | 90 | 5 | 80 |
+----+--------+---------+------+---------+----------+------+
6 rows in set (0.00 sec)
-- 3.having后面接子查询
mysql> select * from t_exam group by class_id having sum(math+english+chinese) >
(select avg(math+english+chinese) from t_exam);
+----+--------+---------+------+---------+----------+
| id | name | chinese | math | english | class_id |
+----+--------+---------+------+---------+----------+
| 1 | 王晶 | 85 | 76 | 70 | 1 |
| 5 | 李连杰 | 90 | 89 | 80 | 3 |
| 8 | 李白 | 70 | 80 | 90 | 6 |
| 9 | 王晶红 | 70 | 80 | 90 | 5 |
+----+--------+---------+------+---------+----------+
4 rows in set (0.00 sec)