Oracle學習筆記-有解無憂

Oracle學習筆記

引言

創建檔案：可以使資料持久化，但是不區分資料型別，運行效率低
java：有資料型別，在記憶體中保存資料，但是資料不能持久化
資料庫：可以持久化保存資料，具備資料型別，運行效率高

Sql

結構化查詢陳述句

1.簡單查詢

sql的基本結構

select 想查詢的欄位名 from 表

查詢全部列

需求：查詢employees表中的所有內容

select * from employees

給查詢結果起別名

select 欄位名 (as) 別名，欄位名 別名......

--需求：查詢員工表中所有員工的工號、名字、工資，展示效果為工號、名字、工資
select employee_id as 工號,first_name 姓名,salary 工資
from employees

字串拼接

--需求：查詢員工表，工號，姓名，工資
select first_name||'.'||last_name 姓名,first_name,last_name
from employees

給查詢結果做算術運算

--需求：查詢員工的工號、名字、年薪
select employee_id,first_name,salary*13
from employees

2.去重

關鍵字：distinct
語法結構：select distinct 欄位名，欄位名...from 表
作用：將查詢結果中一行完全相同的記錄剔除

--需求:查詢所有是領導的作業人員的id
select distinct manager_id from employees

3.排序

關鍵字：order by 欄位名 asc(升序)|desc(降序)
語法結構：select 欄位1，欄位2,...from 表 order by 欄位 asc,欄位2 desc

--需求:查詢員工ID,first_name,salary,根據salary降序排序
select employee_id,first_name,salary
from employees
order by salary desc

--需求:查詢員工ID,first_name,salary,根據salary降序排序,當工資相同時要按照員工id降序排序
select employee_id,first_name,salary
from employees
order by salary desc,employee_id desc

注意：order by 之后可以寫多個排序規則，但是只有當第一個排序規則失效時，第二排序規則才有作用，只有當第一個和第二個排序規則都失效時，第三個才有用

4.條件查詢

關鍵字：where 查詢條件
語法結構：select...from...where 查詢條件

等值查詢

-- 需求:查詢工資等于17000的員工的id,名字,工資
select employee_id,first_name,salary
from employees
where salary = 17000

不等值查詢

-- 需求:查詢工資大于17000的員工的id,名字工資
select employee_id,first_name,salary
from employees
where salary > 17000

注意：在where后可以跟任何查詢條件，并不是非要使用欄位

select employee_id,first_name,salary
from employees
where 1=1  ----  結果為所有資料全部展示

多條件查詢
關鍵字：and（兩者都要成立）| or（兩者成立其一即可）

-- 需求:查詢工資大于10000并且小于24000的員工資訊
select employee_id,first_name,salary
from employees
where salary>10000  and salary<24000

-- 需求:查詢在90號部門或者在100部門的員工資訊
select employee_id,first_name,salary
from employees
where department_id = 90 or department_id = 100

區間查詢：多條件查詢的優化
關鍵字：between...and...
語法結構：where 欄位 between 條件一 and 條件二（閉區間）
作用：欄位的值在條件一與條件二之間

-- 需求:查詢工資大于等于10000并且小于等于24000的員工資訊
select employee_id,first_name,salary
from employees
where salary between 10000 and 24000

列舉查詢：對多條件查詢的優化
關鍵字：in（？，？，？）
語法結構：where 欄位 in （條件一，條件二...）
作用：欄位值等于條件一或等于條件二

--需求:查詢在90號部門或者在100部門的員工資訊
select employee_id,first_name,salary
from employees
where department_id in (90,100)

模糊查詢
關鍵字：like '模糊匹配字串'
- 模糊匹配字串
  ‘_’：一個下劃線，代表在此處有一個任意字符

-- 查詢以'S'開頭,并且名字長度為6的員工
'S_____'
select * 
from employees
where first_name like 'S_____'

	   '%'：一個百分號，代表在此處有0-n個任意字符

'S%' --->以S開頭,后面有幾個都行
Svn √          Sabclmasbfasbdm √                S √  
'%k'  --->以k結尾
dasdasdk √          k√
'%a%' --->含a就行,a能開頭也能結尾
bac √        ac√            gdfgdfgdfgdfga √

-- 查詢名字中有k的員工
select * 
from employees
where first_name like '%k%'

空值查詢
關鍵字：is [not] null
語法結構：where 欄位 is null
作用：查詢該欄位為空的資訊

-- 查詢沒有提成的員工資訊
select * 
from employees
where commission_pct is null

5.特殊關鍵字

sysdate：顯示當前系統時間，當做一個欄位來用

-- 查詢當前時間
select sysdate from employees

