记录灵感、技术、思考

Postgresql笔记01基本操作

•

主要目标

熟悉和理解SQL的基本操作。基本功能和逻辑。这部分和其他数据库共通。属于标准SQL范围。

学习资料

资料

视频

实战笔记

如下，自我验证的笔记。

一、开始

安装

https://www.postgresql.org/download/

安装指定版本

Ubuntu

sudo apt install postgresql-9.6

安装成功提示：


成功。您现在可以用下面的命令开启数据库服务器：

/usr/lib/postgresql/9.6/bin/pg_ctl -D /var/lib/postgresql/9.6/main -l logfile start

Mac

brew install postgresql@9.6

参考 https://formulae.brew.sh/formula/postgresql

安装成功提示

If builds of PostgreSQL 9 are failing and you have version 8.x installed,
you may need to remove the previous version first. See:
  https://github.com/Homebrew/legacy-homebrew/issues/2510

This formula has created a default database cluster with:
  initdb /usr/local/var/postgresql@9.6
For more details, read:
  https://www.postgresql.org/docs/9/app-initdb.html

postgresql@9.6 is keg-only, which means it was not symlinked into /usr/local,
because this is an alternate version of another formula.

If you need to have postgresql@9.6 first in your PATH, run:
  echo 'export PATH="/usr/local/opt/postgresql@9.6/bin:$PATH"' >> ~/.zshrc

For compilers to find postgresql@9.6 you may need to set:
  export LDFLAGS="-L/usr/local/opt/postgresql@9.6/lib"
  export CPPFLAGS="-I/usr/local/opt/postgresql@9.6/include"

For pkg-config to find postgresql@9.6 you may need to set:
  export PKG_CONFIG_PATH="/usr/local/opt/postgresql@9.6/lib/pkgconfig"


To have launchd start postgresql@9.6 now and restart at login:
  brew services start postgresql@9.6
Or, if you don't want/need a background service you can just run:
  pg_ctl -D /usr/local/var/postgresql@9.6 start
==> Summary
🍺  /usr/local/Cellar/postgresql@9.6/9.6.22: 1,651 files, 24.8MB

仔细看这里需要执行

echo 'export PATH="/usr/local/opt/postgresql@9.6/bin:$PATH"' >> ~/.zshrc

卸载

Ubuntu

sudo apt-get --purge remove postgresql\*

Mac

brew uninstall postgresql

启动服务

Ubuntu

sudo service postgresql start 
sudo service postgresql restart

Mac

brew services start postgresql@9.6

查看状态

Ubuntu

pg_lsclusters

1.修改psql的密码

想要进入Postgresql。在Ubuntu中 Postgresql会创建一个默认用户postgres 并且密码随机。所以需要修改密码。

1） 登录Postgresql

sudo -u postgres psql

2） 修改postgres的密码

ALTER USER postgres WITH PASSWORD 'postgres';

这样外部才能正常使用比如pgAdmin登录进去。

2.修改Linux中posgres的密码

PostgreSQL会创建一个默认的Linux用户 postgres，修改系统中这个用户的密码

1） 删除密码

sudo passwd -d postgres

2） 设置密码

sudo -u postgres passwd

遵循系统提示就好了。

ubuntu下进入 psql超级用户操作

1) 以Postgresql建立的 postgres用户进入 psql

sudo -u postgres psql

2） 查看所有数据库

\l

3) 退出 数据库界面

\q

4) 进入 test 数据库

\c test

5) 列出所有表

\d

6) 列出 test下 tb1 信息

\d tb1

二、简单使用

1.默认建立了一个用户 postgres

切换到用户才能使用。默认账户也是系统管理员

进入特定用户

sudo su postgres

Mac 用户是 brew安装的，直接使用好了。

在用户的全局添加了一些全局的shell命令，比如建立数据库 createdb，dropdb, postgresql的命令行程序 psql

查看版本

psql --version

查看数据库列表

psql -l


postgres@ubuntu:/home/mark24/LabSpace/sql-test$ psql -l
                                     数据库列表
   名称    |  拥有者  | 字元编码 |  校对规则   |    Ctype    |       存取权限        
-----------+----------+----------+-------------+-------------+-----------------------
 markblog  | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 |
 postgres  | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 |
 template0 | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(4 行记录)

创建数据库

createdb <db name>

删除数据库

dropdb <db name>

进入数据库

psql <db name>

进入数据库界面会换一个样子:

postgres@ubuntu:/home/mark24/LabSpace/sql-test$ psql markblog
psql (9.6.22)
输入 "help" 来获取帮助信息.

markblog=#

进入数据库之后，可以用一些简单缩写命令

markblog=# help
您正在使用psql, 这是一种用于访问PostgreSQL的命令行界面
键入： \copyright 显示发行条款
       \h 显示 SQL 命令的说明
       \? 显示 pgsql 命令的说明
       \g 或者以分号(;)结尾以执行查询
       \q 退出



\h  可以加上SQL关键字查看关键字用法，什么都不加，打开关键字列表
\?  加上命令可以看命令解释没什么都不加，可以看到所有的命令列表

两个系统提供的函数。进入DB之后

select  version();
select now();

三、操作数据表

1.建立一个数据表

create table

例如

CREATE TABLE postes ( title varchar(255), content text);

\dt 可以通过这个命令列出所有的表查看

2.删除一个数据表

drop table

drop table <table name>;

3.使用sql 文件建立表格

1) 推出数据库模式

2) vim 编辑文件 比如 db.sql

db.sql

CREATE TABLE postes ( title varchar(255), content text);

3) 进入 database 即 posql

i命令导入了sql文件执行

\i db.sql

4) 变更表名

alter table <old table name> rename to <new table name>;

5) 列出所有的表，查看

\dt

6) 查看表的描述

\d <table name>

7) 删除一个表

DROP TABLE <table name>;

四、 字段类型

仅仅列出工作中比较常用的：

数字

  • integer(int)

    四个字节

  • real

    四个字节，浮点数

  • serial

    序列，用于自增ID

文字

  • char

    定长字符。比如10个字符空间，赋值2个，其余8个留空

  • varchar 常用。变长。比如10个字符，赋值2个就是2个。

  • text

    大文本

布尔

  • boolean

日期

  • date

    年月日

  • time

    时分秒

  • timestamp

    年月日+时分秒

特色类型

  • Array

  • 网络地址型（inet）

  • JSON型

  • XML型

全部的数据类型可以参考官方文档 Postgresql 9.6 datatype

五、约束

对字段的一些限制就是约束。

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

约束举例子解释

not null 不能为空

unique 在所有数据中必须唯一

check 字段设置条件

default  默认值

primary key (not null, unique)  主键，自带两个属性，非空，不重复

代码分析

  • ID作为主键

    单一ID做主键方便增删查改 primary key 是一个关键字，标记是主键（暗含了，not null 且 unique） serial 是自增类型

  • 标题

    varchar 变长，最长255，非空

  • 内容

    内容长度必须 >8

  • 草稿标记

    默认是草稿

  • 软删除标记

    默认是不删除

    推荐做逻辑删除。

  • 创建时间

六、Insert语句

insert into <table name> (field, ...) values (value, ...)

sql 实验

-- 1. 约束便捷
insert into posts (title, content) values ('', '');

-- ERROR:  new row for relation "posts" violates check constraint "posts_content_check"
-- DETAIL:  Failing row contains (2, , , t, f, 2021-06-30 13:34:41.442131).

-- 2. 标题null
insert into posts (title, content) values (null, '');

-- ERROR:  null value in column "title" violates not-null constraint
-- DETAIL:  Failing row contains (3, null, , t, f, 2021-06-30 13:34:41.442131).

-- 3. 全对的情况

insert into posts (title, content) values ('title' , 'content ABC');

-- markblog=# insert into posts (title, content) values ('title' , 'content ABC');
-- INSERT 0 1

-- 插入成功,查看插入结果，需要用select。 想想一下 select就像在excel中选择特定的列，然后会返回特定的行。

select  * from  posts;
--  id | title |   content   | if_draft | is_del |        created_date
-- ----+-------+-------------+----------+--------+----------------------------
--   4 | title | content ABC | t        | f      | 2021-06-30 13:34:41.442131
-- (1 row)

理解 select，查看插入结果，需要用select。 想想一下 select就像在excel中选择特定的列，然后会返回特定的行。

七、Select 语句

select的基本使用方法。

实战部分，先要准备数据。

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


insert into users (player, score, team) 
values
('tom', 28.4, 'A'),
('nick', 98.4, 'B'),
('mark', 30.4, 'C'),
('linda', 100.0, 'D');

SQL实战

-- 1. 拿出所有数据
select * from users;

--  id | player | score | team
-- ----+--------+-------+------
--   1 | tom    |  28.4 | A
--   2 | nick   |  98.4 | B
--   3 | mark   |  30.4 | C
--   4 | linda  |   100 | D
-- (4 rows)

