【postgresql 基础入门】表的操作，表结构查看、修改字段类型、增加删除字段、重命名表，对表的操作总是比别人棋高一着

Posted on by hackdl

表的操作

​专栏内容：

  • postgresql内核源码分析
  • 手写数据库toadb
  • 并发编程

​开源贡献：

  • toadb开源库

个人主页：我的主页
管理社区：开源数据库
座右铭：天行健，君子以自强不息；地势坤，君子以厚德载物.

系列文章

  • 入门准备
  • postgrersql基础架构
  • 快速使用
  • 初始化集群
  • 数据库服务管理
  • psql客户端使用
  • pgAdmin图形化客户端
  • 数据库的使用
  • 创建数据库
  • 数据库操作
  • 表的使用
  • 表的创建
  • 表的操作
  • 数据查询
  • 数据查询
  • 多表联合查询

文章目录

  • 表的操作
  • 系列文章
  • 前言
  • 概述
  • 查看表的结构
  • 查询数据
  • 插入数据
  • 修改字段
    • 修改类型
    • 修改字段名称
  • 添加和删除字段
    • 删除字段
    • 添加字段
  • 重命名表
  • 删除表
  • 查表的OID
  • 总结
  • 结尾

前言

postgresql 数据库是一款通用的关系型数据，在开源数据库中能与商业数据媲美，在业界也越来越流行。

因为是开源数据库，不仅公开源码，还有很多使用案例，好用的插件，所以它的慢慢变成了数据库的先驱和标准，通过postgresql可以很好从使用到原理，彻底搞懂；

如果是学习编程，也可以学到丰富的编程知识，数据结构，编程技巧，它里面还有很多精妙的架构设计，分层思想，可以灵活定制的思想。

本专栏主要介绍postgresql 入门使用，数据库维护管理，通过这些使用来了解数据库原理，慢慢了解postgresql是什么样的数据库，能做那些事情，以及如何做好服务，最关键的是这些知识都是面试的必备项。

概述

前一篇分享了创建表的相关内容，对于表也有很多属性，如果这些属性在创建时搞错了，那是否需要删除表再来呢？

本文就从以下几个方面，带大家看看表的各种操作，在平时数据库开发和维护时，不需要再求助别人了。

  • 查看表结构
  • 查询数据
  • 插入数据
  • 删除数据
  • 修改表字段
  • 添加和删除表字段
  • 重命名表
  • 删除表
  • 查表的OID

查看表的结构

当我们使用表时，也要知道表的定义结构，有那些字段，以及字段的类型，有没有主键，外键等；

如果不是我们自己创建的表，可以在数据库中进行查询表结构；
下面使用安装时自带的psql做为客户端进行操作；

首先登录数据库，我们看到前期已经有两张table存在；

[senllang@hatch bin]$ ./psql -d db_factory2 -U hr
WARNING:  permission denied to set role "vp3"
psql (16beta1)
Type "help" for help.

db_factory2=> d
                   List of relations
 Schema |           Name            |   Type   | Owner
--------+---------------------------+----------+-------
 public | company                   | table    | hr
 public | employees                 | table    | hr
 public | employees_employee_id_seq | sequence | hr
(3 rows)

db_factory2=>

接下来看一下这两张表的结构，并验验证查询和插入数据试一下；

db_factory2=> d company
                  Table "public.company"
 Column  |     Type      | Collation | Nullable | Default
---------+---------------+-----------+----------+---------
 id      | integer       |           | not null |
 name    | text          |           | not null |
 age     | integer       |           | not null |
 address | character(50) |           |          |
 salary  | real          |           |          |
Indexes:
    "company_pkey" PRIMARY KEY, btree (id)

可以看到company 这张表的信息通过表格的形式展示出来，第一列是字段史，第二列是类弄，后面是约束，还有默认值；

可以看到有五个字段，id字段是主键，主键也是一种索引，这里默认采用btree索引类型，还有非空约束；address字段的长度为50；

查询数据

再来看一下表中是否有数据，这里可以按字段查，也可以用*来代替所有字段 ；

db_factory2=> select * from company;
 id | name | age | address | salary
----+------+-----+---------+--------
(0 rows)
db_factory2=> select id from company ;
 id
----
(0 rows)

表中还没有数据；

插入数据

插入数据，我们使用标准SQL语句，如果每个字段都有值时，可以省略字段名，解析时会根据值顺序对应到每个字段；

db_factory2=> insert into company (id,name,age,address,salary) values(1,'hongxing',8,'shangdong',4.8),(2,'dongfeng',20,'shenyang',10.22);
INSERT 0 2
db_factory2=> select * from company;
 id |   name   | age |                      address                       | salary
----+----------+-----+----------------------------------------------------+--------
  1 | hongxing |   8 | shangdong                                          |    4.8
  2 | dongfeng |  20 | shenyang                                           |  10.22
(2 rows)

