Basic Commands
- Connect to MySQL:
mysql -u username -p
- Show all databases:
SHOW DATABASES;
- Use a database:
USE database_name;
- Show all tables in a database:
SHOW TABLES;
- Show table structure:
DESCRIBE table_name;
Data Types
-
String Types:
CHAR(size)VARCHAR(size)TEXT
-
Numeric Types:
INT(size)FLOAT(size, d)DOUBLE(size, d)
-
Date and Time Types:
DATEDATETIMETIMESTAMPTIME
Table Management
- Create a table:
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
column3 datatype,
PRIMARY KEY (column1)
);
- Drop a table:
DROP TABLE table_name;
- Alter a table:
ALTER TABLE table_name
ADD column_name datatype;
Data Manipulation
- Insert data:
INSERT INTO table_name (column1, column2)
VALUES (value1, value2);
- Update data:
UPDATE table_name
SET column1 = value1, column2 = value2
WHERE condition;
- Delete data:
DELETE FROM table_name
WHERE condition;
Queries
- Select data:
SELECT column1, column2
FROM table_name
WHERE condition;
- Select all data:
SELECT * FROM table_name;
- Order by:
SELECT column1, column2
FROM table_name
ORDER BY column1 ASC | DESC;
- Group by:
SELECT column1, COUNT(*)
FROM table_name
GROUP BY column1;
Joins
- Inner join:
SELECT a.column1, b.column2
FROM table1 a
INNER JOIN table2 b ON a.common_column = b.common_column;
- Left join:
SELECT a.column1, b.column2
FROM table1 a
LEFT JOIN table2 b ON a.common_column = b.common_column;
- Right join:
SELECT a.column1, b.column2
FROM table1 a
RIGHT JOIN table2 b ON a.common_column = b.common_column;
Indexes
- Create an index:
CREATE INDEX index_name
ON table_name (column1, column2);
- Drop an index:
DROP INDEX index_name ON table_name;
Backup and Restore
- Backup a database:
mysqldump -u username -p database_name > backup.sql
- Restore a database:
mysql -u username -p database_name < backup.sql
User Management
- Create a user:
CREATE USER 'username'@'host' IDENTIFIED BY 'password';
- Grant privileges:
GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'host';
- Flush privileges:
FLUSH PRIVILEGES;
- Drop a user:
DROP USER 'username'@'host';
Feel free to ask if you need more details on any specific topic!
Top comments (0)