oracle从入门到精通(PDF格式)-第7部分

快捷操作: 按键盘上方向键 ← 或 → 可快速上下翻页 按键盘上的 Enter 键可回到本书目录页 按键盘上方向键 ↑ 可回到本页顶部! 如果本书没有阅读完，想下次继续接着阅读，可使用上方 "收藏到我的浏览器" 功能 和 "加入书签" 功能！



　　　　



2。　　　删除　　



　　DELERT　FROM　TABLE　WHERE　条件；　　

　　删除所有符合　WHERE　条件的行。　　

　　基于子查询的删除。　　

　　注意及联删除。　　



3。　　　更新　　



　　UPDATE　TABLE　SET　列名1=值　1，列名2=值2。。。。。WHERE　条件；　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　第　31　页，共　106　页　


…………………………………………………………Page　32……………………………………………………………

资源来自网络，仅供学习！　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　Oracle　从入门到精通　



　　如果更新错误，要用　ROLLBACK　回滚。　　

　　利用子查询更新另外表中的数据，在　SET　后和　WHERE　后都可以利用子查询语句。　　

　　更新的时候要注意参照完整性约束。　　

　　子表的外键字段值必须是父表主键字段值的真子集。　　

　　　　

　　DEFAULT＋‘’　　默认值　　

　　在创建表的时候用，在第三个参数的位置上。　　

　　例：　　

　　CREATE　TABLE　A　　

　　　（　　　　C1　CHAR（10）　DEFAULT　　

　　　　　　　。　　

　　　　　　　。　　

　　　　　　　。　　　　　

　　）　　　　　　

　　在修改的时候，如果先给　DEFAULT赋值的话，可以直接用　列名=DEFAULT，使列名回复为默认值。　　

　　　　



4。　　　MERGE语句　　



　　提供了对表根据条件进行插入或者更新的能力。　　

　　如果行存在则执行　UPDATE，如果不存在则执行　INSERT。　　

　　　　

　　避免了单独的修改。　　

　　提高了性能，更便于使用。　　

　　对于数据库应用很有益。　　

　　　　

　　语法：别名　AL　　

　　MERGE　INTO　table_name（目的表）　table_alias　USING　（table/view/sub_query）//数据来源　

　　　（可以用子查询）　　alias（别名）　　

　　on（连接条件）　　

　　WHEN　MATCHED　THEN　UPDATE　SET（关键字）　（修改目的表）　　

　　目的表　AL。col1=原表　AL。col_var1　　

　　目的表　AL。col2=原表　AL。col2_var2　　

　　WHEN　NOT　MATCHEN　THEN（关键字）　　

　　INSERT　（目的表　AL。COL_LIST）　　

　　VALUES（原表AL。COL_VARS）；（插入原表）　　

　　create　table　newtable_name（新表）　as　select　*　from　oldtable_name（原表）　where　1=0；　　

　　　将原表中的结构复制到新表中，但具体的数据项不进行复制。　　

　　　　



5。　　　事务（transaction）：　　



　　由被逻辑组织在一起的多个　DML　语句的构成。　　

　　MIT：提交。　　

　　ROLLBACK：回滚。　　

　　SAVEPOINT：存储点，只在事务执行过程中有效，事务结束即被释放。　　

　　事务的组成：　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　第　32　页，共　106　页　


…………………………………………………………Page　33……………………………………………………………

资源来自网络，仅供学习！　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　Oracle　从入门到精通　



　　一组相同改变特性的　DML　语句；　　

　　一个　DDL：数据定义语句；　　

　　一个　DCL：权限控制语句；　　

　　　　

　　建立存储点：　　

　　例：SAVEPOINT　A；　　

　　　　　　　。　　

　　　　。　　

　　　　。　　

　　ROLLBACK　TO　A；　　

　　　　

　　事务的开始：　　

　　开始于第一个　DML　SQL语句执行时开始　　

　　结束的时候是在：　　

　　*、一个　MIT或　ROLLBACK　被执行的时候。　　

　　*、一个　DDL或　DCL　语句被执行（自动提交）注意*&*！（隐式）　　

　　*、用户退出SQLPLUS（隐式）　　　　　　　　　　　

　　*、系统崩溃（隐式）　　

　　　　

　　语句级回滚：　　

　　ORACLE　服务器执行隐式的存储点。　　

　　　　



2。4　创建和管理表　　



1、表　（TABLE）基本的存储单位，由行和列组成。　　



1。　　规则：　　

　　1。表名和列名（使用规则）：　　

　　2。必须是字母开头；　　

　　3。必须是1…30　的字符长度；　　

　　4。只能包括A…Z，a…z；0…9；_；；#；　　

　　5。在同一个用户下不能头重名的对象；　　

　　6。不能是ORACLE　的保留字；　　

　　　　

