【データベース入門1-1】導入、MySQLインストール

2024年10月31日 19:40

プログラミング学習のうち、今日からデータベースを学び始めました。
Node.jsもまだまだまだなんだかなという感じですが、以前データベースのオンライン講座をお願いしていたためこのタイミングで。
セミナーは全4日間（＋予備日1日）、一回3時間の全12時間を予定しています。
初日の今日学んだことをまとめます。

データベース入門セミナー初日概要

・データベース（DB）とは？
→価値ある情報を1つの場所に集約し、整理・管理、検索するためのシステム
・データの保存をデータベースで実現すると何が良いか？
→統一した記載ができる、高速、共有が容易
・DBの構成要素は？
テーブル：データを表形式で保存する基本単位
フィールド：テーブル内の各列で、特定のデータ項目を表す
レコード：テーブル内の各行で、特定のエンティティに関連付けられた情報を含んでいる
DBMS：データベースの頭脳にあたり、データ本体とユーザーとの橋渡しとなるプログラム
・データベース管理システム（DBMS）でできること
①データ定義
②データ操作
　検索
　更新
　共有
③データ制御
・主なSQL言語
◆DBの一覧を見る　SHOW DATABASES;
◆テーブルを作る　 create database ＜任意の名前、例えばfe＞;
◆作ったテーブルを見る　show fe;
◆テーブルを使う　use fe;
◆テーブルのフィールドを定義する　create table staff(
-> ID INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
-> NAME VARCHAR(10) not null,
-> ADDRESS varchar(10),
-> WAGE int unsigned
-> );
◆フィールドの中身を表示する　show columns from staff;
◆フィールドの名前を変更する　 alter table staff rename to shain;
◆テーブルとフィールドを見る　show tables;
◆テーブルの中のフィールドを変更する　alter table shain change wage gekkyu int unsigned not null;
◆テーブルの中にフィールドを追加する　alter table shain add isadmin bit(1) not null default 0;
◆フィールドを選択する　select * from shain;
◆フィールドにvalueを追加する　insert into shain(NAME,ADDRESS,
gekkyu,isadmin)
-> VALUES("田中","京都府",2000,0),
-> ("佐藤","大阪府",4000,1);
◆フィールドから任意の属性と属性値を表示する　select NAME,ADDRESS from shain;
◆フィールドから条件を満たしたデータを表示する　select * from shain
-> where not gekkyu > 3000;　/where gekkyu between 1000 and 3000/where gekkyu in (1000,2000)/where address is null
◆属性名を変える　select name as " 氏名",address as "住所" from shain;
◆レコードをある属性の昇順に並び替える　 select * from shain order by gekkyu desc;
◆レコードをある属性の降順に並び替える　 select * from shain order by gekkyu asc;
◆レコードの数を数える　select count(*) from shain;
◆レコードの数を数える（数という属性名で）　 select count(*) as "数" from shain;
◆属性の平均を求める　select avg(gekkyu) from shain;
◆属性の平均を求める（月給平均という属性名で）　select avg(gekkyu) as "月給平均" from shain;
◆ある条件を満たした属性の平均を求める　select avg(gekkyu) as "月給平均" from shain where gekkyu > 3000;
◆テーブルに属性を加える　alter table shain add busyoID int not null;
◆フィールドに属性の値を追加する　insert into busho (bushoID,NAME)
-> VALUES(1,"経理"),
-> (2,"事業"),
-> (3,"営業");　
◆属性の値を更新する update shain set busyoID = 2 where busyoID = 0;
◆属性に外部キーを指定する　alter table shain add constraint bushoID foreign key (bushoID) references busho(bushoID);
◆指定した外部キーの参照元と参照先を結合する　select * from shain inner join busho on shain.bushoid =busho.bushoID;
◆フィールドからレコードを削除する　delete from shain where name = "村田";

感想

今日はデータベースの概要について学びました。
データベースを使って何ができるのかがふんわり理解でき、情報の更新や削除、追加をどのようにするのか手を動かして演習しました。
次回の講座は11/5を予定しているので、それまでに今日使った機能をもう一巡は書いて復習したいと思います。
それではおやすみなさい。

備考
MySQLのインストール手順：

以下の2章と4.1~4.5章参照：

今日の演習内容

