Basic Commands
| Command | Description |
|---|---|
mysql -u username -p | Start the MySQL command-line interface |
CREATE DATABASE dbname | Create a new database |
DROP DATABASE dbname | Delete a database |
CREATE USER 'username'@'localhost' IDENTIFIED BY 'password' | Create a new user |
GRANT SELECT ON *.* TO ‘username‘ | Grant select on all databases and tables. |
GRANT ALL ON dbname.* TO ‘username‘; | Grant all access to all tables in database |
GRANT ALL ON dbname.* TO ‘username‘@’localhost’; | Grant all access to all tables in database but only when connected from localhost. User %, or IP in place of localhost where % is wildcard like *, and by IP to restrict to a single interface. |
FLUSH PRIVILEGES | Refresh the privileges |
DROP USER 'username'@'localhost' | Delete a user |
mysqldump -u username -p dbname > filename.sql | Backup a database |
mysql -u username -p dbname < filename.sql | Restore a database |
Creating Tables
| Command | Description |
|---|---|
CREATE TABLE tablename (column1 datatype1, column2 datatype2, ...) | Create a new table |
ALTER TABLE tablename ADD COLUMN column datatype | Add a new column to a table |
ALTER TABLE tablename DROP COLUMN column | Remove a column from a table |
DROP TABLE tablename | Delete a table |
Data Manipulation
| Command | Description |
|---|---|
INSERT INTO tablename (column1, column2, ...) VALUES (value1, value2, ...) | Insert a new row into a table |
UPDATE tablename SET column = value WHERE condition | Update one or more rows in a table |
DELETE FROM tablename WHERE condition | Delete one or more rows from a table |
SELECT column1, column2, ... FROM tablename WHERE condition | Query data from a table |
Querying Data
| Command | Description |
|---|---|
SELECT * FROM tablename | Retrieve all rows and columns from a table |
SELECT column1, column2, ... FROM tablename | Retrieve specific columns from a table |
SELECT column1, column2, ... FROM tablename WHERE condition | Retrieve specific rows based on a condition |
SELECT column1, COUNT(*) FROM tablename GROUP BY column1 | Group rows based on a column and count the number of rows in each group |
SELECT column1, AVG(column2) FROM tablename GROUP BY column1 | Group rows based on a column and calculate the average value of another column in each group |
Joins
| Command | Description |
|---|---|
SELECT * FROM table1 JOIN table2 ON table1.column = table2.column | Join two tables based on a common column |
SELECT * FROM table1 LEFT JOIN table2 ON table1.column = table2.column | Left join two tables based on a common column |
SELECT * FROM table1 RIGHT JOIN table2 ON table1.column = table2.column | Right join two tables based on a common column |
SELECT * FROM table1 FULL OUTER JOIN table2 ON table1.column = table2.column | Full outer join two tables based on a common column |