First episode: http://www.arppinging.com/wordpress/?p=96
Table operation of oracle rookie learning
1. create table
In oracle, create table is used to create a table
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 the 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.Modify column properties,Note that if the modified attribute is length, the existing data length cannot exceed the modified data length. For example, there is an existing message, sname Length is 6, if you will sname If the length of is changed to 5, an error will occur. # modify is used here 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 columns
In oracle, to delete a column, you need to use 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.Conventional insertion,All columns are inserted
SQL> insert into student values(1,'A','BJ');
1 row created.
SQL> select * from student;
SNO SNAME ADDRESS
---------- --------------- ------------------------------------
1 A BJ
2.Insert null value
SQL> insert into student values(2,'B',null);
1 row created.
SQL> select * from student;
SNO SNAME ADDRESS
---------- --------------- ------------------------------------
1 A BJ
2 B
SQL>
3.Specify column insert data
SQL> insert into student(sno,address) values(3,'SH');
1 row created.
SQL> select * from student;
SNO SNAME ADDRESS
---------- --------------- ------------------------------------
1 A BJ
2 B
3 SH
SQL> 4. copy table
1. Copy all 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 SH
SQL> 2. 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 selected SQL>
3. Insert all the information of the student table into the student 3 table (you can also use where filter)
SQL> select * from student3;
no rows selected
SQL> insert into student3 select * from student;
3 rows created.
SQL> select * from student3;
SNO SNAME ADDRESS
---------- --------------- ------------------------------------
1 A BJ
2 B
3 SH
SQL> 5. update table
Update content in table
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 SH
SQL>6. Delete content
1. To delete a piece of information, you need to commit to use delete
SQL> delete student2 where sname='C';
1 row deleted.
SQL> select * from student2;
SNO SNAME ADDRESS
---------- --------------- ------------------------------------
1 A BJ
2 B
SQL> commit;
Commit complete.2. Clear the contents of the table and keep the table structure
If you use delete to clear, you need to submit. The contents cleared by delete will be written to the log and can be recovered.
SQL> delete student3; 3 rows deleted. SQL> select * from student3; no rows selected SQL> desc student3; Name Null? Type ----------------------------------------- -------- ---------------------------- SNO NUMBER(6) SNAME VARCHAR2(5) ADDRESS VARCHAR2(12) SQL> commit; Commit complete.
3. The deleted content using truncate table will not be 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 selected SQL> desc student2; Name Null? Type ----------------------------------------- -------- ---------------------------- SNO NUMBER(6) SNAME VARCHAR2(5) ADDRESS VARCHAR2(12) SQL>
7. delete table
SQL> drop table student3; Table dropped. SQL> SQL> SQL> desc student3; ERROR: ORA-04043: object student3 does not exist
8. renaming
1.Rename table
//Format: rename a to b;
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 24
SQL>
2.Renaming ranks
//Format: alter table [name rename column a to B;
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