xM6GqtsP

1# MySQL基本操作
2
3## ユーザー・アカウント関連
4### SQLサーバーへのログイン
5` mysql -u user -p  `  
6`password `
7
8### rootのパスワード変更
9`SET PASSWORD FOR root@localhost=PASSWORDS('password');`  
10
11→この後にMAMPの`C:\MAMP\MAMP.exe.config`の9行目付近のパスワードも変更しておく
12
13## ユーザー作成
14`CREATE USER user IDENITFIED BY 'new-password';`
15
16## ユーザー権限を設定
17`GRANT 権限(全てならALL) ON db.table TO user;`
18
19----
20
21## 超基本操作
22
23### データベース作成
24`CREATE DATABASE db;`
25
26### データベースの一覧表示
27`SHOW DATABASE;`
28
29### データベースの指定
30`use db`
31
32### 現在のデータベースの確認
33`SELECT DATABASE();`
34
35### テーブルの作成
36`CREATE TABLE table(column1 type1,column2 type2,.....);`
37
38### テーブルの一覧表示
39`SHOW TABLES;`
40
41### テーブルのカラム構造確認
42`DESC table;`
43
44### データの挿入
45`INSERT INTO table VALUES(data1,data2,...);`
46
47### 順番関係ない挿入
48`INSERT INTO table (column1,column2,....) VALUES(data1,data2,....);`
49
50### たくさん入力
51`INSERT INTO table (column1,column2,....) VALUES(data1,data2,....),(data1,data2,....),(data1,data2,....),......;`
52
53### 特定のカラムのデータの表示
54`SELECT column1,column2,.... FROM table;`
55
56### すべてのデータの表示
57`SELECT * FROM table;`
58
59### テーブルのコピー
60`CREATE TABLE new-table SELECT * FROM table;`
61
62----
63
64## テーブルの改造
65### カラムの定義変更
66`ALTER TABLE table MODIFY column type  ;`
67
68### カラムの追加
69`ALTER TABLE table ADD column type;  `
70
71### カラムの名前と定義変更
72`ALTER TABLE table CHANGE before-column after-column after-type;`
73
74### カラムを削除
75`ALTER TABLE table DROP column;`
76
77### カラムの位置変更
78#### カラムの先頭に追加
79`ALTER TABLE table ADD column type FIRST;`
80#### カラムの自由な位置に追加
81`ALTER TABLE table ADD column1 type AFTER column2;`
82#### カラム順の変更
83`ALTER TABLE table MODIFY column type FIRST;`
84
85----
86
87## 主キー・連続番号
88
89### 主キー(被り＆NULLなし)を設定してテーブル作成
90`CREATE TABLE table (column type PRIMARY KEY,...);`
91
92### 一意キー(被りなし)を設定してテーブル作成
93`CREATE TABLE table (column type UNIQUE,...);`
94
95### 自動的に連続版番号
96`CREATE TABLE table (column1 INT AUTO_INCREMENT PRIMARY KEY,column2 type2...);`
97
98### 連続番号の初期化
99`ALTER TABLE table AUTO_INCREMENT = num;`
100
101----
102
103## デフォルト値
104
105### カラムのデフォルト値
106#### 作るとき
107`CREATE TABLE table column type DEFAULT default;`
108#### 変更するとき
109`ALTER TABLE table MODIFY column type DEFAULT default;`
110
111----
112
113## インデックス
114### インデックスの作成
115`CREATE INDEX index ON table(column);`
116### インデックスの確認
117`SHOW INDEX FROM table (; or \G)`
118### インデックスの削除
119`DROP INDEX index ON table;`
120
121----
122
123## テーブルやレコードのコピー
124### テーブルのカラム構造とデータをコピー
125** AUTO_INCREMENTなどの一部の属性はコピーされない（後で設定する必要がある) **  
126** DESCなどで確認してから使う **   
127`CREATE TABLE new-table SELECT * FROM before-table;`
128`
129
130### テーブルのカラム構造をコピー
131`CREATE TABLE new-table LIKE before-table;`
132
133### 他のテーブルのデータをコピー(同じカラム構造の場合)
134`INSERT INTO table SELECT * FROM before-table;`
135
136### 特定のカラムを選択してコピー
137`INSERT INTO table (column) SELECT before-column FROM before-table;`
138
139----
140
141## テーブルやレコードの削除
142### テーブルの削除
143`DROP TABLE table;`
144
145#### 指定のテーブルがある場合には削除する
146IF EXISTSをつけると目的のテーブルが存在しない状況でエラーが出なくなる→テーブルの存在にかかわらず削除できる  
147`DROP TABLE IF EXISTS table;`
148
149### データベースの削除
150`DROP DATABASE db;`
151
152### テーブル内のレコード(データ)のみ削除
153`DELETE FROM table;`
154
155----
156
157## 抽出
158### カラム入れ替えて表示
159` SELECT column2,column1 FROM table; `
160
161### エイリアスを指定して表示
162エイリアスは日本語ok  
163`()`とかを使うときは`""`でエイリアスをくくるといい  
164` SELECT column AS alias FROM table; `
165#### 複数の場合
166` SELECT column1 AS alias1,column2 AS alias2 FROM table; `
167
168### カラムの値を計算して表示
169` + - * / ` で四則演算  
170`DIV`で結果が整数の割り算  
171`%`,`MOD`で余り  
172
173`SELECT column * 100 FROM db`
174
175### カラムの値を関数で計算して表示
176`AVG(column)` 平均値  
177`SUM(column)` 合計値  
178`COUNT(column)` 個数  
179
180`SELECT AVG(column) FROM db;`
181### 情報表示の関数
182`PI()` 円周率  
183`VERSION()` MySQLのバージョン  
184`DATABASE()` 現在のデータベース  
185`USER()` 現在のユーザー  
186`CHAESET('text')` 引数で指定した文字の文字エンコーディング表示  
187
188`SELECT PI();`
189
190## 文字列操作関数
191`text`を`column`にして`from db`をつけるとカラムのデータをいい感じにしてくれる()  
192  
193`CONCAT(text1,text2,text3,...)` 文字列を連結  
194`RIGHT(text,num)` 右からnum文字取る  
195`LEFT(text,num)` 左からnum文字取る  
196`SUBSTRING(text,x,y)` x番目からy文字取る  
197`REPEAT(text,num)` textをnum回繰り返す  
198`REVERSE(text)` textを逆向きに表示
199
200`SELECT CONCAT(column1,'+',column2,'=aa') FROM table;`
201> column1-1+column2-2=aa
202
203## 日付、時刻の関数
204`NOW()` 現在の時刻  
205
206## 条件指定
207全部`FROM table`の後に付け加える  
208
209`LIMIT num` num件だけ表示  
210`WHERE 条件` 条件をしてして表示
211
212### 等しい
213`WHERE column = num`
214### 異なる
215`WHERE column <> num`
216### 比較
217`>  <  >=  <=`  
218### 指定
219`WHERE column IN (num1,num2)` num1,num2  
220`WHERE column NOT IN (num1,num2)` num1,num2以外
221### 範囲指定
222`WHERE column BETWEEN num1 AND num2` num1<=x<=num2  
223`WHERE column NOT BETWEEN num1 AND num2` num1>x , x>num2  
224
225## 文字列で条件指定
226### 完全一致
227`WHERE column = 'text'` 完全一致  
228### あいまい検索
229`WHERE column LIKE 'aiu'` aiuを含むもの  
230`WHERE column NOT LIKE 'aiu'` aiuを含まないもの  
231#### LIKEのワイルドカード
232`%` 任意の文字列  
233`_` 任意の一文字  
234
235### NULL検索
236`WHERE column IS NULL` NULLのもの  
237`WHERE column IS NOT NULL` NULLじゃないもの  
238### 重複なし表示
239`SELECT DISTINCT column FROM table` 重複している項目を省いて表示
240
241### AND OR
242`AND`と`OR`で条件を組み合わせられる  
243※ANDのほうが優先順位高いからそうしたくないときは  
244`WHERE (A OR B) AND C` みたいにしたらok
245
246### CASE WHEN
247条件1のときはtext1を表示し、条件2のときはtext2を表示し、それ以外はtext3を表示  
248```
249
250SELECT
251CASE
252  WHEN 条件1 THEN text1
253  WHEN 条件2 THEN text2
254  ELSE text3
255END
256FROM table;
257  
258```
259
260#### AS使って圧倒的成長()
261```
262
263SELECT column1,column2
264CASE
265  WHEN 条件1 THEN text1
266  WHEN 条件2 THEN text2
267  ELSE text3
268END AS column2-review
269FROM table;
270  
271```
272  
273例  
274
275| column1 | column2 | column2-review |
276| :---| :--- |:--- |
277| 1-1 | 2-1 |text2 |
278| 1-2 | 2-1 |text1 |
279
280----
281
282## 並び替え
283`FROM table` の後につける  
284### 昇順
285`ORDER BY column` or `ORDER BY column ASC`   
286### 降順
287`ORDER BY column DESC`   
288
289### 上位３つ
290`LIMIT 3 OFFSET 4` なら 5,6,7  
291`LIMIT 3 OFFSET 0` なら 1,2,3  
292`SELECT * FROM table ORDER BY column LIMIT 3 OFFSET 0;`  
293
294----
295
296## グループ
297あるカラムに同じ値が入ってるレコードが複数ある場合に、それらを**グループ**にすることができる。
298### グループごとに表示
299`SELECT * FROM table GROPU BY column;`  
300※グループ化されたものは適当なレコードが選ばれる
301### グループごとの数
302`SELECT COUNT(*) FROM table GROUP BY column`  
303わかりやすく↓  
304`SELECT column,COUNT(*) FROM table GROUP BY column`  
305  
306`SUM()`や`AVG()`でグループの合計値や平均値がわかる  
307
308### 条件つきグループ
309グループ化した後の条件 `HAVING`  
310グループ化前の条件     `WHERE`
311
312#### グループ化してから抽出
313例:`SELECT column1 SUM(column2) FROM table GROUP BY column1 HAVING SUM(column2) >= 200;`  
314column2の合計をグループごとに出し,合計が200以上のグループのみを表示
315#### 抽出してからグループ化
316例:`SELECT column1 SUM(column2) FROM table WHERE column2>=200 GROUP BY column1;`  
317column2の200以上のものを抽出し、それらの中からグループ化を行う。
318
319#### グループ化してから並び替え
320`SELECT column1,AVG(column2) GROUP BY column1 ORDER BY AVG(column2)`
321
322----
323
324## データの更新
325### カラム内のすべてのデータの更新
326`UPDATE table SET column = value;`  
327※UPDATEコマンドは普通は条件を指定して行う。MySQLモニタ起動時に`--safe-updates`を指定すると、WHERE条件のないUPDATEやDELETEができなくなる。
328### 条件指定してレコードのカラム修正
329`UPDATE table SET column = value WHERE 条件`  
330ORDERしてLIMITしたら上から何人のデータを修正とかできる()
331
332## 条件指定してコピー
333`CREATE TABLE new-table SELECT * FROM table WHERE column 条件`  
334`INSERT INTO another-table SELECT * FROM table WHERE ~`
335
336## 条件指定してレコード削除
337`DELETE FROM table WHERE ~`  
338
339----
340## 複数のテーブルを並べて表示
341### 同じカラム構造のテーブルを合わせて表示
342`UNION`だと重複してる項目は省いてくれる  
343`SELECT * FROM table1 UNION SELECT * FROM table2;`  
344わかりやすい  
345`(SELECT * FROM table1) UNION (SELECT * FROM table2);`  
346  
347`UNION ALL`なら省かなくなる。大量のデータがあるときは`ALL`をつけたほうが処理が早い  
348`(SELECT * FROM table1 WHERE ~) UNION ALL (SELECT * FROM table2 WHERE ~);`
349
350----
351
352### テーブルの結合(内部結合)
353一致するレコード**だけ**を取り出す→内部結合  
354`SELECT * FROM table1 JOIN table2 ON table1.column1 = table2.column1`  
355本当は内部結合は`INNER JOIN`だけど`INNER`は省略可能  
356`SELECT * FROM table1 INNER JOIN table2 ON table1.column1 = table2.column1`  
357  
358表示するカラムを指定するときは  
359`SELECT table1.column1,table2.column2,.... ~`  
360みたいに`テーブル名.カラム名`で指定する
361
362### テーブル名のエイリアス
363`table AS alias`  
364こんなかんじで使う（IQ2)  
365`SELECT t1.a t2.b FROM table1 AS t1 JOIN table2 AS t2 ON t1.num = t2.num;`
366
367### 結合のキーになるカラム名が同じなら
368`ON table1.column = table2.column`じゃなくて  
369`USING(column)`でok
370
371### 結合後の抽出
372`ON table1.a = table2.b WHERE table1.num > 200;`
373
374----
375
376### テーブルの結合(外部結合)
377一致していなくても一方のレコードは**全て**取り出す→外部結合  
378左外部結合:一致したレコードと左側(結合する側)の全データを表示  
379右外部結合:一致したレコードと右側(結合される側)の全データを表示  
380
381一致していないところはNULLで表示される  
382
383å·¦:`SELECT column FROM table1 LEFT JOIN table2 ON table1.a = table2.a;`  
384右:`SELECT column FROM table1 RIGHT JOIN table2 ON table1.a = table2.a;`
385
386※同じDB内で左外部結合と右外部結合の混在は避ける  
387※`LEFT JOIN` = `LEFT OUTER JOIN`  
388※`RIGHT JOIN` = `RIGHT OUTER JOIN`
389
390----
391### 自己結合
392すべての組み合わせが作られる  
393`SELECT * FROM table AS a JOIN table AS b;`  
394
395得点で順位を決める  
396```
397SELECT a.name,a.score,COUNT(*) 
398FROM table AS a 
399JOIN table AS b 
400WHERE a.score <= b.score
401GROUP BY a.name;
402```
403
404----
405
406## サブクエリ
407サブクエリは`()`で囲む  
408
409**該当するレコードが二件以上ある場合にエラーが出るので`=` じゃなくて`IN`を使う**
410### 最大値のレコードを表示
411サブクエリは値を返す  
412`SELECT * FROM table WHERE num IN (SELECT MAX(num) FROM table);`
413
414
415### 別のテーブルからnumが100以上のレコードを見つけてそれとnameが同じものを表示同じものを表示
416サブクエリはカラムを返す  
417`SELECT * FROM table1 WHERE name IN (SELECT name FROM table2 WHERE num>=100);`
418
419### EXISTS
420tableにtable1にあるものが存在するなら、存在するものを表示する  
421`SELECT * FROM table1 WHERE EXISTS (SELECT * FROM table WHERE table.a=table1.a);`  
422
423### NOT EXISTS
424tableにtable1にあるものが存在しないなら、存在しないものを表示する  
425`SELECT * FROM table1 WHERE NOT EXISTS (SELECT * FROM table WHERE table.a=table1.a);`  
426
427----
428
429## ビュー
430ビューを変更するとテーブルの値も変更される  
431基本的にテーブルと使い方同じ  
432片方を変更するともう片方も自動的に変更される  
433### ビューの作成
434`CREATE VIEW view AS SELECT column FROM table WHERE ~`
435
436### ビューにINSERT
4371つのテーブルからカラムを抜き出して作ったビューならINSERTもUPDATEもできる  
438UNIONやJOINやサブクエリを使ったものならINSERTやJOINはできない
439
440### ビューのWHERE条件に合わないINSERT
441成功するけどビューには表示されずテーブルに追加される
442
443### ビューのWHERE条件に合わないINSERTをエラーにする
444ビューを作るときに`WITH CHECK OPTION`をつける  
445`CREATE VIEW view AS SELECT column FROM tableWHERE 条件 WITH CHECK OPTION;`
446
447### ビューの上書き
448同じ名前のビューを条件を変えて作るときなどは`OR REPLACE`をつけると上書きになる  
449`CREATE OR REPLACE VIEW view AS ~~`
450
451### ビューの定義変更
452`ALTER VIEW view AS SELECT column FROM table;`
453
454### ビューの削除
455`DROP VIEW view;`
456
457----
458
459## ストアドプロシージャ
460一連のSQLコマンドの塊に名前をつける→関数みたい   
461### ストアドプロシージャの作成
462`;`を入力した時点で実行されるからデリミタの変更をする  
463
464`delimiter //`  
465これした後はENDの後に`//`をつけるとoK  
466ストアドプロシージャを作った後はデリミタを元に戻す  
467`delimiter ;`
468
469
470```
471CREATE PROCEDURE procedure()
472
473BEGIN
474  処理;
475  処理;
476END
477```
478
479作った後に呼ぶとき  
480`CALL procedure;`
481
482### 引数ありのストアドプロシージャ
483`CREATE PROCEDURE procedure(parameter type);`  
484`CALL procedure(value);`
485
486### ストアドプロシージャの内容の表示
487`SHOW CREATE PROCEDURE procedure;`
488
489### ストアドプロシージャの削除
490`DROP PROCEDURE procedure;`
491
492----
493
494## ストアドファンクション
495返り値があるストアドプロシージャ  
496設定で使えるようにする  
497`SET GLOBAL log_bin_trust_function_creators=1;` 
498
499### ストアドファンクションの作成
500```
501CREATE FUNCTION function(parameter type) RETURN return-type
502BEGIN
503  SQL-text;
504  RETURN value;
505  
506END
507
508```  
509
510変数に代入  
511`value INTO parameter`
512
513### 表示
514`SHOW CREATE FUNCTION function;`
515### 削除
516`DROP FUNCTION function`
517
518----
519
520## トリガ
521DELETEやINSERTやUPDATEが行われたときに自動的に実行するようにするの 
522
523| コマンド | 実行前(OLD.カラム) | 実行後(NEW.カラム) |
524| :---| :--- |:--- |
525| INSERT | ✖ | 〇 |
526| DELETE | 〇 | ✖ |
527| UPDATE | 〇 | 〇 |
528
529### 作成
530```
531CREATE TRIGGER trigger [BEFORE of AFTER] トリガとなるコマンド
532ON table FOR EACH ROW
533BEGIN
534  更新前のテーブル→OLD.column
535  更新後のテーブル→NEW.column
536  を使った処理
537END
538```
539
540### 削除したものを別のテーブルに移動しておく
541```
542delimiter //
543
544CREATE TRIGGER trigger BEFORE DELETE ON table FOR EACH ROW
545BEGIN
546  INSERT INTO table-deleted VALUES(OLD.column1,OLD.column2,...);
547END
548
549//
550delimiter ;
551
552```
553
554### 確認
555`SHOW TRIGGERS`
556### 表示
557`DROP TRIGGERS triggers;`
558
559----
560## トランザクション
561複数の処理をまとめる機能で、それが終わった後で結果を反省させることを**コミット**、処理を反映しないで戻すことを**ロールバック**という。  
562
563**コミットせずに終わると自動的にロールバックされる**
564
565**ロールバックできないもの**  
566・DROP [DATABASEやTABLEやVIEW]  
567・ALTER TABLEなど
568
569### トランザクションのスタート
570`START TRANSACTION;` 
571
572### ロールバック
573`ROLLBACK;`
574
575### 反映
576`COMMIT;`
577
578----
579
580## ファイル
581
582### CSVファイル
583カンマで区切られただけのテキスト
584
585### インポート/エクスポートを行うために
586C:\MAMP\conf\mysql\my.iniの41行目付近に追加する  
587`secure-file-priv = ""`
588
589### インポート
590`LOAD DATA INFILE 'file-name' INTO TABLE table option`  
591
592オプションの種類  
593・`FIELDS TERMINATED BY '区切り文字'(デフォルトは'\t':タブ)`  
594・`LINES TERMINATED BY '改行文字(デフォルトは'\n')'`  
595・`IGNORE '最初にスキップする行' LIENS (デフォルトは0)`
596
597### エクスポート
598`SELECT * INTO OUTFILE 'file-name option FROM table`  
599
600オプションの種類  
601・`FIELDS TERMINATED BY '区切り文字'(デフォルトは'\t':タブ)`  
602・`LINES TERMINATED BY '改行文字(デフォルトは'\n')'`  
603・`IGNORE '最初にスキップする行' LIENS (デフォルトは0)`
604
605### ファイルからSQLコマンド実行
606`SOURCE ファイル名.txt(''で囲わない)`
607
608#### sqlモニタ開かずに実行
609`mysql dbname -u user -ppassword -e "SOURCE file.txt";`
610
611### 実行結果をファイル出力
612開始  
613`tee file.txt;`
614
615中  
616`notee;`
617
618----
619
620## データベース全体のバックアップとリストア
621
622### ダンプ
623データベースの内容を全て書き出す→ダンプ  
624SQL文で書き出される  
625`mysqldump -u user -ppassword db > file.txt;`
626
627### リストア
628```
629mysqladmin -u user -ppassword CREATE db;
630
631mysql -u user -ppassword db < file.txt;
632```
633
634
635----
636
637## テーブルのロック
638ロックの種類  
639
640READ :読み書き専用  (InnoDBだとREAD LOCALもこれ。ほかだとREAD LOCALはロックをかけたクライアントだけDELECT以外できなくなる)  
641
642WRITE :ロックをかけたクライアント以外ではすべて操作できなくなる。ロックをかけたクライアントでは操作できる。
643### テーブルのロック
644`LOCK TABLES table lock`
645### テーブルのアンロック
646**全てのテーブルに対するロック解除**  
647`UNLOCK TABLES`