Oracle General Operations

1. Create a table

SQL code 650) this.width=650; "class=" star "src=" Http://1395573703.iteye.com/images/icon_star.png "alt=" Favorite Code "style=" border:0px; "/>

1. Create Table Test (

2. ID VARCHAR2 (10),

3. Age Number

4. );

2. Backup table

SQL code 650) this.width=650; "class=" star "src=" Http://1395573703.iteye.com/images/icon_star.png "alt=" Favorite Code "style=" border:0px; "/>

1. create  as    

2. select  * from  test group  by  id;  

3. Delete a table

SQL code 650) this.width=650; "class=" star "src=" Http://1395573703.iteye.com/images/icon_star.png "alt=" Favorite Code "style=" border:0px; "/>

1. Drop table test; --Delete table structure and table data

4. Clear the table

SQL code 650) this.width=650; "class=" star "src=" Http://1395573703.iteye.com/images/icon_star.png "alt=" Favorite Code "style=" border:0px; "/>

1. truncate  --Empty the data table data, there is no room for return   

2. delete  from  test; ---emptying data table data, There is room for return   

5. Add Field Download

SQL code 650) this.width=650; "class=" star "src=" Http://1395573703.iteye.com/images/icon_star.png "alt=" Favorite Code "style=" border:0px; "/>

1. Alter Table Test Add (

2. name varchar2 (10),

3. Interest VARCHAR2 (20)

4. );

6. Delete fields

SQL code 650) this.width=650; "class=" star "src=" Http://1395573703.iteye.com/images/icon_star.png "alt=" Favorite Code "style=" border:0px; "/>

1. Alter table Test drop name;

7. Update data

7.1 Updating a single piece of data

SQL code 650) this.width=650; "class=" star "src=" Http://1395573703.iteye.com/images/icon_star.png "alt=" Favorite Code "style=" border:0px; "/>

2. Update Test T

4. set t.id=' 001 '

6. where t.id is not null;

8. commit;

7.2 Updating multiple data from other tables

SQL code download 650) this.width=650; "class=" star "src=" Http://1395573703.iteye.com/images/icon_star.png "alt=" Collection code "style=" border:0px; "/>

2. update Test T set T.name= (

4. Select TM. name

6. from Test_common TM

8. where t.id=tm.id

10. );

12. commit;

14. --Note: When updating data, it is best to create a separate table for SQL in the update subquery to improve the speed of updates.

7.3 Querying data in PL/SQL to change data directly into edit mode

SQL code 650) this.width=650; "class=" star "src=" Http://1395573703.iteye.com/images/icon_star.png "alt=" Favorite Code "style=" border:0px; "/>

1. select  * for  update ;   

2. --Note: The update operation is finished, to lock the data table, while executing the commit action   

8. Query data

SQL code 650) this.width=650; "class=" star "src=" Http://1395573703.iteye.com/images/icon_star.png "alt=" Favorite Code "style=" border:0px; "/>

1. Select * from test;

9. Query the number of data sheets

SQL code Download

1. Select Count (0) from test;

2. --Note: count (0) or other numbers save database resources more efficiently and faster than COUNT (*)

10. Inserting data

10.1 Inserting multiple fields in a single piece of data

SQL code 650) this.width=650; "class=" star "src=" Http://1395573703.iteye.com/images/icon_star.png "alt=" Favorite Code "style=" border:0px; "/>

1. Insert into Test (C1,C2) values(1,' Technical Department ');

SQL code 650) this.width=650; "class=" star "src=" Http://1395573703.iteye.com/images/icon_star.png "alt=" Favorite Code "style=" border:0px; "/>

1. insert  select  c1,c2 from  test_common;  

2. commit ;   

10.2 Inserting more than one data

SQL code 650) this.width=650; "class=" star "src=" Http://1395573703.iteye.com/images/icon_star.png "alt=" Favorite Code "style=" border:0px; "/>

2. Insert into Test (

4. Id

6. name,

8. Age

10. )

12. Select

14. Id

16. name,

18. Age

20. from Test_common;

22. commit;

24. --Note: 1. When inserting multiple data, insert INTO statement does not have values, directly splicing data query statement; 2. After you insert, UPDATE, Delete, and so on in Oracle, you perform a commit commit. Otherwise, in the system is forbidden to operate the database, because the database has been locked down, this is the database in order to prevent multiple people at the same time to modify the database data caused confusion of a preventive mechanism.

Oracle General Operations