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 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 field value of the string type exceeds the defined length, it is best to perform the length before insertion Check. Field value of the date field can use the current database system time sysdate, accurate to second or by string into a date function to_date ('2001-08-01', 'YYY-mm-mm-dd') to_date () There are also many date formats, you can see Oracle Doc. Year - Years - Sun: Mini-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 with a clob type, borrow the DBMS_LOB package from Oracle. Insert If you want to use the serial number from 1 to start automatically, you should first create a serial number Create. The name of the sequence serial number (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) The maximum value is 99999 INSERT statement inserts this field value: the name of the serial number. NexTVal 2.Delete (Statement recorded in the data sheet) delete from the name of the WHERE condition; Note: Deleting records do not release Oracle to be occupied Data block table space. 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, it can release the occupied data block table space TRUNCATE TABLE table name; this Operation is not rollback. 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 , will Clear the original record content to NULL, it is best to perform non-empty verification before modification; value N exceeds the defined length error, it is best to perform a length check before insertion .. Note: A. The above SQL statement pair Both have a row-level lock. After confirming, the commands that must be completed in the end of the event can be formally effective, otherwise it is not necessarily written to the database. If you want to withdraw these operations, you can use the command rollback to restore. B. Before INSERT, DELETE, and UPDATE statements, it is best to estimate the possible operation record range, and should be limited to smaller (10,000 records). Otherwise, Oracle handles this thing to use a large return. Program Response Slow or even lose the response. If the number of records exceeds 100,000 operations, you can divide these SQL statements to be divided, and there is a Commital Confirmation. II. Data Definition (DDL) section 1.create (Created Table, Index , View, synonym, process, function, database link, etc.) Oracle common field type with char fixed length string varchar2 variable length string Number (m, n) digital M is bit number total length, 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 next to create a table name, but it is best to create in English field name. When the table can add default values, such as the DEFAULT SYSDATE, when inserting and modifying, it is possible to obtain the time to create the table when the field is created, and the constraint condition can be added to the field, for example, not allowing repetition UNIQUE, keywords PRIMARY Key 2.alter changes the name of the table in the table, the name of the table name 2;
Add a field name segment name description in the table after the table; Modify the definition of the field in the form Description ALTER TABLE Table Name MODIFY Field Name Description; Add a Confirmation Condition ALTER TABLE Name Add Constraint Constrained Name Primary Key; ALTER TABLE Name Add Constraint Constrained Name Unique (Field Name); put the table or remove the memory area of the database ALTER TABLE NOCHE; ALTER TABLE Name Nocache; 3.Drop Delete Table, Index, View, Synonyms, Procedures, Functions, Database Links, etc.) Remove Table and All Constraint Conditions Drop Table Name Cascade Constraints; 4.Truncate (All records in the empty table, retention table structure) Truncate table Name; III. 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 = null return expr2 else return expr1 Decode (AA, V1, R1, V2, R2 ....) function explanation: if aa = v1 Then Return R1 if AA = V2 THEN RETURN R2 .. ... Else Return Null LPAD (CHAR1, N, CHAR2) function explanation: Character char1 is displayed in the set number n, and the short bit number can be used to replace the left space field name between the left space field, 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 result of the two query statements can be made, for example : Convert UNION (remove repeated record), set union all (do not deduct repetition), gather 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]; two-oriented connection Query 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 location automatic null value query The sequencing operation of the result set, the default sort is an ascending ASC, the descending order is the desc select field name 1, field name 2, ... from table name 1, [Table name 2, ...] order By field name 1, field name 2 DESC ; Character string fuzzy comparison method INSTR (field name, 'string')> 0 field name Like 'string%' ['% string%'] There is a hidden field RowID, which is marked The uniqueness of the record. IV. Common data objects (SCHEMA) 1. Index (INDEX) CREATE INDEX Index Name ON Name (Field 1, [Field 2, ...]); ALTER INDEX Index Rebuild;
