Oracle Learning Summary 3-Basic objects

(a) Table

1. Table

To create a table:

Create Table  Number varchar2 ());

--Add a new column:

Alter Table add photo blob;

--Modify Columns

Alter Table varchar2 (a);

--Delete Columns

Alter Table Drop column photo;

--Rename Columns

Alter Table column  to username;

--Renaming a table

 to Test2;

--Delete Table

Drop table test2;

--View the Recycle Bin (can be queried by the name of the table in the Recycle Bin, not really deleted)

Show RecycleBin;

--Emptying the Recycle Bin

Purge RecycleBin;

--Note: The administrator does not have a recycle Bin

Line address: (Pseudo-column) rowid-------aaamfpaaeaaaaagaaa (Such values)

Select  from EMP;

To create a table from a query result:

Create a table: Save employees in Department number 20th

Create Table  as Select *  from where deptno=;

Create a table: Employee number name monthly salary Department name

Create Table Empinfo  as Select e.empno,e.ename,e.sal,e.sal*  annsal,d.dname    from  EMP e,dept d   where e.deptno=D.deptno

2, the constraints of the table:

Primary key, non-null, unique, check, foreign key (all included)

Create TableStudent (Sid Number constraintStudent_pkPrimary Key, Snamevarchar2( -)constraintStudent_name_notnull not NULL, Gendervarchar2(2)constraintStudent_gender_checkCheck(Genderinch('male','female') ), emailvarchar2( -)constraintStudent_email_uniqueUnique             constraintStudent_email_notnull not NULL, Deptno Number constraintStudent_fkReferencesDept (DEPTNO) on Delete Set NULL)

(ii) View

1. The administrator user gives permission to create the view:

Grant Create View  to Scott;

2. Create a View

Create or Replace View Empinfoview  as Select e.empno,e.ename,e.sal,e.sal*  annsal,d.dname from emp E , Dept Dwhere e.deptno=d.deptnowithreadonly 

(iii) Sequence

To create a sequence:

Create sequence myseq;

The initial value is 0.

Select  from Dual. Select  from dual. (just created cannot execute this sentence)

(iv) Index

Build an index

Create Index  on EMP (DEPTNO);

SQL Execution Plan:--you can see a decrease in CPU usage after indexing

Plan  for Select *  from where deptno=ten; Select *  from Table (Dbms_xplan.display);

(v) Synonyms

Administrator grants Scott users the ability to view employees tables for HR users

Grant Select  on  to Scott;

Administrator authorization to create synonyms:

Grant Create  to Scott;

To create a synonym:

Create  for hr.employees; Select *  from Hremp;

Oracle Learning Summary 3-Basic objects