Oracle Database Operations

　　　Oracle Database Operations

One: Unlock Scott/tiger users (Locked)
Alter user Scott account lock; --Lock
Alter user Scott account unlock; --Unlock
Alter user Scott identified by Tiger; --Change Password
Grant DBA to Scott; --Assigning permissions

Second: query

2.1: Querying all data in a table

SELECT * from EMP;
SELECT * FROM dept;

2.2: Query Some fields
Select Empno,ename,job from EMP;

2.3: With conditional query
SELECT * from EMP where ename = ' SMITH ';--case sensitivity in string contents

--Remove duplicate content
SELECT distinct job from EMP;
Select COUNT (Distinct job) from EMP;

--Alias the field (no single quotation marks, and aliases that contain spaces require double quotes)
Select Ename as staff name, Sal "wages" from EMP;

--arithmetic operation (null with null value operation result)
Select SAL+NVL (comm,0) from EMP;

--string concatenation (| | Can be stitched to any type)
Select ' Staff name: ' | | ename| | ' wages: ' | | Sal from EMP;

--Sort
SELECT * from emp ORDER by Sal Desc,empno ASC;
SELECT * FROM emp ORDER BY comm Desc nulls last; --nulls first;

--Fuzzy query: wildcard character (% _), null query, in between and
SELECT * FROM EMP where comm are NOT null;

% any length, any content
_ One length, any content

--Query the person whose name begins with J
SELECT * from emp where ename like ' j% ';
SELECT * from emp where ename like '%a% ';
SELECT * from emp where ename like ' j____ ';


--Pseudo-column
--rowid: Is the storage address of a row in a table
--rownum: Is the ordinal of the row in the result set returned by the query
Select rowid,rownum,emp.* from emp
where RowNum <=2;
--rownum can only use less or less than equals when used as a condition
--Implement top in SQL Server

--Aggregate grouping count sum avg Max min GROUP by have
--statistics on the highest salary for each position
Select Job,max (SAL) from the EMP group by job;
--statistics of average salary per department, less than 2000 not shown, results sorted in descending order
Select Deptno,avg (SAL)
From EMP
GROUP BY Deptno
Having avg (SAL) >=2000
ORDER by AVG (SAL) desc;

--Connection query (internal connection)--public fields
--Find out more about all the staff and the Department in which they are located
SELECT * FROM EMP a inner joins dept b on a.deptno = B.deptno;
SELECT * FROM emp A, dept b where A.deptno = B.deptno;
--Outer connection left Right
SELECT * FROM emp a full join dept b on a.deptno = B.deptno;
-Cross Connect
SELECT * from emp a cross join Dept B;

CREATE TABLE Team
(
Teamname VARCHAR2 (10)
);
INSERT into team values (' A ');
INSERT into team values (' B ');
INSERT into team values (' C ');

SELECT * FROM Team a cross join Team B
where a.teamname>b.teamname;

--Self-connection: query King's Subordinates
SELECT * FROM EMP where Mgr
= (select empno from emp where ename= ' KING ');

SELECT * from emp a join EMP b
On a.mgr = B.empno
where b.ename= ' KING ';

--sub-query = < > <= >= in
--Check with all accounting Department staff
SELECT * from emp where deptno =
(select Deptno from dept where dname = ' ACCOUNTING ');

--Search all locations in New York and DALLAS Department staff
SELECT * from EMP where deptno in
(select Deptno from dept where Loc in (' NEW YORK ', ' DALLAS '));

--Query all employees who share the same position with Smith (In,= can contain multiple fields)
SELECT * from EMP where (deptno, job)
In (select Deptno,job from emp where ename= ' SMITH ');

--Inline View sub-query
SELECT * FROM
(select A.*,b.dname,b.loc from emp a joins Dept B on A.deptno=b.deptno) Y

--Use a join query as a subquery to remove duplicate fields

--query for people who pay more than the average wage per department
Select *from emp A Join
(select Deptno,avg (SAL) as avgsal from EMP Group by DEPTNO) Y
On a.deptno = Y.deptno
where a.sal>y.avgsal;

--statistics of the details of each department and department average salary, less than 2000 not shown, results in descending order
Select *from Dept A join
(select Deptno,avg (SAL) as avgsal from EMP Group by DEPTNO) Y
On a.deptno = Y.deptno
where avgsal>=2000;

--Query the minimum wage of 5 people (using pseudo-column rownum and sorting)
Select Rownum,y.* from
(SELECT * from emp ORDER by Sal) Y
where rownum<=5;

--paged query converts a pseudo-column to a normal column of a temporary table
SELECT * FROM
(select RowNum as rn,emp.* from EMP where rownum<=16) Y
where Y.rn >=11;



--function
--Date function