systimetamp：時間戳，當做一個欄位來用，比sysdate更加精確

select systimetamp from employees

dual：啞表，虛表，一行一列
dual從資料意義上來看，沒有任何作用
作用：為了維護sql陳述句語法的完整性

select sysdate from dual

函式

可以解決一個特定問題的sql語言

1.單行函式

表中任意一條資料都能得出一個結果

length（欄位/‘字串’）：字串的長度

-- 查詢‘fafafaf’的長度
select length('fafafaf') from dual

to_date(‘字串型別的日期’,‘日期的格式’)：將一個字串型別的日期,變為日期型別的日期

select to_date('2020-09-25','yyyy-mm-dd') from dual

年	yyyy
月	mm
日	dd
時	hh24
分	mi
秒	ss
星期	day

to_char(日期型別的日期,‘日期型別的格式’) : 將日期型別轉化為字串型別

2.組函式

需要表中的多條資料，經過分析，給出一個結果
常用組函式

count（欄位）統計有效行數
min（欄位）最小值
max（欄位）最大值
avg （欄位）平均值
sum （欄位）求和

--求工資最高的工資為多少
select max(salary),min(salary),avg(salary)
from employees


-- 求公司員工總人數
select count(employee_id)
from employees

select count(*)
from employees

-- 求有提成的人的數量
select count(commission_pct)
from employees
where commission_pct is not null

注意：

1.目前若select后添加了組函式，那么在select之后，就只能寫組函式，不能寫欄位名
2.在where后不能直接寫組函式
3.對null不做統計

分組

關鍵字：group by 欄位
作用：將表中的資料根據對應欄位進行分組
語法順序：select...from...where...group by...order by
執行順序：from...where...group by ...select...order by
注意：如果sql進行了分組，在select后可以寫分組依據欄位

--需求：統計員工表中每個部門的人數
select count(*),department_id
from employees
group by department_id

--需求:統計每個部門的平均工資
select department_id,avg(salary)
from employees
group by department_id

--需求:求每個部門的工資總數

select department_id,sum(salary)
from employees
group by department_id

--需求:求出平均工資大于9000的部門的平均工資
---1.求出所有部門平均工資
select avg(salary),department_id
from employees
group by department_id
---2.做條件查詢   avg(salary)>9000
select avg(salary),department_id
from employees
where  avg(salary)>9000
group by department_id    錯誤 ×××××××××××××××××××××××××××××

having

作用：分組之后的條件過濾
語法：select...from...where...group by...having...order by
指定順序：from...where...group...having...select...order by

--需求:求出平均工資大于9000的部門的平均工資
--1,求每個部門的平均工資
select avg(salary),department_id
from employees
group by department_id
having avg(salary)>9000

-- 需求:求70,80,90部門的人數
--1.求每個部門的人數
--2.在做過濾(having)  department_id in(70,80,90)
select department_id,count(*)
from employees
group by department_id
having department_id in (70,80,90)

--1.將70,80,90部門的人過濾出來
--2,將過濾后的人進行分組
select count(*),department_id
from employees
where deparment_id in (70,80,90)
group by department_id

偽列

在建表時不用主動宣告有此一列，但是建表完成Oracle資料庫會自動添加的一列
特點：不宣告查詢此列時不會自動顯示

rowid：儲存了一條資料在電腦硬碟中的保存位置

select e.*,rowid
from employees e

rownum

作用：給查詢結果進行排序,從1開始,依次加1，每次查詢,都重新排序，只給符合查詢結果的資料進行排序(rownum只能小于某個數,rownum不能大于某個數)

--查詢:員工表中的前十個人
select employee_id,first_name,salary,rownum
from employees e
where rownum <=10


--查詢:員工表中的第11~20人
select employee_id,first_name,salary,rownum
from employees e
where rownum >=11 and rownum<=20      錯××××××××××××××××××××××××××××

--查詢:工資最高的前十個人
select employee_id,first_name,salary,rownum
from employees 
where rownum<=10
order by salary desc                 錯XXXXXXXXXXXXXXXXXXXXXXXXXX
問題:先進行where判斷,再進行order by排序

子查詢

一個sql的查詢結果，為另一個sql的查詢條件

--查詢工資最高的人的資訊
-- 查詢最高工資   maxSalary
select max(salary)
from employees   ---maxSalary

select * 
from employees
where salary = (select max(salary)
from employees)

1.子查詢結果為一行一列

一般還會當做另一個sql的where條件來用

--查詢工資大于平均工資的人的資訊
--1.查出平均工資    avgSalary
select avg(salary)
from employees

--2.以平均工資作為條件查詢員工資訊  where salary>avgSalary
select * 
from employees
where salary > avgSalary

