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 |
+
外部キー操作
外部キー FOREIGN KEY/REFERENCE
department_id | department_name
1 | development_department
2 | sales_department
3 | accounting_department
*以下のコマンドで、従業員テーブルを準備します。この時、 department_idを外部キーとして、部署テーブルと結びつけます。
FOREIGN KEY(department_id) で外部キーを指定します。この場合department_idです。
また、REFERENCES departments(department_id))で、参照するテーブルとフィールドを指定します。この場合、”department”テーブルの"department_id"を参照することを意味します。
CREATE TABLE employees(id int PRIMARY KEY NOT NULL, department_id int , name varchar(256), FOREIGN KEY(department_id) REFERENCES departments(department_id));
データを入力して、最終的には、以下のようになります。 department_idで二つのテーブルが紐づけられます。
select * from employees ;
id | department_id | name
1 | 1 | Yamada
2 | 1 | Jack
3 | 3 | Lee
4 | 2 | Rick
select * from departments;
department_id | department_name
1 | development_department
2 | sales_department
3 | accounting_department
INNER JOIN
以下のような従業員・部署テーブルを考えます。
外部キーのdepartment_idで紐づけられています。
select * from employees ;
id | department_id | name
1 | 1 | Yamada
2 | 1 | Jack
3 | 3 | Lee
4 | 2 | Rick
select * from departments;
department_id | department_name
1 | development_department
2 | sales_department
3 | accounting_department
外部キーを利用して、2つのテーブルを紐づけたうえで、id, name, department_nameの順で表示します。
「SELECT ~ FROM employees」で表示したいフィールドを記載します。 e.はemployeesテーブル、d. はdepartmentテーブルの略称を意味します。
「INNER JOIN departmens ON e.department_id = d.department_id 」で紐づけたいテーブル(department)を指定し、ONの後で、紐づけのためのフィールドを記載します。
SELECT e.id, e.name, d.department_name FROM employees e INNER JOIN departments d ON e.department_id = d.department_id;
id | name | department_name
1 | Yamada | development_department
2 | Jack | development_department
3 | Lee | accounting_department
4 | Rick | sales_department
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