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)

select e.last_name, d.department_name

from employees e, departments d

where e.department_id=d.department_id;

 

select e.last_name, d.department_name

from employees e join departments d on e.department_id=d.department_id;

 

左外连接：107（106+1）

select e.last_name, d.department_name

from employees e, departments d

where e.department_id=d.department_id(+);

 

select e.last_name, d.department_name

from departments d, employees e

where e.department_id=d.department_id(+);

 

select e.last_name, d.department_name

from employees e left outer join departments d

on e.department_id=d.department_id;

 

右外连接：122（106+16）

select e.last_name, d.department_name

from employees e, departments d

where e.department_id(+)=d.department_id;

 

select e.last_name, d.department_name

from employees e right outer join departments d

on e.department_id=d.department_id;

 

完全外连接：123（106+1+16）

select e.last_name, d.department_name

from employees e full outer join departments d

on e.department_id=d.department_id;

 

多表连接的扩展：

n张表连接：

select e.last_name, d.department_name, l.city

from employees e, departments d, locations l

where e.department_id=d.department_id

and d.location_id=l.location_id;

 

select e.last_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;

 

select e.last_name, d.department_name, l.city

from employees e, departments d, locations l

where e.department_id=d.department_id(+)

and d.location_id=l.location_id(+);

 

select e.last_name, d.department_name, l.city

from employees e left outer join departments d on e.department_id=d.department_id

               left outer join locations l on d.location_id=l.location_id;

 

练习：

查询所有员工姓名，部门名称，部门所属城市(city)，国家(country)和区域(region)名称，对于空值用“无”代替。(N/A)

（使用oracle和sql99的语法）

select e.last_name, d.department_name, l.city, c.country_name, r.region_name

from employees e, departments d, locations l, countries c, regions r

where e.department_id=d.department_id(+)

and d.location_id=l.location_id(+)

and l.country_id=c.country_id(+)

and c.region_id=r.region_id(+);

 

select e.last_namee.last_name, d.department_name, l.city, c.country_name, r.region_name

from employees e

left outer join departments d on e.department_id=d.department_id

left outer join locations l on d.location_id=l.location_id

left outer join countries c on l.country_id=c.country_id

left outer join regions r on c.region_id=r.region_id;

 

自连接：

empid       ename      mgrid

100            abc           

101            def             100

102            xyz             100

 

emp:                                                                 mgr:

empid       ename      mgrid                          empid       mgrname

100            abc                                                 100            abc

101            def             100

102            xyz             100

 

 

101            def             100            100            abc

102            xyz             100            100            abc

 

select emp.ename, mgr.mgrname

from emp, mgr

where emp.mgrid=mgr.empid

 

emp:                                                                 mgr:

empid       ename      mgrid                          empid       ename      mgrid

100            abc                                                 100            abc

101            def            100                              101            def             100

102            xyz             100                              102            xyz             100

select e.last_name, m.last_name

from employees e, employees m

where e.manager_id=m.employee_id;

 

有经理的员工数：106

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

没有经理的员工数：1

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

练习：

显示所有员工姓名和经理姓名，没有经理的显示“无”。

select e.last_name, nvl(m.last_name, 'N/A')

from employees e, employees m

where e.manager_id=m.employee_id(+);

 

不等值连接：

conn scott/tiger

select e.ename, sg.grade

from emp e, salgrade sg

where e.sal between sg.losal and sg.hisal;

 

练习：

找出工资大于所在部门平均工资的员工姓名。

create table avg_sal_dept as select department_id, avg(salary) avg_sal from employees where department_id is not null group by department_id;

select e.last_name, e.salary, asd.avg_sal

from employees e, avg_sal_dept asd

where e.department_id=asd.department_id

and e.salary>asd.avg_sal;

 

select e.last_name, e.salary, asd.avg_sal

from employees e, (select department_id, avg(salary) avg_sal from employees where department_id is not null group by department_id) asd

