mysql 基礎

データベースの操作

• MySQLサーバへの接続
  mysql -u ユーザ名
  passwordを設定している場合は、ユーザ名の後ろに -p を付ける
• データベースの作成
  CREATE DATABASE データベース名;
• データベースの接続
  USE データベース名;
• データベースの削除
  DROP DATABASE データベース名;
  

ユーザの作成

GRANT ALL PRIVILEGES ON データベース名.* TO ユーザ名@接続元 IDENTIFIED BY 'パスワード' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO ‘test’@’%’ IDENTIFIED BY ‘123456789’ WITH GRANT OPTION;

テーブルの操作

• テーブルの作成

CREATE TABLE テーブル名(
    フィールド名 データ型,
    フィールド名 データ型,
    フィールド名 データ型,
    ...
);

• フィールドのデータ型
  • 数値型
    整数型：INT , BIGINT , TINYINT
    浮動小数点型：DOUBLE , FLOAT
  • 文字列型
    固定長文字列：CHAR
    可変長：VARCHAR
  • 日付型
    日付型：DATE
    時刻型：DATETIME
  • テキスト型
    テキスト型：TEXT , LONGTEXT

CREATE TABLE my_table(
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(255),
    password CHAR(32)
);

• データベース内のテーブル一覧の取得
  SHOW TABLES;

• テーブルのフィールドの確認
  DESC テーブル名;

• テーブルの削除
  DROP TABLE テーブル名;

レコードの操作

• レコードの追加
  INSERT INTO テーブル名(フィールド名) VALUES(値);
• レコードの確認
  SELECT フィールド名　FROM テーブル名 WHERE 抽出条件;
• レコードの更新
  UPDATE テーブル名　SET フィールド名 = 値 WHERE 抽出条件;
• レコードの削除
  DELETE FROM テーブル名 WHERE 抽出条件;

INSERT INTO user(username, password) VALUES('初音ミク','i_love_negi@vocaloid.ne.jp');
UPDATE products SET quantity = "hogehoge" WHERE id = 1;

複数のテーブルを更新する
下記の例では、エイリアス名を指定してますが、テーブル名だけでも大丈夫です。
言わずもがなですが、WHERE句を指定しない場合は全フィールドが更新対象となります。

UPDATE `テーブル名1` エイリアス名1, `テーブル名2` エイリアス名2
SET エイリアス名1.`フィールド名` = 値, エイリアス名2.`フィールド名` = 値
WHERE エイリアス名1.`フィールド名` = 値 AND エイリアス名2.`フィールド名` = 値

レコードの抽出条件

基本形式
SELECT フィールド名 FROM テーブル名;

• 抽出条件の指定
  WHERE フィールド名 比較演算子 値;

• あいまい検索 LIKE

  • %
    任意の0文字以上の文字列
  • _
    任意の1文字

抽出レコードの並べ替え(ソート)

ORDER BY フィールド名 ソート;
– 昇順
ASC
– 降順
DESC
複数のソートも可能
SELECT * FROM テーブル名 ORDER BY フィールド名1 DESC, フィールド名2 ASC
※フィールド名1をDESCした後、フィールド名2がASCされる

抽出レコード数の制限

• 取得数の指定
  LIMIT 取得数;
• 開始と条件数の指定
  LIMIT 開始位置, 取得数;

LIMITは最後に書かないとエラーを吐く(MySQLのみで確認)
▼これはエラー
SELECT * FROM テーブル名 LIMIT 0, 10 ORDER BY フィールド名1 DESC, フィールド名2 ASC
▼これはok
SELECT * FROM テーブル名 ORDER BY フィールド名1 DESC, フィールド名2 ASC LIMIT 0, 10

データの集計

• レコード件数の取得
  SELECT COUNT(フィールド名) FROM テーブル名;
• 指定フィールドの合計値
  SELECT SUM(フィールド名) FROM テーブル名;
• 指定フィールドの平均値
  SELECT AVG(フィールド名) FROM テーブル名;

グループ単位でのデータの集計
基本形式
SELECT フィールド名,データの集計 FROM テーブル名 GROUP BY フィールド名;

テーブル構造の変更

• テーブル名の変更
  ALTER TABLE テーブル名 RENAME 新テーブル名;
• フィールドの追加
  ALTER TABLE テーブル名 ADD フィールド名 データ型;
• フィールドの変更
  ALTER TABLE  テーブル名 CHANGE フィールド名 新フィールド名 データ型;
• フィールドの変更
  ALTER TABLE テーブル名 MODIFY フィールド名 データ型;
• フィールドの削除
  ALTER TABLE テーブル名 DROP フィールド名;

テーブルの結合

• 内部結合
  INNER JOIN
  テーブルの指定したフィールドの値が一致するデータだけを取得

SELECT テーブル名1.フィールド名,テーブル名2.フィールド名
FROM テーブル名1
INNER JOIN テーブル名2
ON テーブル名1.フィールド名 = テーブル名2.フィールド名;

• 外部結合
  OUTER JOIN
  テーブルの指定したフィールドの値が一致するデータに加え、
  どちらかのテーブルにしか存在しないデータについても取得します。
  LEFT OUTER JOIN
RIGHT OUTER JOIN
FULL OUTER JOIN
  があります。

SELECT テーブル名1.フィールド名,テーブル名2.フィールド名
FROM テーブル名1
LEFT OUTER JOIN テーブル名2
ON テーブル名1.フィールド名 = テーブル名2.フィールド名;

■メモ

• コマンドプロンプトから日本語を実行する場合
  mysql を起動後、
  SET NAMES sjis;
  を実行する。
• 文字コードを確認する
  SHOW VARIABLES like 'char%';
• Excelで例えると…
  ファイル名 => データベース名(sampledb)
  シート名 => テーブル名(user)
  Row => フィールド
  Column => レコード