分からんこと多すぎ

何故か就職できたので技術磨く。一人前の多重線形代数使いを目指しつつ、機械学習、データマイニングをやる

超絶初心者が初めてMySQLを使ってみる

server mysql

優しい解説はこちら
MySQL初心者入門講座
MySQL入門 (全19回) - プログラミングならドットインストール
MySQL 逆引きリファレンス


MySQLとは


MySQLとはデータベースである。
MySQL :: Developer Zone
日本語版マニュアルあり

用語


  • テーブル（行列）

    • フィールド（列）

    • レコード（行）

使ってみた


シェル・コマンドで-u（ユーザ）と-p（パスワード）を指定してmysqlの中へ

mysql -u root -p


  1. status
    mysqlの状態が確認できる。
    OS,文字コード,起動時間など

  2. set password for root@localhost=password('自分のパスワード');
    パスワードの設定変更

  3. grant all on sample.* to ユーザ名@localhost identified by '自分のパスワード';
    作業用ユーザを作ることができる
    この場合はsampleというデータベース以下の全てのテーブル（.*）にアクセス件のあるユーザ

  4. show databases;
    データベース名が見れる

  5. use mysql;
    mysqlというデータベース内に入る

  6. show tables;
    mysqlというデータベースの中にあるテーブルが見れる。

  7. describe user;
    mysqlというデータベースの中のuserというテーブルの中が見れる
    desc user;でも可

  8. select Host from user;
    mysqlというデータベースの中のuserというテーブルの中のuserの中が見れる

  9. create database sample;
    sampleという名前のデータベースができる

  10. create table テーブル名();
    テーブル名で指定した名前のテーブルが作られる。

    create table test1(
  id int not null auto_increment,
  name varchar(255),
  email varchar(255) unique,
  password char(32),
  score double,
  sex enum('male','female') default 'male',
  memo text,
  created datetime,
  primary key (id),
  key score (score)
);

create table item(
  id int not null auto_increment primary key,
  item_name varchar(255) unique key,
  user_id int,
  value int,
  memo text,
  created datetime
);

    とすると、以下の様なテーブルができる。

    +----------+-----------------------+------+-----+---------+----------------+
| Field    | Type                  | Null | Key | Default | Extra          |
+----------+-----------------------+------+-----+---------+----------------+
| id       | int(11)               | NO   | PRI | NULL    | auto_increment |
| name     | varchar(255)          | YES  |     | NULL    |                |
| email    | varchar(255)          | YES  | UNI | NULL    |                |
| password | char(32)              | YES  |     | NULL    |                |
| score    | double                | YES  | MUL | NULL    |                |
| sex      | enum('male','female') | YES  |     | male    |                |
| memo     | text                  | YES  |     | NULL    |                |
| created  | datetime              | YES  |     | NULL    |                |
+----------+-----------------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)
    • オプション
      • 数値
        • int（整数）
        • double（実数）
      • 文字列
        • char（固定長）
        • varchar（可変長）
        • text（長さ不明）
      • 日付
        • date
        • datetime
      • その他
        • enum（列挙型）
      • 指定
        • not null（入力必須事項,null不許可）
        • default（デフォルト値）
        • auto_increment（自動連番）
        • 索引（インデックス）フィールドの検索の高速化。挿入は遅い
          • primary key（主キー、レコードを一意に特定する（テーブルで一つ））
          • key（キー、よく検索するフィールドの索引を作る）
          • unique（ユニークキー、重複禁止）

  11. insert
    データをテーブルに挿入するコマンド

    insert into test1 (name,email,password,score,memo,created) values
('satou','tmp1@yahoo.co.jp','pasdayo','1','早く帰りたい','2013-05-24 11:11:11'),
('suzuki','tmp2@yahoo.co.jp','eeeeee','5','おなかへった','2013-05-24 11:11:29'),
('takeda','tmp3@gmail.com','koregakagi','10','allow us to get back home','2013-05-25 11:31:11'),
('大橋','tmp4@gmail.com','passsss','100','','2014-05-30 21:11:11');

insert into item (item_name,user_id,value,memo,created) values
('suger','1','100','','2000-05-24 11:11:11'),
('solt','2','200','','2013-05-24 11:11:11'),
('apple','3','300','','2013-06-24 11:11:11'),
('banana','1','1000','','2014-05-24 11:11:11'),
('スイカ','1','2000','','2022-05-24 11:11:11'),
('牛乳','2','10000','','2100-05-24 11:11:11');
    +----+--------+------------------+------------+-------+------+---------------------------+---------------------+
| id | name   | email            | password   | score | sex  | memo                      | created             |
+----+--------+------------------+------------+-------+------+---------------------------+---------------------+
|  1 | satou  | tmp1@yahoo.co.jp | pasdayo    |     1 | male | 早く帰りたい              | 2013-05-24 11:11:11 |
|  2 | suzuki | tmp2@yahoo.co.jp | eeeeee     |     5 | male | おなかへった              | 2013-05-24 11:11:29 |
|  3 | takeda | tmp3@gmail.com   | koregakagi |    10 | male | allow us to get back home | 2013-05-25 11:31:11 |
|  4 | 大橋   | tmp4@gmail.com   | passsss    |   100 | male |                           | 2014-05-30 21:11:11 |
+----+--------+------------------+------------+-------+------+---------------------------+---------------------+
4 rows in set (0.00 sec)

    insertを何回か打ってメールのダブリ不許可に引っかかると、idが1から10に飛んだりする。
    auto_incrementを使うと番号を飛ばしそうで怖いことがわかった。

  12. select

    1. select * from test1;
      test1テーブルの全フィールドの全レコードを表示

    2. select name, email from test1;
      test1テーブルのnameとemailのみ表示

    3. select * from test1 where id == '1';
      idが1の行のみ表示

    4. select * from test1 where email like '%yahoo.co.j_';
      emailが「（文字列）＋yahoo.co.j＋（一文字）」の行のみ表示

    5. select * from test1 where id like '1' \G
      横方向に表示

      *************************** 1. row ***************************
      id: 1
    name: satou
   email: tmp1@yahoo.co.jp
