oracle学习笔记

oracle安装

1. 安装virtualbox：

成都网站设计、网站建设的开发，更需要了解用户，从用户角度来建设网站，获得较好的用户体验。创新互联多年互联网经验，见的多，沟通容易、能帮助客户提出的运营建议。作为成都一家网络公司，打造的就是网站建设产品直销的概念。选择创新互联，不只是建站，我们把建站作为产品，不断的更新、完善，让每位来访用户感受到浩方产品的价值服务。

# yum install gcc kernel-devel kernel-headers

# yum install virtualbox-5....

# /etc/init.d/vboxdrv setup 手动编译内核模块，安装时自动完成

# usermod -G vboxusers root

2. 安装linux：

创建虚拟机：

名称：oracle11gR2_RHEL6.4_x64

类型：linux 64bit

内存：2048MB

硬盘大小：100GB

设置：

启动顺序：硬盘，网络

网络：网卡1，桥接eth0

去除声音、usb设备

安装：desktop方式

主机名：node1.test.com，ip使用dhcp，swap4GB，其余给/

3. 调整linux系统：

关闭防火墙：

# service iptables stop

# service ip6tables stop

# chkconfig iptables off

# chkconfig ip6tables off

管理工具中disabled防火墙

关闭selinux：

# vi /etc/selinux/config

SELINUX=disabled

配置yum：

# rm -f /etc/yum.repos.d/*

# wget ftp://172.16.8.100/rhel6.repo -P /etc/yum.repos.d/

或者：

# vi /etc/yum.repos.d/rhel6.repo

[Server]

name=Server

baseurl=file:///media/"RHEL_6.5 x86_64 Disc 1"/Server

enabled=1

gpgcheck=0

安装vb增强功能：

# yum -y install gcc kernel-devel

# ln -s /usr/src/kernels/2.6.32-431.el6.x86_64/ /usr/src/linux

设备-->安装增强功能

右键eject弹出光盘

4. 确认root身份:

# id

5. 硬件配置：

# grep MemTotal /proc/meminfo

# grep SwapTotal /proc/meminfo

# vi /etc/fstab（永久修改）

tmpfs /dev/shm tmpfs defaults,size=2G 0 0

# mount -o remount /dev/shm

临时修改

# mount -t tmpfs shmfs -o size=2g /dev/shm

# uname -m

# df -h

6. rpm检查并安装：

# cat /etc/redhat-release

# uname -r

# rpm -qa | grep glibc

# yum install glibc

# yum install glibc.i686

binutils-2.20.51.0.2-5.11.el6 (x86_64)

compat-libcap1-1.10-1 (x86_64)

compat-libstdc++-33-3.2.3-69.el6 (x86_64)

compat-libstdc++-33-3.2.3-69.el6.i686

gcc-4.4.4-13.el6 (x86_64)

gcc-c++-4.4.4-13.el6 (x86_64)

glibc-2.12-1.7.el6 (i686)

glibc-2.12-1.7.el6 (x86_64)

glibc-devel-2.12-1.7.el6 (x86_64)

glibc-devel-2.12-1.7.el6.i686

ksh

libgcc-4.4.4-13.el6 (i686)

libgcc-4.4.4-13.el6 (x86_64)

libstdc++-4.4.4-13.el6 (x86_64)

libstdc++-4.4.4-13.el6.i686

libstdc++-devel-4.4.4-13.el6 (x86_64)

libstdc++-devel-4.4.4-13.el6.i686

libaio-0.3.107-10.el6 (x86_64)

libaio-0.3.107-10.el6.i686

libaio-devel-0.3.107-10.el6 (x86_64)

libaio-devel-0.3.107-10.el6.i686

make-3.81-19.el6

sysstat-9.0.4-11.el6 (x86_64)

7. 创建用户：

# groupadd -g 1000 oinstall

# groupadd -g 1001 dba

# groupadd -g 1002 oper

# useradd -u 1000 -g oinstall -G dba,oper oracle

# passwd oracle

8. 修改内核参数：

# vi /etc/sysctl.conf

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

# sysctl -p

9. 修改资源限制：

# vi /etc/security/limits.conf

oracle soft nofile 1024

oracle hard nofile 65536

