查询分类

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 Code as '代号',Name 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

高级查询

1.连接查询，对列的扩展

select * from Info,Nation #形成笛卡尔积
select Info.Code,Info.Name,Info.Sex,Nation.Name,Info.Birthday from Info,Nation where Info.Nation = Nation.Code

select * from Info join Nation
select * from Info join Nation on Info.Nation = Nation.Code

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 )