一次插入了两条数据，values后面可以是多个值域，这样就可以插入多条，但是值域中字段值数量必须一样，否则解析器是没有办法对齐到表字段的；

修改字段

对已经存在的表的字段进行修改，可以对字段名称，类型进行变更；

修改类型

db_factory2=> alter table company alter column name type varchar(255);
ALTER TABLE
db_factory2=> d company
                      Table "public.company"
 Column  |          Type          | Collation | Nullable | Default
---------+------------------------+-----------+----------+---------
 id      | integer                |           | not null |
 name    | character varying(255) |           | not null |
 age     | integer                |           | not null |
 address | character(50)          |           |          |
 salary  | real                   |           |          |
Indexes:
    "company_pkey" PRIMARY KEY, btree (id)

将name字段的类型长度由原来无限制变更为255字符；

类型的修改需要注意，并不是所有类型都可以任意修改，修改后的类型必须兼容之前的类型，最好不存在数据丢失的问题；

修改字段名称

表定义后，老板说这个名字起的不好，那只好修改一下，可以这样操作；

db_factory2=> alter table company rename age TO founded;
ALTER TABLE
db_factory2=> d company
                      Table "public.company"
 Column  |          Type          | Collation | Nullable | Default
---------+------------------------+-----------+----------+---------
 id      | integer                |           | not null |
 name    | character varying(255) |           | not null |
 founded | integer                |           | not null |
 address | character(50)          |           |          |
 salary  | real                   |           |          |
Indexes:
    "company_pkey" PRIMARY KEY, btree (id)

添加和删除字段

虽然上面把age改成了founded成立日期，但是类型却怎么也修改不成功； 那这 。。。

db_factory2=> alter table company alter column founded type timestamp without time zone;
ERROR:  column "founded" cannot be cast automatically to type timestamp without time zone
HINT:  You might need to specify "USING founded::timestamp without time zone".

注意：在做下列操作之前需要对数据进行备份，否则数据就会丢失；
遇到这种情况，当在初期时，还在测试阶段，可以采用删除字段，再添加的办法；当然这样做之后，这列的数据都会丢失；

删除字段

db_factory2=> alter table company drop column founded ;
ALTER TABLE
db_factory2=> d company
                      Table "public.company"
 Column  |          Type          | Collation | Nullable | Default
---------+------------------------+-----------+----------+---------
 id      | integer                |           | not null |
 name    | character varying(255) |           | not null |
 address | character(50)          |           |          |
 salary  | real                   |           |          |
Indexes:
    "company_pkey" PRIMARY KEY, btree (id)

添加字段

之后可以按正确的字段名称，类型进行添加

db_factory2=> alter table company add column founded date;
ALTER TABLE
db_factory2=> d company
                      Table "public.company"
 Column  |          Type          | Collation | Nullable | Default
---------+------------------------+-----------+----------+---------
 id      | integer                |           | not null |
 name    | character varying(255) |           | not null |
 address | character(50)          |           |          |
 salary  | real                   |           |          |
 founded | date                   |           |          |
Indexes:
    "company_pkey" PRIMARY KEY, btree (id)

现在可以了，那么这一列的数据就需要重新录入了，可以使用update语句批量录入；

重命名表

如果发现表名含义不清，可以通过重命名的方式进行修改；

db_factory2=> alter table company rename to tbl_company; 
ALTER TABLE

这个操作对于数据库内部不会产生影响，因为内部是通过OID进行引用；但是对于已经运行的业务系统，需要修改SQL中的引用方式；

删除表

删除一张不再使用的表

db_factory2=> drop table employees ;
DROP TABLE

删除成功的前提是，这张表没有被别的数据库对象引用(视图，外键等)，比如在这个表上创建了视图，那么就先要删除视图，或者是采取级联删除的方式一起删除；

查表的OID

数据库内部使用OID引用，OID是唯一标识，在磁盘上存储时也采用OID来命名，如何知道表的OID呢？
表相关信息记录在pg_class这张系统表中；

db_factory2=> select oid,relname from pg_class where relname='tbl_company';
  oid  |   relname
-------+-------------
 16510 | tbl_company
(1 row)

总结

通过本文的分享，对于表的属性的查询，变更有了进一步了解，数据字典中关于表的信息，如OID，存储位置，是普通表还是临时表等，都记录在pg_class当中，当然它还记录了很多有用的信息，有兴趣的同学可以自己查询试试。

结尾

非常感谢大家的支持，在浏览的同时别忘了留下您宝贵的评论，如果觉得值得鼓励，请点赞，收藏，我会更加努力！

作者邮箱：study@senllang.onaliyun.com
如有错误或者疏漏欢迎指出，互相学习。

注：未经同意，不得转载！

服务器托管，北京服务器托管，服务器租用，机房机柜带宽租用

服务器托管

咨询：董先生

电话13051898268 QQ/微信93663045！

上一篇:
下一篇: