多表设计
关联查询
含义: 又称多表查询,当查询的字段来自多个表时,就会用到连接查询
笛卡尔乘积现象:表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