where e.department_id=asd.department_id

and e.salary>asd.avg_sal;

子查询

单行子查询的思路：

SQL> select salary from employees where last_name='Feeney';

SQL> select last_name from employees where salary>3000;

SQL> select last_name from employees where salary>(select salary from employees where last_name='Feeney');

多行子查询的思路：

SQL> select distinct department_id from employees where department_id is not null;

SQL> select department_name from departments where department_id in (10, 20,30);

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

用多表连接改写：

select distinct d.department_name

from employees e, departments d

where e.department_id=d.department_id

 

for dept in 1..27

  for emp in 1..107

查看emp中是否出现deptid

 

练习：

工资大于全公司平均工资的员工姓名。

SQL> select last_name from employees where salary>(select avg(salary) from employees);

和Feeney同年入职的员工姓名

select last_name, hire_date

from employees

where extract(year from hire_date)=

(select extract(year from hire_date) from employees where last_name='Feeney')

and last_name != 'Feeney';

select last_name, hire_date

from employees

where hire_date between

(select to_date(to_char(hire_date, 'yyyy')||'0101', 'yyyymmdd') from employees where last_name='Feeney')

and

(select to_date(to_char(hire_date, 'yyyy')||'1231', 'yyyymmdd') from employees where last_name='Feeney')

在Seattle工作的所有员工姓名

select last_name

from employees

where department_id in

(select department_id from departments

where location_id=

(select location_id from locations where city='Seattle'));

查找符合下列条件的员工姓名：和Abel在同一个部门，工资比Olson高

select last_name from employees

where department_id=

(select department_id from employees where last_name='Abel')

and salary >

(select salary from employees where last_name='Olson');

 

配对子查询：

和Feeney在同一个部门、做同一职位的员工姓名：

select last_name, department_id, job_id

from employees

where department_id=

(select department_id from employees where last_name='Feeney')

and job_id=

(select job_id from employees where last_name='Feeney')

and last_name != 'Feeney';

select last_name, department_id, job_id

from employees

where (department_id, job_id)=

(select department_id, job_id from employees where last_name='Feeney')

and last_name != 'Feeney';

 

in和notin受null值的影响：

所有管理者的姓名：

SQL> select last_name from employees where employee_id in (select manager_id from employees);

所有普通员工的姓名：

SQL> select last_name from employees where employee_id not in (select manager_id from employees where manager_id is not null);

 

关联子查询：

工资大于所在部门平均工资的员工姓名。

for i in 1..107所有员工

{

select avg(salary) from employees where department_id=i.department_id

if i.salary > i所在部门的平均工资

保留此记录

}

select last_name，salary,department_id

from employees outer

where salary >

(select avg(salary) from employees

 where department_id = outer.department_id)

order by department_id;

 

select e.last_name, e.salary, asd.avg_sal

from employees e, (select department_id, avg(salary) avg_sal from employees where department_id is not null group by department_id) asd

where e.department_id=asd.department_id

and e.salary>asd.avg_sal;

 

exists/not exists查询：

for i in 1..27所有部门

{

  forjin1..107所有员工

{

  if i.department_id = j.department_id

保留此记录

  break

}

}

select department_name

from departments outer

where exists

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

 

select department_name

from departments outer

where not exists

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

 

练习：

所有管理者的姓名：

for i in 1..107所有员工

{

  forjin1..107所有员工

{

  if i.employee_id = j.manager_id

保留此记录

  break

}

}

select last_name

from employees outer

where exists

(select 1 from employees where manager_id=outer.employee_id);

 

select last_name

from employees

where employee_id in

(select manager_id from employees);

 

 

 

select last_name

from employees,(select distinct(manager_id) from employees)asd

where employee_id=asd.manager_id

所有普通员工的姓名：

select last_name

from employees outer

where not exists

(select 1 from employees where manager_id=outer.employee_id);

 

 

select last_name

from employees

