oracle数据库学习笔记

小凡012

于 2024-04-22 21:21:40 发布

阅读量390

点赞数 4

文章标签： oracle 数据库学习

本文链接：https://blog.csdn.net/fjdjdj1/article/details/138092906

版权

安装Oracle

安装版本：oracleXE

https://blog.csdn.net/Li__Daxia/article/details/136387699 安装网址

安装plsql developer

图形化工具

https://blog.csdn.net/Li__Daxia/article/details/136387699 安装网址

sqlplus语句

登录语句

sqlplus 用户名/密码 [as sysdba] [可省略]

sqlplus scott/triger

导入文件语句

@加全路径加文件名和文件后缀

显示目前用户

show user

显示数据长度、行数

set linesize 300; 设置每行显示的数据长度

set pagesize 30；设置每页显示的数据行数

调用本机命令

有四张表

1.dept

2.emp

3.salgrade

SQL简介

简单查询

别名

select empno 编号，ename 姓名，sal*12 年薪 from emp；

数据去重 --distinct

select distinct job from emp;

数据连接 – ||

select '编号:'||empno||'姓名:'||ename info  from emp;

限定查询

查询工资大于2000的职工

select * from emp where sal>2000;

查询姓名SMITH的雇员信息

select * from emp where ename='SMITH';

查询所有职位不是销售人员的信息

select * from emp where job<>'SALESMAN';

select * from emp where job!='SALESMAN';

AND连接操作

要求查询出所有销售人员并且工资高于1200的所有雇员

select * from emp where job='SALESMAN' AND sal>1200;

查询出工资范围在1200-3000之间的数据

select * from emp where sal>=1200 AND sal<=3000;

select * from emp where sal between 1200 and 3000;

查询10部门的经理数据

select * from emp where job='MANAGER' AND deptno=10;

OR 连接操作

查询工资高于3000 或则职位是办事员

select * from emp where job='CLERK' or sal>=3000;

NOT 取反

查询职位不是办事员的信息

select * from emp where not job='CLERK';

between and

select * from emp where sal between 1200 and 3000;

查询所有在1981年雇佣的信息

select * from emp where hiredate between '01-1月-1981' and '31-12月-1981';

空判断：IS NULL 、IS NOT NULL

查询出所有不领取佣金的雇员

select * from emp where comm is bull;

查询出所有领取佣金的雇员

select * from emp where comm is not bull;

技术范围：IN、NOT IN

查询出雇员为7369、7566、7839、8899的雇员

一般方法使用or

select * from emp where empno=7369 or empno=7566 or empno=7839 or empno=8899;

select * from emp where empno in(7369,7566,7839,8899);

在NOT IN 中使用NULL

select * from emp where empno  not in(7369,7566,7839,null);

模糊查询

like模糊查询

_ 匹配任意一个字符

% 匹配0个或多个任意字符

查询所有姓名以字母A开头的所有演员

select * from emp where ename like 'A%';

查询所有姓名第二个字母以A开头的所有雇员

select * from emp where ename like '_A%';

查询所有位置上包含字母A的雇员信息

select * from emp where ename like '%A%';

‘%%’表示查询全部

select * from emp where ename like '%%';

查询排序

order by

升序asc（默认）|降序desc

按照工资由高到低排

select * from order by sal desc;

按照工资由高到低排序，工资相同，则按照雇佣日期由早到晚排序

select * from order by sal desc,hiredate asc;

按照年薪排序，因为order by 是select 之后执行

select empno,ename,(sal*12) income from emp order by income;

查询出所有办事员的信息，同时按照工资由高到低排序

select empno,ename,sal from emp where job='CLERK' order by sal desc;

思考题A

1.选择部门30中的所有员工

select * from where deptno=30;

2.列出所有办事员的姓名、编号和部门编号

select ename,empno,deptno from emp where job='CLERK';

3.找出佣金高于薪金60%的员工

select * from where comm > sal*0.6;

4.找出部门10中所有经理和部门20中所有办事员的详细资料

select * from where (deptno=10 and job='MANAGER') or (dept=20 and job='CLERK');

5.找出部门10中所有经理和部门20中所有办事员，既不是经理又不是办事员但其薪金大于或等于2000的所有员工的详细资料

select * from where (deptno=10 and job='MANAGER') or (dept=20 and job='CLERK') or (job not in('MANAGER','CLERK')and sal>=2000);

6.找出收取佣金的员工的不同工作

select distinct job from emp where comm is not null;

7.找出不收取佣金或收取佣金低于100的员工

select * from emp where comm is null or comm < 100;

8.显示不带有R的员工的姓名

select ename from emp where ename not like '%R%';

9.显示姓名字段的任何位置包含A的所有员工的姓名，显示的结果按照基本工资由高到低排序，如果基于工资相同，则按照雇佣年限由早到晚排序，如果雇佣日期相同，则按照职位排序

select * from emp where ename like '%A%' order by sal desc,hiredate  asc,job;

单行函数

字符串函数

大小写转换函数

转大写：upper（字符串|数据列）

转小写：lower（字符串|数据列）

验证函数

select upper('Hello'),lower('Hello') from dual;
select lower(ename) from emp;

要求用户输入一个雇员的姓名，而后查询出此雇员的详细信息

select * from emp where ename=upper('&name');

首字母大写，其余字母小写：initcap（）

雇员的姓名首字母大写，其余字母小写

select initcap(ename) from emp;

取得字符串长度：数字length（）

计算字符串长度

select length('hello world') from dual;

查询出所有雇员姓名及姓名长度

select ename,length(ename) from emp;

查询出雇员姓名长度为5的全部雇员信息

select ename,length(ename) from emp where length(ename)=5;

替换指定字符串的内容：字符串replace（）

替换字符串数据

