A summary of Oracle that allows Java program apes to look through at any time

A summary of Oracle that allows Java program apes to look through at any time

Foreword : Oracle study also has more than 10 days, but, next also to learn many other things, and can not mention step, so this summarizes the following Oracle common commands and statements, no syntax is an instance, in order to work later at any time to look over, after all, is their own things , you can understand it at a glance.

The statements and operations are basically summarized in the Order of actual combat, such as creating users, building tables, initializing sequences, inserting data in order.

The base table for this article is the EMP employee table, which is the most well-known Scott User, the Dept department table and the Salgrade salary scale, all of which are written around it.

Let's take a look at some of the operations that are commonly used in Oracle.

I. User-related actions.

• Create user

Create user Scott identified by 123456;

• Assigning Permissions to users

Grant Connect,resource to scott;grant create view to scott;grant create synonym to Scott;

• Revoke user Rights

Revoke Connect,resource from  scott;revoke create View from Scott;revoke create synonym from Scott;

• Delete User

Drop user Scott Cascade;

• Modify User Password

Alter user Scott identified by 123456;--command Modify Conn scott/123456password;--command visual modify 1connect scott/123456password;-- Command Visualization Modification 2

• Set whether the user is locked

Alter user Scott account Lock;alter user Scott account unlock;

　　

Two. Table space-related operations.

• CREATE TABLE Space

Create Tablespace mysapcedatafile ' d:a.ora ' size 10m--absolute path and size extent management Localuniform size 1m;--sizes of each partition

• Extending table Space

Alter tablespace mysapceadd datafile ' d:b.ora ' size 10M;

• Specifying table spaces for Oracle objects

Create user Space_text identified by 123456 account unlock default tablespace mysapce;--Creating tables, indexes can also specify table spaces, and once specified, tablespace cannot be modified.

• Delete Table space

Drop Tablespace mysapce;

　　

Three. DDL-related operations.

• Tables table

--Create an employee table  EMP (  EMPNO number (4) CONSTRAINT pk_emp PRIMARY key,ename VARCHAR2, JOB VARCHAR2 (9), MGR V Archar (Ten),    --Boss HireDate date,    --Entry date sal number (7,2),--  Salary Comm Number (7,2),--allowance Deptno number (2) CONSTRAINT fk_deptno REFERENCES DEPT  );

　　

--Creating a departmental table create  table DEPT (  DEPTNO number (2) CONSTRAINT pk_dept PRIMARY KEY, dname VARCHAR2 (+), LOC VARCHAR2 (13)- -Address  );

　　

--Create a salary scale table  Salgrade (   GRADE number,--rank losal number, the lowest salary hisal number in the hierarchy  -the highest salary in the hierarchy  );

　　

• Views View

--Creates a view for the empno,ename,sal of the EMP table and the grade of the Dept table dname and Salgrade table create view  emp_dept_salgrade as  Select E.empno,e.ename,e.sal,d.dname,s.grade from  EMP e INNER JOIN Dept D using (DEPTNO)  inner join Salgrade s on e.sal Between S.losal and S.hisal;    SELECT * from emp_dept_salgrade;--query by view

• Sequence sequence

--Creates a sequence for the empno of the employee table create  sequence emp_empno_seq  start with 1001  increment by  1  nomaxvalue  Nocycle  cache Ten;  Select Emp_empno_seq.currval from dual; Query the current value  of a sequence Select Emp_empno_seq.nextval from dual; the next value of the query sequence

• Synonyms synonym

--Creating synonyms for view Emp_dept_salgrade create  synonym eds for  Emp_dept_salgrade;  SELECT * from EDS;

• Trigger Trigger

--Create an automatically inserted trigger for the empno of the employee table create  or replace trigger Emp_empno_tri  before insert on EMP for each  row  Begin    : New.empno:=emp_empno_seq.nextval;  --The statement level (for each row) trigger can be: New. Column name to be manipulated.   end;

• Stored Procedure procedure

