MySQL/Introduction

Readings

• Wikipedia: MySQL

Activities

MySQL Environment

Establish a MySQL environment using one of the following:

• Docker Playground
• MySQL in Docker
• Install MySQL

Docker Playground

Docker Playground is a free online Docker environment. It requires no installation or configuration.

1. Use Play with Docker. Create an account and/or log in.
2. Start an interactive session and add a new instance.
3. In the terminal window, enter the following command:
   • docker run --name mysql-server -e MYSQL_ROOT_PASSWORD=secret -d mysql
4. To connect to the mysql-server:
   • docker exec -it mysql-server bash
   • mysql -uroot -p
     Enter secret for the root password when prompted.

MySQL in Docker

You can use your own Docker environment to run MySQL.

1. Install Docker Desktop or the Docker Engine.
2. In a terminal window, enter the following commands:
   • docker run --name mysql-server -e MYSQL_ROOT_PASSWORD=secret -d mysql
3. To connect to the mysql-server:
   • docker exec -it mysql-server bash
   • mysql -uroot -p
     Enter secret for the root password when prompted.

Install MySQL

Install MySQL on your own system.

1. Review MySQL: Installation Guide
2. Download and install MySQL.
3. Use the following terminal command to access the MySQL command interface:
   • mysql -uroot -p

MySQL Activities

Create a Database

1. Use the following SQL command to create a temperature database:
   CREATE DATABASE Temperature;
2. Use the following SQL command to show existing databases:
   SHOW DATABASES;
3. Use the following SQL command to open the Temperature database:
   USE Temperature;
4. Use the following SQL command to create a Countries table:
   CREATE TABLE Countries(
ID INT NOT NULL AUTO_INCREMENT,
Country VARCHAR(255) UNIQUE NOT NULL,
Temperature FLOAT NOT NULL,
PRIMARY KEY (ID));
5. Use the following SQL command to show existing tables in the database:
   SHOW TABLES;
6. Use the following SQL command to insert records into the Countries table:
   INSERT INTO Countries (Country, Temperature)
VALUES('Bulgaria', 45.2),
('Canada', 45),
('Federated States of Micronesia', 36.1),
('Finland', 37.2),
('Germany', 40.3),
('Japan', 41),
('Marshall Islands', 35.6),
('Palau', 35),
('Turkmenistan', 50.1);

Query an MySQL Database

1. Use the following SQL command to query the Countries table:
   SELECT * FROM Countries;

Insert a Record

1. Use the following SQL command to insert a record into the Countries table:
   INSERT INTO Countries (Country, Temperature)
VALUES('United States of America', 56.7);
2. Use the following SQL command to display the inserted record:
   SELECT * FROM Countries;

Update a Record

1. Use the following SQL command to update a record in the Countries table:
   UPDATE Countries
SET Temperature = 56.5
WHERE Country = 'United States of America';
2. Use the following SQL command to display the updated record:
   SELECT * FROM Countries;

Delete a Record

1. Use the following SQL command to delete a record from the Countries table:
   DELETE FROM Countries
WHERE Country = 'United States of America';
2. Use the following SQL command to display the remaining records:
   SELECT * FROM Countries;

Delete All Records

1. Use the following SQL command to delete all records from the Countries table:
   DELETE FROM Countries;
2. Use the following SQL command to display the empty table:
   SELECT * FROM Countries;

Remove a Table

1. Use the following SQL command to remove the Countries table:
   DROP TABLE Countries;
2. Use the following SQL command to verify that the table was removed:
   SHOW TABLES;

Drop a Database

1. Use the following SQL command to show existing databases:
   SHOW DATABASES;
2. Use the following SQL command to remove the temperature database:
   DROP DATABASE Temperature;
3. Use the following SQL command to show existing databases:
   SHOW DATABASES;

See Also

• MySQL Tutorial]