Download
mysql n.
Skip this Video
Loading SlideShow in 5 Seconds..
MySQL PowerPoint Presentation

MySQL

338 Views Download Presentation
Download Presentation

MySQL

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

  1. MySQL Case study about MySQL On XAMPP server http://www.thaiall.com/mysql Update : August 23,2012

  2. เข้าสู่ MySQL Command Line C:\>cd c:\xampp\mysql\bin DOS> mysql -u root -p enter mysql> help;แสดงส่วนช่วยเหลือ mysql> \s แสดงสถานะ mysql> quit;ออกจากโปรแกรม C:\>cd c:\thaiabc\mysql\bin

  3. สร้าง แสดง และเข้าฐานข้อมูล mysql> create database oho; mysql> show databases; mysql> use oho;

  4. สร้างตารางไว้เก็บข้อมูลสร้างตารางไว้เก็บข้อมูล mysql> create table wow1( ->xidint , ->xnamevarchar(50), ->xsalarydouble -> ); mysql> create table w(w1 int, w2 int); mysql> show tables;

  5. เพิ่มหลายระเบียน และอ่านข้อมูลจากตาราง w มาแสดง mysql> insert into w values(1,10); mysql> insert into w values(1,10); mysql> insert into w values(1,10); mysql> select * from w;

  6. เพิ่มหนึ่งระเบียนมีหลายเขตข้อมูลในตาราง wow1 mysql> insert into wow1 values( ->1, ->"boy", ->5000); mysql> select xid,xname from wow1;

  7. เลือกข้อมูลได้หลายแบบเลือกข้อมูลได้หลายแบบ mysql> select count(xid) from wow1; mysql> select sum(xsalary) from wow1; mysql> select max(xid) from wow1; mysql> select min(xid) from wow1; mysql> select avg(xid) from wow1;

  8. เลือกแบบมีเงือนไข mysql> select * from wow1 where xid=1; mysql> select * from w order by w2 desc; mysql> show columns from wow1; ตัวอย่าง Key : Primary, Unique, Multiple occurrences (address) Extra : auto_increment

  9. การลบระเบียน ลบตาราง หรือลบฐานข้อมูล mysql> delete from wow1 where xid=1; mysql> delete from wow1; mysql> drop table wow1; mysql> drop database oho;

  10. ปรับปรุงข้อมูล mysql> update wow1 set xname="tom" ->where xid=1; mysql> update wow1 set ->xname="tom", ->xsalary=3000 ->where xid=2; mysql> update w set w2=5;

  11. ชุด 1 : เมนูภาษา PHP เรียกใช้โปรแกรม : s1.php <body><ol> <li>s1connect.php</li> <li><a href=s2crtdb.php>s2crtdb.php</a></li> <li><a href=s3select.php>s3select.php</a></li> <li><a href=s4insert.php>s4insert.php</a></li> <li><a href=s5delete.php>s5delete.php</a></li> <li><a href=s6update.php>s6update.php</a></li> <li><a href=s7drop.php>s7drop.php</a></li> </ol><hr color=red> <?phprequire("s3select.php"); ?> </body>

  12. ชุด 1 : เชื่อมต่อฐานข้อมูล : s1connect.php <?php $db = "perlphpasp"; $host = "127.0.0.1:3306"; $uname = "root"; $passwd = ""; if (!$connect=mysql_connect($host,$uname,$passwd)){ echo 'Could not connect to mysql'; exit; } ?>

  13. ชุด 1 : สร้างฐานข้อมูล ตาราง และ 3 ระเบียน :s2crtdb.php <?php require("s1connect.php"); $tb="worker"; $sql = "create database $db"; if (!$result=mysql_query($sql,$connect))echo "error"; else echo "ok "; $sql="create table $tb (eid char(4),ename char(40))"; mysql_select_db($db,$connect); if (!$result=mysql_query($sql,$connect)) echo "error"; else echo "ok "; $sql="insert into $tb values('1001','Tom')"; mysql_select_db($db,$connect); if (!$result=mysql_query($sql,$connect)) echo "error"; else echo "ok "; $sql="insert into $tb values('1002','Dang')"; mysql_select_db($db,$connect); if (!$result=mysql_query($sql,$connect)) echo "error"; else echo "ok "; $sql="insert into $tb values('1003','Pom')"; mysql_select_db($db,$connect); if (!$result=mysql_query($sql,$connect)) echo "error"; else echo "ok "; mysql_close($connect); ?>

  14. ชุด 1 : อ่านข้อมูลมาแสดง : s3select.php <?php include("s1connect.php"); $tb="worker"; echo "Display records : "; $query="select * from $tb"; mysql_select_db($db,$connect); $result = mysql_query($query,$connect); if ($result) { echo "OK<br>"; } else { exit; } while ($object = mysql_fetch_object($result)) { echo $object->eid . " " . $object->ename . "<br>"; } echo "Total records : ".mysql_num_rows($result); mysql_close($connect); ?>

  15. ชุด 1 : เพิ่มข้อมูลใหม่ : s4insert.php <body> <form action=s4insert.php> <input name=nid value=1004><input name=nname value=kmit> <input type=submit value=s4insert.php> </form> <?php require("s1connect.php"); if (!isset($_GET['nid']) || !isset($_GET['nname'])) exit; $sql="insert into worker values ('". $_GET['nid'] ."','". $_GET['nname']."')"; mysql_select_db($db,$connect); if(!$result=mysql_query($sql,$connect)) echo "$sql : not found"; else echo "$sql : ok"; mysql_close($connect); ?> </body>

  16. ชุด 1 : ลบข้อมูล : s5delete.php <body><form action=s5delete.php> <input name=delid value=1001> <input type=submit value=s5delete.php> </form> <?php require("s1connect.php"); if (!isset($_GET['delid'])) { exit; } $sql="delete from worker "; $sql.="where eid ='".$_GET['delid']."'"; mysql_select_db($db,$connect); if(!$result=mysql_query($sql,$connect)) echo "$sql : not found"; else echo "$sql : ok"; mysql_close($connect); ?> </body>

  17. ชุด 1 : ปรับปรุงข้อมูล : s6update.php <body><form action=s6update.php> <input name=updid value=1003> <input name=updenamevalue="nation university"> <input type=submit value=s6update.php> </form> <?php require("s1connect.php"); if (!isset($_GET['updid'])) { exit; } $sql="update worker set "; $sql.="eid='". $_GET['updid'] ."', "; $sql.="ename='". $_GET['updename'] ."' "; $sql.="where eid='". $_GET['updid'] ."'"; mysql_select_db($db,$connect); if(!$result=mysql_query($sql,$connect)) echo "$sql : not found"; else echo "$sql : ok"; mysql_close($connect); ?> </body>

  18. ชุด 1 : ลบฐานข้อมูล : s7drop.php <?php require("s1connect.php"); $sql="drop database $db"; mysql_select_db($db,$connect); if(!$result=mysql_query($sql,$connect)) echo "Database : not found"; else echo "Database : droped"; mysql_close($connect); ?>

  19. ชุด 2: ฟอร์มสมัครของผู้ใช้ และเข้าระบบ Administrator : apply.htm <body> <form action=apply.php method=post> Name : <input name=mname><br> Surname : <input name=msurn><br> Address : <input name=maddr><br> <input type=submit> </form> <form action=session.php method=post> Admin Password : <input name=admin type=password value=p><br> <input type=submit value='Log in'> </form> <form action=session.php method=post> <input type=submit value='Log Out'> </form>

  20. ชุด 2: ตรวจรหัส Admin และเก็บสถานะ Admin : session.php <?php session_start(); $_SESSION['admin'] = ""; if (isset($_POST['admin'])) { if ($_POST['admin'] == "p") $_SESSION['admin'] = "ok"; } echo $_SESSION['admin']."<br>"; echo date("d/m/Y H:i:s")."<br>"; ?> <a href=crtdbm.php>crtdbm.php</a> <a href=display.php>display.php</a> <a href=apply.htm>apply.htm</a>

  21. ชุด 2: สร้างฐานข้อมูล ถ้าเป็น Admin : crtdbm.php <?php session_start(); if ($_SESSION['admin'] != "ok") exit; $db = "mem"; $tb = "member"; $host = "127.0.0.1:3306"; $uname = "root"; $passwd = ""; if (!$connect=mysql_connect($host,$uname,$passwd)){ echo 'Could not connect to mysql'; exit; } $s = "create database $db"; if (!$result=mysql_query($s,$connect)) echo "$s : error<br>"; else { echo "$s : ok<br>"; $s="create table $tb ("; $s.="mid int primary key not null auto_increment,"; $s.="mname char(40),msurn char(40),maddr char(40))"; mysql_select_db($db,$connect); if(!$result=mysql_query($s,$connect)) echo "$s error<br>"; else echo "$s ok<br>"; } mysql_close($connect); ?>

  22. ชุด 2: ผู้ใช้สมัครสมาชิก : apply.php <?php $db = "mem"; $tb = "member"; $host = "127.0.0.1:3306"; $uname = "root"; $passwd = ""; if (!$connect=mysql_connect($host,$uname,$passwd)){ echo 'Could not connect to mysql'; exit; } if (isset($_POST["mname"]) && isset($_POST["mname"])){ $sql="insert into $tb values('',"; $sql.="'".$_POST["mname"]."',"; $sql.="'".$_POST["msurn"]."',"; $sql.="'".$_POST["maddr"]."')"; mysql_select_db($db,$connect); if(!$result=mysql_query($sql,$connect)) echo "$sql : error<br>"; else { echo "$sql : ok<br>"; } } mysql_close($connect); ?><br><a href=apply.htm>apply.htm</a>

  23. ชุด 2: แสดงข้อมูล ถ้าเป็น Admin : display.php <?php session_start(); if ($_SESSION['admin'] != "ok") exit; $db = "mem"; $tb = "member"; $host = "127.0.0.1:3306"; $uname = "root"; $passwd = ""; if (!$connect=mysql_connect($host,$uname,$passwd)){ echo 'Could not connect to mysql'; exit; } echo "Display Members : <ol>"; $query="select * from $tb order by mid"; mysql_select_db($db,$connect); if($result=mysql_query($query,$connect)){ while ($object = mysql_fetch_object($result)) { echo "<li>" . $object->mid . "<br>"; echo $object->mname." ". $object->msurn." ". $object->maddr . "<br>"; echo "<a href=del.php?mid=".$object->mid.">x</a>"; } echo "</ol>Total records:".mysql_num_rows($result); } mysql_close($connect); ?><br><a href=apply.htm>apply.htm</a>

  24. ชุด 2: ลบข้อมูลตาม mid ถ้าเป็น Admin : del.php <?php session_start(); if ($_SESSION['admin'] != "ok") exit; $db = "mem"; $tb = "member"; $host = "127.0.0.1:3306"; $uname = "root"; $passwd = ""; if (!$connect=mysql_connect($host,$uname,$passwd)){ echo 'Could not connect to mysql'; exit; } if (isset($_GET["mid"])) { $sql="delete from $tb where mid =".$_GET["mid"]; mysql_select_db($db,$connect); if(!$result=mysql_query($sql,$connect)) echo "error"; else echo "ok"; } mysql_close($connect); ?> <br><a href=apply.htm>apply.htm</a>

  25. ชุด 3 : ฟอร์มรับความคิดเห็น : boardf.htm <body> <form action=boardi.php method=post> Subject : <input name=subj><br> Writer : <input name=writer><br> Message : <textarea cols=20 rows=5 name=msg> </textarea><br> <input type=submit> </form> <br><a href=boards.php>boards.php</a> <br><a href=crtdb.php>crtdb.php</a> </body>

  26. ชุด 3 : ส่วนเชื่อมต่อฐานข้อมูล : connect.php <?php $db = "wboard"; $tb = "webboard"; $host = "127.0.0.1:3306"; $uname = "root"; $passwd = ""; if (!$connect=mysql_connect($host,$uname,$passwd)){ echo 'Could not connect to mysql'; exit; } ?>

  27. ชุด 3 : สร้างฐานข้อมูล : crtdb.php <?php require("connect.php"); $s = "create database $db"; if (!$result=mysql_query($s,$connect)) echo "$s : error<br>"; else { echo "$s : ok<br>"; $s="create table $tb ("; $s.="widint primary key not null auto_increment,"; $s.="subj char(40),writer char(40),"; $s.="msg char(100))"; mysql_select_db($db,$connect); if(!$result=mysql_query($s,$connect)) echo "$s : error<br>"; else echo "$s : ok<br>"; } mysql_close($connect); ?>

  28. ชุด 3 : เพิ่มความคิดเห็นใหม่ : boardi.php <?php require("connect.php"); if (isset($_POST["subj"]) && isset($_POST["writer"])){ $sql="insert into $tb values('',"; $sql.="'".$_POST["subj"]."',"; $sql.="'".$_POST["writer"]."',"; $sql.="'".$_POST["msg"]."')"; mysql_select_db($db,$connect); if(!$result=mysql_query($sql,$connect)) echo "$sql : error<br>"; else { echo "$sql : ok<br>"; } } mysql_close($connect); ?> <br><a href=boardf.htm>boardf.htm </a> <br><a href=boards.php>boards.php</a>

  29. ชุด 3 : แสดงข้อมูล : boards.php <?php require("connect.php"); echo "<ul>Webboard : <ol>"; $sql="select * from $tb order by widdesc"; mysql_select_db($db,$connect); if($result=mysql_query($sql,$connect)) { while ($object = mysql_fetch_object($result)) { echo "<li>" . $object->wid . " : "; echo $object->subj . " : " . $object->writer; echo "<pre>" . $object->msg . "</pre>"; } echo "</ol>Total records:".mysql_num_rows($result); echo "</ul>"; } mysql_close($connect); ?> <br><a href=boardf.htm>boardf.htm </a>