左外连,右外连
CREATE DATABASE test_mysql; use test_mysql; CREATE TABLE left_tb( id INT(11) PRIMARY KEY AUTO_INCREMENT,name VARCHAR(32) ); CREATE TABLE right_tb( id INT(11) PRIMARY KEY AUTO_INCREMENT,name VARCHAR(32),age INT(11) ); INSERT INTO left_tb (name) VALUES ('zhangsan'); INSERT INTO left_tb (name) VALUES ('lisi'); INSERT INTO left_tb (name) VALUES ('wangwu'); SELECT * from left_tb; 1 zhangsan 2 lisi 3 wangwu INSERT INTO right_tb (name,age) VALUES ('aaa',11); INSERT INTO right_tb (name,age) VALUES ('bbb',12); INSERT INTO right_tb (name,age) VALUES ('ccc',13); INSERT INTO right_tb (name,age) VALUES ('ddd',14); SELECT * from right_tb; 1 aaa 11 2 bbb 12 3 ccc 13 4 ddd 14
SELECT * from left_tb l LEFT JOIN right_tb r ON l.id=r.id; +----+----------+------+------+------+ | id | name | id | name | age | +----+----------+------+------+------+ | 1 | zhangsan | 1 | aaa | 11 | | 2 | lisi | 2 | bbb | 12 | | 3 | wangwu | 3 | ccc | 13 | +----+----------+------+------+------+ SELECT * from left_tb l RIGHT JOIN right_tb r ON l.id=r.id; +------+----------+----+------+------+ | id | name | id | name | age | +------+----------+----+------+------+ | 1 | zhangsan | 1 | aaa | 11 | | 2 | lisi | 2 | bbb | 12 | | 3 | wangwu | 3 | ccc | 13 | | NULL | NULL | 4 | ddd | 14 | +------+----------+----+------+------+ SELECT * from left_tb l LEFT JOIN right_tb r ON l.id=r.age; +----+----------+------+------+------+ | id | name | id | name | age | +----+----------+------+------+------+ | 1 | zhangsan | NULL | NULL | NULL | | 2 | lisi | NULL | NULL | NULL | | 3 | wangwu | NULL | NULL | NULL | +----+----------+------+------+------+ SELECT * from left_tb l LEFT JOIN right_tb r ON 1=1; +----+----------+------+------+------+ | id | name | id | name | age | +----+----------+------+------+------+ | 1 | zhangsan | 1 | aaa | 11 | | 2 | lisi | 1 | aaa | 11 | | 3 | wangwu | 1 | aaa | 11 | | 1 | zhangsan | 2 | bbb | 12 | | 2 | lisi | 2 | bbb | 12 | | 3 | wangwu | 2 | bbb | 12 | | 1 | zhangsan | 3 | ccc | 13 | | 2 | lisi | 3 | ccc | 13 | | 3 | wangwu | 3 | ccc | 13 | | 1 | zhangsan | 4 | ddd | 14 | | 2 | lisi | 4 | ddd | 14 | | 3 | wangwu | 4 | ddd | 14 | +----+----------+------+------+------+
关于去重:
SELECT * from right_tb; +----+------+------+ | id | name | age | +----+------+------+ | 1 | aaa | 11 | | 2 | bbb | 12 | | 3 | ccc | 13 | | 4 | ddd | 14 | | 5 | ccc | 13 | | 6 | ddd | 14 | | 7 | ccc | 13 | | 8 | ddd | 14 | | 9 | ddd | 14 | | 10 | ddd | 14 | | 11 | ccc | 13 | | 12 | ddd | 15 | +----+------+------+
要求把name和age相同的记录当成重复记录
SELECT min(r.id),r.name,r.age from right_tb r GROUP BY r.age,r.name; +-----------+------+------+ | min(r.id) | name | age | +-----------+------+------+ | 1 | aaa | 11 | | 2 | bbb | 12 | | 3 | ccc | 13 | | 4 | ddd | 14 | | 12 | ddd | 15 | +-----------+------+------+
删除:
delete o from tb_order o join member m on o.user_id = m.id and m.phone_number='1xx27237628'; delete f from tb_order_refund f join tb_order o on f.order_id =o.id join member m on o.user_id = m.id and m.phone_number='1xx27237628'; delete r from tb_order_return r join tb_order o on r.order_id =o.id join member m on o.user_id = m.id and m.phone_number='1xx27237628';