MySQL 基礎文法

はじめに

こちらでまとめた内容とほぼ同じです。

MySQL 基礎文法 - Endo Tech Blog

ブログだとやはり見辛いので、Qiitaにも書きます

開発環境について

開発環境はMacを使ってます。
MySQLのバーションは

mysql  Ver 14.14 Distrib 5.7.21, for osx10.13 (x86_64) using  EditLine wrapper

MySQL とは?

• MySQL は高速性と堅牢性を追及したマルチユーザ・マルチスレッドのＳＱＬデータベースです。
• 世界で最も人気のあるオープンソースデータベースです。

ref:MySQL

用語説明

• 行:レコード(Record/Row)
  • データ
  • 横
• 列:フィールド(Fields/Column)
  • 属性値
  • 縦
  • カラム
• SQL(Structured Query Language)
  • Database や Table、 Field や Record を扱う言語を SQL （ Structured Query Language ）と呼ぶ

基本的なコマンド

MySQLのクエリは大文字、小文字が区別されないです。

MySQLが動いているか確認する

$ mysql.server status
SUCCESS! MySQL running (581)

ログイン

ルートユーザでログインする

mysql -uroot

現在のユーザを確認する

mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

接続を切る

 \q

もしくは

 exit;

データベースへの操作

データベースの一覧を取得する

 mysql> show databases
 +--------------------+
| Database           |
+--------------------+
| information_schema |
| homestead          |
| laravel_app        |
| laravel_tutorial   |
+--------------------+

データベースを作成する

create database dotinstall_db
Query OK, 1 row affected (0.03 sec)

データベースを削除する

drop database dotinstall_db;
Query OK, 0 rows affected (0.00 sec)

データベースの操作対象を切り替える

useコマンドを使う

選択されてない状態

mysql> select database();
+------------+
| database() |
+------------+
| NULL       |
+------------+

選択する

mysql> use dotinstall_db;
Database changed
mysql> select database();
+---------------+
| database()    |
+---------------+
| dotinstall_db |
+---------------+
1 row in set (0.00 sec)

作業ユーザを作成して権限を与える

ユーザを作成する

create user dbuser@localhost identified by '****';

ユーザ名dbuser@localhostにdot_installDBに対する操作権限を全て与える

grant all on dotinstall_db.* to dbuser@localhost;

ログイン

mysql -u dbuser -p
パスワード入力

ユーザを確認する

mysql> select user();
+------------------+
| user()           |
+------------------+
| dbuser@localhost |
+------------------+
1 row in set

ユーザを削除する

※ルートユーザで作業を行う

drop user dbuser@localhost

外部からSQLを実行する

リダイレククションで実行する

mysql -u root < mysql/create_mysql.sql

mysqlサーバに入ってsorce or \.コマンドを実行する

mysql> \. ./mysql/create_mysql.sql
Query OK, 0 rows affected (0.01 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

テーブルへの操作

テーブル一覧を見る

mysql> show tables;
+-----------------+
| Tables_in_myapp |
+-----------------+
| users           |
+-----------------+

テーブル構造を見る

descコマンド

mysql> desc users;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| id    | int(10) unsigned | YES  |     | NULL    |       |
| name  | varchar(20)      | YES  |     | NULL    |       |
| score | float            | YES  |     | NULL    |       |
+-------+------------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

テーブルの削除方法

drop <table_name>

mysql> drop table users;
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
Empty set (0.00 sec)

データ型について

• Number
  • int:整数
  • float:浮動小数
  • double:倍精度浮動小数点
  • int unsigned (マイナスの領域は使わず、プラスの領域だけを使う)
• String
  • char:固定
  • varchar:可変長
  • text:長さがわからない大きいデータの場合
• Date/Time
  • date:日付
  • time:時間
  • datetime:日時
    • 2017-07-22 17:23:33
• True/False
  • boolean == tinyint(1)
    • trueは1(空文字を含むそれ以外の値は全て true になる)
    • falseは0と管理されれている

ref:MySQL :: MySQL 5.6 リファレンスマニュアル :: 11 データ型

フィールドの属性について

• not null : nullは入らない
• default : デフォルト値を設定できる
• unique: 重複した値を許さない
• primary key:レコードを一意に特定できるようなフィールドには、主キーを付けることが推奨されています。
  • これを入れるだけで、nullでなく、unique(重複しない値)である事が保証されます。
  • 主キー属性のフィールドには自動的にUNIQUE & NOT NULL属性がつきます。
• auto_increment:自動で連番にしてくれる

Alter テーブル構造の変更

フィールドを後から追加する

• alterコマンドを使って属性やカラムを追加する

-- フィールドの属性を後から追加する
alter table users add column email varchar(255);

• afterを指定すると、特定のカラムの後ろに追加する事ができる

-- フィールドの属性を後から追加する
alter table users add column email varchar(255) after name;

フィールドを後から削除する

alter table users drop column score ;

フィールドのデータの型や名前を変えたい場合

alter table users change name user_name varchar(80) default 'nobody';

テーブルの名前を変えたい場合

alter table users rename persons;

テーブルのフィールド名を変更せずにデータ型を変更したい

alter table テーブル名 modify フィールド名 データ型

alter table users modify user_id int(20) not null;

Select レコード抽出

• slect * from table_name:全件表示
• select id,name ftom table_name:カラムを指定して表示
• where:条件付きで抽出する
• 論理演算
  • x<=y:xはy以下である
  • x>=yxはy以上である
  • <> or != 等しくない
• null
  • is null:nullか
  • is not nullnullではない
• where column between X and Y:X~Yの範囲に含まれているか
• in: () の中の値のどれか
• like:部分一致
  • %:0文字以上のワイルドカード
    • %endu%:enduを含む
    • %ですね:ですねで終わる文字列
• binaly:SQLは大文字、小文字を区別できないがbinaly演算子を使うと厳密に比較をしてくれる
• _:任意の文字を指定できる
  • _e%:名前の2文字目がeの人
• order by:並び替えを行う
  • desc:降順
  • ack:昇順
• limit:件数を絞る
• offset:取得開始地点を指定ずる

sql_practice/select.sql at master · Fendo181/sql_practice

Update レコードの更新

update <table_name> set <column_name>コマンドを使う

• whereでレコードを指定して更新する

update users set score = 5.7 where id = 1;
update users set name = 'tanaka', score = 6.0 where id = 1;

• whereでレコードを指定しなければ、条件にマッチするレコードを全て更新する

Delete レコードの削除

全件削除

delete from users

スコアが5.0以下のレコードを削除する

 delete from users where score < 5.0;

sql_practice/update.sql at master · Fendo181/sql_practice

組み込み関数

数値計算

• rand(5.355):5
  • 四捨五入
• rand(5.355,1):5.4
  • 小数点 1 桁目で丸めたい場合
• floor(5.355):5
  • 小数点以下切り捨てにできる
• ceil(5.355)):6
  • 小数点以下を切りあげ
• rand()
  • 乱数

select rand(5.355); -- 5
select rand(5.355, 1); -- 5.4
select floor(5.833); -- 5
select ceil(5.238); -- 6
select rand();

スコアをランダムの値でソートして、一番上の値を抽出する際のSQL

select * from users order by rand() limit 1;

文字列の演算

• length:長さを取得する
• substr:文字を切り取る
• upper:文字列を大文字にする
• lower:小文字にする
• concat:文字列を連結する

そのほかに使える関数はこちらをご覧下さい。

ref:MySQL :: MySQL 5.6 リファレンスマニュアル :: 12 関数と演算子

sql_practice/string.sql at master · Fendo181/sql_practice

enum型

複数の値から、1つだけが格納できるようにする。
MySQL の設定にもよりますが、この中にある値以外をはじくことができます。
また内部的には連番で振られているので、1や2で指定できます。

enum型のtableを作成する

create table users (
  id int unsigned primary key auto_increment,
  name varchar(20),
  score float,
  rank enum('gold', 'silver', 'bronze')
);

sql_practice/enum.sql at master · Fendo181/sql_practice

set型

