|
1 安装及配置1.1 安装在windowXP下安装 PostgreSQL 9.1: Installation Directory àC:\Program Files\PostgreSQL\9.1 Data Directory à D:\PostgreSQL\9.1\data Password à postgres Port à5432 Locale à [Default locale] 1.2 配置1.1.1远程连接:修改文件：D:\PostgreSQL\9.1\data\pg_hba.conf, 增加: ********************************** # IPv4 local connections: host all all 127.0.0.1/32 md5 host all all 192.168.0.0/24 md5********************************** 使得ip在 192.168.0.0 到 192.168.0.255 之间的客户端可以通过密码访问数据库. 修改文件：D:\PostgreSQL\9.1\data\postgresql.conf，确保: ********************************** listen_address = '*' ********************************** 1.1.2 表空间访问权限创建表空间文件夹: E:\PostgreSQL\mytablespace，修改文件夹权限，使postgres用户有读写权限。 2 创建数据库脚本2.1 创建用户及表空间脚本CreateTableSpace.sql ********************************** CREATE ROLE myuser LOGIN PASSWORD 'mypassword' SUPERUSER INHERIT NOCREATEDB NOCREATEROLE REPLICATION; CREATE TABLESPACE mytablespace OWNER myuser LOCATION 'E:/PostgreSQL/mytablespace';********************************** 2.2 创建数据库脚本CreateTableSpace.sql ********************************** CREATE DATABASE mydb WITH OWNER = myuser ENCODING = 'UTF8' TABLESPACE = mytablespace LC_COLLATE = 'Chinese_People''s Republic of China.936' LC_CTYPE = 'Chinese_People''s Republic of China.936' CONNECTION LIMIT = -1;********************************** 2.3 建表脚本********************************** CREATE TABLE mytable ( id integer unique not null, value varchar(50) ); ALTER TABLE mytable OWNER TO myuser;********************************** 2.4 导入缺省数据Insert.sql ********************************** insert into mytable (id, value) values (1, 'V1'); insert into mytable (id, value) values (2, 'V2'); ********************************** 2.5 批处理文件********************************** set PGUSER=postgres set PGPASSWORD=postgres c:\Progra~1\PostgreSQL\9.1\bin\psql.exe -f CreateTableSpace.sql c:\Progra~1\PostgreSQL\9.1\bin\psql.exe -d mydb -f CreateTable.sql c:\Progra~1\PostgreSQL\9.1\bin\psql.exe -d mydb -f Insert.sql**********************************
3 sql语句与Oracle的区别 3.1 数据类型 Oracle | PostgreSQL | VARCHAR2 | VARCHAR （character varying） | Long、 CLOB | TEXT | DATE | DATE/TIME/TIMESTAMP | NUMBER | SMALLINT/INTEGER/BIGINT/NUMERIC/REAL/DOUBLE PRECISION | BLOB | SMALLINT/INTEGER/BIGINT/NUMERIC/REAL/DOUBLE PRECISION | sysdate | now()、O_TIMESTAMP(CURRENT_TIMESTAMP,'YYYY-MM-DD HH24:MI:SS')、CURRENT_TIMESTAMP |
Tips: PostgreSQL 中字段名区分大小写，为保证兼容行，强烈建议脚本中的字符均用小写，这样在Sql语句中将忽略大小写。 3.2 方法 Function | Returns | Example | to_char(timestamp, text) | text | to_char(timestamp 'now','HH12:MI:SS') | to_char(interval, text) | text | to_char(interval '15h 2m 12s','HH24:MI:SS') | to_char(int, text) | text | to_char(125, '999') | to_char(double precision, text) | text | to_char(125.8, '999D9') | to_char(numeric, text) | text | to_char(numeric '-125.8', '999D99S') | to_date(text, text) | date | to_date('05 Dec 2000', 'DD Mon YYYY') | to_timestamp(text, text) | timestamp | to_timestamp('05 Dec 2000', 'DD Mon YYYY') | to_number(text, text) | numeric | to_number('12,454.8-', '99G999D9S') |
3.3 sql | Oracle | PostgreSQL | Constraint | alter table schema.prefix_info add (
constraint pk_prefix_info primary key (info_id)); | alter table schema.prefix_info add constraint prefix_info_pkey primary key(info_id); | Default Maximun in sequence | create sequence prefix_info_sequence
increment by 1
start with 582
minvalue 1
maxvalue 9999999999999999999999999999
nocycle
cache 20
noorder; | create sequence schema.prefix_info_sequence
increment 1
minvalue 1
maxvalue 9223372036854775807
start 582
cache 20; | || | | select a||b from table1; returns null when there is one null value in a and b. | Dual table | select sysdate from dual; | select now(); | Associated query | select count(distinct(a.col1)) as rcount from
schema.prefix_table1 a,schema.prefix_table2 b
where 1 = 1
and a.col2 = b.col2(+)
and a.col3 > 0
and a.col4 = '1' | select count(distinct(a.col1)) as rcount from
schema.prefix_table1 a left outer join schema.prefix_table2 b on (a.col2 = b.col2)
where 1 = 1 | | select count(distinct(a.col1)) as rcount from
schema.prefix_table1 a,schema.prefix_table2 b,schema.prefix_table3 c,schema.prefix_table4 d
where 1 = 1
and a.col2 = b.col2
and a.col3 = c.col3(+)
and a.col4 = d.col4(+)
and a.col5 > 0
and a.col6 = '1' | select count(distinct(a.col1)) as rcount from
schema.prefix_table1 a inner join schema.prefix_table2 b on (a.col2 = b.col2)
left outer join schema.prefix_table3 c on (a.col3 = c.col3)
left outer join schema.prefix_table4 d on (a.col4 = d.col4)
where 1 = 1
and a.col5 > 0
and a.col6 = '1' | Subquery | oracle:
select * from (
select * from (
select * from schema.prefix_table order by col1
) where x=1 order by col2
) where y=2 order by col3 | select * from (
select * from (
select * from schema.prefix_table order by col1 alias1
) where x=1 order by col2 alias2
) where y=2 order by col3 | Rownum Vs limit | select * from ( select * from (select * from schema.prefix_table1 order by col1 desc,col2 asc) where rownum <= 50 order by col3 asc,col4 desc)
where rownum <= 20 order by col5 desc,col6 asc; | select * from ( select * from (select * from schema.prefix_table1 order by col1 desc,col2 asc) selb order by col3 asc,col4 desc limit 50 ) sela
order by col5 desc,col6 asc limit 20; limit must be used after order by | sequence | select schema.prefix_table1_sequence.nextval as ncode from dual | select nextval('schema.prefix_table1_sequence') as ncode | AS | select a.col1 a_col1，a.col2 a_col2 from a_table a | select a.col1 as a_col1，a.col2 as a_col2 from a_table a | NVL | select nvl(sum(value11),0) fs_value1, nvl(sum(value21),0) fs_value2 from field_sum | select coalesce(sum(value11),0) as fs_value1,coalesce(sum(value21),0) as fs_value2
from field_sum | Decode | select decode(endflag,'1','a','b') endflagfrom test | select (caseendflag when '1' then 'a'else 'b' end) as endflag from test
|
3.4 函数 / 存储过程例子: 1. CREATE OR REPLACE FUNCTION message_deletes(ids "varchar", userid int8) 2. RETURNS int4 AS 3. $BODY$ 4. DECLARE 5. r RECORD; 6. del bool; 7. num int4 := 0; 8. sql "varchar"; 9. BEGIN 10. sql := 'select id,receiveuserid,senduserid,senddelete,receivedelete from message where id in (' || ids || ')'; 11. FOR r IN EXECUTE sql LOOP 12. del := false; 13. IF r.receiveuserid=userid and r.senduserid=userid THEN 14. del := true; 15. ELSEIF r.receiveuserid=userid THEN 16. IF r.senddelete=false THEN 17. update message set receivedelete=true where id = r.id; 18. ELSE 19. del := true; 20. END IF; 21. ELSEIF r.senduserid=userid THEN 22. IF r.receivedelete=false THEN 23. update message set senddelete=true where id = r.id; 24. ELSE 25. del := true; 26. END IF; 27. END IF; 28. IF del THEN 29. delete from message where id = r.id; 30. num := num + 1; 31. END IF; 32. END LOOP; 33. return num; 34. END; 35. $BODY$ 36. LANGUAGE 'plpgsql' VOLATILE; 4 C#里的连接及查询语句4.1 Npgsql 下载 Npgsql .Net Data Provider for Postgresql 组件 根据 .Net famework 的版本选择正确地Npgsql版本 URL：http:///frs/?group_id=1000140 解压zip 文件, 复制 Npgsql.dll、Mono.Security.dll 文件到 C# 工程的obj目录下，在 VS2010中把Npgsql.dll文件加入到 References. 在需要使用Npgsql的C#头文件加入如下 using 语句. [c-sharp] view plaincopy 4.2 连接字符串创建 PostgreSQL 数据库连接 [c-sharp] view plaincopy 1. 2. string connectionString = "Server=127.0.0.1;Port=5432;User Id=myuser;Password=mypassword;Database=mydb;" 3. NpgsqlConnection conn = new NpgsqlConnection(string connectionString); 4. // 打开一个数据库连接，在执行相关SQL之前调用 5. conn.Open(); 6. //关闭一个数据库连接，在执行完相关SQL之后调用 7. conn.Close(); 4.3 查询方法用 NpgsqlCommand.ExecuteScalar() 方法获得唯一值的检索结果. [c-sharp] view plaincopy 1. try 2. { 3. string sql = "select count(*) from mytable"; 4. conn.Open(); 5. NpgSqlCommand objCommand = new NpgSqlCommand(sql, conn); 6. int count = Convert.ToInt32(objCommand.ExecuteScalar()); 7. } 8. finally 9. { 10. conn.Close(); 11. } 用 NpgsqlCommand.ExecuteReader() 方法获得一个结果集的检索结果. [c-sharp] view plaincopy 1. string sql = "select * from mytable"; 2. NpgsqlCommand objCommand = new NpgsqlCommand(sql,conn); 3. NpgsqlDataReader dr = command.ExecuteReader(); 4. while(dr.Read()) 5. { 6. for (i = 0; i < dr.FieldCount; i++) 7. { 8. Console.Write("{0} /t", dr[i]); //获得字段名 9. } 10. int testId = dr["id"]; // 获得指定字段的值。（id是test表的一个字段） 11. …… 12. Console.WriteLine(); 13. } 14. dr.Close(); 4.4 修改方法用 NpgsqlCommand.ExecuteNonQuery() 方法获得对指定表进行添加、更新和删除一条记录的操作 添加记录 [c-sharp] view plaincopy 1. string sql = "insert mytable values (3,'V3')"; 2. NpgsqlCommandobjCommand = new NpgsqlCommand(sql, conn); 3. objCommand.ExecuteNonQuery(); 更新记录 [c-sharp] view plaincopy 1. sql = "update mytable set value='V30' where id=3"; 2. NpgsqlCommandobjCommand = new NpgsqlCommand(sql, conn); 3. objCommand.ExecuteNonQuery(); 删除记录 [c-sharp] view plaincopy 1. sql = "delete from mytable where id=1"; 2. NpgsqlCommandobjCommand = new NpgsqlCommand(sql, conn); 3. objCommand.ExecuteNonQuery(); 用NpgsqlDataAdapter.Fill方法，把检索的结果集放到DataSet object中，这样可以使用DataSet object设置DotNet的DataGridView控件的DataSource属性，这样在DataGridView中显示从表中取出的所有记录。 [c-sharp] view plaincopy 1. string sql = "select id,value from test"; 2. DataSet ds = new DataSet(); 3. NpgsqlDataAdapter objAdapter = new NpgsqlDataAdapter(sql, conn); 4. objAdapter.Fill(ds, "a"); //“a”这个表是自定义的 5. dgvBaseResult.DataSource = ds.Tables["a"]; //dgvBaseResult是DataGridView的一个Object。 |
