Shulou(Shulou.com)05/31 Report--

This article introduces you how to achieve select operation in oracle, the content is very detailed, interested friends can refer to, hope to be helpful to you.

 view table structure

DESC emp

 queries all columns

SELECT * FROM dept

Don't use select easily *

SET TIMING ON; turns on the switch that displays the operation time and displays the query time below.

CREATE TABLE users (userId VARCHAR2 (10), uName VARCHAR2 (20), uPassw VARCHAR2 (30)

INSERT INTO users VALUES ('a0001', 'aaaaaaaaaaaa', 'aaaaaaaaaaaaaaaaaaaaaaa')

-- copy from yourself and increase the amount of data by tens of thousands of rows. It can be used to test the execution efficiency of sql statements.

INSERT INTO users (userId,UNAME,UPASSW) SELECT * FROM users

SELECT COUNT (*) FROM users; counts rows

 query specified column

SELECT ename, sal, job, deptno FROM emp

How to cancel duplicate line DISTINCT by 

SELECT DISTINCT deptno, job FROM emp

? Inquire about SMITH department, job, salary

SELECT deptno,job,sal FROM emp WHERE ename = 'SMITH'

Note: oracle is case-sensitive, so ename='SMITH' and ename='smith' are different.

Oracle notes

eight

 uses the arithmetic expression nvl null

Question: how to display the annual salary of each employee?

SELECT sal*13+nvl (comm, 0) * 13 "Annual salary", ename, comm FROM emp

 uses aliases for columns

SELECT ename "name", sal*12 AS "annual income" FROM emp

How does  handle null values

Use the nvl function to handle the

How  connects the string (| |)

SELECT ename | |'is a'| | job FROM emp

 uses the where clause

Question: how to display employees whose salary is higher than 3000?

SELECT * FROM emp WHERE sal > 3000

Question: how to find employees who joined the workforce after 1982.1.1?

SELECT ename,hiredate FROM emp WHERE hiredate > 'January-January-1982'

Question: how to display employees with a salary of 2000 to 3000?

SELECT ename,sal FROM emp WHERE sal > = 2000 AND sal 500or job = 'MANAGER') and ename LIKE' J%'

 uses the order by sentence default asc

Question: how to display the information of employees in the order from low to high wages?

SELECT * FROM emp ORDER by sal

Problem: according to the ascending order of the department number and the descending order of the wages of the employees

SELECT * FROM emp ORDER by deptno, sal DESC

 sorts using column aliases

Question: sort by annual salary

Select ename, (sal+nvl (comm,0)) * 12 "Annual salary" from emp order by "Annual salary" asc

Aliases need to use "" in the circle, English does not need ""

Oracle notes

nine

 paging query

Wait until you learn the sub-query.

Clear clear screen command

Complex query of oracle table

 description

In practical applications, we often need to perform complex data statistics, and we often need to display the data of multiple tables. Now let's introduce the more complex select statements.

Data packets-max,min, avg, sum, count

Question: how to display the maximum and minimum wages of all employees?

SELECT MAX (sal), min (sal) FROM emp e

Who is the man with the highest wage?

Incorrect writing: select ename, sal from emp where sal=max (sal)

Correct writing: select ename, sal from emp where sal= (select max (sal) from emp)

Note: select ename, max (sal) from emp; this statement will report an error when executed, saying ORA-00937: not a single grouping function. Because max is a grouping function

Ename is not a grouping function.

But the sentence select min (sal), max (sal) from emp; can be executed. Because both min and max are grouping functions, that is, if there is one in the column

Grouping function, everything else must be a grouping function, otherwise something will go wrong. It's grammatical.

Question: how to display the average salary and the sum of wages of all employees?

Question: how to calculate the total number of employees question: how to

Extension requirements:

Inquire about the name and job position of the highest wage employee

SELECT ename, job, sal FROM emp e where sal = (SELECT MAX (sal) FROM emp)

Display information about employees whose salary is higher than the average salary

SELECT * FROM emp e where sal > (SELECT AVG (sal) FROM emp)

 group by and having clauses

Group by is used to group the results of a query.

The having clause is used to restrict grouping the display of results.

Question: how to display the average wage and maximum wage of each department?

SELECT AVG (sal), MAX (sal), deptno FROM emp GROUP by deptno

(note: the hidden point here is that if you want to group a query, the grouped field deptno must appear in the list of the query, otherwise an error will be reported. Because grouping

If none of the fields appear, there will be no way to group)

Question: show the average wage and minimum wage for each position in each department?

SELECT min (sal), AVG (sal), deptno, job FROM emp GROUP by deptno, job

Question: show the department number where the average salary is less than 2000 and its average salary?

SELECT AVG (sal), MAX (sal), deptno FROM emp GROUP by deptno having AVG (sal)

