Oracle Study Notes: SQL query Summary

1 query statement 1.1 obtain the unique record -- distinct obtain the employee's unique salary status SQL statement is as follows. SQLgt; selectdistincte. employee_name,

1 query statement 1.1 obtain the unique record -- distinct obtain the employee's unique salary status SQL statement is as follows. SQLgt; select distinct e. employee_name,

1Query statement

1.1Retrieve unique records-- Distinct

Obtain the employee's unique salarySQLThe statement is as follows.

SQL> select distinct e. employee_name, s. salary

From employees e, salary s

Where e. employee_id = s. employee_id;

1.2

In database queries, grouping is a very important application. Grouping refers to dividing all records in a data table into groups based on one or more columns.

Grouping query should be used

1.3

Where

To get a total salary greater

Select e. employee_id, e. employee_name, sum (s. salary) total_salary

From employees e, salary s

Where e. employee_id = s. employee_id

Group by e. employee_id, e. employee_name

Having (sum (s. salary)> 10000

1.4

Order

The search results are arranged in the order of salary levels from high to low,SQLThe statement is as follows.

SQL> select distinct e. employee_name, s. salary

From employees e, salary s

Where e. employee_id = s. employee_id

Order by s. salary desc;

1.5 order

When two seed sentences exist at the same time,

Order

Obtain the total salary of the employee, and sort the total salary from high to low.

SQL> select e. employee_name, sum (s. salary) total_salary

From employees e, salary s

Where e. employee_id = s. employee_id

Group by e. employee_name

Order by total_salary desc;

1.6 orderAndDistinct

Order

Rules

Obtain the names of all employees and sort them by salary in ascending order.

SQL> select e. employee_name, s. salary

From employees e, salary s

Where e. employee_id = s. employee_id

Order by s. salary desc;

2Subquery

A subquery is embedded in a query statement to obtain a temporary result set.

OracleThe query statements with subqueries are always automatically optimized. If the number in the subquery

The data in the source and parent queries can be connected.

Otherwise, the subquery is executed first, and then the parent query is executed.

2.1Subquery in the query Condition

For exampleEmployeesEmployee information is stored in. However, some employees have not received any salary.

Subquery can be used as the query condition to obtain information about all employees who have received their salaries.

SQL> select * from employees

Where employee_id in (select employee_id from salary );

2.2Subqueries in Table creation statements

Another Application Scenario of subquery is used in table creation statements.

For example,

To create an empty data table with the same structure as the view, you can use the followingSQLStatement.

SQL> create table tmp_user_objects

As select * from user_objects where 1 <> 1;

2.3Subquery in insert statement

We can also use subqueries In the insert statement. This is equivalent to batch inserting data into the table.

The created data table is an empty table. We can use subqueries to insert data into it.

SQL> insert into tmp_user_objects

Select * from user_objects

Where object_type = 'table ';

Select * from user_objects where object_type = 'table'

Used to obtain a view

All records of this subquery will be inserted into the table.Tmp_user_objects.

3Union statement

A Union statement is used to perform a set operation on the result sets obtained by multiple queries.

These set operations include:

These set operations are binary operations, and the operation results are still a set of records.

3.1

UnionThe operation actually merges all records in the two result sets and removes the duplicate records (ensure that the records in the result set are unique ).

Instance: two tables exist in the database.

Stored separately

Select student_id, student_name from a_students

Union

Select student_id, student_name from B _students;

3.2

Union all

HoweverUnion allOnly simple integration of the two operation result sets does not remove duplicate data.

This isUnionThe biggest difference between operations.

In the preceding example

3.3

InterseectAn operation refers to an intersection operation.

This operation returns the intersection of two result sets.--That is, records that exist in both result sets.

In the table

Now you want to get both participants

That is, the Student name that exists in both tables can be usedIntersectOperation.

CorrespondingSQLThe statement is as follows.

SQL> select student_name from a_students

Intersect

Select student_name from B _students;

3.4

MinusIs the subtraction operation between sets. This operation returns the first set that exists,

The second collection does not exist.

Now you want to get the participants

In this case, we can use

Does not exist in the tableB _studentsStudent name.

SQL> select student_name from a_students

Minus

Select student_name from B _students;

3.5Hybrid operation of joint statements

For these four set operations

OracleAllows mixed operations. In a hybrid operation, these four operations have the same priority,

That is to say, they will follow the order from left to right.

Intersect

SQL> select student_name from a_students

Intersect

Select student_name from a_students

Union all

Select student_name from B _students

4Connection

In most queries, multiple data sources are used.

How do I perform these data sources when multiple data sources are used at the same time?

Combination becomes a crucial issue. Connection is used

Specifies the combination relationship between multiple data sources. By default,

The Cartesian Product method is used to combine multiple data sources.

In addition,OracleThere are also several special combinations.

These special methods effectively supplement the limitations of cartesian products

4.1Natural connection

Natural connections, as the name suggests, do not require you to specify any connection conditions,

You only need to specify the two connected data sources. You don't have to worry about how to integrate the two data sources.

The keyword used by the natural connection isNatural join. The connection principle is,

The two data sources have a common column with the same column value.

Table

The two can be naturally connected.SQLThe statement is as follows.

Select * from employees natural join salary;

Natural joinUsed for the natural connection between two tables.

The result set obtained from the search results will contain public columns.Employee_id.

The public column mentioned here means that it cannot be a column.Employee_idSpecify the qualifier.

For example, Columns cannot be

SQL> select e. employee_id

4.2Internal Connection

The natural connection forces the use of public columns between two tables as search conditions;

The values of public columns must be equal. This imposes great restrictions,

Therefore, natural connections are not commonly used. Internal connections break through these two constraints.

You can specify the connection columns and connection conditions for internal connections. The keyword of the inner join operation isInner join.

Similarly, in order to meet the demand for employee salaries, the internal connectionSQLThe statement is as follows.

SQL> select e. employee_id, e. employee_name, s. month, s. salary

From employees e

Inner join salary s

On e. employee_id = s. employee_id;

4.3External Connection

The two data sources specified by the internal connection are in equal status. Different from external connections, external connections are always based on one data source and match another data source with the conditions. Even if the conditions do not match, the data in the basic data source always appears in the result set. Then, based on which data source is used as the basic data source, there are two external connection methods.--The left (outer) connection and the right (outer) connection. Because there are no left or right connections, we are used to connecting left outer connections and right outer connections for short.

1. Left join

2.Right join

3.Simple Writing of external connections

Advantages and disadvantages of Simple Writing

5. Full connection

Statement segment:

　　Select modelname, TABLENAME

　　FORM EMCD_MONITOR_MODEL, EMCD_MONITOR_TABLE

　　WHERE EMCD_MONITOR_TABLE.MODELID (+) = EMCD_MONITOR_MODEL.ID

This

Always remember

Conversely,A. bid = B. id (+)Left join;

Equivalent to Standard

5Hierarchical Query

In a relational database, records in the same data table have the same columns. Therefore,

There is a parallel relationship between different records. However, sometimes

There may be

View the data in the entire table as a tree structure, and query the data based on the tree structure,

It is called hierarchical query.

5.1Tree Model

A typical instance of the tree model is market information. There is a parent-child relationship between different levels of markets.

5.2Hierarchical Query

From

Select

FromTable Name

StartStart Condition

ConnectRecursive Condition

Where,

5.3Hierarchical query Functions

For hierarchical queries, the most common function is

Sys_connect_by_path (

Specifically, the name specifies the value of the column to be connected in series, while the separator specifies the delimiter when the string is connected in series.