SQL

文末は必ず「;」で終わる。
打ち忘れた場合は改行後に;を打ってenterでOK。

SQL(Structured Query Language)はリレーショナルデータベース(RDB)の操作を行うための言語。

命令は大きく2つ、
データを定義するDDL(Data Definition Language)
データを操作するDML(Data Manipulation Language)

DDLの出来ること

命令機能
CREATEデータベースやテーブルの作成
ALTERデータベースやテーブルの更新
DROPデータベースやテーブルの削除

 
・DMLの出来ること

命令機能
INSERTデータの登録
UPDATEデータの更新
DELETEデータの削除
SELECTデータの検索

DB/Tableの操作

terminal

mysqlに接続
なおmysqlを抜ける場合はexit

mysql -u root

データベースを表示

show databases;

CREATE DATABASE文

データベースを作る

CREATE DATABASE データベース名;

USE文

データベースを選択する

USE データベース名;

SHOW TABLES文

選択したデータベースに存在するテーブルを一覧で表示する

SHOW TABLES;

CREATE TABLE文

テーブルを作成する

CREATE TABLE テーブル名 (カラム名 カラム名の型, ……);

mysqlで数字型や文字列型を定義する際は以下のような型名を使用する。

型名保存できる値
INT数字
VARCHAR(M)最大M文字の文字列
CREATE TABLE goods (id INT, name VARCHAR(255));

SHOW COLUMNS FROM

テーブルのカラムを表示する。

SHOW columns FROM テーブル名;

ALTER TABLE

カラムの追加

ALTER TABLE テーブル名 ADD カラム名 カラムの型;
ALTER TABLE テーブル名 ADD (カラム名 カラムの型, ……);

カラム名や型の変更

ALTER TABLE テーブル名 CHANGE 古いカラム名 新しいカラム名 新しいカラムの型;

この際、名前の変更のみをする場合でも型は入力しなければならない。

カラムの削除

ALTER TABLE テーブル名 DROP カラム名;

データの操作

INSERT文

全てのカラムに値を入れる場合

INSERT INTO テーブル名 VALUES(値1, 値2, 値3);

特定のカラムのみに値を入れる場合

INSERT INTO テーブル名(カラム名1, カラム名2) VALUES(値1, 値2);

データの取得

SELECT * FROM テーブル名;

UPDATE文

値の変更

UPDATE テーブル名 SET 変更内容 WHERE 条件;
例
UPDATE goods SET price = 100 WHERE id = 2;

DELETE文

DELETE FROM テーブル名 WHERE 条件;

データの検索

*

ワイルドカード

FROM句

テーブルを選択

FROM テーブル名

SELECT句

SELECT カラム名

WHERE句

WHERE 条件
例
WHERE id = 1
WHERE family_name = "ほげほげ"
WHERE id <= 8

演算子

AND演算子

A かつ B

WHERE a AND b

OR演算子

A または B

WHERE a OR b

NOT演算子

A ではない

WHERE NOT a

BETWEEN演算子

WHERE カラム名 BETWEEN 下限 AND 上限
例
WHERE age BETWEEN 20 AND 30

IN演算子

リストを指定し、カラムの値がそのリストに含まれるときにtrue

WHERE カラム名 IN (値1, 値2, ……)
例
WHERE prefecture IN ("東京都", "神奈川県")

関数

CONCAT関数

concatenateの略。意味は鎖状に繋ぐ。
転じて、複数の文字列を結合できる。
nullが含まれる場合、結合した文字列はnullとなる。

CONCAT(文字列1, 文字列2, ……)
例
SELECT CONCAT(family_name, first_name)
FROM users

AS句

SELECT句でデータを取得する時に、AS句を併用するとカラムに別名を付けられる。

SELECT 取得するデータ AS 別名
例
SELECT CONCAT(family_name, first_name) AS fullname
FROM users

ASは省略できるため、実際にはselectに続けて表記したいカラム名を書く。

SELECT CONCAT(family_name, first_name) "名前"

DISTINCTキーワード

指定したカラムの値が重複する行を除外してデータを取得できる。
distinct 違う、はっきり異なる。

SELECT DISTINCT カラム名
例
SELECT DISTINCT user_id
FROM shifts
WHERE date = "2015-07-01"

GROUP BY句

同じ値を持つデータを1つのグループとしてまとめる。
distinctと異なり、グルーピングしているだけなのでグルーピング内のデータも保持しているため、グループ内のデータ処理も可能。

GROUP BY カラム名

COUNT関数

カラムを指定して使用し、値がnullでないデータの行数を取得する。

SELECT COUNT(カラム名)

カラム名にワイルドカードを指定すれば、nullを含む全レコードの件数を取得する。

SELECT COUNT(*)

AVG関数

MAX関数

MIN関数

テーブルの結合

inner joinとouter join

outer joinは優先テーブルを指定する。
優先テーブルに指定されたテーブル内の要素は全て出力され、対応するjoin先の項目がなければnullが入る。
FROM users JOIN reviews 
の左か右のどちらのテーブルを指定するかで、left join/ right joinがある。

inner joinは優先テーブルを考慮しない。
考慮しないため、項目の少ないテーブルに項目数の多いテーブルをjoinした場合、項目数の多いテーブルに合わせてテーブル内容がコピーされてデータ数が増える。
この際、結合先がnullのデータは無視される。

以下のようなテーブルを結合した場合
users id name
1 aa
2 bb
reviews id text user_id
1 ttt 1
2 yyy 1
3 uuu 3

FROM users JOIN reviews ON users.id = reviews.user_id

users id name r.id r.text r.user_id
1 aa 1 ttt 1
1 aa 2 yyy 1

JOIN

無記名のjoinは常にinner joinを示す。
指定したそれぞれのテーブルの、カラムの値が一致するデータを結合できる。
JOIN句はFROM句のあとに記述し、結合の対象となるテーブルを指定する。

FROM テーブル名1
JOIN テーブル名2 ON テーブル名1.カラム名1 = テーブル名2.カラム名2

サブクエリ

ある検索結果を使用して別のSQL文を実行する仕組み。
以下のようにネストにして利用する。

SELECT *
FROM users
WHERE id NOT IN (
    SELECT DISTINCT user_id
    FROM shifts
    WHERE date = "2015-07-01"
)

データベースの削除

drop database

mysql -u rootでSQLにアクセスして、以下のコマンドでデータベースの消去。
データベース名に「-」をそのまま用いるとsyntax errorになる。
回避するには`データベース名`とバックチックで囲う。

drop database `model-app_development`

その他

mysql

indexを張るとmul型になる。外部キーと同じ表記なので紛らわしいがindexの意味にとってOK

SQL
スポンサーリンク
Once and Only

コメント