mysql离散查询_MYSQL 查询

查询：

1.普通查询，查所有

select * from info 查所有数据

select code,name from info 查指定列

2.条件查询

select * from info where code='p001' 一个条件

select * from info where name='张三' and nation='n001' 并关系

select * from info where name='张三' or nation='n001' 或关系

3.排序查询

select * from info order by birthday 默认升序排列(asc)降序(desc)

select * from car order by brand,oil desc 多列排序

4.聚合函数

select count(*) from info 取个数

select sum(price) from car 查询price列的和

select avg(price) from car 查询price列的平均值

select max(price) from car 查询price列的最大值

select min(price) from car 查询price列的最小值

5.分页查询

select * from car limit 0(跳过几条数据),5(取几条数据)

6.分组查询

select brand from car group by brand 简单分组查询

select brand from car group by brand having count(*)>2 查询系列里面车的数量大于2的系列

7.去重查询

select distinct brand from car 去重

8.修改列名

select brand as '系列' from car

9.模糊查询

select * from car where name like '_奥迪%' %表示任意多个字符(包含%***%)(_代表一个字符)

10.离散查询

select * from car where code in('c001','c002','c003','c004')

select * from car where code not in('c001','c002','c003','c004')

查询汽车表中 价格大于50 40 39 这三个数中最大的

select * from car where prise >50

高级查询：

1.连接查询：

select * from info,nation 得出的结果称为笛卡尔积，效率低

select * from info,nation where info.nation = nation.code

join on连接：

select * from info join nation join连接

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='汉族' 查询nation表中汉族的民族代号

select * from info where nation = () 在info表中查询民族代号为上一个结果查询的民族代号

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