weishaoxiang

浏览: 93615 次
性别:
来自: 广西

最近访客更多访客>>

temfulX

wwd2010

13552249730

hoodzwh

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

ORACLE学习笔记系列（7）SQL语言分类

博客分类：

ORCLE

SQL语言的五大组成部分DQL DML DDL DCL TC

SQL语言共分为五大类：

数据查询语言（DATA QUERY LANGUAGE, DQL）,

数据操纵语言（DATA MANIPULATION LANGUAGE, DML）

数据定义语言（DATA DEFINITION LANGUAGE, DDL）

数据控制语言（DATA CONTROL LANGUAGE, DCL）

事务控制（TRANSACTION CONTROL, TC）

一、数据查询语言DQL

查询语句的一般结构：

单表查询

多表查询

嵌套查询

分组查询

集合查询

查询语句的一般结构

SELECT [ALL|DISTINCT]<目标列表达式>[，<目标列表达式>]···

FROM <表名或视图名>[，<表名或视图名>]···

[WHERE <条件表达式>]

[GROUP BY <列名1>[HAVING<条件表达式>]]

[ORDER BY <列名2>[ASC|DESC]]；

SQL语句的执行顺序

SELECT DNAME,MAX(SAL)-- 5 对列筛选（分组字段或聚集函数）

FROM EMP

LEFTOUTERJOIN DEPT -- 1 确定表

USING(DEPTNO)

WHERE DEPTNO >0-- 2 确定行（记录）

GROUPBY DNAME -- 3 将行分组

HAVINGMAX(COMM)ISNULLORMAX(COMM)>0-- 4 对组筛选

ORDERBY DNAME -- 6 对结果集排序

单表查询:

1、无条件：

SELECT中的<目标表达式>可以是表中的列，也可以是表达式，包括算术表达式、字符串常数、函数等。（字符串用单引号定界）

1. 使用算术表达式（+、-、*、/）

SQL>SELECT EMPNO, SAL *0.8FROM EMP;

2. 使用字符常量

SQL>SELECT EMPNO,'NAME IS :', ENAME FROM EMP;

3.使用函数

SQL>SELECT EMPNO,UPPER(ENAME)FROM EMP;

4. 改变列标题

SQL>SELECT EMPNO EMPLOYEENAME,SAL AS SALARY, JOB, DEPT FROM EMP;

5.使用连接字符串

SQL>SELECT ENAME ||':'||'1'||'month salary='|| SAL AS MONTHY FROM EMP

6.消除重复行

SQL>select [ALL] ename from emp;

SQL>selectDISTINCT ename from emp;

2、有条件：

WHERE常用的查询条件

查询条件	谓词
比较大小	=, >, <, >=, <=, < >
确定范围	BETWEEN AND，NOT BETWEEN AND
确定集合	IN，NOT IN
字符匹配	LIKE，NOT LIKE
空值	IS NULL，IS NOT NULL
多重条件	AND，OR

确定范围：

SQL>SELECT EMPNO, ENAME, SAL, DEPTNO FROM EMP WHERE DEPTNO BETWEEN10AND20;

确定集合：

SQL>SELECT EMPNO, ENAME, SAL, DEPTNO FROM EMP WHERE DEPTNO IN(10,30);

等价于：

SQL>SELECT EMPNO, ENAME, SAL, DEPTNO FROM EMP WHERE DEPTNO =10OR DEPTNO =30;

字符匹配：[NOT] LIKE ‘<匹配串>’

1.<匹配串>可以是一个完整的字符串，也可以含有通配符的字符串。通配符包括‘%’、‘_’。

2.%（百分号）代表任意长（长度为0）字符串。

3._（下划线）代表任意单个字符。

SQL>SELECT EMPNO, ENAME, SAL, DEPTNO FROM EMP WHERE ENAME LIKE'S%';

SQL>SELECT ENAME FROM EMP WHERE ENAME LIKE'_S%';

空值：涉及空值查询时使用IS NULL或 IS NOT NULL，这里的IS不能用=替代

多重条件:用逻辑运算符NOT、AND和OR来联结多个查询条件。

优先级：NOT、AND、OR（用户可以用括号改变优先级）。

IN谓词实际上是多个OR运算的缩写。

SELECT的交互查询：

使用替代变量，以“&”开头。

SQL>SELECT*FROM EMP WHERE ENAME ='&NAME';

3、排序： ASC升序排序，DESC降序排序

ORDERBY从句要放在 SELECT语句的最后。

表达式排序：

SQL>SELECT EMPNO, ENAME, SAL，SAL *12FROM EMP ORDERBY SAL *12DESC;

多列排序:

SQL>SELECT EMPNO, DEPTNO, SAL FROM EMP ORDERBY DEPTNO ASC, SAL DESC;

