postgreSQL 使用,及 php 连接 使用

时间:2022-07-15 22:50:03

    最近由于mysql服务器压力过大,老大决定在本地测试一下postgreSQL的性能,

从网上淘了两篇入门使用的文章学习。感谢原创作者。

转载地址:   http://hi.baidu.com/%BD%AD%C3%E6%C1%B0%E4%F4/blog/item/9256c7b2a32833b7d9335a41.html

  http://www.gaobo.info/read.php/394.htm

(1)postgresql的基本使用

 

导出数据库的命令:

pg_dump -Upostgres -d databasename > database_back.sql

 

导入数据库的命令:

psql -Upostgres -f database_back.sql -d databasename

 

 

1.测试数据库启动与否定命令是:

psql -Upostgres postgres

2.创建database的命令是

createdb -Upostgres -Eunicode databasename

databasename是数据库的名称,根据个人需要更改成子需要的名称。

3备份数据库的命令是:

pg_dump -b -c -Eunicode -o -O -Fc -Z9 -Upostgres databasename > databasename.dmp

4.恢复数据库的命令是:pg_restore -dap -i -v -c -O -Upostgres -Fc databasename.dmp

5.删除数据库的命令是:dropdb -Upostgres databasename

6.建立数据库的命令是:createdb -Eunicode -Upostgres databasename

7.修改记录的命令是:

psql database -Upostgres

update "public"."tb_apmacaddr" set "lastcomunicationtime"= '2009-08-11 19:12:29';

8.安装数据库和测试基本的命令是:

apt-get install postgresql-8.3

psql -Upostgres postgres

 

(2) 使用php连接postgreSQL

 

主要有以下四种方法:

1、用adodb联结:

<?php

Session_Start();

$user=$_POST["user"];

$pwd=$_POST["pwd"];

if(isset($user)) {

$_SESSION["user"]=$user;

$_SESSION["pwd"]=$pwd;};

session_write_close ();

error_reporting(0);

include_once('/usr/share/php/adodb/adodb-errorhandler.inc.php');

include_once('/usr/share/php/adodb/adodb.inc.php');

$db =&ADONewConnection('postgres'); # eg 'mysql' or 'postgres'

// $db->debug = true;

$host='xxx.xxxx.xxx';

$port='5432';

$dbname='test';

$user=$_SESSION["user"];

$password=$_SESSION["pwd"];

$conn_string = "host=$host dbname=$dbname user=$user password=$password port=$port";

$db->Connect($conn_string) or die("can't connect!");

?>

2、直接联结:

<?php

//dl('pgsql.so');

Session_Start();

$user=$_POST["user"];

$pwd=$_POST["pwd"];

if(isset($user)) {

$_SESSION["user"]=$user;

$_SESSION["pwd"]=$pwd;};

session_write_close ();

echo '<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">';


$host='xxxx.xxxx.xxxx';

$port='5432';

$dbname='test';

$conn_string = "host=$host dbname=$dbname user=$user password=$password port=$port";

$dbconn = pg_connect($conn_string) or die("Could not connect");

if ($dbconn){echo 'ok';}else{echo 'fales';};

print_r(pg_version());

$query='select * from bzzl';

$rs=pg_query($dbconn,$query);

$fd=pg_fetch_assoc($rs);

echo "\n";

$myfd=$fd['dw'];

echo $myfd;

pg_close($dbconn);

?>

 

3、使用pdo联结:

<?php

// Connect to an ODBC database using driver invocation

$htmm='<html>';

$htmm.='<head>';

$htmm.="<meta http-equiv='Content-Type' content='text/html; charset=UTF-8'>";

$htmm.='<title>产品基础数据</title>';

$htmm.='</head>';

echo $htmm;

$host='xxx.xxxx.xxxx';

$port='5433';

$dbname='test';

$user='xxxxxx';

$password='xxxxxxxxxxxxx';

//$cc = "host=$host,dbname=$dbname,port=$port";

$dsn = "pgsql:host=$host port=$port dbname=$dbname";



try {

$dbh = new PDO($dsn, $user, $password);

} catch (PDOException $e) {

echo 'Connection failed: ' . $e->getMessage();

}

//echo "<p>";

echo '<table border=1 borderwidth=0>';

$sql='select * from cpjcsj';

print '<tr><th>产品代码</th><th>产品代号</th><th>产品名称</th></tr>';

foreach ($dbh->query($sql) as $row) {

print '<tr><td>'.$row['cpdm'].'</td>';

print '<td>'.$row['cpdh'] . '</td>';

print '<td>'.$row['cpmc'] . '</td></tr>';

}

// echo "</p>";

echo "</table>";

$dbh->disconnect;

?>


4、使用pear-db联结

<?php

require_once 'DB.php';


$dsn = array(

'phptype' => 'pgsql',

'username' => 'xxxxxxxx',

'password' => 'xxxxxxxxxx',

'hostspec' => 'xxxx.xxxx.xxxx',

'database' => 'xxxxx',

'port' => '5433'

);


$options = array(

'debug' => 2,

'portability' => DB_PORTABILITY_ALL,

);


$db =& new DB;

$conn=$db->connect($dsn, $options);

if (PEAR::isError($db)) {

die($db->getMessage());

}else{echo 'dbconnect ok';}

$sql='select * from cpjcsj;';

$sth=&$conn->prepare($sql);

$res=&$conn->execute($sth);

if (PEAR::isError($res)) {

die($res->getMessage());}

while ($res->fetchInto($row)) {

// Assuming DB's default fetchmode is DB_FETCHMODE_ORDERED

echo $row[1] . "\n";

}