< 2000;  对数据分组的总结 1 分组函数只能出现在选择列表、having、order by 子句中(不能出现在 where 中) 2 如果在 select 语句中同时包含有 group by, having, order by 那么它们的顺序是 group by, having, order by 3 在选择列中如果有列、表达式和分组函数，那么这些列和表达式必须有一个出现在 group by 子句中，否则就会出错。 如 SELECT deptno, AVG(sal), MAX(sal) FROM emp GROUP by deptno HAVING AVG(sal) < 2000; 这里 deptno 就一定要出现在 group by 中 多表查询  说明 多表查询是指基于两个和两个以上的表或是视图的查询。在实际应用中，查询单个表可能不能满足你的需求，（如显示 sales 部门位置和其员工的姓名），这种情况下需要使用到（dept 表和 emp 表） Oracle 笔记 10 问题：显示雇员名，雇员工资及所在部门的名字【笛卡尔集】？ 规定：多表查询的条件是 至少不能少于 表的个数-1 才能排除笛卡尔集 （如果有 N 张表联合查询，必须得有 N-1 个条件，才能避免笛卡尔集合） SELECT e.ename, e.sal, d.dname FROM emp e, dept d WHERE e.deptno = d.deptno; 问题：显示部门号为 10 的部门名、员工名和工资？ SELECT d.dname, e.ename, e.sal FROM emp e, dept d WHERE e.deptno = d.deptno and e.deptno = 10; 问题：显示各个员工的姓名，工资及工资的级别？ 先看 salgrade 的表结构和记录 SQL>

Select * from salgrade

GRADE LOSAL HISAL

-1 700 1200

2 1201 1400

3 1401 2000

4 2001 3000

5 3001 9999

SELECT e.ename, e.sal, s.grade FROM emp e, salgrade s WHERE e.sal BETWEEN s.losal AND s.hisal

Extension requirements:

Question: show the name of the employee, salary and the name of the department, and sort by department?

SELECT e.ename, e.sal, d.dname FROM emp e, dept d WHERE e.deptno = d.deptno ORDER by e.deptno

(note: if you use group by, be sure to put e.deptno in the query column.)

 self-connection

Self-join refers to join queries in the same table

Question: show the name of the superior of an employee?

Such as showing the superiors of the employee 'FORD'

SELECT worker.ename, boss.ename FROM emp worker,emp boss WHERE worker.mgr = boss.empno AND worker.ename = 'FORD'

Subquery

 what is a subquery

Subqueries are select statements embedded in other sql statements, also known as nested queries.

 single-row subquery

A single-row subquery is a subquery statement that returns only one row of data

Think about it: show all employees in the same department as SMITH?

Train of thought:

1 query the department number of SMITH

Select deptno from emp WHERE ename = 'SMITH'

2 display

SELECT * FROM emp WHERE deptno = (select deptno from emp WHERE ename = 'SMITH')

The database is scanned from left to right when the sql is executed, and if there are parentheses, the parentheses will be executed first.

 multiline subquery

A multirow subquery is a subquery that returns multiple rows of data

Please think about: how to query the name, position, salary and department number of employees who are the same as those working in department 10

SELECT DISTINCT job FROM emp WHERE deptno = 10

SELECT * FROM emp WHERE job IN (SELECT DISTINCT job FROM emp WHERE deptno = 10)

(note: job=.., cannot be used because the equal sign = is one-to-one)

 uses the all operator in multiple-row subqueries

Oracle notes

eleven

Question: how do I display the name, salary and department number of an employee whose salary is higher than that of all employees in department 30?

SELECT ename, sal, deptno FROM emp WHERE sal > all (SELECT sal FROM emp WHERE deptno = 30)

Extension requirements:

Let's think about whether there is any other way to inquire.

SELECT ename, sal, deptno FROM emp WHERE sal > (SELECT MAX (sal) FROM emp WHERE deptno = 30)

In terms of execution efficiency, the function is much higher.

 uses the any operator in multiple-row subqueries

Question: how to display the name, salary and department number of an employee whose salary is higher than that of any employee in department 30?

SELECT ename, sal, deptno FROM emp WHERE sal > ANY (SELECT sal FROM emp WHERE deptno = 30)

Extension requirements:

Let's think about whether there is any other way to inquire.

SELECT ename, sal, deptno FROM emp WHERE sal > (SELECT min (sal) FROM emp WHERE deptno = 30)

 multi-column subquery

Single-row subquery means that subqueries only return single-column and single-row data, while multi-row subqueries return single-column and multi-row data, all for single columns, and multi-column subqueries refer to

A subquery statement that returns multiple columns of data.

Think about how to query all employees who have exactly the same department and position as SMITH.

SELECT deptno, job FROM emp WHERE ename = 'SMITH'

SELECT * FROM emp WHERE (deptno, job) = (SELECT deptno, job FROM emp WHERE ename = 'SMITH')

 uses subqueries in the from clause

Please think about how to display information about employees who are higher than the average salary of their own department.

Train of thought:

1. Find out the average salary and department number of each department

SELECT deptno, AVG (sal) mysal FROM emp GROUP by deptno