　　创建需求　　

　　必须有：　1。　CREATE　TABLE　权限；　　

　　　　　　　　　　2。足够的存储空间；　　

　　语法：　　

　　CREATE　TABLE　＇SCHEMA（方案）。＇TABLE　　

　　　（COL　DATATYPE　＇DEFAULT　默认值＇＇＇）；　　

　　　　

　　当前用户所有的表　　

　　select　table_name　from　user_tables；　　

　　当前用户所有的对象：　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　第　33　页，共　106　页　


…………………………………………………………Page　34……………………………………………………………

资源来自网络，仅供学习！　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　Oracle　从入门到精通　



　　desc　user_objects　　

　　当前用户对象的别名：　　

　　select　*　from　cat；　　

　　　　

　　字段类型：　　

　　　　　　　　　　　VARCHAR（size）　　　变长字符串类型　　

　　　　　　　　　　　CHAR（size）　　　　　　定长字符串类型　　

　　　　　　　　　　　NUMBER（p；s）p　　　　位整数，s　位小数　　　　　　　　

　　　　　　　　　　　DATE　　　　　

　　　　　　　　　　　DATETIME　　秒级最多可以到小数点后的　9　位　　

　　　　　　　　　　　TIMESTAMP　带有小数秒的日期　　

　　　　　　　　　　　TIMESTAMP　WITH　TIME　ZONE　带时区的类型　　

　　　　　　　　　　　TIMESTAMP　WITH　LOCAL　TIME　ZONE　带时区的并会进行时区转换的类型（同一时间在不　

　　　　　　　同地区看到的时间）　　

　　　　　　　　　　　INTERVAL　YEAR　TO　MONTH　按年和月的间隔存储的类型　　

　　　　　　　　　　　INTERVAL　‘123…2’　TEAR（3）　TO　MONTH　　

　　　　　　　　　　　INTERVAL　DAY　TO　SECOND　按天、小时、分和秒的间隔存储的类型　　

　　　　　　　　　　　INTERVAL　　　　

　　　　　　　　　　　LONG　变长的长字符串类型　　

　　　　　　　　　　　CLOB　字符类型　4GIGABYTES　　　　　　

　　　　　　　　　　　RAW　　二进制类型与　CHAR对应　　

　　　　　　　　　　　LONG　RAW　二进制类型与　LONG　对应　　

　　　　　　　　　　　BLOB　　　

　　　　　　　　　　　BFILE　以文件的形式存储在操作系统中　　

　　　　　　　　　　　ROWID　表中行的唯一地址（行地址）　　

　　　　



2、方案：一个用户所有对象的命名集合。　　



　　如果想访问其他用户或方案的表要加上用户或方案作为前缀。　　

　　　　

　　必须指明：　　

　　　　表名称；　　

　　　　列名，列类型和长度；　　

　　用户表：　　

　　　　被用户创建和维护的一些表；　　

　　　　包括了用户自己的信息；　　

　　数据字典表：　　

　　　　被　ORACLE　数据库创建和维护的一些表；　　

　　　　包括了数据库的信息；　　

　　　　



3、CTAS（子查询建表）：　　



　　CREATE　TABLE　table_name　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　第　34　页，共　106　页　


…………………………………………………………Page　35……………………………………………………………

资源来自网络，仅供学习！　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　Oracle　从入门到精通　



　　＇（col；coltype；。。）＇　　

　　as　subquery（子查询）；　　

　　　　

　　创建的表的列的数目匹配子查询的列的数目。　　

　　使用子查询的列的名字和默认值定义表。　　

　　注：　　

　　*、被创建表的字段名要遵循如果没有字段别名和子查询中没设置别名的话，使用子查询中的列　

　　名；　　

　　　　　如果有别名，使用别名；如果有字段列表（＇（col；coltype；。。）＇），在被创建的表中使用字　

　　段列表；　　

　　*、有字段列表与子查询的列要匹配。　　

　　*、当没有字段列表的时候，而在子查询中有表达式的时候一定要在表达式后要加上别名。　　　　　　　　　　　　　　　　　　　　　　　　　　　

　　*、只会把属性当中的非空属性复制过来，其他的比如约束条件、关联。。。都不会复制过来。　　

　　使用　ALTER　TABLE　语句可以：　　

　　*、在表中增加一个新列　　

　　语法：ALTER　TABLE　table　add　（col　datatype　＇default＇；。。。；。。。。）；　　

　　新增加的字段一定是放在表的最后。　　

　　*、修改表字段的类型和长度　　

　　ALTER　TABLE　table　modify　（col　datatype　＇default＇；。。。；。。。。）；　　

　　对默认值的修改只会影响到新插入的行。　　

　　如果字段下有值的话，类型的修改成功率很小（要修改数据类型，要修改的列必须为空，即没　

