MySQL的单表和多表查询

���们在前面曾构建过三个用于实验的表格，下面将基于这三个表进行实践。

# 建立一个用于实验的三个表格mysql> create table emp (    ->   empno varchar(10),    ->   ename varchar(50),    ->   job varchar(50),    ->   mgr int,    ->   hiredate timestamp,    ->   sal decimal(10, 2),    ->   comm decimal(10, 2),    ->   deptno int    -> );Query OK, 0 rows affected (0.03 sec)mysql> insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) values    -> ('007369', 'smith', 'clerk', 7902, '1980-12-17 00:00:00', 800.00, null, 20),    -> ('007499', 'allen', 'salesman', 7698, '1981-02-20 00:00:00', 1600.00, 300.00, 30),    -> ('007521', 'ward', 'salesman', 7698, '1981-02-22 00:00:00', 1250.00, 500.00, 30),    -> ('007566', 'jones', 'manager', 7839, '1981-04-02 00:00:00', 2975.00, null, 20),    -> ('007654', 'martin', 'salesman', 7698, '1981-09-28 00:00:00', 1250.00, 1400.00, 30),    -> ('007698', 'blake', 'manager', 7839, '1981-05-01 00:00:00', 2850.00, null, 30),    -> ('007782', 'clark', 'manager', 7839, '1981-06-09 00:00:00', 2450.00, null, 10),    -> ('007788', 'scott', 'analyst', 7566, '1987-04-19 00:00:00', 3000.00, null, 20),    -> ('007839', 'king', 'president', null, '1981-11-17 00:00:00', 5000.00, null, 10),    -> ('007844', 'turner', 'salesman', 7698, '1981-09-08 00:00:00', 1500.00, 0.00, 30),    -> ('007876', 'adams', 'clerk', 7788, '1987-05-23 00:00:00', 1100.00, null, 20),    -> ('007900', 'james', 'clerk', 7698, '1981-12-03 00:00:00', 950.00, null, 30),    -> ('007902', 'ford', 'analyst', 7566, '1981-12-03 00:00:00', 3000.00, null, 20),    -> ('007934', 'miller', 'clerk', 7782, '1982-01-23 00:00:00', 1300.00, null, 10);Query OK, 14 rows affected (0.01 sec)Records: 14  Duplicates: 0  Warnings: 0mysql> create table dept (    ->   deptno int,    ->   dname varchar(50),    ->   loc varchar(50)    -> );Query OK, 0 rows affected (0.03 sec)mysql> insert into dept (deptno, dname, loc) values    -> (10, 'accounting', 'new york'),    -> (20, 'research', 'dallas'),    -> (30, 'sales', 'chicago'),    -> (40, 'operations', 'boston');Query OK, 4 rows affected (0.01 sec)Records: 4  Duplicates: 0  Warnings: 0mysql> create table salgrade (    ->   grade int,    ->   losal int,    ->   hisal int    -> );Query OK, 0 rows affected (0.03 sec)mysql> insert into salgrade (grade, losal, hisal) values    -> (1, 700, 1200),    -> (2, 1201, 1400),    -> (3, 1401, 2000),    -> (4, 2001, 3000),    -> (5, 3001, 9999);Query OK, 5 rows affected (0.01 sec)Records: 5  Duplicates: 0  Warnings: 0mysql> select * from emp;+--------+--------+-----------+------+---------------------+---------+---------+--------+| empno  | ename  | job       | mgr  | hiredate            | sal     | comm    | deptno |+--------+--------+-----------+------+---------------------+---------+---------+--------+| 007369 | smith  | clerk     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 || 007499 | allen  | salesman  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 || 007521 | ward   | salesman  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 || 007566 | jones  | manager   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL |     20 || 007654 | martin | salesman  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 || 007698 | blake  | manager   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    NULL |     30 || 007782 | clark  | manager   | 7839 | 1981-06-09 00:00:00 | 2450.00 |    NULL |     10 || 007788 | scott  | analyst   | 7566 | 1987-04-19 00:00:00 | 3000.00 |    NULL |     20 || 007839 | king   | president | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 || 007844 | turner | salesman  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 || 007876 | adams  | clerk     | 7788 | 1987-05-23 00:00:00 | 1100.00 |    NULL |     20 || 007900 | james  | clerk     | 7698 | 1981-12-03 00:00:00 |  950.00 |    NULL |     30 || 007902 | ford   | analyst   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    NULL |     20 || 007934 | miller | clerk     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    NULL |     10 |+--------+--------+-----------+------+---------------------+---------+---------+--------+14 rows in set (0.00 sec)mysql> 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)mysql> select * from salgrade;+-------+-------+-------+| grade | losal | hisal |+-------+-------+-------+|     1 |   700 |  1200 ||     2 |  1201 |  1400 ||     3 |  1401 |  2000 ||     4 |  2001 |  3000 ||     5 |  3001 |  9999 |+-------+-------+-------+5 rows in set (0.00 sec)

