参考资料
准备工作
SQL:
create database test;
use test;
create table Persons(
Id_P int primary key AUTO_INCREMENT,
LastName varchar(30) not null default '',
FirstName varchar(30) not null default '',
Address varchar(30) not null default '',
City varchar(30) not null default ''
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
INSERT INTO Persons(Id_P, LastName, FirstName, Address, City)VALUES(1, 'Adams', 'John', 'Oxford Street', 'London');
INSERT INTO Persons(Id_P, LastName, FirstName, Address, City)VALUES(2, 'Bush', 'George', 'Fifth Avenue', 'New York');
INSERT INTO Persons(Id_P, LastName, FirstName, Address, City)VALUES(3, 'Carter', 'Thomas', 'Changan Street ', 'Beijing');
create table Orders(
Id_O int primary key AUTO_INCREMENT,
OrderNo int not null default 0,
Id_P int not null default 0
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
INSERT INTO Orders(Id_O, OrderNo, Id_P)VALUES(1, 77895, 3);
INSERT INTO Orders(Id_O, OrderNo, Id_P)VALUES(2, 44678, 3);
INSERT INTO Orders(Id_O, OrderNo, Id_P)VALUES(3, 22456, 1);
INSERT INTO Orders(Id_O, OrderNo, Id_P)VALUES(4, 24562, 1);
INSERT INTO Orders(Id_O, OrderNo, Id_P)VALUES(5, 34764, 65);
查看准备工作
Persons表
SQL:
select * from Persons;
结果：
+------+----------+-----------+-----------------+----------+
| Id_P | LastName | FirstName | Address | City |
+------+----------+-----------+-----------------+----------+
| 1 | Adams | John | Oxford Street | London |
| 2 | Bush | George | Fifth Avenue | New York |
| 3 | Carter | Thomas | Changan Street | Beijing |
+------+----------+-----------+-----------------+----------+
3 rows in set (0.00 sec)
Orders表
SQL:
select * from Orders;
结果：
+------+---------+------+
| Id_O | OrderNo | Id_P |
+------+---------+------+
| 1 | 77895 | 3 |
| 2 | 44678 | 3 |
| 3 | 22456 | 1 |
| 4 | 24562 | 1 |
| 5 | 34764 | 65 |
+------+---------+------+
5 rows in set (0.00 sec)
RIGHT JOIN
SQL:
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
RIGHT JOIN Orders
ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName;
结果
+----------+-----------+---------+
| LastName | FirstName | OrderNo |
+----------+-----------+---------+
| NULL | NULL | 34764 |
| Adams | John | 22456 |
| Adams | John | 24562 |
| Carter | Thomas | 44678 |
| Carter | Thomas | 77895 |
+----------+-----------+---------+s
5 rows in set (0.00 sec)
LEFT JOIN
SQL:
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
LEFT JOIN Orders
ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName;
结果
+----------+-----------+---------+
| LastName | FirstName | OrderNo |
+----------+-----------+---------+
| Adams | John | 24562 |
| Adams | John | 22456 |
| Bush | George | NULL |
| Carter | Thomas | 77895 |
| Carter | Thomas | 44678 |
+----------+-----------+---------+
5 rows in set (0.00 sec)
准备工作
SQL:
CREATE TABLE `EMP` (
`EMPNO` int(11) NOT NULL AUTO_INCREMENT,
`ENAME` varchar(10) DEFAULT NULL,
`JOB` varchar(9) DEFAULT NULL,
`MGR` int(11) DEFAULT NULL,
`HIREDATE` date DEFAULT NULL,
`SAL` float DEFAULT NULL,
`COMM` float DEFAULT NULL,
`DEPTNO` int(11) DEFAULT NULL,
PRIMARY KEY (`EMPNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES(7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800.0, NULL, 20);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES(7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600.0, 300, 30);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES(7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250.0, 500, 30);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES(7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975.0, NULL, 20);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES(7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250.0, 1400, 30);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES(7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850.0, NULL, 30);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES(7782, 'CLARK', 'MANAGER', 7839, '1981-06-21', 2450.0, NULL, 10);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES(7788, 'SCOTT', 'ANALYST', 7566, '1987-06-13', 3000.0, NULL, 20);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES(7839, 'KING', 'PRESIDENT', 0, '1981-11-17', 5000.0, NULL, 10);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES(7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500.0, NULL, 30);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES(7876, 'ADAMS', 'CLERK', 7788, '1987-06-13', 1100.0, NULL, 20);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES(7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950.0, NULL, 30);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES(7902, 'FORD', 'ANALYST', 7566, '1982-12-03', 3000.0, NULL, 20);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES(7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300.0, NULL, 10);
CREATE TABLE `DEPT` (
`DEPTNO` int(11) NOT NULL AUTO_INCREMENT,
`DNAME` varchar(14) DEFAULT NULL,
`LOC` varchar(13) DEFAULT NULL,
PRIMARY KEY (`DEPTNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO DEPT (DEPTNO, DNAME, LOC) VALUES(10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPT (DEPTNO, DNAME, LOC) VALUES(20, 'RESEARCH', 'DALLAS');
INSERT INTO DEPT (DEPTNO, DNAME, LOC) VALUES(30, 'SALES', 'CHICAGO');
INSERT INTO DEPT (DEPTNO, DNAME, LOC) VALUES(40, 'OPERATIONS', 'BOSTON');
查看准备的环境
EMP表
SQL:
select * from emp;
结果：
+-------+--------+-----------+------+------------+------+------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+-----------+------+------------+------+------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 | 300 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250 | 500 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-21 | 2450 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-06-13 | 3000 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500 | 0 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-06-13 | 1100 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1982-12-03 | 3000 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300 | NULL | 10 |
+-------+--------+-----------+------+------------+------+------+--------+
14 rows in set (0.00 sec)
DEPT表
SQL:
select * from dept;
结果：
+--------+------------+----------+
| DEPTNO | DNAME | LOC |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+--------+------------+----------+
4 rows in set (0.00 sec)
RIGHT JOIN
select e.*,d.dname,d.loc from emp e right join dept d on e.deptno = d.deptno;
结果：
+-------+--------+-----------+------+------------+------+------+--------+------------+----------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | dname | loc |
+-------+--------+-----------+------+------------+------+------+--------+------------+----------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800 | NULL | 20 | RESEARCH | DALLAS |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 | 300 | 30 | SALES | CHICAGO |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250 | 500 | 30 | SALES | CHICAGO |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975 | NULL | 20 | RESEARCH | DALLAS |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 | 30 | SALES | CHICAGO |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850 | NULL | 30 | SALES | CHICAGO |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-21 | 2450 | NULL | 10 | ACCOUNTING | NEW YORK |
| 7788 | SCOTT | ANALYST | 7566 | 1987-06-13 | 3000 | NULL | 20 | RESEARCH | DALLAS |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL | 10 | ACCOUNTING | NEW YORK |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500 | 0 | 30 | SALES | CHICAGO |
| 7876 | ADAMS | CLERK | 7788 | 1987-06-13 | 1100 | NULL | 20 | RESEARCH | DALLAS |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950 | NULL | 30 | SALES | CHICAGO |
| 7902 | FORD | ANALYST | 7566 | 1982-12-03 | 3000 | NULL | 20 | RESEARCH | DALLAS |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300 | NULL | 10 | ACCOUNTING | NEW YORK |
| NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | OPERATIONS | BOSTON |
+-------+--------+-----------+------+------------+------+------+--------+------------+----------+
15 rows in set (0.00 sec)
LEFT JOIN
SQL:
select e.*,d.dname,d.loc from emp e left join dept d on e.deptno = d.deptno;
结果：
+-------+--------+-----------+------+------------+------+------+--------+------------+----------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | dname | loc |
+-------+--------+-----------+------+------------+------+------+--------+------------+----------+
| 7782 | CLARK | MANAGER | 7839 | 1981-06-21 | 2450 | NULL | 10 | ACCOUNTING | NEW YORK |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL | 10 | ACCOUNTING | NEW YORK |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300 | NULL | 10 | ACCOUNTING | NEW YORK |
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800 | NULL | 20 | RESEARCH | DALLAS |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975 | NULL | 20 | RESEARCH | DALLAS |
| 7788 | SCOTT | ANALYST | 7566 | 1987-06-13 | 3000 | NULL | 20 | RESEARCH | DALLAS |
| 7876 | ADAMS | CLERK | 7788 | 1987-06-13 | 1100 | NULL | 20 | RESEARCH | DALLAS |
| 7902 | FORD | ANALYST | 7566 | 1982-12-03 | 3000 | NULL | 20 | RESEARCH | DALLAS |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 | 300 | 30 | SALES | CHICAGO |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250 | 500 | 30 | SALES | CHICAGO |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 | 30 | SALES | CHICAGO |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850 | NULL | 30 | SALES | CHICAGO |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500 | 0 | 30 | SALES | CHICAGO |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950 | NULL | 30 | SALES | CHICAGO |
+-------+--------+-----------+------+------------+------+------+--------+------------+----------+
14 rows in set (0.00 sec)
