タイトル通り、覚えたことをアウトプットすることで定着と理解を深めようというものです。
メモ書きですが何かの役に立てば幸いです。

1,MySQLをコマンドプロンプトで操作

　・起動
　　mysql -u root -p

　・データベース作成
　　create database データベース名

　・データベース移動
　　use データベース名

　・テーブル作成
　　create table テーブル名(カラム名)

2,エラーが出て解決したのでメモ

　・テーブル作成時、auto_incrementを設定したカラムは主キーにしなければならない。

　・テーブル作成時、主キーを複数設定する場合は一番最後にまとめて指定する。

例：

create table sampletable(
　　　　　　id int,
　　　　　　name varchar(10),
　　　　　　age int,
　　　　　　primary key(id,name));  

目的

• データベースの勉強をするにあたって、DE系用語まとめという投稿をしたが、MySQLの内容も記載されていたため、別途投稿としてまとめる。
• MySQLの勉強内容をまとめる。

MySQL

• Web系で主に使われているデータベースのこと。
• オープンソースデータベースの一つである。
• 使用しやすく、高速である。

数値のデータ型

• 数値の型の例

型	値の範囲	UNSIGNED	ZEROFILL
TYNYINT	-128～127	0～255	000～255
SMALLINT	-32768～32767	0～65536	00000～65536
MEDIUMINT	-8388608～8388607	0～16777215	00000000～16777215
INTEGER	-2147483648～2147483647	0～4294967295	0000000000～4294967295
BIGINT	-9223372036854775808～9223372036854775807	0～18446744073709551615	00000000000000000000～18446744073709551615

テキストのデータ型

• 各バイト数を超えた場合はカットされる。
• 可変長のものは格納された文字列に合わせて表現される。
• 固定長のものは指定された文字列長さに、格納する文字列長さが満たなかった場合、満たない分だけ余白(文字としてのスペース)が格納される。
• 文字列の型の例

型	バイト数
TYNYTEXT	0～255バイト（固定）
VARCHAR	0～255バイト（可変）
TEXT	0～65535バイト（固定）
MEDIUMTEXT	0～16777215バイト（固定）
LONGTEXT	0～4294967295バイト（固定）

日付・時間のデータ型

• ダブルクオートかシングルクオートで囲んで格納する。
• 日付・時間のデータ型の例

型	内容	範囲
DATETIME	日付と時刻	’1000-01-01 00：00： 00’～’9999-12-31 23：59：59’
TIMESTAMP	日付と時刻	’1970-01-01 00:00:01.000000’UTC～ ’2038-01-19 03:14:07.999999’
DATE	日付	’1000-01-01’～’9999-12-31’
TIME	時刻	’-838：59：59～838：59：59’
YEAR	時刻	’1901～2155’

ある列の判定をしたいのですが、書き方が良くわからず質問させて頂きます。
空白の場合・・・001
1の場合・・・001
2の場合・・・002
このように処理をしたいのです。

今までは空白の場合が、なかったので下記で使用しておりました。
RIGHT('000' + convert(char,列名),3)

これではなく、初めに0かどうか判定して0なら001、その後以前同様の処理をすればいいのでしょうか？

ご教授願います。

playのEvolutionsでハマったところがあったので備忘録として残しておきます。

ある日のこと

Evolutionsでテーブルを作成しようとした時の話。
以下のように1.sqlを作成し、Playを起動した。

1.sql

# --- !Ups
create table clients {
   id bigint auto_increment not null,
   name varchar(255),
   client_id varchar(255) not null,
   secret varchar(255) not null,
   email varchar(255) not null,
   user_id varchar(255) not null
}

insert into clients values (default, "Hiroro", "Hiroro0970", "hogehoge", "hiroro@exampple.com", "fuga298")
# --- !Downs
drop table clients;

しかし、画面上にはDatabase 'default' is in an inconsistentの文字が。

よくみると'create table'のかっこが{}になっていたので1.sqlを以下のように修正して再起動。

1.sql

