Oracle Prerequisites for Getting Started

、、、、、、、、、、、、、、、、 create tablespace \ Assign role \ create data table \ Insert data \ Create sequence \ Add Comment 、、、、、、、、、、、、、、、、、、、、、、、、、、、

--Create TABLE space
Create Tablespace New_tabspace
DataFile ' E:\File_Orc\File\A.DBF '
Size 100m

Create tablespace haha
DataFile ' E:\File_Orc\File\B.DBF '
Size 50m


--Delete the space and

Delete physical files
Drop tablespace haha including contents and datafiles

--Create User
Create User Zhangsan
Identified by 123
Default Tablespace new_tabspace


--Giving permissions to users
Grant Connect,resource to Zhangsan
Grant DBA to Zhangsan


--Create a data table
--Master Table
CREATE TABLE Master (
ID Number (5) Not null primary key,
Name NVARCHAR2 (NOT NULL)
)

--Inserting data
INSERT INTO master values (1, ' AA ')
INSERT INTO master values (2, ' BB ')

SELECT * FROM Master

--Delete the inserted data
Delete from Master


--Create a sequence
Create sequence Master_seq
Start with 1-starting from 1
Increment by 1--1 increase each time
Nomaxvalue--No maximum value
Cache 10-10 increase each time


--Inserting data
INSERT into master values (Master_seq.nextval, ' Zhang San ')
INSERT into master values (Master_seq.nextval, ' John Doe ')


--View the current and next values of the sequence
Select Master_seq.currval from dual
Select Master_seq.nextval from dual


--Add comments to the master table
Comment on table master is ' pet '
Comment on column master.id is ' master ID '

、、、、、、、、、、、、、、、、、、、 add constraints to the table 、、、、、、、、、、、、、、、

There are several major categories of Oracle-built constraints:
Nn:not NULL non-null constraint
Uk:unique KEY UNIQUE Constraint
Pk:primary Key PRIMARY KEY constraint
Fk:foreign key FOREIGN KEY constraint
Ck:check conditional constraints

Adding constraints when creating a table

CREATE TABLE Pet (

ID number primary KEY,--PRIMARY KEY constraint

Usrername nvarchar2 () not NULL,--Non-null constraint

Email VARCHAR2 (a) unique, unique constraint

Sal Number (5) Check (sal>1500),--verification constraints

Status char (1) Default 1 NOT NULL,--check constraint

MASTER_ID Number (5) References Pet_type (ID)-FOREIGN KEY constraint

)

--some other related operations

--Modifying a constraint on a field in a data table (as a unique constraint)

ALTER TABLE PET Add constraint Un_name unique (username)

--Add columns

ALTER TABLE name add new column column list data type [default 0 NOT NULL] (add column defaults to 0)

--Delete Columns
ALTER TABLE name drop column name

--Modify Columns
ALTER TABLE name ALTER COLUMN name the newly added data type (Modify columns)

Oracle Prerequisites for Getting Started