いよいよDB(Database) Defensive Backではない【Day 88 of 100】

root権限管理者だとすべてのデータベースが見れる

@kiyo4810 ➜ /workspaces/learn-mysql (main) $ sudo mysql -u root

rootユーザー（全権限者）でmysqlにログインする

mysql> create user dbuser02@localhost identified by '●●●●●';

●●●●●のパスワードでdbuser02@localhostというユーザーを作成する

mysql> grant all on db02.* to dbuser02@localhost;

dbuser02@localhost に db02への全権限を委譲する

あかん、、、、

過去一、、、

ただの写経感しかない、、、

まぁここはさらっと終わらせよう。深堀りするなら別のDB講座一つやり切るぐらい必要そうだ。Reactだってまぁまぁサンプル作ってようやくうっすら分かったわけだし、、、。

mysql> create table users (id int unsigned, name varchar(32), age int);

、、、、、、、、これこそハッカーしかわからんやつやん！今は分からん。分かるようになる、やればできる。

MySQLでは0とnullがfalseで空文字を含むそれ以外の値はすべてtrueとなる

重要そうだからこれは見出しにしておこう

			
mysql> create table users (id int unsigned auto_increment not null primary key, name varchar(32), age int not null);

Database tableを作る上記の呪文、、、（横幅余裕で収まっていない）。長い長い！

コマンドのメモ Select update delete

			
@kiyo4810 ➜ /workspaces/learn-mysql (main) $ cd mysql-projects/
@kiyo4810 ➜ /workspaces/learn-mysql/mysql-projects (main) $ sudo mysql -u root < initialize_lecture_select.sql 
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (111)
@kiyo4810 ➜ /workspaces/learn-mysql/mysql-projects (main) $ sudo service mysql start
 * Starting MySQL database server mysqld                                                    su: warning: cannot change directory to /nonexistent: No such file or directory
