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 power = 130; #或用or
3.模糊查询
select * from car where name like '%奥迪%'; %代表任意多个字符包括0个 _代表一个字符
4.排序查询
select * from car order by brand, powers desc; asc升序默认可以不写
5。范围查询
select * from car where price between 40 and 60;
6.离散查询
select * from car where code in('coo1','c003','c005','c007');
select * from car where code not in('coo1','c003','c005','c007');
7.统计查询(聚合函数)
select count(code) from car; 查询数据条数也可以count(*)这么写,性能低
select sum(price) from car; 求和
select max(code) from car; 最大
select min(brand) from car; 最小
select avg(price) from car; 平均
select year(brithday) from dtudent; # 取出生年月的年
8.分页查询
select * from Car limit (n-1)*5,5 #第n页,每页显示五条数据
9.去重查询
select distinct Brand from Car
10.分组查询
select brand,count(*),Brand from Car group by Brand #把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 )