Oracle 总结

Oracle部署

局域网内部环境使用：虚拟机还原到  系统安装完毕  快照

分步部署和一键部署：

1. 部署本地 yum 源

   rm -rf /etc/yum.repos.d/*
   
   curl -o /etc/yum.repos.d/CentOS-7.repo http://192.168.2.251/Centos/CentOS7.repo
   
   yum clean all
   
   yum makecache

2. 安装必须的安装包

   yum install -y libaio bc flex net-tools

3. 安装Oracle

   rpm -ivh http://192.168.2.251/Centos/oracle/oracle-xe-11.2.0-1.0.x86_64.rpm

4. 配置Oracle
   1. Oracle Application Express 的有效 HTTP 端口（默认值为 8080）
   2. Oracle 数据库侦听器的有效端口（默认为 1521）
   3. SYS 和 SYSTEM 管理员账号的密码：123456
   4. 确认 SYS 和 SYSTEM 管理员账号的密码：123456
   5. 数据库在计算机启动时自动启动（下次重新引导）

      echo -e "\n\n123456\n123456\ny" | /etc/init.d/oracle-xe configure

5. 环境变量配置

   cat >> .bash_profile << EOF
   . /u01/app/oracle/product/11.2.0/xe/bin/oracle_env.sh
   EOF

   让环境变量生效

   su -

6. 允许远程访问 Oracle 11g XE GUI
   1. 进入SQL命令窗口

      sqlplus "sys/123456 as sysdba" <<EOF
      EXEC DBMS_XDB.SETLISTENERLOCALACCESS(FALSE);
      EOF

7. 直接关闭防火墙

   systemctl stop firewalld
   systemctl disable firewalld

   可通过下面网址访问确认安装成功： http://<server-ip>:8080/apex/f?p=4950:1  用户名： system  密码： 123456 

8. 可以直接一键部署（不用上面的1-7步）

   rm -rf /etc/yum.repos.d/*
   curl -o /etc/yum.repos.d/CentOS-7.repo http://192.168.2.251/Centos/CentOS7.repo
   yum clean all
   yum makecache
   yum install -y libaio bc flex net-tools
   rpm -ivh http://192.168.2.251/Centos/oracle/oracle-xe-11.2.0-1.0.x86_64.rpm
   echo -e "\n\n123456\n123456\ny" | /etc/init.d/oracle-xe configure
   cat >> .bash_profile << EOF
   . /u01/app/oracle/product/11.2.0/xe/bin/oracle_env.sh
   EOF
   su -
   sqlplus "sys/123456 as sysdba" <<EOF
   EXEC DBMS_XDB.SETLISTENERLOCALACCESS(FALSE);
   EOF
   systemctl stop firewalld
   systemctl disable firewalld

    

Oracle语法总结

数据定义语言DDL：create，alter，drop

数据操纵语言DML：insert，update，delete，select

数据查询语言DQL：order by，group by

数据控制语言DCL：grant，revoke

事物控制语言TCL：commit，rollback，savepoint

 

语法结构-用户操作

创建用户

create user 用户名 identified by 口令 [account lock|unlock];

ps：create user tom identified by tom account unlock;

 

修改用户密码

alter user 用户名 identified by 新密码;

ps：alter user tom identified by 123456;

 

修改用户锁定状态

alter table 用户名 account lock|unlock;

 

删除用户

drop user 用户名 [cascade];

cascade：要是用户不是空的，用 cascade 删除。空的不需要。

 

权限：角色

授予权限：

1. connect：连接
2. resource：资源
3. dba：管理员

grant 角色|权限 to 用户|角色;

ps：grant connect,resource to tom;

 

撤销权限：

revoke 角色|权限 from 用户|角色;

 

语法结构-表操作

创建表

create table 表名(
　　列名　类型　[null|not null] [constraint],
　　列名　类型
);

ps：
　　create table test(
　　　　name varchar2(6),
　　　　sex char(4),
　　　　age number(3)
　　);

 

修改表名

rename 原表名 to 新表名;

ps：rename test to test10;

 

添加列

添加列：
alter table 表名 add 列名 列类型 列约束;
ps:alter table test10 add birthday date;

添加多列：
alter table 表名 add (列名 列类型 列约束,列名 列类型 列约束,...);

 

删除列

alter table 表名 drop column 列名;

ps:alter table test10 drop column name;

 

添加约束

alter table 表名 add constraint 约束名 约束内容;

ps：创建表的同时添加约束
　　create table infos(
       id char(5) primary key, -- 主键约束
       stu_id char(5) unique, -- 唯一约束
       name char(10) not null, -- 非空约束
       sex char(3) check(sex='男' or sex='女'), -- 检查约束
       address char(50) default '地址不详' -- 默认约束
　　);

ps：先创建表再加约束
　　create table scores (
       id varchar2(7),
       s_id char(5),
       score number(4, 1)
　　);

-- 主键约束
　　alter table scores
　　add constraint pk_scores_id primary key(id);

-- 唯一约束
　　alter table socres
　　add constraint u_scores_s_id unique(s_id);

-- 检查约束
　　alter table scores
　　add constraint ck_scores_score check(score>=0 and score<=100);

-- 外键约束
　　alter table scores
　　add constraint fk_scores_s_id
    　　foreign key(s_id) references infos(stu_id);

-- 非空约束
　　alter table scores modify score not null;
-- 默认约束
　　alter table scores modify score default 0;

 

删除约束

alter table 表名 drop constraint 约束名;

ps：alter table scores drop constraint fk_scores_s_id;

 

语法结构：查询操作

基本查询：单表查询

select *|列名|表达式
from 表名
where 条件
order by 列名;　　-- 对结果排序：asc，desc

ps：查询所有列
　　select * from dept;

ps：查询指定列
　　select deptno,loc from dept;

ps：使用别名
　　select d.deptno,d.dname from dept d;
　　select d.deptno 部门编号,d.dname as 部门名称 from dept d;

ps：
　　select * from emp e
　　where e.sal > 2000
　　order by e.sal desc;

 

运算符

运算符优先级：

　　算数运算符 > 连接运算符 > 关系运算符 > is null | like | in > between > not > and > or　（记不住就用小括号）

• 算术运算：+，-，*，/
• 关系运算：=，>，<，!=，>=，<=
• 逻辑运算：and or not
• 拼接字符串：
  • Access、Microsoft SQL server：使用 +
  • DB2、Oracle、SQLite：使用 ||
  • MySQL：无
  • 最好使用函数拼接

ps：
　　select e.ename,e.job,e.sal,e.sal+200,e.sal * 2
　　from emp e
　　where e.sal > 3000 or e.job = '经理';

ps：
　　select e.ename,e.sal,'我爱' || e.ename || '带上工资' || e.sal || '来吃饭'
　　from emp e
　　where e.sal >= 2500
　　order by e.sal desc;

-- null：空
　　select *
　　from emp e
　　where e.comm is null;    -- 非空：not null

-- in
　　select *
　　from emp e
　　where e.job = '经理' or e.job = '普通员工';
    　　可替换为：
　　where e.job in('经理','普通员工');    -- 非空：not in()

-- like
    %：代表0个或多个任意字符
    _：代表一个任意字符
    '/_%' escape '/'：对 _ 进行转义 ，以 _ 开头的字符串
    like '字符串' [escape '字符']
ps：
　　select * from emp e
　　where e.ename like 's%';    -- 查看以s开头的

-- between...and
　　select * from emp e
　　where e.sal >= 1000 and e.sal <= 2000;
   　　 可替换为：
　　where e.sal between 1000 and 2000;

-- distinct
　　select distinct deptno from emp;

 

多表查询

语法总结：

select *
from A
join B             --内连接
     on 表之间关联的条件
right join C             --右外连接
     on 表之间关联的条件
left join D             --左外连接
     on 表之间关联的条件
full join E  --全连接
     on 表之间关联的条件
where 筛选条件
order by 排序列;

 

交叉连接：笛卡尔积：cross join

ps：
    select * from emp,dept;
    select * from emp cross join dept;

内连接：inner join：等值连接

ps1：
    select *
    from emp e, dept d
    where e.deptno = d.deptno;

ps2：
    select *
    from emp e
    inner join dept d
        on e.deptno = d.deptno;

非等值连接

select *
from emp e
inner join salgrade s
      on e.sal between s.losal and s.hisal;

自连接

select e1.empno, e1.ename 员工姓名, e1.mgr 领导编号, e2.ename 领导姓名
from emp e1
inner join emp e2
      on e1.mgr = e2.empno;

外连接：left join，right join

ps：
    select *
    from emp e
    left outer join dept d
         on e.deptno = d.deptno;

ps：
    select *
    from emp e
    right outer join dept d
         on e.deptno = d.deptno
    where e.sal > 3000;

联合查询：union，union all

ps：
    select deptno from emp
    union
    select deptno from dept;

ps：
    select deptno from emp
    union all
    select deptno from dept;

全（满）连接：full join

select *
from emp e
full join dept d
     on e.deptno = d.deptno;

 

子查询

• 单行子查询：最多返回一行结果
• 多行子查询：可以返回多行结果

select *
from emp e
where e.deptno = (
      select d.deptno
      from dept d
      where d.dname = '销售部'
);

 

any和all

any：

　　< any：小于最大的

　　> any：大于最小的

　　= any：等于任意一个

select *
from emp e1
where e1.sal <any (
      select e.sal
      from emp e
      where e.job ='销售员'
);

all：

　　> all：大于最大的

　　< all：小于最小的

select *
from emp e1
where e1.sal >all (
      select e.sal
      from emp e
      where e.job ='销售员'
);

 

case：多分支条件

case
    when 条件 then 语句
    when 条件 then 语句
    else 语句
end;

ps：
　　select e.ename,e.sal,(
　　　　case
　　　　　　when e.sal>3500 then '高工资'
　　　　　　when e.sal>2000 then '中等工资'
　　　　　　else '低等工资'
　　　　end
　　) 工资等级
　　from emp e;

 

根据查询结果创建表

create table 表名 as select查询;

ps：
    create table empinfo as
    select e.ename,e.sal,e.job,e.hiredate
    from emp e
    where e.sal>2000
    order by e.hiredate asc;

 

复制表结构

create table empinfo_1 as
select *
from emp
where 1=2;    -- where条件为假，查找数据为空，只复制表结构

 

数据插入

insert into 表名(列名1,列名2...) values(值1,值2...);

ps：
    insert into empinfo(ename,sal) values('张三',500);

 

将查询结果一次性插入到表中

insert into 表名 select查询;

ps：
    insert into empinfo
    select e.ename,e.sal,e.job,e.hiredate
    from emp e
    where e.sal<2000;

 

更新数据

update 表名 set 列名=值1,列名=值2 where 条件;

ps：
    update empinfo
    set job = '分析师'
    where ename = 'JONES';

当删除列时，该列数据必须为空，即可删除整列数据：

update empinfo
set job = null;

 

删除数据

delete from 表名 where 条件;

ps：
    delete from empinfo
    where sal>2000;


语法结构：清空数据！！！无法回滚，高度危险！！！
truncate table 表名;

 

事务

自己的操作只有自己能看，具有隔离性，所以操作确认的话，commit。不确认的话，回滚。

commit;    -- 提交事务
savepoint    -- 事务保存点
rollback    -- 回滚事务
rollback to savepoint    -- 回滚到事务保存点

ps：
    select *
    from empinfo;

    insert into empinfo
    values('lisi', 200, null, null);

    commit;

    savepoint a;
    update empinfo
    set sal=sal+1
    where ename='lisi';

    savepoint b;
    update empinfo
    empinfo
    set sal=sal+1
    where ename='lisi';

    savepoint c;
    update empinfo
    set sal=sal+1
    where ename='lisi';

    rollback to c;
    rollback to a;

 

函数

• 单行函数
• 聚合函数

数值型函数

abs：绝对值

select abs(100), abs(-100), abs('100') from dual;
　　　　100          100         100

 

ceil：向上取整

select ceil(10), ceil(10.5), ceil(10.4), ceil(-10.4) from dual;
            10         11           11         -10

 

floor：向下取整

select floor(10), floor(10.1), floor(10.5), floor(-10.4) from dual;
　　　　　　10　　　　　　　　10　　　　　　10　　　　　　-11

 

mod：余数

select mod(5, 2), mod(5.5, 2) from dual;
　　　　　　　　1　　　　1.5

 

round：四舍五入

select round(10, 2), round(10.25677, 2), round(10.123), round(10.523)
from dual;
　　　　　　　　　10　　　　　　　　10.26　　　　　　10　　　　　　　　11

 

trunc：截取

select trunc(155.530456),trunc(155.5556, 2)
from dual;
　　　　　　　　155　　　　　　　　　　155.55

 

字符型函数

--concat：拼接
select concat('我', '爱测试'),
          '我' || '爱测试'
from dual;

--initcap：首字母大写
select initcap('this is a test')
from dual;

--upper：大写
select upper('this is a test')
from dual;

--lower：小写
select lower('THIS IS A TEST')
from dual;

--length：长度
select length('this is a 测试')
from dual;

--substr：截取字符串
select substr('this is a 测试', 5, 2),
          substr('this is a 测试', -5, 2)
from dual;

--instr：搜索
select instr('this is a 测试', '测'),
         instr('this is a 测试', 's', -1),
         instr('this is a 测试sss', 's', -2),
         instr('this is a 测试sss', 's', -4)
from dual;

--replace：替换
select replace('this is a 测试', '测'),
         replace('this is a 测试', '测', 'test')
from dual;

 

日期型函数

-- sysdate：当前时间
select sysdate
from dual;

--增加天数
select sysdate + 2,sysdate + 1/24 *2,sysdate + 1/24/60 *2
from dual;

--add_months：增加月
select add_months(sysdate, 2), add_months(sysdate, -2)
from dual;

--last_day：最后一天
select last_day(sysdate)
from dual;

--months_between：月份间隔
select months_between(
       to_date('2019/8/31', 'YYYY/MM/DD'),
       to_date('2018/7/1', 'YYYY/MM/DD')
)
from dual;

--转换函数
--to_char：转换为字符串
select 16.8, 
          to_char(16.8),
          to_char(16.8, '099.99'),
          to_char(16.8, 'L099.99'),
          to_char(123456789, '999,999,999'),
          to_char(sysdate),
          to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS'),
          to_char(sysdate, 'YYYY'),
          to_char(sysdate, 'MM'),
          to_char(sysdate, 'YYYY"年"MM"月"DD"日"')
from dual;

--to_date：转换为日期
select to_date('28-8月 -19'),
          to_date('2019-08-28 16:55:36', 'YYYY-MM-DD HH24:MI:SS')
from dual;

--to_number：转换为数字
select to_number('123456'),
          to_number('123,456', '999,999')
from dual;

--null函数
--nvl：如果第一个参数为null，返回第二个参数
select ename, comm, nvl(comm, 0)
from emp;

--nvl2：如果第一个参数为null，则返回第三个参数，如果不为空，返回第二个参数
select ename, comm, nvl2(comm, 0, 1)
from emp;

--lnnvl：返回除了满足条件之外的数据，包括 null
select *
from emp
where comm < 1000 or comm is null;

select *
from emp
where lnnvl(comm >= 1000);

 

转载于:https://www.cnblogs.com/purewhite/p/11416951.html