Category Archives: SQL

サブクエリで集計したものをleft join

元テーブル

+----+----------+-------+
| id | name     | value |
+----+----------+-------+
|  1 | yohei    |    10 |
|  2 | yohei    |    20 |
|  3 | miyamoto |    10 |
+----+----------+-------+

クエリ

select
  a.id ,
  a.name ,
  a.value ,
  a.Flg1 ,
  b.Flg4

from
  (select
    id ,
    name ,
    value ,
    CASE
      when value >= 15 then '1'
      else '0'
    END as Flg1
  from
    test
  ) as a
  left join
  (select
    name ,
    CASE
      when count(*) >= 2 then '1'
      else '0'
    END as Flg4
  from
    test
  group by
    name
  ) as b

on
  a.name = b.name

実行結果

+----+----------+-------+------+------+
| id | name     | value | Flg1 | Flg4 |
+----+----------+-------+------+------+
|  1 | yohei    |    10 | 0    | 1    |
|  2 | yohei    |    20 | 1    | 1    |
|  3 | miyamoto |    10 | 0    | 0    |
+----+----------+-------+------+------+

left join 2つ以上

以下のように普通につなげて書けばよい

select
  t1.id ,
  t2.name ,
  t3.name
from
  table1 as t1

left join
  table2 as t2
on
  t1.id = t2.id

left join
  table3 as t3
on
  t1.id = t3.id;

権限付与

全DB、全テーブルに対して、すべてのIPからアクセス可能

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'

mysql dbコピー(dump)

DBのバックアップ

ターミナル

// データコピー
mysqldump -u root -p --skip-lock-tables --database {移行元のDB名} > {出力先のファイル名}

// 移行先のDB作成
create database {移行先のDB名}

// データインポート
mysql -u root -p --default-character-set=utf8 {移行先のDB名} < {上記の出力したファイル名}

Tableの文字コード確認及び、変更

確認

show table status from {データベース名} like '{テーブル名}'

テーブルの文字コードを変更

// sjisに変更
alter table {テーブル名} charset='sjis';

データベースの文字コードを変更

ALTER DATABASE {データベース名} CHARACTER SET 'sjis'

備考

sjisの場合は、sql文もsjisで書く必要あり。

mysql created_at, updated_at 追加

レコード追加時に、自動で各日付が入る。

create table sample (
  created_at timestamp not null default current_timestamp,
  updated_at timestamp not null default current_timestamp on update current_timestamp
)

SQLでCSVファイルを取り込む

sqlファイル作成

set character_set_database=sjis; -- sjisを取り込む場合 ※末尾にセミコロン必要
load data local infile
"D:/Users/test/rpy.csv" // CSVファイル指定
into table table_test // テーブル名を指定

FIELDS TERMINATED BY ','
ENCLOSED BY '"' -- フィールドを囲むキャラクタ(ダブルコーテーション)

lines terminated by '\r\n'
ignore 1 lines
(@id,@name) // CSVの各カラムに名前を付ける
set id=@id,name=@name // DBカラム名=CSVカラム名とする

sql実行

// local_infileを有効にしてsqlにログイン
mysql --local_infile=1 -u root

// ファイル実行
source /Users/yoheimiyamoto/Documents/sql/data.sql;