还需要使得表之间有联系。

# 关联表格# 设置主键mysql> alter table dept modify column deptno int not null, add primary key (deptno);Query OK, 0 rows affected (0.07 sec)Records: 0  Duplicates: 0  Warnings: 0# 添加外键mysql> alter table emp add foreign key (deptno) references dept(deptno);Query OK, 14 rows affected (0.08 sec)Records: 14  Duplicates: 0  Warnings: 0# 查看设置mysql> desc emp;+----------+---------------+------+-----+-------------------+-----------------------------+| Field    | Type          | Null | Key | Default           | Extra                       |+----------+---------------+------+-----+-------------------+-----------------------------+| empno    | varchar(10)   | YES  |     | NULL              |                             || ename    | varchar(50)   | YES  |     | NULL              |                             || job      | varchar(50)   | YES  |     | NULL              |                             || mgr      | int(11)       | YES  |     | NULL              |                             || hiredate | timestamp     | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP || sal      | decimal(10,2) | YES  |     | NULL              |                             || comm     | decimal(10,2) | YES  |     | NULL              |                             || deptno   | int(11)       | YES  | MUL | NULL              |                             |+----------+---------------+------+-----+-------------------+-----------------------------+8 rows in set (0.00 sec)mysql> desc dept;+--------+-------------+------+-----+---------+-------+| Field  | Type        | Null | Key | Default | Extra |+--------+-------------+------+-----+---------+-------+| deptno | int(11)     | NO   | PRI | NULL    |       || dname  | varchar(50) | YES  |     | NULL    |       || loc    | varchar(50) | YES  |     | NULL    |       |+--------+-------------+------+-----+---------+-------+3 rows in set (0.00 sec)

1.单表查询

1. 查询工资高于 1000 或岗位为 manager 的雇员，同时还要满足他们的姓名首字母为 j

   （）

   # 需求 1mysql> select * from emp where ((sal>1000 or job='manager') and left(ename, 1)='j');+--------+-------+---------+------+---------------------+---------+------+--------+| empno  | ename | job     | mgr  | hiredate            | sal     | comm | deptno |+--------+-------+---------+------+---------------------+---------+------+--------+| 007566 | jones | manager | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL |     20 |+--------+-------+---------+------+---------------------+---------+------+--------+1 row in set (0.00 sec)select * from EMP where (sal>500 or job='MANAGER') and ename like 'J%';mysql> select * from emp where (sal>1000 or job='manager') and ename like 'J%';;+--------+-------+---------+------+---------------------+---------+------+--------+| empno  | ename | job     | mgr  | hiredate            | sal     | comm | deptno |+--------+-------+---------+------+---------------------+---------+------+--------+| 007566 | jones | manager | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL |     20 |+--------+-------+---------+------+---------------------+---------+------+--------+1 row in set (0.00 sec)

2. 按照部门号升序、雇员的工资降序来排序

   # 需求 2mysql> select sal, deptno, ename from emp order by deptno asc, sal desc;+---------+--------+--------+| sal     | deptno | ename  |+---------+--------+--------+| 5000.00 |     10 | king   || 2450.00 |     10 | clark  || 1300.00 |     10 | miller || 3000.00 |     20 | scott  || 3000.00 |     20 | ford   || 2975.00 |     20 | jones  || 1100.00 |     20 | adams  ||  800.00 |     20 | smith  || 2850.00 |     30 | blake  || 1600.00 |     30 | allen  || 1500.00 |     30 | turner || 1250.00 |     30 | ward   || 1250.00 |     30 | martin ||  950.00 |     30 | james  |+---------+--------+--------+14 rows in set (0.00 sec)

