# 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.

{% tabs %}
{% tab title="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
  {% endtab %}

{% tab title="Answer" %}

* Open up the Explorer View (**Cmd/Ctrl + Shift + E**)
* Notice that there is a MySQL view section

![](https://1151923643-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-LlvtEHKkq5bvzPq1pJ4%2F-Lm7CX3e4vvcaDmu9LTy%2F-Lm7ClkB-dIpY05Dr89r%2Fimage.png?alt=media\&token=e3b1957d-7929-4dd5-a836-a05dda823d2d)

* 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

![](https://1151923643-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-LlvtEHKkq5bvzPq1pJ4%2F-Lm7Co18zJLcV1Rz1ZMV%2F-Lm7D8vx-RYibhqApc7b%2Fimage.png?alt=media\&token=6ef6bd82-5382-4aac-a3eb-2b512909d0bd)

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

![](https://1151923643-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-LlvtEHKkq5bvzPq1pJ4%2F-Lm7DAhgPcMW0o0iqbW8%2F-Lm7DIyBxBW6MVOlSIvE%2Fimage.png?alt=media\&token=65766ffb-9df2-48da-bd34-cd1cc3b40212)

* 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"

![](https://1151923643-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-LlvtEHKkq5bvzPq1pJ4%2F-Lm7DAhgPcMW0o0iqbW8%2F-Lm7DuJHA2THA72idshQ%2Fimage.png?alt=media\&token=2cda31fb-82a3-42fc-b471-f14865d8f330)

* 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"

![](https://1151923643-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-LlvtEHKkq5bvzPq1pJ4%2F-Lm7DAhgPcMW0o0iqbW8%2F-Lm7ESasOLTw9guW_VQE%2Fimage.png?alt=media\&token=5a1a76ac-b348-4045-b314-ce32dbb0a0c1)

* 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

![](https://1151923643-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-LlvtEHKkq5bvzPq1pJ4%2F-Lm7F-ehFPhAeDvTxTC9%2F-Lm7FJlHBgSxRc574OP1%2Fimage.png?alt=media\&token=1fc5fe72-1481-4e7d-b87b-7bd86001170a)
{% endtab %}
{% endtabs %}