Oracle数据库(整合版)

Abcdzzr

已于 2022-02-20 15:42:38 修改

阅读量738

点赞数

文章标签： oracle 数据库 dba

于 2022-02-20 15:41:50 首次发布

本文链接：https://blog.csdn.net/Abcdzzr/article/details/123030443

版权

1.Oracle11g安装

2.安装Oracle11g可视化操作界面(plsqldev.exe)

1.Oracle11g安装

官方网站下载地址：

Oracle Database 11g 第 2 版 JDBC 驱动程序下载 | Oracle 中国

2.安装Oracle11g可视化操作界面(plsqldev.exe)

PLSQL Developer下载-PLSQL Developer官方版下载[电脑版]-华军软件园

找到plsqldev.exe程序添加桌面快捷方式运行即可

3.查看Oracle服务是否开启

win+r services.msc
下图这三个服务必须开启，其他4个服务建议关闭，占用cpu资源

4.修改普通登录用户(scott)

win+r cmd
sqlplus
账号：system
密码：123123 这里的密码为自己安装数据库时设置的口令
查询所有表格：select * from user_tables;
修改普通用户：alter user scott identified by 123123 account unlock;
用修改好的scott用户登录plsqldev

5.sql的分类

DML：Data Manipulation Language 数据操纵语言
DDL：Data Definition Language 数据定义语言
DCL：Data Control Language 数据库控制语言
DQL：Data Query Language 数据库查询语言

命令分类操作：

分类	描述	命令
`DDL`	数据定义语言	create：创建；drop：删除；alter：修改；rename: 重命名; truncate：截断
`DML`	数据操作语言	insert：插入；delete：删除；update：更新；select：查询
`DCL`	数据控制语言	grant：授权；revoke：回收权利；commit:提交事务；rollback：回滚事务

6.Oracle分页查询

rownum是一个动态增长的列,需要先查出此列在进行分页操作

--加上for update就可以修改表
select * from student for update
--oracle 中增删改必须提交事务
insert into student values(6,'李八',18);
rollback;
insert into student values(7,'李九',18);
commit;
--分页
select * from 
(select student.*,rownum rn from student)
where rn>2 and rn<6

7.DQL快速上手练习题

                创建两张表：
        Employee 员工
        eid
        ename
        ebrithday(出生日期)
        eaddress
        did

        Dept  部门
        did
        dname
        dsalary(工资)

要求：以合适的类型创建好这两张表
并且每张表插入至少五条数据 Employee插入至少十条

查询所有员工的基本信息按照工号排序
```
select * from employee ORDER BY eid desc
```

查询“财务部”的岗位工资

select * from dept where dname='财务部'

查询财务部下有哪些员工

select * from dept where dname='财务部'

查询比张三工资高的员工信息

select * from employee e
inner join dept d on(e.did=d.did)
where d.dsalary>(select dsalary from dept where did=(select did from employee where ename='张三'))

查询每个部门有多少员工从多到少排列

select d.dname,count(*) as 人数 from dept d
inner join employee e on(e.did=d.did)
group by d.dname
ORDER BY count(*)desc

查询本公司一年总共支出多少工资

select sum(12*d.dsalary) from dept d
inner join employee e on(e.did=d.did)

查询公司薪资最高的前三名

select * from (select e.ename,max(d.dsalary) from dept d
inner join employee e on(e.did=d.did)
group by e.ename
ORDER BY max(d.dsalary) desc) where rownum<4

查询公司薪资最高的第5到8名

select * from(select ee.*,rownum as rn from (select e.ename,max(d.dsalary) from dept d
inner join employee e on(e.did=d.did)
group by e.ename
ORDER BY max(d.dsalary) desc) ee)
where rn>4 and rn<9

查询年龄比李四大的员工信息

select * from employee 
where ebrithday<(select ebrithday from employee where ename='李四')

查询今年年满30岁的员工信息

SELECT
	ename,TRUNC( ( to_char( SYSDATE, 'yyyyMMdd' ) - to_char( ebrithday, 'yyyyMMdd' ) ) / 10000 ) AS age 