--the process of creating a multiplication table that can control the number of rows. Create or Replace procedure Nine_nine (nine_line in number) Asbegin  for  i in 1..nine_line loop for    J in 1..i Loo P      dbms_output.put (i| | ' * ' | | j| | ' = ' | | i*j| | '  );    End Loop;      Dbms_output.put_line (");  End loop;end;--calls this multiplication process set Serveroutput On;execute nine_nine (9);

• stored function functions

--Create a seek 1!+2!+. Storage function for +20! value Create or replace function One_twreturn numberasvalue_sum number:=0;value_loop number:=1;begin for  i in 1..20 loop    value_loop:=value_loop*i;    Value_sum:=value_sum+value_loop;  End Loop;  return Value_sum;end;select ONE_TW () from dual; --Call function Note: A call to a stored function can be placed in the position of an expression, where the expression is formed and where it can be called. 

• Transaction rollback, Commit, savepoint

Four. Operations related to DML.

• Inserting data Insert

--dept--insert into dept Select Ten, ' ACCOUNTING ', ' NEW YORK ' from Dualunionselect, ' the ' to ', ' DALLAS ' from Dualunionselect, ' SALES ', ' CHICAGO ' from Dualunionselect, ' OPERATIONS ', ' BOSTON ' from Dual;commit;                                    - Use the multi-line insert method in Oracle, the keyword union,select the data you want, and build a complete structure with the dual pseudo-table. 

　　

--emp--insert into EMP (ename,job,mgr,hiredate,sal,comm,deptno) Select ' SMITH ', ' Clerk ', 1009,to_date (' 17-12-1980 ', ' Dd-mm-yyyy '), 800,null,20 from Dualunionselect ' ALLEN ', ' salesman ', 1006,to_date (' 20-2-1981 ', ' dd-mm-yyyy '), 1600,300,30 from Dualunionselect ' WARD ', ' salesman ', 1006,to_date (' 22-2-1981 ', ' dd-mm-yyyy '), 1250,500,30 from Dualunionselect ' JONES ', ' MANAGER ', 1009,to_date (' 2-4-1981 ', ' dd-mm-yyyy '), 2975,null,20 from Dualunionselect ' MARTIN ' , ' salesman ', 1006,to_date (' 28-9-1981 ', ' dd-mm-yyyy '), 1250,1400,30 from Dualunionselect ' BLAKE ', ' MANAGER ', 1009,to_ Date (' 1-5-1981 ', ' dd-mm-yyyy '), 2850,null,30 from Dualunionselect ' CLARK ', ' MANAGER ', 1009,to_date (' 9-6-1981 ', ' Dd-mm-yyyy '), 2450,null,10 from Dualunionselect ' SCOTT ', ' ANALYST ', 1004,to_date (' 13-10-87 ', ' dd-mm-rr ') -85,3000,null , Dualunionselect ' KING ', ' president ', 1007,to_date (' 17-11-1981 ', ' dd-mm-yyyy '), 5000,null,10 from Dualunionselect ' TURNER ', ' salesman ', 1006,to_date (' 8-9-1981 ', ' dd-mm-yyyy '), 1500,0,30 from Dualunionselect ' ADAMS ', ' Clerk ', 1009,to_dAte (' 13-10-87 ', ' dd-mm-rr ') -51,1100,null,20 from Dualunionselect ' JAMES ', ' Clerk ', 1009,to_date (' 3-12-1981 ', ' Dd-mm-yyyy '), 950,null,30 from Dualunionselect ' FORD ', ' ANALYST ', 1004,to_date (' 3-12-1981 ', ' dd-mm-yyyy '), 3000,null,                                  From Dualunionselect ' MILLER ', ' Clerk ', 1004,to_date (' 23-1-1982 ', ' dd-mm-yyyy '), 1300,null,10 from Dual;commit;--The trigger Emp_empno_tri is used here empno employee number from the inserted EMP table

　　

--salgrade--insert into Salgrade values (1,700,1200), insert into Salgrade values (2,1200,1400), insert into Salgrade VALUE S (3,1400,2000); insert into salgrade values (4,2000,3000), insert into Salgrade values (5,3000,9999); commit;

　　

• Updating Data Update

Update emp set sal=3000 where empno=1004;

• Deleting data Delete

Delete from emp where empno=1004; --from can be omitted

• Query Data Select

Querying data is the most critical part of the DML statement and the hardest part, where there are many instances of the Scott user, all of which are slightly more complex queries, and simply don't need to be written.

　　　　1. Most commonly used.

SELECT * FROM Emp;select * from Dept;select * from Salgrade;

　　　　2. Internal connection.

2-1. Query the department where each employee is located, using where connection.

Select E.empno,e.ename,d.dname from emp e,dept D where E.deptno=d.deptno;

2-2.inner join on connection.

Select E.empno,e.ename,d.dname from EMP e inner joins dept D on E.deptno=d.deptno;

2-3.inner join using connection.

Select E.empno,e.ename,d.dname from EMP e INNER JOIN Dept D using (DEPTNO);

　　　3. External connection.

3-1. Left outer connection: for example, query the employee of the department (show all departments).

Select E.ename,d.dname from emp e left JOIN Dept D using (DEPTNO);

3-2. Use the right outer connection (+).

Select E.ename,d.dname from emp e,dept D where E.deptno=d.deptno (+);

3-3. Right-to-outer connection: for example, to find out the department of all employees (showing all employees).

Select E.ename,d.dname from EMP e right Join Dept D using (DEPTNO);

3-4. Use the right outer connection (+).

Select E.ename,d.dname from emp e,dept D where E.deptno (+) =d.deptno;

　　　　4. Self-connect.　

4-1. Find out the employee and his superiors.

Select A.ename as staff, b.ename as superior from EMP A, EMP b where a.mgr=b.empno;  Select A.ename as Superior, b.ename as superior from EMP a INNER join EMP B on a.mgr=b.empno;

　　　5. Sub-query.

5-1. Check employee information for wages above average.

SELECT * from emp where sal> (select AVG (SAL) from EMP);

5-2. Use any to query employee information that meets any salary below the minimum wage.

SELECT * from emp where Sal<any (select Losal from Salgrade);

5-3. Check with all employees for their respective departments.

Select Dname from (SELECT DISTINCT dname from dept);

5-4. Query for employee information that satisfies the minimum wage for each department.

SELECT * from emp where Sal>all (select min (sal) from EMP Group by DEPTNO);

5-5. Find the employee number and name of the employee in each department that is higher than the average salary of the department.

Select Empno as employee number, ename as name from the EMP outer where sal>  (select AVG (SAL) from emp inner where inner.deptno=outer. DEPTNO);

5-6. Query the employee information that is not in the department 10: note 1 in the subquery, because only care about whether the subquery returns a true value, using 1 can improve the efficiency of the query.

5-6.1.exists query efficiency is higher than in subquery.

SELECT * from EMP a Where NOT EXISTS (select 1 from emp b where a.deptno=10);

5-6.2.in is inefficient, but better understood.

SELECT * from EMP where deptno not in 10;

5-7. Query the EMP table to manage employees of other employees.

Select ename from  emp a  where exists (select ename from  EMP b where a.empno=b.mgr);

5-8. Delete the duplicate lines in the department.

Delete emp where rowid not in (the Select min (rowid) from the EMP Group by DEPTNO);

5-9. Find the EMP table 第6-10条 record.

SELECT * FROM (select RowNum m,ename,sal,deptno from emp where rownum<=10) where m>5;

　　　　　　

A summary of Oracle that allows Java program apes to look through at any time