oracle soft nproc 2047

oracle hard nproc 16384

oracle soft stack 10240

oracle hard stack 32768

10. 创建目录：

# mkdir -p /u01/app/oracle

# chown -R oracle:oinstall /u01

# chmod -R 775 /u01

11. 修改概要文件：

# vi ~oracle/.bash_profile

export ORACLE_SID=orcl

export ORACLE_BASE=/u01/app/oracle

export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1

export ORACLE_HOSTNAME=node1.test.com

export ORACLE_UNQNAME=orcl

export PATH=$PATH:$ORACLE_HOME/bin

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib

export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib

export NLS_LANG=american_america.AL32UTF8

export NLS_DATE_FORMAT='yyyy-mm-dd hh34:mi:ss'

export EDITOR=vi

export

12. 使用hosts文件解析主机名：

# hostname

node1.test.com

# vi /etc/hosts

192.168.0.1 node1.test.com node1

# ping node1

# ping node1.test.com

13. 解压缩安装包：

# cd /installation

# unzip p10404530_112030_Linux-x86-64_[12]of7.zip

或者：

在物理主机中解压缩2个安装包，然后共享给虚拟机，命令如下：

# mkdir /database

# mount -t vboxsf database /database

14. 图形界面安装：

# xhost +

# su - oracle

$ cd /database/

$ ./runInstaller

略

以root身份执行两个脚本

15. 创建监听和数据库：

图形界面下创建监听，oracle执行：

$ netca

$ lsnrctl status

$ netstat -tlnp | grep 1521

图形界面创建db，oracle执行：

$ dbca

16. 测试：

sqlplus测试：

$ sqlplus sys/sys@orcl as sysdba

$sqlplus/as sysdba

SQL> show user

SQL> select count(*) from hr.employees;

SQL> exit

浏览器测试：

https://192.168.0.1:1158/em

sys/password sysdba

安装rlwrap：

root身份安装

# yum install rlwrap-0.42-1.el6.x86_64.rpm

# vi ~oracle/.bashrc

alias sqlplus='rlwrap sqlplus'

alias rman='rlwrap rman'

# su - oracle

$ sqlplus / as sysdba

root身份安装

# yum install flash-plugin-11.2.202.508-release.x86_64.rpm

sqldeveloper测试：

root身份安装

# yum install jdk-8u51-linux-x64.rpm

# yum install sqldeveloper-4.1.1.19.59-1.noarch.rpm

# /usr/local/bin/sqldeveloper

输入jdk的路径 /usr/java/jdk1.8.0_51/

点左上角+号，创建新连接：

sys/password，连接类型：basic，角色：sysdba

192.168.0.1端口1521 sid：orcl

测试，保存。

17. 添加启动脚本：

# vi /etc/oratab 把N改为Y

orcl:/u01/app/oracle/product/11.2.0/db_1:Y

# vi /etc/rc.d/init.d/oracle

#!/bin/bash

#chkconfig:35 99 01

case "$1" in

start)

echo -n "Starting Oracle Database& Listener:"

su - oracle -c 'dbstart $ORACLE_HOME' >/dev/null

echo "[ ok ]"

echo -n "Starting Oracle EM dbconsole:"

su - oracle -c "emctl start dbconsole" >/dev/null

echo "[ ok ]"

touch /var/lock/subsys/oracle

;;

stop)

echo -n "Shutting Down Oracle EM dbconsole:"

su - oracle -c "emctl stop dbconsole" >/dev/null

echo "[ ok ]"

echo -n "Shutting Down Oracle Database& Listener:"

su - oracle -c 'dbshut $ORACLE_HOME' >/dev/null

echo "[ ok ]"

rm -f /var/lock/subsys/oracle

;;

echo "Usage: oracle {start|stop}"

exit 1

esac

# chmod 755 /etc/rc.d/init.d/oracle

# chkconfig --add oracle

# chkconfig --list oracle

18. 删除口令有效期：

#su - oracle

$ sqlplus / as sysdba

SQL> alter profile default limit PASSWORD_LIFE_TIME unlimited;

19. 修改virtualbox的网络设置（可选）：

全局设置à添加hostonly网络，设置ip为：192.168.0.253，取消dhcp（linuxonly）

