Oracle 数据库操作总结v20120329

Oracle 数据库操作总结 目 录 1 登录操作 2 2 常用工具 2 3 SQL Plus 常用命令 2 4 常用数据类型 3 5 SQL 分类 3 6 数据操作 DML 4 6 1 查询语句 4 6 2 插入语句 6 6 3 修改记录 7 6 4 删除记录 7 6 5 ROLLBACK 回滚 8 6 6 左连接与右连接 8 7 标量函数 9 8 对象管理 DDL 12 8 1 表 12 8 2 视图 14 8 3 序列 14 8 4 表空间 15 8 4 1 表空间维护 15 8 4 2 表空间 17 8 4 3 段 18 9 约束 18 10 权限管理 DCL 20 10 1 用户管理 20 10 2 角色管理 20 10 3 权限管理 21 1 登录操作登录操作 Oracle 安装好后 系统默认有超级管理员 sys 和普通用户 scott scott 的密码是 tiger 如果要使用 scott 用户登录 则要做以下工作 1 新建一个服务别名 myorcl 名字可以自己命名 使服务别名指向要登录的数据库 2 用 sys 超级管理员给 scott 用户解锁 因为 scott 这个用户被锁住了 不能登录 解锁命令 ALTER USER scott ACCOUNT UNLOCK 3 用户 scott 用户登录 登录命令 sysplus scott tiger myorcl SQLPlus 工具登录命令 系统管理员登录 DOS 中执行命令 sqlplus username password serviceName as sysdba 普通用户登录 DOS 中执行命令 sqlplus username password serviceName 2 常用工具常用工具 SQLPlus 系统管理员登录 DOS 中执行命令 sqlplus username password serviceName as sysdba 普通用户登录 DOS 中执行命令 sqlplus username password serviceName SQLPLusW 可视化窗口 DOS 中执行命令 sqlplusw iSQLPlus 网页形式 访问网页地址 http 192 168 0 1 5560 isqlplus Enterprise Manager 网页形式 访问网页地址 http 192 168 0 1 5500 em 说明 访问 iSQLPlus 网页形式 和 Enterprise Manager 网页形式 时需要网址端口号 如果 忘记 可在此文件中查 ORACL HOME product 10 2 0 db 1 install portlist ini 3 SQL Plus 常用命令常用命令 SQL Plus 中的常用命令 select from tab 列出当前用户下的所有表 show user 察看当前连接的用户 connect scott password 采用 scott 的用户名 密码连接数据库 desc table name 察看 tableName 表结构 quit exit 退出 disconnect 断开连接 clear screen 清屏 相当于 Windows 下的 cls 命令 path 执行 path 指定的脚本文件 4 常用数据类型常用数据类型 VARCHAR2变长字符串 CHAR定长字符串 INTEGER整型 NUMBER m n 数字型 INT整型 FLOAT浮点数 DECIMAL十进制数字 DATE日期型 TIMESTAMP日期型 带有毫秒数 比 date 更精确 5 SQL 分类分类 DML Data Manipulation Language 数据操作语言 用于检索或修改数据 DDL Data Definition Language 数据定义语言 用于定义数据的结构 如创建修改或者删除数据库对象 DCL Data Control Language 数据控制语言 用于定义数据库用户的权限 6 数据操作 数据操作 DML 6 1 查询语句查询语句 基本数据查询 select 内容 from 表名 select distinct job from emp 去除重复行 限定查询 查询满足条件的行 SELECT 列名 FROM 表名 WHERE 条件 SELECT empno ename commFROM empWHERE comm IS NOT NULL 限定查询 IS NULL 和 IS NOT NULL 的使用 SELECT empno ename commFROM empWHERE comm IS NULL 查询谁没有奖金 限定查询 AND 的使用 查询工资大于 1500 并且可以领取奖金的雇员 SELECT empno ename sal commFROM EMPWHERE sal 1500 AND comm IS NOT NULL 限定查询 OR 的使用 查询工资大于 1500 和可以领取奖金的雇员 SELECT empno ename sal commFROM EMPWHERE sal 1500 OR comm IS NOT NULL 限定查询 使用 NOT 对条件整体取反 查询工资不大于 1500 并且不能领取资金的雇员 SELECT empno ename sal commFROM empWHERE NOT sal 1500 AND comm IS NOT NULL 限定查询 BETWEEN AND 的使用 查询基本工资大于等于 1500 并且小于等于 3000 的雇员 SELECT empno ename sal commFROM empWHERE sal BETWEEN 1500 AND 3000 限定查询 IN 的使用 查询出雇员编号是 7369 7499 7521 的雇员的具体信息 SELECT FROM empWHERE empno IN 7369 7499 7521 限定查询 NOT IN 的使用 查询出雇员编号不是 7369 7499 7521 的雇员的具体信息 SELECT FROM empWHERE empno NOT IN 7369 7499 7521 限定查询 LIKE 的使用 查询中雇员的名字第二个字符是 M 的雇员信息 SELECT empno ename comm salFROM empWHERE ename LIKE M 说明 匹配一个字符 匹配 0 个或多个字符 限定查询 LIKE 匹配日期 查询 1982 年入职的所有雇员的信息 SELECT FROM empWHERE hiredateLIKE 82 限定查询 LIKE 匹配数字 查询工资中包含 5 的雇员信息 SELECT FROM empWHERE salLIKE 5 对结果排序 ORDER BY 带有 ORDER BY 子句的 SQL 语句基本格式 SELECT 列 FROM 表 WHERE 条件 ORDER BY 列 查询员工资大于 1500 的信息 按工资排序 SELECT FROM emp WHERE sal 1500 ORDER BY sal 查询工资大于 1500 员工的信息 按工资降序 按雇佣日期升序排序 SELECT FROM emp WHERE sal 1500ORDER BY salDESC hiredate ASC 说明 ASC 排序 DESC 降序 默认 ASC 左 右外连接 查询员工编号 姓名 所在部门号 部门名称 将没有员工的部门也显示出来 SELECT e ename d deptno d dnameFROM emp e dept dWHERE e deptno d deptno 注 在左边 表示右连接 会列出右表中出现但是没有在左表中出现的行 交叉连接 CROSS JOIN 用来产生笛卡尔积的 SELECT FROM emp CROSS JOIN dept 自然连接 NATURAL JOIN 自动进行关联字段的匹配 SELECT FROM emp NATURAL JOIN dept USING 子句 直接指定操作关联列 SELECT FROM emp JOIN dept USING deptno ON 子句 用户自己编写连接条件 SELECT FROM emp JOIN dept ON emp deptno dept deptno RIGHT JOIN 右外连接 SELECT e empno e ename d deptno d dname FROM emp e RIGHT JOIN dept d ON e deptno d deptno 子查询 IN 的使用 查询和 SMITH 或 JONES 在同一部门 同一职位工作的员工 SELECT FROM emp WHERE deptno job IN SELECT deptno job FROM emp WHERE enameIN SMITH JONES 子查询 ANY 的使用 ANY 与 IN 操作符的效果一致 查询和 SMITH 或 JONES 在同一部门 同一职位工作的员工 SELECT FROM emp WHERE deptno job ANY SELECT deptno jopFROM emp WHERE ename IN SMITH JONES ANY 只要大于子查询中的任何一个值即可 SELECT FROM emp WHERE sal ANY SELECT MIN sal FROM EMP GROUP BY deptno ALL 比最大的值大 SELECT FROM emp WHERE sal ALL SELECT MIN sal FROM emp GROUP BY deptno ANY 只要小于子查询中的任何一个值即可 SELECT FROM emp WHERE sal ANY SELECT MIN sal FROM emp GROUP BY deptno ALL 比最小的值小 SELECT FROM emp WHERE sal 和 3 自然连接 自然连接 Natural join 是一种特殊的等值连接 它要求两个关系中进行 比较的分量必须是相同的属性组 并且在结果中把重复的属性列去掉 而等值连接并不去 掉重复的属性列 等值连接与自然连接的区别 1 等值连接中不要求相等属性值的属性名相同 而自然连接要求相等属性值的属性名 必须相同 即两关系只有在同名属性才能进行自然连接 2 等值连接不将重复属性去掉 而自然连接去掉重复属性 也可以说 自然连接是去 掉重复列的等值连接 2 外连接 1 左外连接 左边的表不加限制 2 右外连接 右边的表不加限制 3 全外连接 左右两表都不加限制 3 自连接 连接发生在一张表内 SQL 标准连接语法 标准连接语法 select table1 column table2 column from table1 inner left right full join table2 on table1 column1 table2 column2 例如 select a b from a left join b on a 编号 b 编号 INNER JOIN 表示内连接 LEFT JOIN 或 LEFT OUTER JOIN 表示左外连接 RIGHT JOIN 或 RIGHT OUTER JOIN 表示右外连接 FULL JOIN 或 FULL OUTER JOIN 表示完全外连接 不管匹配 ON 子句用于指定连接条件 如果主表的主键列和从表的外部键列名称相同 那么可以使用自然连接 natural join 关键 字自动匹配内连接操作 使用使用 符号的左右连接 符号的左右连接 右连接 select a studentno a studentname b classname from students a classes b where a classid b classid 左连接 select a studentno a studentname b classname from students a classes b where a classid b classid 写在主表的另一侧 主表的所有记录均会被显示 所在位置的另一侧为连接的方向 右连接说明等号右侧的所有记录均会被显示 无论 其在左侧是否得到匹配 左连接相反 注意 如果使用 from 子句指定内 外连接 则必须要使用 on 子句指定连接条件 如果使用 操作符指定外连接 则必须使用 where 子句指定连接条件 7 标量函数标量函数 转化大小写 select upper sddfa from dual select lower sddfa from dual 将首字母大写 其余小写 select initcap ename from emp 连接字符串 select count deptno from dept select contact hello woeld from dual select abc jkh sdf from dual 求子串 select substr 字符串 起始位 结束位 from dual 起始位写 0 或者 1 都可以代表第一位 select substr 字符串 起始位 from dual 从起始位开始往后截取 求字符串长度 select length ename from emp 字符串替换 select replace 原始字符串 想要被替换的字符串 替换字符串 from dual 输出字符串的后三位 select ename substr ename length ename 2 from dual select ename substr ename 3 from dual 四舍五入 select round 132 78454 from dual 四舍五入取整 select round 132 78345 2 from dual 四舍五入并且指定保留小数点的位数 select round 1323 7858416 2 from dual 小数不要 并且 整数位两位取整 本代码得 1300 截断小数位 select trunc 546541 151 from dual 不管四舍五入 直接截断小数 5465541 select trunc 789 536 2 from dual 截断小数 指定小数的数位 789 53 select trunc 789 536 2 from dual 截断小数 并且对整数进行截断 700 取余 select mod 10 3 from dual 取余数 1 运算规律 日期 数字 日期 日期 数字 日期 日期 日期 数字 显示系统时间 select sysdate from dual select to char sysdate yyyy mm dd hh24 mi ss from dual 显示某本门员工进入公司星期数 select empno ename round sysdate hiredate 7 from emp where deptno 10 在指定的日期加上指定的月数后的日期 select add months sysdate 2 from dual 求出给定日期范围的月数 select empno ename month between sysdate hiredate from emp 下一个给定的星期是那个日期 select next day sysdate 星期一 from dual 求出给定日期所在的 月份的最后一天的日期 select last day sysdate from dual 转换函数 转换成字符串 TO CHAR 查询雇员号 姓名 以及入职的年份 SELECT empno ename TO CHAR hiredate yyyy FROM emp 说明 yyyy 匹配年份 mm 匹配月份 dd 匹配日 使用 TO CHAR 设置日期的显示格式 SELECT empno ename TO CHAR hiredate yyyy mm dd FROM emp 使用 fm 去掉前导 0 SELECT empno ename TO CHAR hiredate fmyyyy mm dd FROM emp 通过 TO CHAR 设置数值的格式 SELECT empno ename TO CHAR sal 99 999 FROM emp 说明 9 代表 1 位数字 显示金额 SELECT empno ename TO CHAR sal 99 999 FROM emp 说明 代表美元 L 代表本地 转换成数字 TO NUMBER SELECT TO NUMBER 123 TO NUMBER 123 FROM dual 转换成日期 TO DATE SELECT TO DATE 2009 07 31 yyyy mm dd FROM dual 通用函数 查询每个员工的年收入 SELECT empno ename sal NVL comm 0 12 incomeFROM emp 说明 COMM 0 当 comm 的值为 null 时 用 0 替换 常用的列函数 SUM expression 求和 MAX expression 求最大值 MIN expression 求最小值 COUNT expression 统计记录数 COUNT DISTINCT COLNAME 统计去除重复行记录数 SQL 语句的执行顺序 FROM WHERE GROUP BY HAVING SELECT ORDER BY 8 对象管理对象管理 DDL 8 1 表表 1 创建表 create table 表名 字段 1 数值类型 defult xx 字段 2 数值类型 create table CUSTOMER CUSTOMER ID int not null CUSTOMER NO varchar 100 CUSTOMER NAME varchar 200 TYPE INT 客户类型 SOURCE TYPE ID int 来源 INDUSTRY TYPE ID int 行业 LEVEL TYPE ID int 等级 REGION TYPE ID int 区域 CREDIT TYPE ID int 信用 primary key CUSTOMER ID defult 是默认值 可写 可不写 create table 表名 2 as 表名 1 复制表 2 到 表 1 中 create table 表名 as 子查询 复制子查询结果这张表 当子查询结果没有记录时 这时 只会复制 表结构 数据类型 而不会增加数据 有时目的只是复制表结构 那么就使用这 种方法 比如查询语句中 使用 select 1 2 from 表名 2 删除表 drop table 表名 注意 delete 是删除表里面的数据 drop 是删除表结构 3 修改表结构 增加一列 alter table 表名 add 列名称 列数据类型 defult 默认值 注意 defult 和默认值 可写 可不写 删除列 alter table 表名 drop column 列名称 注意 不管有没有数据都会删除 修改列的数据类型 alter table 表名 modify 列名称 数据类型 defult 默认值 注意 修改的时候 和原来相同数据类型 长度 要求不能小于数据中 数据的最小长度 但是能增大 要想 改变数据类型的话 必须将字段下的数据全部删除 才可以更改数据类 型 表名重命名 rename 表名 旧 to 表名 新 4 截断表 truncate 表名 注意 清空表中的所有数据 立即释放资源 该操作不可回滚 ROWNUM 表示行号 是一个伪列 可以在每一张表中出现 应用 查询表中记录的前五行 SELECT FROM EMP WHERE ROWNUM BETWEEN 5 现在要查询从 6 到 10 条记录 使用子查询 SELECT FROM SELECT ROWNUM num empno ename salFROM emp eeWHERE rownum BETWEEN 6 AND 10 8 2 视图视图 1 创建视图 语法 CREATE VIEW 视图名称 AS 子查询 例如 创建部门 20 员工的雇员信息 包含 EMPNO ENAME SAL DEPTNO CREATE VIEW empv20 AS SELECT empno ename sal deptno FROM emp 注 当创建视图以后 可以像操作表一样的操作视图 注 当创建视图时 视图的操作会影响到对表的操作 这是不安全的 我 们可以采用如下选项来创建视图 WITH CHECK OPTION 不能更新创建视图的条件 WITH READ ONLY 创建只读视图 2 删除视图 语法 DROP VIEW 视图名称 DROP VIEW empv20 删除视图 empv20 注 当删除视图所在的表时 则视图也不能再被使用 8 3 序列序列 1 创建序列 语法 CREATE SEQUENCE 序列名称 MAXVALUE num NOMAXVALUE MINVALUE num NOMINVALUE INCREMENT BY num START WITH 10 CACHE num NOCACHE CYCLE NOCYCLE 创建序列 CREATE SEQUENCE myseq 序列的操作 nextVal 取得序列的下一个内容 currVal 取得当前序列的内容 select 序列名称 nextval from dual 创建表验证序列的操作 CREATE TABLE testseq next NUMBER CURR NUMBER INSERT INTO testseq VALUES myseq nextVal myseq currVal 将以上插入语句 执行 5 次 观察效果 不难得出结论 nextVal 每次都会加 1 而 currVal 都是取当前值 创建序列指定每次增长的增量 CREATE SEQUENCE myseq INCREMENT BY 2 创建序列指定开始的序列 默认的序列从 1 开始 CREATE SEQUENCE myseq INCREMENT BY 2 START WITH 10 创建一个循环序列 1 3 5 7 9 CREATE SEQUENCE myseq MAXVALUE 9 INCREMENT BY 2 START WITH 1 CACHE 2 CYCLE 8 4 表空间表空间 8 4 1 表空间维护表空间维护 1 创建表空间 CREATE TABLESPACE 表空间名称 DATAFILE 数据文件的全路径1 SIZE 表空间的大小 数据文件的全路径 2 SIZE 表空间的大小 例如 DROP TABLESPACE MYTableSpace CREATE TABLESPACE MYTableSpace DATAFILE D MYTableSpaceFile01 DBF SIZE 10M AUTOEXTEND ON NEXT 50M D MYTableSpaceFile02 DBF SIZE 10M AUTOEXTEND OFF ONLINE BLOCKSIZE 8192 LOGGING PERMANENT EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO 参数说明 AUTOEXTEND ON NEXT 50M AUTOEXTEND OFF 表空间文件不够用时自动扩展 其中 AUTOEXTEND 表明是否自动扩展 OFF ON 表示自动扩展是否被关闭 NEXT 表示数 据文件满了以后文件扩展的大小 2 删除表空间 DROP TABLESPACE 表空间名称 注 在进行相应操作时 应该具有相应的操作权限 DROP TABLESPACE data01 INCLUDING CONTENTS AND DATAFILES 级联删除表空间上的数据库对象和数据文件 表空间与段 请参看后面的 表空间与段 一节 3 改变表空间状态 1 使表空间脱机 ALTER TABLESPACE game OFFLINE 如果是意外删除了数据文件 则必须带有 RECOVER 选项 ALTER TABLESPACE game OFFLINE FOR RECOVER 2 使表空间联机 ALTER TABLESPACE game ONLINE 3 使数据文件脱机 ALTER DATABASE DATAFILE 3 OFFLINE 4 使数据文件联机 ALTER DATABASE DATAFILE 3 ONLINE 5 使表空间只读 ALTER TABLESPACE game READ ONLY 6 使表空间可读写 ALTER TABLESPACE game READ WRITE 4 扩展表空间 首先查看表空间的名字和所属文件 select tablespace name file id file name round bytes 1024 1024 0 total space from dba data files order by tablespace name 1 增加数据文件 ALTER TABLESPACE game ADD DATAFILE oracle oradata db GAME02 dbf SIZE 1000M 2 手动增加数据文件尺寸 ALTER DATABASE DATAFILE oracle oradata db GAME dbf RESIZE 4000M 3 设定数据文件自动扩展 ALTER DATABASE DATAFILE oracle oradata db GAME dbf AUTOEXTEND ON NEXT 100M MAXSIZE 10000M 设定后查看表空间信息 SELECT A TABLESPACE NAME A BYTES TOTAL B BYTES USED C BYTES FREE B BYTES 100 A BYTES USED C BYTES 100 A BYTES FREE FROM SYS SM TS AVAIL A SYS SM TS USED B SYS SM TS FREE C WHERE A TABLESPACE NAME B TABLESPACE NAME AND A TABLESPACE NAME C TABLESPACE 8 4 2 表空间表空间 Oracle 数据库中主要的表空间类型有 永久表空间 撤销表空间和临时表空间 永久表空间 撤销表空间 多个撤销表空间可以存在于一个数据库中 但在任何给定的时间内只有一个 撤销表空间可以是活动的 撤销表空间用于回滚事务 临时表空间 数据库中可以有多个临时表空间联机并处于活动状态 但在 Oracle 10g 之前 同一个用户的多个会话只可以使用同一个临时表空间 因为只有一个默认的临时表空间可 以被赋予用户 为了解决这个潜在的性能瓶颈 Oracle 现在支持临时表空间组 临时表空 间组即为一系列临时表空间 临时表空间组必须至少包含一个临时表空间 它不可以为空 一旦临时表空间组没有任何成员 它将不再存在 使用临时表空间组的一个最大优点是 向具有多个会话的单个用户提供如下功能 对每个会话使用不同的实际临时表空间 并不 是将单个临时表空间赋给用户 而是赋予临时表空间组 在创建用户的时候可以显性指定用户的默认永久表空间和临时表空间 也可以不显性指定 在早期的 Oracle 版本中 如果在创建用户时没有指定 DEFAULT TABLESPACE 和 TEMPORARY TABLEPACE 它们将默认为 SYSTEM 表空间 在 Oracle Database 10g 中 在创建数据库期间 CREATE DATABASE 命令可以包含子句 DEFAULT TABLESPACE tablespace 在创建用户时没有指定 DEFAULT TABLESPACE 和 TEMPORARY TABLEPACE 则用数据库的默认表空间 8 4 3 段段 估计一个数据库对象有一个段 比如一个表有一个表段 需要验证一下 Oracle 段的类型 段类型段类型名称名称 index partition 索引分区段 table partition 表分区段 table 表段 cluster 簇段 index 索引段 rollback 回退段 deferred rollback 延迟回退段 undo 撤销段 temporary 临时段 cache 高速缓存段 lob 大对象段 lobindex 大对象索引段 1 临时段 当执行如下操作时会产生临时数据 create index order by group by distinct union intersect minus analyze 2 回退段与撤销段 在 Oracle 9i 之前 回滚表空间创建之后 Oracle 随后创建回滚段供数据库使用 也可以手 工创建或者删除回滚段进行 通过如下命令指定使用特定的回滚段 set transaction user rollback segment 从 Oracle 9i 开始 Oracle 引入了自动管理的 Undo 表空间 用撤销段 undo segment 代 替回退段 rollback segment 如果选择使用自动的 Undo 表空间的管理 那么用户不再 能够创建或删除回滚段 也不再需要为事务指定回滚段 这一切将由 Oracle 自动进行 9 约束约束 constraint 约束名 约束 字段 主键约束 PRIMARY KEY CONSTRAINT persion pid pk PRIMARY KEY pid 非空约束 name VARCHAR2 30 NOT NULL 唯一约束 name VARCHAR2 30 UNIQUE NOT NULL 在创建表时声明 CONSTRAINT person name uk UNIQUE pid 在表的结尾声明 检查约束 check age NUMBER 3 NOT NULL CHECK age between 1 and 150 主外键约束 如一本书要属于一个人 CREATE TABLE person pid VARCHAR2 18 name VARCHAR2 30 NOT NULL age NUMBER 3 NOT NULL birthdate DATE sex VARCHAR2 2 DEFAULT 男 CONSTRAINT person pid pk PRIMARY KEY pid CONSTRAINT person name uk UNIQUE name CONSTRAINT person age ck CHECK age BETWEEN 1 AND 150 CONSTRAINT person sex ck CHECK sex IN 男 女 中 CREATE TABLE book bid NUMBER PRIMARY KEY NOT NULL bname V