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