MySchool项目业务

1.查询全部17级的学生信息

select 
s.StudentNo,
s.StudentName,
s.Sex,
g.GradeName,
s.Phone,
s.[Address],
s.BornDate,
s.Email 
from 
Student as s,
Grade as g 
where 
s.GradeId = g.GradeId 
and g.GradeName = '17'

关键词:17

解题思路:
首先我们需要学生信息表年级信息表的数据,虽然不需要直接显示年级信息,但仍然需要进行年级判断。所以要引入。在进行复数表查询的时候必须进行条件链接。
需要注意的是:最好将列名列表补全

执行结果

StudentNoStudentNameSexGradeNamePhoneAddressBornDateEmail
S1101001金蝶17717215147天津市河西区2002-11-09 00:00:00.000okiwcydml@ryhlo.com
S1101002洛飞17666762663天津市南开区2003-02-07 00:00:00.000jnqlpkdwb@nsjpt.com
S1101003凌辉17353149818北京市海淀区成府路1993-04-04 00:00:00.000eepispykh@oitbl.com
S1101004白燕17676151367学生宿舍2003-09-01 00:00:00.000cxmnnrhfn@sjsam.com
S1101005夏一桐17397739963北京市朝阳区大屯2002-09-12 00:00:00.000npgiygxox@ootml.com
S1101006欧阳燕飞1713512345678河南省南阳市1987-06-19 00:00:00.000dkwkiqbrj@yrkro.com
S1101007孟祥亚1713512345679河南省洛阳市涧西区1993-02-22 00:00:00.000gekdinmky@ijisq.com
S1101008凌洋1715812345680湖南省长沙2002-11-30 00:00:00.000NULL
S1101009杨阳1713512345681上海市长虹区2002-01-19 00:00:00.000NULL
S1101010方晴1713412345682山东省聊城市2003-07-12 00:00:00.000NULL
S1101011圆荷1713512344483河北省石家庄2002-03-16 00:00:00.000idfwxlbjr@bkxko.com
S1101012崔今生1713512345684河北省邯郸市2003-01-05 00:00:00.000qrakldetd@ogtso.com
S1101013姜北17578346237学生宿舍2003-01-25 00:00:00.000soppebati@hwxpo.com
S1101014姜丫丫17010926457035北京市朝阳区慧忠里2004-06-24 00:00:00.000rfkhgcxhy@oomkw.com
S1101015孙河174567894北京市通州区2003-06-25 00:00:00.000kdaqojpjj@olmcy.com
S1101016王可17328884827北京市石景山2003-06-26 00:00:00.000bojzfsixf@qjwgw.com
S1101017赵七17511686053北京市海淀区中关村1985-06-27 00:00:00.000ltshcitdp@qdpeh.com
S1101018买燕17155426854河南省周口2003-06-28 00:00:00.000rmpnhbiop@mwrom.com
S1101019冯征17436987577湖北省襄樊1987-06-29 00:00:00.000mdokehdic@omcte.com
S1101020冯中17786340887天津市南开区2003-06-30 00:00:00.000lwspyimci@kcplq.com
S114河马1712345678901湖北武汉2015-12-19 15:09:43.257hema@163.com
S1201902001张三1701062768866解放路2005-01-01 00:00:00.000zhangsan@126.com
S1201902002李四1713812345678长江路2002-02-01 00:00:00.000未知@
S1201902003王五1713912345678学生宿舍2001-05-03 00:00:00.000未知@
S1201902004王丽丽1713112345678山东省济南市文化路1号院1987-06-02 00:00:00.000wangll@sohu.com
S5106001河马17180811111111男校区宿舍2006-04-01 00:00:00.000hema@qq.com

2.查询所有女同学的信息

>select 
s.StudentNo,
s.StudentName,
s.Sex,
g.GradeName, -- 友好的以年级名称显示
s.Phone,
s.[Address],
s.BornDate,
s.Email 
from 
Student as s,
Grade as g 
where s.GradeId = g.GradeId -- 条件链接
and s.Sex = '女'

