Shulou(Shulou.com)06/01 Report--

This article is to share with you about mysql multi-table query implementation, the editor feels very practical, so share with you to learn, I hope you can learn something after reading this article, do not say much, follow the editor to have a look.

There are three ways of multi-table query, namely: 1, the traditional way, including left outer join query, right outer join query, complete outer link query; 2, subquery mode, including single-row query, multi-row query; 3, aggregate query mode, including summation, average query, total number of records.

There are three ways to query multiple tables, which are:

I. the traditional way

/ *-- traditional connection method-- * / select e.enamered.dname from dept d, emp e where d.deptno = e.deptno / *-- natural join is joined when there is only one field with the same name in two tables-- * / select e.enamezod.dname from dept d natural join emp e / *-- when there are multiple fields with the same name in two tables of the same name-- * / select e.enamered.dname from dept d join emp e using (deptno)

1. Left outer join query

/ *-- all records in the left table are linked to the left outside (all records in the department table: demp)-- * / / *-- Mode 1: Fuli from dept d left outer join emp e on d.deptno / select e.ename.dname from dept d left outer join emp e on d.deptno = e.deptno / *-Mode 2: Mustang / select e.enamethd.dname from dept d, emp e where d.deptno = e.deptno (+)

2. Right outer join query

/ *-- all records in the left table are linked to the left outside (all records in the department table: demp)-- * / / *-- Mode 1: Fuli from dept d left outer join emp e on d.deptno / select e.ename.dname from dept d left outer join emp e on d.deptno = e.deptno / *-Mode 2: Mustang / select e.enamethd.dname from dept d, emp e where d.deptno = e.deptno (+)

3. Full external link query

/ *-- full external link (match and mismatch)-- (including all records in the department table and employee table) * / select e.ename.dname from dept d full outer join emp e on d.deptno = e.deptno

Second, subquery (single or multi-line)

1. Single-line query

/ *-- subquery (single-line, multiple-line)-- * / select * from emp where sal > (select avg (sal) from emp) / *-- single-row subquery can refer to (=, >, > =, <, < >) operator-- * / select * from emp e where e.deptno = (select d.deptno from dept d where d.deptno=1)

2. Multi-line query

/ *-- multiple-row subqueries can reference the (in,any,all) operator-- * / select * from emp e where e.deptno in (select d.deptno from dept d) / *-- any compares any one of the returned values, and returns true-- * / select * from emp e where e.sal if one of them is satisfied.

< any(select sal from emp where deptno=2) /*-- all 比较返回值中的所有,全部满足，则返回true --*/ select * from emp e where e.sal < all(select sal from emp where deptno=2) 三、聚合查询（求和，平均，记录总数） 1、求和，平均查询 /*--求和，平均 --*/ select sum(sal),avg(sal) from emp select avg(nvl(sal,0)) from emp 2、记录总数 /*--记录总数 --*/ select count(*) from emp select count(memo) from emp /*--统计该栏非空记录 --*/ select count(distinct(sex)) from emp /*--去掉重复记录 --*/ /*-- group by --*/ select e.deptno,sum(e.sal) from emp e group by e.deptno /*-- group by having --*/ select e.deptno,avg(e.sal) from emp e group by e.deptno having avg(e.sal) >

More than 4500 is the implementation of mysql's multi-table query, and the editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.