よく使うSQL文

よく使うSQL文

目次

検索

or検索

SELECT *
FROM table_name
WHERE (col_name = 'aaa')
or (col_name = 'bbb')

in検索

SELECT *
FROM table_name
WHERE col_name IN (
'aaa',
'bbb'
)

文字列を含む(LIKE)

SELECT *
FROM table_name
WHERE col_name LIKE '%aaa%'

文字列を含まない(NOT LIKE)

SELECT *
FROM table_name
WHERE col_name NOT LIKE '%aaa%'

検索結果に指定項目のみ表示

SELECT col_name1 , col_name2 , col_name3
FROM tb_sample
WHERE col_name IN (
'aaa'
'bbb')

重複行を除外して検索(DISTINCT)

SELECT DISTINCT col_name FROM table_name

空白以外を検索

SELECT *
FROM table_name
WHERE col_name <> ''

範囲検索(BETWEEN )

WHERE col_name BETWEEN min AND max

範囲検索(範囲外)

WHERE col_name NOT BETWEEN min AND max

正規表現検索(REGEXP)

 WHERE col_name REGEXP '(text.*)'

置換

フィールドを完全に書き換える

UPDATE table_name
SET col_name = ''

文字列の置換(REPLACE)

UPDATE table_name
SET col_name = REPLACE(col_name, '置換前の文字列', '置換後の文字列')

改行(rn)の削除

UPDATE table_name
SET col_name = REPLACE(col_name, 'rn', '')

先頭に文字列を追加(CONCAT)

UPDATE table_name
SET col_name = CONCAT('先頭に追加する文字列', col_name)

末尾に文字列を追加(CONCAT)

UPDATE table_name
SET col_name = CONCAT(col_name, '末尾に追加する文字列')