Oracle database Basic Operations-data definition language

Familiar with Oracle machine environment and Oracle client configuration; Master and use the DDL language to establish, modify, and delete database tables, primary keys, FOREIGN KEY constraint relationships, and indexes.

1. (Build database table) set up four database tables for the teaching database, where the student table does not contain the Ssex (c,2) field, Sname fields are Sname (c,8) and can be empty.

   Create TableStudent (SnoChar(5), snamevarchar2(8), Sage Number(2)Check(agebetween  A  and  -), SdeptChar(2), SclassChar(2),    constraintStudent_pkPrimary Key(Sno));

   Create Table Course    Char (3notnullprimarykey,    Char(16  ),    number (3)); 

   Create TableScore (SnoChar(5), CNOChar(3), Grade Number(3),    constraintScore_pkPrimary Key(SNO,CNO),constraintS_sno_fkForeign Key(SNO)ReferencesStudent (Sno),constraintS_cno_fkForeign Key(CNO)ReferencesCourse (CNO),constraintScore_ck1Check(Grade>= 0  andGrade<=  -));

   Create TableTeach (TnameChar(8) not NULL, TsexChar(2) not NULL, CNOChar(3) not NULL, Tdate date not NULL, TdeptChar(2) not NULL,    constraintTeach_pkPrimary Key(CNO),constraintTeach_cno_fkForeign Key(CNO)ReferencesCourse (CNO));

3. (Modify database table) adds the sex (c,2) field to the student table.
   

   Alter Table Student Add Char (2));

5. (Modify the column name) to modify the column name in the student table to Ssex.
   

   Alter Table  column to Ssex;
   Modify (Ssex char (2) Check (Ssex in (' Male ', ' female ')));

7. (Modify database table) modifies the Sname field in the Student table to Sname (c,10) and is not empty.

   Alter Table  Char(ten)notnull);

9. (indexed) index The score table by course number ascending, fractional descending, and index named Sc_grade.

   Create Index  on desc);

11. Delete Index Sc_grade (delete index).

    Drop index Sc_grade;

13. (Build database table)   Build database table S1 (SNO,SNAME,SD,SA), whose field type definition is the same as the data type definition for the corresponding field (Sno,sname,sdept,sage) in the student table.

     create  table  S1 (
     Sno char  (5 ) not  null , 
     sname char  (10 ) not  null  Span style= "COLOR: #0000ff" >unique , 
     SD char  (2 ), sa number  (2 )"); 

15. (Modify database table)   Delete the referential integrity constraint relationship for the score of the score table.

     alter  table  score drop  constraint  S_SNO_FK; 
     alter  table  score drop  constraint  s_cno_fk; 

17. Add a referential integrity constraint to the score table score (modify the database table).

    Alter Table  Add(constraintforeignkeyreferences  student (Sno), constraint s_cno_fk  foreignkeyreferences course (CNO));

19. (Modify the database table name) Rename the database table S1 to student_temp.

     to Student_temp;

21. View constraints on a table

    Select constraint_name, TABLE_NAME, R_owner, R_constraint_name  from all_constraints where = ' score ';

23. To delete a column in a table

    Alter Table Student Drop column spec;

25. View all the table names under the user

    Select  from User_tables;  //  Select from All_tables;  //  Select from Dba_tables;  //  Select*from// can query all user table indexes

27. View all column names in a table

29. Select  from WHERE table_name='STUDENT'; // table name must be uppercase
    Or
    desc student;

　　When you define a FOREIGN key constraint, you cannot define a property that is not in the other table in the foreign key of the table, or you will get an error.

In the construction of the table, because the name of the constraint is repeatedly defined, resulting in the table is not built: Improved method, the textbook constraints on the name of the home table name prefix, to avoid the repetition of the definition of the problem;

Often missing semicolons, resulting in a messy format in cmd;

When you set the type of a property, you cannot change its property to null if it is set to NOT null;

In Oracle, the Kanji account is three bytes, so the name appears four words out of range, need to modify its scope, gender should be modified to 3 bytes;

The name can not be set to NOT null unique, if there is a duplicate of the error will occur, because at that time the unique set of the last to rebuild the table, delete this constraint;

There are two ways to delete table data: Delete and truncate.

The use of delete is as follows:

Delete from < table name > [Where Condition]

The usage of truncate is as follows:

TRUNCATE TABLE < name >

The difference between delete and truncate is as follows:

1. Delete can delete one or more data from a table, or delete all data, and truncate can only delete all the data in the table.

2. After delete deletes the table data, the identity field cannot be reused. In other words, if you delete the row of id=10 (if the ID is an identity field), you may not be able to insert a piece of data for id=10.

3. After truncate deletes the table data, the identity resumes its initial state. The default is the initial value of 1, that is, after truncate, insert one more data, id=1.

However, when you use truncate to delete data, you may be restricted by the foreign key, and you must delete it from the subset before you can delete the data.

Reference: http://www.cnblogs.com/laipDIDI/articles/2615210.html
Http://www.jb51.net/article/82660.htm

Oracle database Basic Operations-data definition language