MySql数据库基础2

创建“部门”表

CREATE TABLE department(
	id INT,
	NAME VARCHAR(50)
);

创建“员工”表

CREATE TABLE employee(
	id INT,
	NAME VARCHAR(50),
	depno INT
);

填入数据

部门表

员工表

内连接

SELECT e.`id`,e.`name` AS '员工名',d.`name` AS '部门' FROM employee e,department d WHERE e.`depno` = d.`id`;

SELECT e.`id`,e.`name` AS '员工名',d.`name` AS '部门' FROM employee e INNER JOIN department d ON e.`depno` = d.`id`;

外连接（左连接，右连接）

左连接

SELECT e.`id`,e.`name` AS '员工名',d.`name` AS '部门' FROM employee e LEFT OUTER JOIN department d ON e.`depno` = d.`id`;

右连接

SELECT e.`id`,e.`name` AS '员工名',d.`name` AS '部门' FROM employee e RIGHT OUTER JOIN department d ON e.`depno` = d.`id`;