MySQL-常用命令(二)

MySQL-常用命令(二)

---

函式

表結構如下

 select * from vendors;
+---------+-----------+---------------------------+-----------+------------+----------+--------------+
| vend_id | vend_name | vend_address              | vend_city | vend_state | vend_zip | vend_country |
+---------+-----------+---------------------------+-----------+------------+----------+--------------+
|       1 | vend_1    | shanghai_baoshan_jinqiulu | shanghai  | shanghai   |   200000 | CN           |
|       2 | vend_2    | hefei_gaoxin_huangshanlu  | hefei     | anhui      |   300000 | CN           |
|       3 | vend_3    | hangzhou_binjiang_binjian | hangzhou  | zhejiang   |   400000 | CN           |
|       4 | vend_4    | ningbo_gaoxin_shijilu     | ningbo    | zhejiang   |   500000 | CN           |
+---------+-----------+---------------------------+-----------+------------+----------+--------------+

---

• CONCAT：用於連線欄位的值或者字串，以行的形式顯示（注意和GROUP_CONCAT的區別）

SELECT CONCAT(vend_name,`!`,vend_country) FROM Vendors;

//輸出
+------------------------------------+
| concat(vend_city,`!`,vend_country) |
+------------------------------------+
| shanghai!CN                        |
| hefei!CN                           |
| hangzhou!CN                        |
| ningbo!CN                          |
+------------------------------------+

• GROUP_CONCAT：用於連線欄位的值或者字串，以“，”分割顯示在一行（注意和CONCAT的區別）

SELECT GROUP_CONCAT(vend_city,vend_country) FROM vendors;

//輸出
+----------------------------------------+
| group_concat(vend_city,vend_country)   |
+----------------------------------------+
| shanghaiCN,hefeiCN,hangzhouCN,ningboCN |
+----------------------------------------+

• GROUP_BY: 對行分組，但輸出的值未必是原資料的同一行。選擇沒有被GROUP_BY的欄位值具有不確定性，特別是和MAX()，MIN()函式結合使用時。

select vend_state, count(*) as state_num from vendors group by vend_state;

//輸出
+------------+-----------+
| vend_state | state_num |
+------------+-----------+
| anhui      |         1 |
| shanghai   |         1 |
| zhejiang   |         2 |
+------------+-----------+

#條件分組  HAVING條件
select vend_state, count(*) as state_num from vendors group by vend_state having state_num > 1;

//輸出
+------------+-----------+
| vend_state | state_num |
+------------+-----------+
| zhejiang   |         2 |
+------------+-----------+