Oracle数据库应用实战

演示环境：CentOS 6.9
所用Oracle版本:11g Release 2

Oracle安装

1.软件包下载
http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html
2.帮助文档
http://www.oracle.com/technetwork/database/enterprise-edition/documentation/index.html
安装过程中的百分之八十以上的问题都可以在产品手册中找到答案。有能力的还是建议参考官方手册。

3.预安装环境设置及硬件要求

• [ ] yum源配置OK。光盘的话需要两张镜像都挂载上
• [ ] 设置静态Ip
• [ ] 保证当前主机名能够被解析，即ip地址与主机名写入/etc/hosts
• [ ] oracle安装包database解压并放置到/root下(11g有两个包，按顺序解压)
• [ ] 关闭防火墙与SElinux（或防火墙放行TCP 1521)
• [x] 内存为2G或以上
• [x] 虚拟机建议安装VMware Tools，可将windows上的软件包直接拖拽至Linux虚拟机内。Oracle的命令行安装难度较大，初学者在Linux虚拟机内桌面安装是通常而高效的做法。

4.Oracle安装前脚本

#!/bin/bash
PKG="
unixODBC
unixODBC-devel
binutils
compat-libstdc++-33
elfutils-libelf
elfutils-libelf-devel
elfutils-libelf-devel-static
gcc
gcc-c++
glibc
glibc-common
glibc-devel
glibc-headers
kernel-headers
ksh
libaio
libaio-devel
libgcc
libgomp
libstdc++
libstdc++-devel
make
numactl-devel
sysstat
"
PACKINSTALL=

for PACKAGE in $PKG ;do
    rpm -q $PACKAGE || PACKINSTALL="$PACKINSTALL $PACKAGE"
done
echo
if [ -z "$PACKINSTALL" ];then
   true
  else
   echo "The followling packages will be install: $PACKINSTALL "
   read -p "Continue? (y/N): " answer
   case $answer in
        ([yY]|[Yy][Ee][Ss])
             echo 1
             yum -y install $PACKINSTALL ;;
        (*);;
   esac
fi

cat /etc/group |grep oinstall &> /dev/null || /usr/sbin/groupadd oinstall
cat /etc/group |grep dba &> /dev/null || /usr/sbin/groupadd dba

id oracle &> /dev/null 
    if [ $? = 0 ];then
     groups oracle | grep dba &> /dev/null || /usr/sbin/usermod -g oinstall -G dba oracle
    else
     /usr/sbin/useradd -g oinstall -G dba oracle
     echo oracle |passwd --stdin oracle
    fi

