PostgreSql学习笔记

最新推荐文章于 2024-05-18 18:50:27 发布

我是小烂毛

最新推荐文章于 2024-05-18 18:50:27 发布

阅读量345

点赞数

分类专栏： sql 文章标签： sql postgres postgresql

本文链接：https://blog.csdn.net/woshixiaolanmao1/article/details/106720379

版权

sql 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

一、操作表数据

（一）基本语法

sudo su postgres //切换到数据库：

psql --version //查看数据库版本

psql-l //查看当前所有数据库

createdb xxx //创建新的数据库

dropdb xxx //删除数据库

psql xxx  // 进入指定的数据库 >help >\q退出

psql xxx

>create table posts(title varchar(255),content text); //创建数据表

>\dt   //当前表一览

>\d post //查看post的详细信息  其中post为表的名字

>alter table posts  rename to xxx; // 修改表名

>drop table xxx;//删除表

>\q //退出

（二）新建一个sql语句并执行

nano db.sql

create table posts(title varchar(255),content text);    //创建数据表

保存退出

psql xxx

>\i db.sql //执行db.sql

>\dt //当前表一览

二、字段类型

（一）postgreSql的基础数据类型

数值型

integer(int)

real //浮点

serial //序列型：一般用于表记录，每次id+1

文字型

char //10位不足10位补空格不常用存男女 01用的多

varchar // 用的多

text

布尔型

boolean

日期型

date //年月日

time //时分秒

timestamp //年月日+时分秒

特色类型

Array //数组

网络地址类型（inet）

JSON型

XML型

三、添加表约束

db.sql

create table posts (
    id serial primary key,
    title varchar(255) not null,
    content text check(length(content) > 8),
    is_draft boolean default TRUE,
    is_del boolean default FALSE,
    created_date timestamp default 'now'
);

说明

约束条件：

not null:不能为空 //比如用户名

unique:在所有数据中值必须唯一 //用户的emial地址，id等

check:字段设置条件 //自定义约束

default:字段默认值 //当用户的insert没有传入的默认值

primary key(not null, unique):主键，不能为空，且不能重复 // 经常用

四、INSERT语句

知识点

insert into [tablename] (field, …) values (value, …)

实战演习

$ psql test
> \dt
> \d posts

SQL部分

> insert into posts (title, content) values ('', ''); //'' 不是null，能过，但是content小于8
> insert into posts (title, content) values (NULL, '');
> insert into posts (title, content) values ('title1', 'content11');
> select * from posts; //看插入表的内容
> insert into posts (title, content) values ('title2', 'content22');
> insert into posts (title, content) values ('title3', 'content33');
> select * from posts;

五、SELECT语句

知识点

select 基本使用

实战演习

init.sql

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

insert into users (player, score, team) values
('库里', 28.3, '勇士'),
('哈登', 30.2, '火箭'),
('阿杜', 25.6, '勇士'),
('阿詹', 27.8, '骑士'),
('神龟', 31.3, '雷霆'),
('白边', 19.8, '热火');

SQL实战

$ psql komablog
> \i init.sql
> \dt
> \d users
> select * from users;
> \x   // 横向显示变成纵向显示
> select * from users;
> \x
> select * from users;
> select player, score from users; // 指定字段

六、WHERE语句

知识点

where语句的使用

使用where语句来设定select,update,delete语句数据抽出的条件。

实战演习

> select * from users;
> select * from users where score > 20;
> select * from users where score < 30;
> select * from users where score > 20 and score < 30;
> select * from users where team = '勇士';
> select * from users where team != '勇士';
> select * from users where player like '阿%';
> select * from users where player like '阿_';

七、数据抽出选项

知识点

select语句在抽出数据时，可以对语句设置更多的选项，已得到想要的数据。

order by //排序
limit //取前x名
offset //从第x条开始取

实战演习

> select * from users order by score asc;  //asc 升序
> select * from users order by score desc;  //desc 降序
> select * from users order by team;
> select * from users order by team, score; //复合排序
> select * from users order by team, score desc;
> select * from users order by team desc, score desc;//球队降序同时得分降序
> select * from users order by score desc limit 3; //得分降序同时取前3
> select * from users order by score desc limit 3 offset 1;//得分降序同时取前3 从第二名开始取
> select * from users order by score desc limit 3 offset 2;得分降序同时取前3 从第三名开始取
> select * from users order by score desc limit 3 offset 3;

八、统计抽出数据

知识点

distinct // 过滤重复的数据
sum //求和
max/min //取出最大值或最小值
group by/having //集合进行分组再进行计算

实战演习

> select distinct team from users; //取出所有user再进行过滤重复的数据
> select sum(score) from users;
> select max(score) from users;
> select min(score) from users;
> select * from users where score = (select max(score) from users);
> select * from users where score = (select min(score) from users);
> select team, max(score) from users group by team;
> select team, max(score) from users group by team having max(score) >= 25;
> select team, max(score) from users group by team having max(score) >= 25 order by max(score);

