MySQL I : Encryption and Passwords

Encoding Passwords: PASSWORD()

The PASSWORD() function creates a non-decryptable hash of the given password.

INSERT INTO users VALUES (NULL, ‘user_name’, PASSWORD(‘user_pw’));

Resetting a password:

mysqladmin -u user_name password
#you will be prompted for the new password

 

The ENCRYPT() function creates a non-decryptable encrypted string.

INSERT INTO clients VALUES (NULL, ENCRYPT(‘client_name’));

Note that the ENCRYPT() function uses the Unix crypt() function, so it may not work in Windows (unless you’re using CygWin).

You can use the DES_ENCRYPT() function when you’re connecting over SSL instead, if necessary.

 

ENCODE() and DECODE() allow you to create encrypted (encoded) strings which can be decrypted. ENCODE() requires a “salt,” or a beginning encryption value. If you lose or forget your salt, you will never be able to DECODE().

INSERT INTO clients VALUES (NULL, ENCODE(‘client_name’, salt));

For instance:

INSERT INTO clients VALUES (NULL, ENCODE(‘Sun Microsystems’, ‘mysalt’));

DECODE() can decrypt these strings, provided you know the salt:

SELECT client_id, DECODE(client_name, ‘mysalt’)client_name FROM clients WHERE client_id=24;

 

Encryption

1. Create a new users table:
CREATE TABLE users (
user_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
user_pass CHAR(16),
user_name TINYBLOB,
PRIMARY KEY (user_id)
);

Note the TINYBLOB datatype. This will be used to hold encrypted binary information.
2. Add a user to the user name and password to the table, and encrypt them both Type:
INSERT INTO users VALUES ( NULL, PASSWORD(‘mypass’), ENCODE(‘jwilson’, ‘hello’) );

The password mypass is stored encrypted, and is the user name with, with encryption generated by a random seed.

View your results with:
SELECT * FROM users;
3. You can select on encoded password and user name information, by decoding:
This works:
SELECT user_id FROM users
WHERE (user_pass = PASSWORD(‘mypass’)) AND
(DECODE(user_name, ‘hello’) = ‘jwilson’);

This does not work:
SELECT user_id FROM users
WHERE (user_pass = PASSWORD(‘mypass’)) AND
(DECODE(user_name, ‘hel’) = ‘jwilson’);

Question: Why does the second query fail?

Note: Password information should generally be encrypted. Other information may or may not be appropriate to encode (SSN might be). Make sure you can decode anything that you encode. If you use the salt feature this can difficult to keep track of!

MySQL I : Functions

Text Functions

MySQL functions look similar to mathematical functions. The function itself has a name, and its transformation is performed on a target: FUNCTION(column). The basic syntax is:

SELECT FUNCTION(column) FROM table_name

You can select multiple columns, and perform the function on only  one:

SELECT title, FUNCTION(artist) FROM albums

 

Text Functions
Name Usage Purpose
LENGTH() LENGTH(column) Returns the length of the string in column.
LEFT() LEFT(column, x) Returns the left x characters from column.
RIGHT() RIGHT(column, x) Returns the right x characters from column.
TRIM() TRIM(column) Removes spaces from the beginning and end of column.
UPPER() UPPER(column) Capitalizes the string in column.
LOWER() LOWER(column) Renders the string in column in all lower-case
SUBSTRING() SUBSTRING(column, start, length)

Returns from column the substring beginning start number of characters from the beginning, of length from that point.

CONCAT() CONCAT(column1, column2 [, column3])

Returns from column the substring beginning start number of characters from the beginning, of length from that point.

•Note that the strings are numbered starting with 1, not with 0.

•There can be no spaces between the function name and the first parenthesis.

Select the first 3 digits of a phone number formatted like 505-555-6666:

SELECT LEFT(phone_number, 3) FROM clients; #Returns 505

Select the first 3 digits of a phone number formatted like (505) 555-6666:

SELECT RIGHT(LEFT(phone_number, 4), 3)FROM clients; #Returns 505

