Introduction to MySQL Functions: Essential Date and String Operations

Introduction to SQL - 3 (MySQL) 353352 – Special Problem (Database) ChoopanRattanapoka

MySQL Functions • MySQL มีฟังค์ชั่นพื้นฐานมาให้ใช้มากมายซึ่งสามารถดูชื่อทั้งหมดที่ http://dev.mysql.com/doc/refman/5.0/en/func-op-summary-ref.html • Function ที่ใช้น่ารู้เกี่ยวกับ วันและเวลา • NOW() คืนค่าวันและเวลา ณ เวลาปัจจุบัน • CURDATE() คืนค่าวันปัจจุบัน • CURTIME() คืนค่าเวลาปัจจุบัน • SELECT NOW(), CURDATE(), CURTIME(); • DATEDIFF(วันเวลาที่1 , วันเวลาที่2) คืนจำนวนวันที่ต่างกันของ วันเวลาที่1 และ วันเวลาที่ 2 • SELECT DATEDIFF('2007-12-31 23:59:59','2007-12-30'); คืนค่า 1 • DATE_ADD(วันเวลา, INTERVAL ช่วงเวลา (DAY, MONTH..etc)) คืนค่าวันเวลารวมกับช่วงเวลาที่กำหนด • TO_DAYS(วันเวลา) คืนค่าจำนวนวัน ตั้งแต่วันที่ 1 มกราคม ปี คศ 0 • FROM_DAYS(จำนวนวัน) คืนค่าวันเวลา จาก จำนวนวันที่ใส่ไป • SELECT TO_DAYS(NOW()), FROM_DAYS(730666);

DATE_FORMAT • DATE_FORMAT( date, format) • Date คือ วันเวลา • Format คือ ส่วนที่จะให้ DATE_FORMAT คืนค่า เช่น • %a ตัวย่อของวัน Sun – Sat • %H ชั่วโมง 00-23 • %h ชั่วโมง 01-12 • %Y ปี 4 หลัก • %y ปี 2 หลัก • %p AM หรือ PM • ตัวอย่าง SELECT DATE_FORMAT(NOW(), ‘%a %h %p); --- > Wed 10 AM

หาอายุคนจากคำสั่ง TO_DAYS และ FROM_DAYS • กำหนดตาราง Employee (ลูกจ้าง) ซึ่งมี attribute คือ • ID รหัสลูกจ้าง • Name ชื่อลูกจ้าง • BirthDateวันเกิดของลูกจ้าง • จงเขียนคำสั่ง SQL เพื่อแสดงชื่อและอายุของลูกจ้าง • SELECT Name, Date_format( FROM_DAYS( TO_DAYS(NOW()) - TO_DAYS(Birthdate) ), ‘%Y’) + 0 FROM Employee;

Come back to SELECT String Operations • ในการทำงานกับข้อความมีสัญลักษณ์พิเศษ 2 ตัวที่ช่วยในการค้นหาข้อมูลประเภท String • %แทนข้อความย่อยอะไรก็ได้ (substring) • _ แทนตัวอักษรอะไรก็ได้ 1 ตัว ตัวอย่าง • Bang% คือ คำที่ขึ้นต้นด้วย Bang เช่น Bangkok, BangBang • %ko% คือ คำที่มี koอยู่ข้างใน เช่น Bangkok, kingkong • _ _ _ คือ คำที่มี 3 ตัวอักษร เช่น kok, abc, xxx • _ _ _ % คือ คำที่มีอย่างน้อย 3 ตัวอักษร เช่น kok, bangk

Example : String Operations กำหนดตาราง • Student ( ID, Firstname, Lastname) จงเขียนคำสั่ง SQL เพื่อแสดง • ชื่อของนักเรียนที่ขึ้นต้นด้วย Wat SELECTFirstnameFROM Student WHEREFirstnameLIKE“Wat%” • รหัสของนักเรียนที่นามสกุลลงท้ายด้วย Wat • ชื่อและนามสกุลของนักเรียนที่มีชื่อยาว 5 ตัวอักษร • รหัสของนักเรียนที่มีชื่อขึ้นต้นด้วย Watและในนามสกุลที่คำว่าwat

Ordering the Display • เราสามารถแสดงข้อมูลที่ได้จากการ Query ให้เรียงลำดับตามฟิลด์ต่างๆ ที่ต้องการได้ด้วยคำสั่ง ORDER BY <ชื่อฟิลด์> [ DESC | ASC ] • DESC มาจาก descending order คือเรียงจากมากไปน้อย • ASC มาจาก ascending order คือเรียงจากน้อยไปมาก (default) ตัวอย่างการใช้งาน SELECT * FROM Student ORDER BY ID

Ordering the Display เราสามารถเรียงลำดับการแสดงผลได้มากกว่า 1 ฟิลด์ เช่นจากตาราง employee (ID, Firstname, Lastname, Salary) ให้แสดงผลข้อมูลทั้งหมดโดยการเรียงชื่อจากน้อยไปมาก แล้ว เรียงเงินเดือนจากมากไปน้อย

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

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

Aggregate Function • Aggregate Function คือ การนำค่าของกลุ่มก้อนมาเป็น input เพื่อทำ operation อย่างหนึ่งและคืนมาเป็น output ค่าเดียว • SQL โดยพื้นฐานจะมี Aggregate Function ให้ใช้ได้ 5 functions คือ • AVG (average)  ค่าเฉลี่ย • MIN (minimum)  ค่าที่น้อยที่สุด • MAX (maximum)  ค่าที่มากที่สุด • SUM (summation)  ผลบวก • COUNT  นับจำนวน input

Example : Aggregate Function กำหนดตาราง employee (ID, Firstname, Lastname, Salary) • คำสั่ง SQL เพื่อหาจำนวนคนที่ชื่อ Joe SELECT COUNT(*) FROM employee WHERE FirstnameLIKE ‘Joe’ • คำสั่ง SQL เพื่อหาเงินเดือนเฉลี่ยของพนักงานในบริษัทนี้ SELECT AVG(salary) FROM employee; • คำสั่ง SQL หาชื่อของคนที่มีเงินเดือนเยอะที่สุด (ไม่สามารถหาได้ตรงๆ จาก aggregate function ต้องใช้ทริคเล็กๆน้อยๆ จากการใช้คำสั่ง ORDER BY และ LIMIT)

GROUP BY • เป็นการรวมกลุ่มก้อนเพื่อให้สามารถใช้ aggregate function ลงไปในแต่ละกลุ่มได้ • ตัวอย่าง หาผลรวมของเงินฝากในแต่ละสาขาของธนาคาร Bangsue 40000 Bangkhen 65000

GROUP BY Bangsue 40000 Bangkhen 65000

HAVING • เป็นการเพิ่มเงื่อนไขโดยการใช้ Aggregate function กับคำสั่ง GROUP BY • ตัวอย่าง : จงหาชื่อสาขาธนาคารที่มีเงินฝากรวมมากกว่า 60000 บาท SELECTBranchNameFROM Account GROUP BY BranchName HAVING SUM(Balance) > 60000; Bangsue 40000 Bangkhen 65000

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

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

Introduction to MySQL Functions: Essential Date and String Operations