一、sql窗口运行sql脚本start d:\aa.sql ; 编辑脚本edit d:\aa.sql; spool

1、理解权限用例

sqlplus->system/login----->create user xiaoming identified by m123; ------->conn system/login------->grant connect to xiaoming;--------->grant resource to xiaoming;-----> create table test (userid varchar2(30),username varchar2(30));------> desc test;----->希望xiaoming用户可以去查询scott的emp表---->对象权限：select、inser、update、delete、all、create 、index、------》grant select on emp to xiaoming;----->conn scott/tiger;---->select * from scott.emp;方案------》set linesize 120;------>select * from scott.emp; 希望xiaoming用户可以去修改、删除、查询、添加scott的emp表---》grant update on emp to xiaoming;------》grant all on emp to xiaoming;//把对emp这张表的所有权限都交给了小明。-------》收回权限.scott希望收回xiaoming对emp表的查询权限。Revoke select on emp from xiaoming;l----->看一下当前的用户是谁？show user;------>切换conn scott/tiger;---->revoke select on scott.emp from xiaoming ;conn xiaoming/m123; select * from scott.emp;查不到了。

2、对权限的维护

希望xiaoming用户可以去查询scott的emp 表，还希望xiaoming可以把这个权限继续给别人。

Grant select on emp to xiaoming with grant option 如果是对象权限，就加入with grant option;

3、文件操作命令

1）start和@ 说明：运行sql脚本案例：sql>@ d:\a.sql 或者sql>start d:\a.sql

2）edit 说明：该命令可以编辑指定的sql脚本案例：sql>edit d:\a.sql

3）spool 说明：该命令可以将sql *plus屏幕上的内容输出到指定文件中去。案例：sql>spool d:\b.sql 并输入sql>spool off

spool d:\bb.sql;

select *from emp;

spool off;

显示页数--set pagesize 2;

4、如果是系统权限

System给xiaoming权限时：grant connect to xiaoming with admin option

？如果scott把xiaoming 对emp 表的查询权限回收，那么xioahong会怎么样？被回收了。

Conn scott/m123;

Revoke select on emp from xiaoming;

Conn xiaohong/m123;

Select * from scott.emp;

二、Oracle用户的管理

1、Profile是口令限制，资源限制的命令集合。

账户锁定：指定该账户登录时最多可以输入密码的次数，也可以指定用户锁定的时间，一般用dba身份去执行该命令

？指定scott这个用户最多只能尝试3次登录，锁定时间为2天，让我们看看怎么实现。(红字不可修改)

Sql>create profile lock_account limit failed_login_attempts 3 password_lock_time 2;

Sql>alter user scott profile lock_account;（指定小明用它）

？给账户（用户）解锁

Sql>alter user scott account unlock;(conn system/login)

2、终止口令：为了让用户定期修改密码可以使用终止口令的指令来完成。同样这个命令也需要dba身份来操作。

案例：给前面创建的用户scott创建一个profile文件，要求该用户每隔10天要修改自家的登陆密码，宽限期为2天。看看怎么做？

Sql>create profile myprofile limit password_life_time 10password_grace_time 2;

Sql>alter user scott profile myprofile（分配给某个用户）

3、口令历史：当发现新旧密码一样时，就提示用户重新输入密码。

例子：1、建立profile

Sql>create profile password_history limit password_life_time 10 password_grace_time 2 password_reuse_time 10 Password_reuse_time //指定口令可重复即10天后就可以重用

2、分配给用户。

3、删除profile：sql>drop profile password_history [cascade]

三、目前主流数据库：微软：sql server 、access、瑞典：mysql 、IBM公司：db2、美国sybase公司：sybase、IBM公司： informix、美国oracle、

掌握：1、oracle表的管理（创建/维护）

2、对oracle表的各种查询技巧

3、学会创建新的oracle数据库

表名和列名规则：必须以字母开头，长度不能超过30字符，不能使用oracle的保留字，只能使用如下字符A-Z,a-z,0-9,$,#等

字符型：char 定长最大2000字符 varchar2() 变长最大4000字符

Clob(character large object) 字符型大对象最大4G

数字型number 范围10的38次方、number（5,2）：表示一个小数有5位有效数，2位小数、number（5）表示一个数5位整数

日期类型：date 包含年月日时分秒 timestamp 时间戳：对date数据类型的扩展。银行项目会用到。Blob 二进制数据可以存放图片、声音、声音 4G（保密性很高的情况下用、安全性）。一般情况下，数据库存放路径。

1) 添加一个字段Sql>alter table 表名 add(字段字段值)；

2) Alter table student modify(xm varchar2(30));//修改字段长度

