1.按照salary排序 顯示s_emp 表中的 id first_name salary 如果工資相同
? 則使用 first_name 降序排列?
2.寫出處理字符串的 函數(shù)有哪些? 寫 5 個
3.分別寫出 round(12.88)? trunc(12.88)? round(12.88,1)? trunc(12.88,1)
? round(3.33)? trunc(3.33)? 的值
4.顯示 s_emp 表中first_name 的前三個字符? 和 后三個字符? 并把這些數(shù)據(jù) 變大寫
5.顯示s_emp 表中第三個字符是a的 first_name?
6.顯示 s_emp 表中 manager_id 是NULL的員工 顯示 id first_name? salary
7.顯示 s_emp 表中? first_name 叫 Carmen? 并且? salary 等于 2500 的員工
顯示? id? first_name? salary
? select? id,first_name,salary from? s_emp
? ? ? where? first_name='Carmen' and? salary=2500;?
--------------------------------------------------------------------
1.多表查詢? 10*10*10
? 1.1 為什么要有多表查詢
? ? 需要的數(shù)據(jù) 已經(jīng)被分散多張表中了。
? 1.2 如何實現(xiàn)?
? ? 查詢 每個員工的? first_name? 和 對應的部門編號
? select? first_name,dept_id from? s_emp;? ?
? ? 在上面查詢的基礎上 顯示部門名
? select? first_name,dept_id,name
? ? ? ? from? s_emp,s_dept;
? ? 上面的查詢 產(chǎn)生了一個超大集合 叫 笛卡爾積? ? ?
? ? select? first_name,dept_id,name
? ? ? ? from? s_emp,s_dept where? dept_id=id;
? 上面的語句出錯 需要使用規(guī)則:多張表中有同名的字段? 使用 表名 點 區(qū)分
? ? select? first_name,dept_id,name
? ? ? ? from? s_emp,s_dept
? ? ? ? ? ? where? dept_id=s_dept.id;
? 1.3 語法
select 需要的字段
? ? from? 表1,表2? where? 表的連接條件;
? 1.4 練習
? s_dept? 部門表? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? s_region? 地區(qū)表
? id? ? number? 部門編號? ? ? ? ? ? ? ? ? id? ? ? ? number? 地區(qū)編號?
? name? varchar2(25) 部門名? ? ? ? ? ? name? ? ? varchar2(25) 地區(qū)名
? region_id? number? 地區(qū)編號
? 顯示 每個部門的名字? 和 對應的地區(qū)的名字?
? select? s_dept.name,s_region.name
? ? from? s_dept,s_region
? ? ? ? where? region_id=s_region.id;
? 1.5 使用表的別名 簡化查詢
? select? d.name,r.name
? ? ? from? s_dept d,s_region? r
? ? ? ? where? region_id=r.id;
? 1.6 等值連接 和 非等值連接
? ? 員工? 和? 部門? ? dept_id = s_dept.id?
? ? 部門? 和? 地區(qū)? ? region_id = s_region.id
? ? 表達兩張表的關系 使用等號? 則稱之為 等值連接差牛。?
? ? 如果連接兩張表的關系 不使用等號 則稱之為非等值連接
? salgrade? 工資級別表
? SQL> desc salgrade;
Name
---------------------
GRADE? ? 級別
LOSAL? ? 級別對應的低工資
HISAL? ? 級別對應的高工資
? 顯示 s_emp 表中 每個人的id , salary? 和 salary 對應的工資級別?
? select? id,salary,grade? ?
? ? ? from? s_emp,salgrade
? ? ? ? ? where? salary? between? losal? and? hisal;
? 1.7 自連接
? ? 一張物理表中? 存儲了兩層業(yè)務含義的數(shù)據(jù)? 要把其中的一層業(yè)務含義的數(shù)據(jù)查詢出來 就需要
? ? 使用到自連接的語法层释。
? s_emp?
? ? 領導
? ? 普通員工?
? ? 把所有的領導查詢出來?
? id? ? ? manager_id
? 80? ? ? 60
? 100? ? ? 100
? 101? ? ? 100
? 102? ? ? 100
? 110? ? ? 100
? select? id,first_name from? s_emp where? id=manager_id;?
? ? ? 當有人的manager_id? 是你的id 時? 則 你就是領導
? select distinct m.id,m.first_name
? ? ? ? from? s_emp? e,s_emp m? where e.manager_id = m.id;
? 8 個領導
? 25-8=17個普通員工? ? 下面的寫法錯誤?
? select distinct m.id,m.first_name
? ? ? ? from? s_emp? e,s_emp m? where e.manager_id != m.id;
2.內連接
? ? ? 內連接的特點就是 符合連接條件的數(shù)據(jù) 被選中? 不符合連接條件的數(shù)據(jù)被過濾掉。
? ? ? 上面的? 等值連接? ? 非等值連接? 自連接 都是內連接。
3.外連接
3.1 概念和實現(xiàn)
? 外連接的結果集 等于 內連接的結果集? 加上 匹配不上的記錄
(一個也不能少)?
? 如何實現(xiàn)?
? (+)? (+) 所在的表的字段 對面的表的數(shù)據(jù)全部被匹配出來
? ? ? ? ? ? ? 底層通過 NULL 記錄匹配出來的?
? select distinct m.id,m.first_name
? ? ? ? from? s_emp? e,s_emp m? where e.manager_id(+) = m.id
? ? ? ? and? e.manager_id is null;
3.2 練習? s_emp? s_dept
? ? ? 顯示每個員工的id, first_name? 和 對應的 部門名(name)
? ? select? e.id,first_name,name
? ? ? ? from? s_emp e,s_dept d
? ? ? ? ? ? where? e.dept_id=d.id;
? ? ? 公司為了業(yè)務發(fā)展 需要抽調一部分人出來? 這些人將離開原來的部門
? update? s_emp? set dept_id = null where id < 3;
? commit;
? ? ? 顯示每個員工的id, first_name? 和 對應的 部門名(name)? 沒有部門編號的員工也要顯示
? select? e.id,first_name,name
? ? ? ? from? s_emp e,s_dept d
? ? ? ? ? ? where? e.dept_id=d.id(+);?
3.3 練習? s_dept? ? s_region
? ? 顯示每個部門的名字? 和 對應的地區(qū)的名字
? select d.id, d.name,r.name
? ? ? from? s_dept? d,s_region r
? ? ? ? ? where? d.region_id = r.id;?
? 公司業(yè)務擴大了? 增加了一些新的部門
? insert? into? s_dept? values(100,'test100',NULL);
? insert? into? s_dept? values(101,'test101',NULL);
? commit;? ? ? ? ? ? ? ? ? ? ?
? 顯示每個部門的名字? 和 對應的地區(qū)的名字? 沒有地區(qū)編號的部門也要顯示?
? select d.id, d.name,r.name
? ? ? from? s_dept? d,s_region r
? ? ? ? ? where? d.region_id = r.id(+);? ? ? ? ?
? 3.4 非等值連接的外連接? ? salgrade? s_emp
? ? 列出每個員工的 id? salary? 和? salary 對應的工資級別
? select id,salary,grade
? ? ? from? s_emp,salgrade
? ? ? ? ? where salary? between? losal and hisal;
? 把 id = 25? 和 id=24 的這兩個人的工資 改成 12345
? update? s_emp? set? salary=12345 where id >= 24;
? commit;
? 列出每個員工的 id? salary? 和? salary 對應的工資級別? 超出統(tǒng)計范圍的員工也要顯示
? select id,salary,grade
? ? ? from? s_emp,salgrade
? ? ? ? ? where salary? between? losal(+) and hisal(+);
? 4.表連接
? ? 內連接
? ? ? ? ? ? 等值連接? ? 員工和部門? ? ? 部門和地區(qū)? ? ? ?
? ? ? ? ? ? 非等值連接? 員工的工資 和 對應的工資級別
? ? ? ? ? ? 自連接? ? ? ? ? 誰是領導?
? ? 外連接
? ? ? ? ? ? 等值連接? ? 員工和部門 (找出沒有部門的員工)? ? ? 部門和地區(qū) (沒有地區(qū)編號的部門)? ? ? ?
? ? ? ? ? ? 非等值連接? 員工的工資 和 對應的工資級別? (超出統(tǒng)計范圍的工資)
? ? ? ? ? ? 自連接? ? ? ? ? 誰是普通員工?
? ? ? 外連接的結果集 等于 內連接的結果集? 加上匹配不上的記錄
? ? ? ? ? ? 如何實現(xiàn)的?
? ? ? (+)? (+)的意思所在字段的對面的表的數(shù)據(jù)全部被匹配出來
? ? ? ? ? ? 本質上 底層是通過 NULL 記錄進行的匹配
? 5.SQL99 內外連接
? ? 5.1 內連接標準
? ? from? a表? join b表? on 連接條件? where 過濾條件;
? ? from? a表? inner join b表? on 連接條件? where 過濾條件;
? ? ? ? ? 顯示 每個部門的名字 和 對應的地區(qū)的名字
? ? ? select? d.name,r.name
? ? ? ? ? from? s_dept d,s_region r
? ? ? ? ? ? ? where? d.region_id = r.id;
? ? select? d.name,r.name
? ? ? ? ? from? s_dept d? join s_region r
? ? ? ? ? ? ? on? d.region_id = r.id;
? ? select? d.name,r.name
? ? ? ? ? from? s_dept d? inner? join s_region r
? ? ? ? ? ? ? on? d.region_id = r.id;
? ? 5.2 外連接的標準
? ? a表? left outer? join? b 表? on 表的連接條件? where? 過濾條件;
? ? a表? right outer? join? b 表? on 表的連接條件? where? 過濾條件;
? ? a表? full? outer? join? b 表? on 表的連接條件? where? 過濾條件;
? ? a表? left outer? join? b 表? 就是a表發(fā)起連接? 意思就是a表的數(shù)據(jù)全部被匹配出來捌蚊。
? ? a表? right outer? join b 表? 就是b表發(fā)起連接? 意思就是 b表的數(shù)據(jù)全部被匹配出來审姓。
? ? ? ? ? 本質上也通過 NULL 記錄進行的匹配
? ? ? ? ? 顯示每個部門的名字? 和 對應的地區(qū)的名字? 沒有地區(qū)編號的部門也要顯示?
? ? ? select d.id, d.name,r.name
? ? ? ? ? from? s_dept? d,s_region r
? ? ? ? ? ? ? where? d.region_id = r.id(+);?
? ? ? select d.id, d.name,r.name
? ? ? ? ? from? s_dept? d left outer? join s_region r
? ? ? ? ? ? ? on? d.region_id = r.id;
? ? ? select d.id, d.name,r.name
? ? ? ? ? from? s_region r? right outer? join? s_dept? d
? ? ? ? ? ? ? on? d.region_id = r.id;? ? ?
? ? ? ? ? ? ? 使用左外連接? 實現(xiàn) s_emp 誰是普通員工的問題 顯示? id? first_name
? ? ? ? ? ? ? 誰是領導?40? 誰是普通員工 40? 改成 left outer? join? 20
? ? ? ? select distinct m.id,m.first_name
? ? ? ? ? ? from? s_emp? e,s_emp m
? ? ? ? ? ? ? ? where? e.manager_id(+) = m.id and e.manager_id is null;? ? ? ?
? ? ? select distinct m.id,m.first_name
? ? ? ? ? ? from? s_emp? m? left outer join s_emp e
? ? ? ? ? ? ? ? on? e.manager_id = m.id where e.manager_id is null;
? ? ? select distinct m.id,m.first_name
? ? ? ? ? ? from? s_emp? m? left? join s_emp e
? ? ? ? ? ? ? ? on? e.manager_id = m.id where e.manager_id is null;
? ? ? 5.3? full outer join?
? ? ? ? ? ? 全外連接 只是一個邏輯概念，代表左外連接的結果集 加上 右外連接的結果集 然后
? ? ? ? ? ? ? ? ? 排除重復的記錄旨椒。
? ? ? Oracle 中如何實現(xiàn)全外連接呢? 不是通過兩端都加(+) 實現(xiàn)的晓褪。
? ? ? ? ? ? 而是通過? union? ? 來實現(xiàn)的 ， union 的意思是 合并兩個結果集? 然后排重综慎。
? ? ? union? all? 合并兩個結果集? 不排重涣仿。
? ? ? select? id from? s_emp? union? select id from s_emp;
? ? ? select? id from? s_emp? union? all select id from s_emp;? ? ? ? ? ? ?
? 6.組函數(shù)
? ? 6.1 特點
? ? ? ? 對一組數(shù)據(jù)處理 最終返回一個結果? 無論sql語句影響多少行 最終只有一個結果。
? ? 6.2 常見的組函數(shù)
? ? max(par1)? 統(tǒng)計最大值
? ? min(par1)? 統(tǒng)計最小值
? ? avg(par1)? 統(tǒng)計平均值
? ? sum(par1)? 統(tǒng)計和
? ? count(par1)? 統(tǒng)計個數(shù)
? ? 6.3 練習
? ? ? ? 統(tǒng)計s_emp 表中的最高工資? 最低工資? 員工的個數(shù)
? ? select? max(salary),min(salary),count(id)? from? s_emp;
? ? ? ? 統(tǒng)計s_emp 表中的工資的和? 以及 工資的平均值
? ? select? sum(salary),avg(salary)? from? s_emp;
? ? 6.4 特殊用法
? ? ? ? 在count 中可以使用? *
? ? select? count(*) from? s_emp;
? ? ? ? 組函數(shù)中可以使用? distinct
? ? select? sum(distinct salary),avg(salary)? from? s_emp;? ?
? ? 6.5 組函數(shù) 對NULL 值如何處理?? 忽略
? ? select max(manager_id) from s_emp;
? ? select avg(manager_id) from s_emp;
? ? 7.分組
? ? 7.1 概念
? ? ? ? 按照一定的標準? 把數(shù)據(jù)分割成若干部分
? ? 7.2 提出問題
? ? ? ? 按照部門編號分組 統(tǒng)計每個部門的人數(shù)
? ? dept_id? ? count
? ? 41? ? ? ? ? 3
? ? 42? ? ? ? ? 4
? ? 50? ? ? ? ? 2
? ? 7.3 sql 中如何完成
? ? select? 字段名,統(tǒng)計的數(shù)據(jù)? from? 表名? ? where 條件
? ? ? ? group by 分組標準? having? 組數(shù)據(jù)過濾條件?
? ? ? ? ? ? order by 排序標準;
按照部門編號分組? 統(tǒng)計 每個部門的人數(shù)? ? 顯示 部門編號 和 平均工資? ? ? ? ? ?
select dept_id,count(id)?
? ? from? s_emp?
? ? ? ? group? by? dept_id;
? ? 按照部門編號 分組? 統(tǒng)計 每個部門的平均工資? 顯示 部門編號 和 平均工資
? select dept_id,avg(salary)?
? ? ? from? s_emp?
? ? ? ? group? by? dept_id;? ?
? 7.4 練習? s_dept
? ? ? 按照地區(qū)編號 分組? 統(tǒng)計每個地區(qū)中的部門個數(shù)? 顯示地區(qū)編號 和 部門個數(shù)
select region_id,count(id)
? from? s_dept
? ? ? group by region_id;
? 要求地區(qū)中部門個數(shù)大于2 的顯示?
select region_id,count(id)
? ? from? s_dept
? ? ? ? group by region_id? having count(id) > 2;?
? 7.5 按照部門編號分組? 統(tǒng)計每個部門的平均工資? 要求平均工資大于1400 的 顯示
? select? dept_id,avg(salary)? ? ?
? ? ? from? s_emp?
? ? ? ? ? group by? dept_id? having? avg(salary) > 1400;
? having 執(zhí)行早? 還是? select 執(zhí)行早?
