Oracle Study Notes (2)

1. multiline Functions, also known as Group Functions and Aggregate Functions

1. Types of Group Functions
Avg, count, max, min, stddev, sum, variance
Avg average
Select avg (nvl (column 1, 0) from table 1
Count calculates the number of rows
Aggregate functions are not allowed in the where condition, but having avg (column 1)> 1000 can be used.
Having plays the same role as where.

Ii. subquery Subqueries

Query the first 10 rows of data
Oracle: select * from table name where rownum <= 10;
SQL: select top 10 * from Table Name
Single Row subquery
Select * from table 1 where Payroll column 1> (select avg (payroll column 1) from table 1)
Multi-row subquery
Select * from table 1 where salary column 1 in (select min (salary column 1) from table 1 group by department column)

Iii. Custom Variables

Set verify on/off
Show all
Help show/set

Column lie justify left

Iv. Data operation statements

1. insert statement
Insert data to table 2
Oracle: insert into (select column 1, column 2 from table 2) values ('xxx', 'xxx ');
Oracle/SQL: insert into (column 1, column 2) values ('xxx', 'xxx ');
Copy data from another table
Oracle/SQL: insert into table (column 1, column 2) select column 1, column 2 from table 2

2. update statement
All are: update table set column1 = '...' [, column2 = '...'] where...
Modify embedded subquery
Update table set column1 = (select column2 form table where columnid = 1) where column1 = '...'

Delete statement
Delete [from] table [where condition]

Merge statement
Oracle:
Merge into Table 1 a using Table 2 B on (a. id = B. id)
When matched then
Update set
A. name = B. name,
A. other = B. other
When not matched then
Insert values (B. id, B. name, B. other );
SQL: Merge insert and update
Method 1:
Declare @ ROWCOUNT int
Set @ ROWCOUNT = (select count (*) from tb_name where name1 = '5 ')
If @ ROWCOUNT! = 0
Update tb_name set name2 = '2013' where name1 = '5'
Else
Insert into tb_name (name1, name2) values ('5', 'insert ')
Method 2:
Update tb_name set name2 = '2013' where name1 = '6'
If @ ROWCOUNT = 0
Insert into tb_name (name1, name2) values ('6', 'insert ')

5. Transactions: implicit and explicit transactions

Commit a transaction
Rollback
Locking lock
The concurrency system is automatically locked and automatically unlocked after the transaction is committed or rolled back.