//having 是针对group进行的过滤类似于where

九、方便的函数

知识点

length //判断字符串长度
concat //连接两个字符串
alias // 起别名
substring //切割字符串
random //返回随机数

参考网站：

https://www.postgresql.org/docs/9.5/static/functions.html

实战演习

> select player, length(player) from users;
> select player, concat(player, '/', team) from users;
> select player, concat(player, '/', team) as "球员信息" from users; //as别名
> select substring(team, 1, 1) as "球队首文字" from users;//切球队的名称，从第一个字符开始切，切1个
> select concat('我', substring(team, 1, 1)) as "球队首文字" from users;
> select random();//随机数
> select * from users order by random();//随机排序
> select * from users order by random() limit 1;

十、更新和删除

知识点

update [table] set [field=newvalue,…] where …
delete from [table] where … //物理删除，数据删完难以恢复，慎用，尽量采用逻辑删除

实战演习

> update users set score = 29.1 where player = '阿詹';
> update users set score = score + 1 where team = '勇士';
> update users set score = score + 100 where team IN ('勇士', '骑士');
> delete from users where score > 30;

十一、变更表结构

知识点

alter table [tablename] …
create index …
drop index …

实战演习

> \d users;
> alter table users add fullname varchar(255);
> \d users;
> alter table users drop fullname;
> \d users;
> alter table users rename player to nba_player;
> \d users;
> alter table users alter nba_player type varchar(100);
> \d users;
> create index nba_player_index on users(nba_player);
> \d users;
> drop index nba_player_index;
> \d users;

十二、操作多个表

知识点

表结合查询的基础知识

实战演习

renew.sql

create table users (
    id serial primary key,
    player varchar(255) not null,
    score real,
    team varchar(255)
);
insert into users (player, score, team) values
('库里', 28.3, '勇士'),
('哈登', 30.2, '火箭'),
('阿杜', 25.6, '勇士'),
('阿詹', 27.8, '骑士'),
('神龟', 31.3, '雷霆'),
('白边', 19.8, '热火');

create table twitters (
    id serial primary key,
    user_id integer,
    content varchar(255) not null
);
insert into twitters (user_id, content) values
(1, '今天又是大胜,克莱打的真好!'),
(2, '今晚我得了60分,哈哈!'),
(3, '获胜咱不怕,缺谁谁尴尬.'),
(4, '明年我也可能转会西部'),
(5, '我都双20+了，怎么球队就是不胜呢?'),
(1, '明年听说有条大鱼要来,谁呀?');

SQL实行

$ dropdb komablog;
$ createdb komablog;
$ psql komablog;
> \i renew.sql
> select * from users;
> select * from twitters;
> select users.player, twitters.content from users, twitters where users.id = twitters.user_id;
> select u.player, t.content from users as u, twitters as t where u.id = t.user_id;
> select u.player, t.content from users as u, twitters as t where u.id = t.user_id and u.id = 1;

十三、使用视图

视图概念

视图（View）是从一个或多个表导出的对象。视图与表不同，视图是一个虚表，即视图所对应的数据不进行实际存储，数据库中只存储视图的定义，在对视图的数据进行操作时，系统根据视图的定义去操作与视图相关联的基本表。

小马解释

视图就是一个SELECT语句，把业务系统中常用的SELECT语句简化成一个类似于表的对象，便于简单读取和开发。

知识点

使用数据库视图(view)
- create view …
- drop view …

实战演习

> select u.player, t.content from users as u, twitters as t where u.id = t.user_id and u.id = 1;
> create view curry_twitters as select u.player, t.content from users as u, twitters as t where u.id = t.user_id and u.id = 1;
> \dv
> \d curry_twitters
> select * from curry_twitters;
> drop view curry_twitters;
> \dv

十四、使用事务

数据库事务(Database Transaction) ，是指作为单个逻辑工作单元执行的一系列操作，要么完全地执行，要么完全地不执行。事务处理可以确保除非事务性单元内的所有操作都成功完成，否则不会永久更新面向数据的资源。通过将一组相关操作组合为一个要么全部成功要么全部失败的单元，可以简化错误恢复并使应用程序更加可靠。一个逻辑工作单元要成为事务，必须满足所谓的ACID（原子性、一致性、隔离性和持久性）属性。事务是数据库运行中的逻辑工作单位，由DBMS中的事务管理子系统负责事务的处理。

知识点

PostgreSql数据库事务使用
- begin
- commit
- rollback

实战演习

> select * from users;
> begin;  //开始一个事务
> update users set score = 50 where player = '库里';
> update users set score = 60 where player = '哈登';
> commit; //提交
> select * from users;
> begin;
> update users set score = 0 where player = '库里';
> update users set score = 0 where player = '哈登';
> rollback; //回滚
> select * from users;

