MySQL備考録 | Kenta Kozuka

Table of Contents

OS: Fedora 25

参考にしたもの

コマンド https://jinzaiipedia.ipa.go.jp/wp-content/uploads/oss/subject1-1_lesson.pdf http://webkaru.net/mysql/mysql-root-password/ http://mysqlweb.net/ http://qiita.com/tamuuu/items/c65ae417877ae01b132c http://qiita.com/ques0942/items/d89de1ff7c92b4810d10 http://d.hatena.ne.jp/yk5656/20141204/1426176800

正規化 https://support.office.com/ja-jp/article/%E3%83%87%E3%83%BC%E3%82%BF%E3%83%99%E3%83%BC%E3%82%B9%E8%A8%AD%E8%A8%88%E3%81%AE%E5%9F%BA%E6%9C%AC-eb2159cf-1e30-401a-8084-bd4f9c9ca1f5#bmnormalization

データ型 https://www.dbonline.jp/mysql/type/ http://mysql.akarukutanoshiku.com/category5/entry21.html

リファレンス（ver.5.6） https://dev.mysql.com/doc/refman/5.6/ja/

#サービスの構築インストール

sudo yum install mysql mysql-server -y

デーモンの起動

systemctl start mysqld

このrootユーザーは、システムのrootユーザーとは別

sudo mysql -u root -p
[sudo] password for <ユーザ名>: 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.7.17 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> quit
Bye

MySQLサーバは3306番ポートを利用します。 3306番ポートが開かれているか確認

ログイン

sudo mysql -u root -p

#DBの操作

##DBの表示

SHOW DATABASES;

DBの作成

CREATE DATABASE <DB-name> CHARACTER SET <char-code>;

##使用するDBの選択

USE <DB-name>;

#文字コード

##文字コード表示

show variables like 'character%';

##文字コードの変換設定ファイルを編集

vim /etc/my.cnf
...
[mysqld]
...
character-set-server=utf8mb4

[client]
default-character-set=utf8mb4

デーモンを再起動

# systemctl restart mysqld

#テーブルの操作

##テーブルの表示

SHOW TABLES;

##テーブルの作成

CREATE TABLE <table-name> (
   ID AUTO_INCREMENT,
　　<field-name1> <table-type(<number-of-char>),
　　<field-name2> <table-type(<number-of-char>),
    ... ,
　　#作成日時
　　CREATED_DATETIME timestamp not null default current_timestamp,
　　#更新日時
　　UPDATE_DATETIME timestamp not null default current_timestamp on update current_timestamp,
　　#主キー
　　PRIMARY KEY (<field-name1>),
　　INDEX(<field-name1>)
) character set utf8mb4;

#カラムの操作

##テーブルのカラムを表示

SHOW COLUMNS FROM <table-name>;

##テーブルの各カラムの情報を表示

DESCRIBE <table-name>;

title	description
Field	フィールド名
Type	データ型
Null	Nullを許可するかどうか
Key	主キー
Default	初期値
Extra	オートインクリメントなど

#フィールドの操作

##新しいフィールドを追加

ALTER TABLE <table-name> ADD <new-field-name> <table-type(<number-of-char>);

##データ型

###整数型

type	range
TINYINT	-128から127 (符号無しの場合0から255)
SMALLINT	-32768から32767 (符号無しの場合0から65535)
MEDIUMINT	-8388608から8388607 (符号無しの場合0から16777215)
INT	-2147483648から2147483647 (符号無しの場合0から4294967295)
BIGINT	-9223372036854775808から9223372036854775807 (符号無しの場合0から18446744073709551615)

###浮動小数点数型

type	range
FLOAT	-3.402823466E+38 から -1.175494351E-38
DOUBLE	-1.7976931348623157E+308 から -2.2250738585072014E-308

###日付型と時刻型

type	format	range
DATE	‘YYYY-MM-DD’	‘1000-01-01’ から ‘9999-12-31’
DATETIME	‘YYYY-MM-DD HH:MM:SS’	‘1000-01-01 00:00:00’ から ‘9999-12-31 23:59:59’
TIMESTAMP	‘YYYY-MM-DD HH:MM:SS’	‘1970-01-01 00:00:01’ から ‘2037-12-31 23:59:59’
TIME	‘HH:MM:SS’	‘-838:59:59’ から ‘838:59:59’
YEAR[(2or4)]	YYYY	1901 から 2155、そして 0000 (4桁の場合). 70 から 69 (2桁の場合、70は1970を表し69は2069を表す). デフォルトは4桁

CHAR型とVARCHAR型

CHAR(M)

固定長文字列 Mは文字数を指定。0から255文字別名：CHAR(M)はCHARACTER(M)の別名

VARCHAR(M)