two。 Think of the above query results as a child table

SELECT e.ename, e.deptno, e.sal, ds.mysal FROM emp e, (SELECT deptno, AVG (sal) mysal FROM emp GROUP by deptno) ds

WHERE e.deptno = ds.deptno AND e.sal > ds.mysal

How to measure the level of a programmer?

Network processing capacity, database, program code optimization program should be very efficient

Small summary:

It should be noted here that when a subquery is used in the from clause, the subquery is treated as a view, so it is called an embedded view, when in the from clause

When using a subquery, you must assign an alias to the subquery.

Note: aliases cannot be as, such as SELECT e.ename, e.deptno, e.sal, ds.mysal FROM emp e, (SELECT deptno, AVG (sal) mysal FROM)

Emp GROUP by deptno) as ds WHERE e.deptno = ds.deptno AND e.sal > ds.mysal

Do not add as before ds, or you will report an error (when aliasing the table, you cannot add as;, but if you alias the column, you can add as).

 paging query

Take out according to the employee's id number in ascending order.

There are three ways to page oracle.

1. Score according to rowid

Select * from t_xiaoxi where rowid in (select rid from (select rownum rn, rid from (select rowid rid, cid from)

T_xiaoxi order by cid desc) where rownum9980) order by cid desc

Execution time 0.03 seconds

two。 Divide according to the analysis function

Select * from (select T. over, row_number () over (order by cid desc) rk from t_xiaoxi t) where rk9980

Execution time 1.01 seconds

3. Divide according to rownum

Oracle notes

twelve

Select * from (select t. Rn from (select * from t_xiaoxi order by cid desc) t where rownum9980

Execution time 0.1 second

T_xiaoxi is the name of the table, and cid is the key field of the table. Taking the 9981-9999 records sorted by cid in descending order, the t_xiaoxi table has more than 70000 records.

I feel that 1 is the most efficient, 3 is the second, and 2 is the worst.

/ / the paged query okokok that passed the test

Select * from (select a1. Where rownum=5, rownum rn from (select ename,job from emp) A1)

The following is the main introduction of the third: according to rownum

1. Rownum paging

SELECT * FROM emp

two。 Show rownum [assigned by oracle]

SELECT, ROWNUM rn FROM (SELECT * FROM emp) e

Rn is equivalent to the ID number of the line assigned by Oracle

3. Pick out 6-10 records

Find out 1-10 records first.

SELECT, ROWNUM rn FROM (SELECT * FROM emp) e WHERE ROWNUM = 6 is not allowed.

4. Then find out 6-10 records.

SELECT * FROM (SELECT E. FROM emp, ROWNUM rn FROM (SELECT * FROM emp) e WHERE ROWNUM = 6

5. Several query changes

a. Specify the query column, only need to modify the innermost subquery

Only check the employee's serial number and salary.

SELECT * FROM (SELECT. E. Stories, ROWNUM rn FROM (SELECT ename, sal FROM emp) e WHERE ROWNUM = 6

b. Sort the query, only need to modify the innermost subquery

Query 6-10 pieces of data after wage ranking

SELECT * FROM (SELECT. E. Stories, ROWNUM rn FROM (SELECT ename, sal FROM emp ORDER by sal) e WHERE ROWNUM = 6

 creates a new table with query results

This command is a quick way to create a table

CREATE TABLE mytable (id, name, sal, job, deptno) as SELECT empno, ename, sal, job, deptno FROM emp

After the creation, desc mytable; and select * from mytable; will see what the result is.

Merge query

 merge query

Sometimes in practical applications, in order to merge the results of multiple select statements, you can use the set operator symbol union,union all,intersect,minus

It is mostly used in the data bureau library with large amount of data, and the running speed is fast.

1)。 Union

This operator is used to obtain the union of two result sets. When you use this operator, duplicate rows in the result set are automatically removed.

SELECT ename, sal, job FROM emp WHERE sal > 2500

UNION

SELECT ename, sal, job FROM emp WHERE job = 'MANAGER'

2). Union all

This operator is similar to union, but it does not unrepeat rows and does not sort.

SELECT ename, sal, job FROM emp WHERE sal > 2500

UNION ALL

SELECT ename, sal, job FROM emp WHERE job = 'MANAGER'

Oracle notes

thirteen

This operator is used to obtain the union of two result sets. When you use this operator, duplicate rows in the result set are automatically removed.

3)。 Intersect

Use this operator to get the intersection of two result sets.

SELECT ename, sal, job FROM emp WHERE sal > 2500

INTERSECT

SELECT ename, sal, job FROM emp WHERE job = 'MANAGER'

4)。 Minus

Use the change operator to get the difference between the two result sets, which displays only the data that exists in the first set, not in the second set.

SELECT ename, sal, job FROM emp WHERE sal > 2500

MINUS

SELECT ename, sal, job FROM emp WHERE job = 'MANAGER'

(MINUS means subtraction)

On how to achieve select operations in oracle to share here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.