レコードの件数表示
select count(*) from users;
レコードのフィールドの値
select distinct team from users;
最大値の抽出
select max(score) from users;
select avg(score) from users;
select sum(score) from users;
チームごとの平均値表示
select avg(score) from users group by team;
ランダム関数も使えます。
select rand();
select * from users order by rand() limit 1;
mysql> select * from users order by rand() limit 1;
+----+--------+------------------+----------+------+-------+---------------------+
| id | name | email | password | team | score | created |
+----+--------+------------------+----------+------+-------+---------------------+
| 3 | kimura | kimura@gmail.com | NULL | red | 2.3 | 2016-11-01 09:00:00 |
+----+--------+------------------+----------+------+-------+---------------------+
1 row in set (0.00 sec)
文字列、日付関数
select email, length(email) from users;
連結関数
select concat(name,'(‘,team,’)’) from users;
select concat(name,'(‘,team,’)’) as label from users;
select name, substring(team,1,1) from users;
select now();
select name,month(created) from users;
select name, datediff(now(), created) from users;
レコードの更新は通常updateを使います。
mysql> update users set team = 'red' where id = 5;
Query OK, 1 row affected (0.06 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from users;
+----+----------+--------------------+----------+--------+-------+---------------------+
| 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 | red | 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)
削除にはdeleteを使用します。
delete from users where score <= 3.0;
データ構造の変更
フィールドの追加
alter table users add full_name varchar(255) after name;
フィールドの変更
alter table users change full_name full_name varchar(100);
フィールドの削除
alter table users drop full_name;
テーブル名変更
alter table users rename blog_users;
テーブルの連結
テーブルを追加して、連結します。
create table posts (
id int not null auto_increment primary key,
user_id int not null,
title varchar(255),
body text,
created datetime
);
insert into posts (user_id,title,body,created) values
(1, 'title-1 by yamada','body-1','2016-11-01 14:00:00'),
(1, 'title-2 by yamada','body-2','2016-10-31 13:00:00'),
(2, 'title-3 by sasaki','body-3','2016-10-30 12:00:00'),
(2, 'title-4 by sasaki','body-3','2016-10-29 11:00:00'),
(3, 'title-5 by kimura','body-4','2016-10-28 10:00:00'),
(3, 'title-6 by kimura','body-4','2016-10-27 09:00:00');
mysql> select * from posts;
+----+---------+-------------------+--------+---------------------+
| id | user_id | title | body | created |
+----+---------+-------------------+--------+---------------------+
| 1 | 1 | title-1 by yamada | body-1 | 2016-11-01 14:00:00 |
| 2 | 1 | title-2 by yamada | body-2 | 2016-10-31 13:00:00 |
| 3 | 2 | title-3 by sasaki | body-3 | 2016-10-30 12:00:00 |
| 4 | 2 | title-4 by sasaki | body-3 | 2016-10-29 11:00:00 |
| 5 | 3 | title-5 by kimura | body-4 | 2016-10-28 10:00:00 |
| 6 | 3 | title-6 by kimura | body-4 | 2016-10-27 09:00:00 |
+----+---------+-------------------+--------+---------------------+
6 rows in set (0.00 sec)
mysql> select users.name,posts.title from users,posts where users.id=posts.user_id;
+--------+-------------------+
| name | title |
+--------+-------------------+
| yamada | title-1 by yamada |
| yamada | title-2 by yamada |
| sasaki | title-3 by sasaki |
| sasaki | title-4 by sasaki |
+--------+-------------------+
4 rows in set (0.00 sec)
条件指定することも可能です。
select users.name,posts.title,posts.created from users,posts where users.id=posts.user_id order by posts.created desc;
外部のsqlファイルからコマンドを打つことも可能です。
drop table if exists users;
create table users (
name varchar(255),
email varchar(255)
);
insert into users (name,email) values ('name','name@gmail.com');
外部コマンドの入力方法は以下の通りです。
[vagrant@localhost mysql]$ mysql -u dbuser -p blog_app < commands.sql
