Transcript select - Staff of CIT

Structured Query Language
(SQL) (2)
357337 – Web Programming and Web Database
1
SQL : String Operations
• LIKE operator
– Percent ( % ): ตรงกับทุก substring
้ึ ต้นด้วย Bang (e.g.,
• “Bang%” ตรงกับคำทุกคำทีข
่ น
BangSue, Bangkok)
• %ko%
คือ คำทีม
่ ี ko อยูข
่ ำ้ งใน เช่น Bangkok,
kingkong
– Underscore ( _ ): ตรงกับทุกอักขระ
้ึ ต้นด้วย Bang และตำมด้วย
• “Bang _ _” ตรงกับคำทุกคำทีข
่ น
อีก 2 ตัวอักษร (e.g., Bangna)
• ___
คือ คำทีม
่ ี 3 ตัวอักษร เช่น kok, abc, xxx
• _ _ _ % คือ คำทีม
่ ีอย่ำงน้อย 3 ตัวอักษร เช่น kok, bangk
2
SQL : String Operations
• escape (\) ใช้ในกรณี ทต
ี่ อ
้ งกำรรวม special character
(I.e., %, \) ไว้ใน string เอง เช่น like “ab\%cd%” ตรง
กับทุก string ทีข
่ น
ึ้ ต้นด้วย ab%cd
• || concatenation
– กำรต่อ strings เช่น “(662)” || “585-8541” ==
“(662)585-8541”
• extract substrings
• หำควำมยำวของคำ
• แปลงให้เป็ นอักษรตัวพิมพ์ใหญ่, เล็ก หรือ Title
• soundex (กำรหำคำเสียงคล้ำย)
3
ตัวอย่ำง
Relational Schema
– Student ( ID, Firstname, Lastname)
จงเขียนคำสั่ง SQL เพือ
่ แสดง
• ชือ
่ ของนักเรียนทีข
่ น
ึ้ ต้นด้วย Wat
SELECT Firstname FROM Student
WHERE Firstname LIKE “Wat%”
• ชือ
่ และนำมสกุลของนักเรียนทีม
่ ีชอ
ื่ ยำว 5 ตัวอักษร
SELECT Firstname, Lastname FROM Student
WHERE Firstname LIKE “_____” AND
Lastname LIKE “_____”
LIMIT
• คำสั่ง LIMIT ใน mySQL ใช้ตอ่ ท้ำยคำสั่ง SELECT เพือ
่ จำกัด
ผลลัพธ์ทแ
ี่ สดงออก
• วิธีใช้แบบที่ 1 LIMIT <จำนวนแถวทีจ่ ะให้แสดง>
LIMIT
• วิธีใช้แบบที่ 2 LIMIT <แถวเริม
่ ต้น> , <จำนวนแถวทีจ่ ะแสดง>
• ระวัง!! แถวเริม
่ ต้นจะเริม
่ นับทีแ
่ ถวที่ 0
Order
• กำรเรียงลำดับผลลัพธ์
SELECT a1, a2, …, an
FROM r
ORDER BY aj1, …, ajn [ASC, DESC]
• กำรเรียงลำดับโดย default เรียงจำกน้อยไปมำก (ASC)
• เรียงจำกมำกไปน้อย (DESC)
• หำกต้องกำรเรียงลำดับมำกกว่ำ 1 attributes หรือทีแ
่ ตกต่ำงไปจำก
default ต้องระบุปะท้ำย ASC หรือ DESC ของแต่ละ attribute
นัน
้
• เป็ น operation ทีใ่ ช้เวลำในกำรทำงำนมำก จึงควรทำเฉพำะทีจ่ ำเป็ น
ตัวอย่ำง order by
• หำชือ
่ พนักงำน ชือ
่ แผนก และเงินเดือนของพนักงำนคนนัน
้
ๆ เรียง ตำมชือ
่ แผนก และ ตำมเงินเดือนจำกมำกไปน้อย
SELECT fname, lname, dName, salary
FROM employee, department
WHERE employee.dNo = department.dNumber
ORDER BY dName ASC, salary DESC
Aggregation Functions
• Built-in aggregate functions
– ค่ำเฉลีย่ : avg
– ค่ำตำ่ สุด: min
– ค่ำสูงสุด: max
– ค่ำรวม: sum
– นับจำนวน tuples: count
• มักใช้รว่ มกับ group by
ตัวอย่ำง Aggregation Functions



นับจำนวนพนักงำนในบริษท
ั
SELECT COUNT(*)
FROM employee
หำเงินเดือนเฉลีย่ ของพนักงำน
SELECT AVG(salary)
FROM employee
หำเงินเดือนรวม, เงินเดือนตำ่ สุด
และสูงสุดของพนักงำน
SELECT SUM(salary),
MIN(salary),
MAX(salary)
FROM employee


นับจำนวนพนักงำนในแต่ละแผนก
SELECT dNo, COUNT(*)
FROM employee
GROUP BY dNo;
หำเงินเดือนเฉลีย่ ของแผนกทีม
่ ี
ค่ำเฉลีย่ สูงกว่ำ 50000
SELECT dNo,AVG(salary)
FROM employee
GROUP BY dNo
HAVING AVG(salary)>
50000
ตัวอย่ำง Aggregation Functions & Group BY
AccountID
BranchName
Balance
023-8-0254
Bangsue
10000
158-7-7894
Bangsue
30000
568-8-7483
Bangkhen
5000
120-3-0478
Bangkhen
40000
347-1-5640
Bangkhen
20000
Bangsue
Bangkhen
40000
65000
having clause: GROUP BY … HAVING ...
• เพิม
่ เงือ
่ นไขโดยกำรใช้ Aggregate function กับคำสั่ง
GROUP BY
SELECT a1, a2, …, an
FROM r
GROUP BY aj
HAVING avg(aj) > 50000
ตัวอย่ำง HAVING
• จงหำชือ
่ สำขำธนำคำรทีม
่ เี งินฝำกรวมมำกกว่ำ 60000 บำท
AccountID
BranchName
Balance
023-8-0254
Bangsue
10000
158-7-7894
Bangsue
30000
568-8-7483
Bangkhen
5000
120-3-0478
Bangkhen
40000
347-1-5640
Bangkhen
20000
Bangsue
40000
Bangkhen
SELECT BranchName FROM Account
GROUP BY BranchName
HAVING SUM(Balance) > 60000;
65000
สรุปรูปแบบกำรทำ SQL Query
• SELECT/FROM/WHERE
SELECT <attribute list>
FROM
<table list>
[WHERE <conditions P>]
[GROUP BY <grouping attributes>]
[HAVING <group condition>]
[ORDER BY <attribute list>]
Rename Operation
• สำมำรถทำกำร rename ได้ทง้ ั attributes และ relations
• รูปแบบ: <old name> AS <new name>
– Rename Attribute:
SELECT fname AS “Name”, dName, salary
FROM employee, department
WHERE employee.dNo = department.dNumber
– Rename ตำรำง:
SELECT fname, lname, e.dNo, dName, salary
FROM employee AS e, department AS d
WHERE e.dNo = d.dNumber
SQL : Set Operations
• Union: union และ union all
• Intersect: intersect และ intersect all
• Except : except และ except all (ในบำง DBMS ใช้
MINUS)
• ถ้ำต้องกำรให้ไม่กำจัดค่ำซำ้ ใช้ <set operation> all
(e.g. union all)
SQL : Union
borrower
depositor
จงหำ รหัสของลูกค้ำของธนำคำรทีก
่ เู้ งินกับธนำคำร
หรือมีเงินฝำกกับธนำคำร
SQL : Union
 Cust _ id (borrower )   Cust _ id (depositor )
SQL : Set Intersect
• ใน MySQL ไม่มีคำสั่ง intersect ให้ใช้โดยตรงแต่สำมำรถเขียนได้
ดังนี้
 cust _ id (borrower )   cust _ id (depositor )
• SELECT cust_id FROM borrower WHERE cust_id
IN (SELECT cust_id FROM depositor)
SQL : Set Difference
• ใน MySQL ไม่มีคำสั่ง set difference โดยตรงแต่สำมำรถเขียนได้
ดูตวั อย่ำง
 Cust _ id (borrower )   Cust _ id (depositor )
• SELECT cust_id FROM borrower WHERE cust_id
NOT IN (SELECT cust_id FROM depositor);
SQL : Natural Join
Cust_id
Name
Cust_id
City
001-586-777
John
001-586-777
Bangkok
367-895-124
DJ
367-895-124
Nonthaburi
CustName
CustCity
วิธีทใี่ ช้ Cartesian product :
CustName.Cust _ id , Name,City ( CustName.Cust _ id CustCity.Cust _ id (CustName X CustCity))
วิธีทใี่ ช้ Natural-Join :
 CustName.Cust _ id , Name,City (CustName
CustCity))
SQL : Natural Join
Export A MySQL Database
• เมือ
่ ผูด
้ แ
ู ลระบบต้องกำรจะ backup ฐำนข้อมูล หรือ นำฐำนข้อมูลนี้ไปใช้
กับเครือ
่ งอืน
่ ใน MySQL สำมำรถใช้คำสั่ง
mysqldump -u login –p database_name > FILE.sql
–
–
–
–
login คือ login ของผูม
้ ีสท
ิ ธิใ์ นกำรทำงำนกับฐำนข้อมูลนัน
้ เช่น root
password คือรหัสผ่ำน
database_name คือ ชือ
่ ของฐำนข้อมูลทีต
่ อ้ งกำรจะ backup
FILE.sql คือ ชือ
่ file ทีจะเก็บฐำนข้อมูลทีต
่ อ้ งกำรจะ backup
ตัวอย่ำง
mysqldump –u root –p ectstudent > ect.sql
Import A MySQL Database
• เมือ
่ ผูด
้ แ
ู ลระบบต้องกำรนำฐำนข้อมูลที่ backup ไว้แล้วมำใช้ใน
ฐำนข้อมูลปัจจุบน
ั ใน MySQL สำมำรถใช้คำสั่ง
mysql -u login –p database_name < FILE.sql
–
–
–
–
login คือ login ของผูม
้ ีสท
ิ ธิใ์ นกำรทำงำนกับฐำนข้อมูลนัน
้ เช่น root
password คือรหัสผ่ำน
database_name คือ ชือ
่ ของฐำนข้อมูลทีต
่ อ้ งกำรจะ import
FILE.sql คือ ชือ
่ file ทีจะเก็บฐำนข้อมูลทีต
่ อ้ งกำรจะ import
ตัวอย่ำง
mysql –u root –p ectstudent < ect.sql