SQL syntax and data objects commonly used in Oracle

I. Data Control statement (DML) section

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

INSERT INTO 表名(字段名1, 字段名2, ……) VALUES ( 值1, 值2, ……);
INSERT INTO 表名(字段名1, 字段名2, ……) SELECT 字段名1, 字段名2, …… FROM 另外的表名;

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 the field with the Clob type.

method 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

CREATE SEQUENCE 序列号的名称 (最好是表名+序列号标记) 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表名 WHERE 条件;

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.

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

UPDATE表名 SET 字段名1=值1, 字段名2=值2, …… WHERE 条件;

If the modified value n is not assigned or defined, the original record content will be cleared to null, preferably a non-null checksum before modification;

The value n exceeds the length of the definition, and it is best to check the length before inserting.

Precautions:

A. The above SQL statement adds row-level locks to the table.

After the confirmation is complete, must add the matter processing end the order COMMIT to take effect formally,

Otherwise, the change is not necessarily written into the database.

If you want to recall these operations, you can use the command ROLLBACK to recover.

B. It is best to estimate the range of records that might be in operation before running the insert, DELETE, and UPDATE statements.

It should be limited to a smaller (10,000 records) range. Otherwise, Oracle handles this thing with a very large fallback segment.

The program responds slowly or even loses its response. If you record more than 100,000 of these operations, you can divide the SQL statements into sections.

With a commit to confirm things.

Two. Data definition (DDL) section

1.CREATE (Create tables, indexes, views, synonyms, procedures, functions, database links, etc.)

The types of fields commonly used by Oracle are

CHAR fixed-length string

VARCHAR2 variable-length strings

Number (M,n) M is the total number of digits, N is the length of the decimal

Date Date Type

To create a table, place a smaller, not-empty field in front of it, possibly with an empty field behind it

You can create a table with a field name in Chinese, but preferably a field name in English

You can add a default value to a field when you create a table, such as Sysdate

So every time you insert and modify this field, you can get the action time without using the program.

You can add constraints to a field when you create a table

For example, do not allow duplicate unique, keyword PRIMARY key

2.ALTER (change table, index, view, etc.)

Change the name of a table

ALTER TABLE 表名1 TO 表名2;

Add a field to the back of the table

ALTER TABLE表名 ADD 字段名 字段名描述;

Modify the definition description of a table or table field

ALTER TABLE表名 MODIFY字段名 字段名描述;

Add constraints to the fields in the table