数据库 (PostgreSQL)
PostgreSQL是一种关系型数据库管理系统。
- RDBMS(リレーショナルデータベース管理システム)
RDB是以表格（类似于Excel）的形式返回数据的。可以将多个表格进行关联。
用中文将以下内容进行泛化翻译:
如何从本地连接至PostgreSQL
$ psql -h localhost -p 5432 -U postgres -d postgres
回到本地的命令。
postgres=# \q
如果要中途停止的话，可以用分号来结束。
postgres=# k
postgres-# ;
ERROR: syntax error at or near "k"
LINE 1: k
^
postgres=#
创建表格
创建一张名为”posts”的表，该表包含两个字段：标题（数据类型为varchar，长度为255）和正文（数据类型为text）。
postgres=# create table posts (title varchar(255), body text);
CREATE TABLE
¥dt
※表格列表
postgres=# \dt
List of relations
Schema | Name | Type | Owner
--------+-------+-------+----------
public | posts | table | postgres
(1 row)
显示$¥d个帖子
显示表格的列
postgres=# \d posts
Table "public.posts"
Column | Type | Collation | Nullable | Default
--------+------------------------+-----------+----------+---------
title | character varying(255) | | |
body | text | | |
将表posts重命名为myposts。
postgres=# alter table posts rename to myposts;
ALTER TABLE
删除表myposts。
postgres=# drop table myposts;
DROP TABLE
使用外部文件的方式
postgres=# \i commands.sql
※ターミナル開いてcommands.sqlファイルを作成して、ファイルの中身に
create table posts (title varchar(255), body text);
を記載して、上記コマンドを実行するとテーブルが作成される
数据类型
-
- 数値: integer(int),real(実数),serial(連番)
-
- 文字: char(5)-固定文字の場合, varchar(255)-上限付きの可変長,
-
- ※読み方、char = キャラ
-
- 真偽: boolean TRUE FALSE t f
- 日付: date(日付), time(時間),timestamp(両方)
让我们在场地上加上一些限制。
-
- 制約
not null(入力必須のものにつける)
unique(重複した値を許さない、同じEメールアドレスを許さない)
check(内容をチェックする)
default
primary key,(not null, unique)(一意に決めるための主キー)
※テーブルにつき1つだけしか設定できない
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',(現在時刻に設定)
);
让我们尝试使用insert函数。
将标题为’title’，内容为’body1111’的帖子插入到posts表中。
postgres=# insert into posts (title, body) values ('title', 'body1111');
INSERT 0 1
查看所有帖子的记录。
postgres=# select * from posts;
title | body
-------+----------
title | body1111
(1 row)
让我们尝试使用select语句
添加值
postgres=# create table users (
id serial primary key,
name varchar(255),
score real,
team varchar(255)
);
insert into users (name, score, team) values
('aa', 5.5, 'red'),
('ss', 8.3, 'blue'),
('dd', 2.2, 'blue'),
('ff', 5.0, 'green'),
('gg', 4.6, 'red'),
('hh', 4.7, 'green');
CREATE TABLE
postgres-# \dt
List of relations
Schema | Name | Type | Owner
--------+-------+-------+----------
public | posts | table | postgres
public | users | table | postgres
(2 rows)
确认已设置了 $ \d 个用户字段
* 确保已启用扩展显示 $ \x。如果显示不同，请尝试输入两次 $ \x 以返回。
postgres-# \d users
Table "public.users"
Column | Type | Collation | Nullable | Default
--------+------------------------+-----------+----------+-----------------------------------
id | integer | | not null | nextval('users_id_seq'::regclass)
name | character varying(255) | | |
score | real | | |
team | character varying(255) | | |
Indexes:
"users_pkey" PRIMARY KEY, btree (id)
从用户表中选择所有字段。
postgres=# select * from users;
id | name | score | team
----+------+-------+-------
1 | aa | 5.5 | red
2 | ss | 8.3 | blue
3 | dd | 2.2 | blue
4 | ff | 5 | green
5 | gg | 4.6 | red
6 | hh | 4.7 | green
(6 rows)
选择用户表中的姓名和分数字段。
postgres=# select name, score from users;
name | score
------+-------
aa | 5.5
ss | 8.3
dd | 2.2
ff | 5
gg | 4.6
hh | 4.7
(6 rows)
让我们试试使用”where”句型
选择*从users表中 where score > 4.0 的数据。
postgres=# select * from users where score > 4.0;
id | name | score | team
----+------+-------+-------
1 | aa | 5.5 | red
2 | ss | 8.3 | blue
4 | ff | 5 | green
5 | gg | 4.6 | red
6 | hh | 4.7 | green
(5 rows)
根据用户的得分，选择评分为5.0的所有用户。
根据用户的得分，选择评分不为5.0的所有用户。
从用户表中选择全部记录，其中名字等于 ‘ff’ 的记录。
postgres=# select * from users where name = 'ff'
postgres-# ;
id | name | score | team
----+------+-------+-------
4 | ff | 5 | green
(1 row)
請從用戶表中選擇所有名字以”a”結尾的用戶。
※ %i = 提取以”a”結尾的項目。
postgres=# select * from users where name like '%a'
postgres-# ;
id | name | score | team
----+------+-------+------
1 | aa | 5.5 | red
(1 row)
根据指定的最初和最后字符进行筛选，从用户表中选择所有满足条件的记录。
请使用order by、limit、offset来进行排序、限制、偏移。
根据得分对用户进行排序，选择所有字段。
postgres=# select * from users order by score;
id | name | score | team
----+------+-------+-------
3 | dd | 2.2 | blue
5 | gg | 4.6 | red
6 | hh | 4.7 | green
4 | ff | 5 | green
1 | aa | 5.5 | red
2 | ss | 8.3 | blue
(6 rows)
选择 * 数据库表中的所有数据，按照分数倒序排列。
postgres=# select * from users order by score desc;
id | name | score | team
----+------+-------+-------
2 | ss | 8.3 | blue
1 | aa | 5.5 | red
4 | ff | 5 | green
6 | hh | 4.7 | green
5 | gg | 4.6 | red
3 | dd | 2.2 | blue
(6 rows)
按照团队排序（按字母顺序）从用户中选择全部数据。
postgres=# select * from users order by team
postgres-# ;
id | name | score | team
----+------+-------+-------
2 | ss | 8.3 | blue
3 | dd | 2.2 | blue
4 | ff | 5 | green
6 | hh | 4.7 | green
1 | aa | 5.5 | red
5 | gg | 4.6 | red
(6 rows)
请按照团队和得分降序排列选择所有用户。
postgres=# select * from users order by team, score desc;
id | name | score | team
----+------+-------+-------
2 | ss | 8.3 | blue
3 | dd | 2.2 | blue
4 | ff | 5 | green
6 | hh | 4.7 | green
1 | aa | 5.5 | red
5 | gg | 4.6 | red
(6 rows)
如果想限制件数，请限制
$ 从用户表中选取前3条数据。
postgres=# select * from users limit 3;
id | name | score | team
----+------+-------+------
1 | aa | 5.5 | red
2 | ss | 8.3 | blue
3 | dd | 2.2 | blue
(3 rows)
$ select * from users limit 3 offset 3;
※ 如需从第4个项目开始显示，
postgres=# select * from users limit 3 offset 3;
id | name | score | team
----+------+-------+-------
4 | ff | 5 | green
5 | gg | 4.6 | red
6 | hh | 4.7 | green
(3 rows)
从用户表中选择所有数据，按照分数降序排列，只显示前三个人的信息。
postgres=# select * from users order by score desc limit 3;
id | name | score | team
----+------+-------+-------
2 | ss | 8.3 | blue
1 | aa | 5.5 | red
4 | ff | 5 | green
(3 rows)
让我们来汇总记录
选择用户表中的所有字段。
postgres=# select * from users;
id | name | score | team
----+------+-------+-------
1 | aa | 5.5 | red
2 | ss | 8.3 | blue
3 | dd | 2.2 | blue
4 | ff | 5 | green
5 | gg | 4.6 | red
6 | hh | 4.7 | green
(6 rows)
$ select count(*) from users;
※ 如果想知道记录有多少条的话
postgres=# select count(*) from users;
count
-------
6
(1 row)
$ 从用户中选择不同的团队;
※ 如果想知道团队中有多少种颜色，请
postgres=# select distinct team from users;
team
-------
blue
red
green
(3 rows)
从用户中选择分数的总和。
postgres=# select sum(score) from users;
sum
------
30.3
(1 row)
请选择用户中的最高分数。
postgres=# select max(score) from users;
max
-----
8.3
(1 row)
$ select avg(score) from users;
※ 获取平均值
postgres=# select avg(score) from users;
avg
-------------------
5.049999992052714
(1 row)
select team, sum(score) from users group by team;
※ 如果想要计算得分总和并按团队分组，则可选择：
postgres=# select team, sum(score) from users group by team;
team | sum
-------+------
blue | 10.5
red | 10.1
green | 9.7
(3 rows)
选择团队，总分数大于10.0的选手，并对团队进行分组和求和。
postgres=# select team, sum(score) from users group by team having sum(score) > 10.0;
team | sum
------+------
blue | 10.5
red | 10.1
(2 rows)
让我们尝试使用方便的函数
选择*从用户表中显示全部字段。
postgres=# select * from users;
id | name | score | team
----+------+-------+-------
1 | aa | 5.5 | red
2 | ss | 8.3 | blue
3 | dd | 2.2 | blue
4 | ff | 5 | green
5 | gg | 4.6 | red
6 | hh | 4.7 | green
(6 rows)
$ select name, length(name) from users;
※ 如果想查詢名字的字符數，
postgres=# select name, length(name) from users;
name | length
------+--------
aa | 2
ss | 2
dd | 2
ff | 2
gg | 2
hh | 2
(6 rows)
请选择将用户表中的name和team字段进行连接，格式为姓名（团队名）。
postgres=# select concat(name, ' (', team, ')') from users;
aa (red)
ss (blue)
dd (blue)
ff (green)
gg (red)
hh (green)
$ select concat(name, ‘ (‘, team, ‘)’) as namelabel from users;
※ 更改标签的方法，
postgres=# select concat(name, ' (', team, ')') as namelabel from users;
aa (red)
ss (blue)
dd (blue)
ff (green)
gg (red)
hh (green)
从用户中选择团队的第一个字符。
postgres=# select substring(team, 1, 1) from users;
r
b
b
g
r
g
从用户中随机选择1人并返回。
postgres=# select *from users order by random() limit 1;
4 | ff | 5 | green
postgres=# select *from users order by random() limit 1;
5 | gg | 4.6 | red
postgres=# select *from users order by random() limit 1;
4 | ff | 5 | green
让我们尝试使用更新和删除功能。
选择 * 从用户表中；显示所有字段
postgres=# select * from users;
id | name | score | team
----+------+-------+-------
1 | aa | 5.5 | red
2 | ss | 8.3 | blue
3 | dd | 2.2 | blue
4 | ff | 5 | green
5 | gg | 4.6 | red
6 | hh | 4.7 | green
(6 rows)
如果要更改分数，请在指定表名后使用[set]，写下要更改的字段，然后使用等号将其与值连接起来：
$ update users set score = 5.8 where name = ‘aa’;
※ 如果要更改分数，请在指定表名后使用[set]，写下要更改的字段，然后使用等号将其与值连接起来。
postgres=# update users set score = 5.8 where name = 'aa';
UPDATE 1
postgres=# select * from users;
id | name | score | team
----+------+-------+-------
2 | ss | 8.3 | blue
3 | dd | 2.2 | blue
4 | ff | 5 | green
5 | gg | 4.6 | red
6 | hh | 4.7 | green
1 | aa | 5.8 | red
(6 rows)
更新用户表，当team字段为’red’时，将分数score增加1。
postgres=# update users set score = score + 1 where team = 'red';
UPDATE 2
postgres=# select * from users;
id | name | score | team
----+------+-------+-------
2 | ss | 8.3 | blue
3 | dd | 2.2 | blue
4 | ff | 5 | green
6 | hh | 4.7 | green
5 | gg | 5.6 | red
1 | aa | 6.8 | red
(6 rows)
将用户表中，当团队为‘red’或‘green’时，将得分加1。
postgres=# update users set score = score + 1 where team = 'red' or team = 'green';
UPDATE 4
postgres=# select * from users;
id | name | score | team
----+------+-------+-------
2 | ss | 8.3 | blue
3 | dd | 2.2 | blue
4 | ff | 6 | green
6 | hh | 5.7 | green
5 | gg | 6.6 | red
1 | aa | 7.8 | red
(6 rows)
从用户表中删除分数低于3.0的用户。
postgres=# delete from users where score < 3.0;
DELETE 1
postgres=# select * from users;
id | name | score | team
----+------+-------+-------
2 | ss | 8.3 | blue
4 | ff | 6 | green
6 | hh | 5.7 | green
5 | gg | 6.6 | red
1 | aa | 7.8 | red
(5 rows)
让我们尝试改变桌子的结构
- まずはこういった型で用意されていることを確認
postgres=# \d users;
Table "public.users"
Column | Type | Collation | Nullable | Default
--------+------------------------+-----------+----------+-----------------------------------
id | integer | | not null | nextval('users_id_seq'::regclass)
name | character varying(255) | | |
score | real | | |
team | character varying(255) | | |
Indexes:
"users_pkey" PRIMARY KEY, btree (id)
$ 在用户表中添加一个fullname字段，类型为varchar(255)。
postgres=# alter table users add fullname varchar(255);
ALTER TABLE
postgres=# \d users;
Table "public.users"
Column | Type | Collation | Nullable | Default
----------+------------------------+-----------+----------+-----------------------------------
id | integer | | not null | nextval('users_id_seq'::regclass)
name | character varying(255) | | |
score | real | | |
team | character varying(255) | | |
fullname | character varying(255) | | |
Indexes:
"users_pkey" PRIMARY KEY, btree (id)
请问您的需求是在用户表中删除 “fullname” 字段吗？
postgres=# alter table users drop fullname;
ALTER TABLE
postgres=# \d users;
Table "public.users"
Column | Type | Collation | Nullable | Default
--------+------------------------+-----------+----------+-----------------------------------
id | integer | | not null | nextval('users_id_seq'::regclass)
name | character varying(255) | | |
score | real | | |
team | character varying(255) | | |
Indexes:
"users_pkey" PRIMARY KEY, btree (id)
$ 将表users中的name列更名为myname。
※ 如果将名称从name改为myname的话，
postgres=# alter table users rename name to myname;
ALTER TABLE
postgres=# \d users;
Table "public.users"
Column | Type | Collation | Nullable | Default
--------+------------------------+-----------+----------+-----------------------------------
id | integer | | not null | nextval('users_id_seq'::regclass)
myname | character varying(255) | | |
score | real | | |
team | character varying(255) | | |
Indexes:
"users_pkey" PRIMARY KEY, btree (id)
如果要修改用户表中myname列的类型为varchar(32)，请执行以下命令：alter table users alter myname type varchar(32)。
postgres=# alter table users alter myname type varchar(32);
ALTER TABLE
postgres=# \d users;
Table "public.users"
Column | Type | Collation | Nullable | Default
--------+------------------------+-----------+----------+-----------------------------------
id | integer | | not null | nextval('users_id_seq'::regclass)
myname | character varying(32) | | |
score | real | | |
team | character varying(255) | | |
Indexes:
"users_pkey" PRIMARY KEY, btree (id)
在用户表上创建团队索引，索引名为team_index。
teamにindexをつける方法、
インデックスという、テーブルについている索引なのですがこれの追加、削除方法、
primary keyには最初からついているのですが、これがついていると検索が早くなります。
運用していったらteamで検索されることが多かったので、teamにインデックスをつけてみよう
postgres=# create index team_index on users(team);
CREATE INDEX
postgres=# \d users;
Table "public.users"
Column | Type | Collation | Nullable | Default
--------+------------------------+-----------+----------+-----------------------------------
id | integer | | not null | nextval('users_id_seq'::regclass)
myname | character varying(32) | | |
score | real | | |
team | character varying(255) | | |
Indexes:
"users_pkey" PRIMARY KEY, btree (id)
"team_index" btree (team)
若要删除索引，请执行以下操作：
$ alter table users drop index myname;
※
postgres=# drop index team_index;
DROP INDEX
postgres=# \d users;
Table "public.users"
Column | Type | Collation | Nullable | Default
--------+------------------------+-----------+----------+-----------------------------------
id | integer | | not null | nextval('users_id_seq'::regclass)
myname | character varying(32) | | |
score | real | | |
team | character varying(255) | | |
Indexes:
"users_pkey" PRIMARY KEY, btree (id)
让我们尝试处理多张表格
※ 創建多個表格。
postgres=# create table users (
id serial primary key,
name varchar(255),
score real,
team varchar(255)
);
insert into users (name, score, team) values
('aa', 5.5, 'red'),
('ss', 8.3, 'blue'),
('dd', 2.2, 'blue'),
('ff', 5.0, 'green'),
('gg', 4.6, 'red'),
('hh', 4.7, 'green');
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'),
('2', 'title2', 'body2'),
('3', 'title3', 'body3'),
('4', 'title4', 'body4'),
('5', 'title5', 'body5'),
('6', 'title6', 'body6');
需要进行确认，是否已经完成制作。
postgres=# \d
List of relations
Schema | Name | Type | Owner
--------+--------------+----------+----------
public | posts | table | postgres
public | posts_id_seq | sequence | postgres
public | users | table | postgres
public | users_id_seq | sequence | postgres
(4 rows)
postgres=# select * from users;
id | name | score | team
----+------+-------+-------
1 | aa | 5.5 | red
2 | ss | 8.3 | blue
3 | dd | 2.2 | blue
4 | ff | 5 | green
5 | gg | 4.6 | red
6 | hh | 4.7 | green
(6 rows)
postgres=# select * from posts;
id | user_id | title | body
----+---------+--------+-------
1 | 1 | title1 | body1
2 | 2 | title2 | body2
3 | 3 | title3 | body3
4 | 4 | title4 | body4
5 | 5 | title5 | body5
6 | 6 | title6 | body6
(6 rows)
选择用户的姓名和帖子的标题，来自于用户表和帖子表中，满足用户表的id等于帖子表的user_id。
誰がどの記事を書いたか引っ張ってくる、
複数のテーブルから引っ張ってくるには、テーブル名とフィールド名を .(ドット)で繋いであげればOKです。
結び付きを指定してあげないといけないので,whereをつけてuser.idとposts.user_idが一緒
postgres=# select users.name, posts.title from users, posts where users.id = posts.user_id;
name | title
------+--------
aa | title1
ss | title2
dd | title3
ff | title4
gg | title5
hh | title6
(6 rows)
SELECT u.name, p.title FROM users AS u, posts AS p WHERE u.id = p.user_id;
postgres=# select u.name, p.title from users u, posts p where u.id = p.user_id;
name | title
------+--------
aa | title1
ss | title2
dd | title3
ff | title4
gg | title5
hh | title6
(6 rows)
选择 u.name, p.title FROM 用户 u, 帖子 p WHERE u.id = p.user_id 并且 u.id = 1
ユーザーごとの記事一覧ページ作成、
andでuserのidが1,つまり{aa}君のものしか知りたくないという場合は、このような条件句を与えること
postgres=# select u.name, p.title from users u, posts p where u.id = p.user_id and u.id=1;
name | title
------+--------
aa | title1
(1 row)
让我们尝试使用视图
以以下方式在中文中进行习惯性转述，只需要一个选项：
$ 创建视图aa_posts作为(さっきのの长文)
長文をviewとして、簡単に出せるようにする、
postgres=# create view aa_posts as
postgres-# select u.name, p.title from users u, posts p where u.id = p.user_id and u.id=1;
CREATE VIEW
postgres=# \dv
List of relations
Schema | Name | Type | Owner
--------+----------+------+----------
public | aa_posts | view | postgres
(1 row)
选择 * 从 aa_posts 表中。
viewで作成したものを出す方法、
postgres=# select * from aa_posts;
name | title
------+--------
aa | title1
(1 row)
删除视图aa_post。
viewを削除する場合、
postgres=# drop view aa_posts;
DROP VIEW
postgres=# \dv
Did not find any relations.
让我们尝试使用交易。
- 複数の処理を必ずまとめて行う処理のこと
开始;
途中に変な処理がいってこない、不整合が起きないための仕組み、
トランザクショの開始には[begin]として、その後に処理をいくつか書いていく
postgres=# begin;
BEGIN
postgres=*# update users set score = score -1.0 where name = 'aa';
UPDATE 1
postgres=*# update users set score = score +1.0 where name = 'ss';
UPDATE 1
postgres=*# commit;
COMMIT
回滚
今までの変更をまとめて取り消される、
postgres=# begin;
BEGIN
postgres=*# update users set score = score -1.0 where name = 'aa';
UPDATE 1
postgres=*# update users set score = score +1.0 where name = 'ss';
UPDATE 1
postgres=*# rollback;
ROLLBACK
用npm启动1。
・npm init -y
パッケージ.jsを初期化して使用していく
・npm i express nodemon pg
・npm start
创建server.js文件
const express = require("express");
const app = express();
const PORT = 5000;
app.listen(PORT, () => {
console.log("server is runing on PORT" + PORT);
});
创建API
const express = require("express");
const app = express();
const PORT = 5000;
app.get("/", (req, res) => {
res.send("Hello Express");
});
app.listen(PORT, () => {
console.log("server is runing on PORT" + PORT);
});
※reg=リクエスト
※res=レスポンス
确认是否能够连接到http://localhost:5000/。
安装PostgreSQL
创建数据库
postgres=# CREATE DATABASE users;
※作成したら /lでデータベース一覧表示、2個目の記事参照
## テーブルクリア
postgres=# ¥l cls
## テーブル作成
postgres=# ¥c users
### usersテーブル作成
users=# CREATE TABLE users (
## フィールド作成
users(# ID serial primary key,
users(# name varchar(255),
users(# email varchar(255),
users(# age int);
※serial=連番
※primary key=主キー(ユーザーを代表する番号)
※varchar(255)=255文字以内(文字列)
※int=整数値
# テーブル確認
users(# ¥dt
使用MySQL进行数据插入，执行SQL语句。
### usersテーブル中身作成
users=# insert into users (name, email, age)
※insert=挿入
users-# values ('shincode', 'shinncode@gmail.com', 25), ('testUser', 'test@gmail.com', 34);
## 作成した情報を出力する方法
users=# select * from users;
※ *=全て
如何从VScode中提取数据并与服务器协作输出？
创建db.js
const Pool = require("pg").Pool;
const pool = new Pool({
user: "postgres",
host: "localhost",
database: "users",
password: "password",
port: 5432,
});
module.exports = pool;
在server.js中添加
const express = require("express");
const app = express();
const PORT = 5000;
//jsonを使用する場合は、ミドルウェアの設定を行う必要がある
app.use(express.json());
app.get("/", (req, res) => {
res.send("Hello Express");
});
//ユーザー情報を全て取得する
app.get("/users", (req, res) => {
pool.query("SELECT * FROM users", (error, results) => {
if (error) throw error;
return res.status(200).json(results.rows);
});
});
app.listen(PORT, () => {
console.log("server is runing on PORT" + PORT);
});
