ORACLE's Common SQL syntax and data Object _oracle

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.

field values for date fields can be sysdate by the current database's system time, accurate to seconds, or converted to date-type functions to_date (' 2001-08-01 ', ' Yyyy-mm-dd ') to_date () There are many date formats, see 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 create SEQUENCE serial number name (preferably 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.

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

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 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 statements add row-level locks to the table, confirm that after the completion of the process must be combined with the end of the order to formally enter into effect, or 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's a good idea to estimate the scope of a possible operation before running the insert, DELETE, and UPDATE statements, which should be limited to a small (10,000 record) 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

When you create a table, you place a smaller, not-empty field in front of it, and you might put an empty field in the field name when you create the table in Chinese, but it's best to use the field name in English

When you create a table, you can add a default value to the field, such as sysdate, so every time you insert and modify it, you can get the action time without 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 name 1 to table name 2;

Add a field to the back of the table
ALTER Table name ADD field First Name section name description;

Modify the definition description of a table or table field
ALTER Table name Modify field name paragraph name description;

Add constraints to the fields in the table
ALTER table name ADD CONSTRAINT constraint name PRIMARY KEY (field name);
ALTER table name ADD CONSTRAINT constraint name UNIQUE (field name);

Place the table in or out of the memory area of the database
ALTER table name CACHE;
ALTER table name NoCache;

3.DROP (delete tables, indexes, views, synonyms, procedures, functions, database links, etc.)

Delete a table and all of its constraints
DROP Table table name CASCADE CONSTRAINTS;

4.TRUNCATE (Clear all the records in the table, keep the structure of the tables)

TRUNCATE table name; three. Query Statement (SELECT) section

Select Field Name 1, field Name 2, ... From table name 1, [table Name 2, ...] WHERE condition;

Field names can be brought into functions
For example: COUNT (*), MIN (field name), MAX (field name), AVG (field name), DISTINCT (field name), To_char (date field name, ' Yyyy-mm-dd HH24:MI:SS ')

NVL (EXPR1, EXPR2) function
Explain:
IF Expr1=null
Return EXPR2
ELSE
Return EXPR1

DECODE (Aa,v1,r1,v2,r2 ...) Function
Explain:
IF aa=v1 THEN return R1
IF Aa=v2 THEN return R2
.. ...
ELSE
Return NULL

Lpad (CHAR1,N,CHAR2) function
Explain:
The character char1 is displayed by the number of digits in the set, and the number of digits replaces the left vacancy with the CHAR2 string.

Arithmetic operations can be performed between field names
Example: (Field name 1* field name 1)/3

Query statements can be nested
For example: SELECT ... From
(SELECT .....) From table name 1, [table Name 2, ...] Where condition) where Condition 2;

The results of two query statements can be set operations
For example: Union (remove duplicate records) and set union ALL (do not remove duplicate records), set minus, intersection intersect

Grouped queries
Select Field Name 1, field Name 2, ... From table name 1, [table Name 2, ...] GROUP by field name 1
[Having conditions];

Connection queries between more than two tables

Select Field Name 1, field Name 2, ... From table name 1, [table Name 2, ...] WHERE
Table name 1. field name = Table Name 2. field name [and ...];

Select Field Name 1, field Name 2, ... From table name 1, [table Name 2, ...] WHERE
Table name 1. field name = Table Name 2. Field name (+) [and ...];

field position Auto fill with (+) number

The sorting operation of the query result set, the default sort is ascending ASC, descending is desc

Select Field Name 1, field Name 2, ... From table name 1, [table Name 2, ...]
Order BY Field name 1, field name 2 DESC;

A method of fuzzy comparison of strings

INSTR (field name, ' string ') >0
Field name like ' String% ' ['% string% ']

Each table has an implied field rowid, which marks the uniqueness of the record.

Data Objects (schemas) commonly used in Oracle

1. indexes (Index)

CREATE index index name on table name (field 1, [Field 2, ...]);
ALTER index index name REBUILD;

A table should not have more than three indexes (except for a special large table), preferably with a single field index, combined with the analysis of SQL statements, and a combination of multiple indexes and a function based index can be established.

ORACLE8.1.7 string can be indexed with a maximum length of 1578 Single-byte
ORACLE8.0.6 string can be indexed with a maximum length of 758 single-byte

2. Views (view)

CREATE View view name as SELECT .... From ...;
ALTER view view name COMPILE;

A view is just a SQL query statement that can make complex relationships between tables simple.

3. Synonyms (synonmy)
CREATE synonym synonym name for table name;
CREATE synonym synonym name for table name @ database link name;

4. Databases link (database link)
CREATE Database link Database links name connect to username identified by password USING ' database connection string '; Database connection strings can be defined in NET8 easy config or directly by modifying Tnsnames.ora.

Database parameter global_name=true requires the database link name to be the same as the remote database name

The database global name can be identified with the following command
SELECT * from Global_name;

Querying tables in the remote database
SELECT ... From table name @ database link name;

Rights Management (DCL) statements

1.GRANT Assignment to Permissions
The common set of system permissions has the following three:
Connect (basic connection), RESOURCE (program development), DBA (database management)
The most commonly used data object permissions have the following five:
All on data object name, SELECT on data object name, UPDATE on data object name delete on data object name, INSERT on data object name, ALTER on data object name

GRANT CONNECT, RESOURCE to user name;
GRANT SELECT on table name to user name;
GRANT SELECT, INSERT, DELETE on table name to username 1, user name 2;

2.REVOKE Reclaim Permissions

REVOKE CONNECT, RESOURCE from user name;
REVOKE SELECT on table name from user name;
REVOKE SELECT, INSERT, DELETE on table name from username 1, user name 2;