Taro13-006linux4.jtdc

Transcription

1 2-4 Linux におけるデータベースアクセス 1 目的 PostgreSQLは,Linuxで利用可能なリレーショナルデータベースマネージメントシステム ( RDBMS: 以下 データベースシステム という ) である 本格的なデータベースシステムは, 一般に高価なものが多いが,PostgreSQLはフリーソフトウェアとして配布されている このPostgreSQLを使用してLinux 上でデータベースシステムを構築するための基礎的な知識や技術を習得する 2 使用機器及びソフトウェア WindowsXPのクライアントパソコン Linux(Fedora Core3) のサーバパソコン PostgreSQL 3 関連知識 PostgreSQLは, どこでも入手できるオープンソースデータベースで, ほとんどすべての SQL の構文をサポートしている 各種言語 (PHP,C,C++,Java,Perl 等 ) と連係し,DB( データベース ) サーバを構築できる PostgreSQLでは, ユーザ root とは別に, データベースシステム全体を管理する PostgreSQL 管理者 (postgres) が存在する また, それぞれのデータベースを管理する管理者 (wwwadmin 等 ) が存在する また,Webサーバの Apache と連携して利用することができる PostgreSQLのデータベース管理体系を図 1に示す 図 1 PostgreSQLのデータベース管理体系 postgres wwwadmin DB 管理者 2 DB 管理者 3 apache (nobody) data base data base data base WWW サーバ DB サーバ -1-

2 PostgreSQLの利用手順は, 次の図 2に示す 図 2 PostgreSQLの利用手順 (1) PostgreSQL 管理者の設定 (2) データベース管理者の登録 (3) データベース及びテーブルの作成 (4) SQL コマンドを使ったデータの処理 4 実習 例題 1 PostgreSQL を使用して, 表 1のようなデータベースシステムを構築する ただし, データベース管理者 wwwadmin, データベース名 test1db, テーブル名 table1 とする 表 1 テーブル table1 の内容 項目名 id nam pre ag int varchar(20) varchar(10) text 1 田中一郎千葉県 30 データ型 2 加藤次郎 北海道 20 3 岡田三郎 京都府 25 4 山田史郎 埼玉県 28 5 小林五郎 東京都 40 (1) PostgreSQL 管理者の設定 PostgreSQLでは,rootとは別に, データベースシステム全体を管理する PostgreSQL 管理者 が存在する 通常は, この権限を持つ管理者として, postgres を登録する Linux(Fedora Core3) では,PostgreSQLのインストール時に, 自動的に管理者として, postgresが作成されるが, パスワードについては設定が行われていないため, 最初にroot 権限でpostgresのパスワードを設定する ここでは, 便宜上, パスワードを pwdpg に設定する 1 パスワードの設定 空いている画面で右クリックし,[ 端末を開く ] を選択する 次のように入力し, それぞれ Enter キーを押す パスワードを設定する ( ユーザ postgres ) -2-

3 パスワードを入力する パスワードを確認 入力する パスワードを設定する -3- root]# passwd postgres パスワードを入力する Changing password for user postgres New password: pwdpg BAD PASSWORD: it is too short パスワードを確認 Retype new password : pwdpg 入力する password: all authentication tokens updated successfully 2 PostgreSQLデータベースシステムの起動 ) [ 端末 ] に次のように入力し, Enter キーを押す ( 再起動する場合は, /etc/rc.d/init.d/postgresql restart と入力する ) [root@epc** root]# /etc/rc.d/init.d/postgresql start データベースを初期中 : [ OK ] Starting postgresql service: [ OK ] 3 Linux 起動時のPostgreSQL 自動起動設定 [ 端末 ] に次のように入力し, Enter キーを押す [root@epc** root]# chkconfig postgresql on (2) データベース管理者の登録一般に, データベースシステムでは, 複数のデータベースがその管理下にある ログインしたpostgresは,PostgreSQLの管理者であり, データベースシステムに対してすべての権限を持っている 管理体系の理解と, データベースシステムの事故防止のために, 新たにユーザ wwwadmin を作成し, データベース管理者として登録する 登録には,PostgreSQLの createuser コマンドを使用する 1 ユーザ wwwadmin の作成( パスワードを pwdwww とする ) [ アプリケーション ] から [ システム設定 ], [ ユーザーとグループ ] をクリックする [ ファイル ], [ ユーザーを追加 ] をクリックする 新規ユーザー作成 ウインドウで, 次のように入力し,[OK] ボタンをクリックする ユーザ名 :wwwadmin 氏名 :WWWADMIN パスワード :pwdwww パスワードの確認 :pwdwww ユーザー wwwadmin の追加方法については, 補助資料 GNOMEの基本操作 の 4ページ (6) ユーザマネージャ を参照

