mysql 多表查询

DQL 语句多表查询

• 子查询
• 关联条件
• 内连接 [INNER JOIN]
• 外连接 [OUTER JOIN]
• 左连接 [LEFT JOIN] 右外
• 连接 [RIGHT JOIN]
• 外连接 [FULL JOIN]
• 自连接

子查询

子查询 subquery 即SQL语句调用另一个SELECT子句,可以是对同一张表,也可以是对不同表,主要有以下常见的用法.

1.用于比较表达式中的子查询；子查询仅能返回单个值

例子： 先求出平均年龄 然后再找出比平均年龄大的

select avg(age) from students;   #求平均年龄
select * from students where age > (select avg(age) from students);    #再找出比平均年龄大的

例子： 利用子查询更新数据

SELECT avg(Age) FROM teachers   #先计算出 教师表的 平均年龄
update students set Age=(SELECT avg(Age) FROM teachers)  where stuid=25; # 再把第25个学生的年龄改成  教师表的平均年龄

例子： 将students 表里的 平均年龄，作为 值 赋给teacher表

update teachers set age= (select avg(age) from students) where tid=4;
#将students  表里的 平均年龄，作为  值  赋给teacher表

2.用于IN中的子查询：子查询应该单独查询并返回一个或多个值重新构成列表

例子：找出 students 表中stuid 和teachers 表中 tid 相同的数据

select  tid  from  teachers;
select name,stuid from students where stuid in (select tid from teachers);

内连接

有两种格式，显式的和隐式的，返回连接表中符合连接条件和查询条件的数据行

隐式：

SELECT [cols_list] from 表 1,表 2 where [condition]

显式：

SELECT [cols_list] from 表 1 INNER JOIN 表 2 ON [关联条件] where [其他筛选条件]
SELECT [cols_list] from 表 1 CROSS JOIN 表 2 ON [关联条件] where [其他筛选条件]
SELECT [cols_list] from 表 1 JOIN 表 2 ON [关联条件] where [其他筛选条件]

可以挑选想要的列

select students.name , teachers.name  from students,teachers where students.stuid=t
                        -> eachers.tid;
+-------------+---------------+
| name        | name          |
+-------------+---------------+
| Shi Zhongyu | Song Jiang    |
| Shi Potian  | Zhang Sanfeng |
| Xie Yanke   | Miejue Shitai |
| Ding Dian   | Lin Chaoying  |
+-------------+---------------+
#表别名
select s.name ,s.stuid , t.tid , t.name  from students s,teachers t  where s.stuid=t.tid;

外连接

 左外连接（LEFT JOIN 或 LEFT OUTER JOIN）

左外连接返回左表中的所有记录，以及右表中与左表匹配的记录。如果左表的某行在右表中没有匹配行，则结果集中对应右表的部分将包含NULL值。

	SELECT columns  

	FROM table1  

	LEFT JOIN table2 ON table1.column_name = table2.column_name;

2. 右外连接（RIGHT JOIN 或 RIGHT OUTER JOIN）

右外连接返回右表中的所有记录，以及左表中与右表匹配的记录。如果右表的某行在左表中没有匹配行，则结果集中对应左表的部分将包含NULL值。

	SELECT columns  

	FROM table1  

	RIGHT JOIN table2 ON table1.column_name = table2.column_name;

自连接

自连接, 即表自身连接自身

SELECT   
    别名1.列1,   
    别名1.列2,   
    ... ,  
    别名2.列X,   
    别名2.列Y  
FROM   
    表名 别名1  
INNER JOIN   
    表名 别名2  
ON   
    别名1.连接条件列 = 别名2.连接条件列   
WHERE   
    其他条件;