mysql数据库中实现内连接、左连接、右连接

内连接：把两个表中数据对应的数据查出来 
外连接：以某个表为基础把对应数据查出来

 

首先创建数据库中的表，数据库代码如下：

/*Navicat MySQL Data TransferSource Server : localhost_3306Source Server Version : 50150Source Host : localhost:3306Source Database : storeTarget Server Type : MYSQLTarget Server Version : 50150File Encoding : 65001Date: 2010-12-15 16:27:53*/SET FOREIGN_KEY_CHECKS=0;-- ------------------------------ Table structure for `grade`-- ----------------------------DROP TABLE IF EXISTS `grade`;CREATE TABLE `grade` ( `no` int(11) NOT NULL AUTO_INCREMENT, `grade` int(11) NOT NULL, PRIMARY KEY (`no`)) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;-- ------------------------------ Records of grade-- ----------------------------INSERT INTO grade VALUES ('1', '90');INSERT INTO grade VALUES ('2', '80');INSERT INTO grade VALUES ('3', '70');-- ------------------------------ Table structure for `student`-- ----------------------------DROP TABLE IF EXISTS `student`;CREATE TABLE `student` ( `no` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(20) NOT NULL, PRIMARY KEY (`no`)) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;-- ------------------------------ Records of student-- ----------------------------INSERT INTO student VALUES ('1', 'a');INSERT INTO student VALUES ('2', 'b');INSERT INTO student VALUES ('3', 'c');INSERT INTO student VALUES ('4', 'd'); 

student表中的字段分别是no和name，grade表中的字段是no和grade。两张表中的no都代表的是学生的学号。

 

查询student表的结果：

 

mysql> select * from grade;+----+-------+| no | grade |+----+-------+| 1 | 90 || 2 | 80 || 3 | 70 |+----+-------+3 rows in set 

 

 

查询grade表的结果：

mysql> select * from student;+----+------+| no | name |+----+------+| 1 | a || 2 | b || 3 | c || 4 | d |+----+------+4 rows in set 

 

 

 

内连接 inner join（查找条件中对应的数据，no4没有数据不列出来） 

mysql> select * from student s inner join grade g on s.no=g.no; +----+------+----+-------+| no | name | no | grade |+----+------+----+-------+| 1 | a | 1 | 90 || 2 | b | 2 | 80 || 3 | c | 3 | 70 |+----+------+----+-------+3 rows in set 

 

左连接（左表中所有数据，右表中对应数据） 

mysql> select * from student as s left join grade as g on s.no=g.no; +----+------+------+-------+| no | name | no | grade |+----+------+------+-------+| 1 | a | 1 | 90 || 2 | b | 2 | 80 || 3 | c | 3 | 70 || 4 | d | NULL | NULL |+----+------+------+-------+4 rows in set 

 

右连接（右表中所有数据，左表中对应数据） 

mysql> select * from student as s right join grade as g on s.no=g.no; +----+------+----+-------+| no | name | no | grade |+----+------+----+-------+| 1 | a | 1 | 90 || 2 | b | 2 | 80 || 3 | c | 3 | 70 |+----+------+----+-------+3 rows in set