mysql多表查询_kuihua baodian-优快云博客

本文深入解析了SQL中各种连接查询的使用方法，包括union、union all、cross join、inner join、left join、right join以及自连接等。通过具体示例展示了如何在不同场景下选择合适的连接类型，以及如何进行子查询操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

文章目录

union, union all

-- 第一张表与第二张表纵向地合并，有去重的功能
MariaDB [hellodb]> select * from teachers
    -> union
    -> select stuid,name,age,gender from students;

MariaDB [hellodb]> select * from teachers union select * from teachers;

-- 纵向合并不去重
MariaDB [hellodb]> select * from teachers union all select * from teachers;

cross join

-- 交叉连接(横向笛卡尔)
MariaDB [hellodb]> select * from students cross join select * from teachers;

inner join

MariaDB [hellodb]> select * from students inner join teachers on students.teacherid=teachers.tid;
MariaDB [hellodb]> select s.stuid,s.name,s.age,t.tid,t.name,t.age from students s inner join teachers t on s.teacherid=t.tid;
MariaDB [hellodb]> select s.stuid,s.name,s.age,t.tid,t.name,t.age from students s,teachers t where s.teacherid=t.tid;
MariaDB [hellodb]> select s.stuid,s.name student_name,s.age student_age,t.tid,t.name teacher_name,t.age teacher_age from students s inner join teachers t on s.teacherid=t.tid and s.age > 30;

left [outer] join

-- 左边表全要，右边的表取left_name 与 right_name的交集 (left outer join)
MariaDB [hellodb]> select s.stuid,s.name,s.age,t.tid,t.name,t.age from students as s left join teachers as t on s.teacherid=t.tid;
MariaDB [hellodb]> select s.stuid,s.name,s.age,t.tid,t.name,t.age from students as s left outer join teachers as t on s.teacherid=t.tid;

right [outer] join

右边的表全要，左边的表取left_name与right_name的交集 (right outer join)

MariaDB [hellodb]> select s.stuid,s.name,s.age,s.teacherid,t.tid,t.name,t.age from students s right outer join teachers t on s.teacherid=t.tid;

A-(A与B的交集)
MariaDB [hellodb]> select s.stuid,s.name,s.age,s.teacherid,t.tid,t.name,t.age from students as s left outer join teachers as t on s.teacherid=t.tid where t.tid is null;

B-(A与B的交集)
MariaDB [hellodb]> select s.stuid,s.name,s.age,s.teacherid,t.tid,t.name,t.age from students s right outer join teachers t on s.teacherid=t.tid where s.teacherid is null;

A 并 B(full outer join)

MariaDB [hellodb]> select s.stuid,s.name,s.age,s.teacherid,t.tid,t.name,t.age from students s left outer join teachers t on s.teacherid=t.tid
    -> union
    -> select s.stuid,s.name,s.age,s.teacherid,t.tid,t.name,t.age from students s right outer join teachers t on s.teacherid=t.tid;

(A并B)-(A交B)
MariaDB [hellodb]> select * from (select s.stuid,s.name s_name,s.teacherid,t.tid,t.name t_name from students s left outer join teachers t on s.teacherid=t.tid union select s.stuid,s.name s_name,s.teacherid,t.tid,t.name t_name from students s right outer join teachers t on s.teacherid=t.tid) as a where a.teacherid is null or a.tid is null;

自连接

MariaDB [hellodb]> select * from emp;
+------+----------+--------+
| id   | name     | leader |
+------+----------+--------+
|    1 | zhangsan |   NULL |
|    2 | lisi     |      1 |
|    3 | wangwu   |      2 |
|    4 | zhaoliu  |      3 |
+------+----------+--------+

MariaDB [hellodb]> select e.name,l.name from emp e left outer join emp l on e.leader=l.id;
+----------+----------+
| name     | name     |
+----------+----------+
| lisi     | zhangsan |
| wangwu   | lisi     |
| zhaoliu  | wangwu   |
| zhangsan | NULL     |
+----------+----------+

三张表内连接

MariaDB [hellodb]> select  st.stuid,st.name,sc.courseid,co.course,sc.score from students st inner join scores sc on st.stuid=sc.stuid inner join courses co on co.courseid=sc.courseid;
+-------+-------------+----------+----------------+-------+
| stuid | name        | courseid | course         | score |
+-------+-------------+----------+----------------+-------+
|     1 | Shi Zhongyu |        2 | Kuihua Baodian |    77 |
|     1 | Shi Zhongyu |        6 | Weituo Zhang   |    93 |
|     2 | Shi Potian  |        2 | Kuihua Baodian |    47 |
|     2 | Shi Potian  |        5 | Daiyu Zanghua  |    97 |
|     3 | Xie Yanke   |        2 | Kuihua Baodian |    88 |
|     3 | Xie Yanke   |        6 | Weituo Zhang   |    75 |
|     4 | Ding Dian   |        5 | Daiyu Zanghua  |    71 |
|     4 | Ding Dian   |        2 | Kuihua Baodian |    89 |
|     5 | Yu Yutong   |        1 | Hamo Gong      |    39 |
|     5 | Yu Yutong   |        7 | Dagou Bangfa   |    63 |
|     6 | Shi Qing    |        1 | Hamo Gong      |    96 |
|     7 | Xi Ren      |        1 | Hamo Gong      |    86 |
|     7 | Xi Ren      |        7 | Dagou Bangfa   |    83 |
|     8 | Lin Daiyu   |        4 | Taiji Quan     |    57 |
|     8 | Lin Daiyu   |        3 | Jinshe Jianfa  |    93 |
+-------+-------------+----------+----------------+-------+

子查询

-- 大于平均年龄的学生信息
MariaDB [hellodb]> select * from students where age > (select avg(age) from students);

-- 将老师表的平均年龄的值赋给25号学生
MariaDB [hellodb]> update students set age=(select avg(age) from teachers) where stuid=25;