mysql基础

左外连，右外连

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';