Oracle 基礎(chǔ)
一断楷、概述
Oracle的安裝
下載地址：oracle官網(wǎng)
卸載： 安裝目錄/dbhome_1/deinstall/deinstall.bat(點(diǎn)擊運(yùn)行)
二萝喘、用戶與表空間
1. Sql Plus 登錄
系統(tǒng)用戶
- sys,system
- sysman
- scott (默認(rèn)密碼是tiger)
使用系統(tǒng)用戶登錄
[username/password][@server][as sysdba|sysoper]
查看用戶登錄
- show user 命令
- desc dba_users 數(shù)據(jù)字典
啟用scott用戶
- alter user username account unlock; -- (lock 是啟用)
- connect scott/tiger; --登錄
2. 表空間
表空間的概述
- 永久表空間 （表、視圖、存儲(chǔ)過程）
- 臨時(shí)表空間 （執(zhí)行結(jié)束尽超，存放被釋放）
- UNDO表空間 （事物所修改的舊址地啰，用于事物的回滾）
查看用戶的表空間
- dba_tablespaces (系統(tǒng)管理員級(jí)的用戶)
- user_tablespaces （普通用戶登陸查看）
- dba_users （系統(tǒng)管理員級(jí)查看）
- user_users （普通登錄用戶查看）
** 設(shè)置用戶的默認(rèn)和臨時(shí)表空間**
alter user user_name default|temporary tablespace tablespace_name
創(chuàng)建修改刪除表空間
- 創(chuàng)建表空間
create tablespace tablespace_name datafile 'filename.dbf' size 10m; --永久表空間
create temporary tablespace tablespace_name tempfile 'filename.dbf' size 10m; --臨時(shí)表空間
- 差看創(chuàng)建的表空間路徑
desc dba_data_files;
select file_name from dba_data_files where tablespace_name = 'tablespace_nameXXX' --注意tablespace_name大寫
- 修改表空間
修改表空間的狀態(tài)
- 設(shè)置聯(lián)機(jī)或脫機(jī)狀態(tài)
alter tablespace tablespace_name online|offline;
select status from dba_tablespaces where tablespace_name='NAME_TABLESPACE'; --查看狀態(tài)
- 設(shè)置只讀或可讀寫狀態(tài)
alter tablespace tablespace_name read only|read write;
修改表空間的數(shù)據(jù)文件
- 增加數(shù)據(jù)文件
alter tablespace tablespace_name add datafile 'xx.dbf' size xx;
- 刪除數(shù)據(jù)文件
alter tablespace tablespace_name drop datafile 'filename.dbf'; --注意 不能刪除創(chuàng)建表空間時(shí)的第一個(gè)數(shù)據(jù)文件
- 刪除表空間
drop tablespace tablespace_name; --只刪除表空間
drop tablespace tablespace_name including contents; --刪除表空間和數(shù)據(jù)文件
三送粱、表與約束
管理表
1. 數(shù)據(jù)類型
- 字符型
- [] char(n) | nchar(n) //長度不可變
- [ ] varchar2(n) | nvarchar(n) //長度可變，節(jié)省空間
- 數(shù)值型
- [ ] number(5,2) //有效數(shù)字5位时捌，保留兩位有效小數(shù)怒医，eg：123.45
- 日期型
- [ ] date
- [ ] timestamp //時(shí)間戳，精確度高
- 其他
- [ ] blob //4GB二進(jìn)制數(shù)據(jù)
- [ ] clob //4GB字符串?dāng)?shù)據(jù)
2. 基本語法
- 創(chuàng)建表
create table table_name(
column_name datatype,...
)
- 修改表
添加字段
alter table table_name add column_name datatype;
更改字段數(shù)據(jù)類型
alter table table_name modify column_name datatype;
刪除字段
alter table table_name drop column column_name;
修改字段名字
alter table table_name rename column column_name to new_column_name;
修改表名
alter table_name to new_table_name;
-刪除表
delete語句
delete from table_name;
truncate table_name;
truncate和delete的區(qū)別奢讨？
（1）都是清空表中的數(shù)據(jù)稚叹，即刪除表中的記錄。
（2）truncate 的速度要比delete快拿诸。
（3）delete可以指定刪除符合條件的記錄
delete from test where name='35';
操作表中的數(shù)據(jù)
添加數(shù)據(jù)
insert into table_name(column1,comlumn2,...)values(value1,value2,...)
復(fù)制表數(shù)據(jù)
create table table_new as select * from table_old;
修改數(shù)據(jù)
update table_name set column1=value1,...[where conditions];
約束
非空約束
not null
在修改表時(shí)添加非空約束
alter table table_name modity column_name datatype not null;
主鍵約束(也是非空約束)
primary keyconstraint constraint_name primary key(column_name1,...)
查找約束
select constraint_names from user_constraints where table_name=' ';
在修改表時(shí)添加主鍵約束
add constraint constraint_names primary key (column_name1,...);
更改約束的名字
rename constraint old_name to new_name;
刪除主鍵約束
disable | enable constraint constraint_names; --禁用約束
drop constraint constraint_names; --刪除約束
drop primary key[cascade]; --cascade用于級(jí)聯(lián)刪除約束
外鍵約束
在創(chuàng)建表時(shí)設(shè)置外鍵約束(兩種方式)
create table typeinfo (typeid varchar2(10) primary key , typename varchar2(30)); --主表(類型信息表)
create table userinfo (
userid varchar2(10) primary key ,
username varchar2(20),
typeid_new varchar2(10) references typeinfo(typeid);
); --從表
-------
create table userinfo_f1 (
id varchar2(10) primary key,
name varchar2(30),
typeid_new varchar2(10),
constraint fk_typeid_new foreign key(typeid_new) references typeinfo(typeid)
on delete cascade;
)
在修改表時(shí)添加約束
create table userinfo(
id varchar2(10) primary key,
name varchar2(30),
typeid_new varchar2(10);
)
alter table userinfo add constraint fk_typeid foreign key (typeid_new)
references typeinfo(typeid);
注:
- 主從表中響應(yīng)字段必須是同一個(gè)數(shù)據(jù)類型扒袖。
- 從表中外鍵字段的值必須來自于主表中相應(yīng)字段的值，或?yàn)閚ull值亩码。
-
on delete caseade是級(jí)聯(lián)刪除季率。
刪除約束
alter table table_names disable | enable constraint constraint_names;
alter table table_names drop constraint constraint_names;
select constraint_name,constraint_type,status from user_constraints where table name = "";
-- 查詢約束
唯一約束
唯一約束和主鍵約束的不同
- 主鍵字段值必須是非空的；唯一約束允許有一個(gè)空值蟀伸。
- 主鍵在一張表中只能有一個(gè)蚀同，但是唯一約束可以有多個(gè)。
在創(chuàng)建表時(shí)設(shè)置唯一約束
create table table_names (column_name datatype unique,...); --列級(jí)
create table table_names (
column_name datatype,...
constraint constraint_name unique(column_name); --表級(jí)
)
在修改表時(shí)添加唯一約束
alter table table_name add constraint constraint_names unique(column_name);
刪除唯一約束
alter table table_names disable | enable constraint constraint_names;
alter table table_names drop constraint constraint_names;
檢查約束
作用:表中的值更具有實(shí)際意義啊掏。
在創(chuàng)建時(shí)設(shè)置檢查約束
create table table_names (column_name datetype check(expressions),...) --列級(jí)
eg:
create table userinfo_c(
id varchar2(10) primary key,
username varchar2(20),
salary number(5,0) check(salary>0)
)
constraint constraint_names check(expressions) --表級(jí)
在修改表時(shí)添加檢查約束
alter table table_names add constraint constraint_name check(expressions);
四蠢络、查詢語句
基本查詢語句
select [distinct] column_names1,... |* from table_name [where conditions];
在SQL*Plus中的設(shè)置格式
column column_names HEADING new_name; --修改字段名稱(column可以簡寫成COL)
column column_names format dataformat; --設(shè)置結(jié)果顯示格式
column column_names clear; --清除之前設(shè)置的格式
給字段設(shè)置別名
select column_names AS new_name from table_names; --AS可以省略
運(yùn)算符和表達(dá)式
- 算術(shù)運(yùn)算符(+,-,*,/)
- 比較運(yùn)算符(>,>=,<,<=,=,<>[不等于])
- 邏輯運(yùn)算符(and,or,not)
- 優(yōu)先級(jí):按照 not、and迟蜜、or 遞減
eg:
- 優(yōu)先級(jí):按照 not、and迟蜜、or 遞減
select id,username,salary+200 from users; --只是查詢的結(jié)果視圖+200
select username from users where salary > 800 and salary <> 1200;
select * from users where not(user_name = 'aaa');
模糊查詢
- 通配符(_,%)
- 一個(gè)_只能代表一個(gè)字符
- %可以代表0到多個(gè)任意字符
- LIKE
select * from users where user_name LIKE '_張%'; --查詢第2個(gè)字是張的人的信息
select * from users where user_name LIKE '%張%'; --查詢名字中含有張的人的信息
范圍查詢
- BETWEEN...AND
- IN / NOT IN
select * from users where salary not between 800 and 1000;
select * from users where user_name not in ('a','b'); --用戶名不等于a且不等于b
排序
- select...from ...[where...] ORDER BY...DESC/ASC;
select * from users order by id desc , dalary asc; --在id相同的情況下salary才按升序排列
case...when
select username ,
case username when 'a' then '語文' when 'b' then '數(shù)學(xué)' else '其他' end as 科目
from users;
select username,case when username='aaa' then '計(jì)算機(jī)部門'
when username='bbb' then '市場(chǎng)部門' else '其他部門' end as 部門
from users;
decode
- decode (column_name,value1,result1,...,defaultValue)
select username , decode(username,'aaa','計(jì)算機(jī)部門','bbb','市場(chǎng)部門','其他') as 部門
from users;
Oracle 函數(shù)
函數(shù)的作用
- 方便數(shù)據(jù)的統(tǒng)計(jì)
- 處理查詢結(jié)果
函數(shù)的分類
數(shù)值函數(shù)
1. 四舍五入
ROUND(n[,m])
- 省略m:0
- m>0:小數(shù)點(diǎn)后m位
- m<0:小數(shù)點(diǎn)前m位
select round(23.4),round(23.42,1),round(23.42,-1) from dual;
-- 結(jié)果分別為23/23.4/20
2. 取整函數(shù)
CEIL(n):取整時(shí)取最大值
FLOOR(n):取整時(shí)取最小值
3. 其他函數(shù)
- ABS(n):取絕對(duì)值
- MOD(m,n):取余函數(shù)
- POWER(m,n):m的n次冪
- SQRT(n):平方根
- sin(n)刹孔、asin(n)、cos(n)、acos(n)髓霞、tan(n)卦睹、atan(n):三角函數(shù)
字符函數(shù)
大小寫轉(zhuǎn)換函數(shù)
- upper(char):小寫變成大寫
- lower(char):大寫變成小寫
- initcap:把首字母變成大寫
獲取子字符串函數(shù)
-
substr(char,[m[n])
- char:字符串源
- m:取字串的開始位置
- n:截取字串的位數(shù)(可省略，截取為m到結(jié)尾的字符串)
獲取子字符串長度函數(shù)
- length(char)
字符串連接函數(shù)
- concat(char1,char2)
- 與||操作符的作用一樣
select concat('a','bc') from dual;
select 'a' || 'bc' from dual;
去除子串函數(shù)
- trim(c2 from char1):從字符串c1中去除一個(gè)字符c2
- ltrip(c1,c2):從c1中去除c2方库，只去除一個(gè)结序，從頭部開始
- rtrip(c1,c2):從c1中去除c2，只去除一個(gè)纵潦，從尾部開始
- trip(char):去除空格
替換函數(shù)
-
replace(char,s_string,r_string)
- 省略r_string用空格替換(即把s_string去除)
日期函數(shù)
-
sysdate
- 默認(rèn)格式:D-MON--Y
-
add_months(date,i)
- i為月份徐鹤，當(dāng)i<0時(shí)，為減去月份
-
next_day(date,char)
- 返回date這個(gè)指定日期的下周幾
select next_day(sysdate,'星期一') from dual;
-
last_day(date)
- 返回日期所在月的最后一天
-
months_between(date1,date2)
- 計(jì)算兩個(gè)日期的間隔時(shí)間
-
extract(date from datetime)
- 返回datetime的日期部分(例如年月日時(shí)分秒)
select extract(hour from timestamp '2016=9-1 14:34:22') from dual;
轉(zhuǎn)換函數(shù)
日期轉(zhuǎn)換成字符的函數(shù)
-
tochar(date,fmt,params)
- date:將要轉(zhuǎn)化的日期
- fmt:轉(zhuǎn)換的格式
- (年:YY|YYYY|YEAR 月:MM|MONTH 日:DD|DAY 時(shí):HH24|HH12 分:MI 秒:SS)
- params:日期的語言
select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') from dual;
字符轉(zhuǎn)換成日期的函數(shù)
-
to_date(char,fmt,params)
- char:將要轉(zhuǎn)化的字符
- fmt:轉(zhuǎn)化的格式
- params:日期的語言
數(shù)字轉(zhuǎn)換成字符的函數(shù)
-
to_char(number,fmt)
- 9:顯示數(shù)字并忽略前面的0
- 0:顯示數(shù)字邀层，位數(shù)不足返敬，用0補(bǔ)齊
- .或D:顯示小數(shù)點(diǎn)
- ,或G:顯示千位符
- $:美元符號(hào)
- S:加正負(fù)號(hào)(前后都可以)
select to_char(12345.678,'$99,999.99') from dual; --結(jié)果為:$12,345.678
字符轉(zhuǎn)換成數(shù)字的函數(shù)
- to_number(char,fmt)
select to_number('$1,000','$9999') from dual;
Oracle 查詢
分組查詢
概念
分組函數(shù)作用于一組數(shù)據(jù)，并對(duì)一組函數(shù)返回一個(gè)值寥院。
常用的分組函數(shù)
- avg
- sum
- min
- max
- count
- wm_concat(行轉(zhuǎn)列)
select avg(sal), sum(sal) , max(sal) ,min(sal) from emp;
select count(*) from emp;
select count(distinct empno) from emp; --去重部門數(shù)統(tǒng)計(jì)
select depno 部門號(hào), wm_concat(ename) 部門中員工的姓名 from emp group by deptno;
部門號(hào) | 部門中員工的姓名
10 | 張三劲赠，李四
20 | 王五，趙六
注意:NVL函數(shù) 使分組函數(shù)無法忽略空值秸谢。
select count(nvl(comm,0)) from emp; --comm若為空凛澎，置為0
分組數(shù)據(jù)
- group by
select deptno, job, sum(sal) from emp group by deptno, job order by deptno;
當(dāng)多個(gè)列分組時(shí)，用逗號(hào)分開钮追。
select 列表中所有未包含在組函數(shù)中的列都應(yīng)包含在group by 子句中预厌。
**eg:select depno count(ename) from emp就會(huì)執(zhí)行錯(cuò)誤 **
過濾分組
select depno, avg(sal) group by deptno having avg(sal) > 2000; --平均工資大于兩千的部門
where和having的區(qū)別
不能在where語句中使用組函數(shù)；
where不能放在group by 之后元媚；
可以在having語句中使用組函數(shù)轧叽；
從SQL優(yōu)化的角度，盡量使用where(where先過濾刊棕，后分組炭晒；having先分組后過濾)
分組查詢中排序
可以按照:列、別名甥角、表達(dá)式网严、序號(hào) 進(jìn)行排序
select depno avg(sal) from emp group by deptno order by avg(sql);
select depno avg(sal) 平均工資 from emp group by deptno order by 平均工資;
select depno avg(sal) from emp group by deptno order by 2 desc;
分組函數(shù)嵌套
select max(avg(sal)) from emp group by deptno; --求平均工資的最大值
GROUP BY 語句增強(qiáng)
- 語法
group by rollup(a,b)
- 等價(jià)于以下三句相加:
- group by a,b （按照a和b分組）
- group by a （按照a分組）
- group by null （不按照任何條件直接分組）
select deptno, job, sum(sal) from emp group by rollup(deptno,job);
用于報(bào)表
多表連接查詢
笛卡兒積
- 為了避免笛卡兒集，可以在where中加入有效的連接條件
- 在實(shí)際運(yùn)行環(huán)境下嗤无，應(yīng)避免使用笛卡爾全集
- 連接條件至少有n(表的個(gè)數(shù))-1個(gè)
等值連接
select e.empno, e.empname, d.dname
from emp e, dept d
where e.deptno = d.deptno;
不等值連接
select e.empno, e.ename, e.sal, s.grade
from emp e, salgrade s
where e.sal between s.losal and s.hisal;
-- 顯示薪水級(jí)別
-- between and 中小值在前
外連接
核心
- 通過外連接震束，把對(duì)于連接不成立的記錄，仍然包含在最后的結(jié)果中当犯。
- 左外連接:當(dāng)連接條件不成立的時(shí)候垢村，等號(hào)左邊的表仍然被包含
- 右外連接:當(dāng)連接條件不成立的時(shí)候，等號(hào)右邊的表仍然被包含
select d.deptno 部門號(hào), d.dname 部門名稱嚎卫， count(e.empno) 人數(shù)
from emp e, dept t
where e.deptno(+)=d.deptno
group by d.deptno, d.dname;
-- 按照部門統(tǒng)計(jì)員工人數(shù)嘉栓，要求顯示:部門號(hào)，部門名稱，人數(shù)
-- 右外連接侵佃，防止部門人數(shù)為0時(shí)不顯示
部門號(hào)|部門名稱|人數(shù)
10|accounting|3
40|operations|0
20|research|5
自連接
核心:通過別名麻昼，將同一張表視為多張表
select e.ename 員工姓名, b,ename 老板姓名
from emp e, emp b
where e.mgr = b.empno
-- 查詢員工的姓名和員工老板的姓名
-- mgr:老板號(hào)(老板也在員工中)
自連接存在的問題
- 不適合操作大表(笛卡爾集)
- 解決辦法:層次查詢
層次查詢
- 某些情況下，可以代替自連接
- 本質(zhì)上馋辈，是一個(gè)單表查詢
select level, empno, ename, sal mgr
from emp
connect by prior empno = mgr
start with mgr is null
order by 1;
子查詢
eg:誰的工資比SCOTT高?
select *
from emp
where sal >(select sal
from emp
where ename='SCOTT');
子查詢中的十個(gè)問題
- 子查詢語法中的小括號(hào)必須有
- 子查詢的書寫風(fēng)格
- 可以使用子查詢的位置:where,select,having,from
select empno,ename, (select job from emp where empno = 7839) 第四列 from emp;
-- select中使用子查詢(必須為單行子查詢)
select deptno,avg(sql) frpm emp
group by deptno
having avg(sal) > (select max(sal)
from emp
where deptno=30);
-- having中使用子查詢抚芦。不能用where代替，因?yàn)閣here中不能使用分組函數(shù)
select * from(select empno, ename, sal from emp);
-- 在from語句中使用子查詢
- 不可以使用子查詢的位置:group by
- from后面的子查詢
- 主查詢和子查詢可以不是同一張表
-- 查詢部門名稱是sales的員工信息
select * from emp
where deptno=(select deptno from dept
where dname='sales')
-- 用表連接的方法
select * from emp e,deptno d
where e.deptno=d.deptno and d.dname='sales'
注意:原則上使用表連接的方法性能高迈螟。因?yàn)橹挥靡粋€(gè)
from燕垃，只對(duì)數(shù)據(jù)庫訪問一次。
但是多表查詢會(huì)產(chǎn)生多卡爾集井联，從而影響性能。
- 一般不在子查詢中您旁，使用排序烙常；但在Top-N分析問題中，必須對(duì)子查詢排序
rownum：行號(hào)鹤盒，偽劣蚕脏。
- 行號(hào)永遠(yuǎn)按照默認(rèn)的順序生成(不隨位置變化而變化)
- 行號(hào)只能用
<,<=，不能用>,>=
因?yàn)樾刑?hào)在Oracle數(shù)據(jù)庫中永遠(yuǎn)從1開始侦锯，所以不能用
>,>=
-- 找出員工表種工資最高的前三名
-- 此方法錯(cuò)誤驼鞭，因?yàn)樾刑?hào)不隨位置變化而變化
select rownum,eno,ename,sal
from emp where rownum>3
order by sal desc;
--以下方法為正確
select rownum,eno,ename,sal
from (select * from emp order by sal desc where rownum>3)
- 一般先執(zhí)行子查詢，再執(zhí)行主查詢尺碰；但相關(guān)子查詢例外
-- 找到員工表種薪水大于本部門平均薪水的員工
select empno,ename,sal,(select avg(sal) from emp where deptno = e.deptno)
from emp e
where sal>(select avg(sal) from emp where deptno = e.deptno);
- 單行子查詢只能使用單行操作符挣棕；多行子查詢只能使用多行操作符
- 單行子查詢：只返回一條記錄
- 單行操作符：
=,>,>=,<,<=,<>
- 單行操作符：
- 多行子查詢：返回多行記錄
- 多行操作符：
IN,ANY,ALL
單行子查詢的相關(guān)例子
- 多行操作符：
- 單行子查詢：只返回一條記錄
-- 查詢員工信息，要求：職位與7566一樣亲桥；薪水大于7782員工薪水
select * from emp where job=(select job from emp where empno='7566')
and sal > (select sal from emp where empno='7782')
-- 查詢最低工資大于20號(hào)部門最低工資的部門號(hào)和部門最低工資
select deptno,min(sal) from emp group by deptno
having min(sal)>(select min(sal) from emp where deptno='20'
單行子查詢之能用單行操作符
在一個(gè)主查詢種可以有多個(gè)子查詢
多行子查詢的相關(guān)例子
--查詢部門名稱是sales和accounting的員工信息
select * from emp
where deptno in (select deptno from dept where dname='sales' or dname='accounting')
--等價(jià)于
select e.* from emp e,dept d
where e.deptno = d.deptno and (d.dname='sales' or d.dname='accounting');
查詢工資比30號(hào)部門任意一個(gè)員工高的員工信息
select * from emp where sal > any (select sal from emp where deptno='30')
查詢工資比30號(hào)部門所有員工高的員工信息
select * from emp where sal > all (select sal from emp where deptno='30')
--等價(jià)于
select * from emp where sal > (select max(mal) from emp where deptno='30')
- 注意:子查詢中的null值問題
只要子查詢的結(jié)果集種包含控制洛心，不要使用NOT IN操作符。
--查詢不是老板的員工
slect * from emp where empno not in (select mgr from emp where mgr is not null);
-- not in 后面的集合不能有空值题篷，否則查詢不出結(jié)果
案例
分頁顯示員工信息：顯示員工號(hào)词身，姓名，月薪
每頁顯示四條記錄
顯示第二頁的員工
按照月薪降序排列
-- Oracle 通過子查詢的方式實(shí)現(xiàn)分頁
select r,empno,ename,sql
from (select rownum r,empno,ename,sal
from ( select rownum ,empno,ename,sal from emp order by sal desc ) e1
where r < 8) e2
where r >= 5
找到員工表中薪水大于本部門平均薪水的員工
-- 使用相關(guān)子查詢方法
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)
-- 使用嵌套子查詢方法
select e.empno,e.ename,e.sal,d.avgsal
from emp e , ( select deptno, avg(sal) avgsal from emp group by deptno ) d
where e.deptno = d.deptno and e.sal>d.avgsal
-- 第一種（相關(guān)子查詢）的性能好番枚，執(zhí)行塊
按照部門統(tǒng)計(jì)員工人數(shù)法严，按照如下格式輸出
select count(*) Total,
sum(decode(to_char(hiredate,'YYYY'),'1980',1,0)) "1980",
sum(decode(to_char(hiredate,'YYYY'),'1981',1,0)) "1981",
sum(decode(to_char(hiredate,'YYYY'),'1982',1,0)) "1982",
sum(decode(to_char(hiredate,'YYYY'),'1987',1,0)) "1987"
from emp;
-- or
select
(select count(*) from emp) Total,
(select count(*) from emp where to_char(hiredate,'YYYY')='1980') "1980",
(select count(*) from emp where to_char(hiredate,'YYYY')='1981') "1981",
(select count(*) from emp where to_char(hiredate,'YYYY')='1982') "1982",
(select count(*) from emp where to_char(hiredate,'YYYY')='1987') "1987",
from dual;
