Common SQL syntax and Data Objects in ORACLE

I. Data Control Statement (DML)

1. INSERT (INSERT the record statement into the data table)

　　

Insert into Table Name (field name 1, field name 2 ,......) VALUES (value 1, value 2 ,......);

Insert into Table Name (field name 1, field name 2 ,......) SELECT (field name 1, field name 2 ,......) FROM another table name;

　　

Field Values of the string type must be enclosed in single quotes, for example, 'good Day'

If the field value contains single quotes and requires String Conversion, we replace it with two single quotes ''.

An error occurs when the value of a string type field exceeds the defined length. It is best to verify the length before insertion.

　　

The field value of the date field can use the system time SYSDATE of the current database, accurate to seconds

Or use a string to convert to the date type function TO_DATE ('2017-08-01 ', 'yyyy-MM-DD ')

TO_DATE () has many date formats. For more information, see oracle doc.

-Month-day hour: minute: seconds format YYYY-MM-DD HH24: MI: SS

　　

The maximum length of a string that can be operated during INSERT is less than or equal to 4000 single bytes. To INSERT a longer string, use the CLOB type for the field,

Use the DBMS_LOB package that comes with ORACLE.

　　

If you want to use a serial number that automatically increases from 1 during INSERT, you should first create a serial number

Create sequence serial number name (preferably table name + Serial number mark) increment by 1 start with 1

MAXVALUE 99999 cycle nocache;

The maximum value is determined by the length of the field. If the defined auto-increment serial NUMBER (6), the maximum value is 999999.

INSERT statement INSERT this field value: name of the serial number. NEXTVAL

　　

2. DELETE (DELETE the statements recorded in the data table)

　　

Delete from table name WHERE condition;

　　

Note: deleting a record does not release the occupied data block tablespace in ORACLE. It only marks the deleted data blocks as unused.

　　

To delete all records in a large table, run the TRUNCATE command to release the occupied data block tablespace.

Truncate table name;

This operation cannot be rolled back.

　　

3. UPDATE (modify the statements recorded in the data table)

　　

UPDATE table name SET field name 1 = value 1, field name 2 = value 2 ,...... WHERE condition;

　　

If the modified value N is not assigned a value or defined, the original record content will be cleared to NULL. It is best to perform non-NULL verification before modification;

An error occurs when the value of N exceeds the defined length. It is best to verify the length before insertion ..

　　

Note:

A. The preceding SQL statements have row-level locks applied to tables,

After confirmation, you must add the Transaction Processing Command COMMIT to take effect,

Otherwise, the changes may not be written into the database.

To recall these operations, run the ROLLBACK command to restore them.

　　

B. Estimate the range of records that may be operated before running INSERT, DELETE, and UPDATE statements,

It should be limited to a small range (10 thousand records). Otherwise, ORACLE will use a large rollback segment to process this transaction.

Slow program response or even loss of response. If more than 100,000 of these operations are recorded, you can complete these SQL statements in multiple parts,

In the meantime, COMMIT is added to confirm transaction processing.

　　

Ii. Data Definition (DDL)

1. CREATE (CREATE a table, index, view, synonym, process, function, database link, etc)

　　

Common ORACLE field types include:

CHAR fixed-length string

VARCHAR2 variable-length string

NUMBER (M, N) numeric type M is the total length of digits, N is the length of decimal places

DATE type

　　

When creating a table, you need to put a small field that is not empty in front of the field that may be empty in the back

　　

You can use a Chinese field name when creating a table, but it is best to use an English field name.

　　

When creating a table, you can add a DEFAULT value to the field, for example, default sysdate.

In this way, the operation time can be obtained every time you insert or modify this field without any program operation.

　　

When creating a table, you can add constraints to fields.

For example, duplicate UNIQUE is not allowed, and the KEY is primary key.

　　

2. ALTER (change tables, indexes, views, etc)

　　

Change table name

Alter table name 1 to table name 2;

　　

Add a field to the end of the table.

Alter table name ADD field Name field name description;

　　

Modify the definition description of fields in the table

Alter table name MODIFY Field Name field name description;

　　

Add constraints to fields in the table

Alter table name add constraint name primary key (field name );

Alter table name add constraint name UNIQUE (field name );

　　

Place the table in or out the memory area of the database

Alter table name CACHE;

Alter table name NOCACHE;

　　

3. DROP (Delete tables, indexes, views, synonyms, processes, functions, database links, etc)

　　

Delete a table and all its constraints

Drop table name cascade constraints;

　　

4. TRUNCATE (clear all records in the table and keep the table structure)

　　