Select the first 3 digits of a phone number formatted like (505) 555-6666:

SELECT SUBSTRING(phone_number, 2, 3)FROM clients; #Returns 505

Select the longest album name from the list:

SELECT album_title FROM albums ORDER BY LENGTH(album_title) DESC LIMIT 1;

 

Concatenate column values with the CONCAT() function. Its basic syntax is:

CONCAT(column, column2)

You can include strings (including spaces) enclosed in single quotes:

CONCAT(last_name, ‘, ‘, first_name)

Once you’ve concatenated values in a query, you’ll need a way to refer to the new string. Use AS:

SELECT CONCAT(column, column2) AS new_value FROM table_name;

For example:

SELECT CONCAT(last_name, ‘, ‘, first_name) AS artist_name FROM artists;

 

Numeric Functions
Name Usage Purpose
ABS() ABS(column) Returns the absolute value of column.
CEILING() CEILING(column) Returns the next-highest value from a column that contains a decimal. For instance, the value 1.25 will be returned as 2.
FLOOR() FLOOR(column) Returns the integer value from column. 1.25 will be returned as 1.
FORMAT() FORMAT(column, x) Returns column formatted with x decimal places, and with commas every three spaces: FORMAT(column, 2) returns 1,234.56
MOD() MOD(x, y) Returns the modulus (remainder) of dividing x by y.
RAND() RAND(column) Returns a random number, which will be a decimal between 0 and 1.0
ROUND() ROUND(x, y)

Returns x rounded to y decimal places.

SIGN() SIGN(column)

Returns -1, 0 or +1, indicating the sign of column.

SQRT() SQRT(column)

Returns the square root of column.

 

Perform simple arithmetic using +, -, * and / :

SELECT invoice_amount*discount FROM invoices;

 

Date and Time Functions
Name Usage Purpose
HOUR() HOUR(column) Returns only the hour of column.
MINUTE() MINUTE(column) Returns only the minute of column.
SECOND() SECOND(column) Returns only the second of column.
DAYNAME() DAYNAME(column) Returns the day name (e.g Monday) of column.
DAYOFMONTH() DAYOFMONTH(column) Returns the numeric day of the month (e.g. 25) of column.
MONTHNAME() MONTHNAME(column) Returns the month name of column (e.g. March).
MONTH() MONTH(column)

Returns numeric month of column (e.g. 10).

YEAR() YEAR(column)

Returns the numeric year of column.

ADDDATE() ADDDATE(column, INTERVAL x type) Returns the value of column plus x intervals of type: SECOND, MINUTE, HOUR, DAY, MONTH or YEAR.
SUBDATE() SUBDATE(column, INTERVAL x type) Returns the value of column minus x intervals of type: SECOND, MINUTE, HOUR, DAY, MONTH or YEAR.
CURDATE() CURDATE() Returns the current date only.
CURTIME() CURTIME() Returns the current time only.
NOW() NOW() Returns the current date and time.
UNIX_TIMESTAMP() UNIX_TIMESTAMP([date])

Returns the number of seconds since the epoch or since the date specified [optional].

Syntax for ADDDATE and SUBDATE:

ADDDATE(‘2002-1-1’, INTERVAL 30 DAY)

DO NOT FORGET THAT ADDDATE HAS THREE ( 3 ) Ds.

 

See http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html for more details.

• Do not forget the %

• Date values can indeed be “0” : 2005-00-00

• Any other characters will go into the result without interpretation.

• TIME_FORMAT() functions like DATE_FORMAT() but only works for time values.

