oracle 常用操作 新手入门

joozh

　　1.建表空间
　　create tablespace data11
　　datafile'/oracle/10g/data11.dbf'size50m
　　修改表空间大小
　　alter database datafile'/oracle/10g/data11.dbf'resize 60m
　　删除表空间
　　droptablespace data11 including contents and datafiles
　　2 建undo表空间
　　create undo tablespace undodata
　　datafile'/oracle/10g/undodata.dbf'size50m
　　3.建立临时表空间
　　createtemporarytablespacetempdata
　　tempfile'/oracle/10g/tempdata.dbf'size50m
　　4. 建用户profile文件
　　create profile student limit
　　failed_login_attempts    3
　　password_lock_time   5
　　password_life_time    30
　　5.      建用户
　　Create User username
　　Identified bypassword
　　DefaultTablespace data11
　　TemporaryTablespace tempdata
　　Profile student
　　Quota integer/unlimited on tablespace;
　　6.      给用户授予权限
　　grant create session tousername
　　将role这个角色授与username,也就是说，使username这个用户可以管理和使用role
　　所拥有的资源
　　GRANT role TO username;
　　7.      修改用户
　　Alter User username
　　Identified by 口令
　　Default Tablespace data01
　　Temporary Tablespace temp_data
　　Profile student
　　Quota integer/unlimited on tablespace;

