Oracle第一天
- Oracle的概念和安裝
- 基本查詢
- 條件查詢和排序
- 單行函數
- 多行函數
- 條件表達式
- 多表查詢
- 子查詢
- 集合運算
- 練習題
1. Oracle的概念和安裝
Oracle:客戶端/服務器 或 B/S體系結構數據庫
DOS命令
// 登錄
sqlplus scott/tiger@192.168.80.100:1521/orcl
// 查詢都有啥表
select * from tab;
Oracle 配置路徑：E:\app\CL\product\11.2.0\dbhome_1\NETWORK\ADMIN
-
PLSQL安裝
- 配置tools---preferences ---配置instantclient路徑
- E:\app\CL\product\11.2.0\dbhome_1\NETWORK\ADMIN 中的tnsname復制到本地
- 配置TNS_ADMIN環(huán)境變量
- 重啟
-
新建一個項目
- MySQL：創(chuàng)建一個數據庫丢习，創(chuàng)建相應的表
- Oracle：創(chuàng)建一個表空間，創(chuàng)建用戶仔蝌，用戶創(chuàng)建表
-
Oracle與MySQL的區(qū)別
Oracle是多用戶的泛领，MySQL是多數據庫的
Oracle安全級別高荒吏，MySQL開源免費
-
mysql: 開源免費 應用于中小型企業(yè) 敛惊，淘寶網（mysql集群）
oracle:收費 安全 應用于國企、金融绰更、銀行瞧挤、證券、保險 按CPU核數收費儡湾，一核-永久使用 18萬
2. 基本查詢
- SQL：結構化查詢語言 (Structured Query Language)
- SQL分類以及每類常見的操作符
- DDL：數據定義語言 修改表結構 create\alter\drop
- DML：數據操作語言 操作表中數據 insert\update\delete
- DCL：數據控制語言 安全級別 授權 qrant\revoke
- DQL：數據查詢語言 select * from 子句 where 子句
- 查詢語句的結構
select [列名] [*] from 表名 [where 條件] [group by 分組條件] [having 過濾] [order by 排序]
- select 1+1 在mysql中為2 oracle報錯（select 1+1 from dual）
- dual表：虛表特恬，主要用來補齊語法結構
- 直接寫一個常量比寫*更高效
select count(1) from emp
select count(*) from emp
- 別名查詢：as關鍵字 可以省略
- 別名中不能有特殊字符或者關鍵字 如果有就加“ ” 如空格、%徐钠、
- 去除重復數據：distinct
-- 單列去除重復
select distinct job from emp;
-- 多列去除重復 每一列都一樣才能算作重復
select distinct job,deptno from emp;
- 查詢中的四則運算
-- 查詢員工年薪
select sal*12 from emp
-- 查詢員工年薪+獎金 null值代表不確定的不可預知的內容癌刽，不可以四則運算
select sal*12+comm from emp
解決辦法：select sal*12 + nvl(comm,0) from emp -- nvl(comm,0) 如果comm為null就返回0
- 字符串拼接: ||
-- 查詢員工姓名：姓名：scott
select '姓名：'|| ename from emp
// 為什么使用單引號不用雙引號
在oracle中 雙引號在別名中使用，單引號引起來的是值是字符
-- 拼接函數 concat(str1,str2) oracle和Mysql通用
select concat('姓名：',ename) from emp
3. 條件查詢和排序
- 關系運算符：> >= < <= != <>
- 邏輯運算符：and or not
- 其他運算符
- like:模糊查詢
- in(set):在某個集合內
- between and :在某個區(qū)間內
- is null:判斷為空
- is not null:判斷不為空
-- 查詢每月得到獎金的員工信息
select * from emp where comm is not null
- 模糊查詢：like
- %:匹配多個字符
- _:匹配單個字符
-- 查詢員工姓名第三個字母是o
select * from emp where ename like '___o%'
-- 查詢員工姓名包含% escape '\' 告訴oracle'\'是反義字符
select * from where ename like '%\%%' escape '\'
- 排序：order by
- 升序：asc -- ascend
- 降序：desc -- descend
- 查詢員工信息按照獎金由高到低select * from emp order by comm desc nulls last-- nulls 默認是first 解決空值排序在前的問題
4. 單行函數
對某一行中的某一個值進行處理,函數必須要有返回值
-
數值函數
- ceil():向上取整 -- ceil(45.9) 46
- floor():向下取整 --floor(45.9) 45
- round(45.926,2):45.93 -- round(45.926,-1):50 --round(45.926,-2):0 --round(65.926,-2):100
- trunc(45.926,2):45.92 -- trunc(45.926,-1):40 --trunc(45.926,-2):0 --trunc(65.926,-2):0
- mod(9,3):0 --mod(9,4):1 求余
-
字符函數
-
substr(str1,起始索引尝丐，長度) ： 起始索引不管寫0還是1都是從第一個字符開始截取
substr('qwerty',0,3): qwe substr('qwerty',1,3): qwe
length():獲取字符串長度
-
trim():去除字符串左右兩邊的空格
trim('x' from 'xxaaxx') -- aa
ltrim('x' from 'xxaaxx') -- aaxx
rtrim('x' from 'xxaaxx') -- xxaa
replace('hello','l','a'):替換字符串
initcap（）:每個單詞的首字母變大寫
lower():字符串變小寫
upper():字符串變大寫
-
-
日期函數
- 查詢今天的日期:select sysdate from dual;
- 查詢三個月后今天的日期:select add_months(sysdate,3) from dual
- 查詢三天后的日期：select sysdate+3 from dual
- 查詢員工入職的天數：select ceil(sysdate - hiredate) from emp
- 查詢員工入職的周數：select ceil(sysdate - hiredate)/7 from emp
- 查詢員工入職的月數：select months_between(sysdate,hiredate) from emp
- 查詢員工入職的年數：select months_between(sysdate,hiredate)/12 from emp
-
轉換函數
數值轉字符显拜、字符轉數值、日期字符轉數值:to_number(str) -- select 100+to_number('10' from dual)
數值轉字符：to_char(sal,'$9,999.99') --- 'B'空格符
-
日期轉字符：to_char(sysdate,'yyyy-mm-dd hh:mi:ss') / to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')
select to_char(sysdate,'d' ) from dual; --代表一個星期中第幾天
select to_char(sysdate,'dd' ) from dual;--代表一個月中第幾天
select to_char(sysdate,'ddd' ) from dual;--代表一年中第幾天
select to_char(sysdate,'day' ) from dual;--代表星期幾 monday
select to_char(sysdate,'dy' ) from dual;--星期的縮寫 mon
-
字符轉日期：to_date('2017-04-10','yyyy-mm-dd')
查詢1981--1985年入職的員工信息:select * from emp where hiredate between to_date('1981','yyyy') and to_date('1985','yyyy')
-
通用函數
- nvl(參數1爹袁，參數2):如果參數1為null ,就返回參數2
- nvl2(參數1远荠，參數2,參數3)：如果參數1為null ,就返回參數3 否則返回參數2
- nullif(參數1，參數2) :如果參數1等于參數2失息，返回null,否則返回參數1
- coalesce(null,null,2,4) :返回第一個不為null的值
5. 多行函數
對某一列所有的行進行處理,直接忽略空值
- max()
- min()
- count()
- sum()
- avg()
-- 查詢平均獎金 avg null有影響select ceil(sum(comm) / count(1)) from emp -- ceil()取整
6. 條件表達式
- 語法
case 字段 ： when 值1 then 值 when 值2 then 值 else 默認值 end
- 案例：給表中姓名取一個中文名
select case ename when 'SMITH' then '張三' when 'ALLEN' then '李四' else '王五' endfrom emp
- oracle特有的寫法
decode(字段,if1,then1,if2,then2,else1)select decode(ename,'SMITH','張三'譬淳，'ALLEN','李四','王五') from emp
- where和having區(qū)別
- where后面不能接聚合函數 可以接單行函數
- having 是在group by 之后執(zhí)行,可以接聚合函數
- 分組表達式：group by
select 分組的條件档址，分組之后的操作 from 表名 group by 分組的條件 having 條件過濾
- 案例;分組統計所有部門的平均工資，找出平均工資大于2000的部門
-- 編寫順序：select ... from ... where ... group by ... having ... order by-- 執(zhí)行順序：from....where...group by ...having ... select ... order by select deptno,avg(sal) from emp group by deptno having avg(sal) > 2000
7. 多表查詢
- 笛卡爾積：兩張表的乘積
select * from emp; 14select * from dept; 4select * from emp,dept; 4*14
-
內連接
-
隱式內連接
- 等值內連接: select * from emp e,dept d where e.deptno = d.deptno;
- 不等值內連接: select * from emp e,dept d where e.deptno <> d.deptno;
- 自連接：查詢員工編號邻梆，員工姓名守伸，員工工資等級，部門編號浦妄，部門名稱含友，經理的姓名，經理部門名稱 -- 將工資等級顯示成中文的一校辩、二窘问、三、四
select e.empno, e.ename, e.deptno, d.dname, case s.grade, when 1 then '一級', when 2 then '二級', when 3 then '三級', when 4 then '四級' else '五級' end "員工工資等級", m.ename, d2.dname, decode( s2.grade,1,'一級',2,'二級',3,'三級',4,'四級','五級') from emp e, emp m, dept d, dept d2, salgrand s, salgrand s2 where e.mgr = m.empno and d.deptno = e, deptno and m.deptno = d2.deptno and e.sal between s.losal and s.hisal and m.sal between s.losal and s.hisal 顯示內連接
select * from emp e inner join dept d on e.deptno = d.deptno- oracle中的內連接（+）: 如果沒有對應的記錄就加上空值
select * from emp e , dept d where e.deptno = d.deptno(+) -
-
外連接
- 左外連接：left outer join 左表中所有的記錄宜咒，如果右表沒有對應記錄就為空
select * from emp e left join dept d on e.deptno = d.deptno- 右外連接：right outer join 右表中所有的記錄惠赫，如果左表沒有對應記錄就為空
select * from emp e right join dept d on e.deptno = d.deptno
8. 子查詢
- 單行子查詢： > 、>= 故黑、< 儿咱、<=、 <>场晶、 !=
-- 查詢最高工資員工的信息select * from emp where sal = (select max(sal) from emp)-- 查詢每個部門最低工資的員工信息和部門信息select e.* , d.* from emp e ,dept d where (e.sal,e.deptno) in (select deptno, min(sal) from emp group by deptno) and e.deptno = d.deptnoselect e.*, d.* from emp e, dept d, (select deptno, min(sal) minsal from emp group by deptno) t where e.deptno = t.deptno and e.sal = t.minsal and d.deptno = d.deptno
- 多行子查詢
-- 查詢是領導的信息select * from emp where empno in (select mgr from emp)-- 查詢不是領導的信息select * from emp where empno not in (select mgr from emp) -- 因為有空值 所以查不出select * from emp where empno not in (select mgr from emp where mgr is not null)
-
exists:數據量大的時候非常高效
- 可以當作布爾值來處理混埠，當查詢語句有結果的時候返回true,否則返回false
select * from emp where exists(select * from emp where deptno = 1234); false
-- 查詢有員工的部門信息select * from dept d where exists(select * from emp e where e.deptno = d.deptno)
9. 集合運算
-
并集運算：將兩個查詢結果進行合并
-- 查詢工資大于1500 或者20號部門下的員工select * from emp where sal > 1500 or deptno = 20- union:去除重復的 根據第一列進行排序
select * from emp where sal > 1500unionselect * from emp where deptno = 20- union all
select * from emp where sal > 1500union allselect * from emp where deptno = 20 差集運算：兩個結果相減
-- 查詢1981年入職的員工 不包括總裁和經理-- 查詢1981年入職的員工select * from emp where to_char(hiredate,'yyyy') = '1981'-- 不包括總裁和經理select * from emp where job = 'president' or job = 'manager'select * from emp where to_char(hiredate,'yyyy') = '1981'minusselect * from emp where job = 'president' or job = 'manager'
- 集合運算中的注意事項
- 列的類型要一致
- 按照順序編寫
- 列的數量要一致，如果不足用null補齊
10. 練習題
- 找到員工表中工資最高的前三名
/* rownum:偽列诗轻，系統自動生成的钳宪，用來表示行號 rownum是Oracle中特有的用來表示行號的，默認值起始值是1扳炬，在查詢出結果之后再添加1 rownum 不能做大于號判斷 可以做小于號判斷 查詢rownum>2不會有記錄*/select rownum,t1.* from (select rownum,* from emp order by sal desc) t1 where rownum <= 3
- 找到員工表中薪水大于本部門平均薪水的員工
select e.* from emp e, (select deptno, avg(sal) avgsal from emp group by deptno) s where sal > s.avgsal and e.deptno = s.deptno
- 統計每年入職的員工個數
select count(1) num,to_char(hiredate,'yyyy') year from emp group by to_char(hiredate,'yyyy')select max(case year when '1980' then num end) "1980", max(case year when '1981' then num end) "1981", max(case year when '1982' then num end) "1982", max(case year when '1987' then num end) "1987", sum(num) "total"from(select count(1) num , to_char(hiredate,'yyyy') year from emp group by to_char(hiredate,'yyyy'))
- rowid:偽劣吏颖，每行記錄所存放的真實物理地址
select rowid,e.* from emp e;-- 去除表中重復記錄保留物理地址最小的delete from p p1 where p1.rowid > (select min(rowid) from p p2 where p1.name = p2.name)
- rownum 分頁查詢:在oracle中只能用子查詢做分頁查詢
-- 查詢6-10條數據select * from (select rownum,e.* from emp e ) tt where tt.rownum between 6 and 10
