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
Answer
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