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