显示为按部门号升序，相同部门号内的工资降序排列。

别名排序：

SQL>SELECT EMPNO, ENAME,SAL *12 ANNSAL FROM EMP ORDERBY ANNSAL DESC;

多表查询

1. 等值查询:

在存在主外键关系的表中，使用等号建立表之间的连接

SELECT A.EMPNO, A.ENAME, B.DEPTNO, B.DNAME

FROM EMP A, DEPT B

WHERE A.DEPTNO = B.DEPTNO;

SQL99新标准:内连接(innerjoin)

SELECT A.EMPNO, A.ENAME, B.DEPTNO, B.DNAME

FROM EMP A

INNERJOIN DEPT B -- inner可省略

ON A.DEPTNO = B.DEPTNO;

2. 非等值查询:

使用非等号关系运算符进行多表的连接

SELECT A.ENAME, A.SAL, B.GRADE

FROM EMP A, SALGRADE B

WHERE A.SAL BETWEEN B.LOSAL AND B.HISAL;

3. 外连接(outerjoin):

类似等值查询，使用特定的符号(+)

(+)符号如果出现在where子句等号的左边，叫左外连接，左外连接会显示右表的全部数据；

(+)符号如果出现在where子句等号的右边，叫右外连接，右外连接会显示左表的全部数据；

SELECT A.EMPNO, A.ENAME, B.DEPTNO, B.DNAME

FROM EMP A, DEPT B

WHERE A.DEPTNO(+)= B.DEPTNO;

SELECT A.EMPNO, A.ENAME, B.DEPTNO, B.DNAME

FROM EMP A, DEPT B

WHERE A.DEPTNO = B.DEPTNO(+);

SQL99新标准：

左外连接：

SELECT A.EMPNO, A.ENAME, B.DEPTNO, B.DNAME

FROM EMP A

LEFTOUTERJOIN DEPT B -- outer可省略

ON A.DEPTNO = B.DEPTNO;

右外连接：

SELECT A.EMPNO, A.ENAME, B.DEPTNO, B.DNAME

FROM EMP A

RIGHTOUTERJOIN DEPT B -- outer可省略

ON A.DEPTNO = B.DEPTNO;

全外连接:外连接是在等值(内)连接的基础上将左表和右表的未匹配数据都加上

SELECT A.EMPNO, A.ENAME, B.DEPTNO, B.DNAME

FROM EMP A

FULLOUTERJOIN DEPT B -- outer可省略

ON A.DEPTNO = B.DEPTNO;

4. 自连接:

特殊的多表连接，连接的多个表来自于同一个表

SELECT A.EMPNO, A.ENAME, B.ENAME MANAGER

FROM EMP A, EMP B

WHERE A.MGR = B.EMPNO;

5. 自然连接(naturaljoin):

基于多个表有一个或多个相同字段名，且这些字段的类型相同

由oracle自行决定哪些列作为连接的条件:将不同表中的那些具有相同名称和数据类型的字段用等值连接起来。

SELECT A.ENAME, B.DNAME FROM EMP A NATURALJOIN DEPT B;

6. 交叉查询(crossjoin):笛卡尔集

SELECT A.ENAME, A.SAL, B.GRADE FROM EMP A, SALGRADE B;

7. join...using:

使用 USING可以在natural join时有多个列满足条件，指定具体的字段做等值连接

如果列名相同但是数据类型不同，这时不能使用natural join可以使用 using来连接。

当多列匹配时，using只能匹配一列。

在引用的列前面不能有表名或者表别名。

natural join和using 是相互排斥的

SELECT A.ENAME, B.DNAME FROM MYEMP A JOIN DEPT B USING(DEPTNO);

嵌套查询

概念：

 查询块：一个select-from-where语句称为一个查询块。

 嵌套查询：将一个查询块嵌套在另一个查询块中的查询，称为嵌套查询，也称为子查询。

处理步骤：

 一般由里向外进行处理。

注意：

子查询可以有多层，所存取的表可以是父查询没有存取的表；

子查询作为查询列时显示子查询选出的记录，其他情况子查询选出的记录不显示；

一个子查询必须放在圆括号中；

将子查询放在比较条件的右边以增加可读性；

子查询不包含 ORDER BY 子句，对一个 SELECT 语句只能用一个 ORDER BY 子句。

子查询位置：

可放在SELECT后面，子查询选出的记录作为列传显示；

可放在FROM后面，子查询选出的记录作为结果集的一部分；

可放在WHERE后面，子查询选出的记录作为条件表达式的一部分；

可放在HAVING后面，子查询选出的记录作为分组结果的过滤条件；

可放在ORDER BY后面，子查询选出的记录作为结果集的排序内容；

