Database Oracle Database Object one

Common Database object tables: A basic collection of data stores, composed of rows and columns: Logically related data collection sequences extracted from a table: Provides a regular numerical index: improve efficiency of queries synonyms: Aliases an object

CREATE TABLE Statement --CREATE TABLE permission --Storage space

data type varchar2 (size)    variable long character data (default 4kb)char(size)    fixed-length character data number (P,s)    Variable long numeric data date        Date type data          Long variable length character data up to 2Gclob        long  Raw    Raw binary data blob binary data        , up to 4Gbfile to        store the binary data of external files, up to 4Growid        line address ( the row address stored in the index )

-- Create a table t2 (Tid number,tname varchar2 (default sysdate--  Set the default value for this field )

ALTER TABLE statement Append, modify, or delete syntax - append SQL> ALTER TABLE t2 Add (sid2 number,sid3 number); -- Modify SQL> ALTER TABLE t2 Modify (tname varchar2(long); -- Delete column SQL> ALTER TABLE t2 drop column Sid2;

Delete a table 1 the data and structure are deleted. 2 all related transactions that are running are committed 3 all related indexes are deleted 4 . The DROP TABLE statement cannot be rolled back, but can be flashed back to the syntax structure: drop TABLE table name

under normal users, the table is deleted into the Recycle Bin (the administrator does not have a recycle Bin)- emptying the Recycle Bin purge recyclebin; -- completely delete a table test1 purge;

Constraints--constraints are table-level constraints--If there is a dependency, the constraint can prevent the erroneous deletion of the data--constraint type NOT NULL non-null unique uniqueness PRIMARY key primary key FOREIGN key foreign key check check create T Able Student (SID number constraint STUDENT_SID_PK primary key,--PRIMARY KEY constraint sname varchar2 ( +), email varchar2 ( +) notNULL,--This is created by the Oracle system constraint name sex number constraint Student_sex_notnull notNULL,--This is the custom constraint name, constraint is the keyword age number constraint student_age_notnull notNULLconstraint Student_age_unique Unique,--A field defines multiple constraints NUMX number constraint Student_age_check check (numx< -)--CHECK constraint deptno number constraint STUDENT_DEPTNO_FK references dept (DEPTNO)--FOREIGN KEY Constraints--references is a keyword that indicates that the Deptno field of the student table needs to refer to the Deptno field of the Dept table)

for CHECK constraints -- You can use the In keyword to create a table student (    sex varchar2('  male ',' female ' )))

for PRIMARY KEY constraints - The PRIMARY KEY constraint uniquely determines a row of values through this column --if you define a PRIMARY key constraint that implies a non-null and UNIQUE constraint

about foreign key FOREIGN KEY constraint -- define two tables, one table column value refers to the column value of another table the foreign key of the departmental table    child table of the employee table is associated with the parent table's primary key -the Data Quilt table reference in the parent table, the corresponding record of the parent table cannot be deleted    If you want to delete CASCADE delete CASCADE null --Insert data in child table, foreign key must exist in parent table or insert error

FOREIGN KEY constraint (cont.)foregin key: A table-level constraint references is defined in a child table: The column on the specified table and parent table on DELETE Cascade: When the parent table is deleted, Cascade deletes the child table records, which are not normally used, Because it destroys the child table data  setnull: Sets the foreign key value of the dependent record associated with the child table to a null value ( typically not used because it destroys the child table data   Setnull)

Database Oracle Database Object one