3) 删除一个字段sql>alter table student drop column sal;

4) 修改表的名字sql>rename student to stu;

5) 删除表sql>drop table student;

6) 表添加数据：所有字段都插入:insert into student values(‘A001’,’张三’,’男’,’01-5月-05’,10);oracle中的默认日期格式’DD-MON-YY’

7) 修改日期的默认格式：alter session set nls_date_format=’yyyy-mm-dd’;

insert into student values(1,'小明','男','1997-12-11'，2345.6,12);

插入部分字段Insert into student(xh,xm,sex) values(‘A003’,’’,’JOHN’,’女’);

插入空值：insert into student (xh,xm,sex,birthday)values(‘’,’’,’’,null);

select * from student where birthday is null;

select * from student where birthday is not null;

改一个字段：update student set sex=’’where xh=’’;

修改多个字段：update student set sex=’男’,birthday=’1764-5-6’where xh=’A001’;

修改含有null值的数据

删除数据：delete from student ;删除所有记录，表结构还在，写日志，可以恢复的，速度慢drop table student;删除表结构和数据 truncate table student ;删除表中对的所有记录，表结构还在，不写日志，无法找回删除的记录，速度快。

设置保存点savepoint 回滚：rollback to

Job职位 clerk职员 salesman销售 analyst分析师president总裁总经理 mgr该员工的上级编号 king没上级 hiredate雇佣的时间 sal 工资 comm 奖金 deptno 部门编号

Dept部门表 accounting财务部 research研发部门 sales 销售部 operations业务部

Loc 部门所在地点

Desc dept;查看表结构

如何取消重复行 select distinct deptno ,job from emp;

set timing on;操作显示时间 count(*) from users; 查询出一共有多少条记录。查询SMITH的薪水，工作，所在部门 select deptno ,job,sal from emp where ename=’SMITH’;oracle中的字段值区分大小写。字段大小写不区分。

Select sal*13 “年工资”,ename from emp;

如何处理null 值？使用nvl函数来处理

Select sal*13 +nvl(comm.,0)*13 “年工资”，ename，comm from emp;

如何连接字符串（||） select ename || ‘is a’||job from emp;

如何使用like操作符 %：表示任意0到多个字符 _:表示任意单个字符

*使用逻辑操作符号

查询工资高于500或是岗位为MANAGER的雇员，同时还要满足他们的姓名首写字母为大写的J

Select * from emp where (sal>500 or job=’MANAGER’) and ename like ‘J%’;

*使用Order by 字句？如何按照工资的从低到高的顺序显示雇员的信息

Select * from emp order by sal;

Select * from emp sal order by sal desc; 从高到低

？按照部门号升序而雇员的工资按降序排列

Select * from emp order by deptno asc , sal desc;

？按照部门号升序而雇员的入职时间按降序排列。

Select * from emp order by denptno asc ,hiredate desc;

*使用列的别名排序

Select ename,(sal+nvl(comm,0))*12 as “年薪” from emp order by “年薪”;

*分页查询按雇员的ID号升序取出

Oracle表复杂查询***

数据分组---max , min , avg , sum , count

?如何显示所有员工中最高工资和最低工资

Select max(sal),min(sal) from emp;

如何显示所有员工中最高工资的员工姓名。

Select ename ,sal from emp where sal=(select max(sal) from);----结果

？显示所有员工的平均工资和工资总和

？计算共有多少员工

扩展要求：

？请显示工资最高的员工的名字，工作岗位。

Select * from emp where sal>(select avg(sal) from emp);

Group by 和having 子句

Group by 用于对查询结果分组统计，

Having字句用于限制分组显示结果。

？如何显示每个部门的平均工资和最高工资

select avg(sal),max(sal),deptno from emp group by deptno;

？显示每个部门的每种岗位的平均工资和最低工资

Select avg(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)>2000;

Select avg(sal),max(sal),deptno from emp group by deptno having avg(sal)>2000 order by avg(sal);

四、对数据分组的总结

分组函数只能出现在选择列，having、order by 子句中。
如果在select 语句中同时包含有group by,having,order by那么他们的顺序是 group by ， having ,order by分组在前排序在后
在选择列中如果有列、表达式、和分组函数，那么这些列和表达式必须有一个group by 子句中，否则就会错。

如 select deptno , avg(sal) , max(sal) from emp group by deptno having avg(sal)<2000; 这里deptno 就一定要出现在group by 中。

Oracle 多表查询------多表查询

？显示雇员名，雇员工资及所在部门的名字【笛卡尔集】多表查询至少不能少于表的个数-1

