ORCL Complex Queries

Test environment:

CREATE TABLE Bqh6 (XM varchar2), Bmbh number (2), Bmmc varchar2 (), GZ int);
INSERT into Bqh6 values (' Zhang San ', 01, ' Technical Support ', 3500);
INSERT into BQH6 values (' John Doe ', 02, ' Research ', 4500);
INSERT into Bqh6 values (' Harry ', 03, ' External mapping ', 5000);
INSERT into Bqh6 values (' Xiao Cui ', 02, ' research and Development ', 8000);
INSERT into BQH6 values (' Money six ', 01, ' Technical Support ', 5500);
INSERT into Bqh6 values (' Zhao er ', 03, ' foreign industry mapping ', 4500);
SELECT * FROM Bqh6

CREATE TABLE Bqh7 (XM varchar2 (Ten), Bmbh number (2), Bmmc varchar2 (+), GWJB varchar2 (10));

INSERT into Bqh7 values (' Zhang San ', 01, ' Technical support ', ' C ');
INSERT into Bqh7 values (' John Doe ', 02, ' Research ', ' C ');
INSERT into Bqh7 values (' Harry ', 03, ' External mapping ', ' A ');
INSERT into Bqh7 values (' Little Cui ', 02, ' Research ', ' A ');
INSERT into Bqh7 values (' Money six ', 01, ' Technical support ', ' A ');
INSERT into Bqh7 values (' Zhao II ', 03, ' outside industry mapping ', ' B ');
SELECT * FROM Bqh7

Grouping functions: Max, MIN, avg, SUM, Count

Check the name of the person with the highest and lowest wages:

Select ' Highest: ' | | Xm,gz from Bqh6 where gz= (select Max (GZ) from Bqh6) UNION ALL
Select ' Lowest: ' | | Xm,gz from Bqh6 where gz= (select min (GZ) from Bqh6)

For information on all people with wages below the average wage:

SELECT * from Bqh6 where gz< (select AVG (GZ) from Bqh6)

Pay up to 10% for all employees below the average wage:

Update Bqh6 Set gz=gz+ (select AVG (GZ) from Bqh6) *0.1 where gz< (select AVG (GZ) from Bqh6);

Commit

SELECT * from Bqh6;

Groupt by is used to group statistics on query results

Check the average salary and maximum wage for each department:

Select AVG (GZ), Max (GZ), BMMC from Bqh6 Group by BMMC;

The HAVING clause is used to restrict the display of grouped results

Query the department name and its average salary below 5000 of the average salary:

Select Bmmc,avg (GZ) from Bqh6 GROUP by BMMC have avg (GZ) <5000

Attention:

The Grouping function (Max,min,avg,count) can only appear in the select list (after select), having and order by clauses;

If both group by,having and order by are included in the SELECT statement, their order must be group by,having and order by (group first → suppress result display → last group);

In the Select column, if there are columns, expressions, and grouping functions, then the columns and expressions must have one in the GROUP BY clause, otherwise they will be error--

----------------------------------------------------------------------------------

Multi-Table Query

Check the employee's name and salary and the job level in the department:

Select A.XM,A.GZ,B.GWJB from Bqh6 A,bqh7 b where a.xm=b.xm

Cartesian product, principle: The condition of a multi-table query is at least not less than the number of tables-1

Query department name, employee name, and salary for department Number 10:

Select a.xm,b.bmbh,b.bmmc,a.gz from Bqh6 A,bqh7 b where a.xm=b.xm and b.bmbh=2;

Query employee name, salary, and department name and sort by department

Select a.xm,b.bmbh,b.bmmc,a.gz from Bqh6 A,bqh7 b where a.xm=b.xm order by B.BMMC

Order By default ascending (ASC), descending (DESC)

Sub-query

. single-line subquery

Query all employee names in the same department as Zhang San:

Select A.xm from Bqh6 a where a.bmmc= (select Bmmc from Bqh7 b where b.xm= ' Zhang San ')

Multi-row subqueries

Query the employee name, department name, and salary for department number 3:

Use of all in multiline subqueries: match every content

Query the employee name, salary, and department name for the salary of all employees whose wages are higher than the department number 3:

>all: Larger than the largest record returned in a subquery

<all: Smaller than the smallest record returned in a subquery

①select xm,bmmc,gz from Bqh6 where Gz>all (select Max (GZ) from Bqh6 where bmbh=3)

②select xm,bmmc,gz from Bqh6 where gz> (select Max (GZ) from Bqh6 where bmbh=3)

② efficiency is higher than ①, because ① will be compared by article by article, and ② directly compare results.

In the use of subqueries: used to specify a subquery's range of judgment

Query the employee name, department name, and payroll information for the department number 1.

SELECT * from Bqh6 where GZ in (select Gz from Bqh6 where bmbh=1);

Use of any in multi-row subqueries: Match each content with three matching forms

①=any: The function is exactly the same as the in operator

②>any: Data that is larger than the smallest return record in a subquery

③<any: Data that is smaller than the largest record returned in a subquery

using subqueries in the FROM clause generally returns more than one row of columns, which can be treated as a single data table:

Check out the number, name, number of departments, average salary of each department

Select A.xm,a.bmbh,a.bmmc,b. Number of departments, B. Average salary from Bqh6 a,
(Select Bmbh bh,count (XM) department number, AVG (GZ) Average salary from Bqh6 Group by Bmbh) b
where A.BMBH=B.BH

Attention:

When a subquery is used in the FROM clause, the subquery is treated as a view, so it is called an inline view, and when a subquery is used in the FROM clause, the subquery must be given an alias. You can use as as an alias for a column, but you can alias a table, view, or subquery with an AS

Paging query: RowNum automatically generates line numbers dynamically for each record displayed.

Automatically generate line numbers dynamically for each record displayed

Query results will be more than one column, RN, indicating rownum, line number, is ORCL allocated.

Query the previous record:

Query the first 3-6 records:

Oracle's paging is the most complex, with 2 sub-queries, but the most efficient, because the 2-point lookup principle is used internally. MySQL paging is the simplest, direct a limit to achieve

ORCL Complex Queries