內容出自於小馬技術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.sql
select * 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;