FROM
	employee
  where TRUNC( ( to_char( SYSDATE, 'yyyyMMdd' ) - to_char( ebrithday, 'yyyyMMdd' ) ) / 10000 )>30

8.DCL/DDL操作

8.1DCL

1.创建用户赋予权限(users)

--用户操作(users)
--赋予权限
--赋予用户登录权限
grant connect,resource to test1;
--赋予具体的实体操作
--select ，update ，delete ，insert
grant all on scott.student to test1;

8.2DDL

1.创建表空间(tablespaces)

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

--删除
drop tablespace tp_a
including contents;

2.索引操作(indexes)

--索引
  --必须建立在字段上
  --一定要建立在大数据量的基础上才有用
  --主键索引 唯一索引 反向键索引 位图索引 大写函数索引
  --创建唯一索引
  create unique index index_u_sname
  on student (sname)
  --创建反向键索引
   create index index_u_sid
  on student (sid) reverse;
  --创建位图索引
   create bitmap index_u_sid
  on student (sid);
  --针对函数建立索引
    create index index_u_sid
    on student (UPPER(sid));
  --删除索引
  drop index index_u_sid;

3.同义词(Synonyms)

--同义词(Synonyms)
  --意思就是给表取一个全局的别名
  --先要在用system登录创建同义词
  select * from student s
  select * from s;

4.序列操作(sequences)

--序列(sequences)
  --是一个能够自增长的列,序列是独立存在的
  --创建序列
  create sequence seq_1
  minvalue 1      --最小值
  maxvalue 1000   --最大值
  start with 1    --开始于
  increment by 1; --增量
--查看序列，先要.nextval
  select seq_1.nextval from dual;
  --使用序列
  insert into students values(seq_1.nextval,'小明',16,'女');

9.PL/SQL编程

--定义区域
declare 
  v_a number(4);  --变量
  v_b varchar2(11);
  c_c constant number(5):=100;  --常量
  --保持跟列的数据类型一致
  v_d student.sage%type
  v_f student.sname%type
  
begin
  --给变量赋值
  v_a:=10;
  v_b:='hello';
  --chr(10)换行
  --拼接符||
  --输出到控制台Dbms_Output.put_line()
   Dbms_Output.put_line('变量的值是:'||v_b||chr(10)||'年龄为:'||v_a||chr(10)||'常量为:'||c_c);
  select sname,sage into v_b,v_a from student where sid=3;
  Dbms_Output.put_line('变量的值是:'||v_b||'年龄为:'||v_a);
end;


--练习一
declare 
  v_YUWEN number(4);  --变量
  v_SHUXUE number(4);
  v_YINGYU number(4);
  v_sum number(5);
  --c_  --常量
begin
  --给变量赋值
  v_YUWEN:=89;
  v_SHUXUE:=56;
  v_YINGYU:=78;
  v_sum:=v_YUWEN+v_SHUXUE+v_YINGYU;
  Dbms_Output.put_line('平均分是:'||v_sum/3);
end;


--if语句
declare 
  v_d student.sage%type;
  v_f student.sname%type;
begin
  select sname,sage into v_f,v_d from student where sid=3;
  if v_d<18 then
     Dbms_Output.put_line(v_f||'今年:'||v_d||',未成年！');
  elsif v_d<30 then
     Dbms_Output.put_line(v_f||'今年:'||v_d||',青年！');
  elsif v_d<50 then  
     Dbms_Output.put_line(v_f||'今年:'||v_d||',中年！');
  else 
     Dbms_Output.put_line(v_f||'今年:'||v_d||',老年！');  
  end if;
end;


--循环
declare 
  v_d number(4):=100;
begin
  loop
    v_d:=v_d+1;
    Dbms_Output.put_line('今天天气不错！'); 
    insert into student values (v_d,'小白'||v_d,19,'男');
    commit;
    exit when v_d=110;
  end loop;
end;


--switct
declare 
  v_d number(11):=11;
