PostgreSQL的CRUD操作

3 年 ago

宇, 华

5 minutes

确认版本

$ psql --version
$ psql -V

退出登录

postgres=# \q

创建数据库

在登录之前创建

$ createdb blogapp -U postgres -p 5433

登录后创建

postgres=# create database blogapp;

在编写SQL语句时，我们必须注意在语句末尾加上分号（;），否则SQL语句无法结束。

连接到已创建的数据库并登录

$ psql blogapp -U postgres -p 5433

登录后切换数据库

postgres=# \c blogapp;

查看数据库列表

在登录之前

$ psql -l

如果不安装在5432号端口上，登录前需要执行的PostgreSQL命令需要指定用户和端口作为选项。

$ psql -l -U postgres -p 5433

登录后

blogapp=# \l

删除数据库

登录前删除

$ dropdb blogapp -U postgres -p 5433

登录后删除

postgres=# drop database blogapp;

查看帮助

登录前

$ psql -?
$ psql --help

登录后

blogapp=# help
blogapp=# \?

创建桌子

blogapp=# create table posts(title varchar(255), body text);

确认桌子

blogapp=# \dt

确认表格的结构

blogapp=# \d posts

更改表格的名称

blogapp=# alter table posts rename to myposts;

删除表格

blogapp=# drop table myposts;

读取外部文件并执行

create table posts(title varchar(255), body text);

$ psql blogapp -U postgres -p 5433

blogapp=# \i commands.sql

在SQL文中的注释

-- 一行コメント

/*
複数行コメント
*/

各种不同的数据类型

文档
http://www.postgresql.org/docs/9.3/interactive/datatype.html

create table posts(
    title varchar(255), -- 可変長型で255文字まで
    body text -- 文字数制限なし
);

数字

整数: integer (int)
实数: real
连续: serial

请用中文重新表达以下内容，仅需提供一种选项：

文字

char(5): 有5个固定长度字符的限制
varchar(255): 最多可变长度为255个字符
text: 没有字符限制

authenticity

布尔值

可以指定的值

日期

日期：仅日期
时间：仅时间
时间戳：包含日期和时间

赛场上的限制（constraints）

文档
http://www.postgresql.org/docs/9.3/interactive/ddl-constraints.html
翻译为中文：
文件
http://www.postgresql.org/docs/9.3/interactive/ddl-constraints.html

create table posts(
    id serial primary key,
    title varchar(255) not null,
    body text check(length(body) > 5),
    is_draft boolean default TRUE,
    created timestamp default 'now'
);

primary key (not null, qnique): 主キーにする

插入文本

我们先重新构建桌子吧。

创建名为“poststable.sql”的外部文件。

create table posts(
    id serial primary key,
    title varchar(255) not null,
    body text check(length(body) > 5),
    is_draft boolean default TRUE,
    created timestamp default 'now'
);

连接至blogapp

$ psql blogapp -U postgres -p 5433

postgres=# \c blogapp;

删除posts表

blogapp=# drop table posts;

重建尾巴

blogapp=# \i commands.sql

确定

blogapp=# \dt
blogapp=# \d posts

插入数据

blogapp=# insert into posts (title, body) values ('title1', 'body11111');

确认数据

blogapp=# select * from posts;

插入数据

blogapp=# insert into posts (title, body) values ('title2', 'body22222');

查看数据 (chá shù jù)

blogapp=# select * from posts;

如果body的长度小于等于5个字符，就无法插入。

blogapp=# insert into posts (title, body) values ('title3', 'b3');

选择文件

显示所有项目

blogapp=# select * from posts;

定义新的表格

create table users (
    id serial primary key,
    name varchar(255),
    score real,
    team varchar(255)
);

插入几个数据

insert into users (name, score, team) values
('yamada', 5.5, 'red'),
('sato', 8.3, 'blue'),
('saito', 6.4, 'blue'),
('takahashi', 2.2, 'blue'),
('suzuki', 5.0, 'green'),
('nomura', 4.6, 'red'),
('imai', 4.7, 'green'),
('yamazaki', 7.1, 'red'),
('yamasaki', 3.9, 'green');

确认数据

blogapp=# \d users

blogapp=# select * from users;

开启扩展显示

当数据量较大时，将其纵向排列以便于查看。

blogapp=# \x

blogapp=# select * from users;

关掉扩展显示

再次执行。

blogapp=# \x

只获取某些字段

blogapp=# select name, score from users;

句子的地方。

获取分数为4.0以上的数据

blogapp=# select * from users where score > 4.0;

在比较中使用的运算符

!= : 異なる

获取得分为5的数据

blogapp=# select * from users where score = 5;

获取分数不等于5的数据。

blogapp=# select * from users where score != 5;

获取名为’yamada’的数据

blogapp=# select * from users where name = 'yamada';

用于模糊搜索的运算符。

– : 任意の一文字

获取以i结尾的name的最后一个字符的数据。

blogapp=# select * from users where name like '%i';

获取名为山崎（Yamazaki）和山崎（Yamasaki）的数据。

blogapp=# select * from users where name like 'yama_aki';

获取名为Sato和Saito的数据。

blogapp=# select * from users where name like 'sa%to';

排序、限制、偏移

将分数按升序排序并获取

blogapp=# select * from users order by score;

将得分按降序排序并获取

blogapp=# select * from users order by score desc;

将团队按降序排序并获取

blogapp=# select * from users order by team;

降序排序团队并按得分降序获取

blogapp=# select * from users order by team, score desc;

将团队和得分按降序进行排序并获取。

blogapp=# select * from users order by team desc, score desc;

限制数据获取的数量

blogapp=# select * from users limit 3;

获取从第4个项目开始的3个项目的数据

索引从0开始。

blogapp=# select * from users limit 3 offset 3;

获取前三名得分的数据。

blogapp=# select * from users order by score desc limit 3;

记录的汇总

获取users表的记录数

blogapp=# select count(*) from users;

获取使得team字段值唯一的数据

你可以从数据中删除重复项并获取结果。

blogapp=# select distinct team from users;

获取得分的总和

blogapp=# select sum(score) from users;

获取分数的最大值

blogapp=# select max(score) from users;

获取分数的最小值

blogapp=# select min(score) from users;

获取分数的平均值

blogapp=# select avg(score) from users;

获取每个团队的总计

blogapp=# select team, sum(score) from users group by team;

根据条件进行统计

blogapp=# select team, sum(score) from users group by team having sum(score) > 10.0;

功能

文档
http://www.postgresql.org/docs/9.3/static/functions.html

获取name的字符数

blogapp=# select name, length(name) from users;

合并文本字符串

blogapp=# select concat(name, ' (', team, ') ') from users;

更改字段名称并进行获取。

blogapp=# select concat(name, ' (', team, ') ') as namelabel from users;

获取字符串的第一个字符

blogapp=# select substring(team, 1, 1) from users;

blogapp=# select substring(team, 1, 1) as teaminitial from users;

获取随机数

生成从0到1之间的随机数。

blogapp=# select random();

随机获取一条记录。

blogapp=# select * from users order by random() limit 1;

更新记录，删除记录

更新yamada的分数

注意：如果不指定句子，将更新所有条目。

blogapp=# update users set score = 5.8 where name = 'yamada';

只有红队将得分加1。

blogapp=# update users set score = score + 1 where team = 'red';

可以指定多个where句。

blogapp=# update users set score = score + 1 where team = 'red' or team = 'green';

删除分数低于3.0的数据。

blogapp=# delete from users where score < 3.0;

修改表结构

在表格中添加字段

blogapp=# alter table users add fullname varchar(255);

确认

blogapp=# \d users;

从表中删除字段。

blogapp=# alter table users drop fullname;

确认

blogapp=# \d users;

修改字段名称

blogapp=# alter table users rename name to myname;

确认

blogapp=# \d users;

更改字段的数据类型

blogapp=# alter table users alter myname type varchar(32);

确认

blogapp=# \d users;

添加索引。

搜索速度会变快，但更新和添加操作会变慢。

blogapp=# create index team_index on users(team);

确认

blogapp=# \d users;

删除索引

blogapp=# drop index team_index;

确认

blogapp=# \d users;

表格的合并

重新制作桌子

drop table users;
drop table posts;
create table posts(
    id serial primary key,
    user_id int not null,
    title varchar(255) not null,
    body text not null
);
insert into posts (user_id, title, body) values
(1, 'title1', 'body1'),
(1, 'title2', 'body2'),
(2, 'title3', 'body3'),
(5, 'title4', 'body4'),
(4, 'title5', 'body5');
create table users (
    id serial primary key,
    name varchar(255),
    score real,
    team varchar(255)
);
insert into users (name, score, team) values
('yamada', 5.5, 'red'),
('sato', 8.3, 'blue'),
('saito', 6.4, 'blue'),
('takahashi', 2.2, 'blue'),
('suzuki', 5.0, 'green'),
('nomura', 4.6, 'red'),
('imai', 4.7, 'green'),
('yamazaki', 7.1, 'red'),
('yamasaki', 3.9, 'green');

确认

blogapp=# select * from users;
blogapp=# select * from posts;

从多个表中获取数据 de

blogapp=# select users.name, posts.title from users, posts where users.id = posts.user_id;

将表格名称缩短并合并

blogapp=# select u.name, p.title from users u, posts p where u.id = p.user_id;

增加条件

blogapp=# select u.name, p.title from users u, posts p where u.id = p.user_id and u.id = 1;

这个角度或视角

创建视图

blogapp=# create view yamada_posts as select u.name, p.title from users u, posts p where u.id = p.user_id and u.id = 1;

确认视图

blogapp=# \dv

从视图中获取数据

blogapp=# select * from yamada_posts;

移除视图

blogapp=# drop view yamada_posts;

确认

blogapp=# \dv

交易

开始交易

blogapp=# begin;

从山田的分数中减去1。

blogapp=# update users set score = score - 1.0 where name = 'yamada';

给Sato的分数加1

blogapp=# update users set score = score + 1.0 where name = 'sato';

确认

blogapp=# select * from users;

表达

blogapp=# commit;

确认

blogapp=# select * from users;

开始交易

blogapp=# begin;

从山田的分数中减去1。

blogapp=# update users set score = score - 1.0 where name = 'yamada';

给sato的得分加1

blogapp=# update users set score = score + 1.0 where name = 'sato';

确认

blogapp=# select * from users;

取消

blogapp=# rollback;

确认

blogapp=# select * from users;