今回はMySQLに関する基本知識を紹介します。
MySQLサーバにアクセス
MySQLの端末ポート番号:3306
windows
windowsでMySQLサーバにアクセスする場合、クライアントが多いですが、以下の二つクライアントをお勧めします。
①Navicat
②MySQL-Front
WEBページ
phpMyAdminでMySQLをアクセスする。
Linux
Linuxではコマンドラインで実行します。
接続コマンド:
$: mysql -h ホスト名 -p 3306 -u ユーザ名 -p
ログアウト:
exit
$: 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_%’; // データベースの文字コード確認
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> 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
カラム名 データ型 [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;
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;
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);
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 * from テーブル名;
# 指定なカラムを抽出する
select カラム名 from テーブル名;
# 計算
select 10*10 as result;
select rand();
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%’;
_: 任意の1文字
%:任意文字
_: 任意の1文字
%:任意文字
union重複除外
select文
union
select文
union
select文
union
select文
union
select文
union all重複除外しない
select文
union all
select文
union all
select文
union all
select文
union all
select文
inner join
#書き方①
select * from テーブル1 inner join テーブル2 on テーブル1.カラム名1=テーブル2.カラム名2 [inner join テーブル3 on テーブル2.カラム名1=テーブル3.カラム名2];
select * from テーブル1 inner join テーブル2 on テーブル1.カラム名1=テーブル2.カラム名2 [inner join テーブル3 on テーブル2.カラム名1=テーブル3.カラム名2];
#書き方②
select * from テーブル1 ,テーブル2 whereテーブル1.カラム名1=テーブル2.カラム名2;
select * from テーブル1 ,テーブル2 whereテーブル1.カラム名1=テーブル2.カラム名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;
=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;
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 テーブル名;
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 カラム名=値;
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 カラム名=”更新条件”;
update テーブル名 set カラム名=設定値 where カラム名=”更新条件”;
# 複数カラム更新
update テーブル名 set カラム名=設定値,カラム名=設定値 where カラム名=”更新条件”;
文字コード関連
# 文字コードの一覧
show variables like ‘character_set%’;
#個別設定
set character_set_client=文字コード;
set character_set_results=文字コード;
# 一括設定
set names 文字コード;
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 | |
9.22337E+18 | (1844京) | ||||
科学計算用小数点数 | 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 | |
浮動小数点数 | D:0~30 かつ M ≧ D | 4.0より古いと符号無しにはできない | |||
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 | タイムスタンプ型 |
カラムのプロパティ
null | not null | default | auto_increment | primary key| unique | comment
SQLコメント
create table aa (
name varchar(29), –コメント
age int, # コメント
demo varchar(29)
/*
* 複数行コメント
*/
);
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) –外部キー作成
);
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 –外部キー作成
);
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
as select文;
algorithmデフォルト値:merge
変更
alter view ビュー名
as
select 文;
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名;
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;
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);
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);
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
#3.142
truncate()
select truncate(3.1415,3);
# 3.141
# 3.141
ceil()
select ceil(3.1415);
# 4
# 4
floor()
select floor(3.9415);
# 3
# 3
ucase()
select ucase(‘asdf’);
# ASDF
# ASDF
lcase()
select ucase(‘ASDF’);
# asdf
# asdf
left()
select left(‘abcdef,3);
#abc
#abc
right()
select right(‘abcdef,3);
#def
#def
substring()
select right(‘abcdef,2, 3);
#bcd
#bcd
concat()
select concat(‘as’,’bc’,’dd’);
# asbcdd
# asbcdd
coalesce()
select coalesce(null,’123′);
# 123
select coalesce(’23’,’123′);
# 23
# 123
select coalesce(’23’,’123′);
# 23
length(),char_length()
select length(‘漢字’),char_length(‘漢字’);
# 6,2
# 6,2
unix_timestamp(),from_unixtime()
select from_unixtime(unix_timestamp());
#2021-06-21 06:23:36
#2021-06-21 06:23:36
now()
select now();
#2021-06-21 06:23:36
#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 |
#2021 | 6 | 21 | 6 | 25 | 37 |
dayname(),dayofyear()
select dayname(now()),dayofyear(now());
#Monday | 172
#Monday | 172
datediff()
select datediff(now(),’2021-01-20′);
#152
#152
md5(),sha()
select md5(‘aa’),sha(‘bb’);
#4124bc0a9335c27f086f24ba207a4912 | 9a900f538965a426994e1e90600920aff0b4e8d2
#4124bc0a9335c27f086f24ba207a4912 | 9a900f538965a426994e1e90600920aff0b4e8d2
prepare文
prepare 名称 from ‘select * from テーブル名 where カラム名=?’;
変数定義 @id
set @id=’1111′;
execute 名称 [using 変数]
変数定義 @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 ;
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> ファイル
# テーブル指定
mysqldump -u ユーザ名 -p パスワード data テーブル名> ファイル
#データベースも対象
mysqldump -u ユーザ名 -p パスワード -B data> ファイル
復旧
#方法1
source ファイル;
#方法2
mysql -u ユーザ名 -p パスワード データベース名 < ファイル;
source ファイル;
#方法2
mysql -u ユーザ名 -p パスワード データベース名 < ファイル;