PostgreSQL学习笔记

PostgreSQl学习笔记

此文章为蒟蒻我的学习笔记，仅供个人日后复习查看，对于想要学习PostgreSQL的新手来说，并没有很强的指导意义，谢谢。
而且大多数内容为B站小马视频的小马哥所做的笔记，再次感谢他！

一、Windows环境下PostgreSQL安装问题

1、PostgreSQL安装地址：下载地址：https://www.enterprisedb.com/downloads/postgres-postgresql-downloads。

注意：在Windows10环境下安装PostgreSQL高版本时，可能会出现未知的错误（怀疑时系统路径有中文的缘故）。因此建议安装较低的版本，其稳定性也是比较不错的。

2、根据安装引导一步一步安装即可。
3、安装完查看是否有这两个工具：psql.exe和pgAdmin4.exe,如果有即安装成功！
注意：因为psql在程序内部默认的管理员是postgres,即我们设置的口令也是postgres的口令，但是spql在初次运行时，默认的管理员是win10的管理员，而这个用户在数据库中根本不存在，此时便会有口令错误，而登不进去的问题。
解决方法：将系统的环境变量里添加一个变量名PGUSER，值设定为postgres

二、常见的用法

（一）、常见命令

$ createdb komablog //创建数据库
$ psql -l //查看现有表单
$ psql komablog //选择数据库
> help
> \h
> \?
> \l
> \q
$ psql komablog
> select now();
> select version();
> \q
$ dropdb komablog
$ psql -l  

（二）、字段类型

1、数值型

（1）integer(int)，整型
（2）real，浮点型。
（3）serial,序列型，加一。

2、文字型

(1)char

(2)varchar

(3)text

3、布尔型 ：boolean

4、日期型

（1） date 年月日
（2）time 时分秒
（3）timestamp 年月日+时分秒

5、特色类型：

（1）Array ，数组型
（2）网络地址型(inet), ip地址型
（3）JSON型
（4）XML型，类似text型。

（三）、添加表约束

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:在所有数据中值必须唯一
check:字段设置条件
default:字段默认值
primary key(not null, unique):主键，不能为空，且不能重复

(四)、INSERT语句

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

(五) SELECT语句

1、知识点

• select 基本使用

2、实战演习

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

3、 SQL实战

> \i init.sql
> \dt
> \d users
> select * from users;  \\选择所有的字段
> \x  \\打开扩展
> select * from users;
> \x
> select * from users;
> select player, score from users; \\选择指定的字段

（六）、WHERE语句

1、 知识点

• where语句的使用

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

2、实战演习

> 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 '阿_';

（七）数据抽出选项

1、知识点

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

• order by 排序
• limit 取前几条
• offset 偏移量

2、实战演习

> select * from users order by score asc; \\依据score升序排列
> 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;
> select * from users order by score desc limit 3;
> select * from users order by score desc limit 3 offset 1;
> select * from users order by score desc limit 3 offset 2;
> select * from users order by score desc limit 3 offset 3;

（八）、统计抽出数据

1、知识点

• distinct 过滤重复的数据
• sum 数据求和
• max/min 挑选最大值和最小值
• group by/having 集合分组，计算

2、实战演习

> select distinct team from users;
> 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);

（九）、一些方便的函数

1、 知识点

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

2、实战演习

> select player, length(player) from users;
> select player, concat(player, '/', team) from users;
> select player, concat(player, '/', team) as "球员信息" from users;
> 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 1;

（十）、更新和删除

1、知识点

• update [table] set [field=newvalue,…] where …
• delete from [table] where …

2、实战演习

> 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;

(十一）、变更表结构

1、知识点

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

2、实战演习

> \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;

（十二）、操作多个表

1、知识点

• 表结合查询的基础知识

2、实战演习

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, '明年听说有条大鱼要来,谁呀?');

3、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;

(十三）、使用视图

1、视图概念

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

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

2、知识点

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

3、实战演习

> 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

（十四）、使用事务

1、事物概念

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

2、知识点

• PostgreSql数据库事务使用
  • begin \\事物开始
  • commit \\事物完成
  • rollback \\数据回滚，回到更新前的状态

3、实战演习

> 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;