select replace('hello world','l','
_') from dual;

替换数据列 (替换ename中的A 替换为_)

select ename,replace(ename,'A','_') from emp;

字符串截取操作

字符串截取-由指定位置截取到结尾（第七个字符开始到结尾）

select substr('hello world',7) from dual;

字符串截取-截取部分（第一个字符开始到第五个字符）

select substr('hello world',0,5) from dual;
select substr('hello world',1,5) from dual;

截取雇员姓名的前三位字母

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

截取雇员姓名的后三位字母 (-3表示后三位)

select ename,substr(ename,-3) from emp;

去掉左右空格函数：字符串trim（）

中间空格无法取消

验证trim函数

select '  hello world   ',trim('  hello world   ') from dual;

数字函数

四舍五入函数：round

验证round（）函数

select round(9615.7652),
round(9615.7652,2),
round(9615.7652,-2),
round(-15,5)
from dual;

round(数字,保留位数) 不写保留位数默认四舍五入到整数

截取小数函数：trunc

验证trunc（）函数

select trunc(9615.7652),
trunc(9615.7652,2),
trunc(9615.7652,-2),
trunc(-15,5)
from dual;

求模：mod（）

求余数

select mod(10,3) from dual;

日期函数

取得当前的系统日期

select sysdate from dual;
select systimestamp from dual;

计算若干天后的日期

select sysdate +3 ,sysdate+150 from dual;

计算若干天之前的日期

select sysdate-120 from dual;

计算两个日期之间所经历的月数：months_between()

计算雇员到今天为止雇佣的年数

select ename,months_between(sysdate,hiredate),
months_between(sysdate,hiredate)/12 
from emp;

查询出所有已经被雇佣了33年以上的雇员

select * from emp 
where months_between(sysdate,hiredate)/12>33;

加上指定月之后的日期：add_months

计算若干个月之后的日期

select sysdate+120,add_months(sysdate,4),add_months(sysdate,500) from dual;

计算雇员到今天为止的雇佣年限

select ename,hiredate,
trunc(months_between(sysdate,hiredate)/12) year,
trunc(mod(months_between(sysdate,hiredate),12)) months,
trunc(sysdate-add_months(hiredate,months_between(sysdate,hiredate))) day 
from emp;

求出指定的下一个一周时间数的日期next_day()

计算下一个星期二

select next_day(sysdate,'星期二') from dual;

求出指定日期所在月的最后一天的日期：last_day()

求出本月的最后一天日期

select last_day(sysdate) from dual;

转换函数

转字符串数据：to_char

将日期格式化为字符串

2024-04-17
select to_char(sysdate,'yyyy-mm-dd') from dual;
2024-4-17
select to_char(sysdate,'fmyyyy-mm-dd') from dual;

在oracle的sysdate里面包含有时间数据的，也可以继续显示时间

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

拆分日期数据

select to_char(sysdate,'yyyy') year,
to_char(sysdate,'mm') months,
to_char(sysdate,'dd') day
from dual;

查询出所有在2月份雇佣的雇员

select * from emp where to_char(hiredate,'mm')='02';

格式化数字显示

select to_char(2347891561516,'L999,999,999,999,999') from dual;

转日期数据：to_date()

将字符串变为日期

select '1981-09-15',to_date('1981-09-15','yyyy-mm-dd') from dual;

转数字类型：to_number()

将字符串变为数字

select to_number('1') + to_number('2') from dual;
select '1' + '2' from dual;

通用函数

oracle的特色函数：nvl() decode()

处理null数据：数字nvl()

要求计算每一位雇员的年薪

select ename,sal,comm,(sal+comm)*12 from emp;

因为好多人没有佣金，所有好多人计算出来的年薪为0，数据库中null与任何数据进行计算结果都是null，遇到了null应该按照数字0来处理。

select ename,sal,comm,(sal+nvl(comm,0))*12 from emp;

多数据判断：decode

将所有雇员的职位数据变成中文

select ename,job,
decode(job,'CLERK','办事员','SALESMAN','销售','MANAGER','经理','ANALYST','分析与','PRESIDENT','总裁','未处理')
from emp;

多表查询

统计两个表中的数据量

select count(*) from emp;
select count(*) from dept;

将emp表和dept表实现多表查询

select * from emp,dept;

解决笛卡尔积

select * from emp,dept where emp.deptno = dept.deptno;

使用别名

select * from emp e,dept d where e.deptno = d.deptno;

查询每个雇员的编号，姓名，职位，工资，部门名称，部门位置

select e.empno,e.ename,e.job,e.sal,d.dname,d.loc  from emp e,dept d where e.deptno = d.deptno;

查询每个雇员的编号，姓名，职位，工资，雇佣日期，工资等级

select e.empno,e.ename,e.job,e.sal,e.hiredate,s.grade  from emp e,salgrade s where e.sal between s.losal and s.hisal;

查询每个雇员的编号，姓名，职位，工资，雇佣日期，工资等级,所在部门名称及位置

select e.empno,e.ename,e.job,e.sal,e.hiredate,s.grade,d.dname,d.loc
from emp e,salgrade s ,dept d
where e.sal between s.losal and s.hisal
and e.deptno=d.deptno;

结论：在数据量大的时候绝对不要采用多表查询，而且就算是数据量小，也别用多表查询

连接方式

增加一个没有部门的数据

insert into emp(empno,ename,job,hiredate,sal)
values(7777,'刘小','CLERK',sysdate,701);

观察内连接

select * from emp e,dept d
where e.deptno = d.deptno;

观察左外连接

select * from emp e,dept d
where e.deptno = d.deptno(+);

观察右外连接

select * from emp e,dept d
where e.deptno(+) = d.deptno;

查询每个雇员的编号，姓名，职位，领导姓名

select e.empno,e.ename,e.job,m.ename
from emp e, emp m
where e.mgr=m.empno;

SQL:1999 语法支持

交叉连接（为了产生笛卡尔积）

select * from emp cross join dept;

自然连接，主要是消除笛卡尔积（内连接）

select * from emp natural join dept;

使用using子句，用户指定关联字段

select * from emp join dept using(deptno);

使用on子句指定关联条件

select * from emp e join dept d on(e.deptno=d.deptno);

外连接

select * from emp e left outer join dept d on(e.deptno=d.deptno);

select * from emp e right outer join dept d on(e.deptno=d.deptno);

select * from emp e full outer join dept d on(e.deptno=d.deptno);

查询结果连接

验证union操作，不显示重复记录

select * from emp where deptno=10 
union 
select * from emp;

验证union all操作，显示所有数据，包含重复数据

select * from emp where deptno=10 
union all
select * from emp;

验证intersect操作，返回相同的部分，交集

select * from emp where deptno=10
intersect
select * from emp;

验证minus操作，返回不同的部分，是一个差集

select * from emp where deptno=10
minus
select * from emp;

分组统计查询

组函数

统计所有雇员的人数，支付的总工资、平均工资、最高工资、最低工资

select count(*),sum(sal),avg(sal),max(sal),min(sal) from emp;

统计公司支付的总年薪与平均年薪

select sum((sal+nvl(comm,0))*12),avg((sal+nvl(comm,0))*12) from emp;

求出公司最早雇佣雇员的日期和最晚雇佣雇员的日期

select max(hiredate),min(hiredate) from emp;

分组统计（难点）

按照职位分组，统计出每个职位的平均工资、最高和最低工资、人数

select job,avg(sal),max(sal),min(sal),count(*)
from emp
group by job;

按照部门编号分组，统计出每个部门的人数、平均工资、平均服务年限

select deptno,count(empno),avg(sal),
avg(months_between(sysdate,hiredate)/12)
from emp
group by deptno;

查出每个部门的名称、部门人数、平均工资

select d.dname,count(e.empno),avg(e.sal)
from emp e,dept d
where e.deptno(+)=d.deptno
group by d.dname;

查询出每个部门的编号、名称、位置、部门人数、平均工资

select d.deptno,d.dname,d.loc,count(e.empno),avg(e.sal)
from emp e,dept d
where e.deptno(+)=d.deptno
group by d.deptno,d.dname,d.loc;

按照职位分组，统计每个职位的平均工资，要求显示的是平均工资高于2000的职位信息

select job,avg(sal) 
from emp
where avg(sal)>2000
group by job;

where之后不能用group by

用having修改程序

select job,avg(sal) 
from emp
group by job
having avg(sal)>2000;

统计公司每个工资等级的人数、平均工资

select s.grade,count(e.empno),avg(e.sal)
from emp e,salgrade s
where e.sal between s.losal and s.hisal
group by s.grade;

统计出公司领取佣金和不领取佣金的人数、平均工资

select '领取佣金',count(empno),avg(sal)
from emp
where comm is not null
union
select '不领取佣金',count(empno),avg(sal)
from emp
where comm is null;

子查询

在where子句之中使用子查询

子查询返回单行单列

要求统计出所有高于公司平均工资的全部雇员信息

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

统计出公司最早雇佣的雇员信息

select * from emp where hiredate=(select min(hiredate) from emp);

子查询返回单行多列（了解）

统计出公司雇佣最早、工资最低的雇员

select * from emp where (hiredate,sal)=(select min(hiredate),min(sal) from emp);

查询与scott工资相同，职位相同的雇员信息

select * from emp 
where (sal,job)=(
select sal,job from emp where ename='SCOTT')
and ename<>'SCOTT';

子查询返回多行单列

in操作

select * from emp 
where sal in (
select sal from emp where job='MANAGER');

not in 操作

select * from emp 
where sal not in (
select sal from emp where job='MANAGER');

any操作：=any

select * from emp 
where sal=any(
select sal from emp where job='MANAGER');

any操作：>any 比子查询的最小值要大

select * from emp 
where sal>any(
select sal from emp where job='MANAGER');

any操作：<any 比子查询的最大值要小

select * from emp 
where sal<any(
select sal from emp where job='MANAGER');

all操作：>all 比子查询返回的最大值还要大

select * from emp
where sal>all(
select sal from emp where job='MANAGER');

all操作：<all 比子查询返回的最小值还要小

select * from emp
where sal<all(
select sal from emp where job='MANAGER');

要求查询出工资比30部门都高的雇员信息

select * from emp 
where sal>all(
select sal from emp where deptno=30);

在having子句之中使用子查询

查询出高于公司平均工资的部门编号、平均工资

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

查询出平均工资最低的职位信息、人数、平均工资

select job,avg(sal)
from emp
group by job
having avg(sal)=(
select min(avg(sal))
from emp
group by job);

在select子句之中使用子查询（了解）

演示

select e.ename,e.job,
(select dname from dept where deptno=e.deptno),
(select loc from dept where deptno=e.deptno)
from emp e;

在from子句之中使用子查询（核心）

查询出每个部门的编号、名称、位置、部门人数、平均人数

实现方式一：多表关联查询

select d.deptno,d.dname,d.loc,count(e.empno),avg(e.sal)
from emp e,dept d
where e.deptno(+)=d.deptno
group by d.deptno,d.dname,d.loc;

实现方式二：采用子查询完成

select d.deptno,d.dname,d.loc,temp.count,temp.avg
from dept d,(
select deptno,count(empno) count,avg(sal) avg
from emp
group by deptno) temp
where d.deptno=temp.deptno(+);

范例:要求查询出高于公司平均工资的雇员姓名、职位、工资、领导姓名、领导职位、部门名称、部门人数、部门平均工资、工资等级，以及此等级的雇员人数。

select e.ename 雇员姓名,e.job 雇员职位,e.sal 雇员工资,
m.ename 领导姓名 ,m.job 领导职位,
d.dname 部门名称,
dtemp.count 部门人数,
s.grade 工资等级,
stemp.count 等级人数
from emp e,emp m,dept d,(
select deptno dno,count(empno) count
from emp
group by deptno) dtemp,salgrade s,(
select s1.grade sg,count(e1.empno) count
from emp e1,salgrade s1
where e1.sal between s1.losal and s1.hisal
group by s1.grade) stemp
where e.sal>(
select avg(sal) from emp)
and e.mgr=m.empno(+)
and e.deptno=d.deptno
and  dtemp.dno=d.deptno
and e.sal between s.losal and s.hisal
and s.grade=stemp.sg;

数据更新

将emp复制为myemp

create table myemp as select * from emp;

数据增加

增加一行新的数据

INSERT INTO myemp VALUES (8003,'从减光','清洁工',null,TO_DATE('1911-11-11','yy-mm-dd'),100,-100,40) ;

数据修改

要求将SMITH的工资修改为5500，佣金修改为5000

UPDATE myemp SET sal= 5500,comm= 5000 WHERE ename= 'SMITH';

将所有销售人员的工资统一修改为2000

UPDATE myemp SET sal= 2000 WHERE job= 'SALESMAN';

将公司雇佣最早的雇员增长10%

UPDATE myemp SET sal=sal*1.1
WHERE hiredate= (SELECT MIN(hiredate) FROM myemp);

将公司工资最低的雇员的工资修改为公司的平均工资

UPDATE myemp SET sal=(
SELECT AVG(sal) FROM myemp)
WHERE sal=(SELECT MIN(sal) FROM myemp);

将所有雇员的雇佣日期修改成今天日期

update myemp set hiredate=sysdate;

数据删除

删除30部门的所有雇员

DELETE FROM myemp WHERE deptno=30;

删除掉公司工资最高的雇员

DELETE FROM myemp WHERE sal=(SELECT MAX(sal) FROM myemp);

删除掉那些没有领导的雇员

DELETE FROM myemp WHERE mgr IS NULL;

所有员工删除

DELETE FROM myemp;

工作之中很烧执行这样的物理数据删除，更多的是数据的逻辑删除

事务处理

事务的提交（commit）：发出更新指令

事务的回滚（rollback）：回滚到最初的状态

session执行如下语句

第一次
update myemp set sal=9000 where empno=7499;
第二次
update myemp set sal=9000 where empno=7499;

思考题：例如，现在有一家公司，其中聘请了高刚同学作为这家公司的CTO,而且这是一家很著名的SNS社区，此网站的用户量有3亿人，现在由于初期的设计问题，要统一修改所有用户表之中的某一个列的内容。请问，高管你怎么做?

事务概念

事物的ACID属性

原子性（atomicity）： **事务是最小的执行单位，不允许分割。**事务的原子性确保动作要么全部完成，要么完全不起作用；
一致性（consistency）：执行事务前后，数据保持一致，例如转账业务中，无论事务是否成功，转账者和收款人的总额应该是不变的；
隔离性（isolation）：并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；
持久性（durability）：一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

oracle的事务

●事务相关的环境变量-AUTOCOMMIT

-Oracle数据库服务器默认为非自动提交事务模式。
SHOW AUTOCOMMIT; -查看
SET AUTOCOMMIT ON|OFF; - 更改

●隐式事务控制:

-自动隐式提交事务:
执行一个DDL语句、执行一个DCL语句、从SQL * Plus正常退出(exit,quit)
-自动隐式回滚事务:
强行退出SQL * Plus、客户端到服务器的连接异常中断、系统崩溃。

●显示事务控制: (建议使用)

-提交和结束事务: COMMIT;
-回滚事务: ROLLBACK [TO回滚点];
-设置事务回滚点: SAVEPOINT 回滚点;

事务隔离级别

事务隔高级别

一个事务对数据库的修改与并行的另外一个事务的隔离程度。

多个事务同时访问数据库中相同的数据时，如果没有采取必要的隔离机制，就可能会发生如下并发问题:

第一类丢失更新:撤消一个事务时，把其它事务已提交的更新数据也撤消了。
脏读:一个事务读到另一个事务未提交的更新数据。
不可重复读:一个事务读到另一个事务已经提交的更改数据。
第二类丢失更新:一个事务覆盖另-一个事务已经提交的更新数据。
幻读:-个事务读到另一个事务已提交的新增或已删除数据。

丢失更新(lost update)

两个事务都更新同一个行，而另一个事务异常回滚，导致两处变化都丢失。

脏读

不可重复读

第二类丢失更新

一个事务覆盖另一个已经提交的数据

幻读

oracle事务隔离级别

SQL标准定义了四种隔离级别:

READ UNCOMMITTED:读未提交数据。脏读、不可重复读、幻读都可能发生。它的事务隔离性最低。
READ COMMITTED:读已提交数据。不允许脏读。
REPEATABLEREAD:可重复读。不允许不可重复读，脏读。
SERIALIZABLE:串行化。不允许任何并发事务问题。最严格的事务隔离性。

Oracle只支持READ COMMITTED和SERIALIZABLE。

----默认为READ COMMITTED

设置一个事务的隔离级别

—set transaction isolation level serializable

设置整个会话的隔离级别

--alter session set isolation_ level=serializable;

--alter session set isolation_ level=read committed;

rownum&rowid

行号：rownum

观察rownum

select rownum,empno,ename,job,hiredate from emp;

查询所有10部门的数据

select rownum,empno,ename,job,hiredate from emp where deptno=10;

查询第一行的数据

select rownum,empno,ename,job,hiredate from emp 
where deptno=10 and rownum=1;

查询前N行记录

select rownum,empno,ename,job,hiredate from emp 
where rownum<=10;

数据的分页显示格式

SELECT * FROM (
SELECT列[别名] ,列[别名]，... ROWNUM rn
FROM表名称[别名]，...
WHERE ROWNUM<=currentPage *lineSize} temp
WHERE temp.rn>.currentPage- 1) * lineSize ;

