PostgreSQL数据库

2023-10-27

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.

首先安装公钥

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

更新源

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’

安装pgAdmin4

sudo apt install pgadmin4

pdAdmin4-web

sudo apt install pgadmin4-web

最后执行

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，不建议使用浮点数来处理处理货币类型，因为存在舍入错误的可能性。

名字	存储容量	描述	范围
money	8 字节	货币金额	-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
);

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构造数据集。

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)

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

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)