How to Use the PowerPoint Template

Transcription

1 MySQL 5.6 Developer (1Z0-882) サンプル問題 解答 解説 オラクルユニバーシティ

2 Q1:MySQL アーキテクチャ MySQL クライアントで 既にデータベースに接続しています SOURCE コマンドを使用してロードできるのは 次のどのファイルでしょうか 1 つ選択してください 1. Tab 区切りのデータ ファイル 2. カンマ区切りのデータ ファイル 3. InnoDBやMyISAMで使用されている データベースのデータ ファイル 4. 実行したいSQLコマンドがリストされているファイル

3 A1:MySQL アーキテクチャ MySQL クライアントで 既にデータベースに接続しています SOURCE コマンドを使用してロードできるのは 次のどのファイルでしょうか 1 つ選択してください 1. Tab 区切りのデータ ファイル 2. カンマ区切りのデータ ファイル 3. InnoDBやMyISAMで使用されている データベースのデータ ファイル 4. 実行したいSQLコマンドがリストされているファイル

4 解説 MySQLアーキテクチャ mysql へ接続している場合には source コマンドまたは. コマンドを使用して SQL スクリプトファイルを実行できます test.sql insert into test values(1, 'TEST1'); insert into test values(2, 'TEST2'); insert into test values(3, 'TEST3'); test表の定義 mysql> desc test; Field Type Null Key Default Extra ID int(11) YES NULL Name char(35) YES NULL rows in set (0.00 sec) mysql> source test.sql Query OK, 1 row affected (0.04 sec) Query OK, 1 row affected (0.12 sec) Query OK, 1 row affected (0.04 sec) mysql>. test.sql でもOK

5 解説 MySQLアーキテクチャ mysqlに接続していない場合でも バッチモードが利用できます test.sql use world; insert into test values(1, 'TEST1'); insert into test values(2, 'TEST2'); insert into test values(3, 'TEST3'); insert into test values('aaa', 'TEST1'); insert into test values(4, 'TEST4'); $ mysql --force < test.sql mysql> select * from test; ID Name TEST1 2 TEST2 3 TEST3 4 TEST rows in set (0.00 sec) test表の定義 mysql> desc test; Field Type Null Key Default Extra ID int(11) YES NULL Name char(35) YES NULL rows in set (0.00 sec) 一部のステートメントでエラーが発生してもスクリプト を続行する--force コマンド行オプションを使用するこ とができます 赤字のスクリプトはエラーになりますが 継続され 青字のスクリプトは正常に実行されていることがわかり ます

6 Q2:MySQL アーキテクチャ 右にある実行履歴を確認してください 作成されるテーブルの名前は 次のうちどれですか?1 つ選択してください 1. TEST 2. test 3. Test 4. TesT mysql> show variables like 'LOWER_CASE_TABLE_NAMES'; Variable_name Value lower_case_table_names row in set (0.00 sec) mysql> mysql> create table Test(col1 int(01)); Query OK, 0 rows affected (0.31 sec)

7 A2:MySQL アーキテクチャ 右にある実行履歴を確認してください 作成されるテーブルの名前は 次のうちどれですか?1 つ選択してください 1. TEST 2. test 3. Test 4. TesT mysql> show variables like 'LOWER_CASE_TABLE_NAMES'; Variable_name Value lower_case_table_names row in set (0.00 sec) mysql> mysql> create table Test(col1 int(01)); Query OK, 0 rows affected (0.31 sec)

8 解説 :MySQL アーキテクチャ サーバ変数 : LOWER_CASE_TABLE_NAMES( デフォルト :0) について 値による振る舞いを整理しておきましょう 0 : テーブル名は指定したとおりに格納大文字と小文字が区別される 1: テーブル名はディスク上に小文字で格納される 比較では大文字と小文字は区別されない 2 : テーブル名は指定したとおりに格納されるが 小文字で比較される なお カラム名 インデックス名 ストアドルーチン名 およびイベント名は どのプラットフォームでも大文字と小文字が区別されません

9 Q3:MySQL の一般的な構文 データベースを作成します 以下のスクリプトのうち 作成することができないのはどれですか 1つ選択してください 1. Create database $test; 2. Create database `test/`; 3. Create database `test db` 4. Create database 99test; 5. Create database 99;

10 A3:MySQL の一般的な構文 データベースを作成します 以下のスクリプトのうち 作成することができないのはどれですか 1つ選択してください 1. Create database $test; 2. Create database `test/`; 3. Create database `test db` 4. Create database 99test; 5. Create database 99;

