Oracle Database Basics Point Summary

1. Database

2. Primary key , the uniqueness of the table identifies a column, and the value of the primary key can never be heavy. Primary key

Type of PRIMARY key: Single PRIMARY key: one column; Compound PRIMARY key: Multiple columns combine to act as primary key. Use serial number, single column as primary key

3. Three major rules : Entity integrity constraints: PRIMARY key: PK

Referential integrity constraints: FOREIGN key: A table of the primary key appears in the B table, in B is a foreign key, referred to as FK, at this time a is the main table, b from the table

The value of the foreign key from the table needs to be populated with a reference to the primary table primary key, or the value of the foreign key from the table is empty if the value of the foreign keys does not exist in the main build, the padding is forbidden

Custom constraint: Uniqueness constraint unique (invalid for null, can have multiple null),

NULL value constraint NOT NULL

Default value constraints default (NULL is not valid and can be entered as null)

Check constraint check (column name = ' or column name = ') (invalid for null)

4. Storage string: Varchar2 (length char) length of a Chinese character

Varcher2 (length)

Store number (n)

Date stored Dates

DATE (N) Error writing

5. syntax for creating a table

CREATE TABLE table name (column name 1 type primary key NOT NULL,

Column Name 2 type NOT NULL,

Column name 3 type NOT NULL); (Note the last one without a comma)

Insert the data into the table :

Insert into table name (column name 1, column name 2, column name 3)

VALUES (column value 1, column value 2, column value 3) the type of the column value must be the type of the column name

Writing a query statement

Select Column Name 1, column name 2, column name 3 B (you can alias the column name for display:

Three cases in which column aliases need double quotes: 1) Case-sensitive

2) There are spaces

3) special characters), B. Column name

From table name A (alias A to the table, and then in select you can write a. Column name),

