oracle 学习笔记

1.给用户解锁
alter user scott account unlock;
上锁
alter user test account lock;

  distinct --消除相同的值

escape '$' --指定转义字符

1.设置SQLPLUS行能显示的数据
set linesize 300
2.设置每页能显示的数据行
set pagesize 行数
3.用文本框创建SQL语句表
  ed 文件名
  编辑完成后可通过@文件名执行
  也可直接执行磁盘上的文件 @路径 如果文件不是'*.SQL'则输入路径时需要输入后缀

// 登录用户，以什么身份登录
conn 用户名/密码 [AS SYSDBA|sysoper]

// 查询用户下的相应表
select * from scott.emp;

// 显示用户是什么用户
show user;

// 获得当前用户下的所有表
select * from tab;

// 查看表的详细结构
desc emp;

// 启动用户
alter user scott account unlock;

// 创建用户
create user 用户名 identified by 密码 default tablespace users quota 10M on users; 有错误。

// 修改用户密码
alter user 用户名 identified by 密码；

// 查找所有用户和密码
select username,password from dba_users;

// 给用户授权
grant 权限 to 用户

// 连接用户
conn 用户名/密码 [as sysdba/sysoper]

// 继续使用上一次的操作
/

SQL结构化查询语言
DML Data Manipulation Language 数据操作语言; --用于检索或者修改数据
DDL data definition language,数据定义语言;  --定义数据的结构，如创建，修改或删除数据库对象define
DCL data control language, 数据控制语言;  --用于定义数据库用户的权限   

// 消除重复列
select [distinct]*| 具体的列 别名 from 表名称

// ORACLE中的字符串连接操作，使用“||”，如果要加入一些现实信息的话，所有其他的固定信息要使用“'”括起来

SLQPLUS清屏命令
clea scre
clear screen
host cls

// ORACLE 不等于的两种形式!= <>

// ASC 升序 DESC降序

// 单行函数语法function_name(column|expression_r_r_r[arg1,arg2...])
//              函数名称      数据库列名  字符串或计算表达式  参数

每个数据库之间唯一不同的最大区别点就在于与函数的支持上，使用函数可以完成一系列的操作功能。

单行函数分类
 1.字符函数，
 2。数值函数
 3.日期函数：
 4.对日期数据进行操作
 5.转换函数
 6.通用函数

// 转换大写
select upper('smith') from dual;
// 小写
lower()

// 将单词的第一个字母转换为大写
initcap()

// 字符串处理可以使用“||”连接外，还可以使用concat('','')进行连接

字符串操作函数
1.内容替换：replace('hello','l','x')
2.内容长度：length('hello')
3.截取字符串长度： substr('hello',1,3)  在ORACLE中截取字符串从0开始和1开始效果是一样的。
  此函数还可以使用负数进行截取
     substr('hello',-3,3)

// 截取该字段的后三位
substr(要截取的字段，开始截取位，截取多少位) 截取多少位如果不写，那说明截取到末尾
select substr(ename,length(ename)-2) from emp;

数值函数主要是包括以下几种:
 1,四舍五入：round() round(要四舍五入的数,小数的位数) round(要四舍五入的数,负数（可以四舍五入整数）)
 2.截断小数位:trunc() round(要截取的数,小数的位数) round(要截取的数,负数（截取多少位整数）)
 3.取于（取模）：MoD() mod(除数，被除数)

// 日期函数

日期-数字=日期
日期+数字=日期
日期-日期=数字（天数）

// 求的当前日期
select sysdate from dual;
 months_between() 求出给定日期范围的月数
 add_months()  在指定日期上加上指定的月数，求出之后的日期
 next_day()  下一个的指定日期是那一天
 last_day()  求出给定日期的最后一天日期

转换函数
 TO_CHAR   转换成字符串 to_char(hiredate,'fmyyyy-mm-dd') 消除前导0的方法
    select empno,ename,to_char(sal,'99,999') from emp; 每隔三位加，号
 $:表示美元符号，L：表示local的缩写，以本地的语言进行金额的显示；
  select empno,ename,to_char(sal,'L99,999') from emp;
 TO_NUMBER  转换成数字  select to_number('1234')+to_number('5435') from dual;
 TO_DATE   转换成日期格式 select to_date('2009-02-16','yyyy-mm-dd') from dual;

通用函数
 NVL  可以将一个指定的NULL值变为指定的的内容
  select empno,ename,(sal+nvl(comm,0))*12 from emp;
 Decode() 类似于IF...ELSE IF...ELSE IF...ELSE...
 decode(col/expression_r_r_r,search1,result[search2,result2,...][default])
 select decode(5,1,'内容是1',2,'内容是2',3,'内容是3','哇哈哈。。。') from dual;
  select empno,ename,
   decode(job,
   'CLERK','业务员','MANAGER','经理',
   'ANALYS','分析员','PRESIDENT','总裁',
   '吃白饭的') from emp;
 

