Oracle Create, delete databases, create tablespaces, and insert Delete modification tables

First, create, delete database

Oracle oradb11g_home-> Configuration and porting tools->database configration Assistant-> You can then create or delete the database

Ii. establishing a table space

Create Tablespace inspur_tablespace

Logging

DataFile ' D:\MyOracleBACK sql\studentdb\inspur_tablespace. dbf '

Size 50m

Autoextend on

Next 50m maxsize 20480m

Extent management Local;

Iii. creating a user and specifying a tablespace

Create user username identified by password

Default tablespace inspur_tablespace

"Temporary tablespace user_temp square brackets Specifies that the temporary table space is optional";

Iv. Assigning permissions to users

Grant CONNECT,RESOURCE,DBA to username;

V. Creating tables and specifying primary and foreign keys

Specify primary key and foreign key when creating 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 notes to a table
Comment on table Ot_stu is ' This table is Student information table '

Add a comment to a column
Comment on ColummOt_stu .stu_idIs ' Student ID '
View column notes this time to label the table name and the column name
SELECT * FROMtable Ot_stu where Table_name= 'table Ot_stu ' and Column_name= 'stu_id’

The primary key and the foreign key are established together:
CREATE TABLE T_score (
Exam_score number (5,2),
Exam_date DATE,
Autoid Number (TEN) is 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)
)

Vi. Modification of the table

(1) Add a field

ALTER TABLE TABLENAME1 Add (columname number (2));

(2) Modify the length of the field

ALTER TABLE tablename1 Modify (columname1 varchar2 (30));

(3) Change the name or type of the field (no numbers)

ALTER TABLE tablename1 Modify (columname1 char (20));

(4) Delete a field

ALTER TABLE tablename1 drop column columname1;

(5) Modify the name of the table

Rename tablename1 to Tablename2;

(6) Delete a table

drop table tablename2;

(7) Adding a primary key

ALTER TABLE Gum_police_info2
Add primary key (ID);

VII. Common INSERT Statements

Query statements are the same as other databases

Oracle Create, delete databases, create tablespaces, and insert Delete modification tables