多表关联查询
:查询字段来自多个表时使用
1、笛卡尔积乘积:
SELECT * FROM student,gra_de
2、内连接:
(1)等值连接(有相同的属性列)
/*
当表中列名相同时,可使用as给表起别名,通过别名访问表中列(as也可省略)
*/
SELECT
stu.num,
stu.name AS stu_name,
stu.sex,
gra.name AS gra_name
FROM
student AS stu
INNER JOIN gra_de gra
ON grade_id = id
(2) 非等值连接
SELECT
stu.name,
stu.sex,
stu.score,
sl.name
FROM
student stu
INNER JOIN score_level sl
ON stu.score BETWEEN lower_score
AND upper_score
(3) 自连接
CREATE TABLE t_area(
id INT,
NAME VARCHAR(10),
pid INT
)
例图如下:
原表:
查询结果: