–创建表空间
create tablespace tedu
datafile ‘D:\tedu_oralce.dbf’
size 100m
autoextend on
next 10m;
–删除表空间
drop tablespace tedu;
–创建用户
create user tedu
identified by tedu
default tablespace tedu;
–给用户授权
–常用的用户角色
connect --普通角色
resource --开发都角色
dba --超级管理员角色
–给新创建用户授权
grant dba to tedu;
oracle数据类型
–创建一个person表
create table person(
pid number(20),
pname varchar(10)
);
–修改表结构
–添加person表中的性别
alter table person add gender number(1);
–修改列的类型
alter table person modify gender char(1);
–修改列的名称
alter table person rename column gender to sex;
–删除列
alter table person drop column sex;
–查询表数据
select * from person;
–增加数据
insert into person(pid, pname) values(1,‘小明’);
commit;
–修改记录
update person set pname = ‘小马’ where pid=1;
commit;
–三个删除
–删除表中数据
delete from person;
–删除表结构
drop table person;
–先删除数据与表结构，再创建新的表
–这个是在大量数据时使用，特别是有索引的表中，使用这个效率高
–索引提高效率，但是影响增删改的效率
truncate table person;
–序列
–序列不真的属于任何一张表，但是可以做逻辑和表做绑定
–序列默认从1开始，依次递增，主要用来给主键赋值使用
–dual是一张虚表，只是为了补全语法使用
create sequence s_person;
select s_person.nextval from dual;
select * from person;
insert into person(pid,pname) values(s_person.nextval,‘小明’);
示例：为emp员工表的员工号和薪水创建索引。
create index empno_sal_index on emp(empno,sal);
–单选函数：作用于一行，返回一个结果
–字母(大小写转换)
select upper(‘yes’) from dual;
select lower(‘yES’) from dual;
–数字函数：1:保留小数点位数，四舍五入 如果-1会向前保留 = 30
select round(26.18,-1) from dual;
select trunc(26.18,-1) from dual; --这个不会四会五入
–日期函数：（可以直接加减）单位：天
–查询emp表中的员工入职距离现在多少天
select sysdate-e.hiredate from emp e;
–查询emp表中的员工入职距离现在多少月
select months_between(sysdate-e.hiredate) from emp e;
–转换函数：
–日期转字符串
select to_char(sysdate,‘yyyy-mm-dd hh:mi:ss’) from dual; --让时间24小时 hh24 如果去0 ：‘fm yyyyy-mm-dd hh:mi:ss’
–字符串转日期
select to_date(‘2020-09-26 09:19:59’,‘yyyy-mm-dd hh:mi:ss’) from emp;
–通用函数：
–任何数值与null做算sy都等于null
–nvl(a,b) 如果a等于null 则取b值
select e.sal*12+nvl(e.comm,0) from emp e;
–条件表达式:mysql与oralce共用
–如果emp表中的员工工资>3000是高收入 <1500低收入
select e.sal,
case
when e.sal>3000 then ‘高收入’
when e.sal>1500 then ‘中等收入’
else ‘低收入’
end
from emp e;
–oracle专用表达式
–oralce中，使用别名使用“”，其它都用单引号
select e.ename,
decode(e.ename,
‘SMITH’,‘杨’,
‘ALLEN’,‘周’,
‘WARD’,‘马’,
‘JONES’,‘王’,
‘无名字’
) “中文名字”
from emp e;
–多选函数【聚合函数】：作用于多选，返回一个结果
select count(1) from emp; --emp表中数据量
select sum(e.sal) from emp e; --工资总和
select max(e.sal) from emp e; --最大工资
select min(e.sal) from emp e; --最小工资
select avg(e.sal) from emp e; --平均工资
–oracle分页查询
select * from (
select rownum rn,e.* from (
select * from emp order by sal desc
) e where rownum<11
) where rn>5
–视图是提供一个查询的窗口，所有数据来源于表
–创建视图必须有dba权限
–查询scott用户下emp表，并从tedu用户下创建emp
create table emp as select * from scott.emp;
select * from emp;
create view v_emp as select ename, sal from emp;
select * from v_empl;
drop from v_emp;
–只读视图
create view v_empl as select ename,sal from emp with read only;
–索引：索引就是在表的列上创建一个二ca树 可以提高查询效率，增删改效率下降
–单列索引(函数和模糊查询不会触发) 须要原始值查询
create index idx_ename on emp(ename);
select * from emp where ename=‘SCOTT’;–触发
–组全索引
create index idx_enamejob on emp(ename,job);
–plsql编程语言
–plsql编程语言是对sql的一个扩展，使用sql有过程化编程的特性，
–plsql编程语言比一般的过程化编程语言理加灵活高效
–主要用来编写存储过程和存储函数
–声明方法
–赋值操作可以使用:= 也可以使用into
declare
i number(2) :=2;
o varchar2(20) :=‘我爱你亲爱的姑娘’;
ena emp.ename%type; --引用类型变量
emprow emp%rowtype; --记录类型变量
begin
dbms_output.put_line(i);
select * into emprow from emp where empno=7788;
select ename into ena from emp where empno=7788;
dbms_output.put_line(ena);
dbms_output.put_line(emprow.ename || ‘的工作是：’|| emprow.job);
end;
–plsql中if判断
declare
i number := ⅈ
begin
if i<18 then
dbms_output.put_line(‘未成年’);
elsif i<40 then
dbms_output.put_line(‘中成年’);
else dbms_output.put_line(‘中成年’);
end if;
end;
–三种循环
declare
i number(2) :=1;
begin
while i<10 loop
dbms_output.put_line(i);
i := i+1;
end loop;
end;
declare
i number(2) :=1;
begin
loop
exit when i>10;
dbms_output.put_line(i);
i := i+1;
end loop;
end;
declare
begin
for i in 1…10 loop
dbms_output.put_line(i);
end loop;
end;
—游标：可以存放多个对象，多行记录。
—输出emp表中所有员工的姓名
declare
cursor c1 is select * from emp;
emprow emp%rowtype;
begin
open c1;
loop
fetch c1 into emprow;
exit when c1%notfound;
dbms_output.put_line(emprow.ename);
end loop;
close c1;
end;
-----给指定部门员工涨工资
declare
cursor c2(eno emp.deptno%type)
is select empno from emp where deptno = eno;
en emp.empno%type;
begin
open c2(10);
loop
fetch c2 into en;
exit when c2%notfound;
update emp set sal=sal+100 where empno=en;
commit;
end loop;
close c2;
end;
----查询10号部门员工信息
select * from emp where deptno = 10;
—存储过程
–存储过程：存储过程就是提前已经编译好的一段pl/sql语言，放置在数据库端
--------可以直接被调用。这一段pl/sql一般都是固定步骤的业务。
----给指定员工涨100块钱
create or replace procedure p1(eno emp.empno%type)
is
begin
update emp set sal=sal+100 where empno = eno;
commit;
end;
select * from emp where empno = 7788;
----测试p1
declare
begin
p1(7788);
end;
----通过存储函数实现计算指定员工的年薪
----存储过程和存储函数的参数都不能带长度
----存储函数的返回值类型不能带长度
create or replace function f_yearsal(eno emp.empno%type) return number
is
s number(10);
begin
select sal*12+nvl(comm, 0) into s from emp where empno = eno;
return s;
end;
----测试f_yearsal
----存储函数在调用的时候，返回值需要接收。
declare
s number(10);
begin
s := f_yearsal(7788);
dbms_output.put_line(s);
end;
—out类型参数如何使用
—使用存储过程来算年薪
create or replace procedure p_yearsal(eno emp.empno%type, yearsal out number)
is
s number(10);
c emp.comm%type;
begin
select sal*12, nvl(comm, 0) into s, c from emp where empno = eno;
yearsal := s+c;
end;
—测试p_yearsal
declare
yearsal number(10);
begin
p_yearsal(7788, yearsal);
dbms_output.put_line(yearsal);
end;
----in和out类型参数的区别是什么？
—凡是涉及到into查询语句赋值或者:=赋值操作的参数，都必须使用out来修饰。
—存储过程和存储函数的区别
—语法区别：关键字不一样，
------------存储函数比存储过程多了两个return。
—本质区别：存储函数有返回值，而存储过程没有返回值。
----------如果存储过程想实现有返回值的业务，我们就必须使用out类型的参数。
----------即便是存储过程使用了out类型的参数，起本质也不是真的有了返回值，
----------而是在存储过程内部给out类型参数赋值，在执行完毕后，我们直接拿到输出类型参数的值。
----我们可以使用存储函数有返回值的特性，来自定义函数。
----而存储过程不能用来自定义函数。
----案例需求：查询出员工姓名，员工所在部门名称。
----案例准备工作：把scott用户下的dept表复制到当前用户下。
create table dept as select * from scott.dept;
----使用传统方式来实现案例需求
select e.ename, d.dname
from emp e, dept d
where e.deptno=d.deptno;
----使用存储函数来实现提供一个部门编号，输出一个部门名称。
create or replace function fdna(dno dept.deptno%type) return dept.dname%type
is
dna dept.dname%type;
begin
select dname into dna from dept where deptno = dno;
return dna;
end;
—使用fdna存储函数来实现案例需求：查询出员工姓名，员工所在部门名称。
select e.ename, fdna(e.deptno)
from emp e;
—触发器，就是制定一个规则，在我们做增删改操作的时候，
----只要满足该规则，自动触发，无需调用。
----语句级触发器：不包含有for each row的触发器。
----行级触发器：包含有for each row的就是行级触发器。
-----------加for each row是为了使用:old或者:new对象或者一行记录。
—语句级触发器
----插入一条记录，输出一个新员工入职
create or replace trigger t1
after
insert
on person
declare
begin
dbms_output.put_line(‘一个新员工入职’);
end;
—触发t1
insert into person values (1, ‘小红’);
commit;
select * from person;
—行级别触发器
—不能给员工降薪
—raise_application_error(-20001~-20999之间, ‘错误提示信息’);
create or replace trigger t2
before
update
on emp
for each row
declare
begin
if :old.sal>:new.sal then
raise_application_error(-20001, ‘不能给员工降薪’);
end if;
end;
----触发t2
select * from emp where empno = 7788;
update emp set sal=sal-1 where empno = 7788;
commit;
----触发器实现主键自增。【行级触发器】
—分析：在用户做插入操作的之前，拿到即将插入的数据，
------给该数据中的主键列赋值。
create or replace trigger auid
before
insert
on person
for each row
declare
begin
select s_person.nextval into :new.pid from dual;
end;
–查询person表数据
select * from person;
—使用auid实现主键自增
insert into person (pname) values (‘a’);
commit;
insert into person values (1, ‘b’);
commit;
----oracle10g ojdbc14.jar
----oracle11g ojdbc6.jar