Home> php教程> PHP开发> body text

Oracle basic learning child query

高洛峰
Release: 2017-01-06 10:37:12
Original
1304 people have browsed it

First of all, when using subqueries, please note that subqueries can be nested multiple levels and subqueries need to be enclosed in parentheses (). Let’s take a look at the detailed introduction below.

Basic introduction

1, wherer: subquery will generally return a single row, single column, single row, multiple columns, multiple rows and single column;

2, having: subquery will return a single row, single column, while Indicates that statistical functions are to be used;

3, from: subquery returns multi-row and multi-column data (table structure);

4, select: returns a single row and single column (generally not used);

Example Detailed Explanation

where (filtering data rows):

a: Query employee information that is lower than the company's average salary.

select * from emp where sal<(select avg(sal) from emp);
Copy after login

The above query returns a single row and a single column that can be used as the filter condition of the where clause;

b: Query the information of the earliest employee hired by the company.

select * from emp where hiredate= (select MIN(hiredate) from emp);
Copy after login

C: Query information about employees who have the same job as scott and have the same salary.

select* from emp where (job,sal) =( select job,sal from emp where ename ='scott') and ename <>'scott';
Copy after login

in: refers to the same content returned by the subquery.

select * from emp where sal in (select sal from emp where job = 'manager');
Copy after login

not in:

select* from emp where sal not in(select sal from emp where job='manager');
Copy after login

There cannot be null in the subquery.

any:

select* from emp where sal = any(select sal from emp where job='manager'); select* from emp where sal > any(select sal from emp where job='manager');
Copy after login

It is larger than the maximum value returned by the subquery

select* from emp where sal < any(select sal from emp where job='manager');
Copy after login

It is smaller than the maximum value returned by the subquery

all:

all: larger than the maximum value returned by the subquery

where subquery The probability is very high;

having:

Query the job title and number of positions and average salary that are higher than the company's average salary.

select job,count(empno),avg(sal) from emp group by job having avg(sal)>(select avg(sal) from emp);
Copy after login

select (generally not used):

Query the number, name, position, and department name of each employee.

select e.empno,e.ename,e.job, (select d.dname from dept d whered.deptno=e.deptno)from emp e;
Copy after login

(1+n) queries;

from(Key points):

Query the name, location, and number of people in each department.

select d.dname,d.loc,count(e.empno) from emp e,dept d where e.deptno(+)=d.deptno group by d.dname,d.loc;
Copy after login

(Multiple table query)

分步1: select d.deptno,d.dname,d.locfrom dept d; 分步2:select deptno,count(empno)from emp group by deptno; 正确的查询: select d.deptno,d.dname,d.loc,temp.count from dept d,(select deptno,count(empno) count from emp group by deptno) temp where d.deptno=temp.deptno(+);
Copy after login

Multiple table queries and subqueries can both achieve statistics, so which method is better?

Answer: In actual work, the main purpose of subquery is to solve the performance problem of multi-table query, so it is used the most in development. The biggest role is to solve the problem of Cartesian product affecting performance caused by multi-table queries.

Complex query = simple query + limited query + multi-table query + grouped statistical query + subquery;

Summary

The above is all about Oracle subquery, I hope The content of this article can be helpful to everyone in learning or using Oracle. If you have any questions, you can leave a message to communicate.

For more articles related to Oracle basic learning, please pay attention to the PHP Chinese website!

Related labels:
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Recommendations
    Latest Downloads
    More>
    Web Effects
    Website Source Code
    Website Materials
    Front End Template
    About us Disclaimer Sitemap
    php.cn:Public welfare online PHP training,Help PHP learners grow quickly!