3. 使用年薪（年薪=月薪*12+奖金）进行降序排序

   # 需求 3mysql> select ename, sal*12+ifnull(comm,0) as 年薪 from emp order by '年薪' desc;+--------+----------+| ename  | 年薪     |+--------+----------+| smith  |  9600.00 || allen  | 19500.00 || ward   | 15500.00 || jones  | 35700.00 || martin | 16400.00 || blake  | 34200.00 || clark  | 29400.00 || scott  | 36000.00 || king   | 60000.00 || turner | 18000.00 || adams  | 13200.00 || james  | 11400.00 || ford   | 36000.00 || miller | 15600.00 |+--------+----------+14 rows in set (0.00 sec)

4. 显示工资最高的员工的名字和工作岗位

   # 需求 4mysql> select max(sal) from emp;+----------+| max(sal) |+----------+|  5000.00 |+----------+1 row in set (0.00 sec)mysql> select ename, job from emp where sal=5000;+-------+-----------+| ename | job       |+-------+-----------+| king  | president |+-------+-----------+1 row in set (0.00 sec)mysql> select ename, job from emp where sal=(select max(sal) from emp); # 复合查找，也叫“查找子句”+-------+-----------+| ename | job       |+-------+-----------+| king  | president |+-------+-----------+1 row in set (0.01 sec)

5. 显示工资高于平均工资的员工信息

   # 需求 5mysql> select * from emp where sal > (select avg(sal) from emp);+--------+-------+-----------+------+---------------------+---------+------+--------+| empno  | ename | job       | mgr  | hiredate            | sal     | comm | deptno |+--------+-------+-----------+------+---------------------+---------+------+--------+| 007566 | jones | manager   | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL |     20 || 007698 | blake | manager   | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL |     30 || 007782 | clark | manager   | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL |     10 || 007788 | scott | analyst   | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL |     20 || 007839 | king  | president | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL |     10 || 007902 | ford  | analyst   | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL |     20 |+--------+-------+-----------+------+---------------------+---------+------+--------+6 rows in set (0.00 sec)

6. 显示每个部门的平均工资和最高工资

   # 需求 6mysql> select deptno, avg(sal), max(sal) from emp group by deptno;+--------+-------------+----------+| deptno | avg(sal)    | max(sal) |+--------+-------------+----------+|     10 | 2916.666667 |  5000.00 ||     20 | 2175.000000 |  3000.00 ||     30 | 1566.666667 |  2850.00 |+--------+-------------+----------+3 rows in set (0.00 sec)

7. 显示平均工资低于 2000 的部门号和它的平均工资

   # 需求 7mysql> select deptno, avg(sal) 平均工资 from emp group by deptno having 平均工资 select job, format(avg(sal), 2) 平均工资, count(*) 人数 from emp group by job;+-----------+--------------+--------+| job       | 平均工资     | 人数   |+-----------+--------------+--------+| analyst   | 3,000.00     |      2 || clerk     | 1,037.50     |      4 || manager   | 2,758.33     |      3 || president | 5,000.00     |      1 || salesman  | 1,400.00     |      4 |+-----------+--------------+--------+5 rows in set (0.00 sec)

2.多表查询

