oracle练习题scott实例

Oracle系列《一》：简单SQL与单行函数

使用scott/密码用户下的emp表和dept表完成下列练习，表的结构说明如下

emp员工表(empno员工号/ename员工姓名/job工作/mgr上级编号/hiredate受雇日期/sal薪金/comm佣金/deptno部门编号)

dept部门表(deptno部门编号/dname部门名称/loc地点)

工资 ＝ 薪金 ＋ 佣金

【1】登录Oracle数据库
sqlplus/nolog
conn scott/密码
若是使用SYS的账号进行登录的话，则使用以下语句
conn / as sysdba

【2】显示当前用户
show user;

【3】查看当前用户的所有表
SELECT * FROM tab;


【4】EMP表内容查询
SELECT * FROM emp;
出错，原因是没有找到该表，因为该表时SCOTT用户的表，所以查询时应该加上scott.emp就可以了


【5】查询一张表的结构，例如dept表
desc dept;

【6】在雇员表中查询雇员的编号、姓名、工作
SELECT empno,ename,job FROM emp;

【7】可以为列名取别名，在Linux下Oracle如果英文别名不加上双引号则会变成大写
SELECT empno 编号,ename 姓名,job 工作 FROM emp;

【8】查询所有的工作
SELECT DISTINCT job FROM emp;
工作可能会重复，加上DISTINCT关键字

【9】练习：若要求按照以下的格式进行结果输出，如 NO:7469,Name:SMITH,Job:CLERK
SELECT'NO:'||empno||',Name:'||ename||',Job:'||job FROM emp;

【10】要求列出每个雇员的姓名及年薪
SELECT ename,sal*12 income FROM emp;
这里年薪最好用别名进行标识，可以一眼就能明白

【11】查看每月可以得到奖金的雇员信息
SELECT * FROM emp WHERE comm is NOT NULL;

【12】要求基本工资大于1500，同时可以领取奖金的雇员信息
SELECT * FROM emp WHERE sal>1500 AND commis NOT NULL;
如果是或的是关系，则使用 OR

【13】查询基本工资不大于1500，同时不可以领取奖金的雇员信息
SELECT * FROM emp WHERE NOT(sal>1500 ANDcomm is NOT NULL);

【14】查询在1981年雇佣的全部雇员信息，BETWEEN .. AND 包含等于的情况
SELECT * FROM emp
WHERE hiredate BETWEEN '01-JAN-81' AND '31-DEC-81';

【15】Oracle对大小敏感，所以查询时名字要区分大小写

【16】要求查询出雇员编号不是 7369、7499的雇员信息
SELECT * FROM emp
WHERE empno NOT IN(7369,7499);

【17】SQL中LIKE语句要注意通配符 % 和 _
SELECT * FROM emp
WHERE hiredate LIKE '%81%';

【18】查看雇员编号不是7369的雇员信息，使用<>或!=
SELECT * FROM emp
WHERE empno<>7369;

【19】要求对雇员的工资由低到高进行排序，升序为默认(ASC)，降序(DESC)
SELECT * FROM emp
order BY sal;

【20】查看出部门号为10的雇员信息，查询的信息按照工资从高到低，若工资相等则按雇用日期从早到晚排列
SELECT * FROM emp
WHERE deptno=10
order BY sal DESC,hiredate ASC;

数据库系统中，每个数据库之间区别最大的就是在函数的支持上，单行函数是最简单的函数，单行函数分为
1、字符函数：接受字符输入并且返回字符或数值
2、数值函数：接受数值输入并返回数值
3、日期函数：对日期型数据进行操作
4、转换函数：将一种数据类型转换为另一种数据类型
5、通用函数：NVL、DECODE 函数

字符函数：
【1】大小写转换 UPPER 和 LOWER
SELECT UPPER('smith') FROM dual;
【2】将雇员姓名变为开头字母大写，INITCAP
SELECT INITCAP(ename) FROM emp;

字符函数中有连接函数CONCAT，但不如 || 好用，还有字符串处理的一些函数
字符串截取：substr()
字符串长度：length()
内容替换：replace()
SELECT substr('hello',1,3),length('hello'),replace('hello','l','x') FROM dual;
这里注意的是Oracle中字符串截取从0和从1开始都是一样的，谨防面试提问

【3】要求显示所有雇员的姓名及姓名的后3个字符
SELECT ename,SUBSTR(ename,LENGTH(ename)-2)FROM emp;
以上操作显得较为麻烦，substr()函数是可以倒着截取
SELECT ename,SUBSTR(ename,-3,3) FROM emp;

数值函数：
1、四舍五入：ROUND()
2、截断小数位：TRUNC()
3、取余(取模)：MOD
SELECT ROUND(789.536) FROM dual;
【1】保留2位小数,(如果是-2则对整数进行四舍五入，变为800了)
SELECT ROUND(783.56,2) FROM dual;
【2】使用MOD()函数进行取余操作
SELECT MOD(10,3) FROM dual;

日期函数：
1、日期 - 数字 = 日期
2、日期 + 数字 = 日期
3、日期 - 日期 = 数字(天数)
4、日期 - 日期 = 数字（月数）
---日期 - 日期 = 数字（月数
select months_between('19-12月-1999','19-3月-1999') from dual;
select months_between(to_date('2000-05-20','yyyy-mm-dd'),to_date('2005-05-20','yyyy-mm-dd')) from dual;
---日期 - 日期 = 数字(天数)
select to_date('20170611', 'yyyymmdd') - to_date('20170507', 'yyyymmdd')
from dual;
select ceil((To_date('2017-05-02 00:00:00', 'yyyy-mm-dd hh24-mi-ss') -
To_date('2016-04-30 23:59:59', 'yyyy-mm-dd hh24-mi-ss'))) "相差天数"
FROM DUAL;

【1】求出当前日期
SELECT SYSDATE FROM dual;

Oracle提供了以下的日期函数支持：
MONTHS_BETWEEN():求出给定日期范围的月数
ADD_MONTHS():在指定日期上加上指定的月数，求出之后的日期
NEXT_DAY():下一个的今天的日期
LAST_DAY():求出给定日期的最后一天日期

【2】求出从雇用日期到今天所有雇员的雇员编号、姓名和月数
SELECT empno,ename,ROUND(MONTHS_BETWEEN(SYSDATE,hiredate)) FROM emp;

【3】验证 ADD_MONTHS()、NEXT_DAY()、LAST_DAY()
SELECT ADD_MONTHS(SYSDATE,4) FROM DUAL;
SELECT NEXT_DAY(SYSDATE,'MON') FROM DUAL;
SELECT LAST_DAY(SYSDATE) FROM DUAL;

转换函数：
1、TO_CHAR()： 将日期或数值转换成字符串
2、TO_NUMBER()：将字符串转换成数字
3、TO_DATE()： 将字符串转换成日期

【1】将年月日进行分开，要指定拆分的通配符,yyyy-mm-dd
SELECT empno,ename,TO_CHAR(hiredate,'yyyy')datetime FROM emp;

【2】将薪水的数字进行格式化，'$99,999'表示美元，'L99,999'表示当地货币
SELECT empno,ename,TO_CHAR(sal,'99,999') salary FROM emp;

【3】TO_NUMBER()验证
SELECT TO_NUMBER('123')+TO_NUMBER('123') FROM DUAL;

【4】TO_DATE()验证，如下例子执行后显示为 11-JUL-11
SELECT TO_DATE('2011-7-11','yyyy-mm-dd')FROM DUAL;

通用函数：
【1】求出每个雇员的年薪(应算上奖金)
SELECT empno,ename,(sal+comm)*12 FROM emp;
由于comm中有NULL,NULL值计算后还是NULL,正确如下：
SELECT empno,ename,NVL(comm,0),(sal+NVL(comm,0))*12income FROM emp;
NVL可以理解为将NULL值转换为具体的内容，这里是0

【2】DECODE()函数，该函数类似于 IF ... ELSEIF...ELSE
语法如下：
DECODE(col/expression,选择1,结果1[，选择2，结果2，...,默认])
验证DECODE()函数
SELECT empno,ename,hiredate,
DECODE(job,'CLERK','业务员','SALESMAN','销售人员','MANAGER','经理','ANALYST','分析员','PRESIDENT','总裁') 职位
FROM emp;

SQL简单语句练习：

【1】找出佣金高于薪金的60%的员工
SELECT * FROM emp WHERE comm>sal*0.6

【2】找出部门10中所有经理(MANAGER)和部门20中所有办事员(CLERK)的详细资料
SELECT * FROM emp
WHERE (deptno=20 AND job='MANAGER')
OR (deptno=10 AND job='CLERK');

【3】找出既不是经理又不是办事员但其薪金大于或等于2000的所有员工的资料
SELECT * FROM emp
WHERE job NOT IN('MANAGER','CLERK') AND sal >=2000;

【4】找出有奖金的员工的不同国祚
SELECT DISTINCT job FROM emp
WHERE comm IS NOT NULL;

【5】找出各月倒数第3天受雇的所有员工
SELECT * FROM emp
WHERE LAST_DAY(hiredate)-2=hiredate;

【6】找出早于12年前受雇的员工
SELECT * FROM emp
WHERE MONTHS_BETWEEN(sysdate,hiredate)/12 > 12;

【7】显示刚好为5个字符的员工的姓名
SELECT ename FROM emp
WHERE length(ename)=5;

【8】显示不带有"R"的员工的姓名
SELECT ename FROM emp
WHERE ename NOT LIKE '%R%';

【9】显示员工的姓名和受雇日期，将最老的员工排在最前
SELECT * FROM emp
order BY hiredate;

【10】显示所有员工的姓名，加入公司的年份和月份，按受雇日期所在月排序，若月份相同则按年份排序
SELECT ename,TO_CHAR(hiredate,'yyyy')year,TO_CHAR(hiredate,'mm') month FROM emp
ORDER BY month,year;

【11】找出在2月受聘的员工
SELECT * FROM emp
WHERE TO_CHAR(hiredate,'mm')=2;

【12】以年月日方式显示所有员工服务年限
SELECTename,TRUNC(MONTHS_BETWEEN(sysdate,hiredate)/12) year,
TRUNC(MOD(MONTHS_BETWEEN(sysdate,hiredate),12)) month,
TRUNC(MOD(sysdate-hiredate,30)) day
FROM emp;


Oracle系列《二》：多表复杂查询和事务处理
多表查询应该注意去除笛卡尔积，一般多个表时会为表起个别名

【1】要求查询雇员的编号、姓名、部门编号、部门名称及部门位置
SELECT e.empno,e.ename,d.deptno,d.dname,d.loc
FROM emp e,dept d
WHERE e.deptno = d.deptno;

【2】要求查询每个雇员的姓名、工作、雇员的直接上级领导的姓名(表自关联)
SELECT e.ename,e.job,m.ename
FROM emp e,emp m
WHERE e.mgr = m.empno;

【3】对【2】进行扩充，将雇员所在部门名称同时列出
SELECT e.ename,e.job,m.ename,d.dname
FROM emp e,emp m,dept d
WHERE e.mgr = m.empno AND e.deptno=d.deptno;

【4】查询每个雇员的姓名、工资、部门名称，工资在公司的等级(salgrade)，及其领导的姓名所在公司的等级
<1>先确定工资等级表的内容
SELECT * FROM salgrade;

<2>查询每个雇员的姓名、工资、部门名称和工资在公司的等级
SELECT e.ename,e.sal,d.dname,s.grade
FROM emp e,dept d,salgrade s
WHERE e.deptno=d.deptno AND e.sal BETWEEN s.losal ANDs.hisal;

<3>查询其领导姓名及工资所在公司的等级
SELECTe.ename,e.sal,d.dname,s.grade,m.ename,m.sal,ms.grade
FROM emp e,dept d,salgrade s,emp m,salgrade ms
WHERE e.deptno = d.deptno AND e.sal BETWEEN s.losalAND s.hisal
AND e.mgr = m.empno AND m.sal BETWEEN ms.losal ANDms.hisal;

【5】左连接与右连接的概念，"+"在等号左边表示右连接，反之，左连接
查询雇员的编号、姓名及其领导的编号、姓名
SELECT e.empno,e.ename,m.empno,m.ename
FROM emp e,emp m
WHERE e.mgr = m.empno(+);
就发现将KING的那条记录也连过来了

SQL1999语法中有如下几种连接(了解)
1、交叉连接CROSS JOIN，产生笛卡尔积
SELECT * FROM emp CROSS JOIN dept;
2、自然连接NATURAL JOIN,自动进行关联字段的匹配
SELECT * FROM emp NATURAL JOIN dept;
3、使用USING子句，直接关联操作列
SELECT * FROM emp JOIN dept USING(deptno)
WHERE deptno=30;
4、使用ON子句，用户自己编写的条件
SELECT * FORM emp JOIN dept ON(emp.deptno =dept.deptno)
WHERE deptno=30;
5、左连接(左外连接、LEFT (OUTER) JOIN)、右连接(右外连接、RIGHT (OUTER) JOIN)

组函数及分组统计
1、COUNT():求出全部记录数
2、MAX()：求出一组中最大值
3、MIN():求出最小值
4、AVG()：求出平均值
5、SUM()：求和

【1】求出每个部门的雇员数量
SELECT deptno,count(empno)
FROM emp
GROUP BY deptno;

【2】按部门分组，并显示部门的名称，及每个部门的员工数
SELECT d.dname,COUNT(e.empno)
FROM emp e,dept d
WHERE e.deptno=d.deptno
GROUP BY d.dname;

【3】要求显示平均工资大于2000的部门编号和平均工资
SELECT deptno,AVG(sal)
FROM emp
WHERE AVG(sal)>2000
GROUP BY deptno;
出错，WHERE子句中不能出现分组函数的条件，要使用HAVING子句
上述语句应该改为如下
SELECT deptno,AVG(sal)
FROM emp
GROUP BY deptno
HAVING AVG(sal)>2000

【4】显示非销售人员工作名称以及从事同一工作雇员的月工资总和，并且要求从事同一工作的雇员月工资合计大于$5000,
输出结果按月工资的合计升序排序
<1>按工作分组，求出非销售人员的月工资总和
SELECT job,SUM(sal)
FROM emp
WHERE job<>'SALESMAN'
GROUP BY job;
<2>对分组条件进行限制，然后进行排序,HAVING子句不能使用别名
SELECT job,SUM(sal) totalSal
FROM emp
WHERE job<>'SALESMAN'
GROUP BY job
HAVING SUM(sal) > 5000
ORDER BY totalSal;

【3】分组函数可以嵌套使用，但是在SELECT列中就不能再出现该分组条件的列名了
SELECT deptno,MAX(AVG(sal))
FROM emp
GROUP BY deptno;
出错！修改如下
SELECT MAX(AVG(sal))
FROM emp
GROUP BY deptno;

