表的操作

​专栏内容：

• 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

          如有错误或者疏漏欢迎指出，互相学习。

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