ORACLE SQL and SQLPLUS.docx

1、ORACLE SQL and SQLPLUSORACLE SQL and SQL*PLUS 前言: 关系数据库的简单介绍 1970.Dr.E.F.codded 创建了关系数据库的模型（类似现在常用的二维表格）关系数据库由许多数据对象组成, 被关系操作SQL命令管理着, 数据之间有完整性的约束条件.RDBMS ( relational database management system )ORDBMS ( object relational database management system )ORACLE RDBMS常称为ORACLE SERVER它的内核是用C语言写的.ORACLE的简单

2、介绍 ORACLE公司是一家提供综合技术产品、方案、服务的数据库公司ORACLE数据库设计成可大量存储数据，快速查询数据，保证数据的安全和一致性，跨网络的分布式管理及客户-服务器的配置等。ORACLE SQL语法符合ANSI 1986标准SQL 和数据库交流的命令式语言SQL*PLUS ORACLE的一种工具, 用来运行SQL和PL/SQL语句PL/SQL ORACLE的过程化编程语言一、选择行1. 简单的SELECT 语句SELECT DISTINCT *,COLUMN ALIAS, FROM table_name;字段的非唯一的结果集 DISTINCT 字段名1 ,字段名2 数字类型字段名

3、之间可以进行算术运算 + - * / 等 例如: (字段名1*字段名2)/3合并字段内容的连接字符 | 字段名1 | 字段名2 字段可以有别名 SELECT 字段名1 AS 字段名1 解释 FROM table;2. 处理NULLNULL 未定义，不可操作，什么都不是NULL != 0 NULL!=空格 和NULL的任何运算都返回NULLORACLE里未定义的变量值都为NULLNVL函数可把NULL转换成其它类型的符号NVL(EXPR1, EXPR2)函数解释: IF EXPR1=NULL RETURN EXPR2 ELSE RETURN EXPR1编程技巧: NVL函数在多条件模糊查询的时候

4、比较有用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系统

5、下不要用$sqlplus username/passworddbname 这样别的用户用$ps命令能看出用户的密码SQL desc table; 显示表结构SQL select * from tab; 查看用户下所有的表SQL set pause on; 可以使大量结果集在用户按“Enter”(回车)后翻页SQL set pagesize 100; 设定SQL语句返回结果集一页的行数100, 默认值是14SQL set linesize 100; 设定SQL语句返回结果集一行的宽度100, 默认值是803. 使用SQL*PLUS（2）SQL*PLUS里的编辑命令最近一条SQL命令语句存在ORA

6、CLE内存（sql buffer）里,但SQL*PLUS命令却不会存进去。SQL*PLUS里的编辑命令: APPEND text 把text增加到当前行后面 CHANGE /old/new/ 把当前行old符号替换成new符号,new为空，删除old符号 CLEAR BUFFER 从sql buffer里删除所有的行 DEL n 删除sql buffer里第n行 INPUT text 在sql buffer后面插入text LIST n 显示sql buffer里第n行 n 使第n行为当前行 n text 第n行替换成text 0 text 在第一行前面插入一行3. 使用SQL*PLUS（3）

7、 SQL*PLUS里的文件命令: SAVE filename REPLACE | APPEND 把sql buffer里的SQL命令存到OS下一个文件filename,默认的文件名后缀为.sql. REPLACE替换filename里的SQL命令 APPEND 把sql buffer里的SQL命令添加到filename后 GET filename 从filename里把SQL命令读到sql buffer START filename 运行上次保存到文件filename里的SQL命令 filename 运行上次保存到文件filename里的SQL命令 EDIT 打开编辑窗口，编辑sql buff

8、er里的SQL命令 EDIT filename 打开编辑窗口，编辑文件filename里的SQL命令 SPOOL filename OFF | OUT 把SQL命令结果输出到OS下一个文件filename OFF结束输出到文件, OUT结束输出到文件并打印文件 4. SQL*PLUS里规定字段的显示格式规定数字的显示格式SQLcolumn 字段名 format 99999999999;SQLcolumn 字段名 format 999,999,999,999;规定字符串的显示宽度 SQLcolumn 字段名 format a数字 word_wrapped;说明: 一行只显示数字位的长度, 超过长

9、度折行,加word_wrapped后, 单词不会折行规定long字符的显示宽度SQLset 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 s

10、alary justify right format $99,999.99;SQL column start_date format a10 null Not Hired;说明：如果start_date为null, 显示字符串Not Hired6. 判断题(T/F)(1). SQL command are always held in sql buffer. T(2). SQL*PLUS command assit with query data. TSQL*PLUS命令只控制SELECT结果集的显示格式及控制文件.只有SQL命令能访问数据库.二、限制选择行1. 按指定的规则排序SELECT

11、expr FROM table ORDER BY column, expr ASC | DESC ;默认的排序是ASC升序(由小到大)还可以ORDER BY 字段名的位置1| 2 ASC| DESC;当字段名很复杂或者是算术表达式时用字段名显示的位置排序很方便.2. 用WHERE限制选择行(1)比较操作符 = = = != = 与NULL比较不能用上面的比较操作符 ANY SOME ALLSQL操作符 BETWEEN AND IN LIKE IS NULL NOT BETWEEN AND NOT IN NOT LIKE IS NOT NULL逻辑操作符 AND OR NOT3. 用WHERE限

12、制选择行(2)比较顺序(可以用括号改变它们的顺序)(1). = = to_date(2001-12-12,YYYY-MM-DD) and日期字段= to_date(2002-02-01,YYYY-MM-DD); 6. 不能用到索引的比较操作符IS NULLIS NOT NULLLIKE %m%三、单行函数1. 数字函数ABS 取绝对值 POWER 乘方 LN 10为底数取冪SQRT 平方根 EXP e的n次乘方 LOG(m,n) m为底数n取冪 数学运算函数:ACOS ATAN ATAN2 COS COSH SIGN SIN SINH TAN TANH CEIL 大于或等于取整数 FLOOR

13、小于或等于取整数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位字符

14、TRANSLATE(c,f1,t1) 字符c按f1到t1的规则转换成新的字符串INITCAP 字符首字母大写,其它字符小写LOWER 字符全部小写UPPER 字符全部大写LTRIM(c1,c2) 去掉字符c1左边出现的字符c2RTRIM(c1,c2) TRIM(c1,c2) 去掉字符c1左右两边的字符c2LPAD(c1,n,c2) 字符c1按制定的位数n显示不足的位数用c2字符串替换左边的空位RPAD(c1,n,c2)3. 日期函数ADD_MONTHS(d,n) 日期值加n月LAST_DAY(d) 返回当月的最后一天的日期MONTHS_BETWEEN(d1,d2) 两个日期值间的月份,d1 a

15、lter session set nls_date_format=yyyy-mm-dd hh24:mi:ss;它的作用顺序如下:initialization parameterEnvironment variableALTER SESSION command4. 转换函数 (4)to_char(number)里数字显示格式 9 数字位 0 数字前面补0 to_char(-1200,00000.00) . 小数点的位置 , 标记位置的逗号 用在数字显示格式的左边 L 根据数据库字符集加货币符号 to_char(-1200,L9999.99) B 把数字0显示为空格,用在数字显示格式的右边 MI

16、右边的负数标记 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的长度,跟数据库的字符集有关, 一个汉字长度为16. 有逻辑比较的函数NVL(EXPR1, EXPR2)函数解释: IF EXPR1=NULL RETURN EXPR2 ELSE RETURN EXPR1DECODE(A

17、AV1R1V2R2.)函数解释: 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 ; 有(+)号的字段位置自动补空

18、值 连接的分类: 等于的连接 = 不等于的连接 != BETWEEN AND IN 注意IN和OR不能一起用 外连接 有一个字段名(+) , 没有满足的条件补空值 自连接 同一个表自己跟自己连接 例如找重复记录2. 数据表间的连接例子删除table_name表里字段名email重复的记录:SQLdelete 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);找到手机用户的服务

19、区域: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. 比用

20、= AND grant plustrace to &username;一般用户下运行/ORACLE_HOME/rdbms/admin/utlxplan.sql 产生plan_tableSQL set time on; 说明：打开时间显示 SQL set autotrace on; 说明：打开自动分析统计，并显示SQL语句的运行结果SQL set autotrace traceonly; 说明：打开自动分析统计，不显示SQL语句的运行结果接下来你就运行测试SQL语句，看到其分析统计结果了。一般来讲，我们的SQL语句应该避免大表的全表扫描。SQL set autotrace off; 说明：关闭自

21、动分析统计五、集合函数 经常和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) 取偏差值,如果组里选择的内容都相同,结果为0VARIANCE (DISTINCT | ALL | N) 取平方偏差值2. 使用集合函数的语法SELECT

22、 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时都

23、会忽略为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 fiel

24、d 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后把结果集排除在外

25、7. 在SQL*PLUS里可使用的其它命令: CtrlC 终止正在运行的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 MAXIMU

26、M AVG STD VARIANCE COUNT NUMBER8.在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条件六、子查