**Course Title:** Mastering Go: From Basics to Advanced Development **Section Title:** Data Persistence: Working with Databases **Topic:** Introduction to databases and SQL ### Introduction to Databases and SQL In this topic, we will introduce the fundamentals of databases and SQL, which are essential for data persistence in Go applications. We will explore the basics of databases, SQL, and how to interact with them using Go. ### What is a Database? A database is a collection of organized data that is stored in a way that allows for efficient retrieval and manipulation. Databases can be used to store a wide range of data, from simple key-value pairs to complex relationships between multiple data entities. ### Types of Databases There are several types of databases, including: * **Relational databases** (e.g., MySQL, PostgreSQL): Organize data into tables with well-defined schemas. * **NoSQL databases** (e.g., MongoDB, Cassandra): Store data in flexible, schema-less formats. * **Key-value databases** (e.g., Redis): Store simple key-value pairs. ### What is SQL? SQL (Structured Query Language) is a standard language for managing relational databases. It allows you to create, modify, and query database schema and data. SQL is used to perform various operations, such as: * **Creating tables and indexes**: Define the structure of your database schema. * **Inserting, updating, and deleting data**: Manipulate data in your database. * **Querying data**: Retrieve specific data from your database using SELECT statements. ### Basic SQL Concepts Here are some basic SQL concepts: * **Tables**: Organize data into rows and columns. * **Columns**: Represent individual fields or attributes of data. * **Rows**: Represent individual records or entries in a table. * **Primary key**: Uniquely identifies each row in a table. * **Foreign key**: References the primary key of another table. ### SQL Queries A SQL query is a request to retrieve or manipulate data in a database. SQL queries can be divided into several categories: * **DML (Data Manipulation Language)**: INSERT, UPDATE, DELETE * **DQL (Data Query Language)**: SELECT * **DDL (Data Definition Language)**: CREATE, ALTER, DROP ### Example SQL Queries Let's consider a simple example database with two tables: `users` and `orders`. ```sql -- Create the users table CREATE TABLE users ( id INT PRIMARY KEY, name VARCHAR(255), email VARCHAR(255) ); -- Create the orders table CREATE TABLE orders ( id INT PRIMARY KEY, user_id INT, order_date DATE, FOREIGN KEY (user_id) REFERENCES users(id) ); -- Insert data into the users table INSERT INTO users (id, name, email) VALUES (1, 'John Doe', 'john@example.com'), (2, 'Jane Doe', 'jane@example.com'); -- Insert data into the orders table INSERT INTO orders (id, user_id, order_date) VALUES (1, 1, '2022-01-01'), (2, 1, '2022-01-15'), (3, 2, '2022-02-01'); -- Query the users table to retrieve all records SELECT * FROM users; -- Query the orders table to retrieve all records for a specific user SELECT * FROM orders WHERE user_id = 1; ``` ### Key Takeaways * Databases are collections of organized data that can be stored in various formats. * SQL is a standard language for managing relational databases. * SQL queries can be used to create, modify, and query database schema and data. ### What's Next? In the next topic, we will explore the `database/sql` package for interacting with databases in Go. We will cover how to connect to a database, execute SQL queries, and handle errors. **Additional Resources** * [SQL Tutorial](https://www.w3schools.com/sql/): A comprehensive SQL tutorial with examples and exercises. * [Database Fundamentals](https://www.tutorialspoint.com/database_fundamentals/index.htm): A tutorial on database fundamentals, including data models and schema design. **Leave a Comment or Ask for Help** Please leave a comment if you have any questions or need help with any of the concepts covered in this topic.

-- Create the users table
CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(255),
  email VARCHAR(255)
);

-- Create the orders table
CREATE TABLE orders (
  id INT PRIMARY KEY,
  user_id INT,
  order_date DATE,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Insert data into the users table
INSERT INTO users (id, name, email) VALUES
  (1, 'John Doe', 'john@example.com'),
  (2, 'Jane Doe', 'jane@example.com');

-- Insert data into the orders table
INSERT INTO orders (id, user_id, order_date) VALUES
  (1, 1, '2022-01-01'),
  (2, 1, '2022-01-15'),
  (3, 2, '2022-02-01');

-- Query the users table to retrieve all records
SELECT * FROM users;

-- Query the orders table to retrieve all records for a specific user
SELECT * FROM orders WHERE user_id = 1;