可変長文字列 Mはカラムの最大長を文字数で表します。Mの範囲は 0 から 65,535。別名：VARCHAR(M)はCHARACTER VARYING(M)の別名

###BINARY型とVARBINARY型

BINARY[(N)]

固定長バイナリバイト文字列 Nはバイトを指定。0から255文字別名：CHAR BYTE

VARBINARY(M)

可変長バイナリバイト文字列 Mはバイト数を指定。0から65535バイト

###BLOB型とTEXT型

BLOB型はバイナリデータを扱うデータ型で格納できるデータのサイズを指定しない。ただ最大長は決まっており、最大長の種類によって次の4つが用意されている。

TINYBLOB

最長255 (28 - 1) バイト

BLOB[(M)]

最長65,535 (216 - 1) バイト

MEDIUMBLOB

最長16,777,215 (224 - 1) バイト

LONGBLOB

最長4,294,967,295、または4GB (232 - 1) バイト

TEXT型は文字列データを扱うデータ型で格納できるデータのサイズを指定しない。ただ最大長は決まっており、最大長の種類によって次の4つが用意されている。

TINYTEXT

最長255 (28 - 1) バイト

TEXT[(M)]

最長65,535 (216 - 1) バイト

MEDIUMTEXT

最長16,777,215 (224 - 1) バイト別名：LONG, LONG VARCHAR

LONGTEXT

最長4,294,967,295、または4GB (232 - 1) バイト

BLOG型及びTEXT型はそれぞれ巨大なバイナリデータとテキストデータを格納する時に利用される。
BLOB型はVARBINARY型で格納できるバイト数を指定しなかったものと考えてよい。データはバイナリデータとしてそのまま格納される。
TEXT型はVARCHAR型で格納できるバイト数を指定しなかったものと考えてよい。データは文字列データとして格納される。

###ENUM型

ENUM(‘value1’,‘value2’,…)

単一選択リスト ENUM型では文字列定数をリストします。このデータ型が設定されたカラムに値を格納する場合、このリストの中の文字列定数の一つを指定します。(例外はあります)。またリストできる値は65,535までです。

###SET型

SET(‘value1’,‘value2’,…)

複数選択リスト SET型では文字列定数をリストします。このデータ型が設定されたカラムに値を格納する場合、このリストの中の0個以上の定数を指定します。(2個以上指定する場合はカンマで区切ります)。またリストできる値は64までです。

#ユーザの操作

ユーザの追加

CREATE USER <user-name>@localhost IDENTIFIED BY "<password>";

MEDIUM ポリシーは、パスワードが最低 1 つの数値文字を含み、1 つの小文字および大文字を含み、1 つの特殊文字 (英数字以外) を含む必要があるという条件を追加します。

ユーザの参照

SELECT user FROM mysql.user;

#権限の操作

権限の付与

GRANT ALL ON <DB-name>.* TO <user-name>@localhost;

ユーザの権限を参照

SHOW GRANTS FOR <user-name>@localhost;

#レコードの操作

##INSERT フィールドの指定

INSERT INTO <table-name> (<field-name1>, <field-name2>) VALUES(value1,value2);

フィールドを省略して順番で指定

INSERT INTO <table-name> VALUES(value1, value2);

次は一度に複数レコードを追加

INSERT INTO T01Prefecture (<field-name1>, <field-name2>)
VALUES(value1, value2),(value1, value2),(value1, value2);

更新(UPDATE)とよく似た書き方フィールド名と値の対応が明確

INSERT INTO <table-name> SET <field-name1> = <value1>, <field-name2> = <value2>;

##SELECT

SELECT * FROM <table-name>;

結合

内部結合 INNER JOIN

左右のテーブルの指定したフィールドが一致するレコードのみ取得する

外部結合 OUTER JOIN

左右のテーブルの指定したフィールドが一致するレコードとどちらからにだけ値が存在するレコードを取得する

LEFT (OUTER) JOIN

左側のレコードはすべて表示する。右側のレコードは値が一致したレコードだけ表示する。

SELECT * FROM <table-name1> OUTER JOIN <table-name2> ON <table-name1>.<column-name>=<table-name2>.<column-name>

LEFT (OUTER) JOIN

右側のレコードはすべて表示する。左側のレコードは値が一致したレコードだけ表示する。

##DELETE

レコード全件削除

DELETE FROM <table-name>;

##UPDATE

UPDATE <table-name> SET <field-name1> = <value1> WHERE <field-name2> = <value>;

#CSVからレコード取り込み

文字コード要確認

LOAD DATA LOCAL INFILE '<file-path>'
INTO TABLE <table-name>
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
(column1, column2);

#外部ファイルからコマンド読み込み

source example.sql

#コメント

コード	description
#	#から始まって行末まで（改行不可）
–	– から始まって行末まで(–の後は半角スペース、改行不可）
/* */	/から/までのコメント(改行可能）