ORACLE数据库操作总结大全.doc

1、 Oracle 数据库操作总结 目 录 1. 登录操作 . 2 2. 常用工具 . 2 3. SQL Plus 常用命令 . 2 4. 常用数据类型 : . 3 5. SQL 分类 . 3 6. 数据操作（ DML） . 3 6.1. 查询语句 . 3 6.2. 插入语句 . 6 6.3. 修改记录 . 7 6.4. 删除记录 . 7 6.5. ROLLBACK （回滚） . 7 6.6. 左连接与右连接 . 7 7. 标量函数的使用 . 9 8. 对象管理（ DDL） . 12 8.1. 表 . 12 8.2. 视图 . 13 8.3. 序列 . 14 8.4. 表空间 . 15 9. 约束

2、 . 15 10. 用户管理 . 16 11. 权限管理 . 17 12. 角色管理 . 18 13. 表空间与段 . 18 13.1. 表空间 . 18 13.2. 段 . 19 1. 登录操作 Oracle 安装好后，系统默认有超级管理员“ sys” 和 普通用户“ scott”。 scott 的密码是tiger。 如果 要使用 scott 用户登录，则要 做以下工作。 (1) 新建 一个服务别名 myorcl（名字 可以 自己命名） ，使服务 别名 指向要登录的数据库。 (2) 用 sys 超级管理员给 scott 用户解锁 （因为 scott 这个用户被锁住了，不能登录 ） 。 解锁命

3、令： ALTER USER scott ACCOUNT UNLOCK。 (3) 用户 scott 用户登录，登录命令： sysplus scott/tigermyorcl。 SQLPlus 工具登录命令： 系统管理员登录， DOS 中执行命令： sqlplus username/passwordserviceName as sysdba 普通用户登录， DOS 中执行命令： sqlplus username/passwordserviceName 2. 常用工具 SQLPlus 系统管理员登录， DOS 中执行命令： sqlplus username/passwordserviceName a

4、s sysdba 普通用户登录， DOS 中执行命令： sqlplus username/passwordserviceName SQLPLusW 可视化窗口 DOS 中执行命令： sqlplusw iSQLPlus(网页形式 ): 访问网页地址： http:/192.168.0.1:5560/isqlplus/ Enterprise Manager(网页形式 ): 访问网页地址： http:/192.168.0.1:5500/em/ 说明： 访问 iSQLPlus(网页形式 )和 Enterprise Manager(网页形式 )时需要网址端口号， 如果忘记， 可 在此文件中查： ORACL

5、_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 执行 pat

6、h 指定的脚本文件 4. 常用数据类型 : varchar2 变长字符串 char 定长字符串 Integer 整型 number ( m, n) 数字型 smallint 短整型 float 浮点数 decimal 十进制数字 date 日期型 5. SQL 分类 DML(Data Manipulation Language,数据操作语言 ) 用于检索或修改数据 DDL(Data Definition Language，数据定义 语言 ) 用于定义数据的结构，如创建修改或者删除数据库对象 DCL(Data Control Language，数据控制语言 ) 用于定义数据库用户的权限 6. 数

7、据操作 （ 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 的使用 查询工资大于 150

8、0，并且可以领取奖金的雇员 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 (sal1500 AND com

9、m 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

10、 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

11、 %5% 对结果排序 -ORDER BY 带有 ORDER BY 子句的 SQL 语句基本格式 SELECT 列 . FROM 表 WHERE 条件 ORDER BY 列 查询员工资大于 1500 的信息，按工资排序 SELECT * FROM emp WHERE sal1500 ORDER BY sal 查询工资大于 1500 员工的信息，按工资降序 ,按雇佣日期升序排序 SELECT * FROM emp WHERE sal1500ORDER BY salDESC,hiredate ASC 说明 : ASC 排序， DESC 降序，默认 ASC 左、右外连接 查询员工编号，姓名，所在部门号

12、，部门名称，将没有员工的部门也显示出来 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 em

13、p 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 d

14、eptno,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 B

15、Y deptno); ALL：比最大的值大 SELECT * FROM emp WHERE salALL(SELECT MIN(sal) FROM emp GROUP BY deptno); 、 =、 、 !。 （ 3） 自然连接 ： 自然连接 (Natural join)是一种特殊的等值连接，它要求两个关系中进行比较的分量必须是相同的属性组，并且在结果中把重复的属性列去掉。而等值连接并不去掉重复的属性列。 等值连接与自然连接的区别： （ 1） 等值连接中不要求相等属性值的属性名相同，而自然连接要求相等属性值的属性名必须 相同，即两关系只有在同名属性才能进行自然连接。 （ 2） 等值连接不将重

16、复属性去掉，而自然连接去掉重复属性，也可以说，自然连接是去掉重复列的等值连接。 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 lef

17、t 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.cla

18、ssname 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 子句指定内、外连接，则必须要

19、使用 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; 求子串 ： sel

20、ect 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（ enam

21、e， -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 截断小数 指定

22、小数的数位 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

23、） /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 匹配日