mysql 查询条件
1简单查询 select* from 表名
select name as“姓名”fromstu (把name改为名字)
2条件查询 where 后面跟条件 条件要写清楚
3模糊查询 like no like %代表任意多个字符 _代表一个字符
4排序查询 order by 字段 排序值 (desc降 asc升)
5范围查询 between....and...
6离散查询 in notin
7聚合查询 sun 求和 count数据条数 max最大值 min最小值 avg平均值
8分页查询 limit 从第几条开始,取多少数据
表名limit(pagesize-1)*5,5 pagesize是页数 *5 是每页条数 ,5 取多少条
9去重查询 distinct select distinct 表段名 from 表名
10分组查询 group by 字段 having 条件
select count(*),cno,group_concat(degree),sum(degree) from score group by cno ;
select cno,group_concat(degree),sum(degree) from score group by cno having count(*)>3
#分组之后根据条件查询使用having 不使用where
高级查询
- 连接查询,对列的扩展
Select * from student as stu,score as sc
where stu.sno = sc.sno and sc.sno = “103” ;
2.联合查询,对行的扩展
select Code,Name from Info
union
select Code,Name from Nation
3.子查询
(1)无关子查询
外层查询 (里层查询)
子查询的结果当做父查询的条件
子查询:select Code from Nation where Name='汉族'
父查询:select * from Info where Nation = ''
select * from Info where Nation = (select Code from Nation where Name='汉族')
(2)相关子查询
查询汽车表中油耗低于该系列平均油耗的所有汽车信息
父查询:select * from Car where Oil<(该系列平均油耗)
子查询:select avg(Oil) from Car where Brand = '某个系列'
select * from Car a where Oil<(select avg(Oil) from Car b where b.Brand = a.Brand )