拿到多表查询（超过3个）的问题一定要先把问题给拆分

1.左右连接

（+）在=左边表示右连接
     在=右边表示左链接

了解即可
2.交叉连接 产生笛卡尔积 CROSS JONN

3.自然连接 自动进行关联字段的匹配 NATURAL JOIN

4.USING子句 直接指定关联的操作列。
select * from emp e join dept d using(deptno) where deptno=30;

5.ON子句，表示用户自己编写连接的条件
select * from emp e join dept d on(e.deptno=d.deptno) where e.deptno=30;

6.左右连接
left join right join
select * from emp e right join dept d on(e.deptno=d.deptno);

统计函数：
1.必须出现在分组统计之中，所谓的分组就是指列中存在重复的内容，使用GROUP BY 的方式进行分组统计。
2.如果要加入分组条件，则编写HAVING子句，所有统计函数作为条件的话只能在HAVING中出现，不能在WHERE中出现。
3.在使用分组的时候，查询的时候只能出现分组函数或是分组条件。
4.分组函数允许嵌套，但是嵌套之后就不能再查询其他字段了，包括组字段。
COUNT 求出全部记录数
MAX 求出一组中的最大值
MIN 求出最小值
AVG 求出平均值
SUM 求和

子查询在操作中有分为以下三类
 单列子查询
 单行子查询
 多行子查询
子查询可以出现在任意位置

查询 部门名称，部门的员工数，部门的平均工资，部门的最低收入雇员和最高收入雇员

select d.dname,ed.c CountNum,ed.a AvgSal,e.ename MinSalName,eOne.eName MaxSalName
from dept d,emp eOne,
(select deptno,count(empno) c,avg(sal) a,min(sal) min,max(sal) max from emp group by deptno) ed,emp e
where d.deptno=ed.deptno and e.sal=ed.min and eOne.sal=ed.max

子查询中，存在以下三种查询的操作符号
IN 指定一个查询的范围 

ANY
=ANY 与IN的作用完全一样
>ANY 大于最小的值
<ANY 小于最大的值

ALL
>ALL 大于查询的值的所有数据
<ALL 小于

多列子查询
select *from emp where
(sal,NVL(comm,-1)) IN (SELECT sal,NVL(comm,-1) from emp where deptno=20);

// 将一个旧表里的数据完全复制到一个新表里。
create table myemp as select * from emp;

// 插入
insert into myemp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values

('7999','zhoujian','manager','7782',sysdate,10000,9999,10)
// 删除
delete myemp where empno='7369'
// 更新
update myemp set empno='8999' where empno='7999'

// 创建表来自数据
create table emp10 as select * from emp where deptno=10
// 删除表
drop table emp10

Oracle 事务处理概念。
对于每一个连接到数据库的窗口，连接之后都会与数据库的链接建立一个SESSION，即每一个连接到数据库上的用户都表示创建了一个

SESSION
一个SESSION对数据库所做的修改，不会立刻反映到数据库的真实数据之上，是允许回滚的，当一个SESSION提交所有的操作之后，数据库才

会真正做出更改。

在数据库的操作中提供了以下的两个主要命令完成事务的处理；
提交 commit
回滚 rollback; 如果数据被提交了，是无法回滚的。

事务的处理也会存在一种死锁的概念
 一个SESSION如果更新了数据库中的记录，其他Session是无法立刻更新的，要等待对方提交之后才允许更新。

列出所有“CLERK”（办事员）的姓名及其部门名称，部门人数。
select e.ename,d.deptno,d.dname,dt.count
from emp e,dept d,(select deptno,count(deptno) count from emp group by deptno) dt where job='CLERK' and e.deptno=d.deptno

and e.deptno in dt.deptno

-- 查询最低工资大于1500 的工作，以及此工作的人数
select job,count(empno)
from emp
where job in(
      select job
      from emp
      group by job having min(sal)>1500)
group by job;

---------------数据类型---------------
VARCHAR,VARCHAR2 表示是一个字符串，有长度限制，为255
NUMBER NUMBER(N) 表示一个整数，数字的长度是N可以使用INT代替
 NUMBER(M,N) 表式一个小数，数字小数长度为n，整数长度为m-n可以使用DOUBLE代替
Date 表示日期类型
CLOB 大对象，表示大文本数据，一般可以存放4G的文本。
BLOB 大对象，表示二进制数据，最大可以存放4G，列如电影，歌曲，图片等。
---------------------------------------

---------------表的操作----------------

