SQL基礎

table基本

tableの作成

idをPrimaryとして設定しています。また、auto_incrementで自動でカウントアップしていきます。

create table member_table2 (id int primary key auto_increment, name varchar(500),  age int);

tableの定義一覧

テーブルの定義一覧を表示。mysqlの構文かも。

# show columns from member_table2;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int          | NO   | PRI | NULL    | auto_increment |
| name  | varchar(500) | YES  |     | NULL    |                |
| age   | int          | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+

tableのデータ追加

insert into items values (1, 'apple', 'APPLE', 100, 10);

tableの更新(Update)

書式 update テーブル名 set age=200 where id=変更したいID;

 update member_table2 set age=200 where id=2;

部分一致検索 Like

文字列の部分検索ができます。 LIKEを利用すると%を利用することができます。 %はワイルドカードと同じ意味になります。 以下の場合、キーにbの文字列が含まれているものを探します。

SELECT * FROM table名 WHERE キー like '%b%'

データの削除

delete from member_table where id = 2;

データの並び替え ORDER BY

データの並び替えには、ORDER BYを利用します。 以下の場合、idというキーを降順で並び替えます。

 SELECT * FROM テーブル名 ORDER BY name ASC;

ASC(Ascending) :  大きい方から小さい方
DESC (DAcending):小さい方から大きい方。

データの集計(SUM, MAX, MIN, COUNT)

以下のようなデータを用意します。

+----+------------+---------------+-------+-------+----------+
| id | item_name  | itemname_kana | price | count | genre_id |
+----+------------+---------------+-------+-------+----------+
|  1 | apple      | APPLE         |   150 |    50 |        1 |
|  2 | carrot     | CARROT        |   150 |    30 |        0 |
|  3 | orange     | ORANGE        |   100 |    10 |        0 |
|  4 | strawberry | STRAWBERRY    |   100 |    10 |        0 |
|  5 | lettuce    | LETTUCE       |   200 |     3 |        1 |
| 50 | cucumber   | CUCUMBER      |    20 |     4 |        1 |
+----+------------+---------------+-------+-------+----------+
データの集計 sum

SELECT sum(集計したいフィールド名) from テーブル名称; 以下の場合、genre_idが1のフィールドの合計を集計します。

select sum(count) from items where genre_id=1;
+------------+
| sum(count) |
+------------+
|        57 |
+------------+
データの最大値、最小値の選定max, min

max. min関数を利用して、フィールドの最大値、最小値を選出できます。

 select max(price) from items ;
 select min(price) from items ;
データの個数のカウント

count関数を利用して対象のフィールドの個数をカウントすることができます。

select * from items;

table定義変更

tableカラム追加

以下は、itemsというテーブルに、カラムgenre_idをint型で追加しています。

 alter table items add genre_id int

tableカラム追加

以下は、itemsというテーブルから、カラムgenre_idを削除しています。

ALTER TABLE items DROP genre_id;

リレーション(テーブルの結合)

以下のように、genre_idを外部キーとした2種類のテーブルを想定します。

itemsテーブル
+----+------------+---------------+-------+-------+----------+
| id | item_name  | itemname_kana | price | count | genre_id |
+----+------------+---------------+-------+-------+----------+
|  1 | apple      | APPLE         |   150 |    50 |        1 |
|  2 | carrot     | CARROT        |   150 |    30 |        0 |
|  3 | orange     | ORANGE        |   100 |    10 |        0 |
|  4 | strawberry | STRAWBERRY    |   100 |    10 |        0 |
|  5 | lettuce    | LETTUCE       |   200 |     3 |        1 |
| 50 | cucumber   | CUCUMBER      |    20 |     4 |        1 |
+----+------------+---------------+-------+-------+----------+

genreテーブル
+----------+------------+
| genre_id | genre_name |
+----------+------------+
|        0 | fruit      |
|        1 | food       |
+----------+------------+

上記の2つのテーブルをgenre_idを外部キーとして、結合します。 書式 SELECT [表示するフィールド] FROM [テーブル名] WHERE [結合するキー]

select items.*, genre_master.* from items, genre_master where items.genre_id=genre_master.genre_id;
+----+------------+---------------+-------+-------+----------+----------+------------+
| id | item_name  | itemname_kana | price | count | genre_id | genre_id | genre_name |
+----+------------+---------------+-------+-------+----------+----------+------------+
|  1 | apple      | APPLE         |   150 |    50 |        1 |        1 | food       |
|  2 | carrot     | CARROT        |   150 |    30 |        0 |        0 | fruit      |
|  3 | orange     | ORANGE        |   100 |    10 |        0 |        0 | fruit      |
|  4 | strawberry | STRAWBERRY    |   100 |    10 |        0 |        0 | fruit      |
|  5 | lettuce    | LETTUCE       |   200 |     3 |        1 |        1 | food       |
| 50 | cucumber   | CUCUMBER      |    20 |     4 |        1 |        1 | food       |
+----+------------+---------------+-------+-------+----------+----------+------------+

mysqlのdockerでの構築方法

以下のコマンドでmysqlのコンテナをデプロイすることが可能です。

docker run --name some-mysql -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql

docker execコマンドで、コンテナにアクセスし、mysqlコマンドで、DBにアクセスできます。

$ sudo docker exec -it  66817df58407  /bin/sh
$mysql -u root -p