取出emp表之中的6~10条记录(currentPage = 2、lineSize= 5) ;

SELECT * FROM (
SELECT empno,ename,job,ROWNUM rn
FROM emp
WHERE ROWNUM<= 10) temp
WHERE temp.rn>5;

取出emp表之中的1~5行记录(currentPage= 1、lineSize = 5)

SELECT * FROM (
SELECT empno,ename,job,ROWNUM rn
FROM emp
WHERE ROWNUM<= 5) temp
WHERE temp.rn>0;

范例:取出emp表之中的11~15条(currentPage = 3、lineSize = 5)

SELECT * FROM (
SELECT empno,ename,job,ROWNUM rn
FROM emp
WHERE ROWNUM<= 15) temp
WHERE temp.rn>10;

补充：数据库分页的三种方法

–不能对ROWNUM使用> (大于1 的数值)、>= (大于1的数值)、= (大于1的数值)，否则无结果
–所以直接用只能从1开始
– rownum>10没有记录，因为第一条不满足去掉的话，第二条的rownum又成了1,所以永远没有满足
件的记录。
select * from student where rownum>=1;
–如果想要用rownum不从1开始，需按下面方法使用
select a1.* from (select student*,rownum rn from student) a1 where rn >5;

分页查询一

select * from (select a1.*,rownum rn from (select * from student) a1 where rownum < =5) where
rn>=2;

分页查询二

select a1.* from (select student *,rownum rn from student where rownum <=5) a1 where rn >=3;

分页查询三

select a1.* from (select student.* ,rownum rn from student) a1 where rn between 3 and 5;

行ID：rowid（了解）

观察rowid

select rowid,deptno,dname,loc from dept;

结果

面试题:现在有一张数据表，由于设计的时候缺少一些限制，同时后期使用的过程之中出现了大量的重复数据，要求将重复的数据删除掉，只保留最原始增加的数据。
准备过程:
1、为了方便观察问题，首先将dept表复制为mydept表;

CREATE TABLE mydept AS SELECT * FROM dept;

2、观察现在的 mydept表之中的数据和ROWID (这个时候的数据就是最重要保存的数据)

SELECT ROWID ,deptno,dname,loc FROM mydept;

3、向 mydept数据表之中增加重复数据。

INSERT INTO mydept(deptno,dname,loc) VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO mydept(deptno,dname, loc) VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO mydept(deptno,dname,loc) VALUES (30,'SALES','CHICAGO');
INSERT INTO mydept(deptno,dname,loc) VALUES (30,'SALES','CHICAGO');
INSERT INTO mydept(deptno,dname,loc) VALUES (30,'SALES','CHICAGO');

