[Server & Network General] MySQL: ドットインストール「MySQL入門」のまとめ

% mysql -u root
/* パスワードを設定する */
> set password for root@localhost=password('hogehoge');
> exit;
% mysql -u root -p
/* データベースを作成する */
> create database blog_app;
/* データベースの一覧を表示する */
> show databases;
/* データベースを削除する */
> drop database blog_app;
/* 利用するデータベースを指定する */
> use blog_app;

/* 作業ユーザーを作成する */
> grant all on blog_app.* to dbuser@localhost identified by 'dbuserpass';
> exit;
/* 作業ユーザーでログインする */
% mysql -u dbuser -p blog_app;

/* テーブルを作成する */
create table users (
  id int,
  name varchar(255),
  email varchar(255),
  password char(32)
);

/* テーブルの一覧を表示する */
> show tables;
/* テーブルを削除する */
> drop table users;

create table users (
  id int,
  name varchar(255),
  email varchar(255),
  password char(32),
  score double,
  sex enum('male', 'female'),
  memo text,
  created datetime
);

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)
);

/* テーブルの構造を表示する */
> desc users;

insert into users (name,email,password,score,memo,created) values ('suzuki','suzuki@exsample.com','password','4.6','memommmm.','2012-06-12 11:00:00');

/* テーブル(users)から、すべて(*)のフィールドを抽出する */
select * from users;
select name, email from users;
/* フィールドを縦に並べて表示するオプション */
select * from users \G

/* 条件付きでレコードを抽出する */
select * from users where score >= 5.0;
select * from users where team != 'red';
select * from users where created > '2012-06-01 00:00:00';
/* あいまいな条件で抽出する */
select * from users where email like '%@example.com';
select * from users where email like '%@example.__'; -- この場合は2文字のみ(ex. jp)

/* 範囲を指定して抽出する */
select * from users where score between 5.0 and 8.0;
select * from users where team in ('red', 'yellow');
/* 条件を組み合わせる */
select * from users where score >= 4.0 and team = 'blue';

/* 並び替え */
select * from users order by score; -- 昇順
select * from users order by score desc; -- 降順
/* 件数の制限 */
select * from users limit 3;
select * from users limit 3, 2; -- 開始位置 3 から 2 件

/* 件数のカウント */
select count(*) from users;
/* 重複のないフィールドに含まれる値 */
select distinct team from users;
/* 最大値(max), min, avg, sum */
select max(score) from users;
/* group by グループ毎の集計 */
select team, avg(score) from users group by team;
/* 乱数を発生 */
select rand();
select * from users order by rand() limit 1;

/* 文字列の長さを求める */
select email,length(email) from users;
/* 文字列を結合する */
select concat(name,'(',team,')') from users;
/* 結果表示時の命令に別名を付ける
concat(name,'(',team,')') を label とスッキリ表示
*/
select concat(name,'(',team,')') as label from users;
/* 文字列の一部のみを表示
team の 1文字目から 1文字目 */
select name,substring(team,1,1) from users;
/* 日付関数
2012-06-27 19:30:00 */
select now();
/* create された月を表示する */
select name,month(created) from users;
/* 日付の差分を日数で求める */
select name, datediff(now(), created) from users;

/* レコードの更新
update テーブル名 set フィールド名 where 条件; */
update users set email = 'kimura@dotinstall.jp' where id = 5;
/* レコードの削除
delete from テーブル名 where 条件;
 */
delete from users where score<= 3.0;

/* フィールドを追加する
alter table テーブル名 add 追加するフィールド名 型 after fooの後に;
*/
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 テーブル名 add index 付ける名前 (フィールド名);
 */
alter table users add index email (email);
alter table users drop index email;
/* テーブル名を変更する */
alter table users rename blog_users;

select users.name,posts.title from users,posts where users.id=posts.user_id;
select users.name,posts.title,posts.created from users,posts where users.id=posts.user_id order by posts.created desc;

drop table if exists users; -- 上書き
create table users (
  name varchar(255),
  email varchar(255)
);
insert into users (name,email) values ('tom','tom@example.com');

% mysql -u dbuser -p blog_app < commands.sql

/* バックアップ */
% mysqldump -u dbuser -p blog_app > blog_app.dump.sql
/* 復元 */
% mysql -u dbuser -p blog_app < blog_app.dump.sql