Shulou(Shulou.com)06/01 Report--

Debut: http://www.arppinging.com/wordpress/?p=96

Oracle rookie learning table operation 1. Create a tabl

In oracle, create tables using create table to implement

SQL > create table student (sno number (6), sname varchar2 (12), address varchar2 (20); Table created.SQL > desc student; Name Null? Type-SNO NUMBER (6) SNAME VARCHAR2 (12) ADDRESS VARCHAR2 (20) SQL > 2. Modify the columns of a table

1. Add a column

SQL > alter table student add phone varchar (11); Table altered.SQL > desc student; Name Null? Type-SNO NUMBER (6) SNAME VARCHAR2 (12) ADDRESS VARCHAR2 (20) PHONE VARCHAR2 (11) SQL > 2. To modify column properties, it is important to note that if the modified attribute is length, then the existing data length cannot exceed the modified data length. For example, there is a message that the length of sname is 6. If you change the length of sname to 5, you will make an error. # modify (modified) SQL > alter table student modify sname varchar2 (5); Table altered.SQL > desc student; Name Null? Type-SNO NUMBER (6) SNAME VARCHAR2 (5) ADDRESS VARCHAR2 (20) PHONE VARCHAR2 (11) SQL > 3. Delete column

In oracle, deleting a column requires column (column)

SQL > alter table student drop column phone;Table altered.SQL > desc student; Name Null? Type-SNO NUMBER (6) SNAME VARCHAR2 (5) ADDRESS VARCHAR2 (20) SQL > 3. Insert data 1. For regular insertions, all columns are inserted into SQL > insert into student values (1 row created.SQL > select * from student; SNO SNAME ADDRESS- 1 A BJ2. Insert null values SQL > insert into student values; 1 row created.SQL > select * from student; SNO SNAME ADDRESS- 1 A BJ 2 BSQL > 3. Specify column insertion data SQL > insert into student (sno,address) values (3 row created.SQL > select * from student; SNO SNAME ADDRESS- 1 A BJ 2 B 3 SHSQL > 4. Copy tabl

1. Copy all the contents of the table

SQL > create table student2 as select * from student;Table created.SQL > desc student2; Name Null? Type-SNO NUMBER (6) SNAME VARCHAR2 (5) ADDRESS VARCHAR2 (12) SQL > select * from student2 SNO SNAME ADDRESS--1 A BJ 2 B 3 SHSQL >

two。 Copy only the table structure, not the content

SQL > create table student3 as select * from student where 1 > 2 * table created.SQL > desc student3; Name Null? Type-SNO NUMBER (6) SNAME VARCHAR2 (5) ADDRESS VARCHAR2 (12) SQL > select * from student3 No rows selectedSQL >

3. Insert all the information from the student table into the student3 table (you can also filter with where)

SQL > select * from student3;no rows selectedSQL > insert into student3 select * from student;3 rows created.SQL > select * from student3; SNO SNAME ADDRESS--1 A BJ 2 B 3 SHSQL > 5. Update table update table contents SQL > update student2 set sname='C' where sno=3;1 row updated.SQL > select * from student2; SNO SNAME ADDRESS--1 A BJ 2 B 3 C SHSQL > 6. Delete content

1. Delete a message, which needs to be submitted by commit to use delete

SQL > delete student2 where sname='C';1 row deleted.SQL > select * from student2; SNO SNAME ADDRESS--1 A BJ 2 BSQL > commit;Commit complete.

two。 Clear the contents of the table and retain the table structure

The content cleared by delete needs to be submitted, and the content cleared by delete will be written to the log and can be restored.

SQL > delete student3;3 rows deleted.SQL > select * from student3;no rows selectedSQL > desc student3; Name Null? Type-SNO NUMBER (6) SNAME VARCHAR2 (5) ADDRESS VARCHAR2 (12) SQL > commit Commit complete.

3. Content deleted using truncate table is not written to the log, cannot be recovered, and does not need to be submitted.

SQL > truncate table student2;Table truncated.SQL > select * from student2;no rows selectedSQL > desc student2; Name Null? Type-SNO NUMBER (6) SNAME VARCHAR2 (5) ADDRESS VARCHAR2 (12) SQL > 7. Delete the table SQL > drop table student3;Table dropped.SQL > desc student3;ERROR:ORA-04043: object student3 does not exist8. Rename 1. The renaming format of the table is: rename a to bounding SQL > rename student2 to newstudent;Table renamed.SQL > select * from newstudent SNO SNAME AGE--1 ZhangSan 21 2 FeiFei 22 3 WangWu 23 4 ZhaoYun 24SQL > 2. Rename column format: alter table table_name rename column a to bounding SQL > alter table newstudent rename column age to sage;Table altered.SQL > SQL > desc newstudent; Name Null? Type-SNO NUMBER (6) SNAME VARCHAR2 (10) SAGE NUMBER (38) SQL > 9. View all table names select * from tab

Finish

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.