トップ > メモ > MySQL 構文・文法などのまとめ

MySQL 構文・文法などのまとめ

メモ

こんにちは、ヒロアキです。 今回は、MySQLの構文・文法のまとめ記事を書いていきます。 引き続き、ただの自分用メモでもありますので、記法が統一されていないのでご了承ください。笑

mysql 操作まとめ

・実行順序と構文
実行
FROM → WHERE → GROUPBY → HAVING → SELECT → ORDERBY
構文
SELECT → FROM → WHERE → GROUPBY → HAVING → ORDERBY

・サーバー接続方法
（rootユーザー）mysql -u root -p

・ユーザー確認
use mysql;
→select user , host from user;　　登録されているユーザーの確認
→show grants for ‘user_name’@’host’;　　ワイルドカードは%

・文字化けの防止
SET NAMES utf8;

・データベース作成
create database db_name;

・データベース作成
drop database db_name;
→テーブルなら、databaseをtableに変更する

・テーブル作成

use *db_name*;
create table *table_name* (
id int not null auto_increment primary key,
title varchar(255) not null,
price int not null
);

※カラムの型などは別途調べる

・テーブル変更

-- カラムの追加
alter table *table_name* add *追加するカラム* *データ型* after *columns*;

-- カラム名変更
alter table *table_name* change *old_name* *new_name* *データ型*;

-- カラム削除
alter table *table_name* drop *columns*;

・データ登録（この例では2件）
INSERT INTO ‘table_name’ (columnA, columnB, columnC) VALUES (A1, B1, C1), (A2, B2, C2);

・データ更新

update
'table_name'
set 'columns_name' = update_thing 
where 更新対象のレコードの絞り込み;
-- where句を複数使いたい時は、andを使う
-- Ex. where ~ and ~ and ~;
-- サブクエリも使用可

・データ削除
delete from table_name where 削除対象のレコードの絞り込み

・ファイルからSQLを読み込む
source file_name

・database確認方法
show databases;

・データベース選択
use database_name;

・バージョン確認
select version();

・テーブル確認
show tables from database_name;

・インデックス確認
show INDEX from table_name;
　→EXPLAIN をselect文の先頭につけると、インデックスが機能しているか確認できる

・カラムの確認
show columns from table_name;
　　→データベース名から指定もあり
　　show columns from table_name from database_name;

・カラムに別名をつける
select columns as 別名 from table_name;
　→asで別名をつけた場合、その別名でクエリを書かないとエラーになる

・指定した値が含まれているかどうか

-- 含まれている
select *columns* from *table_name* where 調べたい値のあるカラム in(*int*);

-- 含まれていない
select *columns* from *table_name* where 調べたい値のあるカラム not in(*int*);

・特定文字列を調べたい時
select columns from tables where 調べたいカラム like ‘文字列’;
　　→「’文字列’」の部分は、「%」、「」というワイルドカードが使用可能
　　→’%’は0文字以上の任意の文字列、’’は1文字の任意の文字列

・取得件数を制限する
select columns from tables limit int;

・集約関数いろいろ

-- 合計値
select sum(*columns*) from *tables* where 合計値を知りたいレコードの絞り込み;

-- 平均
select avg(*columns*) from *tables* where 平均値を知りたいレコードの絞り込み;

-- 最小値、最大値
select min(*columns*) from *tables* where 最小値を知りたいレコードの絞り込み;
select max(*columns*) from *tables* where 最大値を知りたいレコードの絞り込み;

集約関数では、基本的にnullは無視される

・文字列検索　ある文字数以上（以下）のレコードを知りたい
select columns from tables where char_length(columns) >= 文字数;
　char_length()の中のカラム名にはクォーテーションをつけない

・対象レコードの数を調べる
select count(columns) from tables where 調べたいレコードを絞り込む;

-- 重複を排除する（下記は1000円以下の商品が何種類あるか、最高料金はいくらか）
Ex. select count(distinct price), max(price) from products where price < 1000;

・データの集計をグループ毎におこなう
select columns from tables group by グループにしたいカラム;
　　→例えば
　　　select organization_id, sum(login_num) from user group by organization_id;
　　　とするとorganization_id毎のlogin_numが表示される。

・集計したグループに条件式を使う
select columns from tables group by グループにしたいカラム having 条件式;
　→whereとの違いは、グループにしたカラムに対してのみ条件式を指定できる