【4】查询出比7654工资要高的全部雇员的信息
<1>首先要查询雇员编号7654的工资
SELECT sal FROM emp WHERE empno=7654;
<2>以上述条件的结果最后后续查询的依据
SELECT * FROM emp
WHERE sal>(SELECT sal FROM emp WHERE empno=7654);

子查询在操作中分为以下三类：
1、单列子查询：返回的结果是一列的内容
2、单行子查询：返回多个列，也可能是一条记录
3、多行子查询：返回多个记录

【1】要求查询工资比7654高，同时与7788从事相同工作的全部雇员
SELECT * FROM emp
WHERE sal>(SELECT sal FROM emp WHERE empno=7654)
AND job=(SELECT job FROM emp WHERE empno=7788);

【2】要求查询 部门名称、部门员工数、部门平均工资，部门的最低收入雇员的姓名
<1>查询部门员工数、部门平均工资
SELECT deptno,COUNT(empno),AVG(sal)
FROM emp
GROUP BY deptno;
<2>查询部门的名称，及最低收入雇员姓名，要进行表关联(子查询)
SELECT d.dname,ed.c,ed.a,e.ename
FROM dept d,(
SELECTdeptno,COUNT(empno) c,AVG(sal) a,MIN(sal) min
FROM emp
GROUP BYdeptno) ed, emp e
WHERE d.deptno=ed.deptno AND e.sal = ed.min;

若上述存在两个最低工资的情况，则会出错，在子查询中存在以下3种查询的操作符号
IN：指定一个查询范围，例如查询每个部门的最低工资(返回值有多个)
SELECT * FROM emp
WHERE sal IN(SELECT MIN(sal) FROM emp GROUP BYdeptno);

ANY：=ANY(与IN操作一样)、>ANY(比最小大)、<ANY(比最大小)
SELECT * FROM emp
WHERE sal <ANY(SELECT MIN(sal) FROM emp GROUP BYdeptno);

ALL: >ALL(比最大要大)、<ALL(比最小的小),SQL语句类似上面

SQL多列子查询示例如下
SELECT * FROM emp
WHERE (sal,NVL(comm,-1)) IN
(SELECTsal,NVL(comm,-1) FROM emp WHERE deptno=20);


数据库更新操作INSERT、UPDATE、DELETE

【1】复制一张表，例如复制EMP表为MYEMP
CREATE TABLE MYTEMP AS SELECT * FROM emp;

【2】将编号为7899的雇员的领导取消
UPDATE myemp SET mgr=null WHERE empno=7899;

【3】更新时，一定要注意不能批量更新(加上WHERE子句)，多列更新例子如下
UPDATE myemp SET mgr=null,comm=null WHEREempno IN(7369,8899);

【4】删除掉全部领取奖金的雇员
DELECT FROM emp WHERE comm is NOT NULL;

事务处理 ACID
A：Atomicity 原子性：事务中的操作或者都完成，或者都取消
C：Consistency 一致性：事务中的操作保证数据库中的数据不会出现逻辑上不一致的情况
I：Isolation 隔离性：当前的事务与其他未完成的事务是隔离的
D：Durability 持久性：在COMMIT之后，数据永久保存在数据库中，在此之前，事务的操作都可以回滚

验证事务过程：
<1>创建一张临时表，只包含部门10
CREATE TABLE emp10 AS SELECT * FROM emp WHEREempno=10;
<2>删除emp10中的7782雇员
DELETE FROM emp10 WHERE empno=7782;
再打开另一个窗口，发现数据还存在，此时如果可以使用以下的两种命令进行事务处理
COMMIT 和 ROLLBACK 提交事务和回滚事务

SQL查询练习
【1】列出至少一个员工的所有部门
SELECT d.*,ed.cou FROM dept d,(
SELECTdeptno,COUNT(empno) cou FROM emp
GROUP BY deptno
HAVINGCOUNT(empno) > 1) ed
WHERE d.deptno=ed.deptno;

【2】列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门
SELECT d.deptno,d.dname,e.empno,e.ename
FROM dept d,emp e
WHERE d.deptno = e.deptno(+);

【3】列出所有"CLERK"(办事员)的姓名及其部门名称，部门的人数
<1>关联dept表
SELECT e.ename,d.dname
FROM emp e,dept d
WHERE e.deptno=d.deptno and e.job='CLERK';
<2>使用GROUP BY 完成部门分组人数
SELECT e.ename,d.dname,ed.cou FROM emp e,deptd,(
SELECTdeptno,COUNT(empno) cou FROM emp
GROUP BY deptno) ed
WHERE job='CLERK' AND e.deptno=d.deptno ANDed.deptno=e.deptno;


Oracle系列《三》：表、（约束）索引、序列、视图的使用
一、创建、删除、修改表

建立表：Oracle中主要数据类型 VARCHAR2、NUMBER、DATE、CLOB(大量文本)、BLOB(图片、音乐、电影)
如果只能复制一张表的结构，但不复制内容，则加上一个不可能成立的条件即可，例如
CREATE TABLE tmp AS (SELECT * FROM emp WHERE 1=2)

