Oracle学习笔记

本文是在网易云课堂学习李兴华老师的Oracle教程过程中写的笔记，李老师操作熟练，讲解生动，而且在解释一些注意事项时会讲一些案例，还是比较容易理解的，课程地址

https://study.163.com/course/courseMain.htm?courseId=1509008

原版笔记地址：

https://pan.baidu.com/s/1OW7XBUUSsITkwCaPGKaPvw 密码：y205

以下为笔记内容，如有错误还望留言讨论，

网易云课堂李兴华

〇、安装&卸载

1、Oracle首先询问用户是否接受更新信息，一般选择不接受；

Oracle安装选项，需为其配置数据库，所以此处会询问是否创建安装数据库，选择是

系统类型，一般选择服务器类型；

网络安装选项，本次不牵涉Oracle的RAC管理（AIX+Oracl配置），选择单实例即可；

安装类型，选择高级安装配置（为了能方便进行配置）；

数据库版本，选择企业版本（最全）；

安装目录选择；

数据库名称定义为“mldn”

配置选择中，将字符编码设置为UTF-8,若此处没有设置，出现中文时又能会乱码，随后在示例方案，创建所有样本数据；

直到方案口令，为方面管理可将用户/密码统一设为“oracleadmin”(不符合oracle标准)；

直接到先决条件检查，如果检查出错，直接选择忽略即可；

随后启动oracle的安装程序；

本次会自动实现数据库的创建，但每一个数据库需我们额外配置，随后使用口令管理程序，主要操作以下用户（设置经典口令）

·超级管理员：sys/change_on_install；

·普通管理员：system/manager；

·普通用户：scott/tiger，需解锁；

·大数据用户：（样本数据库才有）：sh/sh，需解锁；

这时，oracle软件安装完成，数据库也安装完成。

但，安装完成后oracle相关服务自动设置为自动启动，建议设为手动启动，每次启动时只需启动两个服务即可：

·oracleoradb11_home1TNSListener:是监听服务

·oracleServiceMLDN：数据库的实例服务，他的命名标准：oracleServiceSID：（默认下SID名称与数据库名称一致，网络上使用的是SID的名称，每当新增数据库时，会自动增加该服务）

2、Oracle卸载，卸载后可能无法立即安装成功

Oracle正常安装完成的卸载：

·使用Oracle提供的卸载程序（Universal Installer）

·重启电脑进入安全模式（启动时按住F8）

·找到系统安装oracle对应的磁盘路径，并且删除（安全模式）；

·运行注册表命令“regedit.exe”，搜索所有与oracle有关内容并删除；

·重启电脑就可以重新安装；

Oracle安装失败原因：

·重启电脑进入安全模式（启动时按住F8）

·找到系统安装oracle对应的磁盘路径，并且删除（安全模式）；

·运行注册表命令“regedit.exe”，搜索所有与oracle有关内容并删除；

·重启电脑就可以重新安装；

Oracle的两个服务：

·如果通过程序链接，必须启动监听服务；

·所有的数据都保存在实例服务之中。

一、Oracle准备知识

1、启动&登录

cmd

sqlplus scott/tiger

2、窗口格式化(每次连接到数据库都需要重新设置)

①窗口加宽

SET LINESIZE 300;

②增加页面显示行数

SET PAGESIZE 30;

3、打开本地编辑器

ed t

会启动本地编辑器，默认创建t.sql文件，需要执行该文件中的命令时，@t.sql

4、切换用户名

CONN username/password [AS SYSDBA]

CONN sys/change_on_install AS SYSDBA

5、HOST开头调用系统命令

HOST echo hello world

二、sql和数据表分析

scott表结构

当前用户下所有表：SELECT * FROM tab;

查询一个表的结构：DESC 表名;

NUMBER(7,2);表示小数最多占2位，整数部分最多5位，

COL 列名 FOR A10；10为为列指定宽度，

三、基本查询

1、SELECT [DISTINCT] * | 列名称 [别名]，列名称 [别名]... FROM 表名[别名]；

2、表中的常量设置：

①如果是字符串，则使用“'”声明，例如'hello'

②如果是数字，则直接编写，如10；

③如果是日期，则应按照日期风格格式编写“xx日-xx月-xx年”

3、可以使用双||拼接字符串结果，

四、限定查询

1、关键运算符：

①关系运算符：>，<，>=，<=，<>，!=

②逻辑运算符：AND、OR、NOT

③范围运算符：BETWEEN。。。AND

