Working with MySQL

MySQL, SQL Server and others are all common transactional database systems. VS Code has extensions for most of these. In this exercise, you'll connect to a MySQL database, execute queries and view the results in VS Code.

Exercise

• Connect to the "mysql" container with the MySQL extension

  • username: root

  • password: example

• Execute the queries in the "mysql.sql" file

• View the query results

Answer

• Open up the Explorer View (Cmd/Ctrl + Shift + E)

• Notice that there is a MySQL view section

• Click the "+" button to create a new connection

  • Server Name: mysql

  • User name: root

  • Password: example

  • Port: Default

  • SSL: Leave Empty

• VS Code will now be connected to MySQL

• Right-click the "mysql" connection and select "New Query"

• Create a database called "Lamp"

CREATE DATABASE Lamp;

• Execute the query by opening the Command Palette (Cmd/Ctrl + Shift + P) and selecting "MySQL: Run MySQL Query"

• Create a new table in the MySQL Database called "Colors"

USE Lamp;

CREATE TABLE IF NOT EXISTS Colors (
    color_id INT AUTO_INCREMENT,
    color VARCHAR(255) NOT NULL,
    PRIMARY KEY (color_id)
)  ENGINE=INNODB;

• Press Ctrl + Opt/Alt + E to execute the query

• Right-click the MySQL connection and select "Refresh"

• Notice there is now a "Lamp" database with a "Colors" table

• Insert a record into the database

USE Lamp;

INSERT INTO Colors (color) VALUES("Blue")

• Execute the query with Ctrl + Opt/Alt + E

• Select everything from the "Colors" table

USE Lamp;

SELECT * FROM Colors

• View the results