Oracle Learning (ix): Creating and Managing Tables

1. Knowledge Points: Can be read against the following record screen

Sql>--Creating table sql> Create tables test1 2 (TID number, 3 tname varchar2, 4 hidatedate date default sysdate); Sql>--using as and subqueries to build tables quickly sql>--Create tables: include employee number name monthly Salary Department name sql> CREATE TABLE Empincome 2 as 3 select Empno,ename,sal,sa L*12 annlsal,dname 4 from EMP E, Dept d 5 where E.deptno=d.deptno; Sql>--rowid line address, pseudo-column sql> select Rowid,empno,ename from emp; Sql>--Modify table: Append new column Modify column Delete column rename column sql>--Append new column sql> ALTER TABLE test1 add photo blob; Sql>--Modify Column sql> ALTER TABLE test1 modify Tname varchar2 (40); sql>-delete columns sql> ALTER TABLE test1 drop column photo; Sql>-Rename column sql> ALTER TABLE test1 rename column tname to username; sql>--Delete table sql> drop tables Testdelete; Sql>--oracle Recycle Bin sql>--View Recycle Bin sql> show recyclebinsql>-Empty the Recycle Bin sql> purge recyclebin; Sql>--Completely delete table sql> drop tables test1 purge; Sql>--Check Constraint sql> CREATE TABLE Test2 2 (TID number, 3 Tname varchar2), 4 gender varchar2 (4) Check (gend Er in (' Male ', ' female '), 5 sal number Check (Sal >0) 6); Sql>--Test CHECK constraint (gender use special value) sql> insert into test2 values (1, ' Tom ', ' ah ', 1000); Sql>--all constraints, constraint aliases sql>create table MyPerson (2 pid varchar2) constraint Myperson_pk primary key, 3 pname var CHAR2 (4) constraint myperson_name NOT NULL, 4 gender VARCHAR2 (4) Constraint Myperson_gender check (gender in (' Male ', ' female ')) , 5 email VARCHAR2 (+) constraint Myperson_email UNIQUE, 6 deptno number constraint MYPERSON_FK references Dept (Dept NO) on DELETE CASCADE 7)

2. Actual execution of the results screen under Sqlplus

