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

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

example:

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

grammar

• 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;

example

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

4. Create user

grammar

--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

example

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

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;

Classification:

example:

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;
--Add
alter table table name add (column description,...);
--Modification
alter table table name modify (column description,...);
--Delete column
alter table table name drop column column name;

example:

--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

example:

--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 commit;

(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 ~~~sql -- 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 commit;

(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 commit;

(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 commit;

(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 commit;

(7) Foreign key constraint

Syntax:

--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);

example:

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 commit;

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,...)

Example:

--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

example:

--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;

example:

--delete --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!!

Add new comment

0 comments