目录
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;