SELECT clause defines the columns in the result table to be return, for example, SELECT name, gender will select the ‘name’ and ‘gender’ column of the result
SELECT * will select all columns of the result
to rename the column, use AS, for example, SELECT foo AS data will select ‘foo’ column, but rename the column to ‘data’
FROM clause defindes the table to get data
FROM table1 a will rename table1 as ‘a’, in later statement, ‘a’ refers to table1.
WHERE put restrictions on the rows to be included
name='a' will select only the row that has ‘a’ as entry of ‘name’ column
name IN ('a','c', 'ac') will select the rows with entry of ‘name’ column either ‘a’ or ‘c’ or ‘ac’
name LIKE 'a%' will select the rows with entry of ‘name’ column that starts with ‘a’. Note % act as a wildcard
GROUP BY…
only show distinct value for the specified column
SELECT gender, COUNT(name) GROUP BY gender will show distinct gender and number of ‘name’ corresponding to each gender
ORDER BY…
order the result according to the column(s) specified
can choose ascending or descending
ORDER BY name DESC will show the result order by name descending
ORDER BY gender DESC, name will show the result first order by genderdescending then order by name
…JOIN…ON (…=…)
joins two table together
JOIN ON select the rows only if the selected columns are the same (specified by ON clause), other are disgarded (including NULL)
LEFT JOIN ON select all the rows in the left (to the JOIN) table, and add columns from right table by matching the ON clause. NULL willbe kept.
result of SELECT name, title FROM table1 JOIN table2 ON (majorid=id): (name, majorid)=[(a, EE), (b, CS), (d, CS)]
result of SELECT name, title FROM talbe1 LEFT JOIN table2 ON (majorid=id): (name, majorid)=[(a, EE), (b, CS), (c, NULL), (d, CS)]
Self join by renaming table1 a JOIN table1 b ON (a.majorid=b.majorid) self join two table1 (‘a’ and ‘b’) on majorid
result of SELECT a.name, b.name, a.majorid FROM table1 a JOIN table1 b ON (a.majorid=b.majorid): (a.name, b.name, a.majorid)=[(a,a,34),(b,b,26),(b,d,26),(d,d,CS),(d,b,26)]
近期评论