Mysql-06--多表设计--关联查询

多表设计

关联查询

含义: 又称多表查询,当查询的字段来自多个表时,就会用到连接查询

笛卡尔乘积现象：表1有m行，表2有n行，结果=m*n

发生原因: 没有有效的连接条件

如何避免: 添加有效的连接条件

按能功能分类:

内连接

• 等着连接
• 非等值连接
• 自连接

外连接

• 左外连接
• 右外连接

交叉连接

– 等值连接

SELECT  1_student.num , 1_student.name, 1_student.sex,1_grade.name  FROM 1_student INNER JOIN 1_grade ON  1_grade.id=1_student.grade_id

– 为表名定义别名, 然后通过别名访问表中的列

SELECT 
 s.num , 
 s.name  AS student_name, 
 s.sex,
 g.name  AS grade_name ,
 g.id
 FROM 
 1_student  AS s
 INNER JOIN 1_grade  AS g
 ON  g.id=s.grade_id

– on是后面两个表的关联条件, 与where后面的条件有区别

– 使用where的条件过滤

 SELECT 
 s.num , 
 s.name  AS student_name, 
 s.sex,
 g.name  AS grade_name ,
 g.id
 FROM 
 1_student  AS s
 INNER JOIN 1_grade  AS g
 WHERE g.id=s.grade_id

非等值链接

 ALTER TABLE 1_student ADD score INT

– 成绩等级表

 CREATE TABLE score_level(
       NAME VARCHAR(5),
     lower_score INT,
     upper_score INT
 )

 SELECT 
 s.name,
 ss.name
 FROM 1_student s
 INNER JOIN score_level ss
 ON s.score BETWEEN ss.lower_score
 AND ss.upper_score

– 自连接

 CREATE TABLE 1t_area(
           id INT,
           NAME VARCHAR(10),
           pid INT
)

SELECT t1.name,t2.name FROM 1t_area t1 INNER JOIN 1t_area t2 ON t1.pid = t2.id

– 外连接

– 左外链接

SELECT * FROM 1_student`1_course`
SELECT * FROM  1_student s LEFT JOIN 1_grade g ON s.grade_id=g.id

– 右外链接

SELECT * FROM  1_student s RIGHT JOIN 1_grade g ON s.grade_id=g.id

– mysql不支持 全连接 full join

– 交叉连接

SELECT * FROM  1_student s CROSS JOIN 1_grade g ON s.grade_id=g.id