1. Access monitor
mysql -u [username] -p; (will prompt for password)
2. Show all databases:
show databases;
3. Access database:
mysql -u [username] -p [database] (will prompt for password)
4.Create new database:
create database [database];
5.Select database:
use [database];
6. Determine what database is in use:
select database();
7. Show all tables:
show tables;
8. Show table structure:
describe [table];
9. List all indexes on a table:
show index from [table];
10. Create new table with columns:
CREATE TABLE [table] ([column] VARCHAR(120), [another-column] DATETIME);
11. Adding a column:
ALTER TABLE [table] ADD COLUMN [column] VARCHAR(120);
12. Adding a column with an unique, auto-incrementing ID:
ALTER TABLE [table] ADD COLUMN [column] int NOT NULL AUTO_INCREMENT PRIMARY KEY;
13. Inserting a record:
INSERT INTO [table] ([column], [column]) VALUES ('[value]', [value]');
14. MySQL function for datetime input:
NOW()
15. Selecting records:
SELECT * FROM [table];
16.Explain records:
EXPLAIN SELECT * FROM [table];
17. Selecting parts of records:
SELECT [column], [another-column] FROM [table];
18. Counting records:
SELECT COUNT([column]) FROM [table];
19.Counting and selecting grouped records:
SELECT *, (SELECT COUNT([column]) FROM [table]) AS count FROM [table] GROUP BY [column];
20. Delete all records in a table:
truncate table [table];
21. Removing table columns:
ALTER TABLE [table] DROP COLUMN [column];
22. Deleting tables:
DROP TABLE [table];
23.Deleting databases:
DROP DATABASE [database];
24. Custom column output names:
SELECT [column] AS [custom-column] FROM [table];
25. Export a database dump:
mysqldump -u [username] -p [database] > db_backup.sql
26. Import a database dump (more info here):
mysql -u [username] -p -h localhost [database] < db_backup.sql
27. Logout:
exit;
