今からSQLの基礎的なコマンドを色々と書いていこうと思う。

１ MySQLに接続する

(root の部分はユーザー名なので適宜変えてください。)

sudo mysql -u root -p
#  自分はsudoを付けずにした

ここから先は、mysqlを接続した状態でコマンドを打つ。

2 DBを作成する

create database  〇〇

# railsで例えると「rails db:create」

3 データベースの一覧をみる

show databases;

4 どのデータベースを使うかを選択する

use 〇〇

5 テーブルを作成する

1 CREATE TABLE user(
    id INT(11) AUTO_INCREMENT NOT NULL, 
    first_name VARCHAR(30) NOT NULL,
    last_name VARCHAR(30) NOT NULL,
    PRIMARY KEY (id));

2 show tables;
 railsで例えると
#「rails g model user first_name:string last_name:string」(not null制約は付けていない）
#「rails db:migrate」的なことをしている。

6 insert (テーブルにレコードを追加。)

 insert into user (first_name, last_name) values ('11', '11');

select * from user;

railsで例えると
#「user = User.new(first_name: '〇〇', last_name: '〇〇')」
#「user.save」

7 select 事前準備

・ユーザーテーブルにレコードを追加しておく。

insert into user (first_name, last_name) values ('22', '22');
Query OK, 1 row affected (0.00 sec)

insert into user (first_name, last_name) values ('33', '33');
Query OK, 1 row affected (0.00 sec)

・掲示板テーブルを作っておく。

CREATE TABLE board(
    id INT(11) AUTO_INCREMENT NOT NULL, 
    title VARCHAR(30) NOT NULL,
    body VARCHAR(30) NOT NULL,
    user_id INT(11) NOT NULL,
    PRIMARY KEY (id),
    INDEX (user_id),
    FOREIGN KEY (user_id)
      REFERENCES user(id));

・データを追加しておく。

insert into board (title, body, user_id) values ('hoge', 'hogehogi', 1);
Query OK, 1 row affected (0.00 sec)

insert into board (title, body, user_id) values ('fuga', 'fugafugi', 1);
Query OK, 1 row affected (0.00 sec)

insert into board (title, body, user_id) values ('bar',  'barbai', 2);
Query OK, 1 row affected (0.00 sec)

・seletしてみる

select * from board;

railsで例えると「Board.all」をしている。

8 whereで絞り込む条件を指定するときはwhereを使う。

select * from board where user_id = 1;

# railsで例えると
 Board.where(user_id: 1)or current_user.boards (current_userはidが1のユーザーという前提)

9 join

あるテーブルとあるテーブルを結合する。条件を指定せずに結合すると単純に全レコードどうしを結合する。5レコードのテーブルと5レコードのテーブルを結合すると結果として25レコードできる。10,000レコードと10,000レコードだと... joinは負荷が高い操作ということを覚えておくべし。

select * from user inner join board;

# Railsに該当するメソッドはない...？

10 結合条件を指定する on

全レコードを結合するのはコストもかかるし、そもそも意味ない。普通は条件を指定する。掲示板テーブルのuser_idとユーザーのidが一致するものという条件でjoinしてみる。

select * from user inner join board on user.id = board.user_id;

結果は「33 33」(user_idは3)のレコードが表示されない。
なぜならuser_idが3の掲示板レコードがないから。

# Railsだとこんな感じ。
これだけで勝手に「掲示板テーブルのuser_idとユーザーのidが一致するもの」
という条件をつけてくれる。

irb(main):008:0> User.joins(:boards)
  User Load (0.4ms)  SELECT  `users`.* FROM `users` INNER JOIN `boards` 
ON `boards`.`user_id` = `users`.`id` LIMIT 11

他にも以下のjoinのやり方もあるので色々と調べてみる。

left inner join
left outer join
right inner join
right outer join

11 order 並び順

select * from board order by title asc;

# railsで例えると
Board.all.order(title: :asc)

ちなみに

DESC：指定した値を降順(値が大きい順)で並べ替える。

ASC：昇順(値が小さい順)で並べ替える。

12 update 更新

update user set first_name="44" where id = 1;

「select * from user;」とコマンドを押すと
１番目のユーザーのfirst_nameが「11」から「44」に変わっている。

#Railsだったらこんな感じ
user.update(first_name: '44') # userはidが1のユーザーという前提

13 delete

delete from user where id = 3;

「select * from user;」とコマンドを押すと
３番目のユーザーが消えているのを確認できる。

# Railsだとこんな感じ
user.destroy # userはidが3のユーザーである前提

14 補足

「Sequel Pro」というデータベース操作のためのGUIツールがある。

Doiya’s blog

日々の進捗を書く雑記ブログ（メインはエンジニアやプログラミング関連）

SQLの基礎

ここから先は、mysqlを接続した状態でコマンドを打つ。