時間

Size: px

Start display at page:

Download "時間"

はるまさこのえ
4 years ago
Views:

1 LAMP 2007/9/27 1

2 LAMP LinuxApacheMySQLPHP Linux Linux 1964 Multics OS MITGEAT&T UNIX UNIX MSDOS Windows Windows MS-DOS UNIX Linux IP Linux Linux Linux root 2

3 root suroot root su root exit Windows (1) telnet telnet (IP ) student01, misa001,

4 (2) TeraTerm (1) telnet logout 4

5 (2) TeraTerm ^d root Windows Unix Unix MSDOS Windows C D Unix / / MSDOS MySQL /usr/local/mysql/bin MySQL /usr/local/mysql/var /var/www/users/student01, /var/www/users/teacher01, teacher02 5

6 3 ls al r w x drwxrwxrwx d rwx 2 rwx 3 rwx rwx pwd (print working directory) ls (list), dir ls (dir) ls al (dir al) 6

7 EUC cd (change directory) cd xxx cd.. cd ~ cd / cd /usr/local mkdir (make directory) 7

8 rmdir (remove directory) cp (copy) rm (remove) mv (move) cat (concatenate) more 1 cat more find find /usr/local/apache2/htdocs name show_products.php 8

9 vi Terapad FTP emacs Terapad FTP mysql /usr/local/mysql/bin PATH MySQL /usr/local/mysql/var/ user01student00 (PHP )/usr/local/apache2/htdocs/ user01student00 free IP Linux UTF-8 UTF-8 LinuxCentOS 4.5 Apachepackage MySQLpackage PHPpackage Windows XP JIS Windows XP Windows XP 9

TeraTerm FTP FFFTP FTP IP student01 ------- student10, teacher01, teacher02

10 TeraTerm FTP FFFTP FTP IP student student10, teacher01, teacher02 /var/www/users/student student10 /var/www/users/teacher01, teacher02 10

11 UTF-8 LAMP Linux MySQL PHP PHP Terapad UTF-8 UTF-8N FTP MySQL 2 telnetteraterm /usr/local/mysql/bin/mysql u p Password: PHP 11

12 2.1 Entity Relationship ER

13 ANSI/X3/SPARC 3 (1978 ) ER (hierarchical model) (network model) (relational model) relationtable (relational database) 13

14 RDB DBMSDatabase Management System MySQL DBMS MySQL OracleDB2SQL ServerSybasePostgreSQL

15 null (candidate key)(foreign key) (normalization) <<>> 15

16 16

17 second normal form2nf 17

18 third normal form3nf 18

19 (operation) (union, OR) R S <<>> (difference) R S (intersection, AND) R S (direct product) R S R S (projection) (selection) (join) 19

20 SQL SQL query(iso) 1992 (SQL-92IBM SEQUEL(Structured English Query Language) 1970 ISO SQL Structured Query Language MySQL MySQL telnet /user/local/mysql/bin/mysql u p (data type) tinyint bit bool boolean smallint 20

21 mediumint int integer bigint float double real decimal dec numeric fixed char varchar tinyblob tinytext bolob text mediumbolob mediumblob mediumtext longblob longtext char(20) date datetime timestamp time year primary key auto_increment 21

22 show databases use create database create database whdb; drop database show tables show columns from show fields from create table create table product (prdid char(15) primary key, prdname char(20), model char(20), prdstate tinyint(2) unsigned, regdate date, deldate date); create table provider (pvrid char(2) primary key, pvrname char(20), zipcode char(10), address char(60), pvrperson char(20), tel char(20), pvrstate tinyint(2) unsigned, regdate date, deldate date); create table rsvtable (rsvid smallint(5) unsigned primary key auto_increment, prdid char(15), whsid char(2), noi int(11), rsvstate tinyint(2) unsigned, shipdate date, shippeddate date, rsvdate date); create table provide (prvid char(17) primary key, prdid char(15), pvrid char(2), unitcost int(10), prvstate tinyint(2) unsigned, regdate date, deldate date); create table warehouse (whsid char(2) primary key, whsname char(20), whsstate tinyint(2) unsigned, regdate date, deldate date); 22

23 create table inventory (invid char(17) primary key, prdid char(15), whsid char(2), rstockno int(10), lstockno int(10), invasset float(20), invstate tinyint(2) unsigned, regdate date, deldate date); NULL not null Default default 0 (0 ) insert into set insert into 1 2values 1 2 drop table alter table change alter table add first alter table add after alter table drop select * from where update set 1 1, 2 2 where delete from where grant on to identified by grant all on databasename.* to username@localhost identified by password ; databasename * username@localhost (all) password revoke on from use 23

24 delete from user where root secret mysqldump u root password= secret > 2 password c: whdbprotected txt mysql u root p < Enterpassword set password ); root set password for root@localhost=password( ); 2 set password for root@localhost=password( ); 2 root set password for root@localhost=password( secret ); set password for root@localhost=password( secret ); 24

