MariaDB 接続方法
local@raspberrypi:~ $ sudo mysql -u <user_name> -p
Databaseの確認 + 使用
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| Database_name |
+--------------------+
Databaseの使用
MariaDB [(none)]> USE <Database_name>;
登録済みテーブルの確認
MariaDB [db]> SHOW TABLES;
+----------------------+
| Tables_in_db |
+----------------------+
| users |
+----------------------+
1 row in set (0.001 sec)
テーブルの追加
MariaDB [db]> CREATE TABLE users (
-> id INT AUTO_INCREMENT PRIMARY KEY,
-> name VARCHAR(100) NOT NULL,
-> email VARCHAR(100) NOT NULL UNIQUE
-> );
Query OK, 0 rows affected (0.040 sec)
テーブルの削除
Mariadb [db]> DROP TABLE <Table_name>;
Query OK, 0 rows affected (0.024sec)
INSERT
MariaDB [db]> INSERT INTO users (name, email)
-> VALUES('John Doe', 'john.doe@example.com');
Query OK, 1 row affected (0.007 sec)
READ 1
MariaDB [db]> SELECT * FROM users;
+----+----------+----------------------+
| id | name | email |
+----+----------+----------------------+
| 1 | John Doe | john.doe@example.com |
| 2 | Kate | kate@example.com |
+----+----------+----------------------+
2 rows in set (0.001 sec)
READ 2 条件付き
MariaDB [db]> SELECT * FROM users WHERE name = 'John Doe';
+----+----------+----------------------+
| id | name | email |
+----+----------+----------------------+
| 1 | John Doe | john.doe@example.com |
+----+----------+----------------------+
1 row in set (0.001 sec)
UPDATE
MariaDB [db]> UPDATE users
-> SET email = 'kate_02@example.com'
-> WHERE name = 'Kate';
Query OK, 1 row affected (0.010 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [db]> SELECT * FROM users;
+----+----------+----------------------+
| id | name | email |
+----+----------+----------------------+
| 1 | John Doe | john.doe@example.com |
| 2 | Kate | kate_02@example.com |
+----+----------+----------------------+
2 rows in set (0.001 sec)
DELETE
MariaDB [db]> DELETE FROM users
-> WHERE name = 'Kate';
Query OK, 1 row affected (0.007 sec)
MariaDB [db]> SELECT * FROM users;
+----+----------+----------------------+
| id | name | email |
+----+----------+----------------------+
| 1 | John Doe | john.doe@example.com |
+----+----------+----------------------+
1 row in set (0.001 sec)
MariaDB [mydatabase]>
テーブル構造の確認
MariaDB [db]> DESCRIBE users;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(100) | NO | | NULL | |
| email | varchar(100) | NO | UNI | NULL | |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.003 sec)