博客

Oracle数据库详解

  • Post author:
  • Post category:其他




Oracle数据库

服务端操作sql语句

登录sql 

sqlplus

超级管理员账号密码 

账号:system 密码:123456

修改账号密码 

 alter user scott identified by 123456 account unlock;

普通登录 

账号:scott 密码:123456

增删改查 

select * from student  for update;
--oracle中 增删改查一定要记得提交
insert into student values(6,'刘雄',19)
commit;

分页查询 

select  * from(select student.*,rownum rn from student) s where s.rn>=3 and s.rn<=6

select * from(select  s.*,rownum  rn from (select  * from (select   eid ,ename,ebrithday,eaddress,dname,dsalary  from Employee  join Dept  on  Employee.did=Dept.did  order by dsalary desc)) s 
) v where v.rn>3

数据作业 

--1.查询所有员工的基本信息 按照工号排序
select * from Employee  join Dept  on  Employee.did=Dept.did  order by eid asc
--2.查询财务部的岗位工资
select dsalary from Employee  join Dept  on  Employee.did=Dept.did  where dname='财务部'
--3.查询财务部下有那些员工
select * from Employee  join Dept  on  Employee.did=Dept.did  where dname='财务部'
--4.查询比张三工资高的员工信息
select * from Employee  join Dept  on  Employee.did=Dept.did  where dsalary >(select dsalary from Employee  join Dept  on  Employee.did=Dept.did  where ename='张三')
--5.查询每个部门有多少员工 从多到少排列
select count(*)  from Employee  join Dept  on  Employee.did=Dept.did group by dname order by dname

--6.查询本公司一年总共支出多少工资
select sum(dsalary)*12 from Employee  join Dept  on  Employee.did=Dept.did  order by eid asc
--7.查询公司薪资最高的前三名
select * from (select eid,ename,ebrithday,eaddress,dname,dsalary,rownum from Employee  join Dept  on  Employee.did=Dept.did  order by dsalary desc
) where rownum <= 3;
--8.查询公司薪资最高的第5到8名
select * from(select  s.*,rownum  rn from (select  * from (select   eid ,ename,ebrithday,eaddress,dname,dsalary  from Employee  join Dept  on  Employee.did=Dept.did  order by dsalary desc)) s 
) v where v.rn>3
 
--9.查询年龄比李四大的员工信息
select * from Employee  join Dept  on  Employee.did=Dept.did where ebrithday < (select ebrithday from Employee  join Dept  on  Employee.did=Dept.did where ename='李四')

--10.查询今年年满30岁的员工信息
select * from (select s.*,TRUNC ( MONTHS_BETWEEN (SYSDATE, ebrithday)/12) as a  from (select * from Employee  join Dept  on  Employee.did=Dept.did  ) s
) r
where r.a>30
    
   第二种方法
    select e.*,d.dname,d.dsalary from employee e join dept d on e.did=d.did where (select sysdate from dual)-e.ebrithday>=30*365

面试题 要掌握 

--（1）查询课程编号为“001”的课程比“002”的课程成绩高的所有学生的学号？

select * from student where sid in (select a.sid from (select * from score where cid='1')a,(select * from score where cid='2')b 
where a.score>b.score)

--（2）查询平均成绩大于60分的学生的学号和平均成绩？ 
select * from(select  avg(score) a,student.sid from student join score on student.sid=score.sid   
group by  student.sid  ) s  where s.a>60 

 --（3）查询所有学生的学号、姓名、选课数、总成绩？
 select  count(course.cid),sum(score.score),student.sname from student ,score ,course,teacher where  student.sid=score.sid and score.cid=course.cid  and  course.tid=teacher.tid
 group by  student.sname
--（4）查询姓“悟”的老师的个数？
select count(*) from teacher where tname like '悟%' 

--（5）查询没学过“悟空”老师课的学生的学号、姓名？

select * from student where sid not in(select sid from score where cid in (select cid from course where  tid=(select tid from teacher where tname='悟空'))
)



1.表空间

ORCL

表空间主要是为了整理 分类数据

创建表空间 

