重庆分公司
重庆分公司，新征程启航
为企业提供网站建设、域名注册、服务器等服务
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)
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://cqcxhl.cn/article/ipsdjd.html