Oracle Study Notes (2)

Source: Internet
Author: User
Recently, oracle has been used, so you should study the basics and sort out the materials, hoping to help your friends in need.

Recently, oracle has been used, so you should study the basics and sort out the materials, hoping to help your friends in need.

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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.