Master the basic Oracle syntax quickly, let you come and go freely

This article, let's continue the previous installation, explain the basic syntax of Oracle, so that you can operate and master the SQL statement, say nothing more, load the goods!!!!!!


Before we talk about Oracle syntax, let's understand ↓ ↓ ↓ ↓ ↓ ↓

1, The difference between Oracle and mysql:

  • MySQL: user name > Database > table



  • Oracle: database user name table



2, Database operations

1. Overview

  • Oracle tables associated with users

  • Each user needs to determine the table storage location, which is called table space.

  • There are two kinds of table spaces in Oracle: permanent table space and temporary table space

    • Temporary table space: when using SQL statement operation, the result enters the temporary table space first

    • Persistent tablespace: when commit is executed, the data will move from the temporary tablespace to the persistent tablespace.


2. Create tablespace (permanent)

Basic syntax create tablespace tablespace name
datafile 'tablespace save location'
Size is used to set the initial size of the tablespace
autoextend on; used to set automatic growth. If the storage capacity exceeds the initial size, automatic expansion will be started


create tablespace test1
datafile 'G:\JAVA\tools\oracle\space\test1.dbf'
size 10m
autoextend on;   --Automatic space size enhancement


3. Create a temporary table space


  • Note that there are two differences between temporary and tempfile creation

--Create a temporary tablespace
create temporary tablespace
tempfile 'temporary tablespace location'
size 10m
autoextend on;



--Create a temporary tablespace
create temporary tablespace test1temp
tempfile 'G:\JAVA\tools\oracle\space\testtemp.dbf'
size 10m
autoextend on;


4. Create user


--Create user
create user user name
identified by password
default tablespace
temporary tablespace
Quota size on tablespace; -- the quota size currently used in the specified tablespace



create user czxy1 -- user name
Identified by czxy1 -- password
default tablespace test1 -- tablespace
temporary tablespace test1temp -- temporary tablespace
quota 50m on test1; -- quota size currently used in the specified tablespace


Then log in with this account password


However, there may be a small problem, unable to log in,

Unable to log in through PL/SQL using czxy1, indicating no permission. As shown in the following figure


Don't panic at this time, Huang sir has already prepared for you

Give users all rights (dba)
--grant dba to user name;
grant dba to czxy002;

Run the SQL statement to improve permissions and log in

Successful display:



5. User empowerment (understanding)




grant create session to czxy1;




grant select on hr.employees to czxy001;




6. User role RBAC (understanding)










grant dba to czxy1;

This is to give users DBA permission and then log in



7. Table operation

(1) Create table

Syntax 1: create a new table

