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

MySQL

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列に表示される