Mysql学习MySQL多表数据记录查询详解
《Mysql学习MySQL多表数据记录查询详解》要点: MYSQL必读在实际应用中,经常需要实现在一个查询语句中显示多张表的数据,这就是所谓的多表数据记录连接查询,简称来年将诶查询.???? MYSQL必读在具体实现连接查询操作时,首先将两个或两个以上的表按照某个条件连接起来,然后再查询到所要求的数据记录.连接查询分为内连接查询和外连接查询.? MYSQL必读在具体应用中,如果需要实现多表数据记录查询,一般不使用连接查询,因为该操作效率比较低.于是MySQL又提供 了连接查询的替代操作,子查询操作. MYSQL必读1.1 并(UNION): MYSQL必读1.2 笛卡尔积(CARTESIAN PRODUCT): MYSQL必读1.3 内连接(INNER JOIN): MYSQL必读1.4 外连接(OUTER JOIN): MYSQL必读2.内连接查询: MYSQL必读内连接查询语法为: MYSQL必读
select field1,field2 ...fieldn
from join_tablename1 inner join join_tablename2 [inner join join_tablename]
on join_condition
MYSQL必读//参数filedn 表示所要查询的字段名称,来源于所连接的表join_tablename1 和 join_tablename2,关键字inner join表进行内连接,join_condition表示进行匹配的条件. MYSQL必读2.1 自连接: MYSQL必读示例(查询每个雇员的姓名、职位、领导姓名): MYSQL必读
mysql> select e.ename,e.job,l.ename from t_employee e inner join t_employee l on e.MGR=l.empno;
+---------+----------+-------+
| ename | job | ename |
+---------+----------+-------+
| SCOTT | ANALYST | JONES |
| FORD | ANALYST | JONES |
| ALLEN | SALESMAN | BLAKE |
| MARD | SALESMAN | BLAKE |
| MARRTIN | SALESMAN | BLAKE |
| TURNER | SALESMAN | BLAKE |
| JAMES | CLEAR | BLAKE |
| MILLER | CLEAR | CLARK |
| ADAMS | CLEAR | SCOTT |
| JONES | MANAGER | KING |
| BLAKE | MANAGER | KING |
| CLARK | MANAGER | KING |
| SMITH | CLEAR | FORD |
+---------+----------+-------+
13 rows in set (0.00 sec)
MYSQL必读2.2等值连接: MYSQL必读示例: MYSQL必读
mysql> select e.empno,e.ename,d.dname,d.loc from t_employee e inner join t_dept d on e.deptno=d.deptno;
+-------+---------+-----------+------------+----------+
| empno | ename | job | dname | loc |
+-------+---------+-----------+------------+----------+
| 7788 | SCOTT | ANALYST | ACCOUNTING | NEW YORK |
| 7839 | KING | PRESIDENT | ACCOUNTING | NEW YORK |
| 7934 | MILLER | CLEAR | ACCOUNTING | NEW YORK |
| 7369 | SMITH | CLEAR | RESEARCH | DALLAS |
| 7499 | ALLEN | SALESMAN | RESEARCH | DALLAS |
| 7566 | JONES | MANAGER | RESEARCH | DALLAS |
| 7782 | CLARK | MANAGER | RESEARCH | DALLAS |
| 7876 | ADAMS | CLEAR | RESEARCH | DALLAS |
| 7902 | FORD | ANALYST | RESEARCH | DALLAS |
| 7521 | MARD | SALESMAN | SALES | CHICAGO |
| 7654 | MARRTIN | SALESMAN | SALES | CHICAGO |
| 7698 | BLAKE | MANAGER | SALES | CHICAGO |
| 7844 | TURNER | SALESMAN | SALES | CHICAGO |
| 7900 | JAMES | CLEAR | SALES | CHICAGO |
+-------+---------+-----------+------------+----------+
14 rows in set (0.00 sec)
MYSQL必读2.3不等连接: MYSQL必读示例: MYSQL必读
mysql> select e.ename employeename,l.ename loadername from t_employee e inner join t_employee l on e.mgr=l.empno
and e.empno>l.empno;
+--------------+----------+------------+
| employeename | job | loadername |
+--------------+----------+------------+
| SCOTT | ANALYST | JONES |
| FORD | ANALYST | JONES |
| TURNER | SALESMAN | BLAKE |
| JAMES | CLEAR | BLAKE |
| MILLER | CLEAR | CLARK |
| ADAMS | CLEAR | SCOTT |
+--------------+----------+------------+
6 rows in set (0.00 sec)
MYSQL必读3.外连接查询: MYSQL必读语法为: MYSQL必读
select field1,field2,...fieldn
from join_tablename1 left|rigth|full [outer] join join_tablename2
on join_condition
MYSQL必读3.1左外连接: MYSQL必读示例: MYSQL必读
mysql> select e.ename employeename,e.job job,l.ename leadername from t_employee e left join t_employee l on e.mgr=l.empno;
+--------------+-----------+------------+
| employeename | job | leadername |
+--------------+-----------+------------+
| SMITH | CLEAR | FORD |
| ALLEN | SALESMAN | BLAKE |
| MARD | SALESMAN | BLAKE |
| JONES | MANAGER | KING |
| MARRTIN | SALESMAN | BLAKE |
| BLAKE | MANAGER | KING |
| CLARK | MANAGER | KING |
| SCOTT | ANALYST | JONES |
| KING | PRESIDENT | NULL |
| TURNER | SALESMAN | BLAKE |
| ADAMS | CLEAR | SCOTT |
| JAMES | CLEAR | BLAKE |
| FORD | ANALYST | JONES |
| MILLER | CLEAR | CLARK |
+--------------+-----------+------------+
14 rows in set (0.00 sec)
MYSQL必读3.2右外连接: MYSQL必读4.合并查询数据记录: MYSQL必读语法为: MYSQL必读
select field1,...fieldn
from tablename1
union | union all
select field1,...fieldn
from tablename2
union | union all
select field1,...fieldn
from tablename3
......
MYSQL必读5.子查询: MYSQL必读5.1 为什么使用子查询: MYSQL必读5.2 返回结果为单行单列和单行多列子查询: MYSQL必读示例(工资比Smith高的全部雇员信息): MYSQL必读
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 | 20 |
| 7521 | MARD | SALESMAN | 7698 | 1983-03-12 | 1250.00 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-03-12 | 2975.00 | NULL | 20 |
| 7654 | MARRTIN | SALESMAN | 7698 | 1981-03-12 | 2850.00 | 1400.00 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-03-12 | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1985-03-12 | 2450.00 | NULL | 20 |
| 7788 | SCOTT | ANALYST | 7566 | 1981-03-12 | 3000.00 | NULL | 10 |
| 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 | CLEAR | 7788 | 1998-03-12 | 1100.00 | NULL | 20 |
| 7900 | JAMES | CLEAR | 7698 | 1987-03-12 | 950.00 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 0000-00-00 | 3000.00 | NULL | 20 |
| 7934 | MILLER | CLEAR | 7782 | 1981-03-12 | 1300.00 | NULL | 10 |
+-------+---------+-----------+------+------------+---------+---------+--------+
13 rows in set (0.00 sec)
MYSQL必读??? 5.2.2 单行多列子查询: MYSQL必读??? where子句中的子查询除了是返回单行单列的数据记录外,还可以是返回多行多列的数据记录,不过这种子查询很少出现. MYSQL必读示例(工资和职位和Smith一样的全部雇员): MYSQL必读
mysql> select ename,job,sal from t_employee where (sal,job)=(select sal,job from t_employee where ename='smith');
+-------+-------+--------+
| ename | job | sal |
+-------+-------+--------+
| SMITH | CLEAR | 800.00 |
+-------+-------+--------+
1 row in set (0.00 sec)
MYSQL必读5.3 返回结果为多行单列子查询: MYSQL必读示例: MYSQL必读
mysql> select * from t_employee where deptno in(select deptno from t_dept);
+-------+---------+-----------+------+------------+---------+---------+--------+
| empno | ename | job | MGR | Hiredate | sal | comm | deptno |
+-------+---------+-----------+------+------------+---------+---------+--------+
| 7369 | SMITH | CLEAR | 7902 | 1981-03-12 | 800.00 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1982-03-12 | 1600.00 | 300.00 | 20 |
| 7521 | MARD | SALESMAN | 7698 | 1983-03-12 | 1250.00 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-03-12 | 2975.00 | NULL | 20 |
| 7654 | MARRTIN | SALESMAN | 7698 | 1981-03-12 | 2850.00 | 1400.00 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-03-12 | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1985-03-12 | 2450.00 | NULL | 20 |
| 7788 | SCOTT | ANALYST | 7566 | 1981-03-12 | 3000.00 | NULL | 10 |
| 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 | CLEAR | 7788 | 1998-03-12 | 1100.00 | NULL | 20 |
| 7900 | JAMES | CLEAR | 7698 | 1987-03-12 | 950.00 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 0000-00-00 | 3000.00 | NULL | 20 |
| 7934 | MILLER | CLEAR | 7782 | 1981-03-12 | 1300.00 | NULL | 10 |
+-------+---------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.00 sec)
MYSQL必读??? 5.3.2 带有关键字any的子查询: MYSQL必读示例(查询雇员工资不低于职位为manager的工资): MYSQL必读
mysql> select ename,sal from t_employee where sal>any(select sal from t_employee where job='manager');
+---------+---------+
| ename | sal |
+---------+---------+
| JONES | 2975.00 |
| MARRTIN | 2850.00 |
| BLAKE | 2850.00 |
| SCOTT | 3000.00 |
| KING | 5000.00 |
| FORD | 3000.00 |
+---------+---------+
6 rows in set (0.00 sec)
MYSQL必读??? 5.3.3 带有关键字all的子查询: MYSQL必读示例: MYSQL必读
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 |
+-------+---------+
3 rows in set (0.00 sec)
MYSQL必读??? 5.3.4 带有关键字exists的子查询: MYSQL必读示例(查询雇员表中各部门的部门号、部门名称、部门地址、雇员人数、和平均工资): MYSQL必读
mysql> select d.deptno,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;
+--------+------------+----------+--------+-------------+
| deptno | dname | loc | number | average |
+--------+------------+----------+--------+-------------+
| 10 | ACCOUNTING | NEW YORK | 3 | 3100.000000 |
| 20 | RESEARCH | DALLAS | 6 | 1987.500000 |
| 30 | SALES | CHICAGO | 5 | 1880.000000 |
+--------+------------+----------+--------+-------------+
3 rows in set (0.00 sec)
MYSQL必读通过子查询来实现: MYSQL必读
mysql> select d.deptno,number,average from t_dept d inner join(select deptno dno,count(empno) number,avg(s
al) average from t_employee group by deptno) employee on d.deptno=employee.dno;
+--------+------------+----------+--------+-------------+
| deptno | dname | loc | number | average |
+--------+------------+----------+--------+-------------+
| 10 | ACCOUNTING | NEW YORK | 3 | 3100.000000 |
| 20 | RESEARCH | DALLAS | 6 | 1987.500000 |
| 30 | SALES | CHICAGO | 5 | 1880.000000 |
+--------+------------+----------+--------+-------------+
3 rows in set (0.00 sec)
MYSQL必读以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持编程之家PHP. (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |