メモ③-関数を用いたSELECT文ほか-

SELECT文いろいろ

①給与平均を求める

データベースをcompanyに切り替え…
MariaDB [world]> use company;
Database changed

以下の形で入力する
MariaDB [company]> SELECT
    -> AVG(basic_salary)
    -> AS "給与平均"
    -> FROM tbl_employee;
+-------------+
| 給与平均    |
+-------------+
| 239800.0000 |
+-------------+
1 row in set (0.003 sec)

②人件費の合計を求める
MariaDB [company]> SELECT SUM(basic_salary) FROM tbl_employee;
+-------------------+
| SUM(basic_salary) |
+-------------------+
|           1199000 |
+-------------------+
1 row in set (0.000 sec)

③最小値を求める
MariaDB [company]> SELECT MIN(basic_salary) FROM tbl_employee;
+-------------------+
| MIN(basic_salary) |
+-------------------+
|            200000 |
+-------------------+
1 row in set (0.001 sec)

④収録されているデータの数(行数)を数える
MariaDB [company]> SELECT COUNT(*) FROM tbl_employee;
+----------+
| COUNT(*) |
+----------+
|        5 |
+----------+
1 row in set (0.000 sec)

⑤④では、NULL値が入ったデータ(post_codeの中にNULLがある)を含んでいるので、それを除外するには、
MariaDB [company]> SELECT COUNT(post_code) FROM tbl_employee;
+------------------+
| COUNT(post_code) |
+------------------+
|                3 |
+------------------+
1 row in set (0.000 sec)

⑥ようわからん例文
MariaDB [company]> SELECT name,basic_salary,MAX(basic_salary)-basic_salary FROM tbl_employee;
+----------+--------------+--------------------------------+
| name     | basic_salary | MAX(basic_salary)-basic_salary |
+----------+--------------+--------------------------------+
| 伊藤英樹 |       200000 |                          90000 |
+----------+--------------+--------------------------------+
1 row in set (0.001 sec)


<データやテーブルとは全く関係のない情報を参照する>
①円周率
MariaDB [company]> SELECT PI();
+----------+
| PI()     |
+----------+
| 3.141593 |
+----------+
1 row in set (0.001 sec)

②SQLサーバーのバージョンを表示
MariaDB [company]> SELECT VERSION();
+----------------+
| VERSION()      |
+----------------+
| 10.5.0-MariaDB |
+----------------+
1 row in set (0.000 sec)

③現在使用しているDBの表示
MariaDB [company]> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| company    |
+------------+
1 row in set (0.000 sec)

④現在ログインしているユーザーの表示
MariaDB [company]> SELECT USER();
+----------------+
| USER()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.001 sec)

⑤()内に入力した文字は何の文字コードを使っているのか検索・表示できる
MariaDB [company]> SELECT CHARSET("もじもじ");
+---------------------+
| CHARSET("もじもじ") |
+---------------------+
| cp932               |←cp932はSHIFT-JISを表す
+---------------------+
1 row in set (0.001 sec)


<文字列に関する関数>
①2つの項目を結合し、同じ枠内に表示する
MariaDB [company]> SELECT CONCAT(name, blood) FROM tbl_employee;
+---------------------+
| CONCAT(name, blood) |
+---------------------+
| 伊藤英樹A           |
| 山本大樹AB          |
| 中村千佳B           |
| 小林谷雄O           |
| 斎藤美緒A           |
+---------------------+
5 rows in set (0.001 sec)

②右から何番目の文字までを表示する
MariaDB [company]> SELECT RIGHT(name,3) FROM tbl_employee;
+---------------+
| RIGHT(name,3) |←右から3番目を指定している
+---------------+
| 藤英樹        |
| 本大樹        |
| 村千佳        |
| 林谷雄        |
| 藤美緒        |
+---------------+
5 rows in set (0.001 sec)

③左から何番目の文字までを表示する
MariaDB [company]> SELECT LEFT(name,3) FROM tbl_employee;
+--------------+
| LEFT(name,3) |
+--------------+
| 伊藤英       |
| 山本大       |
| 中村千       |
| 小林谷       |
| 斎藤美       |
+--------------+
5 rows in set (0.000 sec)

④n番目からr個の文字を取り出す
MariaDB [company]> SELECT SUBSTRING(name,3,1) FROM tbl_employee;
+---------------------+
| SUBSTRING(name,3,1) |
+---------------------+
| 英                  |
| 大                  |
| 千                  |
| 谷                  |
| 美                  |
+---------------------+
5 rows in set (0.001 sec)

⑤逆順に表示
MariaDB [company]> SELECT REVERSE(name) FROM tbl_employee WHERE code =101;
+---------------+
| REVERSE(name) |
+---------------+
| 樹英藤伊      |
+---------------+
1 row in set (0.000 sec)

<グループ化>
①部門ごとに給与平均を出す

MariaDB [company]> SELECT code, dpt_code, AVG(basic_salary)
    -> FROM
    -> tbl_employee
    -> GROUP BY			
    -> dpt_code;	←ここでグループ分けの基準をdpt_code(部門コード)に指定している
+------+----------+-------------------+
| code | dpt_code | AVG(basic_salary) |
+------+----------+-------------------+
|  101 |       10 |       217500.0000 |
|  102 |       20 |       250000.0000 |
|  104 |       30 |       257000.0000 |
+------+----------+-------------------+
3 rows in set (0.001 sec)

MariaDB [company]> SELECT code,dpt_code,basic_salary
    -> FROM
    -> tbl_employee;
+------+----------+--------------+
| code | dpt_code | basic_salary |
+------+----------+--------------+
|  101 |       10 |       200000 |
|  102 |       20 |       250000 |
|  103 |       10 |       235000 |
|  104 |       30 |       224000 |
|  105 |       30 |       290000 |
+------+----------+--------------+
5 rows in set (0.000 sec)

②worldデータベースのcityテーブルから、国の人口が1億人以上の国を抽出する
MariaDB [world]> SELECT
    ->     CountryCode,
    ->     SUM(Population)
    -> FROM
    ->     city
    -> GROUP BY
    ->     CountryCode
    -> HAVING SUM(Population) >= 100000000
    -> ORDER BY SUM(Population) DESC;
+-------------+-----------------+
| CountryCode | SUM(Population) |
+-------------+-----------------+
| CHN         |       175953614 |
| IND         |       123298526 |
+-------------+-----------------+
2 rows in set (0.006 sec)


<CASE式で条件分岐を作る>
①部署コードを部署名に変換して表示する

MariaDB [company]> SELECT
    -> CASE dpt_code
    -> WHEN 10 THEN "総務部"	←部署コードが10なら総務部と表示
    -> WHEN 20 THEN "営業部"	←部署コードが20なら営業部と
    -> ELSE "開発部"		←それ以外なら開発部と
    -> END AS post_name
    -> FROM
    -> tbl_employee;
+-----------+
| post_name |
+-----------+
| 総務部    |
| 総務部    |
| 営業部    |
| 開発部    |
| 開発部    |
+-----------+
5 rows in set (0.001 sec)

上記の書き方はJavaでいうSwitch文に近い書き方。
単純CASE式とも呼ばれる。

(if文に近い書き方)
MariaDB [company]> SELECT
    -> CASE
    -> WHEN dpt_code = 10 THEN "総務部"
    -> WHEN dpt_code = 20 THEN "営業部"
    -> ELSE "開発部"
    -> END AS post_name
    -> FROM tbl_employee;
+-----------+
| post_name |
+-----------+
| 総務部    |
| 総務部    |
| 営業部    |
| 開発部    |
| 開発部    |
+-----------+
5 rows in set (0.000 sec)

②worldDBのcityテーブルを100行取得し、
　人口ごとに都市の規模を振り分け、一覧表示する
MariaDB [world]> SELECT
    -> Name AS "都市名",
    -> CASE
    -> WHEN Population >= 1000000 THEN "大都市"
    -> WHEN Population >= 100000 THEN "都市"
    -> WHEN Population >= 30000 THEN "市"
    -> ELSE "町"
    -> END AS "都市の規模"
    -> FROM
    -> city LIMIT 100;

・・・④へ続く