// 创建表
create table 表名（
字段1 类型 [default默认],
字段2 类型 [default默认]
。。。

）
// 复制表
create table 表名称 AS（子查询）
 // 如果子查询加入了一个不可能成立的条件，则此时表示的是只复制表结构，但是不复制表内容。列如1=2；

// 删除表
drop table person

// 修改表
 // 添加列
 alter table person add (Address varchar2(200) default '暂时无地址')
 // 修改列
 alter table person modify(name varchar(20) default 'xuanyuan')
  在修改表结构的时候，假设数据库中对应的字段里有一个很长的数据，则无法将表的长度缩小。 

// 修改表的名称
rename 表名to 别名 （只能在Oracle）

// 截断表
truncate table 表名
 在使用普通的SQL语句进行删除数据时，可以使用ROLLBACK进行回滚。现在假如想清空一个表但是同时又不需要回滚，需要立刻释放

资源就需要使用截断表的语法

---------------数据约束----------------
使用月素可以更好的保证数据库中数据的完整性
1.建立表的时候指定约束

create table 表名（
字段1 类型 [default默认] Primary key,
字段2 类型 [default默认]
。。。

）
主键列插入重复值将报出错误信息，错误信息上给出的是一个编号的形式，实际上此编号就表示约束的名称。

 也可以通过CONSTARINT
create table 表名（
字段1 类型 [default默认] not null,
字段2 类型 [default默认] UNIQUE,
字段3 类型 [default默认] CHECK(age between 0 and 150),
字段4 类型 [default默认] CHECKsex in ('男','女')),
。。。,
CONSTRAINT person_pid_pk primary key(pid),
CONSTRAINT person_name_uk unique(name),
CONSTRAINT person_age_ck CHECK(age Between 0 and 150),
CONSTRAINT person_sex_ck CHECKsex in ('男','女')),
）

// 外键约束
CONSTRAINT person_book_pid_fk Foreign key(pid) references person(pid)

// 可以使用强制性的删除手段，直接删除而不管约束
DROP TABLE book CASCADE CONSTRAINT

// 在主-外键关联中可以使用级联删除的情况（重）
 在外键建立的时候，在最后添加 ON DELETE CASCADE 即可以实现级联删除
CONSTRAINT person_book_pid_fk Foreign key(pid) references person(pid)  ON DELETE CASCADE

约束实例

// 不建议使用下1方式，会造成删除约束时麻烦（系统自动分配约束）
create table empTest(
id int Primary key,
name varchar(8) Unique,
age int Check(age between 0 and 150),
Address varchar(50) not null
)

create table book(
pid int,
bookId int,
bookName varchar(10),
bookBIN varchar(20),
bookPrice int,
CONSTRAINT book_bookId_pk Primary key(bookId),
CONSTRAINT book_bookName_uq Unique(bookName),
CONSTRAINT book_bookPrice_ck Check(bookPrice between 0 and 100),
CONSTRAINT person_pid_fk Foreign key(pid) References empTest(id) on delete cascade
)

// 添加约束
alter table 表
add constraint persion_字段_? 约束类型（约束字段）

// 删除约束
alter table 表
drop constraint 约束名

---------------------------------------
// 数据库脚本
1.删除
2.创建
3.测试数据

----------------伪列-------------------
ROMNUM，表示行号（自动编号的形式出现）
select rownum,emp.* from emp
查询前5列
select * from emp where rownum<=5
如果想要进行中间的截取操作则只能采用子查询。
// 查询6-10
select * from (select rownum rn,emp.* from emp where rownum<=10) temp where temp.rn>5
// 查询11-15
select * from (select rownum rn,emp.* from emp where rownum<=15) temp where temp.rn>10

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

----------------视图-------------------
虚拟表
一个试图就是封装了一条复杂的查询语句,创建视图的目的是为了方便下次直接调用，而不需要写复杂的查询语句
create view 试图名称 as 子查询
 此时的子查询就表示一条非常复杂的语句
 create view empv20 as select empno,ename,job,hiredate from emp where deptno=20
 // 查询视图
 select * from empv20
// 可以使用试图包装需要的查询语句
// 试图无法重名的
// 删除视图
drop view 视图名称

// Oracle中提供的创建视图的完整语法
create or replace view 视图名称 as 子查询
 使用以上的语法，在更改试图的时候就不用先删除再执行了，系统会为用户自动进行删除和重建的功能

// 注意，修改了视图，主表里的数据也会进行相应的修改
// Oracle提供了以下两个函数
 1.with check option 创建的时候使用它将使得该视图的！条件列！不能被修改。
create view empv20 as select empno,ename,job,hiredate from emp where deptno=20
with check option
 2.with read only 创建的试图只读
