Oracle Review (Review Lite version v1.0)

I can't remember, Super basic Oracle Knowledge, Novice can look at.

Most examples are done with the EMP table in the Scott user

Sort: Order by column name desc is descending, default is ascending;

Update table name set column name = ';

INSERT into table name ( column name [column name write full or not write all]) values (to be corresponding to the column name);

Fuzzy query: Like"%s%" contains S, like"s%" is the beginning of s, like"S_" with s beginning only two letters;

ALTER TABLE name add constraint Key name ( column name )

If the foreign key name ( column name ) reference table name ( column );

ALTER TABLE name drop column name ;

Sequences, commonly used for insert operations, can grow on their own, and can be set in MySQL and SQL Server for self-growth, commonly used in Oracle sequences

Create sequence sequence name

start with 1 starting from 1

increment by 1 per cent growth of 1

minvalue 1 minimum value is 1

nomaxvalue No maximum value

Cache 10 (The Cahe here refers to the number of caches 10);

Date conversion involved

Select To_char (sysdate, "Yyyy-mm-dd Hh24:mi:ss") from dual;

For employees who have entered the office after January 1, 1981 in the enquiry Department 20:

Select ename from emp where deptno=20 and HireDate > To_date (' 19810101 ', ' yyyymmdd ');

Group

Select AVG (SAL) from EMP table name grop by deptno column name

Select each department, average salary for each position

Select Deptno,job avg (SAL)

From EMP

GROUP BY Deptno,job

Order BY Deptno;

groupby/have/order by and where are used in the order

where "group BY" has "order by"

Multi-Table Connection query

Query and the name of the employee who works the same in department 10

Check the work in department 10 first Select distinct "This is used to remove the heavy" job from the EMP where deptno=10;

then the complete sentence is the select Ename,job from emp where job in "is referred to in the back range"(SELECT distinct "This is used to remove the heavy" job from EMP where deptno=10) and deptno<>10;

into keyword

Select Ename,job,into sname,sjob from emp where empno=7396;

Three types of loops:

①loop②while i<=100 loop③for i in reverse 1......100 loop

If i>100 then exit; s:=s+i; s:=s+i;

End If; i:=i+1; end loop;

s:= s+i; end loop;

i:=i+1;

End Loop;

Execute Dynamic SQL statements

Daclare

strSQL varchar2 (100);

Begin

strsql:= ' CREATE table TTT (a mumber) ';

Excute immediate (strSQL);

End

Cursor

Declare

Cursor "vernier keyword" c1 is a select empno, ename,job from EMP;

Begin

Open C1;

Fetch C1 into emp.empno,emp.ename,emp.job;

While C1%found loop

Dbms_output.put_line ();

Fetch C1 into emp.empno,emp.ename,emp.job;

End Loop;

Close C1;

End

Trigger

Table-level triggers are executed only once when several rows are inserted at a time, and row-level triggers are executed once by inserting a row

Create or Replace Trigger TRI1

Before update

On EMP

For each row, if there is a row-level trigger, if it does not exist, it is a table-level trigger

Begin

Dbms_output.put_line (");

If you want to terminate the operation, throw an abnormal raised ——————————————-here;

End

About Old:new

Insert: no old, new

Update: There is old, new

Delete: No old, no new

End!....

Oracle Review (Review Lite version v1.0)