はじめに

SQLについて学習したので、これまでに学んだ知識をまとめました。

学習した事

• MySQLのインストール
• MySQL Workbenchの使用方法
• SQL構文の基本の使い方

今回は、SQL構文の基本の使い方について、自分用メモと知識定着の目的で書きました。訂正箇所などありましたら、ご指摘いただけると幸いです。

SQLの基本

• SQLは大文字と小文字を区別しない
• 文字と日付はシングルクォーテーションで囲う

データ型

テーブルを作成する時に、それぞれのカラムに指定した形式のデータしか入力できないように設定する。この時指定するデータの形式をデータ型という。

データ型名	データ種別
int型	数値型	整数
tinyint型	数値型	-128〜127
char型	文字列	固定長の文字列255文字まで
varchar型	文字列	可変長の文字列255文字まで
text	文字列	長い文字列65535文字まで
date型	日付	1000-01-01〜9999-12-31まで
datetime型	日付・時刻	00:00:00.000000〜23:59:59.999999
time型	時刻	838:59:59〜838:59:59

集約関数

集約関数ではnullは基本的に無視されるが、COUNT(*)とする事でnullを含めたレコードを数える。

関数	意味
COUNT	テーブルのレコード数を数える
SUM	カラムの数値を合計する
AVG	カラムの数値を平均する
MAX	カラムの中の最大値を求める
MIN	カラムの中の最小値を求める

SQL文の記述順序と実行順序

記述順序	実行順序
SELECT	FROM
FROM	JOIN
JOIN	WHERE
WHERE	GROUP BY
GROUP BY	HAVING
HAVING	SELECT
ORDER BY	ORDER BY
LIMIT	LIMIT

in演算子・・・　ある値が値セット内に含まれているかどうか。
not in演算子・・・　ある値が値セット内に含まれていないかどうか。

テーブル操作

CRUD

CRUDとは(Create, Read, Update, Delete)のこと。

SQLではこの4つの処理を

• Create = INSERT
• Read = SELECT
• Update = UPTATE
• Delete = DELETE

の構文で操作する。

テーブルの作成

CREATE TABLE [テーブル名](
  列名 [データ型] [その他の記述],
  列名 [データ型] [その他の記述],
  列名 [データ型] [その他の記述]
);

テーブルにデータの追加

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

この時、列と値の数が一致している必要がある。

複数行を一度に追加する場合は、それぞれが括弧で囲まれ、カンマで区切られた、カラム値の複数のリストを記入していく。

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

列の追加

ALTER TABLE [テーブル名] ADD [追加する列名] [データ型] AFTER [列名];

列名の変更

ALTER TABLE [テーブル名] CHANGE [旧列名] [新列名] [データ型];

データの取得(SELECT)

SLECT ・・・ カラムの指定
FROM ・・・ 対象テーブルの指定
JOIN ・・・ テーブルの結合
WHERE ・・・ 絞り込み条件の指定
GROUP BY ・・・ グループ化の条件の指定
HAVING ・・・ グループ化した後の絞り込み条件を指定
ORDER BY ・・・ 並び替え条件を指定
LIMIT ・・・ 取得する行数を指定

例 adventurerテーブル

id	name	sex	lv	mp	job
1	山田	man	20	40	戦士
2	鈴木	woman	30	230	魔法使い
3	田中	man	40	280	僧侶
4	小野	woman	50	360	魔法戦士
5	佐藤	man	40	200	レンジャー
6	西野	woman	70	220	パラディン

WHERE句

SELECT name, lv FROM adventurer WHERE name = '鈴木';

実行結果

name     lv
鈴木      25

LIKE

ワイルドカード文字で文字列のパターンを指定する事ができる。

• '山%' → '山'で始まる文字列
• '%山' → '山'で終わる文字列
• '%山%' → '山'を含む文字列
  
• '__山' → 何かしらの2文字で始まり、'山'で終わる文字列
  

jobに戦士の文字列を含む情報を取得

SELECT * FROM adventurer WHERE job LIKE '%戦士%';

実行結果

id  name  sex   lv   mp    job
1   山田   man   20   40    戦士
4   小野  woman  50   360  魔法戦士

サブクエリ

鈴木さんよりもmpが高い人の名前を取得

SELECT name FROM adventurer WHERE mp > (SELECT mp FROM adventurer WHERE name = '鈴木');

実行結果

name
田中
小野

CASE

CASE式を使う事で条件分岐を指定する事ができる。

lvが60以上なら'A'
lvが40以上なら'B'
lvが40未満なら'C'

SELECT name,
       CASE WHEN lv >= 60 THEN 'A'
            WHEN lv >= 40 THEN 'B'
            ELSE 'C'
       END AS ranking
FROM adventurer;

実行結果

name   ranking
山田       C
鈴木       C
田中       B
小野       B
佐藤       B
西野       A

GROUP BY句

性別毎のレベルの平均を取得

SELECT sex, AVG(lv) FROM adventurer GROUP BY sex;

実行結果

sex     lv
man     50
woman   75

ORDER BY句

並び替え条件を指定する。複数条件を指定する時は、,区切りで指定する。
ASC・・・昇順（デフォルト）
DESC・・・降順

lvが高い順に並び替え。lvが同じ場合はid順。

SELECT * FROM adventurer ORDER BY lv DESC, id ASC;

実行結果

id  name  sex  lv  mp  job
6   西野  woman 70 220  パラディン
4   小野  woman 50 360  魔法戦士
3   田中   man  40 280  僧侶
5   佐藤   man  40 200  レンジャー
2   鈴木  woman 30 230  魔法使い
1   山田   man  20 40   戦士

