【MySQL】入門用にも!基本構文・書き方一覧~SQL TIPS~
MySQLの構文をまとめました
MySQLの構文をまとめてみました。(随時更新予定です。)
PostgreSQLの構文まとめもやっていますので、こちらも参考になれば幸いです。
文字列操作
1.文字列の結合(concat)
select CONCAT('abc','def'); # 'abcdef'
select CONCAT('abc','def','efg'); # 'abcdefefg'
select CONCAT('abc','def',ifnull(null,'')); # 'abcdef'
# nullを結合すると、返り値がnullになるため注意。(上はその対応にifnullで置換した例)
2.文字数の取得(char_length)
select char_length('a'); # 1(文字)
select char_length('あ'); # 1(文字)
select char_length('123あいう'); # 6(文字)
3.文字列のバイト数を取得(length)
select length('a'); # 1(バイト)
select length('あ'); # 3(バイト)
select length('123あいう'); # 12(バイト)
4.文字列の一部分を取得(substring、left、right)
select substring('abcdefghijk',2,3); # 'bcd' (2文字目から3文字取得する)
select left('abcdefghijk',5); # 'abcde'(左から5文字取得する)
select right('abcdefghijk',5); # 'ghijk'(右から5文字取得する)
5.検索文字位置の取得(locate)
select locate('ef','abcdefg'); # 5(5文字目にある)
6.スペース除去(trim)
SELECT TRIM(' abc '); # 'abc' : 先頭・後方のスペースを除去
SELECT LTRIM(' abc '); # 'abc ': 先頭のスペースを除去
SELECT RTRIM(' abc '); # ' abc': 後方のスペースを除去
7.大文字・小文字の変換(upper、lower)
select upper('abc'); # 'ABC' (大文字へ変換)
select lower('EFG'); # 'efg' (小文字へ変換)
8.置換(replace)
select replace('everywhere','e','E'); # 'EvErywhErE'(e ⇒ Eへ置換)
9.前0埋め(lpad)
select lpad('12345',10,'0'); # '0000012345'(10桁前0埋め)
10.後ろスペース埋め(rpad)
select rpad('12345',10,' '); # '12345 '(後ろスペース埋め10桁)
11.Nullの場合の処理(COALESCE)
select ifnull(null , 'abc'); # 'abc'(一つ目の引数がnullなら二つ目の引数を返す)
数値操作
1.数値の書式設定(format)
select format(12345.67, '3'); # 12,345.67 少数3桁カンマ区切り
2.絶対値を取得(abs)
select abs(-5); # 5
3.割り算の余りを取得(mod)
select mod(11,3); # 2(11÷3の余り)
4.四捨五入(round、trunc)
select round(123.45678,3); # 123.457(小数第3位四捨五入)
select truncate(123.45678,3); # 123.456(小数第3位切り捨て)
# 切り上げを小数第1位までとする時:0.09を足して、小数第1位で切り捨てる
5.ランダム値(random)
select rand(); # 0.896430・・・
# 範囲指定したランダム値
select round(( rand() * (下限値 - 上限値) ), 0) + 上限値; # 50
日付操作
1.現在日時の取得
select current_date(); # 2020-07-19
select current_time(); # 23:01:23
select now(); # 2020-07-19 23:01:23
select current_timestamp(); # 2020-07-19 23:01:23
select curdate(); # 2020-07-19(現在日時の文字列)
select curdate()+0; # 20200719(現在日時の数値)
2.日付の書式設定(to_char)
select DATE_FORMAT(now(),'%Y/%m/%d'); # yyyy/mm/dd形式
select DATE_FORMAT(now(),'%k:%i:%s'); # hh:mm:ss形式
select DATE_FORMAT(now(),'%Y/%m/%d %k:%i:%s'); # yyyy/mm/dd hh:mm:ss形式
3.月、週、日、時刻を加算する
select DATE_ADD(now(),INTERVAL 5 MONTH); # 月の加算
select DATE_ADD(now(),INTERVAL 5 WEEK); # 週の加算
select DATE_ADD(now(),INTERVAL 5 DAY); # 日の加算
select DATE_ADD(now(),INTERVAL 5 HOUR); # 時間の加算
select DATE_ADD(now(),INTERVAL 5 MINUTE); # 分の加算
select DATE_ADD(now(),INTERVAL 5 SECOND); # 秒の加算
4.曜日を取得する(date_part)
select DAYOFWEEK(current_date()); # 1:日、2:月、3:火、4:水、5:木、6:金、7:土
select WEEKDAY(current_date()); # 0:月、1:火、2:水、3:木、4:金、5:土、6:日
5.月末の日を取得する
SELECT LAST_DAY(now()); # 2020-07-31
型の変換(cast)
1.文字⇒日付
select cast('20200101' as date); # 2020-01-01
2.文字⇒datetime
select cast('20200401123456' as datetime); # 2020-04-01 12:34:56
3.文字⇒数値
select cast(123456789 as SIGNED); # 123456789
select cast(123456.789 as DECIMAL(9,3)); # 123456.789
# Decimal(全体桁数 , 小数点以下の桁数)で指定する
4.数値⇒文字
select cast(123456789 as char); # '123456789'
5.数値⇒日付
select cast(20200701 as date); # 2020-07-01
テーブル・列の変更
1.列の追加・変更・削除
# 列の追加
ALTER TABLE テーブル名 add 列名 型;
# Not Null制約の追加
ALTER TABLE テーブル名 MODIFY COLUMN カラム名 データ型 NOT NULL;
# デフォルト値の追加
ALTER TABLE テーブル名 ALTER COLUMN カラム名 SET DEFAULT デフォルト値;
# 列の削除
ALTER TABLE テーブル名 DROP COLUMN 列名;
# インデックスの作成
CREATE INDEX インデックス名 ON テーブル名 (列名1 , 列名2 , ・・・ );
# インデックスの削除
ALTER TABLE テーブル名 DROP INDEX インデックス名;
2.データベース・テーブル・列にコメントを付加する
# テーブルにコメント追加
ALTER TABLE テーブル名 COMMENT 'コメント'
# 列にコメント追加
ALTER TABLE テーブル名 MODIFY 列名 型 NOT NULL COMMENT 'コメント';
# NOT NULLの場合、「NOT NULL」は必ず含める(NullOKになってしまうため)
# 列のコメント削除
ALTER TABLE テーブル名 MODIFY 列名 型 NOT NULL COMMENT 'コメント';
# NOT NULLの場合、「NOT NULL」は必ず含める(NullOKになってしまうため)
3.生成列(自動で計算される列)
列名 型 as 式 STORED # STORED # 通常の列情報のように扱える
列名 型 as 式 VIRTUAL # VIRTUAL # 実データを持たず、読み込むタイミングで計算
bmi int AS (weight / ((height / 100) * (height / 100))) STORED # BMI列の例
ビュー
1.ビューの作成
CREATE VIEW ビュー名 AS select文;
2.ビューの削除
DROP VIEW ビュー名;
データベースやテーブル情報の取得
1.テーブル一覧の取得
show tables from データベース名;
2.テーブルの列一覧の取得
show columns from テーブル名;
3.ビュー一覧の取得
SELECT * FROM information_schema.tables
WHERE table_type = 'VIEW' and TABLE_SCHEMA = 'データベース名';
# データベース名はシングルクオーテーションありで指定
4.データベース一覧の取得
show databases; # このまま実行可
5.オートインクリメント
--オートインクリメントの列ありのテーブル作成
CREATE TABLE テーブル名 (列名 INT AUTO_INCREMENT NOT NULL, 他の列, PRIMARY KEY (列名));
# オートインクリメントの値設定
ALTER TABLE テーブル名 AUTO_INCREMENT = 10;
# オートインクリメントの値確認
SHOW TABLE STATUS WHERE name = 'テーブル名' # AUTO_INCREMENT列に表示される