Oracle Database Small Summary

Small summary OF---database---1. Basic common data types for databases

①VARCHAR2 (size)//Floating length character type: Length will change, according to user input value to change the corresponding length, save memory space

②char (size)//fixed character length, if the user entered a worthy length, not enough size, the system by default with a space instead of

③number//numeric type, containing decimals and integers (1. The total length/number of digits representing the number 2. Indicates the length/number of digits after the decimal point)

④date//Time type Sysdate (System time) to_date (' Input time ', ' time format ') time format General YYYY-MM-DD

2.DDL Data Definition Language

2.1 Creating a Table

Create Tabletest1 (Test2varchar2( -)Primary Key,//primary Key primary key (one table can only have one, and cannot be duplicated and empty) Test3 Number(5,2) not NULL,//not null cannot be NULL NULL can be NULL (default constraint is null) test4Char(Ten) not NULL, Test5 datedefault(sysdate)//default add default constraints);

2.2 Add to table, modify, delete

Alter TableTest1Add(test2varchar2( -) not NULL); Where test1 is the table name Test2 is the added column name, NOT NULL is the constraintAlter TableTest1 Modify (Test2 vatchar2 ( -) not NULL); where VARCHAR2 (20) is a modified property NOT NULL constraint based on previous write not write appropriate addAlter TableTest1Drop(TEST2); Test2 column names that are removed for test1Drop Tabletest1//delete table directly test1

2.3 Adding constraints

 alter  table  test add  constraint  fk_test1 foreign  key   (test1) refenerces test3 (test1)// Add a FOREIGN KEY constraint, test3 the Test1 column in the table as its primary key, test1 the foreign key to be added in the test table  alter  table  test add  constraint                                   ck_test1 check   (conditional) Add a check constraint, assign a value when the constraint  alter  table  test add                                constraint  un_test1 unique  (test1) A unique constraint, which cannot be duplicated, can have an empty value of 

3.DML Language Data Manipulation language

Insert  intoTest1 (Test2,3,4)Values(2,3,4)//Assign a value to the table, test1 as the table name, test2,3,4 the value to add for the column name (do not write the default order for the column when the table was created) 2,3,4 to the specific valuesUpdateTestSetTest2=XxwhereTest2=xxx; Modify (update) the data in the test table to change the value of the qualifying Test2 example to XX (no write condition will modify the entire Test2 column value to XX by default)DeleteTest1 fromTestwhereTest1=XX//Ibid. delete the value of the Test1 column in the test table that meets the criteria (by default, all values of the Test1 column are removed)

Oracle Database Small Summary