レコードを挿入しよう

2024.02.02

SQLの命令文とは？

SQLの基本的な命令文は、

  • SELECT　…取得
  • INSERT　…登録
  • UPDATE　…修正
  • DELETE　…削除

です。
具体的な文法も参考サイト等で調べてください。

レコードを追加しよう！

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

m_usersの内容

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

データの追加

以下を実行してみましょう！

コマンド例
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;

実行結果

データを挿入変更しよう！

挿入実行例
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では、コマンド日本語対応がされていません。気になる方で変更したい場合はこちらを参考で実行してください。

文字コードを変更しよう！

MySQL
1
mysql>SET NAMES cp932;

データを更新しよう！

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;

トランザクションの機能を活用すると、実際に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文の使い方を知っておこう

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_profailes WHERE place IN (20, 21,30);
SELECT * FROM m_profailes WHERE place BETWEEN 10 AND 30;
SELECT * FROM m_profailes WHERE place >30 OR place <=20; //OR検索
SELECT * FROM m_profailes WHERE age =21 AND age =23; //AND検索
SELECT * FROM m_profailes 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;

課題

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

　m_place(都道府県テーブル)

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

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

②作成したSQLテーブルを利用して、データを表示してください。件数が足りない場合は、以下の件数になるように仮のレコードを挿入してください。

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

③m_profilesとm_placeテーブルを利用して、内部結合（INNER JOIN）を使って表示してください。

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

　　テキスト名：profiles_place.txt

④m_usersとm_profilesテーブルを利用して、外部結合(LEFT JOIN句, RIGHT JOIN句)を使って表示してください。

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

　　テキスト名：users_profiles.txt

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

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

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

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

MySQLについての課題は以上となります。
作成したデータベースは、この先の「システム編」で使用しますので消さないようにしておいてください！

コメント