mysql> show fe;
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 'fe' at line 1
mysql> drop database fe;
Query OK, 0 rows affected (0.02 sec)

mysql> create database fe;
Query OK, 1 row affected (0.00 sec)

mysql> use fe;
Database changed
mysql> create table staff(
-> ID INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
-> NAME VARCHARA(10) not null,
-> ADRESS varchara(10),
-> WAGE int unsigned
-> );
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 'VARCHARA(10) not null,
ADRESS varchara(10),
WAGE int unsigned
)' at line 3
mysql> create table staff(
-> ID INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, -> NAME VARCHAA(10) not null,
-> NAME VARCHAA(^[[B^Zt null,
zsh: suspended mysql -u root

mysql> create table staff(
-> ID INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
->
-> NAME VARCHAR(10) not null,
-> ADRESS varchara(10),
-> WAGE int unsigned
-> );
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 'varchara(10),
WAGE int unsigned
)' at line 5
mysql> ADRESS varchar(10),
-> create table staff(
-> create table staff(^C

^C
mysql> create table staff(
-> ID INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, -> NAME VARCHAR(10) not null, -> ADRESS varchar(10),
-> WAGE int unsigned -> ); ERROR 1046 (3D000): No database selected
mysql> use fe; Database changed
mysql> create table staff(
-> ID INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, -> NAME VARCHAR(10) not null, -> ADRESS varchar(10), -> WAGE int unsigned -> ); Query OK, 0 rows affected (0.02 sec)

mysql> alter table staff rename to shain;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+--------------+
| Tables_in_fe |
+--------------+
| shain |
+--------------+
1 row in set (0.00 sec)

mysql> alter table shain change wage gekkyu int unsigned not null;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> alter table shine add isadimin bit(1) not null dafault 0;
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 'dafault 0' at line 1
mysql> alter table shain add isadimin bit(1) not null dafault 0;
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 'dafault 0' at line 1
mysql> alter table shain add isadmin bit(1) not null default 0;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> select * from shain;
Empty set (0.00 sec)

mysql> insert into shain(NAME,ADDRESS,gekkyu,isadmin)
-> VALUES("田中","京都府",2000,0),
-> '("佐藤" , " 大阪府",4000,1);
'> ^C

^C
mysql> insert into shain(NAME,ADDRESS,gekkyu,isadmin)
-> VALUES("田中","京都府",2000,0),
-> ("佐藤","大阪府",4000,1);
ERROR 1054 (42S22): Unknown column 'ADDRESS' in 'field list'
mysql> alter table shain change ADRESS ADDRESS varchar(10);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> insert into shain(NAME,ADDRESS,gekkyu,isadmin) -> VALUES("田中","京都府",2000,0), -> ("佐藤","大阪府",4000,1); Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from shain;
+----+--------+-----------+--------+------------------+
| ID | NAME | ADDRESS | gekkyu | isadmin |
+----+--------+-----------+--------+------------------+
| 1 | 田中 | 京都府 | 2000 | 0x00 |
| 2 | 佐藤 | 大阪府 | 4000 | 0x01 |
+----+--------+-----------+--------+------------------+
2 rows in set (0.00 sec)

mysql> select NAME,ADDRESS from shain;
+--------+-----------+
| NAME | ADDRESS |
+--------+-----------+
| 田中 | 京都府 |
| 佐藤 | 大阪府 |
+--------+-----------+
2 rows in set (0.00 sec)

mysql> select * from shain
-> where gekkyu > 3000;
+----+--------+-----------+--------+------------------+
| ID | NAME | ADDRESS | gekkyu | isadmin |
+----+--------+-----------+--------+------------------+
| 2 | 佐藤 | 大阪府 | 4000 | 0x01 |
+----+--------+-----------+--------+------------------+
1 row in set (0.00 sec)

mysql> select * from shain
-> where not gekkyu > 3000;
+----+--------+-----------+--------+------------------+
| ID | NAME | ADDRESS | gekkyu | isadmin |
+----+--------+-----------+--------+------------------+
| 1 | 田中 | 京都府 | 2000 | 0x00 |
+----+--------+-----------+--------+------------------+
1 row in set (0.00 sec)

mysql> select * from shain where gekkyu between 1000 and 3000;
+----+--------+-----------+--------+------------------+
| ID | NAME | ADDRESS | gekkyu | isadmin |
+----+--------+-----------+--------+------------------+
| 1 | 田中 | 京都府 | 2000 | 0x00 |
+----+--------+-----------+--------+------------------+
1 row in set (0.00 sec)

mysql> select * from shain where gekkyu in (1000,2000);
+----+--------+-----------+--------+------------------+
| ID | NAME | ADDRESS | gekkyu | isadmin |
+----+--------+-----------+--------+------------------+
| 1 | 田中 | 京都府 | 2000 | 0x00 |
+----+--------+-----------+--------+------------------+
1 row in set (0.00 sec)

mysql> select * from shain where address is null;
Empty set (0.00 sec)

mysql> select name as " 氏名",address as "住所" from shain;
+--------+-----------+
| 氏名 | 住所 |
+--------+-----------+
| 田中 | 京都府 |
| 佐藤 | 大阪府 |
+--------+-----------+
2 rows in set (0.00 sec)

mysql> select * from shain order by gekkyu;
+----+--------+-----------+--------+------------------+
| ID | NAME | ADDRESS | gekkyu | isadmin |
+----+--------+-----------+--------+------------------+
| 1 | 田中 | 京都府 | 2000 | 0x00 |
| 2 | 佐藤 | 大阪府 | 4000 | 0x01 |
+----+--------+-----------+--------+------------------+
2 rows in set (0.00 sec)

mysql> select * from shain order by gekkyu desc;
+----+--------+-----------+--------+------------------+
| ID | NAME | ADDRESS | gekkyu | isadmin |
+----+--------+-----------+--------+------------------+
| 2 | 佐藤 | 大阪府 | 4000 | 0x01 |
| 1 | 田中 | 京都府 | 2000 | 0x00 |
+----+--------+-----------+--------+------------------+
2 rows in set (0.01 sec)

mysql> select * from shain order by gekkyu asc;;
+----+--------+-----------+--------+------------------+
| ID | NAME | ADDRESS | gekkyu | isadmin |
+----+--------+-----------+--------+------------------+
| 1 | 田中 | 京都府 | 2000 | 0x00 |
| 2 | 佐藤 | 大阪府 | 4000 | 0x01 |
+----+--------+-----------+--------+------------------+
2 rows in set (0.00 sec)

ERROR:
No query specified

mysql> select * from shain order by gekkyu;;
+----+--------+-----------+--------+------------------+
| ID | NAME | ADDRESS | gekkyu | isadmin |
+----+--------+-----------+--------+------------------+
| 1 | 田中 | 京都府 | 2000 | 0x00 |
| 2 | 佐藤 | 大阪府 | 4000 | 0x01 |
+----+--------+-----------+--------+------------------+
2 rows in set (0.00 sec)

ERROR:
No query specified

mysql> select count() from shain;
+----------+
| count() |
+----------+
| 2 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) as "数" from shain;
+-----+
| 数 |
+-----+
| 2 |
+-----+
1 row in set (0.00 sec)

mysql> select avg(gekkyu) from shain;
+-------------+
| avg(gekkyu) |
+-------------+
| 3000.0000 |
+-------------+
1 row in set (0.00 sec)

mysql> select avg(gekkyu) as "月給平均" from shain;
+--------------+
| 月給平均 |
+--------------+
| 3000.0000 |
+--------------+
1 row in set (0.00 sec)

mysql> select avg(gekkyu) as "月給平均" from shain
-> having gekkyu > 3000;
ERROR 1054 (42S22): Unknown column 'gekkyu' in 'having clause'
mysql> select avg(gekkyu) as "月給平均" from shain where gekkyu > 3000;
+--------------+
| 月給平均 |
+--------------+
| 4000.0000 |
+--------------+
1 row in set (0.00 sec)

mysql> alter table shain add busyoID int not null;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> select * from shain;
+----+--------+-----------+--------+------------------+---------+
| ID | NAME | ADDRESS | gekkyu | isadmin | busyoID |
+----+--------+-----------+--------+------------------+---------+
| 1 | 田中 | 京都府 | 2000 | 0x00 | 0 |
| 2 | 佐藤 | 大阪府 | 4000 | 0x01 | 0 |
+----+--------+-----------+--------+------------------+---------+
2 rows in set (0.00 sec)

mysql> create table busho(
-> bushoID int primary key,
-> name varchar(10) not null
-> );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into busho (bushoID,NAME)
-> VALUES(1,"経理"),
-> (2,"事業"),
-> (3,"営業");
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> insert into shain(NAME,ADDRESS,GEKKYU)
-> VALUES("中村","東京都",5000),
-> ("村田","広島県",3000);^C

^C
mysql> insert into shain(NAME,ADDRESS,GEKKYU)
-> -> VALUES("中村","東京都",5000),
-> insert into shain(NAME,ADDRESS,GEKKYU^C

^C
mysql> insert into shain(NAME,ADDRESS,GEKKYU,busyoID)
-> -> VALUES("中村","東京都",5000,3),
-> insert into shain(NAME,ADDRESS,GEKKYU,busyoID)^[[B^[[A^C^C

^C
mysql> insert into shain(NAME,ADDRESS,GEKKYU,busyoID)
-> VALUES("中村","東京都",5000,3),
-> ("村田","広島県",3000,1);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> updata shain set busyoID = 2 where bushoID = 0;
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 'updata shain set busyoID = 2 where bushoID = 0' at line 1
mysql> updata shain set busyoID = 2 where busyoID = 0;
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 'updata shain set busyoID = 2 where busyoID = 0' at line 1
mysql> update shain set busyoID = 2 where busyoID = 0;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0

mysql> select * from shain;
+----+--------+-----------+--------+------------------+---------+
| ID | NAME | ADDRESS | gekkyu | isadmin | busyoID |
+----+--------+-----------+--------+------------------+---------+
| 1 | 田中 | 京都府 | 2000 | 0x00 | 2 |
| 2 | 佐藤 | 大阪府 | 4000 | 0x01 | 2 |
| 3 | 中村 | 東京都 | 5000 | 0x00 | 3 |
| 4 | 村田 | 広島県 | 3000 | 0x00 | 1 |
+----+--------+-----------+--------+------------------+---------+
4 rows in set (0.00 sec)

mysql> alter table shain change busyoID bushoID int not null;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> alter table shain add foreign key bushoID references busho bushoID;
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 'references busho bushoID' at line 1
mysql> show colums from busho;
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 'colums from busho' at line 1
mysql> show columns from busho;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| bushoID | int | NO | PRI | NULL | |
| name | varchar(10) | NO | | NULL | |
+---------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> alter table shain add constraint bushoID foreign key (bushoID) references busho(bushoID);
Query OK, 4 rows affected (0.02 sec)
Records: 4 Duplicates: 0 Warnings: 0

mysql> update shain set bushoID = 5 where bushoID =2;
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`fe`.`shain`, CONSTRAINT `bushoID` FOREIGN KEY (`bushoID`) REFERENCES `busho` (`bushoID`))
mysql> select * from shain inner join busho on shain.bushoid =busho.bushoID;
+----+--------+-----------+--------+------------------+---------+---------+--------+
| ID | NAME | ADDRESS | gekkyu | isadmin | bushoID | bushoID | name |
+----+--------+-----------+--------+------------------+---------+---------+--------+
| 4 | 村田 | 広島県 | 3000 | 0x00 | 1 | 1 | 経理 |
| 1 | 田中 | 京都府 | 2000 | 0x00 | 2 | 2 | 事業 |
| 2 | 佐藤 | 大阪府 | 4000 | 0x01 | 2 | 2 | 事業 |
| 3 | 中村 | 東京都 | 5000 | 0x00 | 3 | 3 | 営業 |
+----+--------+-----------+--------+------------------+---------+---------+--------+
4 rows in set (0.01 sec)

mysql> delete from shain where name = "村田";
Query OK, 1 row affected (0.01 sec)

mysql> select * from shain;
+----+--------+-----------+--------+------------------+---------+
| ID | NAME | ADDRESS | gekkyu | isadmin | bushoID |
+----+--------+-----------+--------+------------------+---------+
| 1 | 田中 | 京都府 | 2000 | 0x00 | 2 |
| 2 | 佐藤 | 大阪府 | 4000 | 0x01 | 2 |
| 3 | 中村 | 東京都 | 5000 | 0x00 | 3 |
+----+--------+-----------+--------+------------------+---------+
3 rows in set (0.00 sec)

【データベース入門1-1】導入、MySQLインストール

データベース入門セミナー初日概要

感想

いいなと思ったら応援しよう！