This article is best read with MySQL foundation.


Oracle is very powerful database software. Default port: 1521. Unlike MySQL, the concept of Oracle database is that an operating system is a large database. A database can have many instances, each of which occupies a series of processes and memory. There is usually only one instance per machine. Each instance can have many users. Each user can have many tables. This user is similar to MySQL’s Databases. When you specify the corresponding tablespace (logical space) when creating a table, the data of the table will be stored in the corresponding data file of the tablespace.


Table Spaces:

Create table space
create tablespace stuspace 	Create the stuspace tablespace
datafile 'f:\db1.dbf' 		-- Specifies the physical file storage path
size 1024m 			Tablespace size
autoextend on 			-- Automatic extension
next 100m 			-- Each expansion is 100m

Drop the tablespace
drop tablespace stuspace	Delete the stuspace tablespace
Copy the code


Create user
create user tuser		-- Create the tuser user
identified by huibox		- Set the password to huibox
default tablespace stuspace	-- Specify the default tablespace stuspace

-- Authorize the user
grant dba to tuser		Give tuser the dba role

Copy the code

Common Oracle Roles

  • connect
    • Connect roles, basic roles
  • resource
    • Developer role
  • dba
    • Super Administrator Role


- create a table
create table person(
    pid number(11),
    pname varchar2(10))Add a column
alter table person add gender number(1)
Add multiple columns
alter table person add  (gender number(1),age number(3),...).-- Change the column type
alter table person modify gender char(1)
-- Change the column name
alter table person rename gender to sex
Delete a column
alter table person drop column sex
Copy the code

Oracle data types

The data type role
varchar,varchar2 A string
number Number (n) represents an integer of length N

Number (m,n) is a decimal number, the total length is m, the decimal is n, and the integer is m-n
data The date type
clob Large text data type (4G)
blob Binary data (4G)

Add bowdlerize:

Create sequence
create sequence s_person

Add a record
insert into person(pid,pname) values(s_person.nextval,'Ming')
-- Modify a record
update person set pname='wang' where pid=1
- delete

delete from person 		Drop all data in the table
drop table person		Drop the table structure
truncate table person		-- Delete a table first and then create a table. The effect is the same as that of delete. It is more efficient than delete when there is a large amount of data or indexes

Copy the code

The sequence

The default value starts from 1 and increases in ascending order. Mainly used to assign values to primary keys. Sequences do not really belong to any table, but can be logically bound to tables.

The query

-- Unlock user Scott
alter user scott account unlock
Unlock Scott password, can also be set to another password
alter user identified by tiger
-- Switch to user Scott

-- Query all data
select * from EMP t
Copy the code

Scott user

The default password is Tiger. It comes with its own data, you can use it to learn queries. To use it, you need to unlock it.

A single function

Function on a line and return a value.

  • Character function
    • Upper (‘ STR ‘) — uppercase
    • Lower (‘STR’) — lower case
  • Numerical function
    • Round (26.16,1) — Keep 1 digit and round it.
    • The mod (10, 3) – more than
  • Date function
    • Sysdate — System time
    • Months_between (sysDate, e.hireDate) — The distance between two dates
    • To_char (sysdate,’ YYYY-MM-DD hh:mi:ss’) — convert the date to a string
    • To_date (sysdate,’ YYYY-MM-DD hh:mi:ss’) — the string is converted to the date
  • The generic function
    • NVL (,0) — Converts null to 0

Multiline functions (aggregate functions)

Function with multiple lines and return a value.

  • Count (*) — Query the total number of primary keys

  • Sum (sal) — Wage republic

  • Max (Sal) — maximum salary

  • Min (sal) — minimum wage

  • Avg (Sal) — the average wage

  • Group by — Groups

  • Inner join/right join/left join Inner /right/outer join

Conditional expression

Alter table employee alias (null); alter table employee alias (null) Common with MySQL.
	case e.ename
    	when 'SMITH' then 'Tom'
        	when 'ALLEN' then 'jerry'
from emp e

-- Added salary assessment
select e.sal,
	case e.sal
    	when e.sal>3000 then 'Low income'
        	when e.sal>15000 then 'Middle income'
            	else 'High income'
from emp e

Oracle unique conditions by expression
select e.ename
        e.sal>3000 then 'Low income'
        	 e.sal>15000 then 'Middle income'
            	 'High income') "rating"from emp e
Copy the code


Provides a window for queries, all data from the original table. You must have DBA permission to create a view.

-- Create an EMP table from the EMP table of the SCPTT user
create table emp as select * from scott.emp
Create view
create view v_emp as select ename,job from emp
Query view
select * from v_emp
Operations on views are the same as operations on tables
If you modify the data, the original table data will also be changed

Create a read-only view
create view v_emp1 as select ename,job from emp with read only
Copy the code

The index

An index is a binary tree built on the columns of a table. Can greatly improve the query efficiency, but will affect the efficiency of adding, deleting and changing.

Create a single-column index
create index idx_ename on emp(ename) Create index by name.
Composite (multi-column) indexes
create index idx_ename jon on emp(ename,job)
Copy the code

Single-column index Only data existing in the original table can trigger the index. If single-row function and fuzzy query are used, the index will be affected. A composite index can be triggered only if it contains the original value in a column with a priority index.