--创建表空间
--真实存在 所以要指定路径
--分配存储大小
create tablespace tp _a 
datafile 'd:\TablespaceShenjian\tp_a01.dbf' size 60M;

删除表空间 

--删除
drop tablespace tp_a
including contents;



2.创建用户

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-vlPTvkkM-1647479815864)(C:\Users\shenjian\AppData\Roaming\Typora\typora-user-images\image-20220216144804328.png)]

填写:

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-qg2ZvcHl-1647479815865)(C:\Users\shenjian\AppData\Roaming\Typora\typora-user-images\image-20220216144131837.png)]

赋予权限 

grant connect,resource to test1;

赋予具体实体的操作权限 

--赋予所有权限
grant all on scott.student to test1; 
--查询权限
grant select on scott.student to test1;
--修改权限
grant update on scott.student to test1;
--删除权限
grant delete on scott.student to test1;

--赋予权限之后查询
grant all on scott.student to test1;



3.序列

什么是序列？

是一个能够自增长的 独立存在的一个东西

创建序列 序列是独立存在的

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-UCM5R89G-1647479815866)(C:\Users\shenjian\AppData\Roaming\Typora\typora-user-images\image-20220216145945497.png)] 

一般nextval和currval用于序列sequence中，
    -通过nextval和currval进行调用
    nextval:每次获得不同的sequence值，根据increment（增量），序列值增加
    currval:获得当前指向的sequence值（只执行currval的话，不会因执行次数而改变）

·--创建序列 是独立存在的
--select seq_a.currval from dual;
--select seq_a.nextval from dual;
--insert  into student values(seq_a.nextval,'小剑',11,'女');



4.同义词

有什么用？

可以使用取的名字去使用他查询

创建同义词

右键new

Synonyms

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-tpMQlyHW-1647479815866)(C:\Users\shenjian\AppData\Roaming\Typora\typora-user-images\image-20220216152946330.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-bOmyeJY9-1647479815866)(C:\Users\shenjian\AppData\Roaming\Typora\typora-user-images\image-20220216152615260.png)]

查询 

select * from s



5.索引

--索引 一定要建立在大数据量的基础上才有用
--Oracle 的索引  位图索引 大写函数索引  主键索引 唯一索引  反向建索引
    主键索引,只能创建到主键索引上，创建主键就出来了,不能为空，唯一索引需要主动创建,可以为空 ,可以创建到任何列，但是不能重复
--唯一索引
create  unique index index_u_age
on student(sid);
--反向键索引
create   index index_u_age
on student(sid) reverse;
--创建位图索引
create   bitmap index index_u_age on student(sid) ;

--针对函数建立索引

create index index_u_age on student(UPPER(sid));
--删除索引
drop index index_u_age



6.表分区



PL/Sql编程

什么是 PL/Sql编程？

不仅仅只是写增删改查 写定义变量、赋值、判断循环 

declare  //声明 定义的区域


 begin //开始书写代码的区域
    

end;


declare 
v_yuwen number(4);
v_shuxue number(4);
v_Englis number(4);
v_avg number(4);
begin
--直接给变量复制,=在数据库中是比较的意思 所以需要加: 表示他是一个变量
v_yuwen:=80;
v_shuxue:=60;
v_Englis:=60;
v_avg:=avg(v_yuwen+v_shuxue+v_Englis);
Dbms_Output.put_line('张唉干的平均成绩是:'||v_avg);
end;

--当你想得到某个表的数据时 你并不知道哪一列的数据类型你就可以使用表名.列名为数据类型
declare 
v_a student.sname%TYPE;
v_b student.sage%TYPE;
c_c constant number(5):=100;--C开头定义的是常量

begin
--直接给变量赋值
select sname,sage
into v_a,v_b
from student where sid=21;


Dbms_Output.put_line('查找赋值:'||v_a||v_b);
end;

if分支 

--当你想得到某个表的数据时 你并不知道哪一列的数据类型你就可以使用表名.列名为数据类型
declare 
v_b student.sage%TYPE;

begin
select sage
into v_b
from student where sid=21;