password: pasdayo
   score: 1
     sex: male
    memo: 早く帰りたい
 created: 2013-05-24 11:11:11
1 row in set (0.00 sec)


    6. select * from test1 where score between 1 and 10;
      scoreが1以上10以下のレコード

    7. select * from test1 where id in ('1',3);
      idが1か3のレコード

    8. select * from test1 where score > 10 or id in (1);
      scoreが10より大きいか、idが1のレコード

    9. select * from test1 order by name;
      名前順。アルファベット優先

    10. select * from test1 order by score dest;
      scoreの逆順

    11. select * from test1 limit 3;
      上位三レコード

    12. select * from test1 limit 1,2;
      二個目のレコードを含めて二個

    13. select * from test1 where score < 80 order by score desc limit 2;
      scoreが80より小さくscore降順の上位二つ

    14. select count(*) from test1;
      レコードの数

    15. select distinct name from test1;
      nameフィールドの要素

    16. select avg(score) from test1;
      scoreフィールドの平均。min,mac,sumなどがある

    17. select sex, avg(score) from test1 group by sex;
      sexフィールドごとの平均score

    18. select * from test1 order by rand() limit 1;
      乱数順？上位一つのレコード

    19. select memo from test1 where length(memo) < 19;
      memoのbyte長が19より小さいmemoフィールドを表示

    20. select concat(name,'(',email,')') as 'mailですよ' from test1;

      +--------------------------+
| mailですよ               |
+--------------------------+
| satou(tmp1@yahoo.co.jp)  |
| suzuki(tmp2@yahoo.co.jp) |
| takeda(tmp3@gmail.com)   |
| 大橋(tmp4@gmail.com)     |
+--------------------------+
4 rows in set (0.00 sec)


    21. select now();
      現在時刻

    22. select name, substring(memo,1,1) from test1;
      memoの1文字目から1文字目まで

    23. select name,datediff(now(),created) from test1;
      now()とcreatedフィールドの差分

    24. select name,month(created) from test1;
      createdフィールドの月のみ


  13. update test1 set memo = 'i can enjoy making server hahahaha' where length(memo) <= 0
    memoフィールドの長さが0のものに文字列を挿入する

  14. delete from test1 where substring(memo,1,1) = 'i';
    memoフィールドの位置文字目がiのものは削除

  15. drop table test1;
    test1というテーブルを削除する

  16. drop database sample;
    sampleというデータベースを削除する

  17. alter

    1. alter table test1 add full_name varchar(255) after name;
      nameフィールドの次にfull_nameフィールドを作る

    2. alter table test1 change full_name fullfull_name varchar(100);
      full_nameフィールドの名前と変数を変更する

    3. alter table test1 drop fullfull_name;
      fullfull_nameフィールドの削除

    4. alter table test1 add index hiduke_desu (created);
      createdフィールドにhiduke_desuという名称のindex付加（検索速度向上）

    5. alter table test1 drop index hiduke_desu;
      creadedフィールドのindexを削除

    6. alter table test1 rename users;
      test1テーブルの名前をusersに変更


  18. select users.name, item.item_name, item.value from users, item where users.id = item.user_id order by item.value desc;
    usersテーブルのidとitemテーブルのuser_idを紐付けて表示

    +--------+-----------+-------+
| name   | item_name | value |
+--------+-----------+-------+
| suzuki | 牛乳      | 10000 |
| satou  | スイカ    |  2000 |
| satou  | banana    |  1000 |
| takeda | apple     |   300 |
| suzuki | solt      |   200 |
| satou  | suger     |   100 |
+--------+-----------+-------+
6 rows in set (0.00 sec)

  19. mysql -u ユーザ名 -p sample < test.sql
    シェルで打ち込むとスクリプトを回せる
    test.sqlの中身は以下。

    drop table if exists tmp;
create table tmp(
  name varchar(255),
  email varchar(255)
);
insert into tmp (name,email) values ('namae', 'mailaddress');

  20. mysqldump -u dgp -p sample > sample.dump.sql
    バックアップファイルの作成

  21. mysql -u dgp -p sample < sample.dump.sql
    バックアップファイルからの復元
    すでにあるデータベース名との一致が必要

  22. select * from users into outfile '/Users/admin/unidir/hatena/sample.csv' fields terminated by ',' optionally enclosed by '"';
    '/Users/admin/unidir/hatena/sample.csv'にusersテーブルをフィールド区切り文字','と、くくり文字'"'で書きだす

  23. load data infile '/Users/admin/unidir/hatena/sample.csv' into table users_copy fields terminated by ',' enclosed by '"';
    sample.csvをusers_copyというテーブルに書き込む
    先にusers_copyというテーブルを作っていないと拒否される