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 (Values 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' need to make a string conversion, we replace it into two single quotes' '. String type field The value exceeds the defined length error, it is best to perform a length check before insertion.
The field value of the date field can be used with the current database system time sysdate, accurate to second or string into a date function to_date ('2001-08-01', 'YYYY-MM-DD') to_date () has a lot. Date format, you can see Oracle Doc. Year - Yue - Yue Hours: Minutes: Second Format YYYY-MM-DD HH24: MI: SS
The maximum operable string length at INSERT is less than or equal to 4000 single-bytes. If you want to insert a longer string, consider the field with a Clob type, and use the DBMS_LOB package from Oracle.
When INSERT, if you want to use the serial number from 1 to start from 1, you should create a serial number create sequence serial number name (preferably a table name serial number tag) increment by 1 start with 1 maxValue 99999 cycle nocache; The maximum value is set by the length of the field. If the defined automatic growth serial number Number (6), the maximum value is 999999insert statement into 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 table space TRUNCATE TABLE table name; this cannot be retracted.
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 will be cleared to null, preferably in a non-air check before modification; the value N exceeds the length of the defined error, it is best to perform a length check before insertion .
Note: A. The above SQL statement panel is equipped with a row lock. After confirming, the commands that must be completed in the end of the event can be formally effective, otherwise the change does not necessarily write to the database. If you want to withdraw these operations, You can use the command rollback. B. It is best to estimate the possible operation record range before running the Insert, DELETE, and UPDATE statements, should be limited to smaller (10,000 records), or Oracle handle this thing Go back to a big return. The program response is slow and even lost. If the number of records exceeds 100,000 operations, these SQL statements can be done twice, and there are COMMIT confirmation. II. Data Definition (DDL ) section
1.create (create table, index, view, synonym, process, function, database link, etc.)
Oracle Common Field Types with CHAR Fixed Strings VARCHAR2 Variable Length String Number (M, N) Digital M is a total length of bit number, 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 default values to the field, such as the DEFAULT SYSDATE, which can be used to operate each time you insert and modify it.
When you create a table, you can add constraints to the field, for example, if you do not allow repeated UNIQUE, keyword primary key 2.alter (changing table, index, view, etc.)
Change the name of the table ALTER TABLE Table Name 1 to Table Name 2; Add a field ALTER TABLE Name Add Field Name Field Name Description;
Modify the definition of fields in the table Description ALTER TABLE Table Name Modify field name field name description;
Plus the fields in the table plus constraints ALTER TABLE TA name Add constraint constraint name Primary key (field name); ALTER TABLE Name Add constraint constraints 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 of 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 names can be brought into functions such as 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 expr2else returnif
Decode (AA, V1, R1, V2, R2 ....) function explanation: if AA = V1 THEN RTURN R1IF AA = V2 THEN RTURN R2 .. ... Elsereturn 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 a char2 string.
Arithmetic operations can be performed between field names, for example: (Field name 1 * field name 1) / 3
The query statement can be nested, for example: select ... from (select ...... from table 1, [Name 2, ...] Where Conditions) Where condition 2;
The results of the two query statements can make a collection operation, for example: set Union (remove repeated record), set Union All (do not remove repeated record), 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 condition];
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 ...];
There is a field of ( ) field position automatic null value query result set sort operation, 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;
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;
The index of a table is preferably not more than three (except special big sheets), it is best to use a single field index, combined with the analysis of the SQL statement, or the combination index of multi-field can also create a function-based index.
Oracle8.1.7 strings can index the maximum length of 1578 single-byte oracle8.0.6 string can index the maximum length of 758 single-byte Oracle DOC saying the maximum length of the string to the maximum index is: 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 Create Synonym synonymous FOR table name; Create Synonym synonym synonym with a name for table name @ Database link name;
4. Database Link Create Database Link Create Database Link Name Connect To Username Identified by Password Using 'Database Connection String'; Database Connection String You can use Net8 Easy Config or directly modify the TNSNames.ora definition.
Database parameter global_name = true requires the database link name as the distal database name
The database global name can be used to use the following command to detect SELECT * from Global_name;
Query the table select ... from the remote database ... from the table name @ database link name;
5. Permission Management (DCL) statement
1.Grant is equipped with the following three-purpose system permission set: Connect (basic connection), resource, DBA (database management), data object permissions, 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 Name To User Name; Grant SELECT, INSERT, DELETE ON Name To User Name 1, User Name 2;
2.Revoke Recycling Permissions
Revoke connect, resource from username; Revoke Select ON Name from username; Revoke Select, Insert, Delete ON Name from User Name 1, User Name 2;
