›
›

Giáo trình Lập trình PHP căn bản (Phần 2) - Nghề: Thiết kế trang web - Trình độ: Cao đẳng - Trường Cao đẳng nghề Đà Lạt

pdf 115 trang Gia Huy 17/05/2022 6770
Download
Bạn đang xem 20 trang mẫu của tài liệu "Giáo trình Lập trình PHP căn bản (Phần 2) - Nghề: Thiết kế trang web - Trình độ: Cao đẳng - Trường Cao đẳng nghề Đà Lạt", để tải tài liệu gốc về máy bạn click vào nút DOWNLOAD ở trên

Tài liệu đính kèm:

  • pdfgiao_trinh_lap_trinh_php_can_ban_phan_2_nghe_thiet_ke_trang.pdf

Nội dung text: Giáo trình Lập trình PHP căn bản (Phần 2) - Nghề: Thiết kế trang web - Trình độ: Cao đẳng - Trường Cao đẳng nghề Đà Lạt

  1. BÀI 6 . HÀM VÀ TẬP TIN TRONG PHP Bài học này chúng ta sẽ làm quen cách khai báo hàm, chèn tập tin và tập tin dùng chung:  Cách khai báo hàm.  Xây dựng tập tin định dạng nội dung  Tập tin dùng chung 1. KHAI BÁO HÀM TRONG PHP Hàm do ngƣời sử dụng định nghĩa cho phép bạn xử lý những tác vụ thƣờng lặp đi lặp lại trong ứng dụng. Để khai bao hàm, bạn sử dụng từ khoá function với cú pháp tƣơng tự nhƣ sau: function functioname($parameter) { return value; } Trong trƣờng hợp hàm không có giá trị trả về thì hàm đƣợc xem nhƣ thủ tục. Ngoài ra, bạn có thể khai báo tham số tuỳ chọn bằng cách gán giá trị mặc định cho tham số. Ví dụ chúng ta khai báo: function functioname($parameter1, $parameter2=10 ) { return value; } Đối với trƣờng hợp này thì tham số $parameter1 là tham số bắt buộc và tham số $parameter2 là tham số tuỳ chọn, khi gọi hàm nếu không cung cấp tham số cho $parameter2 thì tham số này có giá trị là 10. Ví dụ, bạn khai báo trang function.php có hàm getResult nhận hai số và phép toán sau đó tuỳ thuộc vào phép toán hàm trả về kết quả. Nếu ngƣời sử dụng không cung vấp phép toán thì mặc định là phép toán +. ::Welcome to PHP Function <?php function getResult($number1, $number2,$operator="+") 67
  2. { $result=0; switch($operator) { case "+": $result=$number1+$number2; break; case "-": $result=$number1-$number2; break; case "*": $result=$number1*$number2; break; case "/": if($number2!=0) $result=$number1/$number2; else $result=0; break; case "%": if($number2!=0) $result=$number1%$number2; else $result=0; break; } return $result; } echo "result of default operator: ".getResult(10,20); echo " "; echo "result of * operator: ".getResult(10,20,"*"); ?> Nếu muốn định nghĩa function không có giá trị trả về, bạn có thể khai báo trong trang void.php nhƣ ví dụ sau: function calloperator() { 68
  3. echo "result of default operator: ".getResult(10,20); echo " "; echo "result of * operator: ".getResult(10,20,"*"); } calloperator(); ?> Trong trƣờng hợp truyền tham số nhƣ tham biến, bạn sử dụng ký hiệu & trƣớc tham số, chẳng hạn chúng ta khai báo hàm có tham biến có tên average nhƣ trong trang reference.php nhƣ sau: ::Welcome to PHP Function "; echo "result of Average is : ".$bq; echo " "; function getAmounts($quantity, $price,$average) { $result=0; $result=$quantity*$price; $average=$result*6/12; return $result; } 69
  4. $bq=0; echo "result is : ".getAmounts(10,20,$bq); echo " "; echo "result of Average is : ".$bq; ?> Trong trƣờng hợp trên thì hàm getAmount có tham số $average là tham biến còn hàm getAmounts có tham số $average là tham trị, và kết quả trả về của biến $bq khi gọi hàm getAmount là 100 trong khi đó giá trị của biến này trong hàm getAmounts là 0. 2. XÂY DỰNG TẬP TIN ĐỊNH DẠNG NỘI DUNG Khi trình bày nội dung trên trang HTML hay trang PHP, để thống nhất định dạng chuỗi trong thẻ body hay thẻ div chẳng hạn bạn cần khai báo thẻ style trong thẻ . A { COLOR: #003063; TEXT-DECORATION: none } A:hover { COLOR: #003063; TEXT-DECORATION: underline } A:link { FONT-WEIGHT: bold; COLOR: red; TEXT-DECORATION: none } A:visited { FONT-WEIGHT: bold; COLOR: black; TEXT-DECORATION: none } .title { 70
  5. FONT-WEIGHT: normal; FONT-SIZE: 22px } .text{ FONT: 11px Arial, Helvetica, sans-serif } Trong đó, A tƣơng ứng với liên kết (chuỗi trong thẻ ) có định dạng ứng với trƣờng hợp liên kết, di chuyển con chuột, chọn liên kết. A { COLOR: #003063; TEXT-DECORATION: none } A:hover { COLOR: #003063; TEXT-DECORATION: underline } A:link { FONT-WEIGHT: bold; COLOR: red; TEXT-DECORATION: none } A:visited { FONT-WEIGHT: bold; COLOR: black; TEXT-DECORATION: none } Chẳng hạn, chúng ta khai báo trang PHP với nội dung đƣợc áp dụng với kiểu định dạng khai báo trong thẻ style nhƣ vú dụ 6-1. Ví dụ 6-1: Khai báo thẻ style Style trong PHP 71
  6. A { COLOR: #003063; TEXT-DECORATION: none } A:hover { COLOR: #003063; TEXT-DECORATION: underline } A:link { FONT-WEIGHT: bold; COLOR: red; TEXT-DECORATION: none } A:visited { FONT-WEIGHT: bold; COLOR: black; TEXT-DECORATION: none } .title { FONT-WEIGHT: normal; FONT-SIZE: 22px; COLOR: #003063; } .text{ FONT: 11px Arial, Helvetica, sans-serif } Style Tag 72
  7. Quản Trị SQL Server 2000 Tìm hiểu cách cài đặt, cấu hình, quản trị, backup & restore, import & export, thiết kế, lập trình, tự động hoá tác vụ quản trị, bản sao dữ liệu, bảo mật và chống thâm nhập dữ liệu bằng. SQL Injection . Welcome to Khi triệu gọi trang style.PHP trên trình duyệt, nội dung của trang web đƣợc định dạng theo thẻ style nhƣ hình 6-1. 73
  8. Hình 6-1: Áp dụng thẻ style Tƣơng tự nhƣ vậy khi bạn muốn thống nhất nội dung trong những thẻ khác của một trang web thì khai báo một định dạng trong thẻ style. Tuy nhiên, khi đặt tên trùng với thẻ HTML, mọi thẻ đó trong trang sẽ cùng chung một định dạng. Chẳng hạn, bạn khai báo định dạng cho thẻ td nhƣ sau: TD { FONT: 10px Arial, Helvetica, sans-serif } Mọi nội dung trình bày trong thẻ td sẽ có định dạng nhƣ trên. Nếu bạn muốn có định dạng khác thì khai báo thuộc tính class cho thẻ td đó, ví dụ sử dụng định dạng khác cho thẻ td: ABC Thay vì chuỗi ABC sẽ có định dạng là FONT: 10px Arial, Helvetica, sans-serif thì chúng sẽ có định dạng của FONT: 11px Arial, Helvetica, sans-serif. Chú ý rằng, trong mỗi trang web bạn phải khai báo thẻ style và định nghĩa thống nhất cho các thẻ. Khi có sự thay đổi bạn phải thay đổi trong mọi trang web. Để sử dụng chung cho mọi trang web trong ứng dụng, bạn cần xây dựng một tập tin style, tập tin đƣợc biết đến với tên gọi custom style sheet (css). Bất kỳ trang web nào trong ứng dụng, muốn áp dụng kiểu định dạng trong tập tin css thì khai báo liên kết tập tin css bằng thẻ link. Ví dụ, chúng ta khai báo tập tin style.css bao gồm các định dạng nhƣ ví dụ 6-2. Ví dụ 6-2: Khai báo tập tin css A { COLOR: #003063; TEXT-DECORATION: none } A:hover { COLOR: #003063; TEXT-DECORATION: underline } A:link { FONT-WEIGHT: bold; COLOR: red; TEXT-DECORATION: none } A:visited { 74
  9. FONT-WEIGHT: bold; COLOR: black; TEXT-DECORATION: none } .title { FONT-WEIGHT: bold; FONT-SIZE: 14px; COLOR: #003063; } .text{ FONT: 11px Arial, Helvetica, sans-serif } Sau đó trong trang PHP, bạn khai báo liên kết tập tin này bằng thẻ link, nếu muốn áp dụng định dạng này trong mỗi thẻ HTML bạn sử dụng thuộc tính class nhƣ khai báo định dạng của thẻ style ngay trong trang đó nhƣ ví dụ 6-3. Ví dụ 6-3: Khai báo sử dụng tập tin css Welcome to Link Style Sheet File Style File Quản Trị SQL Server 2000 75
  10. Tìm hiểu cách cài đặt, cấu hình, quản trị, backup & restore, import & export, thiết kế, lập trình, tự động hoá tác vụ quản trị, bản sao dữ liệu, bảo mật và chống thâm nhập dữ liệu bằng. SQL Injection . Welcome to Triệu gọi trang includestyle.php trên trình duyệt nhƣ hình 6-3, màu và kích thƣớc font cùng với kiểu chữa của nội dung không thay đổi so với style.php, bởi vì phần thẻ style đƣợc tách ra thành tập tin style.css, sau đó dùng thẻ link để liên kết tập tin css này vào trang PHP trở lại. Hình 6-3: Liên kết tập tin css Chú ý rằng, nếu khai báo thuộc tính class trong thẻ thì những nội dung trong thẻ sẽ có định dạng theo định dạng khai báo trong thuộc tính class. 76
  11. Tƣơng tự, nếu khai báo thuộc tính class trong thẻ thì nội dung trong thẻ sẽ có định dạng giống nhƣ định dạng khai báo trong thông tin class. 3. THỐNG NHẤT KÍCH THƢỚC CỦA MỌI TRANG PHP Khi xây dựng ứng dụng web chuyên nghiệp, điều đầu tiên bạn nên quan tâm là sự thống nhất về kích thƣớc của các phần trên trang web. Điều này có nghĩa là khi ngƣời sử dụng thay đổi trang web khi duyệt, phần top, left, right, bottom có kích thƣớc nhƣ nhau. Để làm điều này, bạn chia trang web ra thành 5 phần: top, left, right, body và bottom. Phần top thƣờng trình bày các thuộc tính nhƣ quản cáo (baner), logo (biểu tƣợng của công ty), menu (thực đơn của ứng dụng) và một số thông tin khác. Phần left là thông tin về các menu phụ hay còn gọi là menu của menu chính, bên cạnh menu con này trang web thƣờng có các liên kết về liên hệ, quảng cáo, mailing list (đăng ký email), gởi đến bạn bè (send to friend), Đối với phần right, thƣờng là phần giới thiệu về các thông đặc biệt và quảng cáo, chẳng hạn đối với ứng dụng bán sách, phần right thƣờng là danh sách các nhóm sách bán chạy, sắp phát hành, Phần bottom thƣờng thông tin liên lạc của công ty, chủ nhân của web site và bản quyền. Ngoài ra, phần bottom đôi khi là danh sách các menu con khác. Tóm lại, tuỳ thuộc vào ý tƣởng thiết kế mỗi phần nhƣ trên bao gồm các thuộc tính mà nhà thiết kế cần trình bày sao cho phù hợp. Tuy nhiên, phần body là phần trình bày nội dung chính của mỗi trang web. Ngoài ra, tuỳ vào từng trƣờng hợp cụ thể, trang web có thể không có phần left và right. Nhƣ vậy, chúng ta sẽ chia trang web ra thành 5 phần, phần body chính là phần chính của trang web đó, còn 4 phần còn lại đƣợc chèn vào khi có nhu cầu. Chẳng hạn, có những trang web do thông tin trình bày trong phần body nhiều, nên cần không gia lớn hơn, bạn có thể không cần sử dụng hai phần left và right. Để làm điều này, trƣớc tiên chúng ta thiết kế trang sample.php có 5 phần nhƣ hình 6-3. 77
  12. Hình 6-3: Trang sample.php Lưu ý: • Tạo một table gồm 3 hàng 3 cột và khai báo border=1 để đễ canh lề sau đó bạn có thể khai báo lại thuộc tính này bằng 0. • Phần top và bottom là một hàng và merge 3 cột thành 1. • Bên trong mỗi phần có thể có một hay nhiều thẻ table khác. • Có thể không có phần left và right nhưng bắt buộc phần top và bottom phải có. • Bạn có thể sử dụng chiều rộng của table theo kích thước tương đối (%) hay số chỉ định, đối với màn hình 600*800 thì chiều rộng thường sử dụng là 780, khi người sử dụng chọn độ phân giải của màn hình lớn hơn thì kích thước của table này không thay đổi, trong khi đó nội dung sẽ phủ đầy màn hình khi bạn khai báo kích thước theo 100%. Để có giao diện nhƣ trang sample.php nhƣ trên, bạn có thể khai báo nhƣ ví dụ 6- 3. Ví dụ 6-3: Nội dung trang sample.PHP 78
  13. Welcome to Including File TOP LEFT BODY RIGHT BOTTOM Trong trƣờng hợp bạn muốn có đƣờng phân cách giữa mỗi phần bằng image, bạn có thể khai báo lại trang sample.php có 5 hàng và 5 cột nhƣ template.php nhƣ hình 6- 4. 79
  14. Hình 2-4: Phân cách có viềng Để trình bày trang tempale.PHP nhƣ hình 6-4, bạn khai báo nội dung trang này nhƣ ví dụ 6-4. Ví dụ 6-4: Khai báo template.php Welcome to Including File TOP 80
  15. LEFT BODY RIGHT BOTTOM Sau đó tách trang template.php này thành 5 trang khác nhau đƣợc đặt tên tƣơng ứng là top.htm, left.htm, right.htm và bottom.htm, trong đó phần body tƣơng ứng với trang templates.php. Để khai báo chèn tập tin trong trang PHP, bạn sử dụng cú pháp nhƣ sau: Hay <?php 81
  16. require(”filename”); ?> Trong đó trang templates.PHP khai báo chèn top.htm, left.htm, right.htm và bottom.htm nhƣ ví dụ 6-5. Ví dụ 6-5: Khai báo chèn tập tin trong templates.php Welcome to HUUKHANG.COM BODY 82
  17. Khi triệu gọi trang templates.php, nội dung của 4 tang left.htm, right.htm, top.htm, bottom.htm chèn vào trang templates.php nhƣ hình 6-5. Hình 6-5: Trang templates.php sau khi chèn Trong đó, nội dung của trang top.htm định nghĩa tƣơng tự nhƣ ví dụ 6-5-1. 83
  18. Ví dụ 6-5-1: Nội dung trang top.htm LOGO BANNER Welcome Home | Search | Contact | Help Nội dung của tập tin left.htm đƣợc định nghĩa tƣơng tự nhƣ ví dụ 6-5-2. Ví dụ 6-5-2: Nội dung trang left.htm LEFT 84
  19. Nếu có sử dụng trang right.htm thì nội dung của tập tin này đƣợc định nghĩa tƣơng tự nhƣ ví dụ 6-5-3. Ví dụ 6-5-3: Nội dung trang right.htm FREE ADV Tƣơng tự nhƣ vậy, trang bottom.htm có nội dung nhƣ ví dụ 6-5-4. Ví dụ 6-5-4: Nội dung trang bottom.htm HUUKHANG.COM Copyright ©2000-2005. All Rights Reserved. Chú ý rằng, trong mỗi trang khai báo chèn không có các thẻ đóng và mở html, body bởi khi chèn thì nội dung của tập tin đƣợc chèn sẽ đƣợc chèn vào tập tin bị chèn và trong tập tin bị chèn đã có hai thẻ này. Kịch bản trình chủ PHP hỗ trợ các tập tin đƣợc chèn với các tên mở rộng nhƣ htm, PHP, inc, lib, html. Do thực chất của việc khai báo chèn là chèn đoạn mã trong tập tin chèn vào tập tin bọ chèn, trong trƣờng hợp này trang chèn htm hay PHP đều giống nhau đó là lý do tại sao các trang chèn ở trên đều có tên mở rộng là htm. Tuy nhiên, khi bạn gọi trang chèn này một mình ví dụ tom.htm, nếu bên trong có mã PHP thì mã đó không đƣợc thông dịch. Nếu những trang chèn này có nhu cầu gọi một mình thì bạn có thể chuyển chúng thành trang PHP thay vì htm nhƣ đã trình bày. 85
  20. Sau khi có đƣợc trang templates.php, bạn có thể sử dụng trang này là mẫu cho các trang khác bằng cách save as thành các trang PHP khác khi lập trình. Khi khai báo chèn tập tin, bạn có thể sử dụng đƣờng dẫn tƣơng đối hoặc tuyệt đối của tập tin chèn so với ập tin bị chèn. 4. TẬP TIN DÙNG CHUNG Ngoài cách chèn ở trên, nếu bạn có những hàm sử dụng chung cho các trang PHP khác thì bạn khai báo thành một trang PHP khác sau đó dùng cú pháp chèn tập tin để chèn chúng vào khi có nhu cầu. Ví dụ trong trƣờng hợp này chúng ta muốn sử dụng chung hàm có tên getPaging nhận 5 tham số $totalRows (tổng số mẩu tin), $curPg (số trang hiện hành), $pg (số trang trình bày), $re (số mẩu tin trên 1 trang), $file (trang php cần gọi) trong tập tin paging.php. $mxR) { $start=1; $end=1; $paging1 =""; for($i=1;$i ((int)(($curPg-1)/$mxP))* $mxP) && ($i<=((int)(($curPg1)/$mxP+1))* $mxP)) { if($start==1) $start=$i; if($i==$curPg) $paging1 .= $i."  "; else { 86
  21. $paging1 .= "   "; } $end=$i; } } $paging.= "Trang :  " ; if($curPg>$mxP) { $paging .="   "; } $paging.=$paging1; if(((($curPg-1)/$mxP+1)*$mxP) Sau đó khai báo trang result.php, chèn tập tin paging.php và gọi hàm getPaging nhƣu sau: Welcome to HUUKHANG.COM 87
  22. Kết quả trả về nhƣ hình 6-6 sau Hình 6-6: Hàm dùng chung 5. KẾT LUẬN Trong bài này, chúng ta tìm hiểu cách khai báo hàm, trang php và khai báo chèn tập tin. 88
  23. BÀI 7. CHUỖI, MẢNG VÀ KIỂU DATETIME Bài học này chúng ta sẽ làm quen cách xử lý chuỗi, mảng, kiểu DataTime trong PHP:  Xử lý chuỗi  Làm việc với mảng dữ liệu  Kiểu DateTime 1. XỬ LÝ CHUỖI PHP là kịch bản đƣợc xem là tốt nhất cho xử lý chuỗi, bằng cách sử dụng các hàm xử lý chuỗi, bạn có thể thực hiện các ý định của mình khi tƣơng tác cơ sở dữ liệu, tập tin hay dữ liệu khác. 1.1. Định dạng chuỗi Khi xuất kết quả ra trình duyệt, bạn có thể sử dụng các định dạng chuỗi tƣơng tự nhƣ ngôn ngữ lập trình C. Chẳng hạn, chúng ta in giá trị của biến $i trong trang dinhdang.php nhƣ ví dụ 7-1. String Functions Dinh dang "; printf("Total amount of order: %.1f", $i); echo " "; printf("Total amount of order: %.2f", $i); echo " "; printf("Total amount of order: i=%.2f, j=%.0f", $i,$j); ?> Kết quả xuất hiện nhƣ hình 7-1 Hình 7-1: Định dạng chuỗi in 89
  24. Trong đó các định dạng đƣợc chia ra nhiêu loại tuỳ thụôc vào các ký tự bạn sử dụng. % - Kông yêu cầu tham số. b – Trình bày dạng số integer và hiện thực dƣới dạng binary. c - Trình bày dạng số integer và hiện thực dƣới dạng mã ASCII. d - Trình bày dạng số integer và hiện thực dƣới dạng decimal. e - Trình bày dạng số logic và hiện thực dƣới dạng 1.2e+2. u - Trình bày dạng số integer và hiện thực dƣới dạng decimal không dấu. f - Trình bày dạng số float và hiện thực dƣới dạng số chấm động. o - Trình bày dạng số integer và hiện thực dƣới dạng hệ số 10. s - Trình bày dạng chuỗi. x - Trình bày dạng số integer và hiện thực dƣới dạng hệ số 16 với ký tự thƣờng. X - Trình bày dạng số integer và hiện thực dƣới dạng hệ số 16 với ký tự hoa. 1.2. Hàm chuyển đổi chuỗi Để chuyển đổi chuỗi ra ký tự hoa thƣờng bạn sử dụng một trong 4 hàm nhƣ ví dụ 7-2 trong trang chuyendoi.php: String Functions Chuyen doi "; echo strtoupper($str); echo " "; echo strtolower($str); echo " "; echo ucfirst($str); echo " "; echo ucwords($str); echo " "; ?> Kết quả trình bày nhƣ hình 7-2. 90
  25. Hình 7-2: Chuyển đổi chuỗi 1.3. Hàm tách hay kết hợp chuỗi Để tách hay kết hợp chuỗi, bạn sử dụng một trong các hàm thƣờng sử dụng nhƣ strtok, explode hay substr. Chẳng hạn, chúng ta sử dụng 4 hàm này trong ví dụ 7-4 trong trang tachchuoi.php. String Functions Tach hop chuoi "; $tok = strtok($string, " "); while ($tok) { echo "Word= $tok "; $tok = strtok(" \n\t"); } echo $str." "; echo substr($str,24)." "; $a[]=array(); $a=explode(" ",$str); while($i=each($a)) { echo $i["value"]." "; } ?> Kết quả trình bày nhƣ hình 7-4. 91
  26. Hình 7-4: Hàm tách chuỗi Trong trƣờng hợp kết hợp giá trị của các phần tử của mảng thành chuỗi, bạn sử dụng hàm implode nhƣ ví dụ 7-5 trong trang kethop.php: String Functions Ket hop chuoi "; } $str=implode(" ",$a); echo $str; ?> 92
  27. Kết quả trình bày nhƣ hình 7-5. Hình 7-5: Hàm kết hợp chuỗi 1.4. Tìm kiếm và thay thế chuỗi Để thay thế chuỗi, bạn sử dụng hàm str_replace, chẳng hạn trong trƣờng hợp hợp bạn lấy giá trị từ thẻ nhập liệu, sau đó tìm kiếm nếu phát hiện dấu „ thì thay thế thành hai dấu nháy nhƣ trang replace.php. String Functions That the chuoi "; ?> 93
  28. fullname: "> Khi triệu gọi trang replace.php trên trình duyệt, bạn sẽ có kết quả nhƣ sau: Hình 7-6: Hàm thay thế chuỗi Ngoài ra, bạn có thể sử dụng các hàm nhƣ strpos (trả về vị trí chuỗi con trong chuỗi mẹ), 2. LÀM VIỆC VỚI MẢNG DỮ LIỆU Nhƣ trong bài kiểu dữ liệu chúng ta đã làm quen với kiểu dữ liệu mảng, trong phần này chúng ta tiếp tục tìm hiểu các khai báo, truy cập và tƣơng tác với tập tin từ mảng một chiều, hai chiều. 2.1. Mảng một chiều Để khai báo mảng một chiều, bạn có thể sử dụng cú pháp nhƣ sau: $arr=array(); $arrs=array(5); Truy cập vào phần tử mảng, bạn có thể sử dụng chỉ mục của phần tử nhƣ sau: $arr[0]=1; $arrs[1]=12; Lấy giá trị của phần tử mảng, bạn cũng thực hiện tƣơng tự nhƣ trƣờng hợp truy cập mảng phần tử. echo $arr[0]; $x=$arrs[5]; 94
  29. Chẳng hạn, chúng ta khai báo mảng động và mảng có số phần tử cho trƣớc, sau đó truy cập và lấy giá trị của chúng nhƣ ví dụ trong trang arrayone.php sau: Array Mang mot chieu "; echo "Gia tri lon nhat ".max($arr)." "; echo "Gia tri nho nhat ".min($arr)." " ; echo "Gia tri trung binh ".array_sum($arr) / sizeof($arr)." " ; echo " "; for($i=0;$i "; echo "Gia tri lon nhat ".max($arrs)." "; echo "Gia tri nho nhat ".min($arrs)." " ; echo "Gia tri trung binh ".array_sum($arrs) / sizeof($arrs)." " ; ?> Kết quả trình bày nhƣ hình 7-7 khi triệu gọi trang arrayone.php. 95
  30. Hình 7-7: Khai báo và sử dụng mảng một chiều 2.2. Mảng hai chiều Tƣơng tự nhƣ mảng một chiều, trong trƣờng hợp làm việc mảng hai chiều bạn khai báo tƣơng tự nhƣ trang arraytwo.php. Array Mang hai chieu <?php $i=0;$j=0; $arr=array(); $arr[0][0]=10; $arr[0][1]=11; $arr[0][2]=12; $arr[1][0]=13; $arr[1][1]=14; $arr[1][2]=15; $arr[2][0]=16; 96
  31. $arr[2][1]=17; $arr[2][2]=18; for($i=0;$i "; } echo " "; $arrs=array(array(1,2,3,4,5,6,7), array(11,12,13,14,15,16,17)); for($i=0;$i "; } echo " "; ?> Khi triệu gọi trang này trên trình duyệt, kết quả trình bày nhƣ hình 7-8. 97
  32. Hình 7-8: Mảng hai chiều 3. KIỂU DATETIME Để làm việc với kiểu dữ liệu Date và Time, bạn sử dụng hàm của PHP có sẵn. Chẳng hạn, muốn trình bày chuỗi ngày tháng, bạn dùng hàm date với các tham số nhƣ ví dụ sau: Date and Time Ngay hien tai "; echo date("M/Y"); echo " "; echo "Days of ".date("M")." is ".date("t"); echo " "; ?> 98
  33. Kết quả trả về nhƣ hình 7-9. Hình 7-9: Sử dụng hàm Date Lƣu ý rằng, than số trong hàm date đƣợc trình bày trong bảng sau Code Diễn giải a Buổi sáng/Chiều bằng hai ký tự thƣờng am/pm. A Buổi sáng/Chiều bằng hai ký tự hoa AM/PM. B Định dạng thời gian Swatch Internet, bạn có thể tham khảo d Day (01-31) trong tháng với hai số, nếu ngày 1-9 sẽ có kèm số 0. D Day (Mon-Sun) trong tuần với 3 ký tự. F Tháng (January-December) trong năm với tên tháng đầy đủ dạng text. g Hour (1-12) trong ngày 1 hoặc 2 số (không kèm 0 nếu giờ từ 1-9). G Hour (0-23) trong ngày 1 hoặc 2 số (không kèm 0 nếu giờ từ 0-9). h Hour (01-12) trong ngày 2 số (kèm 0 nếu giờ từ 01-09). H Hour (00- 23) trong ngày 2 số (kèm 00 nếu giờ từ 00-09). i Minutes (01-59) đã trôi qua (kèm 00 nếu phút từ 00-59). j Day (1-31) 1 hoặc 2 số (không kèm 0 nếu ngày từ 1-9). l Day (Monday-Sunday) trong tuần dạng text. L Năm nhuần trả về 1, ngƣợc lại hàm trả về 0. m Month (01-12) trong năm 2 số (kèm 00 nếu tháng từ 01-09). M Month (Jan-Dec) trong năm 3 ký tự. 99
  34. n Month (1-12) 1 hoặc 2 số (không kèm 0 nếu tháng từ 1-9). s Seconds (01-59) đã trôi qua (kèm 00 nếu giây từ 00-59). S Thêm hai ký tự st, nd, rd hay th theo sau ngày dạng hai ký tự số (ví dụ nhƣ 12th). t Trả về tổng số ngày trong tháng (từ 28 -31). T Ký tự Timezone của server với 3 ký tự, chẳng hạn nhƣ EST. U Tổng số Seconds tứ 1 January 1970 tới hôm nay ứng với UNIX Time Stamp. w Day (0-6) của tuần, 0 ứng với Sunday và 6 ứng với Saturday. y Năm định dạng 2 con số (03). Y Năm định dạng 4 con số (2003). z Ngày trong năm một hoặc 2 con số (0-365). X Timezone hiện tại tính bằng giây từ – 43200 đến 43200. 4. KẾT LUẬT Trong bài này, chúng ta tập trung tìm hiểu xử lý chuỗi, mảng và hàm ngày tháng. Trong bài tiếp, chúng ta tiếp tục tìm hiểu cơ sở dữ liệu mySQL. 100
  35. Bài 8. My SQL Bài học này chúng ta sẽ làm quen cách thao tác trên cơ sở dữ liệu MySQL:  Giới thiệu cơ sở dữ liệu MySQL  Cài đặt MySQL  Cấu hình  Kiểu dữ liệu  Khai báo các phát biểu 1. GIỚI THIỆU CƠ SỞ DỮ LIỆU MYSQL MySQL là cơ sở dữ liệu đƣợc sử dụng cho các ứng dụng Web có quy mô vừa và nhỏ. Tuy không phải là một cơ sở dữ liệu lớn nhƣng chúng cũng có trình giao diện trên Windows hay Linux, cho phép ngƣời dùng có thể thao tác các hành động liên quan đến cơ sở dữ liệu. Cũng giống nhƣ các cơ sở dữ liệu, khi làm việc với cơ sở dữ liệu MySQL, bạn đăng ký kết nối, tạo cơ sở dữ liệu, quản lý ngƣời dùng, phần quyền sử dụng, thiết kế đối tƣợng Table của cơ sở dữ liệu và xử lý dữ liệu. Tuy nhiên, trong bất kỳ ứng dụng cơ sở dữ liệu nào cũng vậy, nếu bản thân chúng có hỗ trợ một trình giao diện đồ hoạ, bạn có thể sử dụng chúng tiện lợi hơn các sử dụng Command line. Bởi vì, cho dù bạn điều khiển MySQL dƣới bất kỳ hình thức nào, mục đích cũng quản lý và thao tác cơ sở dữ liệu. 2. CÀI ĐẶT MYSQL Để cài đặt MySQL trên nền Windows bạn theo các bƣớc sau:  Trƣớc tiên bạn chép tập tin mysql-4.0.0a-alpha-win.zip vào đĩa cứng hoặc chọn chúng từ đĩaq CD và giải nén tập tin  Chạy tập tin Setup.exe, chọn đĩa C hay D  Sau khi cài đặt thành công, bạn kiểm tra trong Windows Services xuất hiện dịch vụ mySQL hay không?. Để sử dụng đƣợc MySQL thì trạng thái của dịch vụ này phải ở chế độ Started. Lƣu ý rằng, trong trƣờng hợp MySQL không thể chạy đƣợc, do dịch vụ của MySQL chƣa Started nhƣ , để có thể chạy đƣợc MySQL thì bạn cần một số thay đổi trong tập tin my.ini trong thƣ mục WINNT #This File was made using the WinMySQLAdmin 1.3 #Tool #9/11/2003 10:50:13 AM #Uncomment or Add only the keys that you know how works. Trang 101
  36. #Read the MySQL Manual for instructions [mysqld-nt] basedir=C:/mysql #bind-address=127.0.0.1 datadir=C:/mysql/data #language=C:/mysql/share/your language directory #slow query log#= #tmpdir#= #port=3306 #set-variable=key_buffer=16M [WinMySQLadmin] Server=C:/mysql/bin/mysqld-nt.exe user=root password= QueryInterval=10 3. TẠO CƠ SỞ DỮ LIỆU VÀ NGƢỜI DÙNG Trong trƣờng hợp bạn sử dụng giao diện đồ hoạ thì dùng ích quản trị cơ sở dữ liệu MySQL, bạn có thể chạy tập tin mysqlfront.exe trong thƣ mục MySQL Control, bằng cách chạy tập tin cửa sổ xuất hiện nhƣ hình 8-1. Nếu lần đầu tiên tạo kết nối cơ sở dữ liệu, bạn cần phải tạo một Connection, cung cấp tên Server hay IP của máy chứa MySQL. Tuy nhiên, trong trƣờng hợp máy chứa cơ sở dữ liệu MySQL là máy đang sử dụng, bạn có thể sử dụng localhost. Ngài ra, cũng giống nhƣ các cơ sở dữ liệu khác, Username mặc định của cơ sở dữ liệu MySQl là root và Password là rỗng. Nếu bạn đã có cơ sở dữ liệu đang tồn tại, bạn có thể gõ tên cơ sở dữ liệu trong phần Databases ( nếu muốn mở nhiều database, bạn có thể dùng dấu ; để phân cách). Trong trƣờng hợp lần đầu tiên, bạn không cần cung cấp tên cơ sở dữ liệu, bạn có thể tạo chúng sau khi kết nối. Hình 8-1: Kết nối cơ sở dữ liệu bằng MySQLFront Tool Sau kết nối cơ sở dữ liệu thành công, trình điều khiển cơ sở dữ liệu MySQL có giao diện nhƣ hình 8-2, cộng việc đầu tiên bạn phải thực hiện là tạo cơ sở dữ liệu. Bắt đầu từ menu có tên Tools | Create Database hay chọn tên root@localhost | R-Click | Create Database, cửa sổ xuất hiện nhƣ hình 8-3. Trang 102
  37. Hình 8-2: Giao diện điều khiển cơ sở dữ liệu MySQL Cung cấp tên cơ sở dữ liệu, trong trƣờng hợp này bạn có thể nhập Test, bấm nút OK, cơ sở dữ liệu xuất hiện trong cửa sổ điều khiển. Hình 8-3: Tạo cơ sở dữ liệu có tên Test Trong cả hai trƣờng hợp tạo cơ sở dữ liệu bằng MySQL thành công nhƣ trên, bạn có thể tìm thấy tên cơ sở dữ liệu đó trong thƣ mục mysql/data nhƣ hình 8-4 sau: Hình 8-4: Thƣ mục tin cơ sở dữ liệu Test 3.1. Quản lý ngƣời dùng Làm thế nào để đăng nhậo vào cơ sở dữ liệu MySQL, bạn có thể sử dụng hai cách nhƣ trình bày ở trên. Tuy nhiên, sau khi tạo ra các username khác, bạn có thể sử dụng chúng để Trang 103
  38. đăng nhập. Để đăng nhập vào MySQL bằng Command line, bạn chỉ cần gõ >mysql – hostname –u username – p từ dấu nhắc hay đăng nhập bằng cách sử dụng trình giao diện đồ hoạ. Từ khoá - h hỉ ra rằng tên (computer name), IP, hay localhost của máy có sử dụng cơ sở dữ liệu MySQL, -u chỉ ra rằng bạn sử dụng username, username là tên username, -p đƣợc chỉ định khi username này có password. Trong trƣờng hợp password là rỗng, bạn có thể không cung cấp tham số –p. Để tạo User trong cơ sở dữ liệu MySQL, bạn có thể sử dụng hai cách trên. Nếu bạn thực hiện việc tạo một Username bằng Command line, bạn có thể gõ từ dấu nhắc nhƣ phát biểu sau: GRANT Select, Insert, Update, Delete, Index, Alter, Create, Drop, References ON *.* TO 'myis'@'%' IDENTIFIED BY '12345678' Trong phát biểu trên, vừa tạo ra User có tên myis, với hostname là cơ sở dữ liệu hiện hành, password là 1234 và đƣợc các đặt quyền Select, Insert, Update, Delete, Index, Alter, Create, Drop trên cơ sở dữ liệu hiện hành. Trong trƣờng hợp bạn tạo ra một Username không cung cấp các đặt quyền trên cơ sở dữ liệu, bạn có thể thực hiện nhƣ phát biểu tạo username: test, password: 1234 sau: GRANT usage ON *.* TO 'test'@'%' IDENTIFIED BY '1234' Nếu bạn sử dụng giao diện đồ hoạ, bạn có thể tạo username và gán quyền nhƣ trên bằng cách sử dụng menu có tên Tools | User-Manager, cửa sổ xuất hiện nhƣ hình 8-5. Hình 8-5: Tạo Username Trang 104
  39. 3.2. Cấp quyền cho ngƣời dùng Các đặt quyền Select, Insert, Update, Delete, Index, Alter, Create, Drop trên cơ sở dữ liệu, bạn có thể tham khảo chi tiết trong bảng 8-1. Bảng 8-1: Các đặt quyền trên cơ sở dữ liệu Loại áp dụng Diễn giải select tables, Cho phép user truy vấn mẩu columns tin từ Table. insert tables, Cho phép user thêm mới mẩu columns tin vào Table. update tables, Cho phép user thay đổi giá columns trị của mẩu tin tồn tại trong Table. delete tables Cho phép user mẩu tin tồn tại trong Table. index tables Cho phép user thêm mới hay xoá chỉ mục của Table. alter tables Cho phép user thay đổi cấu trúc của đối tƣợng Table hay Database tồn tại, nhƣ thêm cột vào trong Table tồn tại, thay đổi kiểu dữ liệu của cột dữ liệu, create databases Cho phép user tạo mới đối tables tƣợng Table hay Database. drop databases Cho phép user xoá đối Trang 105
  40. tables tƣợng Table hay Database. Xuất phát từ các quyền có ảnh hƣởng đến cấu trúc cơ sở dữ liệu, các đối tƣợng của cơ sở dữ liệu và dữ liệu, bạn có thể xem xét kỹ càng trƣớc khi cấp quyền cho user àm việc trên cơ sở dữ liệu. Ngoài các quyền trên, trong MySQL còn có một số quyền không gán mặc định nhƣ trong bảng 8-2, bạn có thể xem xét các đặt quyền quản trị để cấp cho ngƣời dùng. Bảng 8-2: Các đặt quyền quản trị trên cơ sở dữ liệu Loại Diễn giải reload Cho phép ngƣời quản trị nạp lại các Table, quyền, host, logs và Table. shutdown Cho phép ngƣời quản trị chấm dứt hoạt động MySQL Server. process Cho phép ngƣời quản trị xem quá trình thực hiện của trình chủ và có thể chấm dứt một số quá trình đang thực thi. file Cho phép dữ liệu ghi vào Table từ tập tin. Lƣu ý: Những username bình thƣờng không nên cấp quyền nhƣ trong bảng 8-2 cho họ, trong trƣờng hợp bạn muốn cầp tất cả các quyền trong bảng 8-1 và Bảng 8-2 cho username khi tạo ra họ, bạn Table sử dụng từ khoá All thay vì All Privileges trong phát biểu tạo user nhƣ sau: GRANT ALL ON *.* TO 'ekhang'@'%' IDENTIFIED BY '12345678' Tƣơng tự nhƣ vậy, trong trƣờng hợp bạn không cung cấp bất kỳ đặt quyền nào trên cơ sở dữ liệu hiện hành, bạn có thể khai báo phát biểu cấp uyền nhƣ sau: Trang 106
  41. GRANT usage ON *.* TO 'ekhang'@'%' IDENTIFIED BY '12345678' 3.3. Xoá quyền của user Để xoá các quyền của user từ cơ sở dữ liệu hiện hành, bạn có thể sử dụng phát biểu SQL có tên Revoke, phát biểu Revoke ngƣợc lại với phát biểu Grant. Nếu bạn xoá một số quyền của user, bạn có thể sử dụng khai báo nhƣ phát biểu sau: Revoke privileges [(columns)] ON item From username Trong trƣờng hợp xoá tất cả các quyền của user, bạn có thể sử dụng phát biểu nhƣ sau: Revoke All ON item From username Nếu user đó đƣợc cấp quyền với tuỳ chọn Grant Option, để xoá các quyền đó của user, bạn có thể khai báo nhƣ sau: Revoke Grant Option ON item From username Để tham khảo chi tiết quá trình cấp và xoá quyền của một user, bạn có thể tham khảo một số phát biểu nhƣ sau: Gán quyền Administrator cho user có tên fred trên mọi cơ sở dữ liệu trong MySQL, password của anh ta là mnb123, bạn có thể khai báo nhƣ sau: Grant all On * To fred indetifyed by „mnb123‟ With Grant Option; Nếu bạn không muốn user có tên fred trong hệ thống, bạn có thể xoá anh ta bằng cách khai báo phát biểu sau: Revoke all On * From fred; Tạo một user có tên ekhang với password là 12345678, đƣợc làm việc trên cơ sở dữ liệu Test, không cấp quyền cho user này, bạn có thể khai báo nhƣ sau: Trang 107
  42. Grant usage On Test.* To ekhang identified by „12345678‟; Tƣơng tự nhƣ vậy, trong trƣờng hợp bạn muốn cấp một số quyền cho user có tên ekhang trên cơ sở dữ liệu Test, bạn có thể khai báo nhƣ sau: Grant select, insert, delete, update, index, drop On Test.* To ekhang; Nếu bạn muốn xoá bớt một số quyền của user có tên ekhang trên cơ sở dữ liệu Test, bạn có thể khai báo nhƣ sau: Revoke update, delete, drop On Test.* From ekhang; Nhƣng trong trƣờng hợp xoá tất cả các quyền của user có tên ekhang trên cơ sở dữ liệu Test, bạn có thể khai báo: Revoke All On Test.* From ekhang; 4. KIỂU DỮ LIỆU CỦA CƠ SỞ DỮ LIỆU MYSQL Trƣớc khi thiết kế cơ sở dữ liệu trên MySQL, bạn cần phải tham khảo một số kiểu dữ liệu thƣờng dùng, chúng bao gồm các nhóm nhƣ: numeric, date and time và string. Đều cần lƣu ý trong khi thiết kế cơ sở dữ liệu, bạn cần phải xem xét kiểu dữ liệu cho môt cột trong Table sao cho phù hợp với dữ liệu của thế giới thực. Điều này có nghĩa là khi chọn dữ liệu cho cột trong Table, bạn phải xem xét đến loại dữ liệu cần lƣu trữ thuộc nhóm kiểu dữ liệu nào, chiều dài cũng nhƣ các ràng buộc khác, nhằm khai báo cho phù hợp. 4.1. Loại dữ liệu numeric Kiểu dữ liệu numeric bao gồm kiểu số nguyên trình bày trong bảng 8-3 và kiểu số chấm động, trong trƣờng hợp dữ liệu kiểu dấu chấm động bạn cần phải chỉ rõ bao nhiều số sau đấu phần lẻ nhƣ trong bảng 8-4. Bảng 8-3: Kiểu dữ liệu số nguyên Loại Range Bytes Diễn giải Trang 108
  43. tinyint -127->128 1 Số nguyên rất nhỏ. hay 0 255 smallint -32768 2 Số nguyên nhỏ. ->32767 hay 0 65535 mediumint -8388608 3 Số nguyên vừa. -> 838860 hay 0 16777215 int -231->231-1 4 Số nguyên. hay 0 232-1 bigint -263->263-1 8 Số nguyên lớn. hay 0 264-1 Bảng 8-4: Kiểu dữ liệu số chấm động Loại Range Bytes Diễn giải float phụ thuộc Số thập phân Số thập dạng Single hay Phân Double. Float(M,D) 4 Số thập phân dạng Single. ±1.175494351E-38 ±3.40282346638 Trang 109
  44. Double(M,D) 8 Số thập phân dạng Double. ±1.7976931348623157308 ±2.2250738585072014E-308 Float(M[,D]) Số chấm động lƣu dƣới dạng char. 4.2. Loại dữ liệu Datet and Time Kiểu dữ liệu Date and Time cho phép bạn nhập liệu dƣới dạng chuỗi hay dạng số nhƣ trong bảng 8-5. Bảng 8-5: Kiểu dữ liệu số nguyên Loại Range Diễn giải Date 1000-01-01 Date trình bày dƣới dạng yyyy-mm-dd. Time -838:59:59 Time trình bày dƣới 838:59:59 dạng hh:mm:ss. DateTime 1000-01-01 Date và Time trình bày dƣới 00:00:00 dạng yyyy-mm-dd hh:mm:ss. 9999-12-31 23:59:59 TimeStamp[(M)] 1970-01-01 TimeStamp trình bày dƣới 00:00:00 dạng yyyy-mm- dd hh:mm:ss. Year[(2|4)] 1970-2069 Year trình bày dƣới 1901-2155 dạng 2 số hay 4 số. Đối với kiểu dữ liệu TimeStamp, bạn có thể định dạng nhiều cách nhƣ trình bày trong bảng 86. Bảng 8-6: Trình bày đại diện của TimeStamp Trang 110
  45. Loại Hiển thị TimeStamp YYYYMMDDHHMMSS TimeStamp(14) YYYYMMDDHHMMSS TimeStamp(12) YYMMDDHHMMSS TimeStamp(10) YYMMDDHHMM TimeStamp(8) YYYYMMDD TimeStamp(6) YYMMDD TimeStamp(4) YYMM TimeStamp(2) YY 4.3. Loại dữ liệu String Kiểu dữ liệu String chia làm ba loại, loại thứ nhất nhƣ char (chiều dài cố định) và varchar (chiều dài biến thiên). Char cho phép bạn nhập liệu dƣới dạng chuỗi với chiếu dài lớn nhất bằng chiều dài bạn đã định nghĩa, nhƣng khi truy cập dữ liệu trên Field có khai báo dạng này, bạn cần phải xử lý khoảng trắng. Điều này có nghĩa là nếu khai báo chiều dài là 10, nhƣng bạn chỉ nhập hcuỗi 4 ký tự, MySQL lƣu trữ trong bộ nhớ chiều dài 10. Ngƣợc lại với kiểu dữ liệu Char là Varchar, chiều dài lớn hất ngƣời dùng có thể nhập vào bằng chiều dài bạn đã định nghĩa cho Field này, bộ nhớ chỉ lƣu trữ chiều dài đúng với chiều dài của chuỗi bạn đã nhập. Nhƣ vậy, có nghĩa là nếu bạn khai báo kiểu varchar 10 ký tự, nhƣng bạn hcỉ nhập 5 ký tự, MySQL chỉ lƣu trữ chiều dài 5 ký tự, ngoài ra, khi bạn truy cập đến Field có kiểu dữ liệu này, bạn không cần phải giải quyết khoảng trắng. Loại thứ hai là Text hay Blob, Text cho phép lƣu chuỗi rất lớn, Blob cho phép lƣu đối tƣợng nhị phân. Loại thứ 3 là Enum và Set. Bạn có thể tham khảo cả ba loại trên trong bảng 8-7. Bảng 8-7: Kiểu dữ liệu String Loại Range Diễn giải char 1-255 Chiều dài của chuỗi lớn nhất characters 255 ký tự. varchar 1-255 Chiều dài của chuỗi lớn nhất characters 255 ký tự (characters). tinyblob 28-1 Khai báo cho Field chứa kiểu đối tƣợng nhị phân cở 255 Trang 111
  46. characters. tinytext 28-1 Khai báo cho Field chứa kiểu chuỗi cở 255 characters. blob 216-1 Khai báo cho Field chứa kiểu blob cở 65,535 characters text 216-1 Khai báo cho Field chứa kiểu chuỗi dạng văn bản cở 65,535 characters. Mediumblob 224-1 Khai báo cho Field chứa kiểu blob vừa khoảng 16,777,215 characters. Mediumtext 224-1 Khai báo cho Field chứa kiểu chuỗi dạng văn bản vừa khoảng 16,777,215 characters. Longblob 232-1 Khai báo cho Field chứa kiểu blob lớn khoảng 4,294,967,295 characters. Trang 112
  47. Longtext 232-1 Khai báo cho Field chứa kiểu chuỗi dạng văn bản lớn khoảng 4,294,967,295 characters. 5. PHÁT BIỂU SQL MySQL là một hệ thống quản lý cơ sở dữ liệu quan hệ (RDBMS) hay còn đƣợc gọi là Relational Database Management System. RDBMS là một trong những mô hình cơ sở dữ liệu quan hệ thông dụng hiện nay. 5.1. Nhóm phát biểu SQL Nhƣ đã trình bày trong chƣơng 3, hầu hết sản phẩm cơ sở dữ liệu quan hệ hiện nay đều dựa trên chuẩn của SQL và ANSI-SQL, chẳng hạn nhƣ SQL Server, Oracle, PostgreSQL và MySQL. Điều này có nghĩa là tất cả những cơ sở dữ liệu quan hệ đều phải có những tiêu chuẩn theo cú pháp SQL và MySQL cũng không phải là ngoại lệ. Ngôn ngữ SQL chia làm 4 loại sau:  DDL (Data Definition Language): Ngôn ngữ định nghĩa dữ liệu, dùng để tạo cơ sở dữ liệu, định nghĩa các đối tƣợng cơ sở dữ liệu nhƣ Table, Query, Views hay các đối tƣợng khác.  DML (Data Manipulation Language): Ngôn ngữ thao tác dữ liệu, dùng để thao tác dữ liệu, chẳng hạn nhƣ các phát biểu: Select, Inert, Delete, Update,  DCL: (Data Control Language): Ngôn ngữ sử dụng truy cập đối tƣợng cơ sở dữ liệu, dùng để thay đổi cấu trúc, tạo ngƣời dùng, gán quyền chẳng hạn nhƣ: Alter, Grant, Revoke,  TCL: (Transaction Control Language): Ngôn sử dụng để khai báo chuyển tác chẳng hạn nhƣ: Begin Tran, Rollback, Commit, 5.2. Phát biểu SQL thao tác dữ liệu Phát biểu SQL bao gồm các loại nhƣ sau:  SELECT (Truy vấn mẩu tin).  INSERT (Thêm mẩu tin).  UPDATE (Cập nhật dữ liệu).  DELETE (Xoá mẩu tin). Trang 113
  48. 5.2.1. Khái niệm cơ bản về Select Phát biểu Select dùng để truy vấn dữ liệu từ một hay nhiều bảng khác nhau, kết quả trả về là một tập mẩu tin thoã các điều kiện cho trƣớc nếu có, cú pháp của phát biểu SQL dạng SELECT: SELECT [FROM ] [WHERE ] [GROUP BY ] [HAVING ] [ORDER BY ] [LIMIT FromNumber | ToNumber] Danh sách các cột: Khai báo các tên cột, biểu thức kết hợp giữa các cột của Table bạn cần truy lục. Trong trƣờng hợp có hai cột cùng tên của hai Table trong phát biểu, bạn cần phải chỉ định tên Table đi trƣớc. Chẳng hạn, nhƣ ví dụ 8-1. Ví dụ 8-1: Phát biểu SELECT Select ItemID,ItemName From tblItems Where Cost>100; Select tblOrders.OrderID,OrderDate,ItemID,Qtty From tblOrders,tblOrderDetails Where tblOrders.OrderID = _ tblOrderDetail.OrderID; 5.2.2. Phát biểu SELECT với mệnh đề FROM Phát biểu SQL dạng SELECT là một trong những phát biểu yêu cầu MySQL truy lục dữ liệu trên cơ sở dữ liệu chỉ định. SELECT dùng để đọc thông tin từ cơ sở dữ liệu theo những trƣờng quy định, hay những biểu thức cho trƣờng đó. Mệnh đề FROM chỉ ra tên một bảng hay những bảng có quan hệ cần truy vấn thông tin. Thƣờng chúng ta sử dụng công cụ MySQL-Front | Query để thực thi phát biểu SQL. Sau khi thực thi phát biểu SQL, kết quả trả về số mẩu tin và tổng số mẩu tin đƣợc lấy ra từ bảng. Dấu * cho phép lọc mẩu tin với tất cả các trƣờng trong bảng, nếu muốn chỉ rõ những trƣờng nào cần lọc bạn cần nêu tên cụ thể những trƣờng đó. Để tiện tham khảo trong giáo trình này chúng tôi sử dụng một phần cơ sở dữ liệu có sẵn của MySQL, đồng thời bổ sung thêm cơ sở dữ liệu dành cho ứng dụng bán hàng qua mạng. Cơ sở dữ liệu bán hàng qua mạng có tên là Test, và bao gồm nhiều bảng. Bằng phát biểu SELECT chúng ta có thể biết số bảng hay đối tƣợng khác đang có trong cơ sở dữ liệu Test Trang 114
  49. Ví dụ 8-2: Thực thi phát biểu SQL SELECT hệ thống show tables from Test /* Hiển thị tất cả tên bảng của cơ sở dữ liệu hiện hành */ Kết quả trả về danh sách bảng nhƣ sau: TABLES_IN_TEST tblCountries tblProvinces tblAuthors tblPayment tblItemsion tblCustomers tblSoftware Ghi chú: Bạn có thể sử dụng phát biểu SQL trên để hiển thị những đối tượng trong cơ sở dữ liệu, bằng cách thay thế các tham số và điều kiện. Cú pháp đơn giản Select * From tablename /* Lọc tất cả số liệu của tất cả các cột (field) của tablename*/ Select field1,field2 From tablename /* Lọc tất cả số liệu của 2 field: field1, field2 của tablename*/ Select * From tablename Limit 0,10 /* Lọc top 10 mẩu tin đầu tiên của tất cả các field của tablename*/ Select field1, field2 From tablename Limit 0,10 /* Lọc top 10 mẩu tin đầu tiên của 2 fields field1, field2 của tablename*/ Ví dụ 8-3: phát biểu phát biểu SQL dạng Select Select * From tblCountries Trang 115
  50. /* Liệt kê tất cả các quốc gia trong bảng tblCountries hoặc bạn có thể liệt kê tên như phát biểu sau */ Select CountryName From tblCountries Kết quả trả về nhƣ sau: CountryCode CountryName VNA Vietnam SNG Singapore USS United Stated UKD United Kingdom GER Germany CAM Cambodia THA Thai Land MAL Malaysia INC Indonesia CHN China 5.2.3. Phát biểu SQL dạng SELECT với mệnh đề Where Khi bạn dùng mệnh đề WHERE để tạo nên tiêu chuẩn cần lọc mẩu tin theo tiêu chuẩn đƣợc định nghĩa, thông thƣờng WHERE dùng cột (trƣờng) để so sánh với giá trị, cột khác, hay biểu thức chứa cột (trƣờng) bất kỳ có trong bảng. Phát biểu SQL dạng Select với mệnh đề Where cú pháp có dạng nhƣ sau: Select * from tablename where conditions Select field1, field2, field3 from tablename where conditions Với conditions trong cả hai phát biểu trên đƣợc định nghĩa điều kiện truy vấn nhƣ khai báo sau: Select * From tablename where field1>10 select * from tblCountries where CountryCode in('VNA','CHN') Trang 116
  51. Các phép toán so sánh trong conditions bao gồm: ♦ > : lớn hơn where Amount > 100000; ♦ = : lớn hơn hoặc bằng where Amount >= 100000; ♦ >= : nhỏ hơn hoặc bằng where Amount : Khác where CustID 100000 And CustID='12'; ♦ Or : Phép toán "or" SELECT * FROM tblOrderDetails Where Amount!>100000 Or CustID=„12‟; ♦ Not : Phép toán phủ định (not) SELECT * FROM tblOrders where OrderDate is not null; ♦ Not in : Phép toán phủ định (not in) SELECT * FROM tblOrders where OrderID not in („12‟,‟15‟); ♦ Between: Kết quả thuộc trong miền giá trị SELECT * FROM tblOrders Where Amount between 10 And 500; ♦ Like : Phép toán so sánh gần giống, sử dụng dấu % để thể hiện thay thế bằng ký tự đại diện SELECT * FROM tblCustomers Trang 117
  52. where CustName like '%A'; ♦ Not Like : Phép toán phủ định so sánh gần giống, sử dụng dấu % để thể hiện thay thế bằng ký tự đại diện SELECT * FROM tblCustomers where CustName not like '%A'; ♦ IN : Phép toán so sánh trong một tập hợp SELECT * FROM tblOrders Where OrderID in ('100','200','300'); Ví dụ 8-5: Ví dụ về SQL dạng SELECT và Where /* > : lớn hơn */ Select * From tblOrders Where Amount > 100000; /* = : lớn hơn hoặc bằng */ Select * From tblOrders Where Amount >= 100000; /* >= : nhỏ hơn hoặc bằng */ Select * From tblOrders Where Amount <= 100000; /* = : bằng */ Select * Trang 118
  53. From tblOrders Where CustID=„12‟; /* != :Khác */ Select * From tblOrders Where CustID !=„12‟; /* „12‟; /* !> : Không lớn hơn */ Select * From tblOrders Where Amount !> 100000; /* ! 100000 And CustID=„12‟; /* Or : Phép toán hoặc */ Select * From tblOrders Where Amount !>100000 Or CustID=„12‟; /* Not : Phép toán phủ định */ Trang 119
  54. Select * From tblOrders Where OrderDate is NOT NULL; /* Between: giá trị nằm trong miền */ Select * From tblOrders Where Amount Between 10 and 500; /* Like : Phép toán so sánh gần giống, sử dụng dấu % để thể hiện thay thế bất kỳ ký tự */ Select * From tblOrders Where Descriion like '%A' Or CustID ='152'; /* Not Like : Phép toán phủ định so sánh gần giống, sử dụng dấu % để thể hiện thay thế bất kỳ ký tự */ Select * From tblOrders Where Descriion not like '%A' Or CustID ='152'; /* IN : Phép toán so sánh trong một tập hợp */ Select * From tblOrders Where OrderID in ('134','244','433'); /* Not IN : Phép toán phủ định so sánh trong một tập hợp */ Select * From tblOrders Where OrderID not in ('134','244','433'); 5.2.4. Mệnh đề Order by Thông thƣờng, trong khi truy vấn mẩu tin từ bảng dữ liệu, kết quả hiển thị cần sắp xếp theo chiều tăng hay giảm dựa trên ký tự ALPHABET. Nhƣng bạn cũng có thể sắp xếp theo một tiêu chuẩn bất kỳ, chẳng hạn nhƣ biểu thức. Trang 120
  55. Khi sắp xếp dữ liệu trình bày trong kết quả, cần phải chọn trƣờng hay biểu thức theo trật tự tăng dần hoặc giảm dần. Cú pháp cho mệnh đề ORDER BY cùng với trạng thái tăng hay giảm, ứng với ASC sắp xếp tăng dần, DESC giảm dần. Cú pháp có dạng nhƣ sau: Order by columnname DESC Order by columnname1 + columnname2 DESC Order by columnname ASC Order by columnname1 ASC, columnname2 DESC Ví dụ 8-6: SELECT với mệnh đề Order by DESC /* Giảm dần theo thời gian */ Select OrderID , OrderDate, CustID, Amount From tblOrders Where Amount >1000 Order by OrderDate DESC Kết quả trả về nhƣ sau: OrderID OrderDate CustID Amount 17 2001-09-20 12 178.243 18 2001-09-20 12 2.78534 16 2001-09-19 12 398.798 15 2001-09-18 12 5.758.876 14 2001-09-17 12 5.539.647 12 2001-09-16 12 1.330 13 2001-09-16 12 1.585.563 31 2001-09-16 13 459.525 11 2001-09-15 11 1.401.803 28 2001-09-15 13 1.45200 Ví dụ 8-7: SQL dạng SELECT với mệnh đề Order by và ASC /* Tăng dần theo thời gian */ Select OrderID , OrderDate, CustID, Amount From tblOrders Where Amount >1000 Order by OrderDate ASC Trang 121
  56. Kết quả trả về nhƣ sau OrderID OrderDate CustID Amount 01 2001-09-05 10 2.903.576 02 2001-09-05 10 48.168.567 03 2001-09-05 10 5.107.032 04 2001-09-08 10 2.355.537 05 2001-09-08 16 1.817.487 06 2001-09-10 16 26.000 19 2001-09-10 12 575.667 29 2001-09-10 13 466.500 07 2001-09-11 16 186.782 23 2001-09-11 12 459.162 Nếu muốn sắp xếp theo nhiều cột (trƣờng), chỉ cần sử dụng dấu phẩy (,) để phân cách các cột. Ví dụ 8-7: SELECT với mệnh đề Order by với 2 cột dữ liệu Select OrderID , OrderDate, CustID, Amount From tblOrders Where Amount >1000 Order by OrderID,CustID DESC Kết quả trả về nhƣ sau: OrderID OrderDate CustID Amount 31 2001-09-16 13 459.525 30 2001-09-15 13 153.120 29 2001-09-10 13 466.500 28 2001-09-15 13 145.200 27 2001-09-14 13 603.033 26 2001-09-13 13 230.000 25 2001-09-11 13 244.904 Trang 122
  57. 24 2001-09-12 13 1.367.228 23 2001-09-11 12 459.162 19 2001-09-10 12 575.667 Nếu muốn sắp xếp theo nhiều trƣờng kết hợp, chỉ cần dùng thứ tự từng cột cách nhau bằng dấu +. Ví dụ 8-8: SELECT với mệnh đề Order by hợp 2 cột /* Giảm dần theo số OrderID và CustID */ Select OrderID , OrderDate, CustID, Amount From tblOrders Where Amount >1000 Order by OrderID + CustID DESC Kết quả trả về nhƣ sau: OrderID OrderDate CustID Amount 31 2001-09-16 13 459.525 30 2001-09-15 13 153.120 29 2001-09-10 13 466.500 28 2001-09-15 13 145.200 27 2001-09-14 13 603.033 26 2001-09-13 13 230.000 25 2001-09-11 13 244.904 24 2001-09-12 13 1.367.228 23 2001-09-11 12 459.162 19 2001-09-10 12 575.667 Nếu trong phát biểu SQL dạng SELECT có nhiều bảng kết hợp lại với nhau, bạn có thể dùng thêm tên bảng ứng với cột của bảng đó. Phần này sẽ đƣợc diễn giải cụ thể hơn trong phần kế tiếp (JOIN -Phép hợp). Trang 123
  58. 5.2.5. SQL dạng SELECT với mệnh đề GROUP BY Khi truy vấn mẩu tin trên một hay nhiều bảng dữ liệu, thông thƣờng có những nghiệp vụ thuộc trƣờng nào đó có cùng giá trị, ví dụ khi hiển thị hợp đồng phát sinh trong tháng, kết quả sẽ có nhiều hợp đồng của khách hàng lặp đi lặp lại nhƣ ví dụ 8-9. Ví dụ 8-9: SQL dạng SELECT với mệnh đề Order by Select CustID, Amount from tblOrders Với phát biểu trên kết quả trả về nhƣ sau: CustID Amount 10 2.903.576 10 48.168.567 10 5.107.032 10 2.3555347 16 181.074.847 16 26.000 16 1.867.682 16 3.600.000 16 195.713.899 16 961.804.228 16 140.180.347 12 138 12 158.555.638 12 5.539.647 12 575.887.767 12 39.879.489 12 17.824.938 12 278.503.048 Trang 124
  59. 12 5.756.667 12 459.162 13 136.727.628 13 244.904 13 230.000 13 603.033 13 1.452.000 13 4.665.100 13 1.531.200 13 459.525 Trong báo cáo chúng ta lại cần phải biết mỗi khách hàng có bao nhiêu lần trả tiền, tổng số tiền của mỗi khách hàng đã trả là bao nhiêu? Để làm điều này, chúng ta sử dụng mệnh đề GROUP BY trong phát biểu SQL dạng SELECT cùng với một số hàm trong MySQL, bạn tham khảo ví dụ 8-10 đƣợc trình bày chi tiết từ ví dụ 4-8 nhƣng nhóm mẩu tin bằng mệnh đề Group By. Ví dụ 8-10: SQL dạng SELECT với mệnh đề Group By Select CustID, count (CustID), Sum(Amount) From tblOrders Group by CustID Order by CustID Kết quả trả về nhƣ sau: CustID 16 7 2.956.562.368 12 9 3.843.022.604 13 8 145.913.378 10 4 72.382.804 Trang 125
  60. 5.3. Các hàm thông dụng trong MySQL 5.3.1. Các hàm trong phát biểu GROUB BY  Hàm AVG: Hàm trả về giá trị bình quân của cột hay trƣờng trong câu truy vấn, ví dụ nhƣ phát biểu sau: Select AVG(Amount) From tblOrders  Hàm MIN: Hàm trả về giá trị nhỏ nhất của cột hay trƣờng trong câu truy vấn, ví dụ nhƣ phát biểu sau: Select Min(Amount) From tblOrders  Hàm MAX: Hàm trả về giá trị lớn nhất của cột hay trƣờng trong câu truy vấn, ví dụ nhƣ các phát biểu sau: Select Max(Amount) From tblOrders  Hàm Count: Hàm trả về số lƣợng mẩu tin trong câu truy vấn trên bảng, ví dụ nhƣ các phát biểu sau: Select count(*) From tblOrders Select count(CustID) From tblOrders Select count(*) From tblOrderDetails  Hàm Sum: Hàm trả về tổng các giá trị của trƣờng, cột trong câu truy vấn, ví dụ nhƣ các phát biểu sau: Select sum(Amount) From tblOrders Chẳng hạn, bạn có thể tham khảo diễn giải toàn bộ các hàm dùng trong mệnh đề GROUP BY. Ví dụ 8-11: SQL dạng SELECT với Group By và các hàm Select CustID, Count (CustID),Sum(Amount), Max(Amount), Min(Amount), Avg(Amount) From tblOrders Trang 126
  61. Group by CustID Order by CustID Kết quả trả về nhƣ sau: CustID 16 7 2956562368 1.95713899 26000 422366052 12 9 3843022604 39879489 459162 427002511 13 8 145913378 1.36727628 230000 18239172.25 10 4 72382804 48168567 2903576 18095701 5.3.2. Các hàm xử lý chuỗi  Hàm ASCII: Hàm trả về giá trị mã ASCII của ký tự bên trái của chuỗi, ví dụ nhƣ khai báo: Select ASCII('TOI') Kết quả trả về nhƣ sau: 84  Hàm Char: Hàm này chuyển đổi kiểu mã ASCII từ số nguyên sang dạng chuỗi: Select char(35) Kết quả trả về nhƣ sau: #  Hàm UPPER: Hàm này chuyển đổi chuỗi sang kiểu chữ hoạ: Select UPPER('Khang') Kết quả trả về nhƣ sau: KHANG  Hàm LOWER: Hàm này chuyển đổi chuỗi sang kiểu chữ thƣờng: Select LOWER('Khang') Kết quả trả về nhƣ sau: khang  Hàm Len: Hàm này trả về chiều dài của chuỗi: Select len('I Love You') Kết quả trả về nhƣ sau: 10  Thủ tục LTRIM: Thủ tục loại bỏ khoảng trắng bên trái của chuỗi: Trang 127
  62. Select ltrim(' Khang') Kết quả trả về nhƣ sau: 'khang'  Thủ tục RTRIM: Thủ tục loại bỏ khoảng trắng bên phải của chuỗi: Select ltrim('Khang ') Kết quả trả về nhƣ sau: 'khang'  Hàm Left: Hàm trả về chuỗi bên trái tính từ đầu cho đến vị trí thứ n: Select left('Khang',3) Kết quả trả về nhƣ sau: 'Kha'  Hàm Right: Hàm trả về chuỗi bên phải tính từ cuối cho đến vị trí thứ n: Select Right('KHang',4) Kết quả trả về nhƣ sau: 'Hang'  Hàm Instr: Hàm trả về vị trí chuỗi bắt đầu của chuỗi con trong chuỗi xét: Select INSTR ('Khang','Pham Huu Khang') Kết quả trả về nhƣ sau: 11 11 là tƣơng đƣơng vị trí thứ 11 của chữ Khang trong chuỗi "Pham Huu Khang" 5.3.3. Các hàm về xử lý thời gian  Hàm CurDate(): Hàm trả về ngày, tháng và năm hiện hành của hệ thống: Select curdate() as 'Today is‟ Kết quả trả về nhƣ sau Today is 2001-11-21  Hàm CurTime(): Hàm trả về giờ, phút và giây hiện hành của hệ thống: Select curtime() as 'Time is‟ Kết quả trả về nhƣ sau Time is 09:12:05  Hàm Period_Diff: Hàm trả về số ngày trong khoảng thời gian giữa 2 ngày: Select Trang 128
  63. Period_diff (OrderDate, getdate()) as 'So ngay giua ngay thu tien đen hom nay:' from tblOrders Kết quả trả về nhƣ sau So ngay giua ngay thu tien đen hom nay: 74 72  Hàm dayofmonth: Hàm dayofmonth trả về ngày thứ mấy trong tháng: Select dayofmonth(curdate()) as 'hom nay ngay Kết quả trả về nhƣ sau: 21 Ngoài các hàm trình bày nhƣ trên, bạn có thể tìm thấy nhiều hàm xử lý về thời gian trong phần Funtions xuất hiện bên phải màn hình của trình điều khiển nhƣ hình 8-6. Hình 8-6: Sử dụng chức năng Funcitons 5.3.4. Các hàm về toán học  Hàm sqrt: Hàm trả về là căn bật hai của một biểu thức: Select sqrt (4) Kết quả trả về là 2  Hàm Round: Hàm trả về là số làm tròn của một biểu thức: Select round (748.58,-1) Trang 129
  64. Kết quả trả về là 7500 Để tham khảo thêm một số hàm khác bạn có thể tham khảo trong phần Functions nhƣ hình 8-9. 5.4. Phát biểu SQL dạng Select với AS Khi cần thiết phải thay đổi tên trƣờng nào đó trong câu truy vấn, bạn chỉ cần dùng phát biểu AS. AS cho phép ánh xạ tên cũ, hay giá trị chƣa có tên thành tên mới (header). Ví dụ, khi sử dụng GROUP BY ở trong phần trên, những cột tạo ra từ các phép toán count, sum, max, min, cho ra kết quả không có header, nghĩa là không có tên cột để tham chiếu trong khi gọi đến chúng. Chúng ta phải cần phát biểu AS cho những trƣờng hợp này. Ví dụ 4-11: SQL dạng SELECT với AS và các hàm Select CustID, Count (CustID) as No, Sum(Amount) as TIENHD, Max(Amount) as HDLONNHAT, Min(Amount) as HDNHONHAT, Avg(Amount) as TRUNGBINH From tblOrders Group by CustID Order by CustID Kết quả hiển thị nhƣ sau: CustID No TIENHD HDLONNHAT HDNHONHAT TRUNGBINH 16 7 2956562368 1.95713899 26000 422366052 12 9 3843022604 39879489 459162 427002511 13 8 145913378 1.36727628 230000 18239172.25 10 4 72382804 48168567 2903576 18095701 5.5. Phát biểu SQL dạng Select với Limit N , M Phát biểu SQL dạng SELECT cho phép truy lục chỉ một số mẩu tin tính từ vị trí thứ n đến vị trí thứ m trong Table (theo một tiêu chuẩn hay sắp xếp nào đó). Để làm điều này, trong phát biểu SQL dạng SELECT bạn dùng chỉ định từ khoá LIMIT với số lƣợng mẩu tin cần lấy từ vị trí thứ n đến m. Chẳng hạn, trong trƣờng hợp bạn khai báo Select * from tblOrders limit 0,10. Kết quả sẽ trả về 10 mẩu tin đầu tiên trong bảng tblOrders. Bạn cũng có thể sử dụng kết hợp LIMIT với các mệnh đề nhƣ WHERE, ORDER BY nhằm tạo ra kết quả nhƣ ý muốn. Trang 130
  65. Do yêu cầu khác nhau thông qua phát biểu SQL dạng SELECT có sử dụng LIMIT, nghĩa là kết quả trả về số lƣợng 10 mẩu tin đầu tiên với tất cả các cột trong bảng tblOrders Ví dụ 8-12: Phát biểu SQL dạng SELECT với Limit N,M Select * From tblOrders Limit 0,10 Kết quả trả về nhƣ sau: OrderID OrderDate CustID Amount 01 2001-09-05 10 2903576 02 2001-09-05 10 48168567 03 2001-09-05 10 5107032 04 2001-09-08 10 2.3555347 05 2001-09-08 16 1.81074847 06 2001-09-10 16 26000 07 2001-09-11 16 1867682 08 2001-09-12 16 3600000 09 2001-09-13 16 1.95713899 10 2001-09-14 16 9.61804228 Nếu muốn lọc ra 10 hợp đồng có số tiền nhiều nhất, bạn chỉ cần sử dụng sắp xếp theo cột TotalAmount hay Amount trong bảng tblOrders. Ví dụ 8-13: Phát biểu SQL dạng SELECT với Limit N,M Select OrderID,OrderDate,CustID,Amount From tblOrders Order by Amount Desc Limit 0,10 Kết quả trả về nhƣ sau: OrderID OrderDate CustID Amount 06 2001-09-10 16 26000 26 2001-09-13 13 230000 Trang 131
  66. 25 2001-09-11 13 244904 23 2001-09-11 12 459162 31 2001-09-16 13 459525 27 2001-09-14 13 603033 28 2001-09-15 13 1452000 30 2001-09-15 13 1531200 07 2001-09-11 16 1867682 01 2001-09-05 10 2903576 Nếu muốn lọc ra 10 sản phẩm có số lƣợng bán nhiều nhất, bạn chỉ cần sử dụng sắp xếp theo cột số lƣợng Qtty. Ví dụ 8-14: Phát biểu SQL dạng Select với Limit N,M Select ItemID,Qtty,Price,Amount from tblOrderDetails Where Amount>10 order by Qtty Limit 0,10 Kết quả trả về nhƣ sau: ItemID Qtty Price Amount 1 900 12000 12960000 2 1000 12000 14400000 3 5000 12000 72000000 3 6000 12000 86400000 4 8000 12000 15200000 4 8000 12000 15200000 4 8000 10000 15200000 5 9000 12000 29600000 5 9000 12000 129600000 5 9000 12000 129600000 5.6. Phát biểu SQL dạng SELECT với DISTINCT Nếu có một hay nhiều bảng kết nối với nhau, sẽ xảy ra trùng lặp nhiều mẩu tin. Nhƣng trong trƣờng hợp này bạn chỉ cần lấy ra một mẩu tin trong tập mẩu tin trùng lặp, bạn sử dụng phát biểu SQL dạng SELECT với chỉ định DISTINCT. Ví dụ 8-14: Phát biểu SQL dạng SELECT Select ItemID,Qtty,Price,Amount from tblOrderDetails order by Qtty Trang 132
  67. Kết quả trả về nhƣ sau: ItemID Qtty Price Amount 1 900 12000 12960000 2 1000 12000 14400000 3 5000 12000 72000000 3 6000 12000 86400000 4 8000 12000 115200000 4 8000 12000 115200000 4 8000 10000 115200000 5 9000 12000 129600000 5 9000 12000 129600000 5 9000 12000 129600000 Ví dụ 8-15: Phát biểu SQL dạng SELECT với DISTINCT Select Distinct ItemID,Qtty,Price,Amount From tblOrderDetails Order by Qtty Kết quả loại bỏ những mẩu tin trùng lắp nhƣ sau: ItemID Qtty Price Amount 1 900 12000 12960000 2 1000 12000 14400000 3 6000 12000 86400000 4 8000 12000 115200000 5 9000 12000 129600000 5.7. Nhập dữ liệu bằng phát biểu SQL dạng Insert Khi cần thêm mẩu tin vào bảng trong cơ sở dữ liệu MySQL, bạn có nhiều cách để thực hiện công việc này. Trong Visual Basic 6.0, VB.NET, C Sharp hay Java có những phƣơng thức để thêm mẩu tin vào bảng trong cơ sở dữ liệu. Tuy nhiên, để sử dụng các phát biểu SQL mang tính chuyên nghiệp trong MySQL, bạn cần sử dụng phát biểu INSERT. Trang 133
  68. Bạn có thể sử dụng phát biểu Insert ngay trên ứng dụng kết nối với MySQL. Trong trƣờng hợp bạn sử dụng cơ sở dữ liệu SQL Server hay Oracle, bạn có thể tạo ra một Stored Procedure với mục đích INSERT dữ liệu vào bảng chỉ định trƣớc. Khi thêm dữ liệu, cần chú ý kiểu dữ liệu giống hoặc tƣơng ứng kiểu dữ liệu đã khai báo của cột đó, nếu không phù hợp thì lỗi sẽ phát sinh. Ngoài ra bạn cần quan tâm đến quyền của User đang truy cập cơ sở dữ liệu. User phải đƣợc cấp quyền Insert dữ liệu vào từng bảng cụ thể (quyền này do nhà quản trị cơ sở dữ liệu phân quyền cho User đó). Trong phát biểu INSERT INTO chúng tôi thực hiện trên bảng tblOrderDetails và bảng tblOrderDetailsHist, hai bảng này có cấu trúc nhƣ sau: /* Bảng tblOrderDetails*/ CREATE TABLE tblorderdetails ( ItemID int(3) unsigned DEFAULT '0' , OrderID int(3) unsigned DEFAULT '0' , No tinyint(3) unsigned DEFAULT '0' , Qtty int(3) unsigned DEFAULT '0' , Price int(3) unsigned DEFAULT '0' , Discount int(3) unsigned DEFAULT '0' , Amount bigint(3) unsigned DEFAULT '0' ); /* Bảng tblOrderDetailsHist, dùng để chứa các thông tin hợp đồng chi tiết khi hợp đồng của khách hàng này kết thúc, chương trình tự động xoá trong tblOrderDetails và lư trữ lại trong bảng tblOrderDetailsHist.*/ CREATE TABLE tblorderdetailshist ( ItemID int(3) unsigned DEFAULT '0' , OrderID int(3) unsigned DEFAULT '0' , No tinyint(3) unsigned DEFAULT '0' , Qtty int(3) unsigned DEFAULT '0' , Price int(3) unsigned DEFAULT '0' , Discount int(3) unsigned DEFAULT '0' , Amount bigint(3) unsigned DEFAULT '0' ); Khi Insert dữ liệu vào bảng, có 3 trƣờng hợp xảy ra: insert dữ liệu vào bảng từ các giá trị cụ thể, insert vào bảng lấy giá trị từ một hay nhiều bảng khác, và cuối cùng là kết hợp cả hai trƣờng hợp trên. 5.7.1. Insert vào bảng lấy giá trị cụ thể: INSERT INTO [ ] Trang 134
  69. Values (data_value) Ví dụ 8-16: INSERT dữ liệu vào bảng từ giá trị cụ thể /* Thêm mẩu tin với một số cột */ INSERT INTO TBLCUSTOMERS (CustName,Username,Password, Address,Tel,FaxNo,Email,Contact, CountryCode,ProvinceCode) Values ('Khach San CENTURY', „century‟, ‟1111‟,‟5 Le Loi‟,‟8676767‟,‟8767676‟, „century@yahoo.com‟,‟Hoang Anh‟, „VNA‟,‟HCM‟) /* Thêm mẩu tin với một số cột */ INSERT INTO TBLORDERS(OrderID,OrderDate, CustID,Description,Amount) Values ('11',curdate(),‟1', 'Dat hang qua mang', 20000) 5.7.2. Insert vào bảng lấy giá trị từ bảng khác: INSERT INTO [ ] Select [columnname list] From Where Ví dụ 8-17: INSERT vào bảng từ giá trị của bảng khác /* Thêm mẩu tin với các cột cụ thể */ /* Chuyển tất cả những hợp đồng chi tiết từ bảng tblOrderDetails vào bảng tblOrderDetailsHist */ INSERT INTO TBLORDERDETAILSHIST( ItemID, OrderID, No, Qtty, Price, Discount, Trang 135
  70. Amount) SELECT ItemID, OrderID, No, Qtty, Price, Discount, Amount From tblOrderDetails ORDER BY OrderID ASC /* Có thể viết lại thêm mẩu tin với tất cả các cột như sau Chuyển tất cả những hợp đồng chi tiết từ bảng tblOrderDetails vào bảng tblOrderDetailsHist với điều kiện số cột tương ứng trong bảng tblOrderDetails bằng với số cột trong bảng tblOrderDetailsHist, bạn có thể viết lại như sau */ INSERT INTO TBLORDERDETAILSHIST SELECT * from tblOrderDetails ORDER BY OrderID ASC 5.7.3. Insert vào bảng lấy giá trị cụ thể, bảng khác: INSERT INTO [ ] Select [columnname list], valueslist From Where ORDER BY ASC/DESC Ví dụ 8-18: INSERT vào bảng từ giá trị cụ thể, bảng khác /* Thêm mẩu tin với các cột cụ thể */ /* Chuyển tất cả những hợp đồng chi tiết từ bảng tblOrderDetails vào bảng tblOrderDetailsHist. Giả sử rằng, ngoài những cột giống như tblOrderDetails, bảng tblOrderDetailsHist còn có thêm cột Tranferdate. */ INSERT INTO TBLORDERSHIST( OrderID, OrderDate, ReceiveFolio, CustID, Trang 136
  71. Descriion, Amount, Historydate) SELECT OrderID, OrderDate, ReceiveFolio, CustID, Descriion, Amount, getdate() as Historydate From tblOrders where Month(OrderDate)=12 Order by OrderDate,CustID /* Có thể viết lại thêm mẩu tin với tất cả các cột như sau */ /* Chuyển tất cả những phiếu thu trong tháng 12 từ bảng tblOrders vào bảng tblOrdersHist với điều kiện số cột tương ứng trong bảng tblOrders bằng với số cột trong bảng tblOrdersHist, bạn có thể viết lại như sau */ INSERT INTO TBLORDERDETAILSHIST( ItemID, OrderID, No, Qtty, Price, Discount, Amount,TranferDate) SELECT ItemID, OrderID, No, Qtty, Price, Discount, Amount,CurDate() Trang 137
  72. From tblOrderDetails ORDER BY OrderID ASC 5.8. Phát biểu SQL dạng UPDATE Phát biểu SQL dạng UPDATE dùng cập nhật lại dữ liệu đã tồn tại trong bảng. Khi UPDATE dùng cập nhật dữ liệu cho một mẩu tin chỉ định nào đó thƣờng UPDATE sử dụng chung với mệnh đề WHERE. Nếu cần cập nhật tất cả các mẩu tin trong bảng bạn có thể bỏ mệnh đề WHERE. Phát biểu này có cấu trúc nhƣ sau: /* nếu cập nhất giá trị cụ thể */ Update Set = ,[ = ] [where ] /* nếu cập nhất giá trị là kết quả trả về từ phát biểu select trên một hay nhiều bảng khác */ Update Set = [where ] UPDATE có thể ảnh hƣởng đến nhiều bảng, nhƣng cập nhất giá trị chỉ có hiệu lực trên bảng đó, bạn có thể tham khảo phần này trong chƣơng kế tiếp JOIN TABLE. Cập nhật giá trị cụ thể vào một hay nhiều cột minh hoạ trong ví dụ 8-18 sau: Ví dụ 8-18: UPDATE trên các cột dữ liệu từ giá trị cụ thể /* cập nhật cột với giá trị cụ thể */ Update tblCustomers Set CustName='Cong ty TNHH Coca cola Vietnam' Where CustID=„12‟ /* cập nhật một cột với giá trị cột khác trong bảng tblOrderDetails*/ Update tblOrders Set Amount= Amount*.01, TotalAmount=Amount*0.1 Where Month(OrderDate)=12 /* cập nhật một cột với giá trị từ bảng khác*/ /* cập nhật cột Price với giá trị từ cột Cost của bảng tblItems, khai báo sau chỉ đúng trong MySQL 4.1 trở về sau*/ Update tblOrderDetails Set Price= (select distinct Cost] from tblItems Trang 138
  73. where ItemID=tblOrderDetails.ItemID) Where Price 1000) 5.9. Phát biểu SQL dạng DELETE Với phát biểu SQL dạng DELETE thì đơn giản hơn. Khi thực hiện lệnh xoá mẩu tin trong bảng chúng ta chỉ cần quan tâm đến tên bảng, và mệnh đề WHERE để xoá với những mẩu tin đã chọn lọc nếu có. Cú pháp của Delete: Delete from Where Với mệnh đề WHERE giống nhƣ bất kỳ mệnh đề WHERE nào trong phát biểu SELECT hay UPDATE và INSERT của bất kỳ ứng dụng cơ sở dữ liệu nào có sử dụng SQL. Conditions có thể là phép toán giữa các cột và giá trị, nhƣng cũng có thể giá trị là kết quả trả về từ một phát biểu SELECT khác. Ghi chú: Không có khái niệm xóa giá trị trong một cột, vì xóa giá trị một cột đồng nghĩa với cập nhật cột đó bằng giá trị rỗng. Ví dụ 8-19: Xóa mẩu tin với phát biểu SQL dạng DELETE /* Xoá mẩu tin từ bảng với điều kiện */ Delete from tblCustomers Where CustName is null Trong trƣờng hợp có ràng buộc về quan hệ của dữ liệu, thì xóa mẩu tin phải tuân thủ theo quy tắc: Xoá mẩu tin con trƣớc rồi mới xoá mẩu tin cha. Chẳng hạn, trong trƣờng hợp ta có 2 bảng: hợp đồng bán hàng (tblOrders) và hợp đồng bán hàng chi tiết (tblOrderDetails). Để xoá một hợp đồng bạn cần xóa mẩu tin trong bảng tblOrders trƣớc rồi mới đến các mẩu tin trong bảng tblOrderDetails. Ví dụ 8-20: Xoá mẩu tin với Delete /* Xoá mẩu tin từ bảng con */ Delete from tblOrderDetails where OrderID=123 /* Xoá mẩu tin từ bảng cha */ Delete from tblOrders where OrderID=123 Bạn có thể thực hiện một phát biểu SQL dạng DELETE với điều kiện trong mệnh đề WHERE lấy giá trị trả về từ phát biểu SELECT từ bảng khác, khai báo nhƣ vậy chỉ có hiệu Trang 139
  74. lực trong cơ sở dữ liệu MySQL phiên bản 8.1 trở về sau hay trong cơ sở dữ liệu SQL Server và Oracle. Ví dụ 8-21: Xoá mẩu tin theo quy tắc có ràng buộc quan hệ /* Xoá mẩu tin từ bảng với điều kiện lấy giá trị từ bảng khác */ Delete from tblOrderDetails where ItemID in (select ItemID from tblItems where ItemName like 'IT%') 6. PHÁT BIỂU SQL DẠNG JOIN Ngoài các phát biểu SQL với 4 dạng trên, trong phần kế tiếp, chúng tôi trình bày một số phát biểu SQL dạng Select để kết nối dữ liệu giữa các bảng có quan hệ với nhau, những phát biểu sẽ trình bày trong chƣơng 5 nhƣ:  Khái niệm JOIN  Phát biểu INNER JOIN ¾ Phát biểu LEFTJOIN  Phát biểu RIGHT JOIN 6.1. Khái niệm về quan hệ Để phát triển ứng dụng Web bằng bất kỳ loại cơ sở dữ liệu nào, giai đoạn phân tích thiết kế hệ thống cực kỳ quan trọng. Nếu kết quả phân tích không tối ƣu thì ứng dụng đó không thể đạt đƣợc giá trị kỹ thuật cũng nhƣ giá trị thƣơng mại. Thiết kế cơ sở dữ liệu không tối ƣu, chúng có thể dẫn đến việc chƣơng trình chạy chậm và không bền vững. Một khi ứng dụng chạy chậm đi do cơ sở dữ liệu không tối ƣu thì rất có thể bạn phải thiết kế và xây dựng lại từ đầu toàn bộ cấu trúc của chƣơng trình và cơ sở dữ liệu. Xuất phát từ lý do này, khi xây dựng một ứng dụng thông tin quản lý, chúng ta cần phải qua những bƣớc phân tích thiết kế hệ thống kỹ lƣỡng để có đƣợc mô hình quan hệ và ERD trƣớc khi đến các mô hình chức năng chi tiết. Tuy nhiên, trong lý thuyết một số kiến thức cơ bản bắt buộc bạn phải thực hiện theo mô hình hệ thống ứng với những quan hệ toàn vẹn, nhƣng trong thực tế, do tính đặc thù của ứng dụng, thƣờng bạn phải thiết kế lại mô hình theo nhu cầu cân đối giữa độ phức tạp và tính tối ƣu. Trong ứng dụng bán hàng qua mạng Test đã trình bày trong chƣơng 3, khi quan tâm đến một hợp đồng trên mạng, ngoài những thông tin liên lạc về khách hàng, bạn cần phải lƣu trữ dữ liệu khác nhƣ chiết hàng mua, phƣơng thức trả tiền, phƣơng thức giao hàng, Vấn đề đƣợc thảo luận ở đây, mỗi hợp đồng có nhì6u mặt hàng chi tiết. Trong trƣờng hợp này, chúng ta có 6 thực thể liên quan nhƣ sau, thực thể danh mục Customers (thông tin liên lạc của khách hàng), Orders (hợp đồng mua hàng), OrderDetails (chi tiết hàng mua), Items (danh mục sản phẩm). Trang 140
  75. Customers 1 -n Orders Items 1 - n 1 -n Order Details Sơ đồ 8-1: Mô hình quan hệ Giả sử rằng khi nhập số liệu vào cơ sở dữ liệu, ứng với hợp đồng có mã 101, của khách hàng có tên Nguyễn Văn A, có hai sản phẩm chi tiết: 11 (Nƣớc ngọt) và 32 (xà phòng Lux). Trong trƣờng hợp này bạn đang có một mẩu tin hợp đồng trong bảng tblCustomers, một mẩu tin hợp đồng trong bảng tblOrders và hai mẩu tin trong bảng tblOrderDetails. Nếu muốn biết thông tin hợp đồng của khách hàng A, rõ ràng bạn cần dùng phát biểu SELECT với mệnh đề kết hợp từ 3 bảng trên. Kết quả trả về 2 mẩu tin là sự kết hợp thông tin từ hai bảng tblCustomers, tblOrders và tblOrderDetails. Khi thực thi phát biểu SQL dạng SELECT ứng với cơ sở dữ liệu nhƣ trên bạn phải duyệt qua hai mẩu tin. Tất nhiên, khi viết ứng dụng thì điều này chấp nhận đƣợc, và có thể coi là tối ƣu. Giả sử rằng, ứng dụng này đƣợc phát triển trên WEB cần lƣu tâm đến vấn đề tối ƣu tốc độ truy vấn thì sao? Ngƣời thiết kế cơ sở dữ liệu trong trƣờng hợp này phải thay đổi lại cấu trúc để tăng tốc độ truy cập qua mạng khi xử lý trên cơ sở dữ liệu của ngƣời dùng. 6.2. Khái niệm về mệnh đề JOIN Trong hầu hết phát biểu SELECT, phần lớn kết quả mà bạn mong muốn lấy về đều có liên quan đến một hoặc nhiều bảng khác nhau. Trong trƣờng hợp nhƣ vậy, khi truy vấn dữ liệu bạn cần sử dụng mệnh đề JOIN để kết hợp dữ liệu trên hai hay nhiều bảng lại với nhau. Khi sử dụng JOIN, bạn cần quan tâm đến trƣờng (cột) nào trong bảng thứ nhất có quan hệ với trƣờng (cột) nào trong bảng thứ hai. Nếu mô hình quan hệ của bạn không tối ƣu hay không đúng, quản trình sử dụng JOIN sẽ cho kết quả trả về không nhƣ ý muốn. Trở lại ứng dụng bán hàng qua mạng trong giáo trình này, khi xuất một hợp đồng bán hàng cho khách hàng, theo thiết kế trong cơ sở dữ liệu chúng ta có rất nhiều bảng liên quan đến nhau. Trang 141
  76. Chẳng hạn, nếu quan tâm bán hàng thì bán cho ai. Suy ra, liên quan đến thông tin khách hàng, bán sản phẩm gì cho họ thì liên quan đến mã sản phẩm, nếu khách hàng trả tiền thì liên quan đến phiếu thu, nếu khách hàng có công nợ thì liên quan đến nợ kỳ trƣớc Trong phân này, chúng tôi tiếp tục thiết kế một số bảng dữ liệu cùng với kiểu dữ liệu tƣơng ứng và quan hệ giữa các bảng đƣợc mô tả nhƣ sau: tblCustomers (danh sách khách hàng) [CustID] int auto_increment Primary key, [CustName] [varchar] (50) NULL , [Address] [varchar] (100) NULL, [Tel] [varchar] (20) NULL, [FaxNo] [varchar] (20) NULL, [Email] [varchar] (50) NULL, [Contact] [varchar] (50) NULL [Country] [varchar] (3) NULL, [Province] [varchar] (3) NULL tblOrders (Hợp đồng bán hàng) [OrderID] [int] Not null auto_increment Primary Key, [OrderDate] [date] NULL , [CustID] int , [Description] [varchar] (200) NULL , [ShipCost] [float] NULL , [TranID] [tinyint] NULL , [PaymentID] [tinyint] NULL , [Amount] [float] NULL , [TotalAmount] [float] NULL , tblOrderDetails (Hợp đồng bán hàng chi tiết) [SubID] [int] auto_increment NOT NULL , [OrderID] int , [ItemID] int, [No] int, [Qtty] [int] NULL , [Price] int NULL , [Discount] [Float] NULL , [Amount] [Float] NULL tblItems (Danh sách sản phẩm) [ItemID] int auto_increment Primary key, [ItemName] [varchar] (200) NULL , Trang 142
  77. [Unit] [nvarchar] (20) NULL , [Cost] [Float] NULL , [Active] [tinyint] NOT NULL , [Category] int Bạn có thể tìm thấy các bảng dữ liệu còn lại trong dữ liệu Test trong đĩa đính kèm theo sách. 6.3. Mệnh đề INNER JOIN Phát biểu SQL dạng SELECT có sử dụng mệnh đề INNER JOIN thƣờng dùng để kết hợp hai hay nhiều bảng dữ liệu lại với nhau, cú pháp của SELECT có sử dụng mệnh đề INNER JOIN: SELECT [SELECT LIST] FROM INNER JOIN ON WHERE ORDER BY [ASC / DESC] Nếu bạn cần lấy ra một số cột trong các bảng có kết nối lại với nhau bằng mệnh đề INNER JOIN thì cú pháp này viết lại nhƣ sau: SELECT [FIELD1,FIELD2, ] FROM INNER JOIN ON WHERE ORDER BY [ASC / DESC] Ví dụ 8-23: INNER JOIN với một số cột chỉ định /* in ra danh sách khách hàng mua hàng trong tháng 10 */ Select CustName,OrderID, OrderDate,Amount, TotalAmount from tblCustomers inner join tblOrders on tblCustomers.CustID = tblOrders.CustID where month (OrderDate) = 10 order by CustName Kết quả trả về nhƣ sau: CustName OrderID OrderDate TotalAmount CENTURY Hotel 13 2001-10-17 388800000 Trang 143
  78. CENTURY Hotel 14 2001-10-18 518400000 CENTURY Hotel 16 2001-10-17 388800000 CENTURY Hotel 17 2001-10-18 14400000 CENTURY Hotel 18 2001-10-18 12960000 CENTURY Hotel 110 2001-10-18 216000000 Plaza Hotel 12 2001-10-17 403200000 Plaza Hotel 19 2001-10-17 86400000 Plaza Hotel 11 2001-10-17 576000000 Plaza Hotel 15 2001-10-17 288000000 Nếu bạn cần lấy ra tất cả các cột trong các bảng có kết nối lại với nhau bằng mệnh đề INNER JOIN, cú pháp trên có thể viết lại nhƣ sau: SELECT first_tablename.*, second_tablename.* [,next table name] FROM INNER JOIN ON [INNER JOIN ON ] WHERE ORDER BY [ASC / DESC] Ví dụ 8-24: INNER JOIN với tất các trƣờng liên quan /* in ra danh sách khách hàng mua hàng trong tháng 10 */ Select CustID,CustName,OrderID, OrderDate,TotalAmount from tblCustomers inner join tblOrders On TblCustomers.CustID=tblOrders.CustID where month (OrderDate) = 10 order by CustName DESC Kết quả trả về nhƣ sau: CustID CustName OrderID TotalAmount 13 Plaza Hotel 11 576000000 13 Plaza Hotel 15 288000000 12 Plaza Hotel 12 . . 403200000 12 Plaza Hotel 19 86400000 16 CENTURY Hotel 13 388800000 16 CENTURY Hotel 14 518400000 16 CENTURY Hotel 16 388800000 Trang 144
  79. 16 CENTURY Hotel 17 14400000 16 CENTURY Hotel 18 12960000 16 CENTURY Hotel 110 216000000 Nếu trong những bảng cần kết nối có tên trƣờng (cột) giống nhau thì khi thực thi phát biểu SQL dạng SELECT phải chỉ rõ cột thuộc bảng nào. Trong trƣờng hợp cả hai cùng lấy dữ liệu ra thì bạn cần chuyển ánh xạ tên khác cho cột thông qua mệnh đề AS, ví dụ nhƣ: SELECT first_tablename.CustID as CUSTID, second_tablename.CustID as CUSTID FROM INNER JOIN ON WHERE ORDER BY [ASC / DESC] Nếu trong những bảng cần kết nối đó có tên trƣờng (cột) giống nhau và không đƣợc chỉ rõ nhƣ trƣờng hợp trên khi khai báo trong cơ sở dữ liệu SQL Server, khi thực thi phát biểu SQL dạng SELECT bạn sẽ bị lỗi, chẳng hạn nhƣ: SELECT first_tablename.*, second_tablename.* FROM INNER JOIN ON WHERE ORDER BY [ASC / DESC] Server: Msg 209, Lecel 16, State Line 1 Ambiguous column name 'CustID' Tuy nhiên, với phát biểu trên bạn có thể thực thi trong cơ sở dữ liệu MySQL. Ngoài ra, phát biểu SQL dạng SELECT sử dụng INNER JOIN bạn có thể ánh xạ (alias) tên của bảng thành tên ngắn gọn để dễ tham chiếu về sau. Thực ra phát biểu ALIAS có ý nghĩa giống nhƣ AS với tên cột trong bảng thành tên cột khác trong phát biểu SELECT. Select p.*,s.* from tablename1 inner join tablename2 On tablename1.field1 = tablename2.field2 Ví dụ 8-25: INNER JOIN với ánh xạ tên bảng /* in ra danh sách khách hàng mua hàng trong tháng 10 */ Select c.CustName, Trang 145
  80. s.OrderID,s.OrderDate, s.TotalAmount from tblCustomer c inner join tblOrders s On c.CustID=s.CustID where month (s.OrderDate) = 10 order by c.CustName DESC Kết quả trả về nhƣ sau: CustName OrderID OrderDate TotalAmount CENTURY Hotel 13 200110-17 388800000 CENTURY Hotel 14 2001-10-18 518400000 CENTURY Hotel 16 2001-10-17 388800000 CENTURY Hotel 17 2001-10-18 14400000 CENTURY Hotel 18 2001-10-18 12960000 CENTURY Hotel 11 2001-10-18 216000000 Plaza Hotel 12 2001-10-17 403200000 Plaza Hotel 19 2001-10-17 86400000 Plaza Hotel 11 2001-10-17 576000000 Plaza Hotel 15 2001-10-17 288000000 Tất nhiên, bạn cũng có thể viết phát biểu trên ứng với từng cột muốn lấy ra bằng cách khai báo tên cột. 6.4. Mệnh đề Left Join Trƣờng hợp bạn mong muốn kết quả lấy ra trong hai bảng kết hợp nhau theo điều kiện: Những mẩu tin bảng bên trái tồn tại ứng với những mẩu tin ở bảng bên phải không tồn tại bạn hãy dùng mệnh đề LEFT JOIN trong phát biểu SQL dạng SELECT, cú pháp có dạng: select from lefttablename LEFT JOIN righttablename on lefttabkename.field1=righttablename.field2 Where Order by ASC/DESC Chẳng hạn, bạn chọn ra tất cả các sản phẩm (với các cột) có hay không có doanh số bán trong tháng hiện tại. Một số sản phẩm không bán trong tháng sẽ có cột Amount có cột Amount giá trị NULL. Trang 146
  81. Ví dụ 8-26: SELECT dùng LEFT JOIN /* in ra danh sách sản phẩm bán trong tháng 10 */ select ItemID,ItemName,Amount from tblItems left join tblOrderDetails on tblItems.ItemID=tblOrderDetails.ItemID order by Amount Kết quả trả về nhƣ sau: ItemID ItemName Amount 12 ASW-60VP NULL 13 ASW-60VT NULL 14 ASW-660T 120V TW 29340 NULL 14 ASW-685V 120V TW 29440 NULL 15 ASW60VP 220V 34571 NULL 16 ASW-45Z1T1 2960000 17 ASW-45Y1T 127V 14400000 18 ASW-45Y1T 220V 72000000 19 ASW-45Y1T 220V 86400000 20 ASW-45Z1T 15200000 6.5. Mệnh đề Right Join Ngƣợc lại với phát biểu SQL dạng SELECT sử dụng mệnh đề LEFT JOIN là phát biểu SQL dạng SELECT sử dụng mệnh đề RIGHT JOIN sẽ xuất dữ liệu của bảng bên phải cho dù dữ liệu của bảng bên trái không tồn tại, cú pháp có dạng: Select From lefttablename RIGHT JOIN righttablename On lefttabkename.field1=righttablename.field2 Where Order by ASC/DESC Trong ví dụ sau, bạn có thể chọn ra tất cả các sản phẩm có hay không có doanh số bán trong tháng hiện tại. Các sản phẩm không tồn tại doanh số bán sẽ không hiện ra. Ví dụ 8-27: SELECT dùng RIGHT JOIN /* in ra danh sách sản phẩm bán trong tháng ngày 17 */ /* trong phát biểu SELECT này có sử dụng mệnh đề Trang 147
  82. WHERE sử dụng phát biểu SELECT khác, kết quả của SELECT trong mệnh đề WHERE trả về một mảng OrderID */ Select ItemName,Qtty, Price,Amount From tblItems Right join tblOrderDetails On tblItems.ItemID=tblOrderDetails.ItemID Where OrderID in (12,14,23,15) Order by ItemID Kết quả trả về nhƣ sau: ItemName Qtty Price Amount ASW-45Y1T 127V SDIA29350 11000 12000 58400000 ASW-45Y1T 127V SDIA29350 10000 12000 44000000 ASW-45Y1T 127V SDIA 29350 10000 12000 14400000 ASW-45Y1T 127V SDIA 29350 10000 12000 44000000 ASW-45Y1T 127V SDIA 29350 11000 12000 58400000 ASW-45Y1T 127V SDIA 29350 10000 12000 44000000 ASW-45Y1T 127V SDIA 29350 11000 12000 58400000 ASW-45Y1T 220V ARG 29391 6000 12000 86400000 ASW-45Z1T 9000 12000 29600000 ASW-45Z1T 9000 12000 29600000 6.6. Phép toán hợp (union) Union không giống nhƣ những mệnh đề JOIN đã giới thiệu trên đây. Union là phép toán dùng để nối hai hay nhiều câu truy vấn dạng Select lại với nhau. Đối với JOIN, bạn có thể kết nối dữ liệu đƣợc thực hiện theo chiều ngang. Đối với Union bạn kết nối dữ liệu đƣợc thực hiện theo chiều dọc. Để chọn ra những khách hàng thƣờng xuyên trong tblCustomers, kết quả trả về là danh sách các khách hàng thƣờng xuyên. Ví dụ 8-28: Khách hàng thƣờng xuyên trong tblCustomers Select CustID,CustName from tblCustomers Kết quả trả về nhƣ sau: CustID CustName Trang 148
  83. 13 New World Hotel 12 Kinh Do Hotel 16 CENTURY Hotel 10 PLAZA Hotel Để chọn ra những khách hàng vãng lai trong tblTempCustomers, kết quả trả về là danh sách các khách hàng vãng lai. Ví dụ 8-29: Khách hàng vãng lai trong tblTempCustomers Select CustID,CustName from tblTempCustomers Kết quả trả về nhƣ sau: CustID CustName 23 Cong ty nuoc giai khat „12‟COLA 24 Cong ty nuoc giai khat PEPSI 25 Cong ty nuoc giai khat REDBULK 26 Cong ty nuoc giai khat TRIBICO Nếu dùng phép toán UNION để kết nối hai bảng trên, kết quả trả về là danh sách cả hai loại khách hàng trong cùng một recordset. Ví dụ 8-30: SELECT sử dụng phép hợp UNION Select CustID,CustName From tblCustomers UNION Select CustID,CustName From tblTempCustomers Kết quả trả về nhƣ sau: CustID CustName 23 Cong ty nuoc giai khat „12‟COLA 24 Cong ty nuoc giai khat PEPSI 25 Cong ty nuoc giai khat REDBULK 26 Cong ty nuoc giai khat TRIBICO 12 Kinh Do Hotel 10 PLAZA Hotel Trang 149
  84. 16 CENTURY Hotel 13 New World Hotel Ghi chú: Khi sử dụng phép toán Union trong phát biểu SQL dạng Select, bạn cần lƣu ý các quy định sau:  Tất cả những truy vấn trong UNION phải cùng số cột hay trƣờng. Nếu truy vấn thứ nhất có hai cột thì truy vấn thứ hai đƣợc sử dụng UNION cũng phải có hai cột tƣơng tự.  Khi sử dụng UNION, những cột nào có tên cột hay bí danh (alias) mới thì kết quả trả về sẽ có tựa đề (header) của từng cột và tên là tên cột của truy vấn thứ nhất.  Kiểu dữ liệu trong các cột của truy vấn 2 tƣơng thích với kiểu dữ liệu các cột tƣơng ứng trong truy vấn thứ nhất.  Trong UNION bạn có thể kết hợp nhiều câu truy vấn lại với nhau.  Kết quả hiện ra theo thứ tự của truy vấn từ dƣới lên trên. 6.7. SQL dạng thay đổi và định nghĩa cơ sở dữ liệu 6.7.1. Phát biểu SQL dạng CREATE Phát biểu SQL dạng CREATE dùng để tạo cơ sở dữ liệu và những đối tƣợng của cơ sở dữ liệu trong MySQL, SQL Server, Oracle, , chúng cú pháp nhƣ sau: CREATE Database CREATE  OBJECT TYPE: Loại đối tƣợng của cơ sở dữ liệu ví dụ nhƣ Procedure, Table, View,  OBJECT NAME: Tên của đối tƣợng trong cơ sở dữ liệu SQL nhƣ sp_IC, tblEmployer, 6.7.2. Tạo cơ sở dữ liệu - Create database Khi xây dựng cơ sở dữ liệu, bạn bắt đầu từ mô hình cơ sở dữ liệu ERD, hay từ một giai đoạn nào đó trong quy trình phân tích thiết kế hệ thống. Để tạo cơ sở dữ liệu trên MySQL hay SQL Server bạn sử dụng cú pháp sau: CREATE DATABASE Trang 150
  85. Cú pháp đầy đủ của phát biểu tạo cơ sở dữ liệu nhƣ sau, nếu bạn sử dụng cơ sở dữ liệu SQL Server: CREATE DATABASE [ ON [PRIMARY] ( [Name= ,] FileName= [, SIZE= ] [, MAXSIZE= ][, FILEGROWTH = ] )] [ LOG ON ( [Name= ,] FileName= [, SIZE= ] [, MAXSIZE= ][, FILEGROWTH = ] )] [COLLATE ] [For Load | For Attach] 6.7.3. Diễn giải CREATE Database trong SQL Server  ON: Dùng để định nghĩa nơi chứa cơ sở dữ liệu và không gian chứa tập tin log.  NAME: Dùng định nghĩa tên của cơ sở dữ liệu. Tên này dùng tham chiếu khi gọi đến cơ sở dữ liệu, tên đƣợc dùng cho quá trình backup, export, Import, Shrink cơ sở dữ liệu đó.  FILENAME: Tên tập tin cơ sở dữ liệu lƣu trong đĩa cứng, thông thƣờng khi cài SQL Server lên ổ đĩa nào thì giá trị mặc định cho phép lƣu tập tin đến thƣ mục đó. Tuy nhiên, nếu muốn bạn cũng có thể thay đổi vị trí các file này. Khi tạo cơ sở dữ liệu, bạn đã định nghĩa vị trí đặt tập tin ở thƣ mục nào thì không thể di chuyển một cách thủ công (nhƣ dùng Explorer của Windows), vì làm điều đó thật nguy hiểm nhất là khi dữ liệu trong cơ sở dữ liệu đang có giá trị kinh tế.  SIZE: Dung lƣợng của cơ sở dữ liệu khi khởi tạo chúng. Thông thƣờng giá trị mặc định là 1 MB.  Dung lƣợng phải là số nguyên, có thể tăng thêm bằng cách sử dụng thủ tục Shrink trong SQL Server. Trang 151
  86.  MAXSIZE: Dung lƣợng lớn nhất, khi dung lƣợng cơ sở dữ liệu tăng lên đến mức MaxSize thì dừng lại. Nếu khi dung lƣợng bằng MaxSize, các chuyển tác có thể bị huỷ bỏ hay trả về lỗi không thể thực hiện đƣợc, và có thể làm cho cơ sở dữ liệu của bạn bị treo. Để tránh điều này xảy ra, thì ngƣời quản trị cơ sở dữ liệu phải thƣờng xuyên theo giỏi quá trình tăng dung lƣợng cơ sở dữ liệu theo thời gian, để có biện pháp tránh mọi rủi ro có thể xảy ra.  FILEGROWTH: Dung lƣợng khởi tạo cùng dung lƣợng tối đa cho phép tăng trong quá trình thêm dữ liệu vào cơ sở dữ liệu. Nhằm tự động hóa, chúng ta phải thiết lập quá trình tăng tự động theo chỉ số KB cho trƣớc hay tỷ lệ phần trăm theo dung lƣợng đang có.  LOG ON: Log on cho phép bạn quản lý những chuyển tác xảy ra trong quá trình sử dụng cơ sở dữ liệu của SQL Server. Xây dựng cơ sở dữ liệu Test Nhƣ đã trình bày ở trên, sau đây ví dụ tạo cơ sở dữ liệu Test có cú pháp nhƣ sau: Ví dụ 8-31: Tạo cơ sở dữ liệu Test trong SQL Server USE master GO CREATE DATABASE Test ON ( NAME = Test, FILENAME = 'c:\mssql7\data\Testdat.mdf', SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 ) LOG ON ( NAME = 'Testlog', FILENAME = 'c:\mssql7\data\Testlog.ldf', SIZE = 5MB, MAXSIZE = 25MB, FILEGROWTH = 5MB ) GO Trang 152
  87. Để đơn giản hoá các đối tƣợng Table trong cơ sở dữ liệu Test, chúng tôi chỉ trình bày một vài phát biểu SQL dạng Create Table, các Table khác bạn có thể tìm thấy trong cơ sở dữ liệu đính kèm. Ví dụ 8-32: Tạo một số bảng trong Test /* Tạo bảng danh sách khách hàng thường xuyên */ CREATE TABLE tblcustomers ( CustID int(3) unsigned NOT NULL auto_increment, Username varchar(20) NOT NULL DEFAULT '' , Password varchar(10) NOT NULL DEFAULT '' , CustName varchar(50) , Address varchar(100) , Tel varchar(20) , FaxNo varchar(10) , Email varchar(50) , Contact varchar(50) , CountryCode char(3) , ProvinceCode char(3) , PRIMARY KEY (CustID), INDEX CustID (CustID) ); /* Tạo bảng hợp đồng mua hàng qua mạng */ CREATE TABLE tblorders ( OrderID int(3) NOT NULL auto_increment, OrderDate date , CustID int(11) , Description varchar(100) DEFAULT '0' , TranID tinyint(3) DEFAULT '0' , PaymentID tinyint(3) DEFAULT '0' , Amount float DEFAULT '0' , ShipCost float DEFAULT '0' , TotalAmount float DEFAULT '0' , Trang 153
  88. PRIMARY KEY (OrderID), INDEX OrderID (OrderID) ); /* Tạo bảng hợp đồng chi tiết mua hàng qua mạng */ CREATE TABLE tblorderdetails ( ItemID int(3) unsigned DEFAULT '0' , OrderID int(3) unsigned DEFAULT '0' , No tinyint(3) unsigned DEFAULT '0' , Qtty int(3) unsigned DEFAULT '0' , Price int(3) unsigned DEFAULT '0' , Discount int(3) unsigned DEFAULT '0' , Amount bigint(3) unsigned DEFAULT '0' ); Một số quy định khi thiết kế Table 6.7.4. Tên cột - Column Name Đặt tên cột cũng giống nhƣ đặt tên bảng, có rất nhiều quy tắc đặt tên (nhƣ đã trình bày ở trên phần table), nhƣng khuyến khích bạn nên theo một số quy tắc cơ bản sau:  Tên cột bắt đầu chữ hoa, còn lại bằng chữ thƣờng.  Tên ngắn gọn và đầy đủ ý nghĩa.  Không nên đặt tên cột có khoảng trắng, sau này bạn sẽ gặp những phiền toái khi tham chiếu đến cột đó.  Không đặt tên cột trùng với những từ khoá, từ dành riêng, và những ký tự đặc biệt nhƣ những phép toán hay toán tử khác.  Chú ý, nên đặt tên cột cùng tên những cột có quan hệ với những bảng khác trong cùng cơ sở dữ liệu, giúp dễ hiểu và tránh bị nhầm lẫn. Một số ngƣời thích thêm vào dấu gạch chân (_) để phân biệt ý nghĩa hay tên gọi của cột, điều này là tùy vào sở thích của bạn. Tuy nhiên chúng tôi không thích qui tắc này. Nhƣng đối với kinh nghiệm lập thiết kế xây dựng cơ sở dữ liệu thì bạn không nên dùng dấu gạch dƣới _, và dĩ nhiên trong nhiều trƣờng hợp khác bạn sẽ cảm thấy khó chịu khi thêm một dấu _ trong tên của đối tƣợng của cơ sở dữ liệu. Mặc dù không có vấn đề gì cho cú pháp hay các phát biểu tham chiếu đến chúng, nhƣng bạn sẽ thấy tại sao chúng ta không nên dùng dấu gạch chân (_) khi đặt tên đối tƣợng hay tên cơ sở dữ liệu trong MySQL. Trang 154
  89.  Nếu bạn đặt tên có dấu _ ,bạn phải tốn thời gian hay năng lƣợng cho hành động tạo ra dấu _  Trong chừng mực hay giới hạn nào đó do hiệu ứng của Font chữ có thể phát sinh lỗi sẽ gây ra nhầm lẫn cho ngƣời lập trình.  Nói tóm lại là bạn sẽ mất thêm thời gian lƣu tâm đến chúng. 6.7.5. Kiểu dữ liệu - Data type Nhƣ đã trình bày các lại dữ liệu trong phần trên, khi xây dựng cơ sở dữ liệu, tất cả những trƣờng trong bảng cần phải có kiểu dữ liệu cụ thể. Vấn đề quan trọng là chọn kiểu dữ liệu nào cho phù hợp với dữ liệu mà ngƣời dùng sẽ nhập vào. Để thiết kế dữ liệu phù hợp với thực tế, ngoài tính ứng dụng hợp với ngữ cảnh bạn cũng cần quan tâm đến kiểu dữ liệu tƣơng thích và chiều dài của từng cột. Chẳng hạn nhƣ: [CustID] [varchar] (10) /* hay */ [CustID] int 6.7.6. Giá trị mặc định - Default Thông thƣờng khi tạo ra một cột trong bảng đôi khi chúng ta cần áp dụng giá trị mặc định, không chỉ cho trƣờng hợp số liệu không nhập từ bên ngoài mà còn cho các cột tự động có giá trị tự sinh. Với những lý do nhƣ vậy, chúng ta cần có một số giá trị mặc định cho những cột cần thiết, ví dụ :  Nếu cột đó là số chúng ta có giá trị mặc định là 0  Nếu cột đó là ngày tháng chúng ta có giá trị mặc định là ngày nào đó (nhƣ 0000-00-00 là CurDate())  Nếu cột đó có giá trị là 0 hoặc 1, bạn có thể khai báo giá trị mặc định là 0 hoặc 1  Nếu cột đó là chuỗi chúng ta có giá trị mặc định nhƣ là 'A' 6.7.7. Số tự động auto_increment auto_increment là khái niệm cực kỳ quan trọng trong MySQL (tƣơng đƣơng với Identity trong SQL Server, Autonumber trong MS Access). Khi bạn muốn một cột có giá trị tăng tự động nhƣ AutoNumber/Identity, bạn nên định nghĩa cột đó nhƣ auto_increment,. Khi sử dụng auto_increment làm số tăng tự động thì kiểu dữ liệu là số nguyên hoặc số nguyên lớn. Trong trƣờng hợp, bạn khai báo số tự động trong SQL Server, bạn cần phải khai báo thêm các thông số nhƣ seed. Seed là giá trị khởi đầu khi SQL Server tự động tăng giá trị, Increament là bƣớc tăng, nó cho biết mỗi lần tăng cần bao nhiêu giá trị. Trang 155
  90. Vì dụ khi tạo auto_increment cho cột ItemID [Int] auto_increment, nghĩa là bắt đầu số 1 và mỗi lần tăng 1 số. Kết quả bạn sẽ có là 1,2,3,4, n. Trong phát biểu SQL của MySQL, để tạo bảng có gá trị tăng tự động bạn chỉ cần khai báo tên cột, kiểu dữ liệu Int (Integer) và auto_increment nhƣ sau: IDNO Int auto_increment NOT NULL Trong giao diện đồ họa bạn chỉ cần check vào tuỳ chọn AutoIncreament nhƣ hình 8-10. Hình 8-10: Chọn auto_increment NULL / NOT NULL Đây là trạng thái của một cột trong bảng cho phép chấp nhận giá trị NULL hay không? Nếu bạn chỉ ra ràng buộc giá trị NOT NULL thì bắt buộc phải có giá trị trong cột này mỗi khi mẩu tin đƣợc nhập vào. Đối với một số kiểu dữ liệu không cho phép NULL bạn nên thiết lập giá trị mặc định cho cột đó, ví dụ nhƣ kiểu dữ liệu bit không cho phép NULL. Trong phát biểu SQL tạo bảng, bạn chỉ cần khai báo NULL hay NOT NULL sau kiểu dữ liệu của cột đó. Trong giao diện đồ họa chỉ cần đánh dấu chọn vào tuỳ chọn Not NULL nhƣ hình 8-10. Trang 156
  91. 6.8. Thay cấu trúc đối tƣợng bằng ALTER Khi chúng ta cần thiết phải sửa đổi một phần cấu trúc của các đối tƣợng nhƣ table (view, hay SP trong SQL Server) vì mục đích nào đó, thì Bạn sử dụng phát biểu ALTER để thay đổi cấu trúc của đối tƣợng hiện có: ALTER Khi một bảng tồn tại trong cơ sở dữ liệu, do nhu cầu cần thiết phải thay đổi cấu trúc bảng, bạn sử dụng phát biểu ALTER TABLE cùng các tham số của chúng nhƣ cú pháp sau: ALTER TABLE table alteration [,alteration] Chẳng hạn, bạn có thể sử dụng phát biểu ALTER TABLE để thêm một cột tên Activate với kiểu dữ liệu TinyInt có giá trị mặc định là 1. Ví dụ 8-33: Thêm một cột tên Activate vào bảng tblOrders ALTER TABLE tblorders ADD Activate TINYINT DEFAULT "1" Khi thay đổi thiết lập giá trị mặc định cho cột bạn nên quan tâm đến giá trị mặc định đó có phù hợp cho những mẩu tin đang tồn tại hay không. Muốn thay đổi giá trị mặc định của cột cho những mẩu tin đang tồn tại, bạn sử dụng đến mệnh đề phụ nhƣ trong ví dụ sau: Ví dụ 8-34: Thiết lập giá trị mặc định trong bảng tblOrders ALTER TABLE tblorders CHANGE OrderDate OrderDate DATETIME DEFAULT "0000-00-00" Thay đổi kiểu dữ liệu từ Date dang DateTime, bạn có thể khai báo nhƣ ví dụ 4-35 sau: Trang 157
  92. Ví dụ 8-35: Thay đổi kiểu dữ liệu ALTER TABLE tblorders CHANGE OrderDate OrderDate DATE DEFAULT "0000-00-00 00:00:00" Mặc khác, bạn cũng có thể tạo hay thay đổi bảng trong màn hình MySQL-Front. Chỉ cần chọn ngăn Database | R-Click | Create New Table, cửa sổ xuất hiện nhƣ hình 8-11. Hình 8-11: Giao diện tạo bảng bằng MySQL-Front 6.9. Phát biểu SQL dạng DROP Drop là phát biểu thực hiện phép xoá. DROP dùng để xoá đối tƣợng của cơ sở dữ liệu nhƣ bảng, cơ sở dữ liệu, Cú pháp của phát biểu DROP: DROP [, n] Bạn có thể xoá cơ sở dữ liệu, bằng cách khai báo nhƣ sau: Drop Database Test Trang 158
  93. /* Phát biểu DROP TABLE chỉ rõ bảng nào cần xoá, nếu xoá nhiều bảng thì bạn cần dùng dấu phẩy (,) */ DROP TABLE tblCustomers, tblSuppliers Ngoài ra, bạn cũng có thể dùng MySQl-Front để xoá bảng hay các đối tƣợng Table trong cơ sở dữ liệu chỉ định. Nếu chọn nhiều bảng cùng một lúc bạn sử dụng phím Control hay Shift nhƣ sau: Hình 8-12: Chọn đối tƣợng để xoá bảng trong MySQL-Front 7. TẠO KỊCH BẢN SQL- SQL SCRIPTS Thông thƣờng khi xây dựng cơ sở dữ liệu để phát triển ứng dụng, đôi khi bạn cần chuyển cơ sở dữ liệu từ máy này sang máy khác, hay từ khu vực này hay đến khu vực khác. Có rất nhiều cách để làm điều này, ở đây chúng tôi giới thiệu đến các bạn một công cụ tái tạo lại cơ sở dữ liệu mới từ kịch bản của cơ sở dữ liệu gốc. Kịch bản SQL (SQL Script) là tổng hợp tất cả các phát biểu SQL dùng để tạo ra cơ sở dữ liệu trong quá trình xây dựng chúng, chúng lƣu trữ dƣới dạng văn bản có tên mở rộng .sql (cautruc.sql). Công cụ này tạo kịch bản cho tất cả các đối tƣợng của cơ sở dữ liệu với những thuộc tính căn bản. Tuy nhiên, nếu bạn chọn vào tuỳ chọn Data, SQL Script bao gồm các phát biểu SQL dạng Insert cùng với dữ liệu trong bảng. Trƣớc tiên bạn có thể nhận thấy cửa sổ công cụ này trong MySQL-Front, bằng cách chọn tên cơ sở dữ liệu Test, sau đó chọn Tools / Im-Export / Export Table, cửa sổ xuất hiện nhƣ hình 8-13 sau: Trang 159