Summary of common methods of Oracle query in software development

The last time the new brother and everyone to explain some of the knowledge of Oracle to find out that we are more interested in Oracle, the following new PA on the relationship between the Oracle common query what kind of? A friend who is an oracle-related developer may know the answer, but a friend who has just contacted Oracle may not know what's going on. Today, the new brother will give you a detailed introduction.

Multi-Table Connection query

Connecting multiple tables

1. Cartesian product

Any row of the left table is connected to any record in the right table without any filtering conditions

2. Using connection queries

The following new PA simple introduction syntax 1:select columnlist from table1, table2 where table1.column1 operator table2.column2 (connection condition) and filter conditions, When you specify a join condition in the WHERE clause, join n tables, the join condition is n-1, and a field reference in several tables with the same name is prefixed to the table alias: from TableName N

This is the syntax 2:select columnlist from table1 join table2 on table1.column1 operator Table2.column2 join Table3 on ... where filter condition

Non-equivalent connection

When connecting two or more tables, do not use the equals sign in the condition, but with operators such as <,>,>=,<=,!=,between M and N,in

Natural connection

The join operation is performed based on a column of the same name in two tables only if the table has the same list and the data type is the same, the natural JOIN is used.

Look at the syntax given by the new bully: Select Columnlist from table1 natural join table2;

To join a table using a using clause

If two tables have the same name, use the Using clause in the JOIN statement to specify the connected column

Select Columnlist from table1 join table2 using (columnName)

Cross Connect

Joins with multiple tables using the keyword cross, the result of which is a Cartesian product

Syntax: Select columnlist from table1 cross join Table2;

External connection

When two tables perform a JOIN operation, rows in the table that do not meet the join criteria are displayed

Syntax: select Columnlistfrom table1 [left|right|full] outer join table2 on ...

Three types of outer joins:

1. Left OUTER JOIN outer connection

Returns all rows in the first table and rows in the second table that meet the join criteria

2. Right outer join outer joins

Returns all rows in the second table and rows in the first table that meet the join criteria

3. Full outer join fully external connection

Returns all rows of two tables, divided into two sections:

1) returns all rows of the first table, even if there are no matching rows in the second table

2) returns all rows of the second table, even if there are no matching rows in the first table

4. Inner Connection Self Join

Used to connect data in the same table.

Subquery Sub Query

Define another SELECT statement within a SELECT statement.

Note the point:

1. Subqueries are executed before the main query executes

2. The results of a subquery using a primary query

3. All sub-queries must be enclosed in parentheses

4. To enhance readability, subqueries should be placed to the right of the comparison criteria

5. Do not use ORDER by in subqueries unless TOP-N analysis is used

Nesting of sub-queries

Query that contains one or more subqueries

Single-row subquery: A subquery results in one row

Multiline subquery: subquery result is multiple rows

Top-n Query

Syntax: Select columnlist from table where RowNum <= n rownum only valid for current select

The new brother has summed up some common problems in the query through some problems encountered in the development:

1. Obtain a single-table all records select * from table;

2. Get single Table A field does not duplicate record select distinct column from table;

3. Query result field take alias Select column Othername from table;

4. Query filter conditions Select Columnlist from table where where_condition;

5. Query result sort select columnlist from table order by column [Asc|desc];

6. Packet query select Columnlist from table group by Column1, Column2;

7. Filter multi-line function results Select columnlist from table group by Column1, Column2 have having_condition;

8. Multi-table query, inner connection, outer connection (left outer connection, right outer connection, full outer connection)

Select Columnlist from table1 [left|right|full] joins table2 on table1.column1 = Table2.column2;

9. Sub-query

10. Nested subqueries

Well, today's summary of the common methods of Oracle queries in software development is written here, and more questions about Oracle are welcome to communicate with new Pa.

Summary of common methods of Oracle query in software development