正式问题:
将mydept表之中现在所有的重复删除掉，保留最早增加的数据。

SELECT ROWID ,deptno,dname,loc FROM mydept;

问题解决:
1、在 mydept表之中可以发现重复数据，而且重复数据都重复的很有规律，在deptno、dname、 loc 都重复，于是换个思路，查询出所有数据之中最早的ROWID, (MIN(ROWID))

SELECT deptno,dname,loc, MIN(ROWID)
FROM mydept 
GROUP BY deptno,dname,loc;

2、
不在此范围的数据删除就行了，使用NOT IN,而且之前的查询返回的是多行单列。

DELETE FROM mydept WHERE ROWID NOT IN (
SELECT MIN(ROWID)
FROM mydept
GROUP BY deptno,dname,loc);

在以后的分析之中，ROWID依然会出现，只需要记住，ROWID就像身份证一样，是作为一行具体数据的唯一的物理标记出现的。

表的创建与管理

对象的操作语法一共有三种：

创建对象: CREATE对象类型对象名称[选项]:
删除对象: DROP对象类型对象名称[选项];
修改对象: ALTER对象类型对象名称[选项]；

常见的数据类型

创建表

范例:创建一-张新的数据表

CREATE TABLE member(
mid NUMBER ,
name VARCHAR2(30) DEFAULT '无名氏',
birthday DATE DEFAULT SYSDATE,
sex VARCHAR2(10) DEFAULT '男',
age NUMBER(3),
note CLOB);

范例:查看表是否成功创建

select * from tab;

注意在命令窗口中执行
desc member;

范例:向表中添加数据

INSERT INTO member(mid,name, birthday,sex,age,note)
VALUES (1,'高软',TO_DATE('1111-11-11','yy-mm-dd'),'女',10,'年龄不像个人');
INSERT INTO member(mid,age,note) VALUES (2,10,'终于像个人');

一般而言，默认值是为了防止用户在增加数据时为null的问题。

复制表（了解）

创建，完整语法如下:
CREATE TABLE 表名称 AS 子查询

将10部门的雇员数据复制到emp10之中

CREATE TABLE emp10 AS SELECT * FROM emp WHERE deptno=10;

注意点:关于事务与DDL的问题
事务可以控制的只是数据的更新操作(增加、修改、删除)，而DDL是不受事务控制的;
在Oracle之中，如果发生了DDL操作，那么所有未提交的事务将会自动进行提交。

创建一张包含有dept统计结果的数据表

CREATE TABLE dept_details AS
SELECT d.deptno,d.dname, d.loc,temp.count temp,avg
FROM dept d,(
SELECT deptno ,COUNT(empno) count,AVG(sal) avg
FROM emp
GROUP BY deptno) temp
WHERE d.deptno=temp.deptno(+) ;

此时dept_details 数据表的结构与子查询返回的结构是完全相同的。

现在希望复制emp的表结构，但是并不复制表中的数据

create table empnull as select * from emp where 1=2;

此时empnull这张表之中会包含有emp表结构，但是由于限定条件不满足，所以不会有任何的数据保存。

为表重命名（了解）

在讲解此概念之前，首先来了解下关于数据字典的概念，数据字典在Oracle之中，是由Oracle自己进行维护的，用于记录所有与Oracle对象有关的内容。在Oracle之中有三类数据字典:

user_ *:用户可以使用的数据字典;
dba_*:管理员可以使用到的数据字典;
all_*:包含了当前用户可以访问的全部数据字典信息(有可能某些数据字典不属于此用户)。

查询一个用户所拥有的全部数据表—user_tables数据字典

SELECT * FROM user_tables ;

清楚了数据字典的基本概念之后，那么所谓的修改表名称，实际上就相当于修改了数据字典中保存的表
名称数据，但是所有的数据字典不能够由用户直接发出更新命令，只能够通过一些命令工具完成。语法如下:

RENAME旧的表名称TO新的表名称;

为表重命名

RENAME member TO users ;

但是此类的操作没有存在的意义，只要了解了数据字典这一概念即可。

截断表（了解）

如果说现在要想删除某一张表之中的全部数据,那么按照之前所学只能够使用"DELETE FROM表名称"这样的语句完成，但是使用删除语句删除数据有一个问题:所有的数据被事务所管理，可以恢复，而且所有的数据删除之后其所占用的些资源(约束、索引等资源)并不能够立刻释放，那么如果希望表的所有资源被彻底释放，则只能够使用截断表的操作，其操作语法如下:

TRUNCATE TABLE表名称;
范例:截断表

TRUNCATE TABLE users;

表一旦被截断，其所有占用的资源都将被彻底释放。

删除数据表

语法：

drop table 表名称

删除表

drop table users;

闪回操作（理解）

从Oracle 10g起为了方便用户进行数据表的恢复。为Oracle增加了一个类似于windows的回收站功能，等于是所有删除是数据表，首先保存在回收站之中，如果用户有需要也可以进行恢复。

查询回收站

show recyclebin;

通过回收站恢复emp10数据表

FLASHBACK TABLE emp10 TO BEFORE DROP;

彻底删除数据表，不让删除的数据表经过回收站，在删除语句之后增加一个PURGE

DROP TABLE emp10 PURGE;

删除回收站之中的一个表

PURGE TABLE empnull;

清空回收站

PURGE RECYCLEBIN;

对于闪回技术，只需要有一定的了解即可，而且也只有oracle有此特性。

修改表结构（了解）

--删除数据表
DROP TABLE member PURGE ;
--创建数据表
CREATE TABLE member(
mid NUMBER,
name VARCHAR2(30)
);
--增加测试数据
INSERT INTO member(mid,name) VALUES (1,'张三') ;
INSERT INTO member(mid,name) VALUES (2,'李四') ;
--提交事务
COMMIT;

为member表增加一个没有设置默认值的列

ALTER TABLE member ADD(address VARCHAR(50)) ;

此时发现address数据列增加之后，所有数据行对应的列信息都是null数据。

为member表增加一个有默认值的列

ALTER TABLE member ADD(sex VARCHAR(10) DEFAULT '男') ;

发现此时所有数据行之中增加列上都存在了默认值，相当于更新了所有的数据行。

修改name列的定义，增加默认值

ALTER TABLE member MODIFY(name VARCHAR2(20) DEFAULT '无名氏') ;
INSERT INTO member(mid) VALUES (5) ;

补充：向表中添加主键约束

alter table student add constraint pk_student primary key(studentid);

约束

约束是保证表中数据完整性的一种有效检测手段。在之前建立完数据表之后，可以发现里面能够随意设置数据,

例如:设置性别的时候，可以设置“不男不女”，在设置编号的时候，编号也可以重复，这些都是因为数据表缺少约束所导致的，那么在数据库之中约束严格来讲一共有六种约束:数据类型(可忽略)、非空约束、唯一约束、主键约束、检查约束、外键约束。

非空约束（not null，nk）

使用非空约束

DROP TABLE member ;
CREATE TABLE member(
mid NUMBER,
name VARCHAR2(20) NOT NULL);

此时表示的是member表中的name字段的内容不允许设置为null.

增加正确的数据

INSERT INTO member(mid, name) VALUES(1, '王小强') ;

增加错误的数据

INSERT INTO member(mid, name) VALUES(1,null) ;
INSERT INTO member(mid) VALUES(1) ;

此时，数据库会提示如下错误信息:“ORA-01400: 无法将NULL插入
“SCOTT”." MEMBER".“NAME”)" 。此时会明确的显示出那个字段上违反了非空约束。

唯一约束（unique，uk）

建立唯一约束

DROP TABLE member ;
CREATE TABLE member(
mid NUMBER ,
name VARCHAR2(20) NOT NULL ,
email VARCHAR2(20) UNIQUE
);

增加正确的数据

INSERT INTO member(mid,name) VALUES (1,'王小强') ;
INSERT INTO member(mid,name,email) VALUES (1,'王大强','110@china.com') ;

在使用唯一约束的时候一定要记住， null 不属于重复的统计范畴。

增加错误的数据

INSERT INTO member(mid,name,email) VALUES (1,'王中强','110@china.com') ;

结果

查看数据字典

SELECT * FROM user_cons_columns ;

修改数据库创建脚本

DROP TABLE member PURGE ;
CREATE TABLE member(
mid NUMBER ,
name VARCHAR2(20) NOT NULL,
email VARCHAR2(20),
CONSTRAINT uk_email UNIQUE(email)
);

此时，如果保存的数据出现了错误，则提示信息“ORA -00001:违反唯一约束条件(SCOTT.UK_ EMAIL)"。

