Oracle笔记2

24.复杂查询的三道题

# 案例一:列出与SCOTT从事相同工作的所有员工及部门名称、人数、平均工资
GROUP BY使用限制:
查询语句中没有GROUP BY,则SELECT子句中只能出现统计函数;
查询语句中有GROUP BY,则SELECT子句中能出现统计函数和分组字段;
统计函数允许嵌套,嵌套之后的SELECT子句不允许出现任何字段,包括分组字段
SELECT e.empno,e.ename,e.job,d.dname,temp.count,temp.avg
FROM emp e,dept d , (
	SELECT deptno dno,COUNT(empno) count,AVG(sal) avg
	FROM emp
	GROUP BY deptno) temp
WHERE e.job=(
	SELECT job FROM emp WHERE ename='SCOTT') 
	AND e.ename<>'SCOTT' 
	AND e.deptno=d.deptno 
	AND e.deptno=temp.dno;

# 案例二:列出薪金高于在部门30工作的所有员工的薪金以及
高于公司平均工资的员工姓名和薪金、部门名称、部门平均工资、个人工资等级
确定要使用的数据表:
emp表:员工姓名和薪金、部门平均工资
dept：部门名称
salgrade:工资等级

# 案列三:查询出所有雇员编号、姓名、职位、薪金、此职位的人数、
领导姓名、领导职位、领导所在部门的人数、雇员部门、雇员部门人数
领导部门、领导所在部门人数、雇员工资等级以及此等级的平均工资

25.数据更新操作

复制emp表到myemp
CREATE TABLE myemp AS SELECT * FROM emp ;
向已有的数据表中进行数据的增加
[推荐]完整的语法格式:
INSERT INTO 表名称(列名称,列名称,...) VALUES (内容1,内容2,...) ;
UPDATE 表名称 SET 字段1=内容1,字段2=内容2, ... [WHERE 更新条件(s)] ;
DELETE FROM 表名称 [WHERE 删除条件(s)] ;
实际项目中的删除操作,数据的删除分为两种:
物理删除:直接使用DELETE语句彻底删除;
逻辑删除:在表中设置一个逻辑位(设计的时候准备一个flag字段),逻辑位的内容如下:
flag=1(非0表示true):表示该数据的内容已经被删除了;
flag=0(0表示false):表示该数据的内容未被删除

26.事务

session(会话):在Oracle数据库之中,同一个数据库可以连接多次
事务处理的两个命令:
commit:所有的操作进行提交;
rollback:数据操作产生问题时需要进行回滚处理,
每个session都有一个更新缓冲区
a.定义两个session分别连接到数据库;
b.[Session-A]现在发出一个更新指,更新一条数据:
UPDATE emp SET sal=9999 WHERE empno=7369 ;
c.Session-B]由于第一个Session并没有提交或回滚事务,
所以这个时候第二个session是无法更新7369这行数据的:
d.[Session-A]执行rollback或commit表示释放掉当前锁住的数据行:rollback;
e.在进行锁定的时候除了更新之外也可以采用查询模式:
SELECT * FROM emp WHERE empno=7369 FOR UPDATE ;
当前事务释放之前,数据不允许修改

27.伪列

伪列就是不存在于表中的列,但是该列又可以直接使用,
三个常用伪列:SYSDATE、ROWNUM、ROWID
a.ROWNUM会自动做一个数据增长列,作用:
查询第一行记录;  查询前N行记录
数据分页显示处理:让表中的数据按照部分的模式来完成
SELECT * 
FROM (
	SELECT 查询字段,查询字段,..,ROWNUM rn FROM 表名
	WHHERE ROWNUM<=currentPage * lineSize) temp
WHERE temp.rn>(currentPage-1)*lineSize ;
案例:取11-15行数据
SELECT * FROM (
	SELECT empno,ename,job,ROWNUM rn
	FROM emp
	WHERE ROWNUM<=15) temp
WHERE temp.rn>10 ;

b.ROWID,每行数据都有一个唯一的ROWID
案例:现有一张表,但该表设计初期没有设计好,有大量完全重复数据,要求删除重复数据
重复的内容保留一条,保留最初增加的一条
DELETE FROM mydept 
WHERE ROWID NOT IN (
	SELECT MIN(ROWID)
	FROM mydept
	GROUP BY deptno,dname,loc) ;

28.数据表的创建与管理

常见的数据类型:
VARCHAR2(n):一般200个字以内都用它
NUMBER:直接编写表示的就是数字,NUMBER(n):表示最多n位整数
NUMBER(n,m):表示m个小数位,n-m位整数
DATE:描述日期时间,包含时间数据
CLOB:描述大文本数据(4G) BLOB:描述二进制数据
一般是通过程序确定"数据默认值"
CREATE TABLE 表名称 (
	列名称 数据类型 [DEFAULT 默认值] ,
	列名称 数据类型 [DEFAULT 默认值] ,
		...
	列名称 数据类型 [DEFAULT 默认值]
) ;

b.复制某表的数据和表结构,出现的几率不高
CREATE TABLE 表名称 AS 子查询 ;
CREATE TABLE emp20 AS SELECT * FROM emp WHERE deptno=20 ;
只复制表结构
CREATE TABLE empnull AS SELECT * FROM emp WHERE 1=2 ;

c.修改表名称
查看数据表保存的字典信息
SELECT * FROM user_tables ;
RENAME member TO person ;

d.表截断
所有资源将彻底释放,并且无法进行事务的回滚操作,一般不进行此操作
TRUNCATE TABLE person ;

e.删除表
DROP 对象类型 对象名称
DROP TABLE emp20;

f.闪回flashback
查看用户回收站
COL object_name FOR A30 ;
COL original_name FOR A30 ;
COL droptime FOR A30 ;
SELECT object_name,original_name,droptime FROM user_recyclebin ;
闪回某张表
FLASHBACK TABLE emp20 TO BEFORE DROP ;
不经过回收站,直接删除某张表
DROP TABLE emp20 PURGE ;
删除回收站的一张表
PURGE TABLE empnull ;
清空回收站
PURGE RECYCLEBIN ;

g.修改表结构,不要去修改,了解即可
一般是删了重新建
CREATE TABLE member(
	mid		NUMBER ,
	name 	VARCHAR2(20)
) ;
创建完成后,发现少了一个字段,现在需要新增一个字段
ALTER TABLE 表名称 ADD (列名称 数据类型 [DEFAULT 默认值],...)
没有设置默认值,则该字段为null
ALTER TABLE member ADD (age NUMBER(3)) ;
设置了默认值之后,所有数据的该字段都被设置为默认值
ALTER TABLE member ADD (sex VARCHAR2(10) DEFAULT '男') ;
不建议在增加字段时设置默认值

将NAME的长度改为50
ALTER TABLE 表名称 MODIFY (列名称 数据类型 [DEFAULT 默认值],...)
ALTER TABLE member MODIFY(name VARCHAR2(50)) ;

删除数据列
ALTER TABLE 表名称 DROP COLUMN 字段名称 ;
ALTER TABLE member DROP COLUMN age ;

29.约束的创建与管理

约束是保证数据完整性的一种手段,一类是[类型为NUMBER,就不能存放字符串]
除了这类约束之外还有五种
非空约束(NOT NULL、NK):某个字段不能为空
唯一约束(UNIQUE、UK):该列上的数据不允许出现重复
主键约束(PRIMARY KET、PK):非空约束+唯一约束
检查约束(CHECK、CK):设置一些判断条件,满足一些条件就允许修改
外键约束(FOREIGN KEY、FK):设置父子表之间的约束关系
表约束越多,数据库性能就越差,如果要保证高性能,唯一可以使用的是主键约束
null不在唯一约束的限定范畴之内
CREATE TABLE member(
	mid		VARCHAR2(50) ,
	name	VARCHAR2(50) NOT NULL ,
	email	VARCHAR2(50) ,
	CONSTRAINT uk_email UNIQUE(email)
) ;
这样设置表的好处:插数据时,如果违反了唯一约束,会自动抛出uk_email的约束名称
c.主键约束
情况一:追加的逐渐内容为null
情况二:设置的主键重复
某个字段显示时太长,则col 字段 for a20;

