【postgresql 基础入门】数据表的创建，永久数据放普通表，中间结果放临时表，附带信用卡业务案例SQL设计

创建表

​专栏内容：

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

​开源贡献：

• toadb开源库

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

系列文章

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

前言

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

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

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

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

概述

使用数据库最常用的操作就是创建表，增删改查数据，表作为真正的数据载体的逻辑单元，联系着数据库的各个模块；

表的定义要符合完整性的要求，完整性包括：实体完整性，数据唯一性；域完整性，各字段取值合法有效；参照完整性，表与表之间的关联约束；用户自定义完整性，根据业务需求，额外定义的一些约束条件；

下面我们一起来看看如何创建一张符合我们业务需求的表；

• 创建表的 SQL 语法
• 定义字段和数据类型
• 设置主键和外键
• 创建索引

创建表的语法

在postgresql中，表有两种存储方式，
一种就是常见的普通表，会存储在对应的database目录下，在database内是共享的，也就是说只要有权限都可以访问；
还有一种时临时表，临时表会在会话结束或者事务结束时被自动删除，也就是临时使用一下，它只能在当前会话中使用，其它会话是看不到它的存在；

创建普通表

基本语法就是

CREATE TABLE 表名(类型 列名,...);

其中表名，类型，列名是可以替换的，类型是数据库中已经预定义好的；名称最好不要超过64字符；

CREATETABLE COMPANY (  
    ID INTPRIMARYKEYNOTNULL,  
    NAME TEXTNOTNULL,  
    AGE INTNOTNULL,  
    ADDRESS CHAR(50),  
    SALARY REAL);

如果要指定schema，那么表名需要写成 schema名称.表名 的形式，这样就会创建到指定的schema下面；

创建临时表

临时表创建时，要使用关键字

temp

，此时创建表在另一个客户端登录时，是看不到的；当前客户端退出后，也会自动删除。

createtemptable result(id int, slary real);

临时表主要用于一些中间结果的存储，比如需要多表联合后计算一些数据，可以先把查询结果放到临时表，这些可以慢慢计算；

字段定义和数据类型

支持的数据类型

以下是postgresql支持的常见的类型列表：
名称别名描述bigintint8有符号的8字节整数bigserialserial8自动增长的8字节整数bit [ (n) ]定长位串bit varying [ (n) ]varbit [ (n) ]变长位串booleanbool逻辑布尔值（真/假）box平面上的普通方框bytea二进制数据（“字节数组”）character [ (n) ]char [ (n) ]定长字符串character varying [ (n) ]varchar [ (n) ]变长字符串cidrIPv4或IPv6网络地址circle平面上的圆date日历日期（年、月、日）double precisionfloat8双精度浮点数（8字节）inetIPv4或IPv6主机地址integerint, int4有符号4字节整数interval [ fields ] [ § ]时间段json文本 JSON 数据jsonb二进制 JSON 数据，已分解line平面上的无限长的线lseg平面上的线段macaddrMAC（Media Access Control）地址macaddr8MAC（Media Access Control）地址（EUI-64格式）money货币数量numeric [ (p, s) ]decimal [ (p, s) ]可选择精度的精确数字path平面上的几何路径pg_lsnPostgreSQL日志序列号pg_snapshot用户级事务ID快照point平面上的几何点polygon平面上的封闭几何路径realfloat4单精度浮点数（4字节）smallintint2有符号2字节整数smallserialserial2自动增长的2字节整数serialserial4自动增长的4字节整数text变长字符串time [ § ] [ without time zone ]一天中的时间（无时区）time [ § ] with time zone timetz一天中的时间，包括时区timestamp [ § ] [ without time zone ]日期和时间（无时区）timestamp [ § ] with time zone timestamptz日期和时间，包括时区
支持的格式非常丰富，有数字，时间，MAC，还有json格式，甚至还有几个路径，这里只是列了一部分，更详细的参加官方手册；

字段的定义

这里需要注意，一个表的列最大不超过1600列，实际上，由于字段数据长度限制，有效的限制通常更低；

主键和外键设置

通常表中会增加主键和外键，达到实体完整性和参照完整性约束；

主键

主键是表中行的唯一标识的候选关键字，一个表只有一个主关键字，也称为主键。

主键可以由一个字段或者多个字段组成，分别称为单字段主键或多字段主键。主键的值用于唯一地标识表中的某一条记录，在两个表的关系中，主关键字用来在一个表中引用来自于另一个表中的特定记录。

