Oracle Basic Operations

1. Create a table

Create Tabletest01 (IDint  not NULL Primary Key, namevarchar(8) not NULL, Gendervarchar2(2) not NULL, Ageint  not NULL, Addressvarchar2( -)default' Address Unknown ' not NULL, Regdata date);

Constraints

nonempty constraint NOT NULL

PRIMARY KEY constraint primary key

FOREIGN KEY constraints

Uniquely constrained unique

Check constraint check

Federated primary Key   constraint Primary Key (id,username); View data dictionary   desc user_constraint renaming  when modifying a table constraint  to b;--Modify Table Delete constraint--disable constraint
constraint constraint name; Delete constraint
DROP constraint constraint name;
Drop primary key; Delete primary key directly

FOREIGN KEY ConstraintsCreate TableTypeInfo (typeidvarchar2( -)Primary Key, TypeNamevarchar2( -));Create TableUserinfo_f (IDvarchar2(Ten)Primary Key, usernamevarchar2( -), Typeid_newvarchar2(Ten)ReferencesTypeInfo (typeid));Insert  intoTypeInfoValues(1,1); Set foreign KEY constraints when creating tablesconstraintname ForeginCreate TableUSERINFO_F2 (IDvarchar2( -)Primary Key, usernamevarchar2( -), Typeid_newvarchar2(Ten),constraintFk_typeid_newForeign Key(typeid_new)ReferencesTypeInfo (typeid));Create TableUSERINFO_F3 (IDvarchar2( -)Primary Key, usernamevarchar2( -), Typeid_newvarchar2(Ten),constraintFk_typeid_new1Foreign Key(typeid_new)ReferencesTypeInfo (typeID) on Delete CascadeThe foreign KEY constraint contains the delete foreign KEY constraint disable constraint disableconstraintconstraint name; Delete constraintDrop constraintconstraint name; Unique constraint differs from PRIMARY key A unique constraint can have multiple, only one nullCreate TableUserinfo_u (IDvarchar2( -)Primary Key, usernamevarchar2( -)Unique, Userpwdvarchar2( -) ; add a constraint when creating a tableconstraintConstraint nameUnique(column name); add a unique constraint when modifying a tableAdd constraintConstraint nameUnique(column name); Check ConstraintCreate TableUserinfo_c (IDvarchar2( -)Primary Key, usernamevarchar2( -), Salary Number(5,0)Check(Salary> -));constraintCk_salaryCheck(Salary> -);/*Get table:*/Selecttable_name fromUser_tables;//the current user's tableSelecttable_name fromAll_tables;//All users ' tablesSelecttable_name fromDba_tables;//including system tablesSelecttable_name fromDba_tableswhereOwner=' Zfxfzb '/*

2. Modify the table

Alter Tabletest01Add constraints_idPrimary Key;Alter Tabletest01Add constraintCk_infos_genderCheck(Gender=MaleorGender=' female ')Alter Tabletest01Add constraintCk_infos_age (age>=0  andAge<= -)Alter TableTable name Modify field namedefaultDefault value;//Change the field typeAlter TableTable nameAddColumn Name field type;//Add Field typeAlter TableTable nameDrop columnField name;//Delete Field namesAlter TableTable name RenamecolumnColumn Name toColumn Name//Modify field name Rename table name toTable name//Modify Table Name

3. Delete a table

truncate Table // Delete all data in a table much faster than delete, TRUNCATE TABLE Delete  from  Table conditions //droptables// Delete form

4. Insert statement

Insert  into Values (value 1, value 2);

5. Modifying statements

Update set Field =[ Modify Condition ]updateset=where = :user

6. Query statements

query    with conditions where Fuzzy query      like % _ Range Query     inch sort    The results of a query Order  by desc || ASC

7.case when

SelectUsername CaseUsername when' AAA ' Then' Computer Department ' when' BBB ' Then' Marketing Department 'Else' Other departments 'End  asDepartment fromusers;SelectUsername CaseUsername=' AAA ' Then' Computer Department ' whenUsername=' BBB ' Then' Marketing Department 'Else' Other departments ' asDepartment fromUsers

8. Operators and expressions

Arithmetic operators and comparison operators

Distinct removing excess rows

Column can set aliases for fields such as column column_name heading new_name

The use of the Decode function is similar to Case...when

Select Username,decode (username, ' aaa ', ' computer Department ', ' BBB ', ' marketing department ', ' other ') as department from users;

9. Copying a table

Create table table name as a query result//Copy query result

Insert into table name value a query result//add-in Query

10. View Table Space

Desc test01;

11. Create a table space

Permanent Table Space Create tablespace test1_tablespace datafile ' testfile.dbf ' size 10m; temp tablespace Create Temporary tablespace temptest1_tablespace tempfile ' tempfile.dbf ' size 10m; desc Dba_data_files; Select file_name  from where tablespace_name=' Test1_tablespace ';

Oracle Basic Operations