虚拟机网络改为hostonly

虚拟机中将网卡的ip改为静态，192.168.0.1

# vi /etc/sysconfig/network-scripts/ifcfg-eth0

BOOTPROTO=none

IPADDR=192.168.0.1

PREFIX=24

# ifdown eth0;ifup eth0

# vi /etc/hosts

192.168.0.1 node1.test.com node1

SQL

select查询语句

1. 查看hr用户名下的表，解锁hr用户：

$ sqlplus / as sysdba或SQL> conn / as sysdba

SQL> show user

SQL> select table_name from dba_tables where owner='HR';

SQL> select * from hr.employees;

SQL> alter user hr account unlock identified by hr;

$ sqlplus hr/hr或者SQL> conn hr/hr

SQL> show user

SQL> select * from tab;

SQL> desc employees

练习：

查看scott用户名下的表，解锁scott用户：

scott/tiger

2. 使用sqlplus的全屏编辑功能：

$ echo $EDITOR

SQL>select * from hr.employees;

SQL> ed

SQL> / 执行

3. 基础select语句：

SQL> select * from employees;

SQL> desc employees

SQL> select LAST_NAME, SALARY, COMMISSION_PCT from employees;

SQL> desc departments

SQL> select department_id, department_name from departments;

SQL> select distinctDEPARTMENT_ID from employees;

SQL> select last_name, salary*12*(1+commission_pct) total_salary, department_id from employees;

SQL> select first_name||', '||last_name from employees;

SQL> select first_name||', '||last_name fullname from employees;

练习：

输出下列员工信息：

Eleni(first_name) Zlotkey(last_name) employeeid is ... at department .. total salary is …

4. 使用连字符构造语句：

SQL> select table_name from user_tables;

SQL> select 'grant select on hr.'||table_name||' to scott;' from user_tables;

SQL> spool /home/oracle/grant.sql

SQL> set head off去除标题

SQL> set feed off去除回馈

SQL> select 'grant select on hr.'||table_name||' to scott;' from user_tables;

SQL> spool off

$ vi /home/oracle/grant.sql 手动去除没用的行，相当于除去标题和回馈，

SQL> @/home/oracle/grant.sql 执行sql脚本

5. 单引号的处理：

SQL> select 'I'm teaher' from dual;

ERROR:

ORA-01756: quoted string not properly terminated

SQL> select 'I''m teaher' from dual;

SQL> selectq'{I'm teaher}' from dual; []<>()都可以

where和orderby

数字条件：

SQL> select salary from employees where employee_id=100;

字符串大小写敏感：

SQL> select last_name, salary from employees where last_name='King';

SQL>select table_name, tablespace_name from user_tables where table_name='EMPLOYEES';

日期是格式敏感：

SQL> alter session set nls_date_format='RR-Mon-dd';

SQL> select last_name from employees where hire_date='2006-05-23';

SQL> select last_name from employees where hire_date=to_date('2006-05-23', 'yyyy-mm-dd');

区间查询：

SQL> select last_name from employees where salary>=3400 and salary<=4000;

SQL> select last_name from employees where salary between 3400 and 4000;

SQL> select last_name from employees where salary between 3000 and 5000 and department_id=50;

in：

SQL> select last_name from employees where department_id=30 or department_id=40 or department_id=50;

SQL> select last_name from employees where department_id in (30, 40, 50);

通配符：

SQL> select last_name, job_id from employees where job_id like '%\_MAN' escape '\';禁止转义符后面的符号的特殊含义，like _通配某一个字符，like %通配某些字符

null作为条件：

SQL> select last_name from employees where commission_pct is null;

SQL> select last_name from employees where commission_pct is not null;

and/or/not：

SQL> select last_name, job_id, department_id, salary from employees where job_id='SA_REP' or department_id=50 and salary>=8000;

SQL> select last_name, job_id, department_id, salary from employees where (job_id='SA_REP' or department_id=50) and salary>=8000;

排序：

SQL> select last_name, salary from employees order by salary;升序

SQL> select last_name, salary from employees order by salary desc;降序

SQL> select last_name, salary from employees order by last_name;

SQL> select last_name, hire_date from employees order by hire_date;

