1. 安裝virtualbox：
成都網(wǎng)站設(shè)計(jì)、網(wǎng)站建設(shè)的開發(fā)，更需要了解用戶，從用戶角度來建設(shè)網(wǎng)站，獲得較好的用戶體驗(yàn)。創(chuàng)新互聯(lián)多年互聯(lián)網(wǎng)經(jīng)驗(yàn)，見的多，溝通容易、能幫助客戶提出的運(yùn)營建議。作為成都一家網(wǎng)絡(luò)公司，打造的就是網(wǎng)站建設(shè)產(chǎn)品直銷的概念。選擇創(chuàng)新互聯(lián)，不只是建站，我們把建站作為產(chǎn)品，不斷的更新、完善，讓每位來訪用戶感受到浩方產(chǎn)品的價(jià)值服務(wù)。
# yum install gcc kernel-devel kernel-headers
# yum install virtualbox-5....
# /etc/init.d/vboxdrv setup 手動編譯內(nèi)核模塊，安裝時(shí)自動完成
# usermod -G vboxusers root
2. 安裝linux：
創(chuàng)建虛擬機(jī)：
名稱：oracle11gR2_RHEL6.4_x64
類型：linux 64bit
內(nèi)存：2048MB
硬盤大?。?00GB
設(shè)置：
啟動順序：硬盤，網(wǎng)絡(luò)
網(wǎng)絡(luò)：網(wǎng)卡1，橋接eth0
去除聲音、usb設(shè)備
安裝：desktop方式
主機(jī)名：node1.test.com，ip使用dhcp，swap4GB，其余給/
3. 調(diào)整linux系統(tǒng)：
關(guān)閉防火墻：
# service iptables stop
# service ip6tables stop
# chkconfig iptables off
# chkconfig ip6tables off
管理工具中disabled防火墻
關(guān)閉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增強(qiáng)功能：
# yum -y install gcc kernel-devel
# ln -s /usr/src/kernels/2.6.32-431.el6.x86_64/ /usr/src/linux
設(shè)備-->安裝增強(qiáng)功能
右鍵eject彈出光盤
4. 確認(rèn)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
臨時(shí)修改
# 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. 創(chuàng)建用戶：
# groupadd -g 1000 oinstall
# groupadd -g 1001 dba
# groupadd -g 1002 oper
# useradd -u 1000 -g oinstall -G dba,oper oracle
# passwd oracle
8. 修改內(nèi)核參數(shù)：
# 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. 創(chuàng)建目錄：
# 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文件解析主機(jī)名：
# 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
或者：
在物理主機(jī)中解壓縮2個(gè)安裝包，然后共享給虛擬機(jī)，命令如下：
# mkdir /database
# mount -t vboxsf database /database
14. 圖形界面安裝：
# xhost +
# su - oracle
$ cd /database/
$ ./runInstaller
略
以root身份執(zhí)行兩個(gè)腳本
15. 創(chuàng)建監(jiān)聽和數(shù)據(jù)庫：
圖形界面下創(chuàng)建監(jiān)聽，oracle執(zhí)行：
$ netca
$ lsnrctl status
$ netstat -tlnp | grep 1521
圖形界面創(chuàng)建db，oracle執(zhí)行：
$ 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/
點(diǎn)左上角+號，創(chuàng)建新連接：
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的網(wǎng)絡(luò)設(shè)置（可選）：
全局設(shè)置à添加hostonly網(wǎng)絡(luò)，設(shè)置ip為：192.168.0.253，取消dhcp（linuxonly）
虛擬機(jī)網(wǎng)絡(luò)改為hostonly
虛擬機(jī)中將網(wǎng)卡的ip改為靜態(tài)，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
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
練習(xí)：
查看scott用戶名下的表，解鎖scott用戶：
scott/tiger
2. 使用sqlplus的全屏編輯功能：
$ echo $EDITOR
SQL>select * from hr.employees;
SQL> ed
SQL> / 執(zhí)行
3. 基礎(chǔ)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;
練習(xí)：
輸出下列員工信息：
Eleni(first_name) Zlotkey(last_name) employeeid is ... at department .. total salary is …
4. 使用連字符構(gòu)造語句：
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去除標(biāo)題
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 手動去除沒用的行，相當(dāng)于除去標(biāo)題和回饋，
SQL> @/home/oracle/grant.sql 執(zhí)行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; []<>()都可以
數(shù)字條件：
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');
區(qū)間查詢：
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 '\';禁止轉(zhuǎn)義符后面的符號的特殊含義，like _通配某一個(gè)字符，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;
練習(xí)：
選擇部門30中的雇員
列出所有職員(CLERK)的姓名、編號和部門
找出薪金大于5000的雇員
找出獎金高于0.1的雇員
找出部門50中的所有員工和部門30中的經(jīng)理的詳細(xì)資料
找出收取獎金的雇員的不同工作職位每種職位顯示一次
找出不收取獎金或收取的工資低于5000的雇員
顯示last_name不帶有'R'的雇員姓名
select last_name name from employees where not last_name like '%R%'；
顯示所有雇員的姓名、工作和薪金，按工作的降序順序排序，而工作相同時(shí)按薪金升序
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;
練習(xí)：
找出各月最后三天內(nèi)受雇的所有雇員
extract(month from hire_date+4) != extract(month from hire_date)
找出早于25年之前受雇的雇員
months_between(sysdate, hire_date)/300>=25
顯示正好為6個(gè)字符的雇員姓名
length(last_name)=6
顯示所有雇員的姓名的前三個(gè)字符
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;
練習(xí)：
查詢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;
其他函數(shù)：
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;
練習(xí)：
顯示所有員工部門編號，沒有部門的顯示“未分配部門”
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;
練習(xí)：
按照員工工資，對員工分級顯示：
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; 去除重復(fù)值
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; 錯誤語法
練習(xí)：
公司中不同職位的數(shù)量
select job_id,count(job_id)
from employees
group by job_id；
計(jì)算每個(gè)部門的人數(shù)
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;
練習(xí)：
按部門求出所有有部門的普通員工的平均工資，部門平均工資少于5000的不顯示，最終結(jié)果按平均工資的降序排列。
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
獲取如下信息，準(zhǔn)備工作：
employees:
員工總數(shù)：107
SQL> select count(*) from employees;
有部門的員工數(shù)：106
SQL> select count(*) from employees where department_id is not null;
SQL> select count(department_id) from employees;
沒有部門的員工數(shù)：1
SQL> select count(*) from employees where department_id is null;
departments：
部門總數(shù)：27
SQL> select count(*) from departments;
有員工的部門數(shù)：11
SQL> select count(distinct department_id) from employees;
沒有員工的部門數(shù)：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表中出現(xiàn)
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;
內(nèi)連接：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;
多表連接的擴(kuò)展：
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;
練習(xí)：
查詢所有員工姓名，部門名稱，部門所屬城市(city)，國家(country)和區(qū)域(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;
有經(jīng)理的員工數(shù)：106
SQL> select count(*) from employees where manager_id is not null;
沒有經(jīng)理的員工數(shù)：1
SQL> select count(*) from employees where manager_id is null;
練習(xí)：
顯示所有員工姓名和經(jīng)理姓名，沒有經(jīng)理的顯示“無”。
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;
練習(xí)：
找出工資大于所在部門平均工資的員工姓名。
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中是否出現(xiàn)deptid
練習(xí)：
工資大于全公司平均工資的員工姓名。
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在同一個(gè)部門，工資比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在同一個(gè)部門、做同一職位的員工姓名：
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);
關(guān)聯(lián)子查詢：
工資大于所在部門平均工資的員工姓名。
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);
練習(xí)：
所有管理者的姓名：
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);
子查詢和多表連接的轉(zhuǎn)換：
有員工的部門的名稱
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;
練習(xí)：
在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;
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;
練習(xí)：
在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數(shù)據(jù) 產(chǎn)生大量undo數(shù)據(jù) 不產(chǎn)生undo數(shù)據(jù)
空間管理不釋放 釋放
語法 where 刪除全部數(shù)據(jù)
字符串：
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;
數(shù)值：
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);
日期時(shí)間：
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;
修改表結(jié)構(gòu)：
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視圖觸發(fā)器
序列：
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); 反復(fù)執(zhí)行
SQL> select * from t1;
索引：
主鍵和唯一性約束自動創(chuàng)建索引：
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; 走索引
重慶分公司
重慶分公司