where employee_id not in(select manager_id from employees  where manager_id is not null);

 

子查询和多表连接的转换：

有员工的部门的名称

select department_name

from departments

where department_id in

(select department_id from employees);

 

select department_name

from departments outer

where exists

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

 

select distinct d.department_name

from employees e, departments d

where e.department_id=d.department_id;

 

练习：

在Seattle工作的所有员工姓名（使用子查询和多表连接两种方式）

select last_name

from employees

where department_id in

(select department_id from departments

where location_id=

(select location_id from locations where city='Seattle'));

 

select e.last_name

from employees e, departments d, locations l

where e.department_id=d.department_id

and d.location_id=l.location_id

and l.city='Seattle';

 

最大值查询：

SQL> select last_name from employees where salary=(select max(salary) from employees);

 

top-N查询：

SQL> select last_name, salary from employees where rownum<=3 order by salary desc;

SQL> select * from (select last_name, salary from employees order by salary desc) where rownum<=3;

 

分页查询：

SQL> select * from

(select * from

(select * from

(select last_name, salary from employees order by salary desc)

where rownum<=6)

order by salary)

where rownum<=3

order by salary desc;

 

SQL> select last_name, salary

 from (select rownum row_num, v1.*              from

               (select last_name, salary from employees order by salary desc) v1

         ) v2

 where row_num between 4 and 6;

 

select * from

(select rownum num,last_name,salary from

(select last_name,salary from employees order by salary desc))

where num between 4 and 6

 

 select last_name, salary

 from (select rownum row_num, v1.*

             from

               (select last_name, salary from employees order by salary desc) v1

             where rownum<=6

         ) v2

 where row_num >= 4;

集合操作

select employee_id, job_id from employees

union all

select employee_id , job_id from job_history;

 

select employee_id, job_id from employees

union

select employee_id, job_id from job_history;

 

select employee_id, job_id from employees

intersect

select employee_id, job_id from job_history;

 

select employee_id from employees

minus

select employee_id from job_history;

 

select employee_id, job_id, salary from employees

union all

select employee_id, job_id, null from job_history;

 

select employee_id, job_id, to_char(salary) from employees

union all

select employee_id, job_id, 'no salary' from job_history;

 

集合排序：

select employee_id, job_id, salary from employees

union all

select employee_id, job_id, null from job_history

orderbysalary;

 

select employee_id, job_id, null from job_history

union all

select employee_id, job_id, salary from employees

orderby 3;

DML

insert:

SQL> create table t1(x int, y char(1), z date);

SQL> insert into t1(x, y, z) values (1, 'a', sysdate);

SQL> insert into t1(x, z, y) values (2, sysdate+1, 'b');

SQL> insert into t1(x, y, z) values (1, null, sysdate);

SQL> insert into t1(x, z) values (2, sysdate+1);

SQL> insert into t1 values (1, null, sysdate);

 

SQL> create table my_emp as select * from employees;

SQL> create table my_emp as select last_name, salary from employees where department_id=50;

SQL> create table avg_sal as select department_id, avg(salary) avg_sal from employees group by department_id;

SQL> create table my_emp as select * from employees where 1=0;

SQL> insert into my_emp select * from employees;

 

update:

SQL> update my_emp set salary=salary*1.1;

SQL> update my_emp set salary=salary*1.1 where department_id=50;

SQL> update my_emp set salary=salary*1.1, commission_pct=0.5 where employee_id=197;

 

delete:

SQL> delete from my_emp where employee_id=197;

SQL> delete from my_emp where department_id=50;

SQL> delete from my_emp;

 

子查询：

SQL> create table my_emp as select * from employees;

SQL> alter table my_emp add(department_name varchar2(30));

SQL> update my_emp outer set department_name=(select department_name from departments where department_id=outer.department_id);

 

update (select t1.department_name as aname,t2.department_name bname from my_emp t1 ,departments t2 where t1.department_id=t2.department_id) set aname=bname;