予め指定した値の中から複数の値を格納できるようにする。
enumと違って複数値を入れられます。ただし、Insert時に順番を変えたとしても、create table で定義した順番に戻っている。
したがって抽出時にはこちらの順番を守りつつ条件を指定してあげる必要があります。

alter table users add column coins set('gold','silver','bronz')
insert into users (name, score, coins) values ('taguchi', 5.8, 'silver,gold');
insert into users (name, score, coins) values ('taguchi', 5.8, 'bronz,gold');
-- ERROR 1265 (01000): Data truncated for column 'coins' at row 1
insert into users (name, score, coins) values ('taguchi', 5.8, 'red,gold');

sql_practice/set.sql at master · Fendo181/sql_practice

if,case文

if文を使った例

-- if文で分岐する
select
    name,
    score,
    -- 5.0以上だったらOKでそれよりも下だったらNGになる
    if (score > 5.0 , 'OK' , 'NG' ) as result
from
    users;

結果

+---------+-------+--------+
| name    | score | result |
+---------+-------+--------+
| endu    |   5.8 | OK     |
| koji    |   5.7 | OK     |
| kikuchi |   4.9 | NG     |
| hoge    |     1 | NG     |
| tomita  |  0.28 | NG     |
| sakita  |     3 | NG     |
+---------+-------+--------+

case文の例

-- case文で分岐する
select
    name,
    score,
    -- 5.0以上だったらOKでそれよりも下だったらNGになる
    case floor(score) % 2
        -- 2で割れたら偶数
        when 0 then '偶数'
        -- 2で割られなかったら奇数
        when 1 then '奇数'
        else NULL
    end as type
from
    users;

結果

+---------+-------+--------+
| name    | score | type   |
+---------+-------+--------+
| endu    |   5.8 | 奇数   |
| koji    |   5.7 | 奇数   |
| kikuchi |   4.9 | 偶数   |
| hoge    |     1 | 奇数   |
| tomita  |  0.28 | 偶数   |
| sakita  |     3 | 奇数   |
+---------+-------+--------+

case文ではwhenの後直接、条件分岐がかける

select
    name,
    score,
    -- 5.0以上だったらOKでそれよりも下だったらNGになる
    case
        when score > 5.0 then 'Team-A'
        when score > 4.0 then 'Team-B'
        else 'Team-C'
    end as TeamName
from
    users;

結果

+---------+-------+----------+
| name    | score | TeamName |
+---------+-------+----------+
| endu    |   5.8 | Team-A   |
| koji    |   5.7 | Team-A   |
| kikuchi |   4.9 | Team-B   |
| hoge    |     1 | Team-C   |
| tomita  |  0.28 | Team-C   |
| sakita  |     3 | Team-C   |
+---------+-------+----------+

sql_practice/if.sql at master · Fendo181/sql_practice

抽出結果で新しくテーブルを作成する

selectで抽出した結果を別のtableとして生成する。

-- selectで抽出した結果を別のテーブルにする
create table users_with_team as
select
    id,
    name,
    score,
    -- 5.0以上だったらOKでそれよりも下だったらNGになる
    case
        when score > 5.0 then 'Team-A'
        when score > 4.0 then 'Team-B'
        else 'Team-C'
    end as TeamName
from
    users;

select * from users_with_team;

結果

+----+---------+-------+----------+
| id | name    | score | TeamName |
+----+---------+-------+----------+
|  1 | endu    |   5.8 | Team-A   |
|  2 | koji    |   5.7 | Team-A   |
|  3 | kikuchi |   4.9 | Team-B   |
|  4 | hoge    |     1 | Team-C   |
|  5 | tomita  |  0.28 | Team-C   |
|  6 | sakita  |     3 | Team-C   |
+----+---------+-------+----------+

また既存のtableもコピーする事ができる。

-- users tableをそのままコピーする
create table uses_copy select * from users;
select * from uses_copy;

結果

+----+---------+-------+
| id | name    | score |
+----+---------+-------+
|  1 | endu    |   5.8 |
|  2 | koji    |   5.7 |
|  3 | kikuchi |   4.9 |
|  4 | hoge    |     1 |
|  5 | tomita  |  0.28 |
|  6 | sakita  |     3 |
+----+---------+-------+
6 rows in set (0.00 sec)

