Structured Query Language (SQL) (2)

1. Structured Query Language (SQL) (2) 357337 – Web Programming and Web Database

2. SQL : String Operations • LIKE operator • Percent ( % ): ตรงกับทุก substring • “Bang%” ตรงกับคำทุกคำที่ขึ้นต้นด้วย Bang (e.g., BangSue, Bangkok) • %ko% คือ คำที่มี ko อยู่ข้างใน เช่น Bangkok, kingkong • Underscore ( _ ): ตรงกับทุกอักขระ • “Bang _ _” ตรงกับคำทุกคำที่ขึ้นต้นด้วย Bang และตามด้วยอีก 2 ตัวอักษร (e.g., Bangna) • _ _ _ คือ คำที่มี 3 ตัวอักษร เช่น kok, abc, xxx • _ _ _ % คือ คำที่มีอย่างน้อย 3 ตัวอักษร เช่น kok, bangk

3. 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 (การหาคำเสียงคล้าย)

4. ตัวอย่าง 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“_____”

5. LIMIT • คำสั่ง LIMIT ใน mySQL ใช้ต่อท้ายคำสั่ง SELECT เพื่อจำกัดผลลัพธ์ที่แสดงออก • วิธีใช้แบบที่ 1LIMIT <จำนวนแถวที่จะให้แสดง>

6. LIMIT วิธีใช้แบบที่ 2LIMIT <แถวเริ่มต้น> , <จำนวนแถวที่จะแสดง> ระวัง!!แถวเริ่มต้นจะเริ่มนับที่แถวที่ 0

7. Order • การเรียงลำดับผลลัพธ์ SELECT a1, a2, …, an FROM r ORDER BY aj1, …, ajn [ASC, DESC] • การเรียงลำดับโดย default เรียงจากน้อยไปมาก (ASC) • เรียงจากมากไปน้อย (DESC) • หากต้องการเรียงลำดับมากกว่า 1 attributes หรือที่แตกต่างไปจาก default ต้องระบุปะท้าย ASC หรือ DESC ของแต่ละ attribute นั้น • เป็น operation ที่ใช้เวลาในการทำงานมาก จึงควรทำเฉพาะที่จำเป็น

9. ตัวอย่าง order by • หาชื่อพนักงาน ชื่อแผนก และเงินเดือนของพนักงานคนนั้น ๆ เรียง ตามชื่อแผนก และ ตามเงินเดือนจากมากไปน้อย SELECT fname, lname, dName, salary FROM employee, department WHERE employee.dNo = department.dNumber ORDER BY dName ASC, salary DESC

10. Aggregation Functions • Built-in aggregate functions • ค่าเฉลี่ย : avg • ค่าต่ำสุด: min • ค่าสูงสุด: max • ค่ารวม: sum • นับจำนวน tuples: count • มักใช้ร่วมกับ group by

11. ตัวอย่าง 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

12. ตัวอย่าง Aggregation Functions& Group BY Bangsue 40000 Bangkhen 65000

13. having clause: GROUP BY … HAVING ... • เพิ่มเงื่อนไขโดยการใช้ Aggregate function กับคำสั่ง GROUP BY SELECT a1, a2, …, an FROM r GROUP BY aj HAVING avg(aj)> 50000

14. ตัวอย่าง HAVING Bangsue 40000 Bangkhen 65000 จงหาชื่อสาขาธนาคารที่มีเงินฝากรวมมากกว่า 60000 บาท SELECT BranchName FROM Account GROUP BY BranchName HAVING SUM(Balance) > 60000;

15. สรุปรูปแบบการทำ 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>]

16. Rename Operation • สามารถทำการ rename ได้ทั้ง attributes และ relations • รูปแบบ: <old name> AS <new name> • Rename Attribute: SELECT fnameAS “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

17. SQL : Set Operations • Union: union และ union all • Intersect: intersect และ intersect all • Except : except และ except all (ในบาง DBMS ใช้ MINUS) • ถ้าต้องการให้ไม่กำจัดค่าซ้ำ ใช้ <set operation> all (e.g. union all)

18. SQL : Union borrower depositor จงหา รหัสของลูกค้าของธนาคารที่กู้เงินกับธนาคารหรือมีเงินฝากกับธนาคาร

19. SQL : Union

20. SQL : Set Intersect • ใน MySQL ไม่มีคำสั่ง intersect ให้ใช้โดยตรงแต่สามารถเขียนได้ ดังนี้ • SELECT cust_id FROM borrowerWHERE cust_id IN(SELECT cust_id FROM depositor)

21. SQL : Set Difference ใน MySQL ไม่มีคำสั่ง set difference โดยตรงแต่สามารถเขียนได้ ดูตัวอย่าง SELECT cust_id FROM borrowerWHERE cust_id NOT IN (SELECT cust_id FROM depositor);

22. SQL : Natural Join CustName CustCity วิธีที่ใช้ Cartesian product : วิธีที่ใช้ Natural-Join :

23. SQL : Natural Join

24. Export A MySQL Database • เมื่อผู้ดูแลระบบต้องการจะ backup ฐานข้อมูล หรือ นำฐานข้อมูลนี้ไปใช้กับเครื่องอื่น ใน MySQL สามารถใช้คำสั่ง mysqldump -ulogin –pdatabase_name>FILE.sql • login คือ login ของผู้มีสิทธิ์ในการทำงานกับฐานข้อมูลนั้น เช่น root • passwordคือรหัสผ่าน • database_nameคือ ชื่อของฐานข้อมูลที่ต้องการจะ backup • FILE.sqlคือ ชื่อ file ทีจะเก็บฐานข้อมูลที่ต้องการจะ backup ตัวอย่าง mysqldump –u root –pectstudent> ect.sql

25. Import A MySQL Database • เมื่อผู้ดูแลระบบต้องการนำฐานข้อมูลที่ backup ไว้แล้วมาใช้ในฐานข้อมูลปัจจุบัน ใน MySQL สามารถใช้คำสั่ง mysql -ulogin –pdatabase_name<FILE.sql • login คือ login ของผู้มีสิทธิ์ในการทำงานกับฐานข้อมูลนั้น เช่น root • passwordคือรหัสผ่าน • database_nameคือ ชื่อของฐานข้อมูลที่ต้องการจะ import • FILE.sqlคือ ชื่อ file ทีจะเก็บฐานข้อมูลที่ต้องการจะ import ตัวอย่าง mysql –u root–pectstudent<ect.sql