Select a1.ename ,a1.sal , a2.dname from emp a1 ,dept a2 where a1.deptno=a2.deptno;

？如何显示部门号为10的部门名、员工名和工资

Select a1.dname， a2.ename ， a2.sal from dept a1, emp a2 where a1.deptno=a2.deptno and a1.deptno=10;

？显示各个员工的姓名，工资，及其工资的级别

select * from salgrade;

GRADE LOSAL HISAL

------ ---------- ----------

1 700 1200

2 1201 1400

3 1401 2000

4 2001 3000

5 3001 9999

Select a1.ename, a1.sal, a2.grade from emp a1 ,salgrade a2 where a1.sal between a2.losal and s2.hisal;

扩展要求：

？显示雇员名，雇员工资及所在部门的名字，并按部门排序。

Select a1.ename ,a2.dname , a1.sal from emp a1, dept a2 where a1.deptno=a2.deptnp order by a1.deptno;多表也可以排序

自连接？显示某个员工的上级领导的姓名比如显示’ford’ 的上级。

Select worker.ename , boss.ename from emp worker,emp boss where worker.mgr=boss.empno and worker.ename=’ford’;

子查询 Select * from table where=(select …..select())

单行子查询

单行子查询是指只返回一行数据的子查询语句

请思考：如何显示已SMIth同一部门的所有员工？

思路：1、查询Smith的部门号

Select deptno from emp where ename=’smith’ ;

2、显示

Select * from emp where deptno = (Select deptno from emp where ename=’smith’);//数据库在执行sql是从左到右

多行子查询

多行子查询指返回多行数据的子查询

请思考：如何查询和部门10的工作相同的雇员的名字、岗位、工资、部门号

Select distinct job from emp where deptno =10

Select * from emp where job in (Select distinct job from emp where deptno =10);

在多行子查询中使用all操作符

请思考：如何显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号

Select ename , sal, deptno from emp where sal>all (select sal from emp where deptno=30);

扩展要求：想想还有没有别的查询方法。

Select max(sal) from emp where deptno=30

Select * from emp where sal>( Select max(sal) from emp where deptno=30);(效率要比上面的要快，比较次数比上面的少。)

在多行子查询中使用any操作符

请思考：如何显示工资比部门30的任意一个员工的工资高的员工的姓名、工资和部门号

Select sal from emp where deptno=30

Select ename, sal, dept from emp where sal>any (select sal from emp where deptno=30);

多列子查询（查询返回多个列数据的子查询语句）

请思考如何查询与Smith的部门和岗位完全相同的所有雇员

查询Smith的部门号，岗位

select deptno,job from emp where ename=’SMITH’;

