1 / 29

MySQL

MySQL. Case study about MySQL On XAMPP server http :// www.thaiall.com/mysql Update : August 23,2012. เข้าสู่ MySQL Command Line. C:>cd c:xamppmysqlbin DOS> mysql -u root -p enter mysql > help ; แสดงส่วนช่วยเหลือ mysql > s แสดง สถานะ mysql > quit ; ออกจากโปรแกรม

natan
Download Presentation

MySQL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

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>

More Related