ソフトウェアエンジニアの技術ブログ：Software engineer tech blog

バックアップの取り方

[vagrant@localhost mysql]$ mysqldump -u dbuser -p blog_app > blog_app.dump.sql
Enter password:
[vagrant@localhost mysql]$ ls
blog_app.dump.sql  commands.sql

復元方法
不等号記号がバックアップの逆になります。
mysql -u dbuser -p blog_app < blog_app.dump.sql

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

テーブルの作り方

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;

mysqlの開発者用サイトです。
MySQL
データベースには、テーブル、フィールド(列)、レコード(行)の概念があります。Excelのスプレッドシートのように考えると分かり易いです。

mysqlの起動では、ユーザー名を入れて起動します。

[vagrant@localhost ~]$ mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.34 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

パスワードの設定は以下の通りです。

set password for root@localhost=password(”)

データベースの作り方は、create databaseで作れます。

mysql> create database blog_app;
Query OK, 1 row affected (0.12 sec)

一覧表示

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| blog_app           |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.14 sec)

データベースの削除

mysql> drop database blog_app;
Query OK, 0 rows affected (0.21 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)

データベースの利用

mysql> use test;
Database changed

作業ユーザーの設定方法

mysql> create database blog_app;
Query OK, 1 row affected (0.06 sec)

mysql> grant all on blog_app.* to dbuser@localhost identified by 'xxxx';
Query OK, 0 rows affected (0.16 sec)

作業ユーザーでのログイン

[vagrant@localhost ~]$ mysql -u dbuser -p blog_app
Enter password: