SQL

SQLのまとめノート

More than 3 years have passed since last update.


参照

スッキリわかるSQL入門

PHPとMySQLのツボとコツが絶対にわかる本


データ型

•数値

整数値 INTEGER型
小数  DECIMAL型 REAL型

•文字列
固定長 CHAR型
可変長 VARCHAR型

•日付と時刻
DATETIME型
DATE型
TIME型


InnoDBとは

InnoDB は、MySQL における、コミット、ロールバック、クラッシュリカバリの各機能を備えたトランザクションセーフ(ACID 準拠)のストレージエンジンです。InnoDB は、行レベルでロックを行い、SELECT ステートメントで Oracle 式の非ロックの読み取り一貫性(consistent read)を実現します。これらの機能によって、マルチユーザでの並行性とパフォーマンスが向上します

http://shindolog.hatenablog.com/entry/2015/04/01/185703

複数のクライアントから同時に更新などのリクエストを受けることが少ないアプリであれば、MyISAMのが良いという意見もあるようです。


DB作成

•データベース作成

CREATE DATABASE salesmanagement DEFAULT CHARACTER SET utf8;

•テーブル作成
CREATE TABLE goods(
GoodsID VARCHAR(4) NOT NULL,
GoodsName VARCHAR(20) NOT NULL,
Price INT NOT NULL,
PRIMARY KEY(GoodsID)
);

•ログイン

mysql -u root -p

•文字コード調べる
show variables like "chara%";

•UTF8にする
set character_set_server = utf8;


テーブル作成例

•テーブルを作る

CREATE TABLE goods(
GoodsID VARCHAR(4) NOT NULL,
GoodsName VARCHAR(20) NOT NULL,
Price INT NOT NULL,
PRIMARY KEY(GoodsID)
);

CREATE TABLE customer(
CustomerID VARCHAR(4) NOT NULL,
CustomerName VARCHAR(30) NOT NULL,
TEL VARCHAR(12),
Email VARCHAR(50),
PRIMARY KEY(CustomerID)
);

CREATE TABLE salesinfo(
id INT AUTO_INCREMENT NOT NULL,
SalesDate DATE NOT NULL,
CustomerID VARCHAR(4) NOT NULL,
GoodsID VARCHAR(4) NOT NULL,
Quantity INT NOT NULL,
PRIMARY KEY(id)
);


データ挿入例

INSERT INTO goods VALUES('1000','シンプルな消しゴム',100);

INSERT INTO goods VALUES('1001','カラフルノート',150);
INSERT INTO goods VALUES('1002','きらきらボールペン',120);
INSERT INTO goods VALUES('1003','天使のメモ帳',200);
INSERT INTO goods VALUES('1004','チョコの味するスケール',1080);
INSERT INTO goods VALUES('1005','センサー付クリップ',650);
INSERT INTO goods VALUES('1006','おせっかいなノート',1500);
INSERT INTO goods VALUES('1007','ミスト付ボールペン',230);

INSERT INTO customer VALUES('0001','青空商事','0268-26-999','ao@aaaa.jp');
INSERT INTO customer VALUES('0002','ひまわり商事','06-6547-8963','info@himawari.como.jp');
INSERT INTO customer VALUES('0003','まこに酒造','0268-36-3214','makon@plala.pr');
INSERT INTO customer VALUES('0004','丸子文具','0268-26-9999','tatsudai2000@gmail.com');

2014年7月24日に丸子文具にシンプルな消しゴムを40個販売した
INSERT INTO salesinfo VALUES(null,'2015-09-24','0004','1000',40)


テーブルを見る

•テーブル一覧を見る

show tables;

•goodsテーブルの構造を見る
describe goods;

•goodsテーブルの全データを見る
SELECT * FROM goods;


主キー

完全に重複した行が存在するとそのうちのある行だけを区別、識別することはできない。

よってある行だけを操作することもできない。

すべてのテーブルは主キーとなる列を必ず持つべき


  • NULLではない

  • ほかの行と値が重複しない


演算子

NOT、AND、ORの順で優先度が高く、先に評価される


基本構文

•抽出

SELECT 列名 FROM テーブル名 WHERE 抽出条件
SELECT 列名 AS 表示する列名 FROM テーブル名 WHERE 抽出条件

例)
SELECT * FROM goods WHERE GoodsID='1001';
SELECT GoodsID,GoodsName FROM goods WHERE PRICE > 300;
SELECT GoodsName AS 商品名 FROM goods WHERE price > 200;

•挿入
INSERT INTO テーブル名(列名)VALUES(値,値);
例)
INSERT INTO goods VALUES('1000','シンプルな消しゴム',100);

•更新
UPDATE テーブル名 SET 列名 = 値 WHERE 条件式
例)
UPDATE goods SET Price=200 WHERE GoodsID = '1000';

 ※抽出条件(WHERE)は主キーにした方が処理が高速

•削除
DELETE FROM テーブル名 WHERE 条件式

スクリーンショット 2015-09-24 13.44.58.png


図引用:スッキリわかるSQL入門


ルール

