Oracle common SQL syntax and data objects
2001-08 Yu Feng
1. Data Control Statement (DML) section
1.insert (insert the recorded statement into the data sheet)
INSERT INTO Name (Field Name 1, Field Name 2, ...) VALUES (value 1, value 2, ...);
INSERT INTO Name (Field Name 1, Field Name 2, ...) SELECT (Field Name 1, Field Name 2, ...) from another table name;
The field value of the string type must be enclosed in single quotes, for example: 'good day'
If the field value contains single quotes', you need to perform a string conversion, we replace it into two single quotes' '.
The field value of the string type exceeds the defined length, it is best to perform a length verification before inserting.
Field value of the date field can use the current database system time sysdate, accurate to second
Or use strings to convert to date-type functions to_date ('2001-08-01', 'YYYY-MM-DD')
TO_DATE () There are also many date formats, you can see Oracle DOC.
Year - Month - Sun: Minute: Second Format YYYY-MM-DD HH24: MI: SS
The maximum operable string length is less than or equal to 4000 single-bytes, if you want to insert a longer string, consider the type of CLOB,
Method Borrows the DBMS_LOB package from Oracle.
When INSERT is used to use the serial number from 1 to start automatically, you should build a serial number first.
Create sequence serial number name (best is a table name serial number tag) Increment by 1 start with 1
MaxValue 99999 Cycle Nocache;
The largest value is set by the length of the field, and if the defined automatic growth serial number Number (6), the maximum is 99999
Insert statement inserts this field value: the name of the serial number. NexTVal
2.Delete (delete the statement recorded in the data sheet)
DELETE FROM Name WHERE Condition;
Note: Deleting records do not release the data block table space occupied in Oracle. It only specifies those deleted data blocks into unused.
If you really want to delete all records in a big table, you can use the truncate command, which can release the occupied data block tablespace
TRUNCATE TABLE name;
This cannot be rolled back.
3.Update (Modify the statement recorded in the data sheet)
Update table name set field name 1 = value 1, field name 2 = value 2, ... Where Conditions;
If the modified value n does not assign or define, the original record content is cleared to NULL, it is best to perform non-air check before modification;
Value N exceeds the defined length error, it is best to perform a length check before insertion.
Precautions:
A. The above SQL statement pair has a row-level lock.
After the confirmation is completed, the command of the end of the event will be formally effective.
Otherwise, change is not necessarily written in the database.
If you want to withdraw these operations, you can recover if you want ROLLBACK.
B. It is best to estimate the possible operation record range before running Insert, DELETE, and UPDATE statements.
It should be limited to smaller (10,000 records),. Otherwise, Oracle handles this thing to use a large return.
The program responded slowly and even lost response. If the record number exceeds 100,000 operations, you can divide these SQL statements to be completed.
In addition, add the commit handling.
2. Data Definition (DDL) section
1.create (create table, index, view, synonym, process, function, database link, etc.)
Oracle common field types
CHAR fixed length string
VARCHAR2 variable length string
Number (m, n) digital M is a total length of bit, n is a decimal length
Date date type
When you create a table, you should put the smaller fields in front, you may be empty in the back
You can use Chinese field name when you create a table, but it is best to use English field name.
When you create a table, you can add a default value to the field, such as default sysdate.
This time is inserted and modified, you can get action time without program operation.
Create a table to add constraints to the field
For example, you are not allowed to repeat unique, keyword primary key
2.Alter (change the table, index, view, etc.)
Change the name of the table
ALTER TABLE Name 1 to Table Name 2;
Add a field after the table
ALTER TABLE Table Name ADD field name field name description;
Modify the definition description of the field field
ALTER TABLE Name Modify field name field name description;
Add constraints to the fields in the table
ALTER TABLE Name Add Constraint Constraint Name Primary Key;
ALTER TABLE Name ADD CONSTRAINT Constraint Name Unique (Field Name);
Put the table or remove the memory area of the database
ALTER TABLE Name Cache;
ALTER TABLE Name Nocache;
3.Drop (Delete Table, Index, View, Synonyms, Procedures, Functions, Database Links, etc.)
Delete the table and all its constraints
DROP TABLE Name Cascade Constraints;
4.Truncate (all records in the clear table, the structure of the table)
TRUNCATE table name;
III. Query statement (SELECT)
SELECT field name 1, field name 2, ... from table name 1, [table name 2, ...] where condition;
Field name 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
Explanation:
IF expr1 = null
Return EXPR2
Else
Return EXPR1
Decode (AA, V1, R1, V2, R2 ....) function
Explanation:
IF aa = v1 Then Return R1
IF aa = v2 Then RTURN R2
.. ...
Else
Return Null
LPAD (CHAR1, N, CHAR2) function
Explanation:
Character char1 is displayed by the number of bits n, and the number of insufficient bits replace the left space with the char2 string.
Arithmetic operations can be performed between field names
For 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 Conditions) Where condition 2;
The results of the two query statements can be made
For example: Equation Union (deduplication), set union all (not deduplication), the difference set minus, intersection intersect
Group query
SELECT field name 1, field name 2, ... from table name 1, [Table name 2, ...] Group By field name 1
[HaVing conditions];
Connection query between two or more 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 ...];
Fields in ( ), automatic empty value
The sorting operation of the query result set, the default sort is ascending ASC, descending order is DESC
SELECT field name 1, field name 2, ... from table name 1, [Name 2, ......]
ORDER BY field name 1, field name 2 DESC;
Method for comparing string fuzzy comparison
INSTR (field name, 'string')> 0
Field name Like 'string%' ['% string%']
Each table has an implicit field ROWID that marks the uniqueness of the record.
Four. Common data objects (Schema)
Index (INDEX)
CREATE INDEX Index Name ON Name (Field 1, [Field 2, ...]);
ALTER INDEX Index Rebuild; a table's index is preferably not more than three (except special big tables), it is best to use single-field index, combine the analysis of the SQL statement,
You can also create a combined index of multi-definitions and a function-based index.
Oracle8.1.7 string can index the maximum length of 1578 single byte
Oracle8.0.6 string can index the maximum length of 758 single byte
Oracle Doc speaks the maximum length of string of strings to determine the length of: the size of the data block (db_block_size) * 40%
2. View (View)
Create View view name as select .... From ... ..;
ALTER VIEW view name Compile;
The view is just a SQL query statement that is simple and complicated between the tables.
3. Synonyms (Synnmony)
Create synonym synonymous with the FOR table name;
Create Synonym synonymous with the name for table name @ Database link name;
4. Database link (Database Link)
Create Database Link Database Link Name Connect To User Name Identified by Password Using 'Database Connection String'
The database connection string can be defined in TNSNames.ora with Net8 Easy Config or directly modify TNSNAMES.ORA.
Database parameter global_name = true requires the database link name as the distal database name
The database global name can be isolated by the following command
Select * from global_name;
Query the table in the remote database
SELECT ... FROM table name @ Database link name;
5. Permission Management (DCL) statement
1.Grant assigns permissions
Common system permission sets have the following three:
Connect (basic connection), resource, DBA (Database Management)
Commonly used data object privileges 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 username;
Grant SELECT ON Name to User Name;
Grant SELECT, INSERT, DELETE ON Table Name To User Name 1, User Name 2;
2.Revoke Recycling Permissions
Revoke Connect, Resource from username;
Revoke Select ON Name from User Name;
Revoke SELECT, INSERT, DELETE ON Name from User Name 1, User Name 2;
