PostgreSQL数据库

0 安装

我使用的操作系统为Ubuntu。
安装命令:

sudo apt-get update
sudo apt-get install postgresql postgresql-client

进入postgres:

sudo -i -u postgres
psql

退出命令:

\q

PostgreSQL启动服务等:

sudo /etc/init.d/postgresql start # 开启
sudo /etc/init.d/postgresql stop # 关闭
sudo /etc/init.d/postgresql restart # 重启

安装pgAdmin4.

  1. 首先安装公钥

curl https://www.pgadmin.org/static/packages_pgadmin_org.pub | sudo apt-key add

  1. 更新源

sudo sh -c ‘echo “deb [https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/ ( l s b r e l e a s e ] ( h t t p s : / / f t p . p o s t g r e s q l . o r g / p u b / p g a d m i n / p g a d m i n 4 / a p t / (lsb_release](https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/ (lsbrelease](https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/(lsb_release) -cs) pgadmin4 main” > /etc/apt/sources.list.d/pgadmin4.list && apt update’

  1. 安装pgAdmin4

sudo apt install pgadmin4

  1. pdAdmin4-web

sudo apt install pgadmin4-web

  1. 最后执行

sudo /usr/pgadmin4/bin/setup-web.sh

1 数据类型

http://www.postgres.cn/docs/12/datatype.html

  • 数值类型
    | 名字 | 存储长度 | 描述 | 范围 |
    | — | — | — | — |
    | smallint | 2 字节 | 小范围整数 | -32768 到 +32767 |
    | integer | 4 字节 | 常用的整数 | -2147483648 到 +2147483647 |
    | bigint | 8 字节 | 大范围整数 | -9223372036854775808 到 +9223372036854775807 |
    | decimal | 可变长 | 用户指定的精度,精确 | 小数点前 131072 位;小数点后 16383 位 |
    | numeric | 可变长 | 用户指定的精度,精确 | 小数点前 131072 位;小数点后 16383 位 |
    | real | 4 字节 | 可变精度,不精确 | 6 位十进制数字精度 |
    | double precision | 8 字节 | 可变精度,不精确 | 15 位十进制数字精度 |
    | smallserial | 2 字节 | 自增的小范围整数 | 1 到 32767 |
    | serial | 4 字节 | 自增整数 | 1 到 2147483647 |
    | bigserial | 8 字节 | 自增的大范围整数 | 1 到 9223372036854775807 |

  • 货币类型

money 类型存储带有固定小数精度的货币金额。
numeric、int 和 bigint 类型的值可以转换为 money,不建议使用浮点数来处理处理货币类型,因为存在舍入错误的可能性。

名字存储容量描述范围
money8 字节货币金额-92233720368547758.08 到 +92233720368547758.07
  • 字符类型
  • 日期/时间类型
  • 布尔类型
  • 枚举类型

枚举类型是一个包含静态和值的有序集合的数据类型。
PostgtesSQL中的枚举类型类似于 C 语言中的 enum 类型。
与其他类型不同的是枚举类型需要使用 CREATE TYPE 命令创建。

CREATE TYPE mood AS ENUM (‘sad’, ‘ok’, ‘happy’);

创建一周中的几天,如下所示:

CREATE TYPE week AS ENUM (‘Mon’, ‘Tue’, ‘Wed’, ‘Thu’, ‘Fri’, ‘Sat’, ‘Sun’);

就像其他类型一样,一旦创建,枚举类型可以用于表和函数定义。

  • 几何类型
  • 网络地址类型

PostgreSQL 提供用于存储 IPv4 、IPv6 、MAC 地址的数据类型。
用这些数据类型存储网络地址比用纯文本类型好, 因为这些类型提供输入错误检查和特殊的操作和功能。

  • 位串类型
  • 文本搜索类型
  • UUID 类型
  • XML 类型

创建XML值
使用函数 xmlparse: 来从字符数据产生 xml 类型的值:

XMLPARSE (DOCUMENT ‘<?xml version="1.0"?>Manual…’) XMLPARSE (CONTENT ‘abcbarfoo’)

  • JSON 类型
  • 数组类型

2 创建表

  • 创建数据库

首先创建数据库,如下所示:

postgres=# create database example;
CREATE DATABASE
postgres=# \l
postgres=# \c example
You are now connected to database “example” as user “postgres”.
example=#

创建数据库:create database example;
连接其它数据库:\c example;
删除数据库:drop database example;

  • 创建数据表

创建表:

create table_name(
Column_name data_type constraints
);

查询表:\d personl;(\dt只显示表)
删除表:drop table person;
WindTerm_Eq2SKFEuO3.png
:::success
example=# create table person(
id bigserial not null primary key,
name varchar(200) not null,
gender varchar(7) not null,
birthday date not null,
email varchar(250));
CREATE TABLE
example=# \d
List of relations
Schema | Name | Type | Owner
--------±--------------±---------±---------
public | person | table | postgres
public | person_id_seq | sequence | postgres
(2 rows)

example=# \d person_
person_id_seq person_pkey
example=# \d person_
person_id_seq person_pkey
example=# \d person_id_seq
Sequence “public.person_id_seq”
Type | Start | Minimum | Maximum | Increment | Cycles? | Cache
--------±------±--------±--------------------±----------±--------±------
bigint | 1 | 1 | 9223372036854775807 | 1 | no | 1
Owned by: public.person.id

example=# \d person
example=# \d person;
example=#
:::

2 插入数据

  • 查询

查询语句

select (column_name)/* from table_name;

插入语句

insert into table_name(column_name,…) values(‘column_value’,…);

example=# select * from person;
 id | name | gender | birthday | email 
----+------+--------+----------+-------
(0 rows)

example=# insert into person (name, gender, birthday)values('dashabi','male','12-11-3')
example-# select * from person;
ERROR:  syntax error at or near "select"
第2行select * from person;
     ^
example=# select * from person;
 id | name | gender | birthday | email 
----+------+--------+----------+-------
(0 rows)

example=# insert into person (name, gender, birthday)values('dashabi','male','12-11-3');
INSERT 0 1
example=# select * from person;
 id |  name   | gender |  birthday  | email 
----+---------+--------+------------+-------
  1 | dashabi | male   | 2012-11-03 | 
(1 row)

example=# insert into person (name, gender, birthday)values('chsihi','male',date'12-11-3','ahha@131.com');
ERROR:  INSERT has more expressions than target columns
第1行...er, birthday)values('chsihi','male',date'12-11-3','ahha@131....
                                                          ^
example=# insert into person (name, gender, birthday,mail)values('chsihi','male',date'12-11-3','ahha@131.com');
ERROR:  column "mail" of relation "person" does not exist
第1行insert into person (name, gender, birthday,mail)values('chsi...
                                                ^
example=# \d person;
example=# insert into person (name, gender, birthday,email)values('chsihi','male',date'12-11-3','ahha@131.com');
INSERT 0 1
example=# \d person;
example=# select * from person;
 id |  name   | gender |  birthday  |    email     
----+---------+--------+------------+--------------
  1 | dashabi | male   | 2012-11-03 | 
  2 | chsihi  | male   | 2012-11-03 | ahha@131.com
(2 rows)

example=# 

4 查询语句

4.1 mock数据

mock构造数据集。
chrome_dpaz1poR7Y.png
https://mockaroo.com/
:::tips
postgres=# create database mockdb;
CREATE DATABASE
postgres=# \c mockdb ;
You are now connected to database “mockdb” as user “postgres”.
mockdb=# \i /home/day3/person.sql
:::

4.2 查询

升序降序。
:::tips
mockdb=# select * from person order by id asc;
mockdb=# select * from person order by id desc;
:::
去重。
:::tips
mockdb=# select distinct birth_country from person order by birth_country;
:::
where,and和or。
:::tips
mockdb=# select * from person where gender=‘Male’;
mockdb=# select * from person where gender=‘Male’ and birth_country=‘China’;
mockdb=# select * from person where gender=‘Male’ and (birth_country=‘China’ or birth_country=‘Poland’);
mockdb=#
:::
比较。

mockdb=# select 1=1;
?column?

t
(1 row)
mockdb=# select 1>1;
?column?

f
(1 row)
mockdb=# select 1<>2;
?column?

t
(1 row)
mockdb=# select ‘a’ > ‘b’;
?column?

f
(1 row)

:::tips
mockdb=# select * from person limit 10;
id | name | email | gender | birthday | birth_country
----±--------------------±---------------------------±------------±-----------±--------------
1 | Neville Catcheside | ncatcheside0@amazon.co.uk | Male | 1995-07-30 | Poland
2 | Pincus Jirus | pjirus1@mapy.cz | Male | 2017-10-08 | Portugal
3 | Flo Lanning | flanning2@mashable.com | Genderfluid | 2018-09-27 | Indonesia
4 | Giulio Eccleshall | geccleshall3@123-reg.co.uk | Male | 2004-09-24 | Serbia
5 | Dorothee Dunmore | | Female | 2018-03-08 | China
6 | Sinclair Giacometti | sgiacometti5@who.int | Male | 2001-12-09 | Philippines
7 | Adolphus Relf | arelf6@psu.edu | Agender | 2001-05-26 | South Africa
8 | Mattias Aloigi | maloigi7@miibeian.gov.cn | Male | 2014-07-11 | Greece
9 | Elsworth Hadfield | ehadfield8@google.com.hk | Male | 2006-02-13 | United States
10 | Fancy Lundon | | Female | 2010-02-27 | China
(10 rows)
mockdb=# select * from person limit 20;
id | name | email | gender | birthday | birth_country
----±--------------------±------------------------------±------------±-----------±--------------
1 | Neville Catcheside | ncatcheside0@amazon.co.uk | Male | 1995-07-30 | Poland
2 | Pincus Jirus | pjirus1@mapy.cz | Male | 2017-10-08 | Portugal
3 | Flo Lanning | flanning2@mashable.com | Genderfluid | 2018-09-27 | Indonesia
4 | Giulio Eccleshall | geccleshall3@123-reg.co.uk | Male | 2004-09-24 | Serbia
5 | Dorothee Dunmore | | Female | 2018-03-08 | China
6 | Sinclair Giacometti | sgiacometti5@who.int | Male | 2001-12-09 | Philippines
7 | Adolphus Relf | arelf6@psu.edu | Agender | 2001-05-26 | South Africa
8 | Mattias Aloigi | maloigi7@miibeian.gov.cn | Male | 2014-07-11 | Greece
9 | Elsworth Hadfield | ehadfield8@google.com.hk | Male | 2006-02-13 | United States
10 | Fancy Lundon | | Female | 2010-02-27 | China
11 | Angel Epine | aepinea@time.com | Male | 2003-03-08 | Russia
12 | Clayson Darke | | Male | 2013-05-05 | China
13 | Crosby Plane | cplanec@cafepress.com | Polygender | 2012-02-26 | Russia
14 | Emmy Grocutt | | Female | 2006-09-05 | Luxembourg
15 | Dianemarie Edgson | dedgsone@comsenz.com | Female | 2014-05-10 | China
16 | Rollins Sporrij | rsporrijf@oaic.gov.au | Male | 2014-09-15 | Peru
17 | Mattheus Gawkroge | | Male | 2012-01-23 | Greece
18 | Cody Bwye | cbwyeh@deliciousdays.com | Male | 1995-12-22 | Tajikistan
19 | Gal Benditt | gbenditti@google.ru | Male | 2001-02-13 | Morocco
20 | Debbie Tregiddo | dtregiddoj@barnesandnoble.com | Female | 2016-04-23 | Belarus
(20 rows)
mockdb=# select * from person offest 10 limit 20;
ERROR: syntax error at or near “10”
第1行select * from person offest 10 limit 20;
^
mockdb=# select * from person offset 10 limit 20;
id | name | email | gender | birthday | birth_country
----±------------------±------------------------------±-----------±-----------±--------------
11 | Angel Epine | aepinea@time.com | Male | 2003-03-08 | Russia
12 | Clayson Darke | | Male | 2013-05-05 | China
13 | Crosby Plane | cplanec@cafepress.com | Polygender | 2012-02-26 | Russia
14 | Emmy Grocutt | | Female | 2006-09-05 | Luxembourg
15 | Dianemarie Edgson | dedgsone@comsenz.com | Female | 2014-05-10 | China
16 | Rollins Sporrij | rsporrijf@oaic.gov.au | Male | 2014-09-15 | Peru
17 | Mattheus Gawkroge | | Male | 2012-01-23 | Greece
18 | Cody Bwye | cbwyeh@deliciousdays.com | Male | 1995-12-22 | Tajikistan
19 | Gal Benditt | gbenditti@google.ru | Male | 2001-02-13 | Morocco
20 | Debbie Tregiddo | dtregiddoj@barnesandnoble.com | Female | 2016-04-23 | Belarus
21 | Gillian Daville | gdavillek@liveinternet.ru | Female | 1998-09-25 | Russia
22 | Marven Cahill | mcahilll@g.co | Male | 1995-03-15 | China
23 | Arlyn Manicomb | amanicombm@goo.gl | Female | 2014-06-27 | China
24 | Stefania Bootes | | Female | 1995-03-27 | Azerbaijan
25 | Edan Yann | eyanno@narod.ru | Male | 2001-02-10 | Japan
26 | Garfield Lago | glagop@dmoz.org | Male | 2016-03-21 | China
27 | Abraham Petren | apetrenq@wordpress.com | Male | 2006-12-15 | China
28 | Merell Cranna | | Male | 2010-03-08 | Japan
29 | Kev Olorenshaw | kolorenshaws@1und1.de | Male | 2022-02-07 | Russia
30 | Elli Roman | eromant@tripadvisor.com | Female | 1996-09-05 | Jamaica
(20 rows)
mockdb=#
:::
like和ilike

mockdb=# select * from person where name like ‘%ll’;
mockdb=# select * from person where name like ‘__ll’;
id | name | email | gender | birthday | birth_country
----±-----±------±-------±---------±--------------
(0 rows)

mockdb=# select * from person where name like ‘___ll’;
id | name | email | gender | birthday | birth_country
----±-----±------±-------±---------±--------------
(0 rows)

mockdb=# select * from person where name like ‘____ll’;
id | name | email | gender | birthday | birth_country
----±-----±------±-------±---------±--------------
(0 rows)

mockdb=# select * from person where name like ‘_____ll’;
id | name | email | gender | birthday | birth_country
----±-----±------±-------±---------±--------------
(0 rows)

mockdb=# select * from person where name like ‘%ll’;
mockdb=# select * from person where name like ‘__________ll’;
id | name | email | gender | birthday | birth_country
-----±-------------±---------------------±-------±-----------±--------------
158 | Erin Gostall | egostall4d@google.ca | Male | 2020-01-17 | Argentina
825 | Glen Snoxall | gsnoxallmw@go.com | Male | 2006-04-08 | Indonesia
(2 rows)

mockdb=# select * from person where birth_country like ‘J%’;
id | name | email | gender | birthday | birth_country
-----±------------------±---------------------------±-------±-----------±--------------
25 | Edan Yann | eyanno@narod.ru | Male | 2001-02-10 | Japan
28 | Merell Cranna | | Male | 2010-03-08 | Japan
30 | Elli Roman | eromant@tripadvisor.com | Female | 1996-09-05 | Jamaica
115 | Alvie Hannay | ahannay36@1688.com | Male | 2018-05-18 | Jordan
147 | Terrell Chimenti | tchimenti42@discovery.com | Male | 2016-05-13 | Japan
151 | Merci Albon | malbon46@zimbio.com | Female | 1997-05-27 | Japan
278 | Yankee Ecclesall | | Male | 2008-11-03 | Japan
286 | Marsiella Sillett | msillett7x@miibeian.gov.cn | Female | 1997-04-05 | Japan
319 | Lilla Rasch | lrasch8u@earthlink.net | Female | 2019-11-28 | Jordan
426 | Siward Ablewhite | | Male | 2021-01-09 | Japan
430 | Avivah Leivesley | aleivesleybx@pbs.org | Female | 2010-02-01 | Japan
549 | Georgeta Borit | gboritf8@mediafire.com | Female | 2012-07-23 | Japan
565 | Dasie Moehler | dmoehlerfo@prnewswire.com | Female | 1995-10-21 | Japan
623 | Jenica D’Onise | jdoniseha@fda.gov | Female | 2000-08-14 | Japan
746 | Murry Moulster | mmoulsterkp@example.com | Male | 2005-01-10 | Japan
868 | Lanita Mitrovic | lmitrovico3@elpais.com | Female | 1998-03-21 | Japan
871 | Osgood Bemlott | | Male | 1993-09-15 | Japan
878 | Jennine Gianelli | jgianelliod@twitter.com | Female | 2014-11-25 | Japan
934 | Julian Acres | jacrespx@mashable.com | Male | 2002-12-25 | Japan
975 | Dorian Howieson | dhowiesonr2@squidoo.com | Female | 2018-03-22 | Japan
(20 rows)

mockdb=# select * from person where birth_country like ‘j%’;
id | name | email | gender | birthday | birth_country
----±-----±------±-------±---------±--------------
(0 rows)

mockdb=# select * from person where birth_country ilike ‘j%’;
id | name | email | gender | birthday | birth_country
-----±------------------±---------------------------±-------±-----------±--------------
25 | Edan Yann | eyanno@narod.ru | Male | 2001-02-10 | Japan
28 | Merell Cranna | | Male | 2010-03-08 | Japan
30 | Elli Roman | eromant@tripadvisor.com | Female | 1996-09-05 | Jamaica
115 | Alvie Hannay | ahannay36@1688.com | Male | 2018-05-18 | Jordan
147 | Terrell Chimenti | tchimenti42@discovery.com | Male | 2016-05-13 | Japan
151 | Merci Albon | malbon46@zimbio.com | Female | 1997-05-27 | Japan
278 | Yankee Ecclesall | | Male | 2008-11-03 | Japan
286 | Marsiella Sillett | msillett7x@miibeian.gov.cn | Female | 1997-04-05 | Japan
319 | Lilla Rasch | lrasch8u@earthlink.net | Female | 2019-11-28 | Jordan
426 | Siward Ablewhite | | Male | 2021-01-09 | Japan
430 | Avivah Leivesley | aleivesleybx@pbs.org | Female | 2010-02-01 | Japan
549 | Georgeta Borit | gboritf8@mediafire.com | Female | 2012-07-23 | Japan
565 | Dasie Moehler | dmoehlerfo@prnewswire.com | Female | 1995-10-21 | Japan
623 | Jenica D’Onise | jdoniseha@fda.gov | Female | 2000-08-14 | Japan
746 | Murry Moulster | mmoulsterkp@example.com | Male | 2005-01-10 | Japan
868 | Lanita Mitrovic | lmitrovico3@elpais.com | Female | 1998-03-21 | Japan
871 | Osgood Bemlott | | Male | 1993-09-15 | Japan
878 | Jennine Gianelli | jgianelliod@twitter.com | Female | 2014-11-25 | Japan
934 | Julian Acres | jacrespx@mashable.com | Male | 2002-12-25 | Japan
975 | Dorian Howieson | dhowiesonr2@squidoo.com | Female | 2018-03-22 | Japan
(20 rows)

group by。

mockdb=# select birth_country, count() from person group by birth_country;
mockdb=# select birth_country, count(
) from person group by birth_country having count(*) > 20;
birth_country | count
---------------±------
Indonesia | 112
Sweden | 28
Portugal | 40
France | 22
Philippines | 54
China | 182
Russia | 59
Brazil | 44
Poland | 35
(9 rows)

mockdb=#

删除。

delete from person where id = 1

chrome_qzg6Ha9U36.png
chrome_EN6dECnfOw.png
chrome_M6Fp5QRQtZ.png

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值