PostgreSQL指令笔记

内容出自于小马技术PostgreSQL入门之观看笔记

整理一些语法与使用方法，未来需要时可以迅速回忆起要怎么使用，再慢慢更新

查询版本
先确认是否下载了postgresql

postgres --version

如果看到下面这行就是下载成功了！

postgres (PostgreSQL) 14.10 (Homebrew)

启动 PostgreSQL 14 伺服器：
使用 brew services start 命令来启动 PostgreSQL 14 伺服器，并且它将在系统启动时自动重新启动。执行以下命令：
brew services start postgresql@14
这将启动 PostgreSQL 14 伺服器。

**关闭 PostgreSQL 14 伺服器：
**
若要停止运行 PostgreSQL 14 伺服器，您可以使用以下命令：
brew services stop postgresql@14
或者，如果使用手动方式启动伺服器，可以按 Ctrl+C 来停止它。

连线到资料库
psql

如果要连接到不同的资料库或使用不同的用户名
psql -U username -d dbname

操作资料库
要列出所有表格：
\dt
要退出psql命令行界面：
\q
切换到postgres用户：

sudo su postgres

建立资料库：

createdb db_name  # 建立一个名为db_name的资料库

连线到资料库：

psql -d db_name  # 连线到名为db_name的资料库

建立表格：

create table table_name (    title varchar(255),    content text);-- 建立名为table_name的表格，宣告列名和资料型态

查看资料表结构

\d table_name-- 显示名为table_name资料表的结构

显示所有SQL语法

\h

显示所有指令

\?

执行SQL文件

\i filename.sql-- 执行filename.sql的SQL文件中的SQL指令

切换回一般用户

su username-- 退出postgres用户，切换回username用户

基础资料库语法

建立posts表格 宣告多个列及资料型态

create table posts (    id serial primary key, -- 主键列，不能为空，且不能重复    title varchar(255) not null, -- 标题列，不能为空    content text check(length(content) > 8), -- 内容列，检查长度是否大于8    is_draft boolean default TRUE, -- 是否是草稿，默认为TRUE    is_del boolean default FALSE, -- 是否删除，默认为FALSE    created_date timestamp default 'now' -- 建立日期，默认为现在时间);

新增资料
insert into [tablename] (field, …) values (value, …)

搜寻表格
select * from [tablename]
select * from users; -- 印出users表格内容
select player, score from users; -- 从"users"表格中选择玩家和分数列印出

where指令
select * from users;
select * from users where score > 20; -- 印出大于20的分数
select * from users where score < 30; -- 印出小于30的分数
select * from users where score > 20 and score < 30; -- 印出20~30之间的分数
select * from users where team = '勇士'; -- 印出队伍为勇士的成员
select * from users where team != '勇士'; -- 印出队伍为勇士以外的成员
select * from users where player like '阿%'; -- 印出队员名以“阿”为开头的成员
select * from users where player like '阿_'; -- 印出队员名以“阿”为开头且只有一个字的成员

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; -- 先按队伍名称降幂排序，再按分数降幂排序
select * from users order by score desc limit 3; -- 按分数降幂排序并限制结果为3调资料
select * from users order by score desc limit 3 offset 1; -- 按分数降幂排序，从第2条资料开始限制结果为三条资料
select * from users order by score desc limit 3 offset 2; -- 按分数降幂排序，从第3条资料开始限制结果为三条资料
select * from users order by score desc limit 3 offset 3; -- 按分数降幂排序，从第4条资料开始限制结果为三条资料

distinct
sum
max/min
group by/having使用

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; -- 按队伍分组，列出分数大于等于25的队伍
select team, max(score) from users group by team having max(score) >= 25 order by max(score); -- 按队伍分组，列出分数大于等于25的队伍，并按最高分数排序

length
concat
alias
substring
random使用

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; -- 随机排序并限制只输出一条资料

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

update users set score = 29.1 where player = '阿詹'; -- 更新玩家"阿詹"的分数为29.1
update users set score = score + 1 where team = '勇士'; -- 更新"勇士"队所有玩家的分数加1
update users set score = score + 100 where team IN ('勇士', '骑士'); -- 更新"勇士"和"骑士"队所有玩家的分数加100
delete from users where score > 30; -- 删除分数大于30的玩家资料

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

alter table users add fullname varchar(255); -- 在"users"表格中新增"fullname"列
alter table users drop fullname; -- 从"users"表格中删除"fullname"列
alter table users rename player to nba_player; -- 将"player"列重命名为"nba_player"
alter table users alter nba_player type varchar(100); -- 修改"nba_player"列的资料型态为varchar(100)
create index nba_player_index on users(nba_player); -- 在"nba_player"列上建立索引
drop index nba_player_index; -- 删除"nba_player_index"索引

表格结合查询的基础知识

在renew.sql中建立"users"表格并插入资料

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

建立twitters表格并插入资料

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

在命令列操作资料库

$ dropdb raysblog; -- 删除资料库"raysblog"$ createdb raysblog; -- 建立资料库"raysblog"$ psql raysblog; -- 进入"raysblog"资料库的命令行介面

执行SQL文件

\i renew.sqlselect * from users;select * from twitters;

使用SQL查询从"users"和"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;

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;select * from curry_twitters;drop view curry_twitters;

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;