MySQL基本知識

MySQL

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

  1. MySQLサーバにアクセス
    1. windows
    2. WEBページ
    3. Linux
  2. データベース関連
    1. データベース一覧取得
    2. データベース作成
    3. データ保存パス
    4. データベース削除
    5. データベース作成コマンドの確認
    6. データベースの変更
    7. データベースの選択
  3. テーブル関連
    1. テーブル作成
    2. 全てテーブル表示
    3. テーブル作成コマンドの確認
    4. テーブル定義の確認
    5. テーブル削除
    6. テーブルコピー
      1. データ含む
      2. テーブル定義のみ
    7. テーブル変更
      1. カラム追加
      2. カラム削除
      3. カラム変更
      4. カラムのプロパティのみ変更
      5. DBエンジンの変更
      6. テーブル名変更
      7. テーブルを他のデータベースに移動
  4. データ関連
    1. 検索
    2. dualテーブル
    3. whereの条件
    4. 関数
    5. group by 
    6. order by 
    7. having
    8. limit
    9. distinct重複除外
    10. like
    11. union重複除外
    12. union all重複除外しない
    13. inner join
    14. left join
    15. right join
    16. cross join
    17. natural join
    18. using
    19. サブクエリ
    20. exists
    21. 削除
    22. 追加
    23. 更新
  5. 文字コード関連
  6. データ型
    1. 数値型
    2. 文字型
    3. ENUM型
    4. SET型
    5. 日時型
  7. カラムのプロパティ
  8. SQLコメント
  9. 外部キー
    1. テーブル作成時に外部キー作成
    2. テーブル変更で外部キー追加
    3. 外部キーの削除
      1. 外部キー名の取得
      2. 外部キー削除
      3. 外部キーのデータ自動更新
  10. ビュー
    1. 作成
    2. 変更
    3. 削除
    4. ビュー一覧
  11. トランザクション
  12. インデクス
    1. unique index
    2. index
    3. index削除
  13. 関数
    1. rand()
    2. round()
    3. truncate()
    4. ceil()
    5. floor()
    6. ucase()
    7. lcase()
    8. left()
    9. right()
    10. substring()
    11. concat()
    12. coalesce()
    13. length(),char_length()
    14. unix_timestamp(),from_unixtime()
    15. now()
    16. year(),month(),day(),hour(),minute(),second()
    17. dayname(),dayofyear()
    18. datediff()
    19. md5(),sha()
  14. prepare文
  15. ストアドプロシージャ
    1. ストアドプロシージャ作成
    2. ストアドプロシージャ削除
  16. データバックアップ&復旧
    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%’;

_: 任意の1文字
%:任意文字

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.カラム名2 [inner join テーブル3 on テーブル2.カラム名1=テーブル3.カラム名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;

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
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)
/*
*  複数行コメント
*/
);

外部キー

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> ファイル

復旧

#方法1
source ファイル;
#方法2
mysql -u ユーザ名 -p パスワード データベース名 < ファイル;
タイトルとURLをコピーしました