MySQL连接查询

select * from user; +---------+--------+---------------------+ | user_id | cos_id | user_creation_date | +---------+--------+---------------------+ | a | 3 | 2016-02-18 17:56:12 | | a1 | 1 | 2016-02-15 15:27:28 | | a2 | 1 | 2016-02-16 10:41:21 | | admin | 1 | 2016-02-15 10:47:51 | | q1 | 1 | 2016-02-21 13:17:05 | | q2 | 1 | 2016-02-21 13:17:05 | | q3 | 1 | 2016-02-21 13:17:05 | +---------+--------+---------------------+ 7 rows in set (0.00 sec) mysql> select * from password; +---------+------------------------------------+ | user_id | password | +---------+------------------------------------+ | admin | {enc8}IF8xUQIn+WSE4NBSSbTNUlc8wVY= | | a1 | {enc8}3t8+Udt7qGZB5eGvx1sYvuB9wVY= | | a2 | {enc8}sI5HqHAyktP0GYRfZ8YSQp1BxFY= | | a | NULL | | q1 | {enc8}4X3Rf0UqSpFh61bNRsisNlFIyVY= | | q2 | {enc8}4X3Rf0UqSpFh61bNRsisNlFIyVY= | | q3 | {enc8}wuHVCTVL+shkSrIFkvEStFJIyVY= | +---------+------------------------------------+ 7 rows in set (0.00 sec)

 

内连接方法：

mysql> select user.user_id,cos_id,user_creation_date,password FROM user,password WHERE user.user_id=password.user_id;
+---------+--------+---------------------+------------------------------------+
| user_id | cos_id | user_creation_date | password |
+---------+--------+---------------------+------------------------------------+
| admin | 1 | 2016-02-15 10:47:51 | {enc8}IF8xUQIn+WSE4NBSSbTNUlc8wVY= |
| a1 | 1 | 2016-02-15 15:27:28 | {enc8}3t8+Udt7qGZB5eGvx1sYvuB9wVY= |
| a2 | 1 | 2016-02-16 10:41:21 | {enc8}sI5HqHAyktP0GYRfZ8YSQp1BxFY= |
| a | 3 | 2016-02-18 17:56:12 | NULL |
| q1 | 1 | 2016-02-21 13:17:05 | {enc8}4X3Rf0UqSpFh61bNRsisNlFIyVY= |
| q2 | 1 | 2016-02-21 13:17:05 | {enc8}4X3Rf0UqSpFh61bNRsisNlFIyVY= |
| q3 | 1 | 2016-02-21 13:17:05 | {enc8}wuHVCTVL+shkSrIFkvEStFJIyVY= |
+---------+--------+---------------------+------------------------------------+
7 rows in set (0.00 sec)

 

外连接方法
分为左连接和右连接LEFT|RIGHT JOIN
有两个表如下：

mysql> SELECT * FROM user;
+---------+--------+---------------------+
| user_id | cos_id | user_creation_date |
+---------+--------+---------------------+
| a | 3 | 2016-02-18 17:56:12 |
| a1 | 1 | 2016-02-15 15:27:28 |
| a2 | 1 | 2016-02-16 10:41:21 |
| admin | 1 | 2016-02-15 10:47:51 |
| q1 | 1 | 2016-02-21 13:17:05 |
| q2 | 1 | 2016-02-21 13:17:05 |
| q3 | 1 | 2016-02-21 13:17:05 |
+---------+--------+---------------------+
7 rows in set (0.00 sec)

mysql> SELECT * FROM pass;
+---------+------------------------------------+
| user_id | password |
+---------+------------------------------------+
| admin | {enc8}IF8xUQIn+WSE4NBSSbTNUlc8wVY= |
| a1 | {enc8}3t8+Udt7qGZB5eGvx1sYvuB9wVY= |
| a2 | {enc8}sI5HqHAyktP0GYRfZ8YSQp1BxFY= |
| a | NULL |
| q1 | {enc8}4X3Rf0UqSpFh61bNRsisNlFIyVY= |
| q2 | {enc8}4X3Rf0UqSpFh61bNRsisNlFIyVY= |
| q3 | {enc8}wuHVCTVL+shkSrIFkvEStFJIyVY= |
| q4 | {enc8}wuHVCTVL+shkSrIFkvEStFJIyVY= |
| q5 | {enc8}wuHVCTVL+shkSrIFkvEStFJIyVY= |
| q6 | {enc8}wuHVCTVL+shkSrIFkvEStFJIyVY= |
| q7 | {enc8}wuHVCTVL+shkSrIFkvEStFJIyVY= |
+---------+------------------------------------+
11 rows in set (0.00 sec)

 

1、进行左连接查询

