Summary of oracle simple learning (5)-Data Operations (add, delete, modify, and so on)

Oracle simple learning Summary (5) -- Data Operations (add, delete, modify, and delete) Link: oracle simple learning Summary (1) -- sqlhttp: // www.bkjia.com/database/210210/159545.html#oraclesimple learning Summary (2) -- Query Summary (3) -- function http://www.bkjia.com/database/201210/159561.html#oraclesimple learning Summary (4) -- Advanced query http://www.bkjia.com/database/201210/159564.html I. insert Data 1.1 CREATE TABLE [html] CREATE TABLE AZJuserInfor (id int NOT NULL primary key, name varchar (50) NULL, birthday date not null, age int not null, sex int not null); 1.2 INSERT data INTO table name [(Field List)] VALUES (expression list ); [html] insert into AZJuserInfor values (1, 'zhang san', to_date ('2017-02-13 ', 'yyyy: MM: dd'), 2012 ); insert into AZJuserInfor values (2, 'lily', to_date ('2017-05-13 ', 'yyyy: MM: dd'), 2001); insert into AZJuserInfor values (3, 'wang 5', to_date ('2017-08-03 ', 'yyyy: MM: dd'), 2001 );
Note: 1. the type of the inserted field value must match the field type one by one. 2. Field Values of the string type must be enclosed in single quotes, for example, 'zhang san '. An error occurs when the field value of the string type exceeds the defined length. 3. If you want to insert all fields in the table, the field list after the table name can be omitted.
4. For other fields not inserted, the default value of the table is entered. If the default value is not specified during table creation, NULL is inserted. 5. The Field Values of the date type should also be enclosed in single quotes, for example, '10-January 1, January-03 '. The default format of date data is DD-MON-YY, the default century is the current century, the default time is midnight. If the specified century is not this century or the time is not midnight, you must use the TO_DATE system function to convert the string. 1.3 copy data [html] create table AZJuserInfor2 as select id, name, birthday FROM AZJuserInfor WHERE id> 1; description: the CREATE command is used to CREATE a new table 1.4 sequence based on an existing table. When using the INSERT statement, you can use the sequence to enter certain numeric or numeric columns. A sequence is an ordered numerical SEQUENCE to be pre-defined. You should first CREATE a SEQUENCE, and then use [html] create sequence abc increment by 1 START WITH4MAXVALUE 99999 cycle nocache In the insert statement; note: create sequence name increment by interval value start with initial value MAXVALUE maximum cycle nocache; 1.4.1 SEQUENCE Description: Fill IDs WITH sequences during insertion and use abc. nextval can obtain the next value in the sequence. The next two records come from the sequence and are incremental. 2. Modify Data 2.1 modify data the statement UPDATE modifies the data of the specified field in the table. Generally, you need to add the WHERE condition to limit the row to be modified, if the WHERE condition is not added, all rows are modified. UPDATE table name SET field name 1 = expression 1, field name 2 = expression 2 ,... WHERE condition; Description: 1. If the modified value is not assigned a value or defined, the content of the original field is cleared to NULL. 2. if the length of the modified value exceeds the defined length, an error occurs. 3. Omit the WHERE condition. Otherwise, all rows in the table will be modified.
2.2 modify the data UPDATE table name SET based on other tables (field name 1, field name 2 ,...) = SELECT (field name 1, field name 2 ,...) FROM another table name WHERE condition. For example, modify the table azjuserinfor2 according to the azjuserinfor table. 3. DELETE the data delete from table name WHERE condition. Note: records meeting the conditions must be deleted FROM the table, generally, the WHERE condition cannot be omitted. If omitted, all data in the table will be deleted. Note: deleting a record does not release the occupied data block tablespace in Oracle. It only marks the deleted data blocks as unused. To delete all records in a large TABLE, run the TRUNCATE command to release the occupied data block tablespace. Syntax: truncate table name;

This command is similar to the DELETE statement without the WHERE condition. The difference is that the DELETE command can be undone, but the DELETE command cannot be undone. Note: The truncate table command is used to delete all data in a TABLE instead of deleting a TABLE. The TABLE still exists.