MySqlを使ってみる
DB確認
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | db1 | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec)
DB作成
mysql> CREATE DATABASE db2;
DB削除
mysql> DROP DATABASE db2;
使用DB決定
mysql> USE db1;
使用中DB確認
mysql> SELECT DATABASE();
TABLE作成
mysql> CREATE TABLE tb1(bang VARCHAR(10),nama VARCHAR(10),tosi INT);
TABLE確認
mysql> SHOW TABLES; +---------------+ | Tables_in_db1 | +---------------+ | tb1 | +---------------+ 1 row in set (0.01 sec)
TABLE定義確認
mysql> DESC tb1; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | bang | varchar(10) | YES | | NULL | | | nama | varchar(10) | YES | | NULL | | | tosi | int(11) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec)
TABLE定義変更
# カラム追加 mysql> ALTER TABLE tbl1C ADD umare DATETIME # カラム定義変更 mysql> ALTER TABLE tb1C MODIFY umare DATETIME FIRST # カラム変更 mysql> ALTER TABLE tb1C CHANGE umare seinen DATE # カラム削除 mysql> ALTER TABLE tb1C DROP seinen;
TABLE コピー
mysql> CREATE TABLE tb1D SELECT * FROM tb1;
INSERT
mysql> INSERT INTO tb1 (bang,nama,tosi) VALUES ("A101","佐藤",40),("A102","高橋",28)
SELECT
# カラム名指定 mysql> SELECT bang,nama,tosi FROM tb1 +------+--------+------+ | bang | nama | tosi | +------+--------+------+ | A101 | 佐藤 | 40 | | A102 | 高橋 | 28 | | A103 | 中川 | 20 | | A104 | 渡辺 | 23 | | A105 | 西沢 | 35 | +------+--------+------+ 5 rows in set (0.00 sec) # エイリアス mysql> SELECT bang AS "社員番号" FROM tb1; +--------------+ | 社員番号 | +--------------+ | A101 | | A102 | | A103 | | A104 | | A105 | +--------------+ # 制限 mysql> SELECT bang FROM tb LIMIT 3; +------+ | bang | +------+ | A101 | | A102 | | A103 | +------+ 3 rows in set (0.00 sec) # 文字列結合 mysql> SELECT CONCAT(bang,nama,"さん" )AS "sales" FROM tb1; +------------------+ | sales | +------------------+ | A101佐藤さん | | A102高橋さん | | A103中川さん | | A104渡辺さん | | A105西沢さん | +------------------+ 5 rows in set (0.00 sec) # 平均 mysql> SELECT AVG(uria) FROM tb; +-----------+ | AVG(uria) | +-----------+ | 123.4000 | +-----------+ 1 row in set (0.00 sec) # 合計 mysql> SELECT SUM(uria) FROM tb; +-----------+ | SUM(uria) | +-----------+ | 1234 | +-----------+ 1 row in set (0.00 sec) # 数 mysql> SELECT COUNT(uria) FROM tb; +-------------+ | COUNT(uria) | +-------------+ | 10 | +-------------+ 1 row in set (0.00 sec) # 集計 mysql> SELECT bang,COUNT(bang) FROM tb GROUP BY bang; +------+-------------+ | bang | COUNT(bang) | +------+-------------+ | A101 | 2 | | A102 | 2 | | A103 | 3 | | A104 | 2 | | A107 | 1 | +------+-------------+ 5 rows in set (0.00 sec) # 並び替え mysql> SELECT * FROM tb WHERE uria > 100 AND uria <250 ORDER BY uria ; # 結合 mysql> SELECT * FROM tb1 UNION SELECT * FROM tb2; +------+--------+------+ | bang | nama | tosi | +------+--------+------+ | A101 | 佐藤 | 40 | | A102 | 高橋 | 28 | | A103 | 中川 | 20 | | A104 | 渡辺 | 23 | | A105 | 西沢 | 35 | | A106 | 中村 | 26 | | A107 | 田中 | 24 | | A108 | 鈴木 | 23 | | A109 | 村井 | 25 | | A110 | 吉田 | 27 | +------+--------+------+ 10 rows in set (0.00 sec) # 内部結合 mysql> SELECT * FROM tb JOIN tb1 ON tb.bang=tb1.bang ; +------+------+------+------+--------+------+ | bang | uria | tuki | bang | nama | tosi | +------+------+------+------+--------+------+ | A103 | 101 | 4 | A103 | 中川 | 20 | | A102 | 54 | 5 | A102 | 高橋 | 28 | | A104 | 181 | 4 | A104 | 渡辺 | 23 | | A101 | 184 | 4 | A101 | 佐藤 | 40 | | A103 | 17 | 5 | A103 | 中川 | 20 | | A101 | 300 | 5 | A101 | 佐藤 | 40 | | A102 | 205 | 6 | A102 | 高橋 | 28 | | A104 | 93 | 5 | A104 | 渡辺 | 23 | | A103 | 12 | 6 | A103 | 中川 | 20 | +------+------+------+------+--------+------+ 9 rows in set (0.00 sec)
DELETE
mysql> DELETE FROM tb WHERE bang="A10";
UPDATE
mysql> SELECT * FROM mst_staff; +------+--------------------+----------------------------------+ | code | name | password | +------+--------------------+----------------------------------+ | 2 | 山田太郎 | tarotaro | | 3 | ラーメン二郎 | jirojiro | | 4 | 風の又三郎 | f96ebc648d37606e25bda1801fd4d1c9 | | 5 | 拳四郎 | 0000 | +------+--------------------+----------------------------------+ UPDATE mst_staff SET name="ケンシロウ" ,password="XXXX" WHERE code=5; mysql> SELECT * FROM mst_staff; +------+--------------------+----------------------------------+ | code | name | password | +------+--------------------+----------------------------------+ | 2 | 山田太郎 | tarotaro | | 3 | ラーメン二郎 | jirojiro | | 4 | 風の又三郎 | f96ebc648d37606e25bda1801fd4d1c9 | | 5 | ケンシロウ | XXXX | +------+--------------------+----------------------------------+
INDEX 作成
mysql> CREATE INDEX my_ind ON tb1G(bang);
mysql> SHOW INDEX FROM tb1G \G;
*************************** 1. row ***************************
Table: tb1G
Non_unique: 1
Key_name: my_ind
Seq_in_index: 1
Column_name: bang
Collation: A
Cardinality: 5
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
1 row in set (0.00 sec)
ERROR:
No query specified
VIEW
mysql> CREATE VIEW V1 AS SELECT nama,tosi FROM tb1; mysql> SELECT * FROM V1;
ストアドプロシージャ
mysql> delimiter //
mysql> CREATE PROCEDURE pr1()
-> BEGIN
-> SELECT * FROM tb;
-> SELECT * FROM tb1;
-> END
-> //
Query OK, 0 rows affected (0.01 sec)
mysql> CALL pr1
mysql> SHOW CREATE PROCEDURE pr1
情報確認
# ユーザー確認 mysql> SELECT USER(); +----------------+ | USER() | +----------------+ | root@localhost | +----------------+ 1 row in set (0.00 sec) # バージョン確認 mysql> SELECT VERSION(); +-------------------------+ | VERSION() | +-------------------------+ | 5.7.23-0ubuntu0.16.04.1 | +-------------------------+ 1 row in set (0.00 sec)
参考書籍
基礎からのMySQL
