三、基础命令

1.查看有哪些数据库

\l

2.进入数据库

\c

3.查看所在数据库里面有哪些表

select * from pg_tables where schemaname = 'public';

或者在命令行模式下使用\d查看

3.1查看表中的详细信息

\d 表名

4.查看数据库相关信息

4.1 查看数据库版本

postgres=# SELECT version();
                           version
-------------------------------------------------------------
 PostgreSQL 12.11, compiled by Visual C++ build 1914, 64-bit
(1 行记录)

4.2 查看用户名和密码

postgres=# SELECT * FROM pg_authid;
 oid  |          rolname          | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit |             rolpassword             | rolvaliduntil
------+---------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------------------------------+---------------
 3373 | pg_monitor                | f        | t          | f             | f           | f           | f              | f            |           -1 |                                     |
 3374 | pg_read_all_settings      | f        | t          | f             | f           | f           | f              | f            |           -1 |                                     |
 3375 | pg_read_all_stats         | f        | t          | f             | f           | f           | f              | f            |           -1 |                                     |
 3377 | pg_stat_scan_tables       | f        | t          | f             | f           | f           | f              | f            |           -1 |                                     |
 4569 | pg_read_server_files      | f        | t          | f             | f           | f           | f              | f            |           -1 |                                     |
 4570 | pg_write_server_files     | f        | t          | f             | f           | f           | f              | f            |           -1 |                                     |
 4571 | pg_execute_server_program | f        | t          | f             | f           | f           | f              | f            |           -1 |                                     |
 4200 | pg_signal_backend         | f        | t          | f             | f           | f           | f              | f            |           -1 |                                     |
   10 | postgres                  | t        | t          | t             | t           | t           | t              | t            |           -1 | md5a3556571e93b0d20722ba62be61e8c2d |
(9 行记录)

5.创建和删除库

5.1创建库

CREATE DATABASE dbname;

--示例
postgres=# CREATE DATABASE TEST;
CREATE DATABASE

--查看是否创建成功
postgres=# \l
                                                        数据库列表
   名称    |  拥有者  | 字元编码 |            校对规则            |             Ctype              |       存取权限     
-----------+----------+----------+--------------------------------+--------------------------------+-----------------------
 mydb      | postgres | UTF8     | Chinese (Simplified)_China.936 | Chinese (Simplified)_China.936 |
 postgres  | postgres | UTF8     | Chinese (Simplified)_China.936 | Chinese (Simplified)_China.936 |
 runoobdb  | postgres | UTF8     | Chinese (Simplified)_China.936 | Chinese (Simplified)_China.936 |
 template0 | postgres | UTF8     | Chinese (Simplified)_China.936 | Chinese (Simplified)_China.936 | =c/postgres          +
           |          |          |                                |                                | postgres=CTc/postgres
 template1 | postgres | UTF8     | Chinese (Simplified)_China.936 | Chinese (Simplified)_China.936 | =c/postgres          +
           |          |          |                                |                                | postgres=CTc/postgres
 test      | postgres | UTF8     | Chinese (Simplified)_China.936 | Chinese (Simplified)_China.936 |
(6 行记录)

5.2删除库

postgres=# DROP DATABASE test;
DROP DATABASE

6.创建和删除表格

6.1创建表格

CREATE TABLE table_name(
   column1 datatype,
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
   PRIMARY KEY( 一个或多个列 )
);

创建一个表格

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

--查看表格是否创建成功
postgres=# \d
                 关联列表
 架构模式 |    名称    |  类型  |  拥有者
----------+------------+--------+----------
 public   | cities     | 数据表 | postgres
 public   | company    | 数据表 | postgres
 public   | department | 数据表 | postgres
 public   | myview     | 视图   | postgres
 public   | weather    | 数据表 | postgres
(5 行记录)

6.2删除表格

DROP TABLE table_name;删除表格

--查看当前有哪些表
postgres=# \d
                 关联列表
 架构模式 |    名称    |  类型  |  拥有者
----------+------------+--------+----------
 public   | cities     | 数据表 | postgres
 public   | company    | 数据表 | postgres
 public   | department | 数据表 | postgres
 public   | myview     | 视图   | postgres
 public   | weather    | 数据表 | postgres
(5 行记录)

--删除department和company表
postgres=# drop table department, company;
DROP TABLE

--查看是否删除成功
postgres=# \d
                关联列表
 架构模式 |  名称   |  类型  |  拥有者
----------+---------+--------+----------
 public   | cities  | 数据表 | postgres
 public   | myview  | 视图   | postgres
 public   | weather | 数据表 | postgres
(3 行记录)