Oracle creates and deletes databases, creates tablespaces, and inserts, deletes, and modifies tables. oracle creates

Oracle creates and deletes databases, creates tablespaces, and inserts, deletes, and modifies tables. oracle creates

1. Create and delete Databases

Oracle OraDb11g_home-> Configuration and porting tools-> Database configration Assistant->... then you can create or delete a Database.

2. Create a tablespace

Create tablespaceInspur_tablespace

Logging

Datafile 'd: \ MyOracleBACK SQL \ StudentDB \Inspur_tablespace. Dbf'

Size 50 m

Autoextend on

Next 50 m maxsize 20480 m

Extent management local;

3. Create a user and specify a tablespace

Create userUsernameIdentifiedPassword

Default tablespaceInspur_tablespace

[Temporary tablespaceThe temporary tablespace specified in the user_temp square brackets can be unavailable ];

 

4. Assign permissions to users

Grant connect, resource, dbaUsername;

5. Create a table and specify the primary key and foreign key

Specify the primary key and foreign key when creating a table
Create table T_STU (
STU_ID char (5) not null,
STU_NAME varchar2 (8) not null,
Constraint PK_T_STU primary key (STU_ID)
);

// Add remarks to the table
Comment on table OT_STUIs 'this table is the student info table'

// Add remarks to the column
Comment on colummOT_STU.STU_IDIs 'student id'
// View the column remarks. The table name and column name are required at this time.
Select * fromTable OT_STUWhere TABLE_NAME ='Table OT_STUAnd column_name ='STU_ID'

The primary key is created with the foreign key:
Create table T_SCORE (
EXAM_SCORE number (5, 2 ),
EXAM_DATE date,
AUTOID number (10) not null,
STU_ID char (5 ),
SUB_ID char (3 ),
Constraint PK_T_SCORE primary key (AUTOID ),
Constraint FK_T_SCORE_REFE foreign key (STU_ID)
References T_STU (STU_ID)
)

 

6. modify a table

(1) Add a field

Alter table tablename1 add (columname number (2 ));

(2) modify the length of a field

Alter table tablename1 modify (columname1 varchar2 (30 ));

(3) modify the field name or type (numbers are not allowed)

Alter table tablename1 modify (columname1 char (20 ));

(4) Delete A Field

Alter table tablename1 drop column columname1;

(5) modify the table name

Rename tablename1 to tablename2;

(6) Delete a table

Drop table tablename2;

(7) Add a primary key

Alter table GUM_POLICE_INFO2
Add primary key (ID );

VII. Common insert statements

Insert into Table Name (field name is attribute) values (the value of the attribute you want to Insert );
The query statement is the same as that of other databases.
Note the following when inserting data:
Inser into tablename values ('26-January 1, December-1989 '); this is required; otherwise, an error occurs.
You can modify the date format:
Alter session set nls_date_format = 'yyyy-mm-dd ';
Insert into tablename values ('2017-12-26 ');
Follow Han shunping's eighth lecture tomorrow