※計算で求められる値はデータベースには保存しない。

※SQL文の中に書き込まれたデータそのものをリテラルと言う

※''でくくられ、'2-15-02-25'のような一定の形式で記述されたリテラルは日付情報として扱われる

※WHEREがないUPDATEやDELETE文は使わない(全件変更される)


考え方

検索系 SELECT 

•データベースのデータを書き換えることはない
•実行結果は表の形になる
•検索結果に対する処理を指示する修飾語を持つ(ORDER BYなど)

更新系 UPDATE、DELETE、INSERT
•データベースのデータを書き換える(成功か失敗かを返す)

既存系 SELECT、UPDATE、DELETE

•WHERE句が利用可能

新規系 INSERT


WHERE

•WHEREは、真か偽になる式のみ。


NULLの判定

IS NULL、NOT NULLを使って条件式を作ること。=や<>では判定できない。

SELECT * FROM goods WHERE Price IS NULL;


演算子


LIKE演算子

% 任意の0文字以上の文字列

_ 任意の1文字

SELECT * FROM goods WHERE GoodsName Like '%きらきら%';

%1月% 1月の前後に0文字以上の文字が付いている→1月という文字を含む文字列

%1月_ 1月で始まり、その後ろに任意の1文字がある文字列


BETWEEN演算子

SELECT * FROM goods WHERE Price BETWEEN 300 AND 1000;


IN/NOT IN演算子

式 IN(値1,値2,値3)

SELECT * FROM goods WHERE Price IN(120,650);
SELECT * FROM goods WHERE Price NOT IN(120,650);


比較演算子

スクリーンショット 2015-09-24 13.52.26.png


検索結果を加工する

DISTINCT  検索結果から重複行を除外する

ORDER BY  検索結果の順序を並び替える
LIMIT    検索結果から件数を限定して取得する
UNION    検索結果にほかの検索結果を足し合わせる
EXCEPT   検索結果からほかの検索結果を差し引く
INTERSECT  検索結果とほかの検索結果で重複する部分を取得する


ORDER BY

SELECT フィールド名 FROM テーブル名 ORDER BY ソート対象のフィールド名 ASC/DESC(省略すると昇順)

SELECT * FROM goods WHERE GoodsID>='1003' ORDER BY Price DESC;


テーブルの結合


  • 一致するレコードを取り出す結合を「内部結合」という。

  • JOINをINNERJOINと記述してもよい。

  • JOINは結合する両方のテーブルにデータがある場合にのみ結合結果データが取得できる

  • ON→同じカラム名の場合、「USING」を使ってもよい。

SELECT カラム名

FROM テーブル1
JOIN 結合するテーブル2
ON テーブル1のカラム=テーブル2のカラム;

salesinfo

1.png

customer

2.png

goods

3.png

例)salesinfoとcustomerとgoodsを結合する

SELECT id,SalesDate,CustomerName,GoodsName,Price,Quantity
FROM salesinfo
INNER JOIN customer
ON salesinfo.CustomerID=customer.CustomerID
INNER JOIN goods
ON salesinfo.GoodsID=goods.GoodsID;

1.png

例)salesinfoとcustomerとgoodsを結合する

SELECT id,SalesDate,CustomerName,GoodsName,Price,Quantity,(Price*Quantity)AS Amount
FROM salesinfo
INNER JOIN customer ON salesinfo.CustomerID=customer.CustomerID
INNER JOIN goods ON salesinfo.GoodsID=goods.GoodsID;

2.png


集計関数を使う

•集計

SUM 各行の値の合計を求める
MAX 各行の値の最大値を求める
MIN 各行の値の最小値を求める
AVG 各行の値の平均値を求める

•計数
COUNT 行数をカウントする

SELECT
集計関数(カラム名)AS 表示名,
FROM テーブル名

※絞りこみを行う場合は、WHERE句ではなく、HAVING句を使う(集計してから絞りこみを行うから)


リレーション SQL例

SELECT distinct salesinfo.CustomerID,customer.CustomerName

FROM salesinfo INNER JOIN customer ON salesinfo.CustomerID=customer.CustomerID
WHERE salesinfo.SalesDate BETWEEN '2014-07-24' AND '2014-07-24'
ORDER BY salesinfo.CustomerID

1.png

2.png

SELECT sum(salesinfo.Quantity*goods.Price)

FROM salesinfo INNER JOIN goods ON salesinfo.GoodsID = goods.GoodsID
WHERE (salesinfo.SalesDate BETWEEN '2015-09-24' AND '2015-09-24') AND salesinfo.CustomerID = 0001

3.png

SELECT salesinfo.id,salesinfo.SalesDate,salesinfo.GoodsID,goods.GoodsName,

goods.Price,salesinfo.Quantity,(goods.Price*salesinfo.Quantity)
FROM salesinfo INNER JOIN goods ON salesinfo.GoodsID=goods.GoodsID
WHERE salesinfo.SalesDate BETWEEN '2015-09-24' AND '2015-09-24'
AND salesinfo.CustomerID=1
ORDER BY salesinfo.SalesDate,salesinfo.id;

4.png