MySQL - More Query Operations
Following my post on MySQL Query Operations, this post goes over more query operations.
GROUP BY, and COUNT()
Group by will display the results grouped by the selection. Count() will return the count for the parameter.
SELECT Year, COUNT(Year)
FROM Movie
GROUP BY Year;
────── ⋅ ⋅ ──── ✩ ──── ⋅ ⋅ ──────
LEFT JOIN
Left joins return records from the first table, and matching records from the second table.
SELECT table1.column2, table2.columnb, table2.columnc
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;
────── ⋅ ⋅ ──── ✩ ──── ⋅ ⋅ ──────
INNER JOIN
Inner join is the center of a venn diagram. This returns values existing in both.
SELECT table.column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;
To inner join a table to itself, such as a table that has Employee EmployeeID, FirstName, Lastname, ManagerID:
SELECT Employee.FirstName, Manager.Firstname
FROM Employee
INNER JOIN Employee AS Manager
ON Employee.ManagerID = Manager.ID
ORDER BY Employee.FirstName;
── ⋅ ⋅ ── ✩ ── ⋅ ⋅ ──
Subquery & AVG()
SELECT Name, Height
FROM Student
WHERE Height >
(SELECT AVG(Height)
FROM Student)
ORDER BY Height;
── ⋅ ⋅ ── ✩ ── ⋅ ⋅ ──
ALTER
Alter is for changing attributes. Use UPDATE for changing the actual values in the column.
ALTER TABLE table_name
ADD ColumnName varchar(255)
DROP COLUMN columnName
MODIFY COLUMN columnName datatype;
── ⋅ ⋅ ── ✩ ── ⋅ ⋅ ──
INSERT INTO
You don’t have to clarify the column names if you are adding all values.
INSERT INTO tableName (column1, column2, column3...)
VALUES (value1, value2, value3...);
── ⋅ ⋅ ── ✩ ── ⋅ ⋅ ──
UPDATE
UPDATE tableName
SET column1 = value1, column2 = value2, ...
WHERE condition;
── ⋅ ⋅ ── ✩ ── ⋅ ⋅ ──
DELETE
DELETE FROM tableName WHERE condition;
── ⋅ ⋅ ── ✩ ── ⋅ ⋅ ──
Joins
Types of joins:
────── ⋅ ⋅ ──── ✩ ──── ⋅ ⋅ ──────
Subqueries
Subqueries allow for two queries to be combined.
────── ⋅ ⋅ ──── ✩ ──── ⋅ ⋅ ──────
Comments