Oracle基础操作
登录
登录时如果使用sys用户，需要使用 SYS AS SYSDBA 方式进行登录
如果登录Navicat的管理员角色，需要在 高级 - 角色 中选择SYSDBA
Scott用户
-- scott默认密码为tiger
-- 解锁scott
alter user scott account unlock;
-- 解锁密码
alter user scott identified by tiger;
-- 切换到scott --
导入SQL文件
SQL Plus
@D:/test.sql
commit;
创建表空间
-- 创建表空间
create tablespace test
-- 数据文件的所在位置
datafile 'c:/test.dbf'
-- 表空间默认大小
size 100m
-- 允许自动扩展
autoextend on
-- 每次扩展的大小
next 10m;
删除表空间
-- 删除表空间
drop tablespace test
创建用户
-- 创建用户
create user aine
-- 指定密码
identified by 980208
-- 指定表空间
default tablespace test;
为用户授权
-- -- connect：链接角色
-- -- resource：开发者角色
-- -- dba：超级管理员角色
grant CONNECT, RESOURCE, DBA TO AINE;
创建表
create table table1(
uid number(20),
uname varchar2(20)
)
修改表结构
-- 添加列
alter table table1 add(
uage date,
uaddr varchar2
)
-- 修改列的数据类型
alter table table1 modify uage varchar2;
-- 修改列的名称
alter table table1 rename column uaddr to uaddress;
-- 删除列
alter table table1 drop column uage;
记录操作
-- 插入一条语句
-- -- 添加部分列就必须写明添加那些列
insert into table1 (uid,uname) value(123,'小黑');
insert into table1 (uid,uname) value(124,'小花');
-- -- 必须手动提价，否则会出现脏数据
commit;
-- 查询
select * from person;
-- 修改一条语句
update table1 set uname = '小白' where uid = 123;
commit;
-- 删除记录
-- -- 删除表中所有数据
delete from table1;
-- -- 删除表结构
drop table table1;
-- -- 先删除表，在再创建表（相当于删除表中的所有记录）
-- -- 数据量大的情况下（或存在索引），此方法效率高
truncate table table;
序列
-- 创建序列
-- -- 默认从1开始，一般用于主键
-- -- 【increment by n】：每次走n个数
-- -- 【start with n】：从n开始
-- -- 【{maxvalue/minvalue n|nomaxvalue}】：最大最小值
-- -- 【{cache n}】：添加一个缓存
create sequence s_table1;
-- 获取序列的下一个值
-- dual是一个虚表，仅为了补全语法，没有意义；类似于python中的pass
select s_table1.nextval from dual;
-- 获取序列的当前值
-- -- 如果没有使用过序列，会产生oracle-08002错误
select s_table1.currval from dual;
-- 插入语句可以使用序列
insert into table1 (uid,uname) value(s_table1.nextval,'小黄');
commit;
查询
单行函数
-- 使用scott用户进行演示
-- ------------------------------------------
-- 字符函数
-- ------------------------------------------
-- -- -- 小写转大写
select upper('hello') from dual;
-- -- -- 大写转小写
select lower('HELLO') from dual;
-- ------------------------------------------
-- -- 数值函数
-- ------------------------------------------
-- -- -- 四舍五入
-- -- -- 后面的数值表示保留的位数
select round(20.188,1) from dual;
-- -- -- 直接截取
select trunc(10.688,1) from dual;
-- -- -- 求余
select mod(10,3) from dual;
-- ------------------------------------------
-- -- 日期函数
-- ------------------------------------------
-- 查询入职日期到当前几天
select sysdate-e.HIREDATE from emp e;
-- 查询入职日期到当前几周
select round((sysdate-e.HIREDATE)/7) from emp e;
-- 查询入职日期到当前几月
select months_between(sysdate,e.HIREDATE) from emp e;
-- 查询入职日期到当前几年
select months_between(sysdate,e.HIREDATE)/12 from emp e;
-- 查询明天当前时刻的时间
select sysdate+1 from dual;
-- ------------------------------------------
-- -- 转换函数
-- ------------------------------------------
-- 格式化日期（日期转字符串）
-- -- 不区分大小写，因此分钟需要用mi来表示
-- -- 包含前导零
select to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual;
-- 格式化日期（日期转字符串）
-- -- 不包含前导零(fm)
-- -- 24小时(在hh后添加24)
select to_char(sysdate,'fm yyyy-mm-dd hh24:mi:ss') from dual;
-- 格式化日期（字符串转日期）
-- -- 不区分大小写，因此分钟需要用mi来表示
-- -- 包含前导零
select to_date('2021-01-18 01:43:22','yyyy-mm-dd hh:mi:ss') from dual;
-- 格式化日期（字符串转日期）
-- -- 不包含前导零(fm)
-- -- 24小时(在hh后添加24)
select to_date('2021-01-18 01:43:22','fm yyyy-mm-dd hh24:mi:ss') from dual;
-- ------------------------------------------
-- -- 通用函数
-- ------------------------------------------
-- 去除null值(nvl()如果是null，则用第二个参数代替)
select e.sal*12 + nvl(e.comm, 0) from emp e;
条件表达式
--------------------------------------------------
-- 通过条件表达式别名（通用写法）
--------------------------------------------------
-- else 可以省略，如果省略则为空
-- when then 可以写多个
select e.ename,
case e.ename
when 'SMITH' then 'Name1'
when 'ALLEN' then 'Name2'
else 'Name3'
end
from emp e;
--------------------------------------------------
-- 通过条件表达式获取输出（通用写法）
--------------------------------------------------
select e.sal,
case
when e.sal>3000 then 'Good'
when e.sal>1500 then 'Ok'
else 'All Right...'
end
from emp e;
--------------------------------------------------
-- 通过条件表达式获取输出（Oracle写法）
--------------------------------------------------
-- Oracle中除了起别名外，都适用单引号（起别名也可以不加引号）
select e.ename,
decode(e.ename,
'SMITH', 'Name1',
'ALLEN', 'Name2',
'Name3') 别名
from emp e;
多行函数
-- 也称为聚合函数
-- 一般用count(1)而不是count(*)
-- 查询总数
select count(1) from emp;
-- 查询总和
select sum(sal) from emp;
-- 查询最大值
select max(sal) from emp;
-- 查询最小值
select min(sal) from emp;
-- 查询平均值
select avg(sal) from emp;
分组查询
-- 查询出每个部门的平均工资
-- 注意，在分组查询中，只有在group by 后面的列
-- 才可以放在select中（也可以出现在聚合函数中）
-- 因为聚合函数可以把多行记录变成一个值
select e.DEPTNO, avg(e.sal)
from emp e
group by e.DEPTNO
-- 查询平均工资高于2000的部门
-- 注意，所有条件都不能以别名来进行判断
-- 因为where/having的优先级高于select
select e.DEPTNO, avg(e.sal)
from emp e
group by e.DEPTNO
having avg(e.sal) > 2000
-- 查询每个部门中工资高于800的员工的平均工资
-- where用于过滤分组前的数据，having用于过滤分组后的数据
-- where必须在group by之前，having必须在group by之后
select e.DEPTNO, avg(e.sal)
from emp e
where e.sal > 800
group by e.DEPTNO
-- 查询每个部门中工资高于800的员工的平均工资
-- 然后查询平均工资高于2000的部门
select e.DEPTNO, avg(e.sal)
from emp e
where e.sal > 800
group by e.DEPTNO
having avg(e.sal) > 2000;
多表查询
-- 笛卡尔积
select * from emp e,dept d;
-- 等值连接
select * from emp e,dept d where e.DEPTNO = d.DEPTNO;
-- 内连接
-- 和等值连接的结果完全一致
select * from emp e inner join dept d on e.DEPTNO = d.DEPTNO;
-- 右外连接
-- 查询出所有的部门和部门下员工的信息（以部门为准）
select * from emp e right join dept d on e.DEPTNO = d.DEPTNO;
-- 左外连接
-- 查询所有员工信息和员工所属部门（以员工为准）
select * from emp e left join dept d on e.DEPTNO = d.DEPTNO;
-- oracle 右外
select * from emp e ,dept d where e.DEPTNO(+) = d.DEPTNO;
-- oracle 左外
select * from emp e ,dept d where e.DEPTNO = d.DEPTNO(+);
-- 自连接
-- 查询每个员工的姓名和其领导的姓名
select e1.ENAME,e2.ENAME
from emp e1, emp e2
where e1.mgr = e2.empno;
-- 查询每个员工的姓名、部门和其领导的姓名、部门
select e1.ENAME, e2.ENAME, d1.DNAME, d2.DNAME
from emp e1, emp e2, dept d1, dept d2
where e1.mgr = e2.empno
and e1.DEPTNO = d1.DEPTNO
and e2.DEPTNO = d2.DEPTNO;
子查询
-- 子查询
-- 返回一个值
-- 查询和SCOTT工资相同的员工信息
select *
from emp
where sal in
(select sal from emp where ENAME = 'SCOTT');
-- 返回集合
-- 查询和10号部门任意员工一样的员工信息
select *
from emp
where sal in (select sal from emp where deptno = 10);
-- 返回表
-- 查询出每个部门最低工资的员工姓名，部门名称
select e.ENAME, d.DNAME, t.msal
from (select DEPTNO, min(sal) msal from emp group by deptno) t,
emp e,
dept d
where t.DEPTNO = d.DEPTNO
and t.msal = e.sal
and e.DEPTNO = d.DEPTNO;
分页
-- 分页查询
-- 查询emp表中工资的倒序排列（每页五条，取第二页）
select *
from (
select rownum r, e.*
from (
select *
from emp
order by sal desc
) e
where rownum < 11
)
where r > 5
-- 也可以这样写
-- 不能做order by操作
select *
from (select rownum r, emp.* from emp) b
where b.r > 5
and b.r < 11
对象
视图
视图就是提供一个查询的窗口，所有的数据来源都是原表
创建视图必须要有DBA的权限
-- 先通过查询创建一个表
create table emp as
select *
from scott.emp;
-- 创建视图
create view v_emp as
select ename, job
from emp;
-- 查询视图
select *
from v_emp;
-- 修改视图
-- -- 修改视图会修改原表中的数据（视图中不存放数据）
update v_emp
set job='CLERK'
where ename = 'ALLEN';
commit;
-- 创建只读视图
create view v_emp_r as
select ename, job
from emp
with read only;
-- 视图的作用
-- -- 视图可以屏蔽敏感字段
-- -- 保证总部和分部的数据及时统一
索引
索引是列上构造的一个二叉树，用于大幅度提高查询效率，但同时也会影响增删改的效率。
-- 创建一个单列索引
-- 单列索引触发条件：索引列中的原始值（非单行函数、模糊查询...）
create index idx_ename on emp (ename);
-- 使用
select * from emp where ename = 'scott';
-- 创建一个复合索引
-- 复合索引触发条件：复合索引中的第一列为优先检索值（必须包含优先检索列中的原始值）
create index idx_ename_job on emp (ename, job);
-- 使用
-- -- 触发复合索引
select * from emp where ename='scott' and job='xx';
-- -- 触发单列索引
select * from emp where ename='scott';
-- -- 不会触发索引
select * from emp where ename='scott' or job='xx';
编程
PL/SQL
Pl/Sql是对SQL的扩展，使SQL具有过程化编程的特性，主要用于编写存储过程和存储函数。
声明方法
declare
-- 变量定义的方法非常类似于Go中定义变量（但不是完全一致）
i number(2) := 10;
-- 定义一个字符串
s varchar(10) := '嘿嘿嘿';
begin
dbms_output.put_line(i);
dbms_output.put_line(s);
end;
96
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
