Oracle数据库应用实战

yes-no

　　演示环境：CentOS 6.9

　　所用Oracle版本:11g>
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 > /etc/security/limits.conf  /dev/null
　　if [ $? != 0 ];then
　　
cat >> /etc/pam.d/login > ~oracle/.bash_profile  .bashrc　　

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

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

　　.登录测试
　　

[oracle@cet6 ~]$ sqlplus / as sysdba　　


　　
SQL*Plus:>　　

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

　　
Connected to:

　　
Oracle Database 11g Enterprise Edition>　　
With the Partitioning, OLAP, Data Mining and Real Application Testing options
　　

　　
SYS@orcl>
　　

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

　　SQL>>

　　切换用户(以下的大多数查询都在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 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>>

　　查询表的主键信息

　　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>>

　　查看表的所有信息

　　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>>

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

　　SCOTT@orcl>>

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

　　SCOTT@orcl>>

　　创建新表
　　

  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>>

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

　　SCOTT@orcl> select table_name,read_only from user_tables;

　　更改表为可读可写

　　SCOTT@orcl>>

　　此时可以正常插入

　　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>
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>
　　
grant create session,create table,create view,create sequence to>　　


　　
create user tiantian>
　　
grant>　　
conn tiantian/tinatina
　　
create table t001 (id number);

　　
alter user 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:>　　

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


　　
Connected to an>　　

　　
SYS@orcl> startup
　　
ORACLE instance started.
　　

　　
Total System Global Area  626327552 bytes

　　
Fixed>
　　
Variable>　　
Database Buffers          385875968 bytes
　　
Redo Buffers                3313664 bytes
　　
Database mounted.
　　
Database opened.
　　
SYS@orcl>