SQL>SELECT(SELECT DNAME FROM DEPT WHERE DEPT.DEPTNO='10') DNAME ,EMP.*FROM EMP;

SQL>SELECT*FROM(SELECT*FROM DEPT WHERE DEPT.DEPTNO='10') DEPT, EMP WHERE EMP.DEPTNO = DEPT.DEPTNO;

SQL>SELECT*FROM EMP WHERE EMP.DEPTNO = (SELECT DEPTNO FROM DEPT WHERE DEPT.DNAME ='SALES');

SQL>SELECT DEPTNO,AVG(SAL)FROM EMP GROUPBY DEPTNO HAVINGAVG(SAL)>(SELECTMIN(SAL)FROM EMP );

SQL>SELECT*FROM EMP ORDERBY(SELECT DEPTNO FROM DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO);

子查询的类型：

单行子查询：从内 SELECT语句只返回一行的查询，可采用单行比较符：=、<、>、<=、>=等；

多行子查询：从内 SELECT语句可返回多行的查询，需采用多行比较符：IN、>ANY、>ALL、<ANY、< ALL等。

相关子查询（Correlated Sub-QUERY）：相关子查询的执行依赖于外部查询的数据，外部查询执行一行，子查询就执行一次。

非相关子查询（Uncorrelated Sub-QUERY）：非相关子查询是独立于外部查询的子查询，子查询总共执行一次，执行完毕后将值传递给外部查询。

SQL>SELECT*FROM EMP WHERE EMP.DEPTNO =(SELECT DEPTNO FROM DEPT WHERE DEPT.DNAME ='SALES');

SQL>SELECT*FROM EMP WHERE EMP.DEPTNO IN(SELECT DEPTNO FROM DEPT );

SQL>SELECT*FROM EMP WHERE EMP.DEPTNO IN(SELECT DEPTNO FROM DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO);

SQL>SELECT*FROM EMP WHERE EMP.DEPTNO IN(SELECT DEPTNO FROM DEPT );

子查询返回结果对主查询的影响：

如果子查询返回的是零值，不会对主程序造成影响；

如果子查询返回的是空值，会影响主程序的返回值；

如果单行子查询返回的是多行，则会查询错误“单行子查询出现多行”；

SQL>SELECT*FROM EMP WHERE EMP.COMM IN(SELECT COMM FROM EMP);--有结果数据

SQL>SELECT*FROM EMP WHERE EMP.COMM IN(SELECTNULLFROM EMP);--无结果数据

SQL>SELECT*FROM EMP WHERE EMP.DEPTNO IN(10,0);--有结果数据

SQL>SELECT*FROM EMP WHERE EMP.DEPTNO IN(NULL);--无结果数据

SQL>SELECT*FROM EMP WHERE EMP.DEPTNO IN(10,NULL);--有结果数据

SQL>SELECT*FROM EMP WHERE EMP.DEPTNO NOTIN(NULL);--无结果数据

SQL>SELECT*FROM EMP WHERE EMP.DEPTNO NOTIN(10,NULL);--无结果数据

SQL>SELECT*FROM EMP WHERE EMP.DEPTNO =(SELECT DEPTNO FROM DEPT );--错误“单行子查询出现多行”

多列子查询适应于：成对比较；非成对比较。

非成对的子查询：

SELECT EMPLOYEE_ID, MANAGER_ID, DEPARTMENT_ID

FROM EMPLOYEES

WHERE(MANAGER_ID, DEPARTMENT_ID) IN

(SELECT MANAGER_ID, DEPARTMENT_ID

FROM EMPLOYEES

WHERE EMPLOYEE_ID IN(178,174))

AND EMPLOYEE_ID NOTIN(178,174);

只有要查询的东西和子查询返回的东西一一对应上了，查询条件才能满足，

如果有一个对应不上那么查询不会满足。

非成对的子查询：

SELECT EMPLOYEE_ID, MANAGER_ID, DEPARTMENT_ID

FROM EMPLOYEES

WHERE MANAGER_ID IN

(SELECT MANAGER_ID FROM EMPLOYEES WHERE EMPLOYEE_ID IN(174,141))

AND DEPARTMENT_ID IN

(SELECT DEPARTMENT_ID FROM EMPLOYEES WHERE EMPLOYEE_ID IN(174,141))

AND EMPLOYEE_ID NOTIN(174,141);

多行嵌套查询(子查询的结果是多行的)

多值比较运算符

in、exists、not in、not exists之间的区别

all、any（some替代）、not in、in之间的区别

来看下面这样一条SQL语句：

select*from student where20<all(select score from student)

其中，(select score from student)返回的是所有分数的记录集

只有当记录集中的所有分数都大于20后，才会执行前面的SQL语句。显然，任何一条记录都大于20.

如果把它改成50，则查不到任何数据，因为只要有一条记录小于50,条件都不成立。

