SELECT column1, column2 FROM table_name WHERE condition;SELECT*FROM table_name WHERE condition1 AND condition2;SELECT*FROM table_name WHERE condition1 OR condition2;SELECT*FROM table_name WHERENOT condition;SELECT*FROM table_name WHERE condition1 AND(condition2 OR condition3);SELECT*FROM table_name WHEREEXISTS(SELECT column_name FROM table_name WHERE condition);
SELECTTOP number columns_names FROM table_name WHERE condition;SELECTTOPpercent columns_names FROM table_name WHERE condition;# 并非所有数据库系统都支持SELECT TOP。 MySQL 中是LIMIT子句SELECT column_names FROM table_name LIMIToffset, count;
LIKE: 用于搜索列中的特定模式,WHERE 子句中使用的运算符
%(percent sign) 是一个表示零个,一个或多个字符的通配符
_ (underscore) 是一个表示单个字符通配符
SELECT column_names FROM table_name WHERE column_name LIKE pattern;LIKE ‘a%’ (查找任何以“a”开头的值)
LIKE ‘%a’ (查找任何以“a”结尾的值)
LIKE ‘%or%’ (查找任何包含“or”的值)
LIKE ‘_r%’ (查找任何第二位是“r”的值)
LIKE ‘a_%_%’ (查找任何以“a”开头且长度至少为3的值)
LIKE ‘[a-c]%’(查找任何以“a”或“b”或“c”开头的值)
IN: 用于在 WHERE 子句中指定多个值的运算符
本质上,IN运算符是多个OR条件的简写
SELECT column_names FROM table_name WHERE column_name IN(value1, value2, …);SELECT column_names FROM table_name WHERE column_name IN(SELECT STATEMENT);
BETWEEN: 用于过滤给定范围的值的运算符
SELECT column_names FROM table_name WHERE column_name BETWEEN value1 AND value2;SELECT*FROM Products WHERE(column_name BETWEEN value1 AND value2)ANDNOT column_name2 IN(value3, value4);SELECT*FROM Products WHERE column_name BETWEEN#01/07/1999# AND #03/12/1999#;
NULL: 代表一个字段没有值
SELECT*FROM table_name WHERE column_name ISNULL;SELECT*FROM table_name WHERE column_name ISNOTNULL;
AS: 用于给表或者列分配别名
SELECT column_name AS alias_name FROM table_name;SELECT column_name FROM table_name AS alias_name;SELECT column_name AS alias_name1, column_name2 AS alias_name2;SELECT column_name1, column_name2 + ‘, ‘ + column_name3 AS alias_name;
UNION: 用于组合两个或者多个 SELECT 语句的结果集的运算符
每个 SELECT 语句必须拥有相同的列数
列必须拥有相似的数据类型
每个 SELECT 语句中的列也必须具有相同的顺序
SELECT columns_names FROM table1 UNIONSELECT column_name FROM table2;UNION 仅允许选择不同的值,UNIONALL 允许重复
ANY|ALL: 用于检查 WHERE 或 HAVING 子句中使用的子查询条件的运算符
ANY 如果任何子查询值满足条件,则返回 true。
ALL 如果所有子查询值都满足条件,则返回 true。
SELECT columns_names FROM table1 WHERE column_name operator (ANY|ALL)(SELECT column_name FROM table_name WHERE condition);
GROUP BY: 通常与聚合函数(COUNT,MAX,MIN,SUM,AVG)一起使用,用于将结果集分组为一列或多列
SELECT column_name1,COUNT(column_name2)FROM table_name WHERE condition GROUPBY column_name1 ORDERBYCOUNT(column_name2)DESC;
HAVING: HAVING 子句指定 SELECT 语句应仅返回聚合值满足指定条件的行。它被添加到 SQL 语言中,因为WHERE关键字不能与聚合函数一起使用。
UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;UPDATE table_name SET column_name =value;
DELETE: 用于删除表中的现有记录/行
DELETEFROM table_name WHERE condition;DELETE*FROM table_name;
3. 聚合查询
COUNT: 返回出现次数
SELECTCOUNT(DISTINCT column_name);
MIN() and MAX(): 返回所选列的最小/最大值
SELECTMIN(column_names)FROM table_name WHERE condition;SELECTMAX(column_names)FROM table_name WHERE condition;
AVG(): 返回数字列的平均值
SELECTAVG(column_name)FROM table_name WHERE condition;
SUM(): 返回数值列的总和
SELECTSUM(column_name)FROM table_name WHERE condition;
4.连接查询
INNER JOIN: 内连接,返回在两张表中具有匹配值的记录
SELECT column_names FROM table1 INNERJOIN table2 ON table1.column_name=table2.column_name;SELECT table1.column_name1, table2.column_name2, table3.column_name3 FROM((table1 INNERJOIN table2 ON relationship)INNERJOIN table3 ON relationship);
LEFT (OUTER) JOIN: 左外连接,返回左表(table1)中的所有记录,以及右表中的匹配记录(table2)
SELECT column_names FROM table1 LEFTJOIN table2 ON table1.column_name=table2.column_name;
RIGHT (OUTER) JOIN: 右外连接,返回右表(table2)中的所有记录,以及左表(table1)中匹配的记录
SELECT column_names FROM table1 RIGHTJOIN table2 ON table1.column_name=table2.column_name;
FULL (OUTER) JOIN: 全外连接,全连接是左右外连接的并集. 连接表包含被连接的表的所有记录, 如果缺少匹配的记录, 以 NULL 填充。
SELECT column_names FROM table1 FULLOUTERJOIN table2 ON table1.column_name=table2.column_name;
Self JOIN: 自连接,表自身连接
SELECT column_names FROM table1 T1, table1 T2 WHERE condition;
5.视图查询
CREATE: 创建视图
CREATEVIEW view_name ASSELECT column1, column2 FROM table_name WHERE condition;