Oracle Common SQL Syntax collection

I. Data Control statement (DML) section

1.insert (Insert the statement into the data sheet)

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 for string types must be enclosed in single quotes, such as: ' Good day '

If the field value contains a single quote ' that requires a string conversion, we replace it with two single quotes '.

The value of a field with a string type exceeds the length of the definition, and it is best to check the length before inserting it.

The field value of the Date field can be sysdate to the second with the current database's system time

or convert a string into a date-type function to_date (' 2001-08-01 ', ' yyyy-mm-dd ')

To_date () also has a number of date formats, which you can refer to Oracle DOC.

Year-month-day hours: minutes: sec format Yyyy-mm-dd HH24:MI:SS

The maximum operable string length at insert is less than or equal to 4,000 single-byte, and if you want to insert a longer string, consider using the CLOB type for the field to borrow the Dbms_lob package from Oracle.

Insert when you want to use the automatic growth of the serial number starting from 1, you should first create a serial number

The name of the Create SEQUENCE serial number (preferably the table name serial number tag) INCREMENT by 1 START with 1

MAXVALUE 99999 CYCLE NoCache;

The maximum value is determined by the length of the field, and if the defined automatically-growing serial number (6), the maximum is 999999

Insert statement inserts this field value as: The name of the serial number. Nextval

2.delete (delete the statement recorded in the datasheet)

Delete from table name where condition;

Note: Deleting records does not release the data block tablespace that is occupied in Oracle. It only marks those deleted chunks of data as unused.

If you are sure you want to delete all records in a large table, you can use the TRUNCATE command, which frees up the data block tablespaces that occupy

TRUNCATE TABLE name;

This operation cannot be rolled back.