附：JSON学习

（一）定义：

JSON(JavaScript Object Notation, JS 对象简谱) 是一种轻量级的数据交换格式。易于人阅读和编写，同时也易于机器解析和生成，并有效地提升网络传输效率。

JSON是一个标记符的序列。这套标记符包含六个构造字符、字符串、数字和三个字面名。

JSON是一个序列化的对象或数组。

构造字符：[ ] { } : ,

字面值 (false、null、true)

一些合法的JSON的实例：

{"a": 1, "b": [1, 2, 3]}
[1, 2, "3", {"a": 4}]
3.14
"plain_text"

（二）JSON与JS对象的关系

JSON 是 JS 对象的字符串表示法，它使用文本表示一个 JS 对象的信息，本质是一个字符串。

如

var obj = {a: 'Hello', b: 'World'}; //这是一个对象，注意键名也是可以使用引号包裹的
var json = '{"a": "Hello", "b": "World"}'; //这是一个 JSON 字符串，本质是一个字符串

（三）示例

表示对象

对象是一个无序的“‘名称/值’对”集合。一个对象以{左括号开始，}右括号结束。每个“名称”后跟一个:冒号；“‘名称/值’ 对”之间使用,逗号分隔。

{"firstName": "Brett", "lastName": "McLaughlin"}

表示数组

和普通的 JS 数组一样，JSON 表示数组的方式也是使用方括号 []。

{ 
"people":[ 
{
"firstName": "Brett",            
"lastName":"McLaughlin"        
},      
{        
"firstName":"Jason",
"lastName":"Hunter"
}
]
}

这不难理解。在这个示例中，只有一个名为 people的变量，值是包含两个条目的数组，每个条目是一个人的记录，其中包含名和姓。上面的示例演示如何用括号将记录组合成一个值。当然，可以使用相同的语法表示更过多的值（每个值包含多个记录）。

（四）实例比较

用XML表示中国部分省市数据如下：

<?xml version="1.0" encoding="utf-8"?>
<country>
    <name>中国</name>
    <province>
        <name>黑龙江</name>
        <cities>
            <city>哈尔滨</city>
            <city>大庆</city>
        </cities>
    </province>
    <province>
        <name>广东</name>
        <cities>
            <city>广州</city>
            <city>深圳</city>
            <city>珠海</city>
        </cities>
    </province>
    <province>
        <name>台湾</name>
        <cities>
            <city>台北</city>
            <city>高雄</city>
        </cities>
    </province>
    <province>
        <name>新疆</name>
        <cities>
            <city>乌鲁木齐</city>
        </cities>
    </province>
</country>

{
    "name": "中国",
    "province": [{
        "name": "黑龙江",
        "cities": {
            "city": ["哈尔滨", "大庆"]
        }
    }, {
        "name": "广东",
        "cities": {
            "city": ["广州", "深圳", "珠海"]
        }
    }, {
        "name": "台湾",
        "cities": {
            "city": ["台北", "高雄"]
        }
    }, {
        "name": "新疆",
        "cities": {
            "city": ["乌鲁木齐"]
        }
    }]
}

PostgreSql和JSON

JSON和JSONB

有两种 JSON 数据类型：json 和 jsonb。它们几乎接受完全相同的值集合作为输入。主要的实际区别之一是效率。

json数据类型存储输入文本的精准拷贝，处理函数必须在每次执行时必须重新解析该数据。

jsonb数据被存储在一种分解好的二进制格式中，它在输入时要稍慢一些，因为需要做附加的转换。但是 jsonb在处理时要快很多，因为不需要解析。jsonb也支持索引，这也是一个令人瞩目的优势。

由于json类型存储的是输入文本的准确拷贝，其中可能会保留在语法上不明显的、存在于记号之间的空格，还有 JSON 对象内部的键的顺序。还有，如果一个值中的 JSON 对象包含同一个键超过一次，所有的键/值对都会被保留（处理函数会把最后的值当作有效值）。相反，jsonb不保留空格、不保留对象键的顺序并且不保留重复的对象键。如果在输入中指定了重复的键，只有最后一个值会被保留。

表 JSON 基本类型和相应的PostgreSQL类型

JSON 基本类型	PostgreSQL类型	注释
`string`	`text`	不允许`\u0000`，如果数据库编码不是 UTF8，非 ASCII Unicode 转义也是这样
`number`	`numeric`	不允许`NaN` 和 `infinity`值
`boolean`	`boolean`	只接受小写`true`和`false`拼写
`null`	(无)	SQL `NULL`是一个不同的概念、

JSON 输入和输出语法

-- 简单标量/基本值
-- 基本值可以是数字、带引号的字符串、true、false或者null
SELECT '5'::json;

