MySQL基本知識

今回はMySQLに関する基本知識を紹介します。

MySQLサーバにアクセス
データベース関連
テーブル関連
データ関連
文字コード関連
データ型
カラムのプロパティ
SQLコメント
外部キー
ビュー
トランザクション
インデクス
関数
prepare文
ストアドプロシージャ
1. ストアドプロシージャ作成
2. ストアドプロシージャ削除
データバックアップ＆復旧
1. バックアップ
2. 復旧

MySQLサーバにアクセス

MySQLの端末ポート番号：3306

windows

windowsでMySQLサーバにアクセスする場合、クライアントが多いですが、以下の二つクライアントをお勧めします。

①Navicat
②MySQL-Front

WEBページ

phpMyAdminでMySQLをアクセスする。

Linux

Linuxではコマンドラインで実行します。

接続コマンド：
$: mysql -h ホスト名 -p 3306 -u ユーザ名 -p
ログアウト：
exit

データベース関連

データベース一覧取得

MySQL> show databases;

データベース作成

MySQL> create database study;
MySQL> create database if not exists study; // 存在チェック
MySQL> create database `create`; // 特殊文字
MySQL> create database emp charset=utf8; //文字コードを指定しない場合、デフォルト値は以下コマンドで確認できます。

MySQL> show variables like ‘character_set_%’; // データベースの文字コード確認

データ保存パス

設定ファイル「my.ini」ファイルにdatadirの設定値を確認する

データベース削除

MySQL> drop database study;
MySQL> drop database if exists study;

データベース作成コマンドの確認

MySQL> show create database study;

データベースの変更

MySQL> alter database study charset=utf8;

データベースの選択

MySQL> use study;

テーブル関連

テーブル作成

create table [if not exists] テーブル名 (
カラム名データ型 [null | not nulll] [default] [auto_increment] [primary key] [comment],
カラム名データ型 …
)[engine=DBエンジン][charset=文字コード] collate=文字比較方法

enginer: myisam,innodb デフォルト値：innodb

使用例：

create table study1 (
id int auto_increment primary key,
name varchar(29) not null
) engine=innodb charset=utf8;

全てテーブル表示

show tables;

テーブル作成コマンドの確認

show create table study1;

テーブル定義の確認

desc study1;

テーブル削除

drop table if exists study1;
drop table if exists study1, study2;

テーブルコピー

データ含む

create table study2 select id, name from study1;

テーブル定義のみ

create table study3 like study1;

テーブル変更

カラム追加

alter table テーブル名 add column カラム名データ型インデクス; インデクスのデフォルト値：最後

alter table study1 add column sex char(1) after id;

カラム削除

alter table テーブル名 drop カラム名;

カラム変更

alter table テーブル名 change 旧カラム名新カラム名データ型;

カラムのプロパティのみ変更

alter table テーブル名 modify カラム名プロパティ;

alter table study1 modify name char(29);

DBエンジンの変更

alter table テーブル名 engine=innodb;

テーブル名変更

alter table テーブル名rename to 新テーブル名;

テーブルを他のデータベースに移動

alter table テーブル名 rename to データベース.新テーブル名;

データ関連

検索

select カラム名 from テーブル名 where 条件 group by グループ order by ソートキー having 条件;

# すべてカラムを抽出する
select * from テーブル名;

# 指定なカラムを抽出する
select カラム名 from テーブル名;

# 計算
select 10*10 as result;
select rand();

dualテーブル

select 10*10 from dual;

whereの条件

> , < ,>= , <=, =, !=, and, or, not, in, not in, between..and, not between..and, is null, is not null

関数

sum(), avg(),max(), min(),count()

group by

select sex, avg(age) from stu group by sex;

order by

select sex, avg(age) from stu group by sex order by sex desc;

having

select * from stu where sex=’1′ having age>29;

limit

select * from stu limit 0,3;

distinct重複除外

select distinct age from stu;

like

select * from stu where name like ‘T_sat%’;

_：任意の１文字
%：任意文字

union重複除外

select文
union
select文
union
select文

union all重複除外しない

select文
union all
select文
union all
select文

inner join

