I. Data Control Statement (DML) section 1.Isert (inserted into the data in 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; string type field value 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 '' '' '' ''. The string type field value exceeds the defined length error, it is best to perform a length verification before insertion . Date field field value can be used with the current database system time sysdate, accurate to second or string into a date function to_date ('2001-08-01', 'YYY-mm-dd') to_date () Many date formats, you can see Oracle Doc. Year - Years - Sun: Minute: Second Format YYYY-MM-DD HH24: MI: The maximum operable string length is less than or equal to 4000 single-bytes, if you want Insert a longer string, consider the field with the clob type, method to borrow the DBMS_LOB package in Oracle. INSERT If you want to use the serial number from 1 to start automatically, 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; where the largest value is set by the length, if the defined automatic growth serial number Number (6), maximum Insert this field value for 999999insert statement: The name of the serial number. NexTVal2.delete (statement recorded in the data sheet) delete from the name of the name; Note: Deleting records do not release the data block table space occupied in Oracle. It only uses the 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 Truncate Table table name that the occupied data block table; this operation cannot be retracted. 3.Update (statement recorded in the data sheet) Update table name SET field name 1 = value 1, field name 2 = value 2, ... where condition; if the modified value n does not assign or define, the original Record content clear NUL L, it is best to perform non-empty verification 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 pair adds a row-level lock After the confirmation is completed, it must be paid to the command of the end of the event. Command can be formally effective. Otherwise, the change is not necessarily written into the database. If you want to withdraw these operations, you can use the command rollback to restore .b. Before running Insert, DELETE and UPDATE It is best to estimate the scope of possible operation, and should be limited to a smaller (10,000 records). Otherwise, Oracle handles this thing to use a big return. The program response is slow and even lost. If record With more than 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, Synonyms, Process, Function, Database Link, etc.) Oracle Common Field Types with CHAR Fixed Strings VARCHAR2 Variable Length Strings Number (M, N) Digital M is a bit number total length, n is a decimal length Date Date type creation table When you want to put the smaller fields in front, you may use the Chinese field name when you create a table later, but it is best to create a table with the field name in English. Default, for example, DEFAULT SYSDATE This time you insert and modify, you can add a constraint condition to the field when you get the time to create a table, you can add constraints, for example, if you do not allow repeating UNIQUE, keyword primary key2.alter (changing table , Index, view, etc.) Change the name of the table ALTER TABLE Table Name 1 to Table Name 2; Add a field name segment name description of a field ALTER TABLE table name after the table;
Modify the definition of the field in the table Description ALTER TABLE Table Name MODIFY field name segment name description Unique (field name); put the table or remove the memory area of the database ALTER TABLE table cache; alter Table Name Nocache; 3.Drop (delete table, index, view, synonym, process, function, database link, etc.) delete Table and all of its constraints DROP TABLE Name Cascade constraints; 4.Truncate (all records in the empty table, retention table structure) 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 name 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 = nullreturn expr2else return expr1decode (AA, V1, R1, V2, R2 ....) Function Explanation: if aa = v1 Then Return R1IF AA = V2 THEN RETURN R2 .. ... Elsereturn Nullpad (Char1, N, Char2) function explanation: Character char1 is displayed by Number N, insufficient bit Number use CHAR2 string to replace the left space field name can be arithmetic operation, for example: (field name 1 * field name 1) / 3 Query statement 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: Equipment, set Union, and set union all (not deduplication), the difference set minus Interse InterSect group query Select field name 1, field name 2, ... from table name 1, [table name 2, ......] group BY field name 1 [h AVING Conditions]; Connection between Two Tables Query SELECT Field Name 1, FROM Name 1, [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 .................................. Name 2, ...] ORDER BY field name 1, field name 2 DESC; string fuzzy comparison method INSTR (field name, 'string')> 0 field name Like 'string%' ['% string%' ] Each table has an implicit field ROWID, which is marked with the uniqueness of the record. IV. Idated data object (Schema) 1. Index) Crete Index Index Name ON Name (Field 1, [ Field 2, ...]); ALTER INDEX Index Name 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 combined index of multi-field can be established and the function-based index Oracle8.1.7 characters. The maximum length of the string can index is 1578 single-byte oracle8.0.6 string can index the maximum length of 758 single-byte Oracle DOC saying that the maximum length of the string is about: the size of the data block is: DB_BLOCK_SIZE) * 40 % 2 (View) Create View view name as select .... From ... ..; alter view view name Compile; view is just a SQL query statement, it can make a complicated relationship between the tables simple. 3. Synonyms ( Synnmony) synonym synonym synonym; Create Synonym synonym synonym is the name for table name @ Database link name; 4. Database link (Database link) Create Database Link database link name Connect to username Identified by password using 'database connection string' Database connection strings can be defined in TNSNames.ora by Net8 Easy Config. Database parameter Global_name = true requires the database link name as the remote database name, the database global name can be used to use the following command to detect SELECT * from global_name; query Table Select ... from the table select ... from the table name @ Database link name; 5. Permission management (DCL) statement 1.Grant assigns three system permission sets: Connect (basic connection), resource ( Program development), DBA (database management) common 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 username; Grant SELECT ON table name to username; GRANT SELECT, INSERT, DELETE ON Name To Username 1, User Name 2; 2.Revoke Recycling Right Revoke Connect, Resource from User Name; Revoke Select ON Name FRO M Username; Revoke Select, INSERT, DELETE ON Name from User Name 1, User Name 2; Foreword: Today, I will go to the interview. I have asked a few oracle's problems, I didn't answer, I thought I have been " Self-passing Oracle "Since the home, it seems that it is not familiar with it, it has been full of light. Excerpt text:
