DML
新增資料
新增1筆資料
INSERT INTO < tablename>(column-list) value (value-list)
新增多筆資料
INSERT INTO < tablename>(column-list) value
(value-list 1),(value-list 2),…..(value-list n);
新增從sql撈出來的資料
INSERT INTO < tablename>(column-list) sub-sql
ex:
INSERT INTO company (company_id,contact,telnet, address)
SELECT id, contact,telnet,address FROM customer WHERE company=systw
…………….
更新資料
UPDATE < tablename> SET < column1=value1>[,< column2=value2>,…] [WHERE < condition>]
ex:
UPDATE 產品資料 SET 庫存量 = 300, 安全存量 = 50 WHERE 產品編號 = 13
UPDATE employee SET salary = salary*1.1
UPDATE employee SET salary = salary+(select avg(salary) from employee)*0.1
value可以為
指定的資料
default 保留字,使用該屬性的預設值
…………….
刪除資料
DELETE FROM < tablename> WHERE < condition>
清空資料表
有以下2種方法
TRUNCATE TABLE < tablename>
並不會將所刪除的執行過程記錄於log file(交易日誌檔)中
DELETE FROM < tablename>
會將所有被刪除的資料全部記錄於log file中,也會顯示刪除的數量
ps:
在刪除大量資料時，TRUNCATE較有效率
ps:
sql server設定log
1資料庫>指定的資料庫>內容
2選項>復原,模型:[簡易|完整|大量登入]
ps:log要使用另一套工具才有辦法分析
…………….
查詢資料
SELECT < express> FROM < tablename>
[WHERE < condition>]
[GROUP BY < column-list>]
[HAVING < condition>]
[ORDER BY < column1> [ASC|DESC] [,< column2> [ASC|DESC],…]]
常用的express有
< column-list | *> 指定要顯示的欄位或顯示所有欄位
DISTINCT < column> 指定該欄位的重複值只輸出一筆即可
< column> AS < newname> 設定欄位別名,可改變輸出時欄位的名稱
< operation> AS < newname> 將欄位做運算並給予別名
< sub-sql> 欄位的值使用sub-sql列出
ex:SELECT 實際單價*數量 AS 小計 FROM 訂單明細
ex:SELECT fname+’ ‘+lname AS name FROM employee
WHERE condition常見用法
% 表示任意長度
ex:找出id為3開頭的值 id like ‘3%’
_ or ? 表示1個字元
ex:找出id的10位數為2,且只有3個位數的值 id like ‘_2_’
[NOT] IN (< value1,value2[,value3,…] | sub-sql>) 表示多個or
ex:
sid=1 or sid=6 or sid=10等同於sid IN (1, 6, 10)
sid IN (select sid from employee)
[NOT] BETWEEN < min> AND < max> 表示在min和max範圍之間
ex:sid BETWEEN 5 AND 10 等同於 sid >5 and sid < 10
[NOT] EXIST (< sub-sql>) 測試是否存在,或測試是否不存在
ex:
SELECT 員工編號, 姓名 FROM 員工 WHERE EXISTS
( SELECT * FROM 訂單 WHERE 員工編號 = 員工.員工編號)
ps:
SELECT id, company FROM customer
WHERE company IN ( SELECT supply FROM supply)
ORDER BY id
等同於
SELECT id, company FROM customer, supply
WHERE company = supply
ORDER BY id
………………………………………………………………………………………..
聚合函數
聚合函數基本用法
格式為
SELECT < aggregate function> FROM < tablename>
ex:
列出所有employee的總筆數
SELECT COUNT(id) AS allemployee FROM employee
依group分組聚合函數結果
格式為
SELECT < aggregate function> FROM < tablename>
GROUP BY < column1 [,column2,…]>
成功分組需符合以下其中一個條件
條件1,被聚合函數所包含
條件2,分群的條件
ex:
列出男女employee各別的總筆數
SELECT sex,COUNT(id) FROM employee GROUP BY sex
列出每個部門的男女員工各別的數量
SELECT dno,sex,COUNT(id) FROM employee GROUP BY dno,sex
ex1:
列出每個員工總工時
資料表如下
employee(fname,minit,lname,ssn,bdate,address,sex,salary,super_ssn,dno)
works_on(essn,pro,hours)
語法如下
符合條件2,group by的欄位和select欄位相同
SELECT ssn,fname,lname,sum(hours) FROM employee,works_on
WHERE essn=ssn GROUP BY ssn,fname,lname
符合條件1,fname使用聚合函數max,lname使用聚合函數min
SELECT ssn,max(fname),min(lname),sum(hours) FROM employee,works_on
WHERE essn=ssn GROUP BY ssn
ex2
列出每個員工總工時的百分比
資料表如下
employee(fname,minit,lname,ssn,bdate,address,sex,salary,super_ssn,dno)
works_on(essn,pro,hours)
語法如下
SELECT ssn,fname,lname,sum(hours)/(select sum(hours) from works_on)*100
FROM employee,works_on
WHERE essn=ssn GROUP BY ssn,fname,lname
過濾group的結果
格式為
SELECT < aggregate function> FROM < tablename>
GROUP BY < column1 [,column2,…]>
HAVING < aggregate column condition>
ex1:
列出每個員工總工時大於100的員工
資料表如下
employee(fname,minit,lname,ssn,bdate,address,sex,salary,super_ssn,dno)
works_on(essn,pro,hours)
語法如下
SELECT ssn,fname,lname,sum(hours) FROM employee,works_on
WHERE essn=ssn GROUP BY ssn,fname,lname
HAVING sum(hours)>100
ROLLUP
可用來各別總計
格式為
GROUP BY < column1 [,column2,…]> with rollup
ps:
mysql不支援order by,若要排序則要直接在欄位後接asc或desc
ex:
假設SQL語法結果如下
SELECT year, SUM(profit) FROM sales GROUP BY year
2000 , 4525
2001 , 3010
使用ROLLUP可做總計
SELECT year, SUM(profit) FROM sales GROUP BY year WITH ROLLUP;
2000 , 4525
2001 , 3010
NULL , 7535
ex:
由大到小排序
SELECT year, SUM(profit) FROM sales GROUP BY year desc WITH ROLLUP;
2001 , 3010
2000 , 4525
NULL , 7535
CUBE
可用來對指定欄位做所有組合的總計
格式為
GROUP BY < column1 [,column2,…]> with cube
ex:
假設sql語法如下
SELECT ip,desturl,count FROM ip2url GROUP BY ip,desturl
執行結果為
ip,desturl,count
10.1.1.1,tw.yahoo.com,12
10.1.1.1,www.pchome.com,5
10.1.1.2,tw.yahoo.com,18
10.1.1.3,www.google.com,4
1
若以rollup為例
SELECT ip,desturl,count FROM ip2url GROUP BY ip,desturl WITH rollup;
執行結果為
ip,desturl,count
10.1.1.1,tw.yahoo.com,12
10.1.1.1,www.pchome.com,5
10.1.1.1,null,17
10.1.1.2,tw.yahoo.com,18
10.1.1.2,null,18
10.1.1.3,www.google.com,4
10.1.1.3,null,4
null,null,39
2
若以cube為例
SELECT ip,desturl,count FROM ip2url GROUP BY ip,desturl WITH cube;
執行結果為
ip,desturl,count
10.1.1.1,tw.yahoo.com,12
10.1.1.1,www.pchome.com,5
10.1.1.1,null,17
10.1.1.2,tw.yahoo.com,18
10.1.1.2,null,18
10.1.1.3,www.google.com,4
10.1.1.3,null,4
null,null,39
null,tw.yahoo.com,30
null,www.pchome.com,5
null,www.google.com,4
………………………………………………………………………………………..
合併資料表
使用WHERE語法合併
屬於sql89格式
等同於inner join ,也就是 (A)|X|a=b(B)
2個資料表的合併查詢格式如下
SELECT * FROM < table1,table2>
WHERE < table1.column = table2.column>
3個資料表的合併查詢
SELECT * FROM < table1,table2,table3>
WHERE < table1.column = table2.column &&
table2.column = table3.column>
4個資料表的合併查詢
SELECT * FROM < table1,table2,table3,table4>
WHERE < table1.column = table2.column &&
table2.column = table3.column &&
table3.column = table4.column>
ex1:
列出部門主管名稱(fname,lname)
資料表如下
employee(fname,minit,lname,ssn,bdate,address,sex,salary,super_ssn,dno)
department(dname,dnumber,mgr_ssn,mgr_start_date)
語法如下
使用where方法,step1 n*m=x 先合併, step2在過濾合併結果x
SELECT fname,lname FROM employee,department WHERE ssn=mgr_ssn
使用in方法,step1 先過濾m=y ,step2在從n挑出符合y的(效能較差)
SELECT fname,lname FROM employee WHERE ssn IN (SELECT mgr_ssn FROM department)
使用sub-sql方法(效能較好)
SELECT fname,lname FROM employee,(SELECT mgr_ssn FROM department) AS mgr WHERE ssn=mgr_ssn
ex2:
列出productx所參與的員工姓名與工時(fname,lname,hours)
資料表如下
employee(fname,minit,lname,ssn,bdate,address,sex,salary,super_ssn,dno)
project(pname,pnumber,plocation,dnum)
works_on(essn,pro,hours)
語法如下
使用where方法
SELECT fname,lname,hours FROM employee,project,works_on
WHERE pnumber=pno && ssn=essn && pname=’productx’
使用in方法
SELECT fname,lname,hours FROM employee,works_on
WHERE essn=ssn && pno IN (SELECT pnumber FROM project WHERE pname=’productx’)
使用sub-sql方法
SELECT fname,lname,hours
FROM employee,works_on,(SELECT pnumber FROM project WHERE pname=’productx’) AS p
WHERE essn=ssn && pno =pnumber
使用JOIN語法合併
屬於sql92格式
格式為
SELECT * FROM ( < table1> < join type> < table2> ON < table1.column = table2.column> )
join type可選inner join,left outer join,right outer join
以下為多資料表合併,以inner join為例
2個資料表的合併查詢
SELECT * FROM ( < table1> INNER JOIN < table2> ON < table1.column = table2.column> )
3個資料表的合併查詢
SELECT * FROM ( (< table1> INNER JOIN < table2> ON < table1.column = table2.column>)
INNER JOIN < table3> ON < table2.column = table3.column> )
4個資料表的合併查詢
SELECT * FROM ( < table1> INNER JOIN < table2> ON < table1.column = table2.column>
INNER JOIN < table3> ON < table2.column = table3.column>
INNER JOIN < table4> ON < table3.column = table4.column> )
ex1:
列出每個部門的經理姓名 (DEPARTMENT.dname,EMPLOYEE.fname,EMPLOYEE.lname)
資料表如下
employee(fname,minit,lname,ssn,bdate,address,sex,salary,super_ssn,dno)
department(dname,dnumber,mgr_ssn,mgr_start_date)
語法如下
SELECT dname,fname,lname FROM department INNER JOIN employee ON mgr_ssn=ssn
ex2:
列出每個員工參與專案的工時表(fname,lname,pname,hours)
資料表如下
employee(fname,minit,lname,ssn,bdate,address,sex,salary,super_ssn,dno)
project(pname,pnumber,plocation,dnum)
works_on(essn,pro,hours)
語法如下
使用innerjoin方法
SELECT fname,dname,pname,hours FROM (employee INNER JOIN works_on ON ssn=essn) INNER JOIN project ON pnumber=pno
使用where方法
SELECT fname,dname,pname,hours FROM employee,works_on,project
WHERE employee.ssn=works_on.essn && works_on.pno=project.pnumber
ex3:
列出所有員工與員工親屬姓名(fname,lname,dependent_name)
資料表如下
employee(fname,minit,lname,ssn,bdate,address,sex,salary,super_ssn,dno)
dependent(essn,depandent_name,sex,bdate,relationship)
語法如下
使用left outer join方法
SELECT fname,lname,dependent_name FROM employee LEFT OUTER JOIN dependent ON ssn=essn
使用right outer join方法
SELECT fname,lname,dependent_name FROM dependent RIGHT OUTER JOIN employee ON ssn=essn
以ssn為主,使用where方法
SELECT fname,lname,dependent_name FROM dependent,employee WHERE essn=*ssn
等同於
SELECT fname,lname,dependent_name FROM dependent,employee WHERE ssn*=essn
自我合併
where格式大致為
SELECT * FROM table1,table1 AS table2 WHERE < table1.column = table2.column>
join格式大致為
SELECT * FROM table1 < join type> table1 AS table2 ON < table1.column = table2.column>
ex1:
列出員工名稱及員工的主管名稱
資料表如下
employee(fname,minit,lname,ssn,bdate,address,sex,salary,super_ssn,dno)
語法如下
不會顯示老闆(因為老闆沒有主管)
SELECT emp.fname,emp.lname,’->’,super.fname,super.lname
FROM employee AS emp, employee AS super
WHERE emp.super_ssn=super.ssn
ps:顯示老闆
SELECT emp.fname,emp.lname,’->’,super.fname,super.lname
FROM employee AS emp, employee AS super
WHERE emp.super_ssn*=super.ssn
…………………..
union
將兩個sql的結果合併,不會重複列出
union all
將兩個sql的結果合併,若發生重複時則會全部列出
ex:
假設有以下兩table
srcip的ip有1.1.1.2 ,1.1.1.3
dstip的ip有1.1.1.1 ,1.1.1.2
ex:
執行sql語法
select ip from srcip
union
select ip from dstip
則結果如下
1.1.1.1
1.1.1.2
1.1.1.3
ex:
執行sql語法
select ip from srcip
union all
select ip from dstip
則結果如下
1.1.1.1
1.1.1.2
1.1.1.2
1.1.1.3
INTERSECT
將兩個sql的結果相同的合併
ex:
執行sql語法
select ip from srcip
intersect
select ip from dstip
則結果如下
1.1.1.2
MINUS
將第1個sql的結果扣掉第二個sql同樣的結果
ex:
執行sql語法
select ip from srcip
minus
select ip from dstip
則結果如下
1.1.1.3