カテゴリー: SQL

Software Design SQL50本ノックやってみた。

投稿日: 2017年12月11日投稿者: 優

環境構築編購入の価値ありです。 SQL1. SELECT

postgres=# SELECT * FROM payment;

 payment_id | customer_id | staff_id | rental_id | amount |        payment_date
------------+-------------+----------+-----------+--------+----------------------------
      16050 |         269 |        2 |         7 |   1.99 | 2007-01-24 21:40:19.996577
      16051 |         269 |        1 |        98 |   0.99 | 2007-01-25 15:16:50.996577
      16052 |         269 |        2 |       678 |   6.99 | 2007-01-28 21:44:14.996577
      16053 |         269 |        2 |       703 |   0.99 | 2007-01-29 00:58:02.996577
      16054 |         269 |        1 |       750 |   4.99 | 2007-01-29 08:10:06.996577
      16055 |         269 |        2 |      1099 |   2.99 | 2007-01-31 12:23:14.996577
      16056 |         270 |        1 |       193 |   1.99 | 2007-01-26 05:10:14.996577
      16057 |         270 |        1 |      1040 |   4.99 | 2007-01-31 04:03:42.996577
      16058 |         271 |        1 |      1096 |   8.99 | 2007-01-31 11:59:15.996577
      16059 |         272 |        1 |        33 |   0.99 | 2007-01-25 02:47:17.996577


(略)




postgres=# SELECT * FROM customer;

 customer_id | store_id | first_name  |  last_name   |                  email                   | address_id | activebool | create_date |     last_update     | active
-------------+----------+-------------+--------------+------------------------------------------+------------+------------+-------------+---------------------+--------
           1 |        1 | MARY        | SMITH        | MARY.SMITH@sakilacustomer.org            |          5 | t          | 2006-02-14  | 2006-02-15 09:57:20 |      1
           2 |        1 | PATRICIA    | JOHNSON      | PATRICIA.JOHNSON@sakilacustomer.org      |          6 | t          | 2006-02-14  | 2006-02-15 09:57:20 |      1
           3 |        1 | LINDA       | WILLIAMS     | LINDA.WILLIAMS@sakilacustomer.org        |          7 | t          | 2006-02-14  | 2006-02-15 09:57:20 |      1
           4 |        2 | BARBARA     | JONES        | BARBARA.JONES@sakilacustomer.org         |          8 | t          | 2006-02-14  | 2006-02-15 09:57:20 |      1
           5 |        1 | ELIZABETH   | BROWN        | ELIZABETH.BROWN@sakilacustomer.org       |          9 | t          | 2006-02-14  | 2006-02-15 09:57:20 |      1
           6 |        2 | JENNIFER    | DAVIS        | JENNIFER.DAVIS@sakilacustomer.org        |         10 | t          | 2006-02-14  | 2006-02-15 09:57:20 |      1
           7 |        1 | MARIA       | MILLER       | MARIA.MILLER@sakilacustomer.org          |         11 | t          | 2006-02-14  | 2006-02-15 09:57:20 |      1
           8 |        2 | SUSAN       | WILSON       | SUSAN.WILSON@sakilacustomer.org          |         12 | t          | 2006-02-14  | 2006-02-15 09:57:20 |      1
           9 |        2 | MARGARET    | MOORE        | MARGARET.MOORE@sakilacustomer.org        |         13 | t          | 2006-02-14  | 2006-02-15 09:57:20 |      1
          10 |        1 | DOROTHY     | TAYLOR       | DOROTHY.TAYLOR@sakilacustomer.org        |         14 | t          | 2006-02-14  | 2006-02-15 09:57:20 |      1

(略)

postgres=# SELECT * FROM payment;

------------+-------------+----------+-----------+--------+----------------------------

16050 | 269 | 2 | 7 | 1.99 | 2007-01-24 21:40:19.996577

16051 | 269 | 1 | 98 | 0.99 | 2007-01-25 15:16:50.996577

16052 | 269 | 2 | 678 | 6.99 | 2007-01-28 21:44:14.996577

16053 | 269 | 2 | 703 | 0.99 | 2007-01-29 00:58:02.996577

16054 | 269 | 1 | 750 | 4.99 | 2007-01-29 08:10:06.996577

16055 | 269 | 2 | 1099 | 2.99 | 2007-01-31 12:23:14.996577

16056 | 270 | 1 | 193 | 1.99 | 2007-01-26 05:10:14.996577

