Common Oracle SQL statements (1) Introduction to insert delete update (DML)

Common Oracle SQL statements (1) preliminary understanding of insert delete update (DML) 1. INSERT (statement for inserting records INTO the data table) www.2cto.com A. INSERT data INTO the table: insert into Table Name (field name 1, field name 2 ,......) VALUES (value 1, value 2 ,......); B. INSERT data from a table to another table: INSERT INTO Table Name (field name 1, field name 2 ,......) SELECT (field name 1, field name 2 ,......) FROM another table name; www.2cto.com Note: a, string type field value must be enclosed in ** single quotes **, for example :...... values ('good Day') B. If the field value contains single quotation marks and requires String Conversion, we replace it with two single quotation marks ''. c. An error occurs when the value of a string type field exceeds the defined length. It is best to verify the length before inserting. d. The field value of the date field can be ** SYSDATE ** of the current database system time **, there are many date formats, such as seconds or String Conversion to date functions TO_DATE ('2017-08-01', 'yyyy-MM-dd') TO_DATE, year-month-day hour: minute: Second format: YYYY-MM-DD HH24: MI: SS e. The maximum operable string length during INSERT is less than or equal to 4000 single bytes, if you want to insert a longer string, consider using the CLOB type for the field, f Primary Key) If you want to use the serial number Automatically increasing from 1 during INSERT, you should first CREATE a sequence create sequence serial number name (preferably table name + Serial number mark) increment by 1 start with 1 MAXVALUE 99999 cycle nocache; the maximum value is determined BY the length of the field. If you define the Automatically increasing serial NUMBER (6 ), the maximum value is 999999 ** INSERT statement. INSERT this field. The value is the name of the serial number. NEXTVAL ** 2. DELETE (DELETE the statement recorded in the data table) delete FROM table name WHERE condition; for example: DELETE from student where student ID = '001' (DELETE student ID is 001) Note: deleting a record does not release the occupied data block tablespace in ORACLE. it only marks the deleted data blocks as unused. If you want to delete all records in a large table You can use the TRUNCATE command to release the empty data block TABLE: TRUNCATE table name; for example, truncate TABLE student (clear data in the student table) this operation cannot roll back rollback. the deleted data is also stored in the cache. You can roll back and restore the data, but truncate is completely deleted and cannot roll back. 3. UPDATE (modify the statement recorded in the data table) UPDATE table name SET field name 1 = value 1, field name 2 = value 2 ,...... WHERE condition; for example: update student set age = '22 '..... where student ID = '002' (the age of the student whose student ID is 002 is updated to 22 ,....) If the modified value N is not assigned a value or defined, the original record content will be cleared to NULL. It is best to perform non-NULL verification before modification. If the value N exceeds the defined length, an error will occur, it is best to verify the length before inserting .. note:. the preceding SQL statement adds row-level locks to the table. After confirmation, you must add the Transaction Processing Command COMMIT to make the statement take effect. (COMMIT the transaction) otherwise the change may not be written to the database. to recall these operations, run the ROLLBACK command to restore them. b. before Running INSERT, DELETE, and UPDATE statements, it is best to estimate the range of records that may be operated. It should be limited to a small (10 thousand records) range ,. otherwise, ORACLE will use a large rollback segment to process this transaction. slow program response or even loss of response. if the number of records is more than 100,000, you can complete these SQL statements in segments and add COMMIT to confirm transaction processing.