mysql> SELECT * FROM user LEFT JOIN pass ON user.user_id=pass.user_id;
+---------+--------+---------------------+---------+------------------------------------+
| user_id | cos_id | user_creation_date | user_id | password |
+---------+--------+---------------------+---------+------------------------------------+
| a | 3 | 2016-02-18 17:56:12 | a | NULL |
| a1 | 1 | 2016-02-15 15:27:28 | a1 | {enc8}3t8+Udt7qGZB5eGvx1sYvuB9wVY= |
| a2 | 1 | 2016-02-16 10:41:21 | a2 | {enc8}sI5HqHAyktP0GYRfZ8YSQp1BxFY= |
| admin | 1 | 2016-02-15 10:47:51 | admin | {enc8}IF8xUQIn+WSE4NBSSbTNUlc8wVY= |
| q1 | 1 | 2016-02-21 13:17:05 | q1 | {enc8}4X3Rf0UqSpFh61bNRsisNlFIyVY= |
| q2 | 1 | 2016-02-21 13:17:05 | q2 | {enc8}4X3Rf0UqSpFh61bNRsisNlFIyVY= |
| q3 | 1 | 2016-02-21 13:17:05 | q3 | {enc8}wuHVCTVL+shkSrIFkvEStFJIyVY= |
+---------+--------+---------------------+---------+------------------------------------+
7 rows in set (0.00 sec)

 

2、进行右连接查询

mysql> SELECT * FROM user RIGHT JOIN pass ON user.user_id=pass.user_id; 
+---------+--------+---------------------+---------+------------------------------------+
| user_id | cos_id | user_creation_date | user_id | password |
+---------+--------+---------------------+---------+------------------------------------+
| admin | 1 | 2016-02-15 10:47:51 | admin | {enc8}IF8xUQIn+WSE4NBSSbTNUlc8wVY= |
| a1 | 1 | 2016-02-15 15:27:28 | a1 | {enc8}3t8+Udt7qGZB5eGvx1sYvuB9wVY= |
| a2 | 1 | 2016-02-16 10:41:21 | a2 | {enc8}sI5HqHAyktP0GYRfZ8YSQp1BxFY= |
| a | 3 | 2016-02-18 17:56:12 | a | NULL |
| q1 | 1 | 2016-02-21 13:17:05 | q1 | {enc8}4X3Rf0UqSpFh61bNRsisNlFIyVY= |
| q2 | 1 | 2016-02-21 13:17:05 | q2 | {enc8}4X3Rf0UqSpFh61bNRsisNlFIyVY= |
| q3 | 1 | 2016-02-21 13:17:05 | q3 | {enc8}wuHVCTVL+shkSrIFkvEStFJIyVY= |
| NULL | NULL | NULL | q4 | {enc8}wuHVCTVL+shkSrIFkvEStFJIyVY= |
| NULL | NULL | NULL | q5 | {enc8}wuHVCTVL+shkSrIFkvEStFJIyVY= |
| NULL | NULL | NULL | q6 | {enc8}wuHVCTVL+shkSrIFkvEStFJIyVY= |
| NULL | NULL | NULL | q7 | {enc8}wuHVCTVL+shkSrIFkvEStFJIyVY= |
+---------+--------+---------------------+---------+------------------------------------+
11 rows in set (0.00 sec)

 

复合查询：
在查询条件后面继续加入其他条件可以让结果更加精确

mysql> select user.user_id,cos_id,user_creation_date,password FROM user,password WHERE user.user_id=password.user_id AND cos_id != 3;
+---------+--------+---------------------+------------------------------------+
| user_id | cos_id | user_creation_date | password |
+---------+--------+---------------------+------------------------------------+
| admin | 1 | 2016-02-15 10:47:51 | {enc8}IF8xUQIn+WSE4NBSSbTNUlc8wVY= |
| a1 | 1 | 2016-02-15 15:27:28 | {enc8}3t8+Udt7qGZB5eGvx1sYvuB9wVY= |
| a2 | 1 | 2016-02-16 10:41:21 | {enc8}sI5HqHAyktP0GYRfZ8YSQp1BxFY= |
| q1 | 1 | 2016-02-21 13:17:05 | {enc8}4X3Rf0UqSpFh61bNRsisNlFIyVY= |
| q2 | 1 | 2016-02-21 13:17:05 | {enc8}4X3Rf0UqSpFh61bNRsisNlFIyVY= |
| q3 | 1 | 2016-02-21 13:17:05 | {enc8}wuHVCTVL+shkSrIFkvEStFJIyVY= |
+---------+--------+---------------------+------------------------------------+
6 rows in set (0.00 sec)

 

 参考书籍：《MySQL入门很简单》

MySQL连接查询

标签：