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