CRUD操作查询

1.查询整体表格数据

select * from car

显示:

2.查询制定列

select code,price from car
#code  代号    
# price   价格  
#  car   表名

显示:

3.给列指定名称

select code as'代号',name as'车名'from car

显示:　　

4 条件查询

①单条件查询

select * from car where code ='c002'

显示:

②多条件查询(用or或and链接)

select * from car where code='c002'or price='37.16'

　显示:


 

select * from car where code='c002' and price='67.49'

显示:

5 模糊查询(关键字查询)

%关键字，关键字前面有n个字符；关键字%，关键字后面有n个字符,%符号代表n个字符

_关键字，关键字前面有一个字符；关键字_，关键字后面有一个字符,_符号代表一个字符

select * from car where name like '奥迪%'

显示

6 排序查询

select * from car order by price desc 

根据价格降序排列

select *from car order by price desc, oil desc

先根据price降序排列，有重复的 在根据oil降序排列

desc降序  asc升序。 默认为升序

7去重查询

distinct去重

显示:

8 分页查询

select * from car limit 5, 5     #去掉前五条,查询后5条,    当前页-1乘以5

9 统计查询(聚合函数)

select count(*) from car

　

select count(code) from car

select max(列名) from 表名 #取最大值
select min（列名） from 表名#取最小值
select avg （列名）from 表名#取平均值

10 分组查询

查询所有

select * from car group by  brand

select brand ,count(*)from car group by brand

select brand from car group by brand having count(*)>=3 #有having前面必须有group by

#有group by 后面可以没有having

11范围查询  in 和 not in   相反 一个是是这些数的  一个是不是这些数的

select * from car where price between 40 and 60

　显示:

12 离散查询

select * from car where price in (10,20,30,40,50,60)

 

select * from car where price not in (10,20,30,40,50,60)