mysql 查询数据
单表查询
查询所有字段
SELECT 字段 1,字段 2,字段 3...FROM 表名;
SELECT id,stuName,age,sex,gradeName FROM t_student ; SELECT stuName,id,age,sex,gradeName FROM t_student ;
SELECT * FROM 表名;
SELECT * FROM t_student
查询指定字段
SELECT 字段 1,字段 2,字段 3...FROM 表名;
SELECT stuName,gradeName FROM t_student;
Where条件查询
SELECT 字段 1,字段 2,字段 3...FROM 表名 WHERE 条件表达式;
SELECT * FROM t_student WHERE id=1;
SELECT * FROM t_student WHERE age>22;
带IN关键字查询
SELECT 字段 1,字段 2,字段 3...FROM 表名 WHERE 字段 [NOT] IN (元素 1,元素 2,元素 3);
SELECT * FROM t_student WHERE age IN (21,23); SELECT * FROM t_student WHERE age NOT IN (21,23);
带BETWEENAND 的范围查询
SELECT 字段 1,字段 2,字段 3...FROM 表名 WHERE 字段 [NOT] BETWEEN 取值 1 AND 取值 2;
SELECT * FROM t_student WHERE age BETWEEN 21 AND 24; SELECT * FROM t_student WHERE age NOT BETWEEN 21 AND 24;
带LIKE的模糊查询
SELECT 字段 1,字段 2,字段 3...FROM 表名 WHERE 字段 [NOT] LIKE ‘字符串’;
“%”代表任意字符;
“_” 代表单个字符
SELECT * FROM t_student WHERE stuName LIKE '张三'; SELECT * FROM t_student WHERE stuName LIKE '张三%'; SELECT * FROM t_student WHERE stuName LIKE '张三__'; SELECT * FROM t_student WHERE stuName LIKE '%张三%'
空值查询
SELECT 字段 1,字段 2,字段 3...FROM 表名 WHERE 字段 IS [NOT] NULL;
SELECT * FROM t_student WHERE sex IS NULL; SELECT * FROM t_student WHERE sex IS NOT NULL;
带AND的多条件查询
SELECT 字段 1,字段 2...FROM 表名 WHERE 条件表达式 1 AND 条件表达式 2 [...AND 条件表达式 n]
SELECT * FROM t_student WHERE gradeName='一年级' AND age=23
带OR的多条件查询
SELECT 字段 1,字段 2...FROM 表名 WHERE 条件表达式 1 OR 条件表达式 2 [...OR 条件表达式 n]
SELECT * FROM t_student WHERE gradeName='一年级' OR age=23
DISTINCT去重复查询
SELECT DISTINCT 字段名 FROM 表名;
SELECT DISTINCT gradeName FROM t_student;
对查询结果排序
SELECT 字段 1,字段 2...FROM 表名 ORDER BY 属性名 [ASC|DESC]
SELECT * FROM t_student ORDER BY age ASC; SELECT * FROM t_student ORDER BY age DESC;
GROUP BY分组查询
GROUP BY 属性名 [HAVING 条件表达式][WITH ROLLUP]
1,单独使用(毫无意义);
SELECT * FROM t_student GROUP BY gradeName;
2,与 GROUP_CONCAT()函数一起使用;
SELECT gradeName,GROUP_CONCAT(stuName) FROM t_student GROUP BY gradeName;
3,与聚合函数一起使用;
SELECT gradeName,COUNT(stuName) FROM t_student GROUP BY gradeName;
4,与 HAVING 一起使用(限制输出的结果);
SELECT gradeName,COUNT(stuName) FROM t_student GROUP BY gradeName HAVING COUNT(stuName)>3;
5,与 WITH ROLLUP 一起使用(最后加入一个总和行);
SELECT gradeName,COUNT(stuName) FROM t_student GROUP BY gradeName WITH ROLLUP; SELECT gradeName,GROUP_CONCAT(stuName) FROM t_student GROUP BY gradeName WITH ROLLUP;
LIMIT分页查询
SELECT 字段 1,字段 2...FROM 表名 LIMIT 初始位置,记录数;
SELECT * FROM t_student LIMIT 0,5;#从第1条开始,共查询5条 SELECT * FROM t_student LIMIT 5,5; SELECT * FROM t_student LIMIT 10,5;