#書き方①
select * from テーブル1 inner join テーブル2 on テーブル1.カラム名1=テーブル2.カラム名２ [inner join テーブル3 on テーブル2.カラム名1=テーブル3.カラム名２];

#書き方②
select * from テーブル1 ,テーブル2 whereテーブル1.カラム名1=テーブル2.カラム名２;

left join

左側のテーブルを基準にする

select * from テーブル1 left join テーブル2 on テーブル1.カラム名1=テーブル2.カラム名2;

right join

select * from テーブル1 right join テーブル2 on テーブル1.カラム名1=テーブル2.カラム名2;

cross join

select * from テーブル1 cross join テーブル2;
=select * from テーブル1,テーブル2:

#以下はinner joinと同じ
select * from テーブル1 cross join テーブル2 on テーブル1.カラム名1=テーブル2.カラム名2;

natural join

同じカラム名で結合

select * from テーブル1 natural join テーブル2;
select * from テーブル1 natural left join テーブル2;
select * from テーブル1 natural right join テーブル2;

using

結合カラムを指定する

select * from テーブル1 inner join テーブル2 using(カラム名);

サブクエリ

select * from stu where stu_no =(select max(ssid_no) from stu);

select * from stu where stu_no in (select stu_no from stu where ssid_no=’ss’);

select * from stu where (sex,age) in (select sex,max(age) from stu group by sex);

exists

select * from stu where exists (select 1 from stu_info where score>60);

削除

# 指定のデータを削除する
delete from テーブル名 where カラム名=”条件”;

# 全件削除(1件づつ削除する)
delete from テーブル名;

# 全件削除(古いテーブルを削除し、新しいテーブルを作成するため、効率高い)
truncate table テーブル名;

追加

# 一部カラムを省略できる
insert into テーブル名(カラム名1,カラム名2,カラム名3) values(値1,値2,値3);

# カラム名を指定しない場合、すべてカラムを設定する必要
insert into テーブル名 values(値1,値2,値3);

# nullまたはデフォルト値を設定する場合
insert into テーブル名 values(値1,null,default);

# 複数件データを登録する
insert into テーブル名 values (値1,null,default), (値1,値2,値3),(値1,値2,値3);

# Primary key重複の場合、削除してから登録
replace into テーブル名 values(値1,値2,値3);

# Primary key重複の場合、更新
insert into テーブル名 values (値1,null,default) on duplicate key update カラム名=値;

更新

# 1カラム更新
update テーブル名 set カラム名=設定値 where カラム名=”更新条件”;

# 複数カラム更新
update テーブル名 set カラム名=設定値,カラム名=設定値 where カラム名=”更新条件”;

文字コード関連

# 文字コードの一覧
show variables like ‘character_set%’;

#個別設定
set character_set_client=文字コード;
set character_set_results=文字コード;

# 一括設定
set names 文字コード;

データ型

数値型

中分類	型名	小分類	値の範囲	符号なしの範囲 unsigned	必要サイズ
整数型	TINYINT, INT1	1バイト整数	-128～127	0～255	1 byte
	SMALLINT, INT2	2バイト整数	-32768～32767	0～65535	2 byte
	MEDIUMINT, INT3	3バイト整数	-8388608～8388607	0～16777215 (1677万)	3 byte
	INT, INTEGER, INT4	4バイト整数	-2147483648～2147483647	0～4294967295 (43億)	4 byte
	BIGINT, INT8	8バイト整数	-9223372036854775808～	0～18446744073709551615	8 byte
	BIGINT, INT8	8バイト整数	9.22337E+18	(1844京)	8 byte
科学計算用小数点数	FLOAT	単精度浮動小数点数	-3.402823466E+38～	0～3.402823466E+38	4 bytes
	FLOAT(p) p：0～24 ※1		3.40E+38	4.0より古いと符号無しにはできない
	FLOAT4
	DOUBLE	倍精度浮動小数点数	-1.7976931348623157E+308～	0～1.7976931348623157E+308	8 bytes
	FLOAT(p) p：25～53 ※1		1.7976931348623157E+308	4.0より古いと符号無しにはできない
	FLOAT8
	DOUBLE PRECISION
	REAL
	FLOAT(M,D)	(桁指定)単精度	M：1～255	負の値が使えなくなる	4 bytes
	FLOAT(M,D)	浮動小数点数	D：0～30 かつ M ≧ D	4.0より古いと符号無しにはできない	4 bytes
	DOUBLE(M,D)	(桁指定)倍精度	M：1～255	負の値が使えなくなる	8 bytes
	DOUBLE PRECISION(M,D)	浮動小数点数	D：0～30 かつ M ≧ D	4.0より古いと符号無しにはできない
	REAL(M,D)