LIMIT句

取得する行数を指定する事ができる。

SELECT * FROM adventurer ORDER BY lv DESC, id ASC LIMIT 3;

実行結果

id  name  sex  lv  mp  job
6   西野  woman 70 220  パラディン
4   小野  woman 50 360  魔法戦士
3   田中   man  40 280  僧侶

データの更新(UPDATE)

UPDATE [テーブル名] SET [変更したい列名] = ['新しい値'] WHEREで条件指定;

データを削除(DELETE)

DELETE FROM [テーブル名] WHERE [条件];

データベース操作

データベース接続

mysql -u root -p

MySQL起動

mysql.server start

MySQL終了

mysql.server stop

MySQLの状態確認

mysql.server status

データベースの作成

CREATE DATABASE [作成するデータベース名];

データベースの命名ルール

• 半角のアルファベット a,b,cなど
• 半角の数字 1,2,3など
• アンダースコア _
• 名前の最初は半角のアルファベット

データベースの削除

DROP DATABASE [削除したいデータベース名];

データベースの一覧表示

SHOW DATABASES;

使用しているデータベースの確認

SELECT DATABASE();

使用するデータベースの選択

USE [データベース名];

1.テーブルを作ります

create

create table indexT
(
    indexNo int not NULL,
    UpdateDatetime datetime,
    Primary Key( indexNo )
);

2.SQLを実行します。

count_up

update [indexT] set 
[indexNo] = ( case when CAST([UpdateDateTime] as DATE) = CAST(getdate() as DATE) then [indexNo] + 1 else 1 end ), 
[UpdateDateTime]= getdate();

insert into [indexT] ([indexNo], [UpdateDateTime]) 
select 1 as indexNo, getdate() as UpdateDateTime 
where NOT EXISTS (select 1 from indexT);

select [indexNo], [UpdateDateTime] from [indexT];

はじめに

sqlを勉強して新しく学んだことなどを忘備録として残します。

where id in(1,2,3)

select * from products where id = 1 or id = 2 or id = 3

みたいに書くのではなく

select * from products where id in (1,2,3)

とかくとスマート

is not null

select * from users where age = null

これだとうまく動かないので

select * from users where age is null

とする

between

select * from foods where price >= 1000 and price <= 2000

これは

select * from foods where between 1000 and 2000

と書ける

%

曖昧検索するときに

select * from users where name like '%田%';

みたいにできる

%田　で最後に田がつくもの
田% で最初に田がつくもの

_

曖昧検索のときに文字の数を指定したいときに_が使える

select * from users where name like '__子'

これで名前が三文字で最後に子がつくものを取ってこれる。

interval

intervalで時間を足し引きできる

select current_date() + interval 3 day;
select current_date() - interval 3 day;
select current_timestamp + interval 3 hour;
select current_timestamp - interval 3 hour;

extract

年、月を数字列で表示してくれる

select * from posts where extract(year_month from created_at) = 201701;

これで2017年1月のpostsがとってこれる。

year_month以外にも

year month day hour minute second が使える

date_format

extractと似ている

select * from posts where date_format(created_at, '%Y%m') = 201701;

みたいにつかう

サブクエリ

()でくくってsql文を実行してその結果を使える。

サブクエリ内の値を使う

  select
    round((sum(a.num) / count(a.order_id)),0) as average, a.users_locate 
from (
    select 
        users.prefecture_id as order_pre,
        prefectures.name as users_locate,
        user_id,
        ord
er_id,
        sum(product_price * product_qty) as num,
        extract(year_month from orders.order_time) as order_month
    from
        order_details
    inner join
        orders
    on
        orders.id = order_details.order_id
    inner join 
        users
    on 
        users.id = orders.user_id
    inner join 
        prefectures
    on 
        users.prefecture_id = prefectures.id
    group by 
        order_id) as a
group by 
    a.users_locate
order by 
    a.order_pre;

サブクエリに対して as で名前を付けたら

名前.valueでサブクエリ内の値を取得できる。

この場合 a.num, a.order_id, a.users_locate

inner join

select users.id, users.name, posts.body from users 
inner join posts on posts.user_id = users.id;

みたいに使える。

outer joinとは違いpostsにusers_idがないものなどposts.user_id = users.idが成立しないものは取ってこない。

outer join

主になるテーブルを指定してそのテーブルのすべてをとってくる。

select users.id, users.name, posts.body from users
left outer join posts on posts.user_id = users.id;

これでpostsを持たないusersも表示してくれる。

union

複数のテーブルを結合

結合する場合カラム数、カラム名は同じ出なければいけない。

select users.name, users.email from users 
union
select admin_users.name, admin_users.email from admin_users;

これでusersとadmin_usersを一緒にして出力してくれる。

create view

create view users_name_and_email(name,email) as 
select name,email from users;

とすると

select * from users_name_and_email;

これでusersのname,emailが取得できる。

sql文を保存する感じ

coalesce(null,0)

nullをほかの何かに変える

select price(null,0) from products;

これでpriceがnullの場合0に変換して出力

create table

create table books (id int not null auto_increment primary key, name varchar(255);

auto_incrementは自動的に値を増やしていってくれる。

insert into

insert intoの時にauto_incrementとなっているものは値を入れなくても勝手に数字を入れてくれる。

例えばidとか

insert into users (name,age) values ('taro',18);

このsql文でusersにidがauto_incrementであれば勝手に次のidを入れてくれる。