検索
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, '末尾に追加する文字列')