事務計算用小数点数	DECIMAL(M,D)	固定小数点数	M：1～65 ※2	負の値が使えなくなる	※3
	DEC(M,D)		D：0～30 かつ M ≧ D	4.0より古いと符号無しにはできない
	NUMERIC(M,D)
	FIXED(M,D)
その他	BIT(M)	BIT型	M：1～64	UNSIGNEDは指定できない	約 (M+7)/8 bytes
	BOOL	真偽値型	-128～127	BOOL, BOOLEANの場合にはUNSIGNEDは指定できない	1 byte
	BOOLEAN
	TINYINT(1)

文字型

中分類	型名	小分類	値の範囲	必要サイズ
文字列型	CHAR(M)	固定長文字列	255文字以下	M × w bytes, 0 <= M <= 255, where w is the number of bytes required for the maximum-length character in the character set.
	VARCHAR(M)	可変長文字列	6万5535バイト(64KB)以下	L + 1 bytes if column values require 0 ～ 255 bytes, L + 2 bytes if values may require more than 255 bytes
	TINYTEXT	テキスト型	255バイト以下	L + 1 bytes, where L < 2^8
	TEXT		6万5535バイト(64KB)以下	L + 2 bytes, where L < 2^16
	MEDIUMTEXT		1677万7215バイト(16MB)以下	L + 3 bytes, where L < 2^24
	LONGTEXT		42億9496万7295バイト(4GB)以下	L + 4 bytes, where L < 2^32
列挙型	ENUM(‘value1′,’value2’,…)	ENUM型	65,535候補。1つ選択	1 or 2 bytes, depending on the number of enumeration values (65,535 values maximum)
列挙型	SET(‘value1′,’value2’,…)	SET型	64候補。複数選択可	1, 2, 3, 4, or 8 bytes, depending on the number of set members (64 members maximum) ※
バイナリ型	BINARY(M)	固定長バイナリ型	255バイト以下	M bytes, 0 <= M <= 255
	VARBINARY(M)	可変長バイナリ型	6万5535バイト(64KB)以下	L + 1 bytes if column values require 0 ～ 255 bytes, L + 2 bytes if values may require more than 255 bytes
	TINYBLOB	バイナリ・ラージ・オブジェクト	255バイト以下	L + 1 bytes, where L < 2^8
	BLOB	Binary Large OBject	6万5535バイト(64KB)以下	L + 2 bytes, where L < 2^16
	MEDIUMBLOB		1677万7215バイト(16MB)以下	L + 3 bytes, where L < 2^24
	LONGBLOB		42億9496万7295バイト(4GB)以下	L + 4 bytes, where L < 2^32

ENUM型

create table テーブル名(name varchar(8), cc enum(‘Blue’, ‘Yellow’, ‘White’));

SET型

create table テーブル名(name varchar(8), cc SET(‘Blue’, ‘Yellow’, ‘White’));

日時型

型名
DATE	日付型、年月日型
TIME	時刻型
DATETIME	日時型
TIMESTAMP	タイムスタンプ型

カラムのプロパティ

SQLコメント

create table aa (
name varchar(29), –コメント
age int, # コメント
demo varchar(29)
/*
* 複数行コメント
*/
);

外部キー

innodbで外部キーを作成できます。

テーブル作成時に外部キー作成

create table stu(
id int primary key,
name varchar(20)
);
create table stuinfo(
sid init primary key,
score tinyint unsigned,
foreign key(sid) references stu(id) –外部キー作成
);

テーブル変更で外部キー追加

alter table テーブル名 add foreign key(キーカラム) references 引用テーブル(カラム名);