16057 | 270 | 1 | 1040 | 4.99 | 2007-01-31 04:03:42.996577

16058 | 271 | 1 | 1096 | 8.99 | 2007-01-31 11:59:15.996577

16059 | 272 | 1 | 33 | 0.99 | 2007-01-25 02:47:17.996577

(略)

postgres=# SELECT * FROM customer;

-------------+----------+-------------+--------------+------------------------------------------+------------+------------+-------------+---------------------+--------

1 | 1 | MARY | SMITH | MARY.SMITH@sakilacustomer.org | 5 | t | 2006-02-14 | 2006-02-15 09:57:20 | 1

2 | 1 | PATRICIA | JOHNSON | PATRICIA.JOHNSON@sakilacustomer.org | 6 | t | 2006-02-14 | 2006-02-15 09:57:20 | 1

3 | 1 | LINDA | WILLIAMS | LINDA.WILLIAMS@sakilacustomer.org | 7 | t | 2006-02-14 | 2006-02-15 09:57:20 | 1

4 | 2 | BARBARA | JONES | BARBARA.JONES@sakilacustomer.org | 8 | t | 2006-02-14 | 2006-02-15 09:57:20 | 1

5 | 1 | ELIZABETH | BROWN | ELIZABETH.BROWN@sakilacustomer.org | 9 | t | 2006-02-14 | 2006-02-15 09:57:20 | 1

6 | 2 | JENNIFER | DAVIS | JENNIFER.DAVIS@sakilacustomer.org | 10 | t | 2006-02-14 | 2006-02-15 09:57:20 | 1

7 | 1 | MARIA | MILLER | MARIA.MILLER@sakilacustomer.org | 11 | t | 2006-02-14 | 2006-02-15 09:57:20 | 1

8 | 2 | SUSAN | WILSON | SUSAN.WILSON@sakilacustomer.org | 12 | t | 2006-02-14 | 2006-02-15 09:57:20 | 1

9 | 2 | MARGARET | MOORE | MARGARET.MOORE@sakilacustomer.org | 13 | t | 2006-02-14 | 2006-02-15 09:57:20 | 1

10 | 1 | DOROTHY | TAYLOR | DOROTHY.TAYLOR@sakilacustomer.org | 14 | t | 2006-02-14 | 2006-02-15 09:57:20 | 1

(略)

SQL2. 条件を指定す…

Read the full article

MySQL デッドロック対処

投稿日: 2017年12月4日2017年12月8日投稿者: 優

MySQLのデッドロック対処おまけでギャップロック寄稿しました。今回はトランザクションでよくありがちなデッドロックのご紹介。たすきがけのデッドロックよくデ…

Read the full article

Percona XtraBackup ロールフォワード

投稿日: 2017年12月2日2017年12月3日投稿者: 優

Percona XtraBackup mysqldumpリストア時間比較おまけでロールフォワード寄稿しました。どっちを使う？ XtraBackup レプリケーションが行われているシステム。 WEBサー…

Read the full article

SQLパフォーマンス要約なぜ、SQLは重たくなるのか？──『SQLパフォーマンス詳解』の翻訳者が教える原因と対策

投稿日: 2017年11月29日2017年12月2日投稿者: 優

自分用に要約メモ。詳しい記事は引用先をみてくださいな。 @see なぜ、SQLは重たくなるのか？ —『SQLパフォーマンス詳解』の翻訳者が教える原因と対策 ORMが自動生成するS…

Read the full article

MySQL 5.6 ファイルディスクリプタカーネルチューニング CentOS7

投稿日: 2017年10月26日投稿者: 優

MySQL5.6 ファイルディスクリプタチューニング CentOS7 [Warning] Buffered warning: Could not increase number of max_open_fi…

Read the full article

MySQL5.6.22以下注意 SET GLOBAL sql_log_bin

投稿日: 2017年10月25日投稿者: 優

MySQLのレプリケーション環境を運用していると、バイナリログへの記録を一時的に止めるためにsql_log_bin=0を実行したことがあるだろう。これを「set GLOBAL sql_log_bin=0」と…

Read the full article

SQL 置換 REPLACE

投稿日: 2017年10月21日投稿者: 優

値を置換します。

UPDATE テーブル名 SET カラム名 = REPLACE(カラム名, '値old', '値new');

1	UPDATE テーブル名 SET カラム名 = REPLACE(カラム名, '値old', '値new');

