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

Navicat MySQL Data Transfer Source Server : localhost_3306 Source Server Version : 50150 Source Host : localhost:3306 Source Database : store Target Server Type : MYSQL Target Server Version : 50150 File Encoding : 65001 Date: 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 |
+----+-------+
rows in set

查询grade表的结果：

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 |
+----+------+----+-------+
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  |
+----+------+------+-------+
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 |
+----+------+----+-------+
rows in set

 

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

标签：arch   字段   select   数据库   targe   arc   date   l数据库   bsp