数据库基本查询与高级查询
一、基本查询:
select * from Fruit --查所有 select Name,Source from Fruit --查特定列 select Ids '代号',Name '名称',Price '价格',Source '产地' from Fruit -- 修改列名 select * from Fruit where Ids='K006' select * from Fruit where Price=2.4 and Source='烟台' -- 查指定行按条件查 select * from Fruit where Price between 2.0 and 4.0 --查指定行按范围查 select * from Fruit where Numbers in (90,80,70)--查指定行,离散查 select distinct Numbers from Fruit --去重查询 select * from News select * from News where title like '%户口' --模糊查询,查以户口结尾的 select * from News where title like '路飞%' --模糊查询,查以路飞开头的 select * from News where title like '%路飞%' --模糊查询,查以包含路飞的 select * from News where title like '%外币货_'--模糊查询,查外币货之后只有一个字符的 select * from Fruit order by Numbers asc --按照Numbers列升序排,如果不加asc默认以升序排 select * from Fruit order by Numbers desc --按照Numbers列降序排 select * from Fruit order by Numbers,Price --先按照Numbers排,然后再按照Price排 select COUNT(*) from Fruit --返回Fruit表里面有多少条数据 select AVG(Numbers)from Fruit --返回某一列的平均值 select SUM(Numbers) from Fruit --返回某一列的所有数据和 select MAX(Numbers) from Fruit --返回某一列中的最大值 select MIN(Numbers) from Fruit --返回某一列中的最小值 select *,(Price*0.8) as '折后价格' from Fruit --加一列数据库中没有的列,这里是加了8折后的价格列 select Numbers, COUNT(*) from Fruit group by Numbers --根据某一列分组,求出该组内成员的个数 select Numbers, COUNT(*) from Fruit group by Numbers having COUNT(*)>1--根据某一列分组,求出该组内成员的个数,返回成员个数大于1的
二、高级查询:
--连接查询 select * from Info,Nation -- 形成笛卡尔积 select * from Info,Nation where Nation.Code=Info.Nation --join on 内连接 select * from Info join Nation on Info.Nation = Nation.Code --查哪位学生的哪一门课考了多少分 select student.sname,course.cname,score.degree from student join score on score.sno=student.sno join course on course.cno = score.cno --右连接,右边表必须显示全,如果在左边表没有与之对应的信息,则补空值 select * from Info right join Nation on Info.Nation=Nation.Code --左连接,左边表必须显示全,如果在右边表没有与之对应的信息,则补空值 select * from Info left join Nation on Info.Nation=Nation.Code --全连接,左右两边的表都显示完全 select * from Info full join Nation on Info.Nation=Nation.Code --联合查询,对于查出的两个或多个结构相同的表联合显示 select Code,Name from Info union select InfoCode,Name from Family --------子查询------------------ --子查询的结果当做父查询的条件 select * from Info --无关子查询,子查询执行是独立的,和父查询是没有关系的(没有用到父查询的东西) select * from Info where year(Birthday)=( select YEAR(Birthday) from info where Code='p005') --相关子查询 select * from teacher --求计算机系和电子工程系不同职称的老师信息 select * from teacher t1 where depart='计算机系' and not exists( select * from teacher t2 where depart='电子工程系' and t1.prof = t2.prof) union select * from teacher t1 where depart='电子工程系' and not exists( select * from teacher t2 where depart='计算机系' and t1.prof = t2.prof ) --查询除了每门课最高分之外的其他学生信息。 select * from score select * from score where degree not in(select MAX(degree) from score group by cno)--错误 select * from score s1 where degree not in( select MAX(degree) from score s2 group by cno having s1.cno = s2.cno ) --select * from score where degree not in(86,75) --分页 select * from Car select top 5 * from Car -- 前5条数据,第一页 select top 5 * from Car where Code not in( select top 5 Code from Car ) -- 第二页的数据 select top 5 * from Car where Code not in( select top 10 Code from Car ) --第三页的数据 select top 5 * from Car where Code not in( select top (5*2) Code from Car ) select ceiling(COUNT(*)/5) from Car --求总页数 select * from Car where 条件 limit 跳过几条数据,取几条数据 --mysql里面的分页