반응형
WHERE
SELECT * FROM tblStudent
WHERE studentGender = 'Female'
AND studentLast IN ('Kim', 'Lee', 'Park')
-- IN is a shorthand for multiple OR conditions.
AND DATE(studentDOB) BETWEEN '1997-01-01' AND '1999-12-31'
-- BETWEEN selects values within a given range. (inclusive)
-- Values can be numbers, text, or dates.
AND studentEmail LIKE '%@gmail.com'
-- LIKE is used to search for a specified pattern. Wildcards : (%) and (_)
-- (%) represents zero, one, or multiple characters.
-- (_) represents one, single character.
SELECT
SELECT * FROM tblStudent
LIMIT 5;
-- LIMIT sets an upper limit on the number of tuples returned.SELECT DISTINCT staffDepartmentId FROM tblStaff;
-- SELECT DISTINCT is used to return only distinct (different) values.SELECT departmentName, COUNT(*) from tblDepartment
GROUP BY departmentName
ORDER BY COUNT(*) ASC|DESC;
-- COUNT() returns the number of rows that matches a specified criterion.
-- GROUP BY groups rows that have the same values into summary rows.
-- ORDER BY sorts the result-set in ascending or descending order. (default is ASC)SELECT gradeEnrolmentId,
MAX(gradeScore),
MIN(gradeScore),
SUM(gradeScore),
ROUND(AVG(gradeScore),2)
FROM tblGrade
GROUP BY gradeEnrolmentId;
/*
MIN() returns the smallest value of the selected column
MAX() returns the largest value of the selected column
SUM() returns the total sum of a numeric column
AVG() returns the average value of a numeric column
ROUND() rounds a number to a specified number of decimal places
*/
JOIN
SELECT * FROM tblSchool s
LEFT JOIN tblBuilding b
ON s.schoolBuildingId = b.buildingId;
/*
LEFT JOIN returns all records from the left table (table1),
and the matching records from the right table (table2).
Even if there are no matches in the right table,
all records from the left table will be returned.
*/SELECT * FROM tblEnrolment e
INNER JOIN tblGrade g
ON e.enrolmentId = g.gradeEnrolmentId
-- INNER JOIN selects records that have matching values in both tables.
SUBQUERY추후 추가 예정
반응형