Mysql備忘録

# SQLまとめ

```terminal:terminal
●mysqlを操作出来るディレクトリに移動(環境変数のpathを通している場合は不要)
cd C:\Program Files\MySQL\MySQL Server 8.0\bin

●ログイン
mysql -u ユーザー名 -p;
mysql -u root -p;

●DBの一覧確認
show databases;
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| localdb            |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| world              |
+--------------------+
7 rows in set (0.00 sec)

●データベース作成
create database 新規作成DB名;
mysql> create database test;
Query OK, 1 row affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| localdb            |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| test               |
| world              |
+--------------------+
8 rows in set (0.00 sec)

●DB接続
use 接続先DB名;

●テーブル作成
CREATE TABLE users (id INT AUTO_INCREMENT, name TEXT, PRIMARY KEY (id));

●テーブル構造確認
describe テーブル名;

describe users;
mysql> use test;
Database changed
mysql> CREATE TABLE users (id INT AUTO_INCREMENT, name TEXT, PRIMARY KEY (id));
Query OK, 0 rows affected (0.04 sec)

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| users          |
+----------------+
1 row in set (0.01 sec)

mysql> describe users;
+-------+------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra          |
+-------+------+------+-----+---------+----------------+
| id    | int  | NO   | PRI | NULL    | auto_increment |
| name  | text | YES  |     | NULL    |                |
+-------+------+------+-----+---------+----------------+
2 rows in set (0.02 sec)

●取得
SELECT * FROM users;
●登録
insert into users value(1,"佐藤");
insert into users value(2,"田中");
insert into users value(3,"渡辺");
insert into users value(4,"佐々木");
insert into users value(5,"鈴木");

mysql> SELECT * FROM users;
Empty set (0.02 sec)
mysql> insert into users value(1,"佐藤");insert into users value(2,"田中");insert into users value(3,"渡辺");insert into users value(4,"佐々木");insert into users value(5,"鈴木");
Query OK, 1 row affected (0.01 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.01 sec)
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM users;
+----+--------+
| id | name   |
+----+--------+
|  1 | 佐藤   |
|  2 | 田中   |
|  3 | 渡辺   |
|  4 | 佐々木 |
|  5 | 鈴木   |
+----+--------+
5 rows in set (0.02 sec)

●更新
update テーブル名 set カラム名　=　変更値　where 条件;

mysql> update users set name="大谷" where id = '1';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM users;
+----+--------+
| id | name   |
+----+--------+
|  1 | 大谷   |
|  2 | 田中   |
|  3 | 渡辺   |
|  4 | 佐々木 |
|  5 | 鈴木   |
+----+--------+
5 rows in set (0.00 sec)

●テーブル一覧
show tables;
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| users          |
+----------------+
1 row in set (0.00 sec)

●データベース削除
drop database 削除DB名;

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| localdb            |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| world              |
+--------------------+
7 rows in set (0.00 sec)

```
***
## 参考
https://qiita.com/hryshtk/items/dd69db351bb47f57b4e1


# テーブル削除についてのメモ
#### delete
・条件指定して削除したい時
・COMMITしていなければロールバック可能

#### truncate
・ロールバックが出来ない
・表構造は残る

####  drop
・表構造も残らない、完全削除
・ロールバックが出来ない

## 参考
https://qiita.com/masapiko/items/cd4aef791608ea919485