- PDF 無料ダウンロード

Size: px

Start display at page:

Download ""

ともなりやなぎしま
5 years ago
Views:

1 III Web Database ver.2.1.1

3 1 Web Database WebDB RDBMS RDBMS RDB SQL SQL MySQL PHP WebDB PHP HTML DB Web Database

5 i III Web Database

7 1 1 Web Database 1.1 (database system, DBS) (database, DB) (database management system, DBMS) DBMS (relational database management system, RDBMS) DBMS (object oriented database, OODB) DBMS

8 2 1 Web Database CAD CASE Web Web WebDB RDBMS WebDB 1.2 WebDB DB DB DB PC(personal computer) DB DB WWW(World Wide Web) WWW PC Web Web DB WebDB DB DBMS

9 1.2 WebDB WebDB Web DB Web DB Web DBMS OS(Unix OS, MS Windows, MacOS ) Web (Apache ) DBMS(Oracle, MySQL, PostgreSQL ) CGI *1 HTML * WebDB PC WebDB WebDB DB *1 Common Gateway Interface, CGI ( C, Perl PHP, Python, Ruby ) CGI *2 PHP Perl. Java Servlet / JavaServer Pages Java

10 4 1 Web Database WebDB *3 WebDB Web (dynamic) Web Web (static) HTML *3

11 5 2 RDBMS WebDB RDBMS 2.1 RDBMS RDBMS(Relational Database Management System) DB (table) RDBMS RDBMS RDBMS WebDB DBMS Oracle Database, Microsoft

12 6 2 RDBMS SQL Server, IBM DB2 Microsoft Access DBMS MySQL PostgreSQL MySQL 2.2 RDB DBMS DBMS DB RDB 1 (non-first normal form) 1 1 (first normal form, 1NF) 2 (second normal form, 2NF) 3 (third normal form, 3NF) (further normalization) - (Boyce-Codd normal form, BCNF) (forth normal form, 4NF) (fifth normal form, 5NF) (nested relation) *1 *2 (domain) *1 RDB *2

13 2.2 RDB 7 *3 *4 *5 (fully functionally dependent) *6 *7 (determinant ) DB * *3 *4 *5 X Y Y X X Y *6 x y x y *7 X Y X Y *8 2.1

14 8 2 RDBMS 2.1 DB /10/07 S L032 N /10/07 I L055 P055 PC 79, /11/07 S L032 N /12/07 J D102 N /15/07 K L115 P031 PC 115, M021 USB 3,150 3 C C P048 1,850 2 P055 PC 79,800 2 P105 CD-R 1, /10/07 S L032 N /10/07 S L032 M021 USB 3, /10/07 S L032 C /10/07 I L055 P055 PC 79, /10/07 I L055 C /11/07 S L032 N /12/07 J D102 N /12/07 J D102 P048 1, /15/07 K L115 P031 PC 115, /15/07 K L115 P055 PC 79, /15/07 K L115 P105 CD-R 1,

15 2.2 RDB , 2.4, /10/07 L032 S /10/07 L055 I /11/07 L032 S /12/07 D102 J /15/07 L115 K N M021 USB 3,150 C P055 PC 79,800 C N N P048 1,850 P031 PC 115,700 P055 PC 79,800 P105 CD-R 1, N M C P C N N P P P P105 2

16 10 2 RDBMS 2 * , /10/07 L /10/07 L /11/07 L /12/07 D /15/07 L D102 L032 L055 L115 J S I K RDB RDB INT, TINYINT DATE, DATETIME CHAR, VARCHAR *9

17 RDB RDB RDB RDB (relational algebra) (relational calculus) (union) (difference) (cartesian product) (projection) (selection) * 10 (join) (natural join) (intersection) (division, quotient) *10 3

18 12 2 RDBMS (outer-join) * USB PC PC null null *11 RDBMS MySQL

19 PC USB 2 PC null PC 1005 USB 2 PC null PC USB 2 PC

20 14 2 RDBMS null (inner-join) 2 null PC 1005 USB 2 PC * (self-join) PC PC PC * (cross-join) 2 *12 *13

21 PC PC PC PC PC PC PC PC 2001 PC * (union) *14

22 16 2 RDBMS USB RDB * 15 *15