除了非空约束之外，所有的约束都-一定定要按照以上的方式进行设置，因为只有一个正常的名称才方便系
统进行约束的维护。

主键约束（primary key，pk）

定义主键约束

DROP TABLE member PURGE ;
CREATE TABLE member(
mid NUMBER,
name VARCHAR2(20) NOT NULL,
CONSTRAINT pk_mid PRIMARY KEY(mid)
);

增加正确的数据

INSERT INTO member(mid,name) VALUES(1,'学冰') ;

增加错误的数据–主键重复“ORA-00001: 违反唯一约束条件(SCOTT.PK _MID)"

INSERT INTO member(mid,name) VALUES(1,'学冰') ;

增加错误的数据–主键为NULL “ORA-01400:无法将NULL插入(” SCOTT".“MEMBER”.“MID”)"

INSERT INTO member(mid,name) VALUES(null,'学冰') ;

提示:

一般正常人来讲，在进行数据表设计的时候都会为每一张数据表至少设置一个主键或不设置，但是从数据库本身的设计角度出发，一张数据表也可以设置多个主键，多个主键就称为复合主键。

观察复合主键

DROP TABLE member PURGE ;
CREATE TABLE member(
mid NUMBER,
name VARCHAR2(20),
CONSTRAINT pk_mid_name PRIMARY KEY(mid,name)
);
INSERT INTO member(mid,name) VALUES(1,'学冰') ;
INSERT INTO member(mid,name) VALUES(1,'学刁') ;
INSERT INTO member(mid,name) VALUES(2,'学刁') ;

检查约束（check，ck）

检查约束算是比较有意思的一种约束，检查约束是由用户自己来设置检查的过滤条件，例如:在保存年龄的时候年龄的范围: 0~ 250，设置性别，可选值:男、女、中，这样的方式都需要由用户自己来指定。

设置检查约束

DROP TABLE member PURGE ;
CREATE TABLE member(
mid NUMBER,
name VARCHAR2(20) NOT NULL ,
sex VARCHAR2(10) , 
age NUMBER(3) ,
CONSTRAINT pk_mid PRIMARY KEY(mid) ,
CONSTRAINT ck_sex CHECK (sex IN ('男','女')),
CONSTRAINT ck_age CHECK (age BETWEEN 0 AND 250)
);

增加错误数据

INSERT INTO member(mid,name,sex,age) VALUES(1,'学冰"雌雄',300) ;

即使设置了多个检查约束，那么也是一个个约束进行过滤的。
但是在这里有一个提示: 一般而言你所设置的约束越多，在进行数据更新操作的时候性能就越低。所以很多时候一些数据的检查操作都会交给程序完成。

主外键约束（foreign key，fk）

在解释主外键约束之前，首先来完成一个简单的程序，要求设计数据表，可以表示出: 一个人有多本书”的关系。编写数据库创建脚本，建立两张数据表。

DROP TABLE member PURGE ;
DROP TABLE book PURGE ;
CREATE TABLE member(
mid NUMBER,
name VARCHAR2(20) NOT NULL,
CONSTRAINT  mid PRIMARY KEY(mid)
);
CREATE TABLE book(
bid NUMBER ,
title VARCHAR2(20) ,
mid NUMBER ,
CONSTRAINT pk_bid PRIMARY KEY(bid)
);

下面向表之中增加正确的数据

INSERT INTO member(mid,name) VALUES (1,'张三') ;
INSERT INTO member(mid,name) VALUES (2,'李四') ;
INSERT INTO book(bid,title,mid) VALUES (1001,'格林童话',1) ;
INSERT INTO book(bid,title,mid) VALUES (1002,'一千零一夜',1) ;
INSERT INTO book(bid,title,mid) VALUES (1005, '西游记',2) ;
INSERT INTO book(bid,title,mid) VALUES (1006,'三国演义',2) ;
INSERT INTO book(bid,title,mid) VALUES (1007,'水浒传',2) ;

错误的数据

INSERT INTO book(bid,title, mid) VALUES (2001,'冒险王',9) ;
INSERT INTO book(bid, title, mid) VALUES (2002,'机器猫'，9) ;

修改约束（了解）

定义使用脚本

DROP TABLE member PURGE ;
CREATE TABLE member(
mid NUMBER,
name VARCHAR2(20)
);
INSERT INTO member(mid,name) VALUES (1,null) ;
INSERT INTO member(mid,name) VALUES (2,'小雪') ;
INSERT INTO member(mid,name) VALUES (2,'小冰') ;

为member表中添加约束

ALTER TABLE member ADD CONSTRAINT pk_ mid PRIMARY KEY(mid) ;

如果此时member表中的mid字段里面包含有违反约束的数据，那么将无法添加。
但是需要提醒的是，此类的语法适合于唯一、主键、检查、外键约束的添加，而无法添加非空约束。

ALTER TABLE member ADD CONSTRAINT nk_name NOT NULL(name) ;
ALTER TABLE member MODIFY(name VARCHAR2(20) NOT NULL) ;

删除表中的主键约束

ALTER TABLE member DROP CONSTRAINT pk_mid;

序列

如果用户直接使用"CREATE SEQUENCE序列名称”创建的是一个默认序列，默认序列的数值从1开始，每次增长1,而且没有最大值，最小值为1。

创建一个默认的序列

CREATE SEQUENCE myseq ;

序列的数据字典应该查 user_sequences 数据字典

COL sequence_ name FOR A20 ;
SELECT * FROM user_sequences ;

操作序列

SELECT myseq.nextval FROM dual ;
SELECT myseq.currval FROM dual ;

解释:关于user sequences数据字典之中的LAST NUMBER和CACHE的作用
首先必须明确的是，LAST_ NUMBER 并不是当前序列真正增长的数据。

SELECT myseq.nextval FROM dual ;
SELECT * FROM user_sequences ;

实际上当用户每次进行数据增长控制的时候，LAST_ NUMBER都表示已经处理完的一批序列内容，当当前序列的数值等于了LAST NUMBER数据时，那么会自动再增加指定个"CACHE" 大小的序列。
但是由于数据存在有缓存的问题，所以当数据库重新启动之后，那么所操作的序列就可能出现跳号的情况。如果想要避免跳号问题出现，那么最简单的做法是取消缓存，使用NOCACHE表示。
现在序列已经创建完成，而且也已经可以正常的进行使用了，可是该如何实现行的自动编号呢?只能够在数据增加的时候手工的进行控制。

建立数据表，同时保存数据

DROP TABLE mytab PURGE ; 
CREATE TABLE mytab(
mid NUMBER ,
title VARCHAR2(50),
CONSTRAINT pk_mid PRIMARY KEY(mid)
);
INSERT INTO mytab(mid,title) VALUES (myseq.nextval,'JAVA') ;

视图

创建一个视图

CREATE VIEW myview
AS
SELECT d.deptno,d.dname,d.loc,temp.count,temp.avg
FROM dept d, (
SELECT deptno,COUNT(empno) count,AVG(sal) avg
FROM emp
GROUP BY deptno) temp
WHERE d.deptno=temp.deptno(+);

注意:从Oracle 10g R2版本之后出现了一个创建视图的权限问题，在这之前，scott 用户本身是具备有创建
视图权限的，但是之后就没有了，那么下面首先执行如下的语句，来实现权限的分配:

CONN sys/change_on_install AS SYSDBA ;
GRANT CREATE VIEW TO scott ;
CONN scott/tiger ;

而当视图创建成功之后就可以利用"user_views"这个数据字典来查看所有视图对象。

SELECT * FROM user_views ;

视图完成后以后开发者就不再需要编写复杂的SQL,直接查询视图即可。

SELECT * FROM myview;

创建一个只包含20部门雇员数据的视图(只包含部分数据)

DROP VIEW myview ;
CREATE OR REPLACE VIEW myview
AS
SELECT empno,ename,sal,job,deptno
FROM emp
WHERE deptno=20 ;

1、向myview视图之中增加一行新的数据

INSERT INTO myview(empno,ename,job,sal,deptno) VALUES (8888,'小雪','工头',9.9,40) ;

此时增加的新数据是40部门,而视图之中的都是20部门]雇员数据，所以这个数据不会显示在视图里面。
但是这些数据却可以真实的反映在emp表之中，只是这样不太合理，毕竟视图属于虚拟数据。
2、删除数据

DELETE FROM myview WHERE empno=7369 ;

删除数据的时候也会影响到原始数据。不过依然不合理。
那么如果说现在的视图记录是多张表来的呢?例如，现在有如下的一个视图。

