mysql join操作【实例教程】

mysql join操作实例

1.准备数据

1.1创建表

create table test1 (
    id TINYINT PRIMARY key,
    name VARCHAR(225) 
);
create table test2 (
    id TINYINT PRIMARY key,
    name VARCHAR(225)
)

1.2插入数据

INSERT INTO test1 (id ,name) values(1,'test11');
INSERT INTO test1 (id ,name) values(2,'test12');
INSERT INTO test1 (id ,name) values(3,'test13');
INSERT INTO test1 (id ,name) values(4,'test14');
INSERT INTO test1 (id ,name) values(5,'test15');

INSERT INTO test2 (id ,name) values(3,'test11');
INSERT INTO test2 (id ,name) values(4,'test11');
INSERT INTO test2 (id ,name) values(5,'test11');
INSERT INTO test2 (id ,name) values(6,'test11');
INSERT INTO test2 (id ,name) values(7,'test11');
INSERT INTO test2 (id ,name) values(8,'test11');

2 left join

SELECT * FROM test1 t1 LEFT JOIN  test2 t2 on t1.id=t2.id;

左连接：左表是主表，数据数量为左表的数量。右表关联没有数据的以空显示。

3 right join

SELECT * FROM test1 t1 right JOIN  test2 t2 on t1.id=t2.id;

右连接：右表是主表，数据数量为右表的数量。左表关联没有数据的以空显示。

4 inner join

SELECT * FROM test1 t1  inner  join  test2 t2 on t1.id=t2.id;

显示的是两张表关联.的共有部分。

5 显示只属于A表不属于B表

SELECT * FROM test1 t1  left  join  test2 t2 on t1.id=t2.id where t2.id is  null;

6 显示 A表和B表的合集

SELECT * FROM test1 t1  left  join  test2 t2 on t1.id=t2.id 
UNION
SELECT * FROM test1 t1  right  join  test2 t2 on t1.id=t2.id ;

这样子就很清楚join操作了。