Posted at

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 は高速性と堅牢性を追及したマルチユーザ・マルチスレッドのSQLデータベースです。

  • 世界で最も人気のあるオープンソースデータベースです。

ref:MySQL


用語説明


  • 行:レコード(Record/Row)


    • データ




  • 列:フィールド(Fields/Column)


    • 属性値




  • SQL(Structured Query Language)



    • DatabaseTableFieldRecord を扱う言語を SQLStructured 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@localhostdot_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;


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 の設定にもよりますが、この中にある値以外をはじくことができます。

また内部的には連番で振られているので、12で指定できます。

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 を使う場合は、グループ化に使ったカラムや、集計した値しか条件に使えない。




  • wheregroupe 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をした時に関連したデータでなければ挿入できないように制限をかける事ができます。


しかし外部キー制約を設定してしまうと、関連するデータがある場合にはデータの削除や更新が簡単にはできなくなります。

commentspost_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)

以上です。

お疲れ様でした。