{"id":41358,"date":"2023-01-08T07:15:54","date_gmt":"2023-04-11T20:45:57","guid":{"rendered":"https:\/\/www.silicloud.com\/zh\/blog\/%e6%95%b0%e6%8d%ae%e5%ba%93-postgresql\/"},"modified":"2024-04-29T09:01:42","modified_gmt":"2024-04-29T01:01:42","slug":"%e6%95%b0%e6%8d%ae%e5%ba%93-postgresql","status":"publish","type":"post","link":"https:\/\/www.silicloud.com\/zh\/blog\/%e6%95%b0%e6%8d%ae%e5%ba%93-postgresql\/","title":{"rendered":"\u6570\u636e\u5e93 (PostgreSQL)"},"content":{"rendered":"<h1>PostgreSQL\u662f\u4e00\u79cd\u5173\u7cfb\u578b\u6570\u636e\u5e93\u7ba1\u7406\u7cfb\u7edf\u3002<\/h1>\n<ul class=\"post-ul\">RDBMS(\u30ea\u30ec\u30fc\u30b7\u30e7\u30ca\u30eb\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u7ba1\u7406\u30b7\u30b9\u30c6\u30e0)<\/ul>\n<div><img decoding=\"async\" class=\"post-images\" title=\"\" src=\"https:\/\/cdn.silicloud.com\/blog-img\/blog\/img\/657d469337434c4406ca5988\/2-0.jpeg\" alt=\"\u30b9\u30af\u30ea\u30fc\u30f3\u30b7\u30e7\u30c3\u30c8 2022-08-26 17 33 48\" \/><\/div>\n<p>RDB\u662f\u4ee5\u8868\u683c\uff08\u7c7b\u4f3c\u4e8eExcel\uff09\u7684\u5f62\u5f0f\u8fd4\u56de\u6570\u636e\u7684\u3002\u53ef\u4ee5\u5c06\u591a\u4e2a\u8868\u683c\u8fdb\u884c\u5173\u8054\u3002<\/p>\n<h1>\u7528\u4e2d\u6587\u5c06\u4ee5\u4e0b\u5185\u5bb9\u8fdb\u884c\u6cdb\u5316\u7ffb\u8bd1:<\/h1>\n<p>\u5982\u4f55\u4ece\u672c\u5730\u8fde\u63a5\u81f3PostgreSQL<\/p>\n<pre class=\"post-pre\"><code>$ psql -h localhost -p 5432 -U postgres -d postgres\r\n<\/code><\/pre>\n<p>&nbsp;<\/p>\n<h3>\u56de\u5230\u672c\u5730\u7684\u547d\u4ee4\u3002<\/h3>\n<pre class=\"post-pre\"><code>postgres=# \\q\r\n<\/code><\/pre>\n<h2>\u5982\u679c\u8981\u4e2d\u9014\u505c\u6b62\u7684\u8bdd\uff0c\u53ef\u4ee5\u7528\u5206\u53f7\u6765\u7ed3\u675f\u3002<\/h2>\n<pre class=\"post-pre\"><code>postgres=# k\r\npostgres-# ;\r\nERROR:  syntax error at or near \"k\"\r\nLINE 1: k\r\n        ^\r\npostgres=# \r\n<\/code><\/pre>\n<h2>\u521b\u5efa\u8868\u683c<\/h2>\n<p>\u521b\u5efa\u4e00\u5f20\u540d\u4e3a&#8221;posts&#8221;\u7684\u8868\uff0c\u8be5\u8868\u5305\u542b\u4e24\u4e2a\u5b57\u6bb5\uff1a\u6807\u9898\uff08\u6570\u636e\u7c7b\u578b\u4e3avarchar\uff0c\u957f\u5ea6\u4e3a255\uff09\u548c\u6b63\u6587\uff08\u6570\u636e\u7c7b\u578b\u4e3atext\uff09\u3002<\/p>\n<pre class=\"post-pre\"><code>postgres=# create table posts (title varchar(255), body text);\r\nCREATE TABLE\r\n<\/code><\/pre>\n<p>\u00a5dt<br \/>\n\u203b\u8868\u683c\u5217\u8868<\/p>\n<pre class=\"post-pre\"><code>postgres=# \\dt\r\n         List of relations\r\n Schema | Name  | Type  |  Owner   \r\n--------+-------+-------+----------\r\n public | posts | table | postgres\r\n(1 row)\r\n<\/code><\/pre>\n<p>\u663e\u793a$\u00a5d\u4e2a\u5e16\u5b50<br \/>\n\u663e\u793a\u8868\u683c\u7684\u5217<\/p>\n<pre class=\"post-pre\"><code>postgres=# \\d posts\r\n                       Table \"public.posts\"\r\n Column |          Type          | Collation | Nullable | Default \r\n--------+------------------------+-----------+----------+---------\r\n title  | character varying(255) |           |          | \r\n body   | text                   |           |          | \r\n<\/code><\/pre>\n<p>\u5c06\u8868posts\u91cd\u547d\u540d\u4e3amyposts\u3002<\/p>\n<pre class=\"post-pre\"><code>postgres=# alter table posts rename to myposts;\r\nALTER TABLE\r\n<\/code><\/pre>\n<p>\u5220\u9664\u8868myposts\u3002<\/p>\n<pre class=\"post-pre\"><code>postgres=# drop table myposts;\r\nDROP TABLE\r\n<\/code><\/pre>\n<p>\u4f7f\u7528\u5916\u90e8\u6587\u4ef6\u7684\u65b9\u5f0f<\/p>\n<pre class=\"post-pre\"><code>postgres=# \\i commands.sql\r\n\u203b\u30bf\u30fc\u30df\u30ca\u30eb\u958b\u3044\u3066commands.sql\u30d5\u30a1\u30a4\u30eb\u3092\u4f5c\u6210\u3057\u3066\u3001\u30d5\u30a1\u30a4\u30eb\u306e\u4e2d\u8eab\u306b\r\ncreate table posts (title varchar(255), body text);\r\n\u3092\u8a18\u8f09\u3057\u3066\u3001\u4e0a\u8a18\u30b3\u30de\u30f3\u30c9\u3092\u5b9f\u884c\u3059\u308b\u3068\u30c6\u30fc\u30d6\u30eb\u304c\u4f5c\u6210\u3055\u308c\u308b\r\n<\/code><\/pre>\n<h1>\u6570\u636e\u7c7b\u578b<\/h1>\n<ul class=\"post-ul\">\n<li style=\"list-style-type: none;\">\n<ul class=\"post-ul\">\u6570\u5024: integer(int),real(\u5b9f\u6570),serial(\u9023\u756a)<\/ul>\n<\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<ul class=\"post-ul\">\n<li style=\"list-style-type: none;\">\n<ul class=\"post-ul\">\u6587\u5b57: char(5)-\u56fa\u5b9a\u6587\u5b57\u306e\u5834\u5408, varchar(255)-\u4e0a\u9650\u4ed8\u304d\u306e\u53ef\u5909\u9577,<\/ul>\n<\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<ul class=\"post-ul\">\n<li style=\"list-style-type: none;\">\n<ul class=\"post-ul\">\u203b\u8aad\u307f\u65b9\u3001char = \u30ad\u30e3\u30e9<\/ul>\n<\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<ul class=\"post-ul\">\n<li style=\"list-style-type: none;\">\n<ul class=\"post-ul\">\u771f\u507d: boolean TRUE FALSE\u3000 t\u3000 f<\/ul>\n<\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<ul class=\"post-ul\">\u65e5\u4ed8: date(\u65e5\u4ed8), time(\u6642\u9593),timestamp(\u4e21\u65b9)<\/ul>\n<h1>\u8ba9\u6211\u4eec\u5728\u573a\u5730\u4e0a\u52a0\u4e0a\u4e00\u4e9b\u9650\u5236\u3002<\/h1>\n<ul class=\"post-ul\">\n<li style=\"list-style-type: none;\">\n<ul class=\"post-ul\">\u5236\u7d04<\/ul>\n<\/li>\n<\/ul>\n<p>not null(\u5165\u529b\u5fc5\u9808\u306e\u3082\u306e\u306b\u3064\u3051\u308b)<br \/>\nunique(\u91cd\u8907\u3057\u305f\u5024\u3092\u8a31\u3055\u306a\u3044\u3001\u540c\u3058E\u30e1\u30fc\u30eb\u30a2\u30c9\u30ec\u30b9\u3092\u8a31\u3055\u306a\u3044)<br \/>\ncheck(\u5185\u5bb9\u3092\u30c1\u30a7\u30c3\u30af\u3059\u308b)<br \/>\ndefault<br \/>\nprimary key,(not null, unique)(\u4e00\u610f\u306b\u6c7a\u3081\u308b\u305f\u3081\u306e\u4e3b\u30ad\u30fc)<br \/>\n\u203b\u30c6\u30fc\u30d6\u30eb\u306b\u3064\u304d1\u3064\u3060\u3051\u3057\u304b\u8a2d\u5b9a\u3067\u304d\u306a\u3044<\/p>\n<pre class=\"post-pre\"><code>create table posts(\r\n    id serial primary key,(\u4e00\u610f\u306b\u6c7a\u307e\u308b\u3082\u306e)\r\n    title varchar(255) not null,(\u30bf\u30a4\u30c8\u30eb\u304c\u4f53\u3068\u56f0\u308b)\r\n    body text check(length(body) &gt; 5),(\u4e94\u6587\u5b57\u4ee5\u4e0a)\r\n    is_draft boolean default TRUE,\r\n    created timestamp default 'now',(\u73fe\u5728\u6642\u523b\u306b\u8a2d\u5b9a)\r\n);\r\n<\/code><\/pre>\n<h1>\u8ba9\u6211\u4eec\u5c1d\u8bd5\u4f7f\u7528insert\u51fd\u6570\u3002<\/h1>\n<p>\u5c06\u6807\u9898\u4e3a&#8217;title&#8217;\uff0c\u5185\u5bb9\u4e3a&#8217;body1111&#8217;\u7684\u5e16\u5b50\u63d2\u5165\u5230posts\u8868\u4e2d\u3002<\/p>\n<pre class=\"post-pre\"><code>postgres=# insert into posts (title, body) values ('title', 'body1111');\r\nINSERT 0 1\r\n<\/code><\/pre>\n<p>\u67e5\u770b\u6240\u6709\u5e16\u5b50\u7684\u8bb0\u5f55\u3002<\/p>\n<pre class=\"post-pre\"><code>postgres=# select * from posts;\r\n title |   body\r\n-------+----------\r\n title | body1111\r\n(1 row)\r\n<\/code><\/pre>\n<h1>\u8ba9\u6211\u4eec\u5c1d\u8bd5\u4f7f\u7528select\u8bed\u53e5<\/h1>\n<p>\u6dfb\u52a0\u503c<\/p>\n<pre class=\"post-pre\"><code>postgres=# create table users (\r\n    id serial primary key,\r\n    name varchar(255),\r\n    score real,\r\n    team varchar(255)\r\n);\r\ninsert into users (name, score, team) values\r\n('aa', 5.5, 'red'),\r\n('ss', 8.3, 'blue'),\r\n('dd', 2.2, 'blue'),\r\n('ff', 5.0, 'green'),\r\n('gg', 4.6, 'red'),\r\n('hh', 4.7, 'green');\r\nCREATE TABLE\r\npostgres-# \\dt\r\n         List of relations\r\n Schema | Name  | Type  |  Owner\r\n--------+-------+-------+----------\r\n public | posts | table | postgres\r\n public | users | table | postgres\r\n(2 rows)\r\n<\/code><\/pre>\n<p>\u786e\u8ba4\u5df2\u8bbe\u7f6e\u4e86 $ \\d \u4e2a\u7528\u6237\u5b57\u6bb5<br \/>\n* \u786e\u4fdd\u5df2\u542f\u7528\u6269\u5c55\u663e\u793a $ \\x\u3002\u5982\u679c\u663e\u793a\u4e0d\u540c\uff0c\u8bf7\u5c1d\u8bd5\u8f93\u5165\u4e24\u6b21 $ \\x \u4ee5\u8fd4\u56de\u3002<\/p>\n<pre class=\"post-pre\"><code>postgres-# \\d users\r\n                                    Table \"public.users\"\r\n Column |          Type          | Collation | Nullable |              Default\r\n--------+------------------------+-----------+----------+-----------------------------------\r\n id     | integer                |           | not null | nextval('users_id_seq'::regclass)\r\n name   | character varying(255) |           |          |\r\n score  | real                   |           |          |\r\n team   | character varying(255) |           |          |\r\nIndexes:\r\n    \"users_pkey\" PRIMARY KEY, btree (id)\r\n<\/code><\/pre>\n<p>\u4ece\u7528\u6237\u8868\u4e2d\u9009\u62e9\u6240\u6709\u5b57\u6bb5\u3002<\/p>\n<pre class=\"post-pre\"><code>postgres=# select * from users;\r\n id | name | score | team\r\n----+------+-------+-------\r\n  1 | aa   |   5.5 | red\r\n  2 | ss   |   8.3 | blue\r\n  3 | dd   |   2.2 | blue\r\n  4 | ff   |     5 | green\r\n  5 | gg   |   4.6 | red\r\n  6 | hh   |   4.7 | green\r\n(6 rows)\r\n<\/code><\/pre>\n<p>\u9009\u62e9\u7528\u6237\u8868\u4e2d\u7684\u59d3\u540d\u548c\u5206\u6570\u5b57\u6bb5\u3002<\/p>\n<pre class=\"post-pre\"><code>postgres=# select name, score from users;\r\n name | score\r\n------+-------\r\n aa   |   5.5\r\n ss   |   8.3\r\n dd   |   2.2\r\n ff   |     5\r\n gg   |   4.6\r\n hh   |   4.7\r\n(6 rows)\r\n<\/code><\/pre>\n<h1>\u8ba9\u6211\u4eec\u8bd5\u8bd5\u4f7f\u7528&#8221;where&#8221;\u53e5\u578b<\/h1>\n<p>\u9009\u62e9*\u4eceusers\u8868\u4e2d where score &gt; 4.0 \u7684\u6570\u636e\u3002<\/p>\n<pre class=\"post-pre\"><code>postgres=# select * from users where score &gt; 4.0;\r\n id | name | score | team\r\n----+------+-------+-------\r\n  1 | aa   |   5.5 | red\r\n  2 | ss   |   8.3 | blue\r\n  4 | ff   |     5 | green\r\n  5 | gg   |   4.6 | red\r\n  6 | hh   |   4.7 | green\r\n(5 rows)\r\n<\/code><\/pre>\n<blockquote><p>\u6839\u636e\u7528\u6237\u7684\u5f97\u5206\uff0c\u9009\u62e9\u8bc4\u5206\u4e3a5.0\u7684\u6240\u6709\u7528\u6237\u3002<br \/>\n\u6839\u636e\u7528\u6237\u7684\u5f97\u5206\uff0c\u9009\u62e9\u8bc4\u5206\u4e0d\u4e3a5.0\u7684\u6240\u6709\u7528\u6237\u3002<\/p><\/blockquote>\n<p>\u4ece\u7528\u6237\u8868\u4e2d\u9009\u62e9\u5168\u90e8\u8bb0\u5f55\uff0c\u5176\u4e2d\u540d\u5b57\u7b49\u4e8e &#8216;ff&#8217; \u7684\u8bb0\u5f55\u3002<\/p>\n<pre class=\"post-pre\"><code>postgres=# select * from users where name = 'ff'\r\npostgres-# ;\r\n id | name | score | team\r\n----+------+-------+-------\r\n  4 | ff   |     5 | green\r\n(1 row)\r\n<\/code><\/pre>\n<p>\u8acb\u5f9e\u7528\u6236\u8868\u4e2d\u9078\u64c7\u6240\u6709\u540d\u5b57\u4ee5&#8221;a&#8221;\u7d50\u5c3e\u7684\u7528\u6236\u3002<br \/>\n\u203b %i = \u63d0\u53d6\u4ee5&#8221;a&#8221;\u7d50\u5c3e\u7684\u9805\u76ee\u3002<\/p>\n<pre class=\"post-pre\"><code>postgres=# select * from users where name like '%a'\r\npostgres-# ;\r\n id | name | score | team\r\n----+------+-------+------\r\n  1 | aa   |   5.5 | red\r\n(1 row)\r\n<\/code><\/pre>\n<blockquote><p>\u6839\u636e\u6307\u5b9a\u7684\u6700\u521d\u548c\u6700\u540e\u5b57\u7b26\u8fdb\u884c\u7b5b\u9009\uff0c\u4ece\u7528\u6237\u8868\u4e2d\u9009\u62e9\u6240\u6709\u6ee1\u8db3\u6761\u4ef6\u7684\u8bb0\u5f55\u3002<\/p><\/blockquote>\n<h1>\u8bf7\u4f7f\u7528order by\u3001limit\u3001offset\u6765\u8fdb\u884c\u6392\u5e8f\u3001\u9650\u5236\u3001\u504f\u79fb\u3002<\/h1>\n<p>\u6839\u636e\u5f97\u5206\u5bf9\u7528\u6237\u8fdb\u884c\u6392\u5e8f\uff0c\u9009\u62e9\u6240\u6709\u5b57\u6bb5\u3002<\/p>\n<pre class=\"post-pre\"><code>postgres=# select * from users order by score;\r\n id | name | score | team\r\n----+------+-------+-------\r\n  3 | dd   |   2.2 | blue\r\n  5 | gg   |   4.6 | red\r\n  6 | hh   |   4.7 | green\r\n  4 | ff   |     5 | green\r\n  1 | aa   |   5.5 | red\r\n  2 | ss   |   8.3 | blue\r\n(6 rows)\r\n<\/code><\/pre>\n<p>\u9009\u62e9 * \u6570\u636e\u5e93\u8868\u4e2d\u7684\u6240\u6709\u6570\u636e\uff0c\u6309\u7167\u5206\u6570\u5012\u5e8f\u6392\u5217\u3002<\/p>\n<pre class=\"post-pre\"><code>postgres=# select * from users order by score desc;\r\n id | name | score | team\r\n----+------+-------+-------\r\n  2 | ss   |   8.3 | blue\r\n  1 | aa   |   5.5 | red\r\n  4 | ff   |     5 | green\r\n  6 | hh   |   4.7 | green\r\n  5 | gg   |   4.6 | red\r\n  3 | dd   |   2.2 | blue\r\n(6 rows)\r\n<\/code><\/pre>\n<p>\u6309\u7167\u56e2\u961f\u6392\u5e8f\uff08\u6309\u5b57\u6bcd\u987a\u5e8f\uff09\u4ece\u7528\u6237\u4e2d\u9009\u62e9\u5168\u90e8\u6570\u636e\u3002<\/p>\n<pre class=\"post-pre\"><code>postgres=# select * from users order by team\r\npostgres-# ;\r\n id | name | score | team\r\n----+------+-------+-------\r\n  2 | ss   |   8.3 | blue\r\n  3 | dd   |   2.2 | blue\r\n  4 | ff   |     5 | green\r\n  6 | hh   |   4.7 | green\r\n  1 | aa   |   5.5 | red\r\n  5 | gg   |   4.6 | red\r\n(6 rows)\r\n<\/code><\/pre>\n<p>\u8bf7\u6309\u7167\u56e2\u961f\u548c\u5f97\u5206\u964d\u5e8f\u6392\u5217\u9009\u62e9\u6240\u6709\u7528\u6237\u3002<\/p>\n<pre class=\"post-pre\"><code>postgres=# select * from users order by team, score desc;\r\n id | name | score | team\r\n----+------+-------+-------\r\n  2 | ss   |   8.3 | blue\r\n  3 | dd   |   2.2 | blue\r\n  4 | ff   |     5 | green\r\n  6 | hh   |   4.7 | green\r\n  1 | aa   |   5.5 | red\r\n  5 | gg   |   4.6 | red\r\n(6 rows)\r\n<\/code><\/pre>\n<h2>\u5982\u679c\u60f3\u9650\u5236\u4ef6\u6570\uff0c\u8bf7\u9650\u5236<\/h2>\n<p>$ \u4ece\u7528\u6237\u8868\u4e2d\u9009\u53d6\u524d3\u6761\u6570\u636e\u3002<\/p>\n<pre class=\"post-pre\"><code>postgres=# select * from users limit 3;\r\n id | name | score | team\r\n----+------+-------+------\r\n  1 | aa   |   5.5 | red\r\n  2 | ss   |   8.3 | blue\r\n  3 | dd   |   2.2 | blue\r\n(3 rows)\r\n<\/code><\/pre>\n<p>$ select * from users limit 3 offset 3;<br \/>\n\u203b \u5982\u9700\u4ece\u7b2c4\u4e2a\u9879\u76ee\u5f00\u59cb\u663e\u793a\uff0c<\/p>\n<pre class=\"post-pre\"><code>postgres=# select * from users limit 3 offset 3;\r\n id | name | score | team\r\n----+------+-------+-------\r\n  4 | ff   |     5 | green\r\n  5 | gg   |   4.6 | red\r\n  6 | hh   |   4.7 | green\r\n(3 rows)\r\n<\/code><\/pre>\n<p>\u4ece\u7528\u6237\u8868\u4e2d\u9009\u62e9\u6240\u6709\u6570\u636e\uff0c\u6309\u7167\u5206\u6570\u964d\u5e8f\u6392\u5217\uff0c\u53ea\u663e\u793a\u524d\u4e09\u4e2a\u4eba\u7684\u4fe1\u606f\u3002<\/p>\n<pre class=\"post-pre\"><code>postgres=# select * from users order by score desc limit 3;\r\n id | name | score | team\r\n----+------+-------+-------\r\n  2 | ss   |   8.3 | blue\r\n  1 | aa   |   5.5 | red\r\n  4 | ff   |     5 | green\r\n(3 rows)\r\n<\/code><\/pre>\n<h1>\u8ba9\u6211\u4eec\u6765\u6c47\u603b\u8bb0\u5f55<\/h1>\n<p>\u9009\u62e9\u7528\u6237\u8868\u4e2d\u7684\u6240\u6709\u5b57\u6bb5\u3002<\/p>\n<pre class=\"post-pre\"><code>postgres=# select * from users;\r\n id | name | score | team\r\n----+------+-------+-------\r\n  1 | aa   |   5.5 | red\r\n  2 | ss   |   8.3 | blue\r\n  3 | dd   |   2.2 | blue\r\n  4 | ff   |     5 | green\r\n  5 | gg   |   4.6 | red\r\n  6 | hh   |   4.7 | green\r\n(6 rows)\r\n<\/code><\/pre>\n<p>$ select count(*) from users;<br \/>\n\u203b \u5982\u679c\u60f3\u77e5\u9053\u8bb0\u5f55\u6709\u591a\u5c11\u6761\u7684\u8bdd<\/p>\n<pre class=\"post-pre\"><code>postgres=# select count(*) from users;\r\n count\r\n-------\r\n     6\r\n(1 row)\r\n<\/code><\/pre>\n<p>$ \u4ece\u7528\u6237\u4e2d\u9009\u62e9\u4e0d\u540c\u7684\u56e2\u961f;<br \/>\n\u203b \u5982\u679c\u60f3\u77e5\u9053\u56e2\u961f\u4e2d\u6709\u591a\u5c11\u79cd\u989c\u8272\uff0c\u8bf7<\/p>\n<pre class=\"post-pre\"><code>postgres=# select distinct team from users;\r\n team\r\n-------\r\n blue\r\n red\r\n green\r\n(3 rows)\r\n<\/code><\/pre>\n<p>\u4ece\u7528\u6237\u4e2d\u9009\u62e9\u5206\u6570\u7684\u603b\u548c\u3002<\/p>\n<pre class=\"post-pre\"><code>postgres=# select sum(score) from users;\r\n sum\r\n------\r\n 30.3\r\n(1 row)\r\n<\/code><\/pre>\n<p>\u8bf7\u9009\u62e9\u7528\u6237\u4e2d\u7684\u6700\u9ad8\u5206\u6570\u3002<\/p>\n<pre class=\"post-pre\"><code>postgres=# select max(score) from users;\r\n max\r\n-----\r\n 8.3\r\n(1 row)\r\n<\/code><\/pre>\n<p>$ select avg(score) from users;<br \/>\n\u203b \u83b7\u53d6\u5e73\u5747\u503c<\/p>\n<pre class=\"post-pre\"><code>postgres=# select avg(score) from users;\r\n        avg\r\n-------------------\r\n 5.049999992052714\r\n(1 row)\r\n<\/code><\/pre>\n<p>select team, sum(score) from users group by team;<br \/>\n\u203b \u5982\u679c\u60f3\u8981\u8ba1\u7b97\u5f97\u5206\u603b\u548c\u5e76\u6309\u56e2\u961f\u5206\u7ec4\uff0c\u5219\u53ef\u9009\u62e9\uff1a<\/p>\n<pre class=\"post-pre\"><code>postgres=# select team, sum(score) from users group by team;\r\n team  | sum\r\n-------+------\r\n blue  | 10.5\r\n red   | 10.1\r\n green |  9.7\r\n(3 rows)\r\n<\/code><\/pre>\n<p>\u9009\u62e9\u56e2\u961f\uff0c\u603b\u5206\u6570\u5927\u4e8e10.0\u7684\u9009\u624b\uff0c\u5e76\u5bf9\u56e2\u961f\u8fdb\u884c\u5206\u7ec4\u548c\u6c42\u548c\u3002<\/p>\n<pre class=\"post-pre\"><code>postgres=# select team, sum(score) from users group by team having sum(score) &gt; 10.0;\r\n team | sum\r\n------+------\r\n blue | 10.5\r\n red  | 10.1\r\n(2 rows)\r\n<\/code><\/pre>\n<h1>\u8ba9\u6211\u4eec\u5c1d\u8bd5\u4f7f\u7528\u65b9\u4fbf\u7684\u51fd\u6570<\/h1>\n<p>\u9009\u62e9*\u4ece\u7528\u6237\u8868\u4e2d\u663e\u793a\u5168\u90e8\u5b57\u6bb5\u3002<\/p>\n<pre class=\"post-pre\"><code>postgres=# select * from users;\r\n id | name | score | team\r\n----+------+-------+-------\r\n  1 | aa   |   5.5 | red\r\n  2 | ss   |   8.3 | blue\r\n  3 | dd   |   2.2 | blue\r\n  4 | ff   |     5 | green\r\n  5 | gg   |   4.6 | red\r\n  6 | hh   |   4.7 | green\r\n(6 rows)\r\n<\/code><\/pre>\n<p>$ select name, length(name) from users;<br \/>\n\u203b \u5982\u679c\u60f3\u67e5\u8a62\u540d\u5b57\u7684\u5b57\u7b26\u6578\uff0c<\/p>\n<pre class=\"post-pre\"><code>postgres=# select name, length(name) from users;\r\n name | length\r\n------+--------\r\n aa   |      2\r\n ss   |      2\r\n dd   |      2\r\n ff   |      2\r\n gg   |      2\r\n hh   |      2\r\n(6 rows)\r\n<\/code><\/pre>\n<p>\u8bf7\u9009\u62e9\u5c06\u7528\u6237\u8868\u4e2d\u7684name\u548cteam\u5b57\u6bb5\u8fdb\u884c\u8fde\u63a5\uff0c\u683c\u5f0f\u4e3a\u59d3\u540d\uff08\u56e2\u961f\u540d\uff09\u3002<\/p>\n<pre class=\"post-pre\"><code>postgres=# select concat(name, ' (', team, ')') from users;\r\n aa (red)\r\n ss (blue)\r\n dd (blue)\r\n ff (green)\r\n gg (red)\r\n hh (green)\r\n<\/code><\/pre>\n<p>$ select concat(name, &#8216; (&#8216;, team, &#8216;)&#8217;) as namelabel from users;<\/p>\n<p>\u203b \u66f4\u6539\u6807\u7b7e\u7684\u65b9\u6cd5\uff0c<\/p>\n<pre class=\"post-pre\"><code>postgres=# select concat(name, ' (', team, ')') as namelabel from users;\r\n aa (red)\r\n ss (blue)\r\n dd (blue)\r\n ff (green)\r\n gg (red)\r\n hh (green)\r\n<\/code><\/pre>\n<p>\u4ece\u7528\u6237\u4e2d\u9009\u62e9\u56e2\u961f\u7684\u7b2c\u4e00\u4e2a\u5b57\u7b26\u3002<\/p>\n<pre class=\"post-pre\"><code>postgres=# select substring(team, 1, 1) from users;\r\n r\r\n b\r\n b\r\n g\r\n r\r\n g\r\n<\/code><\/pre>\n<p>\u4ece\u7528\u6237\u4e2d\u968f\u673a\u9009\u62e91\u4eba\u5e76\u8fd4\u56de\u3002<\/p>\n<pre class=\"post-pre\"><code>postgres=# select *from users order by random() limit 1;\r\n  4 | ff   |     5 | green\r\n\r\npostgres=# select *from users order by random() limit 1;\r\n  5 | gg   |   4.6 | red\r\n\r\npostgres=# select *from users order by random() limit 1;\r\n  4 | ff   |     5 | green\r\n<\/code><\/pre>\n<h1>\u8ba9\u6211\u4eec\u5c1d\u8bd5\u4f7f\u7528\u66f4\u65b0\u548c\u5220\u9664\u529f\u80fd\u3002<\/h1>\n<p>\u9009\u62e9 * \u4ece\u7528\u6237\u8868\u4e2d\uff1b\u663e\u793a\u6240\u6709\u5b57\u6bb5<\/p>\n<pre class=\"post-pre\"><code>postgres=# select * from users;\r\n id | name | score | team\r\n----+------+-------+-------\r\n  1 | aa   |   5.5 | red\r\n  2 | ss   |   8.3 | blue\r\n  3 | dd   |   2.2 | blue\r\n  4 | ff   |     5 | green\r\n  5 | gg   |   4.6 | red\r\n  6 | hh   |   4.7 | green\r\n(6 rows)\r\n<\/code><\/pre>\n<p>\u5982\u679c\u8981\u66f4\u6539\u5206\u6570\uff0c\u8bf7\u5728\u6307\u5b9a\u8868\u540d\u540e\u4f7f\u7528[set]\uff0c\u5199\u4e0b\u8981\u66f4\u6539\u7684\u5b57\u6bb5\uff0c\u7136\u540e\u4f7f\u7528\u7b49\u53f7\u5c06\u5176\u4e0e\u503c\u8fde\u63a5\u8d77\u6765\uff1a<br \/>\n$ update users set score = 5.8 where name = &#8216;aa&#8217;;<br \/>\n\u203b \u5982\u679c\u8981\u66f4\u6539\u5206\u6570\uff0c\u8bf7\u5728\u6307\u5b9a\u8868\u540d\u540e\u4f7f\u7528[set]\uff0c\u5199\u4e0b\u8981\u66f4\u6539\u7684\u5b57\u6bb5\uff0c\u7136\u540e\u4f7f\u7528\u7b49\u53f7\u5c06\u5176\u4e0e\u503c\u8fde\u63a5\u8d77\u6765\u3002<\/p>\n<pre class=\"post-pre\"><code>postgres=# update users set score = 5.8 where name = 'aa';\r\nUPDATE 1\r\npostgres=# select * from users;\r\n id | name | score | team\r\n----+------+-------+-------\r\n  2 | ss   |   8.3 | blue\r\n  3 | dd   |   2.2 | blue\r\n  4 | ff   |     5 | green\r\n  5 | gg   |   4.6 | red\r\n  6 | hh   |   4.7 | green\r\n  1 | aa   |   5.8 | red\r\n(6 rows)\r\n<\/code><\/pre>\n<p>\u66f4\u65b0\u7528\u6237\u8868\uff0c\u5f53team\u5b57\u6bb5\u4e3a&#8217;red&#8217;\u65f6\uff0c\u5c06\u5206\u6570score\u589e\u52a01\u3002<\/p>\n<pre class=\"post-pre\"><code>postgres=# update users set score = score + 1 where team = 'red';\r\nUPDATE 2\r\npostgres=# select * from users;\r\n id | name | score | team\r\n----+------+-------+-------\r\n  2 | ss   |   8.3 | blue\r\n  3 | dd   |   2.2 | blue\r\n  4 | ff   |     5 | green\r\n  6 | hh   |   4.7 | green\r\n  5 | gg   |   5.6 | red\r\n  1 | aa   |   6.8 | red\r\n(6 rows)\r\n<\/code><\/pre>\n<p>\u5c06\u7528\u6237\u8868\u4e2d\uff0c\u5f53\u56e2\u961f\u4e3a\u2018red\u2019\u6216\u2018green\u2019\u65f6\uff0c\u5c06\u5f97\u5206\u52a01\u3002<\/p>\n<pre class=\"post-pre\"><code>postgres=# update users set score = score + 1 where team = 'red' or team = 'green';\r\nUPDATE 4\r\npostgres=# select * from users;\r\n id | name | score | team\r\n----+------+-------+-------\r\n  2 | ss   |   8.3 | blue\r\n  3 | dd   |   2.2 | blue\r\n  4 | ff   |     6 | green\r\n  6 | hh   |   5.7 | green\r\n  5 | gg   |   6.6 | red\r\n  1 | aa   |   7.8 | red\r\n(6 rows)\r\n<\/code><\/pre>\n<p>\u4ece\u7528\u6237\u8868\u4e2d\u5220\u9664\u5206\u6570\u4f4e\u4e8e3.0\u7684\u7528\u6237\u3002<\/p>\n<pre class=\"post-pre\"><code>postgres=# delete from users where score &lt; 3.0;\r\nDELETE 1\r\npostgres=# select * from users;\r\n id | name | score | team\r\n----+------+-------+-------\r\n  2 | ss   |   8.3 | blue\r\n  4 | ff   |     6 | green\r\n  6 | hh   |   5.7 | green\r\n  5 | gg   |   6.6 | red\r\n  1 | aa   |   7.8 | red\r\n(5 rows)\r\n<\/code><\/pre>\n<h1>\u8ba9\u6211\u4eec\u5c1d\u8bd5\u6539\u53d8\u684c\u5b50\u7684\u7ed3\u6784<\/h1>\n<ul class=\"post-ul\">\u307e\u305a\u306f\u3053\u3046\u3044\u3063\u305f\u578b\u3067\u7528\u610f\u3055\u308c\u3066\u3044\u308b\u3053\u3068\u3092\u78ba\u8a8d<\/ul>\n<pre class=\"post-pre\"><code>postgres=# \\d users;\r\n                                    Table \"public.users\"\r\n Column |          Type          | Collation | Nullable |              Default\r\n--------+------------------------+-----------+----------+-----------------------------------\r\n id     | integer                |           | not null | nextval('users_id_seq'::regclass)\r\n name   | character varying(255) |           |          |\r\n score  | real                   |           |          |\r\n team   | character varying(255) |           |          |\r\nIndexes:\r\n    \"users_pkey\" PRIMARY KEY, btree (id)\r\n<\/code><\/pre>\n<p>$ \u5728\u7528\u6237\u8868\u4e2d\u6dfb\u52a0\u4e00\u4e2afullname\u5b57\u6bb5\uff0c\u7c7b\u578b\u4e3avarchar(255)\u3002<\/p>\n<pre class=\"post-pre\"><code>postgres=# alter table users add fullname varchar(255);\r\nALTER TABLE\r\npostgres=# \\d users;\r\n                                     Table \"public.users\"\r\n  Column  |          Type          | Collation | Nullable |              Default\r\n----------+------------------------+-----------+----------+-----------------------------------\r\n id       | integer                |           | not null | nextval('users_id_seq'::regclass)\r\n name     | character varying(255) |           |          |\r\n score    | real                   |           |          |\r\n team     | character varying(255) |           |          |\r\n fullname | character varying(255) |           |          |\r\nIndexes:\r\n    \"users_pkey\" PRIMARY KEY, btree (id)\r\n<\/code><\/pre>\n<p>\u8bf7\u95ee\u60a8\u7684\u9700\u6c42\u662f\u5728\u7528\u6237\u8868\u4e2d\u5220\u9664 &#8220;fullname&#8221; \u5b57\u6bb5\u5417\uff1f<\/p>\n<pre class=\"post-pre\"><code>postgres=# alter table users drop fullname;\r\nALTER TABLE\r\npostgres=# \\d users;\r\n                                    Table \"public.users\"\r\n Column |          Type          | Collation | Nullable |              Default\r\n--------+------------------------+-----------+----------+-----------------------------------\r\n id     | integer                |           | not null | nextval('users_id_seq'::regclass)\r\n name   | character varying(255) |           |          |\r\n score  | real                   |           |          |\r\n team   | character varying(255) |           |          |\r\nIndexes:\r\n    \"users_pkey\" PRIMARY KEY, btree (id)\r\n<\/code><\/pre>\n<p>$ \u5c06\u8868users\u4e2d\u7684name\u5217\u66f4\u540d\u4e3amyname\u3002<\/p>\n<p>\u203b \u5982\u679c\u5c06\u540d\u79f0\u4ecename\u6539\u4e3amyname\u7684\u8bdd\uff0c<\/p>\n<pre class=\"post-pre\"><code>postgres=# alter table users rename name to myname;\r\nALTER TABLE\r\npostgres=# \\d users;\r\n                                    Table \"public.users\"\r\n Column |          Type          | Collation | Nullable |              Default\r\n--------+------------------------+-----------+----------+-----------------------------------\r\n id     | integer                |           | not null | nextval('users_id_seq'::regclass)\r\n myname | character varying(255) |           |          |\r\n score  | real                   |           |          |\r\n team   | character varying(255) |           |          |\r\nIndexes:\r\n    \"users_pkey\" PRIMARY KEY, btree (id)\r\n<\/code><\/pre>\n<p>\u5982\u679c\u8981\u4fee\u6539\u7528\u6237\u8868\u4e2dmyname\u5217\u7684\u7c7b\u578b\u4e3avarchar(32)\uff0c\u8bf7\u6267\u884c\u4ee5\u4e0b\u547d\u4ee4\uff1aalter table users alter myname type varchar(32)\u3002<\/p>\n<pre class=\"post-pre\"><code>postgres=# alter table users alter myname type varchar(32);\r\nALTER TABLE\r\npostgres=# \\d users;\r\n                                    Table \"public.users\"\r\n Column |          Type          | Collation | Nullable |              Default\r\n--------+------------------------+-----------+----------+-----------------------------------\r\n id     | integer                |           | not null | nextval('users_id_seq'::regclass)\r\n myname | character varying(32)  |           |          |\r\n score  | real                   |           |          |\r\n team   | character varying(255) |           |          |\r\nIndexes:\r\n    \"users_pkey\" PRIMARY KEY, btree (id)\r\n<\/code><\/pre>\n<p>\u5728\u7528\u6237\u8868\u4e0a\u521b\u5efa\u56e2\u961f\u7d22\u5f15\uff0c\u7d22\u5f15\u540d\u4e3ateam_index\u3002<\/p>\n<p>team\u306bindex\u3092\u3064\u3051\u308b\u65b9\u6cd5\u3001<\/p>\n<p>\u30a4\u30f3\u30c7\u30c3\u30af\u30b9\u3068\u3044\u3046\u3001\u30c6\u30fc\u30d6\u30eb\u306b\u3064\u3044\u3066\u3044\u308b\u7d22\u5f15\u306a\u306e\u3067\u3059\u304c\u3053\u308c\u306e\u8ffd\u52a0\u3001\u524a\u9664\u65b9\u6cd5\u3001<br \/>\nprimary key\u306b\u306f\u6700\u521d\u304b\u3089\u3064\u3044\u3066\u3044\u308b\u306e\u3067\u3059\u304c\u3001\u3053\u308c\u304c\u3064\u3044\u3066\u3044\u308b\u3068\u691c\u7d22\u304c\u65e9\u304f\u306a\u308a\u307e\u3059\u3002<br \/>\n\u904b\u7528\u3057\u3066\u3044\u3063\u305f\u3089team\u3067\u691c\u7d22\u3055\u308c\u308b\u3053\u3068\u304c\u591a\u304b\u3063\u305f\u306e\u3067\u3001team\u306b\u30a4\u30f3\u30c7\u30c3\u30af\u30b9\u3092\u3064\u3051\u3066\u307f\u3088\u3046<\/p>\n<pre class=\"post-pre\"><code>postgres=# create index team_index on users(team);\r\nCREATE INDEX\r\npostgres=# \\d users;\r\n                                    Table \"public.users\"\r\n Column |          Type          | Collation | Nullable |              Default\r\n--------+------------------------+-----------+----------+-----------------------------------\r\n id     | integer                |           | not null | nextval('users_id_seq'::regclass)\r\n myname | character varying(32)  |           |          |\r\n score  | real                   |           |          |\r\n team   | character varying(255) |           |          |\r\nIndexes:\r\n    \"users_pkey\" PRIMARY KEY, btree (id)\r\n    \"team_index\" btree (team)\r\n<\/code><\/pre>\n<p>\u82e5\u8981\u5220\u9664\u7d22\u5f15\uff0c\u8bf7\u6267\u884c\u4ee5\u4e0b\u64cd\u4f5c\uff1a<br \/>\n$ alter table users drop index myname;<br \/>\n\u203b<\/p>\n<pre class=\"post-pre\"><code>postgres=# drop index team_index;\r\nDROP INDEX\r\npostgres=# \\d users;\r\n                                    Table \"public.users\"\r\n Column |          Type          | Collation | Nullable |              Default\r\n--------+------------------------+-----------+----------+-----------------------------------\r\n id     | integer                |           | not null | nextval('users_id_seq'::regclass)\r\n myname | character varying(32)  |           |          |\r\n score  | real                   |           |          |\r\n team   | character varying(255) |           |          |\r\nIndexes:\r\n    \"users_pkey\" PRIMARY KEY, btree (id)\r\n<\/code><\/pre>\n<h1>\u8ba9\u6211\u4eec\u5c1d\u8bd5\u5904\u7406\u591a\u5f20\u8868\u683c<\/h1>\n<p>\u203b \u5275\u5efa\u591a\u500b\u8868\u683c\u3002<\/p>\n<pre class=\"post-pre\"><code>postgres=# create table users (\r\n    id serial primary key,\r\n    name varchar(255),\r\n    score real,\r\n    team varchar(255)\r\n);\r\ninsert into users (name, score, team) values\r\n('aa', 5.5, 'red'),\r\n('ss', 8.3, 'blue'),\r\n('dd', 2.2, 'blue'),\r\n('ff', 5.0, 'green'),\r\n('gg', 4.6, 'red'),\r\n('hh', 4.7, 'green');\r\n\r\ncreate table posts (\r\n    id serial primary key,\r\n    user_id int not null,\r\n    title varchar(255) not null,\r\n    body text not null\r\n);\r\ninsert into posts (user_id, title, body) values\r\n('1', 'title1', 'body1'),\r\n('2', 'title2', 'body2'),\r\n('3', 'title3', 'body3'),\r\n('4', 'title4', 'body4'),\r\n('5', 'title5', 'body5'),\r\n('6', 'title6', 'body6');\r\n<\/code><\/pre>\n<p>\u9700\u8981\u8fdb\u884c\u786e\u8ba4\uff0c\u662f\u5426\u5df2\u7ecf\u5b8c\u6210\u5236\u4f5c\u3002<\/p>\n<pre class=\"post-pre\"><code>postgres=# \\d\r\n              List of relations\r\n Schema |     Name     |   Type   |  Owner\r\n--------+--------------+----------+----------\r\n public | posts        | table    | postgres\r\n public | posts_id_seq | sequence | postgres\r\n public | users        | table    | postgres\r\n public | users_id_seq | sequence | postgres\r\n(4 rows)\r\n\r\npostgres=# select * from users;\r\n id | name | score | team\r\n----+------+-------+-------\r\n  1 | aa   |   5.5 | red\r\n  2 | ss   |   8.3 | blue\r\n  3 | dd   |   2.2 | blue\r\n  4 | ff   |     5 | green\r\n  5 | gg   |   4.6 | red\r\n  6 | hh   |   4.7 | green\r\n(6 rows)\r\n\r\npostgres=# select * from posts;\r\n id | user_id | title  | body\r\n----+---------+--------+-------\r\n  1 |       1 | title1 | body1\r\n  2 |       2 | title2 | body2\r\n  3 |       3 | title3 | body3\r\n  4 |       4 | title4 | body4\r\n  5 |       5 | title5 | body5\r\n  6 |       6 | title6 | body6\r\n(6 rows)\r\n<\/code><\/pre>\n<p>\u9009\u62e9\u7528\u6237\u7684\u59d3\u540d\u548c\u5e16\u5b50\u7684\u6807\u9898\uff0c\u6765\u81ea\u4e8e\u7528\u6237\u8868\u548c\u5e16\u5b50\u8868\u4e2d\uff0c\u6ee1\u8db3\u7528\u6237\u8868\u7684id\u7b49\u4e8e\u5e16\u5b50\u8868\u7684user_id\u3002<\/p>\n<p>\u8ab0\u304c\u3069\u306e\u8a18\u4e8b\u3092\u66f8\u3044\u305f\u304b\u5f15\u3063\u5f35\u3063\u3066\u304f\u308b\u3001<\/p>\n<p>\u8907\u6570\u306e\u30c6\u30fc\u30d6\u30eb\u304b\u3089\u5f15\u3063\u5f35\u3063\u3066\u304f\u308b\u306b\u306f\u3001\u30c6\u30fc\u30d6\u30eb\u540d\u3068\u30d5\u30a3\u30fc\u30eb\u30c9\u540d\u3092 .(\u30c9\u30c3\u30c8)\u3067\u7e4b\u3044\u3067\u3042\u3052\u308c\u3070OK\u3067\u3059\u3002<br \/>\n\u7d50\u3073\u4ed8\u304d\u3092\u6307\u5b9a\u3057\u3066\u3042\u3052\u306a\u3044\u3068\u3044\u3051\u306a\u3044\u306e\u3067,where\u3092\u3064\u3051\u3066user.id\u3068posts.user_id\u304c\u4e00\u7dd2<\/p>\n<pre class=\"post-pre\"><code>postgres=# select users.name, posts.title from users, posts where users.id = posts.user_id;\r\n name | title\r\n------+--------\r\n aa   | title1\r\n ss   | title2\r\n dd   | title3\r\n ff   | title4\r\n gg   | title5\r\n hh   | title6\r\n(6 rows)\r\n<\/code><\/pre>\n<p>SELECT u.name, p.title FROM users AS u, posts AS p WHERE u.id = p.user_id;<\/p>\n<pre class=\"post-pre\"><code>postgres=# select u.name, p.title from users u, posts p where u.id = p.user_id;\r\n name | title\r\n------+--------\r\n aa   | title1\r\n ss   | title2\r\n dd   | title3\r\n ff   | title4\r\n gg   | title5\r\n hh   | title6\r\n(6 rows)\r\n<\/code><\/pre>\n<p>\u9009\u62e9 u.name, p.title FROM \u7528\u6237 u, \u5e16\u5b50 p WHERE u.id = p.user_id \u5e76\u4e14 u.id = 1<\/p>\n<p>\u30e6\u30fc\u30b6\u30fc\u3054\u3068\u306e\u8a18\u4e8b\u4e00\u89a7\u30da\u30fc\u30b8\u4f5c\u6210\u3001<\/p>\n<p>and\u3067user\u306eid\u304c1,\u3064\u307e\u308a{aa}\u541b\u306e\u3082\u306e\u3057\u304b\u77e5\u308a\u305f\u304f\u306a\u3044\u3068\u3044\u3046\u5834\u5408\u306f\u3001\u3053\u306e\u3088\u3046\u306a\u6761\u4ef6\u53e5\u3092\u4e0e\u3048\u308b\u3053\u3068<\/p>\n<pre class=\"post-pre\"><code>postgres=# select u.name, p.title from users u, posts p where u.id = p.user_id and u.id=1;\r\n name | title\r\n------+--------\r\n aa   | title1\r\n(1 row)\r\n<\/code><\/pre>\n<h1>\u8ba9\u6211\u4eec\u5c1d\u8bd5\u4f7f\u7528\u89c6\u56fe<\/h1>\n<p>\u4ee5\u4ee5\u4e0b\u65b9\u5f0f\u5728\u4e2d\u6587\u4e2d\u8fdb\u884c\u4e60\u60ef\u6027\u8f6c\u8ff0\uff0c\u53ea\u9700\u8981\u4e00\u4e2a\u9009\u9879\uff1a<br \/>\n$ \u521b\u5efa\u89c6\u56feaa_posts\u4f5c\u4e3a(\u3055\u3063\u304d\u306e\u306e\u957f\u6587)<\/p>\n<p>\u9577\u6587\u3092view\u3068\u3057\u3066\u3001\u7c21\u5358\u306b\u51fa\u305b\u308b\u3088\u3046\u306b\u3059\u308b\u3001<\/p>\n<pre class=\"post-pre\"><code>postgres=# create view aa_posts as\r\npostgres-# select u.name, p.title from users u, posts p where u.id = p.user_id and u.id=1;\r\nCREATE VIEW\r\npostgres=# \\dv\r\n          List of relations\r\n Schema |   Name   | Type |  Owner\r\n--------+----------+------+----------\r\n public | aa_posts | view | postgres\r\n(1 row)\r\n<\/code><\/pre>\n<p>\u9009\u62e9 * \u4ece aa_posts \u8868\u4e2d\u3002<\/p>\n<p>view\u3067\u4f5c\u6210\u3057\u305f\u3082\u306e\u3092\u51fa\u3059\u65b9\u6cd5\u3001<\/p>\n<pre class=\"post-pre\"><code>postgres=# select * from aa_posts;\r\n name | title\r\n------+--------\r\n aa   | title1\r\n(1 row)\r\n<\/code><\/pre>\n<p>\u5220\u9664\u89c6\u56feaa_post\u3002<\/p>\n<p>view\u3092\u524a\u9664\u3059\u308b\u5834\u5408\u3001<\/p>\n<pre class=\"post-pre\"><code>postgres=# drop view aa_posts;\r\nDROP VIEW\r\npostgres=# \\dv\r\nDid not find any relations.\r\n<\/code><\/pre>\n<h1>\u8ba9\u6211\u4eec\u5c1d\u8bd5\u4f7f\u7528\u4ea4\u6613\u3002<\/h1>\n<ul class=\"post-ul\">\u8907\u6570\u306e\u51e6\u7406\u3092\u5fc5\u305a\u307e\u3068\u3081\u3066\u884c\u3046\u51e6\u7406\u306e\u3053\u3068<\/ul>\n<p>\u5f00\u59cb;<\/p>\n<p>\u9014\u4e2d\u306b\u5909\u306a\u51e6\u7406\u304c\u3044\u3063\u3066\u3053\u306a\u3044\u3001\u4e0d\u6574\u5408\u304c\u8d77\u304d\u306a\u3044\u305f\u3081\u306e\u4ed5\u7d44\u307f\u3001<\/p>\n<p>\u30c8\u30e9\u30f3\u30b6\u30af\u30b7\u30e7\u306e\u958b\u59cb\u306b\u306f[begin]\u3068\u3057\u3066\u3001\u305d\u306e\u5f8c\u306b\u51e6\u7406\u3092\u3044\u304f\u3064\u304b\u66f8\u3044\u3066\u3044\u304f<\/p>\n<pre class=\"post-pre\"><code>postgres=# begin;\r\nBEGIN\r\npostgres=*# update users set score = score -1.0 where name = 'aa';\r\nUPDATE 1\r\npostgres=*# update users set score = score +1.0 where name = 'ss';\r\nUPDATE 1\r\npostgres=*# commit;\r\nCOMMIT\r\n<\/code><\/pre>\n<p>\u56de\u6eda<\/p>\n<p>\u4eca\u307e\u3067\u306e\u5909\u66f4\u3092\u307e\u3068\u3081\u3066\u53d6\u308a\u6d88\u3055\u308c\u308b\u3001<\/p>\n<pre class=\"post-pre\"><code>postgres=# begin;\r\nBEGIN\r\npostgres=*# update users set score = score -1.0 where name = 'aa';\r\nUPDATE 1\r\npostgres=*# update users set score = score +1.0 where name = 'ss';\r\nUPDATE 1\r\npostgres=*# rollback;\r\nROLLBACK\r\n<\/code><\/pre>\n<hr \/>\n<hr \/>\n<hr \/>\n<h1>\u7528npm\u542f\u52a81\u3002<\/h1>\n<pre class=\"post-pre\"><code>\u30fbnpm init -y\r\n\u30d1\u30c3\u30b1\u30fc\u30b8.js\u3092\u521d\u671f\u5316\u3057\u3066\u4f7f\u7528\u3057\u3066\u3044\u304f\r\n\r\n\u30fbnpm i express nodemon pg\r\n\r\n\u30fbnpm start\r\n<\/code><\/pre>\n<h3>\u521b\u5efaserver.js\u6587\u4ef6<\/h3>\n<pre class=\"post-pre\"><code>const express = require(\"express\");\r\nconst app = express();\r\nconst PORT = 5000;\r\n\r\napp.listen(PORT, () =&gt; {\r\n    console.log(\"server is runing on PORT\" + PORT);\r\n});\r\n<\/code><\/pre>\n<h3>\u521b\u5efaAPI<\/h3>\n<pre class=\"post-pre\"><code>const express = require(\"express\");\r\nconst app = express();\r\nconst PORT = 5000;\r\n\r\napp.get(\"\/\", (req, res) =&gt; {\r\n    res.send(\"Hello Express\");\r\n});\r\n\r\napp.listen(PORT, () =&gt; {\r\n    console.log(\"server is runing on PORT\" + PORT);\r\n});\r\n\r\n\u203breg=\u30ea\u30af\u30a8\u30b9\u30c8\r\n\u203bres=\u30ec\u30b9\u30dd\u30f3\u30b9\r\n<\/code><\/pre>\n<blockquote><p>\u786e\u8ba4\u662f\u5426\u80fd\u591f\u8fde\u63a5\u5230http:\/\/localhost:5000\/\u3002<\/p><\/blockquote>\n<h1>\u5b89\u88c5PostgreSQL<\/h1>\n<p>&nbsp;<\/p>\n<h1>\u521b\u5efa\u6570\u636e\u5e93<\/h1>\n<pre class=\"post-pre\"><code>postgres=# CREATE DATABASE users;\r\n\u203b\u4f5c\u6210\u3057\u305f\u3089\u3000\/l\u3067\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u4e00\u89a7\u8868\u793a\u30012\u500b\u76ee\u306e\u8a18\u4e8b\u53c2\u7167\r\n\r\n## \u30c6\u30fc\u30d6\u30eb\u30af\u30ea\u30a2\r\npostgres=# \u00a5l cls\r\n\r\n## \u30c6\u30fc\u30d6\u30eb\u4f5c\u6210\r\npostgres=# \u00a5c users\r\n\r\n### users\u30c6\u30fc\u30d6\u30eb\u4f5c\u6210\r\nusers=# CREATE TABLE users (\r\n\r\n##\u3000\u30d5\u30a3\u30fc\u30eb\u30c9\u4f5c\u6210\r\nusers(# ID serial primary key,\r\nusers(# name varchar(255),\r\nusers(# email varchar(255),\r\nusers(# age int);\r\n\u203bserial=\u9023\u756a\r\n\u203bprimary key=\u4e3b\u30ad\u30fc(\u30e6\u30fc\u30b6\u30fc\u3092\u4ee3\u8868\u3059\u308b\u756a\u53f7)\r\n\u203bvarchar(255)=255\u6587\u5b57\u4ee5\u5185(\u6587\u5b57\u5217)\r\n\u203bint=\u6574\u6570\u5024\r\n\r\n# \u30c6\u30fc\u30d6\u30eb\u78ba\u8a8d\r\nusers(# \u00a5dt\r\n<\/code><\/pre>\n<div><img decoding=\"async\" class=\"post-images\" title=\"\" src=\"https:\/\/cdn.silicloud.com\/blog-img\/blog\/img\/657d469337434c4406ca5988\/171-0.jpeg\" alt=\"\u30b9\u30af\u30ea\u30fc\u30f3\u30b7\u30e7\u30c3\u30c8 2022-08-26 17 33 48\" \/><\/div>\n<h1>\u4f7f\u7528MySQL\u8fdb\u884c\u6570\u636e\u63d2\u5165\uff0c\u6267\u884cSQL\u8bed\u53e5\u3002<\/h1>\n<pre class=\"post-pre\"><code>### users\u30c6\u30fc\u30d6\u30eb\u4e2d\u8eab\u4f5c\u6210\r\nusers=#\u3000insert into users (name, email, age)\r\n\u203binsert=\u633f\u5165\r\n\r\nusers-# values ('shincode', 'shinncode@gmail.com', 25), ('testUser', 'test@gmail.com', 34);\r\n\r\n## \u4f5c\u6210\u3057\u305f\u60c5\u5831\u3092\u51fa\u529b\u3059\u308b\u65b9\u6cd5\r\nusers=#\u3000select * from users;\r\n\u203b *=\u5168\u3066\r\n<\/code><\/pre>\n<div><img decoding=\"async\" class=\"post-images\" title=\"\" src=\"https:\/\/cdn.silicloud.com\/blog-img\/blog\/img\/657d469337434c4406ca5988\/174-0.jpeg\" alt=\"\u30b9\u30af\u30ea\u30fc\u30f3\u30b7\u30e7\u30c3\u30c8 2022-08-26 17 33 48\" \/><\/div>\n<blockquote><p>\u5982\u4f55\u4eceVScode\u4e2d\u63d0\u53d6\u6570\u636e\u5e76\u4e0e\u670d\u52a1\u5668\u534f\u4f5c\u8f93\u51fa\uff1f<\/p><\/blockquote>\n<h3>\u521b\u5efadb.js<\/h3>\n<pre class=\"post-pre\"><code>const Pool = require(\"pg\").Pool;\r\n\r\nconst pool = new Pool({\r\n    user: \"postgres\",\r\n    host: \"localhost\",\r\n    database: \"users\",\r\n    password: \"password\",\r\n    port: 5432,\r\n});\r\n\r\nmodule.exports = pool;\r\n<\/code><\/pre>\n<h3>\u5728server.js\u4e2d\u6dfb\u52a0<\/h3>\n<pre class=\"post-pre\"><code>const express = require(\"express\");\r\nconst app = express();\r\nconst PORT = 5000;\r\n\r\n\/\/json\u3092\u4f7f\u7528\u3059\u308b\u5834\u5408\u306f\u3001\u30df\u30c9\u30eb\u30a6\u30a7\u30a2\u306e\u8a2d\u5b9a\u3092\u884c\u3046\u5fc5\u8981\u304c\u3042\u308b\r\napp.use(express.json());\r\n\r\napp.get(\"\/\", (req, res) =&gt; {\r\n    res.send(\"Hello Express\");\r\n});\r\n\r\n\/\/\u30e6\u30fc\u30b6\u30fc\u60c5\u5831\u3092\u5168\u3066\u53d6\u5f97\u3059\u308b\r\napp.get(\"\/users\", (req, res) =&gt; {\r\n    pool.query(\"SELECT * FROM users\", (error, results) =&gt; {\r\n        if (error) throw error;\r\n        return res.status(200).json(results.rows);\r\n    });\r\n});\r\n\r\napp.listen(PORT, () =&gt; {\r\n    console.log(\"server is runing on PORT\" + PORT);\r\n});\r\n<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"<p>PostgreSQL\u662f\u4e00\u79cd\u5173\u7cfb\u578b\u6570\u636e\u5e93\u7ba1\u7406\u7cfb\u7edf\u3002 RDBMS(\u30ea\u30ec\u30fc\u30b7\u30e7\u30ca\u30eb\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u7ba1\u7406\u30b7\u30b9\u30c6\u30e0) RDB [&hellip;]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-41358","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v21.5 (Yoast SEO v21.5) - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>\u6570\u636e\u5e93 (PostgreSQL) - Blog - Silicon Cloud<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.silicloud.com\/zh\/blog\/\u6570\u636e\u5e93-postgresql\/\" \/>\n<meta property=\"og:locale\" content=\"zh_CN\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"\u6570\u636e\u5e93 (PostgreSQL)\" \/>\n<meta property=\"og:description\" content=\"PostgreSQL\u662f\u4e00\u79cd\u5173\u7cfb\u578b\u6570\u636e\u5e93\u7ba1\u7406\u7cfb\u7edf\u3002 RDBMS(\u30ea\u30ec\u30fc\u30b7\u30e7\u30ca\u30eb\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u7ba1\u7406\u30b7\u30b9\u30c6\u30e0) RDB [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.silicloud.com\/zh\/blog\/\u6570\u636e\u5e93-postgresql\/\" \/>\n<meta property=\"og:site_name\" content=\"Blog - Silicon Cloud\" \/>\n<meta property=\"article:published_time\" content=\"2023-04-11T20:45:57+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-04-29T01:01:42+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/cdn.silicloud.com\/blog-img\/blog\/img\/657d469337434c4406ca5988\/2-0.jpeg\" \/>\n<meta name=\"author\" content=\"\u79d1, \u9896\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"\u4f5c\u8005\" \/>\n\t<meta name=\"twitter:data1\" content=\"\u79d1, \u9896\" \/>\n\t<meta name=\"twitter:label2\" content=\"\u9884\u8ba1\u9605\u8bfb\u65f6\u95f4\" \/>\n\t<meta name=\"twitter:data2\" content=\"11 \u5206\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.silicloud.com\/zh\/blog\/%e6%95%b0%e6%8d%ae%e5%ba%93-postgresql\/\",\"url\":\"https:\/\/www.silicloud.com\/zh\/blog\/%e6%95%b0%e6%8d%ae%e5%ba%93-postgresql\/\",\"name\":\"\u6570\u636e\u5e93 (PostgreSQL) - Blog - Silicon Cloud\",\"isPartOf\":{\"@id\":\"https:\/\/www.silicloud.com\/zh\/blog\/#website\"},\"datePublished\":\"2023-04-11T20:45:57+00:00\",\"dateModified\":\"2024-04-29T01:01:42+00:00\",\"author\":{\"@id\":\"https:\/\/www.silicloud.com\/zh\/blog\/#\/schema\/person\/8ca01ba7f7362ad4edb7da206a12f29e\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.silicloud.com\/zh\/blog\/%e6%95%b0%e6%8d%ae%e5%ba%93-postgresql\/#breadcrumb\"},\"inLanguage\":\"zh-Hans\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.silicloud.com\/zh\/blog\/%e6%95%b0%e6%8d%ae%e5%ba%93-postgresql\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.silicloud.com\/zh\/blog\/%e6%95%b0%e6%8d%ae%e5%ba%93-postgresql\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"\u9996\u9875\",\"item\":\"https:\/\/www.silicloud.com\/zh\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"\u6570\u636e\u5e93 (PostgreSQL)\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.silicloud.com\/zh\/blog\/#website\",\"url\":\"https:\/\/www.silicloud.com\/zh\/blog\/\",\"name\":\"Blog - Silicon Cloud\",\"description\":\"\",\"inLanguage\":\"zh-Hans\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.silicloud.com\/zh\/blog\/#\/schema\/person\/8ca01ba7f7362ad4edb7da206a12f29e\",\"name\":\"\u79d1, \u9896\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"zh-Hans\",\"@id\":\"https:\/\/www.silicloud.com\/zh\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/8a6fb3cc7ba2f69d2189ba532aec4633ea7ed75ac0af162ec367cb3abc0fb2af?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/8a6fb3cc7ba2f69d2189ba532aec4633ea7ed75ac0af162ec367cb3abc0fb2af?s=96&d=mm&r=g\",\"caption\":\"\u79d1, \u9896\"},\"url\":\"https:\/\/www.silicloud.com\/zh\/blog\/author\/keying\/\"},{\"@type\":\"ImageObject\",\"inLanguage\":\"zh-Hans\",\"@id\":\"https:\/\/www.silicloud.com\/zh\/blog\/%e6%95%b0%e6%8d%ae%e5%ba%93-postgresql\/#local-main-organization-logo\",\"url\":\"\",\"contentUrl\":\"\",\"caption\":\"Blog - Silicon Cloud\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"\u6570\u636e\u5e93 (PostgreSQL) - Blog - Silicon Cloud","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.silicloud.com\/zh\/blog\/\u6570\u636e\u5e93-postgresql\/","og_locale":"zh_CN","og_type":"article","og_title":"\u6570\u636e\u5e93 (PostgreSQL)","og_description":"PostgreSQL\u662f\u4e00\u79cd\u5173\u7cfb\u578b\u6570\u636e\u5e93\u7ba1\u7406\u7cfb\u7edf\u3002 RDBMS(\u30ea\u30ec\u30fc\u30b7\u30e7\u30ca\u30eb\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u7ba1\u7406\u30b7\u30b9\u30c6\u30e0) RDB [&hellip;]","og_url":"https:\/\/www.silicloud.com\/zh\/blog\/\u6570\u636e\u5e93-postgresql\/","og_site_name":"Blog - Silicon Cloud","article_published_time":"2023-04-11T20:45:57+00:00","article_modified_time":"2024-04-29T01:01:42+00:00","og_image":[{"url":"https:\/\/cdn.silicloud.com\/blog-img\/blog\/img\/657d469337434c4406ca5988\/2-0.jpeg"}],"author":"\u79d1, \u9896","twitter_card":"summary_large_image","twitter_misc":{"\u4f5c\u8005":"\u79d1, \u9896","\u9884\u8ba1\u9605\u8bfb\u65f6\u95f4":"11 \u5206"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.silicloud.com\/zh\/blog\/%e6%95%b0%e6%8d%ae%e5%ba%93-postgresql\/","url":"https:\/\/www.silicloud.com\/zh\/blog\/%e6%95%b0%e6%8d%ae%e5%ba%93-postgresql\/","name":"\u6570\u636e\u5e93 (PostgreSQL) - Blog - Silicon Cloud","isPartOf":{"@id":"https:\/\/www.silicloud.com\/zh\/blog\/#website"},"datePublished":"2023-04-11T20:45:57+00:00","dateModified":"2024-04-29T01:01:42+00:00","author":{"@id":"https:\/\/www.silicloud.com\/zh\/blog\/#\/schema\/person\/8ca01ba7f7362ad4edb7da206a12f29e"},"breadcrumb":{"@id":"https:\/\/www.silicloud.com\/zh\/blog\/%e6%95%b0%e6%8d%ae%e5%ba%93-postgresql\/#breadcrumb"},"inLanguage":"zh-Hans","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.silicloud.com\/zh\/blog\/%e6%95%b0%e6%8d%ae%e5%ba%93-postgresql\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.silicloud.com\/zh\/blog\/%e6%95%b0%e6%8d%ae%e5%ba%93-postgresql\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"\u9996\u9875","item":"https:\/\/www.silicloud.com\/zh\/blog\/"},{"@type":"ListItem","position":2,"name":"\u6570\u636e\u5e93 (PostgreSQL)"}]},{"@type":"WebSite","@id":"https:\/\/www.silicloud.com\/zh\/blog\/#website","url":"https:\/\/www.silicloud.com\/zh\/blog\/","name":"Blog - Silicon Cloud","description":"","inLanguage":"zh-Hans"},{"@type":"Person","@id":"https:\/\/www.silicloud.com\/zh\/blog\/#\/schema\/person\/8ca01ba7f7362ad4edb7da206a12f29e","name":"\u79d1, \u9896","image":{"@type":"ImageObject","inLanguage":"zh-Hans","@id":"https:\/\/www.silicloud.com\/zh\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/8a6fb3cc7ba2f69d2189ba532aec4633ea7ed75ac0af162ec367cb3abc0fb2af?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/8a6fb3cc7ba2f69d2189ba532aec4633ea7ed75ac0af162ec367cb3abc0fb2af?s=96&d=mm&r=g","caption":"\u79d1, \u9896"},"url":"https:\/\/www.silicloud.com\/zh\/blog\/author\/keying\/"},{"@type":"ImageObject","inLanguage":"zh-Hans","@id":"https:\/\/www.silicloud.com\/zh\/blog\/%e6%95%b0%e6%8d%ae%e5%ba%93-postgresql\/#local-main-organization-logo","url":"","contentUrl":"","caption":"Blog - Silicon Cloud"}]}},"_links":{"self":[{"href":"https:\/\/www.silicloud.com\/zh\/blog\/wp-json\/wp\/v2\/posts\/41358","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.silicloud.com\/zh\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.silicloud.com\/zh\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.silicloud.com\/zh\/blog\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.silicloud.com\/zh\/blog\/wp-json\/wp\/v2\/comments?post=41358"}],"version-history":[{"count":2,"href":"https:\/\/www.silicloud.com\/zh\/blog\/wp-json\/wp\/v2\/posts\/41358\/revisions"}],"predecessor-version":[{"id":84315,"href":"https:\/\/www.silicloud.com\/zh\/blog\/wp-json\/wp\/v2\/posts\/41358\/revisions\/84315"}],"wp:attachment":[{"href":"https:\/\/www.silicloud.com\/zh\/blog\/wp-json\/wp\/v2\/media?parent=41358"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.silicloud.com\/zh\/blog\/wp-json\/wp\/v2\/categories?post=41358"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.silicloud.com\/zh\/blog\/wp-json\/wp\/v2\/tags?post=41358"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}