# --- !Ups
create table clients (
   id bigint auto_increment not null,
   name varchar(255),
   client_id varchar(255) not null,
   secret varchar(255) not null,
   email varchar(255) not null,
   user_id varchar(255) not null
)

insert into clients values (default, "Hiroro", "Hiroro0970", "hogehoge", "hiroro@exampple.com", "fuga298")
# --- !Downs
drop table clients;

ところが、またもや画面上にはDatabase 'default' is in an inconsistentの文字が。

表示されているクエリをみると、以前のものが表示されており反映されていない様子。
キャッシュが残っているのかと思い、何度もプロジェクトをクリーン&ビルドしたが一向に直る気配がない。

エラーログをみても心当たりがないものばかり。

play.api.db.evolutions.InconsistentDatabase: Database 'default' is in an inconsistent state![An evolution has not been applied properly. Please check the problem and resolve it manually before marking it as resolved.]
    at play.api.db.evolutions.DatabaseEvolutions.$anonfun$checkEvolutionsState$3(EvolutionsApi.scala:309)
    at play.api.db.evolutions.DatabaseEvolutions.$anonfun$checkEvolutionsState$3$adapted(EvolutionsApi.scala:294)
    at play.api.db.evolutions.DatabaseEvolutions.executeQuery(EvolutionsApi.scala:341)
    at play.api.db.evolutions.DatabaseEvolutions.checkEvolutionsState(EvolutionsApi.scala:294)
    at play.api.db.evolutions.DatabaseEvolutions.evolve(EvolutionsApi.scala:263)
    at play.api.db.evolutions.DefaultEvolutionsApi.evolve(EvolutionsApi.scala:104)
    at play.api.db.evolutions.EvolutionsWebCommands.handleWebCommand(ApplicationEvolutions.scala:496)
    at play.core.DefaultWebCommands.$anonfun$handleWebCommand$1(WebCommands.scala:42)
    at scala.collection.immutable.Stream.flatMap(Stream.scala:195)
    at play.core.DefaultWebCommands.handleWebCommand(WebCommands.scala:42)

原因

ネットで検索してもなかなか解決策が見つからなかったので、ひとまずコンソールに出力されたエラーログから分析していくことにした。
すると、EvolutionsApi.scalaの293行目に
{"select id, hash, apply_script, revert_script, state, last_problem from ${schema}play_evolutions where state like 'applying_%'"
の一文を発見。

どうやら知らぬ間にデータベース内にplay_evolutionsと言うテーブルが作成されているようだ。
何が入っているのかを知るためにとりあえずMySQL(使っているDB)に接続して

select * from play_evolutions;

で中身を見てみる。
出力はこんな感じだった。

id	hash	applied_at	apply_script	revert_script	state	last_problem
1	ad61f40497895f0274086616dc2bc8e519244216	2019-10-07 22:43:56	create table clients { id bigint auto_increment not null, name varchar(255), client_id varchar(255) not null, secret varchar(255) not null, email varchar(255) not null, user_id varchar(255) not null } insert into clients values (default, "Hiroro", "Hiroro0970", "hogehoge", "hiroro@exampple.com", "fuga298")	drop table clients	applying_up	You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '{ id bigint auto_increment not null, name varchar(255), client_id varchar(255) n' at line 1 [ERROR:1064, SQLSTATE:42000]

apply_scriptのカラムを見てみると、そこには修正前のSQLが!?
どうやら直接1.sqlを起動しているのではなく、このテーブル内のapply_scriptに記載されているクエリを実行しているようだった。

解決方法

play_evolutions内のデータを削除して、Playを再起動してみた。
すると、クエリが更新され、修正した1.sqlが正しく実行された。

以前H2DB(in-memory)を使ってEvolutionsしていた頃は再起動かけたらきちんと動作してくれたんだけど、よく考えたらそれはin-memoryDBは文字通りインメモリだから再起動かけたらデータベース自体が全て初期化されるからだ。

MySQLとかを使うときはクエリを更新かけたらplay_evolutionsテーブルのデータを削除してから再度試してみよう。

SQLの制約と指定方法

前回までの流れ

前回は、SQLの四大命令文についてまとめました。
→SQLの四大命令文まとめ

今回は制約についてまとめています。

SQLの制約とは？

制約とは、テーブルに格納するデータを制限する方法です。

データ型によって、

• 整数であるか？
• 文字列であるか？

といったデータの種類を限定することはできますが、それだけでは、

• 正数のデータのみを受け付ける
• 同じデータが重複してはいけない

とかといった制限を行うことができません。

制約はこのような制限を列やテーブルに対して定義することができます。

※「そもそも、なぜ制約が必要なのか？」と言う方はこちらのサイトがわかりやすいです。
→「分かりそう」で「分からない」でも「分かった」気になれるIT用語辞典

1.　制約の種類

• NOT NULL制約
• 一意性制約（UNIQUE KEY)
• 主キー制約 (PRIMARY KEY)
• 外部キー制約 (FOREIGN KEY)
• チェック制約