④谓词范围：IN、NOT IN

⑤空判断：IS NULL，IS NOT NULL NOT IN 不能和NULL同时出现，

⑥模糊查询：LIKE

“_”匹配一个字符，“%”匹配任意个字符，

0&NULL：卖0元和无价是不同的概念，

五、序列

1、Order BY

③SELECT *

①FROM T

②WHERE 过滤条件

④ORDER BY 字段【ASC|DESC】，字段【ASC|DESC】，字段【ASC|DESC】，

六、单行函数

dual是验证查询的虚拟表，

1、字符串函数

UPPER（），LOWER（），INITCAP（），LENGTH（），SUBSTR（），REPALCE（），

2、数值函数，

ROUND（列|数字【，小数位】），TRUNC（列|数字【，小数位】），MOD（列|数字，列|数字），

3、日期函数

4、转换函数

TO_CHAR（列|日期|数字，格式），

数字，9表示任意数字：，L表示本地货币符号，

TO_DATE(列|字符串，转换格式)，TO_MEMBER（列|字符串，）

5、通用函数，

数字 NVL（列|NULL，默认值）

数值类型 DECODE（列|字符串|数值，比较内容1，显示内容1，比较内容2，显示内容2，...【默认显示内容】）

七、多表查询

1、多表查询

一般多个消除笛卡尔积的条件都会使用AND连接，

2、表的连接

①内连接：等值连接，所有满足条件的数据都会显示，

SELECT e.empno, e.ename, e.job,d.deptno, d.dname, d.loc

FROM emp e,dept d

WHERE e.deptno = d.deptno;

此时部门编号为空的雇员的信息不会显示，

②外连接：左外连接，右外连接，全外连接，

a.左外连接

SELECT e.empno, e.ename, e.job,d.deptno, d.dname, d.loc

FROM emp e,dept d

WHERE e.deptno = d.deptno(+) ;

此时雇员信息全部显示，即，忽略部门编号限制，显示全部雇员的信息，

b.右外连接

SELECT e.empno, e.ename, e.job,d.deptno, d.dname, d.loc

FROM emp e,dept d

WHERE e.deptno(+) = d.deptno ;

忽略雇员的部门编号限制，显示全部部门的信息，

3、SQL-1999

SELECT [DISTINCT] * |列 [别名]

FROM 表名称1

① [CROSS JOIN 表名称2] 交叉连接：产生笛卡尔积

eg.SELECT * FROM emp CROSS JOIN dept;

② [NATURAL JOIN 表名称2] 自然连接：自动使用关联字段取消笛卡尔积，（一般关联字段为外键，通常以名称相同为主，即内连接）默认关联字段在第一列显示，不再显示重复列，eg.SELECT * FROM emp NATURAL JOIN dept;

③ [JOIN 表名称2 ON（条件）| USING（字段）]

USING是指定用哪些字段进行关联；

eg.SELECT * FROM emp JOIN dept USING(deptno);

ON子句是设置关联的条件，

eg.SELECT * FROM emp e JOIN salgrade s ON(e.sal BETWEEN s.losal AND s.hisal);

④ [LEFT | RIGHT | FULL OUTER JOIN 表名称2];

eg.SELECT * FROM emp LEFT OUTER JOIN dept USING(deptno);

SELECT * FROM emp RIGHT OUTER JOIN dept USING(deptno);

4、多表数据集合操作

语法：

【③确定要使用的数据列】SELECT [DSTINCT] *|列名 [别名],...|常量

【①确定要查找的数据来源】FROM

[【②针对于数据行的筛选】WHERE过滤条件]

[【④针对于返回结果进行排序】ORDER BY 字段 [ASC|DESC],...]

UNION|UNION ALL|INTSECT|MINUS

【③确定要使用的数据列】SELECT [DSTINCT] *|列名 [别名],...|常量

【①确定要查找的数据来源】FROM

[【②针对于数据行的筛选】WHERE过滤条件]

[【④针对于返回结果进行排序】ORDER BY 字段 [ASC|DESC],...]

...

①UNION 合并查询结果，不显示重复的结果，

SELECT empno, ename, job FROM emp WHERE deptno =10

UNION

SELECT empno, ename, job FROM emp;

②UNION ALL 合并所有查询结果并显示，

SELECT empno, ename, job FROM emp WHERE deptno =10

UNION ALL

SELECT empno, ename, job FROM emp;

③INTERSECT 交集查询

SELECT empno, ename, job FROM emp WHERE deptno =10

INTSECT

SELECT empno, ename, job FROM emp;

④MINUS 第一个查询的结果减去第二个查询的结果

SELECT empno, ename, job FROM emp

INTSECT

SELECT empno, ename, job FROM emp WHERE deptno =10;

八、分组统计查询

1、统计函数

①统计个数：COUNT(* |[DISTINCT] 字段),

COUNT(*):查询表的所有行数

COUNT(字段):查询该字段不为空的行数

COUNT(DISTINCT 字段):查询字段去重后的行数

②MAX(字段),MIN(字段),

eg.

SELECT MAX(sal), min(sal) FROM emp;

SELECT MAX(hiredate), MIN(hiredate) FROM emp;

③SUM(), AVG(),

eg.

SELECT SUM(sal), AVG(sal) FROM emp;

SELECT AVG(MONTHS_BETWEEN(SYSDATE, hiredate)/12) FROM emp;

2、分组统计查询部分数据具有共性

【④确定要使用的数据列】SELECT [DSTINCT] 分组字段 [别名],...|统计函数

【①确定要查找的数据来源】FROM

[【②针对于数据行的筛选】WHERE过滤条件]

[【③针对于数据实现分组】GROUP BY 分组字段，分组字段...]

[【④针对于返回结果进行排序】ORDER BY 字段 [ASC|DESC],...]

eg.按照职位分组，统计各个职位的名称，人数，平均工资，

SELECT JOB, COUNT(empno), AVG(sal)

FROM emp

GROUP BY JOB;

eg.查询每个部门编号，各部门人数，最高与最低工资，

SELECT deptno, COUNT(*) 人数, MAX(sal), MIN(sal)

FROM emp

GROUP BY deptno;

使用限制一：

在没有编写GROUP BY子句时，全表作为一组，则SELECT子句中只允许出现统计函数或查询字段，不允许统计函数与字段同时出现，

使用限制二：

在使用GROUP BY子句分组的时候，SELECT子句中只允许出现分组字段和统计函数，

使用限制三：

统计函数允许嵌套查询，但嵌套后的统计查询中，SELECT子句中不允许出现任何字段，包括分组字段，只能使用嵌套的统计函数，

eg.

ACCEPT，

SELECT deptno, AVG(sal)

FROM emp

GROUP BY deptno;

REJECT，

SELECT MAX(AVG(sal))

FROM emp

GROUP BY deptno;

多表分组查询：

查询每个部门的名称，人数，平均工资

SELECT d.dname, COUNT(*), AVG(e.sal)

FROM emp e,dept d

WHERE e.deptno(+) = d.deptno

GROUP BY d.dname;

【⑤确定要使用的数据列】SELECT [DSTINCT] 分组字段 [别名],...|统计函数

【①确定要查找的数据来源】FROM

[【②针对于数据行的筛选】WHERE过滤条件]

[【③针对于数据实现分组】GROUP BY 分组字段，分组字段...]

[【④针对于分组后的数据进行筛选】HAVING分组后的过滤条件]

[【⑥针对于返回结果进行排序】ORDER BY 字段 [ASC|DESC],...]

查询平均工资高于2000的职位的平均工资，

SELECT job, AVG(sal)

FROM emp e

GROUP BY e.job

HAVING AVG(sal)>2000;

HAVING在GROUP BY之后，允许使用统计函数；WHERE不允许使用统计函数，

九、子查询

1、清除表数据，添加新数据，提交

truncate table digit1;

insert into

commit;

1、子查询

子查询位置：

①WHERE子句：子查询一般会返回单行单列，单行多列，多行单列

SELECT *

FROM emp

WHERE sal<(

SELECT AVG(sal)

FROM emp

);

B.查找最早雇佣员工的信息，

SELECT MIN(hiredate) FROM emp;

SELECT *

FROM emp

WHERE hiredate=(

SELECT MIN(hiredate)

FROM emp

);

C.查找与WARD从事同一工作且工资相同的人，

SELECT *

FROM emp

WHERE (job,sal)=(

SELECT job,sal

FROM emp

WHERE ename='WARD')

AND ename<>'WARD';

D.IN 操作

SELECT *

FROM emp

WHERE sal IN(

SELECT sal

FROM emp

WHERE job='MANAGER');

注：IN集合中不能有NULL，否则返回结果为空，

②HAVING子句：子查询会返回单行单列，同时表示要使用统计函数，

A.查找高于平均工资的职位，人数和平均工资，

SELECT job, COUNT(empno), AVG(sal)

FROM emp

GROUP BY job

HAVING AVG(sal)>(

SELECT AVG(sal)

FROM emp

);

③FROM子句：子查询返回多行多列数据（表结构），

A.查找部门名称，地址，人数，

SELECT d.dname, d.loc, temp.count

FROM dept d,(

SELECT deptno,COUNT(empno) count

FROM emp

GROUP BY deptno ) temp

WHERE d.deptno=temp.deptno(+);

④SELECT子句：返回单行单列，一般不使用，

查找雇员的雇员编号，姓名，职位，部门名称，

SELECT e.empno, e.ename, e.job,

(SELECT d.dname FROM dept d WHERE d.deptno=e.deptno)

FROM emp e;

2、复杂查询

①列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金，部门名称，部门人数

SELECT e.ename, e.sal, d.dname, c.count

FROM emp e,dept d, (

SELECT deptno, COUNT(empno) count

FROM emp

GROUP BY deptno) c

WHERE sal>(

SELECT MAX(t.sal)

FROM emp t

WHERE t.DEPTNO='30'

) AND d.deptno=e.deptno

AND d.deptno=c.deptno;

②列出所有与SMITH从事相同工作的所有员工及部门名称，部门人数，领导姓名

SELECT e.ename, d.dname, t.count, m.ename

FROM emp e, dept d, (

SELECT d.dname dname, COUNT(*) count

FROM emp e,dept d

WHERE e.deptno=d.deptno

GROUP BY d.dname) t, emp m

WHERE e.job=( SELECT job

FROM emp

WHERE ename='SMITH')

AND d.deptno=e.deptno

AND d.dname=t.dname

AND e.mgr=m.empno;

③列出薪金比SMITH或ALLEN多的所有员工的编号，姓名，部门名称，其领导姓名，部门人数，平均工资，最高及最低工资，

SELECT e.empno, e.ename, d.dname, m.ename, t.count, t.avg, t.max, t.min

FROM emp e, dept d, emp m,(

SELECT deptno, COUNT(empno) count, AVG(sal) avg, MAX(sal) max, MIN(sal) min

FROM emp

GROUP BY deptno

WHERE e.sal> (

SELECT MIN(sal)

FROM emp e

WHERE e.ename IN ('SMITH', 'ALLEN'))

AND e.ename NOT IN('SMITH', 'ALLEN')

AND e.deptno=d.deptno

AND e.mgr=m.empno(+)

AND t.deptno=e.deptno;

④列出受雇日期早于其直接上级的所有员工的编号，姓名，部门名称，部门位置，部门人数

SELECT e.empno, e.ename, d.dname, d.loc, t.count

FROM emp e,emp m, dept d,(

SELECT deptno, COUNT(empno) count

FROM emp

GROUP BY deptno) t

WHERE e.hiredate<m.hiredate

AND e.mgr=m.empno（+）

AND e.deptno=d.deptno

AND e.deptno=t.deptno;

⑤列出所有CLERK的姓名，部门名称，部门人数，工资等级，

SELECT e.ename,d.dname, t.count, s.grade

FROM emp e,dept d,(

SELECT deptno, COUNT(empno) count

FROM emp

GROUP BY deptno) t,salgrade s

WHERE e.job='CLERK'

AND e.deptno=d.deptno

AND e.deptno=t.deptno

AND e.sal BETWEEN s.losal AND s.hisal;

十、数据更新与事务处理

1、新增数据

复制表：CREATE TABLE myemp AS SELECT * FROM emp;（仅适用于Oracle）

INSERT INTO 表名称[(列名称1，列名称2,...)]VALUES(值1，值2...);

字符串：使用“'”声明，例如'abc';

数字：直接写

日期：当前日期（SYSDATE）、使用TO_DATE()转换，按照日期格式书写，

INSERT INTO myemp(empno, sal,job,comm,ename,mgr,hiredate,deptno)

VALUES(8888,9000.0, '清洁工', 10.0,'张三',7369,TO_DATE('1979-10-10','yyyy-mm-dd'),10);

【应使用带列名称代码操作数据库，必须WHERE子句】

2、数据修改

UPDATE 表名称 SET 字段1=值，字段2=值...[WHERE更新条件(s)];

WHERE 中使用IN ,BETWEEN,LIKE,

①UPDATE myemp SET sal=8000, comm=9000 WHERE ename='SMITH';

②UPDATE myemp SET sal=(

SELECT sal FROM emp WHERE ename='KING')

WHERE ename='ALLEN';

③

UPDATE myemp

SET sal=sal*1.2

WHERE sal<(

SELECT AVG(sal)

FROM emp);

3、数据删除

DELETE FROM 表名称 [WHERE 删除条件(s)];

①删除高于平均工资的员工

DELETE FROM myemp WHERE sal>(

SELECT AVG(sal) FROM myemp);

4、事务处理

commit:事务提交

rollback：事务回滚

八、数据伪列

1、数据伪列

①ROWNUM：

A.取出第一行记录

WHERE ROWNUM=1;

B.取出前N行记录

分页操作：

SELECT *

FROM(

SELECT 列,...ROWNUM rn

FROM 表名称，表名称

WHERE ROWNUM<=(currentPage*lineSize)

ORDER BY 字段...

) temp

WHERE temp.rn>((currentPage-1)*lineSize));