--3.組裝
select * 
from employees
where salary > (select avg(salary)
from employees)

2.子查詢結果為n行一列

會作為另一個sql的查詢條件，一般用作列舉查詢

--查詢和last_name='King'的員工在同一個部門的所有員工資訊
--1.查'King'在哪個部門
select department_id
from employees
where last_name='King'   ----x1,x2
--2.查詢在x1和x2部門的人
select employee_id
from employees
where department_id in (x1,x2)
--3.組裝
select employee_id
select *
from employees
where department_id in (select department_id
         from employees
         where last_name='King')

3.子查詢結果為n行n列

結果作為另一個sql的資料來源表，寫在from后面

--查詢工資最高的前十個人
--1.給salary進行排序
select *
from employees
order by salary desc
--2.給排序的結果添加rownum  t1
select t1.*,rownum
from t1

--3.組裝
select t1.*,rownum
from (select *
from employees
order by salary desc) t1
where rownum<=10

4.分頁查詢

--需求:工資降序排序,每頁十條,查詢第二頁
--工資降序排序,查詢第11條到第20條
--1.將工資降序排序
select *
from employees
order by salary desc
--2.給降序的結果添加rownum
select t1.*,rownum r
from (select *
		from employees
		order by salary desc) t1
--3.使rownum能大于某個數
select *
from (select t1.*,rownum r
		from (select *
				from employees
				order by salary desc) t1)
where r>=11 and r<=20......

注:三層嵌套
內層:排序
中間:給排序的結果添加偽列rownum
外層:把偽列變為實際的一列

表連接

查詢結果分別在兩張表中，想要得到完整結果，需要從兩張表中找資料

1.內連接

關鍵字：[inner] join
語法規則：from 表1 [inner] join 表2 on 表1.欄位名=表2.欄位名
注意：內連接只展示兩表之間有關聯的內容，如果有一條資料在另一張表中沒有與之關聯的資料，內連接會舍棄此資料

--使用內連接查詢員工id,姓名,工資,部門編號,部門名
select e.employee_id,e.first_name,e.salary,e.department_id,d.department_name
from employees e inner join departments d
on e.department_id = d.department_id

2.外連接

左外連接
關鍵字：left [outer] join
語法規則：from 表1 left [outer] join 表2 on 表1.欄位名 = 表2.欄位名

左表的所有內容全都展示,右表只展示與左表相關內容

--使用左外連接查詢員工id,姓名,工資,部門編號,部門名
select e.employee_id,e.first_name,e.salary,e.department_id,d.department_name
from employees e left outer join departments d
on e.department_id = d.department_id

右外連接
關鍵字：right join
語法規則：>from 表1 right [outer] join 表2 on 表1.欄位名 = 表2.欄位名
全外連接
關鍵字：full join
語法規則：>from 表1 full [outer] join 表2 on 表1.欄位名 = 表2.欄位名

3.表連接的應用

--需求：查詢部門資訊：部門編號，部門名稱，部門所在地標號，部門所在城市
--表連接  departments  lcoations   鏈接條件 d.location_id = l.location_id

select *
from departments d left join locations l
on d.location_id = l.location_id



--需求：查詢員工的工號，名字，工資，部門編號，部門名稱，部門所在地編號，部門所在城市
-- 表連接  employees departments lcoations
--1.鏈接 employees departments
select *
from employees e left join departments d
on e.department_id = d.department_id
--2.將上述結果與locations鏈接
select *
from employees e left join departments d
on e.department_id = d.department_id
left join locations l
on d.location_id = l.location_id



--需求：查詢員工的工號，名字，工資，領導編號，領導名字
--表連接  員工表(employees)  領導表(employees)  自連接
--物理上是同一張表,邏輯上是兩張表
select e1.*,e2.*
from employees e1 left join employees e2
on  e1.manager_id = e2.employee_id

建表

create table 表名(
	欄位1的名字  資料型別  (約束),
	欄位2的名字  資料型別  (約束),
	.......
	欄位n的名字  資料型別  (約束)
)

資料型別

1.數字型別

number(x1) ----> 該欄位只能保存數字,是個整數,最大有x1位
number(4) -----> 最大能保存9999
number(x1,x2) ---->該欄位只能保存數字,可以保存小數,小數位數最多有x2位,整數位數最多有x1-x2位
number(5,2) ---->999.99
double integer

2.字串型別

可變長字串
varchar2(n) —> 該欄位能保存字串,字串最大有n個字符
varchar2(200) —>最大能寫200個字符
如果我存的內容不夠200,不夠就不夠

定長字串
char(n)
char(200) —>該欄位只能存200個字符,不能多也不能少
如果我存的內容不夠200,在后面補空格,直到補夠為止

3.日期

date -------->日期

4.補充

club:大文本域,可以保存很多字串,varchar2最大能保存4000個字符
blub:二進制檔案,可以保存圖片,音樂,視頻…

約束

主鍵約束(primary key)
要求該欄位的值不能為空,不能重復
舉個栗子: 身份證ID,學號,工號…

非空約束(not null)
要求該欄位的值不能為空
栗子: 姓名

不可重復(unique)
要求該欄位的值不能重復

自定義約束(check)
栗子:
銀行卡密碼(pwd)必須六位
check(length(pwd)=6)
必須要以qq郵箱注冊(email)—> 結尾 @qq.com
check(email like ‘%@qq.com’)

外鍵約束(foreign key)
語法: references 表名(欄位名)
作用:使欄位的值只能從關聯的表中的欄位取值,或者是null
要求:先有外鍵關聯的表,再有存在外鍵的表

班級表 
   c_id
   c_name
   
學生表
    s_id
    s_name
    c_id
   
create table t_cla(
       c_id number(2) primary key,
       c_name varchar2(200)
)

create table t_stu(
       s_id number(4) primary key,
       s_name varchar2(200),
       c_id references t_cla(c_id)
)

增刪改操作

CRUD:增刪改查

1.增加操作

insert into 表名 (欄位1,欄位2,欄位3…) values (欄位1對應的值,欄位2對應的值…)
注意:添加時只需保證前面的欄位名與后面的欄位值一一對應,不需要與表中的欄位順序完全一致
添加時可以不寫欄位名,但是要保證欄位值的順序與表中欄位的順序完全一致

2.洗掉操作

delete [from] 表名 [where 限制條件]
作用:將符合限制條件的資料洗掉
注意:如果沒有where限制條件,將會洗掉表中的所有資料
–洗掉person_id = 3 的資料
delete from t_person where person_id=3
delete t_person —>洗掉表格中的所有資料
drop table 表名 —>洗掉表格

3.修改操作

update 表名 set 欄位1=新值,欄位2=新值… [where 限制條件]
作用:將符合限制條件的記錄中的欄位改為新的值
注意:如果沒有限制條件,會將表中的所有資料的該欄位都修改為新的值

SQL的分類

dql：資料查詢語言 select
dml：資料控制語言 insert delete update
ddl：資料定義語言 create drop
tcl：事務控制語言 commit（提交） rollback（回滾）

事務

在sql運行中,多個sql陳述句要不一起執行成功,要不一起執行失敗

事務的運行原理

原子性:在同一個事務的sql不可分割,要不同時成功,要不同時失敗
一致性:一旦提交事務,在回滾段中的內容會完全復制到原始資料中,提交之前的查詢,與提交之后的查詢結果一致
隔離性:在事務沒有提交或者回滾時,其他用戶不可得知事務中的內容
持久性:一旦提交或者回滾事務,事務堆資料庫的影響是永久的不可修復的

序列

oracle提供的一個可以自動生成用不重復的數字的工具

創建序列
create sequence 序列名 [start with 數字]

create sequence seq_person

drop sequence 序列名

使用
序列名.nextval -->獲取當前值然后序列+1
序列名.currval -->獲取當前值,但是不增加

1.視圖

將一個dql的查詢結果當做一張表來進行使用

創建視圖
create view 視圖名 as dql(查詢陳述句)

create view view_per as select * from t_person

drop view 視圖名

select * 
from view_per

作用:

1.將復雜的sql簡單化
? 2.對開發者屏蔽底層資訊

注意:

視圖將一個dql陳述句保存起來
使用視圖可以提高查詢效率錯XXXXXXXXXXXXXXXXXXXXXXXX

2.索引

資料庫給一張表創建的目錄,在做dql(查詢),如果使用了索引查詢,查詢速度會大大提高

創建:
create index 索引名 on 表名(欄位名) --給表中的某個欄位添加了索引
create index stu_index on t_stu(stu_id)

drop index 索引名

使用:自動使用(只針對有索引的欄位)
select * from t_stu where stu_id=5; ---可以使用索引

注意：

? 1. 給表中的所有欄位都添加索引?

  	錯,索引可以提高查詢效率,但是會降低增刪改效率

? 2. 應該哪些欄位添加索引

     主鍵自帶索引,不用手動創建,添加索引的欄位最好有唯一約束

3. 添加索引的欄位不能經常被修改

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/247709.html

標籤：其他

上一篇：SQL Server On Linux：基于實際專案案例，總結功能支持情況及相關問題解決方案

下一篇：ORACLE中通過SQL陳述句(alter table)來增加、洗掉、修改欄位