MySQLに関する私的なメモです。書いた本人にしかわからないことが多いです。

メモ

起動

mysql -u username -p リターン → パス入力
・エラーが出るときはmysqlがサービス開始していない。コントロールパネル→管理ツール→サービス→MySQL57を選択し、サービスの開始を押す。

起動(xamppのMySQLに接続)

  1. コマンドプロンプトから実行するにはpath(mysql.exeの場所)を指定する必要がある
  2. 「コントロールパネル」→「システムとセキュリティ」→「システム」→「システムの詳細設定」で設定ウィンドウが開く
  3. 「詳細設定」タブの「環境変数」
  4. 「ユーザー環境変数」の「Path」をセレクトして「編集」ボタン
  5. 「新規」をクリックし、xamppのmysql.exeがあるpathを記入(通常はC:¥xampp¥mysql¥bin
  6. コマンドプロンプトを起動し、mysql -u ユーザー名 -p Enter

終了

quit

目的のデータベースを使う

use データベース名;

テーブル作成

create table テーブル名 (列名 データ型, ….)
データ型種類:int, double, bool, varchar, char, text, blob, datetime, date, datetime

テーブル/データベースの削除

drop table テーブル名
drop database データベース名

テーブルを確認

show tables
show columns from テーブル名

行を追加

↓カラム名を指定して、値を入れる。
insert into テーブル名 (カラム名1, カラム名1, …) values (値1, 値2, …)

↓全てのカラムに値を入れる場合。カラム名を省略できる。
insert into テーブル名 values (値, ….)
insert into テーブル名 values (値1, …..)

データの表示

select * from テーブル名 ・・・・・・テーブル全て表示
select 列名 from テーブル名  ・・・・・・指定列のみ表示する 列はカンマで複数指定可能

条件で行検索

select 列名 from テーブル名 where 条件  ・・・・条件の例:where prefecture = ‘東京都’

行の更新

update テーブル名 set 列名 = 値, … where 条件

行の削除

delete from テーブル名 where 条件

列の削除

alter table テーブル名 drop 列名 ・・・・・・列の削除は、下にある「テーブルの定義を変更する」で行う

テーブルの定義を変更する。

alter table テーブル名 add 列名 データ型 (first または after 「列名」) ・・・・列を追加 afterの場合は「列名」の後に追加
alter table テーブル名 change 列名 新列名 新データ型  ・・・・列の定義を全体的に変更
alter table テーブル名 modify 列名 データ型   ・・・・・・・列のデータ型を変更
alter table テーブル名 drop 列名 ・・・・・・・・・列を削除する

テーブル名の変更

alter table 旧テーブル名 rename 新テーブル名;

抽出時の条件指定

= 等しい
<>, != 等しくない
>=, <= 以上、以下
>, < より大きい、より小さい
between 値1 and 値2 値1以上かつ値2以下
in (値1, 値2, …) いずれかの値と等しい
is null データがnull
is not null データがnullではない
条件1 and 条件2 両方の条件が真 (&&でも可)
条件1 or 条件2 どちらかの条件が真 (||でも可)
not 否定 (!でも可)

あいまい検索

like ‘Japa%” Japaで始まるという条件(%は文字数制限なし)
like ‘Japa_’ Japaプラス1文字の条件

件数を限定する

limit 件数
limit 開始行, 件数 ※注意;テーブルの最初の行は0行目

並び順

order by 列名 ascまたはdesc

値を計算する

列名/1000  などの式でOK 例 select population/1000 from city;

データの集計

avg(列名) 平均値
count(列名) 行数 count(*)とすると全行数
max(列名) 最大値
min(列名) 最小値
sum(列名) 合計

グループ化して集計する

select文の後に続けてgroup by 列名 with rollup ※with rollupを付けると総計が最後に付く。

Where 抽出する前に条件で取捨選択(取捨選択したあとでグループ化)

注意:havingとの使い分けに注意
【例】
select music, sum(sales) as TotalSales from musiccategory
where sales > 1000000
group by music; ←グループ化

Having 抽出した後に条件で取捨選択(グループ化した後で取捨選択)

注意:whereとの使い分けに注意

group by の後でhaving 条件 を記載する
【例】
select music, sum(sales) as TotalSales from musiccategory
group by sales
having TotalSales > 1000000

日時の関数

curdate() , current_date(), current_date : 現在の日付を返す
durtime() , current_time(), current_time : 現在の時間を返す
now(), current_timestamp(), current_timestamp : 現在の日付と時間を返す
dayname() : 曜日の名前を返す
datediff(日付1, 日付2) : 日付1と日付2の差を返す
date_add(日付, interval 期間) : 日付に期間を加える。
期間はyear, month, day, hour, minute, second が指定できる。
date_format() : 日付を指定内容に基づいてフォーマットする
日時フォーマットの指定
%Y: 年 4桁の年
%y: 年 2桁の年
%b: 月 Jan ….. Dec
%c: 月 1 …….12
%m: 月 01…….12
%a: 曜 Sun ….. Sat
%d: 日 01…….31
%H: 時 01…….23
%h: 時 01…….12

予約後を使うには

・予約語とは、limit, select などのもともとQSLの命令語として使用方法が決まっている語のこと。
・予約後を列名に使いたい場合などはクオートで囲う。【例】 as ‘Limit’ などのように。

主キーの設定

列の定義設定でprimary keyと追記する。
create table テーブル名(列名 データ型 primary key);

ユニークキーの設定

列の定義設定でunique keyと追記する。

主キーとユニークキーの違い

主キーの列はNullが許されず、全てのデータが重複不可。
ユニークキーの列はNullが許される(Nullは重複可)。Null以外のデータは重複不可。

主キーに自動で連番を振る

・テーブル作成で主キー列を指定する時にauto_increment と追記する ※auto_incrementは主キーの列しか使用できない。
【例】
create table users(id int aut_increment primary key);

列の順序入れ替え

・たとえば NAME, ID という列順を逆にするには下のように書く
alter table テーブル名 modify NAME text after ID;

デフォルトで値を入れる

テーブル作成時に列を記述する箇所でdefault ‘デフォルト値’ と記述する。
【例】create table テーブル名(username varchar(10) default ‘defftable’);

テーブルの結合の種類

直積結合(cross join):テーブルN(n行)×テーブルM(m行)のテーブルを作成
内部結合(inner join):合致する行のみを結合する
外部結合(left join, right join):合致しない行を含めて結合する

直積結合cross join

下のどちらの書き方でもOK
select * from テーブル名1.列名1, テーブル名2.列名2;
select * from テーブル名1.列名1 cross join テーブル名2.列名2;

内部結合inner join

条件が一致する行だけが結合される
【例】
select city.countrycode, country.code ・・表記する列を、「テーブル名.列名」で記述
from city inner join country・・・・・・・元側のテーブル名とくっつける側のテーブル名
on city.countrycode = country.code;・・・on 結合列 ※using(共通の列名) という記述でもok。ただし列名や定義が等価であること。

外部結合 left join , right join

条件の合わない行も含めて結合することができる
left joinは、fromの後に書かれたテーブルを中心に結合する。
right joinは、joinの後に書かれたテーブルを中心に結合する。
※機能的にはleftもrightも同じなので、leftで統一して使うとよい。

列に別名を付ける

select music.name as musicname from musiccategory ・・・・・列の指定時にas 別名 で抽出結果を別名表記できる。

テーブルに別名を付ける

select msc.name from music as msc ・・・・・・・・・テーブルの指定時にas 別名 で好きな別テーブル名でクエリを記述できる。

外部制約キー(親テーブルに存在する値のみ入力が認められる)

【例】
create table employee (name varchar(10), divisionID int, foreign key (divisionID) references divisions(ID));
従業員テーブルの部署IDは、親テーブルであるdivisionsテーブルのID列を参照する。親テーブルのID列に無い値は入力できない。

クエリの結果を別のクエリで使う

・Where や from などの記述部分に、サブクエリを()で閉じて記述すればよい。
【例】
select music, sales from city
where sales = (select max(sales) from music where musiccode=’POP’)
and country code = ‘POP’;