解题思路:

数据来源:学生信息表
判断条件:性别为女
优化:年级ID改为年级NAME 因此 额外链接年级信息表


执行结果

StudentNoStudentNameSexGradeNamePhoneAddressBornDateEmail
S1101002洛飞17666762663天津市南开区2003-02-07 00:00:00.000jnqlpkdwb@nsjpt.com
S1101003凌辉17353149818北京市海淀区成府路1993-04-04 00:00:00.000eepispykh@oitbl.com
S1101008凌洋1715812345680湖南省长沙2002-11-30 00:00:00.000NULL
S1101011圆荷1713512344483河北省石家庄2002-03-16 00:00:00.000idfwxlbjr@bkxko.com
S1101012崔今生1713512345684河北省邯郸市2003-01-05 00:00:00.000qrakldetd@ogtso.com
S1101017赵七17511686053北京市海淀区中关村1985-06-27 00:00:00.000ltshcitdp@qdpeh.com
S1201902004王丽丽1713112345678山东省济南市文化路1号院1987-06-02 00:00:00.000wangll@sohu.com
S2102001包卫丽1813875940258湖州德清2003-07-01 00:00:00.000sakuralove2829@yahoo.com
S2102004朱月清1813875940261衢州开化2003-07-04 00:00:00.000yinyin01094@yahoo.com.hk
S2102006吴宝玉1813875940263湖州埭溪2000-12-01 00:00:00.000NULL
S2102007胡梦秋1813875940264杭州临安2002-12-02 00:00:00.000bobo_lok13@yahoo.com
S2102008吴春红1813875940265金华金东区2002-12-03 00:00:00.000yintakli@yahoo.com.hk
S2102014卢凤华1813574220089衢州开化2002-09-21 00:00:00.000sulia123@yahoo.com
S2102015胡灵1813574220190台州天台2002-09-22 00:00:00.000chankwaiching@yahoo.com
Y21003004章巧丹1913174220199宁波宁海2002-06-09 00:00:00.000NULL
Y21003005刘丽云1913174220200学生宿舍2001-11-12 00:00:00.000raymondchoi04@yahoo.com
Y21003007李明华1913574220202金华兰溪2001-03-19 00:00:00.000mimichoi276@yahoo.com
Y21003013刘雪琼1913374220208绍兴嵊州2002-06-26 00:00:00.000wengsanc16l@yahoo.com
Y21003014胡晓奕1913374220209学生宿舍2002-09-23 00:00:00.000wengsanc17l@yahoo.com

3.查询超过60个课时的科目信息

select
 * 
from 
Subject 
where 
ClassHour >= 60


执行结果

SubjectIdSubjectNameClassHourgradeId
1HTML和CSS网页技术601
2C#语言和数据库技术701
4深入.NET平台和C#编程762
10使用ASP.NET技术开发网上书店863
11开发基于Ajax和控件技术的Web应用系统603
13使用Java企业级技术开发企业应用683

4.创建一个17级毕业生通讯录

select 
StudentName,
Sex,
Phone,
BornDate,
Email 
into 
TongXun 
from 
student

列名列表的最后加入into才能创建新表


信息来源:学生信息表
没有判断条件,此题目考点在于如何基于查询建表。重点是into语句及其的插入位置。

执行结果(在新表TongXun中)

