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=#
- 创建数据库
postgres=#
postgres=# create database db1;
CREATE DATABASE
postgres=#
- 选择数据库
postgres=#
postgres=# \c db1;
You are now connected to database "db1" as user "postgres".
db1=#
2. 表
- 创建表
db1=#
db1=# create table tb1_1(
idA int not null,
nameA text,
scoreA decimal,
primary key(idA)
);
CREATE TABLE
db1=#
- 查看所有表
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=#
- 查看表结构
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客户端
- pom.xml
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>42.2.23</version>
</dependency>
- 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();
}
}