Code Description
%a Abbreviated weekday name (Sun..Sat)
%b Abbreviated month name (Jan..Dec)
%c Month, numeric (0..12)
%D Day of the month with English suffix (0th, 1st, 2nd, 3rd, …)
%d Day of the month, numeric (00..31)
%e Day of the month, numeric (0..31)
%f Microseconds (000000..999999)
%H Hour (00..23)
%h Hour (01..12)
%I Hour (01..12)
%i Minutes, numeric (00..59)
%j Day of year (001..366)
%k Hour (0..23)
%l Hour (1..12)
%M Month name (January..December)
%m Month, numeric (00..12)
%p AM or PM
%r Time, 12-hour (hh:mm:ss followed by AM or PM)
%S Seconds (00..59)
%s Seconds (00..59)
%T Time, 24-hour (hh:mm:ss)
%U Week (00..53), where Sunday is the first day of the week
%u Week (00..53), where Monday is the first day of the week
%V Week (01..53), where Sunday is the first day of the week; used with %X
%v Week (01..53), where Monday is the first day of the week; used with %x
%W Weekday name (Sunday..Saturday)
%w Day of the week (0=Sunday..6=Saturday)
%X Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V
%x Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v
%Y Year, numeric, four digits
%y Year, numeric (two digits)
%% A literal ‘%’ character

 

GROUP BY returns blocks of aggregated groups (functionally, one result per group) rather than ordering multiple results, as in ORDER BY.

SELECT * FROM albums GROUP BY artist_name;

SELECT COUNT(albums) FROM albums GROUP BY artist_name;

 

Grouping Functions
Name Usage Purpose
MIN() MIN(column) Returns the smallest value of column.
MAX() MAX(column) Returns the largest value of column.
SUM() SUM(column) Returns the sum of all values of column.
COUNT() COUNT(column) Returns the count of values (rows) in column.

• COUNT() counts only non-null values.

• GROUP BY will group NULL values together.

 

LAST_INSERT_ID() returns the last auto-incremented value for your user session.

Other user’s sessions will have their own LAST_INSERT_ID() value; these values are not shared between users.

INSERT INTO users VALUES (NULL, user_name);

Assuming the auto-increment value is 8,

SELECT LAST_INSERT_ID();

will return 8 .

 

The DISTINCT() function returns all unique values from a column. It will, for instance, eliminate multiple instances of client_name (if you’ve somehow gotten multiple instances).

SELECT DISTINCT(user_name) FROM clients;

 

Note that you can embed functions within functions, as well as performing arithmetic:

SELECT CONCAT(“$”, FORMAT(invoice_amount*discount, 2)) FROM invoices;

 

 

MySQL Functions

1. Open the mysql monitor: mysql –u root –p
2. Use the accounting database: USE accounting;
3. Add an extraneous white space to the left of a string:
UPDATE clients SET client_name=’ Nepotism Inc.’
WHERE client_name LIKE ‘Nepotism Inc.’;

Check by typing: SELECT client_name FROM clients;
4. To remove all white extraneous spaces, tabs, and returns from a field, type:
SELECT TRIM(client_name) FROM clients;

– The function only affects the SELECT view, not the actual data!
– MySQL function can be used in operations other than SELECT, like INSERT of UPDATE.
5. Permanently remove the extraneous white space to the left of a string:
UPDATE clients SET client_name=’Nepotism Inc.’
WHERE client_name LIKE ‘ Nepotism Inc.’;

Check by typing: SELECT client_name FROM clients;
6. View client phone numbers:
SELECT client_phone FROM clients;
7. View all the are codes of client phone numbers:
SELECT RIGHT(LEFT(client_phone,4),3) FROM clients
WHERE client_phone IS NOT NULL;

This query retrieves the four left most characters, then strips out the last three right most. Note that the SUBSTRING function would also work here. Type this:
SELECT SUBSTRING(client_phone,2,3) FROM clients
WHERE client_phone IS NOT NULL;
8. Find the longest expense category name, and make the results upper case:
SELECT LENGTH(expense_category), UPPER(expense_category) FROM expense_categories
ORDER BY LENGTH(expense_category) DESC;
9. To concatenate all of the client address information as one value, and display it as an alias:
SELECT client_name, CONCAT(client_street, ‘, ‘, client_city, ‘, ‘,client_state, ‘, ‘,client_zip)
AS address FROM clients;
10. Show every expense along with its description and category, using an inner join:
SELECT expense_amount, expense_date, CONCAT(expense_category, ‘: ‘, expense_description)
FROM expenses, expense_categories
WHERE expenses.expense_category_id = expense_categories.expense_category_id;
11. Show the ten most expensive invoices, along with the client name and identification number:
SELECT invoices.*, CONCAT(client_name, ‘ – ‘, clients.client_id) AS client_info FROM invoices
LEFT JOIN clients USING (client_id) ORDER BY invoice_amount DESC LIMIT 10;

