Oracle Database Basic Operations (i)

First, the basic type of data

　　1. Pseudo-Columns of Oracle

　　　　A pseudo-column in Oracle is like a table column, but it is not stored in a table where pseudo-columns can be queried from a table, but cannot be inserted, updated, and deleted by their values the commonly used pseudo-columns are rowid and rownum;

ROWID is the storage address for rows in a table that uniquely identifies a row in the database, and can be used to quickly locate a row in a table using the ROWID pseudo-column

ROWNUM is the ordinal of the row in the result set returned by the query, which can be used to limit the number of rows returned by the query;

Select  from goods;

　　2. Data definition language (DDL)

Create----ALTER----DROP----truncate

　　3. Data manipulation Language (DML)

Insert---Select---delete---update

　4. Transaction control Language (TCL)

　　Commit---savepoint---rollback

　　5. Data Control Language (DCL)

Grant---Revoke

II. Basic Overview of Oracle Database

　　1.The maximum length of the table name is 30 characters;

2, the same user mode, different tables can not have the same name;

3, the Oracle database table name, column name, user name and other object names, not case-sensitive, the system will automatically turn into uppercase;

Third, the basic operation of the database

　　 1. Select rows with no Duplicates

Distinct sentence screen except for all the same rows in the result set, keep only one row;

　　2. Select command with condition and sort (similar to MySQL)

Select  from where stuage>orderbyASCdesc;

　　3. Adding Oracle database column aliases is similar to MySQL

Create Table  as Select *  from Student

　　4. You can also copy only the table structure and not copy the data

Create Table  as Select *  from where 1 = 2

Third, DML language operations

　　1. View the number of rows in a table　

-- --Low efficiency Select Count (* from student----High efficiency SelectCount(1  from Student

2, take out the table in the corresponding column does not exist duplicate data record　

Select  from Group  by  having (Count(stuname| | Stuage)<2)

Iv. language of control of Things (TCL)

　　1, Commit: Submit things;

2, Rollback: roll back things;

3. SavePoint: Creating a storage point in a thing

V. Language of Data Control　　

　　1. Create a table

--Oracle has no self-incrementso create a sequence that inserts the sequence into the defined primary key when inserting the dataCreatesequence stu_id;Create TableStudent (ID Number Primary Key, namevarchar2( -), age Number      );Insert  intoStudent (Stu_id.nextval,'Zhang San',Ten);

　　2. Add constraint (same as MySQL)

　　3. Adding columns to the table　

Alter Table Add varchar2 (varchar2());

　　4. Delete Columns

Alter Table Drop column name;

　　5. Modify a field

Alter Table varchar2 (default ' unknown ');

　　6. Paging　

Select *  from (Select A.*from(Select* from)  where x>2 and x<=4

Oracle Database Basic Operations (i)