查询:
1.最简单查询:( 查所有数据)
select * from 表名;注:*在这代表所有的列
select * from info
2.查询指定列
select code ,name from info
3.修改结果集的列名
select code as '代号',name as '姓名' from info
针对于行
4条件查询:
select *from info where code='p003'
5.多条件查询:
查询info表中code为p003或者national 为n001的所有数据
select * from info where code ='p003' or nation='n001'
查询 info表中 code 为 p0004并且 nation 为n001 的数据
select * from info where code ='p004'and nation='n001'
6. 范围查询
select * from car where price>40 and price<= 60
select* from car where price between 40 and 60
【between and】 查询范围
7.离散查询
查询汽车价格在(10,20,30,40,50,60,)中出现的汽车信息
select * from car where price = 10 or price=20 price = 30 or price=40 price = 50 or price=60
select *from car where price in(10,20,30,40,50,60,)
不出现在这里面
select * from car where perice not in(10,20,30,40,50,60,)
8 模糊查询 (关键字查询)
查询汽车表里面名称包含奥迪的
select *from car where name like '%奥德%' %代表任意N个字符
查询汽车表中名称第二个字符为“马”的汽车;
select *from car where name like '_马%' _代表任意一个字符
9.排序查询
select * from car[ order by price asc] asc 代表升序(省略)
select * from car [order by oil desc ] desc 代表降序 不可省略
先按照 brand 升序排, 在按照price 降序拍
select * from car order by brand ,price desc
10. 去重查询
select [distinct] brand from car
11.分页查询
一页显示10条, 当前是第二页
select *from chinastates limit 10,10
一页显示10条, 当前是第二页
limit (n-1)*m, m
12. 聚合函数( 统计函数 )
select count(areacode) from chinastates # 查询数据的总条数
求所有汽车的总价格
select sum ( price) from car #查询总和
求平均
select avg ( pricr) from car #求平均
最大值
select max (price) from car #求最大值
求最小值
select min ( price) from car #最小值
13.分组查询
查询汽车表中每个系列有多少个汽车
select brand ,count(*)from car group by brand
查询汽车表中卖的汽车数量大于3的系列
select *from car group by brand having count(*)>3
【group by brand having 】
转载于:https://www.cnblogs.com/gdbaby/p/6130279.html