SQL> select last_name, salary, commission_pct from employees order by salary desc, commission_pct desc;

SQL> select last_name, salary*12*(1+commission_pct) from employees order by 2;

SQL> select last_name, salary*12*(1+commission_pct) total_salary from employees order by total_salary;

练习：

选择部门30中的雇员

列出所有职员(CLERK)的姓名、编号和部门

找出薪金大于5000的雇员

找出奖金高于0.1的雇员

找出部门50中的所有员工和部门30中的经理的详细资料

找出收取奖金的雇员的不同工作职位每种职位显示一次

找出不收取奖金或收取的工资低于5000的雇员

显示last_name不带有'R'的雇员姓名

select last_name name from employees where not last_name like '%R%'；

显示所有雇员的姓名、工作和薪金，按工作的降序顺序排序，而工作相同时按薪金升序

单行函数

SQL> select upper(first_name), lower(last_name), length(last_name) from employees;

SQL> select (sysdate-hire_date)/7 from employees;

SQL> select trunc((sysdate-hire_date)/30, 0) from employees;

SQL> select trunc(months_between(sysdate,hire_date), 0) from employees;

SQL> select sysdate+3650 from dual;

SQL> select add_months(sysdate, 120) from dual;

SQL> select next_day('2015-09-01', 'friday') from dual;

SQL> select next_day('2015-10-01', 6) from dual;

SQL> select last_day(sysdate) from dual;

SQL> select round(to_date('2015-10-10','yyyy-mm-dd'), 'MONTH') from dual;

SQL> select round(to_date('2015-10-16','yyyy-mm-dd'), 'MONTH') from dual;

SQL> select round(to_date('2015-10-10','yyyy-mm-dd'), 'YEAR') from dual;

SQL> select round(sysdate, 'DAY') from dual;

练习：

找出各月最后三天内受雇的所有雇员

extract(month from hire_date+4) != extract(month from hire_date)

找出早于25年之前受雇的雇员

months_between(sysdate, hire_date)/300>=25

显示正好为6个字符的雇员姓名

length(last_name)=6

显示所有雇员的姓名的前三个字符

substr(last_name, 1, 3)

显示所有雇员的姓名，用a替换所有'A'

replace(last_name, 'A', 'a')

类型转换和其他函数

SQL> select to_char(salary, '$999,999.00') from employees;

SQL> select last_name, to_char(hire_date, 'dd-Mon-RR') from employees;

SQL> select to_char(sysdate, 'yyyy-mm-dd hh34:mi:ss') from dual;

SQL> select to_char(sysdate, 'yyyy-mm-dd hh:mi:ss AM') from dual;

SQL> select last_name from employees where hire_date=to_date('2006-05-23', 'yyyy-mm-dd');

SQL> select to_number('$123,456.78', '$999,999.00') from dual;

练习：

查询2006年入职员工：

select last_name

from employees

where hire_date between to_date('2006-01-01', 'yyyy-mm-dd')

and to_date('2006-12-31', 'yyyy-mm-dd');

select last_name

from employees

where to_char(hire_date, 'yyyy')='2006';

select last_name

from employees

where extract(year from hire_date)=2006;

--不推荐

select last_name

from employees

where hire_date like '2006%';

查询历年9月份入职的员工：

select last_name

from employees

where to_char(hire_date, 'mm')='09';

select last_name from employees where extract(month from hire_date)=9;

其他函数：

nvl：

nvl(val1, val2)

if val1 is not null

then

return val1;

else

return val2;

SQL> select last_name, salary*12*(1+nvl(commission_pct, 0)) total_salary from employees;

练习：

显示所有员工部门编号，没有部门的显示“未分配部门”

select nvl(to_char(department_id),'未分配部门') from employees

case和decode：

IT_PROG +1000

SA_REP+1500

ST_CLERK +2000

其他人工资不变

select salary+1000 from employees where job_id='IT_PROG';

select last_name, job_id, salary,

case job_id

when 'IT_PROG' then salary+1000

when 'SA_REP' then salary+1500

when 'ST_CLERK' then salary+2000

else salary

end new_salary

from employees;

select last_name, job_id, salary,

