mysql基础：5、mysql中的连接查询

来源：互联网发布：sdrsharp软件编辑：程序博客网时间：2024/06/05 05:19

1.分别创建学生信息表student、学生分数信息表score，作为连接查询的测试表。

CREATE TABLE STUDENT (
ID VARCHAR(11) NOT NULL,
NAME VARCHAR(20),
AGE INT(5),
PRIMARY KEY (ID)
);
INSERT INTO STUDENT VALUES('2017001','Andy',20);

INSERT INTO STUDENT VALUES('2017002','Tom',20);

INSERT INTO STUDENT VALUES('2017003','Lily',20);

INSERT INTO STUDENT VALUES('2017004','Lucy',20);

INSERT INTO STUDENT VALUES('2017005','Jack',20);

----------------------------------------------------------
CREATE TABLE SCORE(
ID INT(11) AUTO_INCREMENT,
SID VARCHAR(11),
CID VARCHAR(11),
SCORE INT(5),
PRIMARY KEY (ID)
);
INSERT INTO SCORE(SID,CID,SCORE) VALUES('2017001','10001',90);

INSERT INTO SCORE(SID,CID,SCORE) VALUES('2017002','10001',82);

INSERT INTO SCORE(SID,CID,SCORE) VALUES('2017003','10002',91);

INSERT INTO SCORE(SID,CID,SCORE) VALUES('2017004','10003',96);

INSERT INTO SCORE(SID,CID,SCORE) VALUES('2017009','10003',96);

2.内连接

即自然连接，考虑这种需求：查询有分数的学生信息。

mysql>SELECT STU.ID,STU.NAME,STU.AGE,SC.CID,SC.SCORE

FROM STUDENT STU , SCORE SC

WHERE STU.ID = SC.SID;

+---------+------+------+-------+-------+
| ID | NAME | AGE | CID | SCORE |
+---------+------+------+-------+-------+
| 2017001 | Andy | 20 | 10001 | 90 |
| 2017002 | Tom | 20 | 10001 | 82 |
| 2017003 | Lily | 20 | 10002 | 91 |
| 2017004 | Lucy | 20 | 10003 | 96 |
+---------+------+------+-------+-------+

4 rows in set (0.00 sec)

3.左连接

考虑这种需求：列出所有学生的所有成绩，每个学生有成绩就显示成绩，没有成绩就用NULL代替。

mysql>SELECT STU.ID,STU.NAME,STU.AGE,SC.CID,SC.SCORE
FROM STUDENT STU
LEFT JOIN SCORE SC
ON STU.ID = SC.SID;

4.右连接

考虑这种需求：列出每个成绩对应的学生信息，一个成绩有对应的学生，也可能没有对应的学生，比如这个学生开除了。有学生就把学生列出来，没有学生的话，学生信息这些字段的值使用NULL填充。

mysql>SELECT STU.ID,STU.NAME,STU.AGE,SC.CID,SC.SCORE
FROM STUDENT STU
RIGHT JOIN SCORE SC
ON STU.ID = SC.SID;

5.UNION 和 UNION ALL

UNION 与 UNION ALL 用于两个或多个结果集的合并，这些结果集对应的字段必须有相同名称和数据类型。

UNION 会去掉结果集中重复行，UNION ALL 则不会。

6.左连接与右连接中的 WHERE 条件语句。

（1）mysql>SELECT STU.ID,STU.NAME,STU.AGE,SC.CID,SC.SCORE
FROM STUDENT STU
LEFT JOIN SCORE SC
ON STU.ID = SC.SID

WHERE STU.ID='2017001';

+---------+------+------+-------+-------+
| ID | NAME | AGE | CID | SCORE |
+---------+------+------+-------+-------+
| 2017001 | Andy | 20 | 10001 | 90 |
+---------+------+------+-------+-------+
1 row in set (0.04 sec)

首先根据 ON STU.ID = SC.SID 条件，获取左连接的结果集；然后根据WHERE STU.ID='2017001' 条件，从左连接的结果集中获取 ID 为 2017001的结果信息。

（2）mysql>SELECT STU.ID,STU.NAME,STU.AGE,SC.CID,SC.SCORE
FROM STUDENT STU
RIGHT JOIN SCORE SC
ON STU.ID = SC.SID

WHERE STU.ID='2017001';

右连接的情况与（1）中类似。

（3）

mysql>SELECT STU.ID,STU.NAME,STU.AGE,SC.CID,SC.SCORE
FROM STUDENT STU
LEFT JOIN SCORE SC
ON (STU.ID = SC.SID AND STU.ID='2017001');

+---------+------+------+-------+-------+
| ID | NAME | AGE | CID | SCORE |
+---------+------+------+-------+-------+
| 2017001 | Andy | 20 | 10001 | 90 |
| 2017002 | Tom | 20 | NULL | NULL |
| 2017003 | Lily | 20 | NULL | NULL |
| 2017004 | Lucy | 20 | NULL | NULL |
| 2017005 | Jack | 20 | NULL | NULL |
+---------+------+------+-------+-------+
5 rows in set (0.00 sec)

根据条件 ON (STU.ID = SC.SID AND STU.ID='2017001') 查询出SID='2017001'的学生成绩；然后用这一条结果集与STUDENT表进行左连接，没有成绩的学生用NULL表示成绩。

0 0