ORACLE SQL and SQL*PLUS 学习（上）

ORACLE SQL and SQL*PLUS 学习（上） (2007-09-11 14:21:10)

<script></script> 标签：文学/原创

一、选择行
1. 简单的SELECT 语句
SELECT [DISTINCT] {*,COLUMN [ALIAS], ……} FROM table_name;
字段的非唯一的结果集
 DISTINCT 字段名1 [ ,字段名2]
数字类型字段名之间可以进行算术运算
 +  - * /  等
 例如: (字段名1*字段名2)/3
合并字段内容的连接字符 ||
 字段名1 || 字段名2
字段可以有别名
 SELECT 字段名1 [AS] '字段名1 解释' FROM table;

2. 处理NULL
NULL 未定义，不可操作，什么都不是
NULL != 0 NULL!='空格'
和NULL的任何运算都返回NULL
ORACLE里未定义的变量值都为NULL
NVL函数可把NULL转换成其它类型的符号
NVL(EXPR1, EXPR2)函数
解释: IF EXPR1=NULL  RETURN EXPR2
  ELSE  RETURN EXPR1
编程技巧: NVL函数在多条件模糊查询的时候比较有用
nvl('+input_value+','0')='0' or filed_name like '%+input_value+%'
NVL函数可返回多种数据类型:
返回日期 NVL(start_date,'2002-02-01')
返回字符串 NVL(title,'no title')
返回数字 NVL(salary,1000)

3. 使用SQL*PLUS（1）
登录SQL*PLUS的方法
 用户名，密码，数据库连接字符串
数据库连接字符串可以用net8 easy configuration建立本地网络服务名配置，
也可以手工编辑$ORACLE_HOME/network/admin/tnsnames.ora文件。
在UNIX系统下不要用
$sqlplus username/password@dbname
 这样别的用户用$ps命令能看出用户的密码
SQL> desc table;  显示表结构
SQL> select * from tab;  查看用户下所有的表
SQL> set pause on;  可以使大量结果集在用户按“Enter”(回车)后翻页
SQL> set pagesize 100;  设定SQL语句返回结果集一页的行数100, 默认值是14
SQL> set linesize 100;  设定SQL语句返回结果集一行的宽度100, 默认值是80

3. 使用SQL*PLUS（2）SQL*PLUS里的编辑命令
最近一条SQL命令语句存在ORACLE内存（sql buffer）里,但SQL*PLUS命令却不会存进去。
SQL*PLUS里的编辑命令:
 A[PPEND] text  把text增加到当前行后面
 C[HANGE] /old/new/ 把当前行old符号替换成new符号,new为空，删除old符号
 CL[EAR] BUFF[ER] 从sql buffer里删除所有的行
 DEL n   删除sql buffer里第n行
 I[NPUT] text  在sql buffer后面插入text
 L[IST] n  显示sql buffer里第n行
 n   使第n行为当前行
 n    text  第n行替换成text
 0    text  在第一行前面插入一行

3. 使用SQL*PLUS（3） SQL*PLUS里的文件命令:
 SAV[E] filename [REP[LACE] | APP[END] ] 
   把sql buffer里的SQL命令存到OS下一个文件filename,默认的文件名后缀为.sql.
     REP[LACE]替换filename里的SQL命令
 APP[END]   把sql buffer里的SQL命令添加到filename后
 GET filename   从filename里把SQL命令读到sql buffer
 STA[RT] filename  运行上次保存到文件filename里的SQL命令
 @filename   运行上次保存到文件filename里的SQL命令
 EDIT    打开编辑窗口，编辑sql buffer里的SQL命令
 EDIT filename   打开编辑窗口，编辑文件filename里的SQL命令
 SPO[OL] filename [OFF | OUT] 把SQL命令结果输出到OS下一个文件filename    OFF结束输出到文件,

OUT结束输出到文件并打印文件
       
