【mariaDB】コマンドチートシート

2020/06/01 01:26 その他::技術情報

前提条件

DB 名 / テーブル名 / フィールド名 / エイリアスの例示

DB 名

3文字に続いてデータベースを示す。

***_database
***_db
xxx_db
yyy_db

テーブル名

3文字に続いてテーブルを示す。

***_table
***_tbl
xxx_table
yyy_table

フィールド名の例示

3文字に続いてフィールドを示す。

***_field
***_fld
***_column
***_clm
xxx_field
yyy_field

エイリアスの例示

きちんと表示させた場合はバッククォート ` で括る必要があるが、省略。

select * from xxx_tbl as x order by `x`.xxx_fld;
↓
select * from xxx_tbl as x order by x.xxx_fld;

大文字 / 小文字

mariaDB では SQL に大文字/小文字の指定はないみたい。

> SELECT * FROM ***_table;
> Select * From ***_table;
> select * from ***_table;

サンプルは小文字で書いている。
たぶん、業務で使うなら SQL 部分を見やすくするために大文字で統一すると思う。

改行

SQL の末尾（SQL の実行）を示すために ; を記述するようで、コマンド以外ならどこで改行を入れても問題なさそう。

短いなら1行で書いてもいいけど、条件等が長くなるようなら段落を使って構造を把握しやすくする。

> select
    *
  from
    xxx_table
  where
    xxx_field = "0"
  ;

CREATE

テーブルを作成する。

通常

基本形

> create table xxx_table ( aaa_field int unsigned not null auto_increment primary key);

いろいろ作りたいとき。

> create table xxx_table (
    aaa_field
      int unsigned
      not null
      auto_increment
      primary key,
    bbb_field
      int,
    ccc_field
      varchar(255),
    ddd_field
      tinyint unsigned
      not null,
    eee_field
      datetime
      default current_timestamp,
    fff_field
      datetime on current_timestamp
      default current_timestamp,
    ggg_field
      bit(1)
  );

SELECT

通常

select を利用した SQL の基本形

> select * from xxx_table;

内部連結 (inner join)

内部連結 inner join は、指定したフィールドの値が一致するデータ（連結先で Null 等がある等）しか取得できない。

どちらかのテーブルにしかないデータが不要な場合に使う。

テーブル二つを連結

> select * from xxx_table as x
    inner join yyy_table as y
      on x.zzz_id = y.zzz_id;

テーブル三つを連結

> select
    *
  from
    xxx_table as x
  inner join yyy_table as y
    on x.yyy_id = y.yyy_id
  inner join zzz_table as z
    on x.zzz_id = z.zzz_id;

外部連結 (outer join)

外部連結 outer join は、指定したフィールドの値が一致するデータ（連結先で Null 等がある等）も取得できる。

どちらかのテーブルにしかないデータを取得したい場合に使う。

左側基準 (left outerjoin)

join の左側データを基準に連結。
join された側のテーブルにしかないものは取得しない。

> select x.fld, y.fld from xxx_table as x
    left outer join yyy_table as y
      on x.zzz_id = y.yyy_id;

右側基準 (right outerjoin)

join の右側データを基準に連結。
join 前のテーブルにしかないものは取得しない。

> select x.fld, y.fld from xxx_table as x
    right outer join yyy_table as y
      on x.zzz_id = y.yyy_id;

基準なし (full outerjoin)

どちらのテーブルにも、それぞれでしか存在しないものでも取得する。

> select x.fld, y.fld from xxx_table as x
    full outer join yyy_table as y
      on x.zzz_id = y.yyy_id;

条件指定 (where)

> select *
  from xxx_table
  where yyy_id = 2;

> select *
  from xxx_table
  where
    yyy_id = 2
      or
    yyy_id = 3
;

並べ替え (order by)

昇順 (asc)

1個

昇順の指定 (asc) は省略

> select
    *
  from
    ***_table
  order by ***_field
  ;

複数

昇順の指定 (asc) は省略

> select
    *
  from
    xxx_table
  order by
    xxx_field,
    yyy_field
  ;

降順 (desc)

降順の指定は (desc)

> select
    *
  from
    ***_table
  order by ***_field desc
  ;

文字を対象 (asc)

UTF-8 を対象に

> select
    *
  from
    ***_table
  order by
    ***_field
      collate
        utf8_unicode_ci
  ;

使ったことない

> select
    *
  from
    ***_table
  order by cast(***_field as char)
  ;

INSERT

フィールド全部を対象に入れるとき

> insert
    into xxx_table
  values (
    default,
    "column",
    null
  );

フィールド個別に入れるとき

> insert
    into xxx_table (
      xxx_field,
      yyy_field
      )
  values (
    default,
    "column",
    null
  );

UPDATE

列単位でアップデート

field1 の列を全部 value1 にしたいとき

update ***_table
  set field1 = value1;

指定の行だけアップデート

field2 が value2 である field1 列の特定行を value1 にしたいとき

update ***_table
  set
    field1 = value1
  where
    field2 = value2;

フィールドコピー

xxx_fld の列を全部 yyy_fld に合わせたいとき

update ***_table
  set xxx_fld = yyy_fld ;

DELETE

指定行を削除

> delete from ***_table
where xxx_field = 1;

テーブル内全削除（TRUNCATEを使うべき）

> delete from ***_table;

降順で3つの行を削除

> delete from ***_table
order by xxx_field desc limit 3;

DROP

drop の対象は、DBそのものと、DB内のテーブル。

データベースをドロップ

> drop database ***_db;

テーブルをドロップ

> use ***_db;
> drop table ***_table;

その他

TRUNCATE

指定テーブル内の全データを削除し、auto_increment も初期化する。

> truncate table ***_table;

SHOW

show は、mariaDB そのものを参照したい場合に利用する。

DATABASES

> show databases;

TABLES

> show tables;

DESCRIBE

> desc ***_table;

ALTER

テーブル自体に操作を加えたいときに ALTER を利用する。

デフォルト値の変更

varchar(x) の場合

> alter table xxx_table
    alter xxx_field
      set default "aaaa";

bit(1) boolean の場合

> alter table xxx_table
    alter xxx_enable
      set default true;

フィールドの追加

最初に追加

> alter table ***_table add ***_field integer first;

符号なし unsigned の場合の指定

> alter table ***_table add ***_field integer unsigned first;

テーブルにデータが追加されたときだけ、タイムスタンプが自動更新されるフィールドの追加

> alter table ***_table
  add ***_field datetime default CURRENT_TIMESTAMP
  first;

テーブルの、他のフィールドが更新されたときもタイムスタンプが自動更新されるフィールドの追加

> alter table ***_table
  add ***_field datetime default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
  first;

末尾に追加

> alter table ***_table add ***_field integer;

複数の場合

> alter table ***_table add (xxx_field integer, yyy_field varchar(5));

指定フィールドの次に追加

> alter table ***_table add yyy_field integer after xxx_field;

フィールドの削除

> alter table ***_table drop column ***_field;

フィールドの型を変更

> alter table ***_table modify ***_field integer;

Default のあるカラムを Not Null にしたいとき

> alter table ***_table modify ***_field DateTime Not null Default CURRENT_TIMESTAMP;

符号なし int 等いろいろ付与したい場合

> alter table ***_table modify ***_field integer unsigned Not null auto_increment;

Primary key がないので付与したい場合

> alter table ***_table modify ***_field integer unsigned Not null primary key auto_increment;

フィールド名の変更

変更後のフィールド名は、型の宣言が必要。

> alter table xxx_table change xxx_field yyy_field varchar(1000);

auto_increment の番号設定（初期化）

> alter table xxx_table auto_increment = 1;

【mariaDB】コマンドチートシート2 ユーザ操作

関連記事