MySQL in a Nutshell: A Handy Cheatsheet of Frequently Used Queries
Tags: mysql, database, sql, cheatsheet
Published: 2023-01-26 11:53:16

This MySQL cheatsheet provides a comprehensive list of essential commands for managing and manipulating databases. From creating and modifying tables to querying and updating data, this reference guide covers all the key commands you need to know to effectively work with MySQL. Whether you're a beginner or an experienced user, this cheatsheet is a valuable resource for quickly finding the command you need.


1. Connect to MySQL: `mysql -u [username] -p`
2. Show all databases: `SHOW DATABASES;`
3. Create a new database: `CREATE DATABASE [database name];`
4. Select a database: `USE [database name];`
5. Show all tables in a database: `SHOW TABLES;`
6. Create a new table: `CREATE TABLE [table name] ([column name] [data type], [column name] [data type], ...);`
7. Display the structure of a table: `DESCRIBE [table name];`
8. Insert data into a table: `INSERT INTO [table name] ([column name], [column name], ...) VALUES ([value], [value], ...);`
9. Select data from a table: `SELECT [column name], [column name], ... FROM [table name];`
10. Update data in a table: `UPDATE [table name] SET [column name] = [new value] WHERE [condition];`
11. Delete data from a table: `DELETE FROM [table name] WHERE [condition];`
12. Select data with a condition: `SELECT [column name], [column name], ... FROM [table name] WHERE [condition];`
13. Select data with a join: `SELECT [column name], [column name], ... FROM [table name1] JOIN [table name2] ON [table name1.[column name] = [table name2].[column name];`
14. Order data by a column: `SELECT [column name], [column name], ... FROM [table name] ORDER BY [column name];`
15. Limit the number of rows returned: `SELECT [column name], [column name], ... FROM [table name] LIMIT [number];`
16. Count number of rows: `SELECT COUNT([column name]) FROM [table name];`
17. Sum of a column: `SELECT SUM([column name]) FROM [table name];`
18. Average of a column: `SELECT AVG([column name]) FROM [table name];`
19. Group by a column: `SELECT [column name], [aggregate function]([column name]) FROM [table name] GROUP BY [column name];`
20. Create a new user: `CREATE USER 'username'@'host' IDENTIFIED BY 'password';`
21. Grant permissions to user: `GRANT [permission] ON [database name].[table name] TO 'username'@'host';`
22. Revoke permissions: `REVOKE [permission] ON [database name].[table name] FROM 'username'@'host';`
23. Show all users: `SELECT User, Host FROM mysql.user;`
24. Change a user's password: `SET PASSWORD FOR 'username'@'host' = PASSWORD('new_password');`
25. Rename a table: `ALTER TABLE [table name] RENAME TO [new table name];`
26. Add a column to a table: `ALTER TABLE [table name] ADD [column name] [data type];`
27. Modify a column in a table: `ALTER TABLE [table name] MODIFY [column name] [data type];`
28. Drop a column from a table: `ALTER TABLE [table name] DROP COLUMN [column name];`
29. Create an index: `CREATE INDEX [index name] ON [table name] ([column name]);`
30. Drop an index: `DROP INDEX [index name] ON [table name];`
31. Create a primary key: `ALTER TABLE [table name] ADD PRIMARY KEY ([column name]);`
32. Create a foreign key: `ALTER TABLE [table name] ADD FOREIGN KEY ([column name]) REFERENCES [referenced table name] ([referenced column name]);`
33. Create a unique constraint: `ALTER TABLE [table name] ADD UNIQUE ([column name]);`
34. Create a check constraint: `ALTER TABLE [table name] ADD CHECK (condition);`
35. Create a trigger: `CREATE TRIGGER [trigger name] [trigger action] [event] ON [table name] FOR EACH ROW [trigger code];`
36. Drop a trigger: `DROP TRIGGER [trigger name];`
37. Create a stored procedure: `CREATE PROCEDURE [procedure name] ([parameter name] [data type], ...) BEGIN [procedure code]; END;`
38. Call a stored procedure: `CALL [procedure name]([parameter value], ...);`
39. Drop a stored procedure: `DROP PROCEDURE [procedure name];`
40. Create a view: `CREATE VIEW [view name] AS SELECT [column name], ... FROM [table name];`
41. Drop a view: `DROP VIEW [view name];`
42. Create a backup: `mysqldump -u [username] -p [database name] > [file name].sql`
43. Restore a backup: `mysql -u [username] -p [database name] < [file name].sql`
44. Show current user: `SELECT CURRENT_USER();`
45. Show server version: `SELECT VERSION();`
46. Show system variables: `SHOW VARIABLES;`
47. Show status: `SHOW STATUS;`
48. Show processlist: `SHOW FULL PROCESSLIST;`
49. Kill a process: `KILL [process id];`
50. Exit MySQL: `EXIT;`


Note: replace the square bracketed placeholders with actual values.