DB command

使用psql進入db，在pq中所有指令開頭都是反斜線開頭 \help則可以查看當前可用的指令（跟你設定權限有關）。

\?則是指令查詢工具
\l列出當前可用的db

創建db

使用pq的sql指令創建，注意在pq中所有的SQL指令結尾都一定要加;不然指令不會成功

在查看pq admin這樣我們就成功創建test db了

切換DB

data type

• char 資料有固定長度，並且都為英文數字。
• nchar 資料有固定長度，但不確定是否皆為英文數字。
• varchar 資料沒有固定長度，並且都為英文數字。
• nvarchar 資料沒有固定長度，且不確定是否皆為英文數字。

創建tabel

在創建table時psql很貼心會偵測你（）的位置來判斷增新的結尾，）代表整個table的schema，;則是執行的符號

查看當前db的table的schema

查看schema info

Insert Into Database

查看table

生成假資料

mockdata

從SQL黨載入

//查看person 所有欄位
danny=# SELECT * FROM PERSON

//查看person中欄位是first_name、last_name的資料
danny=# SELECT first_name,last_name FROM PERSON

//將資料已country_of_birth排序 [asc|desc]
danny=# SELECT first_name,last_name FROM PERSON ORDER BY country_of_birth ASC

//將資料先已id排再以email排序
danny=# select * from person order by id,email;

//distinct filter重複資料
danny=# 
select distinct  country_of_birth from person 
order by country_of_birth;

//combine and in where
danny=# 
select * from person
where gender = 'Female' 
AND ( country_of_birth='Japan' OR country_of_birth='China' );

danny=# 
select * from person
where gender = 'Female'
AND ( country_of_birth in ('Japan','China') )
order by country_of_birth;

condition select

like是相似的operator語法，返回fileds中有符合like的敘述，%代表萬用字符

danny=# 
select * from person
where email like '%@google.com.%';

 id  | first_name | last_name |          email           | gender | date_of_birth | country_of_birth
-----+------------+-----------+--------------------------+--------+---------------+------------------
  67 | Der        | Al Hirsi  | dalhirsi1u@google.com.hk | Male   | 2022-10-03    | Ghana
 201 | Maris      | Lowey     | mlowey5k@google.com.hk   | Female | 2022-05-23    | United States
 523 | Bobbye     | Aiken     | baikenei@google.com.br   | Female | 2022-07-26    | China
 635 | Baillie    | Sowle     | bsowlehm@google.com.hk   | Male   | 2022-12-31    | Thailand
 697 | Crissie    | Barcke    | cbarckejc@google.com.br  | Female | 2022-09-23    | Thailand
 749 | Norman     | Skyme     | nskymeks@google.com.au   | Male   | 2022-06-17    | Sweden
(6 rows)
    
//________@% 代表找尋7個字元後加@的email
danny=# 
 select * from person 
 where email like '________@%'
 limit 5;

 id | first_name | last_name |          email          | gender | date_of_birth | country_of_birth
----+------------+-----------+-------------------------+--------+---------------+------------------
  9 | Freddy     | Savege    | fsavege8@pinterest.com  | Male   | 2022-11-16    | Brazil
 10 | Alex       | Grover    | agrover9@wisc.edu       | Male   | 2023-01-12    | Indonesia
 18 | Oralla     | Harley    | oharleyh@reddit.com     | Female | 2022-03-30    | China
 32 | Sarette    | Walder    | swalderv@bravesites.com | Female | 2022-02-18    | Netherlands
 33 | Lynne      | Darwin    | ldarwinw@scribd.com     | Female | 2022-10-28    | Cuba
(5 rows)

group by

danny=# 
select country_of_birth,count(*) from person
group by country_of_birth
order by country_of_birth;
                              

having 接在group by 後面

aggregate doc

danny=# 
select country_of_birth,count(*) from person
group by country_of_birth
having count(*)>40
order by country_of_birth;

 country_of_birth | count
------------------+-------
 China            |   195
 Indonesia        |   120
 Philippines      |    43
 Russia           |    50
(4 rows)

aggregate

//get avage data
danny=# select MAX(price) from car;
    max
-----------
 $99900.20
(1 row)

danny=# 
select make,model,min(price) from car
group by make ,model;

Operators

在sql中可以透過Operator去計算filed新值

danny=#
select id , make ,model,Round( price *.10,2),Round( price-(price *.10),2),price from car;