25 PHP testdb01 person mysql u root p ********(secret) Enter create database testdb01; use testdb01; create table person(id int(11) primary key auto_increment, name char(20), age int(11)); insert into person(name,age) values( suzuki ichiro,28); insert into person(name,age) values( suzuki jiro,26); insert into person(name) values( kimura saburo ); (Linux)/usr/local/apache2/htdocs/php WindowsXPC: Program Files Apache Group Apache2 htdocs php show_persons.php body <?php // DB $dbserver="localhost"; $dbuser="root"; $dbpassword="secret"; $dbname="testdb01"; $tbname="person"; //MySQL $con=mysql_connect($dbserver, $dbuser, $dbpassword); // mysql_query("set names sjis"); // $selectdb=mysql_select_db($dbname); // $query="select * from $tbname"; 25

26 // $result=mysql_query($query); // //html body $body=mysql_num_rows($result)."<br><br>"; $body.="<table border='1'> <tr> <th>id</th><th>name</th> <th>age</th> </tr>"; while ($row=mysql_fetch_array($result)){ $body.="<tr>"."<td>$row[0]</td>"."<td>$row[1]</td>"."<td>$row[2]</td>"."</tr>"; } $body.="</table>"; //body // mysql_free_result($result); // $con=mysql_close($con);?> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> <HTML> <HEAD> <META http-equiv="content-type" content="text/html; charset=utf-8"> <META http-equiv="content-styole-type" content="text/css"> </HEAD> <BODY> <?=$body?> </BODY> </HTML> body <html> <head> 26

27 <meta http-equiv= content-type content= text/html; charset=utf-8 > <title>person </title> </head> <body> <h3>person </h3> <table border= 1 > <tr><td>id</td><td></td><td></td></tr> <?php // $condb = mysql_connect( localhost, root, secret ); // $seldb = mysql_select_db( testdb01 ); //SQL $query = select * from person; ; //SQL $result = mysql_query($query); // while($row = mysql_fetch_array($result)){ print( <tr><td>.$row[ id ]. </td><td>.$row[ name ]. </td><td>.$row[ age ]. </td></tr> ); } // mysql_free_result($result); mysql_close($condb);?> </table> </body> </html> 27

28 show_one_person_form.html <html> <head> <title>person 1 </title> <meta http-equiv= content-type content= text/html; charset=utf-8 > </head> <body> <script language= JavaScript > function check(){ var a=document.form1.personid.value; if (a== ){ alert( person ID ); return false; } } </script> <form name= form1 action= show_one_person.php method = post onsubmit= return check(); > <h3>person </h3> <table> <tr><td>id</td><td><input type= text name= id ></td></tr> </table> <input type= submit value= 1 <br> </form> </body> </html> show_one_person.php <html> <head> <title>person 1 </title> <meta http-equiv="content-type" content="text/html; charset=utf-8"> </head> <body> 28

29 <?php // personid $id=$_post["id"]; // $condb = mysql_connect("localhost","root","secret"); // $seldb = mysql_select_db("testdb01"); //SQL $query = "select * from person where id=".$id; //SQL $result = mysql_query($query);?> <h3>person 1 </h3> <table border = "1"> <tr><td>id</td><td></td><td></td></tr> <?php while($row = mysql_fetch_array($result)){ print('<tr><td>'.$row["id"].'</td><td>'.$row["name"].'</td><td>'.$row["age"].'</td></tr>' ); } mysql_free_result($result); mysql_close($condb);?> </table> </body> </html> 29

30 add_one_person_form.html <html> <head> <title>person 1 </title> <meta http-equiv="content-type" content="text/html; charset=utf-8"> </head> <body> <script language="javascript"> function check(){ var a=document.form1.name.value; if (a==""){ alert("name "); return false; } } </script> <form name= "form1" action= "show_one_person.php" method ="post" onsubmit= "return check();"> <h3>person </h3> <table> <tr><td>name</td><td><input type="text" name="name"></td></tr> <tr><td>name</td><td><input type="text" name="age"></td></tr> </table> <input type= "submit" value= "1 "<br> </form> </body> </html> add_one_person.php <html> <head> <title>person 1 </title> <meta http-equiv="content-type" content="text/html; charset=utf-8"> </head> 30

31 <body> <?php // name $name=$_post["name"]; // age $age=$_post["age"]; // $condb = mysql_connect("localhost","root","secret"); // $seldb = mysql_select_db("testdb01"); //SQL $query = "insert into person set name='".$name."', age=".$age; print "query:".$query; //SQL $result = mysql_query($query); $id = mysql_insert_id($condb); //insert id?> <h3>person 1 </h3> <table border = "1"> <tr><td>id</td><td></td><td></td></tr> <tr><td><?=$id?></td><td><?=$name?></td><td><?=$age?></td></tr> <?php //mysql_free_result($result); mysql_close($condb);?> </table> </body> </html> 31

