Oracle Learning NOTE 2

The difference between rowID and rownum in 1.oracle

In an Oracle database, each row has an identifier for a unique row, ROWID, where the physical location of the row is stored inside the database. The ROWID is a 18-digit number with base-64 encoding. When using the DESC table, rowID is not displayed, so rowid is often called a pseudo-column.

RowNum usually returns the line number of each row in the result set.

2. Understanding Null Values

The database has a hollow value that identifies the value of the column as unknown.

Null values are usually checked with is null, distinguishing between null values and empty strings, usually with the function NVL ().

the LIKE operator in 3.SQL

Usually to match the '% ' in the string 10%, use ESCAPE

For example: Select name from promotions where name like '%\%% ' ESCAPE ' \ ';

4.BETWEEN is used to retrieve rows that contain column values within a specified range. Contains two endpoints of a range .

For example: SELECT * FROM Customers where customers_id between 1 and 3. (which contains 1 and 3.)

5. Cartesian product

Querying in multiple tables without specifying a connection relationship before a table causes all rows in one table to be connected to all rows in another table, which becomes a Cartesian product.

For example: the first table has 50 rows of data, the first table has 100 rows of data, and if the two tables are queried, then 5000 rows will be detected without the association condition.

6. Link conditions between Tables

1) etc. connection (=)

2) Unequal connection (<>, between,<,, <=, >=, like, in)

3) Inner JOIN returns a row only if the column in the connection contains a value that satisfies the join condition, and the row does not return if a column in the join condition of a row is a null value.

Example: Select P.name,pt.name

FRM products P INNER join Product_types PT

on p.product_type_id = pt.product_type_id;

Inner joins of multiple tables:

SQL/86 's Query

Select C.first_name, c.last_name,p.name as product,pt.name as type

From customers C, purchases PR, Products p, product_types PT

where c.customer_id = pr.customer_id

and p.product_id = pr.product_id

and p.product_type_id = pt.product_type_id

Order BY P.name;

Or you can write it sql/92 like this:

Select C.first_name, c.last_name,p.name as product,pt.name as type

From customers c INNER JOIN purchases PR

using (customer_id)

INNER JOIN Products P

using (product_id)

INNER JOIN Product_types PT

using (product_type_id)

Order BY P.name;

If the association condition joins multiple columns of 2 tables, this can be done:

SELECT *

From table1 INNER JOIN table2

On table1.col1 = Table2.col1

and table1.col2 = Table2.col2;

Or:

SELECT * FROM table1 inner JOIN table2

using (col1,col2);

4) External Connection

Left OUTER join: the connection symbol (+) is actually to the right of the equal sign, which means that columns with null values in the table to the left of the equals sign are retrieved

(+ on the right side of the equals sign, the data that retrieves the empty values of product_type_id in the table products P is retrieved.) ）

Example: Select P.name, Pt.name

From Products p, product_types PT

where p.product_type_id = pt.product_type_id (+)

Right outer join: the connection symbol (+) is retrieved from the left side of the equal sign, which indicates that there are null values in the table to the right of the equals sign.

Restrictions on external links:

You cannot use the Connect symbol on both ends of the equals sign +

Example: Select P.name, Pt.name

From Products p, product_types PT

where p.product_type_id (+) = pt.product_type_id (+) error is used

Order BY P.name;

cannot use both outer joins and in operators

Example: Select P.name, Pt.name

From Products p, product_types PT

where p.product_type_id (+) in (1,2,3,4) error is used

One outer join condition and another join condition using the OR operator cannot be used at the same time

Example: Select P.name, Pt.name

From Products p, product_types PT

where p.product_type_id (+) = pt.product_type_id (+)

or p.product_type_id = 1; Incorrect use

5) Self-connect

Since a connection is a connection to the same table, to perform a self-join, you must use the alias of a different table to identify the reference to the table in the query. Self-connection can be used in conjunction with external links

Example: Select W.last_name | | ' Work as ' | | NVL (M.last_name, ' the shareholders ')

From Employees W, Employees m

where w.manager_id = m.manager_id (+)

Order BY W.last_name;

Oracle Learning NOTE 2