レコードの件数表示
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
