mysql查询

 

查询条件

(1)简单查询

select * from Info

select Code as '代号',Name as '姓名' from Info

 

2. 条件查询

 Where后面跟条件  条件要写清楚

查询成绩表中成绩（degree）为92的信息

Select * from score where degree =”92”;

查询成绩表中课程号是3-245并且成绩是86的信息

Select * from score where cno='3-245' and degree=”86”

 或者用or  并且用and

 

3. 模糊查询 like  not like

查找老师表中姓李的 名字是两个字老师

select * from teacher

 where tName like '%李%'  

%代表任意多个字符  _代表一个字符

 

 

(4)排序查询 order by 字段 排序值（desc/asc ）

  select * from student order by class asc

 

(5)范围查询 关系运算符  between。。。and

select * from Car where Price>=40 and Price<=60

select * from Car where Price between 40 and 50、

 

(6)离散查询 in   not in

select * from student where sname in ('张三','李四')

。。。where sname =“张三” or  sname =“李四”

 

(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)分页查询 limit 从第几条开始，取多少条数据

#每页显示5条数据，取第2页的数据

select * from student limit (pageSize-1)*5,5 

 

(9)去重查询distinct 

select distinct cno from score;

 

(10)分组查询 group by 字段  having 条件

select count(*),cno,group_concat(degree),sum(degree) from score group by cno ;

select cno,group_concat(degree),sum(degree) from score group by cno having count(*)>3 

#分组之后根据条件查询使用having 不使用where

 

 

高级查询

 

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

   Select * from student as stu,score as sc

where stu.sno = sc.sno and sc.sno = “103” ; 

 

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 )