2017年3月10日金曜日

MySQL 集計関数

MySQL で利用できる集計関数の一覧です。

関数機能
AVG平均値を計算する
COUNTレコード数を計算する
MAX最大値を調べる
MIN最小値を調べる
SUM合計値を計算する


下記テーブルを例に、これら集計関数を利用してみましょう。

SELECT * FROM person;
+----+-------+------+--------+---------+
| id | name  | age  | gender | dept_no |
+----+-------+------+--------+---------+
|  1 | kai   |   27 |      1 |       1 |
|  2 | gerda |   25 |      2 |       2 |
|  3 | ragi  |   33 |      1 |       1 |
+----+-------+------+--------+---------+


AVG 関数
AVG 関数で平均年齢を求めます。

SELECT AVG(age) FROM person;
+----------+
| AVG(age) |
+----------+
|  28.3333 |
+----------+


COUNT 関数
COUNT 関数で、person テーブルのレコード数を求めます。

SELECT COUNT(*) FROM person;
+----------+
| COUNT(*) |
+----------+
|        3 |
+----------+


MAX 関数
MAX 関数で一番年上を求めます。

SELECT MAX(age) FROM person;
+----------+
| MAX(age) |
+----------+
|       33 |
+----------+


MIN 関数
MIN 関数で一番年下を求めます。

SELECT MIN(age) FROM person;
+----------+
| MIN(age) |
+----------+
|       25 |
+----------+


SUM 関数
今回のテーブルではあまり意味はないかもしれませんが、SUM 関数で全員の延べ年齢を求めてみます。

SELECT SUM(age) FROM person;
+----------+
| SUM(age) |
+----------+
|       85 |


リファレンス
12.19.1 GROUP BY (集約) 関数

2017年3月9日木曜日

MySQL GROUP BY 句によるグループ化

person テーブルと gender テーブルのデータを確認します。

SELECT * FROM person;
+----+-------+------+--------+---------+
| id | name  | age  | gender | dept_no |
+----+-------+------+--------+---------+
|  1 | kai   |   27 |      1 |       1 |
|  2 | gerda |   25 |      2 |       2 |
|  3 | ragi  |   33 |      1 |       1 |
+----+-------+------+--------+---------+


SELECT * FROM gender;
+----+-------+
| id | label |
+----+-------+
|  0 | 不明  |
|  1 | 男性  |
|  2 | 女性  |
+----+-------+


それでは GROUP BY 句を利用して person テーブルの性別ごとにグループ化を行います。グループ化を行い、更に COUNT 関数を利用してグループごとのレコード数を集計します。

SELECT g.label AS '性別',  COUNT(p.name) AS '人数' FROM person AS p, gender AS g 
WHERE g.id = p.gender GROUP BY p.gender;
+------+------+
| 性別 | 人数 |
+------+------+
| 男性 |    2 |
| 女性 |    1 |
+------+------+

※ GROUP BY 句を利用する際には、「グループ化を行う列」と「集計関数(ここでは COUNT 関数)の対象列」のみ選択(SELECT)の対象とすることができます。

2017年3月8日水曜日

MySQL AS 句によりテーブルに別名を付ける

まずはテーブルの構成を確認します。今回も person テーブルと department テーブルを利用します。

MariaDB [test]> SELECT * FROM person;
+----+-------+------+--------+---------+
| id | name  | age  | gender | dept_no |
+----+-------+------+--------+---------+
|  1 | kai   |   27 |      1 |       1 |
|  2 | gerda |   25 |      2 |       2 |
|  3 | ragi  |   33 |      1 |       1 |
+----+-------+------+--------+---------+
3 rows in set (0.00 sec)

MariaDB [test]> SELECT * FROM department;
+----+----------------+
| no | name           |
+----+----------------+
|  1 | sales          |
|  2 | human resource |
+----+----------------+
2 rows in set (0.00 sec)


person テーブルは p、department は d という別名を付けています。一度別名を付けると、後続の WHERE 句では p、d と短い別名を使うことで簡潔な記述になっています。

MariaDB [test]> SELECT p.name, d.name from person AS p, department AS d 
WHERE p.dept_no = d.no;
+-------+----------------+
| name  | name           |
+-------+----------------+
| kai   | sales          |
| gerda | human resource |
| ragi  | sales          |
+-------+----------------+
3 rows in set (0.00 sec)

2017年3月7日火曜日

MySQL AS 句により列に別名を付ける

「MySQL SELECT 文」では下記のようにテーブルを結合して name 列を表示しました。

MariaDB [test]> SELECT person.name, department.name 
FROM person, department 
WHERE person.dept_no = department.no;
+-------+-------------------+
| name  | name                |
+-------+-------------------+
| kai      | sales                 |
| gerda  | human resource |
| ragi     | sales                 |
+-------+-------------------+

→ 左の name 列は person テーブルから、右の name 列は department テーブルから取得されています。このままでは分かりづらいのでそれぞれの列に別名を付けます。


AS 句を使って列に別名をつける

MariaDB [test]> SELECT person.name AS 'person name', department.name  AS 'department name' 
FROM person, department 
WHERE person.dept_no = department.no;
+-------------+-----------------------+
| person name | department name |
+-------------+-----------------------+
| kai                | sales                   |
| gerda            | human resource   |
| ragi               | sales                   |
+-------------+-----------------------+

→ それぞれの列がどのような意味を持つのかがひと目でわかるようになりました。

2017年3月6日月曜日

MySQL SELECT 文

person テーブルと department テーブルを結合してみましょう。

まずはそれぞれのテーブルの定義とレコードの確認です。


person テーブル

MariaDB [test]> desc person;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(11)     | NO   | PRI | NULL    |       |
| name    | varchar(30) | YES  |     | NULL    |       |
| age     | int(11)     | YES  |     | NULL    |       |
| gender  | smallint(2) | YES  |     | NULL    |       |
| dept_no | int(11)     | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+


MariaDB [test]> select * from person;
+----+-------+------+--------+---------+
| id | name  | age  | gender | dept_no |
+----+-------+------+--------+---------+
|  1 | kai   |   27 |      1 |       1 |
|  2 | gerda |   25 |      2 |       2 |
|  3 | ragi  |   33 |      1 |       1 |
+----+-------+------+--------+---------+


department テーブル

MariaDB [test]> desc department;
+-------+-------------+------+-----+---------+-------+
| Field   | Type            | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| no      | int(11)         | NO   | PRI | NULL    |         |
| name  | varchar(30) | YES  |       | NULL    |         |
+-------+-------------+------+-----+---------+-------+


MariaDB [test]> select * from department;
+----+-------------------+
| no | name                |
+----+-------------------+
|  1 | sales                  |
|  2 | human resource  |
+----+-------------------+


まずは person テーブルと department テーブルの全列を表示してみましょう。
WHERE 句により、person テーブルの dept_no 列と department テーブルの no 列を結合しています。

MariaDB [test]> SELECT * FROM person, department
WHERE person.dept_no = department.no;
+----+-------+------+--------+---------+----+-------------------+
| id | name  | age  | gender | dept_no | no | name                |
+----+-------+------+--------+---------+----+-------------------+
|  1 | kai     |   27 |          1 |           1 |   1 | sales                 |
|  2 | gerda |   25 |          2 |           2 |   2 | human resource |
|  3 | ragi    |   33 |          1 |           1 |   1 | sales                 |
+----+-------+------+--------+---------+----+-------------------+


続いて、表示する列を絞ってみましょう。person テーブル、department テーブルの name 列のみを表示してみます。

MariaDB [test]> SELECT person.name, department.name 
FROM person, department 
WHERE person.dept_no = department.no;
+-------+-------------------+
| name  | name                |
+-------+-------------------+
| kai      | sales                 |
| gerda  | human resource |
| ragi     | sales                 |
+-------+-------------------+

2017年3月5日日曜日

SQL 基本コマンド一覧

MySQL で検証していますが、標準 SQL の範囲だと思います。


データベースの一覧表示
SHOW DATABASES;

データベースの選択
USE test;

テーブルの一覧表示
SHOW TABLES;

テーブルの作成
CREATE TABLE person(id int not null primary key, name nvarchar(30), age int, gender smallint(2));

テーブルの構造表示
DESC person;

テーブルの削除
DROP TABLE person;

行の挿入(単一行)
INSERT INTO 
person(id, name, age, gender) 
VALUES (1, 'kai', 27, 1);

行の挿入(複数行)
INSERT INTO 
person(id, name, age, gender) 
VALUES (2, 'gerda', 25, 2), (3, 'ragi', 33, 1);

テーブルに列を追加
ALTER TABLE person ADD dept_no int;

行の更新
UPDATE person SET dept_no = 1 WHERE name = 'kai';

行の選択1
--person テーブルの dept_no 列と、department テーブルの no 列を結合。両方のテーブルの全列を選択。
SELECT * 
FROM person, department 
WHERE person.dept_no = department.no;

行の選択2
--person テーブルの dept_no 列と、department テーブルの no 列を結合。
--person テーブルの name 列と department テーブルの name 列を選択。
SELECT person.name, department.name 
FROM person, department 
WHERE person.dept_no = department.no;

2017年3月4日土曜日

Visual Studio Code のデバッグがとても便利

Visual Studio Code のデバッグですが、Visual Studio ゆずりでとっても便利ですね。

Superglobals の変数をウォッチ式に入れるのも Visual Studio 同様簡単です。



Visual Studio Code で XAMPP Lite と組み合わせたデバッグ環境

Visual Studio Code をインストール後、XAMPP Lite の php.ini に下記を追記。
※ XAMPP は D フォルダ配下にインストールしています。


D:\xampp\php\php.ini
[XDebug]
zend_extension="D:\XAMPP\php\ext\php_xdebug.dll"
;xdebug.remote_enable=On
xdebug.remote_enable=1
xdebug.remote_autostart=1
xdebug.remote_host="localhost"
xdebug.remote_port=9000
xdebug.remote_handler=dbgp


D:\xampp\htdocs\.vscode\launch.json
{
    "version": "0.2.0",
    "configurations": [
        {
            "name": "Listen for XDebug",
            "type": "php",
            "request": "launch",
            "port": 9000
        },
        {
            "name": "Launch currently open script",
            "type": "php",
            "request": "launch",
            "program": "${file}",
            "cwd": "${fileDirname}",
            "port": 9000
        }
    ]
}

XAMPP Lite の Apache 設定(IIS との共存)


IIS をインストールしている環境だと localhost:80 は先に使われているので、XAMPP には localhost:8080 を利用するように変更します。下記手順です。

1.httpd.config の編集
























80 を 8080 に変更

























2.httpd-ssl.config の編集
httpd.config と同様に、443 を 4499 に変更



3.XAMPP Control Panel の設定変更

XAMPP Control Panel の「Admin」ボタンクリックで表示するページの URL も 8080, 4499 ポートに対応するための設定変更です。















※追記
httpd.config と httpd-ssl.config にある Listen ポートやサーバーのポート指定も 80 → 8080, 443 → 4499 に全て変更する必要があります。

2017年2月23日木曜日

SQL ループ文でインクリメント


ループして複数行を挿入する SQL 文を書いてみました。

テーブルはこんな構成です。







register 列は DateTime 型のデータです。ループの中では変数の @index を利用して
分(MINUTE)の部分をインクリメントして追加しています。

DECLARE @index int;
SET @index = 1;
WHILE @index < 10
BEGIN
DECLARE @toDate DATETIME = DATEADD(DAY, -1, GETDATE())
INSERT [Test1].[dbo].[TestTable] (id, name, register)
VALUES(
@index,
'user' + LTRIM(STR(@index)),
(SELECT DATEADD(MINUTE, @index, @toDate))
);
SET @index = @index + 1
END

実行すると行が複数挿入されており、分の部分が1ずつ増加しています。



2017年2月12日日曜日

ASP.NET MVC ビューにクラスインスタンスを渡す際のエラー(InvalidOperationException)

2つのモデルを結合したモデルを表示しようとしたところ、こんなエラーが出ました。

--------エラーここから--------

'/' アプリケーションでサーバー エラーが発生しました。

ディクショナリに型 'System.Collections.Generic.List`1[WordLearner.Models.WordDetail]' のモデル項目が渡されましたが、このディクショナリには型 'WordLearner.Models.WordDetail' のモデル項目が必要です。

説明: 現在の Web 要求を実行中に、ハンドルされていない例外が発生しました。エラーに関する詳細および例外の発生場所については、スタック トレースを参照してください。

例外の詳細: System.InvalidOperationException: ディクショナリに型 'System.Collections.Generic.List`1[WordLearner.Models.WordDetail]' のモデル項目が渡されましたが、このディクショナリには型 'WordLearner.Models.WordDetail' のモデル項目が必要です。

--------エラーここまで--------

調べてみると同じエラーで困っている人がいました。
Unable to cast object of type 'System.Data.Entity.Infrastructure.DbQuery`1[]' using linq lambda expression
http://stackoverflow.com/a/22918838

そして回答も掲載されていました。今回検索結果のオブジェクトをビューに返すところを、クエリの状態でビューに渡しているのが原因でした💨
変数 query に FirstOrDefault() メソッドでオブジェクトを取得することで解決しました。

public ActionResult Test(int? id)
{
  if (id == null)
  {
      return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
  }
  Word word = db.Word.Find(id);
  if (word == null)
  {
      return HttpNotFound();
  }

  int _id = id.Value;
  var meaning = db.Meanings;
  var query = from x in db.Word
            join y in meaning on x.ID equals y.WordID
            where x.ID.Equals(_id)
            select new WordDetail
            {
                ID = x.ID,
                Spelling = x.Spelling,
                Meaning = y.Meaning
            };

  return View("Details", query.FirstOrDefault());
}

2017年2月11日土曜日

C# Linq によるオブジェクトの結合

C# Linq を使って、2つのオブジェクトを結合した結果を取得します。

データベースのテーブルはこうなっています。










private WordLearnerDataContext db = new WordLearnerDataContext();

public ActionResult Index()
{
    var words = db.Words;
    var meanings = db.Meanings;

    var query = from x in words
                join y in meanings on x.ID equals y.WordID
                select new { ID = x.ID, Word = x.Word, Meaning = y.Meaning };

    foreach (var item in query)
    {
        Debug.WriteLine("{0}: {1} = {2}", item.ID, item.Word, item.Meaning);
    }

    return View(query.AsQueryable());
}


実行結果

901: deposit = (お金を)預ける
902: evolve         = 進化する

2017年2月5日日曜日

SQL CASE 式の使い方

CASE 式を使って条件に応じた値を出力してみます。

select 
社員コード, 氏名, 在籍支社,/* テーブルに既存の列 */
case 在籍支社
 when '東京本社' then '関東'
 when '大阪支社' then '関西'
 else 'その他'
end as '在籍地域' /*CASE 式で取得した列の名前*/
 from 社員


実行結果