　　1、修改口令字：  SQL>Alter user acc01>
　　2、修改用户缺省表空间：  SQL>>
　　3、修改用户临时表空间    SQL>>
　　4、强制用户修改口令字：  SQL>>
　　5、将用户加锁：          SQL>>
　　SQL>>　　8.      删除用户
　　SQL>dropuser 用户名;  //用户没有建任何实体
　　SQL> dropuser 用户名CASCADE;  // 将用户及其所建实体全部删除
　　*1. 当前正连接的用户不得删除。
　　查看用户权限
　　查看所有用户
　　SELECT * FROM DBA_USERS;
　　SELECT * FROM ALL_USERS;
　　SELECT * FROM USER_USERS;
　　查看用户系统权限
　　SELECT * FROM DBA_SYS_PRIVS;
　　SELECT * FROM USER_SYS_PRIVS;
　　查看用户对象或角色权限
　　SELECT * FROM DBA_TAB_PRIVS;
　　SELECT * FROM ALL_TAB_PRIVS;
　　SELECT * FROM USER_TAB_PRIVS;
　　查看所有角色
　　SELECT * FROM DBA_ROLES;
　　查看用户或角色所拥有的角色
　　SELECT * FROM DBA_ROLE_PRIVS;
　　SELECT * FROM USER_ROLE_PRIVS;
　　-------遇到no privileges on tablespace'tablespace '
　　alter useruserquota 10M[unlimited] on tablespace;
　　建表
　　--学生表
　　create table donkey(    ---表名
　　xh       number(4),   --学号
　　xm   varchar2(20),   --姓名
　　sex      char(2),     --性别
　　birthday date,         --出生日期
　　sal      number(7,2)   --奖学金
　　);
　　--班级表
　　CREATE TABLEclass(

　　>　　cName VARCHAR2(40)
　　);
　　修改表
　　添加一个字段    SQL>ALTER TABLE donkeyadd (home VARCHAR2(40));
　　修改一个字段的长度SQL>ALTER TABLE donkey MODIFY (xm VARCHAR2(30));
　　修改字段的类型/或是名字（不能有数据）
　　SQL>ALTER TABLEdonkey modify (xm CHAR(30));
　　删除一个字段  不建议做(删了之后，顺序就变了。加就没问题，应为是加在后面)
　　SQL>ALTER TABLEdonkey DROP COLUMN sal;
　　修改表的名字   很少有这种需求     SQL>RENAME donkey TO stu;
　　删除表  SQL>DROP TABLE donkey;
　　添加数据
　　所有字段都插入数据
　　INSERT INTO donkeyVALUES ('001', '张三', '男', '01-5月-05', 10);
　　oracle中默认的日期格式‘dd-mon-yy’ dd日子（天） mon 月份  yy  2位的年 ‘09-6月-99’ 1999年6月9日
　　修改日期的默认格式（临时修改，数据库重启后仍为默认；如要修改需要修改注册表）
　　ALTER SESSION SETNLS_DATE_FORMAT ='yyyy-mm-dd';
　　修改后，可以用我们熟悉的格式添加日期类型：
　　INSERT INTO donkeyVALUES ('002', 'MIKE', '男', '1905-05-06', 10);
　　插入部分字段
　　INSERT INTOdonkey(xh, xm, sex) VALUES ('003', 'JOHN', '女');
　　插入空值
　　INSERT INTOdonkey(xh, xm, sex, birthday) VALUES ('004', 'MARTIN', '男', null);
　　查询donkey表里birthday为null的记录，怎么写sql呢？
　　错误写法：select * from donkey where birthday = null;
　　正确写法：select * from donkey where birthday is null;
　　如果要查询birthday不为null,则应该这样写：
　　select * from donkeywhere birthday is not null;
　　修改数据
　　修改一个字段    UPDATE donkey SET sex = '女' WHERE xh = '001';
　　修改多个字段    UPDATE donkey SET sex = '男', birthday = '1984-04-01' WHERE xh= '001';
　　修改含有null值的数据 ,不要用 = null 而是用 is null；
　　SELECT * FROMstudent WHERE birthday IS null;
　　删除数据
　　DELETE FROM donkey;
　　删除所有记录，表结构还在，写日志，可以恢复的，速度慢。Delete 的数据可以恢复。
　　savepoint a; --创建保存点
　　DELETE FROM donkey;rollback to a;  --恢复到保存点
　　一个有经验的DBA，在确保完成无误的情况下要定期创建还原点。
　　DROP TABLE donkey;--删除表的结构和数据；
　　delete from donkeyWHERE xh = '001'; --删除一条记录；
　　truncate TABLE donkey;--删除表中的所有记录，表结构还在，不写日志，无法找回删除的记录，速度快。
　　五：oracle表查询(1)
　　oracle表基本查询
　　介绍
　　在我们讲解的过程中我们利用scott用户存在的几张表（emp，dept）为大家演示如何使用select语句，select语句在软件编程中非常有用，希望大家好好的掌握。
　　emp 雇员表
　　clerk  普员工
　　salesman 销售
　　manager  经理
　　analyst 分析师
　　president  总裁
　　mgr  上级的编号
　　hiredate 入职时间
　　sal 月工资
　　comm 奖金
　　deptno 部门
　　dept部门表
　　deptno 部门编号
　　accounting 财务部
　　research  研发部
　　operations 业务部
　　loc 部门所在地点
　　salgrade   工资级别
　　grade    级别
　　losal    最低工资
　　hisal    最高工资
　　简单的查询语句
　　查看表结构    DESC emp;
　　查询所有列    SELECT * FROM dept;
　　切忌动不动就用select *
　　SET TIMING ON; 打开显示操作时间的开关，在下面显示查询时间。
　　CREATE TABLEusers(userId VARCHAR2(10), uName VARCHAR2 (20), uPassw VARCHAR2(30));
　　INSERT INTO usersVALUES('a0001', '啊啊啊啊','aaaaaaaaaaaaaaaaaaaaaaa');
　　--从自己复制，加大数据量 大概几万行就可以了  可以用来测试sql语句执行效率
　　INSERT INTO users(userId,UNAME,UPASSW) SELECT * FROM users;
　　SELECT COUNT (*)FROM users;统计行数
　　查询指定列     SELECT ename, sal, job,deptno FROM emp;
　　如何取消重复行 DISTINCT
　　SELECT DISTINCTdeptno, job FROM emp;
　　查询SMITH所在部门，工作，薪水
　　SELECTdeptno,job,sal FROM emp WHERE ename = 'SMITH';
　　注意：oracle对内容的大小写是区分的，所以ename='SMITH'和ename='smith'是不同的
　　使用算术表达式 nvl  null
　　问题：如何显示每个雇员的年工资？
　　SELECTsal*13+nvl(comm, 0)*13 "年薪" , ename, comm FROM emp;
　　使用列的别名
　　SELECT ename"姓名",sal*12 AS "年收入" FROMemp;
　　如何处理null值
　　使用nvl函数来处理
　　如何连接字符串（||）
　　SELECT ename  || ' is a ' || job FROM emp;
　　使用where子句
　　问题：如何显示工资高于3000的 员工？
　　SELECT * FROM empWHERE sal > 3000;
　　问题：如何查找1982.1.1后入职的员工？
　　SELECTename,hiredate FROM emp WHERE hiredate >'1-1月-1982';
　　问题：如何显示工资在2000到3000的员工？
　　SELECT ename,salFROM emp WHERE sal >=2000 AND sal 500 or job = 'MANAGER') and ename LIKE 'J%';
　　使用order by 字句   默认asc
　　问题：如何按照工资的从低到高的顺序显示雇员的信息？
　　SELECT * FROM empORDER by sal;
　　问题：按照部门号升序而雇员的工资降序排列
　　SELECT * FROM empORDER by deptno, sal DESC;
　　使用列的别名排序
　　问题：按年薪排序
　　select ename,(sal+nvl(comm,0))*12 "年薪" from emp order by "年薪" asc;
　　别名需要使用“”号圈中,英文不需要“”号
　　分页查询
　　等学了子查询再说吧。。。。。。。。
　　Clear 清屏命令
　　oracle表复杂查询
　　说明
　　在实际应用中经常需要执行复杂的数据统计，经常需要显示多张表的数据，现在我们给大家介绍较为复杂的select语句
　　数据分组——max，min， avg， sum， count
　　问题：如何显示所有员工中最高工资和最低工资？
　　SELECTMAX(sal),min(sal) FROM emp e;
　　最高工资那个人是谁？
　　错误写法：select ename, sal from emp where sal=max(sal);
　　正确写法：select ename, sal from emp where sal=(select max(sal) from emp);
　　注意：select ename, max(sal) from emp;这语句执行的时候会报错，说ORA-00937：非单组分组函数。因为max是分组函数，而ename不是分组函数.......
　　但是select min(sal), max(sal) from emp;这句是可以执行的。因为min和max都是分组函数，就是说：如果列里面有一个分组函数，其它的都必须是分组函数，否则就出错。这是语法规定的问题：如何显示所有员工的平均工资和工资总和？
　　问题：如何计算总共有多少员工问题：如何
　　扩展要求：
　　查询最高工资员工的名字，工作岗位
　　SELECT ename, job,sal FROM emp e where sal = (SELECT MAX(sal) FROM emp);
　　显示工资高于平均工资的员工信息
　　SELECT * FROM empe where sal > (SELECT AVG(sal) FROM emp);
　　group by 和 having子句
　　group by用于对查询的结果分组统计，
　　having子句用于限制分组显示结果。
　　问题：如何显示每个部门的平均工资和最高工资？
　　SELECT AVG(sal),MAX(sal), deptno FROM emp GROUP by deptno;
　　（注意：这里暗藏了一点，如果你要分组查询的话，分组的字段deptno一定要出现在查询的列表里面，否则会报错。因为分组的字段都不出现的话，就没办法分组了）
　　问题：显示每个部门的每种岗位的平均工资和最低工资？
　　SELECT min(sal),AVG(sal), deptno, job FROM emp GROUP by deptno, job;
　　问题：显示平均工资低于2000的部门号和它的平均工资？
　　SELECT AVG(sal),MAX(sal), deptno FROM emp GROUP by deptno having AVG(sal) < 2000;
　　对数据分组的总结
　　1 分组函数只能出现在选择列表、having、order by子句中(不能出现在where中)
　　2 如果在select语句中同时包含有group by, having, order by 那么它们的顺序是group by, having, order by
　　3 在选择列中如果有列、表达式和分组函数，那么这些列和表达式必须有一个出现在groupby 子句中，否则就会出错。
　　如SELECT deptno, AVG(sal), MAX(sal) FROM emp GROUP by deptno HAVING AVG(sal)< 2000;
　　这里deptno就一定要出现在groupby 中
　　多表查询
　　说明
　　多表查询是指基于两个和两个以上的表或是视图的查询。在实际应用中，查询单个表可能不能满足你的需求，（如显示sales部门位置和其员工的姓名），这种情况下需要使用到（dept表和emp表）
　　问题：显示雇员名，雇员工资及所在部门的名字【笛卡尔集】？
　　规定：多表查询的条件是至少不能少于 表的个数-1 才能排除笛卡尔集
　　（如果有N张表联合查询，必须得有N-1个条件，才能避免笛卡尔集合）
　　SELECT e.ename,e.sal, d.dname FROM emp e, dept d WHERE e.deptno = d.deptno;
　　问题：显示部门号为10的部门名、员工名和工资？
　　SELECT d.dname,e.ename, e.sal FROM emp e, dept d WHERE e.deptno = d.deptno and e.deptno = 10;
　　问题：显示各个员工的姓名，工资及工资的级别？
　　先看salgrade的表结构和记录
　　SQL>select *from salgrade;
　　GRADE         LOSAL          HISAL
　　-------------   -------------   ------------
　　1          700           1200
　　2          1201          1400
　　3          1401          2000
　　4          2001          3000
　　5         3001          9999
　　SELECT e.ename,e.sal, s.grade FROM emp e, salgrade s WHERE e.sal BETWEEN s.losal AND s.hisal;
　　扩展要求：
　　问题：显示雇员名，雇员工资及所在部门的名字，并按部门排序？
　　SELECT e.ename,e.sal, d.dname FROM emp e, dept d WHERE e.deptno = d.deptno ORDER by e.deptno;
　　（注意：如果用group by，一定要把e.deptno放到查询列里面）
　　自连接
　　自连接是指在同一张表的连接查询
　　问题：显示某个员工的上级领导的姓名？
　　比如显示员工‘FORD’的上级
　　SELECTworker.ename, boss.ename FROM emp worker,emp boss WHERE worker.mgr = boss.empnoAND worker.ename = 'FORD';
　　子查询
　　什么是子查询
　　子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询。
　　单行子查询
　　单行子查询是指只返回一行数据的子查询语句
　　请思考：显示与SMITH同部门的所有员工？
　　思路：
　　1 查询出SMITH的部门号
　　select deptno fromemp WHERE ename = 'SMITH';
　　2 显示
　　SELECT * FROM empWHERE deptno = (select deptno from emp WHERE ename = 'SMITH');
　　数据库在执行sql 是从左到右扫描的， 如果有括号的话，括号里面的先被优先执行。
　　多行子查询
　　多行子查询指返回多行数据的子查询
　　请思考：如何查询和部门10的工作相同的雇员的名字、岗位、工资、部门号
　　SELECT DISTINCTjob FROM emp WHERE deptno = 10;
　　SELECT * FROM empWHERE job IN (SELECT DISTINCT job FROM emp WHERE deptno = 10);
　　（注意：不能用job=..，因为等号=是一对一的）
　　在多行子查询中使用all操作符
　　问题：如何显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号？
　　SELECT ename, sal,deptno FROM emp WHERE sal > all (SELECT sal FROM emp WHERE deptno = 30);
　　扩展要求：
　　大家想想还有没有别的查询方法。
　　SELECT ename, sal,deptno FROM emp WHERE sal > (SELECT MAX(sal) FROM emp WHERE deptno = 30);
　　执行效率上，函数高得多
　　在多行子查询中使用any操作符
　　问题：如何显示工资比部门30的任意一个员工的工资高的员工姓名、工资和部门号？
　　SELECT ename, sal,deptno FROM emp WHERE sal > ANY (SELECT sal FROM emp WHERE deptno = 30);
　　扩展要求：
　　大家想想还有没有别的查询方法。
　　SELECT ename, sal,deptno FROM emp WHERE sal > (SELECT min(sal) FROM emp WHERE deptno = 30);
　　多列子查询
　　单行子查询是指子查询只返回单列、单行数据，多行子查询是指返回单列多行数据，都是针对单列而言的，而多列子查询是指查询返回多个列数据的子查询语句。
　　请思考如何查询与SMITH的部门和岗位完全相同的所有雇员。
　　SELECT deptno, jobFROM emp WHERE ename = 'SMITH';
　　SELECT * FROM empWHERE (deptno, job) = (SELECT deptno, job FROM emp WHERE ename = 'SMITH');
　　在from子句中使用子查询
　　请思考：如何显示高于自己部门平均工资的员工的信息
　　思路：
　　1. 查出各个部门的平均工资和部门号
　　SELECT deptno,AVG(sal) mysal FROM emp GROUP by deptno;
　　2. 把上面的查询结果看做是一张子表
　　SELECT e.ename,e.deptno, e.sal, ds.mysal FROM emp e, (SELECT deptno, AVG(sal) mysal FROM empGROUP by deptno) ds WHERE e.deptno = ds.deptno AND e.sal > ds.mysal;
　　如何衡量一个程序员的水平？
　　网络处理能力，数据库， 程序代码的优化程序的效率要很高
　　小总结：
　　在这里需要说明的当在from子句中使用子查询时，该子查询会被作为一个视图来对待，因此叫做内嵌视图，当在from子句中使用子查询时，必须给子查询指定别名。
　　注意：别名不能用as，如：SELECTe.ename, e.deptno, e.sal, ds.mysal FROM emp e, (SELECT deptno, AVG(sal) mysalFROM emp GROUP by deptno) as ds WHERE e.deptno = ds.deptno AND e.sal >ds.mysal;
　　在ds前不能加as，否则会报错  （给表取别名的时候，不能加as；但是给列取别名，是可以加as的）
　　分页查询
　　按雇员的id号升序取出
　　oracle的分页一共有三种方式
　　1.根据rowid来分
　　select * from t_xiaoxi where rowid in (selectrid from (select rownum rn, rid from(select rowid rid, cid from t_xiaoxi orderby cid desc) where rownum9980) order by cid desc;
　　执行时间0.03秒
　　2.按分析函数来分
　　select * from (select t.*, row_number()over(order by cid desc) rk from t_xiaoxi t) where rk9980;
　　执行时间1.01秒
　　3.按rownum来分
　　select * from (select t.*,rownum rnfrom(select * from t_xiaoxi order by cid desc)t where rownum9980;
　　执行时间0.1秒
　　其中t_xiaoxi为表名称，cid为表的关键字段，取按cid降序排序后的第9981-9999条记录，t_xiaoxi表有70000多条记录。
　　个人感觉1的效率最好，3次之，2最差。
　　//测试通过的分页查询okokok
　　select * from(select a1.*, rownum rn from(select ename,job from emp) a1 whererownum=5;
　　下面最主要介绍第三种：按rownum来分
　　1. rownum 分页
　　SELECT * FROM emp;
　　2. 显示rownum[oracle分配的]
　　SELECT e.*, ROWNUMrn FROM (SELECT * FROM emp) e;
　　rn相当于Oracle分配的行的ID号
　　3.挑选出6—10条记录
　　先查出1-10条记录
　　SELECT e.*, ROWNUMrn FROM (SELECT * FROM emp) e WHERE ROWNUM =6是不行的，
　　4. 然后查出6-10条记录
　　SELECT * FROM(SELECT e.*, ROWNUM rn FROM (SELECT * FROM emp) e WHERE ROWNUM = 6;
　　5. 几个查询变化
　　a. 指定查询列，只需要修改最里层的子查询
　　只查询雇员的编号和工资
　　SELECT * FROM(SELECT e.*, ROWNUM rn FROM (SELECT ename, sal FROM emp) e WHERE ROWNUM = 6;
　　b. 排序查询，只需要修改最里层的子查询
　　工资排序后查询6-10条数据
　　SELECT * FROM (SELECTe.*, ROWNUM rn FROM (SELECT ename, sal FROM emp ORDER by sal) e WHERE ROWNUM= 6;
　　用查询结果创建新表
　　这个命令是一种快捷的建表方式
　　CREATE TABLEmytable (id, name, sal, job, deptno) as SELECT empno, ename, sal, job, deptnoFROM emp;
　　创建好之后，desc mytable；和select * from mytable;看看结果如何？
　　合并查询
　　合并查询
　　有时在实际应用中，为了合并多个select语句的结果，可以使用集合操作符号union，union all，intersect，minus
　　多用于数据量比较大的数据局库，运行速度快。
　　1). union
　　该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中重复行。
　　SELECT ename, sal,job FROM emp WHERE sal >2500
　　UNION
　　SELECT ename, sal,job FROM emp WHERE job = 'MANAGER';
　　2).union all
　　该操作符与union相似，但是它不会取消重复行，而且不会排序。
　　SELECT ename, sal,job FROM emp WHERE sal >2500
　　UNION ALL
　　SELECT ename, sal,job FROM emp WHERE job = 'MANAGER';
　　该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中重复行。
　　3). intersect
　　使用该操作符用于取得两个结果集的交集。
　　SELECT ename, sal,job FROM emp WHERE sal >2500
　　INTERSECT
　　SELECT ename, sal,job FROM emp WHERE job = 'MANAGER';
　　4). minus
　　使用改操作符用于取得两个结果集的差集，他只会显示存在第一个集合中，而不存在第二个集合中的数据。
　　SELECT ename, sal,job FROM emp WHERE sal >2500
　　MINUS
　　SELECT ename, sal,job FROM emp WHERE job = 'MANAGER';
　　（MINUS就是减法的意思）
　　创建数据库有两种方法：
　　1). 通过oracle提供的向导工具。√
　　database Configuration Assistant  【数据库配置助手】
　　2).我们可以用手工步骤直接创建。
　　ORACLE dept与emp查询练习
　　1、查询除去 SALESMAN职业,平均工资超过$1500的部门
　　2、查询雇员姓名为King的雇员号、雇员姓名、雇员所在的部门号、雇员所在部门的地址
　　3、查询在纽约工作的员工姓名、部门号和工资信息
　　4、查询在accounting或sale部门工作的雇员姓名、工种、工资情况
　　NVL函数
　　1.select deptno,job, avg(sal) from emp group by deptno, job having avg(sal)(select sal from emp whereename=upper('smith'));
　　--列出所有雇员的姓名及其直接上级的姓名
　　select e.ename,m.ename from emp e,emp m where e.mgr=m.empno(+);
　　--列出所有“CLERK”（办事员）的姓名及其部门名称
　　select ename,dname from emp e left join dept d on e.deptno=d.deptnowhere job=upper('clerk');
　　--列出从事“SALES”（销售）工作的雇员的姓名，假定不知道销售部的部门编号
　　select ename from emp where deptno = (select deptno from dept wheredname=uppder('SALES'))
　　--列出薪金高于公司平均水平的所有雇员
　　select ename from emp where sal>(select avg(sal) from emp);
　　--列出与“SCOTT”从事相同工作的所有雇员
　　select ename from emp where job=(select job from emp whereename=upper('scott'));