-- 1.1 \x 字段比较多的时候，可以通过增加 \x 返回让字段更清晰。 \x 是一个开关。上下文调用呈现 toggle开关效果

\x

-- markblog=# \x
-- Expanded display is on.
-- markblog=# select * from users;
-- -[ RECORD 1 ]-
-- id     | 1
-- player | tom
-- score  | 28.4
-- team   | A
-- -[ RECORD 2 ]-
-- id     | 2
-- player | nick
-- score  | 98.4
-- team   | B
-- -[ RECORD 3 ]-
-- id     | 3
-- player | mark
-- score  | 30.4
-- team   | C
-- -[ RECORD 4 ]-
-- id     | 4
-- player | linda
-- score  | 100
-- team   | D


-- 再次运行 \x 可以关闭

-- 2. 选出目标字段

select player, score from users;

八、where语句

where语句基本使用。配合提取、更新删除。用于过滤条件。


select * from users;

select * from users where score > 20;

select * from users where score > 20 and score <30 ;

-- 等于
select * from users where team = 'A';

-- 不等于
select * from users where team != 'A';

-- %表示后面所有数据
select * from users where player like 't%';

-- _表示一个字符
select * from users where player like 't_';

实验结果



markblog=# \x
Expanded display is off.
markblog=# select * from users;
 id | player | score | team
----+--------+-------+------
  1 | tom    |  28.4 | A
  2 | nick   |  98.4 | B
  3 | mark   |  30.4 | C
  4 | linda  |   100 | D
(4 rows)

markblog=# select * from users where score > 20;
 id | player | score | team
----+--------+-------+------
  1 | tom    |  28.4 | A
  2 | nick   |  98.4 | B
  3 | mark   |  30.4 | C
  4 | linda  |   100 | D
(4 rows)

markblog=# select * from users where score > 50;
 id | player | score | team
----+--------+-------+------
  2 | nick   |  98.4 | B
  4 | linda  |   100 | D
(2 rows)

markblog=# select * from users where score > 20 and score <30 ;
 id | player | score | team
----+--------+-------+------
  1 | tom    |  28.4 | A
(1 row)

markblog=# select * from users where team = 'A';
 id | player | score | team
----+--------+-------+------
  1 | tom    |  28.4 | A
(1 row)

markblog=# select * from users where team != 'A';
 id | player | score | team
----+--------+-------+------
  2 | nick   |  98.4 | B
  3 | mark   |  30.4 | C
  4 | linda  |   100 | D
(3 rows)

markblog=# select * from users where player like 't%';
 id | player | score | team
----+--------+-------+------
  1 | tom    |  28.4 | A
(1 row)

markblog=# select * from users where player like 't_';
 id | player | score | team
----+--------+-------+------
(0 rows)

九、数据抽出选项

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

  • order by
  • limit
  • offset
-- 升序：从小到大排序 （默认）
select * from users order by score asc;
-- 降序：从大到小排序
select * from users order by score 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;
-- 团队，获得前三， 默认offset是0
select * from users order by team desc limit 3;
-- 从第一名后面，取三个 即 2，3，4
select * from users order by team desc limit 3 offset 1;
-- limit和offset可以完成分页操作
select * from users order by team desc limit 3 offset 2;

十、统计抽出选项

  • distinct 过滤重复数据
  • sum 求和
  • max/min 最大值/最小值
  • group by / having 对集合进行分组然后计算

-- 去重
 select distinct team from users;

-- 进行求和
 select sum(score) from users;

-- 求最高
-- 这里使用函数，select <分组ID>, min(score) from users; 将是无效语句，这是一个汇总语句，需要跟上 group by 
-- select <分组ID>, min(score) from users group by <分组ID>; 才是有效的
 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;

--  having是分组中的where，就是对分组数据的过滤。
 select team, max(score) from users group by team having max(score) >= 25;

--  max(score) 其实就是一个独立字段了。 这里 as 未生效
 select team, max(score) from users group by team having max(score) >= 25 order by max(score);

十一、方便函数

知识点

  • length
  • concat
  • alias
  • substring 切割字符串
  • random

更多参考 https://www.postgresql.org/docs/9.6/functions.html

-- 获得player的长度
select player, length(player) from users;

select player, concat(player,'/',team) from users;

-- select player, count(player) from users; 这句不能工作。函数应用场景不同


-- 起一个别名
-- 警告⚠：一定要是双引号
select player, concat(player, '/', team) as "球员信息" from users;


-- 如果不加as 字段名字是函数名

-- testblog=# select player, concat(player, '/', team) from users;
--  player |  concat  
-- --------+----------
--  tom    | tom/A
--  nick   | nick/B
--  mark24 | mark24/C
--  m-pl   | m-pl/C
--  kk     | kk/C
--  SSark  | SSark/C
--  dddk   | dddk/C
--  linda  | linda/D
-- (8 行记录)

-- 第一个字节开始切，切一个
select substring(team,1,1) as "球队首文字" from users;

-- '我' 单引号作为字符串，双引号作为变量识别
select concat('我',substring(team,1,1)) as "球队首文字" from users;

select random();

-- 随机排序，抽奖的时候可以用
select * from users order by random();

-- 随即选择一个用户
select * from users order by random() limit by 1;

十二、更新和删除

update [table name] set [field=newValue,....]  where ...


-- 这个是物理删除
delete from [table name] where ....


-- 更新属性
update users set score = 29.1 where player = 'mark';

-- 属性自+1
update users set score = score + 1 where team = 'A';


-- 给定范围
update users set score = score + 100 where team IN ('A', 'B');


-- 条件物理删除
delete from users where score > 30;

十三、更新表结构

知识点

  • alter table [table name] …

创建索引

  • create index … 删除索引
  • drop index …

对需要的字段增加索引查询的速度会提升。

-- 查看users表结构
\d users;

-- 给users表新增字段
alter table users add fullname varchar(255);

-- 删除字段
alter table users drop fullname;

-- 更改字段名
alter table users rename player to nba_player;

-- 更改表中某个字段的类型
alter table users alter nba_player type varchar(100);

-- 用 users(nba_player) 的字段，创建索引，索引名字为 nba_player_index
create index nba_player_index on users(nba_player);


-- 删除制定索引
drop index nba_player_index;

十四、操作多个表

表结合查询的基础知识

数据准备,请清空之前的数据

reinit.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, '热火');

-- 通过user_id 进行逻辑关联
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, '走了。');

select * from users;

select * from twitters;


-- 组合两张表的数据
select users.player, twitters.content from users, twitters where users.id = twitters.user_id;

-- 输出结果
-- marktest=# select users.player, twitters.content from users, twitters where users.id = twitters.user_id;
--  player |   content    
-- --------+--------------
--  库里   | 今天有时大胜
--  哈登   | 我得了60分
--  阿杜   | 获胜赞不怕
--  阿詹   | 明年在来
--  神龟   | 我20+
--  库里   | 走了。
-- (6 行记录)



-- 获得所有的球员
-- 使用 as 进行别名缩短 sql，注意的是这里是from后面标记了。然后其他部分可以使用。其实from是最先执行的。
-- select和where涉及到两个表,通过一个Id实现关联 - 这就是表关联
select u.player, t.content from users as u, twitters as t where u.id = t.user_id;

-- 筛选一个用户id=1的
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语句简化成一个类似表的对象,便于简单的读取和开发。

相当于一个逻辑表。

知识点:

使用数据库视图

  • create view …

  • drop view …

-- 库里(curry)的推特
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_twitter as select u.player, t.content from users as u, twitters as t where u.id = t.user_id and u.id = 1;


-- 使用视图,像一个虚拟表,可以大幅度简化书写的SQL.
-- 我的视角这个个很像函数

select * from curry_twitter;

-- 查看视图
\dv 


-- 删除视图

drop view curry_twitter;

对于要索引的字段,要建立索引来提高速度.

对于常用的SQL,可以建立视图来方便编码和管理.

十六、使用事务

数据库事务(Database Transation) 是指作为但个逻辑工作单元执行的一些列操作.要么完全的执行,要么完全的不执行.

一个逻辑工作单元要成为十五,必须满足所谓的 ACID(原子性,一致性,隔离性,持久性)属性/

事务是数据库运行的逻辑工作单位.

知识点:

Postgresql的事务使用

  • begin
  • commit
  • rollback

-- 例子1
select * from users;

-- 开启事务
begin;

-- 这两个操作,需要同时完成
update users set score = 50 where player = '库里';
update users set score = 60 where player = '哈登';

-- 然后提交
commit;


-- 例子2

select * from user;

begin;

update users set scire = 0 where player = '库里';
update users set score = 0 where player = '哈登';

-- 回滚到更新前的状态
rollback;

select * from users;

常规基本操作完结，撒花~

Mark24

Everything can Mix.