mysql join操作【实例教程】
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操作了。
mysql join操作【实例教程】相关教程