1. MySQL Basics:
- Installation: `sudo apt-get install mysql-server`
- Connect: `mysql -u username -p`
- Create Database: `CREATE DATABASE dbname;`
2. Data Manipulation:
- SELECT Data: `SELECT * FROM tablename WHERE condition;`
SELECT * FROM employees WHERE age > 30;
- UPDATE Data: `UPDATE tablename SET column = newvalue WHERE condition;`
UPDATE employees SET salary = 50000 WHERE department = 'Sales';
- DELETE Data: `DELETE FROM tablename WHERE condition;`
DELETE FROM orders WHERE status = 'Canceled';
4. **Filtering and Sorting Data:**
- Filtering: `SELECT * FROM tablename WHERE column = value;`
- Sorting: `SELECT * FROM tablename ORDER BY column ASC;`
5. **Joins and Relationships:**
- INNER JOIN: `SELECT * FROM table1 INNER JOIN table2 ON table1.column = table2.column;`
- Relationships: `FOREIGN KEY, REFERENCES`
6. **Aggregating Data:**
- SUM: `SELECT SUM(column) FROM tablename;`
- GROUP BY: `SELECT column, SUM(othercolumn) FROM tablename GROUP BY column;`
7. **Subqueries:**
- Subquery in WHERE: `SELECT * FROM tablename WHERE column = (SELECT othercolumn FROM othertable);`
- Correlated Subquery: `SELECT * FROM tablename WHERE column > (SELECT AVG(othercolumn) FROM tablename);`
8. **Indexes and Optimization:**
- Create Index: `CREATE INDEX indexname ON tablename (column);`
- Query Optimization: `EXPLAIN SELECT * FROM tablename WHERE condition;`
9. **Stored Procedures and Functions:**
- Create Procedure: `CREATE PROCEDURE procedurename() BEGIN ... END;`
- Create Function: `CREATE FUNCTION functionname() RETURNS datatype BEGIN ... END;`
10. **Transactions:**
- Start Transaction: `BEGIN;`
- Commit: `COMMIT;`
- Rollback: `ROLLBACK;`
11. **User Management:**
- Create User: `CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';`
- Grant Privileges: `GRANT ALL PRIVILEGES ON dbname.* TO 'username'@'localhost';`
12. **Backups and Recovery:**
- Backup: `mysqldump -u username -p dbname > backup.sql`
- Restore: `mysql -u username -p dbname < backup.sql`
13. **Security Best Practices:**
- Use Strong Passwords
- Limit User Privileges
14. **Advanced Topics:**
- Triggers: `CREATE TRIGGER triggername ...`
- Replication: `Master-Slave Replication`
15. **Troubleshooting:**
- Common Error: `ERROR 1045 (28000): Access denied for user 'user'@'host'`
16. **Useful MySQL Commands:**
- Show Databases: `SHOW DATABASES;`
- Show Tables: `SHOW TABLES;`
- Show Users: `SELECT user FROM mysql.user;`
**Conclusion:**
- MySQL is a powerful relational database system.
- Regular practice and reference are essential for mastering it.
**Additional Tips:**
- Use these commands as templates and adapt them to your specific needs.
- Always replace placeholders like `dbname`, `tablename`, `username`, and `password` with actual values.
- Experiment in a safe environment to avoid accidental data loss.
SELECT * FROM table_name;SELECT * FROM table_name1, table_table2;SELECT field1, field2 FROM table_name1, table_name2;
SELECT field1 FROM table_name WHERE field1 = '';SELECT ... FROM ... WHERE condition GROUP BY field;SELECT ... FROM ... WHERE condition GROUP BY field HAVING condition2;SELECT ... FROM ... WHERE condition ORDER BY field1, field2;SELECT ... FROM ... WHERE condition ORDER BY field1, field2 DESC;SELECT ... FROM ... WHERE condition LIMIT 10;SELECT DISTINCT field1 FROM ...