select * from emp where (deptno,job)=(select deptno,job from emp where ename=’SMITH’;

在from子句中使用子查询

请思考：如何显示高于自己部门平均工资的员工的信息

1、查询出各个部门的平均工资和部门号select deptno , avg(sal) mysal from emp group by deptno;

DEPTNO MYSAL

------ ----------

30 1566.66667

20 2175

10 2916.66667

2、把上面的查询看作是一张子表

Select a2.eanme, a2.sal, a2.deptno , a1.mysal from emp a2, (select deptno , avg(sal) mysal from emp group by deptno;) a1 where a2.depntno=a1.deptno and a2.sal>a1.mysal;

衡量一个程序员的水平：数据库知识，网络处理，程序优化

说明：当在from子句中使用子查询时，（内嵌视图），必须给子查询指定别名。给表取别名，不可以加as. 给列取别名可以加as.

Oracle分页查询（三种方式）按雇员的id号升序

Rownum分页 select a1.*,rownum rn from (select * from emp ) a1;

显示rownum [oracle分配二分机制思想]

select a1.*,rownum rn from (select * from emp ) a1 where rownum<=10;

select * from (select a1.*,rownum rn from (select * from emp ) a1 where rownum<=10) where rn>=6;

几个查询的变化：

指定查询列，只需修改最里层对的信息。

select * from (select a1.*,rownum rn from (select ename , sal from emp ) a1 where rownum<=10) where rn>=6;

如何排序

select * from (select a1.*,rownum rn from (select ename , sal from emp order by sal desc) a1 where rownum<=10) where rn>=6;

显示4-9记录

select * from (select a1.*,rownum rn from (select ename , sal from emp order by sal desc) a1 where rownum<=9) where rn>=4;

用查询结果创建新表

这个命令是一种快捷的建表方法。 Create table my table (id, name, sal, job, deptno) as select empno, ename, sal, job, deptno from emp;

Desc myemp;

合并查询有时在实际应用中，为了合并多个select语句的结果，可以使用集合操作符号 union ，union all ,intersect（交集查询）, minus(合并查询)

Union（很少用）

该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中重复行。

Select ename , sal, job from emp where sal>2500 union select ename, sal, job from emp where job = ‘MANAGER’；

创建数据库有两种方法：

通过Oracle提供的向导工具
我们可以用手工步骤直接创建。

创建数据使用工具dbca[数据库配置助手]

package Testmyoracle;

import java.sql.*;

public class TestOra {

public static void main(String[] args) throws SQLException {

//1、加载驱动

try {

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

//2、得到连接

Connection ct=DriverManager.getConnection("jdbc:odbc:testsp", "scott", "tiger");

/* 控制面板\所有控制面板项\管理工具--->数据源---->用户DSN-->添加Oracle in OraDb11g_home1 点击完成 oracle ODBC Driver Configuration Datasourcename:自己随便起名字。TNS Service Name:选择ORCL TestConnection*/

Statement sm=ct.createStatement();

ResultSet rs=sm.executeQuery("select * from emp");

while(rs.next()){

System.out.println("用户名："+rs.getString(2));

}

} catch (ClassNotFoundException e) {

e.printStackTrace();

}

五、分页功能实现过程

int pagecount=0;//查询总页数

int rowcount=0;//共有几条记录

int pagesize=3;//每页显示几条记录

ResultSet rs = sm.executeQuery(“select count(*) from emp”);

If(rs.next()){

Rowcount=rs.getInt(1);

If(rowcount%pagesize==0){

Pagecount =rowcount/pagesize; //总条数÷（条数/每页）

}else{

Pagecount=rowcount/pagesize+1; //总条数÷（条数/每页）=商...还要余数剩下余数需要新加一页

}

For(int i=1; i<=pagecount；i++){ //打印总页数

Out.print(“<a href=mytest.jsp?pagenow=”+i+”> [”I”+i+”]</a>”);

}

Int pagenow=request.getParameter(“pagenow”);

Int pagenow=1;

If(s_pageNow!=null){

Pagenow=integer.parseint(s_pagenow);

}

//接受pageNow

String s_pageNow=(String)request.getParameter(“pageNow”);

Int pageNow=1;

If(s_pageNow!=null){

pageNow=Integer.parseInt(s_pageNow);

}

rs=sm.executeQuery(“select * from (select a1.*, rownum rn from (select * from emp) a1 where rownum<=”pageNow*pagesize+”) where rn>=”((pageNow-1)*pagesize+1) +” ”) ;

ageNow=1;pageNow=2

insert into emp values(9998,’小红’,’MANAGER’,7782,’11-11月-1988’,78.9,55.33,10);

使用to_date函数如何插入带有日期的表，并按照年-月-日的格式插入？

insert into emp values(9998,’小红’,’MANAGER’,7782,to_date(’1988-12-12’,’yyyy-mm-dd’),78.9,55.33,10);或者to_date(’1988/12/12’,’yyyy/mm/dd’)

create table kkk(myid number(4),myname varchar2(50),mydept number(5);

insert into kkk(myid,myname,mydept) select empno,ename,deptno from emp where deptno=10;//数据行迁移，十号部门的信息

select * from kkk;

使用子查询更新数据使用update语句更新数据时，既可以使用表达式或者数值修改数据，也可以使用子查询修改数据。

？希望员工scott的岗位、工资、补助、与Smith员工一样

Update emp set (job,sal,comm)=(select job,sal,comm from emp where ename=’SMITH’) where ename=’SCOTT;

ORACLE中事务处理：由一组相关的dml（增删改要么全部成功，要么全部失败）语句组成，

当执行事务操作时（dml语句），oracle会在被作用的表上加锁，防止其他用户改表表的结构，这里对我们用户来说是很重要。

Savepoint a1;

Delete from emp where empno=9996;

Savepoin a2;

Delete from emp where empno=9999;

Rollback to a2;

Commit //一旦提交，保存点不存在。提示：从未创建保存点。当执行使用commit语句可以提交事务，会确认事务的变化、结束事务、删除保存点、释放锁，其他会话将可以查看到事务变化后的新数据。

Exit//自动提交。

Java程序中如何使用事务

在Java操作数据库时，为了保证数据的一致性，比如转账操作（1）从一个账户减掉10￥（2）在另一个账户上加入10￥，如何使用事务？

Ct.setAutoCommit(false);//不能默认提交

Statement sm=ct.createStatement();

Sm.executeUpdate(“update emp set sal=sal-100 where ename=’scott’”);

Sm.executeUpdate(“update emp set sal=sal+100 where ename=’smith”);

Ct.commit();

//如果发生异常，就回滚

Ct..rollback();//本身就会抛出异常。用友财务软件。

只读事务设置只读事务set transaction read only;尽管其他会话可能会提交新的事务，但只读事务将不会取得最新数据的变化，从而可以保证取得特定时间点的数据信息。

假定机票代售点每天18点开始统计今天的销售情况。

Sql函数的使用字符函数

Lower(char) upper(char) length(char) substr(char,m,n)

?将所有员工的名字按小写的方式显示

Select lower(ename) ,sal from emp;

?将所有员工的名字按大写的方式显示

Select upper(ename) from emp;

？显示正好为5个字符的员工的姓名

Select * from emp where length(ename)=5;

？显示所有员工姓名的前三个字符。

Select substr(ename,1,3) from emp;

1.完成首字母大写 Select upper(substr (ename,1,1)) from emp;

2.完成后面字母小写 Select lower(substr(ename,2,length(ename)-1)) from emp;

3.合并 select upper(substr(ename,1,1))||lower(substr(ename,2,length(ename)-1)) from emp;

？以首字母大写的方式显示所有员工的姓名

select upper(substr(ename,1,1))||lower(substr(ename,2,length(ename)-1)) from emp;

？以首字母小写的方式显示所有员工的姓名

select lower(substr(ename,1,1))||upper (substr(ename,2,length(ename)-1)) from emp;

replace(char1,search string ，replace string)

instr(char1,char2,[,n[,m]])取子串在字符串的位置

？显示所有员工的姓名，用”我是a”替换所有”A”

Select replace (ename, ”A”, ”我是a”) from emp;

Round(n,[m]) trunk(n,[m]) mod(m,n)

Floor(n) ceil(n)

日期函数

(1)sysdate:系统时间 select sysdate from dual;

(2)add_months(d,n)

查找已经入职8个月多的员工select * from emp from emp where sysdate>add_months(hiredate,8);

?显示满10年服务年限的员工的姓名和雇佣日期。

select * from emp from emp where sysdate>add_months(hiredate,12*10);

？对于每个员工，显示其加入公司的天数

Select sysdate-hiredate “入职天数” from emp

Select trunk(sysdate-hirdate “入职天数” ，ename from emp;

?找出各月倒数第3天手雇佣的所有员工。

Select hiredate , last_day(hiredate) from emp;

Select hiredate ename from emp where last_day(hiredate)-2=hiredate;

转换函数to_char : 用于将字符串转换成date类型的数据

?日期是否可以显示时分秒

？薪水是否可以显示指定的货币符号

select ename， to_char(hiredate,’yyyy-mm-dd hh24:mi:ss’) from emp;

select ename,to_char(hiredate,’yyyy-mm-dd hh24:mi:ss’), to_char(sal,’L99,999.99’) from emp;//数字每三位用逗号隔开

？显示1980年入职的所有员工

Select * from emp where to_char(hiredate,’yyyy’)=1980;

？显示所有12月份入职的员工

Select * from emp where to_char(hiredate,’mm’)=12;

系统函数 sys_context

1)terminal 2)language 3)db_name 4)nls_date_format 5)session_user

6)current_schema 7)host

