标王 热搜:
 
当前位置: 首页 » 系统运维 » 数据库 » 正文

JavaÃæÊÔÖгöÏÖÂʼ«¸ßµÄÊý¾Ý¿â²éѯÌâ

放大字体  缩小字体 发布日期:2019-04-27  来源:9JWaqC  作者:xinb2b.cn  浏览次数:113
核心提示:¾­Àú¹ýÃæÊԵijÌÐòÔ±¶¼ÖªµÀ£¬ÃæÊÔ¹ý³ÌÖÐÃæÊÔ¹Ù¿ÉÄܶÔÄãÎʳöǧÆæ°Ù


¾­Àú¹ýÃæÊԵijÌÐòÔ±¶¼ÖªµÀ£¬ÃæÊÔ¹ý³ÌÖÐÃæÊÔ¹Ù¿ÉÄܶÔÄãÎʳöǧÆæ°Ù¹ÖµÄÎÊÌ⣬µ«Íò±ä²»ÀëÆä×Ú£¬ÖÕ¹éÒ²ÊÇÒªÎʵÀÖصãÉÏÀ´£¬±ÈÈçÏñÊÇijһ¹¤×÷µÄ»ù±¾²Ù×÷²½ÖèÒÔ¼°´úÂëµÄÈçºÎ±àдµÈµÈ£¬±¾ÎĽ²µÄ¾ÍÊÇ×î×î¾­µäµÄÊý¾Ý¿â²éѯÎÊÌâ¡£

1554349207837929.jpg

»ù±¾±í½á¹¹£º

teacher(tno,tname) ½Ìʦ±í

student(sno,sname,sage,ssex)ѧÉú±í

course(cno,cname,tno) ¿Î³Ì±í

sc(sno,cno,score) ³É¼¨±í

NO.1²éѯ¿Î³Ì1µÄ³É¼¨±È¿Î³Ì2µÄ³É¼¨¸ßµÄËùÓÐѧÉúµÄѧºÅ

select a.sno from(select sno,score from sc where cno=1) a,(select sno,score from sc where cno=2) bwhere a.score>b.score and a.sno=b.sno

NO.2²éѯƽ¾ù³É¼¨´óÓÚ60·ÖµÄͬѧµÄѧºÅºÍƽ¾ù³É¼¨

select a.sno as "ѧºÅ", avg(a.score) as "ƽ¾ù³É¼¨" from(select sno,score from sc) a group by sno having avg(a.score)>60

NO.2²éѯËùÓÐͬѧµÄѧºÅ¡¢ÐÕÃû¡¢Ñ¡¿ÎÊý¡¢×ܳɼ¨

select a.sno as ѧºÅ, b.sname as ÐÕÃû,count(a.cno) as Ñ¡¿ÎÊý, sum(a.score) as ×ܳɼ¨from sc a, student bwhere a.sno = b.snogroup by a.sno, b.sname

»òÕߣº

selectstudent.sno as ѧºÅ, student.sname as ÐÕÃû, count(sc.cno) as Ñ¡¿ÎÊý, sum(score) as ×ܳɼ¨from student left Outer join sc on student.sno = sc.snogroup by student.sno, sname

NO.3²éѯÐÕ¡°ÕÅ¡±µÄÀÏʦµÄ¸öÊý

selectcount(distinct(tname)) from teacher where tname like 'ÕÅ%¡®

»òÕߣº

select tname as "ÐÕÃû", count(distinct(tname)) as "ÈËÊý" from teacher where tname like'ÕÅ%'group by tname

NO.4²éѯûѧ¹ý¡°ÕÅÈý¡±ÀÏʦ¿ÎµÄͬѧµÄѧºÅ¡¢ÐÕÃû

select student.sno,student.sname from student
where sno not in (select distinct(sc.sno) from sc,course,teacher
where sc.cno=course.cno and teacher.tno=course.tno and teacher.tname='ÕÅÈý')

NO.5²éѯͬʱѧ¹ý¿Î³Ì1ºÍ¿Î³Ì2µÄͬѧµÄѧºÅ¡¢ÐÕÃû

