P-13. レコードを挿入してみよう

2022.10.18

レコードを追加しよう！

レコードを追加するには、データベースの操作に応じた適切なSQLクエリを使用します。前回のセクションの例題であった「m_users」テーブルと課題で作成した「m_profiles」テーブルをもとに、例題と課題を取り組んでみましょう！

m_usersの内容

カラム名名称データ型NULLDefault自動採番KEY備考
idIDintNOTNULL◯IDをプライマリーキーにする（PRIMARY KEY）–
user_nameユーザー名varchar(100)NOTNULL––最大全角100文字
mail_addressメールアドレスvarchar(200)NOTNULL––最大全角200文字
passwordパスワードvarchar(100)NOTNULL–––
created登録日datetimeDEFAULTNULL––‘YYYY-MM-DD HH:MM:SS’ 
modified更新日datetimeDEFAULTNULL––‘YYYY-MM-DD HH:MM:SS’ 

m_profilesの内容

カラム名名称データ型NULLDefault自動採番KEY備考
idIDintNOTNULL◯IDをプライマリーキーにする（PRIMARY KEY）–
name名前varchar(100)NOTNULL––最大全角100文字（200バイト）
kanaカナvarchar(100)NOTNULL––最大全角100文字（200バイト）
age年齢intNOTNULL–––
profileプロフィールtextNOTNULL–––
place出身都道府県地番号int(5)NOTNULL––47都道府県を数字で管理
sex性別int(2)NOTNULL––1:男　2:女
created登録日datetimeDEFAULTNULL––‘YYYY-MM-DD HH:MM:SS’ 
modified更新日datetimeDEFAULTNULL––‘YYYY-MM-DD HH:MM:SS’ 

データの追加

追加変更削除のコマンド文は　INSERT　UPDATE　DELETEです。

コマンド文説明
INSERTデータベースのテーブルに新しいレコードを追加します。
UPDATEデータベースのテーブル内の既存のレコードの値を更新します。
DELETEデータベースのテーブルから特定の条件に一致するレコードを削除します。

INTO句とFROM句

INSERT文やDELETE文、SELECT文などは、INTOやFROMのような「句」を使用したりします。他にもWHERE句などの句があるが、ここではINTO句とFROM句の説明をさせていただきます。他に何があるかは、こちらを参照いただくか、「SQL 句 一覧」等で検索してみてください。

句説明直訳句を使用する文(基本例)
INTO新しいデータを挿入する先のテーブルを指定します。「〜の先へ」INSERT…etc
FROMデータの参照元や操作対象のテーブルを指定します「〜から」DELETE、SELECT…etc
コマンド例
MySQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
//--追加
mysql> INSERT INTO [テーブル名] [フィールド名] VALUES [値];
 
//--追加例
mysql> INSERT INTO m_users (user_name, mail_address, password, created, modified)　
                    VALUES ("Yamada Taro", "yamda@gmail.com", "123123", now(), now());
 
//--※ now() ・・・ 現在の日時
 
//--更新
mysql> UPDATE [テーブル名] SET [フィールド名]=[値] WHERE [条件式];
//--更新例
mysql> UPDATE m_users SET user_name="SuzukiYamada Takao", mail_address="ytakao@gmail.com" WHERE id = 1;
 
 
//--削除：素人はやらないように！
//--全レコード削除 　注意これ危険！
mysql> DELETE FROM [テーブル名];
 
//--一部レコード削除
mysql> DELETE FROM [テーブル名] WHERE [条件式];
 
//--削除例：id 5超えたものは全部削除
mysql> DELETE FROM m_users WHERE id > 5;

実行結果例

データを挿入しよう！

INSERT文をさらに実行してみましょう！

挿入実行例
MySQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
//--挿入
mysql> INSERT INTO m_users (user_name, mail_address, password, created, modified)
           VALUES ("Yamada Taro", "yamda@gmail.com", "123123", now(), now()),
                  ("Suzuki Ichiro", "suzuki@gmail.com", "123123", now(), now()),
                  ("Sato   Jiro", "sato@gmail.com", "123123", now(), now());
 
//--挿入確認
mysql> SELECT * FROM m_users;
 
mysql> INSERT INTO m_profiles (name, kana, age, profile, place, sex, created, modified)
           VALUES ("山田　太郎", "ヤマダタロウ", 21, "プロフィール１", 20, 1,now(), now()),
                  ("鈴木　一朗", "スズキイチロウ", 33, "プロフィール２", 30, 1,now(), now()),
                  ("佐藤　治郎", "サトウジロウ", 23, "プロフィール３", 35, 1,now(), now());  
 
//--挿入確認
mysql> SELECT * FROM m_profiles;

実行結果例

※日本語文字化けについて：初期のxammpでは、コマンド日本語対応がされていません。気になる方で変更したい場合はこちらを参考で実行してください。

以下で文字コードを「ShiftJIS」に変更しておきます。

MySQL
1
mysql>SET NAMES cp932;

データを更新しよう！

UPDATE文をもっと実行してみましょう！

1
2
3
sql>UPDATE m_users SET user_name="Suzuki Hanako" WHERE id = 2;
 
sql>UPDATE `m_profiles` SET `name` = '鈴木　花子', `kana` = 'スズキハナコ', `sex` = '2' WHERE `m_profiles`.`id` = 2;

変更されているか確認してください！！

レコードの操作実行しよう！

トランザクション

トランザクションの機能を活用すると、実際にUPDATEなどのクエリを実行してSELECTで結果を確認したあとに、意図通りに変更できていれば、反映させます。
意図通りに変更できてなければ、反映させないということができます。

確認のための機能というより同じデータを、他者から書き込ませたいために利用するものです。

トランザクションの種類
1
2
3
4
5
6
//--トランザクションの開始
mysql> START TRANSACTION;
//--コミット
mysql> COMMIT;
//-- ロールバック
mysql> ROLLBACK;

実行してみよう！

MySQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> START TRANSACTION;
mysql> UPDATE m_users SET user_name="Yamada Tarou", mail_address="Yamada_Tarou@gmail.com" WHERE id = 1;
mysql> SELECT * FROM m_users WHERE id IN(1,2,3);
//--ここで状態確認 まだ更新されていない
 
mysql> COMMIT;
 
 
mysql> START TRANSACTION;
mysql> UPDATE m_users SET user_name="YamadaHOGE" WHERE id = 1;
mysql> SELECT * FROM m_users WHERE id IN(1,2,3);
//--ここで状態確認 まだ更新されていない
//--間違っていれば　キャンセルしてもどす
 
mysql> ROLBACK;

テーブルデータを読み出そう！

select文の使い方を知っておこう

SELECT文は、データベースからデータを取得するために使用されます。

MySQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SELECT * FROM m_profiles;
//--部分カラムを選択
SELECT id, name, kana, updated FROM m_profiles;
 
//--ageの平均
SELECT AVG(age) FROM m_profiles;
//-- テーブル数
SELECT count(id) FROM m_profiles;
 
//where limit
SELECT id, user_name FROM m_users WHERE id = 2;
SELECT id, user_name FROM m_users WHERE id >= 2;
SELECT * FROM m_profiles　WHERE place IN (20, 21,30);
SELECT * FROM m_profiles　WHERE place BETWEEN 10 AND 30;
SELECT * FROM m_profiles　WHERE place >30 OR place <=20; //OR検索
SELECT * FROM m_profiles　WHERE age =21 AND age =23; //AND検索
SELECT * FROM m_profiles　WHERE name LIKE "%鈴木%"; //部分検索
 
SELECT * FROM m_profiles　LIMIT 2; // 2レコードまで表示

JOIN　内部　外部接続

テーブル同士を接続して表示します。

内部結合・JOINの基礎
MySQL
1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT table_name.col_name1 [, table_name.col_name2 ...]
  FROM table_name1
  INNER JOIN tbl_name2
  ON table_name1.col_name1 = table_name2.col_name2;
 
//記述例
SELECT * FROM m_users JOIN m_profiles ON m_users.id = m_profiles.id;
 
//見やすく
SELECT  name AS 名前, age AS 年齢 FROM m_profiles AS 社員の名前と年齢;
 
--//USINGで簡単に
SELECT  * FROM m_users  JOIN m_profiles USING(id);
JOIN
1
SELECT * FROM m_users JOIN m_profiles ON m_users.id = m_profiles.id;

課題

課題手順

1．placesテーブルを作成して、出身県１～47をいれてください。

　m_place(都道府県テーブル)

　都道府県データ挿入のテーブルデータ　参考のサイト

カラム名名称データ型NULLDefault
place_no県NOintNOTNULL
place_name県名varchar(100)NOTNULL

2．作成したSQLテーブルを利用して、データを表示してください。

　　m_users:20件・ m_profiles:10件・m_place（都道府県テーブル）:47件

３．m_profilesとm_placeテーブルを利用して、内部結合を使って表示してください。

　　提出物：作成したSQL文と出力されたデータをコピーして、テキストに貼り付けて送ってください。

　　テキスト名：profiles_place.txt

４．m_usersとm_profilesテーブルを利用して、外部結合を使って表示してください。

　　提出物：作成したSQL文と出力されたデータをコピーして、テキストに貼り付けて送ってください。

　　テキスト名：users_profiles.txt

課題補足

・内部結合：テーブルとテーブルの互いの条件に一致するレコード(データ行)をのみを抽出します。

・外部結合：一致しない場合もデータとして取得、一致しないデータは取得しない。

実行結果

m_placeテーブル作成後、テーブルの中身を確認した例

内部結合・実行結果例

外部結合・実行結果例

ヒント

ヒント：都道府県名のは、内部結合（INNERJOIN）を使います。

m_users と m_profilesは外部結合(LEFT JOIN句, RIGHT JOIN句)を使います。m_usersにしかないテーブルも表示するようにします。

課題・復習用検索キーワード

検索例
🔍sql　構文　一覧
🔍sql　内部結合　外部結合
🔍sql　句　とは