CREATE OR REPLACE VIEW myview
AS
SELECT e.empno,e.ename,e.job,d.dname,d.loc
FROM emp e,dept d
WHERE e.deptno=d.deptno AND e.deptno=20 ;

此时的视图里面除了雇员信息之外，还包含有部门信息，那么下面继续执行增加操作。

向myview表中增加一行新数据

INSERT INTO myview(empno,ename,job,dname,loc)
VALUES (9999, 'zhangsan','程序员','开发部','北京') ;

结果

ORA-01776:无法通过联接视图修改多个基表

这个时候的数据是多张表来的，在dept表之中根本就没有"娱乐部”部门的数据，既然没有那么通过视图增加的时候发现是无法增加的。
在以后的实际工作之中，一定要本着一个原则:“视图只能够用于查询，而不要用于任何更新”。
在创建视图的时候还存在有两个创建的选项，这两个选项主要也是为了防止用户错误的更新操作而准备的。

1、避免视图创建条件更新: WITH CHECK OPTION
问题分析:首先创建一张包含有20部门雇员的视图。

CREATE OR REPLACE VIEW myview
AS
SELECT * FROM emp WHERE deptno=20 ;

在此视图之中核心的创建条件是"WHERE deptno=20”，即， deptno字段的内容不应该被修改才对，
但是此时发出如下的更新命令:

UPDATE myview SET deptno=30 WHERE empno=7369 ;

这样的操作根本就不合理，于是为了避免此操作，则可以增加指定子句。

重新创建视图

CREATE OR REPLACE VIEW myview
AS
SELECT * FROM emp WHERE deptno=20
WITH CHECK OPTION ;

那么此时继续执行更新的指令，会出现

“ORA- 01402:视图WITH CHECK OPTION where子包违规”
错误提示信息，直接告诉用户不能够更新视图的创建条件。
2、设置只读视图: WITH READ ONLY
使用WITH CHECK OPTION子句只能够保护视图的创建条件，但是其它的字段依然允许更新。

UPDATE myview SET ename= '史密斯' WHERE empno=7369 ;

视图不是真实数据，而是真实数据的映射。所以这样的做法依然不合理。

创建只读视图

CREATE OR REPLACE VIEW myview
AS 
SELECT * FROM emp WHERE deptno=20
WITH READ ONLY;

此时执行更新后会出现“ORA- 42399:无法对只读视图执行DML操作”错误提示信息。

同义词

同义词就是我们上学时所学习过近以词，在之前曾经写过以下的一种查询。

SELECT SYSDATE FROM dual ;

一直强调"dual" 是一张虚拟表。
前提:在数据库之中，每张数据表的完整名称应该是“用户名.表名称”(模式名.表名称}，所以不同用户之间要访问数据
表，必须加上“用户名”，例如在scott中可以直接访问的emp表，在system用户下就必须采用"scott.emp’进行访问。
经过一系列的查询，可以发现dual实际上是sys用户的表，那么现在就会出现一个问题，明明有一个要求:访问不同用户的数据表需要加上用户名，但是为什么scott访问sys.dual的时候没有加上用户名。而这就是同义词的应用，即: dual 表示的就是sys.dual数据表。可是如果要想创建属于自己的同义词，则必须由管理员完成，而创建的语法如下:

CREATE [PUBLIC] SYNONYM同义词的名称 FOR 用户名表名称;

将scott.emp表定义一个myemp的同义词(由sys完成)

CONN sys/change_on_install AS SYSDBA ;
CREATE SYNONYM myemp FOR scott.emp ;

创建完同义词之后，下面就可以直接通过myemp来访问scott.emp数据表了。

SELECT * FROM myemp ;

那么现在创建的同义词myemp存在一个小问题。因为此时的同义词只能够在sys下使用，它并不是一个公共的同义词，所以要想让所有用户都访问，则必须创建公共同义词。

修改同义词的创建

CONN sys/change_on_install AS SYSDBA ;
DROP SYNONYM myemp ;
CREATE PUBLIC SYNONYM myemp FOR scott.emp ;

在实际的工作之中，同义词本身只属于Oracle自己的概念定义，所以此处只需要了解下基本概念即可。

索引

Oracle的索引|相当复杂，有几十种索引。索|的主要功能是进行数据查询优化的，提升数据库操作性能的。为了更好的观察出性能问题，那么下面首先针对于一些查询做一个分析。

查询所有工资高于3000的雇员工资

SELECT * FROM emp WHERE sal> 3000 ;

但是现在这个代码严格来讲性能是很差的，如果要想观察出性能(提示信息)问题，那么需要打开查询
分析器。

CONN sys/change_on_install AS SYSDBA ;
SET AUTOTRACE ON ;
SELECT * FROM myemp WHERE sal> 3000 ;

此时为了观察出问题只能够在sys用户下观察查询的情况，可以发现此时的查询采用的是"TABLE ACCESS FULL”(全表扫描，逐行扫描)。那么这样的查询有可能出现这样一个问题。
假设现在emp表之中存在有50W条记录，而在第20W条记录之后就不会再有满足条件的数据了
(sal>3000)，但是如果是全表扫描，意味着，要继续查询后面的30W行记录，而且都是逐行扫描(逐行判断)，那么性能一定不能高

此时，如果要想解决此问题，那么唯一的途径就是数据排序，按照工资排序，但是这个时候的排序不是简单的由高到低的排序。那么现在唯一的方式是按照 “树”的形式保存排序数据。
现在假设数据表之中所包含的数据顺序如下:“1500、 1250、2850、3000、 2450、 2975、1100、950、800、 1600、 5000”。数据检索的时候一定是查询工资数据，而后由工资数据找到对应的数据记录。
那么此时如果存在了这样树的结构，在进行数据查询的时候就不再需要进行全表扫描的操作了，只需要查询部分数据即可，而这样的操作就可以称为索引，在Oracle数据库里面如果要想创建索引有两种方式:

当一个列上设置了主键约束或者是唯一约束的时候会自动的创建索引;
用户可以自己通过语法在指定的列上创建索引。

在sal字段上创建索引

CONN scott/tiger ;
CREATE INDEX emp_sal_ind ON emp(sal) ;

此时索引创建完成。随后再次进行sal查询的时候显示的不再是全表扫描，而是根据个基数扫描。那么通过索引的查询可以明显的提升查询性能，而索引实现的关键是这棵树的维护(树是由Oracle内部自行维护的)。那么继续以本程序为例，如果说此时修改了某些雇员的工资呢?那么这棵树一定要发生变化，所有的数据都需要重新排列。那么此时配置的索3 |不仅没有提升性能，反而降低了性能，所以索引|是一种相对的手段，而且永恒都要记住，没有绝对的性能提升途径。

思考题:现在有一个新闻的检索数据库，里面保存了全世界大概有100亿条的数据，但是此数据库由于信息的
维护的问题，所以每秒种都会发出10 ~ 20次的更新指令，但是为了保证查询的性能又需要设置索引，请问，你该如何设计此数据库，以达到查询性能优秀，更新的影响又降低到最小?
矛盾点:如果要想提升查询性能只能够使用索引，但是如果频繁更新，所以又会出现性能严重降低。

在所有的设计上只有一个原则: “以时间换空间、以空间换时间”。可以准备出两张表、一张表作为索引的数据查询(表A) ,另外一-张表作为接收传入数据(表B) ;

白天所有的数据都保存在表B之中(这之中有可能包含有一些重复数据，或一些错误的数据)，而在访问量小的时候(1:00 ~ 7:00访问量小)将一些新的数据保存在表A之中，而后给它充足的时间进行索引的生成。相当于牺牲了时实性，但是提升了整体的操作性能，而这样的设计在很多地方都可以见到，例如:你们所有App的访问记录。或者最早sina 的blog有一个访问统计。

create 索引和rebuild索引的时候最好加上online

数据库备份

在你们的工作范围之内，除非是那种单人工作环境，有可能就需要使用到数据库备份的概念。

单个用户的数据导出和导入

本操作指的是将一个用户的所有数据进行导出，如果有需要执行导入，一般此类操作都会出现在项目部署的时候，例如:在项目之中有可能会出现修改数据表，但是脚本没有更新的情况。所以就会造成真实的数据库和最初编写的数据库创建脚本代码不一致的情况，那么在这样的情况下就需要将数据库之中的表和部分测试数据导出，而后在发布服务器进行恢复。

1、数据的导出操作

首先建立一个文件夹，用于保存所有的导出数据，路径: d:\backup;
通过命令行方式进入到backup目录之中(cd backup) ;
执行exp指令;

输入用户名和密码: scott / tiger;
导出文件: EXPDAT.DMP;