Sql>--Creating table sql> Create tables test1 2 (TID number, 3 tname varchar2, 4 hidatedate date default sysdate), table already Create. sql> INSERT INTO test1 (tid,tname) VALUES (1, ' Tom '); 1 rows have been created.       Sql> select * from Test1;                                                                                                            TID Tname Hidatedate                                                                                                                 ---------- -------------------- --------------                                                                                                            1 Tom 2 March-October-12 1 rows have been selected. Sql> CREATE TABLE EMP10 as SELECT * from EMP where deptno=10;     Sql> select * from EMP10;                                                                     EMPNO ename JOB MGR hiredate SAL COMM DEPTNO ---------- ---------- --------- ---------- -------------- ----- ---------- ------                                                                          7782 CLARK MANAGER 7839 September-June-81 2450 7839 KING Presi                                                                           DENT 1 July-November-81 5000 10                                                                     7934 MILLER Clerk 7782 2 March-January-82 1300 10 3 rows have been selected. Sql>--Quick Build Table sql>--CREATE TABLE: include employee number name salary annual salary Department name sql> CREATE TABLE Empincome 2 as 3 select empno,ename,sal,sal*12 Ann Lsal,dname 4 from EMP E, Dept d 5 where e.deptno=d.deptno; table created.     Sql> select * from Empincome;                                                                                                          EMPNO ename SAL annlsal dname                                                       ---------- ---------- ----- ---------- --------------                                                7369 SMITH 9600                                                                                                                7499 ALLEN 19200 SALES 7521 WARD 1                                                                                                                15000 SALES                                                                                                             7566 JONES 2975 35700                                                                                                                7654 MARTIN 1250 15000 SALES                                                                                                                7698 BLAKE 2850 34200 SALES 7782 CLARK 2450 2         9400 ACCOUNTING                                                                                                  7788 SCOTT 3000                                                                                                             36000                                                                                                           7839 KING 60000 ACCOUNTING                                                                                                                7844 TURNER 18000 SALES                                                                                                            7876 ADAMS 1100 13200 EMPNO ename SAL annlsal D NAME-----------                                                                                                       --------- ----- ---------- -------------- 79JAMES 950 11400 SALES                                                                                                             7902 FORD 36000                                                                                                     7934 MILLER 1300 15600 ACCOUNTING 14 rows have been selected. Sql> host clssql>--rowid line address sql> select Rowid,empno,ename from emp;                                                                                                                   ROWID EMPNO ename                                                                                                              ------------------ ---------- ----------                                                                                                                   AAANNOAAEAAAAACAAA 7369 SMITH      Aaannoaaeaaaaacaab 7499 ALLEN                                                                                                                    AAANNOAAEAAAAACAAC 7521 WARD                                                                                                                   Aaannoaaeaaaaacaad 7566 JONES                                                                                                                  Aaannoaaeaaaaacaae 7654 MARTIN AAANNOAAEAAAAACAAF 7698 Blak E Aaanno                                                                                                                   Aaeaaaaacaag 7782 CLARK                                                                                         Aaannoaaeaaaaacaah 7788 SCOTT                          Aaannoaaeaaaaacaai 7839 KING                                                                                                                  Aaannoaaeaaaaacaaj 7844 TURNER                                                                                                                   Aaannoaaeaaaaacaak 7876 ADAMS                   ROWID                                                                                                                   EMPNO ename                                                                                                              ------------------ ---------- ----------                                                                                                                   Aaannoaaeaaaaacaal 7900 JAMES                                        Aaannoaaeaaaaacaam 7902 FORD                                                                            Aaannoaaeaaaaacaan 7934 MILLER 14 rows have been selected.                                                                                Sql> Host clssql>--Modify table: Append new column Modify column Delete column rename column sql> desc test1 name Is it empty? Type-------------------------------------------------------------------------------------------------------------                                                                                          --------------------------------------TID  Number Tname VARCHAR2 (20) Hidatedate datesql> ALTER TABLE test 1 add photo blob; The table has changed. Sql> desc test1 is the name empty? Type ----------------------------------------------------------------------------------- -------- --------------------                                                                                          ------------------------------------TID Number Tname VARCHAR2 (H)                                                                                        Idatedate DATE PHOTO Blobsql> ALTER TABLE test1 modify Tname varchar2 (40 ); The table has changed. Sql> desc test1 is the name empty? Type-------------------------------------------------------------------------------------------------------------                                                                                          --------------------------------------TID                          Number Tname                                                              VARCHAR2 (+) hidatedate                                                                                        DATE PHOTO blobsql> ALTER TABLE test1 drop column photo; the table has changed. Sql> desc test1 is the name empty? Type-------------------------------------------------------------------------------------------------------------                                                                                          --------------------------------------TID  Number Tname VARCHAR2 (40) Hidatedate datesql> ALTER TABLE test 1 rename column tname to username; the table has changed. sql> desc test1;                                 Name                                               Is it empty? Type-------------------------------------------------------------------------------------------------------------                                                                                          --------------------------------------TID  Number USERNAME VARCHAR2 (40) Hidatedate datesql> Host Clssql> --Delete Table sql> select * from tab;                                                                                                     Tname Tabtype Clusterid                                                                                                     ------------------------------ ------- ----------                                                                                                    DEPT TABLE              EMP TABLE                                                                                                                  BONUS TABLE                                                                                                                  Salgrade TABLE                          EMP20                                                                                                                  TABLE                                                                                                                  Testsavepoint TABLE                                                                                                                  Testdelete TABLE                                                  TEST1 TABLE                                                                EMP10 TABLE                      Empincome TABLE is selected Choose 10 lines. sql> drop table testdelete, table deleted. Sql>--oracle Recycle Bin sql>--View Recycle Bin sql> show recyclebinoriginal name RecycleBin name OBJECT TYPE DRO P Time----------------------------------- ------------------------------------------Testdele                                                                      TE bin$oy28blkesle/ljnupkzrmq==$0 TABLE 2012-10-23:15:42:09 Sql>-Emptying the Recycle Bin sql> purge RecycleBin 2; the Recycle Bin has been emptied. Sql>--Completely delete table sql> drop tablesTest1 purge; The table has been deleted. Sql> Show recyclebinsql> host clssql>-check constraint sql> CREATE TABLE Test2 2 (TID number, 3 tname varchar2 (2 0), 4 gender varchar2 (4) Check (gender in (' Male ', ' female '), 5 sal number check (Sal >0) 6), table created. sql> INSERT INTO test2 values (1, ' Tom ', ' Male ', 1000); 1 rows created. sql> INSERT INTO test2 values (1, ' Tom ', ' ah ', +); insert into test2 values (1, ' Tom ', ' Ah ', 1000) * error 1th: ORA-02290: Violation check Beam conditions (SCOTT. sys_c006031) sql> CREATE TABLE MyPerson (2 pid varchar2) constraint Myperson_pk primary key, 3 pname varchar2 (   4) constraint myperson_name NOT NULL, 4 gender VARCHAR2 (4) Constraint Myperson_gender check (gender in (' Male ', ' female '), 5 Email VARCHAR2 (+) constraint Myperson_email Uqiue, 6 deptno number constraint MYPERSON_FK references dept (DEPTNO) on DELETE CASCADE 7); Email VARCHAR2 (+) constraint Myperson_email uqiue, * error on line 5th: ORA-02253: Constraint description is not allowed here sql> Ed written to fi Le afiedt.buf 1 CREATE TABLE MyPerson (2 pid varchar2 (constraint) MYPERSON_PK primary key, 3 pname VARCHAR2 (4) constraint myperson_name NOT NULL, 4 gender VARCHAR2 (4 ) Constraint Myperson_gender Check (Gender in (' Male ', ' female ')), 5 email varchar2 (+) constraint Myperson_email UNIQUE, 6 D EPTNO number constraint MYPERSON_FK references dept (DEPTNO) on DELETE CASCADE 7*) sql>/table created. sql> INSERT into MyPerson values (' P001 ', ' Tom ', ' Male ', ' [email protected] ', 10), 1 rows created. sql> INSERT into MyPerson values (' P002 ', ' Tom ', ' Ah ', ' [email protected] ', ' ten '); INSERT into MyPerson values (' P002 ', ' Tom ', ' Ah ', ' [email protected] ', 10 * error on line 1th: ORA-02290: violation of CHECK constraint (SCOTT. Myperson_gender) sql> spool off