查询数据：
简单查询：
1.查询所有数据：
select * from 表名
#select * from info 查询所有列
2.查询指定列：
select code,name from info
3.查询指定行（条件查询）：
select * from info where nation='n001'
select * from info where code='p001' or/and nation='n001'
4.给列制定名称
select code as '代号' name as '姓名' from info
5.模糊查询
select * from car where name like '%/_奥迪%' #'%'---表示在‘奥迪’前后可以出现n个字符，'_'表示在‘奥迪’前后可以出现1个字符
6.排序查询
select * from car order by price #根据价格进行排序，默认升序（asc），后面加desc降序
select * from car order by price asc，oil desc #先根据价格升序排列，在次前提下出现价格相同的项再按油耗降序排列
7.去重查询
select distinct brand from car
8.分页查询
select * from car limit 5,5 跳过前面5条取5条数据
9.统计查询（聚合函数）
数据条数：select count(主键/*) from car
取最大值/最小值：select max/min(price) from car
取平均值：select avg(price) from car
10.分组查询
select brand,count(*) from car group by brand
select brand from car group by brand having count(*)>=3
11.范围查询
select * from car where price>=40 and price <=60
select * from car where price between 40 and 60
12.离散查询
select* from car where price in/not in(10,20,30,40)
高级查询：
1.联合查询
select code,name from info
union
select code,name from nation
2.连接查询
形成笛卡尔积
select * from info,nation where info.nation=nation.code
为了查询效率更高可以筛选出重复的数据：
select info.name,info.sex,nation.name,info.birthday from info,nation where info.nation=nation.code
select info.name,info.sex,nation.name,info.birthday from info join nation on info.nation=nation.code
3.子查询
---子查询的结果作为父查询的条件使用
i.无关子查询
---子查询和父查询没有关系，子查询单独拿出来可以执行
a.查找民族为“汉族”的所有人员信息
select * from info where nation =(select code from nation where name='汉族')
b.查询所有厂商是“一汽大众”的车
select * from car where brand in(select brand_code from brand where prod_code in(select prod_code from productor where prod_code='一汽大众'))
ii.相关子查询
查询油耗低于该系列平均油耗的汽车信息
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)