1. 显示雇员名、雇员工资以及所在部门的名字

   # 需求 1# 将表合外表整合为一个表mysql> select * from emp, dept where emp.deptno=dept.deptno;+--------+--------+-----------+------+---------------------+---------+---------+--------+--------+------------+----------+| empno  | ename  | job       | mgr  | hiredate            | sal     | comm    | deptno | deptno | dname      | loc      |+--------+--------+-----------+------+---------------------+---------+---------+--------+--------+------------+----------+| 007782 | clark  | manager   | 7839 | 1981-06-09 00:00:00 | 2450.00 |    NULL |     10 |     10 | accounting | new york || 007839 | king   | president | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 |     10 | accounting | new york || 007934 | miller | clerk     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    NULL |     10 |     10 | accounting | new york || 007369 | smith  | clerk     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 |     20 | research   | dallas   || 007566 | jones  | manager   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL |     20 |     20 | research   | dallas   || 007788 | scott  | analyst   | 7566 | 1987-04-19 00:00:00 | 3000.00 |    NULL |     20 |     20 | research   | dallas   || 007876 | adams  | clerk     | 7788 | 1987-05-23 00:00:00 | 1100.00 |    NULL |     20 |     20 | research   | dallas   || 007902 | ford   | analyst   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    NULL |     20 |     20 | research   | dallas   || 007499 | allen  | salesman  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |     30 | sales      | chicago  || 007521 | ward   | salesman  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |     30 | sales      | chicago  || 007654 | martin | salesman  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 |     30 | sales      | chicago  || 007698 | blake  | manager   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    NULL |     30 |     30 | sales      | chicago  || 007844 | turner | salesman  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 |     30 | sales      | chicago  || 007900 | james  | clerk     | 7698 | 1981-12-03 00:00:00 |  950.00 |    NULL |     30 |     30 | sales      | chicago  |+--------+--------+-----------+------+---------------------+---------+---------+--------+--------+------------+----------+14 rows in set (0.00 sec)mysql> select emp.ename sal dname from emp, dept where emp.deptno=dept.deptno;ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'dname from emp, dept where emp.deptno=dept.deptno' at line 1mysql> select emp.ename sal, dname, from emp, dept where emp.deptno=dept.deptno;ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from emp, dept where emp.deptno=dept.deptno' at line 1mysql> select emp.ename, sal, dname from emp, dept where emp.deptno=dept.deptno;+--------+---------+------------+| ename  | sal     | dname      |+--------+---------+------------+| clark  | 2450.00 | accounting || king   | 5000.00 | accounting || miller | 1300.00 | accounting || smith  |  800.00 | research   || jones  | 2975.00 | research   || scott  | 3000.00 | research   || adams  | 1100.00 | research   || ford   | 3000.00 | research   || allen  | 1600.00 | sales      || ward   | 1250.00 | sales      || martin | 1250.00 | sales      || blake  | 2850.00 | sales      || turner | 1500.00 | sales      || james  |  950.00 | sales      |+--------+---------+------------+14 rows in set (0.00 sec)

2. 显示部门号为 10 的部门名，员工名和工资

   # 需求 2mysql> select dept.dname ,emp.ename, emp.sal from emp, dept where emp.deptno=dept.deptno and emp.deptno=10;+------------+--------+---------+| dname      | ename  | sal     |+------------+--------+---------+| accounting | clark  | 2450.00 || accounting | king   | 5000.00 || accounting | miller | 1300.00 |+------------+--------+---------+3 rows in set (0.00 sec)

