MySQL | PostgresWeb - ポスグレウェブ

【MySQL】入門用にも！基本構文・書き方一覧～SQL TIPS～

MySQLの構文をまとめました

MySQLの構文をまとめてみました。（随時更新予定です。）

PostgreSQLの構文まとめもやっていますので、こちらも参考になれば幸いです。

文字列操作

１．文字列の結合（concat）

select CONCAT('abc','def');                   # 'abcdef'
select CONCAT('abc','def','efg');             # 'abcdefefg'
select CONCAT('abc','def',ifnull(null,''));   # 'abcdef'
# nullを結合すると、返り値がnullになるため注意。（上はその対応にifnullで置換した例）

２．文字数の取得（char_length）

select char_length('a');          # 1（文字）
select char_length('あ');         # 1（文字）
select char_length('123あいう');  # 6（文字）

３．文字列のバイト数を取得（length）

select length('a');          # 1（バイト）
select length('あ');         # 3（バイト）
select length('123あいう');  # 12（バイト）

４．文字列の一部分を取得（substring、left、right）

select substring('abcdefghijk',2,3);  # 'bcd'  (2文字目から3文字取得する)
select left('abcdefghijk',5);         # 'abcde'(左から5文字取得する)
select right('abcdefghijk',5);        # 'ghijk'(右から5文字取得する)

５．検索文字位置の取得（locate）

select locate('ef','abcdefg');  # 5（5文字目にある）

６．スペース除去（trim）

SELECT TRIM('   abc   ');   # 'abc'   : 先頭・後方のスペースを除去
SELECT LTRIM('   abc   ');  # 'abc   ': 先頭のスペースを除去
SELECT RTRIM('   abc   ');  # '   abc': 後方のスペースを除去

７．大文字・小文字の変換（upper、lower）

select upper('abc');  # 'ABC' （大文字へ変換）
select lower('EFG');  # 'efg' （小文字へ変換）

８．置換（replace）

select replace('everywhere','e','E');  # 'EvErywhErE'（e ⇒ Eへ置換）

９．前0埋め（lpad）

select lpad('12345',10,'0');  # '0000012345'（10桁前0埋め）

１０．後ろスペース埋め（rpad）

select rpad('12345',10,' ');  # '12345     '（後ろスペース埋め10桁）

１１．Nullの場合の処理（COALESCE）

select ifnull(null , 'abc');  # 'abc'(一つ目の引数がnullなら二つ目の引数を返す)

数値操作

１．数値の書式設定（format）

select format(12345.67, '3'); # 12,345.67  少数3桁ｶﾝﾏ区切り

２．絶対値を取得（abs）

select abs(-5);  # 5

３．割り算の余りを取得（mod）

select mod(11,3);  # 2（11÷3の余り）

４．四捨五入（round、trunc）

select round(123.45678,3);     # 123.457（小数第3位四捨五入）
select truncate(123.45678,3);  # 123.456（小数第3位切り捨て）
# 切り上げを小数第1位までとする時：0.09を足して、小数第1位で切り捨てる

５．ランダム値（random）

select rand(); # 0.896430・・・
# 範囲指定したランダム値
select round(( rand() * (下限値 - 上限値) ), 0) + 上限値;  # 50

日付操作

１．現在日時の取得

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（現在日時の数値）

２．日付の書式設定（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形式

３．月、週、日、時刻を加算する

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);  # 秒の加算

４．曜日を取得する（date_part）

select DAYOFWEEK(current_date());  # 1:日、2:月、3:火、4:水、5:木、6:金、7:土
select WEEKDAY(current_date());    # 0:月、1:火、2:水、3:木、4:金、5:土、6:日

５．月末の日を取得する

SELECT LAST_DAY(now()); # 2020-07-31

型の変換（cast）

１．文字⇒日付

select cast('20200101' as date);  # 2020-01-01

２．文字⇒datetime

select cast('20200401123456' as datetime);  # 2020-04-01 12:34:56

３．文字⇒数値

select cast(123456789 as SIGNED);         # 123456789 
select cast(123456.789 as DECIMAL(9,3));  # 123456.789
# Decimal(全体桁数 , 小数点以下の桁数)で指定する

４．数値⇒文字

select cast(123456789 as char);  # '123456789'

５．数値⇒日付

select cast(20200701 as date);  # 2020-07-01

テーブル・列の変更

１．列の追加・変更・削除

# 列の追加
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 インデックス名;

２．データベース・テーブル・列にコメントを付加する

# テーブルにコメント追加
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になってしまうため）

３．生成列（自動で計算される列）

列名 型 as 式 STORED  # STORED   # 通常の列情報のように扱える
列名 型 as 式 VIRTUAL # VIRTUAL  # 実データを持たず、読み込むタイミングで計算
bmi int AS (weight / ((height / 100) * (height / 100))) STORED # BMI列の例

ビュー

１．ビューの作成

CREATE VIEW ビュー名 AS select文;

２．ビューの削除

DROP VIEW ビュー名;

データベースやテーブル情報の取得

１．テーブル一覧の取得

show tables from データベース名;

２．テーブルの列一覧の取得

show columns from テーブル名;

３．ビュー一覧の取得

SELECT * FROM information_schema.tables
WHERE table_type = 'VIEW' and  TABLE_SCHEMA = 'データベース名';  
# データベース名はシングルクオーテーションありで指定

４．データベース一覧の取得

show databases;  # このまま実行可

５．オートインクリメント

--オートインクリメントの列ありのテーブル作成
CREATE TABLE テーブル名 (列名 INT AUTO_INCREMENT NOT NULL, 他の列, PRIMARY KEY (列名));

# オートインクリメントの値設定
ALTER TABLE テーブル名 AUTO_INCREMENT = 10;

# オートインクリメントの値確認
SHOW TABLE STATUS WHERE name = 'テーブル名' # AUTO_INCREMENT列に表示される