begin
  case 
    when v_d<10 then Dbms_Output.put_line('小于10');
    when v_d<20 then Dbms_Output.put_line('小于20');
    when v_d<30 then Dbms_Output.put_line('小于30');
    when v_d<40 then Dbms_Output.put_line('小于40');
  end case;
end;


--游标（cursor）
  --定义游标
  --打开游标
  --提取游标
  --使用游标
  --关闭游标
declare 
  --定义游标
  cursor cursor_a is 
  select * from student;
  
  v_sid student.sid%TYPE;
  v_sname student.sname%TYPE;
  v_sage student.sage%TYPE;
  v_ssex student.ssex%TYPE;
begin
  --打开游标
  open cursor_a;
  loop
       --提取游标
      fetch cursor_a into v_sid,v_sname,v_sage,v_ssex;
      exit when cursor_a%notfound; 
      --使用游标
      Dbms_Output.put_line(v_sid||v_sname||v_sage||v_ssex);  
  end loop;
  --关闭游标
  close cursor_a;
end;


--存储过程(procedures)
--存储过程相当于封装了一个方法
--先声明
create procedure add_student(
       sid student.sid%type,
       sname student.sname%type,
       sage student.sage%type,
       ssex student.ssex%type
)
is
begin
  insert into student values (sid,sname,sage,ssex);
  --提交事务
  commit;
end;

--定义带返回值存储过程
create procedure add_student2(
       sid student.sid%type,
       sname student.sname%type,
       sage student.sage%type,
       ssex student.ssex%type,
       out_flag out number
)
is
begin
  insert into student values (sid,sname,sage,ssex);
  out_flag:=1;
  --提交事务
  commit;
end;


--调用
call add_student(8,'小蓝',23,'男');
--指定参数类型
call add_student(sname=>'小紫',sid => 9,ssex=>'女',sage => 19);


--有返回值的存储过程必须这样调用
declare
  flag number;
begin
  add_student2(14,'小红',32,'女',flag);
  Dbms_Output.put_line(flag);
end;


--删除存储过程
drop procedure add_student;

10.PL/SQL编程练习题

        1.创建一张工人表work
            wid wname waddress wage wmoney(薪资)
            flag(是否转正 0：未转正 1：转正)
            wdate(转正年份) gdate(入职年份)
        己知未转正员工工资只能发80%

2.结算所有员工实际发放的工资逐一输出

--2.结算所有员工实际发放的工资 逐一输出
declare 
 --定义游标
  cursor cursor_a is 
  select wid,wname,wmoney,flag from work;
  
  v_wid work.wid%type;
  v_wname work.wname%type;
  v_wmoney work.wmoney%type;
  v_flag work.flag%type;
  v_moneys number;
begin
  open cursor_a;
  loop
    fetch cursor_a into v_wid,v_wname,v_wmoney,v_flag;
    exit when cursor_a%notfound; 
    if v_flag=0 then
       v_moneys:=v_wmoney*0.8;
       Dbms_Output.put_line(v_wname||'的实发工资为:'||v_moneys);
    else 
       Dbms_Output.put_line(v_wname||'的实发工资为:'||v_wmoney);
    end if;
  end loop;
end;

        3.给转正了的员工工资加200
         给转正了的员工并且转正年限大于3年的加400
         给转正了的员工并且转正年限大于5年的加1000

--3.给转正了的员工工资加200
   --给转正了的员工 并且转正年限大于3年的加400
   --给转正了的员工 并且转正年限大于5年的加1000
declare 
 --定义游标
  cursor cursor_a is 
  select wid,wname,wmoney,flag,gdate from work;
  
  v_wid work.wid%type;
  v_wname work.wname%type;
  v_wmoney work.wmoney%type;
  v_flag work.flag%type;
  v_gdate work.gdate%type;
  v_year number;
