查詢當前用戶：show user
查看當前用戶下的表：select *from tab;
設置行寬： show linesize;(查看行寬) set linesize 120;(設置行寬)
設置列寬：col 列名 for a8; (設置字符串) col 列名 for 9999;(設置數字的)
查看表的結構：dese emp;
查詢所有的員工信息：select * from emp;
投影：select empno,ename,sal from emp;
濾空函數：select empno,ename,(sal*12+nvl(comm,0)) from emp;
取別名的三種方式：
as "別名"
"別名"
別名 ——不能用關鍵字
去重：distinct
select distinct (列) from emp;
select distince 列,列 from emp;
字符串的連接
select concat('hello','world') from emp;
select 'hello'||'world' from emp;
可用偽表來測試：dual;
where的應用：
查詢10號部門的員工：
select * from emp where deptno=10;
查詢名字叫KING的員工
select * from emp where ename='KING';
修改日期格式：
select * from v$nls_parameters;(查詢日期格式) alter session/system set NLS_DATE_FORMAT='yyyy-mm-dd'; (一般修改當前會話session）
between...and... 在...之間
in/not in
is null 為空的
is not null 不為空的
模糊查詢：
select * from emp
where job like'%H%' 包含了H的 / 'H%' 以H開頭 / '%H' 以H結尾的 / '_H%' 第二位為H的 / '%\_%'escape'\' 找帶有_的;
commit 提交
rollback 事務回滾
a兩個空格; 語句後追加
排序：
order by 默認升序(asc) + desc降序
order by +列、表達式、別名、序號
order by 字段1,字段2; 先用字段1來排序，其中相同的再用字段2來排
null 是oracle中的最大值
null last 讓空值最後顯示
字符函數：
upper 小寫轉換大寫
cower 大寫轉換小寫
initcap 首字母大寫
//select upper('hello') 大寫,lower('HELLO') 小寫,initcap('hello world') 首字母大寫 from dual;
length 獲取字符的長度
//select length('hello') 長度,lengthb('hello') from dual;
lengthb 獲取字節的長度
//select length('中') 長度1,lengthb('中') 長度2 from dual;
replace 替換
//select replace('hello world','l','a') from dual;
substr 截取
//select substr('hello world',2,4) from dual;
instr 查找字符串中某個字符的位置
//select instr('hello world','o') from dual;
lpad 左補齊
//select lpad(999,10,'*') from dual; *******999
rpad 右補齊
trim 去除前後的某個字符或字符串
select trim('h' from 'hello world') from dual;
數值函數：
round 四捨五入小數
//select round(45.926,2) 數據1,round(45.926,1) 數據2,round(45.926,0) 數據三,round(45.926,-1) 數據4, round(45.926,-2) 數據5 from dual;
trunc 截斷小數
//select trunc(45.926,2) 數據1,trunc(45.926,1) 數據2,trunc(45.926,0) 數據三, trunc(45.926,-1) 數據4, trunc(45.926,-2) 數據5 from dual;
mod 除數取余
//select mod(10,3) from dual;
日期函數：
sysdate 今天
sysdate+1 明天
sysdate-1 昨天
// select sysdate - (sysdate -1) from dual; 今天減去昨天
last_day 某一月的最後一天
//select last_day(sysdate) from dual;
next_day 獲取下個星期幾
//next_day(sysdate,"星期三")
add_moths 追加幾個月
//add_months(sysdate,3)
months_beween 精確計算月份
//months_beween(sysdate.hiredate)
round 四捨五入日期
//select round(sysdate,'year') from dual;
truuc 截斷日期
//select round(sysdate,'month') from dual;
轉換函數：
to_char() 將其他類型的數據轉換為字符串
//select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')from dual;
//select to_char(12535,'L99,999') from dual;
to_date： 將其他格式的數據轉換為日期格式
//select to-date(sydate) from dual;
to_number 將其他格式的數據轉換為數字
//select to_number('999') from dual;
通用函數：
nvl: 慮空函數
nvl2: 是nvl函數的一個加強版 nvl2(a,b,c) 如果a是空的則返回c，否則返回b
//select comm,nvl2(comm,comm,100) from emp;
nullif nullif(a,b) 如果a等於b則返回空，否則返回a
//select nullif('hello','world') from dual;
coalesce(a,b,c...) 從左往右一次查找第一個不為空的值返回
漲工資：
第一種方式：case....end SQL99語句
select job, sal,
case
when job = 'SALESMAN',sal+800
when job = 'MANAGER' then sal+1000
when job = 'CLERK' then sal + 900
else sal+400
end
from emp;
第二種方式：用decode Oracle中獨有的
select job,sal,decode(job,'SALESMAN',sal+800,'MANAGER',sal+1000,'CLERK',sal+900,sal+400) from emp;
多行函數
多行函數也稱組函數，自帶濾空功能
nvl(com ,0) 取消濾空
sum 求和
count(*) 統計
//select count(empno) from emp;
avg 平均值
max 最大值
min 最小值
group by 分組
//select sum(sal) ,avg(nvl(sal,0))
deptno from emp
group by;//
查詢部門平均工資大於2000的部門編號：
//select deptno,avg(sal) 平均工資
from emp
group by deptno
having avg(sal) > 2000;//
求20號部門的平均獎金和獎金總和
//select sum(comm) 總和,avg(comm) 獎金1,sum(comm)/count(*) 獎金2
from emp
where deptno=20
group by deptno
查詢大於本部門的平均工資的員工信息
//select ename,empno,deptno,avg(sal) 平均
from emp
group by deptno,ename,empno
//
group by 和where 不能一起使用，可以用having來代替
SQL中的順序
1、select *from emp
2、where
3、group by
4、having
增強的group by ——rollup()
//select deptno,job,sum(sal)
from emp
group by rollup(deptno,job);
//
break on deptno skip 2; 可用來調整格式
break on null; 用來取消上面的格式
注意：
where後面不能跟組函數
如條件中要使用組函數，那麼只能用having條件
如果條件不需要使用組函數，可以使用where也可以使用having，推薦使用where
多表查詢：
笛卡爾集：
//select e.empno,e.ename,job,d.deptno,d.dname
from emp e,dept d
等值鏈接：等值連接 用等號去連接2個表，但是一定要注意連接的2個表一定要有關系
//select e.empno,e.ename,job,d.deptno,d.dname
from emp e,dept d
where e.deptno = d.deptno;
非等值連接：
//select e.empno,e.sal,s.grade,s.losal,s.hisal
from emp e,salgrade s
where e.sal between s.losal and s.hisal;
//-- 統計每一個部門的人數
-- 顯示部門號 部門名稱 部門人數
select e.deptno,d.dname,count(*)
from emp e,dept d
where e.deptno = d.deptno
group by e.deptno,d.dname;
外連接：分類左外連接和右外連接 左外連接以左邊表為主表，主表會顯示所有，另一個表如果沒有顯示空,右外連接以右邊表為主表.....
左外連接: where e.deptno = d.deptno 不成立的時候,無論右邊是否有數據與之對應,左邊的都要顯示 where e.deptno = d.depton(+)
select d.deptno,d.dname,count(e.empno)
from emp e,dept d
where e.deptno = d.deptno(+)
group by d.deptno,d.dname;
右外連接: where e.deptno = d.deptno 不成立的時候,無論左邊表是否有數據與之對應,右邊表都會顯示 where e.deptno(+) = d.deptno;
select d.deptno,d.dname,count(e.empno)
from emp e,dept d
where e.deptno(+) = d.deptno
group by d.deptno,d.dname;
自連接：只有一張表，虛擬出一張自身表；自連接會產生大量的笛卡爾集，只能做數據量小的表，對於大表用層次查詢
select e.ename 領導名字,e.empno 領導ID,p.ename 員工名字,p.empno 員工ID
from emp e,emp p
where e.empno = p.mgr;
層次查詢：
select e.ename 領導名字,e.empno 領導ID
from emp e
connect by prior empno = mgr
start with mgr is null;
子查詢：
子查詢解決什麼問題: 一個查詢查詢不到結果的時候，可以使用子查詢來豐富查詢的條件
子查詢的格式: 用一個小括號包含,然後在裡面寫sql語句
注意事項：
1. 一定要有小括號
2. 一定要注意你的書寫格式
3. 子查詢可以放在select,from ,where having,order by 後面
4. 子查詢一定不能放在group by的後面
5. 子查詢和主查詢可以不是同一張表,只要子查詢的結果在主查詢中能用就可以
5. 子查詢中一般都不排序,但是在TOP-N中需要排序
7. 一般是先執行子查詢操作,在執行主查詢操作,但是在相關子查詢中先執行主查詢，在執行子查詢
8. 對於子查詢的結果，如果是單行只能用單行操作符，如果是多行，只能用多行操作符
9. 自查中的null處理
-- 子查詢放在select後面
// select ename,job,(select sal from emp where empno=7566) 子查詢 from emp;
-- from後面放置子查詢 重點要掌握的
//select * from (
select ename,job,sal from emp
);
-- where 後面跟子查詢 ——-- 一定要注意的地方: 子查詢不能過多的去嵌套，一般嵌套三層，加多了就影響性能
//select * from
emp where sal > (
select sal from emp where ename = (
select ename from emp where empno = 7566
)
);
-- order by 後面跟子查詢 order by 後面不能跟子查詢,語法上是可以跟的，但是跟了不起作用 -- group by 後面不能跟子查詢
//select * from emp order by (select count(*) from emp where ename='SCOTT');
//select * from emp order by (select empno from emp where ename='SCOTT') desc;
//select * from emp order by (select count(*) from emp where ename='SCOTT') desc;
--子查詢和主查詢可以不是同一張表,只要子查詢的結果在主查詢中能用就可以
-- 查詢部門名稱為SAL的所有的員工信息
//select *
from emp
where deptno = (
select deptno from dept where dname='SALES'
);
對於子查詢的結果，如果是單行只能用單行操作符，如果是多行，只能用多行操作符
單行操作符: ><= <= !=
多行操作符 int not in any all
//select * from emp where deptno in(
select deptno from emp where ename='SCOTT' or ename='CLARK'
);
//select * from emp where deptno not in(
select deptno from emp where ename='SCOTT' or ename='CLARK'
);
//select * from emp where sal > any(
select sal from emp where deptno = 30
);
集合：借鑒：http://1632004.blog.163.com/blog/static/29991497201282653334529/
注意：集合運算的時候必須要保證每一個集合擁有同樣多的列數，並且每一列的類型必須要一致,如果不一致可以使用to_char(null)或者to_number(null) 來補齊，補齊的時候一定要注意類型
課堂練習：
1、找到員工表中工資最高的前三名
//select rownum r,empno,ename,sal
from (select empno,ename,sal from emp order by sal,ename,empno
desc)
where rownum<=3
rownum ——偽列 可用來做表的序列號
1、它是按照默認的順序生成，一單生成後不在發生變化
2、它只能使用< <=，不能使用> >=
//select * from (
select rownum r,empno,sal
from (
select rownum,empno,sal
from emp
order by sal desc
)
where rownum < 5
)
where r > 2;
rowid ——也是偽列 返回的是地址，可用來檢索
2、找到員工表中薪水大於本部門平均薪水的員工
//select empno,ename,sal, (select avg(sal) agv from emp where deptno = e.deptno group by deptno) avgsal
from emp e,(select deptno,avg(sal) agv from emp group by deptno) t
where e.deptno = t.deptno and e.sal > t.agv
//select empno,ename,sal,(select avg(sal) from emp where deptno = e.deptno) avgsal
from emp e
where sal > (
select avg(sal) from emp where deptno = e.deptno
);
3、統計每年入職的員工（不能用子查詢）
//select count(ename) total,sum(decode(to_char(hiredate,'yyyy'),'1980',1,0)) "1980" from emp;
表相關：
insert 插入數據
插入一行完整的數據：insert into emp (empno,ename) values (1,'ss');
insert into empvalues (插入的數據) 直接插入值，每一列都要有
更新表裡的數據：update
//update emp set sal=1300 where empno=2015;
//update emp set job ='值',mgr='值' where empno= 2015;
刪除表裡的數據：
//delete from emp where empno =2015;
復制表結構：
//create table 表名 as select * from emp where 1=2;
復制表：
//create table 表名 as select * from emp;
根據自己的需求創建一張表：
//create table 表名
(id number,name varchar(20),pwd varchar2(20))
刪除表：
//drop table 表名;
//delete from 表名;
//truncate table 表名;
修改表裡列的名字：
//alter table 表名 rename column 名字 to 修改的名字;
修改表的類型：
//alter table 表名 modfy 列名 類型;
刪除表裡的某一列：
//alter table 表名 drop column 列名;
向表裡追加一列：
//alter table 表名 add 列名 類型;
修改表名（重命名）：
//rename 表名(原) to 表名：
delete 和truncate 的差別：
1、delete是DML操作，truncate是DDL操作
2、delete會產生碎片，而truncate不會
3、delete不會真正的刪除表數據，而truncate會將整個表刪除以後再從新新建一個表
4,、delete不會釋放空間，而truncate會
事物：
將多個操作做成一個原子，要麼這幾個操作都成功，要麼都失敗
事物的開啟和關閉
開啟：
顯示開啟：start transaction
隱示開啟：執行第一個DML的時候會開啟事物
關閉;
顯示關閉：commit rollback
隱示關閉：非正常關閉
創建閃回點：savepoint a;
回滾到閃回點：rollback to a;
其他4個對象：
視圖：
是一個虛擬化的表，是一個邏輯表，本身不包含數據
創建一個視圖：
//create view 名 as select * from emp;
刪除一個視圖：
//drop view 名
清空回收站：purge reyclebin
創建視圖要給用戶授權：
用SYS給Scott用戶授權：
grant create view to scott;
移除授權：
revoke create view from scott;
索引：index
主要用來優化查詢效率
create index 名 on 表名( 列名)
注意：
通過查看索引的條用日志才能看到是否調用了索引
索引使用用於經常發生變化的列
索引可以提高查詢的效率，但是不適應於經常變化的列上創建索引
序列：
一組由oracle來負責維護的數組，主要用來做ID的自動增長
創建序列：sequence
create sequence 名
序列的兩個成員變量：nextval(取下一個值),currval(當前的值)
//inser into emp10 (empno,ename) values(mgse.nextval,'ss');
//select mgse.currval from dual;
刪除序列：drop sequeale 名;
復雜的序列創建：
create sequence 名
increment by 2 增加2
start with 100 從100開始
minvalue 100 最小100
maxvalue 200 最大200
nocache 不緩存
同義詞： synonym 別名
主要用來給另外一個用戶的表取別名
create synonym emp,foremp;
Oracle中表的約束條件
1、主鍵約束：用來唯一表示一行記錄
2、非空約束：用來控制某一列不能為空 not null
3、檢查約束：用來控制某一列的值只能是固定幾個
4、外鍵約束：別約束的這一列的值是另一個表的主鍵
5、唯一性約束：有些值唯一
