テーブルの作り方
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;