それ以外だとtableの構造だけコピーをしたい時はlike文だけを使うようにする。

-- テーブルの構造だけコピーしたい
create table users_empty like users;
desc users_empty;

結果

+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20)      | YES  |     | NULL    |                |
| score | float            | YES  |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

sql_practice/abstract_table.sql at master · Fendo181/sql_practice

データの集計処理

• count():集計を行う
• avg():平均値を取る
• distinct:重複するレコードを除く ユニークな値だけを取得する

create table users (
    id int unsigned primary key auto_increment,
    name varchar(20),
    score float
);

insert into users(name,score)values
    ('endu',5.8),
    ('koji',null),
    ('kikuchi',4.9),
    ('hoge',1.0),
    ('hoge',.28),
    ('sakita',3.0)
;

--  重複するレコードを除く ユニークな値だけを取得する
select distinct name from users_with_team;

結果

+---------+
| name    |
+---------+
| endu    |
| koji    |
| kikuchi |
| hoge    |
| sakita  |
+---------+
5 rows in set (0.00 sec)

sql_practice/count.sql at master · Fendo181/sql_practice

groupe by,having

• groupe byでグループ集計する事ができる。
• グルーピングした場合はwhereではなくhavingで条件をつけるようにする
  • having を使う場合は、グループ化に使ったカラムや、集計した値しか条件に使えない。
• whereとgroupe byを両方使うと、最初にwhere文が優先される。

-- グループごとで集計を取る
select sum(score), TeamName from users_with_team group by TeamName;

--降順で
select sum(score), TeamName from users_with_team group by TeamName desc;

-- スコアが10よりも大きいチームだけを表示する
select sum(score), TeamName from users_with_team group by TeamName having sum(score) >10;

ref:sql_practice/groupe_by.sql at master · Fendo181/sql_practice

サブクエリ

新しくテーブルを作らずに一時的な抽出結果を用意する事ができます。

select
    sum(t.score),
    t.TeamName
from
    (
    select
        id,
        name,
        score,
        -- 5.0以上だったらOKでそれよりも下だったらNGになる
        case
            when score > 5.0 then 'Team-A'
            when score > 4.0 then 'Team-B'
            else 'Team-C'
        end as TeamName
    from
        users
    ) as t
group by
  t.TeamName;

sql_practice/sub_query.sql at master · Fendo181/sql_practice

View

viewとは抽出条件に名前を付けてテーブルのように扱うことができます。
ただし、view は抽出条件であって値のコピーをして新しいテーブルを作るわけではないので、おおもとになる テーブルの値が変われば結果も当然変わります。

-- view(抽出条件に名前をつけてテーブルように扱えるようにする)
create view top3 as select * from users order by score desc limit 3;

-- 表示する
select * from top3;

-- 一覧を見る
show tables;

--  どうやってそのviewが作られたかを見る
show create view top3 \G;

結果

*************************** 1. row ***************************
                View: top3
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`myapp_user`@`localhost` SQL SECURITY DEFINER VIEW `top3` AS select `users`.`id` AS `id`,`users`.`name` AS `name`,`users`.`score` AS `score` from `users` order by `users`.`score` desc limit 3
character_set_client: utf8
collation_connection: utf8

sql_practice/view.sql at master · Fendo181/sql_practice

Transaction

一連の処理を必ずまとめて行いたい場合に使います。

• 変更を反映させる場合はcommit

start TRANSACTION;

-- 何らかのSQL

-- 変更を反映させる
COMMIT;

• 行った変更を破棄させる場合はROLLBACKです。

start TRANSACTION;

-- 何らかのSQL

ROLLBACK;

ref:sql_practice/transaction.sql at master · Fendo181/sql_practice

index(索引)

indexをつけるとデータの抽出が速くなる。
primary key には必ず索引が作られます。
索引は抽出時は早いですが、データの追加や更新処理を行うたびに作り直されるので、そちらの処理が遅くなってしまうというデメリットがあります。
とはいえ、抽出処理が遅いなと思ったら索引を付けたり外したりして、パフォーマンスを最適化できるようになっておくと良いです。

