ORACLE SQL 培训内容.doc

ORACLE SQL and SQL*PLUS 培训内容(一).前言: 关系数据库的简单介绍 它的内核是用C语言写的.ORACLE的简单介绍 ORACLE公司是一家提供综合技术产品、方案、服务的数据库公司ORACLE数据库设计成可大量存储数据，快速查询数据，保证数据的安全和一致性，跨网络的分布式管理及客户-服务器的配置等。ORACLE SQL语法符合ANSI 1986标准SQL和数据库交流的命令式语言SQL*PLUSORACLE的一种工具, 用来运行SQL和PL/SQL语句PL/SQLORACLE的过程化编程语言一、选择行1. 简单的SELECT 语句SELECT DISTINCT *,COLUMN ALIAS, FROM table_name;字段的非唯一的结果集DISTINCT 字段名1 ,字段名2 数字类型字段名之间可以进行算术运算+ - * / 等例如: (字段名1*字段名2)/3合并字段内容的连接字符 |字段名1 | 字段名2字段可以有别名SELECT 字段名1 AS 字段名1 解释 FROM table;例：select art_no 货号 from article ;优化的SQL 2. 处理NULLNULL 未定义，不可操作，什么都不是NULL != 0NULL!=空格和NULL的任何运算都返回NULLORACLE里未定义的变量值都为NULLNVL函数可把NULL转换成其它类型的符号NVL(EXPR1, EXPR2)函数解释:IF EXPR1=NULL RETURN EXPR2ELSE RETURN EXPR1编程技巧: NVL函数在多条件模糊查询的时候比较有用nvl(+input_value+,0)=0 or filed_name like %+input_value+%NVL函数可返回多种数据类型:返回日期NVL(last_saleday,2002-02-01)返回字符串NVL(last_saleday,新品)返回数字NVL(last_saleday,1000)3. 使用SQL*PLUS（1）登录SQL*PLUS的方法用户名，密码，数据库连接字符串数据库连接字符串可以用net8 easy configuration建立本地网络服务名配置，也可以手工编辑$ORACLE_HOME/network/admin/tnsnames.ora文件。在UNIX系统下不要用$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命令语句存在ORACLE内存（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后面插入textLIST n显示sql buffer里第n行n使第n行为当前行n text第n行替换成text0 text在第一行前面插入一行3. 使用SQL*PLUS（3） 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 bufferSTART filename运行上次保存到文件filename里的SQL命令filename运行上次保存到文件filename里的SQL命令EDIT打开编辑窗口，编辑sql buffer里的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;说明: 一行只显示数字位的长度, 超过长度折行,加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 salary justify right format $99,999.99;SQL col last_saleday hea 最后销售日 for date null 新品;select last_saleday from article ;说明：如果last_saleday为null, 显示字符串新品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比较不能用上面的比较操作符ANYSOMEALLSQL操作符BETWEEN ANDINLIKEIS NULL NOT BETWEEN AND NOT INNOT LIKEIS NOT NULL逻辑操作符ANDORNOT3. 用WHERE限制选择行(2)比较顺序(可以用括号改变它们的顺序)(1). = = to_date(2001-12-12,YYYY-MM-DD) and日期字段 select add_months(sysdate,12) Next Year from dual; 13-JAN-07LAST_DAY(d) 返回当月的最后一天的日期MONTHS_BETWEEN(d1,d2) 两个日期值间的月份,d1 alter session set nls_date_format=yyyy-mm-dd hh24:mi:ss;它的作用顺序如下:initialization parameterEnvironment variableALTER SESSION command5. 输入字符,返回数字的函数instr(c1,c2)字符c2出现在c1的位置, 不出现, 返回0, 常用于模糊查询length(c)按数据库的字符集,算出字符c的长度,跟数据库的字符集有关, 一个汉字长度为16. 有逻辑比较的函数NVL(EXPR1, EXPR2)函数DECODE(AAV1R1V2R2.)函数解释: IF AA=V1 THEN RETURN R1 IF AA=V2 THEN RETURN R2. ELSE RETURN NULL举例: select sum(decode(to_date,(&1,yyyymmdd),run_date ,stock,0) From tb_fin_art_stock where run_date between to_date(&1,yyyymmdd) and to_date(&2,yyyymmdd) 四、从多个表里选取数据记录1. 数据表间的连接简单的连接语法:SELECT 字段名1, 字段名2, FROM 表名1, 表名2, WHERE 表名1.字段名 = 表名2. 字段名 AND ; SELECT 字段名1, 字段名2, FROM 表名1, 表名2, WHERE 表名1.字段名 = 表名2. 字段名(+) AND ; 有(+)号的字段位置自动补空值 3. 数据表间的连接技巧连接N个表, 需要N-1个连接操作被连接的表最好建一个单字符的别名, 字段名前加上这个单字符的别名BETWEEN . AND. 比用 = 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; 说明：关闭自动分析统计五、集合函数经常和group by一起使用 1. 集合函数列表AVG (DISTINCT | ALL | N)取平均值COUNT(DISTINCT | ALL | N | expr | * )统计数量MAX(DISTINCT | ALL | N)取最大值MIN (DISTINCT | ALL | N)取最小值SUM (DISTINCT | ALL | N)取合计值2. 使用集合函数的语法SELECT column, group_function FROM tableWHERE conditionGROUP BY group_by_expressionHAVING group_conditionORDER BY column;4. 用group by时的限制条件SELECT字段名不能随意, 要包含在GROUP BY的字段里 GROUP BY后ORDER BY时不能用位置符号和别名 限制GROUP BY的显示结果, 用HAVING条件 5. 例子SQL select title,sum(salary) payroll from s_empwhere title like VP%group by titlehaving sum(salary)5000order by sum(salary) desc;!六、子查询1. 查询语句可以嵌套 例如: SELECT FROM (SELECT FROM表名1, 表名2, WHERE 条件) WHERE 条件2; 2. 何处可用子查询?当查询条件是不确定的条件时DML(insert, update,delete)语句里也可用子查询HAVING里也可用子查询3. 两个查询语句的结果可以做集合操作 例如: 并集UNION(去掉重复记录)并集UNION ALL(不去掉重复记录) 差集MINUS, 交集INTERSECT 4. 子查询的例子(1)查找12月销售数量最高的商品SQL select art_no,descr,sum(dec) from article group by art_no,descr having sum(dec)=(select max(sum(dec) from article group by art_no,descr);5. 子查询的例子(2)子查询可以用父查询里的表名SQL select invoice_no from invoice_line a where invoice_no in (select invoice_nofrom invoice b where a.invoice_no=b.invoice_no and a.till_no=b.till_noand to_char(invoice_date,yyyymmdd)=20051129 )说明：父查询调用子查询只执行一次七、在执行SQL语句时绑定变量SQL select id, last_name, salary froms_emp where dept_id = &department_number;Enter value for department_number: 10old 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= 10SQL SET VERIFY OFF | ON;可以关闭和打开提示确认信息old 1和new 1的显示.把绑定字符串和日期类型变量时，变量外面要加单引号也可绑定变量来查询不同的字段名输入变量值的时候不要加;等其它符号4. ACCEPT的语法和例子SQL ACCEPT variable datatype FORMAT PROMPT text HIDE说明：variable指变量名datatype指变量类型，如number,char等format指变量显示格式prompt text可自定义弹出提示符的内容texthide隐藏用户的输入符号使用ACCEPT的例子：ACCEPT p_dname PROMPT Provide the department name: ACCEPT p_salary NUMBER PROMPT Salary amount: ACCEPT pswd CHAR PROMPT Password: HIDEACCEPT 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 = salesSQL DEFINE dnameDEFINE dname = “sales” (CHAR)SQL select name from dept where lower(name)=&dname;NAME-salessalesSQL UNDEFINE dnameSQL DEFINE dnameSymbol dname is UNDEFINED5. SQL*PLUS里传递参数到保存好的*.sql文件里SQL /路径名/文件名 参数名1,参数名2, .SQL start /路径名/文件名 参数名1,参数名2, .注意事项:一次最多只能获取9个&变量, 变量名称只能是从&1,&2到&9变量名后不要加特殊的结束符号如果在SQL*PLUS里要把&符号保存在ORACLE数据库里,要修改sql*plus环境变量defineSQL set define off;九、创建表1. ORACLE常用的字段类型ORACLE常用的字段类型有 VARCHAR2 (size) 可变长度的字符串, 必须规定长度 CHAR(size) 固定长度的字符串, 不规定长度默认值为NUMBER(p,s) 数字型p是位数总长度, s是小数的长度, 可存负数 最长38位. 不够位时会四舍五入.DATE 日期和时间类型 LOB超长字符, 最大可达4GCLOB超长文本字符串BLOB超长二进制字符串FILE超长二进制字符串, 保存在数据库外的文件里是只读的.2. 创建表时给字段加默认值 和约束条件创建表时可以给字段加上默认值例如: 日期字段DEFAULT SYSDATE 这样每次插入和修改时, 不用程序操作这个字段都能得到动作的时间 创建表时可以给字段加上约束条件例如:非空NOT NULL不允许重复 UNIQUE 关键字 PRIMARY KEY 按条件检查CHECK (条件)外键REFERENCES 表名(字段名) 3. 创建表的例子CREATE TABLE DEPT(EPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,DNAME VARCHAR2(14),LOC VARCHAR2(13) ;4. 创建表时的命名规则和注意事项表名和字段名的命名规则：必须以字母开头，可以含符号A-Z,a-z,0-9,_,$,#大小写不区分不用SQL里的保留字, 一定要用时可用双引号把字符串括起来用和实体或属性相关的英文符号长度有一定的限制注意事项:建表时可以用中文的字段名, 但最好还是用英文的字段名 创建表时要把较小的不为空的字段放在前面, 可能为空的字段放在后面建表时如果有唯一关键字或者唯一的约束条件，建表时自动建了索引一个表的最多字段个数也是有限制的，254个.想在事务处理后, 做约束的检查SQL alter session set constraints deferred.8. 通过子查询建表通过子查询建表的例子SQLCREATE TABLE emp_41 AS SELECT id, last_name, userid, start_date FROM s_emp WHERE dept_id = 41; SQL CREATE TABLE A as select * from B where 1=2;只要表的结构.10. 用子查询建表的注意事项可以关连多个表及用集合函数生成新表,注意选择出来的字段必须有合法的字段名称,且不能重复。用子查询方式建立的表，只有非空NOT NULL的约束条件能继承过来, 其它的约束条件和默认值都没有继承过来.根据需要，可以用alter table add constraint 再建立其它的约束条件，如primary key等.12. 如果数据库表里有不满足的记录存在，建立约束条件将不会成功.数据库链接 (DATABASE LINK)CREATE DATABASE LINK数据库链接名CONNECT TO 用户名 IDENTIFIED BY 密码 USING 数据库连接字符串;数据库连接字符串可以用NET8 EASY CONFIG或者直接修改TNSNAMES.ORA里定义.数据库参数global_name=true时要求数据库链接名称跟远端数据库名称一样十、ORACLE里的数据字典1. 什么是数据字典ORACLE的数据字典是数据库的重要组成部分之一，它随着数据库的产生而产生, 随着数据库的变化而变化, 体现为sys用户下所有的一些表和视图.2. 数据字典里存了以下内容：用户信息用户的权限信息所有数据对象信息表的约束条件统计分析数据库的视图等不能手工修改数据字典里的信息.3. 常用的数据字典Dictionary存放所有数据表，视图，同义词名称和解释Dict_columns数据字典里字段名称的和解释Dba_users用户Dba_tablespaces表空间Dba_data_files数据库的文件Dba_free_space 空闲表空间Dba_rollback_segs 回滚段User_objects数据对象User_constraints约束条件User_sequences序列号User_views视图User_indexes索引User_synonyms同义词Session_roles用户的角色User_role_privs用户的角色权限User_sys_privs用户的系统权限User_tab_privs用户的表级权限V$session实时用户情况V$sysstat实时系统统计V$sesstat实时用户统计V$sgastat实时SGA使用V$locked_object 实时锁V$controlfile控制文件V$logfile日志文件V$parameter参数文件4. 数据字典的分类数据字典四大类别User_用户下所有数据库对象All_用户权限范围内所有的数据库对象Dba_所有的数据库对象V$统计分析数据库的视图赋于oem_monitor权限非DBA用户也可查询V$*视图十一. 控制数据1 、INSERT(往数据表里插入记录的语句)SQL insert into 表名(字段名1, 字段名2, ) values ( 值1, 值2, );SQL insert into 表名(字段名1, 字段名2, ) select (字段名1, 字段名2, ) from 另外的表名 where 条件; 可以用&标记变量的方法多次输入记录快速插入数据的方法, 一般用于大于128M的数据转移SQL insert /*+ append */ into 表名 select * from 另外的用户名 .另外的表名 WHERE 条件; SQL commit; 3、UPDATE (修改数据表里记录的语句) SQL UPDATE 表名 SET 字段名1=值1, 字段名2=值2, WHERE 条件;如果修改的值N没有赋值或定义时, 将把原来的记录内容清为NULL, 最好在修改前进行非空校验;值N超过定义的长度会出错, 最好在插入前进行长度校验.新功能，可以修改子查询后的结果集例子：SQL update (select * from s_dept) set id=50 where id=60;4、DELETE (删除数据表里记录的语句) SQL DELETE FROM 表名 WHERE 条件;注意：删除记录并不能释放ORACLE里被占用的数据块表空间. 它只把那些 被删除的数据块标成unused. 如果确实要删除一个大表里的全部记录, 可以用 TRUNCATE 命令, 它可以释放占用的数据块表空间 SQL TRUNCATE TABLE 表名;此操作不可回退. 5、 SQL语句的分类数据定义语言(DDL)：create、alter、drop（创建、修改结构、删除）（其他：rename）数据操纵语言(DML)：insert、delete、select、update（增、删、查、改）（其他：truncate） 数据控制语言(DCL)：grant、revoke（授权、回收）、set role 事务控制：commit、rollback、savepoint（其他：lock table、set constraint(s)、set transaction）审计控制：audit、noaudit系统控制：alter system 会话控制：alter session 其他语句：comment（添加注释）、explain plan、analyze、validate、call 6、ORACLE里事务控制 Commit提交事务Rollback回退事务Savepoint设置断点, 在事务中标记位置, 事务结束, 断点释放事务结束的情况遇到commit或者rollback遇到DDL和DCL语句发现错误，如死锁用户退出SQL*PLUS系统重启或崩溃6、事物控制和SAVEPOINT命令7. DML操作的注意事项 以上SQL语句对表都加上了行级锁, 确认完成后, 必须加上事物处理结束的命令COMMIT 才能正式生效,否则改变不一定写入数据库里.行级锁也未能得到释放. 如果想撤回这些操作, 可以用命令 ROLLBACK 复原. 在运行INSERT, DELETE 和 UPDATE 语句前最好估算一下可能操作的记录范围, 应该把它限定在较小 (一万条记录) 范围内,. 否则ORACLE处理这个事物用到很大的回退段. 程序响应慢甚至失去响应. 如果记录数上十万以上这些操作, 可以把这些SQL语句分段分次完成, 其间加上COMMIT 确认事物处理. 太过频繁的commit不好十二、改变表和约束条件 1. 改变表的几种情况(1)运行时会加表级锁改变表的名称 SQL RENAME 表名1 TO 表名2; SQL ALTER TABLE 表名1 RENAME TO 表名2;在表的后面增加一个字段 SQL ALTER TABLE 表名 ADD 字段名 字段名描述 DEFAULT expr NOT NULL ,字段名2 ; 修改表里字段的定义描述 SQL ALTER TABLE 表名 MODIFY 字段名1 字段名1描述 DEFAULT expr NOT NULL ,字段名2 ; 记录为空时，可以减少字段长度，改变字段类型修改DEFAULT值只作用于修改后的INSERT和UPDATE的记录修改NOT NULL约束只对现存含非空记录的字段起作用1. 改变表的几种情况(2)运行时会加表级锁删除表里的某个字段SQL ALTER TABLE 表名 DROP 字段名; 给表里的字段加上/禁止/启用约束条件SQL ALTER TABLE 表名 ADD | DISABLE | ENABLE CONSTRAINT 约束名 PRIMARY KEY (字段名1,字段名2 ); SQL ALTER TABLE 表名 ADD | DISABLE | ENABLE CONSTRAINT 约束名 UNIQUE (字段名1,字段名2 ); 加唯一关键字或者唯一约束条件时自动建立索引说明：禁止唯一关键字和唯一约束时索引仍然存在，可以被使用.1. 改变表的几种情况(3)运行时会加表级锁删除表里的约束条件SQL ALTER TABLE 表名 DROP CONSTRAINTS 约束名 CASCADE;会把约束相关的索引一起删除. CASCADE能同时删去外键的约束条件.把表放在或取出数据库的内存区 SQL ALTER TABLE 表名 CACHE;SQL ALTER TABLE 表名 NOCACHE; 改变表存储的表空间SQL ALTER TABLE 表名 MOVE TABLESPACE 表空间名 ;注意: 如果被转移表空间的表含有索引, 表转移后索引变得不可用. 我们要删除旧索引,建立新索引2. 删除表及表里的数据删除表SQL DROP TABLE 表名 CASCADE CONSTRAINTS;清空表里的记录SQL TRUNCATE TABLE 表名;按时间清空日志表里的记录，使用重新命名的方法(应用程序可能有短暂出错, 可以选择在不繁忙的时间执行)按原来表A的建表语句创建新表A1,把表A重命名为A2（如果表A上有较频繁的DML操作,会对表加上行级锁，重命名过程用递归的方式循环做，直到DML操作结束，命名成功).把创建新表A1重命名为A历史记录表A2备份或删除3. 删除表后应该注意的问题删除表后把表里的索引一起删去.删除表后会结束基于它的悬而未决的事物删除表后根据表创建的views,synonym,stored procedure,stored function依然存在，但views,synonym变成非法的. 需要手工找出它们并删除.如果用了CASCADE CONSTRAINTS会把与它相关的约束一起删除此操作不可回退十三、创建序列号 1. 创建序列号里各参数的解释SQL CREATE SEQUENCE name INCREMENT BY n START WITH n MAXVALUE n | NOMAXVALUE MINVALUE n | NOMINVALUE CYCLE | NOCYCLE CACHE n | NOCACHEINCREMENT BY n 一次增长n 个数字NOMAXVALUE缺省值10E+27NOMINVALUE缺省值1NOCYCLE不循环, 常用于唯一关键字CACHEn在内存里缓存n个序列,出错回退时会丢失-CREATE SEQUENCE lpeng_sequence - INCREMENT BY 1 - 每次加几个 - START WITH 1 - 从1开始计数 - NOMAXVALUE - 不设置最大值 - NOCYCLE - 一直累加，不循环 - noCACHE;序列号的名称一般可以采用“表名_字段名”的命名规则2. 插入自动增长序列号字段的方法 例子: SQL insert into s_dept(id) values (lpeng_sequence.nextval);3. 查询序列号的情况SQL select lpeng_sequence.nextval from dual;4. 改变序列号SQL ALTER SEQUENCE sequence INCREMENT BY n MAXVALUE n | NOMAXVALUE MINVALUE n | NOMINVALUE CYCLE | NOCYCLE CACHE n | NOCACHE;注意: 不能改变它的起始值如果要改变序列的起始值, 先把序列号删除掉, 再新建一个. 5. 删除序列号SQLDROP SEQUENCE sequence;6. 不能用序列号的nextval和currval的地方视图的查询有distinct的查询有group by,having,order by的查询有子查询的查询表里的缺省值十四、创建视图 1. 视图的概念和优点视图是基于一个或多个表及视图的一些查询语句, 它象显示数据的视窗, 它本身是不存储数据的.视图可以限制数据库的访问, 更好的控制权限,使用户使用简单的查询语句数据的非依赖性,同一数据的不同表现形式.2. 创建视图的语法SQL CREATE OR REPLACE FORCE|NOFORCE VIEW view(alias, alias.)AS subqueryWITH CHECK OPTION CONSTRAINT constraintWITH READ ONLY参数解释:FORCE表不存在时,视图仍然可以创建成功WITH CHECK OPTION 只有符合视图定义的记录才能被插入或修改WITH READ ONLY不允许DML操作Oracle8i以后创建视图可以用order by3. 创建修改视图的例子SQL create view voucher_04 as select * from voucher where store_no=4;4. 查询视图的数据字典SQL select view_name,text from user_views;5. 在视图上可以用DML命令吗? 可以, 但有一定的限制条件没有下面的情况, 可以删除view里的记录.group function, group by, distinct没有上面和下面的情况, 可以修改view里的记录.字段表达式, 例如: salary*12含rownum的view没有上面两种情况, 且view里含基表里所有非空字段的情况, 可以往view里插入记录. 8. 删除视图SQL DROP VIEW view_name;十五、创建索引1.索引的概念索引是数据库里的一种数据对象它利用B*树, hash, bitmap结构直接快