Explain Oracle subqueries, set operations, data processing, transactions, RowNum instances, and rownum instances

Explain Oracle subqueries, set operations, data processing, transactions, RowNum instances, and rownum instances
Subquery

Explanation: A subquery is a nested query statement in a query statement. The condition for a primary query is subquery.

Case 1:

To find information about employees with higher salaries than employees A, you need to first check the salaries of employees A and the information about employees with higher salaries. There are two steps.

Subquery is used to solve the problem step by step.

SELECT *FROM EMPWHERE SAL > (SELECT SAL             FROM EMP             WHERE ENAME = 'SCOTT');

Notes:

1. Reasonable Writing Style

2. Brackets

3. subqueries can be used after where select having from in the primary query.

Where: subquery as a condition

Select: In a function, only single-number subqueries are allowed.

Having: filtering condition (Having can only follow the function)

From: as another table

4. subqueries cannot be used after group.

5. Emphasize subqueries after from

6. The primary query and subquery can be different from the same table, as long as the results returned by the subquery can be used by the primary query.

7. Generally, subqueries are not sorted. However, in top-n analysis, subqueries must be sorted.

8. Generally, the subquery is executed first and then the primary query is executed.

9. single-row subqueries can only use single-row operators. multi-row subqueries can only use multiple-row operators.

10. null in the subquery

SQL optimization:

1. the query statement must contain segments.

2. The where statement runs from right to left.

3. having is first grouped in filtering. Where is first filtered in the group. Select where first

4. Select multi-Table query and subquery and select multi-Table query. Subqueries are converted to multi-table queries.

Returns only one row using the single row comparison operator.

Operator	Meaning
=	Equal
>	Greater
> =	Greater than or equal
<	Less
<=	Less than or equal
<>	Not equal

Note: The result set of a single-row function does not allow many rows. Otherwise, the following error is returned:

ERROR at line 4:ORA-01427:single - row subquery returns more than one row

Multi-row subquery returns multiple rows using the multi-row comparison operator

Operator	Description
IN	Equal to any
ANY	Compare with any value returned by the subquery
ALL	Compare with all values returned by the subquery

Any one IN the IN list

-- In collection -- query the employees whose department names are SALES and ACCOUNTING select * from empwhere deptno in (select deptno from dept where dname = 'sales' or dname = 'accounting ');

ANY and ANY value are larger than the minimum value in this department.

-- Any: Compare with any value in the set -- query the information about employees with higher salaries than any employee in department 30. select * from empwhere sal> any (select sal from emp where deptno = 30 );

select *from empwhere sal > (select min(sal) from emp where deptno=30)

All is equivalent to the maximum value.

-- All: Compare with all values in the set -- Query Information about employees with higher salaries than all employees in department 30 select * from empwhere sal> all (select sal from emp where deptno = 30 );

select *from empwhere sal > (select max(sal) from emp where deptno=30)

 
Set operation

Difference between Union and Union All

Union: value, in deduplication.

Union All: value, not heavy.

SQL Optimization

1. the query statement must contain segments.

2. The where statement runs from right to left.

3. having is first grouped in filtering. Where is first filtered in the group. Select where first

4. Select multi-Table query and subquery. Subqueries are converted to multi-table queries.

5. Try to use union all

Union = union all + distinct

Enhanced Group by statements

6. SQL principles do not use set operations as far as possible

 

About Set Operations

1. Each set involved in the calculation must have the same number of columns and the same type.

2. Use the first set as the final Header

3. order by is always at the end

The number of computing columns in the set must be the same. If the number of columns is not equal, add null.

select deptno,job,sum(sal) from emp group bydeptno,jobunionselectdeptno,to_char(null),sum(sal) from emp group by deptnounionselectto_number(null),to_char(null),sum(sal) from emp;

SQL Execution time switch DOS command

Set time on

Set timing off

Data processing SQL type

1. DML (data manipulation language): insert update delete select

Sometimes the query statement is separately divided into DQL

2. DDL (data definition language ):

Create table, alter table, droptable, truncate table (clear table)

View, sequence, index, and synonym)

3. DCL (data control language ):

Grant (authorized) revoke (revoking permissions)

Example:

The address character can be an add, field, table, or condition. (Submit to view data)

Insert intoemp(empno,ename) values(&empno,&ename);

Use the query statement to add multiple data records at a time.

Insert intoemp(empno,ename)Select empno,enamefrom emp;

Create a table

Data is also added by default. If the Condition Statement is followed by false, data is not added.

-- Insert multiple data entries at a time. create table EMP10 as select * from emp where 1 = 2;

Add Table Data

You can add specified columns separately, but remember to add columns equal to the queried columns.

No value column is null.

Massive Data insertion

1. Data Pump (PLSQL program: dbms_datapump)

2. SQL * Loader Tool

3. External table

Update table data

1. The updated data is a single row result set.

2. Consider that the returned result set is null.

Delete statement

Differences between delete and truncate:

1. delete one by one; truncate destroys the table and then recreates the table.

2. (*) delete is DML (rollback is allowed) truncate is DDL (rollback is not allowed)

3. delete will not release space. truncate will

4. delete can flash back (flashback) truncate cannot

5. delete will generate fragments. truncate will not

Drop, delete, and truncate are different.

1. the drop statement will delete the constraints, triggers, and indexes that the table structure is dependent on. Stored Procedures/functions that depend on the table will be retained, but the status changes to invalid.