2.　制約と指定方法

特に使用頻度が高い、

• NOT NULL制約
• 主キー制約

を中心に説明します。

2.1　NOT NULL

• NULL値を禁止

CREATE TABLE user_table (
　　user_id 　　INT PRIMARY KEY,
　　user_name 　VARCHAR(50) NOT NULL,　     // ここ
　　tel_no 　　　VARCHAR(50) NOT NULL       // ここ
);

2.2　UNIQUE KEY(一意性制約)

• 重複した値を禁止
  一意性 = 「オンリーワンな値」　という意味で思ってもらえば大丈夫です。
  つまり、
  一意性制約 = 「他の行の値と重複する値はダメですよ〜」という制約。

• 複数の列に設定可能

• NULLを禁止するわけではない

以下の文は「電話番号とアドレスは他の人と被っちゃマズイよね〜」ということで、制約しています。

CREATE TABLE user_table (
　　user_id 　　INT PRIMARY KEY,
　　address     VARCHAR(255),
　　tel_no 　　　VARCHAR(50) ,
   UNIQUE (telephone, address)    // ここ　
);

2.3　PRIMARY KEY(主キー制約)

• 「列」、または「複数の列の組み合わせ」に対して指定
• 指定できるのは1つのテーブルに1つだけ
• 一意を保証
• 重複とNULLを禁止

つまり、「指定された列、または複数の列は重複もNULLもダメですよ〜」ということ。

CREATE TABLE user_table (
　　user_id 　　INT PRIMARY KEY,　　　 // ここ
　　user_name 　VARCHAR(50) NOT NULL,
　　tel_no 　　　VARCHAR(50) NOT NULL
);

2.4　FOREIGN KEY(外部キー制約)

• 他のテーブルの列を参照し、その列にある値以外は禁止

以下の例を見てみよう。
２つのテーブルがある。

■　生徒テーブル

CREATE TABLE student (
  student_id    INT PRIMARY KEY,
  student_name  VARCHAR(20),
  school_id     INT,
  age           INT
);

■　学校テーブル

CREATE TABLE school (
  school_id     INT PRIMARY KEY,
  school_name   VARCHAR(20)
);

といったときに、
生徒(student)テーブルのschool_idの値には、
学校(school)テーブルのschool_idの値を入れ、
「その生徒がどの学校に在籍しているかを紐づけたい」という場合がありますよね。

その場合は、生徒テーブルに外部キー制約を以下のように指定すれば良い。

■　生徒テーブル

CREATE TABLE student (
  student_id    INT PRIMARY KEY,
  student_name  VARCHAR(20),
  school_id     INT,
  age           INT

  //　以下の部分
  FOREIGN KEY (school_id) 
  REFERENCES  school (school_id)
);

解説

FOREIGN KEY (school_id)　　　 　・・・どの列に外部キーで制約をつけるのか（参照元）
REFERENCES school (school_id)　・・・どのテーブルの、どの列を参照するか（参照先）

つまり、以下が式になる。

FOREIGN KEY (制約をつけたい列名)
REFERENCES  参照したいテーブル (参照したい列名)

まとめ

以上、SQLの制約と指定方法についてのまとめでした。

間違ってる点があれば、ご指摘お願いします(^ ^)