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.
MySQL Queries with Examples
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
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)
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 product_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 category_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;
Inner Join Queries
Query 5: Inner Join between Users and Orders Tables
SELECT u.*, o.*
FROM users u
INNER JOIN orders o ON u.user_id = o.user_id;
Query 6: Inner Join between Products and Product_Categories Tables
SELECT p.*, pc.*
FROM products p
INNER JOIN product_categories pc ON p.product_id = pc.product_id;
Left Join Queries
Query 7: Left Join between Users and Orders Tables
SELECT u.*, o.*
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id;
Query 8: Left Join between Products and Product_Categories Tables
SELECT p.*, pc.*
FROM products p
LEFT JOIN product_categories pc ON p.product_id = pc.product_id;
Right Join Queries
Query 9: Right Join between Users and Orders Tables
SELECT u.*, o.*
FROM users u
RIGHT JOIN orders o ON u.user_id = o.user_id;
Query 10: Right Join between Products and Product_Categories Tables
SELECT p.*, pc.*
FROM products p
RIGHT JOIN product_categories pc ON p.product_id = pc.product_id;
Outer Join Queries
Query 11: Full Outer Join between Users and Orders Tables
SELECT u.*, o.*
FROM users u
FULL OUTER JOIN orders o ON u.user_id = o.user_id;
Query 12: Full Outer Join between Products and Product_Categories Tables
SELECT p.*, pc.*
FROM products p
FULL OUTER JOIN product_categories pc ON p.product_id = pc.product_id;
