To connect to a MySQL server, use the following command:
mysql -u username -pReplace username with your username. You will be prompted to enter your password.
SHOW DATABASES; – Displays a list of all databases.
USE database_name; – Selects the database you want to work with.
SHOW TABLES; – Displays a list of all tables in the current database.
DESCRIBE table_name; – Displays the structure of a table.
CREATE DATABASE database_name; – Creates a new database with the specified name.
DROP DATABASE database_name; – Deletes the database with the specified name.
CREATE TABLE table_name (column1 datatype, column2 datatype, column3 datatype); – Creates a new table with the specified columns and datatypes.
DROP TABLE table_name; – Deletes the table with the specified name.
INSERT INTO table_name (column1, column2, column3) VALUES (value1, value2, value3); – Inserts a new row into the specified table with the specified values.
SELECT * FROM table_name; – Retrieves all rows and columns from the specified table.
SELECT column1, column2 FROM table_name; – Retrieves specific columns from the specified table.
SELECT * FROM table_name WHERE column1 = 'value'; – Retrieves all rows from the specified table where the specified column has the specified value.
UPDATE table_name SET column1 = 'value1', column2 = 'value2' WHERE column3 = 'value3'; – Updates the specified columns in the specified table where the specified column has the specified value.
DELETE FROM table_name WHERE column1 = 'value'; – Deletes all rows from the specified table where the specified column has the specified value.
SELECT COUNT(column_name) FROM table_name; – Returns the number of rows in the specified column.
SELECT SUM(column_name) FROM table_name; – Returns the sum of all values in the specified column.
SELECT AVG(column_name) FROM table_name; – Returns the average of all values in the specified column.
SELECT MAX(column_name) FROM table_name; – Returns the maximum value in the specified column.
SELECT MIN(column_name) FROM table_name; – Returns the minimum value in the specified column.
SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name; – Groups rows with the same value in the specified column and returns the count of each group.
SELECT column1, AVG(column2) FROM table_name GROUP BY column1; – Groups rows with the same value in column1 and returns the average of column2 for each group.
The HAVING clause is used to filter the groups generated by the GROUP BY clause. It is similar to the WHERE clause but operates on the groups instead of individual rows.
SELECT column_name(s), aggregate_function(column_name)
FROM table_name
GROUP BY column_name(s)
HAVING condition
INNER JOIN
SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;
LEFT JOIN
SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;
RIGHT JOIN
SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;
FULL OUTER JOIN
SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name;
SELF JOIN
SELECT column_name(s)
FROM table1 t1, table1 t2
WHERE t1.column_name = t2.column_name;