Oracle Study Notes (5)

Total combination set:
Group by with rollup/cube
Grouping sets
Subqueries are executed in the following ways: Standard subqueries and associated subqueries
Standard subquery: Nested subquery
Scalar Value Query
Case with then
Use exits to query
Select user_id, user_name from tb_001 tb
Where [not] exists (select 'x'
From tb_001
Where manager_id = tb. user_id)
Associated update
Update table1 alias1
Set column = (select expression
From table2 alias2
Where alias1.column = alias2.column );
Delete Association
Delete from table1 alias11
Where column operator
(Select expression
From table2 alias2
Where alias1.column = alias2.column );
Layered Structure
Hierarchical search
Select [level], column, expr...
From table
[Where with condition (s)]
[Connect by prior condition (s)]
Connect by prior column1 = column2
Top down:
Colum1 = parentkey
Column2 = childkey
Bottom up:
Column1 = childkey
Column2 = parentkey

Insert statement
Insert into table [(column [, column...])]
Values (value [, value...])
Modify statement update statement
Update table
Set column = value [, column = value,...]
[Where condition];

Multitable Insert Statements)

Insert [all] [conditional_insert_clause]
[Insert_cmd_cause values_clause] (subquery)
Conditional_insert_clause
[All] [first]
[When condition then] [insert_assist_clause values_clause]
[Else] [insert_into + clause values_clause]
Column:
Intsert all
Into tb_user values (id, uid, upwd)
Into tb_user_info values (id, name, sex, age)
Select id, uid, upwd, name, sex, age
From tb_u_10
Where id> 10000;
Insert multiple rows to be specified
Insert all
When id & gt; 11000 then
Into tb_user values (id, uid, upwd)
When age> 40 then
Into tb_user_info values (id, name, sex, age)
Select id, uid, upwd, name, sex, age
From tb_u_10
Where id> 10000;
Insert first
When conditional then
Into...
When conditional then
Into...
Else
Into...
Select...
Create an index
Cretate table t_10
(Id number (6) not null primary key using index (create index index1 on t_10 (id )),
Name varchar2 (50 ));
Query Indexes
Select * from user_indexes;