【数据库】四（1）、数据查询之单表查询

单表查询

参考：SQL单表查询

单表查询语法

select 列名1,列名2... 
from 表名
where 条件
group by field
having 筛选
order by field
limit 限制条数

关键字的执行优先级

1. 找到表:from
2. where指定的约束条件
3. 将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组
4. 将分组的结果进行having过滤
5. 执行select
6. distinct去重
7. 将结果按条件排序：order by
8. limit限制结果的显示条数

实例

假设我们有这么一张表：

表名为：class_score

+----+--------+--------+------+------+
| id | name   | gender | math | eng  |
+----+--------+--------+------+------+
|  1 | 赵艺   | 女     |   85 |   88 |
|  2 | 钱尔   | 女     |   85 |   90 |
|  3 | 孙散   | 男     |   90 |   90 |
|  4 | 赵思   | 男     |   80 |   85 |
|  5 | 周武   | 男     |   80 |   85 |
|  6 | 吴流   | 女     |   90 |   90 |
|  7 | 郑其   | 男     |   70 |   75 |
|  8 | 王     | 男     |   70 |   75 |
|  9 | 冯九   | 男     |   95 |   85 |
| 10 | 陈时   | 男     |   60 |   60 |
+----+--------+--------+------+------+

select *
from class1_score

select *
from class1_score
where gender='男'

select name,math,eng
from class1_score
where math>89 and eng> 89

select *
from class1_score
where gender='男'
having math>70

数据库中where与having的区别

select gender
from class1_score
group by gender

select name,math
from class1_score
where math between 80 and 90

select *
from class1_score
where name like '赵%'

select *
from class1_score
where name like '_思'

select count(id) as '男/女人数' ,gender
from class1_score
group by gender

select sum(math),gender
from class1_score
group by gender

select max(eng),gender
from class1_score
group by gender

select avg(math),gender
from class1_score
group by gender

select top 3 *
from class1_score
Order By math DESC   -- ASC升序，DESC降序 排序键中包含NULL时，会在开头或末尾进行汇总

select name,math
from class1_score
order by math desc
limit 0,3