メモ③-関数を用いた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)

①部署コードを部署名に変換して表示する

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;

・・・④へ続く