②ROWID：数据存在的具体物理行

ROWID:AAASOKAAEAAAIbLAAA

数据对象编号：AAASOK

数据保存文件编号：AAE

数据保存的块号：AAAIbL

数据的保存行号：AAA

十一、表的创建与管理

1、常用数据类型

No	类型	作用
1	VARCHAR(n)	字符串，n为最大长度，建议200字以内，
2	NUMBER(n,m)	NUMBER(n):整数数据，不超过n,INT代替， NUMBER(n,m):小数位占m位，整数位占n-m位，FLOAD代替，
3	DATE	保存日期时间数据
4	CLOB	大文本数据，可达4G，
5	BLOB	二进制数据，可达4G

2、表的创建

CREATE TBALE 表名称(

列名称类型 [DEFAULT 默认值],

列名称类型 [DEFAULT 默认值]

);

①创建成员信息表

CREATE TABLE member(

mid NUMBER,

name VARCHAR2(50) DEFAULT '无名氏',

age NUMBER(3),

birthday DATE DEFAULT SYSDATE,

note CLOB);

新增数据

INSERT INTO member(mid, name, age, birthday, note)

VALUES(1,'张三',30,TO_DATE('1985-11-11','yyyy-mm-dd'),'呀~');

3、表的重命名

Oracle对象的数据字典：

A.用户级别：user_*开头，用户可使用

B.管理员级别：dba_*开头，管理员可用

C.全部级别：all_*开头，用户和管理员都可用

①查询用户全部数据表

SELECT * FROM user_tables;

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

发生DDL操作，所有数据将直接自动提交，比如表的重命名

查看当前用户的表，

SELECT * FROM TAB;

4、截断表

清空表数据，约束，索引等，

TRANCATE TABLE 表名称;

5、复制表

CREATE TABLE 表名称 AS 子查询;

6、表的删除

DROP TABLE 表名称;

7、闪回技术-回收站机制

查看回收站

SHOW RECYCLEBIN;

或者，

COL original_name FOR A30;

COL object_name FOR A30;

COL droptime FOR A 30;

SELECT original_name, object_name, droptime FROM user_recyclebin;

表的恢复

将表恢复到删除之前的状态，

FLASHBACK TABLE deptstatus TO BEFORE DROP;

不经过回收站的强制删除

DROP TABLE deptstatus PURGE;

从回收站清理数据表

PURGE TABLE emp10;

清空回收站

PURGE RECYCLEBIN;

8、修改表结构

数据库脚本流程

删除表-创建表-测试数据-事务提交

-- 删除表

DROP TABLE member PURGE;

--创建表

CREATE TABLE member(

mid NUMBER,

name VARCHAR2(50)

);

--测试数据

INSERT INTO member(mid,name) VALUES(10,'张三');

INSERT INTO member(mid,name) VALUES(20,'李四');

--事务提交

COMMIT;

①增加表中的数据列

ALTER TABLE 表名称 ADD(

列名称类型[ DEFAULT 默认值],

列名称类型[ DEFAULT 默认值],...

);

②修改列

ALTER TABLE 表名称 MODIFY(

列名称类型[ DEFAULT 默认值],

列名称类型[ DEFAULT 默认值],...

);

ALTER TABLE member MODIFY (name VARCHAR2(20) DEFAULT '无名');

【更新表不建议设置默认值】

③删除列

ALTER TABLE 表名称 DROP COLUMN sex;

对象操作：

创建对象：CREATE 对象类型对象名称 ...；

删除对象：DROP 对象类型对象名称 ...；

修改对象：ALTER 对象类型对象名称 ...；

十二、约束的创建与管理

1、非空约束

CREATE TABLE member(

mid NUMBER,

name VARCHAR2(50) NOT NULL

);

2、唯一约束

CREATE TABLE member(

mid NUMBER,

name VARCHAR2(50) NOT NULL,

email VARCHAR2(50) UNIQUE

);

唯一约束仅对非空数据进行唯一约束，

查看约束对象，所在表

SELECT owner, constraint_name, table_name FROM user_constraints;

查看约束对象，所在列

SELECT * FROM user_cons_columns;

为约束添加名称，

CREATE TABLE member(

mid NUMBER,

name VARCHAR2(50) NOT NULL,

email VARCHAR2(50),

CONSTRAINT uk_email UNIQUE(email)

);

3、主键约束

CREATE TABLE member(

mid NUMBER,

name VARCHAR2(50) ，

CONSTRAINT pk_mid_name PRIMARYKEY(mid)

);

复合主键

CREATE TABLE member(

mid NUMBER,

name VARCHAR2(50) ，

CONSTRAINT pk_mid_name PRIMARYKEY(mid, name)

);

4、检查约束不建议使用，影响更新速度，

CREATE TABLE member(

mid NUMBER,

name VARCHAR2(50) ，

CONSTRAINT ck_age CHECK(age BETWEEN 0 AND 250)

);

5、外键约束

DROP TABLE member PURGE;

CREATE TABLE member(

mid NUMBER,

name VARCHAER2(20) NOT NULL,

CONSTRAINT pk_mid PRIMARYKEY(mid)

);

CREATE TABLE book(

bid NUMBER,

title VARCHAR2(20),

mid NUMBER,

CONSTRAINT pk_bid PRIMARY KEY(bid),

CONSTRAINT fk_mid FOREIGN KEY(mid) REFERENCE member(mid)

);

使用外键约束对表对象和表数据存在的限制：

①如果表中存在有外键关系，在删除父表之前，一定要先删除子表，

可以强制删除

DROP TABLE member CASCADE CONSTRAINT;

②父表中作为子表关联的外键字段，必须设置为主键约束或唯一约束，

③默认情况下，如果父表记录中有对应的子表记录，那么父表记录无法被删除。

如果想在删除父表数据时，子表数据随之自动删除，需要配置级联删除，

a.数据的级联删除：ON DELETE CASCADE;

CREATE TABLE book(

bid NUMBER,

title VARCHAR2(20),

mid NUMBER,

CONSTRAINT pk_bid PRIMARY KEY(bid),

CONSTRAINT fk_mid FOREIGN KEY(mid) REFERENCE member(mid) ON DELETE CASCADE

);

b.级联更新：ON DELETE SET NULL;当主表数据删除时，子表关联的数据相关字段设置为空，

CREATE TABLE book(

bid NUMBER,

title VARCHAR2(20),

mid NUMBER,

CONSTRAINT pk_bid PRIMARY KEY(bid),

CONSTRAINT fk_mid FOREIGN KEY(mid) REFERENCE member(mid) ON DELETE SET NULL

);

6、约束修改【不建议使用】

①为表增加约束

ALTER TABLE 表名称 ADD CONSTRAINT 约束名称约束类型(字段) [选项];

该语法只适用于检查、唯一、主键、外键四种约束，不适用于非空约束，

非空约束的增加，通过修改表结构方式新增，

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

②为表删除约束

ALTER TABLE 表名称 DROP CONSTRAINT 约束的对象名称；

ALTER TABLE member DROP CONSTRAINT pk_mid;

ALTER TABLE member DROP CONSTRAINT SYS_C……;

7、综合实战：DML&DDL（建表，增加数据）

--删表

DROP TABLE purcase PURGE;

DROP TABLE product PURGE;

DROP TABLE customer PURGE;

--建表

--1、创建顾客表

CREATE TABLE customer(

customerid VARCHAR2(3),

name VARCHAR2(20) NOT NULL,

location VARCHAR2(50),

CONSTRAINT pk_customerid PRIMARY KEY(customerid)

);

--2、创建商品表

CREATE TABLE product(

productid VARCHAR2(3),

productname VARCHAR2(20) NOT NULL,

unitprice NUMBER,

category VARCHAR2(20),

provider VARCHAR2(20),

CONSTRAINT pk_productid PRIMARY KEY(productid),

CONSTRAINT ck_unitprice CHECK (unitprice>0)

);

