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