万単位のレコード置換は本当に気持ちが良いもの。このクエリ大好き。使う機会を震えて待ってます…

Read the full article

MySQL レプリケーション監視スクリプト

投稿日: 2017年9月1日2017年10月4日投稿者: 優

レプリケーション監視スクリプト replication_check.sh

#!/bin/sh

#PASSWORD="パスワードを入力"
EMAIL=hoge@example.net
DOMAIN=crient.com
CRIENT="もげ株式会社"

#eval  "`mysql -u root -p${PASSWORD} -e 'show slave status \G' | sed -ne 's/: \(.*\)/="\1"/p' `"
eval  "`mysql --defaults-extra-file=/root/.my.cnf -e 'show slave status \G' | sed -ne 's/: \(.*\)/="\1"/p' `"
if [ "$?${Slave_IO_Running}${Slave_SQL_Running}" != "0YesYes" ]; then
echo "show slave status\G" | mysql --defaults-extra-file=/root/.my.cnf | mail -s "レプリケーションエラー:${DOMAIN}:${CRIENT}:"`hostname` ${EMAIL}
exit 1
fi

#!/bin/sh

#PASSWORD="パスワードを入力"

EMAIL=hoge@example.net

DOMAIN=crient.com

CRIENT="もげ株式会社"

#eval "`mysql -u root -p${PASSWORD} -e 'show slave status \G' | sed -ne 's/: $.*$/="\1"/p' `"

eval "`mysql --defaults-extra-file=/root/.my.cnf -e 'show slave status \G' | sed -ne 's/: $.*$/="\1"/p' `"

if [ "$?${Slave_IO_Running}${Slave_SQL_Running}" != "0YesYes" ]; then

echo "show slave status\G" | mysql --defaults-extra-file=/root/.my.cnf | mail -s "レプリケーションエラー:${DOMAIN}:${CRIENT}:"`hostname` ${EMAIL}

exit 1

