ORACLE SQL and SQL*PLUS 学习(下二）

十、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$Content$nbsp; 统计分析数据库的视图  赋于oem_monitor权限非DBA用户也可查询V$*视图 5. 查询数据字典 SQL> select * from dictionary where instr(comments,'index')>0; SQL> select constraint_name, constraint_type,   2  search_condition, r_constraint_name   3  from user_constraints   4  where table_name = ‘&table_name'; 十一. 控制数据 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; 注意事项：      用INSERT /*+ APPEND */ 的方法会对target_tablename产生级别为6的独占锁，      如果运行此命令时还有对target_tablename的DML操作会排队在它后面,      对OLTP系统在用的表操作是不合适的。 2. 插入字符串类型的字段的注意事项: 字符串类型的字段值必须用单引号括起来, 例如: ’GOOD DAY’ 如果字段值里包含单引号’ 需要进行字符串转换, 我们把它替换成两个   单引号’ ’ 字符串类型的字段值超过定义的长度会出错, 最好在插入前进行长度校验 ‘’ 标记是NULL, user 标明当前用户 日期字段的字段值可以用当前数据库的系统时间SYSDATE, 精确到秒 用字符串转换成日期型函数TO_DATE(‘2001-08-01’,’YYYY-MM-DD’) TO_DATE(  )还有很多种日期格式, 可以参看ORACLE DOC. 年-月-日 小时:分钟:秒 的格式YYYY-MM-DD HH24:MI:SS INSERT时最大可操作的字符串长度小于等于4000个单字节, 如果要插入更长的字符串, 请考虑字段用CLOB类型, 方法借用ORACLE里自带的DBMS_LOB程序包. 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会把与它相关的约束一起删除 此操作不可回退 4. 给表加注释 加注释的语法 SQL> COMMENT ON TABLE 表名 | COLUMN表名.字段名 IS ‘text‘ 加注释的例子 SQL> comment on table s_emp is ‘Enployee information‘; SQL> comment on column s_emp.last_name is ‘‘; 关于注释的数据库字典  ALL_COL_COMMENTS  USER_COL_COMMENTS  ALL_TAB_COMMENTS  USER_TAB_COMMENTS 十三、创建序列号 1. 创建序列号里各参数的解释 SQL> CREATE SEQUENCE name [INCREMENT BY n]    [START WITH n] [{MAXVALUE n | NOMAXVALUE}]    [{MINVALUE n | NOMINVALUE}] [{CYCLE | NOCYCLE}]    [{CACHE n | NOCACHE}] INCREMENT BY n  一次增长n 个数字 NOMAXVALUE  缺省值10E+27 NOMINVALUE  缺省值1 NOCYCLE  不循环, 常用于唯一关键字 CACHE n  在内存里缓存n个序列,出错回退时会丢失     oracle8i里默认的n是20 序列号的名称一般可以采用“表名_字段名”的命名规则 2. 插入自动增长序列号字段的方法 INSERT时如果要用到从1开始自动增长的数字做唯一关键字, 应该先建立一个序列号. CREATE SEQUENCE 序列号的名称 (最好是表名+序列号标记) INCREMENT BY 1 START WITH 1 MAXVALUE 99999 NOCYCLE NOCACHE; 其中最大的值按字段的长度来定,比如定义的自动增长的序列NUMBER(6) , 最大值为999999 INSERT 语句插入这个字段值为: 序列号的名称.NEXTVAL 例子: SQL> insert into s_dept(id, name, region_id)  values (s_dept_id.nextval, 'finance', 2);  1 row created. 只有运行了序列号的名称. nextval后序列号的名称. currval 才有效才有值. 3. 查询序列号的情况 SQL> select sequence_name, min_value, max_value, increment_by, last_number from user_sequences; 其中last_number指的是序列号的下一个值. 4. 改变序列号 SQL> ALTER SEQUENCE sequence [INCREMENT BY n]           [{MAXVALUE n | NOMAXVALUE}]    [{MINVALUE n | NOMINVALUE}]    [{CYCLE | NOCYCLE}] [{CACHE n | NOCACHE}]; 注意: 不能改变它的起始值   如果要改变序列的起始值, 先把序列号删除掉, 再新建一个.   5. 删除序列号 SQL>DROP SEQUENCE sequence; 6. 不能用序列号的nextval和currval的地方 视图的查询 有distinct的查询 有group by,having,order by的查询 有子查询的查询 表里的缺省值 十四、创建视图 1. 视图的概念和优点 视图是基于一个或多个表及视图的一些查询语句, 它象显示数据的视窗, 它本身是不存储数据的.   视图可以限制数据库的访问, 更好的控制权限 使用户使用简单的查询语句 数据的非依赖性 同一数据的不同表现形式 2. 创建视图的语法 SQL> CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW  view[(alias[, alias]...)]   AS subquery   [WITH CHECK OPTION [CONSTRAINT constraint]]   [WITH READ ONLY] 参数解释: FORCE   表不存在时,视图仍然可以创建成功 WITH CHECK OPTION  只有符合视图定义的记录才能被插入或修改 WITH READ ONLY 不允许DML操作 Oracle8i以后创建视图可以用order by 3. 创建修改视图的例子 SQL> CREATE OR REPLACE VIEW salvu41  AS SELECT id, first_name FIRST,     last_name LAST, salary MONTHLY_SALARY      FROM s_emp  WHERE dept_id = 41; SQL> CREATE VIEW dept_sum_vu (name, minsal, maxsal, avgsal)  AS SELECT d.name, MIN(e.salary), MAX(e.salary),  AVG(e.salary) FROM s_emp e, s_dept d WHERE e.dept_id = d.id GROUP BY d.name; 注意: 如果用select * from table_name创建的视图   table_name的结构改变后 view要重建或compile后才能显示新的字段内容 4. 查询视图的数据字典 SQL> set long 1600; SQL> select view_name,text from user_views; 说明: 可以根据视图text_length来设置set long 数字; User_updatable_columns视图能查询视图里能被修改的字段 5.  简单和复杂的视图对比 特    性   简单视图 复杂视图 表的数量   一个   多个 有函数吗?   没有   有 有分组操作吗?   没有   有 有基于视图的DML操作吗?  有   没有 6. 在视图上可以用DML命令吗? 可以, 但有一定的限制条件 没有下面的情况, 可以删除view里的记录. group function, group by, distinct 没有上面和下面的情况, 可以修改view里的记录. 字段表达式, 例如: salary*12 含rownum的view 没有上面两种情况, 且view里含基表里所有非空字段的情况,  可以往view里插入记录.   7. 在视图里使用 WITH CHECK OPTION约束条件 SQL> create or replace view empvu41   as select * from s_emp where dept_id = 41  with check option constraint empvu41_ck; 如果运行下面命令会出错ora-01402 SQL> update empvu41 set dept_id=42 where id=16; 原因: 视图empvu41里规定只能看部门号为41的记录修改后会把记录排除在视图empvu41以外  与它的约束条件冲突 8. 删除视图 SQL> DROP VIEW view_name; 十五、创建索引 1.索引的概念 索引是数据库里的一种数据对象 它利用B*树, hash, bitmap结构直接快速地访问数据 它和表是分开存放的两个实体 索引创建好了后, 由系统自动调用和管理 2. 什么时候创建索引? 自动创建的索引:唯一关键字, 唯一的约束条件 手工需要创建的索引:大表查询时, sql语句where后经常用到的字段或字段组合   字段内容差别很大有大量NULL值表很大, 返回记录数较少 3. B*树索引的结构每个索引由字段值和指针或ROWID组成 4.创建索引的语法 CREATE INDEX  索引名 ON  表名 ( 字段1, [字段2, ……] )  TABLESPACE 表空间名; 5.创建索引的注意事项  创建索引时会加行级独占锁  一个表的索引最好不要超过三个 (特殊的大表除外)  最好用单字段索引  索引最好和表分不同的表空间存放  结合SQL语句的分析执行情况, 也可以建立多字段的组合索引和基于函数的索引  大表的索引会占用很大的存储空间  不要建唯一的索引, 而应该加唯一的约束条件 6.查询索引的方法 查询数据字典user_indexes和user_ind_columns 例子: SQL> SELECT ic.index_name, ic.column_name,   2         ic.column_position col_pos,ix.uniqueness   3  FROM user_indexes ix, user_ind_columns ic   4  WHERE ic.index_name = ix.index_name   5  AND ic.table_name = 'S_EMP'; 注意: 数据字典里存放的字符都是大写的. 7. 不用索引的地方 表很小 where后不经常使用的比较字段 表被频繁修改 返回记录数很多 where后含IS NULL /IS NOT NULL/ like ‘%输入符%’等条件 8. 重建索引的语法 ALTER INDEX 索引名 REBUILD TABLESPACE 原来表空间名 NOLOGGING; 定期重建索引可以减少索引的碎片, 更有效地使用表空间. 9. 删除索引 SQL> drop index 索引名; SQL> alter table 表名 drop constraint 约束名; 十六、控制用户访问 1.权限的类别 系统级权限: 针对整个系统操作的权限   如: 用户名/密码, 使用表空间的限额等 对象级权限: 针对某个具体object操作的权限  如: 针对某个表, 视图, 表的某个字段的select, update, delete权限 2. 查看当前数据库的用户信息 SQL>select username,default_tablespace,temporary_tablespace from dba_users;    查看在线用户信息 SQL>select count(*) “number”,username “current username”  from v$session group by username;   用户查看自己的缺省表空间SQL>select username,default_tablespace from user_users; 3. 创建新用户 SQL> create user username identified by password  default tablespace tablespace_name temporary tablespace temp  quota unlimited on tablespace_name  quota 1k on system  [quota 1k on other_tablespace_name ……] ; 给用户赋权限 SQL> grant connect, resource to username; 查看当前用户的权限角色 SQL>  select * from user_role_privs; 查看当前用户的系统权限和表级权限 SQL>  select * from user_sys_privs;SQL>  select * from user_tab_privs; 4 、常用的角色及其权限 CONNECT        8 privs 连上Oracle,做最基本操作 RESOURCE       8  privs 具有程序开发最的权限 DBA            114  privs 数据库管理员所有权限 EXP_FULL_DATABASE      5  privs 数据库整个备份输出的权限 IMP_FULL_DATABASE        64  privs 数据库整个备份输入的权限 查看角色明细的系统权限 SQL> select * from role_sys_privs; 5、改变老用户     可以改变老用户的密码, 缺省表空间, 临时表空间, 空间限额. SQL> alter user username identified by password        default tablespace tablespace_name       temporary tablespace temp         quota unlimited on tablespace_name       quota 1k on system       [quota 1k on other_tablespace_name ……] ; 撤销用户的角色或权限 SQL> revoke role_name or priv_name from username; 注意事项      撤消用户的角色dba时, 同时撤消了用户unlimited tablespace的系统权限, 切记要再次赋予resource角色给此用户 SQL> grant resource to username; 6、删除用户 如果用户下没有任何数据对象 SQL> drop user username; 如果用户下有数据对象 SQL> drop user username cascade; 注意事项     如果用户下有含clob,blob字段的表, 应该先删除这些表后,才能用cascade选项完全删除. 7、角色的概念和管理 角色是命名多个相关权限的组合. 能把它赋于其它的用户或角色我们能创建角色, 使权限管理更容易一些. 8、赋于系统的权限语法和例子 语法: SQL> GRANT sys_priv TO {user|role|PUBLIC}      [WITH ADMIN OPTION]; 例子: SQL> GRANT create session  TO sue, rich; SQL> GRANT create table To scott, manager; 注意: 如果用WITH ADMIN OPTION通过中间用户赋于的系统权限 中间用户删除后, 系统权限仍然存在. 9、赋于数据对象级的权限语法和例子 语法: SQL> GRANT object_priv [(columns)] ON object TO {user|role|PUBLIC}      [WITH GRANT OPTION]; 例子: SQL> GRANT select ON s_emp  TO sue, rich; SQL> GRANT update (name, region_id)  ON s_dept  TO scott, manager; 注意: 如果用WITH GRANT OPTION通过中间用户赋于的对象权限中间用户删除后,对象权限就不存在了.

相关的文章 教你如何从MySQL导出XLS数据库工具 Oracle入门――起动和关闭详解 SQL Server日志文件丢失的恢复方法 ORACLE中常用的SQL语法和数据对象 JSP中连接SQL 2000数据库的问题总结 Oracle初学者笔记(八)--Oracle中的对象(嵌套表与对象表) C#首席设计师Anders Hejlsberg专访(二) VSFTP＋MySQL虚拟用户配置过程讲解 学习在JSP中使用JavaBeans [Oracle]减少临时表使用空间的几种方法 我写的通用分页源代码，简单到你只需要写一条 sql 语句就可以了，超级 cool！ 在SQL server 中恢复数据的两种办法