3. 显示各个员工的姓名，工资，及工资级别

   # 需求 3# (1)查看工资等级mysql> select * from salgrade;+-------+-------+-------+| grade | losal | hisal |+-------+-------+-------+|     1 |   700 |  1200 ||     2 |  1201 |  1400 ||     3 |  1401 |  2000 ||     4 |  2001 |  3000 ||     5 |  3001 |  9999 |+-------+-------+-------+5 rows in set (0.00 sec)# (2)查看员工信息表mysql> select * from emp;+--------+--------+-----------+------+---------------------+---------+---------+--------+| empno  | ename  | job       | mgr  | hiredate            | sal     | comm    | deptno |+--------+--------+-----------+------+---------------------+---------+---------+--------+| 007369 | smith  | clerk     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 || 007499 | allen  | salesman  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 || 007521 | ward   | salesman  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 || 007566 | jones  | manager   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL |     20 || 007654 | martin | salesman  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 || 007698 | blake  | manager   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    NULL |     30 || 007782 | clark  | manager   | 7839 | 1981-06-09 00:00:00 | 2450.00 |    NULL |     10 || 007788 | scott  | analyst   | 7566 | 1987-04-19 00:00:00 | 3000.00 |    NULL |     20 || 007839 | king   | president | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 || 007844 | turner | salesman  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 || 007876 | adams  | clerk     | 7788 | 1987-05-23 00:00:00 | 1100.00 |    NULL |     20 || 007900 | james  | clerk     | 7698 | 1981-12-03 00:00:00 |  950.00 |    NULL |     30 || 007902 | ford   | analyst   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    NULL |     20 || 007934 | miller | clerk     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    NULL |     10 |+--------+--------+-----------+------+---------------------+---------+---------+--------+14 rows in set (0.00 sec)# (3)整合两表（求笛卡尔积）mysql> select * from emp, salgrade;+--------+--------+-----------+------+---------------------+---------+---------+--------+-------+-------+-------+| empno  | ename  | job       | mgr  | hiredate            | sal     | comm    | deptno | grade | losal | hisal |+--------+--------+-----------+------+---------------------+---------+---------+--------+-------+-------+-------+| 007369 | smith  | clerk     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 |     1 |   700 |  1200 || 007369 | smith  | clerk     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 |     2 |  1201 |  1400 || 007369 | smith  | clerk     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 |     3 |  1401 |  2000 || 007369 | smith  | clerk     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 |     4 |  2001 |  3000 || 007369 | smith  | clerk     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 |     5 |  3001 |  9999 || 007499 | allen  | salesman  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |     1 |   700 |  1200 || 007499 | allen  | salesman  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |     2 |  1201 |  1400 || 007499 | allen  | salesman  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |     3 |  1401 |  2000 || 007499 | allen  | salesman  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |     4 |  2001 |  3000 || 007499 | allen  | salesman  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |     5 |  3001 |  9999 || 007521 | ward   | salesman  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |     1 |   700 |  1200 || 007521 | ward   | salesman  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |     2 |  1201 |  1400 || 007521 | ward   | salesman  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |     3 |  1401 |  2000 || 007521 | ward   | salesman  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |     4 |  2001 |  3000 || 007521 | ward   | salesman  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |     5 |  3001 |  9999 || 007566 | jones  | manager   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL |     20 |     1 |   700 |  1200 || 007566 | jones  | manager   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL |     20 |     2 |  1201 |  1400 || 007566 | jones  | manager   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL |     20 |     3 |  1401 |  2000 || 007566 | jones  | manager   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL |     20 |     4 |  2001 |  3000 || 007566 | jones  | manager   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL |     20 |     5 |  3001 |  9999 || 007654 | martin | salesman  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 |     1 |   700 |  1200 || 007654 | martin | salesman  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 |     2 |  1201 |  1400 || 007654 | martin | salesman  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 |     3 |  1401 |  2000 || 007654 | martin | salesman  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 |     4 |  2001 |  3000 || 007654 | martin | salesman  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 |     5 |  3001 |  9999 || 007698 | blake  | manager   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    NULL |     30 |     1 |   700 |  1200 || 007698 | blake  | manager   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    NULL |     30 |     2 |  1201 |  1400 || 007698 | blake  | manager   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    NULL |     30 |     3 |  1401 |  2000 || 007698 | blake  | manager   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    NULL |     30 |     4 |  2001 |  3000 || 007698 | blake  | manager   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    NULL |     30 |     5 |  3001 |  9999 || 007782 | clark  | manager   | 7839 | 1981-06-09 00:00:00 | 2450.00 |    NULL |     10 |     1 |   700 |  1200 || 007782 | clark  | manager   | 7839 | 1981-06-09 00:00:00 | 2450.00 |    NULL |     10 |     2 |  1201 |  1400 || 007782 | clark  | manager   | 7839 | 1981-06-09 00:00:00 | 2450.00 |    NULL |     10 |     3 |  1401 |  2000 || 007782 | clark  | manager   | 7839 | 1981-06-09 00:00:00 | 2450.00 |    NULL |     10 |     4 |  2001 |  3000 || 007782 | clark  | manager   | 7839 | 1981-06-09 00:00:00 | 2450.00 |    NULL |     10 |     5 |  3001 |  9999 || 007788 | scott  | analyst   | 7566 | 1987-04-19 00:00:00 | 3000.00 |    NULL |     20 |     1 |   700 |  1200 || 007788 | scott  | analyst   | 7566 | 1987-04-19 00:00:00 | 3000.00 |    NULL |     20 |     2 |  1201 |  1400 || 007788 | scott  | analyst   | 7566 | 1987-04-19 00:00:00 | 3000.00 |    NULL |     20 |     3 |  1401 |  2000 || 007788 | scott  | analyst   | 7566 | 1987-04-19 00:00:00 | 3000.00 |    NULL |     20 |     4 |  2001 |  3000 || 007788 | scott  | analyst   | 7566 | 1987-04-19 00:00:00 | 3000.00 |    NULL |     20 |     5 |  3001 |  9999 || 007839 | king   | president | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 |     1 |   700 |  1200 || 007839 | king   | president | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 |     2 |  1201 |  1400 || 007839 | king   | president | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 |     3 |  1401 |  2000 || 007839 | king   | president | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 |     4 |  2001 |  3000 || 007839 | king   | president | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 |     5 |  3001 |  9999 || 007844 | turner | salesman  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 |     1 |   700 |  1200 || 007844 | turner | salesman  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 |     2 |  1201 |  1400 || 007844 | turner | salesman  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 |     3 |  1401 |  2000 || 007844 | turner | salesman  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 |     4 |  2001 |  3000 || 007844 | turner | salesman  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 |     5 |  3001 |  9999 || 007876 | adams  | clerk     | 7788 | 1987-05-23 00:00:00 | 1100.00 |    NULL |     20 |     1 |   700 |  1200 || 007876 | adams  | clerk     | 7788 | 1987-05-23 00:00:00 | 1100.00 |    NULL |     20 |     2 |  1201 |  1400 || 007876 | adams  | clerk     | 7788 | 1987-05-23 00:00:00 | 1100.00 |    NULL |     20 |     3 |  1401 |  2000 || 007876 | adams  | clerk     | 7788 | 1987-05-23 00:00:00 | 1100.00 |    NULL |     20 |     4 |  2001 |  3000 || 007876 | adams  | clerk     | 7788 | 1987-05-23 00:00:00 | 1100.00 |    NULL |     20 |     5 |  3001 |  9999 || 007900 | james  | clerk     | 7698 | 1981-12-03 00:00:00 |  950.00 |    NULL |     30 |     1 |   700 |  1200 || 007900 | james  | clerk     | 7698 | 1981-12-03 00:00:00 |  950.00 |    NULL |     30 |     2 |  1201 |  1400 || 007900 | james  | clerk     | 7698 | 1981-12-03 00:00:00 |  950.00 |    NULL |     30 |     3 |  1401 |  2000 || 007900 | james  | clerk     | 7698 | 1981-12-03 00:00:00 |  950.00 |    NULL |     30 |     4 |  2001 |  3000 || 007900 | james  | clerk     | 7698 | 1981-12-03 00:00:00 |  950.00 |    NULL |     30 |     5 |  3001 |  9999 || 007902 | ford   | analyst   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    NULL |     20 |     1 |   700 |  1200 || 007902 | ford   | analyst   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    NULL |     20 |     2 |  1201 |  1400 || 007902 | ford   | analyst   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    NULL |     20 |     3 |  1401 |  2000 || 007902 | ford   | analyst   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    NULL |     20 |     4 |  2001 |  3000 || 007902 | ford   | analyst   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    NULL |     20 |     5 |  3001 |  9999 || 007934 | miller | clerk     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    NULL |     10 |     1 |   700 |  1200 || 007934 | miller | clerk     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    NULL |     10 |     2 |  1201 |  1400 || 007934 | miller | clerk     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    NULL |     10 |     3 |  1401 |  2000 || 007934 | miller | clerk     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    NULL |     10 |     4 |  2001 |  3000 || 007934 | miller | clerk     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    NULL |     10 |     5 |  3001 |  9999 |+--------+--------+-----------+------+---------------------+---------+---------+--------+-------+-------+-------+70 rows in set (0.00 sec)# (4)列出员工姓名、员工工资、工资对应等级mysql> select emp.ename, emp.sal, salgrade.grade from emp, salgrade where sal between salgrade.losal and salgrade.hisal;+--------+---------+-------+| ename  | sal     | grade |+--------+---------+-------+| smith  |  800.00 |     1 || allen  | 1600.00 |     3 || ward   | 1250.00 |     2 || jones  | 2975.00 |     4 || martin | 1250.00 |     2 || blake  | 2850.00 |     4 || clark  | 2450.00 |     4 || scott  | 3000.00 |     4 || king   | 5000.00 |     5 || turner | 1500.00 |     3 || adams  | 1100.00 |     1 || james  |  950.00 |     1 || ford   | 3000.00 |     4 || miller | 1300.00 |     2 |+--------+---------+-------+14 rows in set (0.00 sec)