2、导入数据

进入到备份文件所在的路径;
执行imp指令导入数据;

但是这样的数据导入只适合小数据量，因为在整个导出过程之中，事务是锁定的，所以很多时候如果非要使用此类方式导出，就需要针对于数据进行分区的概念。

数据库的冷备份

所谓的数据库冷备份指的是数据库的归档备份。例如:网游每个月总有三、四天是不让你玩的，因为服务器要备份，而且一一旦出现了错误之后，统一的处理方案:恢复到上一备份点，再给你点补偿。
但是如果要想执行冷备份，则必须要由sys用户进行，而且备份的数据是最完整的，那么冷备份过程之中需要备份以下的文件数据:控制文件、日志文件、数据文件、数据库的核心配置(pfile) 。
在实际的工作之中，以上的这些文件都要分在不同的磁盘上的，以达到性能平衡。一般项目的部署的数据库人员会考虑到项目的负载平衡，将其以上的文件保存在不同的磁盘上。所有记录这些数据的位置就在数据字典之中。
1、首先要使用sys登录

CONN sys/change on_install AS SYSDBA ;

2、查询出所有的控制文件路径 (路径记录完整) :

SELECT * FROM v$controlfile ;

3、查找所有重做日志文件路径(路径记录完整) :

SELECT * FROM v$logfile ;

4、查找数据文件路径(路径记录完整) :

SELECT * FROM v$datafile ;

5、查找pfile文件(数据库的灵魂)

SHOW PARAMETER pfile ;

6、
关闭数据库服务:

SHUTDOWN IMMEDIATE ;

7、将以上的文件拷贝到备份目录中;

8、重新启动服务:

STARTUP

此时得到的备份数据是最完整的，但是前提:服务器可以关闭的情况下进行。

用户管理

在之前讲解完成的是DDL定义功能，但是除了DML、DDL之外还有一个DCL (GRANT、REVOKE) ,指的是权限的控制。但是此部分操作一般都会由DBA负责，下面就简单的演示一下关于用户的创建、权限分配等功能。
1、如果要想进行用户的维护，那么首先-定是具备管理员权限的用户: sys、 system,本次使用sys。

CONN sys/change_on_install AS SYSDBA ;

2、创建一个新的用户，用户名dog,密码为wangwang。
语法:

CREATE USER用户名IDENTIFIED BY密码;

使用:

CREATE USER dog IDENTIFIED BY wangwang ;

但是这个时候也只是一个普通的用户而已，它连登录都做不了。此时登录的错误提示信息: “ORA-01045:user DOG lacks CREATE SESSION privilege; logon denied”，缺少创建SESSION的权限(每个连接到数据库上的用户都被称为SESSION,缺少创建SESSION就意味着无法连接数据库)。
3、为dog用户授予连接数据库的权限:

GRANT CREATE SESSION TO dog ;

之后就可以使用dog用户进行登录了。但是非常遗憾的是，此时dog用户只能够登录。无法进行数据
表等对象的创建，例如，编写如下的脚本:

DROP TABLE mytab ; 
DROP SEQUENCE myseq ;
CREATE SEQUENCE myseq ;
CREATE TABLE mytab(
id NUMBER ,
name VARCHAR2(20) ,
CONSTRAINT pk_id PRIMARY KEY(id)
);

4、授予创建表的权限:

GRANT CREATE TABLE TO dog ;
GRANT CREATE SEQUENCE TO dog ;

那万一现在还有很多对象要创建，例如:索引视图、过程、函数等。这样的授权是非常麻烦的，所以为了方便用户的维护，在Oracle里面提供了两个角色(每个角色包含有多个权限) : RESOURCE、CONNECT ,一般只需要将这两个角色授予新用户即可。

补充

5、将RESOURCE和CONNECT角色授予dog

GRANT CONNECT,RESOURCE TO dog ;

6、用户维护

修改用户密码:

ALTER USER dog IDENTIFIED BY miaomiao ;

让用户的密码在登录一次之后失效，强迫用户去修改密码;

ALTER USER dog PASSWORD EXPIRE ;

锁定一个用户，让用户无法使用:

ALTER USER dog ACCOUNT LOCK ;

用户解锁:

ALTER USER dog ACCOUNT UNLOCK ;

7、那么以上都是为用户授予的系统权限，可是除了系统权限之外，还存在有对象权限。所谓对象权限指的是一个用户的操作权限，主要有四个权限: INSERT、 UPDATE、DELETE、 SELECT.

将scott.emp的INSERT、SELECT 权限授予dog

GRANT SELECT,INSERT ON scott.emp TO dog ;

8、权限回收，权限回收操作主要使用REVOKE指令完成;

回收对象权限

AREVOKE SELECT,INSERT ON scott.emp FROM dog ;

范例:回收角色

REVOKE CONNECT, RESOURCE FROM dog ;

范例:回收系统权限

REVOKE CREATE SESSION,CREATE TABLE,CREATE SEQUENCE FROM dog ;

9、
删除用户

DROP USER dog CASCADE ;

以上的这几步操作也只是一个基本参考而已。

数据库设计范式

实际工作中，对于数据表的设计只有一个原则：“根据业务尽可能的减少多表查询”

第一范式（单表）

数据表之中的每一个字段都不可再分，简单的理解就是都使用标准数据类型，例如:以下的数据库设计就不符合于第一设计范式。

DROP TABLE member PURGE ;
CREATE TABLE member(
编号 NUMBER PRIMARY KEY,
姓名 VARCHAR2(20) ,
联系方式VARCHAR2(200)
);

对于联系方式还可以划分出许多的子字段，例如:手机、邮箱、QQ、地址、邮政编码，所以此时的设计就不符合于第一-设计范式，那么必须按照如下的方式进行修改:
DROP TABLE member PURGE ;

CREATE TABLE member(
编号 NUMBER PRIMARY KEY ,
姓名 VARCHAR2(20),
地址 VARCHAR2(200) ,
邮政编码 VARCHAR2(6) ,
电话 VARCHAR2(20) ,
qq VARCHAR2(20)
);

此时的字段已经无法再细分，所以此时的设计符合于第一设计范式。例如，之前的dept表salgrade表都是不能够再分，所以都符合于第一设计范式。但是针对于第一设计范式有两点小说明:

说明一:如果系统在中国则名字就表示一个字段，如果在国外，则需要编写firstname、lastname, 此不为不可再分的概念范畴;
说明二:在设计表的时候都使用标准类型(NUMBER、VARCHAR2、CLOB、DATE) ,所以在表示生日的时候，千万不要将生日拆分为三个字段:生日年(NUMBER(4))生日月(NUMBER(2))、生日天(NUMBER(2))。

第二范式（多对多）

第三范式（一对多）

第一范式（1NF）
第一范式要求数据表中每个字段的值必须具有原子性，也就是不可再分；
属性的原子性具有主观性，需要根据场景应用具体分析；
第二范式（2NF）
在第一范式基础上，要求：

1）非主键字段必须完整依赖主键字段，而不是部分依赖；

2）数据表记录可唯一标识；
第二范式说明每个数据表都是一个独立的对象，有自己的独立含义；
第三范式（3NF）
在第二范式基础上，要求：数据表中的所有非主键字段必须与主键字段直接相关，而不能与其他非主键字段存在依赖关系；
所有非主键字段之间互相独立；
巴斯·科德范式（BCNF）
简称巴斯范式，是改进的第三范式；
在第三范式基础上，要求：数据表只能有一个候选键或每个候选键都是单属性，进一步降低数据冗余性；

sybase powerDesigner

数据库设计分析案例

所有的数据库设计都一定要存在有需求，而且所有的数据库设计设计过程之中，奉劝一句：不要考虑太多，以完成需求的功能为主。今天重点关注表的关系，而字段的扩充可以由你们自己去做。

前台用户

1、前台用户可以进行注册、登陆及修改个人信息的操作(用户注册后需要等待审核才可以登陆);

现在的用户名都使用 email, 所以此时使用VARCHAR2 就够了。

所谓的审核实际上可以增加一个审核的标志位，这个标志位使用NUMBER(flag 字段 ) 表示：

flag=0: 表示已经通过审核了，可以直接使用；
flag=1: 表示未通过审核，或者是用户被锁定。

2、用户可以浏览本程序的合作站点，网站上可以给出友情链接的地址；

建立一个单独的友情链接数据表，保存连接信息。如果要想进行实体表数据的维护，那么一定需要一个主键字段，而且这个时候的主键字段只需要自动增长即可，所以可以使用序列处理。

3、用户登陆之后可以下载相关的软件，每种软件都放在不同的分类之中；