4. SQL*PLUS里规定字段的显示格式
规定数字的显示格式
SQL>column 字段名 format 99999999999;
SQL>column 字段名 format 999,999,999,999;
规定字符串的显示宽度
SQL>column 字段名 format a数字 [word_wrapped];
说明: 一行只显示数字位的长度, 超过长度折行,加word_wrapped后, 单词不会折行
规定long字符的显示宽度
SQL>set long 200;
规定字段名的显示内容
SQL> column 字段名 heading '字段名显示内容';
SQL> set heading off;    查询时不显示字段名称
规定字段的对齐方向
SQL> column 字段名 justify [left | right | center];
清除字段的格式 
SQL> column 字段名 clear;

5. SQL*PLUS里规定字段的显示格式例子
SQL> column last_name heading 'Employee|Name' format a15;
SQL> column salary justify right format $99,999.99;
SQL> column start_date format a10 null 'Not Hired';
说明：如果start_date为null, 显示字符串'Not Hired'

6. 判断题(T/F)
(1). SQL command are always held in sql buffer.  [T]
(2). SQL*PLUS command assit with query data.  [T]
SQL*PLUS命令只控制SELECT结果集的显示格式及控制文件.只有SQL命令能访问数据库.

二、限制选择行
1. 按指定的规则排序
SELECT expr FROM table [ORDER BY {column, expr} [ASC | DESC] ];
默认的排序是ASC升序(由小到大)
还可以ORDER BY 字段名的位置[1]| [2] ASC| DESC;
当字段名很复杂或者是算术表达式时用字段名显示的位置排序很方便.

2. 用WHERE限制选择行(1)
比较操作符 =  >  <  >=  <=  !=  <>  ^=  与NULL比较不能用上面的比较操作符   ANY SOME

ALL
SQL操作符 BETWEEN … AND…  IN LIKE  IS NULL 
  NOT BETWEEN … AND…  NOT IN NOT LIKE IS NOT NULL
逻辑操作符 AND OR NOT

3.  用WHERE限制选择行(2)
比较顺序(可以用括号改变它们的顺序)
(1). = < > >= <= in like is null between
(2). and
(3). Or
注意: char和varchar2的比较规则有不同:
char比较时会忽略字符串后面的空格. varchar2会计算字符串后面的空格

4.  LIKE操作
% 零到任意多个字符  _ 一个字符
例如: 字段名 like 'M%'  字段名 like '%m%'  字段名 like  'job_'
如果要找含下划线的字符, 要加反斜线       例如:字段名 like '%X/_Y%' escape '/'

5. 日期字段的比较
举例:
日期字段 between to_date('2001-12-12','YYYY-MM-DD') and to_date('2002-02-01','YYYY-MM-DD')
日期字段> to_date('2001-12-12','YYYY-MM-DD') and日期字段<= to_date('2002-02-01','YYYY-MM-DD');

6. 不能用到索引的比较操作符
IS NULL
IS NOT NULL
LIKE '%m%'

三、单行函数
1. 数字函数
ABS  取绝对值 POWER 乘方   LN   10为底数取0
SQRT 平方根 EXP       e的n次乘方  LOG(m,n)   m为底数n取0  
数学运算函数:ACOS ATAN ATAN2 COS COSH  SIGN SIN SINH TAN TANH
CEIL    大于或等于取整数
FLOOR  小于或等于取整数
MOD    取余数  
ROUND(n,m)   按m的位数取四舍五入值如果round(日期): 中午12以后将是明天的日期. round(sysdate,'Y')是年的第一天
TRUNC(n,m) 按m的位数取前面的数值如果trunc(日期), 确省的是去掉时间

2. 字符函数
CHR     按数据库的字符集由数字返回字符 
CONCAT(c1,c2)    把两个字符c1,c2组合成一个字符, 和 || 相同
REPLACE(c,s,r)   把字符c里出现s的字符替换成r, 返回新字符
SUBSTR(c,m,n)  m大于0,字符c从前面m处开始取n位字符,m等于0和1一样,
   m小与0,字符c从后面m处开始取n位字符
TRANSLATE(c,f1,t1)   字符c按f1到t1的规则转换成新的字符串
INITCAP   字符首字母大写,其它字符小写
LOWER    字符全部小写
UPPER   字符全部大写
LTRIM(c1,c2)  去掉字符c1左边出现的字符c2
RTRIM(c1,c2)
TRIM(c1,c2)   去掉字符c1左右两边的字符c2
LPAD(c1,n,c2)   字符c1按制定的位数n显示不足的位数用c2字符串替换左边的空位
RPAD(c1,n,c2)

3. 日期函数
ADD_MONTHS(d,n)   日期值加n月
LAST_DAY(d)    返回当月的最后一天的日期
MONTHS_BETWEEN(d1,d2)   两个日期值间的月份,d1<d2 返回负数
NEXT_DAY(d)    返回日期值下一天的日期
SYSDATE    当前的系统时间
    DUAL是SYS用户下一个空表，它只有一个字段dummy
4. 转换函数(1)
TO_CHAR(date,'日期显示格式')  
TO_CHAR(number)    用于显示或报表的格式对齐
TO_DATE(char,'日期显示格式')  
TO_LOB      把long字段转换成lob字段
TO_NUMBER(char)    用于计算或者比较大小

4. 转换函数(2)
to_date里日期显示格式
 YYYY  年 YEAR YYY YY Y
 Q  季度
 MM  月 MONTH MON
 W  星期 (week of month) WW, IW (week of year) 
(说明：周计是按ISO标准,从1月1日的星期数到后面七天为一周,不一定是从周一到周日)
 DD  日 DAY  DY
 HH24  小时 HH12  HH
 MI  分钟
 SS  秒
如果想固定日期的显示格式可以在数据库的参数文件initorasid.ora里新写一行参数 NLS_DATE_FORMAT=yyyy-mm-dd

hh24:mi:ss可以在UNIX环境变量或者NT的注册表里的设置 NLS_DATE_FORMAT=yyyy-mm-dd hh24:mi:ss

4. 转换函数(3)
如果想固定日期的显示格式可以用alter session命令改变
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
它的作用顺序如下:
initialization parameter
Environment variable
ALTER SESSION command

4. 转换函数 (4)
to_char(number)里数字显示格式
 9 数字位
 0 数字前面补0    to_char(-1200,'00000.00')
 . 小数点的位置
 , 标记位置的逗号用在数字显示格式的左边 
 L 根据数据库字符集加货币符号 to_char(-1200,'L9999.99') 
 B 把数字0显示为空格,用在数字显示格式的右边
 
 MI 右边的负数标记        to_char(-1200,'9999.99MI')
 PR     括起来的负数       to_char(-1200,'9999.99PR')
 EEEE 用指数方式表示       to_char(-1200,'9999.99EEEE')

5. 输入字符,返回数字的函数
instr(c1,c2) 字符c2出现在c1的位置, 不出现, 返回0, 常用于模糊查询
length(c) 按数据库的字符集,算出字符c的长度,跟数据库的字符集有关, 一个汉字长度为1

6. 有逻辑比较的函数NVL(EXPR1, EXPR2)函数
解释: IF EXPR1=NULL  RETURN EXPR2
  ELSE  RETURN EXPR1
DECODE(AA0V10R10V20R2....)函数
解释: IF AA=V1 THEN RETURN R1
          IF AA=V2 THEN RETURN R2
  ..…
   ELSE
   RETURN NULL 
举例: decode(id,1,'dept sale',2,'dept tech')

四、从多个表里选取数据记录
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. 查询语句可以嵌套
例如: SELECT …… FROM (SELECT …… FROM表名1, [表名2, ……]  WHERE 条件) WHERE 条件2;

2. 何处可用子查询?
当查询条件是不确定的条件时
DML(insert, update,delete)语句里也可用子查询
HAVING里也可用子查询

3. 两个查询语句的结果可以做集合操作
例如:
并集UNION(去掉重复记录)
并集UNION ALL(不去掉重复记录) 
差集MINUS,    
交集INTERSECT

4. 子查询的注意事项
先执行括号里面的SQL语句，一层层到外面
内部查询只执行一次
如果里层的结果集返回多个，不能用= > < >= <=等比较符要用IN.

