一、简单查询
1、SELECT 语句总述
SELECT [DISTINCT] *|字段名1,字段名2,字段名3...
FROM 表名 // 指定查询的表
[WHERE 条件表达式1] // 指定查询条件
[GROUP BY 字段名[HAVING 条件表达式2]] // GROUP BY 将查询结果按照指定字段进行分组
[ORDER BY 字段名 [ASC|DESC]] // ASC 升序排序,DESC 降序排序;默认为升序排序
[LIMIT [OFFSET] 记录数]; // 限制输出的条数;OFFSET 表示偏移量,若为0表示从第一条数据开始
- *|字段名1,字段名2,字段名3... // 其中 * 表示表中的所有字段,“字段名1,字段名2,字段名3...”表示查询指定的字段;两者为互斥关系。DISTINCT 可选,用于剔除查询结果中的重复数据;
- GROUP BY 将查询结果按照指定字段进行分组;HAVING 对分组后的结果进行过滤;
2、查询所有字段
(1)罗列所有字段
SELECT 字段名1, 字段名2, ... 字段名n FROM 表名;
(2)使用 * 通配符代替所有字段
SELECT * FROM 表名;
3、查询指定字段
(1)罗列出所要查询的字段名
SELECT 字段名1, 字段名2, ... FROM 表名;
二、按条件查询 WHERE
1、带关系运算符的查询
SELECT *|字段名1,字段名2,字段名3... FROM 表名 // 指定查询的表 WHERE 条件表达式; // 指定查询条件
可以指定的关系运算符包括:= 等于,<= 小于等于,<> 不等于,>= 不等于,> 大于,!= 不等于,< 小于。
2、带 IN 关键字的查询
SELECT *|字段名1,字段名2,字段名3... FROM 表名 WHERE 字段名 [NOT] IN (元素1, 元素2, ...);
筛选出某个字段的值在 (元素1, 元素2, ...) 的对象;
3、带 BETWEEN AND 关键字的查询
SELECT *|字段名1,字段名2,字段名3... FROM 表名 WHERE 字段名 [NOT] BETWEEN 值1 AND 值2;
4、空值查询 IS [NOT] NULL
SELECT *|字段名1,字段名2,字段名3... FROM 表名 WHERE 字段名 IS [NOT] NULL;
筛选出某个字段为 NULL,或者不为 NULL 的对象;
5、带 DISTINCT 关键字的查询
SELECT DISTINCT *|字段名1,字段名2,字段名3... FROM 表名 // 指定查询的表
过滤重复的结果,只有当DISTINCT 关键字后面的指定字段名都重复时,才认为两条数据重复。
6、带 LIKE 关键字的查询
SELECT *|字段名1,字段名2,字段名3... FROM 表名 WHERE 字段名 [NOT] LIKE '匹配字符串';
(1)百分号 % 通配符,匹配任意长度的字符串,包括空字符串
例:SELECT id,name FROM pople WHERE name LIKE "s%"; // 匹配所有name字段以s开头的数据
(2)下划线 _ 通配符,匹配单个字符
例:SELECT id,name FROM student WHERE name NOT LIKE "a_b"; // 匹配所有name以a开头,b结尾,且中间只有一个字符的数据;
注:若要匹配字面内容为 % 或者 _ 的字段,需要使用下划线 \,“\%,\_”。
7、带 AND 关键字的多条件查询
SELECT *|字段名1,字段名2,字段名3... FROM 表名 WHERE 表达式1 AND 表达式2 [AND 表达式3 ...];
8、带 OR 关键字的多条件查询
SELECT *|字段名1,字段名2,字段名3... FROM 表名 WHERE 表达式1 OR 表达式2 [OR 表达式3 ...];
注:AND 的优先级高于 OR。若 WHERE 条件后的表达式语句既包括 AND 又包括 OR 关键字,则应该先运算 AND 两边的表达式,后运算 OR 两边的表达式。
三、高级查询
1、聚合函数
函数名称 | 作用 | 函数名称 | 作用 |
COUNT() | 返回某列的行数 | MAX() | 返回某列的最大值 |
SUM() | 返回某列的和 | MIN() | 返回某列的最小值 |
AVG() | 返回某列的平均值 |
语法结构: SELECT 聚合函数名(参数) FROM 表名;
(1)COUNT() 函数,用于统计记录的条数
SELECT COUNT(*) FROM 表名;
(2)SUM() 函数,用于求某个字段的总和
SELECT SUM(字段名) FROM 表名;
(3)ACG() 函数,用于求某个字段的平均值
SELECT AVG(字段名) FROM 表名;
(4)MAX() 函数,用于求某个字段的最大值
SELECT MAX(字段名) FROM 表名;
(5)MIN() 函数,用于求某个字段的最小值
SELECT MIN(字段名) FROM 表名;
2、对查询结果进行排序 ORDER BY
SELECT *|字段名1,字段名2,字段名3...
FROM 表名 // 指定查询的表
ORDER BY 字段名1 [ASC|DESC], 字段名2[ASC|DESC], ... // ASC 升序排序,DESC 降序排序;默认为升序排序
若使用多个字段进行排序,则会首先按照“先指定的字段”进行排序,对于先指定的字段值相同时,再根据后面的字段进行排序。
3、分组查询 GROUP BY
SELECT *|字段名1,字段名2,字段名3...
FROM 表名 // 指定查询的表
GROUP BY 字段名1, 字段名2, ... [HAVING 条件表达式2] // GROUP BY 将查询结果按照指定字段进行分组
HAVING 关键字指定条件表达式对分组后的结果进行过滤。
对于5.7后的版本默认 sql_mode=only_full_group_by 模式?// 待具体研究
(1)单独使用 GROUP BY 分组
SELECT *|字段名1,字段名2,字段名3... FROM 表名 GROUP BY 字段名1, 字段名2, ...
对于查询结果,只显示每个分组的一条记录。
(2)GROUP BY 和聚合函数一起使用
SELECT 聚合函数(参数), 字段名1, 字段名2, 字段名3... FROM 表名 GROUP BY 字段名1, 字段名2, ...
例:SELECT COUNT(*), 字段名1 FROM 表名 GROUP BY 字段名1;
(3)GROUP BY 和 HAVING 关键字一起使用
HAVING 关键字和 WHERE 关键字的作用相同,都用于设置条件表达式对查询结果进行过滤,但是,HAVING 后可以跟聚合函数,WHERE 后不能。通常,HAVING 都和 GROUP BY 一起使用,对分组结果进行过滤。
SELECT 聚合函数(参数), 字段名1, 字段名2, 字段名3... FROM 表名 GROUP BY 字段名1, 字段名2, ... HAVING 聚合函数表达式;
例:SELECT SUM(字段名1), 字段名2 FROM 表名 GROUP BY 字段名2 HAVING SUM(字段名1)<300;
4、使用 LIMIT 限制查询结果的数量
SELECT *|字段名1,字段名2,字段名3... FROM 表名 LIMIT [OFFSET,] 记录数 // OFFSET 表示偏移量,从第一条开始偏移量为0
5、函数(列表)
MySQL提供了很多函数,通过使用函数可以简化对数据的操作。包括:数学函数、字符串函数、日期和时间函数、条件判断函数、加密函数等。
(1)数学函数
函数名称 | 作用 |
ABS(X) | 返回x的绝对值 |
SQRT(X) | 返回X的非负2次方根 |
MOD(X, Y) | 返回X被Y除后的余数 |
CEELING(X) | 返回不小于X的最小整数 |
FLOOR(X) | 返回不大于X的最大整数 |
ROUND(X,Y) | 对X进行四舍五入操作,小数点后保留Y位 |
TRUNCATE(X,Y) | 舍去X中小数点后Y位后面的数 |
SING(X) | 返回X的符号,-1、0 或 1 |
(2)字符串函数
函数名称 | 作用 |
LENGTH(str) | 返回字符串str的长度 |
CONCAT(s1,s2, ...) | 返回一个或者多个字符串连接产生的新的字符串 |
TRIM(str) | 删除字符串两侧的空格 |
REPLACE(str, s1, s2) | 使用字符串s2替换字符串str中所有的字符串s1 |
SUBSTRING(str, n, len) | 截取字符串,起始位置为n,长度为 len |
REVERSE(str) | 返回字符串翻转后的结果 |
LOCATE(s1, str) | 返回子串 s1 在字符串 str 中的起始位置 |
(3)日期和时间函数
函数名称 | 作用 |
CURDATE() | 获取系统当前的日期 |
CURTIME() | 获取系统当前的时间 |
SYSDATE() | 系统日期和时间 |
TIME_TO_SEC() | 返回将时间转换为秒的结果 |
ADDDATE() | 执行日期的加运算 |
SBUDATE() | 执行日期的减运算 |
DATE_FORMAT() | 格式化输出日期和时间值 |
(4)条件判读函数
函数名称 | 作用 |
IF(expr, v1, v2) | 如果 expr 表达式为ture,返回v1,否则返回v2 |
IFNULL(v1, v2) | 如果 v1 不为NULL 返回 v1,否则返回 v2 |
CASE expr WHEN v1 THEN r1 [WHEN v2 THEN r2 ...] [ELSE rn] END | 如果 expr 值等于 v1, v2 等,则返回对应位置 THEN 后面的结果,否则返回 ELSE 后的结果 rn |
(5)加密函数
函数名称 | 作用 |
MD5(str) | 对字符串 str 进行MD5 加密 |
ENCODE(str, pwd_str) | 使用 pwd 作为密码加密字符串 str |
DECODE(str, pwd_str) | 使用 pwd 作为密码 解密字符串 str |
例:
SELECT CONCAT (id, '_', name, '_', grade, '-', gender) FROM student;
// 将表“student”中指定的字段内容使用下划线连接后输出。
SELECT *, IF(gender='男', 1, 0) FROM student;
// 将结果根据gender字段属性,对应输出1或者0。
四、为表格字段取别名 [AS]
1、为表取别名
在查询操作时,如果表名很长使用起来就不太方便,这时可以去一个别名,用这个别名来代替表的名称。
SELECT * FORM 表名 [AS] 别名;
2、为字段取别名
SELECT 字段名1 [AS] 别名1 [, 字段名2 [AS] 别名2, ...] FROM 表名;