Transcript MySQL資料庫教學 - Java2物件導向

SQL的資料處理語言
7-1選取查詢select
7-1-1運算子與函數
7-1-2 where 、like 、group by 和order by
7-1-3定義欄位的表格
7-1-4多個資料表查詢
7-1-5SQL的查詢語言
7-2insert新增資料
7-2-1插入1
7-2-2插入2
7-2-3選取插入
7-3delete刪除資料和update更新資料
7-3-1delete
7-3-2update
7-3-3update集合資料
• 資料處理語言DML(data manipulation
language)。當資料庫的schema已經被編譯，
使用者可以使用資料處理語言DML來操作
資料庫。一般的資料處理語言包括抽取資
料、插入資料、刪除資料和修改資料這幾
種。
• 我們經常用到選取查詢(select)敘述、插入
(insert)敘述、更新敘述(update)、刪除敘述
(delete)，這些都是SQL的資料操作。
1-1選取查詢select
•
•
•
•
•
•
•
select 欄位名稱串列
from 資料表串列
where 條件
group by 群組條件
order by 排序條件
having by 篩選經過group by 群組之後的資料。
使用select來選取total_tutor資料表中的資料編號為
5號的資料。
• select * from total_tutor where number=3
• 選取total_tutor資料表的number和name欄
位，我們使用total_tutor.Number和
total_tutor.name這兩個欄位，並且查出編
號number=3的資料。
• select total_tutor.Number,total_tutor.name
from total_tutor where number=3
我們設定tutor.salary的資料為m並且當其m為350才符合，而且資料的編號必需
大於150，使用experience為群組。
select tutor.number,tutor.name,tutor.salary as m from tutor where number >150
group by experience having m=350
1-1-1運算子與函數
• 在選取敘述中我們用了number > 150條件，
而>大於就是一個運算子。MySQL提供我們
基本的運算子，我們可以使用算術運算子
來運算資料並且限制資料的條件。
算術運算子
邏輯運算子
比較運算子
範例and
•
•
•
•
•
select number,name,experience
from tutor
where name like “吳%” and experience >5
order by number desc
顯示姓吳的和經驗大於5在tutor資料表的所
有資料
範例or
•
•
•
•
顯示姓吳的或姓徐的家教老師。
Select number,name,experience
From tutor
Where name like “吳%” or name like “徐%”
• MySQL對於group by 群組敘述也提供了我們簡單的函數
• 群組資料和這些函數讓我們可以分析資料裡面的內容，使
得方便運用及分析資料。
• Count函數：與group by 敘述，可以得到該群組的數目。
• Count(*)取得群組的數值
• 使用count(*)，可以算出資料編號從5到150號的經驗群組
group by experience，經驗為1的總共有9人。
• Select total_tutor.name,total_tutor.experience,COUNT(*)
from total_tutor where total_tutor.number <150 and
total_tutor.number >5 group by experience
• avg(欄位名稱)
• avg(欄位名稱) 搭配group by就可以算出該群組的
平均值
• 我們以經驗為群組資料編號從5到350作分析，得
出經驗第4和第6等級的家教老師終點費需求較底。
• Select total_tutor.experience,avg(salary) from
total_tutor where total_tutor.number <350 and
total_tutor.number >5 group by experience
• max(欄位)
• max( )函數可以算出該欄位經過群組後的最
大值
• 從編號2到編號50，經驗6的群組中最高要
求薪資為每小時350元的鐘點費。
• Select total_tutor.experience,max(salary)
from total_tutor where number >2 and
number <50 group by experience
• min( )函數可以算出該欄位經過群組後的最
小值
• 在total_tutor資料表中，第三級中最少的鐘
點費也要300元
• Select total_tutor.experience,min(salary)
from total_tutor where number >2 and
number <50 group by experience
• stddev( )函數
• stddev( )函數可以算出該欄位經過群組後的標準
差
• 我們可以發現第三級、第五級、第七級和第八級
的標準差較小，也就是在同等級中鐘點費較為平
均，但第一級和第六級的標準差大，也就是其差
異很大。
• Select total_tutor.experience,stddev(salary) from
total_tutor where number >2 and number <50
group by experience
• sum( )函數
• 我們使用sum( )函數可以求出群組後的總值
• 在total_tutor資料表中，以編號0號到50號
資料可以群組分類算出該類經驗欄的總值。
• Select
total_tutor.experience,sum(experience)
from total_tutor where number >2 and
number <50 group by experience
1-1-2 where 、like 、group by 和
order by
•
•
•
•
•
where 子句
語法：
where 欄位名稱 = 運算式 或 ‘字串’
使用where來限制條件
我們使用where來尋找編號為3的資料，並
且顯示total_tutor資料表中number、name
和salary的欄位資料。
• select number, name,salary from
total_tutor where number=3
• where 欄位名稱 like 子句
• 語法 where欄位名稱like
• select total_tutor.number,total_tutor.name from
total_tutor where name like "吳%"
• %為萬用字元，他是任意長度的任意字元。
• 我們可以使用like子句找尋吳開頭的名子，
我們只要在吳後面加入%，就是代表只要為
吳開頭的就可以顯示。這是total_tutor資料
表中以吳為開頭的資料。
•
•
•
•
•
•
•
group by子句
將指定同值欄位的紀錄資料群組起來
語法：group by 群組欄位
select number,name,experience
from tutor where name like “吳%”
group by experience
我們使用group by將經驗相同的值群組起來，下
面範例我們將經驗為6的群組起來了編號127和
313成為編號127
•
•
•
•
•
•
•
•
•
•
•
have 子句
語法：have 條件
使用having子句來限制條件，通常跟在group by 之後。
在這裏限制編號250到256的資料，我們使用having n>250 and
n<256。
select tutor.number as n,tutor.name
from tutor
where tutor.number <256 and tutor.number >250
group by experience
having n >250 and n<256
order by salary
這裏顯示資料從250到256以經驗experience為群組的資料，並且以薪
資高的排序資料。
•
•
•
•
•
•
•
order by 子句
我們使用order by 來排序某個欄位
語法：order by 欄位
select number,name,experience
from tutor where name like “吳%”
order by number
我們根據編號欄位來排序姓名為吳開頭的
資料。
•
•
•
•
•
•
使用遞減來排序欄位
語法：Order by 欄位 desc
select number,name,experience
from tutor where name like “吳%”
order by number desc
在tutor資料表中搜尋以吳為開頭的資料，
並依據其編號使用遞減desc來排序欄位。
1-1-3定義欄位的表格
• 我們使用表格 as 變數名稱，來定義表格。
通常as命名表格的代號。
• 語法：表格 as 變數名稱
• 定義表格tutor為t，t.number的編號在156到
153之間
• select number,name,experience
• from tutor as t
• where t.number<156 and t.number >153
1-1-4多個資料表查詢
• 當我們需要兩個表格以上作顯示時使用已定義過的表格代
號，將更為方便。
• 語法：
• select 表格1.欄位名稱，表格2.欄位名稱
• from 表格1，表格2
• 我們將tutor資料表定義為t的代號，並將asir資料表定義為a
的代號，然後再用select來操作資料表的資料。
• select t.name,a.name,a.tcourse
• from tutor as t,asir as a
• where (t.tcourse=a.tcourse and t.salary=a.salary) and
(a.number<15 and a.number >10)
• 這是合併tutor資料表和asir資料表的資料。
• join 語法
• select 表格1.欄位 , 表格2.欄位 from 表格1 join 表
格2
• 我們使用join來合併tutor和asir資料表。
• select tutor.name,asir.name,asir.tcourse
• from tutor join asir
• where (tutor.tcourse=asir.tcourse and
tutot.salary=asir.salary)
• and (asir.number <15 and asir.number >10)
• 這是合併tutor資料表和asir資料表的資料。
1-1-5SQL的查詢語言
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
一個SQL的查詢語言可以由六個子句所組成，只有select和from是必須要的
select <欄位或函數串列>
from <表格串列>
where<條件>
group by <群組欄位>
having <群組條件>
order by <欄位>
我們使用tutor.number as n來設定編號的代號為n，並且使用having限制條件時
使用運算式，運算式使用資料表的代號n來作運算。
Select tutor.number as n,tutor.name
From tutor
Where tutor.number <256 and tutor.number >250
Group by experience
Having n >250 and n<256
Order by salary
這是編號250到256的資料，並且使用薪水作為排序的根據。
1-2insert新增資料
• 我們使用insert指令就可以把資料輸入資料
庫
1-2-1插入1
•
•
•
•
•
•
•
•
•
•
語法：
insert into 資料表
(欄位1,欄位2,欄位3….)
values
(欄位1的值,欄位2的值,欄位3的值…..)
我們將資料“陳小胖”,800,”台北市北投區”按照欄位
(name,salary,location)依序插入到tutor資料表中。
insert into tutor
(name,salary,location)
values
(“陳小胖”,800,”台北市北投區”);
1-2-2插入2
•
•
•
•
•
語法﹕
insert into 資料表
values
(欄位1的值,欄位2的值,欄位3的值…)
我們使用insert指令將資料(1,'吳佳諺',98)插
入student資料表中。
• INSERT INTO student VALUES (1,'吳佳諺
',98);
1-2-3選取插入
•
•
•
•
•
•
•
•
•
•
•
•
我們可以將選取select的資料表2資料增加insert到資料表1
語法：
insert into 資料表1
(資料表1的欄位1,資料表1欄位2,資料表1的欄位3)
select (資料表2的欄位1，資料表2的欄位2，資料表2的欄位3)
from 資料表2
我們使用select指令將asir資料表中編號從50到53的資料插入tutor資
料表中。
insert into tutor
(name,salary,location)
select name,salary,location
from asir
where number<53 and number>50
1-3delete刪除資料和update更新資料
1-3-1delete
•
•
•
•
•
•
•
•
•
我們使用delete子句就可以把資料列給刪除
語法：
delete from 資料表
where 條件
這樣就可以把tutor資料庫中編號為321到323的資料給去
刪除。
delete from tutor
where number<=323 and number >=321
我們如果使用delete指令而沒有限制條件，則會將所有的
tutor資料刪除。
delete from tutor
1-3-2update
•
•
•
•
•
•
•
•
•
•
如果我們要把資料列給更新則要用update子句
語法：
update 資料表
set 欄位 =更新的資料
where 條件
我們使用update total_tutor來更新資料編號為3的資料，
並且將其depar欄位設為”資訊工程”。
update total_tutor
set depar="資訊工程"
where Number=3
這是還未update的資料，其depar欄位為中國文學系。
• 這是已經被更新的欄位資料，depar欄位為
資訊工程。
1-3-3update集合資料
•
•
•
•
•
我們可以用in來指定集合的資料。
update total_tutor
set salary=salary*0.75
where Number in (3,5,6,7,10)
我們使用update來修正資料編號Number在3、
5、6、7、10的資料，將這些資料的薪水
salary欄位乘以0.75。