Oracle Common SQL Syntax collection

Source: Internet
Author: User
Tags date insert table name

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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.