但是，如果把all改面any就不一样了：

select*from student where50<any(select score from student)

它的意思是，只要记录集中的记录有一条大于50,条件就成立。

1、ALL与ANY的关系就是，AND与 OR的关系。

2、ANY与SOME等价，据说搞这两个不同的词出来是为了迁就英语语法。例如，在用=ANY的地方在（英语）语法上就应该是=SOME。 some是新版本的SQL中取代any的关键字，用法基本一样。

3、 IN与= ANY等价，均表示，变量在（子查询）列表之中，即 a IN(table B)表示 a =ANY B.b

4、 NOTIN与 <>ALL等价，而不等于<>ANY，前两者均表示，变量不在（子查询）列表之中，即 a NOTIN(table B)表示 a <>ALL B.b。而如果a <>ANY B.b，则只要任意一个b<>a就true了。

5、 IN与 EXISTS的性能区别主要来自，IN会编列子查询的每行记录，然后再返回，而EXISTS 则只要遇到第一个满足条件的记录就马上返回。

6、NOTIN与 NOTEXISTS并不能完全等价，只有当子查询中，select关键字后的字段有not null约束或者有这种暗示时才用NOT IN。

[例]查询所有雇员的工资都不低于1000的部门的所有人员信息

SQL>select ename,sal,job from emp where deptno notin(select deptno from emp where sal <1000);

SQL>select ename,sal,job from emp where deptno !=all(select deptno from emp where sal <1000);

结果

ENAME SAL JOB

---------------- ---------- ----------

CLARK 2450 MANAGER

KING 5000 PRESIDENT

MILLER 1300 CLERK

[例2]查找有工资在4000-5000之间的雇员所在部门的所有人员信息。

SQL> select ename,sal,job from emp where deptno =any(select deptno from emp where sal between4000and5000);

结果

 ENAME SAL JOB

 ---------------- ---------- -----------------

 CLARK 2450 MANAGER

 KING 5000 PRESIDENT

 MILLER 1300 CLERK

[例3]查找那些雇员的部门号在部门表中没有的雇员的信息。

SQL>select ename, sal, job from emp where notexists(select * from dept where deptno = emp.deptno);

分组查询

（一般句中有每个、各个字样的要用到分组查询）

Having后一般是统计函数,不能是emp.depno=dept.depno这样的等式

常用统计函数常与分组查询一块用

常用统计函数（一般where后不用统计函数）

函数AVG 和 SUM 应用于数值型

MIN 和 MAX 可用于任意类型.

函数AVG 和 SUM 应用于数值型

COUNT(*) 返回符合条件的记录数.(包括含null的所有行数)

COUNT(expr) 返回的非NULL的行数.

集合函数忽略列中的 null 值(COUTN除外).

SQL> SELECT AVG(comm) FROM emp;

在组函数中使用 NVL函数，NVL 函数强制集合函数包括 null 值.

SQL> SELECT AVG(NVL(comm,0)) FROM emp;

总结：当NVL(comm,0)时，佣金值赋0值，显示结果为0，不空

当NVL(comm,-1)时，佣金值赋-1值，不显示结果，为空

NVL 函数：

功能：把可能包含了空值的表达式1，转换成有实际意义的数据表达式2 。

格式：NVL(表达式1，表达式2）

作用的数据类型：可用于字符型、数值型、日期型.

注意事项：转换前后的类型必须一致：

NVL(comm,0)

NVL(hiredate,'01-JAN-97')

NVL(job,'No Job Yet')

在包含GROUP BY子句的查询语句中，SELECT子句后面的所有字段列表（除聚集函数外），均应该包含在GROUP BY 子句中。

在 GROUP BY 从句中使用多个列

SQL > SELECT deptno, job, sum(sal) FROM emp GROUP BY deptno, job

使用 HAVING 从句选择满足条件分组

执行次序：

对行进行分组.

对每组数据执行组函数.

返回符合 HAVING 从句的分组结果.

WHERE与HAVING的区别

作用对象不同。

WHERE 作用于基本表或视图，从中选择满足条件的元组

HAVING短语作用于组，从中选择满足条件的组。

[例]查询平均工资超过2000的部门，并按部门号进行排序

SQL> SELECT deptno, avg(sal) from emp

GROUP BY deptno

HAVING AVG(SAL)>2000

ORDER BY deptno;

结果

DEPTNO AVG(SAL)

10 3725

20 2175

集合查询：

查询结果的集合运算

并集（UNION [ ALL]）

交集（INTERSECT [ALL]）

差集（MINUS [ALL] ）

1. union[all]:

取多个查询结果的并集,不要all表示重复的记录只保留一条

注意：union、minus、intersect做连接查询时，多个查询结果的字段和类型要一致