create table table name(
Field name type [constraint],
Field name 2 type [constraint],

For example:

create table book (
bid int primary key,
title varchar2(50)


Syntax 2: copy a table (structure + data)

create table table name as query statement;

For example:

select * from hr.employees;
create table emp as select * from hr.employees;






create table student(
  stuid int,
  stuname varchar2(20),
  sex char(1),
  age number(3,0),
  birthday date,
  email varchar2(20),
  phone char(11)



(2) Modification table (understanding)

Specific operation data of alter table name;
alter table table name add (column description,...);
alter table table name modify (column description,...);
--Delete column
alter table table name drop column column name;


--1.Add field
alter table  student add(
remark varchar2(20),
order_no int

--2.Modify field
alter table student modify(
 remark nvarchar2(50),
 order_no varchar2(50)

--Delete field
alter table student drop column order_no;




(3) Delete table

drop table table name


--Delete table
drop table student2;



Common constraints



(1) Primary key constraint

Add constraint

-- Primary key constraint 0
-- Method 1: add constraints when creating tables and declaring fields (Only one field can be specified as the primary key)
create table pk01(
  pid int primary key,
  name varchar2(20)
-- Method 2: create a table, declare the fields, add constraints (you can set a union primary key) [recommended]
create table pk02(
  pid int,
  name varchar2(20),
  constraint pk02_pid primary key (pid)
-- Method 3: after creation, modify the table structure and add the constraint recommend
create table pk03(
  pid int,
  name varchar2(20)
alter table pk03 add constraint pk03_pid primary key (pid);


Delete constraint

-- Delete primary key
alter table pk03 drop primary key;


(2) Primary key validation

insert into pk01(pid,name) values(1,'jack');  -- Duplicate execution not allowed
insert into pk01(name) values('jack');        -- Not allowed null



(3) Unique, non empty, default

### 2.3.4 unique, non empty, default

* Unique constraint, add constraint method and`Primary key constraint`Consistent, i.e. three ways

  -- Unique constraint (third)
  create table un01(
    pid int,
    name varchar2(20)
  alter table un01 add constraint un01_name unique(name);


Unique verification

insert into un01(pid,name) values(1,'jack');  -- Duplicate execution not allowed
insert into un01(name) values('rose');        -- Can be executed



(4) Non empty constraint:

-- Nonnull constraint
create table nn01(
  pid int,
  name varchar2(20) not null 


Non empty validation

insert into nn01(pid,name) values(1,'jack');  -- Allow repeated entry
insert into nn01(pid) values(2);                --Not allowed null



(5) Default constraint:

-- Default constraint
create table de01(
  pid int,
  name varchar2(20) default('tourist')


Default validation

insert into de01(pid) values(2);       -- Enter only id,name Default visitors



(6) Check constraints

-- Check and verify
create table ch01(
  pid int,
  sex char(10) check( sex in ('male','female') ),
  age int check( age >=0 and age <=100 )



Check and verify

insert into ch01(pid,sex,age) values(1,'male',18); -- Can be entered
insert into ch01(pid,sex,age) values(2,'Demon',18); -- not allow


(7) Foreign key constraint


--alter table from table add [constraint] [foreign key name] foreign key (from table foreign key field name) references main table (primary key of main table);


Alter table stuinfo add constraint fk_stuno foreign key(stuno) references student(stuno);

composite keys

  • Union primary key: two or more fields, which are combined as primary keys, are called Union primary keys.

  • Application scenario: many to many relationship middle table


drop table student_course;
create table student_course(
  sid varchar2(20) ,
  cid varchar2(20) ,
  score number(4,1)
alter table student_course add constraint student_course_pk primary key (sid,cid);

insert into student_course(sid,cid,score) values('s001','c001',100); --Duplicate not allowed




3, DML (addition, deletion and modification)

DML: data operation language to complete data addition, deletion and modification


1. Add

--Complete usage
insert into table name (field 1, field 2,...) values (value 1, value 2,...)
--Omit usage: value order must correspond to column order in the table
insert into table name values (value 1, value 2,...)


--insert data
--Mode 1
insert into student(sid,sname,phone)values(1,'tom','123');

--Mode 2
insert into student values(2,'aa',null,20,null,null,'111111');

insert into student values(2,'bb','male',18,null,'123@com','22222')



2. Modification

--Syntax 1: update all data in the table
update table name set field 1 = value 1, field 2 = value 2
--Syntax 2: update specified data (condition)
update table name set field 1 = value 1, field 2 = value 2,... where condition


--Modify data ------------

update student set phone='123321' where sid=1;


3. Delete

--Syntax 1: delete all
delete from table name;

--Syntax 2: delete specified data (condition)
delete from table name where condition;

--Syntax 3:
truncate table table name;


--Syntax 1 delete Support rollback
--Delete data
delete from student where sid=1;

--Delete all
delete from student;

--Grammar 3;Delete table structure,Recreate
truncate table student2;

Difference between delete and truncate:

  • Delete delete delete the data in the table. Rollback is supported.

  • truncate deletes the table structure and recreates it.




This is the end of this article, but before the end, we should pay attention to the database operation

When PL/SQL executes DML (add, delete and modify), no data can be queried.

, which can be described in the following three cases:

Adding a piece of data to Oracle: in fact, adding data to temporary tablespace

When will the data from the temporary tablespace be submitted to the tablespace?

A: three situations:

  1. Commit
  2. In about half an hour, automatic submission
  3. Close plsql development tool
    • Oracle transaction, auto commit closed by default. So you need to submit it manually



Let's move on to the next part!!

After watching, Congratulations, and know a little bit!!!

The more you know, the more you don't know

~Thank you for reading. Your support is the biggest driving force for my study! Come on, strangers work together and encourage together!!

Tags: Programming Oracle SQL Database MySQL

Posted on Fri, 22 May 2020 12:38:53 -0400 by Ghost_81st