练习：

在new_dept表中删除没有员工的部门

SQL> create table my_dept as select * from departments;

delete from my_dept outer

where not exists

(select 1 from my_emp

where department_id=outer.department_id);

 

delete和truncate：

                                     delete                                   truncate

语句类型                   dml                                        ddl

undo数据                  产生大量undo数据       不产生undo数据

空间管理不释放                                 释放

语法                            where                                   删除全部数据

DDL

字符串：

SQL> create table t1(x char(10), y varchar2(10));

SQL> insert into t1 values('x', 'y');

SQL> select dump(x), dump(y) from t1;

数值：

SQL> create table t1(x number(5,2), y number(5));

SQL> insert into t1 values (123.45, 12345);

SQL> insert into t1 values (12.345, 12345);

SQL> insert into t1 values (12.345, 123.45);

SQL> select * from t1;

SQL> insert into t1 values (12.345, 112345);

日期时间：

SQL> create table t1(a date, b timestamp, c timestamp with time zone, d timestamp with local time zone);

SQL> insert into t1 values (sysdate, systimestamp, systimestamp, systimestamp);

SQL> alter session set time_zone='+9:00';

SQL> select * from t1;

修改表结构：

SQL> alter table t1 add(e char(10));

SQL> alter table t1 drop(e);

SQL> alter table t1 modify(d not null);

 

约束条件：

字段（列）：not null, check(salary>0)

行与行：primary key, unique

表与表之间：foreign key

create table dept (

    deptno int constraint dept_deptno_pk primary key,

    dname varchar2(20) constraint dept_dname_nn not null);

 

create table emp (

    empno int constraint emp_empno_pk primary key,

    ename varchar2(20) constraint emp_ename_nn not null,

    email varchar2(50) constraint emp_email_uq unique,

    salary int constraint emp_salary_ck check(salary>0),

    deptno int constraint emp_deptno_fk references dept(deptno))

 

SQL> select constraint_name, constraint_type from user_constraints where table_name in('DEPT', 'EMP');

 

SQL> insert into emp values (100, 'abc', 'abc@123.com', 10000, 10);

insert into emp values (100, 'abc', 'abc@123.com', 10000, 10)

*

ERROR at line 1:

ORA-02291: integrity constraint (HR.EMP_DEPTNO_FK) violated - parent key not

found

 

 

SQL> insert into dept values (10, 'sales');

 

1 row created.

 

SQL> insert into dept values (10, 'market');

insert into dept values (10, 'market')

*

ERROR at line 1:

ORA-00001: unique constraint (HR.DEPT_DEPTNO_PK) violated

 

 

SQL> insert into dept values (20, 'market');

 

1 row created.

 

SQL> commit;

 

Commit complete.

 

SQL> insert into emp values (101, 'def', 'def@123.com', 10000, 20);

 

create table emp (

    empno int constraint emp_empno_pk primary key,

    ename varchar2(20) constraint emp_ename_nn not null,

    email varchar2(50) constraint emp_email_uq unique,

    salary int constraint emp_salary_ck check(salary>0),

    deptno int constraint emp_deptno_fk references dept(deptno) on delete set null)或者on delete cascade

 

instead of trigger视图触发器

 

序列：

SQL> create sequence test_seq increment by 1 start with 1 maxvalue 1000 nocycle cache 20;

SQL> create table t1(x int primary key, y int);

SQL> insert into t1 values (test_seq.nextval, 11);             反复执行

SQL> select * from t1;

 

索引：

主键和唯一性约束自动创建索引：

SQL> select constraint_name, constraint_type from user_constraints where table_name='EMPLOYEES';

SQL> select index_name, index_type from user_indexes where table_name='EMPLOYEES';

SQL> set autot on

SQL> select last_name from employees where employee_id=100;         走索引

网站栏目：oracle学习笔记
分享网址：http://ybzwz.com/article/ipsdjd.html