Oracle 常用语句档案（二）

Displaying Data from Multiple Tables

Types of Joins

SQL:1999  Compliant Joins:

--Cross joins

--Natural joins

--Using clause

--Full or two sided outer joins

--Arbitrary join conditions for outer joins

Syntax：

Select tableA.column,tableB.column from tableA

[CROSS JOIN tableB] |

[NATURAL JOIN tableB] |

[JOIN tableB USING (column_name)] |

[JOIN tableB ON (tableA.column_name = tableB.column_name)] |

[LEFT | RIGHT | FULL OUTER JOIN tableB ON (tableA.column_name = tableB.column_name)];

 

Oracle Proprietary Joins

--Equijoin

select a.columnA, b.columnB,c.columnC from tableA a,tableB b,tableC c where a.id=b.id and b.id=c.id;

--Non-euqijoins

select a.columnA,b.columnB from tableA a,tableB b where a.salary between b.lowestsalary and b.highestsalary;

--Outer join

select a.column,b.column from tableA a,tableB b where a.column(+) = b.column;

select a.column,b.column from tableA a,tableB b where a.column = b.column(+);

--Self join

select a.columnA, a.columnB from tableA a,tableA b where a.columnC=b.columnC;

 

Cartesian Products   

select ColumnA,ColumnB,ColumnC from tableA tableB;

select columnA,columnB from tableA cross jion tableB;

 

Aggregating Data Using Group Functions

Types of group functions: avg,count,max,min,sum,stddev,variance

select count(distinct columnA) from dual;

select avg(nvl(columnA,0) from tale;

 

using the group by clause on multiple columns

select avg(salary) from tableA group by columnA,columnB;

Syntax:

SELECT column,group_function  FROM table  [WHERE condition]

[group by group_by_expression]  [having   group_condition]   [order by  column];

 

Note:you can’t use where clause to restrict groups but you can use the having clause to restrict groups.

Error: select department_id,avg(salary) from employee where avg(salary)>8000 group by department_id;

Right:select department_id,avg(salary) from employee group by department_id having avg(salary)>8000;

 

nesting group functions :

select max(avg(salary)) from employees group by department_id;

 

Subqueries

SELECT select_list FROM table WHERE expr operator 

              (SELECT select _list FROM table);

Operator : IN , ANY  , ALL

IN: Equal to any member in the list

ANY:compare value to each value returned by the subquery

ALL:compare value to every value returned by the subquery

<ANY  means less than the maximum

>ANY  means more than the minimum

=ANY  is equivalent  to IN

<ALL  means less than the minimum

>ALL  means more than the maximum

The NOT operator can be used with IN,ANY and ALL operators.

If one of the values returned by the inner query is a null value, and hence the entire query returns no rows. The reason is that all conditions that compare a null value result is a null.So whenever null values are likely to be part of the results set of a subquery,do not use the NOT IN operator.The NOT IN operator is equivalent to  <>ALL.

 

Manipulating Data

Data Manipulation Language

A DML statement is executed when you:

-Add new rows to a table

-Modify exsitng rows in a table

-Remove exsting rows from a table

A transaction consists of a collection of DML statements that form a logical unit of work.

 

INSERT INTO table [(column [,column …])] VALUES (value [,value…]);

INSERT INTO table( login_time) values ( TO_DATE(DATE(’FEB 3, 1999’, ’MON DD, YYYY’));

INSERT ITNO (SELECT columnA,columnB from tableA where id=50) VALUES(avariableA,avariableB);

INSERT INTO (columnA) values (default);

UPDATE table SET column=value[,column =value,…] [WHERE condition];

DELETE [FROM] table [WHERE condition];

WITH CHECK OPTION keyword

The with check option keyword prohibits you from changing rows that are not in the subquery

The MERGE statement

Provides the ability to conditionally update or insert data into a database table

performs an update if the row exists,and an insert if it is a new row;

--avoids separate updates

--Increases performance and ease of use

--Is useful in data warehousing applications

Syntax:

MERGE INTO  table_name table_alias

USING  (table|view|sub_query) alias

ON (join condition)

WHEN MATCHED THEN

UPDATE SET

col1 = col_vall,

col2 = col_val2

WHEN NOT MATCHED THEN

INSERT (column_list)

VALUES (column_values);