通过该函数，可以查询一些重要信息，比如你怎在使用那个数据库？

Select sys_context(‘userenv’,’db_name’) from dual;

Select sys_context(‘userenv’,’db_name’)from dual;

Select sys_context(‘userenv’,’language’)from dual;

Select sys_context(‘userenv’,’ current_schema’) from dual;

方案和用户的关系* 当你创建一个用户，oracle自动给你创建一个方案，

方案：表、视图、包、函数、触发器、存储过程、

Dba （数据库管理员）安装和升级oracle数据库建库，表空间，表，视图，索引制定并实施备份与恢复计划数据库权限管理，调优，故障排除对于高级dba,要求能参加与项目开发，会编写sql语句，存储过程，规则，约束，包

管理员数据库的用户：sys和system sys董事长 system 总经理

Sys:拥有dba,sysdba,sysoper（系统操作员）角色或者权限，是oracle权限最高的用户。所有的数据字典的基表和视图都放在sys用户中，由数据库自己维护，任何用户不能手动更改。

数据库常用知识总结

Conn system/login

Conn system/login as sysdba

sysdba和sysoper权限区别图

数据库常用知识总结

Shutdown; //oracle例程已启动

Startup select * from emp;

Show parameter;//参数

数据库表的逻辑备份与恢复

Import

导出分三种类型：导出表，导出方案，导出数据库 exp 命令该命令常用选项：userid: 用于指定执行导出操作的用户名，口令，连接字符串

Tables: 用于指定执行导出操作的表

