MySQL数据库学习笔记之简单查找（知识点）

时间2016.05.26  发布者北宋小康康（koker）

编程代码中增删改查

今天主讲查（select）

1.增加内容
insert into Info values('p001','张三',true,'n001','1989-2-3')
insert into Info (Code,Name) values('p002','李四');

2.删除数据
delete from Info where Code = 'p002'

3.修改数据
update Info set Name='李四' where Code='p001'

4.查询数据

(1)简单查询
select * from Info
select name as'名字'，code as'代号' from info

 

(2)条件查询
select * from Car where Code='c002'
select * from Car where Brand='b001' and Powers=130 或者用or

(3)模糊查询
select * from Car where Name like '%奥迪%' %代表任意多个字符 _代表一个字符

(4)排序查询
select * from Car order by Brand,Powers desc

(5)范围查询
select * from Car where Price>=40 and Price<=60
select * from Car where Price between 40 and 50

(6)离散查询
select * from Car where Code in ('c001','c003','c005','c007')
select * from Car where Code not in('c001','c003','c005','c007')

(7)聚合函数，统计查询
select sum(Price) from Car #查询所有价格之和 sum()求和
select count(Code) from Car #查询数据条数
select max(Code) from Car #求最大值
select min(Brand) from Car #求最小值
select avg(Price) from Car #求平均值

(8)分页查询
#每页显示5条数据，取第2页的数据
select * from Car limit (n-1)*5,5

(9)去重查询
select distinct Brand from Car

(10)分组查询
select count(*),Brand from Car group by Brand
select Brand from Car group by Brand having count(*)>3 #分组之后根据条件查询使用having 不使用where