--3、创建购买记录表

CREATE TABLE purcase(

customerid VARCHAR2(3),

productid VARCHAR2(3),

quantity NUMBER,

CONSTRAINT fk_customerid FOREIGN KEY(customerid) REFERENCES customer(customerid) ON DELETE CASCADE,

CONSTRAINT fk_productid FOREIGN KEY(productid) REFERENCES product(productid) ON DELETE CASCADE,

CONSTRAINT ck_quantity CHECK(quantity BETWEEN 0 AND 20)

);

--测试

--1 插入商品数据

INSERT INTO product(productid, productname, unitprice, category, provider) VALUES('M01', '佳洁士', 8.00, '牙膏', '宝洁');

INSERT INTO product(productid, productname, unitprice, category, provider) VALUES('M02', '高露洁', 6.50, '牙膏', '高露洁');

INSERT INTO product(productid, productname, unitprice, category, provider) VALUES('M03', '洁诺', 5.00, '牙膏', '联合利华');

INSERT INTO product(productid, productname, unitprice, category, provider) VALUES('M04', '舒肤佳', 3.00, '香皂', '宝洁');

INSERT INTO product(productid, productname, unitprice, category, provider) VALUES('M05', '夏士莲', 5.00, '香皂', '联合利华');

INSERT INTO product(productid, productname, unitprice, category, provider) VALUES('M06', '雕牌', 2.50, '洗衣粉', '纳爱斯');

INSERT INTO product(productid, productname, unitprice, category, provider) VALUES('M07', '中华', 3.50, '牙膏', '联合利华');

INSERT INTO product(productid, productname, unitprice, category, provider) VALUES('M08', '汰渍', 3.00, '洗衣粉', '宝洁');

INSERT INTO product(productid, productname, unitprice, category, provider) VALUES('M09', '碧浪', 4.00, '洗衣粉', '宝洁');

--2 增加顾客数据

INSERT INTO customer(customerid, name, location) VALUES('C01', 'Dennis', '海淀');

INSERT INTO customer(customerid, name, location) VALUES('C02', 'John', '朝阳');

INSERT INTO customer(customerid, name, location) VALUES('C03', 'Tom', '东城');

INSERT INTO customer(customerid, name, location) VALUES('C04', 'Jenny', '东城');

INSERT INTO customer(customerid, name, location) VALUES('C05', 'Rick', '西城');

--3 增加购买记录信息

INSERT INTO purcase(customerid, productid, quantity) VALUES('C01', 'M01', 3);

INSERT INTO purcase(customerid, productid, quantity) VALUES('C01', 'M05', 2);

INSERT INTO purcase(customerid, productid, quantity) VALUES('C01', 'M08', 2);

INSERT INTO purcase(customerid, productid, quantity) VALUES('C02', 'M02', 5);

INSERT INTO purcase(customerid, productid, quantity) VALUES('C02', 'M06', 4);

INSERT INTO purcase(customerid, productid, quantity) VALUES('C03', 'M01', 1);

INSERT INTO purcase(customerid, productid, quantity) VALUES('C03', 'M05', 1);

INSERT INTO purcase(customerid, productid, quantity) VALUES('C03', 'M06', 3);

INSERT INTO purcase(customerid, productid, quantity) VALUES('C03', 'M08', 1);

INSERT INTO purcase(customerid, productid, quantity) VALUES('C04', 'M03', 7);

INSERT INTO purcase(customerid, productid, quantity) VALUES('C04', 'M04', 3);

INSERT INTO purcase(customerid, productid, quantity) VALUES('C05', 'M06', 2);

INSERT INTO purcase(customerid, productid, quantity) VALUES('C05', 'M07', 8);

--事务提交

COMMIT;

①购买供应商宝洁产品的所有顾客

SELECT DISTINCT c.customerid, c.name

FROM customer c, product p, purcase pc

WHERE p.provider='宝洁'

AND p.productid=pc.productid

AND pc.customerid=c.customerid;

②求购买的商品包含了顾客Dennis所购买的所有商品的顾客

SELECT * FROM customer ca

