Category Archives: SQL

unique設定

ユニークを使った設定

複数のカラムを対象にユニーク設定を行う

// テーブル作成
create table test (id int, name varchar(10), unique(id, name));

// レコード追加
insert into test (id,name) values(1,"test"); // 1レコード目
insert into test (id,name) values(2,"test"); // 2レコード目。nameは1レコード目と同じだがinsertできる
insert into test (id,name) values(1,"test"); // 3レコード目。 idとnameが1レコードと合致するためにエラーでる

プライマリーキーを使った設定(上記のuniqueを使った時と同じような挙動になる)

// テーブル作成
create table test2 (id int, name varchar(10), primary key(id, name));

// レコード追加
mysql> insert into test2 (id,name) values(1,"test");
Query OK, 1 row affected (0.01 sec)

mysql> insert into test2 (id,name) values(2,"test");
Query OK, 1 row affected (0.00 sec)

mysql> insert into test2 (id,name) values(1,"test");
ERROR 1062 (23000): Duplicate entry '1-test' for key 'PRIMARY'

サブクエリで集計したものを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
)