-- OK
select A, B from db where C > 100 group by D;
select A, B from db group by D having B > 10;

-- NG
select A, B from db group by D having C > 10;

・逆順で表示（カラム１を降順にしたい）
select columns_1,columns_2 from tables order by columns_1 desc;
　→昇順だと以下の通り
　　select columns_1,columns_2 from tables order by columns_1 asc;

・unionによる和結合
　→inner joinなどは、テーブルを結合させて自テーブルには無いレコードも出力できるようにする方法。unionは、テーブルを結合させて出力結果を増やす（＝データソースを増やす）。
　　重複分を表示させるには、union allとする
例：admin_usersとusersテーブルをunionする

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

・内部結合（ベン図で言う両方の要素をもつ部分）
例：productsテーブルとstoresテーブルを結合させて、「店舗名・商品名・1000円以上の価格」の情報を取得
　→productsテーブルにとって、store_idは外部キー。
　　storesテーブルにとってはそれが主キー。

select
s.name,
p.product_name,
p.price
from
products as p
inner join
stores as s
on 
p.store_id = s.id
where
p.price > 1000;

・外部結合（ベン図で言う片方の円のこと。もう片方と合わさらない箇所はNULL）
　参考：https://qiita.com/devopsCoordinator/items/3ba154429973a4ddc9e0 など
例：ordersテーブルとcustomersテーブルを結合させて、「顧客名（1, 2）・配達日・顧客Eメール」の情報を取得

select
c.id,
c.name1,
c.name2,
o.customer_id,
o.delivery_date,
o.customer_email
FROM
orders as o
right outer join -- left outer joinとすると、ordersテーブルがマスタテーブル
customers as c
on o.customer_id = c.id
group by c.id;

内部結合も外部結合も、3つ以上結合する時は、on〜の後にinner join〜などと続ける。

・もしも値がNULLなら〜に変更する
例：company_idがNULLなら0にする

select  
c.id,
case
    when c.company_id is NULL then 0
    else c.company_id
end company_id
from
customers as c;

・case文（条件分岐）
出力結果を条件分岐させたい時に使用する
例：2017年以降に注文してくれた顧客を優良顧客に、2017年〜2016年に注文してくれた顧客をメルマガ送付対象に、それ以外をノーアクティブに分けたい。

select
c.id as customerID,
c.name1,
c.name2,
case
    when o.delivery_date > '2016-12-31' then '優良顧客'
    when o.delivery_date > '2015-12-31' then 'メルマガ送付対象'
    else 'ノーアクティブ'
end as '顧客ランク'　-- このendは忘れやすい。名前を付与しないとカラム名は「case」になる
from
orders as o
inner join
customers as c
on
o.customer_id = c.id
group by
c.id
order by c.id asc;

・サブクエリ
ある問い合わせに基づいて、異なる問い合わせをおこなう
例：最も高額な商品の「店舗ID・商品名」を出力する

select
store_id,
product_name
from
products
where
price in(
    select
    max(price)
    from
    products
    );

※max(price)は通常where句に使用できない
※サブクエリ内のカラムやテーブル名に別名をつける際は、別途指定する必要がある

・mysqlサーバーの確認
sudo service mysqld status

・mysqlサーバーの自動起動
sudo chkconfig mysqld on

・mysqlサーバーの起動
sudo service mysqld start

・mysqlサーバーの自動起動の確認（２〜５がon）
sudo chkconfig --list mysqld

・エクスポート、インポート（CSV含む）
こちらを参照　→　https://normalblog.net/system/mysql_matome/

・正規化
DBで保持するデータの冗長性を排除し、一貫性と効率性を保持するデータ形式にすること

・主キー
プライマリーキー。１つの行を特定できる列のこと。

・外部キー
フォーリンキー。他テーブルとの関連づけに使う列のこと。 関連づけられた先のテーブルでは主キーになる。

・リレーションシップ
一対多
ユーザー　：　注文
ブログ主　：　投稿記事
注文　：　注文明細
部署　：　従業員

多対多（中間テーブルがあることを想像すると分かりやすい）
商品　：　商品カテゴリー
投稿記事　：　記事カテゴリー
ユーザー　：　操作権限

一対一（操作権限がある人しか見れないようにする際などに使用）
ユーザー　：　電話番号