4 2 データベース管理者の登録 ( ユーザ postgres は, インストール時に登録されている ) [ 端末 ] に次のように入力し, それぞれ Enter キーを押す ユーザ ID を postgres に変更する データベース管理者 wwwadmin を登録する ユーザIDを root に戻す ( Ctrl キーと D キーを同時に押す ) に変更する [root@epc** root]# su postgres データベース管理者 bash-3.00$ createuser wwwadmin を登録する Enter name of user to add: wwwadmin Shall the new user be allowed to create databases? (y/n) y Shall the new user be allowed to create more new users? (y/n) n CREATE USER bash-3.00$ [Ctrl]+[D] ユーザIDを root [root@epc** root]# に戻す (3) データベース及びテーブルの作成ユーザ wwwadmin でログインしてデータベース及びテーブルを作成する データベースの作成には,PostgreSQLの createdb コマンドを使用する 1 データベース test1db の作成作成したデータベースの保守 管理は,psqlインタプリタで行う [ 端末 ] に次のように入力し, それぞれ Enter キーを押す ユーザ ID を wwwadmin に変更する データベース名 test1db を作成する test1db を psql インタプリタで起動する [root@epc** root]# su wwwadmin ユーザIDを postgres ユーザIDを wwwadmin に変更する データベース名 test1db [wwwadmin@epc** root]# CREATEDB DATABASE createdb test1db を作成する test1db をpsqlイン [wwwadmin@epc** root]# psql test1db タプリタで起動する Welcome to psql, the PostgreSQL interactive terminal. Type: \ copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands \g or terminate with semicolon to execute query \q toquit test1db=> -4-

