oracle sql 基础（二）：数据操纵语言（select 语句）

狐狸情

　　为了从数据库中查询数据，你需要用SQL语言中使用最多的SELECT语句。我们分别介绍SELECT语句的基础语法、子查询、从多表中查询数据，然后再进行实例解析。
　　一、SELECT语句的基础语法
　　SELECT语句就像叠加在数据库表上的过滤器，即选择查询用于定位数据库特定的列和行。下面是SELECT语句的基础语法。
　　

SELECT [ALL|DISTINCT SELECT_LIST　　
FROM {table_name|view_name}
　　
[WHERE search_condition
]　　
[GROUP BY group_by_expression]
　　
[HAVING search_condition]
　　
[ORDER BY order_by_expression [ASC|DESC]]
　　

　　用中括号[]括起来的表示可选的，用大括号{}括起来的表示必须从中选择其中的一个。下面我们对SELECT语句的每个子句进行详细的解析。
　　1.SELECT子句
　　（1）SELECT子句：指定由查询返回的列，可以一次返回多个列，用“,”分开即可。可用空格或关键字AS定义列别名。
　　（2）SELECT子句中，应避免使用通配符 *，尽可能查询符合某条件的数据，以提高查询效率。
　　（3）关键字DISTINCT的作用为去重，默认为ALL，既显示所有数据。
　　2.FROM子句
　　（1）FROM子句：用于指定所要查询的表或视图，用“,”分隔不同的表或视图，空格或关键字AS定义列别名。
　　（2）FROM子句完整格式：FROM user_name.table.name;(用户名.表名)
　　3.WHERE子句
　　（1）WHERE子句：指定被检索表中返回行的搜索条件，若无此子句，则默认所有行都满足条件。
　　（2）比较运算符：>、<、>=、<=、=、<>、!=、BETWEEN...AND...
　　（3）逻辑运算符：AND、OR、NOT
　　（4）多行运算符：IN(set)、ANY(condition)、ALL(conditon)
　　（4）除了上述运算符，还有IS NULL/IS NOT NULL 条件，LIKE条件（% 通配所有字符，_ 配一个字符）
　　4.GROUP BY子句
　　（1）GROUP BY子句：把表中的行划分为组，然后你可以用组函数返回每一组的摘要信息。
　　（2）GROUP BY子句中可以包含多个列，不能使用列别名，不能使用WHERE来约束分组结果。
　　（3）组函数：AVG、SUM、MAX、MIN、COUNT、STDDEV（标准差）、VARIANCE（方差）。
　　5.HAVING子句
　　（1）HAVING子句：指定组或聚合的筛选条件，通常与GROUP BY子句混合使用。
　　（2）HAVING筛选器可以使用和WHERE相同的运算符，用于约束GROUP BY创建的分组。
　　6.ORDER BY子句
　　（1）ORDER BY子句：指定排序显示返回的行，ASC以升序排序（默认），DESC以降序排序。
　　（2）除非使用FOR UPDATE子句，ORDER BY子句应该放最后。
　　（3）ORDER BY子句可以指定一个表达式，一个别名作为排序条件，也可进行多列排序。
　　二、子查询
　　子查询是一个SELECT语句，它是嵌在另一个SELECT语句中的子句。使用子查询你可以用简单的语句构建功能强大的语句。当你需要从表中用依赖于表本身的数据选择行时它们是非常有用的。
　　你可以将子查询放在许多的SQL子句中，包括：WHERE子句、HAVING子句、FROM子句、CREATE VIEW语句、CREATE TABLE语句、UPDATE语句、INSERT语句的INTO子句和UPDATA语句的SET子句中。
　　一个子查询必须放在圆括号中。将子查询放在比较条件的右边以增加可读性。子查询根据查询结果可分为：单行子查询、多行子查询、多列子查询。
　　三、从多表中查询数据（多表连接）
　　从多表中查询数据也被称为多表连接，通过连接，可以从两个或多个表中根据各个表之间的逻辑关系来检索数据。ORACLE SQL 的连接类型有：内连接、外连接、自连接。内连接分为：等值连接、非等值连接。外连接分为：左外连接、右外连接、全外连接。除此之外，我们还会介绍下SQL99标准的连接语法。
　　在进行多表连接的过程中，使用表别名能加速数据库的访问，有助于保存SQL代码较小，并节省储存器。
　　1.内连接
　　当两个表中至少有一个行符合连接条件时，内部连接才能返回行，内连接消除了与另外一个表中不匹配的行。内连接分为等值连接和非等值连接。
　　（1）等值连接
　　等值连接是使用最多的一种连接方式，可以把等值连接想象成本来是一个大表，将其拆分为两个子表，并且两个子表都包含大表的某一列（也可能是多列），而等值连接就是将两个子表重新组合为一个大表。
　　主要语法：... WHERE table1.column=table2.column。
　　（2）非等值连接
　　一种非等值连接是一种不同于等值操作（=）的连接条件，如；...WHERE table1.column BETWEEN table2.min(column) AND table2.max(column)。
　　2.外连接
　　外连接会返回FROM子句中提到的至少一个表或视图中的所有行，只要这些行符合任何WHERE或HAVING搜索条件。将检索通过左外连接引用左表中的所有行，以及通过右外连接引用的右表中的所有行，在完全外连接中，将放回两个表的所有行。
　　（1）左外连接
　　数据列表包括了满足查询条件的左边表的所有行。为了显示table1中不满足条件的行，可用如下两种语句：

　　

#第一种方式　　
SELECT table1.column1,table2.column2
　　
FROM table1,table2
　　
WHERE table1.column1=table2.column2(+)
　　
#第二种方式
　　
SELECT table1.column1,table2.column2
　　
FROM table1 LEFT OUTER JOIN table2
　　
ON (table1.column1=table2.column2)
　　


LEFT OUTER JOIN　　（2）右外连接
　　数据列表包括了满足查询条件的右边表的所有行。为了显示table2中不满足条件的行，可用如下两种语句：  

　　

#第一种方式　　

SELECT table1.column1,table2.column2　　

FROM table1,table2　　

WHERE table1.column1(+)=table2.column2　　
#第二种方式
　　

SELECT table1.column1,table2.column2　　

FROM table1 RIGHT OUTER JOIN table2　　

ON (table1.column1=table2.column2)　　


RIGHT OUTER JOIN　　（3）全外连接
　　数据包含了所有满足查询条件的列，为了显示table1和table2中不满足条件的行，可用如下语句：

　　

SELECT table1.column1,table2.column2　　

FROM table1 FULL OUTER JOIN table2　　

ON (table1.column1=table2.column2)　　


FULL OUTER JOIN　　3.自连接
　　自连接就是连接的两个表均来自于同一个列，可以是等值连接或非等值连接。自连接是一种使用很少的连接形式，可以用子查询替换其功能。下面是一个自连接的示例。

　　

SELECT w.Last_Name || ' works for ' || m.Last_Name　　

FROM Employees w, Employees m　　

WHERE w.Manager_Id = m.Employee_Id;　　


SELF JOIN　　4.SQL1999连接语法
　　SQL1999标准，简称SQL99，是国际标准组织于1999年定义的SQL标准，前几个连接语法是ORACLE独有的，下面我们来介绍ORACLE中SQL99的连接语法。
　　（1）交叉连接
　　等价于两表无过滤条件的情况，返回满足查询条件记录的笛卡尔积运算的集合。
　　主要语法：...FROM table1 CROSS JOIN table2。
　　（2）自然连接
　　等价于等值连接，前提是两个表有相同名称的列。如果没有，则为交叉连接。如果列名称相同，但列类型不同，会报错，可以用USING子句来处理这种情况。
　　主要语法：...FROM table1 NATURAL JOIN table2。
　　（3）ON子句
　　等价于等值连接和非等值连接。对于自然连接的连接条件，基本上是带有相同名字的所有列的等值连接，为了指定任意条件，或者指定要连接的列，可以用ON子句来连接。下面是一个用ON子句创建三向连接的示例。

　　

Select Employee_Id, City, Department_Name　　

From Employees e　　

Join Departments d On d.Department_Id = e.Department_Id　　

Join Locations l On d.Location_Id = l.Location_Id;　　


ON 子句　　四、SELECT语句实例解析
　　为了便于直观理解，我们先介绍下SCOTT用户下的部门表（DEPT）、雇员表（EMP）、工资等级表（SALGRADE）的表结构。
　　1、部门表（DEPT）

序号
名称
类型
描述
1
DEPTNO
NUMBER(2)
表示部门编号，由两位数字组成
2
DNAME
VARCHAR2(14)
部门名称，最多由14个字符组成
3
LOC
VARCHAR2(13)
部门所在位置　　2、雇员表（EMP）

序号
名称
类型
描述
1
EMPNO
NUMBER(4)
雇员的编号，由四位数字所组成
2
ENAME
VARCHAR2(10)
雇员的名称，由十位字符组成
3
JOB
VARCHAR2(9)
雇员的职位
4
MGR
NUMBER(4)
雇员对应的领导编号
5
HIREDATE
DATE
雇员雇佣的日期
6
SAL
NUMBER(7,2)
基本工资，其中两位小数，五位整数
7
COMM
NUMBER(7,2)
奖金，拥金
8
DEPTNO
NUMBER(2)
雇员所在的部门编号　　3、工资等级表（SALGRADE）

序号
名称
类型
描述
1
GRADE
NUMBER
工资的等级
2
LOSAL
NUMBER
此等级的最低工资
3
HISAL
NUMBER
此等级的最高工资　　现在，我们要找出入职时间在1981年1月1日之后且工资等级在2-4的雇员，并对他们按部门编号进行分组，求出满足条件的部门编号(DEPTNO)、部门名称(DNAME)、员工数(NUM_EMPNO)、最低薪水(MIN_SAL)、最高薪水(MAX_SAL)、平均薪水(MEAN_SAL)、平均薪水所属工资等级(SAL_GRADE)，然后通过“工资等级”和“最高薪水”两个字段对查找结果进行排序。查询结果如下

　　该例相当于一个简单的数据建模过程，多次使用用了select语句中的子查询和多表连接功能，查询并整合了在特定需求下的信息。详细代码如下

　　

SELECT new_deptno deptno,　　dept.dname,      
　　num_empno,        
　　min_sal,         
　　max_sal,         
　　mean_sal,         
　　CASE
　　WHEN mean_sal between (select losal from salgrade where grade=1) and (select hisal from salgrade where grade=1)
　　THEN 1
　　WHEN mean_sal between (select losal from salgrade where grade=2) and (select hisal from salgrade where grade=2)
　　THEN 2
　　WHEN mean_sal between (select losal from salgrade where grade=3) and (select hisal from salgrade where grade=3)
　　THEN 3
　　WHEN mean_sal between (select losal from salgrade where grade=4) and (select hisal from salgrade where grade=4)
　　THEN 4
　　ELSE 5
　　END sal_grade         
　　
FROM
　　

　　
(
　　SELECT newemp.
　　deptno new_deptno,
　　count(distinct empno) num_empno,
　　min(sal) min_sal,
　　max(sal) max_sal,
　　avg(sal) mean_sal
　　FROM
　　
    (
　　SELECT * FROM emp
　　WHERE hiredate>To_Date('1981-1-1','yyyy-mm-dd')
　　AND sal between (SELECT losal from SALGRADE where GRADE=2) and (SELECT HISAL FROM SALGRADE WHERE GRADE=4)
　　) newemp
　　GROUP BY newemp.deptno
　　
)
　　
LEFT OUTER JOIN dept
　　
ON (new_deptno=dept.deptno)
　　
ORDER BY sal_grade,max_sal
　　
;
　　


SELECT语句实例

robin6621

111