PHP & MySQL - データベースの実践(バックアップ、リストア、インデックス)

開発環境

OS X Lion - Apple(OS)
Apache (Web Server)
PHP (サーバーサイドプログラミング言語)
MySQL (データベース)
TextWrangler(Text Editor) (BBEditの無料機能制限版、light版)

『初めてのPHP & MySQL 第2版』(Michele E. Davis、Jon A. Phillips 著、西沢直木訳、オライリー・ジャパン、2008年、ISBN978-4-87311-365-4)の8章(データベースの実践)の問題を解いてみる。

問7-1, 2.

blogデータベースはないので、前章で作成したデータベース、storeをバックアップ、削除、リストア。

入出力結果(Terminal)

$ mysqldump -u root -p store > my_backup_of_store.sql
Enter password: 
$ cat my_backup_of_store.sql
-- MySQL dump 10.13  Distrib 5.5.18, for osx10.6 (i386)
--
-- Host: localhost    Database: store
-- ------------------------------------------------------
-- Server version 5.5.18

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `months`
--

DROP TABLE IF EXISTS `months`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `months` (
  `month_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  `days` int(11) DEFAULT NULL,
  PRIMARY KEY (`month_id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `months`
--

LOCK TABLES `months` WRITE;
/*!40000 ALTER TABLE `months` DISABLE KEYS */;
INSERT INTO `months` VALUES (1,'January',31),(2,'February',28),(3,'March',31),(4,'April',30),(5,'May',31),(6,'June',30),(7,'July',31),(8,'August',31),(9,'September',30),(10,'October',31),(11,'November',30),(12,'December',31);
/*!40000 ALTER TABLE `months` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2012-03-03 15:18:55
$ mysql -u kamimura -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6447
Server version: 5.5.18 MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sample_store       |
| store              |
| test               |
+--------------------+
6 rows in set (0.02 sec)

mysql> drop database store;
Query OK, 1 row affected (0.32 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sample_store       |
| test               |
+--------------------+
5 rows in set (0.00 sec)

mysql> quit;
Bye
$ mysql -u kamimura -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6449
Server version: 5.5.18 MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sample_store       |
| test               |
+--------------------+
5 rows in set (0.00 sec)

mysql> create database store;
Query OK, 1 row affected (0.00 sec)

mysql> quit
Bye
$ mysql -u root -p -D store < my_backup_of_store.sql
Enter password: 
$ mysql -u kamimura -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6451
Server version: 5.5.18 MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sample_store       |
| store              |
| test               |
+--------------------+
6 rows in set (0.00 sec)

mysql> use store;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> describe months;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| month_id | int(11)     | NO   | PRI | NULL    | auto_increment |
| name     | varchar(10) | YES  |     | NULL    |                |
| days     | int(11)     | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> select month_id,name,days from months;
+----------+-----------+------+
| month_id | name      | days |
+----------+-----------+------+
|        1 | January   |   31 |
|        2 | February  |   28 |
|        3 | March     |   31 |
|        4 | April     |   30 |
|        5 | May       |   31 |
|        6 | June      |   30 |
|        7 | July      |   31 |
|        8 | August    |   31 |
|        9 | September |   30 |
|       10 | October   |   31 |
|       11 | November  |   30 |
|       12 | December  |   31 |
+----------+-----------+------+
12 rows in set (0.00 sec)

mysql> quit
Bye
$

問7-3.

テーブルにインデックスを作成するメリットは、検索が速くなること。デメリットはその分ディスク容量が必要になる、メンテナンスが必要になる、読み取りに時間がかかるようになること。

併せて読んでいる書籍。

独習SQL 第2版黒石博明 (著), 長谷川裕行 (監修)
独習PHP 第2版山田祥寛 (著)

Pythonの学習が1周したら上記の2冊を順に取り組む計画。それまではひたすら復習！

Kamimura's blog

2012年3月3日土曜日

PHP & MySQL - データベースの実践(バックアップ、リストア、インデックス)

0 コメント:

コメントを投稿