1、玩转oracle从入门到熟练第1章 Oracle系统使用1.1. 连接命令连接命令 conn 用户名/密码网络服务名特权用户连接，必须带上as sysydba(超级)或者as sysoper(管理员)SQLshow user;SQLconn system/manager;1.2. 断开连接 disc 断开数据库连接 passw修改用户密码alter user scott identified by tiger;1.3. Oracle 账号scott被锁定，1.3.1. conn sys/lsy403 as sysdba;/以dba身份登录1.3.2. alter user scott acco
2、unt unlock;/解锁1.3.3. conn scott/tiger;/弹出修改密码SQL start d:aa.sql;SQLspool d:bb.sql;SQLspool off;Select * from emp where ename=SMITH第2章 用户的管理2.1. 用户的管理2.1.1. 创建用户：create user xiaoming identified by m123;(密码以字母开头)2.1.2. 删除用户：在删除用户的时候，如果用户已经创建了表，那就需要在删除时带上一个参数cascade例如：drop user 用户名 cascade;2.1.3. 赋予权限g
3、rant connect to xiaoming;系统权限：用户对数据库的相关权限（140多种）对象权限：用户对其他用户的数据对象操作的权限（25种）角色：预定义角色（connect , dba, resource在任一表空间建表）希望小明能查询scottd的emp表Grant select on emp to xiaoming;授权成功登录xiaoming账号：select * from scott.emp;grant update(all) on emp to xiaoming;2.1.4. 收回权限scott希望回收xiaoming对emp表的查询权限Revoke select on e
4、mp from xiaoming;(用scott账号登陆)2.1.5. 权限传递grant select on emp to xiaoming with grant option;如果是对象权限：with grant option如果是系统权限：with admin optionconn system/manager;create user xiaoming2 identified by m123;用户已创建grant connect to xiaoming2;conn xiaoming/m123;grant select on scott.emp to xiaoming2;第3章 管理用户3
5、.1. 用户管理Profile管理用户口令创建profile文件create profile lock_account limit failed_login_attempts 3 password_lock_time 2;(密码尝试3次，锁定2天)alter user xiaoming profile lock_account;3.2. 给账户（用户）解锁alter user xiaoming account unlock;3.3. 终止口令给创建的用户tea创建一个profile 文件，要求用户每隔10天修改自家的登陆密码，宽限期2天create profile myprofile limi
6、t password_life_time 10 password_grace_time 2;alter user tea profile myprofile;create profile password_history limit password_life_time 10 password_grace_time 2 password_reuse_time 10;3.4. 删除profiledrop profile password_history cascade级联删除相关第4章 oracle表的管理4.1. 表名与列名的命名规则必须以字母开头长度不能超过30字符不能使用oracle的保留
7、字只能使用如下字符A-Z,a-z;4.2. 数据类型字符型：char(20)定长 最大2000 查询速度快 varchar2(20)变长，最大4000字符 clob数字型：number -10381038 范围number(5,2) 有五位有效数字，2位小数日期型：date 包含年月日时分秒 timestamp 是oracle 9i 对 date数据类型的扩展图片类型：图片，视频，mp3 等；4.3. 建表create table student( -表名xh number(4), -学号xm varchar2(20),-姓名sex char(2), -性别birthday date, -出生
8、日期sal number(7,2) 奖学金);4.4. 修改表添加字段 alter table student add(classid number(2);修改字段长度 alter table student modify(xm varchar2(30);修改字段类型名称 alter table student modify(xm char(30); (表不能有数据)删除一个字段 alter table student drop column sal;修改表的名字 rename student to stu;删除表 drop table student;查看表结构 desc student;i
9、nsert into student values(1,小明，男，11-12月-1997,236.7,12)修改oracle默认日期格式alter session set nls_date_format=yyyy-mm-dd;(重启后失效)插入部分字段 insert into student(xh,xm,sex) values(A03,JOHN,女);select * from student where birthday is null;eg: 把男的工资减半（修改一个字段）update student set sal=sal/2 where sex=男;update student set
10、 sex=男，birthday=1980-04-01 where xh=A001;4.5. 删除数据delete from student; 表结构在，写日子，可以恢复savepoint aa; 保存回滚点；设置保存点rollback to aa; 回滚到aa点drop table students; 删除表的结构和数据delete from student where xh=A001;删除一条记录delete from student;truncate table student; 速度快，结构在，无法恢复；第5章 oracle表查询 5.1. 查看表结构 desc student;查询所有
11、列 select * from dept;查询指定列 select ename,sal,job from emp;取消重复行 select distinct deptno,job from emp;打开显示操作时间的开关 set timing on;5.2. 疯狂复制insert into users(userid,username,userpasw) select * from user;select count(*) from user; 查询总共有多少条记录例：显示每个员工的年工资select sal*13,ename from emp;select sal*13+nvl(comm,0)
12、*13 “年工资”，ename,comm from emp;5.3. 使用where 子句显示工资高于3000的员工select ename,sal from emp where sal3000;找1982.1.1后入职的员工select ename,hiredate from emp where hiredate1-1月-1982；工资在20002500之间 where sal=2000 and sal500 or job=MANAGER) and ename like J%;5.5. order by 默认从低到高select * from emp sal order by sal asc
13、(升序)/desc降序；例：部门从低到高，薪水从高到低select * from emp order by deptno asc,sal desc;5.6. 复杂查询数据分组 max,min,avg,sum,counteg:显示员工最高工资，最低工资select max(sal),min(sal) from emp;子查询 select ename,sal from emp where sal=(select max(sal) from emp);查询高于平均工资的人select * from emp where sal(select avg(sal) from emp);5.7. group
14、 by 和 having 子句例：显示每个部门的平均工资和最高工资select avg(sal),max(sal),deptno from emp group by deptno;(按组分)例：显示每个部门的每种岗位的平均工资和最低工资；select ava(sal),max(sal),deptno,job from emp group by deptno,job;例： 显示平均工资，小于2000的部门号，和它的平均工资select avg(sal),max(sal),deptno from emp group by deptno having avg(sal)a2.mysal;5.12. 分
15、页查询select * from (select a1.*,rownum rn from (select * from emp) a1 where rownum=6;几个查询变化1 只需修改最里层的子查询5.13. 用查询结果建表create table myemp2(id,ename,sal) as select empno,ename,sal from emp;第6章 数据库管理6.1. 数据库管理conn system/manager as sysdba; 否则是普通用户登录shutdown 关闭数据库6.2. 数据库备份（逻辑备份）导出分三种：导出表 导出方案 导出数据库exp 命令来
16、完成userid 用于指定执行导出操作的用户名，口令，连接字符串tables 用于指定导出表owner 导出方案full=y 指定导出的数据库inctype 导出增量类型rows 是否导出表中的数据file 指定导出文件名例：导出自己的表exp userid=scott/tigermyoral tables=(emp) file=d:e1.dmp在cmd中输入oracle安装目录d:programmyoraclebin6.3. 导出方案exp userid=scott/tigermyoral owner=scott file=d:scott.dmp第7章 数据库管理7.1. 导出数据库（需要具
17、有 dba或者exp_full_database权限）exp userid=system/managermyoracl full=y inctype=complete file=d:aa.dmpinctype=incremental 备份上一次改变的数据导入表imp userid=scott/m123myoral tables=(emp) file=d:emp.dm导入表到其他用户imp userid=system/managermyoral tables=(emp) file=d:*.dmp touse=scott;导入方案imp userid=scott/tiger file=d:*.dm
18、p导入其他方案imp userid=system/manager file=d:*.dmp fromuser=system touser=scott; 原用户（从） 目标用户（到）7.2. 导入数据库imp userid=system/manager full=y file=d:*.dmp7.3. 数据字典1 user_tables当前用户拥有的所有表select table_name from user_tables;2 all_talbes当前用户可以访问的所有表（还会返回当前用户可以返回的其他方案的表）select table_name from all_tables;3 dba_tab
19、lesselect table_name from dba_tables;7.4. 用户名、权限、角色dba_users 数据库中总共有多少用户select username from dba_users; 查询前看下表的结构 desc dba_users;数据字典视图 dba_sys_privs 用户所有的系统权限数据字典视图 dba_tab_privs 用户所有的对象权限数据字典 dba_col_privs 用户具有的列权限（少）数据字典视图 dba_role_privs 用户具有角色例：scott具有的角色，可查 dba_role_privsdesc dba_role_privs;sel
20、ect * from dba_role_privs where GRANTEE=SCOTT;例：oracle中有多少角色 select * from dba_roles;例：某个角色中有多少权限呢？1 一个角色包含的系统权限select * from dba_sys_privs where grantee=DBA/CONNECT;(角色)2 一个角色包含的对象权限select * from dba_tab_privs where grantee=角色例：查看某用户有什么角色 select * from dba_role_privs where grantee=SCOTT;数据库中的系统权限se
21、lect * from system_privilege_map order by name;数据库中的对象权限select distince privilege from dba_tab_privs;7.5. 当前用户可以访问数据字典的视图select * from dict where comments like %grant%;当前数据库的全称select * from global_name;第8章 数据库管理8.1. 表空间表空间是数据库的逻辑组成部分，从物理上讲，数据库数据存放在数据文件中。逻辑上讲，数据库则是存放在表空间中，表空间由一个或者多个数据文件组成。oracle 中逻辑结
22、构包括表空间，段，区和块（例如：省、市、县、区）8.2. 建立表空间create tablespace 例如： create tablespace spool datafile d:spool.dbf size 20M uniform size 128k; 自定义表空间名 保存位置 20兆 区的大小8.3. 使用表空间创建一个表，这个表保存在名叫SPOOL的表空间上。create table mypart(deptno number(2),dname varchar2(14),loc varchar2(13) tablespace spool;8.4. 改变表空间状态使用表空间脱机 alter
23、 tablespace 表空间名 offline;使表空间联机 alter tablespace 表空间名 online;只读表空间 alter tablespace query_data read only;insert into mypart valuse(12,sp,beijing);可读可写 alter tablespace 表空间名（例如：spool） read write;8.5. 查看表空间包括的所有表select * from all_tables where tablespace_name=表空间名（大写）;表属于哪个表空间select tablespace_name,tab
24、le_name from user_tables where table_name=EMP;也是大写8.6. 扩展表空间增加数据文件增加数据文件大小设置文件的自增长alter tablespace spool add datafile d:spoo2.dbfsize 300M; 增加数据文件8.7. 移动数据文件 数据文件在的表空间select tablespace_name from dba_data_files where file_name=d:spool.dbf; 使表空间脱机alter tablespace spool offline; 移动到指定位置host move d:spoo
25、l.dbf c:spool.dbf; 逻辑修改alter tablespace spool rename datafile d:spool.dbf to c:spool.dbf; 表空间联机alter tablespace spool online;第9章 oracle 事物9.1. 事物commit; 提交savepoint a1;rollback to a1;注意：一旦提交了，保存点就不存在了第10章 oracle 的约束insert into emp valus(8888,ok,clea,7782,to_date(1992-12-12,yyyy-mm-dd);10.1. 约束oracle
26、数据完整性的技巧会建索引oracle的权限和角色数据库的完整性：约束，触发器，应用程序（过程，函数）约束包括：not null,nuique,primary key ,foreign key checkcreate table goods(goodsId char(8) primary key, -主键 goodsName varchar2(30), unitprice number(10,2) check(unitprice0) -单价 category varchar2(8), -商品类别 provider varchar2(30);修改：alter table goods modify goodsName not null;alter table customer add constraint cardunique unique(cardId);alter table customer add constraint addresscheck check(address in(东城区,西城区);删除约束alter table 表名 drop constraint 约束名(addresscheck)第11章 PLSQ
