docker pull orchardup/postgresql
docker run -d -p 5432:5432 -e POSTGRESQL_USER=test -e POSTGRESQL_PASS=oe9jaacZLbR9pN -e POSTGRESQL_DB=test orchardup/postgresql
docker exec -it a397deee6810 /bin/bash
## 使用test用户连接到test数据库
psql -h localhost -U test test
## 查看有什么数据库
test=# l
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
test | test | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
## 切换到test数据库
test=# c test
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
You are now connected to database "test" as user "test".
test=#
## 查看表
test=# dt
No relations found.
## 创建表
test=# create table test2 (id integer,name text);
CREATE TABLE
## 查看表
test=# d
List of relations
Schema | Name | Type | Owner
--------+-------+-------+-------
public | test2 | table | test
(1 row)
## 插入数据
test=# insert into test2 values(1,'david');
INSERT 0 1
## 查询表中数据
test=# select * from test2;
id | name
----+-------
1 | david
(1 row)
## 更新表中记录
test=# update test2 set fullname = 'ryan' where id=1;
UPDATE 1
test=#
## 查看表结构
test=# d test2
id | integer |
name | text |
## 修改表结构，增加一个字段
test=# alter table test2 add column age integer;
ALTER TABLE
test=# d test2
id | integer |
name | text |
age | integer |
## 修改表结构，删除一个字段
test=# alter table test2 drop column age;
ALTER TABLE
test=# d test2
id | integer |
name | text |
## 修改表结构，重命名一个字段
test=# alter table test2 rename column name to fullName ;
ALTER TABLE
test=# d test2
id | integer |
fullname | text |
## 首先，创建数据库用户dbuser，并指定其为超级用户。
root@a397deee6810:/# sudo -u postgres createuser --superuser dbuser
## 登录数据库控制台，设置dbuser用户的密码，完成后退出控制台。
root@a397deee6810:/# sudo -u postgres psql
psql (9.3.5)
Type "help" for help.
postgres=# password dbuser
Enter new password:
Enter it again:
postgres=# q
could not save history to file "/var/lib/postgresql/.psql_history": No such file or directory
## 在shell命令行下，创建数据库exampledb，并指定所有者为dbuser。
root@a397deee6810:/# sudo -u postgres createdb -O dbuser exampledb
root@a397deee6810:/# sudo -u postgres psql
psql (9.3.5)
Type "help" for help.
postgres=# l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
exampledb | dbuser | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
test | test | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(5 rows)
## 使用dbuser用户登录，切换到数据库exampledb
root@a397deee6810:/# psql -U dbuser -d exampledb -h 127.0.0.1 -p 5432
## 导入sql文件数据到test表中
root@a397deee6810:/# vi test.sql
insert into test2 values(2,'aaa');
insert into test2 values(3,'bbb');
insert into test2 values(4,'ccc');
root@a397deee6810:/#
root@a397deee6810:/# psql -U test -d test < test.sql
INSERT 0 1
INSERT 0 1
INSERT 0 1
root@a397deee6810:/# psql -U test -d test -h 127.0.0.1 -p 5432
psql (9.3.5)
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.
test=# select * from test2;
id | fullname
----+----------
1 | ryan
2 | aaa
3 | bbb
4 | ccc
(4 rows)
test=#