Jump to content

MySQL/Introduction

From Wikiversity

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.

  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

[edit | edit source]

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

[edit | edit source]

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

[edit | edit source]

Create a Database

[edit | edit source]
  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

[edit | edit source]
  1. Use the following SQL command to query the Countries table:
    SELECT * FROM Countries;

Insert a Record

[edit | edit source]
  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

[edit | edit source]
  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

[edit | edit source]
  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

[edit | edit source]
  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

[edit | edit source]
  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

[edit | edit source]
  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

[edit | edit source]

References

[edit | edit source]