Table name B (can be queried with multiple tables) (the table's writing order when the table is concatenated, from the table in front, the main table behind,)

Where conditional statement (write filter condition, post-write connection)

(where you can do a nested query to the results of another query as a filter, but not recommended, poor performance, generally the select query as a virtual table from the processing)

ORDER by ASC column name (positive order) or DESC column name (reverse) sort output (null maximum in order by)

Rewrite statements

Update table name set column value (new) Where condition satisfies what condition to modify

Delete statement

Delete from table name entire table data deletion

Delete from table name where conditional statement has conditional delete data

In table spaces:

1. Table space and sub-user management

There are two ways to implement the sub-user management

mode 1: physical Isolation

all data for each user is stored in a separate tablespace that only mounts all of the user's tables , so that a user's tablespace is problematic and has no effect on other users

mode 2: logical Isolation

The table is owned by the user, but all users ' tables share a table space

2. tablespace (tablespace)

is the container of a table, which is actually a collection of data files , and the data in the user table is actually stored in the specified file in the table space .

Table Space Classification :

1. Staging tablespace ----- Swap partition

2. Permanent table space ------ to store data

There are two delete tables:

Drop: Recoverable

Ttruncate (data truncation): Unrecoverable there is a data rollback in Oracle, and truncate is bypassing this data rollback, and the drop can be rolled back to recover the data that was just deleted.

Time in Oracle database date:sysdate can be obtained from the table: select Sysdate from dual;

Conversion between string and date: To_date (' 1997-08-08 ', ' yyyy-mm-dd ')

To_char (sysdate, ' yyyy-mm-dd HH25:MI:SS ')

Operation of the time by day

The configuration between tables in a multi-table association query

1. a master more from

2. one from multi-master

3. Single Line

self-connect : a table and its own connection, the virtual into another table can be connected

For example, to query the manager of every clerk in EMP under Scott.

SELECT a.empno,a.ename,a.job,a.mgr,b.ename    from  WHERE a.mgr=b.empno

Inner connection (equivalent connection):

Display only data that satisfies the condition of the connection, does not show; application context: A search for a fait accompli applied to the past and present

Outer joins : Based on a table, all data in the table is displayed, corresponding from the table, if there is data, the data is displayed, and null if no data exists: for the future, the whole concept.

Syntax in Oracle: Add (+) from the outer table key;

For example, look for Scott under the table EMP owner's manager;

SELECT a.empno,a.ename,a.mgr,b.empno,b.ename    from  WHERE a.mgr=b.empno (+)

The future of the dream is not realized in the query outside the connection can be implemented: is to take the corresponding column from the table null, the part of the data table in the future

Querying people who may be managed in the future

SELECT a.empno,a.ename,a.mgr,b.empno,b.ename    from  WHERE a.mgr=b.empno (+ and is NULL

This example may not be appropriate, but the usage is

The previously mentioned sort order by ASC positive Order desc Flashback is also called descending

Multi-field sorting: Grouping by fields, sorting one field in a group

Grouping statistics functions:

Min to find minimum value

Max asks Max (Max min count can take values for any data type column)

AVG averages (AVG and sum can only have values for numeric type columns)

Sum sum

Count counts the number of records that satisfy the condition (automatically ignores null values)

Count (column) counts only non-null values, and all non-null participation count on this column

COUNT (*) as long as one column is not empty

The Count five statistics function has the filtering function,

Using the aggregate function count, written in select and other column names to check, you have to do aggregation group by, select appears to be written in group by. The GROUP BY clause cannot be grouped with other column aliases, or there can be subqueries, and there can be a HAVING clause.

Order of execution of SQL statements

1 from Get Data

2 Where to filter data

3.group by aggregation grouping

4. Aggregation function Operations

5.having conditional statements

Columns required for 6.select queries

7 DISTINCT Delete duplicate data

8. Order by sort the desired data

Decode function

　　 It is the Chinese character meaning that is displayed for the characters. For example, in the Database Status column information, 1 stands for living. 2 means dead.

Find out 1 or 2 people don't understand. We can use the Decode function to implement decode (table name. Column name, ' 1 ', ' alive ') so if it's 1 it shows alive,

substr function

Used to intercept the string substr (' ABCDEFG ', 2, 3) from the second start to intercept three characters ' BCD ', if 2 is changed to 2 then the order is reversed, if 3 is changed to 100 intercept to the last position ' BCDEFG '.

rowID

　　This is a two pseudo-column; The physical address of the line, retrieving or deleting the record that satisfies the condition at the fastest speed

This uses nested queries to find out the maximum number of duplicate data to delete and then remove all the rowid we don't need.

RowNum

　　The row number of the record for each row in the query result set. Generally used for top-n analysis; adding rownum directly to the select will add an ordinal to each row of the queried data, and you can then write rownum<3 in the Where condition to get the data from the first two rows. < with! = one meaning, <= less than equals

Most people say: rownum is used to make paged queries:

In this I learned that RowNum used to do top-n analysis. is to obtain the first few data, first query out with a column with order by order as a virtual table with RowNum Select the first few we want and then we can use an ORDER BY statement to select the order output we want, note what to sort must be in the above select Found in the virtual table.

sequence

Create sequence sequence name: Used to generate an incremented value, but does not guarantee continuous

Delete a drop sequence sequence name

Index

Role: Helps improve query efficiency

Time to create: generally run on-line for six months on the system, creating an index of the query condition columns that users frequently use, based on the optimization performance perspective.

Auto index, primary key index and UNIQUE constraint index exist in Oracle

Creating index statements create index name on table name (column name, column name) can be listed as single index multiple column composite index

Remove index is DROP index index name

View

View Create VIEWS statement The CREATE VIEW name as select queries the data that is equivalent to a virtual table in the real project that does not use the view

Delete statement Drop view name

The relationship of the view to the original table:

View is simply a reference to the original table data, and there is no data in the view

When querying a view, it is equivalent to executing the SELECT statement again when the view is created.

The role of views: confusing raw data, refactoring data, and simplifying queries.

Oracle Database Basics Point Summary