PostgreSQL数据库基本SQL语法

1. 数据库

1. 查看数据库

postgres=# 
postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | 
 template0 | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(3 rows)

postgres=# 
postgres=# select * from pg_database;
  oid  |  datname  | datdba | encoding | datcollate  |  datctype   | datistemplate | datallowconn | datconnlimit | datlastsysoid | datfrozenxid | datminmxid | dattablespac
e |               datacl                
-------+-----------+--------+----------+-------------+-------------+---------------+--------------+--------------+---------------+--------------+------------+-------------
--+-------------------------------------
 14174 | postgres  |     10 |        6 | zh_CN.UTF-8 | zh_CN.UTF-8 | f             | t            |           -1 |         14173 |          479 |          1 |          166
3 | 
     1 | template1 |     10 |        6 | zh_CN.UTF-8 | zh_CN.UTF-8 | t             | t            |           -1 |         14173 |          479 |          1 |          166
3 | {=c/postgres,postgres=CTc/postgres}
 14173 | template0 |     10 |        6 | zh_CN.UTF-8 | zh_CN.UTF-8 | t             | f            |           -1 |         14173 |          479 |          1 |          166
3 | {=c/postgres,postgres=CTc/postgres}
(3 rows)

postgres=# 

2. 创建数据库

postgres=# 
postgres=# create database db1;
CREATE DATABASE
postgres=#

3. 选择数据库

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

2. 表

1. 创建表

db1=# 
db1=# create table tb1_1(
idA int not null,
nameA text,
scoreA decimal,
primary key(idA)
);
CREATE TABLE
db1=# 

2. 查看所有表

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

db1=#
db1=# SELECT * FROM information_schema.tables where "table_catalog" = 'db1' and "table_schema" = 'public';
 table_catalog | table_schema | table_name | table_type | self_referencing_column_name | reference_generation | user_defined_type_catalog | user_defined_type_schema | user
_defined_type_name | is_insertable_into | is_typed | commit_action 
---------------+--------------+------------+------------+------------------------------+----------------------+---------------------------+--------------------------+-----
-------------------+--------------------+----------+---------------
 db1           | public       | tb1_1      | BASE TABLE |                              |                      |                           |                          |     
                   | YES                | NO       | 
(1 row)

db1=# 

3. 查看表结构

db1=# 
db1=# \d tb1_1;
               Table "public.tb1_1"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 ida    | integer |           | not null | 
 namea  | text    |           |          | 
 scorea | numeric |           |          | 
Indexes:
    "tb1_1_pkey" PRIMARY KEY, btree (ida)

db1=#

3. Java JDBC客户端

1. pom.xml

        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <version>42.2.23</version>
        </dependency>

2. Postgresql_test.java

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class Postgresql_test {
    public static void main(String[] args) throws Exception{

        String url = "jdbc:postgresql://192.168.23.33:5432/db1";
        String user = "postgres";
        String password = "postgres123";

        Class.forName("org.postgresql.Driver");
        Connection conn= DriverManager.getConnection(url, user, password);
        Statement stmt = conn.createStatement();

        String sql = "select * from tb1_1";
        ResultSet resultSet = stmt.executeQuery(sql);
        while(resultSet.next()) {
            System.out.println(resultSet.getInt(1));
        }

        stmt.close();
        conn.close();
    }
}