decode( job_id,

'IT_PROG', salary+1000,

'SA_REP', salary+1500,

'ST_CLERK', salary+2000,

salary) new_salary

from employees;

练习：

按照员工工资，对员工分级显示：

A 20001-25000

B 15001-20000

C 10001-15000

D 5001-10000

E 0-5000

答案：

select last_name,salary,

decode(trunc(salary/5000,0),

0,'E',

1,'D',

2,'C',

3,'B',

4,'A',

salary) n_sal

from employees；

答案拓展：

select last_name,salary,

decode(trunc(salary/5000,0),

0,'E',

1,'D',

2,'C',

3,'B',

4,'A',salary) n_sal

from employees

order by salary desc,n_sal

分组函数

SQL> select count(*), sum(salary), avg(salary), min(salary), max(salary) from employees;

SQL> create table t1(x int);

SQL> insert into t1 values (null);

SQL> insert into t1 values (1);

SQL> commit;

SQL> select count(*) from t1;

SQL> select count(x) from t1;

SQL> select max(x) from t1;

SQL> select min(x) from t1;

SQL> select sum(x) from t1;

SQL> select avg(x) from t1;

SQL> select avg(salary), avg(nvl(commission_pct, 0)) from employees;

SQL> select count(distinct department_id) from employees; 去除重复值

Groupby分组：

SQL> select department_id, avg(salary) from employees group by department_id;

多列分组：

SQL> select department_id, job_id, max(salary) from employees group by department_id, job_id;

SQL> select department_id, job_id, max(salary), last_name from employees group by department_id, job_id; 错误语法

练习：

公司中不同职位的数量

select job_id,count(job_id)

from employees

group by job_id；

计算每个部门的人数

select department_id,count(last_name)

from employees

group by department_id

select department_id,count(department_id)

from employees

group by department_id

按年份分组，求员工的工资总和

select to_char(hire_date,'yyyy') year,sum(salary)

from employees

group by to_char(hire_date,'yyyy')

order by year

selec extract(year from hire_date) year,sum(salary)

from employees

group by extract(year from hire_date)

order by year

Having语句：

SQL> select department_id, avg(salary) from employees where avg(salary)>=5000 group by department_id; 错误语句

SQL> select department_id, avg(salary) from employees group by department_id having avg(salary)>=5000;

练习：

按部门求出所有有部门的普通员工的平均工资，部门平均工资少于5000的不显示，最终结果按平均工资的降序排列。

select department_id,trunc(avg(salary)) avg_sal

from employees

where department_id is not null

group by department_id

having trunc(avg(salary)) >= 5000

order by avg_sal desc

select department_id, avg(salary) avg_sal

from employees

where job_id not like '%\_MGR' escape '\'and department_id is not null

group by department_id

having avg(salary)>=5000

order by avg_sal desc;

多表连接

emp： dept:

empno ename deptno deptno dname

100 abc 10 10 sales

101 def 10 20 market

102 xyz 20 30 it

103 opq null

for emp in 100 .. 103

for dept in 10 .. 30

emp.deptno=dept.deptno

100 abc 10 10 sales

101 def 10 10 sales

102 xyz 20 20 market

订单表:

CustID StoreID ProdID ChannelID

100 S100 P100 C100

客户表：

CustID name creditlevel

100 abc

地址表：

CustID adress

100 bj

100 tj

获取如下信息，准备工作：

employees:

员工总数：107

SQL> select count(*) from employees;

有部门的员工数：106

SQL> select count(*) from employees where department_id is not null;

SQL> select count(department_id) from employees;

没有部门的员工数：1

SQL> select count(*) from employees where department_id is null;

departments：

部门总数：27

SQL> select count(*) from departments;

有员工的部门数：11

SQL> select count(distinct department_id) from employees;

没有员工的部门数：16

SQL> select count(*) from departments where department_id not in (select department_id from employees where department_id is not null);

for dept in 1..27

for emp in 1..107

dept.deptid不在emp表中出现

select count(*)

from employees e, departments d

where e.department_id(+)=d.department_id

and e.employee_id is null;

select count(*)

from departments d

where not exists

(select 1 from employees where department_id=d.department_id);

select (select count(*) from departments)-(select count(distinct department_id) from employees) from dual;

内连接：106(106, 11)