StudentNameSexPhoneBornDateEmail
金蝶7172151472002-11-09 00:00:00.000okiwcydml@ryhlo.com
洛飞6667626632003-02-07 00:00:00.000jnqlpkdwb@nsjpt.com
凌辉3531498181993-04-04 00:00:00.000eepispykh@oitbl.com
白燕6761513672003-09-01 00:00:00.000cxmnnrhfn@sjsam.com
夏一桐3977399632002-09-12 00:00:00.000npgiygxox@ootml.com
欧阳燕飞135123456781987-06-19 00:00:00.000dkwkiqbrj@yrkro.com
孟祥亚135123456791993-02-22 00:00:00.000gekdinmky@ijisq.com
凌洋158123456802002-11-30 00:00:00.000NULL
杨阳135123456812002-01-19 00:00:00.000NULL
方晴134123456822003-07-12 00:00:00.000NULL
圆荷135123444832002-03-16 00:00:00.000idfwxlbjr@bkxko.com
崔今生135123456842003-01-05 00:00:00.000qrakldetd@ogtso.com
姜北5783462372003-01-25 00:00:00.000soppebati@hwxpo.com
姜丫丫0109264570352004-06-24 00:00:00.000rfkhgcxhy@oomkw.com
孙河45678942003-06-25 00:00:00.000kdaqojpjj@olmcy.com
王可3288848272003-06-26 00:00:00.000bojzfsixf@qjwgw.com
赵七5116860531985-06-27 00:00:00.000ltshcitdp@qdpeh.com
买燕1554268542003-06-28 00:00:00.000rmpnhbiop@mwrom.com
冯征4369875771987-06-29 00:00:00.000mdokehdic@omcte.com
冯中7863408872003-06-30 00:00:00.000lwspyimci@kcplq.com
河马123456789012015-12-19 15:09:43.257hema@163.com
张三010627688662005-01-01 00:00:00.000zhangsan@126.com
李四138123456782002-02-01 00:00:00.000未知@
王五139123456782001-05-03 00:00:00.000未知@
王丽丽131123456781987-06-02 00:00:00.000wangll@sohu.com
包卫丽138759402582003-07-01 00:00:00.000sakuralove2829@yahoo.com
吴可鹏138759402592005-07-02 00:00:00.000peggyonhkhk@yahoo.com
於炉冰138759402602003-07-03 00:00:00.000yyyyyyyyyyu@yahoo.com
朱月清138759402612003-07-04 00:00:00.000yinyin01094@yahoo.com.hk
赵献星138759402622002-11-30 00:00:00.000ada19262002@hotmail.com
吴宝玉138759402632000-12-01 00:00:00.000NULL
胡梦秋138759402642002-12-02 00:00:00.000bobo_lok13@yahoo.com
吴春红138759402652002-12-03 00:00:00.000yintakli@yahoo.com.hk
张文俊139748077502004-12-04 00:00:00.000natalielam01098@yahoo.com
裴珍138731403862002-09-28 00:00:00.000lingling52030996@hotmail.com
周巧飞131074284842002-09-19 00:00:00.000yuenfunki@yahoo.com.hk
王晓文135742200882004-09-20 00:00:00.000kykwok9@hotmail.com
卢凤华135742200892002-09-21 00:00:00.000sulia123@yahoo.com
胡灵135742201902002-09-22 00:00:00.000chankwaiching@yahoo.com
盛立135742201912002-09-23 00:00:00.000yankchan2004@yahoo.com
吴孝红136789456172002-09-24 00:00:00.000ayako04620@yahoo.com
曾飞燕136789456181993-09-25 00:00:00.000ayako04620@yahoo.com
陈科136789456192002-06-12 00:00:00.000NULL
乐晓燕130074264772002-12-05 00:00:00.000NULL
严丽丽136789456202003-05-04 00:00:00.000phoebe_swy@yahoo.com
河马1808111111112006-04-01 00:00:00.000hema@qq.com
hema111111112222015-11-26 13:49:23.000123123123@163.com
孙瑞晨135742201962002-05-04 00:00:00.000faat11@yahoo.com
黄金宵135742201972002-01-24 00:00:00.000NULL
陈婷婷135742201982002-04-07 00:00:00.000mandylau2000@yahoo.com
章巧丹131742201992002-06-09 00:00:00.000NULL
刘丽云131742202002001-11-12 00:00:00.000raymondchoi04@yahoo.com
丁 亮131742202012002-01-13 00:00:00.000tlh10202003@yahoo.com
李明华135742202022001-03-19 00:00:00.000mimichoi276@yahoo.com
金 晶135742202032001-03-20 00:00:00.000mapdhhljp@ncqfg.com
张世庆135742202042003-03-21 00:00:00.000godspnrel@eblox.com
方汝滔135742202052006-03-22 00:00:00.000wieiowzmq@jplop.com
王 波135742202062001-03-23 00:00:00.000wengsanc14l@yahoo.com
颜俊俊133742202072001-03-24 00:00:00.000wengsanc15l@yahoo.com
刘雪琼133742202082002-06-26 00:00:00.000wengsanc16l@yahoo.com
胡晓奕133742202092002-09-23 00:00:00.000wengsanc17l@yahoo.com
金辉135742201112002-04-24 00:00:00.000wengsanc19l@yahoo.com
郑峰135742201122006-09-24 00:00:00.000wengsanc20l@yahoo.com
何国英135742201132001-03-01 00:00:00.000NULL
方振135742201142002-11-29 00:00:00.000wengsanc22l@yahoo.com
雷应飞135742201152002-01-16 00:00:00.000wengsanc23l@yahoo.com

