mysqlで遊ぼう2

テーブルの作り方

mysql> create table users (
    ->  id int,
    ->  name varchar(255),
    ->  email varchar(255),
    ->  password char(32)
    -> );
Query OK, 0 rows affected (0.33 sec)

テーブルの確認

mysql> show tables;
+--------------------+
| Tables_in_blog_app |
+--------------------+
| users              |
+--------------------+
1 row in set (0.00 sec)

削除する際は、drop table users とします。
mysqlでは扱えるデータ型は複数あります。
-数値
int
double
-文字列
char(固定長)
varchar(可変長)
text
-日付
date
datetime
それ以外
enum

また、フィールドにオプションをつけることが可能です。
入力必須:not null
デフォルト値: default ”
自動連番 auto_increment
索引(インデックス) 主キー(primary key)、キー(key)、ユニークキー(unique)

では、createして、descでテーブルの構造を見てみましょう。

mysql> create table users (
    ->  id int not null auto_increment primary key,
    ->  name varchar(255),
    ->  email varchar(255) unique,
    ->  password char(32),
    ->  score double,
    ->  sex enum('male', 'female') default 'male',
    ->  memo text,
    ->  created datetime,
    ->  key score(score)
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> desc users;
+----------+-----------------------+------+-----+---------+----------------+
| 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)

挿入はinsert int tablename() values();、入力後は、select * from users;で確認できます。

mysql> insert into users (name,email,password,score,memo,created) values('name','test@gmail.com','xxx','5.5','memomemomemo','2016-11-04 12:00:00');
Query OK, 1 row affected (0.01 sec)

mysql> select * from users;
+----+------+----------------+----------+-------+------+--------------+---------------------+
| id | name | email          | password | score | sex  | memo         | created             |
+----+------+----------------+----------+-------+------+--------------+---------------------+
|  1 | name | test@gmail.com | xxx      |   5.5 | male | memomemomemo | 2016-11-04 12:00:00 |
+----+------+----------------+----------+-------+------+--------------+---------------------+
1 row in set (0.00 sec)

select * from users \G で縦に表示することも可能です。
select * from users where score >= 5.0; など条件付きの検索はwhereを使います。

mysql> select * from users where score >= 5.0;
+----+----------+--------------------+----------+--------+-------+---------------------+
| id | name     | email              | password | team   | score | created             |
+----+----------+--------------------+----------+--------+-------+---------------------+
|  1 | yamada   | yamada@gmail.com   | NULL     | blue   |   5.5 | 2016-11-03 11:00:00 |
|  2 | sasaki   | sasaki@gmail.com   | NULL     | yellow |   8.2 | 2016-11-02 10:00:00 |
|  5 | yamamoto | yamamoto@gmail.com | NULL     |        |   7.4 | 2016-10-30 07:00:00 |
+----+----------+--------------------+----------+--------+-------+---------------------+
3 rows in set (0.00 sec)

一部一致の検索は、likeを使います。

mysql> select * from users where email like '%@gmail.com';
+----+----------+--------------------+----------+--------+-------+---------------------+
| id | name     | email              | password | team   | score | created             |
+----+----------+--------------------+----------+--------+-------+---------------------+
|  1 | yamada   | yamada@gmail.com   | NULL     | blue   |   5.5 | 2016-11-03 11:00:00 |
|  2 | sasaki   | sasaki@gmail.com   | NULL     | yellow |   8.2 | 2016-11-02 10:00:00 |
|  3 | kimura   | kimura@gmail.com   | NULL     | red    |   2.3 | 2016-11-01 09:00:00 |
|  4 | tanaka   | tanaka@gmail.com   | NULL     | blue   |   4.5 | 2016-10-31 08:00:00 |
|  5 | yamamoto | yamamoto@gmail.com | NULL     |        |   7.4 | 2016-10-30 07:00:00 |
|  6 | yoshida  | yoshida@gmail.com  | NULL     | yellow |   4.2 | 2016-10-29 06:00:00 |
+----+----------+--------------------+----------+--------+-------+---------------------+
6 rows in set (0.00 sec)

その他の以下のようにコマンドです。
mysql> select * from users where score between 5.0 and 8.0;
mysql> select * from users where team in (‘red’, ‘yellow’);
mysql> select * from users where score <= 4.5 and team 'blue'; 並び替えはorder byを指定します。descは並び順が逆になります。 select * from users order by score; select * from users order by score desc; 制限を加えることもできます。 select * from users limit 3; select * from users limit 2, 2; select * from users order by score limit 3;