d.外键约束
一般是先删子表,再删父表,有一种删除叫不管不顾式:
DROP TABLE member CASCADE CONSTRAINT ;
在外键设置中,可以设置级联操作:
级联删除:可以直接删除父表中数据,子表数据随之被删除
CONSTRAINT fk_mid FOREIGN KEY(mid) REFERENCES member(mid) ON DELETE CASCADE
级联更新:父表中数据被删除了,子表中对应的外键字段设置为null
CONSTRAINT fk_mid FOREIGN KEY(mid) REFERENCES member(mid) ON DELETE SET NULL

e.修改约束
DROP TABLE member PURGE ;
CREATE TABLE member(
	mid		VARCHAR2(50) ,
	name	VARCHAR2(50) 
) ;
ALTER TABLE 表名称 ADD CONSTRAINT 约束名称 约束类型;
如果表中的数据有违反主键的情况出现,则该约束无法添加
ADD CONSTRAINT只能追加非空以外的约束,想要追加非空约束,得用MODIFY
ALTER TABLE member MODIFY(name VARCHAR2(50) NOT NULL) ;
表和约束要"同生共死",不能有表没约束,不能删约束不删表
ALTER TABLE member DROP CONSTRAINT pk_mid ;

30.数据表操作

多行单列用where子句

31.Oracle序列

Oracle 12C开始才真正追加了自动增长列的配置
CREATE SEQUENCE 序列名称 默认值:
起始值为1 步长为1 最大值:NOMAXVALUE 最小值为0
非循环序列:NOCYCLE 缓冲个数为20个:CACHE
序列名称nextval:获取下一个增长值
序列名称curval:获取序列当前内容
一定要先调用nextval才可以执行curval

32.同义词

为什么scott访问dual表的时候没有设置用户名.dual?
因为dual属于sys.dual的同义词,同义词的主要作用是方便不同用户的数据表访问,
想要创建同义词往往需要具备管理员的权限
示例:将scott.emp的数据表创建为emp的同义词
CREATE SYNONYM emp FOR scott.emp ;

33.视图

视图的主要作用是:封装复杂的查询语句,利用视图就可以实现复杂查询
范例:创建一个不允许修改创建字段的视图
CREATE OR REPLACE VIEW myview
	AS
SELECT * FROM emp WHERE deptno=20
WITH CHECK OPTION ;
此时发现其他字段仍可以更改,但是视图之中并不是真实的数据,建议追加
"WITH ONLY"的选项

34.索引

在数据库中创建索引有两种方式:
a.当数据列上使用了主键约束或唯一约束的时候自动创建索引
b.手动创建索引
CREATE INDEX scott.emp_sal_ind ON scott.emp(sal) ;

35.用户管理

oracle里提供了两个角色,每一个角色包含多个权限
授权完成之后如果想要使用,则要重新登录.
CREATE USER dog IDENTIFIED BY wangwang ;
GRANT connect,resource TO dog ;
不同用户之间要进行访问,就必须拥有其他用户的对象权限
GRANT SELECT,INSERT ON scott.emp TO dog ;
回收权限
REVOKE SELECT,INSERT ON scott.emp FROM dog ;
REVOKE connect,resource FROM dog ;
REVOKE CREATE SESSION FROM dog ;
DROP USER dog CASCADE ;

36.数据库冷备份

备份控制文件,控制整个Oracle的服务信息
SELECT * FROM v$controlfile ;
备份重做日志文件,通过"v$logfile"数据字典获得
SELECT * FROM v$logfile ;
数据文件:保存真实的数据信息,通过"v$datafile"数据字典获得
SELECT * FROM v$datafile ;
核心配置文件(pfile):整个Oracle核心参数
show parameter pfile
将之前记录好的路径文件都拷贝到其它的磁盘上
这种备份方式的前提是:允许你关闭服务

37.数据库设计范式

第一设计范式--设计要求:数据库中的每个字段不可再分,单表设计原则
第二设计范式:多对多关系
第三设计范式:一对多关系