请选择 进入手机版 | 继续访问电脑版

MySQL的那些事儿(四)高级查询

[复制链接]
滚雪球少年 发表于 2021-1-2 19:46:46 | 显示全部楼层 |阅读模式 打印 上一主题 下一主题
distinct 排重
  1. select distinct depart from teacher;
复制代码
between...and... 查询区间(还可以使用运算符比力)
  1. select * from score where degree between 60 and 80;
复制代码
in/not in 同列大概关系
  1. select * from score where degree in(85,86,88);
复制代码
or 异列大概关系
  1. select * from student where sclass='95031' or sgender='女';
复制代码
order by 开启排序
asc 升序(默认)
desc 降序
  1. select * from student order by sclass desc;
复制代码
  1. //以cno升序,相同的cno再以degree降序 select * from score order by cno asc,degree desc;
复制代码
count 统计
  1. select count(*) from student where sclass='95031';
复制代码
max,min 找最大最小值
  1. select max(degree) as '最高分' from score;
复制代码
limit a,b 分页查询
从a位置开始,查找b条记录
  1. select * from student limit 0,3;
复制代码
avg(degree) 求均匀结果
  1. select avg(degree) from score where cno='3-105';
复制代码
group by 分组
having 分组条件
  1. //求每门课的均匀结果(把课程号相同的分为一组求均匀结果) select avg(degree) from score group by cno;
复制代码
like/not like 暗昧匹配
like 'X%' 匹配以X开头的
like '%X%' 匹配包罗X的
like '%X' 匹配以X末端的
  1. select * from student where sname like '王%';
复制代码
as 为字段取别名
  1. select sname as stu_name,cname as cou_name,degree from student,course,score where student.sno=score.sno and course.cno=score.cno;
复制代码
any 此中至少一个
  1. //查询选修编号为"3-105"课程且结果至少高于选修编号为'3-245'同学的cno,sno和degree, //而且按照degree从高到地序次排序。 select * from score where cno='3-105' and degree>any(select degree from score where cno='3-245') order by degree desc;
复制代码
all 此中全部
  1. select * from score where cno='3-105' and degree>all(select degree from score where cno='3-245');
复制代码
union 将两次查询效果归并(求并集、团结)
  1. select * from teacher where depart='电子工程系' and tprof not in(select tprof from teacher where depart='盘算机系') union select * from teacher where depart='盘算机系' and tprof not in(select tprof from teacher where depart='电子工程系');
复制代码
year(datetime) 从datetime范例的字段中取出年份
month(datetime) 从datetime范例的字段中取出月份
day(datetime) 从datetime范例的字段中取出几号
now() 获取当前datetime
 
子查询:一步一步来。
 
  1. //训练:查询score表中至少有两名学生选修而且以3开头的课程的课程号平静均分数。 select cno,avg(degree) from score group by cno having count(cno)>=2 and cno like &#39;3%&#39;; //训练:两表查询 select sno,cname,degree from course,score where course.cno=score.cno; //训练:三表查询 select sname,cname,degree from student,course,score where student.sno=score.sno and course.cno=score.cno; //训练:求“95031”班每门课的课程号、课程名及均匀结果 select score.cno as s_cno,cname,avg(degree) from score,course where score.cno=course.cno and sno in(select sno from student where sclass=&#39;95031&#39;) group by s_cno; +-------+-----------------+-------------+ | s_cno | cname       |   avg(degree)   | +-------+-----------------+-------------+ | 3-105 | 盘算机导论   |     82.0000     | | 3-245 | 操纵系统     |     71.5000     | | 6-166 | 数字电路     |     80.0000     | +-------+-----------------+-------------+ //训练:查询结果比该课程均匀结果低的同学的结果表(复制表数据查询) SELECT * FROM score AS sc1 WHERE sc1.degree < (SELECT  AVG(degree) FROM score AS sc2 WHERE sc1.cno = sc2.cno);
复制代码
 


来源:https://blog.csdn.net/ngq0273/article/details/112060696
免责声明:如果侵犯了您的权益,请联系站长,我们会及时删除侵权内容,谢谢合作!
回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则


专注素材教程免费分享
全国免费热线电话

18768367769

周一至周日9:00-23:00

反馈建议

27428564@qq.com 在线QQ咨询

扫描二维码关注我们

Powered by Discuz! X3.4© 2001-2013 Comsenz Inc.( 蜀ICP备2021001884号-1 )