MySQL连接查询

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了MySQL连接查询，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含6286字，纯文字阅读大概需要9分钟。

内容图文

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连接查询

标签：

本文系统来源：http://www.cnblogs.com/xiangshui/p/5205139.html

内容总结

以上是互联网集市为您收集整理的MySQL连接查询全部内容，希望文章能够帮你解决MySQL连接查询所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

---