測試環境為 CentOS 8 x86_64 虛擬機.
- 字串函數 ( String Functions ) 與 型別轉換 – https://benjr.tw/102953
- 數字函數 (Numeric Functions) – https://benjr.tw/101970
- 日期與時間函數 (Date & Time Functions) – https://benjr.tw/102964
- 資訊函數 (Information Functions) – https://benjr.tw/102961
- 搭配 GROUP BY 函數 (Aggregate Functions) – 這邊介紹.
- 流程控制函數 (Control Flow Functions) – https://benjr.tw/102747
先建立一個測試用資料庫 (testdb) , 與 tables (employee) ,下面範例會使用到 . 格式為 K1 int(11) – auto_increment & PRIMARY KEY , Name char(20), Dept char(20), jobTitle char(20) ,email char(30) 以及 Salary int(11).
SELECT 查詢需注意資料庫字元編碼 Character Sets 與 文字排序 Collations ,請參考資料庫編碼 – https://benjr.tw/102156 .
[root@localhost ~]# mysql -u root -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 12 Server version: 10.3.11-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> CREATE DATABASE testdb DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; Query OK, 1 row affected (0.001 sec) MariaDB [(none)]> USE testdb; Database changed MariaDB [testdb]> CREATE TABLE employee (K1 int(11) NOT NULL auto_increment, Name char(20) NOT NULL, Dept char(20) NOT NULL, JobTitle char(20) NOT NULL, Salary int(11) NOT NULL , email char(30) NOT NULL, PRIMARY KEY (K1)) ; Query OK, 0 rows affected (0.010 sec) MariaDB [testdb]> DESCRIBE employee; +----------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+----------+------+-----+---------+----------------+ | K1 | int(11) | NO | PRI | NULL | auto_increment | | Name | char(20) | NO | | NULL | | | Dept | char(20) | NO | | NULL | | | JobTitle | char(20) | NO | | NULL | | | Salary | int(11) | NO | | NULL | | | email | char(30) | NO | | NULL | | +----------+----------+------+-----+---------+----------------+ 6 rows in set (0.004 sec)
透過 INSERT 新增加了五筆資料.
MariaDB [testdb]> INSERT INTO employee (Name , Dept , JobTitle , Salary , email) VALUES ('Ben' , 'Testing' , 'Engineer' , '45000' , 'ben@benjr.tw') , ('Afa' , 'Power' , 'Engineer' , '48000' , 'Afa@benjr.tw') , ('Boss' ,'Testing' , 'Manager' , '75000' , 'Boss@benjr.tw') , ('JoJo' , 'Testing' , 'Engineer' , '45000' , 'jojo@benjr.tw') , ('Ian' , 'PM' , 'Engineer' , '85000' , 'ian@benjr.tw');
Query OK, 5 rows affected (0.002 sec)
Records: 5 Duplicates: 0 Warnings: 0
MariaDB [testdb]> SELECT * FROM employee;
+----+------+---------+----------+--------+---------------+
| K1 | Name | Dept | JobTitle | Salary | email |
+----+------+---------+----------+--------+---------------+
| 1 | Ben | Testing | Engineer | 45000 | ben@benjr.tw |
| 2 | Afa | Power | Engineer | 48000 | Afa@benjr.tw |
| 3 | Boss | Testing | Manager | 75000 | Boss@benjr.tw |
| 4 | JoJo | Testing | Engineer | 45000 | jojo@benjr.tw |
| 5 | Ian | PM | Engineer | 85000 | ian@benjr.tw |
+----+------+---------+----------+--------+---------------+
5 rows in set (0.001 sec)
下面幾個函數通常搭配 GROUP BY 來使用.
- AVG
計算數字平均.MariaDB [testdb]> SELECT AVG(Salary) FROM employee; +-------------+ | AVG(Salary) | +-------------+ | 59600.0000 | +-------------+ 1 row in set (0.001 sec)
MariaDB [testdb]> SELECT Dept , AVG(Salary) FROM employee GROUP BY Dept; +---------+-------------+ | Dept | AVG(Salary) | +---------+-------------+ | PM | 85000.0000 | | Power | 48000.0000 | | Testing | 55000.0000 | +---------+-------------+ 3 rows in set (0.001 sec)
- COUNT
回傳 SELECT 的筆數.COUNT(expression)
利用 COUNT(*) 來看回傳的資料筆數.
MariaDB [testdb]> SELECT GROUP_CONCAT(Name SEPARATOR ',') AS "Member Name" , COUNT(*) COUNT FROM employee; +---------------------------+-------+ | Member Name | COUNT | +---------------------------+-------+ | Ben,Afa,Boss,JoJo,Ian,Mat | 6 | +---------------------------+-------+ 1 row in set (0.000 sec)
可以利用 COUNT(DISTINCT(column)) 或是 COUNT(DISTINCT column) 來計算不重覆的欄位有幾個.
MariaDB [testdb]> SELECT GROUP_CONCAT(DISTINCT Dept SEPARATOR ',') AS "Dept Name" , COUNT(DISTINCT(Dept)) AS Dept FROM employee; +------------------+------+ | Dept Name | Dept | +------------------+------+ | Testing,Power,PM | 3 | +------------------+------+ 1 row in set (0.001 sec)
COUNT 還可以搭配 IF 來使用 COUNT(IF(express,TRUE,NULL))
MariaDB [testdb]> SELECT COUNT(IF( Salary > 50000,TRUE,NULL)) AS Dept FROM employee; +------+ | Dept | +------+ | 2 | +------+ 1 row in set (0.002 sec)
- GROUP_CONCAT
通常搭配 GROUP BY 使用,透過 GROUP_CONCAT 函數,可以把同 Group 的欄位文字整合在一起.GROUP_CONCAT([DISTINCT] expr [,expr ...] [ORDER BY {unsigned_integer | col_name | expr} [ASC | DESC] [,col_name ...]] [SEPARATOR str_val])MariaDB [testdb]> SELECT Dept, GROUP_CONCAT(Name SEPARATOR ',') AS "Member Name" FROM employee GROUP BY Dept; +---------+---------------+ | Dept | Member Name | +---------+---------------+ | PM | Ian | | Power | Afa | | Testing | Ben,Boss,JoJo | +---------+---------------+ 3 rows in set (0.001 sec)
如果 GROUPBY 的資料要按照順序,可以加 ORDER BY DESC 或是 ASC
MariaDB [testdb]> SELECT Dept, GROUP_CONCAT(Name ORDER BY Name DESC SEPARATOR ',') AS "Member Name" FROM employee GROUP BY Dept; +---------+---------------+ | Dept | Member Name | +---------+---------------+ | PM | Ian | | Power | Afa | | Testing | JoJo,Boss,Ben | +---------+---------------+ 3 rows in set (0.001 sec) MariaDB [testdb]> SELECT Dept, GROUP_CONCAT(Name ORDER BY Name ASC SEPARATOR ',') AS "Member Name" FROM employee GROUP BY Dept; +---------+---------------+ | Dept | Member Name | +---------+---------------+ | PM | Ian | | Power | Afa | | Testing | Ben,Boss,JoJo | +---------+---------------+ 3 rows in set (0.001 sec)
- MAX , MIN
MAX() 函數會返回數值中最大的.MariaDB [testdb]> SELECT MAX(Salary) FROM employee; +-------------+ | MAX(Salary) | +-------------+ | 85000 | +-------------+ 1 row in set (0.001 sec) MariaDB [testdb]> SELECT Name , Dept , Salary FROM employee WHERE Salary = (SELECT MAX(Salary) FROM employee); +------+------+--------+ | Name | Dept | Salary | +------+------+--------+ | Ian | PM | 85000 | +------+------+--------+ 1 row in set (0.003 sec)
MIN() 函數會返回數值中最小的.
MariaDB [testdb]> SELECT MIN(Salary) FROM employee; +-------------+ | MIN(Salary) | +-------------+ | 45000 | +-------------+ 1 row in set (0.001 sec) MariaDB [testdb]> SELECT Name , Dept , Salary FROM employee WHERE Salary = (SELECT MIN(Salary) FROM employee); +------+---------+--------+ | Name | Dept | Salary | +------+---------+--------+ | Ben | Testing | 45000 | | JoJo | Testing | 45000 | +------+---------+--------+ 2 rows in set (0.001 sec)
- SUM
計算數字加總.MariaDB [testdb]> SELECT SUM(Salary) FROM employee; +-------------+ | SUM(Salary) | +-------------+ | 298000 | +-------------+ 1 row in set (0.001 sec)
通常搭配 GROUP BY 使用,可以依據指定欄位將數字欄位把數字加總,還可以搭背 WITH ROLLUP 可以把分類的資料做個總結(數字部分),詳細範例請參考 – https://benjr.tw/98988 (GROUP BY , HAVING , ORDER BY 以及 LIMIT).
MariaDB [testdb]> SELECT Dept , SUM(Salary) FROM employee GROUP BY Dept; +---------+-------------+ | Dept | SUM(Salary) | +---------+-------------+ | PM | 85000 | | Power | 48000 | | Testing | 165000 | +---------+-------------+ 3 rows in set (0.002 sec)
沒有解決問題,試試搜尋本站其他內容