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