linux下postgres的基本数据库操作

linux下postgres的基本数据库操作

 

步骤：

• 1.进入数据库  su - postgres ,psql -p5432
• 2.创建数据库  create database dbname;
• 3.列出数据库（验证是否成功） \l
• 4.选择数据库  \c dbname
• 5.创建表  create table tablename(....);
• 6.验证表是否创建成功  \d
• 7.查看表信息   \d tablename 
  
• 8.向表插入数据
• 9.更新表数据
• 10.删除表 drop table tablename;

PostgreSQL 创建数据库可以用以下三种方式：

• 1、使用 CREATE DATABASE SQL 语句来创建。
• 2、使用 createdb 命令来创建。
• 3、使用 pgAdmin 工具。

我这里使用第一种，具体实例看下面的命令：

[root@xiaoxiao ~]# su - postgres
Last login: Tue Nov 17 13:45:33 CST 2020 on pts/0
-bash-4.2$ psql -p5432
psql (9.6.17)
Type "help" for help.

postgres=# create database rundb;
CREATE DATABASE

postgres=# \l
                                   List of databases
    Name     |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-------------+----------+----------+-------------+-------------+-----------------------
 hrun        | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 mockserver  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 postgres    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 renren_fast | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 rundb       | 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        | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
(8 rows)

postgres=# \c rundb
You are now connected to database "rundb" as user "postgres".

rundb=# CREATE TABLE Person(
rundb(#    ID INT PRIMARY KEY     NOT NULL,
rundb(#    NAME           TEXT    NOT NULL,
rundb(#    AGE            INT     NOT NULL,
rundb(#    ADDRESS        CHAR(50),
rundb(#    SALARY         REAL
rundb(# );
CREATE TABLE

rundb=# \d
         List of relations
 Schema |  Name  | Type  |  Owner   
--------+--------+-------+----------
 public | person | table | postgres
(1 row)


rundb=# \d person
        Table "public.person"
 Column  |     Type      | Modifiers 
---------+---------------+-----------
 id      | integer       | not null
 name    | text          | not null
 age     | integer       | not null
 address | character(50) | 
 salary  | real          | 
Indexes:
    "person_pkey" PRIMARY KEY, btree (id)

rundb=# \d
         List of relations
 Schema |  Name  | Type  |  Owner   
--------+--------+-------+----------
 public | person | table | postgres
(1 row)

rundb=# INSERT INTO Person (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'xiaoxiao', 20, 'Guangzhou ', 65000.00), (2, 'kefeng', 27, 'ChongQing', 85000.00);
INSERT 0 2
rundb=#  SELECT * FROM person;
 id |   name   | age |                      address                       | salary 
----+----------+-----+----------------------------------------------------+--------
  1 | xiaoxiao |  20 | Guangzhou                                          |  65000
  2 | kefeng   |  27 | ChongQing                                          |  85000
(2 rows)

rundb=# UPDATE person SET SALARY = 90000.00 WHERE ID = 2;
UPDATE 1
rundb=#  SELECT * FROM person;
 id |   name   | age |                      address                       | salary 
----+----------+-----+----------------------------------------------------+--------
  1 | xiaoxiao |  20 | Guangzhou                                          |  65000
  2 | kefeng   |  27 | ChongQing                                          |  90000
(2 rows)

rundb=# drop table person;
DROP TABLE
rundb=# \d
No relations found.
rundb=# 

善于跌倒仍喜爱奔跑~