Uses a left join, orders by invoice_amount, and limits results to 10 records.
12. — TO simplify the query in step 10 using aliases for table names:
SELECT expense_amount, expense_date, CONCAT(expense_category, ‘: ‘, expense_description)
FROM expenses AS e, expense_categories AS e_c
WHERE e.expense_category_id = e_c.expense_category_id;
13. To display invoices by date, formatting the amounts as dollars:
SELECT *, CONCAT(‘$’, FORMAT(invoice_amount, 2)) AS amount
FROM invoices ORDER BY invoice_date ASC;
14. To round each expense to the nearest dollar:
SELECT ROUND(expense_amount), expense_amount FROM expenses;
15. Retrieve all client names in a random order:
SELECT client_id, client_name FROM clients ORDER BY RAND();

Then run it again, and notice the difference…

SELECT client_id, client_name FROM clients ORDER BY RAND();
A use of this might be for showing random advertising banners on a website.
16. To show every invoice billed in April:
SELECT * FROM invoices WHERE MONTH(invoice_date) = 4;
17. Show the current date and time (according to MySQL):
SELECT CURDATE(), CURTIME();

Note, this is dependant on the System Clock being correct.
18. Display every invoice amount and client name billed in the last six months:
SELECT CONCAT(‘$’, FORMAT(invoice_amount, 2)) AS amount, client_name FROM invoices, clients
WHERE (invoices.client_id = clients.client_id) AND
(invoice_date BETWEEN SUBDATE(CURDATE(), INTERVAL 6 MONTH) AND CURDATE());

This is a join on the client’s name and invoice amount, invoice amount is converted to a more readable format. BETWEEN and the date functions specify a range between 6 months ago and today.
19. To return the current date and time, in a readable format:
SELECT DATE_FORMAT(NOW(), ‘%M %e, %Y – %l:%i’);
20. To return the current time in 24 hour notation:
SELECT TIME_FORMAT(CURTIME(), ‘%T’);
21. Select every expense, ordered by date and amount, formatting the date as Weekday Day Month Year.
SELECT DATE_FORMAT(expense_date, ‘%a %b %e %Y’)
AS the_date, CONCAT(‘$’, FORMAT(expense_amount, 2))
AS amount
FROM expenses
ORDER BY expense_date ASC, expense_amount DESC;

MySQL I : Update and Delete

1. The structure of and the information in tables changes, you need to be able to update them.
2. Select a record:
SELECT client_street, client_city, client_state, client_zip FROM clients
WHERE client_id = 3;
3. Perhaps the address has changed. To update this field:
UPDATE clients SET client_street = ‘1313 Blackbird Lane’, client_city=’Castle Rock’, client_state=’ME’, client_zip = ‘13131’ where client_id = 3;
4. Check your results:
SELECT client_street, client_city, client_state, client_zip FROM clients
WHERE client_id = 3;
5. To delete records, like those associated with travel expenses, we may need to do some preparation:
SELECT * FROM expense_categories
ORDER BY expense_category ASC;

Make a note of what expense_category_id ‘s are represented by travel categories: 1, 2, 18
6. Delete these three records. Type:
DELETE FROM expense_categories
WHERE
expense_category_id = 1 OR
expense_category_id = 2 OR
expense_category_id = 18;
7. Check your results:
SELECT * FROM expense_categories
ORDER BY expense_category ASC;
8. Create a new travel category:
INSERT INTO expense_categories VALUES (NULL, ‘Travel’);
9. Check your results:
SELECT * FROM expense_categories
ORDER BY expense_category ASC;

