MySQL導入記録（０）

MySQLを導入するにあたってまずApacheを導入します。

だいぶ前にやったことあるんですが、やり方を書いておかなかったので忘れました。
で、やったらつまづくところで必ずつまずいて１時間オーバーを無駄に使う。

設定ファイルはhttpd.confで自分の場合/etc/httpd/conf/httpd.confにある。
ここでScriptAliasで置く場所の設定をして、
#AddHandler cgi-script .cgiのコメントを外す。（#を取る。）
CGIファイルを書く。だいたい以下のよう。

#!/usr/bin/perl

print "Content-type: text/plain\n";
print "\n";

print "test";

chmodでアクセス権を実行できるように変更。
これでApacheを起動して呼び出せばできるはず。
テスト用のtest-scriptというのが最初からあって、これが役立った。

 MySQL導入記録（１）

データベースサーバを構築してWebで使います。
データベースにはMySQL、WebサーバにはApache、CGIでPerlを使用。
Apacheの導入とCGIの設定は昨日やってある。
PerlはLinuxのディストリビューションで最初から使えた。
で、MySQLを導入。
場所はどこでも良いらしいが解説は/usr/localが多いのでここで展開
tar zxvf mysql-version.tar.gz
展開したディレクトリ名は長いので、
ln -s mysql-version mysql
くらいにしておく。
できたディレクトリに入って、
./configure --with-charset=ujis
とする。（日本語コードはEUCの場合ujis、ShiftJISの場合sjis）
make
make install
します。
/usr/local/mysql/bin/mysql_install_db
でデータベースを初期化。
所有者やグループの設定は、
groupadd mysql
useradd mysql -g mysql
chown -R root /usr/local/mysql
chgrp -R mysql /usr/local/mysql
chown -R mysql /usr/local/mysql/data
とする。
サーバの起動は、
/usr/local/mysql/support-scripts/mysql.server start
とサポートスクリプトを利用。
パスワードの設定は、
/usr/local/mysql/bin/mysqladmin -u root password hogehoge
などとします。
使う場合は、
/usr/local/mysql/bin/mysql -u root -p
でパスワードを入力してコンソールをmysqlの対話形式に変える。
mysql>
こんな感じ。
-u root というのはrootで入るという意味だが、
このrootはlinuxの管理者としてのrootとは違うようだ。
出る時には
exit
と入力。
サーバを止める時は
/usr/local/mysql/support-scripts/mysql.server stop
とする。

 MySQL導入記録（２）

さて、前回でMySQLは導入されたものとして、
次にPerlスクリプトからSQLサーバにアクセスします。
モジュールとして次の２つが必要。
DBI-version.tar.gz
Msql-Mysql-modules-version.tar.gz
２つとも tar zxvf で展開後、
perl Makefile.PL
make
make test
make install
とする。
testのときMySQLサーバを使っているようなのでMySQLサーバは起動しておきます。
Msql-Mysql-modulesのほうは途中で７つ質問してきますが、
最初は1であとはディフォルトを使っておく。
自分の場合、途中で cannot find -lz なんたらというエラーがでたが、
Web上のどこかの国の人がzlib-develを入れろと書いてたのでやったら通った。

最初はdatabaseというデータベースのtableというテーブルのデータ項目数を得る。
というようなコードを書いてブラウザに表示してみました。
以下のよう、

#!/usr/bin/perl

use DBI;

$query = "select count(*) from table";

$dsn = "DBI:mysql:database:localhost";

$dbh = DBI->connect($dsn, 'user', 'hogehoge');

$sth = $dbh->prepare($query);

$sth->execute();

@rows = $sth->fetchrow_array;
$row = $rows[0];


print "Content-type: text/plain\n";
print "\n";

print "test:";

print $row;

$dbh->disconnect();

手順としては
DBIの指定、MySQLの指定、データベース名の指定、サーバの場所ドメイン名
などを指定し、ユーザ名前、パスワードをつけてconnectする。
SQL文をprepareしてexecuteする。
fetchrow_arrayで情報を取り出す。
disconnectで切断。
というような手順。

 MySQL導入記録（３）

いろんな情報源をもとにコマンドの覚え書きを作成。
実際使って試してないので違ってるかもしれませんが、
違ってたらその都度直すということで・・・。

 １、起動と終了

接続

/usr/local/mysql/bin/mysql -u USER name -p

コマンドキャンセル

mysql>\c

終了

mysql>exit


 ２、データベース、テーブル、カラムの操作

データベース一覧を見る

mysql>show databases;

データベースの作成

mysql>create database DATABASE;

データベースの削除

mysql>drop database DATABASE;

データベースの切り替え

mysql>use DATABASE;

データベースを選択して、そのテーブル一覧を見る
カラムの追加

mysql>alter table TABLE change COLUMN_NAME add NEW_C int;
mysql>show tables from DATABASE;

テーブルの作成

mysql>create table TABLE(ID int, NAME varchar(16));

テーブル名の変更

