1.Basic Commands
- Connect to MySQL:
mysql -u username -p
- Show Databases:
SHOW DATABASES;
- Create Database:
CREATE DATABASE database_name;
- Use Database:
USE database_name;
- Drop Database:
DROP DATABASE database_name;
2.Table Management
- Show Tables:
SHOW TABLES;
- Create Table:
CREATE TABLE table_name ( column1 datatype, column2 datatype, ... ); - Describe Table Structure:
DESCRIBE table_name;
- Drop Table:
DROP TABLE table_name;
- Alter Table:
- Add a column:
ALTER TABLE table_name ADD column_name datatype;
- Modify a column:
ALTER TABLE table_name MODIFY column_name datatype;
- Drop a column:
ALTER TABLE table_name DROP COLUMN column_name;
- Add a column:
3.Data Manipulation
- Insert Data:
INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);
- Select Data:
SELECT column1, column2 FROM table_name WHERE condition;
- Update Data:
UPDATE table_name SET column1 = value1 WHERE condition;
- Delete Data:
DELETE FROM table_name WHERE condition;
4.Querying Data
- Select All Rows:
SELECT * FROM table_name;
- Filtering Results:
SELECT * FROM table_name WHERE column_name = 'value';
- Ordering Results:
SELECT * FROM table_name ORDER BY column_name ASC|DESC;
- Limit Results:
SELECT * FROM table_name LIMIT number;
- Distinct Values:
SELECT DISTINCT column_name FROM table_name;
5.Aggregate Functions
- Count Rows:
SELECT COUNT(*) FROM table_name;
- Sum Values:
SELECT SUM(column_name) FROM table_name;
- Average Value:
SELECT AVG(column_name) FROM table_name;
- Minimum and Maximum:
SELECT MIN(column_name) FROM table_name; SELECT MAX(column_name) FROM table_name;
6.Joins
- Inner Join:
SELECT columns FROM table1 INNER JOIN table2 ON table1.common_column = table2.common_column;
- Left Join:
SELECT columns FROM table1 LEFT JOIN table2 ON table1.common_column = table2.common_column;
- Right Join:
SELECT columns FROM table1 RIGHT JOIN table2 ON table1.common_column = table2.common_column;
- Full Outer Join: (Not natively supported, but can be emulated)
SELECT columns FROM table1 LEFT JOIN table2 ON table1.common_column = table2.common_column UNION SELECT columns FROM table1 RIGHT JOIN table2 ON table1.common_column = table2.common_column;
7.Indexes and Keys
- Create Index:
CREATE INDEX index_name ON table_name (column_name);
- Drop Index:
DROP INDEX index_name ON table_name;
- Primary Key:
CREATE TABLE table_name ( id INT PRIMARY KEY, ... );
8.User Management
- Create User:
CREATE USER 'username'@'host' IDENTIFIED BY 'password';
- Grant Privileges:
GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'host';
- Show Grants for a User:
SHOW GRANTS FOR 'username'@'host';
- Revoke Privileges:
REVOKE ALL PRIVILEGES ON database_name.* FROM 'username'@'host';
- Drop User:
DROP USER 'username'@'host';
9.Backup and Restore
-
- Backup Database:
mysqldump -u username -p database_name > backup_file.sql
- Restore Database:
mysql -u username -p database_name < backup_file.sql
- Backup Database:
10.Quick Tips
- Use Comments:
-- This is a comment /* This is a multi-line comment */
- Transactions:
START TRANSACTION; -- SQL statements COMMIT; -- or ROLLBACK;