试想一下,我的需求是建立一张学生-老师-选课表,并且一个学生可能会选多门课程,而且一个老师也不避免的会教授多门课程
案例:
CREATE TABLE csdn(sid INT,
sname VARCHAR(10),
stel INT,tid INT,
tname VARCHAR(10),
ttel INT,
cname VARCHAR(10),
ctime INT) CHARACTER SET utf8;
INSERT INTO csdn VALUES(1,'张三',111,1,'王老师',211,'数学',36),
(1,'张三',111,2,'张老师',212,'语文',48);
现在只是一个学生的选课信息,如果学生的手机号在填写过程中出错了,如果修改的话,要去修改两行数据,造成不必要的消耗
解决方法:将大表拆分成四张小表,一张存放学生信息,一张存放老师信息,一张存放课程信息,第四张表存放最终的选课信息。如果修改学生的信息,去学生表。修改老师信息,去老师表......
当我们在查询数据时,我们需要通过连接操作查询出存放在多个表中的不同实体的信息。当两个或多个表中存在相同意义的字段时,便可以通过这些字段对不同的表进行连接查询
1.连接操作用处:
1.避免有大量的冗余数据
2.避免dml操作都会造成很多多余的消耗
2.连接分类:
1.根据查询返回的结果
1.内连接:两张表中的相同含义的字段值相等时才符合连接查询的条件
2.外连接
左外连接:返回左表中所有记录和右表中连接字段相等的记录
右外连接:返回右表中所有记录和左表中连接字段相等的记录
3.交叉连接
2.根据连接条件所使用的操作符
1.相等连接(使用等号操作符)
2.不等连接(不适用等号操作符)
3.内连接的多种写法(只掌握前两种即可,后面有案例)
1.传统写法;where and
2.join on
3.using (两张表关联时,列名相同的关联在一起,需要指定列名—如果只有一张表有指定的列名,会报错)
4.natural join (列名相同的去重自动关联-------------不建议使用)
案例1:
**建立以下三张表teacher、course、c_t_map**
mysql> select * from teacher;
+--------+-------+-------+
| t_name | t_sex | t_tel |
+--------+-------+-------+
| cui | nan | 111 |
| chen | nan | 112 |
| deng | nan | 113 |
+--------+-------+-------+
mysql> select * from course;
+---------+----------+--------+
| c_name | c_creait | c_time |
+---------+----------+--------+
| network | 3 | 10 |
| mysql | 4 | 15 |
| linux | 5 | 20 |
+---------+----------+--------
mysql> select * from c_t_map;
+--------+---------+
| t_name | c_name |
+--------+---------+
| cui | network |
| chen | network |
| cui | mysql |
| deng | linux |
需求一:查一下deng老师教的课程信息以及deng的联系方式
需求分析:deng老师的练习方式在teacher中就可以查询出
deng老师教的课程信息需要c_t_map表中查出教授的课程,拿着课程再去course表中查找课程信息
传统写法:
SELECT c.c_name,c.c_creait,c.c_time,t.t_tel-----------------deng老师的联系方式在teacher表中就可以查找
FROM teacher t,course c,c_t_map ct
WHERE t.t_name='deng' ----------------------------------入口表,查找老师名称是‘deng’
AND ct.t_name = t.t_name ---------------------------两张表相关联,查找出deng老师教授的课程名称
AND ct.c_name = c.c_name------------------------两张表相关联,查找出课程名称对应的相关课程信息
;
+--------+----------+--------+-------+
| c_name | c_creait | c_time | t_tel |
+--------+----------+--------+-------+
| linux | 5 | 20 | 113 |
+--------+----------+--------+-------+
join on写法:
SELECT c.c_name,c.c_creait,c.c_time,t.t_tel
FROM teacher t
JOIN c_t_map ct
ON ct.t_name = t.t_name
JOIN course c
ON ct.c_name = c.c_name
WHERE t.t_name='deng'--------------------------最后过滤where条件
;
需求2:查一下手机号码是111的老师教的课程信息
需求分析:在teacher表中找到手机号码是111的老师名称,再去c_t_map表中找此老师教授的课程名称,
再去course表中找相对应的课程信息
传统写法:
SELECT c.c_name,c.c_creait,c.c_time
FROM teacher t,course c,c_t_map ct
WHERE t.t_tel=111 ---------------------------------入口表,查找出手机号码为111的老师的名字
AND ct.t_name=t.t_name --------------------两张表相关联,查找出老师对应教授的课程名称
AND ct.c_name=c.c_name-------------两张表相关联,查找出课程名称对应的课程信息
;
+---------+----------+--------+
| c_name | c_creait | c_time |
+---------+----------+--------+
| network | 3 | 10 |
| mysql | 4 | 15 |
+---------+----------+--------+
join on写法:
SELECT c.c_name,c.c_creait,c.c_time
FROM teacher t
JOIN c_t_map ct
ON ct.t_name=t.t_name
JOIN course c
ON ct.c_name=c.c_name
WHERE t.t_tel=111
;
需求3:查一下教linux和mysql课程的老师信息(此需求只需要两张表的关联即可)
SELECT t.t_name,t.t_sex,t.t_tel
FROM teacher t,c_t_map ct
WHERE ct.c_name in ('linux' ,'mysql') ------------------------从ct表中查找出教授linux或者mysql的老师的名字
AND ct.t_name=t.t_name----------------------两张表相关联,查找出教授这两门课程的老师信息
;
SELECT t.t_name,t.t_sex,t.t_tel
FROM teacher t,c_t_map ct
WHERE (ct.c_name ='linux' OR ct.c_name ='mysql')
AND ct.t_name=t.t_name
;
+--------+-------+-------+
| t_name | t_sex | t_tel |
+--------+-------+-------+
| cui | nan | 111 |
| deng | nan | 113 |
+--------+-------+-------+