MySQL单表查询

MySQL单表查询主要关键字。

select

select '内容/实体' (as) 字段名/属性		#as 取别名用

from

from ??? 来自哪张表
多表查询结果是笛卡尔积

dual(伪表)

select 2*7 (as) res from dual;
# 等价于
select 2*7 (as) res

where(筛选)

符号：< <= >= = ……

in

in(‘’), not in(‘’)等价于 = 和!=

select * from t4 where address in('Beijing', 'Shanghai');

between…and

where age >= 15 and age <= 20等价于where age between 15 and 20

is null

select * from t3 where age is not null

聚合函数（自带函数）

select sum(chinese) from score

函数：

sum()
avg()
max()
min()
count() 注意谨慎使用count(*)

like模糊查询

select * from student where name like '张%';
select * from student where name like '高_';
# %匹配多个字符，_匹配单个字符

order by排序查询

ascend 升华 descend 下降

select * from score order by chinese asc;   # asc升序
select * from score order by chinese desc;  # desc降序

group by分组查询

select avg(age) as '年龄', gender as '性别’ from info group by gender;

group_concat(聚合查询信息)

group_concat 将查询结果中同组的name组合在一起

select group_concat(name), gender from student group by gender;

having

having，在已经筛选的结果中再次筛选

select avg(age) as '年龄', address as '地区' from info group by address having age > 24;

limit

select * from info order by desc limit 3
# limit x, y x：开始位置，y：长度
# limit x x：开始位置

distinct(去重)

select count(distinct address) from info