5.查询18级所有科目

select 
g.GradeName,
SubjectName,
ClassHour 
from 
subject s,
grade g 
where 
s.GradeId = g.GradeId 
and g.gradeName like '18%'


重点:模糊查询like语句


解题思路:
数据来源:科目信息表年级信息表。因为要显示年级名称,而且查询者也不一定知道年级ID
条件:模糊查询18级和18级的特殊班级。

执行结果

GradeNameSubjectNameClassHour
18深入.NET平台和C#编程76
18设计MySchool数据库42
18基于.NET平台的软件系统分层开发44
18面向对象程序设计59
18使用JavaScript增强交互效果40
18使用Java EE技术开发新闻发布系统55

6.查询18级男同学的姓名和生日

select 
s.StudentName,
s.BornDate 
from 
Student as s,
Grade as g 
where 
s.GradeId = g.GradeId 
and g.GradeName = '18' 
and s.Sex = '男'


重点:遇到条件复杂的题目,最好进行分析分类,就像本文的“解题思路”一栏,毕竟分好类之后所有的问题都很好解决了。


解题思路:
数据来源:学生信息表年级信息表。加入年级表具体原因我不想再说了。
条件:年级为18级,性别为男。
注意一定要链接

执行结果

StudentNameBornDate
吴可鹏2005-07-02 00:00:00.000
於炉冰2003-07-03 00:00:00.000
赵献星2002-11-30 00:00:00.000
张文俊2004-12-04 00:00:00.000
裴珍2002-09-28 00:00:00.000
周巧飞2002-09-19 00:00:00.000
王晓文2004-09-20 00:00:00.000
盛立2002-09-23 00:00:00.000
吴孝红2002-09-24 00:00:00.000
曾飞燕1993-09-25 00:00:00.000
陈科2002-06-12 00:00:00.000
乐晓燕2002-12-05 00:00:00.000
严丽丽2003-05-04 00:00:00.000

不要怀疑,这些人的性别信息真的是男

7.无邮箱的学生姓名和年级信息

select 
s.StudentName,
g.GradeName 
from 
Student as s,
Grade as g 
where 
s.GradeId = g.GradeId 
and s.Email is null
and s.Email = ''


需要注意!null必须用is,不能使用=,否则无匹配数据(执行不会报错但没有任何数据)。


解题思路:
数据来源:学生信息表年级信息表
列要求:姓名,班级(根据题目,虽然建议列出尽量多的列,但更应该按照题目做事。)
条件:Email是null或Email是空文本。

执行结果

StudentNameGradeName
凌洋17
杨阳17
方晴17
吴宝玉18
陈科18
乐晓燕18
黄金宵19
章巧丹19
何国英19

8.查询出生日期在2003年之后的18级学生姓名和班级信息

select 
s.StudentName,
g.GradeName 
from 
Student as s,
Grade as g 
where 
s.BornDate > '2003-01-01' 
and g.GradeName ='18'


重点:日期时间也可以比较大小,较晚的较大


