目錄
建立表空間建立使用者以及使用者授權
#%E5%88%9B%E5%BB%BA%E8%A1%A8%E7%A9%BA%E9%97%B4%E5%88%9B%E5%BB%BA%E7%94%A8%E6%88%B7%E4%BB%A5%E5%8F%8A%E7%94%A8%E6%88%B7%E6%8E%88%E6%9D%83
修改表結構
#%E4%BF%AE%E6%94%B9%E8%A1%A8%E7%BB%93%E6%9E%84
資料的增删改
#%E6%95%B0%E6%8D%AE%E7%9A%84%E5%A2%9E%E5%88%A0%E6%94%B9
序列的使用
#%E5%BA%8F%E5%88%97%E7%9A%84%E4%BD%BF%E7%94%A8
oracle的查詢
#oracle%E7%9A%84%E6%9F%A5%E8%AF%A2
單行函數
#%E5%8D%95%E8%A1%8C%E5%87%BD%E6%95%B0
字元函數
數值函數
日期函數
轉換函數
通用函數
條件表達式
多行函數
#%C2%A0%E5%A4%9A%E8%A1%8C%E5%87%BD%E6%95%B0
分組查詢
多表查詢中的一些概念
自連接配接概念和練習
子查詢
分頁查詢
oracle對象
視圖
索引
索引的概念
單列索引
複合索引
建立表空間建立使用者以及使用者授權
建立表空間
create tablespace xiaowang
datafile 'D:\xiaowang.dbf'
size 100m
autoexdent on
next 10m;
删除表空間
drop tablespace xiaowang;
建立使用者表
create user xiaowang
identified by xiaowang
default tablespace xiasowang;
給使用者授權
grant dba to xiaowang;
修改表結構
添加一列
alter table student sex number(1); --增加一列
alter table student (sex number(1),address varchar2(20));--增加多列
修改列類型
alter table student modify sex varchar(5);
修改列名稱
alter table student rename column sex to xingbie;
删除一列
alter table sutdent drop column xingbie;
資料的增删改
添加一條記錄
insert into student (pId ，pName) values （1，'曉航'）;
修改一條資料
update student set pname = '小航航' where pId = 1;
三個删除
delete from student -- 删除表中全部記錄
drop table student --删除表結構
在表資料量大的情況下 尤其是表中帶有索引的情況下 該操作效率高
索引可以提高查詢效率 但是會影響增删改 效率
truncate table student --先删除表 再次建立表 等同于删除表中全部記錄
序列的使用
- 預設從1開始 依次遞增 主要用來給主鍵指派使用
- 序列不真的屬于任何一張表，但是可以邏輯和表綁定
create sequence s_person;
select s_person.nextval from dual;
insert into student (pId ，pName) values （ s_person.nextval，'曉航'）;
oracle的查詢
scott使用者介紹
- 使用者名scott 密碼tiger
- 解鎖scott使用者
alter user scott account unlock;
- 解鎖使用者密碼 【詞句也可以重置密碼】
alter user scott identified by tiger;
- 切換到scott使用者下 Log off退出目前使用者 Log on 登陸使用者
單行函數
- Java開發 增删改查 一張表的增删改查 多張表的增删改查 一個項目的增删改查 分布式項目的增删改查
增删改都沒有問題 難就難在查詢 尤其是多張表的查詢
- 作用于一行 傳回一個值
字元函數
select upper(‘yes’ ) from dual; -- YES
select lower (‘YES’) from dual; --yes
首字元大寫函數
字元串連結函數
字元串截取函數
字元串替換函數
擷取字元串長度函數
數值函數
select round(26.18) from dual; --26 四舍五入 後面的參數表示保留的位數
select round(26.18,1) from dual; --26.2
select trunc(26.18，-1) from dual; --50 直接截取 不看後面位數的數字是否大于5
select mod(10，3) from dual; --求餘數
日期函數
查詢出emp表中所有員工入職距離現在幾天
select e.ename，sysdate-e.hiredate from emp e；
算出明天此刻
select SYSDATE+1 from dual
注意 navicat mysql server 和oracle函數文法不樣 此文都為oracle資料庫
查詢出emp表中所有的員工入職距離現在幾月
select e.ename，month_between(sysdate,e.hiredate) from emp e；
查詢出emp表中所有的員工入職距離現在幾年
select e.ename，(sysdate-e.hiredate)/365 from emp e；也可以
select e.ename，month_between(sysdate,e.hiredate) /12 from emp e；
查詢出emp表中所有的員工入職距離現在幾周
select e.ename，month_between(sysdate,e.hiredate)/4 from emp e；這樣算不夠精準
select e.ename，（sysdate-e.hiredate）/7 from emp e；
select e.ename，round(（sysdate-e.hiredate）/7) from emp e；
轉換函數
日期轉字元串 這個類型是字元串類型
select to_char(sysdate ,’fm yyyy-mm-dd hh24:mi:ss’) from dual;
字元串轉日期 這個類型是date類型
select to_date(‘2021-9-12 20:12:30’ , ‘fm yyyy-mm-dd hh24:mi:ss’ ) from dual;
通用函數
- 算出emp表中所有員工的年薪
- 獎金裡面有null 值 如果null值和任意數字做算術運算 結果都為null
- nva函數的意思就是如果括号内第一個參數為null 那就用第二個參數0 做運算
- 如果不為null 就用第一個參數内的值
select e.name ，e.sal*12+nvl（e.comm,0） from emp e;
條件表達式
給emp表中員工起中文名稱
select e.ename ,
CASE e.ename
WHEN 'SMITH' THEN'管理者'
WHEN 'ALLEN' THEN '銷售員'
WHEN 'WARD' THEN '銷售經理'
WHEN 'JONES' THEN '區域經理'
-- ELSE '員工' END
from emp e
不要else時候 表内資料顯示為null
- 判斷emp表中員工工資，如果高于3000顯示高收入 如果高于1500低于3000顯示中等收入
select e.sal
case
when e.sal > 30000 then '高收入'
when e.sal > 1500 then '中等收入'
else '低等收入'
from emp e;
等值判斷 case 後面跟字段 範圍判斷不需要跟字段
Oracle資料庫專用條件表達式語句
select e.ename
decode ( e.ename
'SMITH ','曹賊 '
'ALLEN ','大耳賊 '
'WARD ','諸葛小二 ',
'無名'
)中文名 --這裡的【中文名】可以不加引号 如果加引号的話直接加雙引号 因為它代表的是字段名稱是一個字元串
from emp e;
多行函數
- 作用于多行 傳回一個值【聚合函數】
select count(1) from emp e; --查詢總條數
select SUM(sal) from emp e;--和計算
select MAX(sal) from emp ;--最大值
select MIN(sal) from emp e;--最小值
select AVG(sal) from emp e;--平均值
分組查詢
- 查詢每個部門的平均工資
- 分組函數中，出現在group by後面的原始列，才能出現在select後面
- 沒有出現在group by後面的列 想在select後面 必須加上聚合函數 如sum max min avg count
- 聚合函數有一個特性 可以把多行記錄變成一個值
select e.deptno，avg（e.sal）
from emp e
group by e.deptno；
- 查詢出平均工資高于2000的部門資訊
- 所有條件都不能使用别名來判斷
select e.deptno ,avg(e.sal) asal
from emp e
group by e.deptno having avg(e.sal) > 2000;
- 查詢出每個部門工資高于800的員工的平均工資
- 然後在查詢出平均工資高于2000的部門
select e.deptno avg（e.sal ）
from emp e
where e.sal > 800
group by e.deptno
having avg(e.sal)> 2000
WHERE後面跟的是條件。HAVING是跟在GROUP BY 分組後面的
多表查詢中的一些概念
笛卡爾積： 一張表中所有的記錄 一一 和另外一整表的所有的記錄比對
等值連接配接
select *
from emp e，dept d
where e.deptno = d.deptno
内連接配接
select *
from emp e
inner join dept d
on e.deptno = d.deptno
外連接配接
select *
from emp e
left jion dept d
on e.deotno = d.deptno
Oracle專用外連接配接
- 一般使用通用的連接配接查詢sql 不使用專用的
select *
from emp e , dept d
where e.deptno(+) = d.deptno
自連接配接概念和練習
- 查詢出員工姓名 員工上司姓名
- 自連接配接 其實就是站在不同的角度把一張表看成多張表
select e1.ename,e2.ename
from emp e1 ,emp e2
where e1.MGR = e2.empno;
在此sql中e1是員工表 e2是上司表 為什麼呢
e2表中的員工字段都是e1表中的上司字段
- 查詢出員工姓名 員工部門名稱 員工上司姓名 員工上司部門名稱
select e1.ename,d1.dname,e2.ename,d2.dname
from emp e1 ,emp e2,dept d1,dept d2
where e1.MGR = e2.empno
and e1.deptno = d1.deptno
and d2.deptno = d2.deptno;
子查詢
- 子查詢傳回一個值
- 查詢出工資和SCOTT一樣的員工
select *
from emp e
where e.sal in(select sal from emp where ename = 'SCOTT')
sal 後面不能使用 = 因為括号内where 後面的字段不是主鍵 不能確定唯一非空
當ename出現另一SCOTT 時 查詢語句使用 = 連接配接就不對了 應該使用 in 連接配接
但是如果使用的是主鍵 那就可以使用 = 連接配接查詢語句
- 子查詢傳回一個集合
- 查詢出工資和10号部門任意員工一樣的員工資訊
select * from emp where sal in(
SELECT sal from emp where deptno = 10
);
- 子查詢傳回一張表
- 查詢出每個部門最低工資 和最低工資員工姓名 和該員工所在部門名稱
- 1.先查詢出每個部門的最低工資
select e.deptno,min(e.sal) msal
from emp e
group by e.deptno
- 2.三表聯查，得到最終結果
SELECT t.deptno,t.msal,e.ename,d.dname
from (
select e.deptno ,min(e.sal)
from emp e
group by e.deptno
) t,emp e,dept d
where t.deptno = e.deptno
and t.msal = e.sal
and e.deptno = d.deptno
這個查詢在工作中用的比較多 如果想掌握好這個sql語句 首先要把簡單的子連接配接查詢搞清楚 搞明白
分頁查詢
rownum 行号
rownum行号：當我們做select操作的時候 每查詢出一行記錄 就會在改行上加上一個行号
行号從1開始 依次遞增，不能跳着走 排序操作會影響rownum的順序
select *
from emp e
where rownum < 4
order by e.sal desc
注意 這個語句是錯誤的 能運作 但是資料不對
select rownum, e.*
from emp e
order by e.sal desc
這個sql語句可以清楚的表達rownum 和 order by排序沖突問題
如果涉及到排序 但是還要使用rownum的話 我們可以再次 嵌套查詢
select rownum, t.*
from (select *
from emp e
order by e.sal desc ) t;
- emp表工資倒叙排列後 每頁五條記錄 查詢第二頁
select rownum e.*
from (
select rownum rn, e.*
FROM (select *from emp order by SAL desc) e
where rownum < 11
) where rn > 5
oracle對象
視圖
- 視圖的概念：視圖就是提供一個查詢的視窗 所有的資料來源于原表
- 建立視圖必須有dba權限
- 查詢語句建立表
create table emp select * from scott.emp ;
select * from emp
建立視圖
create view v_emp as select ename, job from emp;
查詢視圖
select * from v_emp;
修改視圖【不推薦】
uodate v_emp set job = 'CLERK' where ename = 'ALLEN';
commit;
修改成功 那麼原表中的資料也随之改變 我們修改視圖 修改的是原表中的資料 因為視圖中本來就沒有資料
建立隻讀視圖
create view v_emp as select ename,job from emp with read only;
視圖有什麼用
1.視圖可以屏蔽掉表中的敏感字段
2.保證總部和分布資料及時統一
索引
索引的概念
索引就是在表的列上建構一個二叉樹 達到大幅度提高查詢效率的目的 但是索引會影響增删改的效率
單列索引
建立單例索引
create index idx_ename on emp(ename);
索引觸發規則 條件必須是索引列中的原始值
單行函數 列如單行函數分為五種類型：字元函數、lower upper數值函數0、round、trunc、mod日期函數、month_between轉換函數、to_char、to_date通用函數nvl
模糊查詢 都會影響索引的觸發
複合索引
建立複合索引
create index idx_enamejob on emp(ename,job);
複合索引中 第一列為優先檢索列
如果要觸發複合索引，必須包含有有限檢索列中的原始值
select * from emp where ename = ‘SCOTT’ and job = ‘xx’ --觸發索引列
select * from emp where ename = ‘SCOTT’ or job = ‘xx’ -- 不觸發索引列 or 表示一個觸發 或者 一個不觸發 那麼結果就是不觸發
select * from emp where ename = ‘SCOTT’ --觸發索引列