新手必读：Oracle数据库操作全接触

四、从多个表里选取数据记录
1. 数据表间的连接
简单的连接语法:
SELECT 字段名1, 字段名2, …… FROM 表名1, [表名2, ……]
WHERE 表名1.字段名 = 表名2. 字段名 [ AND ……] ;
SELECT 字段名1, 字段名2, …… FROM 表名1, [表名2, ……]
WHERE 表名1.字段名 = 表名2. 字段名(+) [ AND ……] ;
有(+)号的字段位置自动补空值

连接的分类:
等于的连接 =
不等于的连接 != BETWEEN … AND … IN 注意IN和OR不能一起用
外连接 有一个字段名(+) , 没有满足的条件补空值
自连接 同一个表自己跟自己连接 例如找重复记录

2. 数据表间的连接例子
删除table_name表里字段名email重复的记录:
SQL>delete from table_name t1
where t1.rowid >
(select min(rowid) from table_name t2
where t1.email = t2.email
group by email
having count(email) > 1);

找到手机用户的服务区域:
SQL> select a.handphoneno,nvl(c.name,'null'),a.totalscore
from topscore a,chargeoperator cc,chargeoperatorinfo c
where substr(a.handphoneno,1,7)=cc.hpnohead(+)
and cc.chargetype=c.chargetype(+)
order by a.totalscore desc;

3. 数据表间的连接技巧
连接N个表, 需要N-1个连接操作
被连接的表最好建一个单字符的别名, 字段名前加上这个单字符的别名
BETWEEN .. AND.. 比用 >= AND <= 要好
连接操作的字段名上最好要有索引
连接操作的字段最好用整数数字类型
有外连接时, 不能用OR或IN的比较操作

4. 如何分析和执行SQL语句
写多表连接SQL语句时要知道它的分析执行计划的情况.
Sys用户下运行@/ORACLE_HOME/sqlplus/admin/plustrce.sql
产生plustrace角色
Sys用户下把此角色赋予一般用户 SQL> grant plustrace to &username;
一般用户下运行@/ORACLE_HOME/rdbms/admin/utlxplan.sql
产生plan_table
SQL> set time on; 说明：打开时间显示
SQL> set autotrace on; 说明：打开自动分析统计，并显示SQL语句的运行结果
SQL> set autotrace traceonly; 说明：打开自动分析统计，不显示SQL语句的运行结果
接下来你就运行测试SQL语句，看到其分析统计结果了。
一般来讲，我们的SQL语句应该避免大表的全表扫描。
SQL> set autotrace off; 说明：关闭自动分析统计

五、集合函数 经常和group by一起使用
1. 集合函数列表
AVG (DISTINCT | ALL | N) 取平均值
COUNT (DISTINCT | ALL | N | expr | * ) 统计数量
MAX (DISTINCT | ALL | N) 取最大值
MIN (DISTINCT | ALL | N) 取最小值
SUM (DISTINCT | ALL | N) 取合计值
STDDEV (DISTINCT | ALL | N) 取偏差值,如果组里选择的内容都相同,结果为0
VARIANCE (DISTINCT | ALL | N) 取平方偏差值

2. 使用集合函数的语法
SELECT column, group_function FROM table
WHERE condition GROUP BY group_by_expression
HAVING group_condition ORDER BY column;

3. 使用count时的注意事项
SELECT COUNT(*) FROM table;
SELECT COUNT(常量) FROM table;

都是统计表中记录数量,如果没有PK后者要好一些
SELECT COUNT(all 字段名) FROM table;
SELECT COUNT(字段名) FROM table;

不会统计为NULL的字段的数量
SUM,AVG时都会忽略为NULL的字段

4. 用group by时的限制条件
SELECT字段名不能随意, 要包含在GROUP BY的字段里
GROUP BY后ORDER BY时不能用位置符号和别名
限制GROUP BY的显示结果, 用HAVING条件

5. 例子
SQL> select title,sum(salary) payroll from s_emp
where title like 'VP%' group by title
having sum(salary)>5000 order by sum(salary) desc;

找出某表里字段重复的记录数, 并显示
SQL> select (duplicate field names) from table_name
group by (list out fields) having count(*)>1;

6. 判断题(T/F)
(1) Group functions include nulls in calculations [F]
(2) Using the having clause to exclude rows from a group calculation [F]
解释:
Group function 都是忽略NULL值的 如果您要计算NULL值, 用NVL函数
Where语句在Group By前把结果集排除在外Having语句在Group By后把结果集排除在外

7. 在SQL*PLUS里可使用的其它命令:

Ctrl^C 终止正在运行的SQL语句
remark /*...*/ -- 注释符号
HOST 可执行的操作系统下的命令 有些unix可以用 !

BREAK ON column_name SKIP n [ ON column_name SKIP n ]
按字段的名称column_name分隔显示,更清晰,SKIP n 是在分隔处空行的数量n
BREAK ON ROW SKIP n 每一行间隔都放n个空行

COMPUTE 集合运算符 OF 字段1 ON 字段2 按字段2对字段1进行集合运算
COMPUTE后面可以跟的集合运算符:
SUM MINIMUM MAXIMUM AVG STD VARIANCE COUNT NUMBER

8.在SQL*PLUS里可使用的其它命令举例:(scott用户)

BREAK ON REPORT
COMPUTE SUM LABEL TOTAL OF SAL ON REPORT 在全部结果集后面算合计
select ename,sal from emp where job='SALESMAN';

COMPUTE AVG LABEL avg OF SAL ON REPORT 在全部结果集后面算平均值
/ 再次执行上次的sql语句

break on DEPTNO skip 2 on JOB skip 1 在BREAK字段结果集后面算合计
COMPUTE SUM OF SAL ON DEPTNO
SELECT DEPTNO,JOB,ENAME,SAL FROM EMP ORDER BY DEPTNO,JOB;

SQL> CLEAR BREAKS; 清除设置的BREAK条件
SQL> CLEAR COMPUTES; 清除设置的COMPUTE条件

上一页  [1] [2] [3] [4] [5] [6] [7] [8] [9] 下一页

【责编:Peng】