【4.1】postgreSQL

一、postgreSQL简介

1.1 基本概念:

关系数据库系统(RDBMS)为采用行列二维表结构来管理数据, 包括Oracle Database, SQL Server, DB,PostgreSQL, MySQL

1.2 语句以及其种类:

SQL语言共分为四大类:数据查询语言DQL,数据操纵语言DML, 数据定义语言DDL,数据控制语言DCL。

1 数据定义语言DDL (D = Definition)

数据定义语言DDL用于建立,修改,删除数据库中的各种对象—–表、视图、 索引、同义词、聚簇等如:

CREATE TABLE/VIEW/INDEX/SYN/CLUSTER
DROP
ALTER
| | | | | 表 视图 索引 同义词 簇

2 数据查询语言DQL (Q = Query )

数据查询语言DQL用于检索数据库

基本结构是由SELECT子句,FROM子句,WHERE子句组成的查询块:

SELECT <字段名表>

FROM <表或视图名>

WHERE <查询条件>

3 数据操纵语言DML( M = Manipulation)

数据操纵语言DML用于改变数据库数据

主要有三种形式:

1) 插入:INSERT

2) 更新:UPDATE

3) 删除:DELETE

4 事务控制语言TCL (C = Control)

事务控制语言TCL用于维护数据的一致性

包含三条语句:

1)COMMIT;

2)ROLLBACK;

3)SAVEPOINT;

5 数据控制语言DCL(自动提交事务)

数据控制语言DCL用来授予或回收访问数据库的某种特权,并控制

数据库操纵事务发生的时间及效果,对数据库实行监视等。

包含两条命令:

  1. GRANT:授权。
  2. REVOKE:撤回。

1.3 基本特点:

  • 关系数据库必须以行为单位进行数据的读写;
  • 在一个单元格中只能输入一个数据;
  • 原则上SQL语句都使用分号来结尾;
  • sql语句不区分大小写,但建议关键字大写,表名首字母大写;插入的数据区分大小写;
  • 字符串何日期常数需要使用单引号括起来,数字常数无需加注单引号
  • 单词之间需要用半角空格或这换行符进行分隔;

二、postgreSQL安装(以Mac为例)

方法一:homebrew 直接安装 PostgreSQL(推荐)

1.在 mac 下,可以利用 homebrew 直接安装 PostgreSQL:

brew install postgresql -v

稍等片刻,PostgreSQL 就安装完成。

#sudo brew install postgresql
==> /usr/local/Cellar/postgresql/9.4.4/bin/initdb /usr/local/var/postgres
/usr/local/var/postgres
initdb: cannot be run as root
Please log in (using, e.g., "su") as the (unprivileged) user that will
own the server process.
Warning: The post-install step did not complete successfully
You can try again using `brew postinstall postgresql`
==> Summary
🍺 /usr/local/Cellar/postgresql/9.4.4: 3014 files, 40M

提示报错,不要以管理员身份来运行,修改部分文件夹的权限

2.接下来就是初始数据库,在终端执行一下命令,初始配置 PostgreSQL:

initdb /usr/local/var/postgres -E utf8

(运行这一步之前/usr/local中是没有var这个文件夹的)

上面指定 “/usr/local/var/postgres” 为 PostgreSQL 的配置数据存放目录,并且设置数据库数据编码是 utf8,更多配置信息可以 “initdb –help” 查看。

如果报错:

creating directory /usr/local/var/postgres ... initdb: could not create directory "/usr/local/var": Permission denied

就修改权限吧:

sudo chmod -R 777 /usr/local/

3.设成开机启动 PostgreSQL:

