Oracle部署
局域网内部环境使用:虚拟机还原到 系统安装完毕 快照
分步部署和一键部署:
- 部署本地 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
- 安装必须的安装包
yum install -y libaio bc flex net-tools
- 安装Oracle
rpm -ivh http://192.168.2.251/Centos/oracle/oracle-xe-11.2.0-1.0.x86_64.rpm
- 配置Oracle
- Oracle Application Express 的有效 HTTP 端口(默认值为 8080)
- Oracle 数据库侦听器的有效端口(默认为 1521)
- SYS 和 SYSTEM 管理员账号的密码:123456
- 确认 SYS 和 SYSTEM 管理员账号的密码:123456
- 数据库在计算机启动时自动启动(下次重新引导)
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 -
- 允许远程访问 Oracle 11g XE GUI
- 进入SQL命令窗口
sqlplus "sys/123456 as sysdba" <<EOF EXEC DBMS_XDB.SETLISTENERLOCALACCESS(FALSE); EOF
- 进入SQL命令窗口
- 直接关闭防火墙
systemctl stop firewalld systemctl disable firewalld
可通过下面网址访问确认安装成功: http://<server-ip>:8080/apex/f?p=4950:1 用户名: system 密码: 123456
- 可以直接一键部署(不用上面的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 删除。空的不需要。
权限:角色
授予权限:
- connect:连接
- resource:资源
- 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);