if v_b<18 then
DBMS_OUTPUT.put_line('未成年!');
elseif v_b<100 then
DBMS_OUTPUT.put_line('大老年!');

elseif v_b<80 then
DBMS_OUTPUT.put_line('老年!');

end if;
end;

while 循环 

--当你想得到某个表的数据时 你并不知道哪一列的数据类型你就可以使用表名.列名为数据类型
declare 
v_a  number(4):=200;

begin
loop 
dbms_output.put_line('今天天气不错');
v_a:=v_a+1;
insert into student values(v_a,'小白',11,'男');
exit when v_a=210;

commit;
end loop;

end;

swich循环 

--当你想得到某个表的数据时 你并不知道哪一列的数据类型你就可以使用表名.列名为数据类型
declare 
v_a  number(11):=11;

begin
case 
 when v_a<10 then dbms_output.put_line('小于10');
 when v_a<20 then dbms_output.put_line('小于20');
when v_a<30 then dbms_output.put_line('小于30');
when v_a<40 then dbms_output.put_line('小于40');


end case;

commit;


end;



游标

游标有啥用？

用来存储多种结果集 

--当你想得到某个表的数据时 你并不知道哪一列的数据类型你就可以使用表名.列名为数据类型
declare 
--定义游标
cursor  cursor_a is
select * from student;

v_sid student.sid%TYPE;
v_sname student.sname%TYPE;
v_age student.sage%TYPE;
v_ssex student.ssex%TYPE;
begin
--打开游标
open cursor_a;
--提取游标
--使用游标
loop
fetch cursor_a into v_sid,v_sname,v_age,v_ssex;

exit when cursor_a%notfound;
Dbms_Output.put_line('6666:'||v_sname||v_sid||v_age||v_ssex);
end loop;
--关闭游标
close cursor_a;
end;


--1.计算所有员工实际发放的工资 逐一输出
declare 
--定义游标
cursor  cursor_Work is
select * from work;

v_wid work.wid%TYPE;
v_wname work.wname%TYPE;
v_waddress work.waddress%TYPE;
v_wage work.wage%TYPE;
v_wmoney work.wmoney%TYPE;
v_flag work.flag%TYPE;
v_wdate work.wdate%TYPE;
v_jdate work.jdate%TYPE;
begin
--打开游标
open cursor_work;
--提取游标
--使用游标
loop
fetch cursor_work into v_wid,v_wname,v_waddress,v_wage,v_wmoney,v_flag,v_wdate,v_jdate;

exit when cursor_work%notfound;
if v_flag<1 then
Dbms_Output.put_line('您的实际工资是:'||v_wmoney*0.8);
else
Dbms_Output.put_line('您的实际工资是:'||v_wmoney);
end if;
end loop;
--关闭游标
close cursor_work;
end;



存储过程

什么是存储过程?

存储过程相当于封装了一个方法 

--存储过程相当于封装了一个方法
--先声明
create procedure add_student(
 sid student.sid%TYPE,
 sname student.sname%TYPE,
 sage student.sage%TYPE,
 ssex student.ssex%TYPE,
 out_flag out number --out代表存储过程的返回值
) 
is 

begin
insert into student values (sid,sname,sage,ssex);
out_flag:=1;
commit;

end;
--调用存储过程

declare 
flag number;
begin
add_student(1114,'小红',23,'女',flag);
dbms_output.put_line(flag);
end;

小练习 

elect wid,wname,waddress,wage,wmoney,flag,wdate, TRUNC(MONTHS_BETWEEN (SYSDATE,jdate)/12)as a   from work;
select * from work
--1.计算所有员工实际发放的工资 逐一输出
declare 
--定义游标
cursor  cursor_Work is
select * from work;

v_wid work.wid%TYPE;
v_wname work.wname%TYPE;
v_waddress work.waddress%TYPE;
v_wage work.wage%TYPE;
v_wmoney work.wmoney%TYPE;
v_flag work.flag%TYPE;
v_wdate work.wdate%TYPE;
v_jdate work.jdate%TYPE;
begin
--打开游标
open cursor_work;
--提取游标
--使用游标
loop
fetch cursor_work into v_wid,v_wname,v_waddress,v_wage,v_wmoney,v_flag,v_wdate,v_jdate;

