Oracle Database basics: Table creation syntax and operations, oracle Database

Oracle Database basics: Table creation syntax and operations, oracle Database
Syntax

1. Create a table

Create table Name (

Column name data type,

......

);

2. delete a table: drop table name;

3. add a column: alter table name add (column name data type );

4. Modify columns: alter table name rename column original column name to column name;

5. modify the Data Type: alter table name modify column name data type;

6. delete a 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. Add Constraints

Add a primary key constraint: alter table name primary key (column name );

Add a unique constraint: alter table name constraint name unique (column name );

(The difference between a primary key constraint and a unique constraint: The primary key constraint is a unique identifier and cannot be blank. Unique constraint: Unique Identifier. Only one value can be blank)

Non-empty constraint: alter table name modify (column name constraints );

9. insert data: insert into (column name ,......) Values (data ,......);

Note: Data functions cannot be written directly to oracle.

Insert time: to_date ('2017-1-4 15:53:34 ', 'yyyy-MM-DD HH24: MI: ss ')

Insert current time: sysdate

The following is an example I used to apply the above Syntax:

1 -- student table

2 create table student (

3 stu_id varchar2 (10) primary key,

4 stu_name varchar2 (10) not null,

5 stu_sex varchar2 (2) not null,

6 stu_birthday date,

7 class_id number

8 );

9 -- Add Table comment

10 comment on table student is 'student info table ';

11 -- add comments to Fields

12 comment on column student. stu_id is 'student ID (primary key )';

13 comment on column student. stu_name is 'student name ';

14 comment on column student. stu_sex is 'student gender ';

15 comment on column student. stu_birthday is 'student birthday ';

16 comment on column student. class_id is 'student's class ';

17

18 -- sclass table

19 create table sclass (

20 class_id number primary key,

21 class_name varchar2 (10) not null

22 );

23 comment on table sclass is 'Class info table ';

24 comment on column sclass. class_id is 'Class number ';

25 comment on column sclass. class_name is 'Class name ';

26

27 -- add a foreign key

28 alter table student add constraint fk_class_id foreign key (class_id) references sclass (class_id );

29

30 -- add data

31 insert into sclass (class_id, class_name) values (1, 'count 1401 ');

32 insert into sclass (class_id, class_name) values (2, 'network analytics 1401 ');

33 insert into sclass (class_id, class_name) values (3, 'Software 100 ');

34 insert into student (stu_id, stu_name, stu_sex, stu_birthday, class_id) values ('a001', 'zhangshan', 'female ', to_date ('2017-10-02 ', 'yyyy-mm-dd'), 1 );

35 insert into student (stu_id, stu_name, stu_sex, stu_birthday, class_id) values ('a002 ', 'LIST', 'female', to_date ('2017-10-02 ', 'yyyy-mm-dd'), 1 );

36 insert into student (stu_id, stu_name, stu_sex, stu_birthday, class_id) values ('a003 ', 'wangwu', 'female', to_date ('2017-10-02 ', 'yyyy-mm-dd'), 2 );

37 insert into student (stu_id, stu_name, stu_sex, stu_birthday, class_id) values ('a004 ', 'Liu Zhao', 'female ', to_date ('2017-12-02 ', 'yyyy-mm-dd'), 3 );

38 insert into student (stu_id, stu_name, stu_sex, stu_birthday, class_id) values ('a005 ', 'Liu Zhao', 'female ', sysdate, 3 );