解题思路:
数据来源:学生信息表,年级信息表
列要求:姓名,班级
条件:生日大于或等于2003年1月,年级为18级

执行结果

StudentNameGradeName
洛飞18
白燕18
方晴18
崔今生18
姜北18
姜丫丫18
孙河18
王可18
买燕18
冯中18
河马18
张三18
包卫丽18
吴可鹏18
於炉冰18
朱月清18
张文俊18
王晓文18
严丽丽18
河马18
hema18
张世庆18
方汝滔18
郑峰18
洛飞18
白燕18
方晴18
崔今生18
姜北18
姜丫丫18
孙河18
王可18
买燕18
冯中18
河马18
张三18
包卫丽18
吴可鹏18
於炉冰18
朱月清18
张文俊18
王晓文18
严丽丽18
河马18
hema18
张世庆18
方汝滔18
郑峰18

9.查询1月份过生日的学生

select 
StudentName 
from 
Student 
where 
MONTH(BornDate) = 1


我直接月份处理生日不就嘚了吗,为啥还要写格式???


重点:聚合函数


解题思路:
数据来源:学生信息表
列要求:这里的列要求是课上说的,本题目的应用场景是统计1月份过生日的人员名单。所以不需要显示其他数据。
条件:生日的月份为1,需要用到聚合函数将生日这整个日期时间类型的数据

执行结果

StudentName
杨阳
崔今生
姜北
张三
黄金宵
丁 亮
雷应飞

单列无法生成表格

10.查询各个班级的各科成绩的最高分及姓名信息

select 
g.GradeName,
u.SubjectName,
MAX(r.StudentResult) as MaxResult 
from [Subject] as u,
Grade as g,
Result as r 
group by 
g.GradeName,
u.SubjectName 
order by g.GradeName,
u.SubjectName

这是我昨天的答案,今天仔细检查后发现,错了。
今天整理了下思路,还是没能做出来。
这是今天的代码:

select
mar.GradeName,
mar.SubjectName,
s.StudentName,
mar.MaxResult
from
(
    select 
    top 150
    g.GradeName,
    u.SubjectName,
    MAX(r.StudentResult) as MaxResult 
    from 
    [Subject] as u,
    Grade as g,
    Result as r
    group by 
    g.GradeName,
    u.SubjectName
    order by 
    g.GradeName,
    u.SubjectName
) as mar,
Result as r,
Student as s,
Subject as sub,
Grade as g
where
sub.SubjectName = mar.SubjectName and
sub.SubjectId = r.SubjectId and
r.StudentNo = s.StudentNo and
r.StudentResult = mar.MaxResult and
s.GradeId = g.GradeId and
g.GradeName = mar.GradeName


很烧脑对不对?我感觉我人都要疯了。

首先,针对这一题,还是要分析信息来源。

班级、科目、成绩、姓名

至少四表连查!
为什么说是至少呢?因为我不知道解开这道题需要多少额外数据的帮助。
首先,我昨天写了一个普通的分组查询。没错是普通的,虽然看上去难了点,但静下心来是可以写出来的。
但是我写出来之后只检查了数据数量基本合理,并没有进行数据的核对。
今天重新审题先发现,题目要求还要显示名字!但是姓名这个列既不能被分组,也不能被函数。而不满足这两个条件的列是不能出现在有group by的语句之中的。之后我就使用了嵌套查询。这就是今天这部分的代码的由来。
但我发现了更加严重的问题。我昨天的查询本来就有问题!因为成绩表中考100的就俩人!这在输出名字的时候非常明显。而这个问题我又思考了良久,发现一些科目不是所有的班级都要考的,但即便这个班级不考这个科目,order by还是会将科目插入该班级。这样自然就会造成异常。

我感觉这个题目很正常,但我们的数据库貌似有点问题。或者以我们的数据库情况,需要适当调整需求?

最后修改:2020 年 02 月 26 日 08 : 23 PM
如果觉得我的文章对你有用,请随意赞赏