ln -sfv /usr/local/opt/postgresql/*.plist ~/Library/LaunchAgents
 launchctl load ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist

4.启动 PostgreSQL:

pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start

#关闭 PostgreSQL:
 pg_ctl -D /usr/local/var/postgres stop -s -m fast

psql: could not connect to server: Permission denied
Is the server running locally and accepting
connections on Unix domain socket "/var/pgsql_socket/.s.PGSQL.5432"?
这个时候服务器关闭了,可以再次开启:pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start

方法二:下载安装(不推荐)

下载地址:http://www.enterprisedb.com/products-services-training/pgdownload

我选择的最新 的mac版本9.4.4

安装说明:http://get.enterprisedb.com/docs/README-languagepack.txt

安装有问题,可以在 http://forums.enterprisedb.com/forums/show/9.page 请教

下载后,双击即可安装,然后修改环境变量,在终端中输入:

sudo vim /etc/bashrc

将下面的代码加到末尾

PERLHOME=/Library/EnterpriseDB/LanguagePack/9.4/Perl-5.16
PYTHONHOME=/Library/EnterpriseDB/LanguagePack/9.4/Python-3.3
TCLHOME=/Library/EnterpriseDB/LanguagePack/9.4/Tcl-8.5

export PYTHONHOME
export PATH=$PYTHONHOME/bin:$PERLHOME/bin:$TCLHOME/bin:$PATH
export DYLD_LIBRARY_PATH=$PYTHONHOME/lib:$PERLHOME/lib/CORE:$TCLHOME/lib:$DYLD_LIBRARY_PATH

然后

source /etc/bashrc

问题:

  1. 是否有效,有待进一步的验证
  2. /Library/EnterpriseDB/LanguagePack/9.4/bin/psql 看上去是乱码,为何?

每次启动还得这样

/Library/PostgreSQL/9.4/scripts/runpsql.sh

太麻烦了

sudo cp /Library/PostgreSQL/9.4/scripts/runpsql.sh /usr/bin/psql

以后就可以直接在终端中调用了

Linux下的安装

1.安装

sudo yum install postgresql-server postgresql-contrib

2.初始化

sudo postgresql-setup initdb

3.修改pg_hba.conf

sudo vim /var/lib/pgsql/data/pg_hba.conf

插入一行:host all all 0.0.0.0/0 md5

4.修改postgresql.conf

sudo vim /var/lib/pgsql/data/postgresql.conf

改一行:listen_addresses = ‘*’

5.重启postgresql服务

sudo service postgresql restart
  1. 修改密码

安装目录/var/lib/pgsql及目录下文件或文件夹的可读、可写、可操作统统只开放给用户postgres

postgres的默认密码为空,上面弄好以后记得改密码。

sudo su - postgres
psql
ALTER USER postgres PASSWORD 'ddd';

7.创建数据库

sudo su - postgres
psql 
createuser sam -P

CREATE DATABASE edenpedia OWNER sam ENCODING 'UTF8';
CREATE DATABASE edenpedia_dev OWNER sam ENCODING 'UTF8';

psql edenpedia
psql -U postgres -d edenpedia -h localhost

psql -U sam -d edenpedia

参考资料: https://www.cnblogs.com/hannyblogs/p/6535664.html (要整理哈!!!!!!!!)

三、一些简单的数据库操作命令

1.创建一个postgresql用户

createuser sam -P

Enter password for new role:
Enter it again:

上面的 sam是用户名,回车输入 2 次用户密码后即用户创建完成。更多用户创建信息可以 “createuser –help” 查看。

2.创建数据库

createdb shop -O sam -E UTF8 -e

上面创建了一个名为 shop 的数据库,并指定sam为改数据库的拥有者(owner),数据库的编码(encoding)是 UTF8,参数 “-e” 是指把数据库执行操作的命令显示出来。

更多数据库创建信息可以 “createdb –help” 查看。

3.连接数据库

#psql -U username -d dbname -h 127.0.0.1
 psql -U sam -d shop -h localhost

4.PostgreSQL 数据库操作(连接数据库以后的操作) 显示已创建的数据库:

\l

(在不连接进 PostgreSQL 数据库的情况下,也可以在终端上查看显示已创建的列表: psql -l )

连接数据库

\c dbname

#显示数据库表

\d

#退出数据库

\q

#创建一个名为 test 的表

CREATE TABLE test(id int, text VARCHAR(50));

插入一条记录

INSERT INTO test(id, text) VALUES(1, 'sdfsfsfsdfsdfdf');

查询记录

SELECT * FROM test WHERE id = 1;

更新记录

UPDATE test SET text = 'aaaaaaaaaaaaa' WHERE id = 1;

删除指定的记录

DELETE FROM test WHERE id = 1;

删除表

DROP TABLE test;

删除数据库

DROP DATABASE dbname;

或者利用 dropdb 指令,在终端上删除数据库

dropdb -U user dbname

四、我所遇到的报错

  1. psql: fe_sendauth: no password supplied

问题:账号对,密码为空

解决办法密码不应该为空

  1. psql: FATAL: password authentication failed for user “sam”

问题:账号不存在。

解决办法:网上找了很多解决办法,比如说:

sudo vim /Library/PostgreSQL/9.4/data/pg_hba.conf

改为md5改为trust

# "local" is for Unix domain socket connections only
local all all trust

参考资料:http://www.2cto.com/database/201204/126854.html

但我并没有解决问题,我的问题是我以为我建立了新的用户,其实我的用户名还是默认的postgre,密码则是我安装过程中输入的密码

参考资料:

《SQL基础教程》

http://www.cnblogs.com/fjfzhkb/archive/2007/10/18/929108.html

http://dhq.me/mac-postgresql-install-usage (赞)

个人公众号,比较懒,很少更新,可以在上面提问题,如果回复不及时,可发邮件给我: tiehan@sina.cn

Sam avatar
About Sam
专注生物信息 专注转化医学