MySQL/Introduction
Appearance
< MySQL
Readings
[edit | edit source]Activities
[edit | edit source]MySQL Environment
[edit | edit source]Establish a MySQL environment using one of the following:
Docker Playground
[edit | edit source]Docker Playground is a free online Docker environment. It requires no installation or configuration.
- Use Play with Docker. Create an account and/or log in.
- Start an interactive session and add a new instance.
- In the terminal window, enter the following command:
docker run --name mysql-server -e MYSQL_ROOT_PASSWORD=secret -d mysql
- To connect to the mysql-server:
docker exec -it mysql-server bash
mysql -uroot -p
Entersecret
for the root password when prompted.
MySQL in Docker
[edit | edit source]You can use your own Docker environment to run MySQL.
- Install Docker Desktop or the Docker Engine.
- In a terminal window, enter the following commands:
docker run --name mysql-server -e MYSQL_ROOT_PASSWORD=secret -d mysql
- To connect to the mysql-server:
docker exec -it mysql-server bash
mysql -uroot -p
Entersecret
for the root password when prompted.
Install MySQL
[edit | edit source]Install MySQL on your own system.
- Review MySQL: Installation Guide
- Download and install MySQL.
- Use the following terminal command to access the MySQL command interface:
mysql -uroot -p
MySQL Activities
[edit | edit source]Create a Database
[edit | edit source]- Use the following SQL command to create a temperature database:
CREATE DATABASE Temperature;
- Use the following SQL command to show existing databases:
SHOW DATABASES;
- Use the following SQL command to open the Temperature database:
USE Temperature;
- 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));
- Use the following SQL command to show existing tables in the database:
SHOW TABLES;
- 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
[edit | edit source]- Use the following SQL command to query the Countries table:
SELECT * FROM Countries;
Insert a Record
[edit | edit source]- 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);
- Use the following SQL command to display the inserted record:
SELECT * FROM Countries;
Update a Record
[edit | edit source]- 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';
- Use the following SQL command to display the updated record:
SELECT * FROM Countries;
Delete a Record
[edit | edit source]- Use the following SQL command to delete a record from the Countries table:
DELETE FROM Countries
WHERE Country = 'United States of America';
- Use the following SQL command to display the remaining records:
SELECT * FROM Countries;
Delete All Records
[edit | edit source]- Use the following SQL command to delete all records from the Countries table:
DELETE FROM Countries;
- Use the following SQL command to display the empty table:
SELECT * FROM Countries;
Remove a Table
[edit | edit source]- Use the following SQL command to remove the Countries table:
DROP TABLE Countries;
- Use the following SQL command to verify that the table was removed:
SHOW TABLES;
Drop a Database
[edit | edit source]- Use the following SQL command to show existing databases:
SHOW DATABASES;
- Use the following SQL command to remove the temperature database:
DROP DATABASE Temperature;
- Use the following SQL command to show existing databases:
SHOW DATABASES;