WHERE NOT EXISTS((

SELECT p1.productid

FROM purcase p1

WHERE p1.customerid=(

SELECT customerid

FROM customer

WHERE name='Dennis'

)

MINUS(

SELECT p2.productid

FROM purcase p2

WHERE p2.customerid=ca.customerid

)

)AND ca.name<>'Dennis';

③求牙膏卖出数量最多的供养商

SELECT p.provider, SUM(pc.quantity) sum

FROM product p,purcase pc

WHERE p.productid=pc.productid

AND p.category='牙膏'

GROUP BY p.provider

HAVING SUM(pc.quantity)=(

SELECT MAX(SUM(pc.quantity))

FROM product p,purcase pc

WHERE p.productid=pc.productid

AND p.category='牙膏'

GROUP BY p.provider)

;

④将所有牙膏商品单价增加10%

UPDATE product SET unitprice=unitprice*1.1 WHERE category='牙膏';

⑤删除从未被购买过的商品记录

DELETE FROM product

WHERE productid NOT IN(

SELECT productid FROM purcase);

十三、常用数据库对象

1、序列

CREATE SEQUENCE 序列名称

[INCREMENT BY 步长][START WITH 开始值]

[MAXVALUE 最大值| NOMAXVALUE]

[MINVALUE 最小值| NOMINVALUE]

[CYCLE | NOCYCLE]

[CACHE 缓存数据 | NOCACHE];

创建序列

CREATE SEQUENCE myseq;

查询序列

SELECT * FROM user_sequences;

使用序列

序列对象.nextval:表示进行序列的增长，每调用一次，序列上加指定的步长；

序列对象.currval：表示取得当前的序列内容，不管如何调用，序列内容不发生改变。

SELECT myseq.nextval FROM dual;

SELECT myseq.currval FROM dual;

INSERT INTO mytab(id, name) VALUES(myseq.nextval, 'aaa');

修改序列步长为2

DROP SEQUENCE myseq;

CREATE SEQUENCE myseq INCREMENT BY 2;

CREATE SEQUENCE myseq INCREMENT BY 2 START WITH 100000000000000;

2、同义词

别名的使用，类似dule

创建同义词

CREATE [PUBLIC] SYNOYM 同义词名字 FOR 用户名.表名;

3、视图

CREATE [OR REPLACE] VIEW 视图名称 AS 子查询;

查看视图信息

SELECT * FROM user_views;

CREATE OR RELPACE VIEW myview

SELECT * FROM emp WHERE deptno=20

WITH CHECH OPTION;

此时再更新视图时，遇到更新视图创建条件的语句会报错，但可以更新其他字段，

定义只读视图

CREATE OR RELPACE VIEW myview

SELECT * FROM emp WHERE deptno=20

WITH READ ONLY;

删除视图

DROP VIEW myview;

4、索引

sys用户，打开跟踪器

CONN sys/change_on_install AS SSYSDBA;

SET AUTOTRACE ON;

5、用户管理

创建用户

CREATE USER dog IDENTIFIED BY wangwang;

GRANT CREATE SESSION TO dog;

为用户授予角色

GRANT CONNECT, RESOURCE TO dog;

修改用户密码

ALTER USER dog IDENTIFIED BY miaomiao;

登录后立即修改密码

ALTER USER dog PASSWORD EXPIRE;

锁定账户

ALTER USER dog ACCOUNT LOCK;

解锁

ALTER USER dog ACCOUNT UNLOCK;

将scott用户操作对象权限授予其他用户

GRANT SELECT, INSERT ON scott.emp TO dog;

收回权限

RECOKE CONNECT, RESOURCE FROM dog;

删除用户

DROP USER dog CASCADE;

6、数据备份

①数据导入导出

A.导出

cmd到存放文件的位置，exp命令，用户导出，

B.导入

系统全局搜索scott用户，可以找到其脚本文件，直接复制相关语句在数据库执行可以恢复数据，

②数据库冷备份

需要备份:

控制文件：可以通过“v$controlfile”数据字典找到，

重做日志文件：通过“v$logfile”找到

数据文件（表空间）：“v$datafile”找到

核心配置文件（pfile）：SHOW PARAMTER pfile找到，

关闭数据库服务：SHUTDOWN IMMEDIATE;

重启数据库服务：STARTUP;

十四、数据库设计

根据业务需求尽量少的使用多表复杂查询，

1、第一范式

表中每个字段都不可再分，使用标准数据类型，

2、第二范式

多对多关系，关系分离，

3、第三范式

一对多关系，

4、PowerDesigner

posted @ 2018-12-13 14:56 十点阅读(558) 评论(0) 编辑收藏举报

刷新页面返回顶部

十点

Oracle学习笔记

公告