首頁 > 開發者 > PostgreSQL
一直想為 pg 做點貢獻，今天終於有空了， 於是寫一個PostgreSQL 9.13 入門的教程 ...
部署上可以移步這裡 ...
php 5.4.10 + nginx1.0.12 + PostgreSQL 9.1.3 源碼編譯自動化部署第二版
http://blog.csdn.net/qzier_go/article/details/7316510
-----------------------------------------------------------------------------------------
| System | CentOS 5.7
-----------------------------------------------------------------------------------------
| DB | PostgreSQL 9.13
-----------------------------------------------------------------------------------------
lnpp指令碼裡面已經做了些初始化的工作，例如：
su postgres -c "$PG_ROOT/bin/initdb -D $PG_ROOT/data && exit"
我們先輸入一些資料以供後面查詢(詳見後面補充）
-- Database: bpsimple-- DROP DATABASE bpsimple;CREATE DATABASE bpsimple WITH OWNER = postgres ENCODING = 'UTF8' TABLESPACE = pg_default LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8' CONNECTION LIMIT = -1;-- Table: item-- DROP TABLE item;CREATE TABLE item( item_id serial NOT NULL, description character varying(64) NOT NULL, cost_price numeric(7,2), sell_price numeric(7,2), CONSTRAINT item_pk PRIMARY KEY (item_id ))WITH ( OIDS=FALSE);ALTER TABLE item OWNER TO neil;以上我直接從pgadmin 3 上的sql pane copy 下來的，是我模擬器上的現有資料，所以以上語句沒有經過測試 !
http://www.postgresql.org/docs/9.1/interactive/index.html
有問題的話,可以手冊一下!
接下來我們還要對postgresql 進行一些配置已經進行外部的訪問 ...
先進行訪問授權 ...
#vim $PG_ROOT/data/pg_hda.conf
host bpsimple neil all trust
#vim postgresql.conf
listen_addresses = '*'
port = 5432
設定完監聽連接埠後我們重啟一下postgresql ...
su $PGUSER -c "$PGCTL stop -D '$PGDATA' -m fast"
su $PGUSER -c "$PGDAEMON -D '$PGDATA' &" >>$PGLOG 2>&1
具體環境變數視不同機子而定，好吧，主題開始，首先編寫一個pg類 ...
#vim ./pgphp/dbconn.php
<?phpclass dbconn { private $linkid; // PostgreSQL link identifier private $host; // PostgreSQL server host private $db; // PostgreSQL database private $user; // PostgreSQL user private $passwd; // PostgreSQL password private $result; // Query result private $querycount; //Total queries excuted /* Class constructor. Initializes the $host, $user, $passwd and $db fields. */ function __construct($host, $db, $user, $passwd) { $this->host = $host; $this->user = $user; $this->passwd = $passwd; $this->db = $db; } /* Connects to the PostgreSQL Database */ function connect() { try { $this->linkid = @pg_connect("host=$this->host dbname=$this->db user=$this->user password=$this->passwd"); if (!$this->linkid) throw new Exception("Could not connect to PostgreSQL server."); } catch (Exception $e) { die($e->getMessage()); } } /* Execute database query. */ function query($query) { try { $this->result = @pg_query($this->linkid, $query); if (!$this->result) throw new Exception("The database query failed."); } catch (Exception $e) { echo $e->getMessage(); } $this->querycount++; return $this->result; } /* Determine total rows affected by query. */ function affectedRows() { $count = @pg_affected_rows($this->linkid); return $count; } /* Determine total rows returned by query */ function numRows() { $count = @pg_num_rows($this->result); return $count; } /* Return query result row as an object. */ function fetchObject() { $row = @pg_fetch_object($this->result); return $row; } /* Return query result row as an indexed array. */ function fetchRow() { $row = @pg_fetch_row($this->result); return $row; } /* Return query result row as an associated array. */ function fetchArray() { $row = @pg_fetch_array($this->result); return $row; } /* Return total number of queries executed during lifetime of this object. Not required, but interesting nonetheless. */ function numQueries() { return $this->querycount; }}?>然後開始進行調用吧 ...
#vim dbtest.php
<html> <title> pgtest</title> <?php require_once 'dbconn.php'; $db = new dbconn("localhost", "bpsimple", "postgres", ""); $db->connect(); $db->query('SELECT * FROM item'); echo 'number of row:' . $db->numRows(); ?></html>接下來就可以訪問 http://hostname/pgphp/dbtest.php
它會輸入 item 的行數 ...
QQ:213572677 && linux c ph sql
Reference :
Beginning.PHP.and.PostgreSQL.8.From.Novice.to.Professional.Feb.2006
Beginning.Databases.With.PostgreSQL-From.Novice.To.Professional.2nd.Edition
PostgreSQL 9.1.3 docshttp://www.postgresql.org/docs/9.1/interactive/index.html
2012/4/10補充的sql初始化：
#su postgrespg$ /tmp/lnpp/pgsql/bin/createuser neil$ /tmp/lnpp/pgsql/bin/createdb bpsimple$ /tmp/lnpp/pgsql/bin/psql -U neil -d bpsimplecreate table item( item_id serial, description varchar(64) not null, cost_price numeric(7,2), sell_price numeric(7,2), CONSTRAINT item_pk PRIMARY KEY(item_id));INSERT INTO item(description, cost_price, sell_price) VALUES('Wood Puzzle', 15.23, 21.95);INSERT INTO item(description, cost_price, sell_price) VALUES('Rubik Cube', 7.45, 11.49);INSERT INTO item(description, cost_price, sell_price) VALUES('Linux CD', 1.99, 2.49);INSERT INTO item(description, cost_price, sell_price) VALUES('Tissues', 2.11, 3.99);INSERT INTO item(description, cost_price, sell_price) VALUES('Picture Frame', 7.54, 9.95);INSERT INTO item(description, cost_price, sell_price) VALUES('Fan Small', 9.23, 15.75);INSERT INTO item(description, cost_price, sell_price) VALUES('Fan Large', 13.36, 19.95);INSERT INTO item(description, cost_price, sell_price) VALUES('Toothbrush', 0.75, 1.45);INSERT INTO item(description, cost_price, sell_price) VALUES('Roman Coin', 2.34, 2.45);INSERT INTO item(description, cost_price, sell_price) VALUES('Carrier Bag', 0.01, 0.0);INSERT INTO item(description, cost_price, sell_price) VALUES('Speakers', 19.73, 25.32);2012/4/11 補充
有關有無密碼登入,主要是在pg_hda.conf裡面進行修改
host bpsimple neil all trust (無密碼登入）
host bpsimple neil all md5 ( 要求輸入密碼）local bpsimple neil all trust (無密碼登入）設定使用者密碼:
#su postgres
$/tmp/lnpp/pgsql/bin/psql -d dbname -U postgres -c "alter role postgres password ‘yourpassword’;"
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
