Basic SQL operations in Oracle (1)

-- Create permanent tablespace
Create tablespace "58 SPACE"
LOGGING
DATAFILE 'd: \ ORACLE \ ORADATA \ MYDB \ 57SPACE. ora 'size 5 M EXTENT
MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
-- Create temporary tablespace
CREATE
Temporary tablespace "58 TMPSPACE" TEMPFILE
'D: \ ORACLE \ ORADATA \ MYDB \ 58TMPSPACE. ora 'size 5 M EXTENT MANAGEMENT
Local uniform size 2 M

-- Create a user
Create user "58 USER"
Identified by "123456" -- specify the password
Default tablespace "58 SPACE" -- assign permanent TABLESPACE
Temporary tablespace "58 TMPSPACE" -- allocate TEMPORARY TABLESPACE
Quota unlimited on "58 SPACE"; -- assign the permission to use the tablespace
-- SQL COMMAND
-- The user logs on to the database from the client
CONN 58 USER/123456 @ MYDB;
CONN system/system @ MYDB;
-- Create a role
Create role "58 ROLE ";
Create role "TESTROLE ";
-- Authorization
Grant alter any table to "58 ROLE ";
Grant alter any table to "58 ROLE" with admin option;
Revoke alter any table from "58 ROLE ";
Grant connect to "58 USER" with admin option;
Revoke connect from "58 USER ";
Grant resource to "58 USER ";
Grant connect to "TESTROLE ";
 
 
-- Create permanent tablespace
Create tablespace "62 SPACE"
LOGGING
DATAFILE 'd: \ ORACLE \ ORADATA \ MYDB \ 62SPACE. ora 'size 5 M EXTENT
MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
-- Create temporary tablespace
CREATE
Temporary tablespace "62 TMPSPACE" TEMPFILE
'D: \ ORACLE \ ORADATA \ MYDB \ 62TMPSPACE. ora 'size 5 M EXTENT MANAGEMENT
Local uniform size 2 M

-- Create a user
Create user "62 USER"
Identified by "123456" -- specify the password
Default tablespace "62 SPACE" -- assign permanent TABLESPACE
Temporary tablespace "62 TMPSPACE" -- allocate TEMPORARY TABLESPACE
Quota unlimited on "62 SPACE"; -- assign the permission to use the tablespace
-- SQL COMMAND
-- The user logs on to the database from the client
CONNECT 62 USER/123456 @ MYDB;
CONN system/system @ MYDB;
-- Create a role
Create role "62 ROLE ";
Create role "TESTROLE ";
-- Authorization
Grant alter any table to "62 ROLE ";
Grant alter any table to "62 ROLE" with admin option;
Revoke alter any table from "62 ROLE ";
Grant connect to "62 USER" with admin option;
Grant connect to "58 USER ";
-- Revoke permissions or roles
Revoke connect from "62 USER ";
Grant resource to "62 USER ";
Grant connect to "TESTROLE ";
-- View System View
SELECT * FROM USER_TABLESPACES;
SELECT * FROM USER_OBJECTS;
SELECT * FROM USER_CONSTRAINTS;
-- Create a table
Create table student (
Id char (10 ),
NAME VARCHAR2 (40) not null,
Sex char (1) CONSTRAINTS STU_SEX_NN not null,
Birthday date,
CONSTRAINTS STU_ID_PK primary key (ID)
);

Create table DEGREEdd (
Id char (10 ),
Cid char (4 ),
Score number (4, 1)
);
 
-- Delete a table
Drop table degree;
-- Add Constraints
ALTER TABLE DEGREE
Add constraints DEG_IDCID_PK primary key (ID, CID );
-- Foreign key constraint
ALTER TABLE DEGREE
Add constraints DEG_ID_FK foreign key (ID) references student (ID );
-- CHECK Constraints
ALTER TABLE DEGREE
Add constraints DEG_ SC _C CHECK (SCORE> = 60 );
 
-- UNIQUE
ALTER TABLE DEGREE
Add constraints DEG_CID_UK UNIQUE (CID );
-- Add NOT NULL
ALTER TABLE DEGREE
Modify cid not null;
 
ALTER TABLE DEGREE
Modify cid constraints DEG_CID_NN not null;
-- Delete Constraints
ALTER TABLE DEGREE
Drop constraints SYS_C003040;
 
-- Add a column
ALTER TABLE STUDENT
Add roomid char (4 );
 
-- SQL COMMAND
Desc student;
-- Delete a column
ALTER TABLE STUDENT
Drop column roomid;
 
-- Modify the column type
ALTER TABLE DEGREE
Modify cid VARCHAR2 (4 );
 
-- Enable/disable Constraints
ALTER TABLE DEGREE
Enable constraints DEG_CID_NN;
ALTER TABLE DEGREE
Disable constraints DEG_CID_NN;
 
-- Modify column name
ALTER TABLE DEGREE
Rename column cid to course;
-- Modify the constraint name
ALTER TABLE DEGREE
Rename constraints DEG_CID_NN TO DEG_COU_NN;
 
-- Modify Table Name
Rename student to stu;
-- Truncate the table and delete the data while releasing the memory space occupied by the table
Truncate table stu;
 
This article is from the "Enthusiasm 10 years" blog