[crayon-5c14…

Read the full article

【Munin】 MySQL InnoDB free tablespace UNKNOWNs: Bytes free is unknown.

投稿日: 2017年9月1日2017年10月10日投稿者: 優

Munin-notification for net::example.net

net :: example.net :: MySQL InnoDB free tablespace
	UNKNOWNs: Bytes free is unknown.

Munin-notification for net::example.net

net :: example.net :: MySQL InnoDB free tablespace

UNKNOWNs: Bytes free is unknown.

# /etc/my.cnf

自動拡張のサイズ
innodb_autoextend_increment     = 64      ##InnoDBテーブルスペースファイル 自動拡大テーブルスペースがいっぱいになった時の 自動増分サイズ

# /etc/my.cnf

自動拡張のサイズ

innodb_autoextend_increment = 64 ##InnoDBテーブルスペースファイル自動拡大テーブルスペースがいっぱいになった時の自動増分サイズ

[crayon-5c14a95379e8e77…

Read the full article

MySQLダンプスクリプト

投稿日: 2017年9月1日2018年5月16日投稿者: 優

ログイン用ファイル作成 ※rootパスワード設定時

# vi /root/.my.cnf

[mysqladmin]
host = localhost
user = root
password = DBpassword

[client]
user = root
password = DBpassword
host = localhost

# vi /root/.my.cnf

[mysqladmin]

host = localhost

user = root

password = DBpassword

[client]

user = root

password = DBpassword

host = localhost

# chmod 400 /root/.my.cnf

# mysql --defaults-extra-file=/root/.my.cnf

# chmod 400 /root/.my.cnf

# mysql --defaults-extra-file=/root/.my.cnf

&nbs…

Read the full article

中間テーブル関連実体 intermediate table

投稿日: 2017年7月26日2018年7月26日投稿者: 優

1対1の関係１対多の関係 playerテーブルのサトシくんはポケモンをたくさん所有しています。多対多の表現プレイヤーとポケモンが互いにシェアされ…

Read the full article

MySQLのデータディレクトリを変更する

投稿日: 2017年7月19日2018年7月19日投稿者: 優

ディレクトリの作成

# mkdir -p /home/mysql
# chmod 755 /home/mysql
# chown mysql:mysql -R /home/mysql

# mkdir -p /home/mysql

# chmod 755 /home/mysql

# chown mysql:mysql -R /home/mysql

新しいディレクトリにデータを複製し、元を削除 [crayon-5c14a9537c35b20…

Read the full article

MySQLTuner CentOS7

投稿日: 2017年7月19日2018年7月19日投稿者: 優

MySQLTunerインストール

# wget -O /usr/local/src/MySQLTuner.zip https://github.com/rackerhacker/MySQLTuner-perl/archive/master.zip

# unzip /usr/local/src/MySQLTuner.zip -d /usr/local/src/

# rm -f /usr/local/src/MySQLTuner.zip

# wget -O /usr/local/src/MySQLTuner.zip https://github.com/rackerhacker/MySQLTuner-perl/archive/master.zip

# unzip /usr/local/src/MySQLTuner.zip -d /usr/local/src/

# rm -f /usr/local/src/MySQLTuner.zip

実行

# /usr/local/src/MySQLTuner-perl-master/mysqltuner.pl -user root



 >>  MySQLTuner 1.7.9 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering

[--] Skipped version check for MySQLTuner script
[!!] Attempted to use login credentials, but they were invalid
[root@localhost MySQLTuner-perl-master]# /usr/local/src/MySQLTuner-perl-master/mysqltuner.pl
 >>  MySQLTuner 1.7.9 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering

[--] Skipped version check for MySQLTuner script
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password:※パスワード入力



[OK] Currently running supported MySQL version 5.7.22-log
[OK] Operating on 64-bit architecture

(略)

# /usr/local/src/MySQLTuner-perl-master/mysqltuner.pl -user root

>> MySQLTuner 1.7.9 - Major Hayden <major@mhtx.net>

>> Bug reports, feature requests, and downloads at http://mysqltuner.com/

>> Run with '--help' for additional options and output filtering

[--] Skipped version check for MySQLTuner script

[!!] Attempted to use login credentials, but they were invalid

[root@localhost MySQLTuner-perl-master]# /usr/local/src/MySQLTuner-perl-master/mysqltuner.pl

>> MySQLTuner 1.7.9 - Major Hayden <major@mhtx.net>

>> Bug reports, feature requests, and downloads at http://mysqltuner.com/

>> Run with '--help' for additional options and output filtering

[--] Skipped version check for MySQLTuner script

Please enter your MySQL administrative login: root

Please enter your MySQL administrative password:※パスワード入力

[OK] Currently running supported MySQL version 5.7.22-log

[OK] Operating on 64-bit architecture

(略)

&nb…

Read the full article

SQL作成考え方

投稿日: 2017年7月14日2018年7月14日投稿者: 優

まずは表示させたい結果の列名を考える

if 関数SUM, COUNTは必要
　⇒列名に対してSUMやCOUNTを入れて AS

	SUMやCOUNT後に条件を加える必要がある？
	　⇒HAVING

if GROUP BYが必要？
　⇒GROUP BYを使う必要がある時はそれを基準に表示結果を考える

	GROUP BY 句の後に条件を加える必要がある？
	　⇒HAVING

if テーブル結合は必要？
　if NULLのあるレコードは表示させる？
　　YES⇒LEFT JOIN
　　NO⇒JOIN

if 条件　WHEREは必要？
　⇒ 入れる
　if 比較の値を得る為にサブクエリが必要？
　　⇒サブクエリ内容を考える

if 表示の行数、昇順降順？
　⇒ ORDER BY DESC/ASC
　
　	if 限度は必要？
　	　⇒ LIMIT

まずは表示させたい結果の列名を考える

if 関数SUM, COUNTは必要

　⇒列名に対してSUMやCOUNTを入れて AS

SUMやCOUNT後に条件を加える必要がある？

　⇒HAVING

if GROUP BYが必要？

　⇒GROUP BYを使う必要がある時はそれを基準に表示結果を考える

GROUP BY 句の後に条件を加える必要がある？

　⇒HAVING

if テーブル結合は必要？

　if NULLのあるレコードは表示させる？

　　YES⇒LEFT JOIN

　　NO⇒JOIN

if 条件　WHEREは必要？

　⇒ 入れる

　if 比較の値を得る為にサブクエリが必要？

　　⇒サブクエリ内容を考える

if 表示の行数、昇順降順？

　⇒ ORDER BY DESC/ASC

　 if 限度は必要？

　　⇒ LIMIT

SQLの実行順序

投稿日: 2017年7月13日2018年7月13日投稿者: 優

実行順序 1 検索条件 WHERE 2 グループ化 GROUP BY 3 関数 SUM, COUNT, AVG, MAX, MIN 4 HAVING 例 [crayon-5c14a9537d4…

Read the full article