リレーショナル・データベース・マネジメント・システム（RDBMS）の人気ランキングは、Oracle、MYSQL、SQLServerがトップ３です。OracleとSQLServerは、有料ですが、MYSQLはオープンソースです。（ただし、営利目的のサービスや製品で使う場合、有償となります）
そのため、身近にあるデータベースと言えば、MYSQLが鉄板といってもいいでしょう。
MYSQLを操作するには、いわずもがなSQL構文を使う必要があります。
SQL構文ではなくても、MYSQLの各種コマンドを駆使し、データを管理することもあります。
そんなよく使うMYSQLのSQL構文と、いくつかのコマンドを集めました。
また、プログラミング可能なSQL構文は、あわせてPythonのコードも載せています。
● ユーザの一覧
● ユーザ作成
● ユーザ削除
● データベース作成
● データベースへのユーザ権限付与
● データベースの削除
● データベースの一覧
● データベースの切替
● テーブル作成
● テーブル削除
● テーブル一覧
● テーブル構造
● レコード登録
● レコード更新
● レコード削除
● レコード検索
● レコード検索（文字列の前方一致・中間一致・後方一致）
● レコードソート
● レコード集計
● テーブル結合（内部結合）
● テーブル結合（外部結合）
● 副問合せ（FROM句）
● 副問合せ（WHERE句）
● インデックス作成
● インデックス削除
● インデックス表示
● SQLファイル実行
● レコードインポート
● データベースバックアップ
● データベースリストア
■ Python動作環境
Python動作環境構築のため、以下から Anaconda 3のLinux用インストーラをダウンロードしてインストールします。
https://www.anaconda.com/products/distribution#Downloads
その後、PythonからMySQLと接続するためのモジュールをインストールします。
以下をUbuntuのターミナルから実行します。
> sudo ./pip3 install mysql-connector-python
※ 類似のものに「mysql-connector」がありますが、 varcharがすべてbytearrayで返却されるので使えません。
■ MYSQL動作環境
動作環境は、Ubuntu（20.04）上で動作するMYSQLバージョン「8.0」を使います。
ツールは、MYSQLの「monitor」を使います。
monitorの起動は、ターミナルを起動し、次のコマンドを入力し、rootでログインします。
> mysql -u test -p
Enter password:<パスワード>
・PythonによるMYSQLへの接続方法
今回、プログラムから接続するユーザも、rootを使用します。
■ よく使うSQL構文＆コマンドベスト３０
よく使うSQL文＆コマンドを３０個について、使用例を記載します。
MYSQLに登録されたユーザのリストを取得します
mysql> SELECT host, user FROM mysql.user;
+-----------+------------------+
| host | user |
+-----------+------------------+
| % | test |
| % | test@localhost |
| localhost | mysql.infoschema |
| localhost | mysql.session |
| localhost | mysql.sys |
| localhost | root |
| localhost | test |
+-----------+------------------+
7 rows in set (0.09 sec)
MYSQLに新しいユーザを作成します。
mysql> CREATE USER 'user001'@'localhost' IDENTIFIED BY 'user001password';
Query OK, 0 rows affected (0.28 sec)
MYSQLのユーザを削除します。
mysql> DROP USER 'user001'@'localhost';
Query OK, 0 rows affected (0.03 sec)
新しいデータベースを作成します。
mysql> CREATE DATABASE mysqltest_db;
Query OK, 1 row affected (0.00 sec)
データベース（testdb）にユーザの権限（全テーブルへの全権限）を付与します。
mysql> GRANT ALL PRIVILEGES ON mysqltest_db.* TO 'user001'@'localhost';
Query OK, 0 rows affected (0.01 sec)
データベースを削除します。
mysql> DROP DATABASE mysqltest_db;
Query OK, 0 rows affected (0.01 sec)
すべてのデータベースのリストを表示します。
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| icebreakaltdb |
| icebreakdb |
| information_schema |
| iotdb |
| leandb |
| mysql |
| performance_schema |
| readb |
| reaserver_testdb |
| sakila |
| shortcodedb |
| sys |
| testdb |
| world |
+--------------------+
14 rows in set (0.01 sec)
mysql> use mysqltest_db;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
※SQLではなく、MYSQL接続時にデータベースを指定します。
データベース内にテーブルを作成します。
mysql> CREATE TABLE mysqltest_db.test_tbl (
-> id int,
-> name varchar(128)
-> );
Query OK, 0 rows affected (0.76 sec)
データベース内のテーブルを削除します。
mysql> DROP TABLE mysqltest_db.test_tbl;
Query OK, 0 rows affected (0.25 sec)
データベース内のテーブルのリストを表示します。
mysql> SHOW TABLES FROM mysqltest_db;
+------------------------+
| Tables_in_mysqltest_db |
+------------------------+
| test_tbl |
+------------------------+
1 row in set (0.05 sec)
データベース内のテーブルのカラム（列）を表示します。
mysql> SHOW COLUMNS FROM test_tbl;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(128) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.02 sec)
データベース内のテーブルにレコード（行）を登録します。
mysql> INSERT INTO test_tbl SET id = 1,name='test';
Query OK, 1 row affected (0.04 sec)
データベース内のテーブルにレコード（行）を更新します。
mysql> UPDATE test_tbl SET id=2,name='testtest' WHERE id=1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 2 Changed: 0 Warnings: 0
データベース内のテーブルのレコード（行）を削除します。
mysql> DELETE FROM test_tbl WHERE id=2;
Query OK, 2 rows affected (0.03 sec)
データベース内のテーブルのレコード（行）を検索します。
mysql> SELECT * FROM test_tbl WHERE id=1;
+------+------+
| id | name |
+------+------+
| 1 | test |
+------+------+
1 row in set (0.00 sec)
文字列一致のためのワイルドカードは「％」です。LIKE文によって指定します。
前方一致「％st1」、中間一致「%st%」、後方一致「te%」となります。
mysql> SELECT * FROM test_tbl WHERE name LIKE '%st%';
+------+-------+
| id | name |
+------+-------+
| 1 | test1 |
| 2 | test2 |
| 3 | test3 |
+------+-------+
3 rows in set (0.00 sec)
データベース内のテーブルのレコード（行）を検索し、ソートします。
mysql> SELECT * FROM test_tbl ORDER BY id;
+------+-------+
| id | name |
+------+-------+
| 1 | test1 |
| 2 | test2 |
| 3 | test3 |
+------+-------+
3 rows in set (0.01 sec)
データベース内のテーブルのレコード（行）を検索し、集計します。
カラムに指定できる集計関数には、件数：count()、合計：sum()、平均：avg()、最大：max()、最小：min()があります。
mysql> SELECT id,count(id) FROM test_tbl GROUP BY id;
+------+-----------+
| id | count(id) |
+------+-----------+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
+------+-----------+
3 rows in set (0.00 sec)
データベース内のテーブルを結合します。
ただし、結合する各テーブルのカラムに、同じ値が存在する場合のみ結合します。
mysql> SELECT * FROM test01_tbl AS t1 INNER JOIN test02_tbl AS t2 ON t1.id = t2.id;
+------+--------+------+--------+
| id | name01 | id | name02 |
+------+--------+------+--------+
| 1 | a | 1 | x |
| 2 | b | 2 | y |
| 3 | c | 3 | z |
+------+--------+------+--------+
3 rows in set (1.33 sec)
データベース内のテーブルを結合します。
ただし、結合する各テーブルのカラムに、同じ値が存在しない場合でも結合します。
mysql> SELECT * FROM test01_tbl AS t1 LEFT OUTER JOIN test02_tbl AS t2 ON t1.id
= t2.id;
+------+--------+------+--------+
| id | name01 | id | name02 |
+------+--------+------+--------+
| 1 | a | 1 | x |
| 2 | b | 2 | y |
| 3 | c | 3 | z |
+------+--------+------+--------+
3 rows in set (0.01 sec)
データベース内のテーブルを検索する際、最初に検索した結果をテーブルとして見立て、再度、検索します。
mysql> SELECT * FROM ( select * from test01_tbl where id > 0) as t1 WHERE t1.nam
e01 = 'c';
+------+--------+
| id | name01 |
+------+--------+
| 3 | c |
+------+--------+
1 row in set (0.00 sec)
データベース内のテーブルを検索条件に、他テーブルで検索した結果の値を設定し、再度、検索します。
mysql> SELECT * FROM test01_tbl WHERE id IN (select id from test02_tbl where name02 = 'x');
+------+--------+
| id | name01 |
+------+--------+
| 1 | a |
+------+--------+
1 row in set (0.00 sec)
検索、集計、結合の処理速度を上げるために、インデックスを使います。
インデックスは、テーブル単位でインデックス化するカラムを指定して、作成します。
（インデックス作成時に、すべての既存レコードのインデックスが作成され、以降、INSERT時にインデクスも自動で作成されます)
インデクス化するカラムを選択する基準は、検索条件のカラム、集計のカラム、結合条件の各テーブルのカラムです。
mysql> CREATE INDEX myindex ON test01_tbl(id);
Query OK, 0 rows affected (0.16 sec)
Records: 0 Duplicates: 0 Warnings: 0
テーブルのインデックスを削除します。
mysql> DROP INDEX myindex ON test01_tbl;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
データベース内のテーブルで作成されたインデックスを表示します。
mysql> SELECT * FROM information_schema.statistics WHERE table_schema = 'mysqltest_db';
+---------------+--------------+------------+------------+--------------+------------+--------------+-------------+-----------+-------------+----------+--------+----------+------------+---------+---------------+------------+------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | NON_UNIQUE | INDEX_SCHEMA | INDEX_NAME | SEQ_IN_INDEX | COLUMN_NAME | COLLATION | CARDINALITY | SUB_PART | PACKED | NULLABLE | INDEX_TYPE | COMMENT | INDEX_COMMENT | IS_VISIBLE | EXPRESSION |
+---------------+--------------+------------+------------+--------------+------------+--------------+-------------+-----------+-------------+----------+--------+----------+------------+---------+---------------+------------+------------+
| def | mysqltest_db | test01_tbl | 1 | mysqltest_db | myindex | 1 | id | A | 2 | NULL | NULL | YES | BTREE | | | YES | NULL |
+---------------+--------------+------------+------------+--------------+------------+--------------+-------------+-----------+-------------+----------+--------+----------+------------+---------+---------------+------------+------------+
1 row in set (0.00 sec)
SQLを記載したファイルを実行します。
mysql> source /home/swata/python/python_win/Python/Python/SQL_blog/SQL_file.txt
Query OK, 0 rows affected (0.03 sec)
Query OK, 0 rows affected (0.03 sec)
Query OK, 1 row affected (0.02 sec)※SQL_file.txt
テーブルにレコードをインポートします。
まず、MYSQLのmonitorからローカルファイルのインポートをONに設定します。
mysql> SET GLOBAL local_infile=on;
Query OK, 0 rows affected (0.01 sec)
その後、ubuntuのターミナルから「テーブル名.csv」のファイルをインポートします。
（*****は、MYSQLのrootパスワード）
(ubuntu)$ mysqlimport -u root -p***** --local mysqltest_db --fields-terminated-by="," --lines-terminated-by="\n" --fields-enclosed-by="\"" --ignore-lines=1 "test_tbl.csv"
mysqlimport: [Warning] Using a password on the command line interface can be insecure.
mysqltest_db.test_tbl: Records: 3 Deleted: 0 Skipped: 0 Warnings: 0
ubuntuのターミナルからデータベースをバックアップします。
(ubuntu)$ mysqldump -u root -p mysqltest_db > mysqltest_db.dump
Enter password:
ubuntuのターミナルからデータベースをリストアします。
(ubuntu)$ mysql -u root -p mysqltest_db < mysqltest_db.dump
Enter password: ■ まとめ
SQL文の組み合わせを考えることは、プログラミングと同様、とても頭を使う作業です。
想定した結果が得られることをMYSQLのmonitorで事前に動作確認してから、コードに埋め込んでいきます。
データが増えて来ると、SQL文の書き方の違いで処理速度が大きく違ったりします。
データベースのテーブル構造をよく分析し、効率の良いSQL文を考える必要があります。
ソフトウェア開発・システム開発業務/セキュリティ関連業務/ネットワーク関連業務/最新技術に関する業務など、「学習力×発想力×達成力×熱意」で技術開発の実現をサポート。お気軽にお問合せ下さい
Yesterday, while I was at work, my cousin stole my iphone and tested to see if it can survive a 40 foot drop,
just so she can be a youtube sensation. My apple ipad is now
destroyed and she has 83 views. I know this is completely off topic
but I had to share it with someone!
We’re a bunch of volunteers and starting a brand new scheme in our
community. Your web site offered us with useful info to work
on. You have performed an impressive process and our
entire group will be thankful to you.
I am not positive where you are getting your info, but great topic.
I must spend some time finding out more or understanding more.
Thanks for excellent information I used to be in search of this info for
my mission.
Hi there this is somewhat of off topic but I was wondering if blogs
use WYSIWYG editors or if you have to manually code with HTML.
I’m starting a blog soon but have no coding experience so I wanted to get
advice from someone with experience. Any help would be enormously appreciated!