drop table xx

2. truncate destroys the table and then recreates it.

truncate table xx

3. delete only deletes data.

delete table xx

Fragment

Our computer will leave system fragments When deleting files, which is equivalent to a blank part. In the attachment of the system, you can find the fragment tool to identify the fragment.

There are also fragments in the table. We need to use truncate to delete the reconstruction table and then import the table data to clear the fragments.

Transactions

Mark of transactions in Oracle

1. Start flag: The first DML statement in the transaction

2. End flag: Submit an explicit commit

Exit, DDL, DCL implicitly

Roll back an explicit rollback

Implicitly exits abnormally, powers down, and goes down

The transaction command in the command line:

Savepoint a; -- save transaction point.

Rollback to savepoint a; -- roll back the transaction to transaction point.

Commit; -- submit

Database isolation level

For multiple transactions running at the same time, when these transactions access the same data in the database, if the necessary isolation mechanism is not adopted, various concurrency problems will occur:

Dirty read: one transaction reads the uncommitted data of another transaction.

Repeatable read: one transaction reads the committed update data of another transaction, resulting in inconsistent query results.

Virtual read: one transaction reads the insert data committed by another transaction, resulting in inconsistent query structures.

Database transaction isolation: the database system must be able to isolate and concurrently run various transactions so that they do not affect each other and avoid various concurrency problems.

A transaction is isolated from other transactions to an isolation level. The database specifies multiple transaction isolation levels. Different isolation sectors correspond to different levels of interference. The higher the isolation level, the better the data consistency, but the weaker the concurrency.

Oracle clearly supports the serializable and read committed transaction isolation levels defined in ANSI/ISO SQL92. Oracle also provides its own unique transaction isolation level: read only.

Therefore, Oracle supports three transaction isolation levels:

1. serializable

2. read committed

3. read only

The default isolation level of Oracle is read committed.

How to view the database isolation level:

SELECT * FROM dual FOR UPDATE;

SELECT s.sid, s.serial#,   CASE BITAND(t.flag, POWER(2, 28))          WHEN 0 THEN 'READ COMMITTED'          ELSE 'SERIALIZABLE'       END AS isolation_level    FROM v$transaction t    JOIN v$session s ON t.addr = s.taddr AND s.sid = sys_context('USERENV', 'SID');

How to modify the database isolation level:

SET TRANSACTION ISOLATION LEVEL [READ UNCOMMITTED|READ COMMITTED|REPEATABLE READ|SERIALIZABLE]

Summary

Through learning, I learned how to use DML statements to change data and transaction control. The main keywords are as follows:

Statement	Function
INSERT	Insert
UPDATE	Correction
DELETE	Delete
COMMIT	Submit
SAVEPOINT	Save point
ROLLBACK	Rollback

Problem

Question 1:

Rownum

About rownum

1. rownum is always generated in the default order.

2. rownum can only be used <=; cannot be used> =.

3. rownum starts from 1 forever.

Rownum can only be smaller than or greater than, because it is a two-dimensional table. During the query, it is the row number that is added to each row of data when querying each row, and the first behavior is 1, if you are greater than 2, then he will go back to compare the row number greater than 1. If not found, he will delete it and continue the comparison. But if you delete it here, the row number will be 1, enter the endless loop.

How to Use rownum for paging?

Query a table with rownum as a sub-table. The base table then performs conditional Filtering Based on the rownum of the sub-table. The rownum can be greater than or less

select * from   (select rownum r,e1.*        from (select * from emp order by sal) e1          where rownum <=8      ) where r >=5;

Table Category

Standard table, index table, temporary table

Temporary table:

1. Manual: create global temporary table *****

2. Automatic: Sorting

Feature: The table is automatically deleted when the transaction or session ends.

 

Temporary table: The following is a session-based table. Once the link is disconnected or the session is closed, the data disappears.

create global temporary table temptest1(tid number,tname varchar2(20))on commit preserve rows;

Temporary table: The following is a transaction-based table. Once a transaction is committed, the data will be deleted.

create global temporary table temptest1 (tidnumber,tname varchar2(20))on commit delete rows;

A temporary table should be used in statistical reports. Sometimes, we only need a temporary table to calculate the data and the final result is displayed, the data can be deleted. We only need results, not data processes.

The following figure shows the standard table on the left. We will give it rownum. Then sort by order by. The table mentioned above is a temporary table, and the table on the right is a temporary table. When sorting a table with rownum, we can see a temporary table, that is, the right table. The order is disordered. But the real table is the table on the left, and its order is always there. It is only after sorting to the temporary table that the order has changed.

Conclusion:

When using a subquery, You can reference the columns in the master table in the where condition.

This mainly involves the running sequence of a query statement. First run from, then run where, and finally run select. Therefore, in a general query statement, where cannot read the value assignment of select. Here, from runs first and assigns values first, so you can read it.

-- Related subquery: Pass the values in the primary query as parameters to the subquery select empno, ename, sal, (select avg (sal) from emp where deptno = e. deptno) avgsalfrom emp ewhere sal> (select avg (sal) from emp where deptno = e. deptno );

Summary:

It is mainly used in multiple groups of functions, such as using conditional statements in avg, such as case when then else

Row-to-column conversion:

select wm_concat(name) name from test;

Between...

BETWEEN value1 AND value2