MySQLの基本的な操作の仕方

今回はデータベースに必須のSQLの操作などについて書いていこうと思う。
* ユーザーの作成、権限付与、削除
* データベースの作成、指定、削除
* テーブルの作成、指定、削除
* データの登録、更新、削除

などの基本的な操作を確認した後に、
* データのグルーピング
* テーブルの結合
* サブクエリの使い方

についてを網羅していく。
# 基本操作
### ユーザーの作成、権限付与,削除
~~~sql:user
//crate user with password
CREATE USER "newuser"@"localhost" IDENTIFIED BY "password";

//confirm the user (一覧が確認できる)
SELECT * FROM mysql.user;

//grant privileges to the user 
GRANT ALL PRIVILEGES ON "*" TO "newuser"@"localhost" WITH GRANT OPTION;

//delete the user
DROP USER "newuser"@"localhost";
~~~
<br>
上記でユーザー設定に関してはできると思う。実際の現場では権限の全てを与えるなんてことはしないだろうが、自分で何かを作ったりする分には大丈夫だろう。

今後今回作ったユーザーを指定してログインするには

~~~sql:Login
mysql -u "newuser" -p 
~~~
とすれば良い。
もしログアウトする時は
~~~sql:Logout
quit;
~~~
とすればmysqlを抜けることができる。
<br>
### データベースの作成、指定、削除

~~~sql:database
//create database
CREATE DATABASE newdatabase;

//show databases
SHOW DATABASES;

//identify the database
USE newdatabase;

//delete the database
DROP DATABASE newdatabase;
~~~

データベース自体についての基本操作は以上で大丈夫だろう。
<br>
### テーブルの作成、検索、指定、削除
上記のデータベース操作の通りにデータベース操作を行い、新しいデータベースを指定する。その中にテーブルを作って動かしていく。
<br>

~~~sql:table
//create new table(今回はexam_tableとする)
CREATE TABLE exam_table{
    id   INT           AUTO_INCREMENT   PRIMARY KEY
    name VARCHAR(255)  NOT NULL
    age  INT           NOT NULL
};
~~~
ここで、 INTは数字、VARCHARは文字列である。PRIMARY KEY　とは主キーと呼ばれるものであ理、その数字一つでデータの全てのレコードがわかるというものである。また、レコードとは、そのデータのそれぞれの情報である。

また、以下の操作でカラム(レコードの中のそれぞれの情報)やテーブルを付け加えたり削除し、確認することができる。
~~~sql:column
//add column
ALTER TABLE exam_table ADD email VARCHAR(255);

//delete column
ALTER TABLE exam_table DROP COLUMN email

//show tables
SHOW TABLES;

//delete the table
DROP TABLE exam_table;
~~~

### データの登録、更新、削除
上記で作ったテーブルにカラムを作りその中にデータを入れることでデータベースとして完成する。
ではデータを今から登録していく。

~~~sql:data
//register data
INSERT INTO exam_table (name) VALUES ("Jhon");

//update the data
UPDATE exam_table SET name = "elis" WHERE id = 1;
//ここでのidとは　PRIMARY KEY　である

//delete the data
DELETE FROM exam_table;
//これでは全てのデータが削除されてしまうので、実際はバックアップを取るべきである
~~~

これにて、基本的なデータの登録などが可能になった。
<br>

最後にデータの検索の仕方を書いておく。
~~~sql:search
SELECT * FROM exam_table
~~~

これで全ての基本的な操作を述べ終えた。
*の部分に様々なカラム名を入れることにより、それぞれのカラムの内容を検索することができる。
*の場合は全て(レコード)を検索するが、データが大量にあると実行時間がかかりすぎるため、以下では効率的にカラムを検索していく方法を述べていく。

# データ検索の方法
まずは最低限の検索の絞り込み方法を書いていく。

~~~sql:search 
SELECT (なんのカラムか)
FROM   (なんのデータベースからか)
WHERE  (絞り込み条件)
~~~
という様な感じである。WHERE以下でLIKEやBETWEEN、LIMITなどを付け加えていく。
* LIKE     　　　　　　は曖昧な条件
* BETWEEN ~ AND　は期間の条件
* LIMIT　　　　　　は数の条件　　である

最低限のことはこれくらいにしておく。

### グルーピング
これは GROUP BY　という関数を使い特定のカラムをグループごとにまとめ、それをSUMやCOUNTなどの集計関数を使って計算していく方法である。
* COUNT 回数を数える
* SUM 　合計を計算する
* MAX 　最大値を求める
* MIN 　最小値を求める
* AVG 　平均を求める

これらは以下のように使用する
~~~sql:grouping
SELECT emp_no, MIN(salary) as min_salary, MAX(salary) as max_salary
FROM salaries
WHERE emp_no BETWEEN 10001 AND 10010
GROUP BY emp_no;
//salariesテーブルから、従業員番号(emp_no)10001から10010までの最小給与と最大給与を求めた
//asを書くとMIN(salary)ではなく、min_salaryとして表示することが可能になる
~~~

