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

  1. 云栖社区>
  2. 博客>
  3. 正文

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

嗯哼9925 2017-12-11 16:04:00 浏览916
展开阅读全文

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

首先创建数据库中的表,数据库代码如下:

复制代码
/*
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 |
+----+-------+
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
复制代码

 

 

 




本文转自xwdreamer博客园博客,原文链接:http://www.cnblogs.com/xwdreamer/archive/2010/12/15/2297058.html,如需转载请自行联系原作者




网友评论

登录后评论
0/500
评论
嗯哼9925
+ 关注