--date conversion (usually in the form of a string conversion description date) To_date (date string, date format)
Select To_date (' 2012-7-28 ', ' YYYY-MM-DD ') from dual;


--1. Querying the system time Sysdate
Select Sysdate from dual;

--2. Adding a month add_months (date, number) over a period of time
Select Add_months (sysdate,-10) from dual;

--3. Find the last day of the month of a date last_day (date)
Select Last_day (sysdate) from dual;

--4. Find the month difference between two dates (return decimals) months_between (date 1, date 2)
Select Months_between (to_date (' 2012-12-12 ', ' yyyy-mm-dd '), sysdate) *30 from dual;

Days--** Difference: subtraction
Select To_date (' 2012-12-12 ', ' yyyy-mm-dd ')-sysdate from dual;
--5. Ask for the first week after the specified date next_day (date, day of the week)
Select Next_day (sysdate, ' Saturday ') from dual;
Select Next_day (sysdate,7) from dual;
--(1-7 Sunday-Saturday)

--6. Rounding round (date, date code)
Select round (sysdate, ' yyyy ') from dual;
--7. Extract date Extract (Year/month/day from date)
Select Extract (year from sysdate) from dual; --Return numbers
Select Extract (month from sysdate) from dual;
Select Extract (Day from sysdate) from dual;
--8.to_char Extract Date part to_char (date, format string)
Select To_char (hiredate, ' yyyy ' year "MM" month "DD" Day "HH24:MI:SS")
from EMP;

Select To_char (hiredate, ' yyyy ') from EMP; --string
Select To_char (hiredate, ' mm ') from EMP;
Select To_char (sysdate, ' Day ') from dual; --Week

--Check all weekend entry staff
SELECT * from emp where TO_CHAR (HireDate, ' d ') in (' 1 ', ' 7 ');

--string
--string length (String) vsize (String)
Select Length (' Hello World!!!! ') from dual;
Select Vsize (' Hello ') from dual;
--Stitching string Concat (String 1, string 2) | |
Select concat (' Hello ', ' world ') from dual;
--Intercept string substr (string, start, length)
Select substr (' Hello world!!!! ', 1,5) from dual;
--0 1 represents the first

--Remove the specified content trim (character from string)
Select Trim (' x ' from ' xxxxxxxxxxxxxhexxlloxxxxxxxxx ') from dual;
--Go to the space trim ()
Select Trim (' he llo ') from dual;
--Content replacement replace (string, replace, replace character)
Select replace (' Hello ', ' l ', ' X ') from dual;
Select Replace (' He llo ', ', ') from dual;

--Multiple substitution translate (string, replace, replace character)
Select translate (' Hello World!!!! ', ' LR ', ' xx ') from dual;

--Find character InStr (string, find content)
Select InStr (' Hello World!!!! ', ' l ') from dual;
--InStr (String, find content, start position)
Select InStr (' Hello World!!!! ', ' l ', 6) from dual;
--instr (string, find content, start position, first few)
Select InStr (' Hello World!!!! ', ' l ', 6,2) from dual;

--ascii code ASCII (character) Chr (number)
Select ASCII (' a ') from dual;
Select CHR from dual;

--Convert to case upper lower
Select Upper (' Hello '), lower (' Hello ') from dual;

--Mathematical functions
Select mod (3,10) from dual; --Take the mold
Select Ceil (99.0000000001) from dual; --Rounding up
Select Floor (99.999999) from dual; --Rounding down
Select round (99.9) from dual; --Rounding
Select Round (99.93,1) from dual; --Rounding, exact number of digits
Select Dbms_random.value (55,100) from dual; --Random number
Select Dbms_random.value () from dual;

--Conversion function to_char (content, format) to_number (numeric character) to_date (date character, date format)
Select To_char (sysdate, ' Yyyy-mm-dd ') from dual;
Select To_char (12345.126, ' L999999.99 ') from dual; --l Local

--Null value processing
SELECT * from EMP;
Select Sal+comm from EMP;

--null value to substitute value nvl (field, replacement value)
Select SAL+NVL (comm,0) from EMP;

--NOT NULL to 1 null to value 2 nvl2 (field, value 1, value 2)
Select NVL2 (comm,comm,0) from EMP;

--Parameter 1 equals parameter 2 becomes empty otherwise parameter 1 nullif (parameter 1, parameter 2)
Select Nullif (comm,300) from EMP;

SELECT * from EMP;
--wm_concat--row-and-column conversion function 10g
Select Deptno, Wm_concat (ename) from the EMP Group by Deptno

--Analytic functions (advanced sorting function)
Select Rownum,y.* from (
Select emp.* from emp order by Sal) Y


Select Ename,sal,row_number () over (order by Sal),
Rank () Over (order by Sal),
Dense_rank () Over (order by Sal)
From Scott.emp;

Oracle Database Operations