Some command examples for managing tables…
#1. SHOW DATABASES - displays all databases in MySQL
SHOW DATABASES;
#2. CREATE DATABASE - creates a new database in MySQL
CREATE DATABASE my_database;
#3. USE DATABASE - selects a specific database to work with
USE my_database;
#4. SHOW TABLES - displays all tables in the current database
SHOW TABLES;
#5. CREATE TABLE - creates a new table in MySQL
CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50));
#6. INSERT INTO - inserts a new row into a table
INSERT INTO users (name) VALUES ('John');
#7. SELECT - retrieves data from a table
SELECT * FROM users;
#8. UPDATE - updates data in a table
UPDATE users SET name = 'Alice' WHERE id = 1;
#9. DELETE - deletes data from a table
DELETE FROM users WHERE id = 1;
#10. ALTER TABLE - modifies an existing table structure
ALTER TABLE users ADD email VARCHAR(50);
#11. DROP TABLE - deletes a table from the database
DROP TABLE users;
#12. DESC - describes the structure of a table
DESC users;
#13. COUNT - counts the number of rows in a table
SELECT COUNT(*) FROM users;
#14. MIN - finds the minimum value in a column
SELECT MIN(salary) FROM employees;
#15. MAX - finds the maximum value in a column
SELECT MAX(salary) FROM employees;
#16. AVG - calculates the average value in a column
SELECT AVG(salary) FROM employees;
#17. SUM - calculates the sum of values in a column
SELECT SUM(sales) FROM products;
#18. DISTINCT - retrieves unique values from a column
SELECT DISTINCT department FROM employees;
#19. LIKE - performs pattern matching in queries
SELECT * FROM users WHERE name LIKE 'A%';
#20. BETWEEN - retrieves values within a range
SELECT * FROM sales WHERE amount BETWEEN 1000 AND 2000;
#21. JOIN - combines rows from two or more tables based on a related column
SELECT * FROM orders JOIN customers ON orders.customer_id = customers.id;
#22. LEFT JOIN - returns all rows from the left table and matching rows from the right table
SELECT * FROM users LEFT JOIN orders ON users.id = orders.user_id;
#23. RIGHT JOIN - returns all rows from the right table and matching rows from the left table
SELECT * FROM users RIGHT JOIN orders ON users.id = orders.user_id;
#24. UNION - combines the results of two or more SELECT statements
SELECT name FROM customers UNION SELECT name FROM suppliers;
#25. GROUP BY - groups rows based on a column
SELECT department, AVG(salary) FROM employees GROUP BY department;
#26. HAVING - filters the results of a GROUP BY clause
SELECT department, AVG(salary) FROM employees GROUP BY department HAVING AVG(salary) > 50000;
#27. ORDER BY - sorts the result set based on a column
SELECT * FROM products ORDER BY price DESC;
#28. LIMIT - limits the number of rows returned in a query
SELECT * FROM users LIMIT 10;
#29. IN - specifies multiple values in a WHERE clause
SELECT * FROM users WHERE department IN ('HR', 'Finance');
#30. NOT IN - excludes specified values in a WHERE clause
SELECT * FROM users WHERE department NOT IN ('IT', 'Engineering');
#31. IS NULL - selects rows where a column is NULL
SELECT * FROM users WHERE email IS NULL;
#32. IS NOT NULL - selects rows where a column is not NULL
SELECT * FROM users WHERE email IS NOT NULL;
#33. AVG() - calculates the average of a set of values
SELECT AVG(sales) FROM monthly_sales;
#34. UPPER() - converts a string to uppercase
SELECT UPPER(last_name) FROM employees;
#35. LOWER() - converts a string to lowercase
SELECT LOWER(first_name) FROM employees;
#36. LENGTH() - returns the length of a string
SELECT LENGTH(description) FROM products;
#37. CONCAT() - concatenates two or more strings
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees;
#38. DATE_FORMAT() - formats a date
SELECT DATE_FORMAT(order_date, '%Y-%m-%d') FROM orders;
#39. TRUNCATE TABLE - deletes all rows in a table but retains the table structure
TRUNCATE TABLE logs;
#40. SHOW INDEXES - displays the indexes on a table
SHOW INDEXES FROM users;
#41. SHOW CREATE TABLE - displays the CREATE TABLE statement for a table
SHOW CREATE TABLE users;
#42. SHOW FULL COLUMNS - displays detailed information about the columns in a table
SHOW FULL COLUMNS FROM users;
#43. CREATE INDEX - creates an index on a table
CREATE INDEX idx_name ON users (name);
#44. DROP INDEX - deletes an index on a table
DROP INDEX idx_name ON users;
#45. REPLACE INTO - inserts a new row or replaces an existing row in a table
REPLACE INTO users (id, name) VALUES (1, 'Alice');
#46. LOAD DATA INFILE - loads data from a text file into
Joins from 2, 3 tables
### Selecting Data:
#1. **SELECT with INNER JOIN:**
SELECT t1.column_name, t2.column_name
FROM table1 AS t1
INNER JOIN table2 AS t2 ON t1.common_column = t2.common_column;
#2. **SELECT with LEFT JOIN:**
SELECT t1.column_name, t2.column_name
FROM table1 AS t1
LEFT JOIN table2 AS t2 ON t1.common_column = t2.common_column;
#3. **SELECT with RIGHT JOIN:**
SELECT t1.column_name, t2.column_name
FROM table1 AS t1
RIGHT JOIN table2 AS t2 ON t1.common_column = t2.common_column;
#4. **SELECT with FULL JOIN (Useful for MySQL 8.0+):**
SELECT t1.column_name, t2.column_name
FROM table1 AS t1
FULL JOIN table2 AS t2 ON t1.common_column = t2.common_column;
#5. **SELECT with INNER JOIN on multiple conditions:**
SELECT t1.column_name, t2.column_name, t3.column_name
FROM table1 AS t1
INNER JOIN table2 AS t2 ON t1.common_column = t2.common_column
INNER JOIN table3 AS t3 ON t1.another_column = t3.another_column;
### Updating Data:
#6. **UPDATE with JOIN:**
UPDATE table1 AS t1
JOIN table2 AS t2 ON t1.common_column = t2.common_column
SET t1.column_to_update = new_value;
#7. **UPDATE with JOIN and WHERE clause:**
UPDATE table1 AS t1
JOIN table2 AS t2 ON t1.common_column = t2.common_column
SET t1.column_to_update = new_value
WHERE t2.other_column = some_condition;
### Example:
#Let's consider the following example with 3 tables 'users', 'orders', and 'products':
SELECT users.name, orders.order_id, products.product_name
FROM users
INNER JOIN orders ON users.user_id = orders.user_id
INNER JOIN products ON orders.product_id = products.product_id;
#This query selects the names of users, order IDs, and product names from the respective tables by joining them based on the relationships between user_id and order_id, and product_id.