例如创建表Person如下：
CREATE TABLE person(
pid VARCHAR2(18),
name VARCHAR2(30),
age NUMBER(3),
birthday DATE,
sex VARCHAR(2) DEFAULT 'M'
);

如果发现创建表后需要添加特定的列，例如address列，则可以使用ALTER TABLE命令
ALTER TABLE person ADD(address VARCHAR2(20));

修改表中的列属性
ALTER TABLE person MODIFY(address VARCHAR2(30));

在数据库程序开发中，很少去修改表结构，在IBM DB2中就没有提供ALTER TABLE命令

在Oracle中提供RENAME命令对表进行重命名
RENAME person to personer;

在Oracle中要清空一张表的数据，但又不需要回滚，需要立即释放资源(与DELETE区别)
TRUNCATE TABLE personer;
（与DROP TABLE区别：前者删除内容，后者删除表）
二、表的约束

约束分类：主键约束、唯一约束、检查约束、非空约束、外键约束

添加约束如下：
CREATE TABLE person(
pid VARCHAR2(18),
name VARCHAR2(30) NOT NULL,
age NUMBER(3) CHECK(age BETWEEN 0 AND 150),
birthday DATE,
sex VARCHAR(2) DEFAULT 'M' ,
CONSTRAINTperson_pid_pk PRIMARY KEY(pid),
CONSTRAINT person_name_ukUNIQUE(name),
CONSTRAINT person_sex_ck CHECK(sexIN('M','F'))
);

以上约束可以采取自动命名和手动命名

现在要再添加一张表，使用主-外键约束
CREATE TABLEbook(
bid NUMBER PRIMARY KEY,
bname VARCHAR(20),
bprice NUMBER(5,2),
pid VARCHAR2(18),
CONSTRAINT person_book_pid_fk FOREIGN KEY(pid)REFERENCES person(pid)
);

这时候如果要删除掉person表的话，就会出现错误，此时可以使用强制性的删除手段
DROP TABLEperson CASCADE CONSTRAINT;
但是这种做法一般不用

如果在person 和 book 表中添加记录，而在person表中进行删除一条记录时，假设该记录的pid被
book表引用，那么会出现删除错误。如果希望一个表中的数据在删除时，能自动删除对应字表的记录，
可以使用级联删除的操作

CREATE TABLEbook(
bid NUMBER PRIMARY KEY,
bname VARCHAR(20),
bprice NUMBER(5,2),
pid VARCHAR2(18),
CONSTRAINT person_book_pid_fk FOREIGN KEY(pid)REFERENCES person(pid) ON DELETE CASCADE
);

添加约束语法如下：
ALTER TABLE 表名称 ADD CONSTRAINT 约束名称 约束类型(约束字段);
约束类型命名规则：
PRIMARY KEY:主键字段_PK
UNIQUE：字段_UK
CHECK：字段_CK
FOREIGN KEY：父子段_子字段_FK

ALTERTABLE person ADD CONSTRAINT person_pid_PK PRIMARY KEY(pid);
ALTERTABLE person ADD CONSTRAINT person_name_UK UNIQUE(name);
ALTERTABLE person ADD CONSTRAINT person_age_CK CHECK(age BETWEEN 0 AND 150);

ALTERTABLE book ADD CONSTRAINT person_book_pid_fk FOREIGN KEY(pid) REFERENCESperson(pid)
ON DELETECASCADE;

删除约束语法如下：
ALTER TABLE 表名称 DROP CONSTRAINT 约束名称;

ALTERTABLE person DROP CONSTRAINT person_age_CK;
ALTERTABLE book DROP CONSTRAINT person_book_pid_fk;

ROWNUM伪列

SELECT ROWNUM,empno,ename FROM emp;
ROWNUM采用自动编号的形式出现

加入只想显示前5条记录，那么ROWNUM<=5
SELECT ROWNUM,empno,ename FROM emp;
WHEREROWNUM<=5;

但是如果要查询5-10条的记录的话，则查询不出，只能采用子查询的方式
SELECT * FROM (SELECT ROWNUM m,empno,ename
FROM emp
WHERE ROWNUM<=10) tmp
WHEREtmp.m>5;

不好的查询思路：
select ROWNUM,empno,job,mgr from empt where rownum<=10
MINUS
select ROWNUM,empno,job,mgr from empt where rownum<=5
集合操作：在Oracle中提供了3类集合操作:并、交、差
UNION：将多个查询结果组合到一个查询结果之中，没有重复值
UNION ALL：与UNICON不同的是包含重复值
INTERSECT：返回多个查询结果中相同的部分
MINUS：返回两个查询结果的差集

验证UNION和UNION ALL
CREATE TABLE emp20 AS SELECT * FROM emp WHERE deptno=20; (5条记录)
SELECT * FROM emp UNION SELECT * FROM emp20; (14条记录)
SELECT * FROM emp UNION ALL SELECT * FROM emp20; (19条记录)

验证MINUS 和 INTERSECT
SELECT * FROM emp MINUS SELECT * FROM emp20; (9条记录)
SELECT * FROM emp INTERSECT SELECT * FROM emp20; (5条记录)

案例：
主键约束添加删除
1、创建表的同时创建主键约束
create table accounts ( accounts_number number primary key, accounts_balance number );

2、删除表中已有的主键约束
SELECT * FROM USER_CONS_COLUMNS WHERE TABLE_NAME='accounts';
找出主键名 ALTER TABLE ACCOUNTS DROP CONSTRAINT SYS_C003063;
3、向表中添加主键约束 ALTER TABLE ACCOUNTS ADD CONSTRAINT PK_ACCOUNTS PRIMARY KEY(ACCOUNTS_NUMBER);

Oracle中视图的操作

1、创建视图
CREATE VIEW 视图名称 AS 子查询
这条子查询是非常复杂的语句
CREATE VIEW empv20 AS
SELECT empno,ename,job,hiredate
FROM emp
WHERE deptno=20;
2、查询视图
SELECT* FROM empv20;

3、删除视图
DROPVIEW empv20;

如果要修改视图，则要先删除视图，在Oracle为了方便用户修改视图，提供了一个替换的命令
CREATE ORREPLACE 视图名称 AS 子查询

视图可以封装复杂的查询，例如查询部门名称，部门的人数，平均工资以及最低工资的雇员
CREATE OR REPLACE VIEW empv20 AS
SELECT d.dname,ed.c,ed.a,e.ename FROM dept d,(
SELECT deptno,COUNT(empno) c, AVG(sal) a,MIN(sal) minsal
FROM emp
GROUP BY deptno) ed,emp e
WHERE d.deptno=ed.deptno AND e.sal=ed.minsal;

在开中发每次都写这么长的SQL语句不方便，可以将其建立成视图

如果对视图进行更新操作，在视图中不应该包含真实数据，按以下命令进行操作
UPDATE empv20 SET deptno=30 WHERE empno=7369;
发现视图已经正常更新，因为emp表中7369编号已经修改为30了，所以在创建视图是有条件的
SQL提供了两个重要的参数

WITH CHECKOPTION：不能更新视图的创建条件
CREATE OR REPLACE VIEW empv20 AS
SELECT * FROM emp WHERE deptno=20
WITH CHECK OPTION;

创建条件不能进行更新了，但其他字段仍然可以更新
UPDATE empv20 SET ename='wilson'WHERE empno=7369;

所以这时可以使用视图的第2个条件：创建只读视图
CREATE OR REPLACE VIEW empv20 AS
SELECT * FROM emp WHERE deptno=20
WITH READ ONLY;

Oracle系列《四》：数据库的设计分析
一、序列的使用

在很多数据库系统中都存在一个自动增长的列，如果在Oracle中要完成自动增长的功能，只能依靠序列完成

序列的创建格式
CREATE SEQUENCE sequence
[INCREMENT BY n][START WITH n]
[{MAXVALUE n| NOMAXVALUE}]
[{MINVALUE n| NOMINVALUE}]
[{CYCLE|NOCYCLE}]
[{CACHE n|NOCACHE}]

创建一个myseq的序列
CREATE SEQUENCE myseq;
创建完该序列之后，所有的自动增长应该由用户自己处理
nextVal:取得序列的下一个内容
currVal:取得序列的当前内容

建立一张表验证序列
CREATE TABLE testseq(
next NUMBER,
curr NUMBER,
);

INSERT INTO testseq(next,curr)VALUES(myseq.nextval,myseq.currval);
将这条SQL执行5次，然后进行查表操作
SELECT * FROM testseq;

可以发现,nextval的内容始终在自动增长，而curr使用取出当前操作的序列的结果，该序列增长幅度为1
如果要进行修改，则加上 INCREMENT BY 长度的语句

DROP SEQUENCE myseq;
CREATE SEQUENCE myseq INCREMENT BY 2;

发现每次取出的结果都是奇数 1,3,5,7,9，序列是从1开始的，我们可以指定序列的开始位置，例如
CREATE SEUENCE myseq MAXVALUE 10 INCREMENT BY 2 START WITH 2 CACHE 2 CYCLE;

二、同义词的概念(了解)

SELECT SYSDATE FROM dual;
dual是一张虚拟表，该表在SYS用户下有定义，可以使用以下语句查询到
SELECT * FROM tab WHERE TNAME='DUAL';
此表在SYS下，但SCOTT用户却可以直接通过表名称访问，正常情况下我们是需要使用"用户名.表名称"
该情况就是同义词的作用

创建同义词：
CREATE synonym 同义词名称 FOR 用户名.表名称;
例如，将scott.emp 定义 emp 的同义词
CREATE synonym emp FOR scott.emp;

删除同义词
DROP synonym emp;
同义词这种特性只适合于Oracle数据库

三、用户管理(*)

创建用户: CREATE USER 用户名 IDENTIFIED BY 密码;
CREATE USER test IDENTIFIED BY test123;

