Thursday, February 17, 2005
5. create tables in contactDB
right this'll be it tonight cos I'm v.tired now (ahhh!)
no flannel, just stuff...
1. create the first table at the command prompt
as per the book (hour 8) i create my first table at the command prompt... like this:
C:\mysql\bin>mysql -usupercontact -pA1234567
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 15 to server version: 4.0.17-nt
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> use contactDB;
Database changed
mysql> CREATE TABLE master_name (
-> name_id SMALLINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
-> name_dateadded DATETIME DEFAULT '0000-00-00 00:00:00',
-> name_datemodified DATETIME DEFAULT '0000-00-00 00:00:00',
-> firstname VARCHAR (75),
-> lastname VARCHAR (75),
-> INDEX idx_fn (firstname),
-> INDEX idx_ln (lastname)
-> );
Query OK, 0 rows affected (0.05 sec)
mysql> show tables;
+---------------------+
| Tables_in_contactdb |
+---------------------+
| master_name |
+---------------------+
1 row in set (0.00 sec)
mysql>
right, done that...
2. type the table definitions into a .sql text file
write the file in notepad, for the practice... like this
USE contactDB;
CREATE TABLE job_function (
job_id SMALLINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
name_id SMALLINT UNSIGNED NOT NULL DEFAULT '0',
job_dateadded DATETIME DEFAULT '0000-00-00 00:00:00',
job_datemodified DATETIME DEFAULT '0000-00-00 00:00:00',
jobfunction VARCHAR (100),
INDEX idx_job (jobfunction)
);
CREATE TABLE company (
company_id SMALLINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
company_dateadded DATETIME DEFAULT '0000-00-00 00:00:00',
company_datemodified DATETIME DEFAULT '0000-00-00 00:00:00',
companyname VARCHAR (100),
INDEX idx_job (companyname)
);
CREATE TABLE name_company_map (
name_id SMALLINT UNSIGNED NOT NULL,
company_id SMALLINT UNSIGNED NOT NULL
);
CREATE TABLE address (
add_id SMALLINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
name_id SMALLINT UNSIGNED NOT NULL DEFAULT '0',
add_dateadded DATETIME DEFAULT '0000-00-00 00:00:00',
add_datemodified DATETIME DEFAULT '0000-00-00 00:00:00',
streetaddress VARCHAR (255),
city VARCHAR (50),
state CHAR (2),
zipcode VARCHAR (10),
add_type ENUM ('home','work','other')
);
CREATE TABLE telephone (
tel_id SMALLINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
name_id SMALLINT UNSIGNED NOT NULL DEFAULT '0',
tel_dateadded DATETIME DEFAULT '0000-00-00 00:00:00',
tel_datemodified DATETIME DEFAULT '0000-00-00 00:00:00',
tel_countrycode CHAR (3),
tel_number VARCHAR (25),
tel_type ENUM ('home','work','other')
);
CREATE TABLE fax (
fax_id SMALLINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
name_id SMALLINT UNSIGNED NOT NULL DEFAULT '0',
fax_dateadded DATETIME DEFAULT '0000-00-00 00:00:00',
fax_datemodified DATETIME DEFAULT '0000-00-00 00:00:00',
fax_countrycode CHAR (3),
fax_number VARCHAR (25),
fax_type ENUM ('home','work','other')
);
CREATE TABLE email (
email_id SMALLINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
name_id SMALLINT UNSIGNED NOT NULL DEFAULT '0',
email_dateadded DATETIME DEFAULT '0000-00-00 00:00:00',
email_datemodified DATETIME DEFAULT '0000-00-00 00:00:00',
email_countrycode CHAR (3),
email_number VARCHAR (150),
email_type ENUM ('home','work','other')
);
CREATE TABLE personal_notes (
notes_id SMALLINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
name_id SMALLINT UNSIGNED NOT NULL DEFAULT '0',
notes_dateadded DATETIME DEFAULT '0000-00-00 00:00:00',
notes_datemodified DATETIME DEFAULT '0000-00-00 00:00:00',
note TEXT
);
if you do cut and paste my table defns. check it over for syntax errors cos it's late now...
then
3. add all the tables to contactDB
C:\mysql\bin>mysql -usupercontact -pA1234567 < c:\mysqldefs\ab_contactDB.sql
C:\mysql\bin>mysql -usupercontact -pA1234567
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 18 to server version: 4.0.17-nt
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> use contactDB;
Database changed
mysql> show tables;
+---------------------+
| Tables_in_contactdb |
+---------------------+
| address |
| company |
| email |
| fax |
| job_function |
| master_name |
| name_company_map |
| personal_notes |
| telephone |
+---------------------+
9 rows in set (0.00 sec)
mysql>
that's all good, time for bed!
no flannel, just stuff...
1. create the first table at the command prompt
as per the book (hour 8) i create my first table at the command prompt... like this:
C:\mysql\bin>mysql -usupercontact -pA1234567
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 15 to server version: 4.0.17-nt
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> use contactDB;
Database changed
mysql> CREATE TABLE master_name (
-> name_id SMALLINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
-> name_dateadded DATETIME DEFAULT '0000-00-00 00:00:00',
-> name_datemodified DATETIME DEFAULT '0000-00-00 00:00:00',
-> firstname VARCHAR (75),
-> lastname VARCHAR (75),
-> INDEX idx_fn (firstname),
-> INDEX idx_ln (lastname)
-> );
Query OK, 0 rows affected (0.05 sec)
mysql> show tables;
+---------------------+
| Tables_in_contactdb |
+---------------------+
| master_name |
+---------------------+
1 row in set (0.00 sec)
mysql>
right, done that...
2. type the table definitions into a .sql text file
write the file in notepad, for the practice... like this
USE contactDB;
CREATE TABLE job_function (
job_id SMALLINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
name_id SMALLINT UNSIGNED NOT NULL DEFAULT '0',
job_dateadded DATETIME DEFAULT '0000-00-00 00:00:00',
job_datemodified DATETIME DEFAULT '0000-00-00 00:00:00',
jobfunction VARCHAR (100),
INDEX idx_job (jobfunction)
);
CREATE TABLE company (
company_id SMALLINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
company_dateadded DATETIME DEFAULT '0000-00-00 00:00:00',
company_datemodified DATETIME DEFAULT '0000-00-00 00:00:00',
companyname VARCHAR (100),
INDEX idx_job (companyname)
);
CREATE TABLE name_company_map (
name_id SMALLINT UNSIGNED NOT NULL,
company_id SMALLINT UNSIGNED NOT NULL
);
CREATE TABLE address (
add_id SMALLINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
name_id SMALLINT UNSIGNED NOT NULL DEFAULT '0',
add_dateadded DATETIME DEFAULT '0000-00-00 00:00:00',
add_datemodified DATETIME DEFAULT '0000-00-00 00:00:00',
streetaddress VARCHAR (255),
city VARCHAR (50),
state CHAR (2),
zipcode VARCHAR (10),
add_type ENUM ('home','work','other')
);
CREATE TABLE telephone (
tel_id SMALLINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
name_id SMALLINT UNSIGNED NOT NULL DEFAULT '0',
tel_dateadded DATETIME DEFAULT '0000-00-00 00:00:00',
tel_datemodified DATETIME DEFAULT '0000-00-00 00:00:00',
tel_countrycode CHAR (3),
tel_number VARCHAR (25),
tel_type ENUM ('home','work','other')
);
CREATE TABLE fax (
fax_id SMALLINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
name_id SMALLINT UNSIGNED NOT NULL DEFAULT '0',
fax_dateadded DATETIME DEFAULT '0000-00-00 00:00:00',
fax_datemodified DATETIME DEFAULT '0000-00-00 00:00:00',
fax_countrycode CHAR (3),
fax_number VARCHAR (25),
fax_type ENUM ('home','work','other')
);
CREATE TABLE email (
email_id SMALLINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
name_id SMALLINT UNSIGNED NOT NULL DEFAULT '0',
email_dateadded DATETIME DEFAULT '0000-00-00 00:00:00',
email_datemodified DATETIME DEFAULT '0000-00-00 00:00:00',
email_countrycode CHAR (3),
email_number VARCHAR (150),
email_type ENUM ('home','work','other')
);
CREATE TABLE personal_notes (
notes_id SMALLINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
name_id SMALLINT UNSIGNED NOT NULL DEFAULT '0',
notes_dateadded DATETIME DEFAULT '0000-00-00 00:00:00',
notes_datemodified DATETIME DEFAULT '0000-00-00 00:00:00',
note TEXT
);
if you do cut and paste my table defns. check it over for syntax errors cos it's late now...
then
3. add all the tables to contactDB
C:\mysql\bin>mysql -usupercontact -pA1234567 < c:\mysqldefs\ab_contactDB.sql
C:\mysql\bin>mysql -usupercontact -pA1234567
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 18 to server version: 4.0.17-nt
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> use contactDB;
Database changed
mysql> show tables;
+---------------------+
| Tables_in_contactdb |
+---------------------+
| address |
| company |
| email |
| fax |
| job_function |
| master_name |
| name_company_map |
| personal_notes |
| telephone |
+---------------------+
9 rows in set (0.00 sec)
mysql>
that's all good, time for bed!