11 解説 :MySQL の一般的な構文 以下のページで オブジェクトの命名規則を整理しておきましょう なお 引用符 (`) で囲まれていれば 数字のみの構成も可能です mysql> create database `99`; Query OK, 1 row affected (0.00 sec)

12 Q4:MySQL の一般的な構文 コメント構文として認識されないものは 次の選択肢のうち どれですか 1 つ選択してください 1. mysql> # SELECT 1+1; 2. mysql> -- SELECT 1+1; 3. mysql > // SELECT 1+1; 4. mysql > /* SELECT 1+1 SELECT 1+1 */

13 A4:MySQL の一般的な構文 コメント構文として認識されないものは 次の選択肢のうち どれですか 1 つ選択してください 1. mysql> # SELECT 1+1; 2. mysql> -- SELECT 1+1; 3. mysql > // SELECT 1+1; 4. mysql > /* SELECT 1+1 SELECT 1+1 */

14 解説 :MySQL の一般的な構文 MySQLでは 以下の3 つのコメントスタイルがサポートされています # 文字から行末まで -- シーケンスから行末まで /* シーケンスから次の */ シーケンスまで ( 複数行可 ) 以下のページで コメントの構文を整理しておきましょう

15 Q5:MySQL の一般的な構文 MySQL クライアントを用いてプリペアド ステートメントを作成します 正しく使用できるコマンドを 2 つ選択してください 1. mysql> PREPARE stmt1 AS 'SELECT COL1 FROM TEST WHERE COL1=?'; 2. mysql> PREPARE STATEMENT stmt1 FROM 'SELECT COL1 FROM TEST WHERE COL1=?'; 3. mysql> PREPARE stmt1 FROM 'SELECT COL1 FROM TEST WHERE COL1=?'; 4. mysql> PREPARE stmt1 FROM SELECT COL1 FROM TEST WHERE COL1=?; 5. mysql> = 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse'; mysql> PREPARE stmt2

16 A5:MySQL の一般的な構文 MySQL クライアントを用いてプリペアド ステートメントを作成します 正しく使用できるコマンドを 2 つ選択してください 1. mysql> PREPARE stmt1 AS 'SELECT COL1 FROM TEST WHERE COL1=?'; 2. mysql> PREPARE STATEMENT stmt1 FROM 'SELECT COL1 FROM TEST WHERE COL1=?'; 3. mysql> PREPARE stmt1 FROM 'SELECT COL1 FROM TEST WHERE COL1=?'; 4. mysql> PREPARE stmt1 FROM SELECT COL1 FROM TEST WHERE COL1=?; 5. mysql> = 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse'; mysql> PREPARE stmt2

17 解説 MySQLの一般的な構文 PREPARE ステートメント SQL ステートメントを準備し 名前 stmt_name を割り当てる 名前はあとでそのステートメントpreparable_stmtを参照するために使用 構文 PREPARE stmt_name FROM preparable_stmt; preparable_stmt ステートメントのテキストを含む文字列リテラル 引用符 で囲む または ユーザー変数のどちらか 文字列値にバインドしようとしている場合でも? 文字を引用符で囲まない

18 解説 :MySQL の一般的な構文 PREPARE ステートメントで許可される構文 ( 一例 ): ALTER TABLE ALTER USER (as of MySQL 5.6.8) CALL {CREATE RENAME DROP} TABLE {CREATE RENAME DROP} USER {CREATE DROP} VIEW OPTIMIZE TABLE REPAIR TABLE. 許可される構文はたくさんありますが ALTER に関しては TABLE と USER のみです

19 解説 :MySQL の一般的な構文 以下の動作も合わせて確認しておきましょう プリペアド ステートメントを解放せずにセッションを終了した場合 そのステートメントはサーバーによって自動的に解放されます 同一名のプリペアド ステートメントが作成されると 新たに作成したステートメントが準備される前に解放されます 新たに作成したステートメントにエラーが含まれる場合には 準備ができないため エラーが返され 指定された名前を持つステートメントは存在しなくなります

20 Q6:MySQL の一般的な構文 MySQL で新しいトランザクションを開始する際に使用できるコマンドを 3 つ選んでください 1. mysql> start transaction 2. mysql> begin 3. mysql> start 4. mysql> set auto_commit = 1 5. mysql> set autocommit=1;

21 A6:MySQL の一般的な構文 MySQL で新しいトランザクションを開始する際に使用できるコマンドを 3 つ選んでください 1. mysql> start transaction 2. mysql> begin 3. mysql> start 4. mysql> set auto_commit = 1 5. mysql> set autocommit=1;

22 解説 :MySQL の一般的な構文 トランザクションの使用について確認しておきましょう START TRANSACTION または BEGIN は 新しいトランザクションを開始します COMMIT は 現在のトランザクションをコミットして その変更を永続的なものにします ROLLBACK は 現在のトランザクションをロールバックして その変更を取り消します SET autocommit は 現在のセッションのデフォルトの自動コミットモードを無効または有効にします

23 Q7:MySQL のスキーマ オブジェクトの設計と作成 データの正規化の目的について 誤っているものを2つ選んでください 1. 冗長なデータを排除する 2. パフォーマンスを向上させる 3. データへの柔軟なアクセスを実現する 4. フィールド値が正しい値で収まるように 変換し トリミングする

24 A7:MySQL のスキーマ オブジェクトの設計と作成 データの正規化の目的について 誤っているものを2つ選んでください 1. 冗長なデータを排除する 2. パフォーマンスを向上させる 3. データへの柔軟なアクセスを実現する 4. フィールド値が正しい値で収まるように 変換し トリミングする

25 解説 MySQLのスキーマ オブジェクトの設計と作成 データの正規化の目的には 以下があります 冗長データの排除 データへの柔軟なアクセスの実現 データの非一貫性の発生の最小化 正規化を行うことによって アクセスされるデータ量が減り パフォーマンスの改善につながるケースもありますが 正規化は パフォーマンス改善を目的としたものではありません

26 解説 MySQLのスキーマ オブジェクトの設計と作成 正規化を考える いずれかのカテゴリ コードに属す商品があります 複数のカテゴリに属す商品はありません 正規化 されたスキーマ設計は カテゴリ コード カテゴリ名 カテゴリ コード 商品コード 商品名 カテゴリ コード カテゴリ名 商品 コード 商品名 1 衣服 1 1 シャツ 1 衣服 1 シャツ 1 2 パンツ 1 衣服 2 パンツ 1 3 スカート 1 衣服 3 スカート A カテゴリマスタ 商品マスタを作成する B 1つの表で表現する Aが 正規化 されたスキーマ設計です

27 Q8:MySQL のスキーマ オブジェクトの設計と作成 外部キーを作成しようとしたところ エラーになりました その理由を 1 つ選んでください CREATE TABLE `ORDERS` ( `id` int(11) unsigned NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 ORDER_ITEM CREATE TABLE `ORDER_ITEM` ( `id` int(11) unsigned NOT NULL, `order_id` int(11) unsigned NOT NULL, PRIMARY KEY (`id`), KEY `order_id` (`order_id`), CONSTRAINT `ORDER_ITEM_ibfk_1` FOREIGN KEY (`order_id`) REFERENCES `ORDERS` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 1. ORDER_ITEM 表が InnoDB 表であるため 2. ORDER 表が MyISAM 表であるため 3. 参照する ORDER_ITEM.ID 列と ORDER_ITEM.ORDER_ID の列名が違うため 4. MySQL ではどのストレージエンジンでも 参照性制約をサポートしていないため

28 A8:MySQL のスキーマ オブジェクトの設計と作成 外部キーを作成しようとしたところ エラーになりました その理由を 1 つ選んでください CREATE TABLE `ORDERS` ( `id` int(11) unsigned NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 ORDER_ITEM CREATE TABLE `ORDER_ITEM` ( `id` int(11) unsigned NOT NULL, `order_id` int(11) unsigned NOT NULL, PRIMARY KEY (`id`), KEY `order_id` (`order_id`), CONSTRAINT `ORDER_ITEM_ibfk_1` FOREIGN KEY (`order_id`) REFERENCES `ORDERS` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 1. ORDER_ITEM 表が InnoDB 表であるため 2. ORDER 表が MyISAM 表であるため 3. 参照する ORDER_ITEM.ID 列と ORDER_ITEM.ORDER_ID の列名が違うため 4. MySQL ではどのストレージエンジンでも 参照性制約をサポートしていないため

29 解説 :MySQL のスキーマ オブジェクトの設計と作成 InnoDB は外部キーをサポートしていますが MyISAM は非サポートです NDB もサポートしていますが 参照先が親テーブルの主キーである場合 ON UPDATE CASCADE はサポートされません 親テーブルと子テーブルは 同じストレージエンジンを使用する必要があります つまり ORDER 表も InnoDB で作成すれば 外部キーの作成ができます 外部キー内の対応するカラムと 参照されるキーは同様のデータ型を持っている必要がありますが 列名が異なっていても問題ありません

30 Q9:MySQL のスキーマ オブジェクトの設計と作成 ビューの使用によるメリットについて 正しくない記述を 2 つ選択してください 1. データの簡略化 複雑なデータを隠し 要約したデータを提供できる 2. セキュリティ 異なるユーザーに対して異なる方法でデータを表示できる 3. 迅速な行アクセス パフォーマンスが改善される 4. 管理性 過去のデータへのアクセスが可能になる

31 A9:MySQL のスキーマ オブジェクトの設計と作成 ビューの使用によるメリットについて 正しくない記述を 2 つ選択してください 1. データの簡略化 複雑なデータを隠し 要約したデータを提供できる 2. セキュリティ 異なるユーザーに対して異なる方法でデータを表示できる 3. 迅速な行アクセス パフォーマンスが改善される 4. 管理性 過去のデータへのアクセスが可能になる

32 解説 :MySQL のスキーマ オブジェクトの設計と作成 ビューを作成してもパフォーマンスの向上にはなりません データ更新が可能なビュー集計関数 (SUM() MIN() MAX() COUNT() など ) HAVING 句 GROUP BY 句などのグループ関数が使用されていないこと そのビュー内の行とベースとなるテーブル内の行の間に 1 対 1 の関係が存在することなどが前提になります 更新させたくない場合には WITH CHECK OPTION は 許可される変更に制約を課します ビューによって選択されなくなるように行が更新されることや ビューで選択できない行が挿入されることがなくなります 前提条件を しっかり理解しておきましょう

33 解説 :MySQL のスキーマ オブジェクトの設計と作成 ビューやテーブルなどの定義は SHOW CREATE TABLES(VIEWS) や INFORMATION_SCHEMA.TABLES(VIEWS) から確認することができます 例 ) ビューの定義を確認する mysql> show create view schema.view_name; または mysql> select view_definition from information_schema.views -> where table_name='all_emp' -> and table_schema='schema_name';

34 Q10:MySQL のストアド プログラムの設計 作成 使用 トリガーに関する説明について 正しい選択肢を 2 つ選んでください 1. INSERTトリガーでは NEWキーワードが利用できる 2. DELETEトリガーでは NEWキーワードが利用できる 3. INSERTトリガーでは OLDキーワードが利用できる 4. DELETEトリガーでは OLDキーワードが利用できる 5. INSERTトリガーは NEWキーワードも OLDキーワードも利用できる 6. DELETEトリガーは NEWキーワードも OLDキーワードも利用できる

35 A10:MySQL のストアド プログラムの設計 作成 使用 トリガーに関する説明について 正しい選択肢を 2 つ選んでください 1. INSERTトリガーでは NEWキーワードが利用できる 2. DELETEトリガーでは NEWキーワードが利用できる 3. INSERTトリガーでは OLDキーワードが利用できる 4. DELETEトリガーでは OLDキーワードが利用できる 5. INSERTトリガーは NEWキーワードも OLDキーワードも利用できる 6. DELETEトリガーは NEWキーワードも OLDキーワードも利用できる

36 解説 MySQL のストアド プログラムの設計 作成 使用 トリガーの特性を確認しておきましょう トリガー本体内で OLD 読み取り専用 および NEW SELECT権限が必須 キーワードを使用することで トリガーの影響を受ける行のカラムにアクセスできる INSERT トリガー内では NEW.col_name だけを使用できる DELETE トリガーでは OLD.col_name だけを使用できる トリガーを破棄するには DROP TRIGGER ステートメントを使用します mysql> DROP TRIGGER test.ins_sum; テーブルを削除すると そのテーブルのトリガーもすべて削除されます 参考

37 Q11:MySQL のストアド プログラムの設計 作成 使用 ストアド プロシージャに関する説明について 正しい選択肢を 2 つ選んでください 1. ストアド ルーチンの実行時 SQL_MODE はルーチン定義時に設定した SQL_MODE が選択される 2. ストアド ルーチンの実行時 SQL_MODE はルーチン実行時に設定されている SQL_MODE が選択される 3. エラーメッセージや状態などの詳細情報は 診断領域から確認できる 4. エラーメッセージや状態などの詳細情報は ハンドラ領域から確認できる

38 A11:MySQL のストアド プログラムの設計 作成 使用 ストアド プロシージャに関する説明について 正しい選択肢を 2 つ選んでください 1. ストアド ルーチンの実行時 SQL_MODE はルーチン定義時に設定した SQL_MODE が選択される 2. ストアド ルーチンの実行時 SQL_MODE はルーチン実行時に設定されている SQL_MODE が選択される 3. エラーメッセージや状態などの詳細情報は 診断領域から確認できる 4. エラーメッセージや状態などの詳細情報は ハンドラ領域から確認できる

39 解説 MySQLのスキーマ オブジェクトの設計と作成 MySQL は ルーチンが作成または変更されたときの 有効な sql_mode システム変数の設定を格納します ルーチンが実行を開始したとき 現在のサーバー SQL モードには関係なく 常にそのルーチンを強制的にこの設定で実行します 参考

40 解説 MySQL のストアド プログラムの設計 作成 使用 MySQL の診断領域とは SQL ステートメントは 診断領域を移入する診断情報を生成 診断領域には 発生した条件の数や 影響を受けた行数などの ステートメント情報や エラーコードやメッセージなどの条件情報を含む 診断領域から特定の項目を抽出するためには GET DIAGNOSTICS あるいは条件またはエラーを確認するための SHOW WARNINGS または SHOW ERRORS を使用して検査できる

41 Q12:MySQL のストアド プログラムの設計 作成 使用 ストアド ファンクションの作成を行おうとしたところ 作成エラーになりました エラーになった理由について正しく述べている選択肢を 1 つ選んでください 1. ファンクションTfuncの引数が空である 2. ユーザ定義変数 countが初期化されていない 3. 関数の戻り型が定義されていない 4. ユーザ変数 countは予約語のため 使えない CREATE FUNCTION TFunc () BEGIN DECLARE count int; SELECT COUNT(*) INTO count FROM test; RETURN count; END

42 A12:MySQL のストアド プログラムの設計 作成 使用 ストアド ファンクションの作成を行おうとしたところ 作成エラーになりました エラーになった理由について正しく述べている選択肢を 1 つ選んでください 1. ファンクションTfuncの引数が空である 2. ユーザ定義変数 countが初期化されていない 3. 関数の戻り型が定義されていない 4. ユーザ変数 countは予約語のため 使えない CREATE FUNCTION TFunc () BEGIN DECLARE count int; SELECT COUNT(*) INTO count FROM test; RETURN count; END

43 解説 MySQL のストアド プログラムの設計 作成 使用 mysql> delimiter // mysql> CREATE FUNCTION TFunc () -> RETURNS int -> BEGIN -> DECLARE count int; -> SELECT COUNT(*) INTO count FROM test; -> RETURN count; -> END// Query OK, 0 rows affected (0.01 sec) FUNCTIONには RETURNS 句が必須 (関数の戻り型を定義する 関数本体には RETURN value ステートメントが 含まれている必要がある RETURN ステートメントが異なる型の値を 返した場合 その値は正しい型に強制的に 変更される mysql> delimiter ;

44 解説 MySQL のストアド プログラムの設計 作成 使用 テスト対策では ループ処理などのプログラムの解析や 作成方法も理解しておきましょう WHILE 構文 LOOP構文 CREATE PROCEDURE dowhile() BEGIN DECLARE v1 INT DEFAULT 5; CREATE PROCEDURE doiterate(p1 INT) BEGIN label1: LOOP SET p1 = p1 + 1; 無限ループも可能 IF p1 < 10 THEN 通常は ITERATE label1; LEAVEステートメントで END IF; ループを抜ける LEAVE label1; END LOOP label1; = p1; END; WHILE v1 > 0 DO... SET v1 = v1-1; END WHILE; END; 式 v1>0)が true で あるかぎり 繰り返す

45 Q13: データに対する問合せ Test 表からデータを検索するため 以下のクエリを発行しました Name 列のどの値が返ってきますか 選択肢から 1 つ選んでください select id, name from test where name = 'a' or name = 'c' and id < 3; 1. a 2. a,c 3. a,b,c 4. a,b,c,d mysql> select * from test; ID Name 1 a 2 b 3 c 4 d 4 rows in set (0.00 sec)

46 A13: データに対する問合せ Test 表からデータを検索するため 以下のクエリを発行しました Name 列のどの値が返ってきますか 選択肢から 1 つ選んでください select id, name from test where name = 'a' or name = 'c' and id < 3; 1. a 2. a,c 3. a,b,c 4. a,b,c,d mysql> select * from test; ID Name 1 a 2 b 3 c 4 d 4 rows in set (0.00 sec)

47 解説 : データに対する問い合わせ 演算子の優先順位を確認しておきましょう mysql> select * from test; ID Name 1 a 2 b 3 c 4 d 4 rows in set (0.00 sec) mysql> select id, name from test -> where name = 'a' or name = 'c' -> and id < 3; id name 1 a 1 row in set (0.00 sec) and は or の条件より優先されるため c は条件に合わず 結果から除外されています

48 Q14: データに対する問合せ Test 表にはデータが 4 件あります Name 列で並び替えた結果の 2 位 ~3 位までの 2 件を取得したいと考えています どの SQL 文を使用したらよいでしょうか 1 つ選択してください 1. select Name from test order by Name Limit 2, 3 2. select Name from test order by Name Limit 1, 2 3. select Name from test order by Name Limit 2, 1 4. select Name from test order by Name Skip 2, 1 mysql> select * from test; ID Name 1 a 2 b 3 c 4 d 4 rows in set (0.00 sec)

49 A14: データに対する問合せ Test 表にはデータが 4 件あります Name 列で並び替えた結果の 2 位 ~3 位までの 2 件を取得したいと考えています どの SQL 文を使用したらよいでしょうか 1 つ選択してください 1. select Name from test order by Name Limit 2, 3 2. select Name from test order by Name Limit 1, 2 3. select Name from test order by Name Limit 2, 1 4. select Name from test order by Name Skip 2, 1 mysql> select * from test; ID Name 1 a 2 b 3 c 4 d 4 rows in set (0.00 sec)

50 解説 データに対する問い合わせ 結果セットから指定した数の行のみが必要な場合 LIMIT句が役に立ちます mysql> select * from test; ID Name 1 a 2 b 3 c 4 d 4 rows in set (0.00 sec) mysql> select Name from test order by Name Limit 1, 2; Name b c rows in set (0.00 sec) 最初の1行を スキップし 次の2行を返す LIMIT [M,]N; キューから最初の N 行を返します (M が指定されている場合 最初の M 行をスキップし 次の N 行を返します )

51 解説 : データに対する問い合わせ こんな使い方もできます こんな使い方はできません! mysql> select * from test order by name limit 2; ID Name 1 a 2 b 2 rows in set (0.00 sec) mysql> select * from test -> order by name -> limit -> (select count(*) -1 from test); LIMIT のパラメータを副問い合わせにする LIMIT は単一の表ならば削除構文でも使用できます Ex) DELETE FROM test ORDER BY name LIMIT 2; 使用方法はマニュアルでしっかり確認しておきましょう

52 解説 : データに対する問い合わせ ワンポイント + * を使用した全列検索は極力避け 必要な列だけの検索にしましょう 必要ではない列が ディスクから読み込まれる可能性があります 必要ではない列が クライアントとサーバー間で送受信されることになります 表の構造が変わった際に プログラムを更新しなくてはならない可能性があります + 並び替えは明示的に ORDER BY 句を使って行いましょう + 検索に使用出来る便利な関数がたくさんあります concat, substr, cast, lower などの主要な関数については一通り 動作を確認しておきましょう

53 Q15: データに対する問い合わせ TestSummary 表のデータを確認してください 以下のクエリで Sal 列の平均を求めました 結果はいくつでしょうか 1 つ選択してください select avg(sal) from TestSummary; NULL 4. 0 mysql> select * from TestSummary; ID Sal NULL 4 NULL 4 rows in set (0.00 sec)

54 Q15: データに対する問い合わせ TestSummary 表のデータを確認してください 以下のクエリで Sal 列の平均を求めました 結果はいくつでしょうか 1 つ選択してください select avg(sal) from TestSummary; NULL 4. 0 mysql> select * from TestSummary; ID Sal NULL 4 NULL 4 rows in set (0.00 sec)

55 解説 : データに対する問い合わせ Null のデータは無視され 平均が求められることに注意してください mysql> select * from TestSummary; ID Sal NULL 4 NULL 4 rows in set (0.00 sec) mysql> Select avg(sal) from TestSummary; avg(sal) ( ) /2 = row in set (0.00 sec)

56 Q16: データの修正 ORDER_ITEM 表の ORDER_ID 列には ORDERS 表の ID 列に対する外部キー制約が ON DELETE CASCADE を指定して設定されています DETELE FROM ORDERS WHERE ID=1 が発行されたら どうなりますか mysql> select * from ORDERS; id rows in set (0.00 sec) mysql> select * from ORDER_ITEM order by ID, ORDER_ID; id order_id rows in set (0.00 sec)

57 Q16: データの修正 1. ORDERS 表から ID=1 のデータが削除されるが ORDER_ITEM 表からは削除されない 2. ORDERS 表から ID=1 のデータが削除され ORDER_ITEM 表の ID=1 である 3 行のデータは Null に変わる 3. ORDERS 表から ID=1 のデータが削除され ORDER_ITEM 表からも ID=1 である 3 行のデータが削除される 4. 何も削除できず エラーになる

58 A16: データの修正 1. ORDERS 表から ID=1 のデータが削除されるが ORDER_ITEM 表からは削除されない 2. ORDERS 表から ID=1 のデータが削除され ORDER_ITEM 表の ID=1 である 3 行のデータは Null に変わる 3. ORDERS 表から ID=1 のデータが削除され ORDER_ITEM 表からも ID=1 である 3 行のデータが削除される 4. 何も削除できず エラーになる

59 解説 データの修正 解答 ON DELETE CASCADEの動作(実行後 mysql> select * from ORDERS; id ID=1が 削除された rows in set (0.00 sec) mysql> select * from ORDER_ITEM; id order_id ORDER_ID=1も 1 2 削除されている この問題ではON DELETE CASCADE 赤字 の 動作を問われています 外部キーに関する参照アクションの仕様は ひととおり 確認しておきましょう e-table-foreign-keys.html 参照アクション UPDATE操作 RESTRICT 親テーブルに対する削除または更新操作を拒否 (エラー CASCADE 参照先の変更に追従 参照先がなくなると 削除 SET NULL NULLに変わる NULLに変わる DELETE操作

60 解説 : データの修正 mysql> select * from ORDERS; id rows in set (0.00 sec) mysql> select * from ORDER_ITEM; id order_id rows in set (0.00 sec) 外部キーがなければ 一度に複数テーブルからデータを削除することもできます 例 :ORDERS.ID=1 のデータを削除する DELETE ORDERS, ORDER_ITEM FROM ORDERS, ORDER_ITEM WHERE ORDERS.id = ORDER_ITEM.order_id AND ORDERS.id = 1; 参考 : e.html

61 Q17: 結合 副問い合わせ UNION 従業員のデータが格納された employee 表と 給与が格納された salary 表の 2 つのテーブルがあります ID 列は両テーブルとも int 型で定義されています mysql> select * from employee; id name SMITH 2 BOB 3 TOM employee 表と salary 表を結合し 給与を取得したいと思います 使用出来る結合方法について 正しい選択肢を 2 つ選択してください 1. NATURAL JOIN で結合できる 2. NATURAL JOIN では結合できない 3. INNER JOIN で結合できる 4. INNER JOIN では結合できない mysql> select * from salary; id sal

62 A17: 結合 副問い合わせ UNION 従業員のデータが格納された employee 表と 給与が格納された salary 表の 2 つのテーブルがあります ID 列は両テーブルとも int 型で定義されています mysql> select * from employee; id name SMITH 2 BOB 3 TOM employee 表と salary 表を結合し 給与を取得したいと思います 使用出来る結合方法について 正しい選択肢を 2 つ選択してください 1. NATURAL JOIN で結合できる 2. NATURAL JOIN では結合できない 3. INNER JOIN で結合できる 4. INNER JOIN では結合できない mysql> select * from salary; id sal

63 解説 : 結合 副問い合わせ UNION NATURAL [LEFT] JOIN は 結合列が同一列名で 同一定義であれば使用できます 両方のテーブル内に存在するすべてのカラムを指定する USING 句を含む INNER JOIN または LEFT JOIN と意味的に同等であるとして定義されます NATURAL JOIN mysql> select * from employee -> natural join salary; id name sal SMITH BOB TOM INNNER JOIN mysql> select employee.id, -> employee.name, salary.sal -> from employee -> inner join salary on -> employee.id = salary.id; id name sal SMITH BOB TOM

64 解説 : 結合 副問い合わせ UNION LEFT JOIN と RIGHT JOIN( 結合する相手の行がなくても表示する ) も理解しておきましょう mysql> select * from employee; id name SMITH 2 BOB 3 TOM 4 WHO LEFT JOIN select employee.id, employee.name, salary.sal from employee left join salary on employee.id = salary.id; mysql> select * from salary; id sal RIGHT JOIN select employee.id, employee.name, salary.sal from employee right join salary on employee.id = salary.id;

65 Q18: 結合 副問い合わせ UNION employee 表 employee_jp 表の 全件を取得します mysql> select * from employee; id name SMITH 2 BOB 列名は employee 表に合わせたいと思います 以下の選択肢のうち 正しい SQL はどれですか 1 つ選択してください 1. SELECT * FROM emoloyeejp UNION SELECT * FROM emoloyee; 2. SELECT * FROM emoloyee UNION SELECT * FROM emoloyeejp; 3. 列名が異なるので検索はできない 4. 列の数が合っていないので検索はできない mysql> select * from employeejp; jpid jpname TOHGO 102 ZENIGATA

66 A18: 結合 副問い合わせ UNION employee 表 employee_jp 表の 全件を取得します mysql> select * from employee; id name SMITH 2 BOB 列名は employee 表に合わせたいと思います 以下の選択肢のうち 正しい SQL はどれですか 1 つ選択してください 1. SELECT * FROM emoloyeejp UNION SELECT * FROM emoloyee; 2. SELECT * FROM emoloyee UNION SELECT * FROM emoloyeejp; 3. 列名が異なるので検索はできない 4. 列の数が合っていないので検索はできない mysql> select * from employeejp; jpid jpname TOHGO 102 ZENIGATA

67 解説 : 結合 副問い合わせ UNION UNION に関する仕様も整理しておきましょう UNION は 複数の SELECT ステートメントからの結果を 1 つの結果セットに結合するために使用します 最初の SELECT ステートメントからのカラム名が 返される結果のカラム名として使用されます ( カラム数が異なるとエラー ) 対応する SELECT カラムのデータ型が一致しない場合 UNION の結果内のカラムの型と長さは すべての SELECT ステートメントによって取得された値を考慮に入れて決定されます UNION のデフォルトの動作 (UNION DISTINCT) では 重複した行が結果から削除されます UNION ALL 重複も含めて結合 UNION DISTINCT 重複した行の削除 ( デフォルト動作と同様 )

68 Q19:MySQL アプリケーションの開発 memcached NoSQL API を使用する際の説明や利点 欠点について 誤った選択肢を 1 つ選んでください 1. my.cnf に memcached オプションを加える 2. libmemcached.so(mysql に対する memcached デーモンプラグイン ) を有効化する 3. libevent がインストールされている必要がある 4. SQL をバイパスした より高速なアクセスが可能になる 5. Memcached は MySQL の認証メカニズムを使用しないことに注意が必要

69 A19:MySQL アプリケーションの開発 memcached NoSQL API を使用する際の説明や利点 欠点について 誤った選択肢を 1 つ選んでください 1. my.cnf に memcached オプションを加える 2. libmemcached.so(mysql に対する memcached デーモンプラグイン ) を有効化する 3. libevent がインストールされている必要がある 4. SQL をバイパスした より高速なアクセスが可能になる 5. Memcached は MySQL の認証メカニズムを使用しないことに注意が必要

70 解説 :A19:MySQL アプリケーションの開発 Memcached API MySQL の SQL の解析レイヤーおよび最適化レイヤーを通過しないため 同等の SQL クエリーよりも高速 MySQL の認証メカニズムが使用されません memcached に必要な libevent がインストールされている必要があります デーモンプラグインを有効化する必要があります mysql> install plugin daemon_memcached soname "libmemcached.so";

71 Q20: 基本的な最適化 この問い合わせを最適化するには 何を行うと効果的でしょうか 1 つ選択してください mysql> explain -> select ORDERS.id, ORDER_ITEM.order_id -> from ORDERS -> inner join ORDER_ITEM on ORDERS.id = ORDER_ITEM.order_id -> where ORDER_ITEM.order_id = 1 order by ORDERS.id G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: ORDERS type: const possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: const rows: 1 Extra: Using index *************************** 2. row *************************** id: 1 select_type: SIMPLE table: ORDER_ITEM type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 6 Extra: Using where select ORDERS.id, ORDER_ITEM.order_id from ORDERS inner join ORDER_ITEM on ORDERS.id = ORDER_ITEM.order_id where ORDER_ITEM.order_id = 1 order by ORDERS.id G a. ORDERS.id 列に索引を作成する b. ORDER_ITEM.order_id に索引を作成する c. オプティマイザ ヒント FORCE INDEX (PRIMARY) を指定する d. STRAIT JOIN を使って結合する

72 A20: 基本的な最適化 この問い合わせを最適化するには 何を行うと効果的でしょうか 1 つ選択してください mysql> explain -> select ORDERS.id, ORDER_ITEM.order_id -> from ORDERS -> inner join ORDER_ITEM on ORDERS.id = ORDER_ITEM.order_id -> where ORDER_ITEM.order_id = 1 order by ORDERS.id G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: ORDERS type: const possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: const rows: 1 Extra: Using index *************************** 2. row *************************** id: 1 select_type: SIMPLE table: ORDER_ITEM type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 6 Extra: Using where select ORDERS.id, ORDER_ITEM.order_id from ORDERS inner join ORDER_ITEM on ORDERS.id = ORDER_ITEM.order_id where ORDER_ITEM.order_id = 1 order by ORDERS.id G a. ORDERS.id 列に索引を作成する b. ORDER_ITEM.order_id に索引を作成する c. オプティマイザ ヒント FORCE INDEX (PRIMARY) を指定する d. STRAIT JOIN を使って結合する

73 解説 : 基本的な最適化 mysql> explain -> select ORDERS.id, ORDER_ITEM.order_id -> from ORDERS -> inner join ORDER_ITEM on ORDERS.id = ORDER_ITEM.order_id -> where ORDER_ITEM.order_id = 1 order by ORDERS.id G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: ORDERS type: const possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: const rows: 1 Extra: Using index *************************** 2. row *************************** id: 1 select_type: SIMPLE table: ORDER_ITEM type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 6 Extra: Using where type:all はフルテーブルスキャンです インデックスを追加することで ALL の回避を検討しましょう possible_keys: 選択可能な索引 key: MySQL によって選択された索引 key_len: 選択された索引の長さ ref: 索引と比較される列 rows: 調査する行の見積 2 つ目の表は 選択可能な索引がありません

74 解説 :A20 基本的な最適化 mysql> create index idx_order_item on ORDER_ITEM(ORDER_ID); Query OK, 0 rows affected (0.27 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> explain select ORDERS.id, ORDER_ITEM.order_id from ORDERS inner join ORDER_ITEM on ORDERS.id = ORDER_ITEM.order_id where ORDER_ITEM.order_id = 1 order by ORDERS.id G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: ORDERS type: const possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: const rows: 1 Extra: Using index *************************** 2. row *************************** id: 1 select_type: SIMPLE table: ORDER_ITEM type: ref possible_keys: idx_order_item key: idx_order_item key_len: 4 ref: const rows: 3 Extra: Using index 索引追加後 実行計画が変わり type:all が回避できています rows: 調査する行の見積も減っており最適化されていることが確認できます