MYSQL 语法常用

最新推荐文章于 2024-09-20 10:07:22 发布

yuyunliuhen

最新推荐文章于 2024-09-20 10:07:22 发布

阅读量687

点赞数

分类专栏： mysql 文章标签： mysql join insert query null table

本文链接：https://blog.csdn.net/yuyunliuhen/article/details/6123967

版权

mysql 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

独立新建表

CREATE TABLE TT (ID INT, NAME VARCHAR(20));

从已知表复制表

CREATE TABLE TT AS SELECT * FROM CC;





2011-01-07                 22:18:00

Mysql之inner join，left join，right join详解

首先借用官方的解释下：

inner join(等值连接)：只返回两个表中联结字段相等的行；

left join(左联接)：返回包括左表中的所有记录和右表中联结字段相等的记录；

right join(右联接)：返回包括右表中的所有记录和左表中联结字段相等的记录。

mysql> create table account( id int ,name varchar(16));
mysql> create account1 ( id int, sex int);

mysql> insert into account (id,name) values(1,'lee');
Query OK, 1 row affected (0.00 sec)

mysql> insert into account (id,name) values(2,'sophia');
Query OK, 1 row affected (0.00 sec)

mysql> insert into account (id,name) values(3,'unlown');
Query OK, 1 row affected (0.00 sec)

mysql> insert into account1 (id, sex) values (1,1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into account1 (id, sex) values (2,0);
Query OK, 1 row affected (0.00 sec)

mysql> select * from account;
+------+--------+
| id   | name   |
+------+--------+
|    1 | lee    |
|    2 | sophia |
|    3 | unlown |
+------+--------+
3 rows in set (0.00 sec)

mysql> select * from account1;
+------+------+
| id   | sex |
+------+------+
|    1 |    1 |
|    2 |    0 |
+------+------+
2 rows in set (0.00 sec)

mysql> select * from account left join account1 on account.id = account1.id;
+------+--------+------+------+
| id   | name   | id   | sex |
+------+--------+------+------+
|    1 | lee    |    1 |    1 |
|    2 | sophia |    2 |    0 |
|    3 | unlown | NULL | NULL |
+------+--------+------+------+
3 rows in set (0.00 sec)

the same as :

mysql> select * from account,account1 where account.id = account1.id;
+------+--------+------+------+
| id   | name   | id   | sex |
+------+--------+------+------+
|    1 | lee    |    1 |    1 |
|    2 | sophia |    2 |    0 |
+------+--------+------+------+
2 rows in set (0.00 sec)

mysql> select * from account left join account1 on account.id = account1.id;




+------+--------+------+------+


| id   | name   | id   | sex  |


+------+--------+------+------+


|    1 | lee    |    1 |    1 |


|    2 | sophia |    2 |    0 |


|    3 | unlown | NULL | NULL |


+------+--------+------+------+


3 rows in set (0.00 sec)





mysql> select * from account right join account1 on account.id = account1.id;




+------+--------+------+------+


| id   | name   | id   | sex  |


+------+--------+------+------+


|    1 | lee    |    1 |    1 |


|    2 | sophia |    2 |    0 |


+------+--------+------+------+


2 rows in set (0.00 sec)





mysql> select * from account inner join account1 using

 (id);

+------+--------+------+

| id   | name   | sex  |

+------+--------+------+

|    1 | lee    |    1 |

|    2 | sophia |    0 |

+------+--------+------+

2 rows in set (0.00 sec)