mysql各种join_MySql——各种join的区别

select c.cno,c.cname,t.tname from course c ,teacher t where t.tno&＃61;c.tno

首先创建表course&＃xff0c;sc&＃xff0c;teacher表。

course表&＃xff1a;

DROP TABLE IF EXISTS &＃96;course&＃96;;

CREATE TABLE &＃96;course&＃96; (

&＃96;cno&＃96; varchar(10) NOT NULL,

&＃96;cname&＃96; varchar(20) DEFAULT NULL,

&＃96;tno&＃96; varchar(20) NOT NULL,

PRIMARY KEY (&＃96;cno&＃96;,&＃96;tno&＃96;)

) ENGINE&＃61;InnoDB DEFAULT CHARSET&＃61;utf8;

-- ----------------------------

-- Records of course

-- ----------------------------

INSERT INTO &＃96;course&＃96; VALUES (&＃39;c001&＃39;, &＃39;J2SE&＃39;, &＃39;t002&＃39;);

INSERT INTO &＃96;course&＃96; VALUES (&＃39;c002&＃39;, &＃39;Java Web&＃39;, &＃39;t002&＃39;);

INSERT INTO &＃96;course&＃96; VALUES (&＃39;c003&＃39;, &＃39;SSH&＃39;, &＃39;t001&＃39;);

INSERT INTO &＃96;course&＃96; VALUES (&＃39;c004&＃39;, &＃39;Oracle&＃39;, &＃39;t001&＃39;);

INSERT INTO &＃96;course&＃96; VALUES (&＃39;c005&＃39;, &＃39;SQL SERVER 2005&＃39;, &＃39;t003&＃39;);

INSERT INTO &＃96;course&＃96; VALUES (&＃39;c006&＃39;, &＃39;C#&＃39;, &＃39;t003&＃39;);

INSERT INTO &＃96;course&＃96; VALUES (&＃39;c007&＃39;, &＃39;Javascript&＃39;, &＃39;t002&＃39;);

INSERT INTO &＃96;course&＃96; VALUES (&＃39;c008&＃39;, &＃39;DIV&＃43;CSS&＃39;, &＃39;t001&＃39;);

INSERT INTO &＃96;course&＃96; VALUES (&＃39;c009&＃39;, &＃39;PHP&＃39;, &＃39;t003&＃39;);

INSERT INTO &＃96;course&＃96; VALUES (&＃39;c010&＃39;, &＃39;EJB3.0&＃39;, &＃39;t002&＃39;);

sc表&＃xff1a;

DROP TABLE IF EXISTS &＃96;sc&＃96;;

CREATE TABLE &＃96;sc&＃96; (

&＃96;sno&＃96; varchar(10) NOT NULL,

&＃96;cno&＃96; varchar(10) NOT NULL,

&＃96;score&＃96; int(11) DEFAULT NULL,

PRIMARY KEY (&＃96;sno&＃96;,&＃96;cno&＃96;)

) ENGINE&＃61;InnoDB DEFAULT CHARSET&＃61;utf8;

-- ----------------------------

-- Records of sc

-- ----------------------------

INSERT INTO &＃96;sc&＃96; VALUES (&＃39;s001&＃39;, &＃39;c001&＃39;, &＃39;79&＃39;);

INSERT INTO &＃96;sc&＃96; VALUES (&＃39;s001&＃39;, &＃39;c002&＃39;, &＃39;83&＃39;);

INSERT INTO &＃96;sc&＃96; VALUES (&＃39;s001&＃39;, &＃39;c003&＃39;, &＃39;59&＃39;);

INSERT INTO &＃96;sc&＃96; VALUES (&＃39;s002&＃39;, &＃39;c001&＃39;, &＃39;81&＃39;);

INSERT INTO &＃96;sc&＃96; VALUES (&＃39;s002&＃39;, &＃39;c002&＃39;, &＃39;73&＃39;);

INSERT INTO &＃96;sc&＃96; VALUES (&＃39;s003&＃39;, &＃39;c001&＃39;, &＃39;82&＃39;);

INSERT INTO &＃96;sc&＃96; VALUES (&＃39;s003&＃39;, &＃39;c002&＃39;, &＃39;82&＃39;);

INSERT INTO &＃96;sc&＃96; VALUES (&＃39;s004&＃39;, &＃39;c001&＃39;, &＃39;61&＃39;);

teacher表&＃xff1a;

DROP TABLE IF EXISTS &＃96;teacher&＃96;;

CREATE TABLE &＃96;teacher&＃96; (

&＃96;tno&＃96; varchar(10) NOT NULL,

&＃96;tname&＃96; varchar(20) DEFAULT NULL,

PRIMARY KEY (&＃96;tno&＃96;)

) ENGINE&＃61;InnoDB DEFAULT CHARSET&＃61;utf8;

-- ----------------------------

-- Records of teacher

-- ----------------------------

INSERT INTO &＃96;teacher&＃96; VALUES (&＃39;t001&＃39;, &＃39;刘阳&＃39;);

INSERT INTO &＃96;teacher&＃96; VALUES (&＃39;t002&＃39;, &＃39;谌燕&＃39;);

INSERT INTO &＃96;teacher&＃96; VALUES (&＃39;t003&＃39;, &＃39;胡明星&＃39;);

1.inner join(内连接或等值连接) : 获取两个表中字段匹配关系的记录。

现在我们打算筛选出课程对应的教师名字&＃xff0c;可以使用以下语句&＃xff1a;

select c.cno,c.cname,t.tname from course c INNER JOIN teacher t on t.tno&＃61;c.tno

可以得到以下结果&＃xff1a;

不过目前使用内连接的次数比较少&＃xff0c;一般直接使用相同项相等即可。如上条SQL可以写为&＃xff1a;

select c.cno,c.cname,t.tname from course c ,teacher t where t.tno&＃61;c.tno

2.left join(左连接) : 获取左表的所有记录&＃xff0c;即使右表没有记录。

现在我们打算选出每科考试学生的平均成绩&＃xff0c;可以使用以下语句&＃xff1a;

SELECT c.cname,sc.avgs from course c LEFT JOIN (select cno,AVG(score) as avgs from sc GROUP BY cno) sc ON sc.cno&＃61;c.cno

运行后得出结果&＃xff1a;

3.right join(右连接) : 与左连接相反。

现在&＃xff0c;我们打算看看每个平均分对应的科目名字&＃xff0c;可以使用以下语句&＃xff1a;

SELECT c.cname,sc.avgs from course c RIGHT JOIN (select cno,AVG(score) as avgs from sc GROUP BY cno) sc ON sc.cno&＃61;c.cno

运行后得出结果&＃xff1a;

4.inner Join后不添加任何条件(全连接):

我们运行以下SQL&＃xff1a;

select c.cno,c.cname,t.tname from course c INNER JOIN teacher t

可以得到以下结果&＃xff1a;