[php] select 技巧

聚合函數的應用：

計算資料表 score 中共有多少筆資料：

mysql> SELECT COUNT(*) FROM score;

+----------+

| COUNT(*) |

+----------+

|       34 |

+----------+

1 row in set (0.00 sec)



計算資料表 score 中第一項成績 score1 及格者共有多少人：

mysql> SELECT COUNT(*) FROM score WHERE score1>=60;

+----------+

| COUNT(*) |

+----------+

|       14 |

+----------+

1 row in set (0.00 sec)



計算資料表 score 中，陳姓同學共有多少位：

mysql> SELECT COUNT(*) FROM score WHERE sname like "陳%";

+----------+

| COUNT(*) |

+----------+

|        4 |

+----------+

1 row in set (0.00 sec)



列出資料表 score 中，所有姓陳的同學的學號與姓名：(注意與上述指令的差別！)

mysql> SELECT sno, sname FROM score WHERE sname like "陳%";

+----------+--------+

| sno      | sname  |

+----------+--------+

| 49313008 | 陳仕程 |

| 49313022 | 陳志彥 |

| 49313045 | 陳紹文 |

| 49313060 | 陳聖元 |

+----------+--------+

4 rows in set (0.00 sec)



計算資料表 score 中，第一項成績 score1 所有同學的平均值：

mysql> SELECT AVG(score1) FROM score;

+-------------+

| AVG(score1) |

+-------------+

|     50.4412 |

+-------------+

1 row in set (0.00 sec)



計算資料表 score 中，第一項成績 score1 陳姓同學們的平均值：

mysql> SELECT AVG(score1) FROM score WHERE sname like "陳%";

+-------------+

| AVG(score1) |

+-------------+

|     29.5000 |

+-------------+

1 row in set (0.00 sec)



計算資料表 score 中，第一項成績 score1 的最高分數：

mysql> SELECT MAX(score1) FROM score;

+-------------+

| MAX(score1) |

+-------------+

|          92 |

+-------------+

1 row in set (0.02 sec)



計算資料表 score 中，第一項成績 score1 的最高分數，並暫存於變數 @maxScore 中：

mysql> SELECT @maxScore := MAX(score1) FROM score;

+--------------------------+

| @maxScore := MAX(score1) |

+--------------------------+

|                       92 |

+--------------------------+

1 row in set (0.00 sec)



利用暫存變數 @maxScore 來列出 score1 成績的最高同學的資料： 

mysql> SELECT *  FROM score WHERE score1 = @maxScore;

+----------+--------+--------+--------+--------+--------+--------+

| sno      | sname  | score1 | score2 | score3 | score4 | score5 |

+----------+--------+--------+--------+--------+--------+--------+

| 49313009 | 彭立瑋 |     92 |     88 |     17 |     48 |     57 |

| 49313056 | 涂柏恩 |     92 |     18 |     47 |     76 |     21 |

+----------+--------+--------+--------+--------+--------+--------+

2 rows in set (0.00 sec)



分別計算資料表 score 中，學號 49313003 同學的五科成績總和與平均： 

mysql> SELECT (score1+score2+score3+score4+score5)  FROM score WHERE sno="49313003";

+--------------------------------------+

| (score1+score2+score3+score4+score5) |

+--------------------------------------+

|                                  261 |

+--------------------------------------+

1 row in set (0.00 sec)



mysql> SELECT (score1+score2+score3+score4+score5)/5 FROM score WHERE sno="49313003";

+----------------------------------------+

| (score1+score2+score3+score4+score5)/5 |

+----------------------------------------+

|                                  52.20 |

+----------------------------------------+

1 row in set (0.00 sec)



mysql> SELECT (score1+score2+score3+score4+score5)/5 AS "總平均" FROM score WHERE sno="49313003";

+--------+

| 總平均  |

+--------+

|  52.20 |

+--------+

1 row in set (0.00 sec)



資料表 student 中，利用做 COUNT 血型的統計：

mysql> SELECT bloodType, COUNT(*)  FROM student GROUP BY bloodType;

+-----------+----------+

| bloodType | COUNT(*) |

+-----------+----------+

| NULL      |        7 |

| A         |       10 |

| AB        |        3 |

| B         |        5 |

| O         |       11 |

+-----------+----------+

5 rows in set (0.00 sec)

多個表格資料的合併：

結合兩個表格共有記錄的所有內容：

mysql> SELECT * FROM student INNER JOIN score ON student.sno = score.sno;

+----------+---------+------------------+--------------+------+-----------+-------------------------------+------------+--------------------------------------+----------+--------+--------+--------+--------+--------+--------+

| sno      | sname   | graduated        | password     | sex  | bloodType | email                         | mphone     | address                              | sno      | sname  | score1 | score2 | score3 | score4 | score5 |

+----------+---------+------------------+--------------+------+-----------+-------------------------------+------------+--------------------------------------+----------+--------+--------+--------+--------+--------+--------+

| 49313003 | 吳峻孝  | 東海高中         | DBJl1R6GJq3v | 男   | A         | [email protected]        | 0921xxxxxx | 北縣三重市                           | 49313003 | 吳峻孝 |     44 |     20 |     50 |     51 |     96 |

| 49313006 | 李呈宏  | 智光             | DBoU7cildXdJ | 男   | A         | [email protected]        | 0919xxxxxx | 永和市                               | 49313006 | 李呈宏 |     58 |     37 |     21 |     73 |     75 |

| 49313007 | 許舜翔  | 智光商工         |......



底下資料略去！



結合兩個表格共有記錄的部分指定內容：

mysql> SELECT student.sno, score.sname, email, score1 FROM student INNER JOIN score ON student.sno = score.sno;

+----------+--------+-------------------------------+--------+

| sno      | sname  | email                         | score1 |

+----------+--------+-------------------------------+--------+

| 49313003 | 吳峻孝 | [email protected]        |     44 |

| 49313006 | 李呈宏 | [email protected]        |     58 |

| 49313007 | 許舜翔 | [email protected]         |     88 |

| 49313008 | 陳仕程 | [email protected]           |      5 |

| 49313009 | 彭立瑋 | [email protected]     |     92 |

| 49313010 | 楊馨翔 | [email protected]       |     74 |

| 49313011 | 顏旭邦 | [email protected]        |     56 |

| 49313012 | 詹陳偉 | [email protected] |     75 |

| 49313016 | 許珈愷 | NULL                          |     83 |

| 49313018 | 李和翔 | [email protected]      |     12 |

| 49313020 | 湯文昌 | [email protected]          |      0 |

| 49313022 | 陳志彥 | NULL                          |     38 |

| 49313028 | 許舜傑 | [email protected]          |     49 |

| 49313031 | 吳信漢 | [email protected]         |     48 |

| 49313033 | 羅浩祐 | [email protected]       |     86 |

| 49313038 | 石綿倫 | [email protected]             |      2 |

| 49313042 | 莊國佑 | [email protected]      |     88 |

| 49313045 | 陳紹文 | [email protected]    |     46 |

| 49313048 | 黃建銘 | NULL                          |     41 |

| 49313049 | 朱引榮 | [email protected] |     22 |

| 49313051 | 楊凡   | [email protected]          |     17 |

| 49313053 | 劉家丞 | [email protected]      |     81 |

| 49313054 | 高勝興 | [email protected]         |     31 |

| 49313055 | 黃韋傑 | [email protected]          |      4 |

| 49313056 | 涂柏恩 | [email protected]      |     92 |

| 49313057 | 謝昇融 | [email protected]      |     61 |

| 49313058 | 李志遠 | [email protected]          |     86 |

| 49313059 | 呂亦晟 | [email protected]       |     47 |

| 49313060 | 陳聖元 | [email protected]        |     29 |

| 49213002 | 王聖賀 | NULL                          |      4 |

| 49213017 | 張舜凱 | [email protected]        |     25 |

| 49213022 | 張景翔 | NULL                          |     70 |

| 49213023 | 曾聖鈞 | NULL                          |     84 |

| 49213031 | 潘聖棟 | NULL                          |     77 |

+----------+--------+-------------------------------+--------+

34 rows in set (0.00 sec)

從多個表格查詢並顯示資料：

學號查詢條件分別符合：

mysql> SELECT student.sno, score.sname, email, score1 FROM student, score WHERE student.sno="49313003" AND score.sno="49313003";

+----------+--------+------------------------+--------+

| sno      | sname  | email                  | score1 |

+----------+--------+------------------------+--------+

| 49313003 | 吳峻孝 | [email protected] |     44 |

+----------+--------+------------------------+--------+

1 row in set (0.00 sec)



資料表 student 中的每一個學號會與 score 中的每個學號比對是否符合：

mysql> SELECT student.sno, score.sname, email, score1 FROM student, score WHERE student.sno = score.sno;

+----------+--------+-------------------------------+--------+

| sno      | sname  | email                         | score1 |

+----------+--------+-------------------------------+--------+

| 49313003 | 吳峻孝 | [email protected]        |     44 |

| 49313006 | 李呈宏 | [email protected]        |     58 |

| 49313007 | 許舜翔 | [email protected]         |     88 |

| 49313008 | 陳仕程 | [email protected]           |      5 |

| 49313009 | 彭立瑋 | [email protected]     |     92 |

| 49313010 | 楊馨翔 | [email protected]       |     74 |

| 49313011 | 顏旭邦 | [email protected]        |     56 |

| 49313012 | 詹陳偉 | [email protected] |     75 |

| 49313016 | 許珈愷 | NULL                          |     83 |

| 49313018 | 李和翔 | [email protected]      |     12 |

| 49313020 | 湯文昌 | [email protected]          |      0 |

| 49313022 | 陳志彥 | NULL                          |     38 |

| 49313028 | 許舜傑 | [email protected]          |     49 |

| 49313031 | 吳信漢 | [email protected]         |     48 |

| 49313033 | 羅浩祐 | [email protected]       |     86 |

| 49313038 | 石綿倫 | [email protected]             |      2 |

| 49313042 | 莊國佑 | [email protected]      |     88 |

| 49313045 | 陳紹文 | [email protected]    |     46 |

| 49313048 | 黃建銘 | NULL                          |     41 |

| 49313049 | 朱引榮 | [email protected] |     22 |

| 49313051 | 楊凡   | [email protected]          |     17 |

| 49313053 | 劉家丞 | [email protected]      |     81 |

| 49313054 | 高勝興 | [email protected]         |     31 |

| 49313055 | 黃韋傑 | [email protected]          |      4 |

| 49313056 | 涂柏恩 | [email protected]      |     92 |

| 49313057 | 謝昇融 | [email protected]      |     61 |

| 49313058 | 李志遠 | [email protected]          |     86 |

| 49313059 | 呂亦晟 | [email protected]       |     47 |

| 49313060 | 陳聖元 | [email protected]        |     29 |

| 49213002 | 王聖賀 | NULL                          |      4 |

| 49213017 | 張舜凱 | [email protected]        |     25 |

| 49213022 | 張景翔 | NULL                          |     70 |

| 49213023 | 曾聖鈞 | NULL                          |     84 |

| 49213031 | 潘聖棟 | NULL                          |     77 |

+----------+--------+-------------------------------+--------+

34 rows in set (0.00 sec)



http://140.129.118.16/~richwang/Database/DB-2006-1228.html