Oracle Notes v. Create tables, constraints, views, indexes, sequences, synonyms, table spaces

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

1. Build a 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

    Without a constraint name:

    CREATE TABLE UserInfo (

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

       Name VARCHAR2 () NOT null,--non-null

       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, federated 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 the view

    Alter VIEW V$_dept_view compile;

    Tip: A view is typically a query or subquery for a table or multiple tables, which reduces the amount of code, but also increases the level of maintenance of the database view, such as: 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 an 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

    --Partition Table Index

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

    --Index partition

    Tip: When you create a primary KEY or UNIQUE key constraint for a table, the system also creates a constraint to that field, and also creates an index that takes up the database space, which improves efficiency when accessing and querying, but slows down when the table is modified;

5. Create a sequence

    Create sequence seq;

    Select Seq.nextval from dual;

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

    Create sequence Seqtab

    Start with and from 2

    Increment by 3-each plus 3

    nomaxvalue-No maximum value

    MinValue 1-Minimum value 1

    Nocycle-does not cycle

    nocache;--does not cache

    --Modify the sequence, cannot modify the starting value

    Alter sequence Seqtab

    MaxValue 1000;

6. Create synonyms

    A synonym, as the 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 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 TABLE space

    Create Tablespace myms

    DataFile ' c:\myMS.dbf '

    Size 1M

    Autoextend on;

    --scale table space--Modify table space size

    ALTER DATABASE

    DataFile ' c:\myMS.dbf '

    Resize 2M;

    --Extension tablespace--Add data file

    Alter Tablespace myms

    Add datafile ' c:\myMS_2.dbf '

    Size 1M;

    --Set the DBF file to grow 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 table spaces with no data

    Drop Tablespace hooms;

    --Delete table space with data

    Drop Tablespace hooms

    including contents;

Oracle Notes v. Create tables, constraints, views, indexes, sequences, synonyms, table spaces