Note that AUTO_INCREMENT primary key values are not replaced. Every number, including deleted ones, should remain unique.
10. Update the “expenses” table to reflect these changes:
UPDATE expenses SET
expense_category_id = 21 WHERE
expense_category_id = 1 OR
expense_category_id = 2 OR
expense_category_id = 18;
11. Check your results:
SELECT * FROM expenses;
12. This is tricky, and it’s easy to forget what is associated. Watch out or you will loose referential integrity. This will be a great reason to upgrade to version 4 of MySQL when it stabilized—cascading deletes!
13. To ALTER a table column structure
ALTER TABLE clients
CHANGE COLUMN contact_name contact_first_name VARCHAR(15);

This changed the contact_name field which was originally VARCHAR(40)

Check by typing:
SELECT contact_name FROM clients; an error is returned.
SELECT contact_first_name FROM clients; names are returned, but still lumped together.
14. Create a new contact_last_name column:
ALTER TABLE clients ADD COLUMN contact_last_name VARCHAR(25);
15. View and update the contact name information:
SELECT client_id, contact_first_name, contact_last_name FROM clients
WHERE contact_first_name IS NOT NULL;

UPDATE clients SET contact_first_name = ‘Jane’, contact_last_name = ‘Doe’
WHERE client_id = 1;

Then check your update:
SELECT client_id, contact_first_name, contact_last_name FROM clients
WHERE contact_first_name IS NOT NULL;

Note: You would need to do this eight more times to update all the name information. Script this!

Note: When adding a new column to a table you can use the AFTER directive.
Example (don’t type this):
ALTER TABLE clients
ADD COLUMN contact_last_name VARCHAR(25) AFTER contact_first_name;

Note: Changing column order is not allowed. You need to dump table contents, drop the table, recreate with the new column order, edit the data to reflect this change, and re-import.

MySQL I : Joins

1. You can retrieve related information that exists by using the JOIN and “dot” syntax. This is the MySQL equivalent of a primary key – foreign key relationship. As of MySQL 3.23 there are no permanently designated foreign keys (unlike Access or Oracle). This should change with MySQL 4.
2. For example, to retrieve (by Inner Join) invoice amount, date, and client names for every invoice, type:
SELECT invoice_amount, invoice_date, client_name FROM invoices, clients
WHERE invoices.client_id = clients.client_id;
3. To retrieve expense category, expense date, and expense amount using an Inner Join:
SELECT expense_category, expense_amount, expense_date FROM expense_categories, expenses
WHERE expense_categories.expense_category_id = expenses.expense_category_id;

Note: the order that you list the tables in does not matter; the end result is the same.
4. To retrieve all client names and invoices for those clients using an Outer Join:
SELECT client_name, invoice_id, invoice_amount, invoice_date, invoice_description
FROM clients
LEFT JOIN invoices USING (client_id);
5. Retrieve all the invoice ID’s, amounts, dates, and descriptions, and the corresponding client name using an Outer Join:
SELECT client_name, invoice_id, invoice_amount, invoice_date, invoice_description
FROM invoices
LEFT JOIN clients USING (client_id);

This only differs from step 4 in that ‘clients’ is being joined to invoices rather than vice versa. One fewer record is returned “Something Clever” and “MiddeEarth Software”, which have no invoice records, i.e. ‘invoice_id’.
6. Here’s a particularly complex query, which sums up the information you’ve learned so far:
SELECT expense_amount, expense_description FROM expenses, expense_categories
WHERE expenses.expense_category_id = expense_categories.expense_category_id AND
expense_category = ‘Sand Paper’
ORDER BY expense_amount DESC LIMIT 2;

Note: a statement this complex might be best run from a script, especially if it is a query that you run often. As you write scripts save them for later use!

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.

MySQL I : Creating Databases and Tables

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.

Database Design and Data Types

1. For this exercise, find paper and pen. You will design a database on paper. This database is a simple catalog that will be used to keep track of product, customers, and their orders.
2. The database should have the following characteristics:

– A way to store information about many individual customers, including a unique identifier, last name, first name, email address, street address, city, state, zip code, day phone, evening phone, and fax.

– A way to store information about many products including a unique product identifying number, a product name, a product description, product manufacturer, price, and a picture of the product.

– A way to store information about an order including a unique order number, a unique customer identifier, how many items were ordered, the total cost, what shipping method was used, the cost of shipping, tax, the date the order was placed, the date the order was shipped.
3. Draw a diagram of each of these tables, with each category of information named.
4. Normalize, using primary key and foreign key relationships. Use one-to-many relationships where possible.
5. Determine the data type (number, text string, money, date, etc…) of each category of information. Write it next to the category name.
6. Is any critical information missing from the information schema described? Discuss with the class. Hint– a lot is missing!
7. What tools might you use to formalize and improve upon this manual modeling process? Discuss with the class.

1. Return to the design you created in the last exercise. Open your text to page 57. You may need to redraw your design for this exercise.
2. Give proper field names to each item in your design. Tables should use a plural name. Field names should be descriptive, use singular alphanumeric character names, lowercase, with no spaces (use the underscore). Primary and foreign keys columns should end with “_id” (no quotes). Names should be unique except for keys.
3. Determine the data type and length (if applicable) for each category of information in your database, based on the table on page 57.

MySQL I : Creating Users

1. In this exercise you will create users other than root for working with databases, and grant them specific privileges. This provides added security against unwanted intruders and (more importantly) against your own goof-ups.
2. Log into the MySQL Monitor client: mysql –u root –p
3. Create two new databases:
CREATE DATABASE alpacas;
4. Create a user that has administrative privileges on the ‘alpacas’ database:
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, INDEX, FILE ON alpacas.* TO llama@localhost IDENTIFIED BY “camel”;
5. What might be wrong with the following commands?
GRANT SELECT ON *.* TO llama IDENTIFIED BY “camel”;
or
GRANT ALL ON *.* TO llama IDENTIFIED BY “camel”;
6. Type: FLUSH PRIVILEGES;
IMPORTANT! This applies the changes that you made and is required. MySQL will not enable your changes without this step.
An alternate way to flush privileges is to exit the client and type: mysqladmin –u root –p reload
7. Quit MySQL: quit
8. Test the new llama account: mysql –u llama –p
Then enter the password: camel
You should enter the client.
9. Exit the MySQL client.
10. Note that the root administrator can revoke privileges as well. The syntax is:
REVOKE <privileges> ON <database.table> FROM <user@location>;

MySQL I : Using the MySQL Client

1. Open a command shell and type: cd c:\mysql\bin (or on Linux: cd /usr/local/mysql/bin).

2. Type: mysql –u root –p. Enter your root password (mypass), press Enter and you are in the MySQL Monitor client.

3. There is an additional argument with which you can determine the host (IP or domain) MySQL that you want to enter. For example: mysql –u root –p –h 127.0.0.1 This allows you to log into remote MySQL servers, if you have been given permission to do so.

4. Select which database that you want to use. Type: USE test;

Note that every command-line directive that you type in the client ends with a semi-colon (;).

5. USE is a directive that tells MySQL which, of possibly many, database you want to work with. ‘test‘ is one of the default databases that comes with MySQL. If you know in advance what database you want to use, you can define this when entering MySQL: mysql –u root –p test

Try this. Type: Exit

You will exit the client.

Quit is an alternative to exit. Note that neither Exit or Quit require a semi-colon. These are the two exceptions to the rule.

6. Renter the client, defining the database upon login.

7. You can see the command line options in the client by typing: \h

8. You can use the up and down arrow keys to scroll through previous commands.

9. On Linux/UNIX Ctrl+A moves to the beginning of the line.
Ctrl+E moves to the end of the line (like in the EMACS text editor).

10. If you are typing a very long line, continue the line by typing \c and pressing Enter.

11. MySQL command line prompts are:

mysql> Ready.
-> Continuing a command.
‘> Need to finish a single quote.
“>Need to finish a double quote.

12. Exit MySQL.