文末は必ず「;」で終わる。
打ち忘れた場合は改行後に;を打って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
コメント