-- 有零个或者更多元素的数组（元素不需要为同一类型）
SELECT '[1, 2, "foo", null]'::json;

-- 包含键值对的对象
-- 注意对象键必须总是带引号的字符串
SELECT '{"bar": "baz", "balance": 7.77, "active": false}'::json;

-- 数组和对象可以被任意嵌套
SELECT '{"foo": [true, "bar"], "tags": {"a": 1, "b": null}}'::json;

如前所述，当一个 JSON 值被输入并且接着不做任何附加处理就输出时， json会输出和输入完全相同的文本，而jsonb 则不会保留语义上没有意义的细节（例如空格）。例如，注意下面的不同：

SELECT '{"bar": "baz", "balance": 7.77, "active":false}'::json;
                      json                       
-------------------------------------------------
 {"bar": "baz", "balance": 7.77, "active":false}
(1 row)

SELECT '{"bar": "baz", "balance": 7.77, "active":false}'::jsonb;
                      jsonb                       
--------------------------------------------------
 {"bar": "baz", "active": false, "balance": 7.77}
(1 row)

值得一提的一种语义上无意义的细节是，在jsonb中数据会被按照底层 numeric类型的行为来打印。实际上，这意味着用E记号输入的数字被打印出来时就不会有该记号，例如：

SELECT '{"reading": 1.230e-5}'::json, '{"reading": 1.230e-5}'::jsonb;
         json          |          jsonb          
-----------------------+-------------------------
 {"reading": 1.230e-5} | {"reading": 0.00001230}
(1 row)

JSONB 包含和存在

-- 简单的标量/基本值只包含相同的值：
SELECT '"foo"'::jsonb @> '"foo"'::jsonb;

-- 右边的数字被包含在左边的数组中：
SELECT '[1, 2, 3]'::jsonb @> '[1, 3]'::jsonb;

-- 数组元素的顺序没有意义，因此这个例子也返回真：
SELECT '[1, 2, 3]'::jsonb @> '[3, 1]'::jsonb;

-- 重复的数组元素也没有关系：
SELECT '[1, 2, 3]'::jsonb @> '[1, 2, 2]'::jsonb;

-- 右边具有一个单一键值对的对象被包含在左边的对象中：
SELECT '{"product": "PostgreSQL", "version": 9.4, "jsonb": true}'::jsonb @> '{"version": 9.4}'::jsonb;

-- 右边的数组不会被认为包含在左边的数组中，
-- 即使其中嵌入了一个相似的数组：
SELECT '[1, 2, [1, 3]]'::jsonb @> '[1, 3]'::jsonb;  -- 得到假

-- 但是如果同样也有嵌套，包含就成立：
SELECT '[1, 2, [1, 3]]'::jsonb @> '[[1, 3]]'::jsonb;

-- 类似的，这个例子也不会被认为是包含：
SELECT '{"foo": {"bar": "baz"}}'::jsonb @> '{"bar": "baz"}'::jsonb;  -- 得到假

-- 包含一个顶层键和一个空对象：
SELECT '{"foo": {"bar": "baz"}}'::jsonb @> '{"foo": {}}'::jsonb;

结构必须匹配的一般原则有一种特殊情况，一个数组可以包含一个基本值：

-- 这个数组包含基本字符串值：
SELECT '["foo", "bar"]'::jsonb @> '"bar"'::jsonb;

-- 反之不然，下面的例子会报告“不包含”：
SELECT '"bar"'::jsonb @> '["bar"]'::jsonb;  -- 得到假

jsonb还有一个存在操作符，它是包含的一种变体：它测试一个字符串（以一个text值的形式给出）是否出现在jsonb值顶层的一个对象键或者数组元素中。除非特别注解，下面这些例子返回真：

-- 字符串作为一个数组元素存在：
SELECT '["foo", "bar", "baz"]'::jsonb ? 'bar';

-- 字符串作为一个对象键存在：
SELECT '{"foo": "bar"}'::jsonb ? 'foo';

-- 不考虑对象值：
SELECT '{"foo": "bar"}'::jsonb ? 'bar';  -- 得到假

-- 和包含一样，存在必须在顶层匹配：
SELECT '{"foo": {"bar": "baz"}}'::jsonb ? 'bar'; -- 得到假

-- 如果一个字符串匹配一个基本 JSON 字符串，它就被认为存在：
SELECT '"foo"'::jsonb ? 'foo';

我是小烂毛

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
1
评论
PostgreSql学习笔记

一、操作表数据（一）基本语法sudo su postgres //切换到数据库：psql --version //查看数据库版本psql-l //查看当前所有数据库createdb xxx //创建新的数据库dropdb xxx //删除数据库psql xxx // 进入指定的数据库 >help >\q退出 psql xxx>create table posts(title varchar(255),content text); //创建数据表>
复制链接

扫一扫