MySQL

Learn by doing!

MySQL Commands and Description

SELECT * FROM table_name;

Select all records from a table.

SELECT column1, column2 FROM table_name;

Select specific columns from a table.

SELECT DISTINCT column_name FROM table_name;

Select distinct values from a column.

SELECT * FROM table_name WHERE condition;

Select records with a specific condition.

SELECT * FROM table_name ORDER BY column_name;

Select records and order them by a column.

SELECT * FROM table_name LIMIT 10;

Select records and limit the result set.

INSERT INTO table_name (column1, column2) VALUES (value1, value2);

Insert a new record into a table.

UPDATE table_name SET column1 = value1 WHERE condition;

Update existing records in a table.

DELETE FROM table_name WHERE condition;

Delete records from a table.

CREATE TABLE new_table_name ( column1 datatype, column2 datatype );

Create a new table.

ALTER TABLE table_name ADD column_name datatype;

Add a new column to an existing table.

ALTER TABLE table_name MODIFY column_name new_datatype;

Modify the data type of a column.

ALTER TABLE table_name DROP COLUMN column_name;

Drop a column from a table.

DROP TABLE table_name;

Drop a table.

CREATE INDEX index_name ON table_name (column_name);

Create an index on a column.

SELECT * FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;

Select records from multiple tables using INNER JOIN.

SELECT * FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name;

Select records from multiple tables using LEFT JOIN.

SELECT * FROM table1 RIGHT JOIN table2 ON table1.column_name = table2.column_name;

Select records from multiple tables using RIGHT JOIN.

SELECT * FROM table1 FULL OUTER JOIN table2 ON table1.column_name = table2.column_name;

Select records from multiple tables using FULL OUTER JOIN.

SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name;

Group records and apply aggregate functions.

SELECT * FROM table_name WHERE condition1 AND condition2;

Select records based on multiple conditions.

SELECT * FROM table_name WHERE column_name LIKE 'pattern%';

Use LIKE operator for pattern matching.

SELECT * FROM table_name WHERE column_name IN (value1, value2, value3);

Use IN operator to match against multiple values.

SELECT * FROM table_name WHERE column_name BETWEEN value1 AND value2;

Use BETWEEN operator to select within a range.

SELECT column1 FROM table1 WHERE EXISTS (SELECT column2 FROM table2 WHERE condition);

Use EXISTS to check for the existence of rows.

Create Tables in MySQL

Note: These SQL statements creating the five tables: users, products, orders, categories, and product_categories.

Create a table for storing user information

