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

How to query operation subquery in oracle? I believe that many inexperienced people are at a loss about this, so this article summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

1. Subquery syntax

SELECT select_listFROM tableWHERE expr operator (SELECT select_listFROM table)

The subquery is executed once before the main query.

The results of the subquery are used by the main query.

Select ename from emp where sal > (select sal from emp where ename='SCOTT')

(* Note: the subquery should be contained in parentheses and placed to the right of the comparison condition. Single-line operators correspond to single-row subqueries, and multi-line operators correspond to multiple-row subqueries.)

A single-row subquery that returns only one row, using the single-line comparator (> =

= (select min (sal) from emp)

Multi-row subquery, returning multiple rows, using multi-line comparator (IN ANY ALL)

-- query employee information with higher salary than anyone in department 10, select ename, sal from emp where sal > any (select sal from emp where deptno = 10);-- query employee information with higher salary than everyone in department 20, select ename, sal from emp where sal > all (select sal from emp where deptno = 20);-- query employee information that is not the boss select ename from emp where empno not in (select mgr from emp)

II. Set operation

Union set

The UNION operator returns all records after the duplicate elements are removed from two collections.

UNION ALL returns all records for both collections, including duplicates.

Intersection

The INTERSECT operator returns records that belong to both sets

-- return employee information select ename, salfrom empwhere sal between 1000 and 1000intersectselect ename, salfrom empwhere sal between 900 and 1200 for wages between 500-500 and 900-1200

Difference set

MINUS returns records that belong to the first collection but not to the second collection.

-- returns the employee information select ename, salfrom empwhere sal between 500 and 1000minusselect ename, salfrom empwhere sal between 900 and 1200, whose salary ranges from 500 to 1000, but not from 900 to 1200.

Considerations for the use of collections

1. The type and number of parameters in the select statement are the same.

2. You can use parentheses to change the order in which the collection is executed.

3. If there is an order by, it must be placed after the last query statement.

4. The set operation uses the header of the first statement as the header.

III. Data manipulation language

Insert data

INSERT INTO table [(column [, column...])] VALUES (value [, value...]); insert into dept (deptno,dname,loc) values (50)

Copy data from other tables

Insert into dept (deptno, dname, loc) select 60, dname, loc from dept where deptno = 10

Update data

UPDATE tableSET column=value [, column=value,...] [WHERE codition]-- Update a piece of data update empset sal=sal+100 where empno=7369;--update using a subquery update empset sal= (select max (sal) from emp) where empno= (select empno from emp where sal= (select min (sal) from emp))

Delete data

DELETE [FROM] table [WHERE condition];-- Delete a data delete from dept where deptno=60

Delete and truncate

1. Delete the data in the table.

2. Delete operations can be rollback and flashback.

3. Delete may produce fragments and do not release space.

4. Truncate emptying table.

IV. Database transactions

A database transaction consists of the following parts:

1. One or more DML statements

2. A DDL data definition statement

3. A DCL data control statement

Start with the execution of the first DML statement

End with one of the following:

End of display: commit rollback

Implicit end (autocommit): DDL statement, DCL statement, exit (transaction exits normally)

Implicit rollback (system exception ends): close window, crash, power down

Advantages of commit and rollback statements

1. Ensure data integrity.

2. Preview the data changes before they are submitted.

3. Grouping logically related operations.

Roll back to the reservation point

Use the save point statement to create a save point in the current transaction.

Use the rollback to savepoint statement to roll back to the SavePoint you created.

Update emp set sal=sal+100 where empno=7369;savepoint update_empno7369;delete from emp where empno=7369;rollback to update_empno7369

Create and manage tables

Common database objects

As follows:

A basic collection of data stores for a table, consisting of rows and columns.

A collection of logically related data extracted by a view from a table.

The sequence provides regular values.

Indexing improves the efficiency of queries.

A synonym gives an alias to an object.

Create a tabl

CREATE TABLE [schema.] table (column datatype [DEFAULT expr] [,...]); create table test (id number (12), name varchar2 (32))

Create a table through a subquery

CREATE TABLE table [(column, column...)] AS subquery;create table test2 as select empno,ename from emp where sal > 1000

Modify the table

-- add column ALTER TABLE tableADD (column datatype [DEFAULT expr] [, column datatype]...);-- add info column alter table test add (info varchar2 default'');-- modify column ALTER TABLE tableMODIFY (column datatype [DEFAULT expr] [, column datatype]...);-- modify info column alter table test modify (info varchar2 (64) default');-- Delete column ALTER TABLE tableDROP column (column);-- Delete info column alter table test drop column info -- modify column name ALTER TABLE tablerename column old_column_name to new_column_name;-- modify name column name alter table test rename column name to name2

Delete tabl

1. Data and structures are deleted

2. All running related things are submitted

3. All relevant indexes are deleted

4. DROP TABLE statements cannot be rolled back, but can be flashed back.

Drop table test

Change the name of the object

Rename dept to newDept

Clear the table

1. Delete all data in the table.

2. Release the storage space of the table.

3. Truncate cannot be rolled back.

After reading the above content in truncate table test;, have you mastered the method of how to query and operate subqueries in oracle? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

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.