exit when cursor_work%notfound;
if v_flag<1 then
Dbms_Output.put_line('您的实际工资是:'||v_wmoney*0.8);
else
Dbms_Output.put_line('您的实际工资是:'||v_wmoney);
end if;
end loop;
--关闭游标
close cursor_work;
end;

--2.给转正的员工 工资加200 给转正了的并且转正年份大于3年的加400 给转正了的并且转正年限大于5年的加1000
declare 
--定义游标
cursor  cursor_Work is
select * from work;

v_wid work.wid%TYPE;
v_wname work.wname%TYPE;
v_waddress work.waddress%TYPE;
v_wage work.wage%TYPE;
v_wmoney work.wmoney%TYPE;
v_flag work.flag%TYPE;
v_wdate work.wdate%TYPE;
v_jdate work.jdate%TYPE;
begin
--打开游标
open cursor_work;
--提取游标
--使用游标
loop
fetch cursor_work into v_wid,v_wname,v_waddress,v_wage,v_wmoney,v_flag,v_wdate,v_jdate;

exit when cursor_work%notfound;
if v_flag>0 and TRUNC(MONTHS_BETWEEN (SYSDATE,v_jdate)/12)>=5  then
Dbms_Output.put_line('您的实际工资是:'||(v_wmoney+1600));
elsif v_flag>0 and  TRUNC(MONTHS_BETWEEN (SYSDATE,v_jdate)/12)>=3 then
Dbms_Output.put_line('您的实际工资是:'||(v_wmoney+600));
else
Dbms_Output.put_line('您的实际工资是:'||v_wmoney*0.8);
end if;
end loop;
--关闭游标
close cursor_work;
end;

--3.开除在公司工作一年还未转正的
declare 
flag number;
--定义游标
cursor  cursor_Work is
select * from work;
v_wid work.wid%TYPE;
v_wname work.wname%TYPE;
v_waddress work.waddress%TYPE;
v_wage work.wage%TYPE;
v_wmoney work.wmoney%TYPE;
v_flag work.flag%TYPE;
v_wdate work.wdate%TYPE;
v_jdate work.jdate%TYPE;
begin
--打开游标
open cursor_work;
--提取游标
--使用游标
loop
fetch cursor_work into v_wid,v_wname,v_waddress,v_wage,v_wmoney,v_flag,v_wdate,v_jdate;

exit when cursor_work%notfound;
if v_flag=0 and TRUNC(MONTHS_BETWEEN (SYSDATE,v_jdate)/12)>=1 then 
delete from work where wid=v_wid;
Dbms_Output.put_line('删除成功!');
else
Dbms_Output.put_line('');
end if;
end loop;
--关闭游标
close cursor_work;
end;

--4.创建一个储存过程：能够根据输入的姓名及关键字 就能查出并返回这个人的实发工资
--存储过程相当于封装了一个方法
--先声明
create procedure add_workss(
 a_wname work.wname%TYPE,
  out_flag out number
) 
is 

begin
select wmoney into out_flag   from work  where wname like  concat(a_wname,'%');

end;
--调用存储过程
declare 

flag number;
begin
add_workss('申',flag);
dbms_output.put_line(flag);

end;
--5.创建一个存储过程：能够根据输入的年份以及年龄 查询返回对应人员跟实发工资
--存储过程相当于封装了一个方法
--先声明
create procedure add_workss(
 a_wage work.wage%TYPE,
 a_wdate work.wdate%TYPE,
 out_flag out number, --out代表存储过程的返回值
 out_name out  nvarchar2
)
is
begin
select wmoney,wname into out_flag,out_name  from work where wdate=a_wdate and wage=a_wage;
commit;

end;

--调用存储过程
declare 

flag number;
name varchar2(10);
begin
add_workss(17,to_date('2022-2-18','yyyy/mm/ss'),flag,name);
dbms_output.put_line(flag);

end;


版权声明：本文为weixin_50098749原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。