CREATE TABLE users (
user_id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Create a table for storing product information

CREATE TABLE products (
product_id INT AUTO_INCREMENT PRIMARY KEY,
product_name VARCHAR(100) NOT NULL,
price DECIMAL(10, 2) NOT NULL,
stock_quantity INT NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Create a table for storing orders

CREATE TABLE orders (
order_id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT NOT NULL,
order_date DATE NOT NULL,
FOREIGN KEY (user_id) REFERENCES users(user_id),
FOREIGN KEY (product_id) REFERENCES products(product_id)
);

Create a table for storing categories

CREATE TABLE categories (
category_id INT AUTO_INCREMENT PRIMARY KEY,
category_name VARCHAR(50) NOT NULL
);

Create a table for associating products with categories (many-to-many relationship)

CREATE TABLE product_categories (
product_id INT NOT NULL,
category_id INT NOT NULL,
PRIMARY KEY (product_id, category_id),
FOREIGN KEY (product_id) REFERENCES products(product_id),
FOREIGN KEY (category_id) REFERENCES categories(category_id) );

Insert Data into the Tables in MySQL

Note: These SQL statements are inserting/adding data into the five tables: users, products, orders, categories, and product_categories.

Data for users table

INSERT INTO users (username, email) VALUES
('john_doe', 'john@example.com'),
('jane_smith', 'jane@example.com'),
('mike_jones', 'mike@example.com');
('alice_wong', 'alice@example.com'),
('bob_smith', 'bob@example.com'),
('emily_doe', 'emily@example.com');
('jane_smith', 'jane@example.com'),
('mike_jones', 'mike@example.com');
('alice_wong', 'alice@example.com'),

Data for products table

INSERT INTO products (product_name, price, stock_quantity) VALUES

('Laptop', 999.99, 10),

('Smartphone', 499.99, 20),

('Headphones', 99.99, 30);

('Tablet', 299.99, 15),

('Smartwatch', 199.99, 25),

('Camera', 599.99, 10);

Data for categories table

INSERT INTO categories (category_name) VALUES
('Electronics'),
('Clothing'),
('Books');
('Home Appliances'),
('Sports Equipment'),
('Toys');

Data for orders table

INSERT INTO orders (user_id, product_id, quantity, order_date) VALUES
(1, 1, 2, '2024-02-27'),
(2, 2, 1, '2024-02-26'),
(3, 3, 3, '2024-02-25');
(1, 4, 1, '2024-02-28'),
(2, 5, 2, '2024-02-28'),
(3, 6, 1, '2024-02-27');

Data for product_categories table (associating products with categories)

INSERT INTO product_categories (product_id, category_id) VALUES
(1, 1), -- Laptop belongs to Electronics category
(2, 1), -- Smartphone belongs to Electronics category
(3, 2); -- Headphones belong to Clothing category
(4, 1), -- Tablet belongs to Home Appliances category
(5, 2), -- Smartwatch belongs to Sports Equipment category
(6, 3); -- Camera belongs to Toys category

Run Queries

Note: These SQL statements are viewing the date available in the five tables: users, products, orders, categories, and product_categories.

User Table Queries

Query 1: Select all users

SELECT * FROM users;

Query 2: Select users with specific conditions (e.g., user_id = 1)

SELECT * FROM users
WHERE user_id = 1;

Query 3: Update a user's email

UPDATE users
SET email = 'new_email@example.com'
WHERE user_id = 2;

Query 4: Delete a user

DELETE FROM users
WHERE user_id = 3;

Products Table Queries

Query 1: Select all products

SELECT * FROM products;

Query 2: Select products with specific conditions (e.g., price less than 500)

SELECT * FROM products
WHERE price < 500;

Query 3: Update a product's price

UPDATE products
SET price = 399.99
WHERE product_id = 1;

Query 4: Delete a product

DELETE FROM products
WHERE products_id = 3;

Categories Table Queries

Query 1: Select all categories

SELECT * FROM categories;

Query 2: Select a specific category (e.g., category_id = 2)

SELECT * FROM categories
WHERE category_id = 2;

Query 3: Update a category's name

UPDATE categories
SET category_name = 'Outdoor Gear'
WHERE category_id = 3;

Query 4: Delete a category

DELETE FROM categories
WHERE categories_id = 3;

Orders Table Queries

Query 1: Select all orders

SELECT * FROM orders;

Query 2: Select orders with specific conditions (e.g., order_date between two dates)

SELECT * FROM orders
WHERE order_date BETWEEN '2024-02-26' AND '2024-02-28';

Query 3: Update an order's quantity

UPDATE orders
SET quantity = 5
WHERE order_id = 1;

Query 4: Delete an order

DELETE FROM orders
WHERE order_id = 2;

Product_Categories Table Queries

Query 1: Select all associations between products and categories

SELECT * FROM product_categories;

Query 2: Select products belonging to a specific category (e.g., category_id = 1)

SELECT p.* FROM products p
JOIN product_categories pc
ON p.product_id = pc.product_id
WHERE pc.category_id = 1;

Query 3: Insert a new association

INSERT INTO product_categories (product_id, category_id)
VALUES (4, 1);

Query 4: Delete an association

DELETE FROM product_categories
WHERE product_id = 6 AND category_id = 3;

Run Join Queries of the Tables

Note: These SQL statements are for joining the tables.

Joining Users and Orders Tables

Query 1: Retrieve user information along with their orders

SELECT u.*, o.order_id, o.product_id, o.quantity, o.order_date
FROM users u
JOIN orders o
ON u.user_id = o.user_id;

Joining Products and Categories Tables

Query 2: Retrieve product information along with their categories

SELECT p.*, c.category_name FROM products p
JOIN product_categories pc
ON p.product_id = pc.product_id
JOIN categories c
ON pc.category_id = c.category_id;

Joining Users, Orders, and Products Tables

Query 3: Retrieve order details along with user and product information

SELECT u.username, p.product_name, o.quantity, o.order_date FROM users u
JOIN orders o
ON u.user_id = o.user_id
JOIN products p
ON o.product_id = p.product_id;

Joining Products, Product_Categories, and Categories Tables

Query 4: Retrieve product information along with their associated categories

SELECT p.product_name, c.category_name FROM products p
JOIN product_categories pc
ON p.product_id = pc.product_id
JOIN categories c
ON pc.category_id = c.category_id;