23 17 3 SQL 3.1 SQL SQL SQL IBM San Jose E.F.Codd RDB D.Chamberlin 1987 ISO RDB ISO SQL RDB ISO SQL: DB SQL RDB SQL DB SQL SQL ISO SQL SQL * MySQL RDBMS RDBMS RDBMS WebDB RDBMS *1 IBM SQL Structured Query Language

24 18 3 SQL MySQL RDBMS MySQL MySQL AB Michael Widenius Monty RDBMS 1995 MySQL AB TcX DataKonsult AB Linux, Solaris, MacOS X, Windows RDBMS Yahoo! Finance, Google AdWords C, C++, Java, Perl, PHP, Python, Ruby API SJIS, EUC, Unicode MySQL PC RDBMS MySQL RDBMS SQL RDBMS RDBMS MySQL SQL RDBMS MySQL, DB SQL WebDB Web Web RDBMS OS Solaris 10 (SunOS 5.10) RDBMS MySQL ver Web Apache ver PHP ver MySQL MySQL

25 3.2 MySQL 19 * MySQL SQL DB SQL ; enter *3 COLUMNS, DATABASES MySQL mysql a cse$ mysql -u a p Enter password: *4 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1688 Server version: log Source distribution Type help; or \h for help. Type \c to clear the buffer. mysql> DB mysql> DBMS help; mysql *2 *3 enter > mysql > ; enter *4

26 20 3 SQL mysql *5 mysql mysql> exit Bye cse$ mysql> quit Bye cse$ DB DB OK mysql> SHOW DATABASES; DB mysql> SHOW DATABASES; Database information_schema a205000db test rows in set (0.00 sec) 3 DB DB a205000db DB mysql> SHOW TABLES FROM a205000db; mysql> SHOW TABLES FROM a205000db; Empty set (0.00 sec) mysql> *5 mysql /opt/webstack/mysql/5.1/bin

27 3.2 MySQL DB a205000db DB seiseki mysql> SHOW COLUMNS FROM a205000db.seiseki; mysql> SHOW COLUMNS from a205000db.seiseki; ERROR 1146 (42S02): Table a205000db.seiseki doesn t exist mysql> a205xxxdb p205xxxdb DB. a205000db DB USE a205000db mysql> USE a205000db Database changed mysql> DB MySQL DB DB DB. USE DB (NULL) 2 *6 NULL list t id c INT name c 16 VARCHAR(16) *6

28 22 3 SQL add c 32 VARCHAR(32) CREATE TABLE mysql> CREATE TABLE list_t (id_c INT, name_c VARCHAR(16), -> add_c VARCHAR(32)); Query OK, 0 rows affected (0.00 sec) mysql> Query OK SHOW TABLES mysql> SHOW TABLES; Tables_in_a205000db list_t row in set (0.00 sec) mysql> mysql> SHOW COLUMNS FROM list_t; Field Type Null Key Default Extra id_c int(11) YES NULL name_c varchar(16) YES NULL add_c varchar(32) YES NULL rows in set (0.00 sec) mysql> OK Null Field YES NULL Key Default NULL 3.2.6

29 3.2 MySQL 23 mysql> INSERT INTO list_t SET id_c=1, name_c="yamamotoya", -> add_c="nagoya-shi Naka-ku Sakae"; Query OK, 1 row affected (0.00 sec) mysql> mysql> INSERT INTO list_t (id_c,name_c,add_c) -> VALUES("2","Yaba-ton","Nagoya-shi Naka-ku Ohsu"); Query OK, 1 row affected (0.00 sec) mysql> OK mysql> INSERT INTO list_t (id_c,name_c,add_c) -> VALUES("3","Atsuta-Horaiken","Nagoya-shi Atsuta-ku Kanbe-cho"), -> ("4","Furaibo","Nagoya-shi Naka-ku Kanayama"), -> ("5","Yokoi","Nagoya-shi Naka-ku Sakae"); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> DB 1 mysql> SELECT * FROM list_t; id_c name_c add_c Yamamotoya Nagoya-shi Naka-ku Sakae 2 Yaba-ton Nagoya-shi Naka-ku Ohsu 3 Atsuta-Horaiken Nagoya-shi Atsuta-ku Kanbe-cho 4 Furaibo Nagoya-shi Naka-ku Kanayama 5 Yokoi Nagoya-shi Naka-ku Sakae rows in set (0.00 sec) mysql> WHERE SELECT FROM DB. WHERE