补充：同一张表也可以进行笛卡尔积，也就是“自连接”

# 尝试自连接mysql> select * from salgrade as t1, salgrade as t2;+-------+-------+-------+-------+-------+-------+| grade | losal | hisal | grade | losal | hisal |+-------+-------+-------+-------+-------+-------+|     1 |   700 |  1200 |     1 |   700 |  1200 ||     2 |  1201 |  1400 |     1 |   700 |  1200 ||     3 |  1401 |  2000 |     1 |   700 |  1200 ||     4 |  2001 |  3000 |     1 |   700 |  1200 ||     5 |  3001 |  9999 |     1 |   700 |  1200 ||     1 |   700 |  1200 |     2 |  1201 |  1400 ||     2 |  1201 |  1400 |     2 |  1201 |  1400 ||     3 |  1401 |  2000 |     2 |  1201 |  1400 ||     4 |  2001 |  3000 |     2 |  1201 |  1400 ||     5 |  3001 |  9999 |     2 |  1201 |  1400 ||     1 |   700 |  1200 |     3 |  1401 |  2000 ||     2 |  1201 |  1400 |     3 |  1401 |  2000 ||     3 |  1401 |  2000 |     3 |  1401 |  2000 ||     4 |  2001 |  3000 |     3 |  1401 |  2000 ||     5 |  3001 |  9999 |     3 |  1401 |  2000 ||     1 |   700 |  1200 |     4 |  2001 |  3000 ||     2 |  1201 |  1400 |     4 |  2001 |  3000 ||     3 |  1401 |  2000 |     4 |  2001 |  3000 ||     4 |  2001 |  3000 |     4 |  2001 |  3000 ||     5 |  3001 |  9999 |     4 |  2001 |  3000 ||     1 |   700 |  1200 |     5 |  3001 |  9999 ||     2 |  1201 |  1400 |     5 |  3001 |  9999 ||     3 |  1401 |  2000 |     5 |  3001 |  9999 ||     4 |  2001 |  3000 |     5 |  3001 |  9999 ||     5 |  3001 |  9999 |     5 |  3001 |  9999 |+-------+-------+-------+-------+-------+-------+25 rows in set (0.00 sec)mysql> select * from salgrade;+-------+-------+-------+| grade | losal | hisal |+-------+-------+-------+|     1 |   700 |  1200 ||     2 |  1201 |  1400 ||     3 |  1401 |  2000 ||     4 |  2001 |  3000 ||     5 |  3001 |  9999 |+-------+-------+-------+5 rows in set (0.00 sec)