mysql>alter table OLD_TABLE rename as NEW_TABLE;

テーブルの削除

mysql>drop table TABLE:

テーブルへのデータの追加

mysql>insert into TABLE set ID_C=1, NAME_C='taro', ADD_C='japan';

mysql>insert into TABLE set DATA(ID_C,NAME_C,ADD_C)
    -> values('1','taro','japan'),
    ->  ('2','hanako','japan');

テーブルへのデータ更新

mysql>updata TABLE set NAME_C='jiro' where ID_C=1;

テーブルへのデータ削除

mysql>delete from TABLE where ID_C=4;

ファイルからのデータ追加(区切りが " で改行が \n の場合)

mysql>load data infile "/root/data.lst" into table TABLE
    -> fields terminated by "," lines terminated by "\n";

カラムの構成を見る

mysql>show columns from DATABASE.TABLE;

カラムの追加

mysql>alter table TABLE change COLUMN_NAME add NEW_C int;

カラム名、データ型の変更

mysql>alter table TABLE change OLD_NAME NEW_NAME int;

カラムの削除

mysql>alter table TABLE drop COLUMN_NAME;


 ３、カラムの属性

データ型

tinyint:整数型
int:整数型
int unsigned:正の整数
bigint:整数型
char:３文字以下の文字列
varchar:４文字以上の文字列
text:可変長文字列(最大長65535バイト)
float:浮動小数
double:倍精度浮動小数
date:日付型 (0000-00-00)
time:時刻型 (00:00:00)

列挙型 (定義されたデータ１つしか入力できない)

mysql>create table TABLE(ID int, TYPE enum("A", "B", "C"));

列挙型 (定義されたデータしか入力できない 複数可)

mysql>create table TABLE(ID int, TYPE set("A", "B", "C"));

nullを許可しない

mysql>create table TABLE(ID int, NAME varchar(16) not null);

デフォルト値の設定

mysql>create table TABLE(ID int, NAME varchar(16) default "無し");

プライマリキーの設定(データの重複を許可しない)

mysql>create table TABLE(ID int primary key, NAME varchar(16));

プライマリキーの追加

alter table TABLE add primary key(ID);

プライマリキーの解除

alter table TABLE drop primary key(ID);

オートインクリメントの設定(自動で数字が１づつ増える)

mysql>create table TABLE(ID int auto_increment primary key, NAME varchar(16));

数字に０をあてる

mysql>create table TABLE(ID int zerofill, NAME varchar(16));

インデックスをつける

mysql>create table TABLE(ID int not null, NAME varchar(16), index INDEX(ID));

インデックスの削除

mysql>drop index INDEX on TABLE;


 ４、データの参照

指定したテーブルのすべてのデータを参照

mysql>select * from TABLE;

指定したテーブルの指定したカラムのデータを参照

mysql>select ID,NAME from TABLE;

テーブルのデータを結合して参照

mysql>select * from DATA,TYPE where ID_C=ID

数値の比較 ( >, <, >=, <=, =, <> が利用可能)

mysql>select * from TABLE where ID_C<5
mysql>select * from TABLE where ID_C<>1

論理演算 ( and, &, or, ||, not が利用可能)

mysql>select * from TABLE where ID_C > 2 and ID_C < 5;
mysql>select * from TABLE where not (ID_C=5);

ワイルドカード検索

mysql>select * from TABLE where NAME like CD"tanaka %";

エスケープ文字:%=\% \=\\

nullの検索 (is null, is not null が利用可能)

mysql>select * from TABLE where NAME is not null;

同じレコードは２つ表示しない

mysql>select distinct(NAME) from TABLE;

表示数を制限する (１ページに１０件の２ページ目を表示

mysql>select * from TABLE limit 1,10;

グループ化 (関数などで利用)

mysql>select GROUP,avg(POINT) from TABLE group by GROUP;

検索されたデータに対する条件比較

mysql>select * from TABLE having TYPE="A";

昇順

mysql>select * from TABLE order by ID;

降順

mysql>select * from TABLE order by ID desc;


メモ：group by→having→order byの順であること。


 ５、関数

平均値:avg()
合計:sum()
最大値:max()
最小値:min()
レコード数:count()
レコード長:length()
現在時刻:now()

 MySQL導入記録（４）

MySQLのユーザ管理について。
管理はrootが行うが、このrootはlinux管理者としてのrootとは別もの。
パスワードは、

/usr/local/mysql/bin/mysqladmin -u root -p password PASSWORD

と入力。
変更の場合は旧パスワードを聞いてくる。
ユーザの追加や変更はmysqlというデータベースで変更できる。
管理の操作はSQL文でmysqlというデータベースをさわる。
パスワードなどもここで変更。

ユーザに権限を与える場合、

mysql>grant select on DATABASE.TABLE to nm@localhost;

これでDATABASEというデータベースのTABLEというテーブルにselectを許可する。

mysql>flush privileges;

とやると変更が反映される。

削除は、

mysql>revoke select on DATABASE.TABLE to nm@localhost;

とする。