kainobi2: MySQL

ラベル MySQL の投稿を表示しています。すべての投稿を表示

2017年9月15日金曜日

XAMPP MySQL の日本語文字化け対応

データベース作成時に、照合順序として utf8_general_ci を選択すると、日本語を文字化けすることなく扱うことができるようになります。

2017年9月14日木曜日

MySQL JOIN コマンド

incident テーブル

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

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

| id | int(11) | NO | PRI | NULL | |

| owner | int(11) | YES | | NULL | |

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

owner テーブル

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

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

| id | int(11) | NO | PRI | NULL | |

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

incident テーブルと owner テーブルを結合する。

SELECT i.id, i.title, o.name
FROM `incident` AS i
RIGHT JOIN (SELECT id, name FROM `owner`) AS o
ON i.owner = o.id

2017年3月16日木曜日

つなぎ合わせるテーブル３つは下記のとおりです。

受注一覧テーブル
SELECT * FROM order_master;
+----+------------+-----------+
| id | product_id | vendor_id |
+----+------------+-----------+
| 1 | 1 | 2 |
| 2 | 2 | 1 |
| 3 | 4 | 3 |
| 4 | 3 | 2 |
+----+------------+-----------+

商品一覧テーブル
SELECT * FROM product_master;
+----+----------------+
| id | product_name |
+----+----------------+
| 1 | 調味料A |
| 2 | 調味料B |
| 3 | 佃煮 |
| 4 | コーンフレーク |
+----+----------------+

配送業者テーブル
SELECT * FROM vendor_master;
+----+-------------+---------------------+
| id | vendor_name | contract_date |
+----+-------------+---------------------+
| 1 | 東京配送 | 2017-03-03 00:00:00 |
| 2 | なにわ屋 | 2017-05-03 00:00:00 |
| 3 | 桃山Express | 2018-03-03 00:00:00 |
+----+-------------+---------------------+

①　配送業者テーブル（vendor_master）から、有効期限内である業者からの受注情報を取得します。有効期限を判定するには、配送業者テーブル（vendor_master）の contract_date が今日よりも大きい日付であることを調べます。

②　①の結果取得される配送業者テーブル（vendor_master）を、RIGHT OUTER JOIN で受注一覧テーブル（order_master）と結合します。

③　②の結果と商品一覧テーブル（product_master）を結合します。

SQL 文はこちらです。

SELECT
 o.id '注文 ID'
 ,o.product_id '商品 ID'
 ,p.product_name '商品名'
 ,v.vendor_name '配送業者'
FROM
 order_master o
 RIGHT OUTER JOIN /*②の処理*/
 (SELECT id, vendor_name FROM vendor_master WHERE contract_date > CURDATE()) v /*①の処理*/
 ON
 o.vendor_id = v.id
 JOIN /*③の処理*/
 product_master p
 ON
 o.product_id = p.id
;

2017年3月14日火曜日

MySQL ログイン方法

下記、root としてログインするコマンドです。

mysql -u root -p

root にパスワードが設定されている場合、続いてパスワードの入力が求められます。

※ログインユーザーは下記で確認することができます。

select user();

リンク：SQL 基本コマンド一覧
http://kainobi2.blogspot.com/2017/03/sql.html

2017年3月13日月曜日

Visual Studio から MySQL に接続してデータを DataGridView に表示する

今回は xampp の MySQL データベースのデータテーブルを取得して Windows Forms の DataGridView に表示する例を紹介します。

NuGet を利用して MySQL のドライバー（MySql.Data）をダウンロード、参照します。
次のスクリーンショットは、MySql.Data をインストールし、プロジェクトでの参照設定がなされた後の状態です。

続いて、フォーム上にボタンを配置してクリックイベントのハンドラーを用意します。イベントハンドラー内に、下記のように実装します。
接続文字列は、MySqlConnectionStringBuilder を利用していますが、代わりに string に直接接続文字列を記述することも可能です。

private void button1_Click(object sender, EventArgs e)
{
    //方法１：MySqlConnectionStringBuilder を利用して接続情報を作成
    MySqlConnectionStringBuilder conBuilder = new MySqlConnectionStringBuilder
    {
        UserID = "root",
        Database = "test",
        Server = "127.0.0.1"
    };

    //方法２：もしくはこのように string で接続文字列を作成
    //string conString = "host=127.0.0.1; user=root; database=test";

    //方法１、方法２で、using を使わずにコネクションオブジェクトを生成する場合、下記２行が利用可能
    //try 内で connection.Open() メソッドでデータベースと接続できるかを確認する
    //MySqlConnection connection = new MySqlConnection(conBuilder.ConnectionString);
    //MySqlConnection connection = new MySqlConnection(conString);

    //using 内でコネクションオブジェクトを生成（利用後に確実に破棄されるようにする）
    using (var connection = new MySqlConnection(conBuilder.ConnectionString))
    //using (var connection = new MySqlConnection(conString))
    {
        try
        {
            // データベースとの接続をオープン
            connection.Open();
            // データを保存するテーブルを作成
            DataTable dt = new DataTable();
            // SQL 文と接続情報を引数に、データアダプターを作成
            MySqlDataAdapter da = new MySqlDataAdapter("select * from books", connection);
            // SQL 文で指定したデータを DataTable に格納
            da.Fill(dt);
            // DataTable を DataGridView に表示
            dataGridView1.DataSource = dt;
            // データベースとの接続をクローズ
            connection.Close();

        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
    }
}

参考情報：

Connect Visual Studio 2015 (VB.net) with XAMPP Tutorial (EASY WAY)
https://www.youtube.com/watch?v=UQ_C8c6rev8

2017年3月10日金曜日

MySQL 集計関数

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

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

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

SELECT * FROM person;

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

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

| 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 (集約) 関数

https://dev.mysql.com/doc/refman/5.6/ja/group-by-functions.html

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;

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;

2017年3月7日火曜日

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

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

MariaDB [test]> SELECT person.name, department.name 
FROM person, department 
WHERE person.dept_no = department.no;

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

MariaDB [test]> SELECT person.name AS 'person name', department.name  AS 'department name' 
FROM person, department 
WHERE person.dept_no = department.no;

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;

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;

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';

行の選択１
--person テーブルの dept_no 列と、department テーブルの no 列を結合。両方のテーブルの全列を選択。

SELECT * 
FROM person, department 
WHERE person.dept_no = department.no;

行の選択２

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

2013年6月16日日曜日

PHP で MySQL のテーブルに行を追加

PHP で MySQL のテーブルに行を追加するコード例です。

<?php
    // レコード追加
    $insert_sql = "INSERT INTO table1 (Name) VALUES ('Chigusa')";
    $result_flag = mysql_query($insert_sql);
?>

2013年5月26日日曜日

MySQL AUTO_INCREMENT を追加設定

MySQL のテーブルに AUTO_INCREMENT を追加することができます。

phpMyAdmin でテーブルを表示して・・・

対象のカラムの「AUTO_INCREMENT」にチェック

これで「保存する」をクリックします。

登録: 投稿 (Atom)