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

表的操作

专栏内容

开源贡献

  • toadb开源库

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

系列文章

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

前言

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 | ***pany                   | table    | hr
 public | employees                 | table    | hr
 public | employees_employee_id_seq | sequence | hr
(3 rows)

db_factory2=>

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

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

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

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

查询数据

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

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

表中还没有数据;

插入数据

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

db_factory2=> insert into ***pany (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 ***pany;
 id |   name   | age |                      address                       | salary
----+----------+-----+----------------------------------------------------+--------
  1 | hongxing |   8 | shangdong                                          |    4.8
  2 | dongfeng |  20 | shenyang                                           |  10.22
(2 rows)

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

修改字段

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

修改类型

db_factory2=> alter table ***pany alter column name type varchar(255);
ALTER TABLE
db_factory2=> \d ***pany
                      Table "public.***pany"
 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:
    "***pany_pkey" PRIMARY KEY, btree (id)

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

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

修改字段名称

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

db_factory2=> alter table ***pany rename age TO founded;
ALTER TABLE
db_factory2=> \d ***pany
                      Table "public.***pany"
 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:
    "***pany_pkey" PRIMARY KEY, btree (id)

添加和删除字段

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

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

添加字段

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

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

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

重命名表

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

db_factory2=> alter table ***pany rename to tbl_***pany; 
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_***pany';
  oid  |   relname
-------+-------------
 16510 | tbl_***pany
(1 row)

总结

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

结尾

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

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

注:未经同意,不得转载!

转载请说明出处内容投诉
CSS教程_站长资源网 » 【postgresql 基础入门】表的操作,表结构查看、修改字段类型、增加删除字段、重命名表,对表的操作总是比别人棋高一着

发表评论

欢迎 访客 发表评论

一个令你着迷的主题!

查看演示 官网购买