このように複数のデータからグループごとにまとめて、その中で集計関数を使うことで効率よく検索することができる。

### テーブル結合　( 内部結合と外部結合 )
そもそもテーブル結合とは何かについて説明していく。
テーブル結合とは、異なるテーブル間から同じデータの内容を統合し、関連するデータの情報を繋げ合わせるためのSQLの操作である。

例えば、学生番号(id)と名前(name)のある　　　　 info_tableと、
　　　　学生番号(id)と学部名(faculty_name)のあるfaculties_tableがあったとする。
この時、学生番号(id)が等しいので、これらをくっつけて表を表示させるには
~~~sql:inner join
SELECT *
FROM info_table
JOIN faculties_table ON info_table.id =  faculties_table.id ;
~~~
とすれば良い。すると、二つのテーブル間でマッチするレコードを表示させることができる。
<dl>
<d>しかし、この時注意するべきことがある。それは、二つのテーブル共に情報がないといけないということである。つまり、今回だと学生番号と名前があって、学部名が存在していない時は結合した表にはその人の情報全てが表示されない。これを<strong>内部結合（INNER JOIN)</strong>という。</d>

<d>一方で、情報の有無にかかわらず、全てのレコードを表示させるものを<strong>外部結合(OUTER JOIN)</strong>という。</d>

<d><strong>OUTER JOIN</strong> には <strong> LEFT JOIN </strong>と<strong> RIGHT JOIN </strong>の二つがあり、テーブルの左側(元となるテーブル)、今回だとinfo_tableにあるものであれば、学部名が存在しなくても表示するというものが<strong> LEFT JOIN </strong>であり、学部名が存在していて、名前が存在していなくても表示させるというものが<strong> RIGHT JOIN </strong>である。</d>
<dl>

~~~sql:left join
SELECT *
FROM info_table
LEFT JOIN faculties_table ON info_table.id = faculties_table.id;
~~~

### サブクエリの使い方
最後はサブクエリだ。サブクエリとは、SQL文の中に組み込まれた別のSQL文である。複雑な条件などを絞り込んだものを、さらにそこから絞り込むときなどに役立つ。一般的にはSELECT句の中のWHERE句などで使用される。
例として、以下ではstudentsテーブルとgrades(成績)テーブルを作りそれらから情報を取得する。今までの総まとめの様なものだと思ってもらえれば良い。
~~~sql:create tables
//students table
CREATE TABLE students(
    student_id   INT AUTO_INCREMENT PRIMARY KEY, //学籍番号(自動数えあげ)
    student_name VARCHAR(255) NOT NULL, //名前
    major        VARCHAR(255) NOT NULL, //専攻
);

//grade table
CREATE TABLE grades(
    grade_id            INT PRIMARY KEY, //成績id
    student_id          INT AUTO_INCREMENT NOT NULL, //学籍番号
    course VARCHAR(255) NOT NULL, //コース名
    grade VARCHAR(1)    NOT NULL, //成績　今回はS,A,B,C,Dのどれかだとして、一文字にした
    FOREIGN KEY (student_id) PREFERENCES students(student_id)
);
~~~
今回出てきた FOREIGN KEY とは、他のテーブルにある他のカラム今回だとstudentsテーブルのstudent_id)を取得するというものである。
つまり、gradeテーブルのstudent_idとstudentsテーブルのstudent_idは同じである。
これにより、新しいstudentsテーブルのsutent_idが生成されるたびに、自動的にgradeテーブルが作成されていく。

話を戻して、ある特定の専攻(今回はComputer Scienceとする)の学生のみの成績を取得する場合、以下のようなサブクエリを使ったクエリを使用し取得することができる。
~~~sql:sabquery
SELECT *
FROM grades
WHERE student_id IN (
    SELECT student_id
    FROM students
    WHERE major == "Computer Science" 
    )
:
~~~
と書くことができる。
今回だと、まず、成績を取得してきたいため
~~~sql:get grade
SELECT *
FROM grades
WHERE //専攻がComputer Scienceの学生
~~~
となる。
しかし、gradesテーブルには専攻カラムがなく絞り込みができないため、studentsテーブルからstudent_idを通して、 Computer Science 専攻の学生を絞り込む必要がある。
よって、WHERE句以下で専攻が Computer Science の学生の学籍番号を取得するためのサブクエリを書いた。
この様にして、複数のテーブルなどからもデータの検索を一度で出来て、効率性を高めるための手段がサブクエリである。

# まとめ
今回はMySQLの基本的な操作について書き、その後groupingなどの項目について扱っていった。今回書いたものは本当に基本的な操作ばかりなので、全て調べずとも扱える様にしたいところだ。