mysql

February 20, 2016 (Last Modified: September 20, 2020)

wiki, tech, tech

Page content

http://www.dbonline.jp/mysql/select/ がよくまとまっている

mysqlのコンソールに入る操作

mysql [OPTIONS] [database] … 省略すると、デフォルト値（ホスト名: localhost, ユーザ名: 現在のアカウント名） mysql -u root -p … rootでパスワード付きで接続

ユーザ管理

ユーザ作成 GRANT ALL PRIVILEGES ON *.* TO ユーザ名 IDENTIFIED BY 'パスワード'　WITH GRANT OPTION; … これは全権限を与えており、権限の書き換えも許可している。
GRANT INSERT,SELECT ON *.* TO user IDENTIFIED BY 'pass'; … insert, selectのみの権限を与えた場合。
ユーザの確認 SELECT host,user FROM mysql.user;
ユーザの権限の確認 SHOW GRANTS FOR ユーザ名;
パスワードの変更 SET PASSWORD FOR ユーザ名@"ホスト名"=password('変更後のパスワード'); … パスワード見られないように注意しながら打て。
ユーザの削除 DELETE FROM mysql.user WHERE user='ユーザ名';

databaseの作成

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

databaseに入る

mysql> use gregs_list;

現在のステータスの確認

mysql> status;
--------------
mysql  Ver 14.14 Distrib 5.1.52, for unknown-linux-gnu (x86_64) using readline 5.1

Connection id:          6
Current database:       zabbix
Current user:           zabbix@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.1.52 Source distribution
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:            /var/lib/mysql/mysql.sock
Uptime:                 15 min 29 sec

Threads: 1  Questions: 147  Slow queries: 0  Opens: 15  Flush tables: 1  Open tables: 8  Queries per second avg: 0.158
--------------

table管理

table作成

mysql> create table my_contacts( last_name varchar(30), first_name varchar(20), email varchar(50), birthday date, profession varchar(50), location varchar(50), status varchar(20), interests varchar(100), seeking varchar(100) );

table一覧表示

mysql> show tables;
+----------------------+
| Tables_in_gregs_list |
+----------------------+
| doughnut_list        |
| my_contacts          |
+----------------------+
2 rows in set (0.01 sec)

tableの様式を見る

mysql> desc my_contacts;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| last_name  | varchar(30)  | YES  |     | NULL    |       |
| first_name | varchar(20)  | YES  |     | NULL    |       |
| email      | varchar(50)  | YES  |     | NULL    |       |
| birthday   | date         | YES  |     | NULL    |       |
| profession | varchar(50)  | YES  |     | NULL    |       |
| location   | varchar(50)  | YES  |     | NULL    |       |
| status     | varchar(20)  | YES  |     | NULL    |       |
| interests  | varchar(100) | YES  |     | NULL    |       |
| seeking    | varchar(100) | YES  |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+
9 rows in set (0.00 sec)

または
mysql> show fields from <tablename>;

基本的なSQL文

SELECT: tableの中身を一覧表示 mysql> select * from my_contacts;

特定列だけ表示 mysql> select email, first_name from my_contacts;

INSERT: エントリーを追加 mysql> insert into <tablename> values (value1, 2, 3); ※数字はそのまま、dateやdatetime, timestampは記号なしで数字を書き続ける（201412290101のように）。文字列は’シングルクォート’する

UPDATE: 既存エントリーを更新する > update <table> set <field1>=‘hogehoge' [where …];

DELETE: エントリーを削除する > delete from <table> [where …];

TRUNCATE: テーブルのinitialize? > truncate table <tablename>; 全部のエントリーを消すだけなら delete from <table> も同じことができるがその場合はエントリーを1つ1つ消していくことになり、 truncateを使うと一度テーブルが消されて再作成される。auto_incrementがリセットされる。また、それが親tableである場合、子tableへの影響が異なる。詳細は http://www.dbonline.jp/mysql/insert/index12.html

ALTER文 - カラムの並び替え

alter table テーブル名 modify 順番を変えたいカラム名　データ型 after 既存のカラム名;
参考: http://mysql.akarukutanoshiku.com/category5/entry27.html

例

テーブル名の変更:

alter table <current tablename> rename as <new tablename>;

カラム名の変更:

alter table <tablename> change column <current columnname> <new columnname> <type> <condition>;

演算子 WHERE句: 特定のフィールドの値で検索 mysql> select * from my_contacts where first_name = 'nakamura';

LIKE句: 文字列の部分一致 ... where name LIKE '%org%';

WHERE + BETWEEN句: 指定フィールドが2つの値の間に収まるエントリーにマッチング

  ... where weight BETWEEN 30 AND 60;
  ... where date between '2013-01-01' AND '2014-12-31';

WHERE + IN: いずれかにマッチする ... where weight IN ('great','good','lovely');

NULLにマッチングさせる mysql> select * from my_contacts where status is NULL;

REGEXP: 正規表現でマッチング

  ... WHERE col_name REGEXP pattern
  ... WHERE col_name NOT REGEXP pattern
  ... WHERE col_name REGEXP BINARY pattern (大文字小文字区別したマッチング)

LIMIT: 表示する個数を絞る ... limit 99,20 (100番目から20個だけ表示)

ORDER BY column1 [, column2, …] [ASC|DESC]: ソートする[昇順|降順]

