MySQL Cheatsheet

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.

- Keep your MySQL server secure and regularly back up your data.

 

SHOW DATABASES;
SHOW TABLES;
SHOW FIELDS FROM table; or DESCRIBE table;
SHOW PROCESSLIST;
KILL process_id;

Select Query :

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 ...

SELECT DISTINCT field1, field2 FROM ...