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