外部キーの削除

外部キー名の取得

show create table テーブル名\G

外部キー削除

alter table テーブル名 drop foreign key 外部キー名;

外部キーのデータ自動更新

主テーブルのデータを削除すると、外部キーのデータをNULLに設定する

create table stu(
id int primary key,
name varchar(20)
);
create table stuinfo(
sid init primary key,
score tinyint unsigned,
foreign key(sid) references stu(id) on delete set null on update cascade –外部キー作成
);

ビュー

作成

create view algorithm=temptable ビュー名
as select文;

algorithmデフォルト値:merge

変更

alter view ビュー名
as
select 文;

削除

drop view ビュー名1,ビュー名2;

ビュー一覧

select * from information_schema.views;
show tables;
show tables status\G;
show table status where comment=’view’\G;
desc view名;

show create view view名;

トランザクション

start transaction または begin

commit; またはrollback;

savepoint xxxx;

rollback to xxxx;

インデクス

primary keyは自動的にインデクスを作成されます。

unique index

# テーブル作成時
create table t1 (
id int primary key,
name varchar(29),
unique uq_name(name)
);

# テーブル変更
create unique index uq_name on t1(name);
または
alter table t1 add unique index uq_name (name);

index

# テーブル作成時
create table t2(
id int primary key,
name varchar(29),
index ix_name(name)
);

# テーブル変更
create index uq_name on t1(name);
または
alter table t1 add index uq_name (name);

index削除

drop index index名 on テーブル名;

関数

rand()

select * from stud order by rand();

round()

select round(3.1415,3);
#3.142

truncate()

select truncate(3.1415,3);
# 3.141

ceil()

select ceil(3.1415);
# 4

floor()

select floor(3.9415);
# 3

ucase()

select ucase(‘asdf’);
# ASDF

lcase()

select ucase(‘ASDF’);
# asdf

left()

select left(‘abcdef,3);
#abc

right()

select right(‘abcdef,3);
#def

substring()

select right(‘abcdef,2, 3);
#bcd

concat()

select concat(‘as’,’bc’,’dd’);
# asbcdd

coalesce()

select coalesce(null,’123′);
# 123

select coalesce(’23’,’123′);
# 23

length(),char_length()

select length(‘漢字’),char_length(‘漢字’);
# 6,2

unix_timestamp(),from_unixtime()

select from_unixtime(unix_timestamp());
#2021-06-21 06:23:36

now()

select now();
#2021-06-21 06:23:36

year(),month(),day(),hour(),minute(),second()

select year(now()),month(now()),day(now()),hour(now()),minute(now()),second(now());
#2021 | 6 | 21 | 6 | 25 | 37 |

dayname(),dayofyear()

select dayname(now()),dayofyear(now());
#Monday | 172

datediff()

select datediff(now(),’2021-01-20′);
#152

md5(),sha()

select md5(‘aa’),sha(‘bb’);
#4124bc0a9335c27f086f24ba207a4912 | 9a900f538965a426994e1e90600920aff0b4e8d2

prepare文

prepare 名称 from ‘select * from テーブル名 where カラム名=?’;

変数定義 @id
set @id=’1111′;

execute 名称　[using 変数]

ストアドプロシージャ

ストアドプロシージャ作成

# ストアドプロシージャ作成
delimiter //
CREATE PROCEDURE プロシージャ名()
BEGIN
SET @変数1 = 10;
SET @変数2 = 0;
# WHILEループ
WHILE @変数1 > @変数2 DO
#処理内容
SET @pos = @pos + 1;
END WHILE;
END
//
delimiter ;

ストアドプロシージャ削除

DROP PROCEDURE IF EXISTS ストアドプロシージャ名;

データバックアップ＆復旧

バックアップ

mysqldump -u ユーザ名 -p パスワード data> ファイル
# テーブル指定
mysqldump -u ユーザ名 -p パスワード data テーブル名> ファイル
#データベースも対象
mysqldump -u ユーザ名 -p パスワード -B data> ファイル

復旧

#方法１
source ファイル;
#方法2
mysql -u ユーザ名 -p パスワードデータベース名 < ファイル;