集計: [例] valueカラムの値の絶対値を取得 > SELECT ABS(value) FROM valuelist; あとはsumとかavgとか
GROUP BY: 集計の時に使います。 select sum(revenue) from revenuetable group by location ってすると、locationごとのrevenue合計値をゲットできるぜ
WITH ROLLUP (GROUP BY modifier): http://dev.mysql.com/doc/refman/5.0/en/group-by-modifiers.html
- 例えばグループごとの小計があるじゃん、その末尾に合計を出してくれるんさ。すげー。
- さらに、合計の時、fieldnameはNULLになってしまって気持ち悪い。そんな時には SELECT IFNULL(year,“Total”) as year, SUM(profit) FROM sales GROUP BY year WITH ROLLUP; ->とすればこうなる:

+-------+-------------+
| year  | SUM(profit) |
+-------+-------------+
|  2000 |        4525 |
|  2001 |        3010 |
| Total |        7535 |
+-------+-------------+

JOIN

※whereはjoinの後に来る。というか select … from table inner join … という、from tableの直後に続くのが決まりとなっているから。

INNER JOIN (内部結合): ON条件に一致するデータを結合して表示

> SELECT table_name.col_name, ... FROM tbl_name1
  INNER JOIN tbl_name2 ON table_name1.col_name1 = table_name2.col_name2;

例:

mysql> select customer_name, contract_bw_commijt from contract inner join customer ON contract.gn_id = customer.gn_id;
+---------------+--------------------+
| customer_name | contract_bw_commit |
+---------------+--------------------+
| custA         |          500000000 |
| custA         |         1000000000 |
+---------------+--------------------+

LEFT JOIN (RIGHT JOIN): 一致しないデータでも表示する。 left joinはfromで指定されたテーブルにしかないデータを表示

mysql> select customer_name, contract_bw_commit from customer left join contract ON contract.gn_id = customer.gn_id;
+---------------+--------------------+
| customer_name | contract_bw_commit |
+---------------+--------------------+
| custA         |          500000000 |
| custA         |         1000000000 |
| custB         |               NULL |
+---------------+--------------------+

columnの指定方法: . (ただしtable間でuniqueなcolumnであればfieldnameだけでも大丈夫)
ONとUSING: ONを使えば異なるcolumn名同士を比較できる -> … ON table1.column1 = table2. column5, table1.column2 = table2.column3, … USINGは比較対象が同じcolumn名であるときに使える。（記述が簡潔になって好き） -> … USING column1, column2, …

mysql> select customer_name, contract_bw_commit from customer left join contract using(gn_id);
+---------------+--------------------+
| customer_name | contract_bw_commit |
+---------------+--------------------+
| custA         |          500000000 |
| custA         |         1000000000 |
| custB         |               NULL |
+---------------+--------------------+

NATURAL JOIN: 2つのテーブルで同じカラム名が含まれる場合、自動的にそのカラムを使って結合する。ただし意図せず同じ名前のカラムがあるときなどは注意。

mysql> select customer_name, contract_bw_commit from customer natural left join contract;
+---------------+--------------------+
| customer_name | contract_bw_commit |
+---------------+--------------------+
| custA         |          500000000 |
| custA         |         1000000000 |
| custB         |               NULL |
+---------------+--------------------+

自己結合: 内部結合／外部結合の際に同じテーブル間で比較／結合する。テーブルにaliasをつけてうまく区別するのが特徴

> SELECT alias1.col_name, ... FROM tbl_name1 AS alias1
  INNER JOIN tbl_name2 AS alias2
  ON alias1.col_name1 = alias2.col_name2;

UNION 複数のSELECT文によってデータをそれぞれ取得し、その結果を結合した上で1つのデータとして取得する場合に使います。あんま使わんかなー

サブクエリ

サブクエリとはSELECT文で取得した結果を他のSELECT文やUPDATE文の中で利用する使い方です。主にはWERE句の中で使用されることが多いのですがそれには限定されません。またサブクエリを利用できる文はSELECT文、INSERT文、UPDATE文、DELETE文、SET文、DO文のいずれかだけです。

使い方: 括弧()で囲んでその中にselect文を書く。

> SELECT col_name1 FROM tbl_name1
  WHERE col_name1 = (SELECT col_name2 FROM tbl_name2);

FROMの直後で使われるサブクエリを「インラインビュー」と呼んだりするらしい（oracle方面）

データ移行

エクスポート

$ mysqldump -u <username> -p -r <output_dumpfile> --single-transaction <dbname>
  Enter password: <password>を入力

インポート

$ mysql -u <username> -p <dest_database> < <input_dumpfile>
Enter password: <password>を入力

import csv

LOAD DATA LOCAL INFILE
  "/tmp/users.csv"
INTO TABLE
  new_users
FIELDS
  TERMINATED BY ','
  ENCLOSED BY '"'
  ESCAPED BY '\\'
LINES
  TERMINATED BY '\r\n'
;

その他

複数行の結果を1行で表現:

select GROUP_CONCAT(name SEPARATOR ','), GROUP_CONCAT(full_name SEPARATOR ',') from countries;
+----------------------------+------------------------------------------------------------------------------+
| GROUP_CONCAT(name)         | GROUP_CONCAT(full_name)                                                      |
+----------------------------+------------------------------------------------------------------------------+
| SG,HK,TH,JP,MY,ID,TW,KR,AU | Singapore,Hong Kong,Thailand,Japan,Malaysia,Indonesia,Taiwan,Korea,Australia |
+----------------------------+------------------------------------------------------------------------------+

わざわざ書いてるけど、separatorは何も書かなければデフォルトでカンマ(,)です。