Complete MySQL + VS Code + SQLTools Study
Guide
Chapter 1: What is a Database?
A database is an organized collection of information. Think of it as a digital filing cabinet. MySQL is
a Database Management System (DBMS) that stores and retrieves information.
Chapter 2: Installing and Verifying MySQL
Use mysql --version to verify installation. Use sudo systemctl status mysql to check if the server is
running. The server must be active before applications can connect.
Chapter 3: Understanding MySQL Users
MySQL uses accounts. Ubuntu often configures root with auth_socket, which means sudo mysql
works even when mysql -u root -p does not.
Chapter 4: VS Code and SQLTools
VS Code is the editor. SQLTools is an extension that connects VS Code to databases. Drivers
allow SQLTools to communicate with specific database engines such as MySQL.
Chapter 5: SQLTools Driver Setup
Install SQLTools and the MySQL/MariaDB/TiDB driver. Configure localhost, port 3306, database
name, username, and password.
Chapter 6: Databases, Tables and Rows
A database contains tables. A table contains rows and columns. Rows represent records. Columns
represent attributes.
Chapter 7: SQL Naming Conventions
Use snake_case for databases, tables, and columns. Example: book_shop, customer_orders,
first_name.
Chapter 8: Creating Databases
CREATE DATABASE book_shop; creates a database. SHOW DATABASES; lists databases.
Chapter 9: Creating Tables
Tables define structure. Example columns include book_id, title, and author.
Chapter 10: Primary Keys
A primary key uniquely identifies each row. Common examples include id or book_id.
Chapter 11: INSERT
INSERT adds new data into tables.
Chapter 12: SELECT
SELECT retrieves data. Example: SELECT * FROM books;
Chapter 13: WHERE
WHERE filters rows. Example: SELECT * FROM books WHERE publication_year > 2020;
Chapter 14: ORDER BY
ORDER BY sorts results ascending or descending.
Chapter 15: UPDATE
UPDATE modifies existing records.
Chapter 16: DELETE
DELETE removes records from a table.
Chapter 17: Aggregate Functions
COUNT, AVG, SUM, MIN, and MAX summarize data.
Chapter 18: String Functions
CONCAT, SUBSTRING, REPLACE, REVERSE, UPPER, LOWER, and CHAR_LENGTH
manipulate text.
Chapter 19: Relationships
One-to-one, one-to-many, and many-to-many relationships connect tables.
Chapter 20: Foreign Keys
Foreign keys enforce relationships between tables.
Chapter 21: JOINs
INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN combine data from multiple tables.
Chapter 22: Security
Use strong passwords, avoid using root for applications, and grant only required permissions.
Chapter 23: Troubleshooting
Access denied errors usually mean incorrect credentials, missing permissions, or user configuration
problems.
Chapter 24: Beginner Exercises
Create a database, create a books table, insert records, and run SELECT queries.
Chapter 25: Quiz
What is a primary key? What does SELECT do? What is a foreign key? Why use snake_case?
Chapter 26: Explain Like You're Five
MySQL is a toy box. Tables are shelves. Rows are toys. Columns describe the toys. VS Code is
your desk. SQLTools is the walkie-talkie that talks to the toy box.
Useful Commands
mysql --version
sudo systemctl status mysql
sudo mysql
SHOW DATABASES;
SHOW TABLES;
SELECT * FROM books;