30 24 3 SQL USE DB SELECT FROM WHERE SELECT (id c) id c=3 mysql> SELECT * FROM list_t WHERE id_c=1; id_c name_c add_c Yamamotoya Nagoya-shi Naka-ku Sakae row in set (0.00 sec) mysql> WHERE = >, <, >=, <=, <> >, <,,, WHERE IN WHERE id c IN (1, 2, 5) BETWEEN AND WHERE id c BETWEEN 3 AND 5 AND (&& WHERE name c= Yokoi AND && ) id c=5 OR WHERE name c= Yaba-ton OR id c=1 NOT WHERE NOT (name c= Furaibo OR id c=2) LIKE NOT LIKE WHERE add c LIKE %Atsuta-ku% IS NULL IS NOT NULL WHERE add c IS NULL AS

31 3.2 MySQL 25 id c, name c, add c Number, Restaurants, Address AS mysql> SELECT id_c AS Number, name_c AS Restaurants, add_c AS Address -> FROM list_t; Number Restaurants Address Yamamotoya Nagoya-shi Naka-ku Sakae 2 Yaba-ton Nagoya-shi Naka-ku Ohsu 3 Atsuta-Horaiken Nagoya-shi Atsuta-ku Kanbe-cho 4 Furaibo Nagoya-shi Naka-ku Kanayama 5 Yokoi Nagoya-shi Naka-ku Sakae rows in set (0.00 sec) mysql> ORDER BY ASC DESC mysql> SELECT * FROM list_t ORDER BY name_c ASC; id_c name_c add_c Atsuta-Horaiken Nagoya-shi Atsuta-ku Kanbe-cho 4 Furaibo Nagoya-shi Naka-ku Kanayama 2 Yaba-ton Nagoya-shi Naka-ku Ohsu 1 Yamamotoya Nagoya-shi Naka-ku Sakae 5 Yokoi Nagoya-shi Naka-ku Sakae rows in set (0.00 sec) mysql> DESC

32 26 3 SQL mysql> UPDATE list_t SET name_c="furaibo Kanayama" WHERE id_c=4; Query OK, 0 rows affected (0.00 sec) Rows matched: 1 Changed: 0 Warnings: 0 mysql> SELECT * FROM list_t; id_c name_c add_c Yamamotoya Nagoya-shi Naka-ku Sakae 2 Yaba-ton Nagoya-shi Naka-ku Ohsu 3 Atsuta-Horaiken Nagoya-shi Atsuta-ku Kanbe-cho 4 Furaibo Kanayama Nagoya-shi Naka-ku Kanayama 5 Yokoi Nagoya-shi Naka-ku Sakae rows in set (0.00 sec) mysql> UPDATE Furaibo Furaibo Kanayama WHERE enter mysql> DELETE FROM list_t WHERE id_c=4; Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM list_t; id_c name_c add_c Yamamotoya Nagoya-shi Naka-ku Sakae 2 Yaba-ton Nagoya-shi Naka-ku Ohsu 3 Atsuta-Horaiken Nagoya-shi Atsuta-ku Kanbe-cho 5 Yokoi Nagoya-shi Naka-ku Sakae rows in set (0.00 sec) mysql>

