Olivia A. Gallucci

What is MySQL?

MySQL is a free and open-source relational database management system. This tutorial shows how to configure and modify a MySQL book database. If you are only interested in the sample MySQL book database, here is a link the downloadable file on my GitHub.

Sample MySQL Book Database

This database is based on createBOOK.sql by RIT Professor James Habermas. The original database, not included in this article, can be used with his permission. The modified database, included in this article, can be used with Gallucci’s and Professor Habermas’ permission.

Link to downloadable file.

Rough diagram

Red lines mean I am unsure of what syntax to use. That is what makes this diagram “rough.”

Key

Key source: Professor Jim Habermas

How to use MySQL?

What is verbose?

MySQL Shell logs information and can display it on the console. MySQL stores logs within an application log file. However, many people like to use it for debugging. As a result, MySQL developed a logging feature known as Verbose, where logs are displayed on the console and stored in the application file. Verbose can be activated using a command line option. 

Starting MySQL

MacOS

With verbose:

/usr/local/mysql/bin/mysql -u root -p -v

Without verbose:

/usr/local/mysql/bin/mysql -u root -p

Create alias: 

cd ~
open .zshrc

Add the following to ~/.zshrc:

# MySQL shortcuts
alias mysqlv="/usr/bin/mysql -u root -p -v"
alias mysqlw="/usr/bin/mysql -u root -p"

Close ~/.zshrc. Then, update your ~/.zshrc configuration source:

source .zshrc

Now, the mysqlv will run mysql with verbose, while mysqlw will run it without verbose. 

Windows

Windows is sensitive to capitalization! Thus, select may need to be SELECT in order to work properly.

Windows Menu → MySQL → MySQL Command Line Client

If prompted for a password, use the same password that was used to set up the MySQL server. 

Linux

Creating an alias:

cd ~
nano .bashrc

Add the following to ~/.bashrc:

# MySQL shortcuts
alias mysqlv='/usr/bin/mysql -u root -p -v'
alias mysqlw='/usr/bin/mysql -u root -p'

Close ~/.bashrc. Then, update your ~/.bashrc configuration source:

source ~/.bashrc

Now, the mysqlv will run mysql with verbose, while mysqlw will run it without verbose. 

Exiting MySQL

Linux and MacOS:

mysql > exit 

Windows, click the “x” in the top, right-hand corner. 

Database statements

Source

SOURCE [full_path_to_createBOOK.sql]; 

Show

SHOW databases;

Use

Source what you want to work with:

USE [DATABASE_NAME];

For this example, we want to type:

USE book; 

Tee the file

This step is unnecessary but can be helpful for debugging.

Create the Tee file (note that there is *no* ending semicolon):

TEE D:\full_path_fo_file\tee_file_name.txt

If the .txt file specified does not exist, the TEE command will create it for you. 

Stop the tee file:

notee

Comments

This step is unnecessary but can be helpful for debugging.

-- bleh blah blah 

Table statements

Show

Show all tables:

SHOW tables;

Describe

Shows the format of the table. 

DESC [table_name];

Select

All columns

* selects all fields

Examples

SELECT * FROM [table_name];
SELECT * FROM [table_name]\G

Some columns

SELECT [field] FROM [table_name];

SELECT ISBN FROM book;

Sorting

SELECT [field]
FROM [table_name]
ORDER BY {Column, Numerical Position, Alias Columns}[ASC | DESC]

SELECT ISBN, Category FROM book ORDER BY Category ASC;

Select books that have not been purchased: 

SELECT * FROM inventory WHERE Purchased=0;

Joins

Learn more about join.

Examples

See price and title of inventory item:

SELECT title, price FROM book JOIN inventory USING(ISBN) JOIN bookcondition USING(Ranks);

See book ranking:

SELECT name AS reviewerName, title, rating FROM book 
JOIN bookreview USING(ISBN) 
JOIN reviewer USING(reviewerID);

Insert

Insert allows you to add new information to a table. In order to add information properly, you need to follow the table structure or specify the values you want to add. 

Example

CREATE TABLE team5 (
    lastName VARCHAR(25) NOT NULL PRIMARY KEY, 
    gpa DECIMAL(3, 2) NULL, 
    age int NULL, 
    uid int NOT NULL AUTO_INCREMENT=1000000
) ENGINE=InnoDB DEFAULT CHARSET=utf8; 

Important notes: Do not specify values for your auto increment fields.

Add the values:

 - - full table structure 
mysql > INSERT INTO team5 VALUES("smith", 4.00, 19); 
 - - specify values 
mysql > INSERT INTO team5 (lastName, age) VALUES("johnson", 22);
 - - full table structure 
mysql > INSERT INTO team5 VALUES("doe", 3.87, 20);

Resulting table is:

When should I use insert? 

When the store gets new inventory or when a user returns a book. Add a new user. When a user purchases a book.

Add inventory: 

INSERT INTO inventory ('ISBN', 'Ranks') VALUES ('THE_ISBN', 'THE_RANK'); 

Here is how you can add a user:

INSERT INTO user (LastName, FirstName, PhoneNumber, Username, Password, Permissions) 
VALUES ('LAST_NAME', 'FIRST_NAME', 'PHONE_NUMBER', 'USERNAME', 'PASSWORD', 'PERMISSIONS'); 

Add to purchase log when a user purchases a book:

INSERT INTO purchaselog VALUES(UserID, InventoryID); 

INSERT INTO purchaselog VALUES(1000008, 1000002);

Update

Update allows you to change information in a table. Remember that if you do not use WHERE, every record will be updated. 

When should I use update?

You should use update when the store changes the price of a book rank or when an error was made to an entry (i.e., misspelled an author’s name). When a user purchases a book.

General format: 

UPDATE [table_name] SET [field]=[value] WHERE [field]=[value];

When user purchases a book:

UPDATE inventory SET Purchased=1 WHERE InventoryID=1000000;

When user returns a book:

UPDATE inventory SET Purchased=0 WHERE InventoryID=1000000;

Delete

Delete allows you to remove information in a table. Remove a user. 

When should I use delete? 

When a user purchases a book. Remove a user.

Delete book:

DELETE FROM inventory WHERE InventoryID="1000009";

Delete user:

DELETE FROM user WHERE UserID=00000000;

Full Sequence Examples

New user purchases book. The user’s UserID=1000008. The book the user is purchasing has InventoryID=1000002; 

-- user creates an account. Remember that the UserID is auto-increment. 
INSERT INTO user (LastName, FirstName, PhoneNumber, Username, Password) 
VALUES ('Gallucci', 'Olivia', '(123) 456-7890', 'oliviagallucci', 'password');
-- change the inventory from available to purchased 
UPDATE inventory SET Purchased=1 WHERE InventoryID=1000002;
-- insert a row in the purchase log
INSERT INTO purchaselog VALUES(1000008, 1000002);
-- remove the inventory 
DELETE FROM inventory WHERE InventoryID=1000002;

Further reading

If you enjoyed this post about creating a sample MySQL book database, consider reading my posts about Connecting Angular Apps to MySQL and Learning How to Build a Database Server on a Raspberry Pi 4.

This blog post was created as part of the Introduction to Software Development (SWEN 261; Spring 2022) class at the Rochester Institute of Technology.