TRUNCATE table name;

　　

Iii. SELECT

SELECT field name 1, field name 2 ,...... FROM table name 1, [Table name 2,...] WHERE condition;

　　

Field names can be imported into functions.

For example, COUNT (*), MIN (field name), MAX (field name), AVG (field name), DISTINCT (field name ),

TO_CHAR (DATE Field name, 'yyyy-MM-DD HH24: MI: ss ')

　　

NVL (EXPR1, EXPR2) Function

Explanation:

IF EXPR1 = NULL

RETURN EXPR2

ELSE

RETURN EXPR1

　　

DECODE (AA, V1, R1, V2, R2....) Function

Explanation:

If aa = V1 then return R1

If aa = V2 then return R2

.....

ELSE

RETURN NULL

　　

LPAD (char1, n, char2) Functions

Explanation:

Character char1 is displayed based on the specified number of digits n. The reserved number of digits is replaced by the reserved number on the left using the char2 string.

　　

Arithmetic Operations can be performed between Field Names

Example: (field name 1 * field name 1)/3

　　

The query statement can be nested.

Example: SELECT ...... FROM

(SELECT ...... FROM table name 1, [Table name 2,...] WHERE condition) WHERE condition 2;

　　

The results of two query statements can be set.

Example: UNION (remove duplicate records), union all (do not remove duplicate records), difference set MINUS, intersection INTERSECT

　　

Group Query

SELECT field name 1, field name 2 ,...... FROM table name 1, [Table name 2,...] Group by field name 1

[HAVING condition];

　　

Query connections between two or more tables

　　

SELECT field name 1, field name 2 ,...... FROM table name 1, [Table name 2,...] WHERE

Table Name 1. Field name = table name 2. Field name [AND…] ;

　　

SELECT field name 1, field name 2 ,...... FROM table name 1, [Table name 2,...] WHERE

Table Name 1. Field name = table name 2. Field name (+) [AND…] ;

　　

The position of a field with a (+) number is automatically null.

　　

Sort the query result set. The default sorting is ASC in ascending order and DESC in descending order.

　　

SELECT field name 1, field name 2 ,...... FROM table name 1, [Table name 2,...]

Order by field name 1, field name 2 DESC;

　　

Fuzzy string comparison

　　

INSTR (field name, 'string')> 0

Field name LIKE 'string % '[' % string % ']

　　

Each table has an implicit field ROWID, which indicates the uniqueness of the record.

　　

4. Common Data Objects (SCHEMA) in ORACLE)

1. INDEX)

　　

Create index name ON table name (Field 1, [Field 2,…] );

Alter index name REBUILD;

　　

It is recommended that a table have no more than three indexes (except for a special large table). It is recommended that you use a single-field index to analyze the execution of SQL statements,

You can also create a multi-field composite index and a function-based index.

　　

ORACLE8.1.7 the maximum length of a string that can be indexed is 1578 bytes.

The maximum length of an ORACLE8.0.6 string that can be indexed is 758 bytes.

　　

In oracle doc, the maximum index length of a string can be about: the size of the data block (db_block_size) * 40%

　　

2. VIEW)

　　

Create view name as select .... FROM .....;

Alter view name COMPILE;

　　

A view is only an SQL query statement that simplifies the complex relationships between tables.

　　

3. SYNONMY)

Create synonym name FOR table name;

Create synonym name FOR table name @ Database Link name;

　　

4. DATABASE LINK)

Create database link database connection name connect to user name identified by password USING 'database connection string ';

　　

You can use NET8 easy config for database connection strings or directly modify the definition in TNSNAMES. ORA.

　　

When the database parameter global_name = true, the Database Link name must be the same as the remote database name.

　　

You can use the following command to check the global name of the database:

SELECT * FROM GLOBAL_NAME;

　　

Query tables in a remote database

SELECT ...... FROM table name @ Database Link name;

　　

V. permission management (DCL) Statements

1. GRANT Permissions

Common system permission sets include the following:

CONNECT (basic connection), RESOURCE (Program Development), DBA (Database Management)

Common Data Objects have the following permissions:

All on Data Object Name, select on Data Object Name, update on Data Object Name,

Delete on Data Object Name, insert on Data Object Name, alter on Data Object Name

　　

Grant connect, resource to user name;

Grant select on table name TO user name;

Grant select, INSERT, delete on table name TO username 1, username 2;

　　

2. REVOKE permissions from REVOKE

　　

Revoke connect, resource from username;

Revoke select on table name FROM user name;

Revoke select, INSERT, delete on table name FROM username 1, username 2;