KELNUM=$(cat /etc/sysctl.conf |grep -v ^# |grep -v ^$ |grep -E "fs.aio-max-nr|fs.file-max|kernel.shmall|kernel.shmmax|kernel.shmmni|kernel.sem|net.ipv4.ip_local_port_range|net.core.rmem_default|net.core.rmem_max|net.core.wmem_default|net.core.wmem_max" |awk '{print $1}' |sort -u |wc -l )

if [ $KELNUM -lt 11 ];then
cat >> /etc/sysctl.conf <<EOF
fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 2097152
kernel.shmmax = 536870912
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048586
EOF
/sbin/sysctl -p
fi

grep oracle /etc/security/limits.conf &> /dev/null 
  if [ $? != 0 ];then
cat >> /etc/security/limits.conf << EOF
oracle              soft    nproc   2047
oracle              hard    nproc   16384
oracle              soft    nofile  1024
oracle              hard    nofile  65536
oracle              soft    stack   1024
EOF
 fi

grep pam_limits.so /etc/pam.d/login &> /dev/null
  if [ $? != 0 ];then
cat >> /etc/pam.d/login <<EOF
session required pam_limits.so
EOF
  fi

[ -d /u01/app ] || ( mkdir -p /u01/app/ ; chown -R oracle:oinstall /u01/app/ ; chmod -R 775 /u01/app )

grep -i oracle ~oracle/.bash_profile &> /dev/null
  if [ $? != 0 ];then
cat >> ~oracle/.bash_profile <<EOF
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=\$ORACLE_BASE/11.2.0/db_1
export ORACLE_SID=orcl
export PATH=\$PATH:\$ORACLE_HOME/bin
EOF
  fi

xhost +
[ -d ~oracle/database ] || mv /root/database ~oracle 
cd ~oracle;su - oracle -c 'database/runInstaller'

5.数据库软件图形化页面安装

上面的脚本运行成功之后，会弹出一个以Oracle用户运行的图形页面。
初学者建议先安装软件，等到后面再配置数据库。
过程中可能需要改一些参数，环境检测会有提示，据此操作即可。另外下面这个包可能是必须的。因为Oracle所用的语言为ksh。可能与系统中某个软件包冲突，卸载系统中软件包即可。
编码可以设置为UTF8以支持中文。

[root@cet6 Desktop]# ls
pdksh-5.2.14-30.x86_64.rpm

6.数据库实例安装
初学者需要一个已经存在的数据库供我们练习。
数据库软件安装完成后，就是实例的安装了。

1. 打开一个新终端，su - oracle切换至oracle用户下。
2. lsnrctl start启动监听
3. 运行dbca命令，开始安装数据库
4. 根据提示进行下一步操作
5. Enable Archving开启日志归档

7.测试及数据库页面设置
<1>.Oracle数据库默认历史记录不保存，退格键也无法使用，安装如下软件包可解决这一问题。

[root@cet6 Desktop]# ls
rlwrap-0.37-1.el6.x86_64.rpm

<2>.设置别名(oracle用户家目录下)

echo 'alias sqlplus=" rlwrap sqlplus" '  >> .bashrc

<3>.Oracle页面提示符及编辑器设置

vim /u01/app/oracle/11.2.0/db_1/sqlplus/admin/glogin.sql 
define _editor=vim
set sqlprompt "_USER'@'_CONNECT_IDENTIFIER> "

<4>.登录测试

[oracle@cet6 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sat Dec 9 14:36:03 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS@orcl> 

Oracle查询语句

解锁用户并设置密码(Oracle所带的测试数据库，其用户默认是锁定的，密码也是过期的。直接登录的话会被数据库踢出来。那样需重新连接才能继续执行SQL语句)

SQL> alter user scott identified by tiger account unlock;

切换用户(以下的大多数查询都在scott用户下)

SQL> conn scott/tiger

检索数据SQL语句

查看当前用户下有哪些表

SQL> select * from tab;

查询表的各项列的属性

SQL> desc dept;

查询表内所有信息

SQL> select * from dept;

只查询表内某些列

SQL> select ename,sal from emp;

数值列可跟表达式进行运算

SQL> select ename,sal*12 from emp;

定义显示列的别名

SQL> select ename,sal*12 "Nianxin" from emp;

连接符

SQL> select ename || ' de nian xin shi ' || sal*12 "Nianxin" from emp;

去除重复行

SQL> select distinct sal from emp;

where限制子句

数值型

SQL> select * from emp where empno=7369;

字符型

SQL> select * from emp where ename='SCOTT';

日期型

SQL> select * from emp where hiredate='03-DEC-81';

大于

SQL> select * from emp where sal > 4900;

大于等于

SQL> select * from emp where sal >= 800;

不等于

SQL> select from emp where sal != 800;
SQL> select from emp where sal ^= 800;
SQL> select * from emp where sal <> 800;

表范围(800<=sal<=300)

SQL> select * from emp where sal between 800 and 3000;

或(sal=800或sal=1100)

SQL> select * from emp where sal in (800,1100);

非(sal!=800或sal!=1100)

SQL> select * from emp where sal not in (800,1100);

模糊查询('SCO%'为以SCO开头，后面有任意长度的任意字符)

SQL> select ename from emp where ename like 'SCO%';

空值查询

SQL> select ename,sal,comm from emp where comm is null;

排序（默认从大到小，加desc）

SQL> select ename,sal from emp where deptno=20 order by sal DESC;

交互式输入（替代变量）

SCOTT@orcl> select * from emp where deptno=&bumen;
Enter value for bumen: 30

SCOTT@orcl> set verify off; #不再显示变量接收的过程

查看数据库中有哪些用户（数据字典）

SYS@orcl> select username from dba_users;

函数

切换为sys用户（有些操作在普通用户下不能进行）
SCOTT@orcl> conn / as sysdba

将dba_users表内username项全部转化为小写，再搜索以sco开头的行(不影响原表数据和输出的大小写)

SYS@orcl> select username from dba_users where lower(username) like 'sco%';

切换回scott用户

SYS@orcl> conn scott/tiger;

改变日期输出类型（dual为Oracle自带的，显示结果为当前时间）

SCOTT@orcl> select to_char(sysdate,' year DY fmyyyy-mm-dd hh12:mi:ss AM') from dual;

日期显示为大写

SCOTT@orcl> select upper(to_char(sysdate,'year,month')) from dual;

新建测试表

SCOTT@orcl> create table emptmp as select * from emp;

插入新行

SCOTT@orcl> insert into emptmp (empno, ename,sal) values (8000,'jiake',1000);

定义空值统一显示为某一特定值

SCOTT@orcl> select ename,nvl(hiredate,'01-JAN-80') hiredate from emptmp;

求平均数（因为同时查询了deptno项，则其必须为条件在group by中指明）

SCOTT@orcl> select deptno,avg(sal) from emp group by deptno;

求emp表中deptno（各部门）的平均工资，只显示平均工资大约2000的项，并按平均薪资降序排列(使用了group by就不能再用where了，要用having限定查询显示条件，order by 2值得是查询的第二列，即avg(sal))

SCOTT@orcl> select deptno,avg(sal) from emp group by deptno having avg(sal) > 2000 order by 2 DESC;

多表查询

自然连接（自然连接基于两个表内所有列中有相同的名字）
----nartual;join;using;on
----数据类型不同时用using;
外连接（可以是毫无关系的两表或多表）
----left outer join;right outer join;full outer join
交叉连接(即笛卡尔乘积，除了生成一堆无用数据用来测试外几无用途)
----cross join

1.只返回两个表中匹配条件行的连接，是内连接;
2.两个表的内连接，返回行包含在左（右）表中不匹配的行，就是左（右）外连接(如下例加left则没有领导的员工也会显示，其领导为自己。不加left则没有领导的项不显示);
3.两个表的内连接，返回行包含左表和右表中不匹配的行，就是完全外连接;
4.多表连接多用 join on

从emp中查询每个人的领导姓名（原表中只有领导编号。这里给emp表设定了两个别名，即将emp当作两张不同的表进行查询）

SCOTT@orcl> select x.ename yuangong,nvl(s.ename,'KING') lingdao from emp x left join emp s on (x.mgr=s.empno);

查看工资对应的工资等级

SCOTT@orcl> select e.ename,e.sal,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;

笛卡尔乘积（除了生成一堆无实际数据用来测试外并无其他卵用）

SCOTT@orcl> select ename,dname from emp cross join dept;

自然连接（hr用户下。查询employees与departments中DEPARTMENT_ID相同的列，且departments表中的LOCATION_ID与locations表中的LOCATION_ID相对应，显示符合条件的employees中的first_name，departments表中的department_name中及locations表中的city项。语句的意义是查询某人所在部门及所在部门的城市。）

select e.first_name,d.department_name,l.city from employees e join departments d on ( e.DEPARTMENT_ID=d.DEPARTMENT_ID) join locations l on (d.LOCATION_ID=l.LOCATION_ID);

与上相同，但限定只显示employees表中manager_id为149的行

HR@orcl> select e.first_name,d.department_name,l.city from employees e join departments d on ( e.DEPARTMENT_ID=d.DEPARTMENT_ID) join locations l on (d.LOCATION_ID=l.LOCATION_ID) where e.manager_id=149;

子查询

即引用另一个select查询的结果

SCOTT@orcl> select ename,sal from emp where sal > (select sal from emp where ename='SCOTT');

SCOTT@orcl> select ename,sal from emp where sal in (select min(sal) from emp group by deptno);

SCOTT@orcl> update emp26 set sal=(select sal from emp26 where ename='SCOTT') where ename='JIAKE';

SCOTT@orcl> select ename,sal from emp26 where ename in ('SCOTT','JIAKE');

SCOTT@orcl> delete emp26 where ename='SMITH';

DDL语句建立与管理表

建表

SCOTT@orcl> create table t001 (
ename varchar2(10),
deptno number(2),
hiredate date default sysdate);

查询表有多少行

SCOTT@orcl> select count(*) from t001;

插入新行

SCOTT@orcl> insert into t001 values ('jiake','01','08-DEC-18');
SCOTT@orcl> insert into t001 (ename,deptno) values('zero',02);

查看表内所有行

SCOTT@orcl> select * from t001;

定义显示列宽度（可以优化显示效果）

col object_name for a12

一些类似的例子：

create table t002 (
empno number(4)
constraint t002_empno_pk primary key,
ename varchar(10) not null,
sal number(5));

insert into t002 values (1000,'jiake',null);

select * from t002;
insert into t002 values (1000,null,800);添加不成功，因为非空约束
insert into t002 values (1000,'chengcheng',800);不成功，主键非空且唯一
insert into t002 values (1001,'chengcheng',800);

create table t003 (
name varchar2(10),
sex varchar2(10),
sal number(5),
constraint t003_name_pk primary key (name)); 表级主键约束

create table t004 (
name varchar2(10),
empid number(5),
constraint t004_empid_unique unique,
email varchar2(20),
constarint t004_email_unique unique);可以在表级定义多个唯一性约束

create table t007 (
empno number(10),
ename varchar2(10));

alter table t007 add constraint t007_empno1 unique(empno);表建立后添加唯一性约束

创建表

SCOTT@orcl> create table emp26 as select from emp;
SCOTT@orcl> create table dept26 as select from dept;

插入行

SCOTT@orcl> insert into emp26 (empno,ename,deptno) values (1934,'hello',50);

删除行

SCOTT@orcl> delete emp26 where deptno=50;

设置主键（主键默认为索引）

SCOTT@orcl> alter table dept26 add constraint dept26_deptno_pk primary key (deptno);

查询表的主键信息

SCOTT@orcl> select constraint_name from user_constraints where table_name='DEPT26';
SCOTT@orcl> select constraint_name from user_constraints where table_name='EMP26';

设置外键

SCOTT@orcl> alter table emp26 add constraint emp26_deptno_fk foreign key (deptno) references dept26(deptno);

查看表的所有信息

SCOTT@orcl> select * from emp26;

试着插入测试行（外键约束，deptno的值必须是dept26表中已存在的值）

SCOTT@orcl> insert into emp26 (empno,ename,deptno) values (7934,'hello',50);加入不成功
SCOTT@orcl> insert into emp26 (empno,ename,deptno) values (7934,'hello',30);加入成功

插入行

SCOTT@orcl> insert into dept26 values (50,'CENTOS','ZHENGZHOU');
SCOTT@orcl> insert into emp26 (empno,ename,deptno) values (7935,'nihao',50);

试着删除（因为主外键约束，不能直接删除外键表里与主键表有关联的项）

SCOTT@orcl> delete from dept26 where deptno=50;删除不了

查看约束

SCOTT@orcl> select constraint_name from user_constraints where table_name='EMP26';

删除主键

SCOTT@orcl> alter table emp26 drop constraint EMP26_DEPTNO_FK;

重新设置主键属性
1.(父表的值被删除，字表的相关列自动被赋予null)

SCOTT@orcl> alter table emp26 add constraint EMP26_DEPTNO_FK foreign key (deptno) references dept26(deptno) on delete set null;

2.(父表的值被删除，子表的相关行自动被删除)

SCOTT@orcl> alter table emp26 add constraint EMP26_DEPTNO_FK foreign key (deptno) references dept26(deptno) on delete cascade;

创建新表

  1  create table student (
  2  name varchar2(10),
  3  stu_id number(2)
  4  constraint stu_stu_id_uk unique,
  5  age number(2)
  6  constraint stu_age_ck check (age between 18 and 30),
  7  sex varchar(6)
  8* constraint stu_sex_ck check (sex in ('male','female')))

SCOTT@orcl> create table dept2626 as select * from dept; 

SCOTT@orcl> create table empnew (empid,empname,salary) as select empno,ename,sal from emp;

设置表只读(只读表只能查看，不能操作)

SCOTT@orcl> alter table dept2626 read only;

查看用户所有表的只读属性

SCOTT@orcl> select table_name,read_only from user_tables;

更改表为可读可写

SCOTT@orcl> alter table dept2626 read write;

此时可以正常插入

SCOTT@orcl> insert into dept2626 values (50,'aaa','bbb');

删除表

SCOTT@orcl> drop table dept2626 purge;

创建视图(类似于脚本或链接，将一个长长的条件设置为一个简单的名称。自身没有数据，但基于原表有些信息可更改，但会影响原表信息)

SYS@orcl> create view empview3 (name,empid,salary) as select ename,empno,sal from scott.emp;

创建多表间视图（sys用户）

select d.dname,max(e.sal),min(e.sal),avg(e.sal) from scott.emp e join in scott.dept d using (deptno) group by d.dname;

create view dept_emp_vu (dname,maxsal,minsal,avgsal) as select d.dname,max(e.sal),min(e.sal),avg(e.sal) from scott.emp e join in scott.dept d using (deptno) group by d.dname;

select * from dept_emp_vu;

视图约束

create view empview6 as select * from scott.emp where deptno=20 with check opction constriaint empview6_ck;
insert into empview6 (empno,ename,deptno) values (8000,'hello',30);添加不成功

create view empview7 as select * from scott.emp where deptno=20;
insert into empview7 (empno,ename,deptno) values (8000,'hello',30);成功
但视图因为定义时的限制查看不到更改的数据。原数据，基表能够查看的到

删除视图(前两行是查询视图名称)

desc user_views;
select view_name from user_views where lower(view_name) like 'emp%';
drop view empview7;

创建索引与删除（一般很少用到删除）

create index emp_last_name_idx on employees(last_name);
drop index emp_last_name_idx;

序列

• [ ] 序列是Oacle提供的用于产生一系列唯一数字的数据库对象。它能自动提供唯一的数值，主要用于提供主键值。将序列值装入内存可以提高访问效率。

conn scott/tiger
desc user_sequences;
select sequence_name from user_sequences;
create table testdept as select * from dept;
create sequence testdept_deptno_seq 
    increment by 10
    start with 50
    maxvalue 900
    nocache
    nocycle;
insert into testdept (dneme) values ('test');
select testdept_deptno_seq.currval from dual;还没启动无法查看
rollback;
insert into testdept values (testdept_deptno_seq.nextval,'test2','zhengzhou');
select * from testdept;
select testdept_deptno_seq.nextval from dual;

drop sequence testdept_deptno_seq;

同义词(e6即为empview6的同义词，即简名)

SYS@orcl> create synonym e1 for hr.employees;

授权

create user jiake identified by jiake;
conn jiake/jiake;不能登录
grant create session to jiake;
conn jiake/jiake;登录成功
select * from tab;
create table t001 (id number(2));失败

conn / as sysdba
grant create session to jiake;
conn jiake/jiake
create table 001 (id number(2));成功

conn / as sysdba
grant create table,create view to jiake;

create role class26;role为模板，这里是用户权限模板
grant create session,create table,create view,create sequence to class26;

create user tiantian identified by tiantian;
grant class26 to tiantian;
conn tiantian/tinatina
create table t001 (id number);
alter user tiantian identified by tiantian;

Oracle数据库启动与关闭

在连接数据库的页面，当我们输入exit退出时，数据库页面我们确实是看不到了。但是用ps查看运行进程时，却还是有一堆的Oracle相关进程。
其实，真正的关闭数据库要用shutdown。shutdown有下面几种用法。

• [ ] shutdown noraml （默认） 禁止新的连接，等待所有的连接结束。已经连接的正常使用。
• [ ] shutdown transactional 禁止新的连接，等待所有事务完成。事务提交后开始关闭数据库。
• [ ] shutdown immediate 禁止新的连接，未提交事务强制回滚。一般强制关闭时使用。
• [ ] shutdown abort 禁止新的连接，不进行一致性检查，直接强制关闭数据库。相当于断电。

  SYS@orcl> shutdown immediate
  Database closed.
  Database dismounted.
  ORACLE instance shut down.

启动数据库 分三个阶段：
阶段一：启动实例
startup nomount: 加载参数文件来启动实例
阶段二：装载数据库
alter database mount: 通过参数文件中记载的控制文件位置来加载控制文件
阶段三：打开数据库
alter database open: 通过控制文件中记载的数据文件、日志文件的位置来加载打开数据文件、日志文件。
数据库启动流程：

[oracle@cet6 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sat Dec 9 20:04:09 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SYS@orcl> startup
ORACLE instance started.

Total System Global Area  626327552 bytes
Fixed Size                  2255832 bytes
Variable Size             234882088 bytes
Database Buffers          385875968 bytes
Redo Buffers                3313664 bytes
Database mounted.
Database opened.
SYS@orcl> 

转载于:https://blog.51cto.com/amelie/2049024