打开一个新的窗口使用test用户登录，发现其没有session权限无法进行登录，此时要进行授权
GRANT CREATE SESSION TO test;
将创建SESSION权限给test用户，之后该用户可以正常登录，但是其没有创建表的权限

Oracle中可以将多个权限定义成一组角色，分配该角色给用户即可
在Oracle中主要提供了两个角色：CONNECT、RESOURCE，将这两个角色赋予test用户
GRANT CONNECT,RESOURCE TO test;

管理员对用户密码进行修改：
ALTER USER test IDENTIFIED BY hello;
在一般系统中，在用户进行第一次登录时可以修改密码，可以使用如下方式
ALTER USER 用户名 PASSWORD EXPIRE;
ALTER USER test PASSWORD EXPIRE;
这时会提示用户输入旧口令及新的密码

锁住用户和对用户解锁
ALTER USER test ACCOUNT LOCK;
ALTER USER test ACCOUNT UNLOCK;

此时，想查询SCOTT用户下的表EMP，发现没有权限，执行如下命令即可
GRANT SELECT,DELETE ON scott.emp TO test;

收回权限的命令：
REVOKE SELECT,DELETE ON scott.emp FROM test;

数据库的备份与恢复
数据库备份：exp
数据库恢复：imp

嵌套表的概念(了解)

嵌套表：在一个表中还包含另外一个子表
首先为嵌套表指定类型，该类型需要单独定义
CREATE TYPE project_ty AS OBJECT(
priodNUMBER(4),
pronameVARCHAR2(30),
prodate DATE
);
/
该类型创建成后，不意味着此类型能够直接使用，要为此类型指定一个名称
CREATE TYPE project_nt AS TABLE OF project_ty
/
这样就可以使用project_nt表示project_ty类型，现在根据此类型创建department表
CREATE TABLE department(
deptno NUMBER(2) PRIMARY KEY,
dname VARCHAR2(50) NOT NULL,
projectsproject_nt
)NESTED TABLE projects STORE ASproject_nt_tab_temp;

如果要进行数据插入的话
INSERT INTO department(deptno,dname,projects)VALUES(
1,'tech',
project_nt(
project_ty(1001,'ERP',SYSDATE),
project_ty(1002,'CRM',SYSDATE)
)
);

查询部门表，可以返回多个项目
SELECT * FROM department;

如果需要查看一个部门的全部项目的话，查询嵌套表
SELECT * FROM TABLE
(SELECT projectsFROM department WHERE deptno=1);

更新编号为1001的项目名称
UPDATE TABLE (SELECT projects FROM departmentWHERE deptno=1) pro
SET VALUES(pro)=project_ty('1001','APR',SYSDATE) WHEREpro.proid=1001;

可变数组的概念：是嵌套表的升级版 ... (有用到再了解)

数据库范式的概念

第一范式：所有的信息都集中在一张表上，例如
CREATE TABLE person(
pid NUMBER(4) PRIMARY KEY,
nameVARCHAR2(50),
infoVARCHAR(200)
);

第一范式会出现问题，例如创建一张学生选课表
CREATE TABLE selectCourse(
stunoVARCHAR2(50),
stunameVARCHAR2(50),
stuage NUMBER,
cnameVARCHAR2(50),
grade NUMBER,
credit NUMBER
);

以上不仅所有的课程信息冗余了，而且还存在以下的问题：
1、没有学生选该门课，那么该门课就消失了
2、课程本身有编号，按照以上设计，课程编号肯定重复
3、要更改课程信息，则要修改许多记录

使用第二范式进行修改
CREATE TABLE student(
stuno VARCHAR2(10) PRIMARY KEY,
stunameVARCHAR2(20),
stuage NUMBER
);

CREATE TABLE course(
cid NUMBER(5)PRIMARY KEY,
cnameVARCHAR2(20),
credit NUMBER
);

CREATE TABLE selectCourse(
stunoVARCHAR2(50),
cid NUMBER(5),
grade NUMBER,
设置主-外键关系
);

以上设计解决了以下问题：
1、学生不选课，课程不会消失
2、更新课程的时候直接更新课程表
3、所有关联关系在关系表中体现

这里是完成了多-多关系

使用第三范式：
按照第二范式的设计一张学生表，包括学号、姓名、年龄、所在院校、学院地址、学院电话等
会出现一个学生同时在多个学院同时上课，正常应该是：一个学院包含多个学生，一个学生属于一个学院C

CREATE TABLE collage(
cid NUMBER(40)PRIMARY KEY,
cnameVARCHAR2(50),
caddressVARCHAR2(20),
ctelVARCHAR2(20)
);

CREATE TABLE student(
stunoVARCHAR2(50) PRIMARY KEY,
stunameVARCHAR2(50),
stuage NUMBER,
cid NUMBER(4),
建立主-外键关联
);
以上是很明确的1对多的关系