有些情况下是需要自连接的，例如“显示员工 ford 的上级领导的编号和姓名”：

# 尝试寻找上级领导# (1)查看员工表mysql> select * from emp;+--------+--------+-----------+------+---------------------+---------+---------+--------+| empno  | ename  | job       | mgr  | hiredate            | sal     | comm    | deptno |+--------+--------+-----------+------+---------------------+---------+---------+--------+| 007369 | smith  | clerk     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 || 007499 | allen  | salesman  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 || 007521 | ward   | salesman  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 || 007566 | jones  | manager   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL |     20 || 007654 | martin | salesman  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 || 007698 | blake  | manager   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    NULL |     30 || 007782 | clark  | manager   | 7839 | 1981-06-09 00:00:00 | 2450.00 |    NULL |     10 || 007788 | scott  | analyst   | 7566 | 1987-04-19 00:00:00 | 3000.00 |    NULL |     20 || 007839 | king   | president | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 || 007844 | turner | salesman  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 || 007876 | adams  | clerk     | 7788 | 1987-05-23 00:00:00 | 1100.00 |    NULL |     20 || 007900 | james  | clerk     | 7698 | 1981-12-03 00:00:00 |  950.00 |    NULL |     30 || 007902 | ford   | analyst   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    NULL |     20 || 007934 | miller | clerk     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    NULL |     10 |+--------+--------+-----------+------+---------------------+---------+---------+--------+14 rows in set (0.00 sec)# (2)查看某个员工的上级领导编号mysql> select mgr from emp where ename='ford';+------+| mgr  |+------+| 7566 |+------+1 row in set (0.00 sec)# (3)查看领导编号对应的领导姓名mysql> select ename, empno from emp where empno=7566;+-------+--------+| ename | empno  |+-------+--------+| jones | 007566 |+-------+--------+1 row in set (0.00 sec)# (4)子查询做法mysql> select ename, empno from emp where empno=(select mgr from emp where ename='ford');+-------+--------+| ename | empno  |+-------+--------+| jones | 007566 |+-------+--------+1 row in set (0.01 sec)# (5)自连接做法mysql> select e2.empno 领导编号, e2.ename 领导名  from emp as e1, emp as e2 where e1.ename='ford' and e1.mgr=e2.empno;+--------------+-----------+| 领导编号     | 领导名    |+--------------+-----------+| 007566       | jones     |+--------------+-----------+1 row in set (0.00 sec)