5. 子查询的例子(1)
SQL> select title,avg(salary) from s_emp
  group by title  Having avg(salary) =
   (select min(avg(salary)) from s_emp
    group by title);
找到最低平均工资的职位名称和工资

5. 子查询的例子(2)
子查询可以用父查询里的表名
这条SQL语句是对的:
SQL>select cty_name from city where st_code in
 (select st_code from state where st_name='TENNESSEE' and
  city.cnt_code=state.cnt_code);
说明：父查询调用子查询只执行一次．

6.取出结果集的80 到100的SQL语句
ORACLE处理每个结果集只有一个ROWNUM字段标明它的逻辑位置,
并且只能 用ROWNUM<100, 不能用ROWNUM>80。
以下是经过分析后较好的两种ORACLE取得结果集80到100间的SQL语句( ID是唯一关键字的字段名 )：
语句写法：
SQL>select * from (
 ( select rownum as numrow, c.* from (
  select [field_name,...] from table_name where 条件1 order by 条件2) c)
    where numrow > 80 and numrow <= 100 )
    order by 条件3;

七、在执行SQL语句时绑定变量
1. 接收和定义变量的SQL*PLUS命令
 ACCEPT
 DEFINE UNDEFINE
 &
 
2.  绑定变量SQL语句的例子(1)
SQL> select id, last_name, salary from s_emp where  dept_id = &department_number;
Enter value for department_number: 10
old   1: select id, last_name, salary from s_emp where  dept_id=&department_number;
new  1: select id, last_name, salary from s_emp where  dept_id= 10
SQL> SET VERIFY OFF | ON;可以关闭和打开提示确认信息old  1和new  1的显示.

3. 绑定变量SQL语句的例子(2)
SQL> select  id, last_name, salary
   from s_emp
     where  title = '&job_title';
Enter value for job_title: Stock Clerk

SQL> select  id, last_name, salary
   from s_emp
     where  hiredate >to_date( '&start_hire_date','YYYY-MM-DD');
Enter value for start_hire_date : 2001-01-01

把绑定字符串和日期类型变量时，变量外面要加单引号
也可绑定变量来查询不同的字段名
输入变量值的时候不要加;等其它符号

4. ACCEPT的语法和例子
SQL> ACCEPT variable [datatype] [FORMAT] [PROMPT text] [HIDE]
说明： variable 指变量名  datatype 指变量类型，如number,char等 format  指变量显示格

式 prompt text 可自定义弹出提示符的内容text hide  隐藏用户的输入符号
使用ACCEPT的例子：
ACCEPT p_dname PROMPT  'Provide the department name: '
ACCEPT p_salary NUMBER PROMPT  'Salary amount: '
ACCEPT pswd CHAR PROMPT  'Password: ' HIDE
ACCEPT low_date date format 'YYYY-MM-DD' PROMPT“Enter the low date range('YYYY-MM-DD'):”

4. DEFINE的语法和例子
SQL> DEFINE variable = value
说明： variable 指变量名 value  指变量值
定义好了变良值后, 执行绑定变量的SQL语句时不再提示输入变量
使用DEFINE的例子：
 SQL> DEFINE dname = sales
 SQL> DEFINE dname
 DEFINE dname = “sales” (CHAR)
 SQL> select name from dept where lower(name)='&dname';
 NAME
 -------------------------
 sales
 sales
 SQL> UNDEFINE dname
 SQL> DEFINE dname
 Symbol dname is UNDEFINED

5. SQL*PLUS里传递参数到保存好的*.sql文件里
SQL> @     /路径名/文件名  参数名1[,参数名2, ….]
SQL> start  /路径名/文件名  参数名1[,参数名2, ….]

注意事项:
一次最多只能获取9个&变量, 变量名称只能是从&1,&2到&9
变量名后不要加特殊的结束符号
如果在SQL*PLUS里要把&符号保存在ORACLE数据库里,要修改sql*plus环境变量define
SQL> set define off;