Owner:用于指定执行导出操作的方案

Fully=y :指定执行导出操作的数据库

Inctype:指定执行导出操作的增量类型

Rows:用于指定执行导出操作是否要导出表中的数据

File:用于指定到文件

导出表分两种：（1）exp userid=scott/[email protected] tables=(emp,dept) file=d:\e1.dmp;

特别说明：在导入和导出的时候，要到oracle目录的bin目录下。

（2）导出其它方案的表 D:\Administrator\product\11.2.0\dbhome_1\BIN

如果用户要导出其他方案的表，需要dba的权限或是exp_full_database 的权限，比如system 就可以导出scott的表 exp userid=system/[email protected] tables=(scott.emp) file=d:\e2.dmp

exp userid=system/[email protected] tables=(scott.emp) file=d:\e2.dmp

导出表的结构

（1）exp userid=system/[email protected] tables=(scott.emp) file=d:\e2.dmp rows=n;//不要数据，要表结构。

（2）使用直接导出方式 exp userid=scott/[email protected] tables=(emp) file=d:\e3.dmp direct=y; 这种方式比默认的常规方式速度要快，当数据量大时，可以考虑使用这样的方法这时需要数据库的字符集要与客户端字符集完全一致，否则会报错。。。。

导出方案 export 工具导出一个方案或者多个方案中的所有对象（表，索引，约束。。）和数据，并存放到文件中。

导出自己的方案

Exp scott/[email protected] owner=scott file=d:\scott.dmp

(2) 导出其他方案

如果用户导出其它方案，则需要dba的权限或是exp_full_database 的权限，

Exp_full_databasede 权限，

例如system用户就可以导出任何方案 exp system/[email protected] owner=(system, scott) file=d:\system.dmp;

导出数据库：利用export导出所有数据库中的对象及数据，要求该用户具有dba的权限或者是exp_full_database权限expuserid=system/[email protected] full=y inctype=complete file=d:\\aaa.dmp

导入：3种方式

导入自己表imp userid=scott/[email protected] tables=(emp) file=d:\xx.dmp
导入表到其他用户要求该用户具有dba的权限，或是imp_full_database

imp userid=system/[email protected] tables=(emp) file=d:\XX.dmp touser=scott

导入表的结构只导入表的结构而不导入数据

imp userid=scott/[email protected] tables=(emp) file=d:\xxx.dmp rows=n;

导入数据如果对象（如：比表）已经存在可以只导入表的数据

imp userid=scott/[email protected] tables=(emp) file=d:\xx.dmp ignore=y;

导入方案

（1）导入自身方案 imp userid=system/login file=d:\xx.dmp;

(2)导入其他方案要求该用户具有dba的权限

imp userid=system/login file=d:\xxx.dmp fromuer=system touser=scott;

(3)导入数据库

在默认情况下，当导入数据库时，会导入所有对象结构和数据，

Imp userid=system/manager full=y file=d:\xxx.dmp;

数据字典和动态性能视图

基表----->存放静态数据

动态视图----->动态数据

User_tables;当前用户-表 all_tables显示当前用户可以访问的所有表 dba_tables

Select username,password from dba_users 查询dba_users 可以显示所有数据库用户的详细信息；查询数据字典视图dba_sys_privs,可以显示用户所具有的系统权限；

查询数据字典视图dba_tab_privs 可以显示用户具有的对象权限；查询数据字典视图dba_col_privs 可以显示用户具有的列权限；查询数据字典视图dba_role_privs 可以显示用户具有的角色；desc dba_role_privs;

Select * from dba_role_privs where GRANTEE=”scott”

角色-对应-权限1-1 或者1-多

//查询oracle中的所有的系统权限，一般是dba

Select * from system_privilege_map order by name;

//查询oracle中所有对象权限，一般是dba

Select distinct privilege from dba_tab_privs;

//查询数据库的表空间

Select tablespace_name from dba_tablespaces;

权限：对象权限系统权限

Select * from dba_roles;oracle究竟有多少种角色。

一个角色包含的系统权限 select * from dba_sys_privs where grantee=’DBA’ 另外也可以这样查看select * from role_sys_privs where role=’connect’;

一个角色包含的对象权限 select * from dba_tab_privs where grantee=’connect’

如何查看某个用户，具有什么样的角色？

Select * from dba_role_privs where grantee=’用户’；

显示当前用户可以访问的所有数据字典视图

Select * from dict where comments like’%grant%’;

显示当前数据库的全称

Select * from global_name;

管理表空间和数据文件表空间存放数据文件表空间区段块

数据库常用知识总结