　　有数据项）。　　

　　CHAR　类型不能修改长度。　　

　　*、删除表字段　　

　　ALTER　TABLE　table　DROP　COLUMN　（COLUMN_NAME_LIST）；　　

　　9I2　版可以修改列名　　　

　　*、SET　UNUSED　设置字段为不可用。　　

　　原理：清楚掉字典信息（撤消存储空间），不可恢复。　　

　　可以使用　SET　UNUSED　选项标记一列或者多列不可用。　　

　　使用　DROP　SET　UNUSED　选项删除被被标记为不可用的列。　　

　　　　

　　语法：　　

　　ALTER　TABLE　table　SET　UNUSED　（COLlist　多个）　或者　　ALTER　TABLE　table　SET　UNUSED　COLUMN　　

　　col　单个；　　

　　ALTER　TABLE　table　DROP　UNUSED　COLUMNS；　　　

　　删除表：　　

　　删除关联：drop　table　table_name　cascade；　　

　　改对象名：　　

　　　　RENAME　对象原名　TO　要改的对象名；　　

　　注：　必须是对象的所有者才能进行改名的操作。　　

　　　　



4、截取：　　



　　不能回滚；　　

　　删除表中所有数据；　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　第　35　页，共　106　页　


…………………………………………………………Page　36……………………………………………………………

资源来自网络，仅供学习！　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　Oracle　从入门到精通　



　　释放存储空间；　　

　　语法：　　　　　　　　

　　TRUNCATE　TABLE　表名称；　　

　　DELETE　也可以删除所有行，但：　　

　　可以回滚。　　

　　不释放存储空间。　　

　　　　



5、给表加注释：MENT　　



　　ment　on　table　table_name　is　'注释内容'；　　

　　　　



6、约束条件：　　



　　如果经常用到约束条件的话，最好自己命名。　　

　　当定义约束的时候可以将定义的语句作为　CREATE　TABLE　中的参数的一部分来完成。　　

　　表级别约束定义：　　

　　CONSTRAINT　约束名　约束条件（字段名）　　

　　约束在表上强制了规则。　　

　　如果有参照的花，约束防止表的删除。　　

　　ORACLE　支持的约束条件：　　

　　1。NOT　NULL　非空　　

　　　　　　　　　　　特点：唯一一个只能在列级定义的约束条件。　　

　　2。UNIQUE　　唯一　　

　　　　　允许有空值（空值不做比较）；　　

　　　　　特点：当创建约束的时候，系统会自动创建对应其的索引。　　

　　3。PRIMARY　KEY　主键　　

　　　　　　特点：当创建约束的时候，系统会自动创建对应其的索引。　　

　　　　　　在一个表中只允许一个主键。　　　　　　　　　　　　　　　　

　　4。FOREIGN　KEY　外键　　　　

　　　　外键参照的一定是主表的主键或唯一键；　　

　　　　保证子表外键字段的值一定是主表中的被参照字段值的真子集；　　

　　　　当主表字段被参照的时候，其值不允许被直接删除。　　

　5。CONSTRAINT　约束名　FOREIGN　KEY　（外键字段名）　REFERENCES　主表名（主表字段名）；　　

　　　　　　　　如果在字段列表中定义外键就可以不写　FOREIGN　KEY　关键字。　　

　　　　　　　　如下格式：CONSTRAINT　约束名　REFERENCES　主表名（主表字段名）；　　

　6。ON　DELETE　CASCADE　　当主表的行被删除的时候，要删除子表中参照主表的行。　　

　　ALTER　TABLE　TABLE_NAME　DROP　（PK）　CASCADE　CONSTRAINTS；把作为主键的字段也同时删除了。　　

　7。ON　DELETE　SET　NULL　当主表的行被删除的时候，转换子表中的参照值为空。　　

　　　　

　　CHECK　　

　　定义一个每行都必须满足的条件。　　

　　CREATE　TABLE　table_name　　

　　（　　。。。。　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　第　36　页，共　106　页　


…………………………………………………………Page　37……………………………………………………………

资源来自网络，仅供学习！　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　Oracle　从入门到精通　



　　salary　number（10；2）；　　

　　CONSTRAINT　约束名　CHECK（SALARY》0）；　　

　　　　。。。。　　　　　

　　）；　　

　　　　

　　约束的使用：　　

　　约束的命名：给约束命名或者　ORACLE　服务器将使用　SYS_Cn　的格式为约束命名。　　

　　　　

　　创建时期：在创建表的同时或者在建表之后。　　

　　定义级别：　　

　　可以在表级定义或列级定义。　　

　　在数据字典中可以查看约束。　　

　　　　

　　使用　ALTER　TABLE　语句：　　

