mysql左连接插入数据_Mysql数据库演示内连接、左连接、右连接

前提

建了两个表：student,worker。

CREATE TABLE

IF NOT EXISTS student (

student_id INT (11) NOT NULL AUTO_INCREMENT,

student_name VARCHAR (16) NOT NULL,

PRIMARY KEY (student_id)

) ENGINE = INNODB DEFAULT CHARSET = utf8;

CREATE TABLE

IF NOT EXISTS worker (

worker_id INT (11) NOT NULL AUTO_INCREMENT,

worker_name VARCHAR (16) NOT NULL,

PRIMARY KEY (worker_id)

) ENGINE = INNODB;

插入数据：

INSERT INTO student (student_id, student_name)

VALUES

(1, 'one'),

(2, 'two'),

(3, 'three'),

(4, 'four'),

(5, 'A');

INSERT INTO worker (worker_id, worker_name)

VALUES

(1, 'one'),

(2, 'two'),

(3, 'A'),

(4, 'B');

三种连接

内连接：

SELECT

*

FROM

student s

INNER JOIN worker w ON s.student_name = w.worker_name;

结果：

左连接：

SELECT

*

FROM

student s

LEFT JOIN worker w ON s.student_name = w.worker_name;

结果：

右连接：

SELECT

*

FROM

student s

RIGHT JOIN worker w ON s.student_name = w.worker_name;

结果：

结论

这三种连接就类似于下图：