表空间作用：（1）控制数据库占用的磁盘空间（2）dba 可以将不同数据类型部署到不同的位置，有利于提高i/o性能，同时利于备份和恢复等管理操作

Crate tablespace data01 datafile ‘d:\test\data01.dbf’ size 20m uniform size 128k

使用数据表空间

Create table mypart (deptno number(4),dname varchar2(14),loc varchar2(13)) tablespace data01;

使表空间脱机 Alter tablespace users offline;
使用表空间联机 alter tablespace users online;
只读表空间（当建立表空间时，表空间可以读写，如果不希望在该表空间上执行update，delete，insert操作，那么可以将表空间修改为只读alter tablespace 表空间名字 read only）

Select * from all_tables where tablespace_name=’表空间名’;//知道表空间名，显示该表空间包括的所有表

Select tablespace_name, table_name from user_tables where table_name=’EMP’;//知道表名，查看该表属于那个表空间

改变表空间的状态使表空间可读写 alter tablespace 表空间名 read write；

删除表空间 drop tablespace ‘表空间’ including contents and datafiles;

说明：including contents 表示删除表空间时，删除该空间的所有数据库对象，而datafiles表示将数据库文件也删除。

扩展表空间三种方法：（1）增加数据文件 alter tablespace sp01 add datafile ‘d:\test\sp01.dbf’ size 20m (2)增加数据文件的大小 alter tablespace 表空间名 ‘d:\test\sp01.dbf’ resize 20m;注意：数据文件的大小不超过500m (3)设置文件的自动增长 alter tablespace表空间名 ‘d:\test\sp01.dbf’ autoextend on next 10m maxsize 500m;

移动数据文件步骤：1.确定数据文件所在的表空间 select tablespace_name from dba_data_files where file_name=’sp001.dbf’;------>2.使用表空间脱机确保数据文件的一致性，将表空间转变为offline的状态。Alter tablespace sp01 offline;----->3.使用命令移动数据文件到指定的目标位置 host move d:\sp01.dbf c:\sp001.dbf ---->4.执行alter tablespace 命令 sql>alter tablespace sp01 rename datafile ‘d:\sp001.dbf’

To ‘c:\sp001.dbf’; ------->5.使表空间联机 sql>alter tablespace data01 online;

其他表空间：索引表空间 undo 表空间临时表空间非标准块的表空间

维护数据的完整性

约束：not null、 unique、 primary key、 foreign key、 check 五种。

Unique 唯一：不能重复，可以为空

Primary key(主键)：不能重复且不能为空。一张表最多只能有一个主键，但可以有多个unique约束。

Foreign key 外键：定义主表和从表之间的关系。

Check :用于强制行数据必须满足的条件，假定在sal列上定义了check约束，并要求sal列值在1000~2000之间如果不再，就会提示出错。

商店售货系统表设计案例

商品goods(商品号goodsId , 商品名goodsName，单价unitprice ，商品类别category，供应商provider)；

客户customer （客户号customerId, 姓名name, 住址address，电邮email ，性别 sex, 身份证 CardId）;

购买purchase （客户号customerId, 商品号goodsId, 购买数量nums）;

请用sql语言完成下列功能：

1建表，在定义中要求声明：

每个表的主外键；
客户的姓名不能为空值；
单价必须大于0，购买数量必须在1~30:；
电邮不能重复；
客户的性别必须是男或者女，默认是男

Create table goods(goodsId char(0) primary key, goodsName varchar2(30), unitprice number(10,2) check(unitprice>0), category varchar2(8), provider varchar2(30));

CREATE table customer(customerId char(8) primary key,---主键 name varchar2(50) not null,---不为空 address varchar2(50), emaile varchar2(50) unique, sex char(2) default ‘男’ check (sex in(‘男’,’女’))， cardId char(18));

Create table purchase (customerId char(18) references customer(customerId),

goodsId char(8) references goods(goodsIs),

nums number(10) check (nums between 1 and 30));

商店售货系统表设计案例

如果在建表时忘记建立必要的约束，则可以在建表后使用alter table命令为表增加约束，但是注意：增加not null约束时，需要使用modify 选项，而增加其他四种约束使用add选项。

每个表的主外码；
客户的姓名不能为空值；-------增加商品名也不能为空
单价必须大于0，购买数量必须在1到30之间；
电邮不能够重复;-----增加身份也不重复
客户的性别必须是男或者女，默认是男
增加客户的住址只能是’海淀’,’朝阳’,’东城’,’西城’,’通州’,’崇文’,

Alter table goods modify goodName not null;

Alter table customer add constraint cardunique unique(cardId);

Alter table customer add constraint addresscheck check(address in (‘东城’，’西城’))；

