Oracle 常用语句档案（二）

最新推荐文章于 2025-09-06 09:33:29 发布

weixin_34368949

最新推荐文章于 2025-09-06 09:33:29 发布

阅读量117

点赞数 1

CC 4.0 BY-SA版权

文章标签：数据库

原文链接：http://www.cnblogs.com/spirit/archive/2009/10/30/1593221.html

本文详细介绍了SQL中的各种联接类型，包括内联接、外联接等，并提供了具体的语法示例。此外，还讲解了如何使用子查询增强查询功能，以及数据操作语言(DML)的基本用法。

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];