Some basic syntax for Oracle Operations __oracle

ALTER TABLE UserInfo Add (msn varchar2 (20));

1, build the table

    CREATE TABLE UserInfo (

       ID Number (6),

       Name VARCHAR2 (20),

       Sex number (1),

       Age Number (3),

       Birthday date,

       Address VARCHAR2 (50),

       Email VARCHAR2 (25),

       Tel Number (11)

    );

2. Create a constraint

    With no constraint name:

    CREATE TABLE UserInfo (

       ID Number (6) primary key,--primary keys

       Name varchar2 NOT NULL,--non-empty

       Sex number (1),

       Age Number (3) default 18,

       Birthday date,

       Address VARCHAR2 (50),

       Email varchar2 Unique,--only

       Tel Number (11),

       Deptno Number (2) References Dept (DEPTNO)-FOREIGN key

    With constraint name:

    CREATE TABLE UserInfo (

           ID number (6) Constraint ID_PK primary key,

           Name VARCHAR2 constraint name_nn NOT NULL,

           Sex number (1),

           Age Number (3) default 18,

           Birthday date,

           Address VARCHAR2 (50),

           email varchar2 (constraint) email_uqe unique,

           Tel Number (11),

           Deptno number (2) Constraint Dept_deptno_ref references dept (DEPTNO)

    );

    Column mode:

    CREATE TABLE UserInfo (

           ID Number (6),

           Name VARCHAR2 (20),

           Sex number (1),

           Age Number (3) default 18,

           Birthday date,

           Address VARCHAR2 (50),

           Email VARCHAR2 (25),

           Tel Number (11),

           Deptno Number (2),

           Constraint ID_PK primary key (ID),--can also be more than two, joint primary key

           Constraint Dept_deptno_ref foreign KEY (DEPTNO) References Dept (DEPTNO),

           Constraint emial_name_uqe Unique (email, name)

    );

    Alter mode:

        ALTER TABLE UserInfo Add (msn varchar2 (20));

        ALTER TABLE UserInfo Modify (msn Varchar2 (25));

        ALTER TABLE UserInfo drop (MSN);

        ALTER TABLE userInfo drop constraint id_pk;

        ALTER TABLE USERINFO ADD constraint ID_PK primary key (ID);

3. Create a View

    CREATE TABLE V$_dept_view

    As

    Select Deptno, dname from dept;

    --Recompile view

    Alter VIEW V$_dept_view compile;

    Tip: A view is typically a query or subquery for a table or multiple tables. This reduces the amount of code, but at the same time increases the level of maintenance of the database view, such as when a table field is deleted or modified, the view is recreated or modified, and a portion of the database is occupied; The view is a virtual table;

4. CREATE index

    Normal index: CREATE index idx_dpt_dname on dept (Dname);

    Federated Index: CREATE index Idx_dept_dname_deptno on dept (Dname, Deptno);

    --Unique index

    Create unique index idx_emp_ename on scott.emp (ename);

    --Reverse Key index

    Create INDEX Idx_emp_rev_no on scott.emp (empno) reverse;

    --Bitmap Index

    Create bitmap index idx_emp_name on scott.emp (dname);

    --Index organization table, must have primary key

    CREATE Table Tab (

        ID int PRIMARY KEY,

        Name VARCHAR2 (20)

    )    organization Index;

    --The Insert efficiency of Index Organization table is very low

    --Partitioned Table index

    CREATE index idx_name on table (col) Local/global;

    --Index partitions

    Tip: When you create a primary key or a unique key constraint on a table, the system also creates a constraint to the field; The index also consumes the database space; the indexes are more efficient when accessing and querying, but the efficiency decreases when the table is modified;

5. Create sequence

    Create sequence seq;

    Select Seq.nextval from dual;

    Insert into tab values (Sql.nextval, ' music ');

    Create sequence Seqtab

    Start with 2– starting from 2

    Increment by 3-each time plus 3

    Nomaxvalue-has no maximum value

    MinValue 1-Minimum value 1

    Nocycle-does not cycle

    nocache;--Not Cached

    --modifies the sequence and cannot modify the starting value

    Alter sequence Seqtab

    MaxValue 1000;

6. Create synonyms

    A synonym, as its name implies, is an alias, or another name.

    Create synonym scott_emp for scott.emp;

    Create public synonym scott_dept for scott.dept;

    SELECT * from Scott_emp;

    SELECT * from Scott_dept;

7. Create a table space

    Create Tablespace Hooms

    DataFile ' E:\HooMS.dbf '

    Size 5M

    Autoextend on next 2M maxsize 10M;

    --Create user, assign can manipulate table space

    Create user Hoo

    Identified by Hoo

    Default Tablespace  hooms

    Temporary tablespace temp;

    --Create a table space

    Create Tablespace myms

    DataFile ' c:\myMS.dbf '

    Size 1M

    Autoextend on;

    --Extending table space--modifying table space size

    ALTER DATABASE

    DataFile ' c:\myMS.dbf '

    Resize 2M;

    --extending tablespace--Adding data files

    Alter Tablespace myms

    Add datafile ' c:\myMS_2.dbf '

    Size 1M;

    --Set up DBF file growth automatically

    DataFile ' c:\myMS_2.dbf '

    Autoextend on next 2M maxsize 4M;

    --Table Space renaming

    Alter Tablespace myms

    Rename to Hooms;

    --Detach table space (offline)

    Alter Tablespace Hooms

    Offline temporary;

    --Offline in archive mode

    Alter Tablespace Hooms

    Offline immediate;

    --Bring the table space online

    Alter tablespace hooms Online;

    --delete tablespace with no data

    Drop Tablespace hooms;

    --Delete table space with data

    Drop Tablespace hooms

    including contents;