MySQL I : Select and Insert

1. Start the MySQL Monitor client: mysql –u root –p
Password: mypass
2. Create and load for use, a new database:
CREATE DATABASE accounting;
USE accounting;
3. Create an invoices table:
CREATE TABLE invoices (
invoice_id SMALLINT(4) UNSIGNED NOT NULL AUTO_INCREMENT,
client_id SMALLINT(3) UNSIGNED,
invoice_date DATE NOT NULL,
invoice_amount DECIMAL(10,2) UNSIGNED NOT NULL,
invoice_description TINYTEXT,
PRIMARY KEY (invoice_id),
INDEX (invoice_date)
);

Note: Keys and indexes are specified last so that they are enacted after the column is created.

Having trouble with typos? Use your arrow keys, including up and down.
4. Create the remaining three tables:

CREATE TABLE clients (
client_id SMALLINT(3) UNSIGNED NOT NULL AUTO_INCREMENT,
client_name VARCHAR(40) NOT NULL,
client_street VARCHAR(80),
client_city VARCHAR(30),
client_state CHAR(2),
client_zip VARCHAR(10),
client_phone VARCHAR(16),
contact_name VARCHAR(40),
contact_email VARCHAR(60),
PRIMARY KEY (client_id),
INDEX (client_name)
);

CREATE TABLE expenses (
expense_id SMALLINT(4) UNSIGNED NOT NULL AUTO_INCREMENT,
expense_category_id TINYINT(3) UNSIGNED,
expense_amount DECIMAL(10,2) UNSIGNED,
expense_description TINYTEXT,
expense_date DATE,
PRIMARY KEY (expense_id)
);

CREATE TABLE expense_categories (
expense_category_id TINYINT(3) UNSIGNED NOT NULL AUTO_INCREMENT,
expense_category VARCHAR(30),
PRIMARY KEY (expense_category_id)
);
5. Confirm the existence of the four tables:
SHOW TABLES;
6. Are these correct? Note, results on paper, where the errors are so we can fix them later. Type:
SHOW COLUMNS FROM invoices;
SHOW COLUMNS FROM clients;
SHOW COLUMNS FROM expenses;
SHOW COLUMNS FROM expense_categories;
7. Exit the client.
8. Typing these sorts of SQL commands by hand is prone to error. An alternative is to type them into an external text file, and import that into MySQL, which executes it just as if you typed it by hand.
9. Using Windows Explorer, locate your .\my documents\mysql\scripts directory. Copy the file ‘create_accounting_tables.sql’ to the directory ‘c:\mysql\scripts\’
10. Go to the ‘c:\mysql\scripts’ directory and open this file with a text editor. Note that it is almost exactly what we typed, with the addition of dropping our probably messed up database first before recreating it. The syntax for dropping a database and all of its associated tables is: DROP DATABASE <database>;
11. Go to the ‘c:\mysql\bin’ directory and enter the MySQL Monitor client. Type:
\. c:\mysql\scripts\create_accounting_tables.sql
Note, this command does not use a semi-colon to close.
12. You should see:
Query OK, 12 rows affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Database changed
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec
Query OK, 0 rows affected (0.00 sec)
mysql>
13. Confirm the existence of the tables:
SHOW TABLES;
SHOW COLUMNS FROM invoices;
SHOW COLUMNS FROM clients;
SHOW COLUMNS FROM expenses;
SHOW COLUMNS FROM expense_categories;
14. More information about the MySQL Server, databases, and tables is available. Type:
SHOW DATABASES;
DESCRIBE invoices;
SHOW STATUS;

This latter show you the state of MySQL and its associated variables. More on this later.