| Connection |
Login to MySQL |
mysql -u user -p |
| Connection |
Exit MySQL |
exit; |
| Database |
Create Database |
CREATE DATABASE dbname; |
| Database |
Show Databases |
SHOW DATABASES; |
| Database |
Use Database |
USE dbname; |
| Table |
Create Table |
CREATE TABLE table (...); |
| Table |
Show Tables |
SHOW TABLES; |
| Table |
Describe Table |
DESCRIBE table; |
| Table |
Drop Table |
DROP TABLE table; |
| Table |
Alter Table |
ALTER TABLE table ADD|DROP|MODIFY column; |
| Data Manipulation |
Insert Data |
INSERT INTO table (col1, col2) VALUES (v1, v2); |
| Data Manipulation |
Update Data |
UPDATE table SET col1 = v1 WHERE condition; |
| Data Manipulation |
Delete Data |
DELETE FROM table WHERE condition; |
| Data Manipulation |
Truncate Table |
TRUNCATE TABLE table; |
| Querying |
Select All |
SELECT * FROM table; |
| Querying |
Select Specific |
SELECT col1, col2 FROM table; |
| Querying |
Where Clause |
SELECT * FROM table WHERE condition; |
| Querying |
Order By / Group By |
SELECT ... ORDER BY col ASC|DESC; / GROUP BY col; |
| Joins |
Inner Join |
SELECT ... FROM A INNER JOIN B ON A.id = B.id; |
| Joins |
Left Join |
SELECT ... FROM A LEFT JOIN B ON A.id = B.id; |
| Joins |
Right Join |
SELECT ... FROM A RIGHT JOIN B ON A.id = B.id; |
| Joins |
Full Outer Join |
SELECT ... FROM A LEFT JOIN B ... UNION SELECT ... FROM A RIGHT JOIN B ...; |
| Indexes |
Create Index |
CREATE INDEX idx_name ON table (column); |
| User Management |
Create User |
CREATE USER 'user'@'host' IDENTIFIED BY 'pass'; |
| User Management |
Grant Privileges |
GRANT ALL ON db.* TO 'user'@'host'; |
| User Management |
Show Users |
SELECT user, host FROM mysql.user; |
| Backup & Restore |
Backup DB |
mysqldump -u user -p dbname > file.sql |
| Backup & Restore |
Restore DB |
mysql -u user -p dbname < file.sql |
| Other |
Show Current DB |
SELECT DATABASE(); |