主关键字是可选的，并且可在CREATE TABLE或ALTER TABLE语句中定义。

如果在创建表时没有加主键，可以修改表定义的方式添加；一般在批量加载数据时，先不指定主键，加载完成后再指定主键，会提升加载的性能；

要使用ALTER TABLE语句添加主键，可以使用以下语法：

ALTERTABLE table_name
ADDPRIMARYKEY(column_name);

其中，

table_name

是要添加主键的表名，

column_name

是要指定为主键的列名。

请注意，添加主键之前，确保表中没有重复的值存在于该列中。如果存在重复值，将无法添加主键。

示例：
假设有一个名为

users

的表，其中有一个名为

id

的列，您想将其指定为主键。可以使用以下语句：

ALTERTABLE users
ADDPRIMARYKEY(id);

这将使

id

列成为

users

表的主键。

外键

外键是指一个表中的一个或多个字段，它们的值与另一个表中的字段值相对应，用来表示两个表之间的联系。

需要注意的是，一个表的外键，在引用表中一定是主键，这样对应关系是明确的；

外键的创建也同样可以CREATE TABLE或ALTER TABLE语句中定义;

要使用ALTER TABLE语句添加外键，可以使用以下语法：

示例：
假设有两个表：学生表（students）和课程表（courses）。学生表中有一个学生ID字段（student_id），课程表中有一个课程ID字段（course_id）。如果要记录每个学生所选的课程，可以在学生表中添加一个外键，指向课程表中的课程ID字段。可以使用以下语句：

ALTERTABLE students
ADDFOREIGNKEY(student_id)REFERENCES courses(course_id);

这将使学生表中的student_id列成为外键，指向课程表中的course_id列。

创建索引

索引是为了加速对表中数据行的检索而创建的一种分散的存储结构。它是针对表而建立的，由数据页面以外的索引页面组成，每个索引页面中的行都会含有逻辑指针，以便加速检索物理数据。索引数据一般非常小，可以快速加载到内存进行查找，再根据查找到的索引项找到数据表的数据项；

创建索引的语句示例：

CREATEINDEX index_name ON table_name (column_name);

语句中，

index_name

是索引的名称，

table_name

是表的名称，

column_name

是要创建索引的列的名称。可以根据需要指定多个列，以创建复合索引。

索引创建时，默认创建的索引算法为btree，当然还有基于hash的索引，gin索引，gist索引等；这些在之后会介绍到；

选择经常用于查询条件的列作为索引列，这样索引才能被有效利用。如果查询条件中不包含索引列，那么索引将不会被使用。

案例演示

设计银行信用卡中心的数据库需要考虑以下几个方面：

• 客户信息：包括客户的姓名、身份证号码、联系方式等。
• 交易信息：包括交易时间、交易金额、交易类型等。
• 账户信息：包括账户号码、账户余额、账户状态等。
• 风险信息：包括信用评分、逾期次数、欠款金额等。

以下是一些银行信用卡中心的数据库设计示例和相应的SQL语句：

创建客户表：

CREATETABLE customers (
  customer_id INTPRIMARYKEY,
  name VARCHAR(50),
  address VARCHAR(100),
  phone_number VARCHAR(20),
  email VARCHAR(50));

创建交易表：

CREATETABLEtransactions(
  transaction_id INTPRIMARYKEY,
  customer_id INT,
  transaction_date DATE,
  transaction_amount DECIMAL(10,2),
  transaction_type VARCHAR(20),FOREIGNKEY(customer_id)REFERENCES customers(customer_id));

创建账户表：

CREATETABLE accounts (
  account_number INTPRIMARYKEY,
  customer_id INT,
  account_balance DECIMAL(10,2),
  account_status VARCHAR(20),FOREIGNKEY(customer_id)REFERENCES customers(customer_id));

创建风险信息表：

CREATETABLE risk_info (
  customer_id INTPRIMARYKEY,
  credit_score INT,
  delinquency_count INT,
  arrears_amount DECIMAL(10,2),FOREIGNKEY(customer_id)REFERENCES customers(customer_id));

查询客户的交易记录：

SELECT customers.name,transactions.transaction_date,transactions.transaction_amount,transactions.transaction_type
FROM customers
JOINtransactionsON customers.customer_id =transactions.customer_id;

查询客户的账户信息：

SELECT customers.name, accounts.account_number, accounts.account_balance, accounts.account_status
FROM customers
JOIN accounts ON customers.customer_id = accounts.customer_id;

结尾

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

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

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