MySQLの復習@4日目

select文の小技

# カラムの入れ替え
SELECT [col]... FROM [tab];

# エイリアス
SELECT [col] as [alias]... FROM [tab];

# 四則演算
SELECT [col][+-*/][n]... FROM [tab];

# 平均，合計，個数
SELECT AVG([col]) FROM [tab];
SELECT SUM([col]) FROM [tab];
SELECT COUNT([col]) FROM [tab];

# その他情報を表示する関数いろいろ
SELECT PI();
SELECT VERSION();
SELECT DATABASE();
SELECT USER();
SELECT CHARSET('なんとか');
NOW();

文字列操作

# 文字列結合
SELECT CONCAT([col|'str'],...) FROM [tab];

# 右からn文字取り出す
SELECT RIGHT([col], n) FROM [tab];

# 左からn文字取り出す
SELECT LEFT([col], n) FROM [tab];

# n番目からm個取り出す
SELECT SUBSTRING([col], n) FROM [tab];

# 文字列をn回繰り返し
SELECT REPEAT([col], n) FROM [tab];

# 文字列を反転
SELECT REVERSE([col] FROM [tab];

条件付き抽出

抽出する最大数を指定

大規模なデータベースを試しに検索するときとか．

SELECT * FROM [tab] LIMIT [n];

WHERE を使う

SELECT * FROM [tab] WHERE [cond];

SELECT * FROM tab WHERE age = 10;
# != ではない
SELECT * FROM tab WHERE age <> 10;
SELECT * FROM tab WHERE age < 10;
SELECT * FROM tab WHERE age > 10;
SELECT * FROM tab WHERE age >= 10;
SELECT * FROM tab WHERE age <= 10;
SELECT * FROM tab WHERE age IN (12, 15, 18);
SELECT * FROM tab WHERE age NOT IN (12, 15, 18);
SELECT * FROM tab WHERE age BETWEEN 16 AND 18;
SELECT * FROM tab WHERE age NOT BETWEEN 16 AND 18;

あいまい検索: LIKE

# ワイルドカード
# _ : 任意の一文字
# % : 任意の文字列
SELECT * FROM [tab] WHERE name LIKE '%春_';
SELECT * FROM [tab] WHERE name LIKE '_窪__';
SELECT * FROM [tab] WHERE name NOT LIKE '_窪__';

NULLのカラムを検索

SELECT * FROM [tab] WHERE [col] IS NULL;
SELECT * FROM [tab] WHERE [col] IS NOT NULL;

抽出したデータの重複を取り除く

SELECT DISTINCT [col] FROM [tab];

条件の論理演算

SELECT * FROM [tab] WHERE [cond1] AND [cond2];
SELECT * FROM [tab] WHERE [cond1] OR [cond2];

CASE WHEN

SELECT name,
   CASE
       WHEN point >= 80 THEN '優'
       WHEN point >= 70 THEN '良'
       WHEN point >= 60 THEN '可'
       ELSE '不可'
   END AS '評語'
FROM tab;

忘れてたこと

INSERT

INSERT INTO tbl VALUES(1, 'ishida');
INSERT INTO tbl (name, no) VALUES('kudo', 2);
INSERT INTO tbl (no, name) VALUES(3, 'sato'), (4, 'suzuki');

SELECTで指定のカラムを取り出す

SELECT [col1], [col2] FROM [tab];