33 3.2 MySQL 27 DELETE FROM SELECT * WHERE SET =NULL MySQL ALTER TABLE ADD mysql> ALTER TABLE list_t ADD tel_c VARCHAR(8); Query OK, 4 rows affected (0.02 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> SHOW COLUMNS FROM list_t; Field Type Null Key Default Extra id_c int(11) YES NULL name_c varchar(16) YES NULL add_c varchar(32) YES NULL tel_c varchar(8) YES NULL rows in set (0.00 sec) mysql> tel c ADD CHANGE ALTER TABLE RENAME AS mysql> ALTER TABLE list_t RENAME AS restaurant_t; Query OK, 0 rows affected (0.00 sec) mysql> SHOW TABLES;

34 28 3 SQL Tables_in_test restaurant_t row in set (0.00 sec) mysql> DROP TABLE mysql> DROP TABLE restaurant_t; Query OK, 0 rows affected (0.00 sec) NULL NOT NULL NULL 2. NULL DEFAULT 3. PRIMARY KEY 4. ID AUTO INCREMENT 5. ZEROFILL 2 0 ZEROFILL 6. UNSIGNED UNSIGNED 0

35 3.2 MySQL mysql SELECT GROUP BY AVG( ) SUM( ) MAX( ) COUNT( ) +,,, /, % SELECT ALTER TABLE UPDATE SET = SQL restaurant t id_c name_c add_c eval_c Yamamotoya Naka-ku Sakae NULL 2 Yaba-ton Naka-ku Ohsu NULL 3 Horaiken Atsuta-ku Kanbe-cho NULL

36 30 3 SQL 4 Furaibo Naka-ku Kanayama NULL 5 Yokoi Naka-ku Sakae NULL eval c mysql> CREATE TABLE rating_t (gradeid_c TINYINT AUTO_INCREMENT -> NOT NULL PRIMARY KEY, comment_c VARCHAR(10) NOT NULL); Query OK, 0 rows affected (0.00 sec) mysql> rating t gradeid c 10 comment c gradeid c NOT NULL ID mysql> INSERT INTO rating_t (comment_c) -> VALUES("excellent"), ("wonderful"), ("yummy"), -> ("OK"), ("poor"), ("terrible"); Query OK, 6 rows affected (0.00 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM rating_t; gradeid_c comment_c excellent 2 wonderful 3 yummy 4 OK 5 poor 6 terrible rows in set (0.00 sec) mysql> * id_c name_c add_c eval_c *7

37 3.2 MySQL 31 1 Yamamotoya Naka-ku Sakae 2 2 Yaba-ton Naka-ku Ohsu 3 3 Horaiken Atsuta-ku Kanbe-cho 1 4 Furaibo Naka-ku Kanayama NULL 5 Yokoi Naka-ku Sakae mysql> SELECT * FROM list_t LEFT JOIN rating_t ON eval_c=gradeid_c; id_c name_c add_c eval_c gradeid_c comment_c Yamamotoya Naka-ku Sakae 2 2 wonderful 2 Yaba-ton Naka-ku Ohsu 3 3 yummy 3 Horaiken Atsuta-ku Kanbe-cho 1 1 excellent 4 Furaibo Naka-ku Kanayama NULL NULL NULL 5 Yokoi Naka-ku Sakae 3 3 yummy rows in set (0.00 sec) mysql> SQL LEFT JOIN RIGHT JOIN OK mysql> SELECT * FROM list_t RIGHT JOIN rating_t ON eval_c=gradeid_c; id_c name_c add_c eval_c gradeid_c comment_c Horaiken Atsuta-ku Kanbe-cho 1 1 excellent 1 Yamamotoya Naka-ku Sakae 2 2 wonderful 2 Yaba-ton Naka-ku Ohsu 3 3 yummy 5 Yokoi Naka-ku Sakae 3 3 yummy NULL NULL NULL NULL 4 OK NULL NULL NULL NULL 5 poor NULL NULL NULL NULL 6 terrible rows in set (0.00 sec) mysql> mysql> SELECT * FROM list_t INNER JOIN rating_t ON eval_c=gradeid_c;

38 32 3 SQL id_c name_c add_c eval_c gradeid_c comment_c Yamamotoya Naka-ku Sakae 2 2 wonderful 2 Yaba-ton Naka-ku Ohsu 3 3 yummy 3 Horaiken Atsuta-ku Kanbe-cho 1 1 excellent 5 Yokoi Naka-ku Sakae 3 3 yummy rows in set (0.00 sec) mysql> SQL PHP Web

39 33 4 PHP WebDB 4.1 PHP PHP (PHP:Hypertext Preprocessor) PHP HTML DBMS WebDB PHP Web 100 PHP 4.2 HTML DB Web DB HTML PHP WebDB HTML HTML PHP RDBMS Web *1 WebDB *1 HTML PHP

40 34 4 PHP WebDB 1. MySQL 2. DB 3. SQL MySQL MySQL DB MySQL mysql connect() PHP DB mysql query() PHP $conn = mysql_connect($sv, $user, $pass) or die("connection Error!"); mysql_select_db($dbname) or die("connection Error!!"); mysql connect() $conn or die( ) mysql select db() or die() SQL list t id c name c name c $sql = "SELECT id_c, name_c FROM list_t ORDER BY name_c"; $res = mysql_query($sql, $conn) or die("data Retrieval Error!"); mysql query() SQL mysql connect() $conn SQL $conn * 2 SELECT SQL INSERT INTO VALUES DELETE FROM WHERE UPDATE *2 PHP web

41 4.2 HTML DB 35 SET mysql query() SQL $res echo "<table border=\"1\">"; echo "<tr>"; echo "<td>id</td>"; echo "<td>name of Restaurant</td>"; echo "<td>address</td>"; echo "</tr>"; while ($row = mysql_fetch_array($res, MYSQL_ASSOC)) { echo "<tr>"; echo "<td>".$row["id_c"]."</td>"; echo "<td>".$row["name_c"]."</td>"; echo "<td>".$row["ad_c"]."</td>"; echo "</tr>"; } echo "</table>"; mysql fetch array() MYSQL ASSOC HTML PHP MySQL mysql_close($conn); mysql xxx() PHP MySQL HTML PHP HTML PHP <html> <head>

42 36 4 PHP WebDB <title>data Indication</title> </head> <body> <h3> </h3> <?php // $sv = "localhost"; $dbname = "sugawara"; $user = "sugawara"; $passwd = "xxxxx"; // $enc_disp = "EUC-JP"; $enc_db = "EUC-JP"; // function cnv_enc($string, $to, $from) { // $det_enc = mb_detect_encoding($string, $from. ", ". $to); if ($det_enc and $det_enc!= $to) { return mb_convert_encoding($string, $to, $det_enc); } else { return $string; } } // $conn = mysql_connect($sv, $user, $passwd) or die("connection Error!"); mysql_select_db($dbname) or die("conection Error!!"); // SQL $sql = "SELECT id_c, name_c, add_c FROM list_t ORDER BY name_c"; $res = mysql_query($sql, $conn) or die("data Retrieval Error!"); // echo "<table border=\"1\">"; echo "<tr>"; echo "<td>id</td>"; echo "<td> </td>"; echo "<td> </td>"; echo "</tr>"; while ($row = mysql_fetch_array($res, MYSQL_ASSOC)) { echo "<tr>"; echo "<td>".$row["id_c"]."</td>"; echo "<td>".cnv_enc($row["name_c"], $enc_disp, $enc_db)."</td>"; echo "<td>".cnv_enc($row["add_c"], $enc_disp, $enc_db)."</td>"; echo "</tr>"; } echo "</table>"; // mysql_close($conn);

43 4.2 HTML DB 37?> </body> </html> mb detect encoding() 1 EUC-JP mb convert encoding() *3 *3

45 39 5 Web Database 5.1 *1 HTTP, PHP, MySQL WebDB RDB 3 web web web *2 *1 *2

46 40 5 Web Database OK 5.1 mysql connect() mysql close() SELECT INSERT UPDATE DELETE mysql close()

47 DB 1 *3 *3

48 42 5 Web Database PHP

49 RDB RDBMS MySQL *1 *2 1. * where 3 *1 3.2 *2 20 *3 3.2

50 *4 web HTML PHP * * *4 1 *5 WebDB *6

51 RDB shinji web PDF MS Word shinji at nitech.ac.jp * 7 webdb xxx 20115xxx 1 webdb xxx.pdf 20115xxx 2 PDF web *8 *9 web * :00 3 *7 at *8 web *9 *10

53 47 7 WebDB WebDB PC

54 48 7 MS PowerPoint * 1 PC *2 *1 PowerPoint *2 (1996).

55 49 WebDB RDBMS

1,.,,,., RDBM, SQL. OSS,, SQL,,.

1,.,,,., RDBM, SQL. OSS,, SQL,,. 3 10 10 OSS RDBMS SQL 11 10.1 OSS RDBMS............................ 11 10.1.1 PostgreSQL................................. 11 10.1.2 MySQL...................................