·例如：媒体工具–>图形处理–> Photoshop

本次给出的分类实际上是分为两类，即：一级类别和二级类别。通过以上的提示应该可以建立出三张表：类别表、子类别表、软件表，一个类别包含有多个软件，一个类别包含有多个子类别，一个子类别包含有多个软件。

4、用户下载软件之后可以对软件的使用评分，并且可以进行简短评价；

需要一张保存用户的下载记录数据表，一个用户可以下载多个软件，一个软件可以被多个用户下载，每个用户可针对于一个软件进行评论和打分。

5、所有的用户(已登陆和未登陆)根据自己的权限，可以浏览不同的公告；

现在需要一张表保存公告的信息，此表一定是一张实体表。

此时的权限实际上应该算是一种访问的级别，那么在公告上应该存在公告的浏览级别，而用户上也应该存在一个用户的级别，在进行访问的时候根据用户的级别来选择可以浏览的公告。

现在假设级别有一共三个：绝密 (level=0)、普通 (level =1)、公共 (level =2);

如果说现在用户的级别是1 (level=1), 那么此时可以访问的公告信息一定要使用WHERE 执行限定

查询(公告level>= 用户level, 公告level>=1, 相当于查询出了等级为1和2的公告，但是看不了0)。

那么下面就可以在用户表和公告表之中都保存有一个level的字段，且类型是 NUMBER。

6、下载软件的时候可以根据不同的操作系统选择不同的版本，也可以设置一些推荐下载功能；

一个操作系统下可以提供有多个软件，而一个软件也可能会提供不同的操作系统版本，是多对多关系。

如果要想设置推荐下载的功能也是设置一个推荐的标志位：推荐(0)、普通(1)。

7、下载软件可以有积分；

首先在软件表之中设置一个积分，同时所有的积分可以在用户表之中体现。

8、用户可以查看自己的下载量，可以查看全部的，也可以查看自己下载的一个软件。

后台用户

1、可以查询一个软件的下载量；

每一个软件的下载量都在下载记录表之中统计，但是如果每次都统计下载记录表，性能很差，所以可以在软件表之中设置一个下载量。

2、有多种不同管理员权限，管理员保存在不同的组之中，每个组有不同的权限，一个管理员可以同时在多个管理员组；

一个管理员可以在多个管理员组，一个管理员组之中可以有多个管理员，多对多；
一个管理员组可以有多个权限，一个权限可以属于多个管理员组，多对多。

3、可以对公告、友情链接功能进行管理，需要相关管理员审核通过，自己无法审核；

公告和友情链接的数据是由管理员发布，但是所有发布的数据需要通过审核，所以应该增加一个审核的标志位 (flag), 此位有如下的取值：

flag=0: 表示通过审核；
flag=1: 表示未通过审核；
flag=2:表示重新审核。

4、管理员根据不同的权限可以上传新的软件，新上传的软件必须由超级管理员(只能有一个)进行审核后才能通过，或者具备相应权利的管理员才可以通过；

一般一个系统都会存在有一个超级管理员和若干个普通管理员，所以可以在管理员表之中增加一个超级管理员标志，取值如下：超级管理员(0)、普通管理员(1)。

5、可以对所有的软件评论进行管理；

在软件评论表之中存在有编号，依据此编号就可以实现删除操作，评论一般别修改。

6、用户积分管理，根据下载可以有积分存在，并可以通过积分兑换不同的礼品；

每种兑换的礼品都有需要积分，所以直接定义礼品表即可，如果有需要可以定义一个礼品的类别表。

7、登陆日志管理，所有的前台用户登陆后都要完整的记录好登陆时间；

一个用户可以存在有多个登录日志信息，是一个一对多关系。

8、添加软件时，需要写清楚软件的生产商，并指明软件的类型；

一个软件生产商可以定义多个软件，属于一对多关系。

9、管理员可以控制一个用户的锁定。

在用户表之中有一个审核标记，通过控制审核标记就可以控制用户锁定了。

但是以上的数据表只是阐述出了表与表之间的关系，具体的字段实际上还是需要进行一些分析后才可以得知。
本程序的合作站点，网站上可以给出友情链接的地址；

[外链图片转存中…(img-nkeEFDn2-1713792069388)]

3、用户登陆之后可以下载相关的软件，每种软件都放在不同的分类之中；

·例如：媒体工具–>图形处理–> Photoshop

[外链图片转存中…(img-Lj2EqvTR-1713792069388)]

4、用户下载软件之后可以对软件的使用评分，并且可以进行简短评价；

需要一张保存用户的下载记录数据表，一个用户可以下载多个软件，一个软件可以被多个用户下载，每个用户可针对于一个软件进行评论和打分。

[外链图片转存中…(img-tmyiZVTu-1713792069388)]

5、所有的用户(已登陆和未登陆)根据自己的权限，可以浏览不同的公告；

现在需要一张表保存公告的信息，此表一定是一张实体表。

现在假设级别有一共三个：绝密 (level=0)、普通 (level =1)、公共 (level =2);

如果说现在用户的级别是1 (level=1), 那么此时可以访问的公告信息一定要使用WHERE 执行限定

查询(公告level>= 用户level, 公告level>=1, 相当于查询出了等级为1和2的公告，但是看不了0)。

那么下面就可以在用户表和公告表之中都保存有一个level的字段，且类型是 NUMBER。

[外链图片转存中…(img-HgRel9Gp-1713792069388)]

6、下载软件的时候可以根据不同的操作系统选择不同的版本，也可以设置一些推荐下载功能；

一个操作系统下可以提供有多个软件，而一个软件也可能会提供不同的操作系统版本，是多对多关系。

如果要想设置推荐下载的功能也是设置一个推荐的标志位：推荐(0)、普通(1)。

[外链图片转存中…(img-MYk1dSiu-1713792069388)]

7、下载软件可以有积分；

首先在软件表之中设置一个积分，同时所有的积分可以在用户表之中体现。

[外链图片转存中…(img-peYelBES-1713792069388)]

8、用户可以查看自己的下载量，可以查看全部的，也可以查看自己下载的一个软件。

后台用户

1、可以查询一个软件的下载量；

每一个软件的下载量都在下载记录表之中统计，但是如果每次都统计下载记录表，性能很差，所以可以在软件表之中设置一个下载量。

[外链图片转存中…(img-nV9mnwua-1713792069389)]

2、有多种不同管理员权限，管理员保存在不同的组之中，每个组有不同的权限，一个管理员可以同时在多个管理员组；

一个管理员可以在多个管理员组，一个管理员组之中可以有多个管理员，多对多；
一个管理员组可以有多个权限，一个权限可以属于多个管理员组，多对多。

[外链图片转存中…(img-2EOur8lj-1713792069389)]

3、可以对公告、友情链接功能进行管理，需要相关管理员审核通过，自己无法审核；

公告和友情链接的数据是由管理员发布，但是所有发布的数据需要通过审核，所以应该增加一个审核的标志位 (flag), 此位有如下的取值：

flag=0: 表示通过审核；
flag=1: 表示未通过审核；
flag=2:表示重新审核。

[外链图片转存中…(img-yNMTc4Ug-1713792069389)]

[外链图片转存中…(img-NxATLql8-1713792069389)]

5、可以对所有的软件评论进行管理；

在软件评论表之中存在有编号，依据此编号就可以实现删除操作，评论一般别修改。

6、用户积分管理，根据下载可以有积分存在，并可以通过积分兑换不同的礼品；

每种兑换的礼品都有需要积分，所以直接定义礼品表即可，如果有需要可以定义一个礼品的类别表。

[外链图片转存中…(img-IMg3jJJs-1713792069389)]

7、登陆日志管理，所有的前台用户登陆后都要完整的记录好登陆时间；

一个用户可以存在有多个登录日志信息，是一个一对多关系。

[外链图片转存中…(img-8tPM9Rmy-1713792069389)]

8、添加软件时，需要写清楚软件的生产商，并指明软件的类型；

一个软件生产商可以定义多个软件，属于一对多关系。

[外链图片转存中…(img-Qk3SGGhD-1713792069389)]

9、管理员可以控制一个用户的锁定。

在用户表之中有一个审核标记，通过控制审核标记就可以控制用户锁定了。

但是以上的数据表只是阐述出了表与表之间的关系，具体的字段实际上还是需要进行一些分析后才可以得知。

小凡012

关注

4
点赞
踩
11

收藏

觉得还不错? 一键收藏
0
评论
oracle数据库学习笔记

select empno 编号，ename 姓名，sal*12 年薪 from emp；求出本月的最后一天日期。
复制链接

扫一扫