🎯 SQL Tutorial: A Deep Dive into MariaDB

Welcome to our comprehensive guide on SQL, focusing on the popular open-source database management system, MariaDB! Whether you're a beginner or an intermediate learner, we've got you covered. Let's get started!

📝 What is MariaDB?

MariaDB is a community-developed, open-source relational database management system that is compatible with MySQL and the official MySQL variant. It's a powerful tool for managing data and is widely used in various projects.

💡 Pro Tip:

Why use MariaDB?

It's free and open-source
Offers more features than MySQL
It's a drop-in replacement for MySQL
Compatible with most MySQL clients

🎯 Understanding SQL

SQL (Structured Query Language) is a standard language for managing and manipulating databases. It allows you to create, modify, and query databases, making it essential for developers and data analysts.

📝 Basic SQL Commands

Creating a Database

sql

CREATE DATABASE your_database;

Creating a Table

sql

CREATE TABLE your_table (
  id INT PRIMARY KEY,
  name VARCHAR(255),
  age INT
);

💡 Pro Tip:

PRIMARY KEY ensures each row in the table is unique.
VARCHAR(255) is used for string data up to 255 characters.
INT is used for integer data.

🎯 Inserting Data

To insert data into a table, use the INSERT INTO command:

sql

INSERT INTO your_table (id, name, age) VALUES (1, 'John', 30);

📝 Retrieving Data

To retrieve data from a table, use the SELECT command:

sql

SELECT * FROM your_table;

🎯 Updating Data

To update data in a table, use the UPDATE command:

sql

UPDATE your_table SET age = 31 WHERE id = 1;

📝 Deleting Data

To delete data from a table, use the DELETE command:

sql

DELETE FROM your_table WHERE id = 1;

💡 Pro Tip:

Always be careful when using the DELETE command.

🎯 Advanced SQL

Joins

Joins allow you to combine rows from two or more tables based on a related column:

sql

SELECT users.name, orders.order_date
FROM users
JOIN orders ON users.id = orders.user_id;

Stored Procedures

Stored Procedures are precompiled collections of SQL statements that can be executed as a single unit:

sql

CREATE PROCEDURE update_user(IN id INT, IN new_age INT)
BEGIN
  UPDATE users SET age = new_age WHERE id = id;
END;

📝 Practical Examples

Example 1: Creating a User and Order Database

sql

CREATE DATABASE users_and_orders;

USE users_and_orders;

CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(255),
  email VARCHAR(255)
);

CREATE TABLE orders (
  id INT PRIMARY KEY,
  user_id INT,
  order_date DATE,
  total_amount DECIMAL(10,2)
);

Example 2: Inserting Data and Performing Queries

sql

INSERT INTO users (id, name, email) VALUES (1, 'John Doe', 'john@example.com');
INSERT INTO orders (id, user_id, order_date, total_amount) VALUES (1, 1, '2022-01-01', 100.00);

SELECT * FROM users;
SELECT * FROM orders;

SELECT u.name, o.order_date, o.total_amount FROM users AS u
JOIN orders AS o ON u.id = o.user_id;

We hope you enjoyed learning about MariaDB and SQL! Stay tuned for more tutorials on CodeYourCraft. Happy coding! 💻🎉