创建表
专栏内容:
- postgresql内核源码分析
- 手写数据库toadb
- 并发编程
开源贡献:
- toadb开源库
个人主页:我的主页
管理社区:开源数据库
座右铭:天行健,君子以自强不息;地势坤,君子以厚德载物.
系列文章
- 入门准备
- postgrersql基础架构
- 快速使用
- 初始化集群
- 数据库服务管理
- psql客户端使用
- pgAdmin图形化客户端
- 数据库的使用
- 创建数据库
- 数据库操作
- 表的使用
- 表的创建
- 表的操作
前言
postgresql 数据库是一款通用的关系型数据,在开源数据库中能与商业数据媲美,在业界也越来越流行。
因为是开源数据库,不仅公开源码,还有很多使用案例,好用的插件,所以它的慢慢变成了数据库的先驱和标准,通过postgresql可以很好从使用到原理,彻底搞懂;
如果是学习编程,也可以学到丰富的编程知识,数据结构,编程技巧,它里面还有很多精妙的架构设计,分层思想,可以灵活定制的思想。
本专栏主要介绍postgresql 入门使用,数据库维护管理,通过这些使用来了解数据库原理,慢慢了解postgresql是什么样的数据库,能做那些事情,以及如何做好服务,最关键的是这些知识都是面试的必备项。
概述
使用数据库最常用的操作就是创建表,增删改查数据,表作为真正的数据载体的逻辑单元,联系着数据库的各个模块;
表的定义要符合完整性的要求,完整性包括:实体完整性,数据唯一性;域完整性,各字段取值合法有效;参照完整性,表与表之间的关联约束;用户自定义完整性,根据业务需求,额外定义的一些约束条件;
下面我们一起来看看如何创建一张符合我们业务需求的表;
- 创建表的 SQL 语法
- 定义字段和数据类型
- 设置主键和外键
- 创建索引
创建表的语法
在postgresql中,表有两种存储方式,
一种就是常见的普通表,会存储在对应的database目录下,在database内是共享的,也就是说只要有权限都可以访问;
还有一种时临时表,临时表会在会话结束或者事务结束时被自动删除,也就是临时使用一下,它只能在当前会话中使用,其它会话是看不到它的存在;
创建普通表
基本语法就是 CREATE TABLE 表名(类型 列名,...);
其中表名,类型,列名是可以替换的,类型是数据库中已经预定义好的;名称最好不要超过64字符;
CREATE TABLE COMPANY (ID INT PRIMARY KEY NOT NULL,NAME TEXT NOT NULL,AGE INT NOT NULL,ADDRESS CHAR(50),SALARY REAL);
如果要指定schema,那么表名需要写成 schema名称.表名 的形式,这样就会创建到指定的schema下面;
创建临时表
临时表创建时,要使用关键字temp
,此时创建表在另一个客户端登录时,是看不到的;当前客户端退出后,也会自动删除。
create temp table result(id int, slary real);
临时表主要用于一些中间结果的存储,比如需要多表联合后计算一些数据,可以先把查询结果放到临时表,这些可以慢慢计算;
字段定义和数据类型
支持的数据类型
以下是postgresql支持的常见的类型列表:
名称 | 别名 | 描述 |
---|---|---|
bigint | int8 | 有符号的8字节整数 |
bigserial | serial8 | 自动增长的8字节整数 |
bit [ (n) ] | 定长位串 | |
bit varying [ (n) ] | varbit [ (n) ] | 变长位串 |
boolean | bool | 逻辑布尔值(真/假) |
box | 平面上的普通方框 | |
bytea | 二进制数据(“字节数组”) | |
character [ (n) ] | char [ (n) ] | 定长字符串 |
character varying [ (n) ] | varchar [ (n) ] | 变长字符串 |
cidr | IPv4或IPv6网络地址 | |
circle | 平面上的圆 | |
date | 日历日期(年、月、日) | |
double precision | float8 | 双精度浮点数(8字节) |
inet | IPv4或IPv6主机地址 | |
integer | int, int4 | 有符号4字节整数 |
interval [ fields ] [ § ] | 时间段 | |
json | 文本 JSON 数据 | |
jsonb | 二进制 JSON 数据,已分解 | |
line | 平面上的无限长的线 | |
lseg | 平面上的线段 | |
macaddr | MAC(Media Access Control)地址 | |
macaddr8 | MAC(Media Access Control)地址(EUI-64格式) | |
money | 货币数量 | |
numeric [ (p, s) ] | decimal [ (p, s) ] | 可选择精度的精确数字 |
path | 平面上的几何路径 | |
pg_lsn | PostgreSQL日志序列号 | |
pg_snapshot | 用户级事务ID快照 | |
point | 平面上的几何点 | |
polygon | 平面上的封闭几何路径 | |
real | float4 | 单精度浮点数(4字节) |
smallint | int2 | 有符号2字节整数 |
smallserial | serial2 | 自动增长的2字节整数 |
serial | serial4 | 自动增长的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语句添加主键,可以使用以下语法:
ALTER TABLE table_nameADD PRIMARY KEY (column_name);
其中,table_name
是要添加主键的表名,column_name
是要指定为主键的列名。
请注意,添加主键之前,确保表中没有重复的值存在于该列中。如果存在重复值,将无法添加主键。
示例:
假设有一个名为users
的表,其中有一个名为id
的列,您想将其指定为主键。可以使用以下语句:
ALTER TABLE usersADD PRIMARY KEY (id);
这将使id
列成为users
表的主键。
外键
外键是指一个表中的一个或多个字段,它们的值与另一个表中的字段值相对应,用来表示两个表之间的联系。
需要注意的是,一个表的外键,在引用表中一定是主键,这样对应关系是明确的;
外键的创建也同样可以CREATE TABLE或ALTER TABLE语句中定义;
要使用ALTER TABLE语句添加外键,可以使用以下语法:
示例:
假设有两个表:学生表(students)和课程表(courses)。学生表中有一个学生ID字段(student_id),课程表中有一个课程ID字段(course_id)。如果要记录每个学生所选的课程,可以在学生表中添加一个外键,指向课程表中的课程ID字段。可以使用以下语句:
ALTER TABLE studentsADD FOREIGN KEY (student_id) REFERENCES courses(course_id);
这将使学生表中的student_id列成为外键,指向课程表中的course_id列。
创建索引
索引是为了加速对表中数据行的检索而创建的一种分散的存储结构。它是针对表而建立的,由数据页面以外的索引页面组成,每个索引页面中的行都会含有逻辑指针,以便加速检索物理数据。索引数据一般非常小,可以快速加载到内存进行查找,再根据查找到的索引项找到数据表的数据项;
创建索引的语句示例:
CREATE INDEX index_name ON table_name (column_name);
语句中,index_name
是索引的名称,table_name
是表的名称,column_name
是要创建索引的列的名称。可以根据需要指定多个列,以创建复合索引。
索引创建时,默认创建的索引算法为btree,当然还有基于hash的索引,gin索引,gist索引等;这些在之后会介绍到;
选择经常用于查询条件的列作为索引列,这样索引才能被有效利用。如果查询条件中不包含索引列,那么索引将不会被使用。
案例演示
设计银行信用卡中心的数据库需要考虑以下几个方面:
- 客户信息:包括客户的姓名、身份证号码、联系方式等。
- 交易信息:包括交易时间、交易金额、交易类型等。
- 账户信息:包括账户号码、账户余额、账户状态等。
- 风险信息:包括信用评分、逾期次数、欠款金额等。
以下是一些银行信用卡中心的数据库设计示例和相应的SQL语句:
创建客户表:
CREATE TABLE customers (customer_id INT PRIMARY KEY,name VARCHAR(50),address VARCHAR(100),phone_number VARCHAR(20),email VARCHAR(50));
创建交易表:
CREATE TABLE transactions (transaction_id INT PRIMARY KEY,customer_id INT,transaction_date DATE,transaction_amount DECIMAL(10,2),transaction_type VARCHAR(20),FOREIGN KEY (customer_id) REFERENCES customers(customer_id));
创建账户表:
CREATE TABLE accounts (account_number INT PRIMARY KEY,customer_id INT,account_balance DECIMAL(10,2),account_status VARCHAR(20),FOREIGN KEY (customer_id) REFERENCES customers(customer_id));
创建风险信息表:
CREATE TABLE risk_info (customer_id INT PRIMARY KEY,credit_score INT,delinquency_count INT,arrears_amount DECIMAL(10,2),FOREIGN KEY (customer_id) REFERENCES customers(customer_id));
查询客户的交易记录:
SELECT customers.name, transactions.transaction_date, transactions.transaction_amount, transactions.transaction_typeFROM customersJOIN transactions ON customers.customer_id = transactions.customer_id;
查询客户的账户信息:
SELECT customers.name, accounts.account_number, accounts.account_balance, accounts.account_statusFROM customersJOIN accounts ON customers.customer_id = accounts.customer_id;
结尾
非常感谢大家的支持,在浏览的同时别忘了留下您宝贵的评论,如果觉得值得鼓励,请点赞,收藏,我会更加努力!
作者邮箱:study@senllang.onaliyun.com
如有错误或者疏漏欢迎指出,互相学习。
注:未经同意,不得转载!