Oracle Database (iii) Table operations, connection queries, paging

Copying tables

-- Copying Tables Create Table  as Select *  from Product

-- duplicate table structure no data Create Table  as Select *  from where 1 = 2

Following a condition that is not established in the where, only the structure of the table is copied and the contents of the table are not copied.

Delete a table

-- Delete a table Delete Table new_table -- Delete table, cannot retrieve truncate Table new_table

Sequence

A sequence (SEQUENCE) is a sequence number generator that automatically generates a sequence number for a row in a table, producing a set of equal-spaced values (type number). Its main purpose is to generate the table's primary key value, which can be referenced in the INSERT statement, or

Checks the current value through a query, or increases the sequence to the next value.

Creating a sequence with a statement

-- --Create sequence Create  by 1 with 1 nomaxvaluenominvaluenocache

Multi-Table Query

Select *  from where e.deptno=D.deptno

Cartesian product

The Cartesian product is implemented in SQL by means of cross-linking, all connections are made to the temporary Cartesian product table, and the Cartesian product is a concept of relational algebra that represents any combination of data in each of the two tables.

Simply put, two tables are connected without a conditional limit, and the number of rows that appear is the product of the number of rows of two table data.

Internal connection

Select *  from where e.deptno=D.deptno

Limitations of internal connections: if there are null values, the query results may be missing.

Workaround:

To use a table as a benchmark for external links:

-- left outer link Select *  from  Left Join  on E.deptno=D.deptno

or use the + symbol

Select *  from where e.deptno=D.deptno (+)

Querying users ' tables

-- Querying all Tables Select *  from User_tables

Self-connect

There are situations where you can compare data from the same or different columns of a table, and you need to make a table to self-connect as two tables, then compare the data and then query

-- self-connect Select  from where e1.empno=e2.mgr

Hierarchical query

SELECT statements in Oracle can be used with the start with ... The Connect by prior clause implements a recursive query, and connect by is used in structured queries with the basic syntax:

Select ... from <TableName>

where <Conditional-1>

Start with <Conditional-2>

Connect by <Conditional-3>

;

<conditional-1>: Filter condition for filtering all records returned.

<conditional-2>: The limit of the root node at which the query results are re-started.

<conditional-3>: Connection conditions

-- Hierarchical Query Select E.*, level from by prior e.empno= with E.ename = ' KING ' Order  by  Level

Pseudo-column:
Level
RowNum

RowNum is the number of rows that the Oracle system is assigned to return from the query, the first row returned is assigned a 1, the second row is 2, and so on, this pseudo-field can be used to limit the number of rows returned by the query.

And rownum cannot be prefixed with the name of any table.

It is important to note that if you sort by the primary key, the order of the rownum changes, and if you do not sort by the primary key, the rownum will not change.

--querying the first 10 columns of dataSelectE.*, rownum fromP_emp EwhereRowNum<=Ten--6-10 column data, nested querySelect *  from(SelectE.*, RowNum Rownu fromP_emp EwhereRowNum<=Ten) rwhereR.rownu>5 

Oracle Database (iii) Table operations, connection queries, paging