一、关系数据操作
1、并(UNION)
把具有相同字段数目和字段类型的表合并在一起
2、笛卡尔积(CARTESIAN PRODUCT)
假设集合A={a, b},集合B={0, 1, 2},则两个集合的笛卡尔积为{(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)}。
3、内连接
在表关系的笛卡尔积数据记录中,保留表关系中所有匹配的数据记录,舍弃不匹配的数据记录。按照匹配的条件可以分成自然连接、等值连接和不等连接。
(1)自然连接(NATURAL JOIN)
特点:
- 在具体执行自然连接时,会自动判断相同名称的字段,然后进行数据中的匹配
- 在执行完自然连接的新关系中,虽然可以指定包含哪些字段,但是不能指定指定过程中的匹配天剑,即哪些字段的值进行匹配
- 在执行完自然连接的新关系中,执行过程中所匹配的字段只有一个,即会去掉重复的字段
(2)等值连接
与自然连接相比,等值连接数据关系操作需要在执行过程中用“符号=”指定匹配条件,在新关系中不会去掉重复字段。
(3)不等连接
与自然连接相比,等值连接数据关系操作需要在执行过程中用“符号!=”指定匹配条件,在新关系中不会去掉重复字段。
4、外连接(OUTER JOIN)
在表关系的笛卡尔积数据记录中,不仅保留表关系中所有匹配的数据记录,而且还会保留部分不匹配的数据记录,按照保留不匹配条件数据记录来源可以分为左外连接、右外连接和全外连接。
- 左外连接
表关系的笛卡尔积中,除了选择相匹配的数据记录,还包含关联左边表中不匹配的数据记录
- 右外连接
表关系的笛卡尔积中,除了选择相匹配(相等)的数据记录,还包含关联右边表中不匹配的数据记录
- 全外连接
表关系的笛卡尔积中,除了选择相匹配(相等)的数据记录,还包含关联左右边表中不匹配的数据记录
二、内连接查询
【语法】
select field1,field2……,fieldn
from join_tablename1 INNER JOIN join_tablename2 【INNTER JOIN join_tablenamen】
on join_condition
1、自然连接
【实例】查询每个雇员的姓名、职位和领导姓名
mysql> select e.ename employeename,e.job,l.ename loadername
-> from t_employee e inner join t_employee l
-> on e.mgr=l.empno;
+--------------+----------+------------+
| employeename | job | loadername |
+--------------+----------+------------+
| SCOTT | ANALYST | JONES |
| FORD | ANALYST | JONES |
| ALLEN | SALESMAN | BLACK |
| WARD | SALESMAN | BLACK |
| MARTIN | SALESMAN | BLACK |
| TURNER | SALESMAN | BLACK |
| JAMES | CLERK | BLACK |
| MILLER | CLERK | CLARK |
| ADAMS | CLERK | SCOTT |
| JONES | MANAGER | KING |
| BLACK | MANAGER | KING |
| CLARK | MANAGER | KING |
| SMITH | CLERK | FORD |
+--------------+----------+------------+
13 rows in set (0.00 sec)
ysql> select e.ename employeename,e.job,l.ename loadername
-> from t_employee e, t_employee l
-> where e.mgr=l.empno;
+--------------+----------+------------+
| employeename | job | loadername |
+--------------+----------+------------+
| SCOTT | ANALYST | JONES |
| FORD | ANALYST | JONES |
| ALLEN | SALESMAN | BLACK |
| WARD | SALESMAN | BLACK |
| MARTIN | SALESMAN | BLACK |
| TURNER | SALESMAN | BLACK |
| JAMES | CLERK | BLACK |
| MILLER | CLERK | CLARK |
| ADAMS | CLERK | SCOTT |
| JONES | MANAGER | KING |
| BLACK | MANAGER | KING |
| CLARK | MANAGER | KING |
| SMITH | CLERK | FORD |
+--------------+----------+------------+
13 rows in set (0.00 sec)
2、等值连接
【实例】查询每个雇员的编号、姓名、职位、部门名称、位置
mysql> select e.empno,e.ename,e.job,d.dname,d.loc
-> from t_employee e inner join t_dept d
-> on e.deptno=d.deptno;
+-------+--------+-----------+------------+----------+
| empno | ename | job | dname | loc |
+-------+--------+-----------+------------+----------+
| 7782 | CLARK | MANAGER | ACCOUNTING | NEW YORK |
| 7839 | KING | PRESIDENT | ACCOUNTING | NEW YORK |
| 7934 | MILLER | CLERK | ACCOUNTING | NEW YORK |
| 7369 | SMITH | CLERK | RESEARCH | DALLAS |
| 7566 | JONES | MANAGER | RESEARCH | DALLAS |
| 7788 | SCOTT | ANALYST | RESEARCH | DALLAS |
| 7876 | ADAMS | CLERK | RESEARCH | DALLAS |
| 7902 | FORD | ANALYST | RESEARCH | DALLAS |
| 7499 | ALLEN | SALESMAN | SALES | CHICAGO |
| 7521 | WARD | SALESMAN | SALES | CHICAGO |
| 7654 | MARTIN | SALESMAN | SALES | CHICAGO |
| 7698 | BLACK | MANAGER | SALES | CHICAGO |
| 7844 | TURNER | SALESMAN | SALES | CHICAGO |
| 7900 | JAMES | CLERK | SALES | CHICAGO |
+-------+--------+-----------+------------+----------+
14 rows in set (0.00 sec)
mysql> select e.empno,e.ename,e.job,d.dname,d.loc
-> from t_employee e,t_dept d
-> where e.deptno=d.deptno;
+-------+--------+-----------+------------+----------+
| empno | ename | job | dname | loc |
+-------+--------+-----------+------------+----------+
| 7782 | CLARK | MANAGER | ACCOUNTING | NEW YORK |
| 7839 | KING | PRESIDENT | ACCOUNTING | NEW YORK |
| 7934 | MILLER | CLERK | ACCOUNTING | NEW YORK |
| 7369 | SMITH | CLERK | RESEARCH | DALLAS |
| 7566 | JONES | MANAGER | RESEARCH | DALLAS |
| 7788 | SCOTT | ANALYST | RESEARCH | DALLAS |
| 7876 | ADAMS | CLERK | RESEARCH | DALLAS |
| 7902 | FORD | ANALYST | RESEARCH | DALLAS |
| 7499 | ALLEN | SALESMAN | SALES | CHICAGO |
| 7521 | WARD | SALESMAN | SALES | CHICAGO |
| 7654 | MARTIN | SALESMAN | SALES | CHICAGO |
| 7698 | BLACK | MANAGER | SALES | CHICAGO |
| 7844 | TURNER | SALESMAN | SALES | CHICAGO |
| 7900 | JAMES | CLERK | SALES | CHICAGO |
+-------+--------+-----------+------------+----------+
14 rows in set (0.00 sec)
【实例】查询每个雇员的编号、姓名、基本工资、职位、领导的姓名、部门名称和位置
mysql> select e.empno,e.ename employeename,e.sal,e.job,l.ename loadername,d.dname,d.loc
-> from t_employee e inner join t_employee l on e.mgr=l.empno
-> inner join t_dept d on e.deptno=d.deptno;
+-------+--------------+---------+----------+------------+------------+----------+
| empno | employeename | sal | job | loadername | dname | loc |
+-------+--------------+---------+----------+------------+------------+----------+
| 7788 | SCOTT | 3000.00 | ANALYST | JONES | RESEARCH | DALLAS |
| 7902 | FORD | 3000.00 | ANALYST | JONES | RESEARCH | DALLAS |
| 7499 | ALLEN | 1600.00 | SALESMAN | BLACK | SALES | CHICAGO |
| 7521 | WARD | 1250.00 | SALESMAN | BLACK | SALES | CHICAGO |
| 7654 | MARTIN | 1250.00 | SALESMAN | BLACK | SALES | CHICAGO |
| 7844 | TURNER | 1500.00 | SALESMAN | BLACK | SALES | CHICAGO |
| 7900 | JAMES | 950.00 | CLERK | BLACK | SALES | CHICAGO |
| 7934 | MILLER | 1300.00 | CLERK | CLARK | ACCOUNTING | NEW YORK |
| 7876 | ADAMS | 1100.00 | CLERK | SCOTT | RESEARCH | DALLAS |
| 7566 | JONES | 2975.00 | MANAGER | KING | RESEARCH | DALLAS |
| 7698 | BLACK | 2850.00 | MANAGER | KING | SALES | CHICAGO |
| 7782 | CLARK | 2450.00 | MANAGER | KING | ACCOUNTING | NEW YORK |
| 7369 | SMITH | 800.00 | CLERK | FORD | RESEARCH | DALLAS |
+-------+--------------+---------+----------+------------+------------+----------+
13 rows in set (0.06 sec)
mysql> select e.empno,e.ename employeename,e.sal,e.job,l.ename loadername,d.dname,d.loc
-> from t_employee e,t_employee l,t_dept d
-> where e.mgr=l.empno and e.deptno=d.deptno;
+-------+--------------+---------+----------+------------+------------+----------+
| empno | employeename | sal | job | loadername | dname | loc |
+-------+--------------+---------+----------+------------+------------+----------+
| 7788 | SCOTT | 3000.00 | ANALYST | JONES | RESEARCH | DALLAS |
| 7902 | FORD | 3000.00 | ANALYST | JONES | RESEARCH | DALLAS |
| 7499 | ALLEN | 1600.00 | SALESMAN | BLACK | SALES | CHICAGO |
| 7521 | WARD | 1250.00 | SALESMAN | BLACK | SALES | CHICAGO |
| 7654 | MARTIN | 1250.00 | SALESMAN | BLACK | SALES | CHICAGO |
| 7844 | TURNER | 1500.00 | SALESMAN | BLACK | SALES | CHICAGO |
| 7900 | JAMES | 950.00 | CLERK | BLACK | SALES | CHICAGO |
| 7934 | MILLER | 1300.00 | CLERK | CLARK | ACCOUNTING | NEW YORK |
| 7876 | ADAMS | 1100.00 | CLERK | SCOTT | RESEARCH | DALLAS |
| 7566 | JONES | 2975.00 | MANAGER | KING | RESEARCH | DALLAS |
| 7698 | BLACK | 2850.00 | MANAGER | KING | SALES | CHICAGO |
| 7782 | CLARK | 2450.00 | MANAGER | KING | ACCOUNTING | NEW YORK |
| 7369 | SMITH | 800.00 | CLERK | FORD | RESEARCH | DALLAS |
+-------+--------------+---------+----------+------------+------------+----------+
13 rows in set (0.00 sec)
3、不等连接
【实例】查询雇员编号大于其领导编号的每个雇员的姓名、职位、领导姓名
mysql> select e.ename employeename,e.job,l.ename loadername
-> from t_employee e inner join t_employee l
-> on e.empno=l.mgr and e.empno>l.empno;
+--------------+-----------+------------+
| employeename | job | loadername |
+--------------+-----------+------------+
| FORD | ANALYST | SMITH |
| BLACK | MANAGER | ALLEN |
| BLACK | MANAGER | WARD |
| KING | PRESIDENT | JONES |
| BLACK | MANAGER | MARTIN |
| KING | PRESIDENT | BLACK |
| KING | PRESIDENT | CLARK |
+--------------+-----------+------------+
7 rows in set (0.05 sec)
mysql> select e.ename employeename,e.job,l.ename loadername
-> from t_employee e,t_employee l
-> where e.empno=l.mgr and e.empno>l.empno;
+--------------+-----------+------------+
| employeename | job | loadername |
+--------------+-----------+------------+
| FORD | ANALYST | SMITH |
| BLACK | MANAGER | ALLEN |
| BLACK | MANAGER | WARD |
| KING | PRESIDENT | JONES |
| BLACK | MANAGER | MARTIN |
| KING | PRESIDENT | BLACK |
| KING | PRESIDENT | CLARK |
+--------------+-----------+------------+
7 rows in set (0.00 sec)
三、外连接查询
【语法】
select field1,field2……,fieldn
from join_tablename1 LEFT|RIGHT|FULL JOIN join_tablename2
on join_condition
1、左外连接
【实例】查询每个雇员的姓名、职位和领导姓名(KING职员没有领导也会显示出来)
mysql> select e.ename employeename,e.job,l.ename loadername
-> from t_employee e left join t_employee l
-> on e.mgr=l.empno;
+--------------+-----------+------------+
| employeename | job | loadername |
+--------------+-----------+------------+
| SMITH | CLERK | FORD |
| ALLEN | SALESMAN | BLACK |
| WARD | SALESMAN | BLACK |
| JONES | MANAGER | KING |
| MARTIN | SALESMAN | BLACK |
| BLACK | MANAGER | KING |
| CLARK | MANAGER | KING |
| SCOTT | ANALYST | JONES |
| KING | PRESIDENT | NULL |
| TURNER | SALESMAN | BLACK |
| ADAMS | CLERK | SCOTT |
| JAMES | CLERK | BLACK |
| FORD | ANALYST | JONES |
| MILLER | CLERK | CLARK |
+--------------+-----------+------------+
14 rows in set (0.00 sec)
(2)右外连接
【实例】查询每个雇员的编号、姓名、职位、部门名称、位置
mysql> select e.empno,e.ename,e.job,d.dname,d.loc
-> from t_dept d right join t_employee e
-> on e.deptno=d.deptno;
+-------+--------+-----------+------------+----------+
| empno | ename | job | dname | loc |
+-------+--------+-----------+------------+----------+
| 7369 | SMITH | CLERK | RESEARCH | DALLAS |
| 7499 | ALLEN | SALESMAN | SALES | CHICAGO |
| 7521 | WARD | SALESMAN | SALES | CHICAGO |
| 7566 | JONES | MANAGER | RESEARCH | DALLAS |
| 7654 | MARTIN | SALESMAN | SALES | CHICAGO |
| 7698 | BLACK | MANAGER | SALES | CHICAGO |
| 7782 | CLARK | MANAGER | ACCOUNTING | NEW YORK |
| 7788 | SCOTT | ANALYST | RESEARCH | DALLAS |
| 7839 | KING | PRESIDENT | ACCOUNTING | NEW YORK |
| 7844 | TURNER | SALESMAN | SALES | CHICAGO |
| 7876 | ADAMS | CLERK | RESEARCH | DALLAS |
| 7900 | JAMES | CLERK | SALES | CHICAGO |
| 7902 | FORD | ANALYST | RESEARCH | DALLAS |
| 7934 | MILLER | CLERK | ACCOUNTING | NEW YORK |
| 7744 | NULL | cjgong | NULL | NULL |
+-------+--------+-----------+------------+----------+
15 rows in set (0.00 sec)
四、合并查询数据记录
union合并时会去掉重复的,union all存在重复数据
【语法】
select field1,field2……,fieldn
from tablename1
union|union all
select field1,field2……,fieldn
from tablename2
union|union all
select field1,field2……,fieldn
from tablename3
【实例】
mysql> select * from t_cstudent
-> union
-> select * from t_mstudent;
+----------+------+
| name | sex |
+----------+------+
| ccjgong2 | 女 |
| ccjgong3 | 男 |
| ccjgong4 | 女 |
| ccjgong5 | 男 |
| cmcjgong | 男 |
| ccjgong1 | 男 |
| mcjgong2 | 女 |
| mcjgong3 | 男 |
| mcjgong1 | 女 |
| mcjgong4 | 女 |
| mcjgong5 | 男 |
+----------+------+
11 rows in set (0.00 sec)
mysql> select * from t_cstudent
-> union all
-> select * from t_mstudent;
+----------+------+
| name | sex |
+----------+------+
| ccjgong2 | 女 |
| ccjgong3 | 男 |
| ccjgong4 | 女 |
| ccjgong5 | 男 |
| cmcjgong | 男 |
| ccjgong1 | 男 |
| cmcjgong | 男 |
| mcjgong2 | 女 |
| mcjgong3 | 男 |
| mcjgong1 | 女 |
| mcjgong4 | 女 |
| mcjgong5 | 男 |
+----------+------+
12 rows in set (0.00 sec)
五、子查询
(1)返回结果为单行单列和单行多列的子查询
- 返回结果为单行单列子查询
【实例】查询雇员表中工资比SMITH还要高的全部雇员信息
mysql> select * from t_employee
-> where sal>(select sal from t_employee where ename='SMITH');
+-------+--------+-----------+------+------------+---------+---------+--------+
| empno | ename | job | MGR | Hiredate | sal | comm | deptno |
+-------+--------+-----------+------+------------+---------+---------+--------+
| 7499 | ALLEN | SALESMAN | 7698 | 1982-03-12 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1983-03-12 | 1250.00 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-03-12 | 2975.00 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-03-12 | 1250.00 | 1400.00 | 30 |
| 7698 | BLACK | MANAGER | 7839 | 1981-03-12 | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1985-03-12 | 2450.00 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1981-03-12 | 3000.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-03-12 | 5000.00 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1989-03-12 | 1500.00 | 0.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1998-03-12 | 1100.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1997-03-12 | 950.00 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 0000-00-00 | 3000.00 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1981-03-12 | 1300.00 | NULL | 10 |
| 7744 | NULL | cjgong | 7769 | 1981-03-12 | 4000.00 | NULL | 50 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.06 sec)
- 单行多列子查询
【实例】查询雇员表中工资和职位和SMITH一样的全部雇员信息
mysql> select ename,sal,job
-> from t_employee
-> where (sal,job)=(select sal,job from t_employee where ename='SMITH');
+-------+--------+-------+
| ename | sal | job |
+-------+--------+-------+
| SMITH | 800.00 | CLERK |
+-------+--------+-------+
1 row in set (0.08 sec)
(2)返回结果为多行单列子查询
- 带有关键字IN的子查询
mysql> select * from t_employee
-> where deptno in (select deptno from t_dept);
+-------+--------+-----------+------+------------+---------+---------+--------+
| empno | ename | job | MGR | Hiredate | sal | comm | deptno |
+-------+--------+-----------+------+------------+---------+---------+--------+
| 7369 | SMITH | CLERK | 7902 | 1981-03-12 | 800.00 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1982-03-12 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1983-03-12 | 1250.00 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-03-12 | 2975.00 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-03-12 | 1250.00 | 1400.00 | 30 |
| 7698 | BLACK | MANAGER | 7839 | 1981-03-12 | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1985-03-12 | 2450.00 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1981-03-12 | 3000.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-03-12 | 5000.00 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1989-03-12 | 1500.00 | 0.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1998-03-12 | 1100.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1997-03-12 | 950.00 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 0000-00-00 | 3000.00 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1981-03-12 | 1300.00 | NULL | 10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.06 sec)
- 带有关键字ANY的子查询
=ANY:其功能与关键字IN一样
>ANY(>=ANY):比子查询中返回数据记录中最小的还要大于(大于等于)数据记录
<ANY(<=ANY):比子查询中返回数据记录中最小的还要小于(小于等于)数据记录
mysql> select ename,sal from t_employee
-> where sal > any(select sal from t_employee where job='MANAGER');
+-------+---------+
| ename | sal |
+-------+---------+
| JONES | 2975.00 |
| BLACK | 2850.00 |
| SCOTT | 3000.00 |
| KING | 5000.00 |
| FORD | 3000.00 |
| NULL | 4000.00 |
+-------+---------+
6 rows in set (0.00 sec)
- 带有关键字ALL的子查询
>ALL(>=ALL):比子查询中返回数据记录中最大的还要大于(大于等于)数据记录
<ALL(<=ALL):比子查询中返回数据记录中最小的还要小于(小于等于)数据记录
mysql> select ename,sal from t_employee
-> where sal > all(select sal from t_employee where job='MANAGER');
+-------+---------+
| ename | sal |
+-------+---------+
| SCOTT | 3000.00 |
| KING | 5000.00 |
| FORD | 3000.00 |
| NULL | 4000.00 |
+-------+---------+
4 rows in set (0.00 sec)
- 带有关键字EXISTS
mysql> select * from t_dept c
-> where exists(select * from t_employee where deptno=c.deptno);
+--------+------------+----------+
| deptno | dname | loc |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
+--------+------------+----------+
3 rows in set (0.01 sec)
(3)返回结果为多行多列子查询
#内连接
mysql> select d.deptno,d.dname,d.loc,count(e.empno) number,avg(e.sal) average
-> from t_employee e inner join t_dept d
-> on e.deptno=d.deptno
-> group by d.deptno desc,d.dname,d.loc;
+--------+------------+----------+--------+-------------+
| deptno | dname | loc | number | average |
+--------+------------+----------+--------+-------------+
| 30 | SALES | CHICAGO | 6 | 1566.666667 |
| 20 | RESEARCH | DALLAS | 5 | 2175.000000 |
| 10 | ACCOUNTING | NEW YORK | 3 | 2916.666667 |
+--------+------------+----------+--------+-------------+
3 rows in set (0.02 sec)
mysql> select count(*) number
-> from t_employee e,t_dept d;
+--------+
| number |
+--------+
| 60 |
+--------+
1 row in set (0.00 sec)
#子连接
mysql> select d.deptno,d.dname,d.loc,number,average
-> from t_dept d inner join(
-> select deptno dno,count(empno) number,avg(sal) average
-> from t_employee
-> group by deptno desc) employee
-> on d.deptno=employee.dno;
+--------+------------+----------+--------+-------------+
| deptno | dname | loc | number | average |
+--------+------------+----------+--------+-------------+
| 10 | ACCOUNTING | NEW YORK | 3 | 2916.666667 |
| 20 | RESEARCH | DALLAS | 5 | 2175.000000 |
| 30 | SALES | CHICAGO | 6 | 1566.666667 |
+--------+------------+----------+--------+-------------+
3 rows in set (0.04 sec)
mysql> select count(*) number
-> from t_dept d,(
-> select deptno dno,count(empno) number,avg(sal) average
-> from t_employee
-> group by deptno) employee;
+--------+
| number |
+--------+
| 16 |
+--------+
1 row in set (0.01 sec)