5 psqlインタプリタでは,psql 独自のコマンドや,SQLコマンドの実行によって, データベ ースを作成, 保守 管理することができる 2 テーブル table1 の作成 psqlインタプリタを使って, データベース名 test1db に, 表 1のテーブル名 table1 を作成し, データを入力する 表 1 テーブル table1 の内容 項目名 id nam pre ag int varchar(20) varchar(10) text 1 田中一郎千葉県 30 データ型 2 加藤次郎 北海道 20 3 岡田三郎 京都府 25 4 山田史郎 埼玉県 28 5 小林五郎 東京都 40 PostgreSQLでは,3 種類のコマンド体系が利用可能である UNIXのコマンドラインで入力を行うコマンド利用可能なコマンドは,/usr/local/pgsql/binディレクトリにある createuser,destroyuser,createdb,dropdb,psqlなどがある man コマンドで, コマンドの内容を閲覧できる psqlコマンド \ ではじまるコマンドで,psqlインタプリタ内で実行する \? でコマンドの一覧が表示される SQLコマンド Structured Query Language. 各種のリレーショナルデータベースにアクセスするために規定された共通の制御言語である \ h でSQLコマンドの一覧が表示される [ 端末 ] に次のように入力し, それぞれ Enter キーを押す table1 のデータ構造( 項目名, データ型 ) を設定する SQLコマンド create table コマンドを使ってテーブルを作成する データ型の区切りは,( コンマ ),SQL 文の末尾には ;( セミコロン ) を入力する table1 のデータ型を表示する \ d table1 コマンドでテーブルのデータ構造が表示される (PostgreSQLのデータ型を表 2に示す ) test1db=> create table table1 ( test1db(> id int, test1db(> nam varchar(20), test1db(> pre varchar(10), test1db(> ag text test1db(> ); CREATE TABLE test1db=> \d table1 table1 のデータ構造を設定する table1 のデータ型を表示する \ は \ キーを押す -5-

6 Tabel "public.table1" Column Type Modifiers id integer nam varchar varying(20) pre varchar varying(10) ag text (10) test1db=> 表 2 PostgreSQLのデータ型 ( 主なもの ) データ型 長さ 説明 備考 int2 2バイト 整数型 ~ int 4バイト 長整数型 ~ int4 char 1バイト 1 文字 char(n) (4+n) バイト n 文字の固定長 n 文字に満たない場合空白 text (4+x) バイト 可変長 varchar(n) (4+n) バイト 可変長 最大長を指定 入力ミス等により CREATE TABLE が表示されなかった場合 \ q を入力し, Enter キーを押し,psqlインタプリタを終了する dropdb test1db を入力し, Enter キーを押し, データベースを削除する 再度 createdb test1db を入力し, Enter キーを押し, データベースを作成する psql test1db を入力し, Enter キーを押し,psqlインタプリタを起動する データ型を再度入力する (4) SQLコマンドを使ったデータの処理 1 データの入力 SQLコマンド insert コマンドを使って, データを入力する ( 漢字変換入力モードは, Ctrl キーとスペースキーを同時に押す ) [ 端末 ] に次のように入力し, Enter キーを押す test1db=> insert into table1 values ('1',' 田中一郎 ',' 千葉県 ','30'); INSERT 同様に, 残りを入力する test1db=> insert into table1 values ('1',' 加藤次郎 ',' 北海道 ','20'); INSERT test1db=> insert into table1 values ('2,' 岡田三郎 ',' 京都府 ','25'); INSERT test1db=> insert into table1 values ('3,' 山田史郎 ',' 埼玉県 ','28'); INSERT test1db=> insert into table1 values ('4',' 小林五郎 ',' 東京都 ','40'); INSERT

7 \ q を入力し, Enter キーを押し,psqlインタプリタを終了する test1db=> \q [wwwadmin@epc** root]$ 2 データの表示 SQLコマンド select コマンドを使って, テーブル内のデータを表示する selectコマンド selectコマンドは, リレーショナルデータベースからデータを抽出する場合にする 形式 select 項目名 from テーブル名 ; 入力例 jusyo=>select * from main; 使用 意味テーブルから全項目を抽出する * は全項目を抽出する [ 端末 ] に次のように入力し, Enter キーを押す [wwwadmin@epc** root]$ psql test1db test1db=> select * from table1; id nam pre ag 田中一郎 千葉県 30 2 加藤次郎 北海道 20 3 岡田三郎 京都府 25 4 山田史郎 埼玉県 28 5 小林五郎 東京都 40 (5 rows) 演習 1 PostgreSQL を使用して, 表 3のようなデータベースシステムを構築する ただし, データベース管理者 wwwadmin, データベース名 testdb, テーブル名 table1 とする 表 3 テーブル table1 の内容 id nam pre ag int varchar(20) varchar(10) text 1 山田花子 滋賀県 20 2 伊藤直子 長崎県 50 3 中村光一 岩手県 35 4 山本和生 高知県 44 5 鈴木洋子 埼玉県 25 6 高橋桃子 北海道 55-7-

8 例題 2 SQLコマンド を使用して, 例題 1 で作成したテーブル table1 のデータの抽出, 更新, 削除を行い, 実行結果を表示する (1) test1db の起動 test1db をpsqlインタプリタで起動する [wwwadmin@epc** root]# psql test1db Welcome to psql, the PostgreSQL interactive terminal. Type: \ copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands \g or terminate with semicolon to execute query \q toquit test1db=> (2) SQLコマンドを使ったデータの処理 SQLコマンドを使って, 作成した表のデータを処理する 1 特定項目のデータ表示特定項目のデータのみを表示させたい場合は,selectの後に表示させたい項目名を記述する また, ある条件に合致するデータのみを表示する場合は,where 句を併用する where 句コマンド where 句は, 抽出する条件を示すことにより, 目的とするデータを抽出する 形式 select 項目名 from テーブル名 where 抽出条件 ; 入力例 jusyo=>select name from main where code <= 10; 意味テーブルmainから,codeが10 番以内のnameを抽出する 例として, テーブル table1 から, 項目名 preが 北海道 の項目名 idとnamを抽出する [ 端末 ] に次のように入力し, Enter キーを押す test1db=> select id,nam from table1 where pre = ' 北海道 '; id nam 加藤次郎 (1 row) 2 データの更新データの更新には,updateコマンドを使用する updateコマンド updateコマンドは, 変更 追加したデータを更新する -8-

9 形式 update テーブル名 set 代入式 ; 入力例 jusyo=>updatese main set pre = ' 青森県 '; 意味テーブルmainの項目名 preを 青森県 に変更する 例として, テーブル table1 から, 項目名 namが 加藤次郎 の項目名 preを抽出し, 青森県 に変更する [ 端末 ] に次のように入力し, それぞれ Enter キーを押す test1db=> update table1 set pre = ' 青森県 ' where nam = ' 加藤次郎 '; UPDATE 1 test1db=> select * from table1 where nam = ' 加藤次郎 '; id nam pre ag 加藤次郎 青森県 20 (1 row) 3 データの削除データの削除には, delete コマンドを使用する where 句を省略すると表のデータがすべて削除されるので注意すること deleteコマンド deleteコマンドは, データを削除する 形式 delete from テーブル名 where 抽出条件 ; 入力例 jusyo=>delete from main where pre=' 青森県 '; 意味テーブルmainの項目名 preが 青森県 のデータを削除する 例として, テーブル table1 から, 項目名 nam が 山田史郎 のデータを削除する test1db=> delete from table1 where nam = ' 山田史郎 '; DELETE 1 test1db=> select * from table1; ( 全データの表示 ) test1db=> \q [wwwadmin@epc** root]$ -9-

10 演習 2 SQLコマンド を使用して, 例題 1 で作成したテーブル table1 から, ア~エの条件に合うようにそれぞれデータの抽出, 更新, 削除を行い, 実行結果を表示する アテーブル table1 から,namとagを抽出する イテーブル table1 から,preが 千葉県 のidとnamを抽出する ウテーブル table1 から,namが 岡田三郎 のpreを抽出し,preを 北海道 に変更する エテーブル table1 から,namが 加藤次郎 のデータを削除する -10-

11 例題 3 表 4に示すMicrosoft Accessのデータファイルを利用したPostgreSQLのデータベースシステムを構築する ただし, データベース管理者 wwwadmin, データベース名 jusyo, テーブル名 main とする 表 4 Accessのデータベース code(int) name(22) furigana(20) zip(10) address(56) konyugaku(int) 1 代々木 一 よよぎ 東京都渋谷区代々木 青山 二郎 あおやま 東京都港区南青山 FFF 相柄 智嗣 さがら 岡山県倉敷市早高 見城 仁志 みしろ 兵庫県三木市宿原 千崎 貴緒 ちざき 愛知県知立市大手町 東海林 東 しょうじ 福岡県北九州市八幡西区藤原 税所 寿子 ぜいしょ 三重県志摩郡磯部町穴川 仙石 成夫 せんごく 栃木県河内郡河内町大字立伏 大久保 里 おおくぼ 石川県松任市千代の町 留萌 里子 るもい 大阪府池田市渋谷町 : : : : : : (1) データファイルの作成 PostgreSQLでMicrosoft Accessのデータファイルを利用するためには, テキスト形式のデータファイル access.txt が必要になる Microsoft Accessのデータファイル access.mdb からテキスト形式のデータファイル access.txt をエクスポートする手順は, 次のように行う 図 3 Accessの起動 1 Accessを起動する ( 図 3) 2 メニューバーの[ ファイル] より 開く ] を選択し, ファイル access.d bm を選択し,[ 開く ] ボタンをクリックする 3 メニューバーの[ ファイル] より エクスポート ] を選択する 4 保存先を選択し, ファイルの種類 テキストファイル ] を選択し, ファイル名 access を入力し,[OK] ボタンをクリックする -11-

12 5 エクスポートウィザード ( 図 4) で [ 区切り記号付き- ] 選択し,[ 設定 ] ボタンをクリックする 図 4 エクスポートウィザード 6 フィールド区切り記号 { タブ }, 文字列の引用符 { なし }, コードページ Unicode(UTF-8 ) を選択し, [OK] ボタンをクリックする ( 図 5) 図 5 エクスポート定義 [ 次へ ] ボタンをクリックする [ 次へ ] ボタンをクリックする [ 完了 ] ボタンをクリックする (2) データベース及びテーブルの作成 1 データベース jusyo の作成 [ 端末 ] に次のように入力し, それぞれ Enter キーを押す [wwwadmin@epc** root]$ createdb jusyo CREATE DATABASE [wwwadmin@epc** root]$ psql jusyo Welcome to psql 7.4.6, the PostgreSQL interactive terminal. Type: \ copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands \g or terminate with semicolon to execute query \q toquit jusyo=> 2 テーブル main の作成 (PostgreSQLのデータ構造を表 5に示す ) -12-

13 [ 端末 ] に次のように入力し, それぞれ Enter キーを押す main のデータ構造 ( 項目名, データ型 ) を設定する main のデータ型を表示する \ d main コマンドでテーブルのデータ構造を確認する (PostgreSQLのデータ型を表 5に示す ) 表 5 テーブル main のデータ構造項目名 code name furigana zip address konyugaku データ型 int2 varchar(22) varchar(20) varchar(10) varchar(56) int4 jusyo=> jusyo(> jusyo(> jusyo(> jusyo(> jusyo(> jusyo(> jusyo(> CREATE create table main ( code int2, name varchar(22), furigana varchar(20), zip varchar(10), address varchar(56), konyugaku int4 ); TABLE jusyo=> \d main Table "public.main" Column Type Modifiers code smallint name character varying(22) furigana character varying(20) zip character varying(10) adress character varying(56) konyugaku integer jusyo=> \q 3 テキスト形式データファイルの取り込み CD-ROM 内 ( フォルダ \data\dbserver\access ) のファイル access.txt を取り込み, PostgreSQLで読み込み可能なファイルに変換する 変換ファイルは /home/wwwadmin/ 内に作成する tr は, 改行コード変換のコマンドである [ 端末 ] に次のように入力し, それぞれ Enter キーを押す [wwwadmin@epc** root]$ /home/ wwwadmin/table.txt tr -d '\ 015' < /mnt/cdrom/data/dbserver/access/access.txt > 4 psqlインタプリタ内でデータをコピー \copy コマンドを使ってテキスト形式データファイル table.txt をテーブル main にコピーする コピー後,selectコマンドでテーブル main のデータを確認する [wwwadmin@epc** root]$ psql jusyo -13-

14 jusyo=> \copy main from /home/wwwadmin/table.txt \. jusyo=> select * from main; 1 ~ ~ ~ ~ (286 rows) jusyo=> \q [wwwadmin@epc** root]$ su root Password:pwdroot 代々木一 よよぎ 東京都渋谷区代々木 演習 3 フォルダ \data\dbserver\access のテキストファイル ensyu3.txt よりPostgreSQL を利用して, データベースシステムを構築する ただし, データベース管理者 wwwadmin, データベース名 gakkou, テーブル名 main1, とする 表 6 テーブル main1 のデータ構造 項 目 名 code name sei school デ ー タ 型 int varchar(20) varchar(4) varchar(50) -14-