【Coursera】University of Colorado ; Database Management Essentials (2)
Database Management Essentialの講義ノート。
【Basic SQL CREATE TABLE statement】
Q. なぜDBMSベンダーはCREATE TABLEクエリを発行する際にそれぞれ異なるGUIを提供するのか。
Ans. CREATE TABLEクエリは記述するよりもGUI化する方が作成の確実性が向上する。(下の問いにも関連)
Q. SQL文でコンマを抜かすことでどのようにあなたは時間を無駄にするのか。
Ans. DBMSはSQLクエリを細かくチェックする。文法間違いはもちろん、コンマのような細かい記述についても誤りがあれば意図した通りに実行されない。
実際の教材のテーブルを作成する。環境はMySQLとする。
講義ではOracle SQLの利用もMySQLに併せて推薦される。
livesql.oracle.com
今回はMySQLでDB/テーブルを作成する。
$docker run --name mysql -v /$SOME_HOSTPATH/mysql:/etc/mysql -e MYSQL_ROOT_PASSWORD=password -d mysql:5.6 $ docker ps CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES 4eb86b7cb2f2 mysql:5.6 "docker-entrypoint.s…" 2 seconds ago Up 1 second 3306/tcp mysql $ docker exec -it 4eb86b7cb2f2 bash root@4eb86b7cb2f2:/# root@4eb86b7cb2f2:/# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.6.41 MySQL Community Server (GPL) Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. 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> mysql> create database test; Query OK, 1 row affected (0.00 sec) mysql> use test Database changed mysql> show tables; Empty set (0.00 sec) mysql> create table Student ( -> stdNo char(11) not null, -> stdFirstName varchar(30) not null, -> stdLastName varchar(30) not null, -> stdCity varchar(30) not null, -> stdState char(2) not null, -> stdZip char(10) not null, -> stdMajor char(6), -> stdClass char(2), -> stdGPA decimal(3,2), -> constraint StudentPk primary key (StdNo) ); Query OK, 0 rows affected (0.06 sec) mysql> show columns from Student; +--------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+--------------+------+-----+---------+-------+ | stdNo | char(11) | NO | PRI | NULL | | | stdFirstName | varchar(30) | NO | | NULL | | | stdLastName | varchar(30) | NO | | NULL | | | stdCity | varchar(30) | NO | | NULL | | | stdState | char(2) | NO | | NULL | | | stdZip | char(10) | NO | | NULL | | | stdMajor | char(6) | YES | | NULL | | | stdClass | char(2) | YES | | NULL | | | stdGPA | decimal(3,2) | YES | | NULL | | +--------------+--------------+------+-----+---------+-------+ 9 rows in set (0.01 sec) mysql> mysql> create table Course( -> courseNo char(6) not null, -> crsDesc varchar(50) not null, -> crsUnits integer, -> constraint CoursePk primary key (courseNo) ); Query OK, 0 rows affected (0.05 sec) mysql> show columns from Course; +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | courseNo | char(6) | NO | PRI | NULL | | | crsDesc | varchar(50) | NO | | NULL | | | crsUnits | int(11) | YES | | NULL | | +----------+-------------+------+-----+---------+-------+ 3 rows in set (0.01 sec) mysql> mysql> create table Faculty( -> FacNo char(11) not null, -> FacFirstName varchar(30) not null, -> FacLastName varchar(30) not null, -> FacCity varchar(30) not null, -> FacState char(2) not null, -> FacZipCode char(10) not null, -> FacRank char(4), -> FacHireDate date, -> FacSalary decimal(10,2), -> FacSupervisor char(11), -> FacDept char(6), -> constraint FacultyPk primary key (FacNo) ); Query OK, 0 rows affected (0.02 sec) mysql> mysql> show columns from Faculty; +---------------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------+---------------+------+-----+---------+-------+ | FacNo | char(11) | NO | PRI | NULL | | | FacFirstName | varchar(30) | NO | | NULL | | | FacLastName | varchar(30) | NO | | NULL | | | FacCity | varchar(30) | NO | | NULL | | | FacState | char(2) | NO | | NULL | | | FacZipCode | char(10) | NO | | NULL | | | FacRank | char(4) | YES | | NULL | | | FacHireDate | date | YES | | NULL | | | FacSalary | decimal(10,2) | YES | | NULL | | | FacSupervisor | char(11) | YES | | NULL | | | FacDept | char(6) | YES | | NULL | | +---------------+---------------+------+-----+---------+-------+ 11 rows in set (0.00 sec) mysql> mysql> create table Offering( -> OfferNo integer not null, -> CourseNo char(6) not null, -> OffTerm char(6) not null, -> OffYear integer not null, -> OffLocation varchar(30), -> OffTime varchar(10), -> FacNo char(11), -> OffDays char(4), -> constraint CourseFk foreign key (CourseNo) references Course (courseNo), -> constraint FacultyFk foreign key (FacNo) references Faculty (FacNo) ); Query OK, 0 rows affected (0.03 sec) mysql> show column from Offering; 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 'column from Offering' at line 1 mysql> show columns from Offering; +-------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+---------+-------+ | OfferNo | int(11) | NO | | NULL | | | CourseNo | char(6) | NO | MUL | NULL | | | OffTerm | char(6) | NO | | NULL | | | OffYear | int(11) | NO | | NULL | | | OffLocation | varchar(30) | YES | | NULL | | | OffTime | varchar(10) | YES | | NULL | | | FacNo | char(11) | YES | MUL | NULL | | | OffDays | char(4) | YES | | NULL | | +-------------+-------------+------+-----+---------+-------+ 8 rows in set (0.00 sec) mysql>
