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 
             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 [ $? =  ];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  ];then
cat >> /etc/sysctl.conf <<EOF
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 = 
EOF
/sbin/sysctl -p
fi

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

grep pam_limits.so /etc/pam.d/login &> /dev/null
  if [ $? !=  ];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  /u01/app )

grep -i oracle ~oracle/.bash_profile &> /dev/null
  if [ $? !=  ];then
cat >> ~oracle/.bash_profile <<EOF
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=\$ORACLE_BASE//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以支持中文。

[[email protected] Desktop]# ls
pdkshx86_64.rpm

6.数据库实例安装

初学者需要一个已经存在的数据库供我们练习。

数据库软件安装完成后，就是实例的安装了。

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

7.测试及数据库页面设置

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

[[email protected] Desktop]# ls
rlwrapel6.x86_64.rpm

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

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

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

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

<4>.登录测试

[[email protected] ~]$ sqlplus / as sysdba

SQL*Plus: Release  Production on Sat Dec  :: 

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

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

[email protected]>

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;

交互式输入（替代变量）

[email protected]> select * from emp where deptno=&bumen;

Enter value for bumen: 30

[email protected]> set verify off; #不再显示变量接收的过程

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

[email protected]> select username from dba_users;

函数

切换为sys用户（有些操作在普通用户下不能进行）

[email protected]> conn / as sysdba

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

[email protected]> select username from dba_users where lower(username) like 'sco%';

切换回scott用户

[email protected]> conn scott/tiger;

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

[email protected]> 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;

新建测试表

[email protected]> create table emptmp as select * from emp;

插入新行

[email protected]> insert into emptmp (empno, ename,sal) values (8000,'jiake',1000);

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

[email protected]> select ename,nvl(hiredate,'01-JAN-80') hiredate from emptmp;

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

[email protected]> select deptno,avg(sal) from emp group by deptno;

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

[email protected]> 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当作两张不同的表进行查询）

[email protected]> select x.ename yuangong,nvl(s.ename,'KING') lingdao from emp x left join emp s on (x.mgr=s.empno);

查看工资对应的工资等级

[email protected]> select e.ename,e.sal,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;

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

[email protected]> 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的行

[email protected]> 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查询的结果

[email protected]> select ename,sal from emp where sal > (select sal from emp where ename='SCOTT');

[email protected]> select ename,sal from emp where sal in (select min(sal) from emp group by deptno);

[email protected]> update emp26 set sal=(select sal from emp26 where ename='SCOTT') where ename='JIAKE';

[email protected]> select ename,sal from emp26 where ename in ('SCOTT','JIAKE');

[email protected]> delete emp26 where ename='SMITH';

DDL语句建立与管理表

建表

[email protected]> create table t001 (

ename varchar2(10),

deptno number(2),

hiredate date default sysdate);

查询表有多少行

[email protected]> select count(*) from t001;

插入新行

SC[email protected]> insert into t001 values ('jiake','01','08-DEC-18');

S[email protected]> insert into t001 (ename,deptno) values('zero',02);

查看表内所有行

[email protected]> select * from t001;

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

col object_name for a12

一些类似的例子：

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

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

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

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

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

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

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

创建表

[email protected]> create table emp26 as select from emp;

[email protected]> create table dept26 as select from dept;

插入行

[email protected]> insert into emp26 (empno,ename,deptno) values (1934,'hello',50);

删除行

[email protected]> delete emp26 where deptno=50;

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

[email protected]> alter table dept26 add constraint dept26_deptno_pk primary key (deptno);

查询表的主键信息

[email protected]> select constraint_name from user_constraints where table_name='DEPT26';

[email protected]> select constraint_name from user_constraints where table_name='EMP26';

设置外键

[email protected]> alter table emp26 add constraint emp26_deptno_fk foreign key (deptno) references dept26(deptno);

查看表的所有信息

[email protected]> select * from emp26;

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

[email protected]> insert into emp26 (empno,ename,deptno) values (7934,'hello',50);加入不成功

[email protected]> insert into emp26 (empno,ename,deptno) values (7934,'hello',30);加入成功

插入行

SCO[email protected]> insert into dept26 values (50,'CENTOS','ZHENGZHOU');

[email protected]> insert into emp26 (empno,ename,deptno) values (7935,'nihao',50);

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

[email protected]> delete from dept26 where deptno=50;删除不了

查看约束

[email protected]> select constraint_name from user_constraints where table_name='EMP26';

删除主键

S[email protected]> alter table emp26 drop constraint EMP26_DEPTNO_FK;

重新设置主键属性

1.(父表的值被删除，字表的相关列自动被赋予null)

[email protected]> alter table emp26 add constraint EMP26_DEPTNO_FK foreign key (deptno) references dept26(deptno) on delete set null;

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

[email protected]> alter table emp26 add constraint EMP26_DEPTNO_FK foreign key (deptno) references dept26(deptno) on delete cascade;

创建新表

create table student (
    name varchar2(),
    stu_id number()
    constraint stu_stu_id_uk unique,
    age number()
    constraint stu_age_ck check (age between  and ),
    sex varchar()
  * constraint stu_sex_ck check (sex in ('male','female')))

[email protected]> create table dept2626 as select * from dept; 

[email protected]> create table empnew (empid,empname,salary) as select empno,ename,sal from emp;

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

[email protected]> alter table dept2626 read only;

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

[email protected]> select table_name,read_only from user_tables;

更改表为可读可写

[email protected]> alter table dept2626 read write;

此时可以正常插入

[email protected]> insert into dept2626 values (50,'aaa','bbb');

删除表

[email protected]> drop table dept2626 purge;

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

[email protected]> 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= with check opction constriaint empview6_ck;
insert into empview6 (empno,ename,deptno) values (,'hello',);添加不成功

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

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

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 
    start with 
    maxvalue 
    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的同义词，即简名)

[email protected]> 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());失败

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

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 禁止新的连接，不进行一致性检查，直接强制关闭数据库。相当于断电。

[email protected]> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

启动数据库分三个阶段：

阶段一：启动实例

startup nomount: 加载参数文件来启动实例

阶段二：装载数据库

alter database mount: 通过参数文件中记载的控制文件位置来加载控制文件

阶段三：打开数据库

alter database open: 通过控制文件中记载的数据文件、日志文件的位置来加载打开数据文件、日志文件。

数据库启动流程：

[[email protected] ~]$ sqlplus / as sysdba

SQL*Plus: Release  Production on Sat Dec  :: 

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

Connected to an idle instance.

[email protected]> startup
ORACLE instance started.

Total System Global Area   bytes
Fixed Size                   bytes
Variable Size              bytes
Database Buffers           bytes
Redo Buffers                 bytes
Database mounted.
Database opened.
[email protected]> 

































本文转自阿拉杜美美51CTO博客，原文链接：http://blog.51cto.com/amelie/2049024 ，如需转载请自行联系原作者

Oracle数据库应用实战

Oracle安装

Oracle查询语句

检索数据SQL语句

where限制子句

函数

多表查询

子查询

DDL语句建立与管理表

Oracle数据库启动与关闭

继续阅读

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艰难安装LDAP,SSL认证

《Linux命令行与Shell脚本编程大全第2版.布卢姆》pdf

MySQL的4种隔离级别？出现问题

ACS基本配置-权限等级管理

XX系统实施过程问题总结

无组件上传图片到数据库中，最完整解决方案

【MySQL数据库】数据库索引事务1.索引2.事务

传说FreeBSD等比Linux更稳定，更“健壮”

无人机--飞控科普

27 Best Free Eclipse Plug-ins for Java Developer to be ProductiveCode Quality PluginsText Editor PluginsDependency ManagementVersion Control Integration PluginsFramework Development Continuous Integration Related PluginsOther Utility Plugins

neo4j之cypher使用文档

NOSQL安全攻击

mybatis_入门程序Mybatis入门

登录plsql 报错 the account is locked --用户被锁

SequoiaDB巨杉数据库C++驱动概述