删除约束 alter table 表名 drop constraint 约束名称；

再删除主键约束的时候，可能有错误，比如：alter table 表名 drop primary key;

这是因为如果在两张表存在主从关系，那么再删除主表的主键约束，必须带上cascade 选项比如：alter table 表名 drop primary key cascade;

显示约束信息 1.通过查询数据字典视图user_constraints,可以显当前用户所有的约束的信息。Select constraint_name,constraint_type,status,validated from user_constraints where table_name=’表名’；2.显示查询数据字典视图user_cons_columns,可以显示约束所对应的表列信息 select column_name, position from user_cons_columns where constrain_name=’约束’;

列级定义如在department 表定义主键约束 create table department4 (dept_id number(2) constraint pk_department primary key, name varchar2(12),loc varchar2(12)); //在定义列的同时定义约束。

表级定义

表级定义是指在定义所有列后，再定义约束，这里需要注意：not null约束只能在列级上定义。以在建立employee2表时定义主键约束和外键约束为例：create table employee2 (emp_id number(4),name varchar2(15),dept_id number(2),

Constrain pk_employee primary key (emp_id),

Constrain fk_department foreign key (dept_id) references department4(dept_id));

索引缺点：1.占用空间大 2.更新比较麻烦

显示表的所有索引先是create index nameindex on customer(name);

在同一张表上可以有多个索引，通过查询数据字典视图 dba_indexs和user_indexs, 可以显示索引信息。其中dba_indexs用于显示当前用户的索引信息：

Select index_name, index_type from user_indexs where table_name=’表名’；

显示索引列通过查询数据字段视图user_ind_columns,可以显示索引所对应的列的信息 select table_name , column_name from user_ind_columns where index_name=’IND_ENAME’ ;

理解oracle的pl/sql概念

掌握pl/sql编程技术（包括编写过程、函数，触发器、、、、）

Java程序数据库（过程，函数，触发器）

编写一个存储过程，该如何可以向某表中添加记录。

1.创建一个简单的表 create table mytest (name varchar2(30),passwd varchar2(30));

2.创建过程 create or replace procedure sp_prol is

begin

---执行部分

Insert into mytest values (‘韩顺平’，’m1234’);

End;

Replace:表示如果有sp_prol, 就替换

如何查看错误信息：show error ;

如何调用该过程

exec 过程名（参数值1，参数值2，、、、）；
cal l 过程名（参数值1，参数值2，、、、）；

exec sp_pro1;

select * from mytest;

用pl/sql创建过程中

create or replace procedure sp_pro2 is

begin

delete from mytest where name =’韩顺平’;

end;

Select * from mytest;

Exec sp_pro2;

重点：掌握sql语句的编写方法，还要掌握语法规则，可以使用变量和逻辑控制语句，编写分页存储过程模块、订单处理存储过程模块、转账存储过程模块

块（编程）-----过程函数触发器包

编写规范

标识符号命名规范

当定义变量时，建议用v_作为前缀v_sal
当定义常量时，建议用c_作为前缀c_rate
当定义游标时，建议用_cursor作为前缀emp_cursor;
当定例外时，建议用e_作为前缀e_error

块由三部分构成：定义部分、执行部分、例外处理部分

Declear /*定义部分-----定义常量、变量、游标、例外、复杂数据类型*/

Begin /*执行部分-----要执行的pl/sql和sql语句*/

Exception /*例外处理部分-----处理运行的各种错误 */

End;

实例1-只包括执行部分的pl/sql块

Set severout put on ---打开输出选项

Begin

Dbms_output.put_line(‘hello’);

End;

相关说明：dbms_output是oracle所提供的包（类似java的开发包），该包包含一些过程，put_line就是dbms_output包的一个过程。

最简单的块

Begin

Dbms_output.put_line(‘hello,world’);

End;

Declare

V_ename varchar2(5);--定义字符串变量

Begin

Select ename from emp where empno=&no;

Dbms_output.put_line(‘雇员名：’||v_ename);

End;

有定义和执行部分的块

----把用户的编号薪水工资也显示？

Declare

---定义变量

V_ename varchar2(5);

V_sal number(7,2);

Begin

---执行部分

Select ename into v_ename from where empno=&aa;

Select ename, sal into v_ename, v_sal from where empno=&aa;//对应的顺序一致

--在控制台显示用户名

Dbms_output.put_line(‘用户名：’||v_ename || ‘工资：’ ||v_sal);

----异常处理

Exception

When no_data_found then

Dbms_output.put_line(‘朋友，你的编号输入有误！’);

End;

数据库常用知识总结

五、分页功能实现过程

相关推荐