begin
  open cursor_a;
  loop
    fetch cursor_a into v_wid,v_wname,v_wmoney,v_flag,v_gdate;
    exit when cursor_a%notfound; 
    if v_flag=1 then
       v_year:= trunc(months_between(sysdate,v_gdate)/12);      
        if v_year>5 then
         update work set wmoney=v_wmoney+1000 where wid=v_wid; commit;
        elsif v_year>3 then 
         update work set wmoney=v_wmoney+400 where wid=v_wid; commit;
        else 
         update work set wmoney=v_wmoney+200 where wid=v_wid; commit;
        end if;    
    end if;
  end loop;
end;

4.开除在公司工作一年还未转正的

--4.开除在公司工作一年还未转正的
declare 
 --定义游标
  cursor cursor_a is 
  select wid,wname,wmoney,flag,gdate from work;
  
  v_wid work.wid%type;
  v_wname work.wname%type;
  v_wmoney work.wmoney%type;
  v_flag work.flag%type;
  v_gdate work.gdate%type;
  v_year number;
begin
  open cursor_a;
  loop
    fetch cursor_a into v_wid,v_wname,v_wmoney,v_flag,v_gdate;
    exit when cursor_a%notfound; 
    if v_flag=0 then
       v_year:= trunc(months_between(sysdate,v_gdate)/12);      
        if v_year>=1 then
         delete from work where wid=v_wid; commit;
        end if;    
    end if;
  end loop; 
end;

5.创建一个存储过程：能够根据输入的姓名关键字
就能查出并返回这个人的实发工资

--5.创建一个存储过程 ：能够根据输入的姓名关键字 
  --就能查出并返回这个人的实发工资
--定义带返回值存储过程
create procedure select_work(
       w_name work.wname%type,      
       out_flag out number
)
is
  v_flag number;
  v_wmoney number;
begin
  --用concat关键字拼接% 
  select wmoney,flag into v_wmoney,v_flag  from work where wname = w_name;--like concat(concat('%',wname),'%');
  if v_flag=0 then
     out_flag:=(v_wmoney*0.8);
  else
     out_flag:=v_wmoney; 
  end if; 
end;  

--有返回值的存储过程必须这样调用
declare
  money number;
begin
  select_work('小橙',money);
  Dbms_Output.put_line(money);
  --SELECT * FROM WORK where wname LIKE '%橙%'
end;

6.创建一个存储过程：能够根据输入的年份以及年龄
就能查出并返回对应的人员跟实发工资

--5.创建一个存储过程 ：能够根据输入的年份以及年龄
  --就能查出并返回对应的人员跟实发工资
  create procedure select_work2(
       wgdate work.wdate%type, 
       w_age work.wage%type,
       out_wname out work.wname%type,     
       out_flag out number
)
is
  v_flag number;
  v_wmoney number;
  v_wage work.wage%TYPE;
begin
  select wmoney,flag,wname,wage into v_wmoney,v_flag,out_wname,v_wage from work 
  where gdate = wgdate
  and wage=w_age;
 
  if v_flag=0 then
     out_flag:=(v_wmoney*0.8);
  else
     out_flag:=v_wmoney; 
  end if; 
end;
--有返回值的存储过程必须这样调用
declare
  money number;
  v_wname work.wname%type;
  v_date work.gdate%type;
begin
  v_date:=to_date('20170215','yyyyMMdd');
  select_work2(v_date,21,v_wname,money);
  Dbms_Output.put_line(money||v_wname);
  --SELECT * FROM WORK where wname LIKE '%橙%'
end;  
  
  --select * from work where gdate = to_date('20170215','yyyyMMdd')
  --and trunc(months_between(sysdate,gdate)/12)=5;

Abcdzzr

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
打赏
0
评论
Oracle数据库(整合版)

目录1.Oracle11g安装2.安装Oracle11g可视化操作界面(plsqldev.exe)3.查看Oracle服务是否开启4.修改普通登录用户(scott)5.sql的分类6.Oracle分页查询7.DQL快速上手练习题8.DCL/DDL操作 8.1DCL 8.2DDL9.PL/SQL编程10.PL/SQL编程练习题1.Oracle11g安装官方网站下载地址：Oracle Database 11g 第 2 ...
复制链接

扫一扫