Oracle Database Basics-Build table syntax + operations

Grammar

1. Build a table

CREATE TABLE Table name (

Column name data type,

......

);

2, delete table:drop table name;

3. Add column:ALTER TABLE name Add (column name data type);

4. Modify column:ALTER TABLE name rename column name to column name;

5, modify the data type:ALTER TABLE table name modify column name data type;

6. Delete column:ALTER TABLE name drop column name;

7. Add comments

Add Table Comment:comment on table name is ' table comment;

Add field Comment:comment on column table name. Column name is ' column comment ';

8. Adding constraints

Add PRIMARY KEY constraint:ALTER TABLE name PRIMARY KEY (column name);

add UNIQUE constraint:ALTER TABLE name constraint constraint name unique (column name);

　　(the difference between a primary KEY constraint and a UNIQUE constraint: PRIMARY KEY constraint: Unique identity, cannot be empty.) Unique constraint: Unique identity, only one value is empty )

non-null constraint:ALTER TABLE name modify (column name constraints);

9. Insert data: INSERT INTO(column name,......) VALUES (data,......) ；

　　Note that the date function cannot be written directly in Oracle

insertion Time:to_date (' 2018-1-4 15:53:34 ', ' yyyy-mm-dd HH24:MI:SS ')

Insert Current time:sysdate

Here is an example of what I did, applied to the syntax above:

1 --Student Table2 Create TableStudent (3stu_idvarchar2(Ten)Primary Key,4Stu_namevarchar2(Ten) not NULL,5Stu_sexvarchar2(2) not NULL,6 stu_birthday Date,7class_id Number8 );9 --Add a table commentTenComment on TableStudent is 'Student Information Form'; One --Field Add Comment AComment on columnstudent.stu_id is 'study number (primary key)'; -Comment on columnStudent.stu_name is 'Student Name'; -Comment on columnStudent.stu_sex is 'Student Sex'; theComment on columnStudent.stu_birthday is 'Student's date of birth'; -Comment on columnstudent.class_id is 'students in the same class'; -  - --sclass Table + Create TableSclass ( -class_id Number Primary Key, +Class_namevarchar2(Ten) not NULL A ); atComment on TableSclass is 'Class Information Table'; -Comment on columnsclass.class_id is 'class Number'; -Comment on columnSclass.class_name is 'class name';  -  - --Add foreign Key - Alter TableStudentAdd constraintfk_class_idForeign Key(class_id)ReferencesSclass (class_id); in  - --Add Data to Insert  intoSclass (class_id, class_name)Values(1,'The 1401 of the meter should'); + Insert  intoSclass (class_id, class_name)Values(2,'Net-Count 1401'); - Insert  intoSclass (class_id, class_name)Values(3,'Software 1401'); the Insert  intoStudent (stu_id, Stu_name, Stu_sex, Stu_birthday, class_id)Values('A001','Zhang Shan','female', To_date ('1995-10-02','YYYY-MM-DD'),1) ; * Insert  intoStudent (stu_id, Stu_name, Stu_sex, Stu_birthday, class_id)Values('A002','li Si','female', To_date ('1995-10-02','YYYY-MM-DD'),1) ; $ Insert  intoStudent (stu_id, Stu_name, Stu_sex, Stu_birthday, class_id)Values('A003','Wang Wu','female', To_date ('1996-10-02','YYYY-MM-DD'),2) ;Panax Notoginseng Insert  intoStudent (stu_id, Stu_name, Stu_sex, Stu_birthday, class_id)Values('A004','Chao Liu','female', To_date ('1996-12-02','YYYY-MM-DD'),3) ; - Insert  intoStudent (stu_id, Stu_name, Stu_sex, Stu_birthday, class_id)Values('A005','Chao Liu','female', Sysdate,3) ;

Oracle Database Basics-Build table syntax + operations