• indexの追加
  • alter table {table_name} add index {index_name} (column);
• indexの削除
  • alter table {table_name} drop index {index_name};

-- indexを追加する
alter table users add index index_score (score);
show index from users\G;

-- その索引が使われているかはkeyで確認できる
explain
select * from users where score > 5.0\G;

-- 使ってない場合はnullになる
explain
select * from users where name = 'endu'\G;

-- 索引の削除
alter table users drop index index_score;
show index from users\G;

sql_practice/index.sql at master · Fendo181/sql_practice

内部結合でデータを抽出する

• inner join:内部結合を行う
  • 2 つのテーブルに共通のデータだけを取得する方法

select posts.id, title, comments.body from posts inner join comments on posts.id = comments.post_id

実行結果

+----+--------+----------------------------+
| id | title  | body                       |
+----+--------+----------------------------+
|  1 | title1 | first comment! for         |
|  1 | title1 | Secound comment! for post1 |
|  3 | title3 | Third comment3             |
|  4 | title4 | Yes!Yes!It me 4            |
+----+--------+----------------------------+
4 rows in set (0.00 sec)

sql_practice/inner_join.sql at master · Fendo181/sql_practice

外部結合

• outer join:外部結合を行う
  • 2 つのテーブルで一致しないデータも含めてデータを取得する方法になります。

ただし、外部結合はどちらのテーブルを軸にするかで書き方が変わってくる

• left outer join
  • 左側のテーブルを軸にする
• right outer join
  • 右側のテーブルを軸にする

※outerは省略する事が可能です。

Left Join(左のテーブルを軸にします)

select * from posts left join comments on posts.id = comments.post_id;

実行結果

+----+--------+--------+------+---------+----------------------------+
| id | title  | body   | id   | post_id | body                       |
+----+--------+--------+------+---------+----------------------------+
|  1 | title1 | Hello! |    1 |       1 | first comment! for         |
|  1 | title1 | Hello! |    2 |       1 | Secound comment! for post1 |
|  3 | title3 | Yes!   |    3 |       3 | Third comment3             |
|  2 | title2 | Bye!   | NULL |    NULL | NULL                       |
+----+--------+--------+------+

Right Join(右側のテーブルを軸にします)

select * from posts right join comments on posts.id = comments.post_id;

+------+--------+--------+----+---------+----------------------------+
| id   | title  | body   | id | post_id | body                       |
+------+--------+--------+----+---------+----------------------------+
|    1 | title1 | Hello! |  1 |       1 | first comment! for         |
|    1 | title1 | Hello! |  2 |       1 | Secound comment! for post1 |
|    3 | title3 | Yes!   |  3 |       3 | Third comment3             |
| NULL | NULL   | NULL   |  4 |       4 | Yes!Yes!It me 4            |

sql_practice/outer_join.sql at master · Fendo181/sql_practice

外部キー制約

外部キー制約を使うとinsertをした時に関連したデータでなければ挿入できないように制限をかける事ができます。
しかし外部キー制約を設定してしまうと、関連するデータがある場合にはデータの削除や更新が簡単にはできなくなります。

comments の post_id に関しては posts テーブルの id に値があるものだけしか挿入できなくなります。加えて、紐付けるカラムの型が一致していないといけないので、統一してあげる。

alter table comments add constraint fk_comments FOREIGN key (post_id) REFERENCES posts(id);

入れようとするとこんなエラーが出る

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`myapp`.`#sql-44e_10`, CONSTRAINT `fk_comments` FOREIGN KEY (`post_id`) REFERENCES `posts` (`id`))

外部キー制約を削除する

alter table comments drop foreigin_key fk_comments;

last_insert_id()を使って、直前にinsertされたレコードのidを取得する

MySQL では直前に挿入されたレコードの id を調べる命令が用意されています。

それがlast_insert_id()です。
これを使えば、直前で挿入されたidを取得する事ができます。
関連した複数のテーブルにデータを挿入する場合は last_insert_id() をよく使います。

INSERT into comments (post_id,body) values
(
    last_insert_id(), 'first comment for new post'
)
;

ref:sql_practice/foreigin_key.sql at master · Fendo181/sql_practice

Trigger

テーブルでなんらかの変更が起きたときに、それをトリガーにして何らかの処理をすることができる仕組みが Trigger です。

ex)postsテーブルにinsertが走ったらlogsテーブルにメッセージが入るposts_insert_triggerトリガーを作成する

create
    trigger
        posts_insert_trigger
after insert
    on posts for each row
        insert into logs(msg) values('post add');

• after:実行後に行う
• before:実行前に行う

triggerを削除する

drop trigger if exists posts_insert_trigger;

sql_practice/trigger.sql at master · Fendo181/sql_practice

1つのTriggerで2つの処理を行う

複数の処理を1つのtriggerで行う場合はbegin~end;文を使います。
加えて、;で終わってしまうので、delimiterを使って複数のSQLが実行できるようにしておきます。

-- 区切り文字(//)を変更する
delimiter //
create
    trigger
        posts_update_trigger
after update
    on posts for each row
begin
     insert into logs(msg) values('post updated!');
    --  更新前のメッセージと更新後のメッセージを入れる
     insert into logs(msg) values(concat(old.title, '->',new.title));
end;
//

delimiter ;

• concatはmysqlに備わってい組み込み関数で文字列の連結ができます。

sql_practice/trigger2.sql at master · Fendo181/sql_practice

作成日時と更新日時を簡単に管理する

• datetime current_timestampで現在の日時情報を入れてくれます。
  - update時に更新をしてほしいときは以下のようにon update current_timestampを入れる。

created datetime default current_timestamp,
updated datetime default current_timestamp on update current_timestamp

sql_practice/datetime.sql at master · Fendo181/sql_practice

日時計算を行う

• date_add()を使えば、足した日数を自動で出してくれる。

-- 14日を足す
select created,date_add(created, interval 14 day ) from posts;
-- 2週間追加する
select created,date_add(created, interval 2 week ) from posts;

• date_formatを使えば、日時の時間のフォーマットを変更できる。

select created, date_format(created, '%W %M %Y') from posts;;

mysqlのマニュアル
ref:MySQL :: MySQL 5.6 リファレンスマニュアル :: 12.7 日付および時間関数

sql_practice/datetime2.sql at master · Fendo181/sql_practice

データベースを書き出してみよう

mysqldumpコマンドを使ってsqlを書き出す。

バックアップを取る

mysqldump -u <table_name> -p <db_name>  > <faile_name>.backup.sql

バックアップを復元させる

意図させてpostテーブルの一部を削除させる

delete from posts where id = 1;
select * from posts

実行結果

+----+--------+-------------+---------------------+---------------------+
| id | title  | body        | created             | updated             |
+----+--------+-------------+---------------------+---------------------+
|  2 | title2 | Bye!        | 2020-07-05 23:50:00 | 2019-07-04 00:42:08 |
|  3 | title3 | Yes!        | 2019-07-04 00:42:08 | 2019-07-04 00:42:08 |
|  4 | title4 | Off Course! | 2019-07-04 00:42:08 | 2019-07-04 00:42:08 |
+----+--------+-------------+---------------------+---------------------+
3 rows in set (0.00 sec)

backupから復元させる

mysql>\. ./****.backup.sql

実行結果

+----+--------+-------------+---------------------+---------------------+
| id | title  | body        | created             | updated             |
+----+--------+-------------+---------------------+---------------------+
|  1 | title1 | Hello!      | 2019-07-04 00:42:08 | 2019-07-04 00:42:08 |
|  2 | title2 | Bye!        | 2020-07-05 23:50:00 | 2019-07-04 00:42:08 |
|  3 | title3 | Yes!        | 2019-07-04 00:42:08 | 2019-07-04 00:42:08 |
|  4 | title4 | Off Course! | 2019-07-04 00:42:08 | 2019-07-04 00:42:08 |
+----+--------+-------------+---------------------+---------------------+
4 rows in set (0.00 sec)

以上です。
お疲れ様でした。