　　*、添加或者删除约束条件，但是不能修改约束条件。　　　　　　　　　　　　　　　　　　　　　就算列名上已经有约束条件，还可以　

　　继续添加约束条件的。　　

　　添加：ALTER　TABLE　table_name　ADD　＇CONSTRAINT　＇　约束名　约束条件（column）；　　

　　删除：ALTER　TABLE　table_name　drop　constraint　约束名；　　　　

　　ALTER　TABLE　table_name　PRIMARY　KEY　CASCADE；删除主键的时候，不用约束名。　　

　　*、启动或禁用约束条件　　

　　　　ALTER　TABLE　table_name　Disable　constraint　约束名；　禁用　　

　　　　ALTER　TABLE　table_name　ENABLE　constraint　约束名；　启用　　

　　*、通过　MODIFY　添加　NOT　NULL　约束条件（因为　NOT　NULL　为列级约束，只能用　MODIFY　添加）。　　

　　ALTER　TABLE　table_name　MODIFY（col　type　NOT　NULL）；　　

　　　　

　　查看约束条件：　　

　　//desc　user_constraints　　

　　OWNER　拥有者；　　

　　CONSTRAINT_NAME　约束名称　　

　　CONSTRAINT_TYPE　约束类型　　

　　SEARCH_CONDITION　　check　的条件　　

　　select　constraint_name；　constraint_type；search_condition；status　　　

　　from　user_constraint　where　table_name='b'；　　

　　　　



2。5。视图　（VIEW）　　　



　　　　　　　　　　　　一个或多个表的数据集的逻辑表示（虚表，不存储数据）　　

　　视图不能提高查询的性能。　　

　　分类：　　

　　简单　　

　　　　　　　数目：一个　　

　　　　　　　函数：不包含　　

　　　　　　　分组数据：不包含　　

　　　　　　　可以做　DML操作　　

　　复杂　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　第　37　页，共　106　页　


…………………………………………………………Page　38……………………………………………………………

资源来自网络，仅供学习！　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　Oracle　从入门到精通　



　　　　　　　数目：一个或多个　　

　　　　　　　函数：包含　　

　　　　　　　分组数据：包含　　

　　　　　　　不一定能做　DML　操作　　

　　视图也可以用　DESC　描述。　　

　　创建视图：　　

　　CREATE　＇or　replace（修改视图）＇　＇force/noforce＇　VIEW　view_name（col　coltype　；。。。。。。）　　

　　as　　　

　　subquery　　

　　＇WITH　CHECK　OPTION　＇CONSTRAINT　constraint＇＇　　

　　＇WITH　READ　ONLY＇CONSTRAINT　constraint＇＇；　　

　　　　

　　USER_VIEWS　关于视图的字典　　

　　修改视图：　　

　　CREATE　OR　REPLACE　原视图名　（字段列表）　　

　　AS　子查询；　　

　　　　

　　包含：　　

　　GROUP　BY　、DISTINCT、ROWNUM　　

　　不能对视图进行删除操作；　　

　　　　

　　包含：　　

　　GROUP　BY　　　

　　DISTINCT　　

　　ROWNUM　　

　　通过表达式定义的列不能对视图进行修改操作；　　

　　　　

　　包含：　　

　　GROUP　BY　　　

　　DISTINCT　　

　　ROWNUM　　

　　通过表达式定义的列　　

　　在视图中没有包含基表中的　NOT　NULL　列，不能对视图进行插入操作；　　

　　　　

　　使用视图的原因；　　

　　为了限制对数据的访问；　　

　　为了使复杂的查询变得简单；　　

　　提供了数据的独立性；　　

　　提供了对相同数据的不同显示；　　

　　　　

　　使用　WITH　CHECK　OPTION　子句创建视图　　

　　创建视图时通过　WITH　CHECK　OPTION　子句确保执行的　DML　语句不会引起数据不出现在视图上。　　

　　在对视图做　DML　操作的时候，一定要符合　WHERE子句中的条件。　　

　　CREATE　OR　REPLACE　VIEW　empvu20　as　select　*　from　employees　　

　　where　check　option　constraint　＇empvu20_ck＇；　　

　　　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　第　38　页，共　106　页　


…………………………………………………………Page　39……………………………………………………………

资源来自网络，仅供学习！　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　Oracle　从入门到精通　



　　WITH　READ　ONLY　　

　　不可以进行　DML　操作；　　

　　　　

　　删除视图：　　

　　DROP　VIEW　view_name；　　

　　　　

　　行内视图：是一个在　SQL　语句中使用的带有别名的子查询，该子查询放在　FROM　之后；　　

　　　　

　　TOP…N：　　

　　select　＇col_list＇；rownum　rank（排名）　　

　　from　（select　＇col_list＇　from　table_name　order　by　top…n_col）　　

　　where　rownum