mysqlの練習用の備忘録です。
やりたいことはvagrantのローカル環境にてmysqlの使い方を忘れないように
練習用の備忘録です。
※ドットインストールさんの動画を参考にしています。
動画見ながらだと時間がかかるのでテキストベースに落とし込んでおこうと思いました。
主な内容
- DBの作成、削除
- テーブルの作成、削除
- データの挿入、編集、削除
- カラムの挿入、編集、削除
- データの検索、応用検索
- データの結合検索
※基本的にmysqlが導入されている状態です。
練習内容
◆作業用ユーザーを作成、rootユーザーでログインする。
mysql -u root -p
◆rootユーザーからDBを作成する。
create database blog_app;
◆作業用ユーザーの作成
grant all on blog_app.* to dbuser@localhost identified by '123456';
◆確認用
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 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)
);
◆データを挿入
insert into users (name,email,password,score,memo,created) values ('taiti','taiti@stone-rise.com','123456','5','test','2015-04-20 11:00:00');
◆データ確認
select * from users;
◆ここまでのテストでのtableは一旦削除
drop table users;
◆新しくtableを作成
create table users (
id int not null primary key auto_increment,
name varchar(255),
email varchar(255),
team enum('blue','red','yellow'),
score double,
created datetime
);
◆テスト用のデータ作成
insert into users (name,email,team,score,created) values
('mikami','mikami@stone-rise.com','blue',5.5,'2012-05-11 11:00:00'),
('fkoji','fkoji@stone-rise.com','yellow',8.2,'2012-06-21 12:00:00'),
('domino','domino@stone-rise.jp','red',2.3,'2012-06-21 13:00:00'),
('sasaki','sasaki@stone-rise.com','blue',4.5,'2012-06-25 14:00:00'),
('kimura','','yellow',7.4,'2012-06-28 15:00:00'),
('nakamura','nakamura@stone-rise.jp','blue',4.2,'2012-06-29 16:00:00');
◆確認用
◆◆すべて表示させる
select * from users;
◆◆!=の後以外を表示させる
select * from users where score != 5.5;
◆◆<>の後以外を表示させる
```lang:
select * from users where score <> 5.5;
◆◆あいまい検索
select * from users where email like '%@stone-rise.com';
◆◆あいまい検索 _これで文字数を指定している
select * from users where email like '%@stone-rise.__';
◆◆範囲の指定 between
select * from users where score between 5.0 and 8.0;
◆◆範囲の指定 in（予め設定した範囲内にあるか）
select * from users where team in ('red', 'yellow');
◆◆応用
select * from users where score >= 4.0 and team = 'blue';
select * from users where score >= 4.0 or team = 'blue';
◆◆並び替え スコア順に並び替える
select * from users order by score;
◆◆並び替え スコア順に並び替える 逆に表示させる
select * from users order by score desc;
select * from users order by name desc;
◆◆並び替え 件数の指定
select * from users limit 3;
select * from users limit 2, 2;
◆◆並び替え スコアの大きい順 上から3つ
select * from users order by score desc limit 3;
◆データの集計、データがいくつあるか確認
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 sum(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, ')') 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 users set email = 'kimura@stone-rise.jp' where id = 5;
select * from users;
-削除（scoreのなになに以下）
delete from users where score <= 3.0;
select * from users;
◆テーブルの構造を変更してみる
-構造確認
desc users;
-カラムを追加する。
alter table users add full_name varchar(255) after name;
desc users;
-カラムの構造を変更する。（varcharを変更するなど）
alter table users change full_name full_name varchar(100);
desc users;
-カラムを削除する。
alter table users drop full_name;
desc users;
-キーをつける。
alter table users add index email (email);
desc users;
-キーを削除する。
alter table users drop index email;
desc users;
-table名を変更する。
alter table users rename blog_users;
show tables;
◆結合検索
-データ準備
create table users (
id int not null primary key auto_increment,
name varchar(255),
email varchar(255),
team enum('blue','red','yellow'),
score double,
created datetime
);
insert into users (name,email,team,score,created) values
('taguchi','taguchi@stone-rise.com','blue',5.5,'2012-05-11 11:00:00'),
('fkoji','fkoji@stone-rise.com','yellow',8.2,'2012-06-21 12:00:00'),
('nakamura','nakamura@stone-rise.com','red',2.3,'2012-06-21 13:00:00');
create table posts (
id int not null primary key auto_increment,
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 taguchi', 'body-1','2012-05-11 14:00:00'),
(1, 'title-2 by taguchi', 'body-2','2012-05-11 12:00:00'),
(2, 'title-3 by fkoji', 'body-3','2012-05-11 13:00:00'),
(3, 'title-4 by nakamura', 'body-4','2012-05-11 10:00:00'),
(3, 'title-5 by nakamura', 'body-5','2012-05-11 09:00:00');
◆データ確認
select * from users;
◆◆複数のテーブルをまたがって検索 タイトルとユーザー名を表示させる
selectの後に表示させるカラムを入れる、from users,postsでテーブルを入れる、whereで条件指定します。
紐付けたいカラムを指定します。
select * from posts;
select users.name, posts.title from users, posts where users.id = posts.user_id;
select users.name, posts.title from users, posts where users.id = posts.user_id order by posts.created desc;