数据库查询——单表查询
假如存在如下的一张数据表(表名:t_student)
![](https://i.imgur.com/WlFMAGR.jpg)
查询所有字段
- select 字段1,字段2,字段3,….from 表名;
select * from 表名;
SELECT stuName,id,age,sex,gradeName FROM t_student ; SELECT * FROM t_student;
查询结果如下:
![](https://i.imgur.com/9yrOboU.jpg)
查询指定字段
select 指定字段1,指定字段2,… from 表名;
SELECT stuName,gradeName FROM t_student;
查询结果如下:
![](https://i.imgur.com/i8L2Z9k.jpg)
Where 条件查询
select 字段1,字段2,… from 表名 where 条件表达式;
SELECT * FROM t_student WHERE id=1;
查询结果如下:
![](https://i.imgur.com/nbHb1mr.jpg)
带IN关键字的查询
select 字段1,字段2,字段3,… from 表名 where 字段 IN(元素1,元素2,元素3,…);
SELECT * FROM t_student WHERE age IN (21,23);
查询结果如下:
![](https://i.imgur.com/wNLLdO7.jpg)
select 字段1,字段2,字段3,… from 表名 where 字段 NOT IN(元素1,元素2,元素3,…);
SELECT * FROM t_student WHERE age NOT IN (21,23);
查询结果如下
![](https://i.imgur.com/6qETPpR.jpg)
带 BETWEEN AND的范围查询
select 字段1,字段2,… from 表名 where 字段 between 取值1 and 取值2;
SELECT * FROM t_student WHERE age BETWEEN 21 AND 24;
查询结果如下:
![](https://i.imgur.com/XM8iEUU.jpg)
select 字段1,字段2,… from 表名 where 字段 NOT BETWEEN 取值1 AND 取值2;
SELECT * FROM t_student WHERE age NOT BETWEEN 21 AND 24;
查询结果如下:
![](https://i.imgur.com/WW5XjaR.jpg)
带LINK的模糊查询
- SELECT 字段 1,字段 2,字段 3…FROM 表名 WHERE 字段 [NOT] LIKE ‘字符串’;
- “%”代表任意字符;
“_” 代表单个字符;
SELECT * FROM t_student WHERE stuName LIKE '张三';
查询结果如下:
![](https://i.imgur.com/CRGDK4M.jpg)
SELECT * FROM t_student WHERE stuName LIKE '张三%';
查询结果如下:
![](https://i.imgur.com/mgyV8f4.jpg)
SELECT * FROM t_student WHERE stuName LIKE '张三__';
查询结果如下:
![](https://i.imgur.com/rpubanP.jpg)
SELECT * FROM t_student WHERE stuName LIKE '%张三%';
查询结果如下:
![](https://i.imgur.com/or3VNSn.jpg)
空值查询
SELECT 字段 1,字段 2,字段 3…FROM 表名 WHERE 字段 IS [NOT] NULL;
SELECT * FROM t_student WHERE sex IS NULL;
查询结果如下:
![](https://i.imgur.com/se5Y4MI.jpg)
SELECT * FROM t_student WHERE sex IS NOT NULL;
查询结果如下:
![](https://i.imgur.com/MSU9lh3.jpg)
带 AND 的多条件查询
SELECT 字段 1,字段 2…FROM 表名 WHERE 条件表达式 1 AND 条件表达式 2 […AND 条件表达式 n]
SELECT * FROM t_student WHERE gradeName='一年级' AND age=23;
查询结果如下:
![](https://i.imgur.com/L71MPsi.jpg)
带 OR 的多条件查询
SELECT 字段 1,字段 2…FROM 表名 WHERE 条件表达式 1 OR 条件表达式 2 […OR 条件表达式 n]
SELECT * FROM t_student WHERE gradeName='一年级' OR age=23
查询结果如下:
![](https://i.imgur.com/aBePr1X.jpg)
DISTINCT 去重复查询
SELECT DISTINCT 字段名 FROM 表名;
SELECT DISTINCT gradeName FROM t_student;
查询结果如下:
![](https://i.imgur.com/54qKOAD.jpg)
对查询结果排序
- SELECT 字段 1,字段 2…FROM 表名 ORDER BY 属性名 [ASC|DESC];
- ASD:升序
DESC:降序
SELECT * FROM t_student ORDER BY age ASC;
查询结果如下:
![](https://i.imgur.com/snlY7Cv.jpg)
SELECT * FROM t_student ORDER BY age DESC;
查询结果如下:
![](https://i.imgur.com/JtHwYmj.jpg)
GROUP BY 分组查询
- GROUP BY 属性名 [HAVING 条件表达式][WITH ROLLUP]
- 1,单独使用(毫无意义);
- 2,与 GROUP_CONCAT()函数一起使用;
- 3,与聚合函数一起使用;
- 4,与 HAVING 一起使用(限制输出的结果);
5,与 WITH ROLLUP 一起使用(最后加入一个总和行);
SELECT * FROM t_student GROUP BY gradeName;
查询结果如下:
![](https://i.imgur.com/qK1olv9.jpg)
SELECT gradeName,GROUP_CONCAT(stuName) FROM t_student GROUP BY gradeName;
查询结果如下:
![](https://i.imgur.com/YlD0ea4.jpg)
SELECT gradeName,COUNT(stuName) FROM t_student GROUP BY gradeName;
查询结果如下:
![](https://i.imgur.com/ke4HMyk.jpg)
SELECT gradeName,COUNT(stuName) FROM t_student GROUP BY gradeName HAVING COUNT(stuName)>3;
查询结果如下:
![](https://i.imgur.com/SHtFcVt.jpg)
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;
查询结果如下:
![](https://i.imgur.com/wPnJI7J.jpg)
LIMIT 分页查询
SELECT 字段 1,字段 2…FROM 表名 LIMIT 初始位置,记录数;
SELECT * FROM t_student LIMIT 0,5;
查询结果如下:
![](https://i.imgur.com/i1EEi0M.jpg)
SELECT * FROM t_student LIMIT 5,5;
查询结果如下:
![](https://i.imgur.com/136Lyut.jpg)
SELECT * FROM t_student LIMIT 10,5;
查询结果如下:
![](https://i.imgur.com/fGLT0Nk.jpg)