oracle常用语法（Oracle common syntax）
ORACLE commonly used SQL syntax and data objects
2001-08
I. data control statement (DML) section
1.INSERT (records inserted into data tables)
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 quotation mark to string conversion, we replace it with two single quotes.
The field value of a string type will go wrong beyond the length of the definition. It is better to perform a le
ngth check before inserting
Field values for date fields can be accurate to seconds by system time SYSDATE of the current database
Or converts a string into a date type function TO_DATE (` 2001-08-01 ',' YYYY-MM-DD ')
TO_DATE () also has many date formats, see ORACLE DOC.
Year month month day hour: minute: seconds format YYYY-MM-DD HH24:MI:SS
INSERT, the maximum operable string length is less than or equal to 4000 single bytes. If you want to insert a longer string, consider the field using the CLOB type,
Method borrows the DBMS_LOB package that comes with ORACLE
In INSERT, if you want to automatically increase the serial number from 1, you should create a serial number first
The name of the CREATE SEQUENCE sequence number (preferably table name + sequence number marker), INCREMENT BY 1, START WITH 1
MAXVALUE 99999, CYCLE, NOCACHE;
The maximum value is determined by the length of the field. If the automatically defined sequence number NUMBER (6) is defined, the maximum value is 999999
The INSERT statement inserts this field as the name of the serial number.NEXTVAL
2.DELETE (statement deleted from the data table)
DELETE FROM table name WHERE condition;
Note: deleting the record does not release the occupied chunks of the ORACLE tablespace. It only labels the deleted data blocks as unused.
If you really want to delete all the records of a large table, you can use the TRUNCATE command to release the occupied chunks of table space
TRUNCATE TABLE table name;
This operation cannot be backed up
3.UPDATE (modifying statements recorded in data tables)
UPDATE table name, SET field name, 1= value 1, field name 2=, value 2,...... WHERE condition;
If the modified value N is not assigned or defined, the original record content is NULL, so it is better to perform a non null check before modification;
The value N exceeds the length of the definition and makes errors. It is better to perform a length check before inserting
Matters needing attention:
The SQL statement above A. adds a row level lock to the table,
After completion of the validation, the COMMIT must be added with the order of the end of the processing,
Otherwise, the changes are not necessarily written into the database
delete inIf you want to retract these operations, you can use the command ROLLBACK to recover
Before INSERT, DELETE, and UPDATE statements are run, B.'s best estimate is the range of records for possible operations,
It should be limited to a smaller (ten thousand record) range. Otherwise, ORACLE handles this thing in very large rollback segments
The program responds slowly or even loses its response. If the number of records is more than one hundred thousand, these operations can be done by splitting the SQL statements into blocks,
Meanwhile, COMMIT is added to confirm the handling of the matter
Two. Data definition (DDL) section
1.CREATE (create tables, indexes, views, synonyms, processes, functions, database links, etc.)
ORACLE commonly used field types are
CHAR fixed length string
VARCHAR2 variable length string
NUMBER (M, N) numeric M is the total number of digits, and N is the length of the decimal
DATE date type
When creating a table, place smaller, non empty fields ahead, and empty fields later
When you create a table, you can use the field name in Chinese, but it's better to use the field name in English
When you create a table, you can add a default value to the field, such as DEFAULT SYSDATE
Thus, every time you insert and modify, you can get the action time without the program operation
You can add constraints to a field when you create a table
For example, duplicate UNIQUE is not allowed, keyword PRIMARY KEY
2.ALTER (change tables, indexes, views, etc.)
Change the name of the table
RENAME table name 1, TO table name 2;
Add a field at the back of the table

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。