ユーザ用ツール

サイト用ツール


よく忘れるsql関数

よく忘れるSQL関数

SQL早くさせる方法

カラムランキング

SELECT COUNT( * ) ,  `id` 
FROM  `data_tbl` 
GROUP BY id
ORDER BY COUNT( * ) DESC
SELECT COUNT( * ) cnt,  `id` 
FROM  `data_tbl` 
GROUP BY id
ORDER BY cnt DESC

よく使うSQL関数一覧

SQL分類意味使用例結果
SELECTDMLデータ行を取得SELECT * FROM table_nametable_nameの全列、全行を取得
DISTINCTDML重複する行を除くSELECT DISTINCT item FROM table_nametable_nameのitem列だけを重複値を取り除いて取得
ASDML別名の指定SELECT item1 AS item2 FROM table_nameitem1列にitem2列という別名を付けて結果を取得
FROMDML対象テーブル指定SELECT * FROM table_nametable_nameの全列、全行を取得
WHEREDML抽出条件の指定SELECT * FROM table_name WHERE item = 100item列が100と等しい行を取得
GROUP BYDMLグループ化SELECT COUNT(*) FROM table_name GROUP BY itemtable_nameをitem列の値によってグループ化し、その行数を集計
HAVINGDML集計関数の結果を抽出条件に指定SELECT x,SUM(i) FROM table_name GROUP BY x HAVING SUM(i) > 100グループの合計値が100以上であるグループのみを取得
ORDER BYDML取得順序・ソートSELECT * FROM table_name ORDER BY item ASCtable_nameをitem列の昇順(DESCは降順)に並び変える
LIMITDML結果の制限SELECT * FROM table_name LIMIT 5,3table_name全体の5行目から3行のみを取得
INSERTDMLデータ行の追加INSERT INTO table_name VALUES(1,2,'test')table_nameに列の左から1,2,'test'を保持する行を追加(VALUESに続く値と対応)
UPDATEDMLデータ行の更新UPDATE table_name SET item1 = 1 WHERE item2 = 2table_nameにおいて、item2列が2のデータ行のitem1列を1に更新
DELETEDMLデータ行の削除DELETE FROM table_name WHERE item = 1table_nameにおいて、item列が1の行のみを削除
BEGINトランザクショントランザクション開始BIGIN WORKCOMITTをして確定するまで、INSERT、DELETE、UPDATEが反映されない
COMMITトランザクショントランザクションをコミットCOMMITINSERT、DELETE、UPDATEを反映する
ROLLBACKトランザクショントランザクションを戻すROLLBACKINSERT、DELETE、UPDATEが破棄され、BIGINを実行する前の状態に戻る
LOCK TABLESトランザクションテーブルのロックLOCK TABLES table_name READテーブルをREADロック(READ/READ LOCAL/WRITE)
UNLOCK TABLESトランザクションテーブルのロック解除UNLOCK TABLES全テーブルのロックを解除
CREATE TABLEDDLテーブルの作成CREATE TABLE table_name(item1 INTEGER, item2 VARCHAR(20))item1とitem2の列を持ち、それぞれの型が整数、文字列であるtable_nameを作成
NOT NULLDDLNOT NULL制約CREATE TABLE table_name(item1 INTEGER NOT NULL, item2 VARCHAR(20) NULL)item1列をNOT NULLに指定、item2列をNULL指定
UNIQUEDDLUNIQUE制約CREATE TABLE table_name(item INTEGER UNIQUE)item1列にUNIQUE制約を設定
PRIMARY KEYDDLPRIMARY KEY制約CREATE TABLE table_name(item INTEGER NOT NULL PRIMARY KEY)item1列にNOT NULL制約と PRIMARY KEY制約を設定
DROP TABLEDDLテーブルの削除DOROP TABLE table_nametable_nameを削除
ALTER TABLEDDLテーブル属性変更ALTER TABLE table_name ADD COLUMN item INTEGERitem列を数値型でtable_nameに追加(DROPは削除)
TRUNCATE TABLEDDLテーブル全行削除TRUNCATE TABLE table_nametable_name全行を高速で削除(DELETEより速い)
CREATE INDEXDDLインデックス作成CREATE INDEX idx ON table_nametable_nameに対してインデックスidxを作成
DROP INDEXDDLインデックス削除DROP INDEX idx ON table_nametable_nameに対してインデックスidxを削除
GRANTDDL権限の付与GRANT ALL PRIVILEGES ON *.* TO name@localhost FLUSH PRIVILEGESすべての権限をlocalhostのユーザーnameにグローバル権限として付与
REVOKEDDL権限の剥奪REVOKE ALL PRIVILEGES ON table_name FROM name FLUSHI PRIVILEGEStable_nameに対して、全ての権限をnameか剥奪
RENAMEDDLオブジェクト名の変更RENAME table_nameA TO table_nameBデータベースtable_nameAをtable_nameBにリネーム
AND演算子かつSELECT * FROM table_name WHERE item1 = 100 AND item2 = 200item1列が100と等しく、かつitem2列が200と等しい行を全て取得
BETWEEN演算子範囲指定SELECT * FROM table_name WHERE 年齢 BETWEEN 30 AND 40年齢列が20以上、30以下の行を全て取得
IN演算子含むSELECT * FROM table_name WHERE item IN (100,200)item列が100か200の行を全て取得
LIKE演算子パターンマッチSELECT 氏名 FROM table_name WHERE 氏名 LIKE '%郎'氏名の最後が「郎」である行を全て取得
NOT演算子否定SELECT * FROM table_name WHERE NOT item = 1table_nameでitemが1ではない行を全て取得
OR演算子またはSELECT * FROM table_name WHERE item1 = 100 OR item2 = 200item1列が100、またはtem2列が200と等しい行を全て取得
IS NULL演算子NULL値の判定SELECT * FROM table_name WHERE item IS NULLitem列がNULLの行を全て取得
IS NOT NULL演算子NULL値の判定SELECT * FROM table_name WHERE item IS NOT NULLitem列がNULLではない行を全て取得
AVG関数(集計)平均SELECT AVG(DISTINCT item) FROM teble_nameitem列の重複を除いて平均値を計算
COUNT関数(集計)行数を集計SELECT COUNT(DISTINCT item) FROM table_nameitem列の重複を除いて行数を計算
MAX関数(集計)最大値SELECT MAX(item) FROM table_nameitem列の最大値を計算
MIN関数(集計)最小値SELECT MIN(item) FROM table_nameitem列の最小値を計算
STDDEV関数(集計)標準偏差SELECT STDDEV(item) FROM table_nameitem列の標準偏差を計算
SUM関数(集計)合計値SELECT SUM(item) FROM table_nameitem列の合計値を計算
ASCLL関数(文字)文字コード変換SELECT ASCALL(item) FROM teble_nameitem列をASCALLにより文字コードに変換
CHAR関数(文字)文字変換SELECT CHAR(item) FROM teble_nameitem列をCHARにより文字に変換
CONCAT関数(文字)文字列結合SELECT CONCAT(item1,item2,item3) FROM table_nameCONCATにより、列item1,item2,item3を文字列結合
INSERT関数(文字)文字列の挿入SELECT INSERT(item,5,1,'xyz') FROM table_nameitem列の5文字目から1文字をxyzに置換
INSTER関数(文字)文字列検索SELECT INSTER(item1,item2) FROM table_nameitem1列の文字列中から文字列item2を検索
LENGTH関数(文字)文字列長を取得SELECT LENGTH(item) FROM table_nameitem列の文字列長を計算
CHARACTER関数(文字)文字列長取得SELECT CHAR_LENGTH(item1) FROM table_name WHERE item2 = 100table_nameのitem2列が100と等しい行から、item1列の文字列の長さを取得
_LENGTH
OCTET関数(文字)文字列長を取得SELECT OCTET_LENGTH(item) FROM table_nameitem列のバイト数を計算
_LENGTH
POSITION関数(文字)文字列検索SELECT POSITION(item1 IN item2) FROM table_nameitem2列の文字列中からitem1の文字列を検索し、最初にitem1が現れる位置を取得
REPEAT関数(文字)繰り返しSELECT REPEAT(item1,item2) FROM table_nameitem1をitem2回繰り返した文字列を取得
REPLACE関数(文字)置換SELECT REPLACE(item1,item2,item3) FROM table_nameitem1列の文字列からitem2列の文字列と等しい箇所を全て文字列item3に置換
REVERSE関数(文字)反転SELECT REVERSE(item) FROM table_nameitem列の文字列を反転した文字列を取得(1バイト文字のみ対応)
SUBSTRING関数(文字)部分抽出SELECT SUBSTRING(item1,item2,item3) FROM table_nameitem1列の文字列をitem2番目から、item3番目までを抽出して取得
RIGHT関数(文字)右部分を取得SELECT RIGHT(item1,item2) FROM table_nameitem1列の文字列をitem2列の値分だけ、右側から抽出した文字列を取得
LEFT関数(文字)左部分を取得SELECT LEFT(item1,item2) FROM table_nameitem1列の文字列をitem2列の値分だけ、左側から抽出した文字列を取得
RTRIM関数(文字)右の空白削除SELECT RTRIM(item) FROM table_nameitem列から右側の空白を取り除く
LTRIM関数(文字)左の空白削除SELECT LTRIM(item) FROM table_nameitem列から左側の空白を取り除く
TRIM関数(文字)指定文字の削除SELECT TRIM(item1 FROM item2) FROM table_nameitem2の両端からitem1を取り除いた結果を取得
INITCAP関数(文字)先頭を大文字変換SELECT INITCAP(item) FROM teble_nameitem列の文字列を先頭だけ、大文字に変換
LOWER関数(文字)小文字変換SELECT LOWER(item) FROM table_nameitem列の文字列を全て小文字に変換した文字列を取得
UPPER関数(文字)大文字変換SELECT UPPER(item) FROM table_nameitem列の文字列を全て大文字に変換した文字列を取得
SYSDATE関数(日付)現在日付SELECT STSDATE FROM DUAL現在の日時を取得
CURRENT関数(日付)現在日付SELECT CURRENT_DATE現在の日時を取得
_DATE
CURRENT関数(日付)現在時刻SELECT CURRENT_TIME現在の時刻を取得
_TIME
CURRENT関数(日付)現在日時SELECT CURRENT_TIMESTAMP現在の日時を取得
_TIMESTAMP
NOW関数(日付)現在日時INSERT INTO table_name VALUES(NOW())現在の日時で行を追加
DATE関数(日付)日付の整形SELECT DATE_FORMAT(item,'%Y/%m/%d') FROM table_nameitem列(YYYY-MM-DD形式)をYYYY/MM/DD形式に整形
_FORMAT
TIME関数(日付)時刻の整形SELECT TIME_FORMAT(item,'%H:/%i:/%s') FROM table_nameitem列(hhmmss形式)をhh:mm:ss形式に整形
_FORMAT
DATE関数(日付)日付の加算DATE_ADD(item, INTERVAL 10 DAY) FROM table_name日付型のitem列の10日後を計算して取得
_ADD
DATE関数(日付)日付の減算DATE_SUB(item, INTERVAL 10 DAY) FROM table_name日付型のitem列の10日前を計算して取得
_SUB
YEAR関数(日付)年を取得SELECT YEAR(item) FROM table_nameitem列(YYYY-MM-DD形式)から年(YYYY)のみを取得
MONTH関数(日付)月を取得SELECT MONTH(item) FROM table_nameitem列(YYYY-MM-DD形式)から月(MM)のみを取得
DAYOFMONTH関数(日付)日付を取得SELECT DAYOFMONTH(item) FROM table_nameitem列(YYYY-MM-DD形式)から日付(DD)のみを取得
HOUR関数(日付)時を取得SELECT HOUR(item) FROM table_nameitem列(hh:mm:ss形式)から時(hh)のみを取得
MINUTE関数(日付)分を取得SELECT MINUTE(item) FROM table_nameitem列(hh:mm:ss形式)から分(mm)のみを取得
SECOND関数(日付)秒を取得SELECT SECOND(item) FROM table_nameitem列(hh:mm:ss形式)から秒(ss)のみを取得
DAYOFWEEK関数(日付)曜日を取得SELECT DAYOFWEEK(item) FROM table_nameitem列(YYYY-MM-DD形式)から曜日のみを計算して取得
DAYNAME関数(日付)曜日名を取得SELECT DAYNAME(item) FROM table_nameitem列(YYYY-MM-DD形式)から曜日名を計算して取得
MONTHNAME関数(日付)月名を取得SELECT MONTHNAME(item) FROM table_nameitem列(YYYY-MM-DD)から月名のみを取得
DAYOFYEAR関数(日付)年間通算日を取得SELECT DAYOFYEAR(item) FROM table_nameYYYY/01/01からitem列(YYYY-MM-DD形式)までの経過日数を計算して取得
WEEK関数(日付)年間通算週を取得SELECT WEEK(item) FROM table_nameYYYY/01/01からitem列(YYYY-MM-DD形式)までの経過週数を計算して取得
NULLIF関数(変換)NULL変換SELECT NULLIF(item1,item2) FROM table_nameitem1列とitem2列が等しい場合、NULLに変換
ABS関数(変換)絶対値SELECT ABS(item) FROM table_nameitem列の絶対値を計算
SIN関数(変換)サインSELECT SIN(item) FROM table_nameitem列のサインを計算(引数の単位はラジアン)
COS関数(変換)コサインSELECT COS(item) FROM table_nameitem列のコサインを計算(引数の単位はラジアン)
TAN関数(変換)タンジェントSELECT TAN(item) FROM table_nameitem列のタンジェントを計算(引数の単位はラジアン)
COT関数(変換)コタンジェントSELECT COT(item) FROM table_nameitem列のコタンジェントを計算(単位はラジアン)
ASIN関数(変換)逆サインSELECT ASIN(item) FROM table_nameitem列の逆サインを計算(単位はラジアン)
ACOS関数(変換)逆コサインSELECT ACOS(item) FROM table_nameitem列の逆コサインを計算(単位はラジアン)
ATAN関数(変換)逆タンジェントSELECT ATAN(item) FROM table_nameitem列の逆タンジェントを計算(単位はラジアン)
ATAN2関数(変換)逆タンジェントSELECT ATAN2(item1,item2) FROM table_nameitem1列とitem2列により逆タンジェントを計算(単位はラジアン)
CEILING関数(変換)最小整数値SELECT CEILING(item) FROM table_nameitem列において、最も小さい整数値を計算(小数点以下切り捨て)
FLOOR関数(変換)最大整数値SELECT FLOOR(item) FROM table_nameitem列において、最も大きい整数値を計算(小数点以下切り捨て)
DEGREES関数(変換)ラジアンを度に変換SELECT DEGREES(item) FROM table_nameitem列(ラジアン)を度に変換
RADIANS関数(変換)度をラジアンに変換SELECT RADIANS(item) FROM table_nameitem列(度)をラジアンに変換
EXP関数(変換)指数値SELECT EXP(item) FROM table_nameitem列の指数値を計算
GREATEST関数(変換)最大値SELECT GREATEST(item1,item2,item3) FROM table_name列item1,item2,item3の中で最も大きい値を計算(引数の個数は可変長)
LEAST関数(変換)最小値SELECT LEAST(item1,item2,item3) FROM table_name列item1,item2,item3の中で最も小さい値を計算(引数の個数は可変長)
MOD関数(変換)余りを計算SELECT MOD(item1,item2) FROM table_nameitem1列÷item2列の余りを計算
POW関数(変換)べき乗SELECT POW(item1,item2) FROM table_nameitem1列のitem2列乗を計算
SQRT関数(変換)平方根SELECT SQRT(item) FROM table_nameitem1列の平方根を計算
ROUND関数(変換)四捨五入SELECT ROUND(item1,2) FROM table_nameitem1列の値を小数点以下2桁が残るように四捨五入
SIGN関数(変換)符号SELECT SIGN(item) FROM table_nameitem列の符号を取得(正:0/負:-1/0:0)
RAND関数(変換)乱数SELECT RAND()乱数(ランダムな数値)を取得
PI関数(変換)円周率SELECT PI()円周率を取得
よく忘れるsql関数.txt · 最終更新: 2013/12/03 11:11 by luis_lee