/*
–Oracle体系结构
数据库：在oracle数据库中只有一个库
实例：由本地磁盘文件构建（控制文件，日志文件，数据库文件） 包含进程（服务）
用户；oracle管理面向使用用户。
表空间：存储数据逻辑单位。
数据文件：存储数据物理单位。表空间跟数据文件进行关联：表空间-数据文件：一对多
*/
—-=================================
在管理员用户system/orcl登陆
–表空间
表空间创建代表项目开始
/*
语法：
create tablespace 表空间名称
datafile ‘数据文件磁盘目录’
size 500m
autoextend on/off
next 50m;
*/
create tablespace demo
datafile ‘c:\lih.dbf’ –数据文件格式 dbf/ora
size 500m
autoextend on
next 50m;
–删除表空间（不建议）
drop tablespace demo including contents and datafiles;
—–用户
/*
语法：
create user 用户名 identified by 密码
default tablespace 表空间名称；
*/
–通过命令创建用户没有任何权限
create user root identified by root
default tablespace demo; –用户关联表空间。将来登陆此用户后，操作表空间中数据，真正数据改变在磁盘中数据文件\
/* TEMPORARY TABLESPACE temp;（这里临时表空间不能使用我们创建的db_test */
–给用户进行授权
/*
授予角色：connect,resource,dba
授予权限：create table,create session,create view…
角色是权限集合
*/
grant create session to root;
grant connect,resource to root;
–回收
revoke connect from root
–==============
使用自定义用户登陆
=
/*
DDL:数据定义语言 create drop alter
DML:数据操作语言 insert update delete
DCL:数据控制语言 grant revoke
DQL:数据查询语言 select
oracle数据类型：
数值：number(a,b) a代表数值长度 b代表小数位个数
*整数 number(3) 999
*小数 number(3,2) 9.99
字符串：
*char:定长度字符串类型 name char(10) 最大长度4000
*varchar：可变长度字符串类型 name vachar(10) 最大长度4000
*varchar2:可变长度字符串类型 oralce声明
日期：
date:年月日时分秒
timestamp:时间戳，精确到秒后9位
大数据类型：
clob:字符类型。存储字符最大4G
blob:字节数据类型。存储4G
创建表
对表进行字段更改操作
数据CURD
*/
create table person(
id number(9) primary key,
name varchar2(30),
gender char(1) default 1
)
– 单独设置主键 (id number(9) primary key)
alter table person
add primary key (id)
using index
tablespace demo
pctfree 10
initrans 2
maxtrans 255;
– 给表增加描述信息(***)
comment on table person is ‘这是一个测试的表’;
comment on column person.id is ‘这是测试表的id’;
comment on column person.name is ‘这是测试表的name’;
–新增默认为空的字段
–ALTER TABLE 表名 ADD COLUMN 字段名 字段类型 DEFAULT NULL;
–增加字段
alter table person add address varchar2(100);
–多列新增
alter table person add (c1 varchar2(30),c2 varchar2 (30));
–修改字段类型
alter table person modify address varchar2(200)
alter table test1 modify (c1 varchar2(50) , c2 varchar2 (50));
–重命名字段名称(修改列名)
–ALTER TABLE 表名 RENAME COLUMN 列名 TO 新列名
alter table person rename column address to address1;
–删除字段
alter table person drop column address1;
–新增数据
–oracle事务需要手动提交
insert into person values(1,‘jack’,0);
–commit;
rollback;
insert into person(id,name) values(2,‘rose’);
commit;
–删除数据
delete from person where id = ‘1’;
– 修改数据
update person set(name = ‘jack1’,gender = ‘女’) where id =‘1’
—-oracle数据库中如何实现主键值自动增长——–序列sequence
/*
序列跟表是两个独立对象，一般在主键值生成使用序列对象来实现。
创建语法：
create sequence 序列名称;
删除语法
DROP SEQUENCE 序列名称;
使用：
select 序列名称.nextval
select 序列名称.currval
*/
–创建序列
create sequence seq_person;
–删除序列
DROP SEQUENCE seq_person;
–使用序列（查询序列值，新增数据使用） 在oracle数据库中有伪表（虚表）dual ，补全oralce语法。有select 必须有from
select seq_person.nextval from dual;
–在新增数据使用序列对象
insert into person values(seq_person.nextval, ‘张三111’, 1);
commit;
select * from person;
select seq_person.currval from dual;
–查询oarcle服务器端编码 ***
select userenv(‘language’) from dual;
–在客户端所在服务器（本地）配置环境变量 key:NLS_LANG value:编码 AMERICAN_AMERICA.ZHS16GBK
–创建序列完整语法
create sequence seq_test
start with 1
increment by 5 –设置步长
maxvalue 50 –最大值 minvalue
cycle –循环
cache 20; –缓存 序列个数 默认就是20个
–新建序列必须先执行一次nextval属性获取下一值 之后才能调用currval获取当前值
select seq_test.nextval from dual;
select seq_test.currval from dual;
/*
– 外键
alter table 从表名
add foreign key (外键id)
references 主表名 (主表id) on delete cascade;
*/
–约束 ：主键 外键 唯一 非空 检查约束
drop table person;
create table person(
id number(9) primary key,
name varchar2(30) not null unique,
gender char(1) default 1 check(gender in(0,1)) –检查约束
)
insert into person values(seq_person.nextval,‘jack’,1);
commit;
–班级表（一方）-学生表（多方）：一对多：
create table t_class(
id number(9) primary key,
name varchar2(200) not null
)
–子表外键字段跟父表主键字段关联
create table t_student(
id number(9) primary key,
name varchar2(200) not null,
class_id number(9),
constraint fk_stu_cls foreign key(class_id) references t_class(id)
)
–序列
create sequence seq_class;
create sequence seq_stu;
insert into t_class values(seq_class.nextval,‘一年级三班’);
commit;
select * from t_class;
select * from t_student;
insert into t_student values(seq_stu.nextval,‘jack’,5);
commit;
———================================scott用户
–使用命令解锁用户 lock unlock
alter user scott account unlock;
–重置密码
alter user scott identified by tiger;
select empno,ename ,job,sal,comm,deptno,mgr,hiredate from emp;
select d.deptno,d.dname,loc from dept d;
–========================
函数
/*
单行函数：作用于一条记录得到一个结果
多行函数：作用于多条记录得到一个结果
*/
–单行函数
–字符串函数
–全大写
select upper(‘abc’) from dual;
–全小写
select lower(‘ABC’) from dual;
–拼接字符串
select concat(‘a’,‘b’) from dual;
select ‘a’||‘b’||‘c’ from dual;
–数值函数
–四舍五入
select round(15.66,1) from dual;
–日期函数
/*
日期-日期=天数
日期-天数=日期
*/
select sysdate from dual;
select ename,sysdate-e.hiredate from emp e
–间隔月份 months_between
select ename,round(months_between(sysdate,e.hiredate)) from emp e
–转换函数 to_char to_number to_date ***
select to_char(123),123 from dual;
select to_char(45434534554.16,‘L999,999,999,999.99’) from dual;
select to_number(‘123’) from dual;
–将字符串转为日期类型
select to_date(‘2019/12/5 12:45:20’,‘YYYY-MM-dd hh:mi:ss’) from dual;
select to_char(sysdate, ‘fmyyyy-mm-dd hh24:mi:ss’) from dual;
–通用函数 decode(val,v1,v11,v2,v22,vn)判断
–将员工工作展示位中文
select e.empno,
decode(e.job, ‘CLERK’, ‘店员’, ‘SALESMAN’, ‘销售’, ‘MANAGER’, ‘经理’, ‘ANALYST’, ‘分析师’, ‘资本家’) job
from emp e
–case when
/*
case val
when ‘’ then ‘’
when ‘’ then ‘’
else
‘’
end
*/
select e.ename,
case e.job
when ‘SALESMAN’ then ‘销售’
when ‘CLERK’ then ‘店员’
else
‘其他人员’
end job
from emp e;
–处理空值 null+数字=null 使用nvl(value, a)
–查询员工年薪
select nvl(‘b’, ‘a’) from dual;
select e.ename,e.sal*12+nvl(comm,0) from emp e;
–====多行函数
select count(*) from emp; –总个数
select sum(sal) from emp; –求和
select avg(sal) from emp; –平均数
select max(sal) from emp; –最大值
select min(sal) from emp; –最小值
—===============分组统计查询 group by
/*
select column… from tab1,tab2 where … group by column having …
*/
–总结：查询普通字段跟聚合函数一块使用，
–在select中出现普通字段必须在group by中出现
–group by中出现普通字段 不一定必须要在select中出现
–查询每个部门人数
select e.deptno, count(*)
from emp e
group by e.deptno;
–查询每个部门平均工资
select e.deptno,avg(sal)
from emp e
group by e.deptno;
–查询部门员工数量 以及 部门名称
–查询每个部门数量
select e.deptno, count(*) dcount
from emp e
group by e.deptno;
–将查询结果当成表使用
select d.dname,t.dcount
from (select e.deptno, count(*) dcount
from emp e
group by e.deptno) t,dept d
where d.deptno = t.deptno
–查询部门平均工资大于2000
–where 用于分组之前 having用户分组后结果进一步筛选
select e.deptno,avg(sal)
from emp e
group by e.deptno
having avg(sal)>2000;
—-================================多表查询
/*
内连接:关联查询如果条件不满足，关联记录不显示
外连接
子查询
*/
–查询员工表，部门表
select * from emp,dept; –笛卡尔积
–在笛卡尔积基础上进步设置关联条件得到内连接
select * from emp e,dept d where e.deptno = d.deptno;
select e.ename,d.dname from emp e inner join dept d on e.deptno = d.deptno;
–查询每个员工上级领导
select e1.
,e2.ename
from emp e1,emp e2
where e1.mgr = e2.empno
–在上面基础上查询员工部门信息
select e1.
,d.dname,e2.ename
from emp e1,emp e2,dept d
where e1.mgr = e2.empno
and e1.deptno = d.deptno
–查询员工工资等级，领导工资等级 引入工资登记表salgrade
select e1.*,d.dname,s1.grade ,e2.ename,s2.grade
from emp e1,emp e2,dept d,salgrade s1,salgrade s2
where e1.mgr = e2.empno
and e1.deptno = d.deptno
and e1.sal between s1.losal and s1.hisal
and e2.sal between s2.losal and s2.hisal;
select e1.*,d.dname,
decode(s1.grade,1,‘一级’,2,‘二级’,3,‘三级’,‘四级’) esalgrade
,e2.ename,s2.grade
from emp e1,emp e2,dept d,salgrade s1,salgrade s2
where e1.mgr = e2.empno
and e1.deptno = d.deptno
and e1.sal between s1.losal and s1.hisal
and e2.sal between s2.losal and s2.hisal;
–=====================外连接
/*
外连接：关联查询即使关联条件不成立，查询基础表对应记录也应该展示。
左外连接：select c… from tab1 left join tab2 on 条件。 tab1记录展示
右外连接：select c… from tab1 right join tab2 on 条件。 tab2记录展示
oracle特有外连接： 内连接语法上加 (+)
select … from tab1,tab2 where tab1.a = tab2.b(+)
*/
–即使员工没有上级领导。
select e1.*,e2.ename
from emp e1 left join emp e2 on e1.mgr = e2.empno
–(+)加在哪张表条件上，对面表记录全部展示
select e1.*,e2.ename
from emp e1,emp e2
where e2.empno(+) = e1.mgr
—-========================子查询
–查询比7654员工 工资高员工
select sal from emp where empno = 7654
select * from emp where sal >(select sal from emp where empno = 7654)
–查询每个部门最低工资员工信息
–查询每个部门最低工资
select e.deptno, min(sal) dminsal
from emp e
group by e.deptno;
–思想：将查询结果当成表使用
select e.*,d.dname,t1.dminsal
from (select e.deptno, min(sal) dminsal
from emp e
group by e.deptno) t1,dept d, emp e
where t1.deptno = d.deptno
and e.sal = t1.dminsal
and e.deptno = t1.deptno;
—================================分页查询
/*
rownum:是伪列，查询结果每条记录都会分配行号
rowid:是伪列，是记录唯一标识
*/
select * from emp where rowid = ‘AAAMfPAAEAAAAAgAAD’
select rownum,rowid,e.* from emp e where sal>1500;
–pageNum,pageSize:3
select rownum,e.* from emp e where rownum<=3
–第二页 问题：rownum不能使用大于号。记录不显示
select rownum,e.* from emp e where rownum<=6 and rownum>3
–rownum生成时机：查询结果一条条加载展示，没加载一条记录给记录分配行号
–没有排序字段
select * from (select rownum r,e.* from emp e) t1 where t1.r>3 and t1.r<=6
–加入排序字段-数据错乱
select * from (select rownum r,e.* from emp e order by e.sal) t1 where t1.r>3 and t1.r<=6
–先排序，再分配行号，使用行号
select * from (select rownum dr ,t1.* from (select * from emp e order by e.sal)t1)t2 where t2.dr>3 and t2.dr<=6
–公式
select * from (select rownum dr ,t1.* from (select * from emp e order by e.sal)t1)t2 where t2.dr>(pageNum-1)
pagesize and t2.dr<=pageNum
pageSize
select * from emp;
select * from dept;
select * from salgrade;
—===================exists & not exists 存在/不存在
/*
语法：select … from tab where exists （查询）
条件boolean类型：如果查询有结果返回true ，结果全部展示 。如果查询null,返回false,结果不展示
*/
select * from emp where exists(select * from dept)
select * from emp where 1=2
–查询有员工部门记录(如果部门编号在员工表中出现（存在），说明该部门有员工)
select * from emp;
select * from dept;
select * from dept d where exists(select * from emp e where d.deptno = e.deptno)
–================================视图
–查询当前用户权限
select * from session_privs;
select * from session_roles;
–注意：需要使用system用户给scott用户授权
grant dba to scott;
/*
视图：是一张虚拟表，视图本身不存放数据，数据来源于原始表。视图跟表是映射关系
语法：create [or replace] view 视图名称 as select 语句；
应用：1、封装一条复杂查询语句。
2、隐藏一些敏感数据
3、设置只读
*/
create or replace view v_emp20 as select * from emp where deptno = 20;
create or replace view v_emp as select empno,ename,job,hiredate from emp;
–使用视图（跟使用表基本一样）
select * from v_emp20;
select * from v_emp;
update v_emp20 set ename = ‘jack’ where empno = 7369;
–创建只读视图
create or replace view v_emp as select * from emp with read only;
update v_emp set ename = ‘jack’ where empno = 7369;
commit;
–=============================索引
/*
索引：为了提高检索效率。
语法：create index 索引名称 on 表名(列名1，列名2)
*/
create table person(
id number(8) primary key,
name varchar2(30) not null
)
alter table person modify name varchar2(100);
–模拟添加百万数据
select sys_guid() from dual;
–
declare
vname varchar2(100);
begin
for i in 1…1000000 loop
select sys_guid() into vname from dual;
insert into person values(i,vname);
end loop;
commit;
end;
select * from person where name = ‘943C452F14774AEDA2C1DBC78FEBEB92’; –没有创建索引查询–0.64s
–创建索引
create index idx_person_name on person(name);
–再次在有索引表中查询
select * from person where name = ‘4308BDBBA2E34BB0AA8E5F5258EB2160’; –0.015
/*
索引使用：
1、在数据量大的表上使用序列。
2、在表中经常用于查询列上使用索引。
3、复合索引（给多列创建索引）列最好不要超过四个。
*/
–======================sql/sql
/*
pl/sql:过程化语言/结构化查询语言-编程
plsql结构：
[declare]
–定义部分
begin
–函数体
[exception]
–处理异常
end;
*/
–定义变量/常量
declare
i constant number(3) := 1; – :=赋值
–pname varchar2(2); –定义变量
pname emp.ename%type; –定义引用型变量-引用某种表中某一列类型
pemp emp%rowtype; –记录型变量-通过变量保存某张表中一条记录
begin
–i:=2; 常量不能重新赋值
dbms_output.put_line(‘i的值：’||i); –在控制台输出打印
–将查询得到结果给某个变量赋值
select ename into pname from emp where empno = 7369; –查询得到结果 通过into关键字赋值
dbms_output.put_line(‘7369姓名为：’||pname);
select * into pemp from emp where empno = 7654; –给记录型变量赋值
dbms_output.put_line(‘7654的姓名：’||pemp.ename||’,工作：’||pemp.job);
end;
–==================================if 判断
/*
语法：
if 条件 then
逻辑
elsif 条件 then
逻辑
else
逻辑
end if;
*/
–判断变量值
declare
i number(4) := &pnum; –&变量名称（任意） 从控制录入
begin
if i = 1 then
dbms_output.put_line(‘我是1’);
else
dbms_output.put_line(‘其他’);
end if;
end;
–判断值
declare
score number(3) := &score;
begin
if score <= 20 then
dbms_output.put_line(‘青铜’);
elsif score > 20 and score <= 50 then
dbms_output.put_line(‘白银’);
else
dbms_output.put_line(‘王者’);
end if;
end;
–==============================循环—-
/*
语法一： while循环
while 循环条件 loop
循环体;
end loop;
语法二： loop循环
loop
循环体
exit when 退出条件;
end loop;
语法三： for循环
for i in 1.。10 loop
循环体
end loop;
*/
–
declare
i number(3):=1;
begin
while i <=10 loop
dbms_output.put_line(i);
i:=i+1;
end loop;
end;
–
declare
i number(3):=1;
begin
loop
dbms_output.put_line(‘loop’||i);
i:=i+1;
exit when i>10; –退出条件
end loop;
end;
–
declare
begin
for i in 1…10 loop
dbms_output.put_line(‘for ‘||i);
end loop;
end;
–===================游标 cursor
/*
游标：存放结果集，返回多条记录存放到游标中。
游标定义在declare部分定义：
cursor 游标名称 is 查询语句；
*/
–将20号部门员工存放到游标中，打印每个员工信息
declare
pemp emp%rowtype;
cursor pc is select * from emp where deptno = 20;
begin
open pc;
loop
fetch pc into pemp; –从游标中取数据
exit when pc%notfound; –游标中数据全部提取完毕 退出循环
dbms_output.put_line(‘姓名：’||pemp.ename||’,工作：’||pemp.job);
end loop;
close pc;
end;
–给员工进行涨工资 销售加500 经理加300 总裁加100
declare
addsal number(3):=0; –定义动态加工资金额
cursor pc is select * from emp;
pemp emp%rowtype; –记录型变量用于存放遍历过程中获取到的员工记录
begin
open pc;
loop
fetch pc into pemp;
exit when pc%notfound;
if pemp.job = ‘SALESMAN’ then
addsal:=500;
elsif pemp.job = ‘MANAGER’ then
addsal:=300;
elsif pemp.job = ‘PRESIDENT’ then
addsal:=100;
else
addsal:=0;
end if;
update emp set sal=sal+addsal where empno = pemp.empno;
end loop;
commit;
close pc;
end;
select * from emp;
–==============================存储过程
/*
存储过程：实现编译好一组sql语句集，为了完成特定功能一组业务逻辑。
实现编译好存放在数据库服务器端。应用程序通过过程名称进行调用进而完成某个业务功能
好处：效率高（提前编译好）
弊端：维护成本高
语法：
create [or replace] procedure 过程名称(参数1 in|out 类型，。。。。)
as|is –定义变量
begin
– 程序体
end;
*/
–使用过程实现helloworld
create or replace procedure pro_helloworld
is
a varchar2(30):=‘hello world procedure’;
begin
dbms_output.put_line(a);
end;
–执行过程，进行编译。如果有语法错误没有提示
–调用（自测）
–方式一：
call pro_helloworld();
–方式二：可以定义变量存放过程返回结果
declare
begin
pro_helloworld();
end;
–过程实现：给某个员工进行涨工资，输出涨前跟涨后工资。
create or replace procedure pro_add_sal(pno in number,money in number)
is
oldsal emp.sal%type;
newsal emp.sal%type;
begin
select sal into oldsal from emp where empno = pno;
dbms_output.put_line(‘涨前工资：’||oldsal);
update emp set sal=sal+money where empno = pno;
commit;
select sal into newsal from emp where empno = pno;
dbms_output.put_line(‘涨后工资：’||newsal);
end;
–调用
call pro_add_sal(7369, 50);
–使用过程查询某个员工年薪
create or replace procedure pro_total_sal(pno number, total out number)
is
begin
–查询结果给输出参数赋值
select sal*12+nvl(comm,0) into total from emp where empno = pno;
end;
–
declare
t number;
begin
pro_total_sal(7369, t);
dbms_output.put_line(‘年薪：’||t);
end;
—==================================存储函数
/*
实现编译好一组sql语句集，为了完成特定功能一组业务逻辑。
实现编译好存放在数据库服务器端。应用程序通过过程名称进行调用进而完成某个业务功能
语法：
create or replace function 函数名称(参数1 in|out 类型，。。。。) return 类型
is
begin
return 变量; –该变量类型一定要跟函数指定返回值类型一致
end;
*/
–查询某个员工年薪
create or replace function fun_total_sal(pno number) return number
is
totalsal number(9);
begin
select sal*12+nvl(comm,0) into totalsal from emp where empno = pno;
return totalsal;
end;
–调用
declare
total number;
begin
total:=fun_total_sal(7369);
dbms_output.put_line(total);
end;
/*
函数过程区别：
相同：从完成特定功能来说，函数能实现过程也可以实现。
不同：语法不一样，函数必须有返回值。 函数可以用在select部分部分
*/
select e.*,d.dname from emp e,dept d where e.deptno = d.deptno;
–需求：根据部门编号返回部门名称
create function fun_dname(dno number) return varchar2
is
dname dept.dname%type;
begin
select dname into dname from dept where deptno = dno;
return dname;
end;
–
declare
d varchar2(20);
begin
d:=fun_dname(10);
dbms_output.put_line(d);
end;
–将函数用在select
select ename,fun_dname(deptno) dname from emp;
—===========================触发器 trigger
/*
理解：给某张表安装监视器监控表中数据，一旦外部对表中记录做操作（增删改）触发 触发器逻辑。符合规则后方可提交
数据库触发器是一个与表相关联的、存储的 PL/SQL 程序。每当一个特定的数据操作语句
(Insert,update,delete)在指定的表上发出时， Oracle 自动地执行触发器中定义的语句序列。
语法：
create or replace trigger 触发器名称
after|before
insert|update|delete
on 表
declare
begin
–程序体
end;
*/
–新增用户后，在控制台输出‘新员工入职’
create or replace trigger tri_after_emp
after
insert
on emp
declare
begin
dbms_output.put_line(‘新员工入职’);
end;
insert into emp(empno,ename) values(5555,‘rose’);
commit;
–在周末不能办理入职
create or replace trigger tri_before_emp
before
insert
on emp
declare
weekend varchar2(10);
begin
select trim(to_char(sysdate,‘day’)) into weekend from dual; –查询触发器执行当前系统日期获取星期数
if weekend in(‘sunday’,‘saturday’) then
raise_application_error(-20001,‘周末不能办理入职’);–抛出异常 参数一：异常返回编号取值：-20001~ -20999 参数：提示字符串
end if;
end;
–不能给员工降薪
create or replace trigger tri_before_sal_emp
before
update
on emp
for each row –行级触发器（每条记录操作都会触发） 如果使用:old :new获取旧记录，新记录需要加for each row
declare
begin
if :old.sal>:new.sal then –sal值不能减少
raise_application_error(-20002,‘不能给员工降薪’);
end if;
end;
update emp set sal = sal-1 where empno = 7369;
commit;
insert into emp(empno,ename) values(6666,‘zhangsan’);
commit;
select * from emp;