Oracle Simple Notes

1. Database creation

Note:

1) Unlike MySQL, Oracle can create database directly

2) Oracle creates schema with one user, that is, the schema's access user, and user one by one, but there can be multiple access users (with permission control)

1.1 Creating a database file

CREATE tablespace XX LOGGING datafile ' D:\app\XX\oradata\orcl\XX.dbf ' SIZE 1000M;

Create temporary tablespace XX tempfile ' D:\app\XX\oradata\orcl\XX.dbf ' size 1000m;

1.2 Creating a user

CREATE USER xx identified by xx DEFAULT tablespace xx temporary tablespace xx;

1.3 Licensing

Grant Connect, resource to XX;
Grant create session to XX;

2. Database operations (default Sccot user):

2.1 Create

CREATE TABLE Persons (
person_id number PRIMARY KEY,
First_Name VARCHAR2 () not NULL,
Last_Name VARCHAR2 () not NULL,
Score number,
Type VARCHAR2 (20)
);

Note:

1) ora-02000:missing always keyword: Do not use generated by DEFAULT as IDENTITY in version 11g, use primary KEY
2) Oracle originally has only number type, used as numbers (19,2), that is, the decimal place, and later in order to be compatible with other databases, the new int, can only be shaped

2.2 Insert

Insert into persons values (1, ' Fred ', ' Xu ', 0, ' a ');

Insert into persons values (seq_person_id. Nextval, ' fred3 ', ' Xu3 ', 0, ' B ')

Note:

1) under Oracle, it is not easy to set up self-increment without MySQL, the steps are as follows:

1.1) CREATE SEQUENCE seq_person_id start with 100; #创建一个序列
1.2) INSERT into Persons VALUES (seq_person_id. Nextval, ' fred1 ', ' xu1 ', 0, ' a ');

#此时插入persons表记录的person_id被设置成了100

1.3) can also take the form of a trigger
CREATE TRIGGER Persons_trigger
Before INSERT on persons
For each ROW
When (new.person_id is null)
Begin
Select Seq_person_id.nextval into:new.person_id from Sys.dual;
End

Note: sys.dual is a virtual table, Oracle guarantees that there is only one record
: new-a new record of the current row during trigger execution
: old-the old record of the current line during the execution of a trigger

After you have a trigger, insert the data without filling in the primary key insert into persons (first_name, last_name, score, Person_type) VALUES (' Fred2 ', ' Xu2 ', 0, ' a ');

2.3 Select

SELECT * from persons where first_name = ' fred ' or last_name = ' xu1 '
SELECT * from persons where first_name like '%fred% '
SELECT * from persons where CONCAT (First_Name, last_name) like '%1% '

SELECT * from users where rownum between 0 and 5 #利用rownum关键字分页

2.4 Group BY

Select AVG (Score), Person_type from persons group by Person_type
Select AVG (Score), Person_type from persons group BY person_type have person_type = ' B '

Note:

1) Ora-00979:not A group BY expression group By column to be able to be processed

2.5 in

SELECT * from persons where Person_type in (' A ', ' B ');

2.6 Insert All

Insert All
into persons (first_name, last_name, score, Person_type) VALUES (' Test ', ' test ', 1, ' C ')
into persons (first_name, last_name, score, Person_type) VALUES (' test1 ', ' test1 ', 1, ' C1 ')
into persons (first_name, last_name, score, Person_type) VALUES (' Test2 ', ' test2 ', 1, ' C2 ')

SELECT 1 from dual;

Note:

SELECT 1 from dual; The last select must have

2.7 views

CREATE VIEW Person_view as SELECT * from persons where first_name like '%f% '

SELECT * from Person_view;

Note:

A view is a table, just a logical definition; unless it's a materialized view, that's physically occupied.

2.8 Index

Create index Person_index on persons (first_name);

Oracle Simple Notes