其它补充：
Oracle数据库的创建有两种方式：
一种是使用CREATE DATABASE命令；
一种是使用DBCA (Database Configuration Assistant)，它是基于图形界面创建的，比较简单方便。而且，使用DBCA可以详细地对将要创建的数据库进行详细的配置。
数据库启动和关闭的几种方式
数据库的启动（STARTUP）
启动一个数据库需要三个步骤：
1、 创建一个Oracle实例（非安装阶段）
2、 由实例安装数据库（安装阶段）
3、 打开数据库（打开阶段）
在Startup命令中，能够通过不同的选项来控制数据库的不同启动步骤。
1、STARTUPNOMOUNT
NONOUNT选项仅仅创建一个Oracle实例。
2、STARTUPMOUNT
该命令创建实例并且安装数据库，但没有打开数据库。

3、STARTUP
该命令完成创建实例、安装实例和打开数据库的任何三个步骤。
假如采用STARTUP NOMOUNT或是STARTUP MOUNT的数据库打开命令方式，必须采用ALTER DATABASE命令来执行打开数据库的操作。例如，假如您以STARTUP NOMOUNT方式打开数据库，也就是说实例已创建，但是数据库没有安装和打开。这是必须运行下面的两条命令，数据库才能正确启动。
ALTER DATABASE MOUNT;
ALTER DATABASE OPEN;
而假如以STARTUP MOUNT方式启动数据库，只需要运行下面一条命令即能够打开数据库：
ALTER DATABASE OPEN.
数据库的关闭（SHUTDOWN）
1、SHUTDOWNNORMAL
这是数据库关闭SHUTDOWN命令的确省选项。
2、SHUTDOWNIMMEDIATE
这是我们常用的一种关闭数据库的方式，想很快地关闭数据库
3、SHUTDOWNTRANSACTIONAL
该选项仅在Oracle 8i后才能够使用。该命令常用来计划关闭数据库
4、SHUTDOWNABORT
数据库处于一种非正常工作状态，需要立即关闭数据库；在启动数据库实例时碰到问题


Oracle数据库体系结构
Oracle服务器由 Oracle 实例和 Oracle 数据库组成，如图所示：
简易表示为下图所示：

每一个Oracle 数据库都关联着一个 Oracle 实例。在数据库服务器上启动数据库后，Oracle 软件会分配一个称为系统全局区(SGA) 的共享内存区，还会启动若干个 Oracle 后台进程。这种 SGA 和Oracle 进程的组合就称为一个 Oracle 实例。
实例启动后，Oracle 软件会将实例与特定的数据库关联起来。这个过程称为装载数据库。接下来就可以打开数据库了。在同一台Oracle服务器上可以并发执行多个实例，每一个实 例只访问自己的物理数据库。Oracle 数据库使用内存结构和进程来管理、访问数据库。所有内存结构都存在于构成数据库服务器的计算机的主存中。

卸载Oracle

　在卸载Oracle时删不干净，搞的要重装系统，总结出如下方法。

　　1.以Administrators group身份登陆windows系统。

　　2.通过Oracle installer 卸载所有orcle产品及组件。

　　3.停掉Oracle Service服务。

　　4.删除%ORACLE_base%文件和SYSTEM_DRIVE:programfiles下的oracle文件。

　　5.删除SYSTEM_DRIVE:Documents and SettingsAll Users「开始」菜单程序中的oracle项。

　　6.打开注册表：

　　到HKEY_CLASSES_ROOT下，删除以Oracle，ORA，ORCL开头的键；

　　到HKEY_LOCAL_MACHINESOFTWARE下，删除ORACLE键和Apache Group键；

　　到HKEY_LOCAL_MACHINESYSTEMCurrentControlSetServices下，删除以Oracle开头的键；

　　到HKEY_LOCAL_MACHINESYSTEMCurrentControlSetServicesEventlogApplication下，删除以Oracle开头的键；

　　到HKEY_CURRENT_USER下，删除Oracle键；

　　到HKEY_CURRENT_USERSOFTWAREORACLE下，删除以Oracle 或 Orcl开头的键（如果有的话）；关闭注册表，重启计算机。

　　7.打开系统系统环境变量（我的电脑－>右键－>系统属性－>高级－>环境变量），在PATH中删除所有以%ORACLE_HOME%开始的项。

　　8.重启计算机。

Oracle listener错误解决方法

Oracle listener错误，可以把安装目录下的listener.ora打开察看。如： ..\db1\network\admin\listener.ora。
打开该配置文件后发现如下一段配置信息：
LISTENER =
(DESCRipTION_LIST=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(ADDRESS_LIST =
(ADDRESS =(PROTOCOL = TCP)(HOST = IBMThinkPad)(PORT = 1521))
)
)
)

当看到这一行HOST=IBMThinkPad的时候，问题解决了。原来是由于几天装好Oracle后，又改了Windows的计算机名，而这个 配置文件中记录的监听主机名还是原来的名称，这也就怪不得 TNSListener服务无法启动了，马上修改为现在的计算 机名，再次启动OracleTNSListener服务成功，PL/SQL Developer连接Oracle数据库成功，不能登陆故障排除。

存笔记