/opt/conda/bin/xz
^[[A^[[A                                                                             [ OK ]
@kiyo4810 ➜ /workspaces/learn-mysql/mysql-projects (main) $ sudo mysql -u root < initialize_lecture_select.sql 
@kiyo4810 ➜ /workspaces/learn-mysql/mysql-projects (main) $ sudo mysql -u mydbuser -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.45-0ubuntu0.24.04.1 (Ubuntu)
Copyright (c) 2000, 2026, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select * from users
    -> ;
ERROR 1046 (3D000): No database selected
mysql> use mydb;
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
mysql> select * from users;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  1 | sato      |   18 |
|  2 | suzuki    |   22 |
|  3 | takahashi |   29 |
|  4 | tanaka    |   30 |
|  5 | ito       |   19 |
|  6 | watanabe  |   20 |
|  7 | yamamoto  | NULL |
+----+-----------+------+
7 rows in set (0.00 sec)
mysql> select * from users where name like 's%';
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | sato   |   18 |
|  2 | suzuki |   22 |
+----+--------+------+
2 rows in set (0.00 sec)
mysql> select * from users where name like 'sa%';
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | sato |   18 |
+----+------+------+
1 row in set (0.00 sec)
mysql> select * from users where name like 'SA%';
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | sato |   18 |
+----+------+------+
1 row in set (0.00 sec)
mysql> select * from users where name like '%a%';
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  1 | sato      |   18 |
|  3 | takahashi |   29 |
|  4 | tanaka    |   30 |
|  6 | watanabe  |   20 |
|  7 | yamamoto  | NULL |
+----+-----------+------+
5 rows in set (0.00 sec)
mysql> select * from users where name like '%a';
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  4 | tanaka |   30 |
+----+--------+------+
1 row in set (0.00 sec)
mysql> select * from users where name like '%a%a';
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  4 | tanaka |   30 |
+----+--------+------+
1 row in set (0.00 sec)
mysql> select * from users where name like '%a%e';
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  6 | watanabe |   20 |
+----+----------+------+
1 row in set (0.00 sec)
mysql> select * from users where name like '%a%k';
Empty set (0.00 sec)
mysql> select * from users where name like '______';
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  2 | suzuki |   22 |
|  4 | tanaka |   30 |
+----+--------+------+
2 rows in set (0.00 sec)
mysql> select * from users where name like '_u_____';
Empty set (0.00 sec)
mysql> select * from users where name like '_u____';
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  2 | suzuki |   22 |
+----+--------+------+
1 row in set (0.00 sec)
mysql> select name as namae, age as nenrei from users;
+-----------+--------+
| namae     | nenrei |
+-----------+--------+
| sato      |     18 |
| suzuki    |     22 |
| takahashi |     29 |
| tanaka    |     30 |
| ito       |     19 |
| watanabe  |     20 |
| yamamoto  |   NULL |
+-----------+--------+
7 rows in set (0.00 sec)
mysql> select name namae, age nenrei from users;
+-----------+--------+
| namae     | nenrei |
+-----------+--------+
| sato      |     18 |
| suzuki    |     22 |
| takahashi |     29 |
| tanaka    |     30 |
| ito       |     19 |
| watanabe  |     20 |
| yamamoto  |   NULL |
+-----------+--------+
7 rows in set (0.00 sec)
mysql> select * from users;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  1 | sato      |   18 |
|  2 | suzuki    |   22 |
|  3 | takahashi |   29 |
|  4 | tanaka    |   30 |
|  5 | ito       |   19 |
|  6 | watanabe  |   20 |
|  7 | yamamoto  | NULL |
+----+-----------+------+
7 rows in set (0.00 sec)
mysql> select * from users;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  1 | sato      |   18 |
|  2 | suzuki    |   22 |
|  3 | takahashi |   29 |
|  4 | tanaka    |   30 |
|  5 | ito       |   19 |
|  6 | watanabe  |   20 |
|  7 | yamamoto  | NULL |
+----+-----------+------+
7 rows in set (0.00 sec)
mysql> select * from users order by age asc;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  7 | yamamoto  | NULL |
|  1 | sato      |   18 |
|  5 | ito       |   19 |
|  6 | watanabe  |   20 |
|  2 | suzuki    |   22 |
|  3 | takahashi |   29 |
|  4 | tanaka    |   30 |
+----+-----------+------+
7 rows in set (0.01 sec)
mysql> select * from users order by age desc;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  4 | tanaka    |   30 |
|  3 | takahashi |   29 |
|  2 | suzuki    |   22 |
|  6 | watanabe  |   20 |
|  5 | ito       |   19 |
|  1 | sato      |   18 |
|  7 | yamamoto  | NULL |
+----+-----------+------+
7 rows in set (0.00 sec)
mysql> select * from users order by age;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  7 | yamamoto  | NULL |
|  1 | sato      |   18 |
|  5 | ito       |   19 |
|  6 | watanabe  |   20 |
|  2 | suzuki    |   22 |
|  3 | takahashi |   29 |
|  4 | tanaka    |   30 |
+----+-----------+------+
7 rows in set (0.00 sec)
mysql> select * from users order by desc;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'desc' at line 1
mysql> select * from users order by age desc;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  4 | tanaka    |   30 |
|  3 | takahashi |   29 |
|  2 | suzuki    |   22 |
|  6 | watanabe  |   20 |
|  5 | ito       |   19 |
|  1 | sato      |   18 |
|  7 | yamamoto  | NULL |
+----+-----------+------+
7 rows in set (0.00 sec)
mysql> select name, age from users order by age desc;
+-----------+------+
| name      | age  |
+-----------+------+
| tanaka    |   30 |
| takahashi |   29 |
| suzuki    |   22 |
| watanabe  |   20 |
| ito       |   19 |
| sato      |   18 |
| yamamoto  | NULL |
+-----------+------+
7 rows in set (0.00 sec)
mysql> select * from users order by age desc;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  4 | tanaka    |   30 |
|  3 | takahashi |   29 |
|  2 | suzuki    |   22 |
|  6 | watanabe  |   20 |
|  5 | ito       |   19 |
|  1 | sato      |   18 |
|  7 | yamamoto  | NULL |
+----+-----------+------+
7 rows in set (0.00 sec)
mysql> select * from users limit 3;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  1 | sato      |   18 |
|  2 | suzuki    |   22 |
|  3 | takahashi |   29 |
+----+-----------+------+
3 rows in set (0.00 sec)
mysql> select * from users order by age asc limit 3;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  7 | yamamoto | NULL |
|  1 | sato     |   18 |
|  5 | ito      |   19 |
+----+----------+------+
3 rows in set (0.01 sec)
mysql> select * from users where age is not null order by age asc limit 3;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | sato     |   18 |
|  5 | ito      |   19 |
|  6 | watanabe |   20 |
+----+----------+------+
3 rows in set (0.00 sec)
mysql> select * from users where age !=18 order by age asc limit 3;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  5 | ito      |   19 |
|  6 | watanabe |   20 |
|  2 | suzuki   |   22 |
+----+----------+------+
3 rows in set (0.00 sec)
mysql> select * from users where age is not null order by age asc limit 3 offset 3;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  2 | suzuki    |   22 |
|  3 | takahashi |   29 |
|  4 | tanaka    |   30 |
+----+-----------+------+
3 rows in set (0.00 sec)
mysql> select * from users;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  1 | sato      |   18 |
|  2 | suzuki    |   22 |
|  3 | takahashi |   29 |
|  4 | tanaka    |   30 |
|  5 | ito       |   19 |
|  6 | watanabe  |   20 |
|  7 | yamamoto  | NULL |
+----+-----------+------+
7 rows in set (0.00 sec)
mysql> update users set age = 40 where id =1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> select * from users;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  1 | sato      |   40 |
|  2 | suzuki    |   22 |
|  3 | takahashi |   29 |
|  4 | tanaka    |   30 |
|  5 | ito       |   19 |
|  6 | watanabe  |   20 |
|  7 | yamamoto  | NULL |
+----+-----------+------+
7 rows in set (0.00 sec)
mysql> update users set name = 'ahoaho', age = 41 where id =1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> select * from users;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  1 | ahoaho    |   41 |
|  2 | suzuki    |   22 |
|  3 | takahashi |   29 |
|  4 | tanaka    |   30 |
|  5 | ito       |   19 |
|  6 | watanabe  |   20 |
|  7 | yamamoto  | NULL |
+----+-----------+------+
7 rows in set (0.00 sec)
mysql> update users set age=99 where age >=30;
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2  Changed: 2  Warnings: 0
mysql> select * from users;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  1 | ahoaho    |   99 |
|  2 | suzuki    |   22 |
|  3 | takahashi |   29 |
|  4 | tanaka    |   99 |
|  5 | ito       |   19 |
|  6 | watanabe  |   20 |
|  7 | yamamoto  | NULL |
+----+-----------+------+
7 rows in set (0.01 sec)
mysql> update users set name ='bokeboke';
Query OK, 7 rows affected (0.00 sec)
Rows matched: 7  Changed: 7  Warnings: 0
mysql> select * from users;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | bokeboke |   99 |
|  2 | bokeboke |   22 |
|  3 | bokeboke |   29 |
|  4 | bokeboke |   99 |
|  5 | bokeboke |   19 |
|  6 | bokeboke |   20 |
|  7 | bokeboke | NULL |
+----+----------+------+
7 rows in set (0.00 sec)
mysql> exit
Bye
@kiyo4810 ➜ /workspaces/learn-mysql/mysql-projects (main) $ sudo mysql -u root < initialize_lecture_select.sql 
@kiyo4810 ➜ /workspaces/learn-mysql/mysql-projects (main) $ sudo mysql -u mydbuser -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 8.0.45-0ubuntu0.24.04.1 (Ubuntu)
Copyright (c) 2000, 2026, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select * from users;
ERROR 1046 (3D000): No database selected
mysql> use mydb;
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
mysql> select * from users;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  1 | sato      |   18 |
|  2 | suzuki    |   22 |
|  3 | takahashi |   29 |
|  4 | tanaka    |   30 |
|  5 | ito       |   19 |
|  6 | watanabe  |   20 |
|  7 | yamamoto  | NULL |
+----+-----------+------+
7 rows in set (0.00 sec)
mysql> delete from users where id =1;
Query OK, 1 row affected (0.01 sec)
mysql> select * from users;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  2 | suzuki    |   22 |
|  3 | takahashi |   29 |
|  4 | tanaka    |   30 |
|  5 | ito       |   19 |
|  6 | watanabe  |   20 |
|  7 | yamamoto  | NULL |
+----+-----------+------+
6 rows in set (0.00 sec)
mysql> delete from users where age >=20;
Query OK, 4 rows affected (0.00 sec)
mysql> select * from users;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  5 | ito      |   19 |
|  7 | yamamoto | NULL |
+----+----------+------+
2 rows in set (0.00 sec)
mysql> delete from users;
Query OK, 2 rows affected (0.00 sec)
mysql> select * from users;
Empty set (0.00 sec)
mysql> exit
Bye
@kiyo4810 ➜ /workspaces/learn-mysql/mysql-projects (main) $ 

		

deleteしてinsertし直す

			
mysql> delete from users where id=1;
Query OK, 1 row affected (0.00 sec)
mysql> select * from users;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  2 | suzuki    |   22 |
|  3 | takahashi |   29 |
|  4 | tanaka    |   30 |
|  5 | ito       |   19 |
|  6 | watanabe  |   20 |
|  7 | yamamoto  | NULL |
+----+-----------+------+
6 rows in set (0.00 sec)
mysql> insert into users (id, name, age) values (1,"nakano",52);
Query OK, 1 row affected (0.01 sec)
mysql> select * from users;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  1 | nakano    |   52 |
|  2 | suzuki    |   22 |
|  3 | takahashi |   29 |
|  4 | tanaka    |   30 |
|  5 | ito       |   19 |
|  6 | watanabe  |   20 |
|  7 | yamamoto  | NULL |
+----+-----------+------+
7 rows in set (0.00 sec)

		

MySQLまとめ

今回の教材のDBのセクション、終わっちゃった。基本的なところだけなのでまずはこんな感じかな。