select sno, sname from studentwhere sno in (select sno from sc where sc.cno = 1)and sno in (select sno from sc where sc.cno = 2)

»òÕߣº

selectc.sno, c.sname from(select sno from sc where sc.cno = 1) a,(select sno from sc where sc.cno = 2) b,student cwhere a.sno = b.sno and a.sno = c.sno

»òÕߣº

select student.sno,student.sname from student,sc where student.sno=sc.sno and sc.cno=1and exists( select * from sc as sc_2 where sc_2.sno=sc.sno and sc_2.cno=2)

NO.6²éѯѧ¹ý¡°ÀîËÄ¡±ÀÏʦËù½ÌËùÓпγ̵ÄËùÓÐͬѧµÄѧºÅ¡¢ÐÕÃû

select a.sno, a.sname from student a, sc bwhere a.sno = b.sno and b.cno in(select c.cno from course c, teacher d where c.tno = d.tno and d.tname = 'ÀîËÄ')

»òÕߣº

select a.sno, a.sname from student a, sc b,(select c.cno from course c, teacher d where c.tno = d.tno and d.tname = 'ÀîËÄ') ewhere a.sno = b.sno and b.cno = e.cno

NO.7²éѯ¿Î³Ì±àºÅ1µÄ³É¼¨±È¿Î³Ì±àºÅ2µÄ³É¼¨¸ßµÄËùÓÐͬѧµÄѧºÅ¡¢ÐÕÃû

select a.sno, a.sname from student a,

(select sno, score from sc where cno = 1) b,

(select sno, score from sc where cno = 2) c

where b.score > c.score and b.sno = c.sno and a.sno = b.sno

NO.8²éѯËùÓпγ̳ɼ¨Ð¡ÓÚ60·ÖµÄͬѧµÄѧºÅ¡¢ÐÕÃû

select sno,sname from studentwhere sno not in (select distinct sno from sc where score > 60)

NO.9²éѯÖÁÉÙÓÐÒ»ÃſγÌÓëѧºÅΪ1µÄͬѧËùѧ¿Î³ÌÏàͬµÄͬѧµÄѧºÅºÍÐÕÃû

select distinct a.sno, a.snamefrom student a, sc bwhere a.sno <> 1 and a.sno=b.sno andb.cno in (select cno from sc where sno = 1)

»òÕߣº

select s.sno,s.sname from student s,(select sc.sno from scwhere sc.cno in (select sc1.cno from sc sc1 where sc1.sno=1)and sc.sno<>1group by sc.sno)r1where r1.sno=s.sno

ÒÔÉϾÍÊÇÃæÊÔÊý¾Ý¿âÏà¹Ø¹¤×÷»Ò³£ÓпÉÄÜÓöµ½µÄÌâÄ¿£¬¸Ï½ôÊÕ²ØÆðÀ´£¬ºÃºÃ¿´¿´°É£¡

¡¾ÍƼö¿Î³Ì£ºMYSQLѧϰÊÓƵ¡¿

ÒÔÉϾÍÊÇJavaÃæÊÔÖгöÏÖÂʼ«¸ßµÄÊý¾Ý¿â²éѯÌâµÄÏêϸÄÚÈÝ£¬¸ü¶àÇë¹Ø×¢phpÖÐÎÄÍøÆäËüÏà¹ØÎÄÕ£¡

9JWaqC
 
关键词: Java
 
[ 系统运维搜索 ]  [ 加入收藏 ]  [ 告诉好友 ]  [ 打印本文 ]  [ 违规举报 ]  [ 关闭窗口 ]
 
推荐图文
MySQL数学函数的简单总结 MongoDB的常用Query操作的介绍(附代码)
MySQL视图简介与操作的介绍(附代码) MySQL如何实现更新查询?
推荐系统运维
点击排行
 
网站首页 | 关于我们 | 联系方式 | 使用协议 | 版权隐私 | 网站地图 | 排名推广 | 广告服务 | 积分换礼 | 网站留言 | RSS订阅