Oracle Table query and precautions

Oracle Table query and precautions

△Query the specified column (remove duplicate rows) select distinct deptno, job from emp; △name must be capitalized select deptno, job from emp where ename = 'Smith '; △ process null values using nvl function select sal * 13 + nvl (comm, 0) * 13 "annual salary", ename, comm from emp; △where condition uses inselect * from emp where empno in (123,456,879); △is null uses select ename from emp where mgr is null; △ logical operation: select * from emp where (sal> 500 or job = 'manager') and ename like 'J % '; △ query and sort by two fields select * from emp order by deptno, sal desc; △sort by column alias select ename, sal + (nvl (comm, 0 )) * 12 "annual salary" from emp order by "annual salary"; △subquery -- grouping function max min avg sum count

 

Note: grouping functions cannot be queried together with non-grouping functions.

select min(sal),max(sal) from emp;select ename,sal from emp where sal=(select mac(sal) from emp);

 

△Grouping function group by having order by -- ① The order of the three cannot be changed ② there must be a group

* Select avg (sal), max (sal), deptno, job from emp group by deptno, job for the average and maximum salaries of each job in each department;

-- Note: the order of grouping conditions must be the same as that of the preceding columns.

* Shows the Department number with an average salary lower than 2000 and its average salary select deptno, avg (sal), max (sal) from emp group by deptno having avg (sal) <2000;

 

△ Cartesian set rules: Multi-Table query conditions must be at least less than the number of tables-1 △self-join query on the same table

* How to display the name of an employee's computer supervisor-use an alias to treat a table as two select worker tables. ename, boss. ename from emp worker, emp boss where worker. mgr = boss. empno;

 

△Subquery -- nested Query

① Single Row subquery * how to display select * from emp where deptno = (select deptno from emp where ename = 'Smith ') for all employees in the same department as SMITH '); ② multi-row subquery-in * How to query the names and salaries of employees with the same work as department 10 select eanem, sal from emp where job in (select distinct job from emp where deptno = 10 ); ③ multi-row subqueries use the all and any operators * how to display the names and salaries of all employees of Department 30 in the wage ratio select ename, sal from emp where sal> all (select sal from emp where deptno = 30 ); ④ multi-column subquery -- Return multiple columns * How to query the select ename from emp where (deptno, job) = (select deptno, job from emp where ename = 'Smith ');

 

△ Use subqueries in the from sentence * how to display employee information higher than the average salary of your own department

1. first, find out the average salary of all departments select deptno, avg (sal) a from emp group by deptno; 2. consider the result of 1 as a sub-table (embedded view) to obtain the result select * from emp a2, (select deptno, avg (sal) a from emp group by deptno) a1 where a1.deptno = a2.deptno and a2.sal> a1.a;

 

△Orecle paging Query

1. select * from emp; 2. use the result of 1 as an embedded view and obtain the pseudo column rn, and then propose the first condition, for example, rn <10 select a1. *, rownum rn from (select * from emp) a1 where rownum <= 10; 3. use the result of 2 as an embedded view to propose the second restriction, for example, rn> 6 select * from (select a1. *, rownum rn from (select * from emp) a1 where rownum <= 10) where rn> 6;

 

△ Create a new table using the query results

create table mytable(id,sal,deptno) as select empno,sal,deptno from emp;

 

△Merge query union -- get union set (automatically remove duplicate rows) union all -- get union set intersect -- get intersection minus -- get difference set