PGSQL学习-基础表结构

1 访问数据库

创建好数据库后，你可以有三种方式访问数据库

• 运行PostgreSQL的交互式终端程序，它被称为psql， 它允许你交互地输入、编辑和执行SQL命令。

• 使用一种已有的图形化前端工具，比如pgAdmin或者带ODBC或JDBC支持的办公套件如DataGrip等来创建和管理数据库。

• 使用多种绑定发行的语言中的一种写一个自定义的应用

psql访问

# psql 访问指定数据库
bash-4.2$ psql postgres
psql (14.12)
Type "help" for help.

postgres=# 

# psql缺省访问，默认postgres数据库
bash-4.2$ psql
psql (14.12)
Type "help" for help.

postgres=# 

# postgres=# 表示 当前数据库为postgres， #表示当前访问用户是数据库超级用户，=>则说明是普通用户

 查看pgsql版本信息

postgres=# select version();
                                                 version                                                  
----------------------------------------------------------------------------------------------------------
 PostgreSQL 14.12 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
(1 row)

psql程序有一些不属于SQL命令的内部命令。它们以反斜线开头，“\”

比如：

# \h 活着 \help 可以查看命令帮助文档
postgres=# \h
Available help:
  ABORT                            CHECKPOINT                       CREATE USER                      DROP TRIGGER

# \c 连接到指定数据库
postgres=# \c mydb;
You are now connected to database "mydb" as user "postgres".
mydb=# 


# \l 列出所有的数据库
postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 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
(3 rows)

# \dn：列出当前数据库中的所有schema
mydb=# \dn
  List of schemas
  Name  |  Owner   
--------+----------
 mydb_s | postgres
 public | postgres
(2 rows)

# \d 表名 查看表结构
mydb=# \d mydb_s.test01
               Table "mydb_s.test01"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 id     | integer |           |          | 


# \q 退出psql连接

2 创建数据库

# 创建一个名字叫mydb的数据库
create database mydb;

# 删除数据库
drop databse mydb;

3 创建schema

一个cluster可以有多个database，而一个database可以有多个shema，一个shema可以有多个表

也就是说pgsql的数据层次结构分了三层，database -> shema -> table

这样划分可以在同一个database中创建名字相同的表但是属于不同shema

每个database默认有一个public的shema，如果创建表时不指定shema则默认为public

这样，多个用户操作同一个数据库时可以避免出现表名冲突。而且一个数据库需要加载第三方应用的数据时可以单独放在一个shema中，避免了表的冲突，同时也位于同一个database

# 创建shema
create shema shema1;
# 删除shema,如果shema下有表则删除失败
drop shema shema1;
# 强制删除
drop shema shema1 cascade;

# 查看databse下所有shema
\dn
mydb=# \dn
  List of schemas
  Name  |  Owner   
--------+----------
 mydb_s | postgres
 public | postgres

4 创建table

CREATE TABLE table_name(
   column1 datatype,
   column2 datatype,
   .....
);

比如创建一个weather表，包含5个字段，此时weather表属于public shema

CREATE TABLE weather (
    city            varchar(80),
    temp_lo         int,           -- 最低温度
    temp_hi         int,           -- 最高温度
    prcp            real,          -- 湿度
    date            date
);

创建指定shema的表,在表名前显示指定shema名称

CREATE TABLE public.cities (
    name            varchar(80),
    location        point
);

\d 查看表

mydb=# \d
          List of relations
 Schema |  Name   | Type  |  Owner   
--------+---------+-------+----------
 public | cities  | table | postgres
 public | weather | table | postgres

\d 表名 查看表结构

mydb=# \d cities
                       Table "public.cities"
  Column  |         Type          | Collation | Nullable | Default 
----------+-----------------------+-----------+----------+---------
 name     | character varying(80) |           |          | 
 location | point                 |           |          | 

插入数据

insert into

INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)
VALUES (value1, value2, value3,...valueN);

请注意所有数据类型都使用了相当明了的输入格式。那些不是简单数字值的常量通常必需用单引号（'）包围 

 在weather表中插入一条数据

mydb=# insert into weather values('cq',19,30,3.0,'2024/06/21');
INSERT 0 1

还可以使用copy命令加载大量的数据

比如 

COPY weather FROM '/home/user/weather.txt';

查看表数据

select

mydb=# select * from weather;
 city | temp_lo | temp_hi | prcp |    date    
------+---------+---------+------+------------
 cq   |      19 |      30 |    3 | 2024-06-21
(1 row)

删除表

drop table 表名 [,表名];

基本对数据的操作都和mysql一致，语法都是sql语法，比如添加数据是insert，删除delete，更新update，查询select

5 事务

pgsql中同样适用事务来保证并发时数据的安全

事务最重要的一点是它将多个步骤捆绑成了一个单一的、要么全完成要么全不完成的操作。步骤之间的中间状态对于其他并发事务是不可见的，并且如果有某些错误发生导致事务不能完成，则其中任何一个步骤都不会对数据库造成影响

pgsql中开启事务使用begin，提交事务使用commit，回滚事务使用 rollback

开启一个事务需要将SQL命令用BEGIN和COMMIT命令包围起来

BEGIN;
UPDATE accounts SET balance = balance - 100.00
    WHERE name = 'Alice';
COMMIT;

pgsql中也有保存点

利用保存点来以更细的粒度来控制一个事务中的语句。保存点允许我们有选择性地放弃事务的一部分而提交剩下的部分

# 开启事务
BEGIN;

# 更新数据
UPDATE accounts SET balance = balance - 100.00
    WHERE name = 'Alice';
# 添加保存点
SAVEPOINT my_savepoint;
# 更新数据2
UPDATE accounts SET balance = balance + 100.00
    WHERE name = 'Bob';
# 出现业务错误，需要回滚
-- oops ... forget that and use Wally's account
# 回滚
ROLLBACK TO my_savepoint;
# 再次更新数据3
UPDATE accounts SET balance = balance + 100.00
    WHERE name = 'Wally';

# 提交事务
COMMIT;

6 表的继承

pgsql中有表继承关系，也就是说一个表可以继承于另一个表，一般表达父子、所属关系，比如城市和首都，首都就属于城市

父表会包含子表信息，子表会继承父表字段

举例：

创建cities和capitals，分别表示城市和首都，首都继承城市，那么首都表拥有cities的所有字段和本身state字段

CREATE TABLE cities (
  name       text,
  population real,
  altitude   int     -- (in ft)
);

CREATE TABLE capitals (
  state      char(2)
) INHERITS (cities);

查询所有cities时，会查询cities表和capitals表

比如：查询海拔高度>500的城市，则会返回cities和captials所有符合要求的

SELECT name, altitude
  FROM cities
  WHERE altitude > 500;

如果只想查询首都海拔高度的，则需要 from only

SELECT name, altitude
    FROM ONLY cities
    WHERE altitude > 500;

only标记可以用着各个语法中，比如 SELECT、UPDATE 和DELETE