create view empv20 as select empno,ename,job,hiredate from emp where deptno=20
with read only
---------------------------------------

---------------序列--------------------
在很多数据库系统中都存在一个自动增长列，如果现在要想在ORACLe中完成自动增长的功能，则只能依靠序列完成，所有的自动增长操作，

需要用户手工完成处理。

完整语法
create sequence sequence
[increment by n][start with n]
[{maxvalue n|nomaxvalue}]
[{Minvalue n|nominvalue}]
[{cycle|nocycle}]
[{cache n|nocache}]

向表中添加数据需要手工使用序列
--创建序列
create sequence myseq;
--取得序列的下一个内容
nextVal;
--取得序列的当前内容（取出当前操作的序列结果）
currVal;
--手动插入操作序列值，此时序列的增长幅度为1
insert into testSeq(one,two) values(myseq.nextval,myseq.currval)
--修改序列的增长幅度
increment by 长度
--指定序列的开始位置
start with 10;

// 修改序列只能先删除原有序列再进行完善后重新创建
drop sequence myseq;
// 重新创建带增长幅度的序列
create sequence myseq increment by 2;
// 重新创建带增长幅度和开始位置的序列
create sequence myseq increment by 2 start with 10;
---------------------------------------

--------------同义词-------------------
DUAL是一张虚拟表。
其是在sys用户下的表，而其他用户可以访问，是因为同义词的作用。
同义词可以让其他用户通过一个名称方面的访问“用户名.表名称”
--创建
create synonym 同义词名称 for 用户名.表名称；
--删除
drop synonym 同义词名称
 此种特性，只适合ORACLE
---------------------------------------

-------------用户管理------------------
一个新的用户所有的权限都要进行赋予，如果想一次性吧多个权限一次性赋予给用户，则可以将这些权限定义成一组角色。

在ORACLE中提供了两个主要角色：CONNECT,RESOURCE，可以直接把这两个角色赋予给用户。
    连接，资源

// 创建用户
create user 用户名 identified by 密码
 如果要创建用户，则首先应该使用管理员进行登录
// 对用户赋予权限(创建会话)
grant 权限1，权限2...to 用户。

// 给与创建会话权限
grant create session to 用户名

// 给用户赋予角色
grant connect,resource to 用户名

--权限在用户登录时取出

// 修改用户密码
alter user 用户名 identified by 密码

// 在一般的系中存在，在用户第一次登录的时候可以修改密码，所以要想完成此功能，可以手工让一个密码失效
alter user 用户名 password expire;

// 可以使用如下命令锁住一个用户
alter user 用户名 account lock;
// 解锁
alter user 用户名 account unlock;

// 假设新建的用户想访问其他用户的表，则需要授予此张表的访问权限

-- 授予访问权限
      权限1，2      on 用户.表名 to 要给的用户
grant select,delete on scott.emp to test;

-- 回收权限
revoke 权限 on 用户.表名称 from 用户

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

----------数据库备份，恢复-------------
数据库备份： EXP
数据库恢复： IMP
在D盘上建立一个DATA的文件夹，在次文件夹值中保存所有的备份文件，如果要备份，则需要使用命令行方式，进入到d;/data文件夹中然后

敲exp命令，输入用户名，密码，一路默认，数据库将被备份到data文件夹中。

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

-------------嵌套表,可变数组-----------
可变数组是嵌套表的升级版
使用较少。
---------------------------------------

-----------数据库设计范式--------------

1.数据库表中的字段都是单一属性的，不可再分，这个单一属性由基本类型构成，包括整形，实数，字符型，逻辑型，日期型等。
2.数据库表中不存在非关键字段对任一候选关键字段的部分函数依赖（函数依赖指的是存在组合关键字中的某些字段决定非关键字段的情况

，也即所有非关键字段都完全依赖于任意一组候选关键字段。）
3.在第二范式的基础上，数据表中如果不存在非关键字段对任一候选关键字段的传递函数依赖则符合第三范式。所谓传递依赖，指的是如果

“A>B>C”的决定关系，则C传递函数依赖于A。因此，满足第三范式的数据库表应该不存在如下关系：
关键字段》非关键字段A》非关键字段B

数据库唯一原则：
数据库表的关联查询越少越好，SQL语句的复杂度越低越好。
此目的在于减少笛卡尔积
---------------------------------------

--------数据库设计工具（重点）---------
Sybase的PowerDesigner,此工具可以方便的做各种设计。
安装启动之后，可以使用此工具的建模设计。
选择ORACLE数据库，选择物理数据模型。
下面使用PowerDesigner工具将Oracle中的DEPT和EMP表还原。
（生成数据库脚本）
---------------------------------------

高级开发部分：游标，触发器，包，函数。

// 显示错误
show errors;