32 mod_one_person_form.html <html> <head> <title>person 1 </title> <meta http-equiv="content-type" content="text/html; charset=utf-8"> </head> <body> <script language="javascript"> function check(){ var a=document.form1.id.value; if (a==""){ alert("person ID "); return false; } } </script> <form name= "form1" action= "mod_one_person.php" method ="post" onsubmit= "return check();"> <h3>person </h3> <table> <tr><td>id</td><td><input type="text" name="id"></td></tr> <tr><td>name</td><td><input type="text" name="name"></td></tr> <tr><td>age</td><td><input type="text" name="age"></td></tr> </table> <input type= "submit" value= "1 "<br> </form> </body> </html> mod_one_person.php <html> <head> <title>person 1 </title> <meta http-equiv="content-type" content="text/html; charset=utf-8"> 32

33 </head> <body> <?php // id $id=$_post["id"]; // name $name=$_post["name"]; // age $age=$_post["age"]; // $condb = mysql_connect("localhost","root","secret"); // $seldb = mysql_select_db("testdb01"); //SQL //$query = "update person set name='".$name."', age=".$age. ; $query = "update person set name='".$name."', age=".$age. " where id=".$id; print "query:".$query; //SQL $result = mysql_query($query);?> <h3>person 1 </h3> <table border = "1"> <tr><td>id</td><td></td><td></td></tr> <tr><td><?=$id?></td><td><?=$name?></td><td><?=$age?></td></tr> <?php //mysql_free_result($result); mysql_close($condb);?> </table> </body> </html> 33

34 del_one_person_form.html <html> <head> <title>person 1 </title> <meta http-equiv="content-type" content="text/html; charset=utf-8"> </head> <body> <script language="javascript"> function check(){ var a=document.form1.id.value; if (a==""){ alert("person ID "); return false; } } </script> <form name= "form1" action= "del_one_person.php" method ="post" onsubmit= "return check();"> <h3>person </h3> <table> <tr><td>id</td><td><input type="text" name="id"></td></tr> </table> <input type= "submit" value= "1 "<br> </form> </body> </html> del_one_person.php <html> <head> <title>person 1 </title> <meta http-equiv="content-type" content="text/html; charset=utf-8"> </head> <body> 34

35 <?php // id $id=$_post["id"]; // $condb = mysql_connect("localhost","root","secret"); // $seldb = mysql_select_db("testdb01"); //SQL //$query = "delete form person where id=".$id; $query = "delete form person where id=".$id; //SQL $result = mysql_query($query);?> <h3>person 1 </h3> <?php //mysql_free_result($result); mysql_close($condb);?> </table> </body> </html> 35

36 person_table.htm <html> <head> <title>person </title> <meta http-equiv="content-type" content="text/html; charset=utf-8"> EUC-JP </head> <body> <h3>person </h3> <a href="show_persons.php">person <br> <a href="show_one_person_form.html">person <br> <a href="add_one_person_form.html">person <br> <a href="mod_one_person_form.html">person <br> <a href="del_one_person_form.html">person <br> </body> </html> person_table_top.html <html> <head> <title>person </title> <meta http-equiv="content-type" content="text/html; charset=utf-8"> </head> <body bgcolor="#ffffff" text="#000000"> <h3>person </h3> <table border="1"> // <form action="show_persons.php" method="post"> <tr> <td bgcolor="#dddddd"><input type="submit" value=""></td> <td>person </td> </tr> </form> // 1 <form action="show_one_person.php" method="post"> 36

37 <tr> <td bgcolor="#dddddd"><input type="submit" value=""></td> <td> ID<input type="text" name="id" size="5"> </td> </tr> </form> // 1 <form action="add_one_person.php" method="post"> <tr> <td bgcolor="#dddddd"><input type="submit" value=""></td> <td> ()<input type="text" name="name" size="20"><br> <input type="text" name="age" size="3"><br> </td> </tr> </form> // 1 <form action="mod_one_person.php" method="post"> <tr> <td bgcolor="#dddddd"><input type="submit" value=""></td> <td> ID <input type="text" name="id" size="5"><br> ()<input type="text" name="name" size="20"><br> <input type="text" name="age" size="3"><br> </td> </tr> </form> // 1 <form action="del_one_person.php" method="post"> <tr> <td bgcolor="#dddddd"><input type="submit" value=""></td> <td> ID <input type="text" name="id" size="5"><br> </td> </tr> 37

38 </form> </body> </html> 38

39 PHP (1) Linux 1999 (2) PHP (3) MySQLTomcat 2003 (4) PHP5.0MySQL5.0 Web 2006 (5) MySQL

hands_on_4.PDF

hands_on_4.PDF PHPMySQL 4 PC LAN 2 () () SQLDBMS DBMS DataBase Management System mysql DBMS SQL Structured Query Language SQL DBMS 3 DBMS DataBase Management System B Table 3 Table 2 Table 1 a 1 a 2 a 3 A SQLStructured